Spaces:
Sleeping
Sleeping
File size: 4,075 Bytes
4b3a33f | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | -- 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$;
|