-- +goose Up
-- WS2 deterministic alias and relationship layer. Two GLOBAL reference tables, like skills they carry no
-- organization_id and sit outside RLS, app role SELECT only, migrator writes. They FK to the global skills
-- table. The engine consults these (curated, ontology backed) before the semantic matcher: an alias is an
-- exact equivalence, a relationship is a curated partial credit. Weights, bands, and the 0.62 threshold are
-- unchanged; this registers ModelVersion v1.2.0 for the new curated match capability.

ALTER TABLE skills ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT '';
ALTER TABLE skills ADD COLUMN IF NOT EXISTS soc_code text NOT NULL DEFAULT '';

CREATE TABLE skill_aliases (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    skill_id    uuid NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    alias_text  text NOT NULL,
    alias_type  text NOT NULL CHECK (alias_type IN ('acronym','synonym','vendor_term','legacy')),
    source      text NOT NULL DEFAULT '',
    confidence  numeric(3,2) NOT NULL DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),
    verified_at timestamptz,
    created_at  timestamptz NOT NULL DEFAULT now()
);
-- A functional unique on lower(alias_text) must be a separate index, Postgres does not allow an
-- expression in an inline UNIQUE constraint. The seed relies on ON CONFLICT (lower(alias_text)).
CREATE UNIQUE INDEX skill_aliases_alias_lower_key ON skill_aliases (lower(alias_text));
CREATE INDEX idx_skill_aliases_skill ON skill_aliases (skill_id);

CREATE TABLE skill_relationships (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    from_skill_id uuid NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    to_skill_id   uuid NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    relation      text NOT NULL CHECK (relation IN ('requires','substitutes_for','broader_than','related_to')),
    strength      numeric(3,2) NOT NULL DEFAULT 1.0 CHECK (strength >= 0 AND strength <= 1),
    source        text NOT NULL DEFAULT '',
    verified_at   timestamptz,
    created_at    timestamptz NOT NULL DEFAULT now(),
    UNIQUE (from_skill_id, to_skill_id, relation)
);
CREATE INDEX idx_skill_rel_from ON skill_relationships (from_skill_id);
CREATE INDEX idx_skill_rel_to ON skill_relationships (to_skill_id);

GRANT SELECT ON skill_aliases TO missionmatch_app;
GRANT SELECT ON skill_relationships TO missionmatch_app;

INSERT INTO aedt_model_versions (version, weights, band_cuts, notes) VALUES (
    'v1.2.0',
    '{"clearance":0.25,"skills":0.25,"certifications":0.15,"customer_experience":0.15,"experience":0.10,"location_availability":0.10}',
    '{"green":90,"yellow":75}',
    'Adds the curated deterministic alias and relationship match layer (skill_aliases and skill_relationships). An alias resolves a curated ontology backed equivalence as an exact match (similarity 1.0); a substitutes_for or broader_than relationship contributes its curated strength as a partial match, gated at or above the 0.62 semantic threshold. The six component weights, the green and yellow band cuts, and the 0.62 threshold are unchanged from v1.1.0. Clearance remains a self reported indicator, never an input proxy for a protected attribute.'
) ON CONFLICT (version) DO NOTHING;

-- +goose Down
DELETE FROM aedt_model_versions WHERE version = 'v1.2.0';
DROP TABLE IF EXISTS skill_relationships;
DROP TABLE IF EXISTS skill_aliases;
ALTER TABLE skills DROP COLUMN IF EXISTS soc_code;
ALTER TABLE skills DROP COLUMN IF EXISTS source;