sql/match_courses.sql

-- =============================================================================
-- Supabase RPC: match_courses
-- Hybrid Recommender Engine — Vector Similarity Search
-- Searches course_chunks embeddings, JOINs to parent courses table.
-- Supports SBERT, E5, and DistilBERT models via model_type parameter.
-- =============================================================================

-- Drop existing function variants (for idempotent re-runs)
DROP FUNCTION IF EXISTS match_courses(vector, float, int, int, text);
DROP FUNCTION IF EXISTS match_courses(vector, float8, int4, int4, text);

CREATE OR REPLACE FUNCTION match_courses(
    query_embedding vector,
    match_threshold float DEFAULT 0.3,
    match_count int DEFAULT 5,
    child_age_months int DEFAULT 48,
    model_type text DEFAULT 'sbert'
)
RETURNS TABLE (
    course_id uuid,
    course_title text,
    course_description text,
    chunk_id uuid,
    chunk_title text,
    chunk_content text,
    similarity float
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF model_type = 'e5' THEN
        RETURN QUERY
        SELECT
            c.id AS course_id,
            c.title AS course_title,
            c.description AS course_description,
            cc.id AS chunk_id,
            cc.chunk_title,
            cc.chunk_content,
            (1 - (cc.embedding_e5 <=> query_embedding))::float AS similarity
        FROM course_chunks cc
        JOIN courses c ON cc.course_id = c.id
        WHERE
            c.age_min <= child_age_months
            AND c.age_max >= child_age_months
            AND cc.embedding_e5 IS NOT NULL
            AND (1 - (cc.embedding_e5 <=> query_embedding)) > match_threshold
        ORDER BY cc.embedding_e5 <=> query_embedding ASC
        LIMIT match_count;

    ELSIF model_type = 'distilbert' THEN
        RETURN QUERY
        SELECT
            c.id AS course_id,
            c.title AS course_title,
            c.description AS course_description,
            cc.id AS chunk_id,
            cc.chunk_title,
            cc.chunk_content,
            (1 - (cc.embedding_distilbert <=> query_embedding))::float AS similarity
        FROM course_chunks cc
        JOIN courses c ON cc.course_id = c.id
        WHERE
            c.age_min <= child_age_months
            AND c.age_max >= child_age_months
            AND cc.embedding_distilbert IS NOT NULL
            AND (1 - (cc.embedding_distilbert <=> query_embedding)) > match_threshold
        ORDER BY cc.embedding_distilbert <=> query_embedding ASC
        LIMIT match_count;

    ELSE
        -- Default: SBERT
        RETURN QUERY
        SELECT
            c.id AS course_id,
            c.title AS course_title,
            c.description AS course_description,
            cc.id AS chunk_id,
            cc.chunk_title,
            cc.chunk_content,
            (1 - (cc.embedding_sbert <=> query_embedding))::float AS similarity
        FROM course_chunks cc
        JOIN courses c ON cc.course_id = c.id
        WHERE
            c.age_min <= child_age_months
            AND c.age_max >= child_age_months
            AND cc.embedding_sbert IS NOT NULL
            AND (1 - (cc.embedding_sbert <=> query_embedding)) > match_threshold
        ORDER BY cc.embedding_sbert <=> query_embedding ASC
        LIMIT match_count;
    END IF;
END;
$$;