-- +goose Up
-- External candidate sourcing (People Data Labs). Sourced people are stored in the existing
-- tenant scoped candidates table tagged source='external', so RLS still isolates them per org. Every
-- sourced record gets an append friendly compliance row in sourcing_log (who, where from, the query,
-- the legal basis, when). Clearance is left 'unknown' for sourced records: absence is not a negative.

ALTER TABLE candidates ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT 'seed'
    CHECK (source IN ('seed', 'external'));
ALTER TABLE candidates ADD COLUMN IF NOT EXISTS external_id text;

-- dedupe a sourced person within an org so re imports update rather than duplicate
CREATE UNIQUE INDEX IF NOT EXISTS uq_candidates_external
    ON candidates (organization_id, external_id) WHERE external_id IS NOT NULL;

-- Compliance log: one row per sourced record. Tenant scoped under RLS (an org sees only its own).
CREATE TABLE sourcing_log (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES organizations(id),
    candidate_id    uuid,                        -- null after the candidate is deleted
    external_id     text NOT NULL DEFAULT '',
    source          text NOT NULL DEFAULT '',    -- for example PDL
    provider        text NOT NULL DEFAULT '',    -- for example People Data Labs
    query           text NOT NULL DEFAULT '',    -- the search query used
    legal_basis     text NOT NULL DEFAULT '',    -- the lawful basis for sourcing
    collected_at    timestamptz NOT NULL DEFAULT now(),
    created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_sourcing_log_org ON sourcing_log (organization_id, collected_at DESC);

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

-- +goose Down
DROP TABLE IF EXISTS sourcing_log;
DROP INDEX IF EXISTS uq_candidates_external;
ALTER TABLE candidates DROP COLUMN IF EXISTS external_id;
ALTER TABLE candidates DROP COLUMN IF EXISTS source;