-- +goose Up
-- requirement_mission_tags tags a tenant requirement with one or more mission areas, each carrying the
-- mechanism and evidence phrase so the inference is traceable. Tenant scoped: denormalized org plus a
-- COMPOSITE FK to the parent (org,id) so a child row can never reference a parent in another tenant, with
-- ENABLE plus FORCE RLS and the standard org isolation policy. This mirrors the candidate_skills pattern
-- from migration 0002 verbatim. Mission tags are never a scoring input and never reach the eeo schema.
CREATE TABLE requirement_mission_tags (
    organization_id uuid NOT NULL,
    requirement_id  uuid NOT NULL,
    mission_code    text NOT NULL REFERENCES mission_areas(code),
    mechanism       text NOT NULL,
    evidence        text NOT NULL DEFAULT '',
    PRIMARY KEY (requirement_id, mission_code),
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_requirement_mission_tags_org ON requirement_mission_tags (organization_id, requirement_id);
ALTER TABLE requirement_mission_tags ENABLE ROW LEVEL SECURITY;
ALTER TABLE requirement_mission_tags FORCE  ROW LEVEL SECURITY;
CREATE POLICY requirement_mission_tags_isolation ON requirement_mission_tags
    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);
GRANT SELECT, INSERT, UPDATE, DELETE ON requirement_mission_tags TO missionmatch_app;

-- candidate_mission_tags tags a tenant candidate with one or more mission areas, same shape and the same
-- isolation guarantees as requirement_mission_tags.
CREATE TABLE candidate_mission_tags (
    organization_id uuid NOT NULL,
    candidate_id    uuid NOT NULL,
    mission_code    text NOT NULL REFERENCES mission_areas(code),
    mechanism       text NOT NULL,
    evidence        text NOT NULL DEFAULT '',
    PRIMARY KEY (candidate_id, mission_code),
    FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_candidate_mission_tags_org ON candidate_mission_tags (organization_id, candidate_id);
ALTER TABLE candidate_mission_tags ENABLE ROW LEVEL SECURITY;
ALTER TABLE candidate_mission_tags FORCE  ROW LEVEL SECURITY;
CREATE POLICY candidate_mission_tags_isolation ON candidate_mission_tags
    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);
GRANT SELECT, INSERT, UPDATE, DELETE ON candidate_mission_tags TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS candidate_mission_tags;
DROP TABLE IF EXISTS requirement_mission_tags;