-- +goose Up
-- Foundation: extensions, tenancy root, users, and the append-only audit log.
-- Run as the BYPASSRLS migrator role. See docs/STACK_DECISIONS.md §2/§4.

CREATE EXTENSION IF NOT EXISTS vector;   -- Azure: the binary/extension name is "vector", NOT "pgvector"

-- ---------------------------------------------------------------------------
-- organizations — the tenant root. RLS scopes a session to its own org row.
-- INSERT (provisioning a new org at signup) is PRIVILEGED (migrator/provisioning
-- path), so the app role gets no INSERT grant. status/slug (org lifecycle) are
-- NOT app-editable — only `name` is (column-level grant below), so a tenant
-- cannot self-un-suspend by flipping status back to 'active' [review RLS-2].
-- ---------------------------------------------------------------------------
CREATE TABLE organizations (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name        text NOT NULL,
    slug        text NOT NULL UNIQUE,
    status      text NOT NULL DEFAULT 'active'
                  CHECK (status IN ('active', 'suspended')),
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organizations FORCE  ROW LEVEL SECURITY;
CREATE POLICY org_self_select ON organizations FOR SELECT
    USING (id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
CREATE POLICY org_self_update ON organizations FOR UPDATE
    USING      (id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- ---------------------------------------------------------------------------
-- users — tenant-scoped. UNIQUE (organization_id, id) lets other tables form a
-- COMPOSITE FK that pins created_by/decided_by to the same org [review RLS-1/INT-4].
-- ---------------------------------------------------------------------------
CREATE TABLE users (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id  uuid NOT NULL REFERENCES organizations(id),
    email            text NOT NULL,
    full_name        text NOT NULL,
    role             text NOT NULL DEFAULT 'member'
                       CHECK (role IN ('owner', 'admin', 'recruiter', 'member', 'viewer')),
    status           text NOT NULL DEFAULT 'active'
                       CHECK (status IN ('active', 'invited', 'disabled')),
    last_login_at    timestamptz,
    created_at       timestamptz NOT NULL DEFAULT now(),
    updated_at       timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_users_org_id UNIQUE (organization_id, id)
);
CREATE UNIQUE INDEX uq_users_org_email ON users (organization_id, lower(email));
CREATE INDEX idx_users_org ON users (organization_id, created_at DESC);  -- composite leads with org
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE users FORCE  ROW LEVEL SECURITY;
CREATE POLICY users_tenant_isolation ON users
    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);

-- ---------------------------------------------------------------------------
-- audit_logs — OUTSIDE RLS, APPEND-ONLY. Defense in depth: (1) app role granted
-- INSERT/SELECT only; (2) a row trigger blocks UPDATE/DELETE; (3) a statement
-- trigger blocks TRUNCATE [review AUDIT-1]; (4) explicit REVOKEs survive default-
-- privilege drift [review AUDIT-5]. Reads MUST filter by org (no RLS here).
-- ---------------------------------------------------------------------------
CREATE TABLE audit_logs (
    id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    organization_id  uuid,            -- nullable: pre-auth events (e.g. failed login) have no org yet
    actor_user_id    uuid,            -- nullable: system/anonymous actor
    action           text NOT NULL,   -- 'candidate.create','slate.generate','export','login','candidate.search',...
    entity_type      text,
    entity_id        text,
    old_values       jsonb,
    new_values       jsonb,
    ip_address       text,
    user_agent       text,
    created_at       timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_audit_org_time ON audit_logs (organization_id, created_at DESC);
CREATE INDEX idx_audit_entity   ON audit_logs (entity_type, entity_id);
-- Privileged read path for NULL-org (platform/security) events so they aren't a black hole [review AUDIT-3].
CREATE INDEX idx_audit_system   ON audit_logs (created_at DESC) WHERE organization_id IS NULL;

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION forbid_mutation() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'append-only table: % not permitted', TG_OP;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd
CREATE TRIGGER audit_logs_append_only
    BEFORE UPDATE OR DELETE ON audit_logs
    FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
CREATE TRIGGER audit_logs_no_truncate
    BEFORE TRUNCATE ON audit_logs
    FOR EACH STATEMENT EXECUTE FUNCTION forbid_mutation();

-- ---------------------------------------------------------------------------
-- Grants. organizations: SELECT + UPDATE(name) ONLY (no status/slug self-edit).
-- audit_logs: INSERT/SELECT only, with explicit REVOKEs as belt-and-suspenders.
-- ---------------------------------------------------------------------------
GRANT SELECT          ON organizations TO missionmatch_app;
GRANT UPDATE (name)   ON organizations TO missionmatch_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO missionmatch_app;
GRANT SELECT, INSERT  ON audit_logs   TO missionmatch_app;
REVOKE ALL ON audit_logs FROM PUBLIC;
REVOKE UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON audit_logs FROM missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS audit_logs;
DROP FUNCTION IF EXISTS forbid_mutation();
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS organizations;