-- fix_profile_embeddings_trigger.sql
-- Run this in your Supabase SQL Editor to fully resolve the "j_emb" error!

-- 1. Redefine the function used by the trigger that refreshes recommendations
-- The error "record j_emb has no field experience" was likely deeply cached in this logic
CREATE OR REPLACE FUNCTION public.trg_refresh_recommendations_for_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE 
  j_id uuid; 
  match_res json;
BEGIN
  -- First clear out old recommendations for this user
  DELETE FROM public.job_recommendations WHERE user_id = NEW.id;
  
  -- Iterate through all existing job embeddings
  FOR j_id IN SELECT job_id FROM public.job_embeddings LOOP
    
    -- Call the fixed match_profile_job function
    match_res := public.match_profile_job(NEW.id, j_id);
    
    -- Only insert if there's an actual match > 0
    IF (match_res->>'match_score')::int > 0 THEN
      INSERT INTO public.job_recommendations (
        user_id, job_id, match_score, skills_match, technical_skills_match,
        work_experience_match, education_match, certifications_match, project_match
      ) VALUES (
        NEW.id, j_id, 
        (match_res->>'match_score')::int, 
        (match_res->>'skills_match')::int, 
        (match_res->>'technical_skills_match')::int,
        (match_res->>'work_experience_match')::int, 
        (match_res->>'education_match')::int, 
        (match_res->>'certifications_match')::int, 
        (match_res->>'project_match')::int
      );
    END IF;
    
  END LOOP;
  
  RETURN NEW;
END;
$function$;

-- 2. Drop the redundant webhook trigger since you only need the recommendation refresh
-- Having both might cause race conditions or unnecessary webhooks
DROP TRIGGER IF EXISTS on_profile_embedding_upsert ON public.profile_embeddings;

-- 3. Ensure the embedding refresh trigger is properly attached
DROP TRIGGER IF EXISTS on_profile_embedding_change ON public.profile_embeddings;

CREATE TRIGGER on_profile_embedding_change
AFTER INSERT OR UPDATE ON public.profile_embeddings
FOR EACH ROW
EXECUTE FUNCTION trg_refresh_recommendations_for_user();