-- +goose Up
-- Two access control additions: a superuser tier above owner, and per user tool entitlement.
--
-- They answer different questions and are deliberately kept separate. The ROLE says what a person may
-- DO (invite, disable, edit a requirement, run an export). The TOOL SET says what they may SEE (which of
-- the four products appears at all). A recruiter at one client may work only recruitMATCH and slateMATCH
-- while a recruiter at another sees everything, and that is not a difference in authority, so it must
-- not be expressed as a difference in role.

-- ---------------------------------------------------------------------------------------------------
-- 1. The superuser tier.
--
-- The ladder becomes viewer, member, recruiter, manager, admin, owner, superuser. The existing rule
-- stands for everyone: you may only grant a role strictly BELOW your own, so an admin cannot mint an
-- admin and an owner cannot mint an owner.
--
-- Superuser is the single deliberate exception, and the reason is operational rather than theoretical:
-- the tier has to be able to reproduce itself or it cannot survive its holders. The named holders grant
-- it to each other, and it is enforced in the service, not here. This constraint only widens the
-- vocabulary; who may write which value is the auth service's separation of duties check.
ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check
    CHECK (role IN ('superuser', 'owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));

-- Both vocabularies must move together. Migration 0058 was shipped incomplete because only the users
-- table was widened, and inviting a manager then failed on the invite constraint instead.
ALTER TABLE user_invites DROP CONSTRAINT user_invites_role_check;
ALTER TABLE user_invites ADD CONSTRAINT user_invites_role_check
    CHECK (role IN ('superuser', 'owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));

-- ---------------------------------------------------------------------------------------------------
-- 2. Per user tool entitlement.
--
-- A row grants one user access to one tool. Absence of a row is absence of access: the API and the
-- interface both fail CLOSED, so a user created without an explicit grant sees nothing rather than
-- everything. That direction matters because the failure mode of the opposite default is silent over
-- exposure of a whole product to someone who was never meant to have it.
--
-- The grant is recorded with WHO granted it and WHEN, because an entitlement change is an access control
-- event and the first question after an incident is who widened it.
CREATE TABLE IF NOT EXISTS user_tool_access (
    organization_id uuid        NOT NULL REFERENCES organizations (id),
    user_id         uuid        NOT NULL,
    tool            text        NOT NULL
                    CHECK (tool IN ('missionmatch', 'screenmatch', 'slatematch', 'recruitmatch')),
    granted_by      uuid,
    granted_at      timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT user_tool_access_pkey PRIMARY KEY (organization_id, user_id, tool),

    -- Composite, leading with organization_id, so the reference cannot resolve across a tenant boundary
    -- (CLAUDE.md rule 2 and the composite FK convention the rest of the schema uses).
    CONSTRAINT user_tool_access_user_fkey
        FOREIGN KEY (organization_id, user_id) REFERENCES users (organization_id, id) ON DELETE CASCADE,

    -- The granter is recorded for the audit trail, not for referential integrity of the grant itself: if
    -- that account is later deleted the entitlement must SURVIVE, or removing an administrator would
    -- silently revoke access for everyone they ever granted. The column list form is required; a bare
    -- ON DELETE SET NULL would try to null organization_id too, which is NOT NULL (see migration 0053).
    CONSTRAINT user_tool_access_granted_by_fkey
        FOREIGN KEY (organization_id, granted_by) REFERENCES users (organization_id, id)
        ON DELETE SET NULL (granted_by)
);

-- The hot read is "which tools does this user have", on every session resolve.
CREATE INDEX IF NOT EXISTS idx_user_tool_access_user ON user_tool_access (organization_id, user_id);

-- +goose StatementBegin
DO $$
BEGIN
    -- Tenant scoped like every other user facing table: forced RLS so even the table owner is subject to
    -- the policy, and no org context yields zero rows.
    EXECUTE 'ALTER TABLE user_tool_access ENABLE ROW LEVEL SECURITY';
    EXECUTE 'ALTER TABLE user_tool_access FORCE ROW LEVEL SECURITY';
    IF NOT EXISTS (SELECT 1 FROM pg_policy WHERE polname = 'user_tool_access_tenant_isolation') THEN
        EXECUTE $p$
            CREATE POLICY user_tool_access_tenant_isolation ON user_tool_access
            USING (organization_id = COALESCE(NULLIF(current_setting('app.current_org_id', true), ''), '00000000-0000-0000-0000-000000000000')::uuid)
            WITH CHECK (organization_id = COALESCE(NULLIF(current_setting('app.current_org_id', true), ''), '00000000-0000-0000-0000-000000000000')::uuid)
        $p$;
    END IF;
END
$$;
-- +goose StatementEnd

GRANT SELECT, INSERT, DELETE ON user_tool_access TO missionmatch_app;

-- ---------------------------------------------------------------------------------------------------
-- 3. Backfill, which is the part that must not be skipped.
--
-- Every existing user today sees all four tools, because tool gating did not exist. Creating the table
-- and failing closed WITHOUT this backfill would revoke every product from every existing account the
-- moment this migration lands, including the owner's, which locks the operator out of the very screen
-- that grants access back. Existing accounts therefore keep exactly what they have; fail closed applies
-- to accounts created from here on.
--
-- granted_by is left NULL: no human granted these, the migration did, and recording a person would be a
-- false audit entry.
INSERT INTO user_tool_access (organization_id, user_id, tool)
SELECT u.organization_id, u.id, t.tool
FROM users u
CROSS JOIN (VALUES ('missionmatch'), ('screenmatch'), ('slatematch'), ('recruitmatch')) AS t(tool)
ON CONFLICT DO NOTHING;

-- +goose Down
DROP TABLE IF EXISTS user_tool_access;

-- Demote BEFORE narrowing, on both tables. The users half was ordered correctly and the user_invites half
-- was inverted: the CHECK was re added first, so a single pending superuser invite aborted the rollback
-- with a check violation, and because the DROP had already succeeded the invite table was left with NO
-- role vocabulary at all while user_tool_access was already gone. A half applied rollback that removes a
-- constraint and then fails is worse than one that refuses to start.
--
-- Demoting to owner preserves the most authority the narrower vocabulary can express.
UPDATE users        SET role = 'owner' WHERE role = 'superuser';
UPDATE user_invites SET role = 'owner' WHERE role = 'superuser';

ALTER TABLE user_invites DROP CONSTRAINT user_invites_role_check;
ALTER TABLE user_invites ADD CONSTRAINT user_invites_role_check
    CHECK (role IN ('owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));

ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check
    CHECK (role IN ('owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));