-- Migration: Add DB-side match scoring RPC function
-- Created: 2025-01-21
-- Description: Moves match scoring from application layer to database for better scalability
-- This allows efficient matching even with 10k+ workers

-- ============================================
-- RPC FUNCTION: find_matching_workers
-- ============================================

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;

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

-- Create index to speed up the worker lookup
CREATE INDEX IF NOT EXISTS idx_worker_profiles_lookup ON worker_profiles (profile_id, availability, experience_years) INCLUDE (trades, certifications, bio);

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Test the function (replace with actual job_id)
-- SELECT * FROM find_matching_workers('your-job-id-here'::uuid, 20, 20);

-- Check function exists
-- SELECT proname, proargnames, prorettype::regtype 
-- FROM pg_proc 
-- WHERE proname = 'find_matching_workers';
