-- 030_cv_bank_matches.sql
-- Tier-1 ranking of banked CVs against a job post.
--
-- Computed by the cvbank.rank_for_job task when a job opens, not on read: the
-- point is to notify a recruiter that the bank already holds candidates, and a
-- notification needs a result that exists before anyone opens the screen.
--
-- rank_score is deterministic keyword overlap (matching/ranking.py), NOT an ATS
-- score. Cheap enough to recompute for the whole bank on every job opening.
-- Applied at startup by alembic_setup.run_manual_sql().

CREATE TABLE IF NOT EXISTS app.cv_bank_matches (
    id                          UUID PRIMARY KEY,
    manual_upload_candidate_id  UUID NOT NULL
        REFERENCES app.manual_upload_candidate (id) ON DELETE CASCADE,
    job_post_id                 UUID NOT NULL
        REFERENCES app.job_posts (id) ON DELETE CASCADE,
    rank_score                  INTEGER NOT NULL DEFAULT 0,
    computed_at                 TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_cv_bank_matches_pair UNIQUE (manual_upload_candidate_id, job_post_id)
);

-- The read is always "best candidates for THIS job", so the job leads.
CREATE INDEX IF NOT EXISTS ix_cv_bank_matches_job_rank
    ON app.cv_bank_matches (job_post_id, rank_score DESC);