-- Migration: Fix SECURITY DEFINER functions with SET search_path
-- Created: 2026-02-10
-- Description: Adds SET search_path = public to all SECURITY DEFINER functions
--              to prevent search_path hijacking. Also drops the payment DELETE
--              policy and restricts match deletion to non-hired matches only.

BEGIN;

-- ============================================
-- FIX: find_matching_workers - add SET search_path
-- ============================================

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
  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';

  IF v_job_record IS NULL THEN
    RETURN;
  END IF;

  v_job_location := v_job_record.location;

  RETURN QUERY
  WITH
  existing_matches AS (
    SELECT m.worker_id AS excluded_worker_id
    FROM matches m
    WHERE m.job_id = p_job_id
  ),
  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
  ),
  scored_workers AS (
    SELECT
      p.id AS worker_profile_id,
      p.name AS worker_name,
      p.display_name AS worker_display_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,
      CASE
        WHEN v_job_record.trade = ANY(wp.trades) THEN 40
        WHEN 'Labourer' = ANY(wp.trades) THEN 10
        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,
      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,
      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,
      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,
      CASE
        WHEN v_job_location IS NULL OR p.location IS NULL THEN 0
        WHEN p.location <-> v_job_location < 0.01 THEN 10
        WHEN p.location <-> v_job_location < 0.05 THEN 7
        WHEN p.location <-> v_job_location < 0.1 THEN 5
        WHEN p.suburb = v_job_record.suburb THEN 10
        WHEN p.suburb ILIKE '%' || v_job_record.suburb || '%'
          OR v_job_record.suburb ILIKE '%' || p.suburb || '%' THEN 7
        ELSE 0
      END AS location_score,
      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)
  ),
  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,
      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,
      'display_name', fs.worker_display_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

-- ============================================
-- FIX: get_cached_abn - add SET search_path
-- ============================================

CREATE OR REPLACE FUNCTION get_cached_abn(p_abn text)
RETURNS TABLE (
  id uuid,
  abn text,
  company_name text,
  status text,
  status_date text,
  state text,
  postcode text,
  entity_type text,
  entity_type_code text,
  gst_registered boolean,
  acn text,
  cached_at timestamptz,
  expires_at timestamptz,
  lookup_count integer,
  last_lookup_at timestamptz,
  abr_response jsonb
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    ac.id,
    ac.abn,
    ac.company_name,
    ac.status,
    ac.status_date,
    ac.state,
    ac.postcode,
    ac.entity_type,
    ac.entity_type_code,
    ac.gst_registered,
    ac.acn,
    ac.cached_at,
    ac.expires_at,
    ac.lookup_count,
    ac.last_lookup_at,
    ac.abr_response
  FROM abn_cache ac
  WHERE ac.abn = p_abn
    AND ac.expires_at > now()
  LIMIT 1;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER

-- ============================================
-- FIX: get_abn_cache_stats - add SET search_path
-- ============================================

CREATE OR REPLACE FUNCTION get_abn_cache_stats()
RETURNS TABLE (
  total_cached bigint,
  active_entries bigint,
  expired_entries bigint,
  avg_lookups numeric,
  max_lookups integer
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    COUNT(*) as total_cached,
    COUNT(*) FILTER (WHERE expires_at > now()) as active_entries,
    COUNT(*) FILTER (WHERE expires_at <= now()) as expired_entries,
    AVG(lookup_count) as avg_lookups,
    MAX(lookup_count) as max_lookups
  FROM abn_cache;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER

-- ============================================
-- FIX: increment_abn_lookup - add SET search_path
-- ============================================

CREATE OR REPLACE FUNCTION increment_abn_lookup(p_abn text)
RETURNS void AS $$
BEGIN
  UPDATE abn_cache
  SET
    lookup_count = lookup_count + 1,
    last_lookup_at = now()
  WHERE abn = p_abn;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER

-- ============================================
-- FIX: cleanup_expired_abn_cache - add SET search_path
-- ============================================

CREATE OR REPLACE FUNCTION cleanup_expired_abn_cache()
RETURNS TABLE (deleted_count integer) AS $$
DECLARE
  deleted integer;
BEGIN
  DELETE FROM abn_cache
  WHERE expires_at <= now();

  GET DIAGNOSTICS deleted = ROW_COUNT;
  RETURN QUERY SELECT deleted;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER

-- ============================================
-- FIX: Drop payment DELETE policy (too permissive)
-- ============================================

DROP POLICY IF EXISTS "Company owners can delete their payments" ON payments;

-- ============================================
-- FIX: Restrict match deletion to non-hired matches only
-- ============================================

DROP POLICY IF EXISTS "Involved parties can delete matches" ON matches;
CREATE POLICY "Involved parties can delete matches"
  ON matches FOR DELETE
  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()
      )
    )
    AND status NOT IN ('hired')
  );

COMMIT;
