-- RateRight v2 Database Schema
-- Run this in the Supabase SQL Editor

-- ============================================
-- ENUMS
-- ============================================

CREATE TYPE user_type AS ENUM ('worker', 'contractor');
CREATE TYPE availability_type AS ENUM ('full-time', 'casual', 'weekends', 'flexible');
CREATE TYPE rate_type AS ENUM ('hourly', 'daily');
CREATE TYPE job_status AS ENUM ('draft', 'active', 'filled', 'closed');
CREATE TYPE match_status AS ENUM ('suggested', 'applied', 'accepted', 'rejected', 'hired');
CREATE TYPE action_status AS ENUM ('pending', 'accepted', 'rejected');
CREATE TYPE payment_status AS ENUM ('pending', 'charged', 'refunded');
CREATE TYPE notification_type AS ENUM ('match', 'hire_request', 'hire_confirmed', 'rating', 'job_posted', 'system');

-- ============================================
-- HELPER FUNCTION: auto-update updated_at
-- ============================================

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- TABLES
-- ============================================

-- Profiles (extends Supabase Auth)
CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES auth.users ON DELETE CASCADE,
  type user_type NOT NULL,
  name text,
  phone text,
  email text,
  avatar_url text,
  location point,
  suburb text,
  verified boolean DEFAULT false,
  onboarding_completed boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  ai_generated boolean DEFAULT false
);

CREATE TRIGGER profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Companies (contractor-specific)
CREATE TABLE companies (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id uuid NOT NULL UNIQUE REFERENCES profiles ON DELETE CASCADE,
  name text NOT NULL,
  abn text,
  address text,
  industry text,
  size text,
  website text,
  logo_url text,
  ai_research jsonb,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TRIGGER companies_updated_at
  BEFORE UPDATE ON companies
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Worker profiles (worker-specific)
CREATE TABLE worker_profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id uuid NOT NULL UNIQUE REFERENCES profiles ON DELETE CASCADE,
  trades text[] DEFAULT '{}',
  experience_years integer,
  certifications text[] DEFAULT '{}',
  white_card_number text,
  white_card_verified boolean DEFAULT false,
  availability availability_type,
  languages text[] DEFAULT '{}',
  bio text,
  voice_note_url text,
  ai_extracted jsonb,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TRIGGER worker_profiles_updated_at
  BEFORE UPDATE ON worker_profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Jobs
CREATE TABLE jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES companies ON DELETE CASCADE,
  title text NOT NULL,
  description text,
  trade text NOT NULL,
  location point,
  suburb text,
  site_address text,
  rate_min decimal,
  rate_max decimal,
  rate_type rate_type DEFAULT 'hourly',
  start_date date,
  duration text,
  workers_needed integer DEFAULT 1,
  certifications_required text[] DEFAULT '{}',
  status job_status DEFAULT 'draft',
  ai_generated boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TRIGGER jobs_updated_at
  BEFORE UPDATE ON jobs
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Matches
CREATE TABLE matches (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  job_id uuid NOT NULL REFERENCES jobs ON DELETE CASCADE,
  worker_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  status match_status DEFAULT 'suggested',
  match_score decimal,
  ai_summary text,
  contractor_action action_status DEFAULT 'pending',
  worker_action action_status DEFAULT 'pending',
  hired_at timestamptz,
  fee_charged boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  UNIQUE (job_id, worker_id)
);

CREATE TRIGGER matches_updated_at
  BEFORE UPDATE ON matches
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Ratings
CREATE TABLE ratings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  match_id uuid NOT NULL REFERENCES matches ON DELETE CASCADE,
  from_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  to_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  score integer NOT NULL CHECK (score BETWEEN 1 AND 5),
  comment text,
  created_at timestamptz DEFAULT now(),
  UNIQUE (match_id, from_id)
);

-- Payments
CREATE TABLE payments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  match_id uuid NOT NULL REFERENCES matches ON DELETE CASCADE,
  company_id uuid NOT NULL REFERENCES companies ON DELETE CASCADE,
  amount decimal DEFAULT 50.00,
  status payment_status DEFAULT 'pending',
  stripe_payment_id text,
  created_at timestamptz DEFAULT now()
);

-- Notifications
CREATE TABLE notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  type notification_type NOT NULL,
  title text NOT NULL,
  body text,
  data jsonb,
  read boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

-- ============================================
-- INDEXES
-- ============================================

CREATE INDEX idx_profiles_type ON profiles (type);
CREATE INDEX idx_profiles_suburb ON profiles (suburb);

CREATE INDEX idx_companies_profile_id ON companies (profile_id);
CREATE INDEX idx_companies_abn ON companies (abn);

CREATE INDEX idx_worker_profiles_profile_id ON worker_profiles (profile_id);
CREATE INDEX idx_worker_profiles_trades ON worker_profiles USING gin (trades);
CREATE INDEX idx_worker_profiles_availability ON worker_profiles (availability);
CREATE INDEX idx_worker_profiles_certifications ON worker_profiles USING gin (certifications);

CREATE INDEX idx_jobs_company_id ON jobs (company_id);
CREATE INDEX idx_jobs_status ON jobs (status);
CREATE INDEX idx_jobs_trade ON jobs (trade);
CREATE INDEX idx_jobs_created_at ON jobs (created_at DESC);
CREATE INDEX idx_jobs_suburb ON jobs (suburb);

CREATE INDEX idx_matches_job_id ON matches (job_id);
CREATE INDEX idx_matches_worker_id ON matches (worker_id);
CREATE INDEX idx_matches_status ON matches (status);

CREATE INDEX idx_ratings_to_id ON ratings (to_id);
CREATE INDEX idx_ratings_from_id ON ratings (from_id);

CREATE INDEX idx_payments_match_id ON payments (match_id);
CREATE INDEX idx_payments_company_id ON payments (company_id);

CREATE INDEX idx_notifications_profile_id ON notifications (profile_id);
CREATE INDEX idx_notifications_read ON notifications (profile_id, read);

-- ============================================
-- ROW LEVEL SECURITY
-- ============================================

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE worker_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE jobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE matches ENABLE ROW LEVEL SECURITY;
ALTER TABLE ratings ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;

-- Profiles: users can read all profiles, update their own
CREATE POLICY "Public profiles are viewable by everyone"
  ON profiles FOR SELECT
  USING (true);

CREATE POLICY "Users can update their own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = id);

CREATE POLICY "Users can insert their own profile"
  ON profiles FOR INSERT
  WITH CHECK (auth.uid() = id);

-- Companies: viewable by everyone, editable by owner
CREATE POLICY "Companies are viewable by everyone"
  ON companies FOR SELECT
  USING (true);

CREATE POLICY "Contractors can insert their own company"
  ON companies FOR INSERT
  WITH CHECK (profile_id = auth.uid());

CREATE POLICY "Contractors can update their own company"
  ON companies FOR UPDATE
  USING (profile_id = auth.uid());

-- Worker profiles: viewable by everyone, editable by owner
CREATE POLICY "Worker profiles are viewable by everyone"
  ON worker_profiles FOR SELECT
  USING (true);

CREATE POLICY "Workers can insert their own profile"
  ON worker_profiles FOR INSERT
  WITH CHECK (profile_id = auth.uid());

CREATE POLICY "Workers can update their own profile"
  ON worker_profiles FOR UPDATE
  USING (profile_id = auth.uid());

-- Jobs: viewable by everyone, editable by company owner
CREATE POLICY "Jobs are viewable by everyone"
  ON jobs FOR SELECT
  USING (true);

CREATE POLICY "Contractors can insert jobs for their company"
  ON jobs FOR INSERT
  WITH CHECK (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "Contractors can update their own jobs"
  ON jobs FOR UPDATE
  USING (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

-- Matches: viewable by involved parties
CREATE POLICY "Users can view their own matches"
  ON matches FOR SELECT
  USING (
    worker_id = auth.uid() OR
    job_id IN (
      SELECT j.id FROM jobs j
      JOIN companies c ON j.company_id = c.id
      WHERE c.profile_id = auth.uid()
    )
  );

CREATE POLICY "System can insert matches"
  ON matches FOR INSERT
  WITH CHECK (true);

CREATE POLICY "Involved parties can update matches"
  ON matches FOR UPDATE
  USING (
    worker_id = auth.uid() OR
    job_id IN (
      SELECT j.id FROM jobs j
      JOIN companies c ON j.company_id = c.id
      WHERE c.profile_id = auth.uid()
    )
  );

-- Ratings: viewable by everyone, insertable by match participants
CREATE POLICY "Ratings are viewable by everyone"
  ON ratings FOR SELECT
  USING (true);

CREATE POLICY "Match participants can insert ratings"
  ON ratings FOR INSERT
  WITH CHECK (from_id = auth.uid());

-- Payments: viewable by company owner
CREATE POLICY "Company owners can view their payments"
  ON payments FOR SELECT
  USING (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "System can insert payments"
  ON payments FOR INSERT
  WITH CHECK (true);

-- Notifications: users can only see their own
CREATE POLICY "Users can view their own notifications"
  ON notifications FOR SELECT
  USING (profile_id = auth.uid());

CREATE POLICY "System can insert notifications"
  ON notifications FOR INSERT
  WITH CHECK (true);

CREATE POLICY "Users can update their own notifications"
  ON notifications FOR UPDATE
  USING (profile_id = auth.uid());

-- ============================================
-- FUNCTIONS
-- ============================================

-- Auto-create profile on signup
-- Fixed: Now handles invalid/missing user_type gracefully (defaults to 'worker')
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
DECLARE
    v_user_type text;
    v_valid_type user_type;
    v_name text;
    v_phone text;
BEGIN
    -- Get fields from metadata
    v_user_type := NEW.raw_user_meta_data->>'type';
    v_name := NEW.raw_user_meta_data->>'name';
    v_phone := NEW.raw_user_meta_data->>'phone';

    -- Validate the user_type before casting (defaults to 'worker' if invalid)
    CASE v_user_type
        WHEN 'worker' THEN
            v_valid_type := 'worker'::user_type;
        WHEN 'contractor' THEN
            v_valid_type := 'contractor'::user_type;
        ELSE
            v_valid_type := 'worker'::user_type;
    END CASE;

    INSERT INTO public.profiles (id, email, type, name, phone)
    VALUES (
        NEW.id,
        NEW.email,
        v_valid_type,
        v_name,
        v_phone
    );

    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'handle_new_user() failed for user %: %', NEW.id, SQLERRM;
        RAISE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Average rating helper
CREATE OR REPLACE FUNCTION public.get_average_rating(user_id uuid)
RETURNS decimal AS $$
  SELECT COALESCE(AVG(score)::decimal, 0)
  FROM ratings
  WHERE to_id = user_id;
$$ LANGUAGE sql STABLE;

-- Rating count helper
CREATE OR REPLACE FUNCTION public.get_rating_count(user_id uuid)
RETURNS integer AS $$
  SELECT COUNT(*)::integer
  FROM ratings
  WHERE to_id = user_id;
$$ LANGUAGE sql STABLE;

-- ============================================
-- MATCHING FUNCTIONS (DB-SIDE SCORING)
-- ============================================

-- RPC FUNCTION: find_matching_workers
-- Calculates match scores in the database for scalability
-- Scoring: trade (40%), availability (20%), experience (15%), 
--          certifications (15%), location (10%), ratings (10%)
CREATE OR REPLACE FUNCTION find_matching_workers(
  p_job_id uuid,
  p_limit integer DEFAULT 20,
  p_min_score integer DEFAULT 20
)
RETURNS TABLE (
  worker_id uuid,
  score integer,
  summary text,
  reasons text[],
  profile jsonb
) AS $$
DECLARE
  v_job_record RECORD;
  v_job_location point;
BEGIN
  -- Get job details once
  SELECT 
    j.id,
    j.trade,
    j.certifications_required,
    j.start_date,
    j.duration,
    j.suburb,
    j.location,
    j.company_id
  INTO v_job_record
  FROM jobs j
  WHERE j.id = p_job_id AND j.status = 'active';

  -- Return empty if job not found or not active
  IF v_job_record IS NULL THEN
    RETURN;
  END IF;

  v_job_location := v_job_record.location;

  RETURN QUERY
  WITH 
  -- Get existing matches to exclude
  existing_matches AS (
    SELECT m.worker_id AS excluded_worker_id
    FROM matches m
    WHERE m.job_id = p_job_id
  ),
  -- Calculate average ratings per worker
  worker_ratings AS (
    SELECT 
      r.to_id AS rating_worker_id,
      AVG(r.score)::decimal AS avg_rating,
      COUNT(*)::integer AS rating_count
    FROM ratings r
    GROUP BY r.to_id
  ),
  -- Score each worker
  scored_workers AS (
    SELECT 
      p.id AS worker_profile_id,
      p.name AS worker_name,
      p.suburb AS worker_suburb,
      p.location AS worker_location,
      wp.trades AS worker_trades,
      wp.availability AS worker_availability,
      wp.experience_years,
      wp.certifications AS worker_certs,
      wp.bio,
      wp.white_card_verified,
      -- Trade match score (40 points max)
      CASE 
        WHEN v_job_record.trade = ANY(wp.trades) THEN 40
        WHEN 'Labourer' = ANY(wp.trades) THEN 10
        -- Related trade groups
        WHEN v_job_record.trade IN ('Steel Fixer', 'Formworker', 'Concreter', 'Scaffolder')
          AND wp.trades && ARRAY['Steel Fixer', 'Formworker', 'Concreter', 'Scaffolder']::text[] THEN 15
        WHEN v_job_record.trade IN ('Painter', 'Tiler', 'Plasterer')
          AND wp.trades && ARRAY['Painter', 'Tiler', 'Plasterer']::text[] THEN 15
        WHEN v_job_record.trade IN ('Electrician', 'Plumber')
          AND wp.trades && ARRAY['Electrician', 'Plumber']::text[] THEN 15
        WHEN v_job_record.trade IN ('Crane Operator', 'Excavator Operator')
          AND wp.trades && ARRAY['Crane Operator', 'Excavator Operator']::text[] THEN 15
        WHEN v_job_record.trade IN ('Bricklayer', 'Roofer', 'Welder')
          AND wp.trades && ARRAY['Bricklayer', 'Roofer', 'Welder']::text[] THEN 15
        ELSE 0
      END AS trade_score,
      -- Availability score (20 points max)
      CASE wp.availability
        WHEN 'flexible' THEN 20
        WHEN 'full-time' THEN 18
        WHEN 'casual' THEN 12
        WHEN 'weekends' THEN 8
        ELSE 0
      END AS availability_score,
      -- Experience score (15 points max)
      CASE 
        WHEN wp.experience_years >= 10 THEN 15
        WHEN wp.experience_years >= 7 THEN 13
        WHEN wp.experience_years >= 4 THEN 11
        WHEN wp.experience_years >= 2 THEN 8
        WHEN wp.experience_years >= 1 THEN 5
        WHEN wp.experience_years IS NOT NULL THEN 2
        ELSE 0
      END AS experience_score,
      -- Certifications score (15 points max)
      CASE 
        WHEN array_length(v_job_record.certifications_required, 1) IS NULL 
          OR array_length(v_job_record.certifications_required, 1) = 0 THEN 15
        ELSE 
          ROUND(
            (
              COALESCE(
                array_length(
                  ARRAY(
                    SELECT unnest(v_job_record.certifications_required)
                    INTERSECT
                    SELECT unnest(wp.certifications)
                  ),
                  1
                ),
                0
              )::decimal / 
              array_length(v_job_record.certifications_required, 1)
            ) * 15
          )
      END AS cert_score,
      -- Location score (10 points max) - uses point distance
      CASE 
        WHEN v_job_location IS NULL OR p.location IS NULL THEN 0
        WHEN p.location <-> v_job_location < 0.01 THEN 10  -- Same area (~1km)
        WHEN p.location <-> v_job_location < 0.05 THEN 7   -- Nearby (~5km)
        WHEN p.location <-> v_job_location < 0.1 THEN 5    -- Same region (~10km)
        WHEN p.suburb = v_job_record.suburb THEN 10        -- Exact suburb match (fallback)
        WHEN p.suburb ILIKE '%' || v_job_record.suburb || '%' 
          OR v_job_record.suburb ILIKE '%' || p.suburb || '%' THEN 7
        ELSE 0
      END AS location_score,
      -- Rating score (10 points max)
      COALESCE(
        ROUND((wr.avg_rating / 5.0) * 10),
        0
      ) AS rating_score,
      wr.avg_rating,
      wr.rating_count
    FROM profiles p
    INNER JOIN worker_profiles wp ON wp.profile_id = p.id
    LEFT JOIN worker_ratings wr ON wr.rating_worker_id = p.id
    WHERE 
      p.onboarding_completed = true
      AND p.type = 'worker'
      AND p.id NOT IN (SELECT excluded_worker_id FROM existing_matches)
  ),
  -- Calculate total score and build reasons
  final_scores AS (
    SELECT 
      sw.*,
      (sw.trade_score + sw.availability_score + sw.experience_score + 
       sw.cert_score + sw.location_score + sw.rating_score) AS total_score,
      -- Build reasons array
      ARRAY_REMOVE(ARRAY[
        CASE WHEN sw.trade_score = 40 THEN 'Trades as ' || v_job_record.trade
             WHEN sw.trade_score = 15 THEN 'Related trade experience'
             WHEN sw.trade_score = 10 THEN 'General labourer available'
             ELSE NULL END,
        CASE WHEN sw.availability_score > 0 THEN 'Available ' || sw.worker_availability ELSE NULL END,
        CASE WHEN sw.experience_score > 0 THEN sw.experience_years || '+ years experience' ELSE NULL END,
        CASE WHEN sw.cert_score > 0 AND array_length(v_job_record.certifications_required, 1) > 0 
             THEN 'Has required certifications' ELSE NULL END,
        CASE WHEN sw.location_score > 0 THEN 'Based in ' || sw.worker_suburb ELSE NULL END,
        CASE WHEN sw.rating_score >= 8 THEN ROUND(sw.avg_rating::numeric, 1) || ' star rating' ELSE NULL END
      ], NULL) AS match_reasons
    FROM scored_workers sw
  )
  SELECT 
    fs.worker_profile_id,
    LEAST(fs.total_score, 100) AS score,
    array_to_string(fs.match_reasons[:3], '. ') || '.' AS summary,
    fs.match_reasons AS reasons,
    jsonb_build_object(
      'id', fs.worker_profile_id,
      'name', fs.worker_name,
      'suburb', fs.worker_suburb,
      'trades', fs.worker_trades,
      'experience_years', fs.experience_years,
      'availability', fs.worker_availability,
      'certifications', fs.worker_certs,
      'bio', fs.bio,
      'white_card_verified', fs.white_card_verified,
      'avg_rating', COALESCE(fs.avg_rating, 0),
      'rating_count', COALESCE(fs.rating_count, 0)
    ) AS profile
  FROM final_scores fs
  WHERE fs.total_score >= p_min_score
  ORDER BY fs.total_score DESC, fs.experience_years DESC NULLS LAST
  LIMIT p_limit;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION find_matching_workers(uuid, integer, integer) TO authenticated;
GRANT EXECUTE ON FUNCTION find_matching_workers(uuid, integer, integer) TO service_role;

-- ============================================
-- MATCHING PERFORMANCE INDEXES
-- ============================================

-- GiST index for efficient geospatial distance calculations on worker locations
CREATE INDEX IF NOT EXISTS idx_profiles_location ON profiles USING gist (location) WHERE type = 'worker';

-- Covering index for worker profile lookups during matching
CREATE INDEX IF NOT EXISTS idx_worker_profiles_lookup ON worker_profiles (profile_id, availability, experience_years) INCLUDE (trades, certifications, bio);
