-- +goose Up
-- ai_memory is the per tenant episodic and semantic memory for the AI layer. It lives INSIDE the tenant
-- database (database per tenant), so one client's memory is physically isolated from another's, and it
-- is RLS forced as defense in depth. Memory that is about a candidate carries candidate_id so the right
-- to erasure wipes it: when a candidate is anonymized, their memory rows are deleted, leaving no semantic
-- trace. The embedding column lets semantic memory be recalled by similarity; it is vector(384) to match
-- the one embedding model the platform uses. No demographic data ever enters this table (the AI layer is
-- barred from the eeo schema by construction).
CREATE TABLE ai_memory (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL,
    candidate_id    uuid,                       -- set when the memory is about a candidate (for erasure)
    kind            text NOT NULL CHECK (kind IN ('episodic', 'semantic')),
    content         text NOT NULL,              -- a craft note or a recalled fact; never demographic data
    embedding       vector(384),                -- optional, for semantic recall
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    -- A candidate scoped memory references the candidate in the SAME org; if a candidate row is ever hard
    -- deleted the link is nulled rather than orphaned. Erasure (anonymize in place) deletes these rows
    -- explicitly, since anonymize does not delete the candidate row.
    CONSTRAINT fk_ai_memory_candidate FOREIGN KEY (organization_id, candidate_id)
        REFERENCES candidates (organization_id, id) ON DELETE SET NULL
);
CREATE INDEX idx_ai_memory_org_candidate ON ai_memory (organization_id, candidate_id);
CREATE INDEX idx_ai_memory_emb_hnsw ON ai_memory USING hnsw (embedding vector_cosine_ops) WHERE embedding IS NOT NULL;

ALTER TABLE ai_memory ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_memory FORCE  ROW LEVEL SECURITY;
CREATE POLICY ai_memory_isolation ON ai_memory
    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 ai_memory TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS ai_memory;