backend/repair_system_mismatches.sql

-- repair_system_mismatches.sql
-- Run this in Supabase SQL Editor to resolve the "j_emb" error and restore automatic matching.

-- 1. FIX THE MATCHING FUNCTION (The "j_emb" bug fix)
-- This function is used by triggers and the RPC.
-- We ENSURE it uses 'work_experience' for jobs and 'experience' for profiles.
CREATE OR REPLACE FUNCTION public.match_profile_job(p_id uuid, j_id uuid)
 RETURNS json
 LANGUAGE plpgsql
 AS $function$
 DECLARE
   p_rec record;
   j_rec record; -- Consistency check: Job record MUST use its real columns
   s_sim float := 0; t_sim float := 0; exp_sim float := 0;
   edu_sim float := 0; cert_sim float := 0; proj_sim float := 0;
   s_score int := 0; t_score int := 0; e_score int := 0;
   ed_score int := 0; c_score int := 0; p_score int := 0;
 BEGIN
   -- Fetch Profile Embeddings
   SELECT * INTO p_rec FROM public.profile_embeddings WHERE id = p_id;
   IF NOT FOUND THEN RETURN json_build_object('error', 'Profile embeddings not found'); END IF;
 
   -- Fetch Job Embeddings
   SELECT * INTO j_rec FROM public.job_embeddings WHERE job_id = j_id;
   IF NOT FOUND THEN RETURN json_build_object('error', 'Job embeddings not found'); END IF;
 
   -- Similarity with Cosine Distance (<=>)
   IF p_rec.skills IS NOT NULL AND j_rec.skills IS NOT NULL THEN
     s_sim := coalesce(nullif(1 - (p_rec.skills <=> j_rec.skills), 'NaN'), 0);
   END IF;
   
   IF p_rec.technical_skills IS NOT NULL AND j_rec.technical_skills IS NOT NULL THEN
     t_sim := coalesce(nullif(1 - (p_rec.technical_skills <=> j_rec.technical_skills), 'NaN'), 0);
   END IF;
   
   -- FIX: Profile column is 'experience', Job column is 'work_experience'
   IF p_rec.experience IS NOT NULL AND j_rec.work_experience IS NOT NULL THEN
     exp_sim := coalesce(nullif(1 - (p_rec.experience <=> j_rec.work_experience), 'NaN'), 0);
   END IF;
 
   IF p_rec.education IS NOT NULL AND j_rec.education IS NOT NULL THEN
     edu_sim := coalesce(nullif(1 - (p_rec.education <=> j_rec.education), 'NaN'), 0);
   END IF;
 
   IF p_rec.certifications IS NOT NULL THEN
     cert_sim := coalesce(nullif(1 - (p_rec.certifications <=> coalesce(j_rec.technical_skills, j_rec.skills)), 'NaN'), 0);
   END IF;
 
   IF p_rec.projects IS NOT NULL AND j_rec.technical_skills IS NOT NULL THEN
     proj_sim := coalesce(nullif(1 - (p_rec.projects <=> j_rec.technical_skills), 'NaN'), 0);
   END IF;
 
   -- Scaling to 0-100
   s_score := (greatest(0, least(1, s_sim)) * 100)::int;
   t_score := (greatest(0, least(1, t_sim)) * 100)::int;
   e_score := (greatest(0, least(1, exp_sim)) * 100)::int;
   ed_score := (greatest(0, least(1, edu_sim)) * 100)::int;
   c_score := (greatest(0, least(1, cert_sim)) * 100)::int;
   p_score := (greatest(0, least(1, proj_sim)) * 100)::int;
 
   RETURN json_build_object(
     'match_score', ((t_score * 0.35) + (e_score * 0.20) + (p_score * 0.15) + (s_score * 0.10) + (ed_score * 0.10) + (c_score * 0.10))::int,
     'skills_match', s_score,
     'technical_skills_match', t_score,
     'work_experience_match', e_score,
     'education_match', ed_score,
     'certifications_match', c_score,
     'project_match', p_score
   );
 END;
 $function$;

-- 2. CREATE THE JOB RECOMMENDATIONS RPC (Ranked Jobs)
-- We drop it first because changing the return schema requires it in Postgres.
DROP FUNCTION IF EXISTS public.get_job_recommendations(uuid, int);

CREATE OR REPLACE FUNCTION public.get_job_recommendations(p_user_id uuid, p_limit int DEFAULT 10)
 RETURNS json
 LANGUAGE plpgsql
 AS $function$
 DECLARE
   results_json JSON;
 BEGIN
   SELECT json_agg(r) INTO results_json
   FROM (
     SELECT 
       j.id,
       j.title,
       j.location,
       j.job_type,
       j.salary_range,
       c.name as company_name,
       c.logo_url as company_logo,
       (match_profile_job(p_user_id, j.id)->>'match_score')::int as match_score
     FROM public.jobs j
     JOIN public.companies c ON j.company_id = c.id
     WHERE j.status = 'Active'
     ORDER BY match_score DESC
     LIMIT p_limit
   ) r;
   
   RETURN coalesce(results_json, '[]'::json);
 END;
 $function$;