-- +goose Up
-- candidate_evaluations stores the latest screenMATCH qualification of one candidate against one
-- requirement: the frozen MatchResult from the shared engine so missionMATCH rollups read a stored
-- evaluation rather than recompute on read. It is upsert latest per (organization_id, candidate_id,
-- requirement_id), so a fresh screen overwrites the prior row and there is exactly one current
-- qualification per pair. The served score is still audited on every screen (aedt.score.served) for
-- the four-fifths analysis; this table is the readable artifact downstream tools consume, not the
-- audit trail. Score columns mirror slate_members so the two stored evaluations share one shape, and
-- every numeric column is decimal in Go via a per column sqlc override (the determinism rule).
CREATE TABLE candidate_evaluations (
    organization_id  uuid NOT NULL,
    candidate_id     uuid NOT NULL,
    requirement_id   uuid NOT NULL,
    total_score      numeric(7,4) NOT NULL CHECK (total_score      BETWEEN 0 AND 100),
    band             text NOT NULL CHECK (band IN ('green','yellow','red')),
    clearance_score  numeric(7,4) NOT NULL CHECK (clearance_score  BETWEEN 0 AND 100),
    skills_score     numeric(7,4) NOT NULL CHECK (skills_score     BETWEEN 0 AND 100),
    certs_score      numeric(7,4) NOT NULL CHECK (certs_score      BETWEEN 0 AND 100),
    customer_score   numeric(7,4) NOT NULL CHECK (customer_score   BETWEEN 0 AND 100),
    experience_score numeric(7,4) NOT NULL CHECK (experience_score BETWEEN 0 AND 100),
    location_score   numeric(7,4) NOT NULL CHECK (location_score   BETWEEN 0 AND 100),
    explanation      text  NOT NULL,
    matched_skills   jsonb NOT NULL DEFAULT '[]',
    gap_summary      jsonb NOT NULL DEFAULT '[]',
    model_version    text  NOT NULL,
    evaluated_by     uuid,
    evaluated_at     timestamptz NOT NULL DEFAULT now(),
    updated_at       timestamptz NOT NULL DEFAULT now(),
    -- The pair is the identity, so the upsert target is the primary key. It leads with organization_id,
    -- so the tenant composite index requirement is satisfied by the PK itself.
    PRIMARY KEY (organization_id, candidate_id, requirement_id),
    FOREIGN KEY (organization_id, candidate_id)   REFERENCES candidates (organization_id, id)   ON DELETE CASCADE,
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE,
    -- The actor is a user in the SAME org; a user delete nulls the column rather than orphaning the row.
    FOREIGN KEY (organization_id, evaluated_by)   REFERENCES users (organization_id, id)        ON DELETE SET NULL
);
-- Read paths: by requirement (missionMATCH coverage and the persisted ranked view) and by candidate
-- (who can support which mission). Both lead with organization_id.
CREATE INDEX idx_candidate_evaluations_org_req  ON candidate_evaluations (organization_id, requirement_id);
CREATE INDEX idx_candidate_evaluations_org_cand ON candidate_evaluations (organization_id, candidate_id);

ALTER TABLE candidate_evaluations ENABLE ROW LEVEL SECURITY;
ALTER TABLE candidate_evaluations FORCE  ROW LEVEL SECURITY;
CREATE POLICY candidate_evaluations_isolation ON candidate_evaluations
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- Runtime app role: read and write its own org's evaluations (NOBYPASSRLS, the policy scopes it).
GRANT SELECT, INSERT, UPDATE ON candidate_evaluations TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS candidate_evaluations;