-- +goose Up
-- Phase 2 Authentication and Accounts. Run as the BYPASSRLS migrator role.
--
-- Posture (see docs/STACK_DECISIONS.md section 7): the secret bearing tables (auth_credentials,
-- auth_sessions, mfa_totp, mfa_recovery_codes, user_invites) live OUTSIDE RLS and the app role gets
-- NO direct table grants on them. Every app access goes through a SECURITY DEFINER function owned by
-- the migrator with a locked search_path, EXECUTE granted narrowly. That restores DB side default
-- deny for the most sensitive data: the app role literally cannot SELECT a credential or session row,
-- so a forgotten predicate cannot leak another org's secrets. provision_org is migrator only.
--
-- Every per user table uses a COMPOSITE foreign key (organization_id, user_id) to users so the org on
-- the row is pinned to the user's real org by the database, closing cross org credential binding.

-- ---------------------------------------------------------------------------
-- users: gain the MFA enrollment flag and a GLOBALLY unique email. Identity is one human, one
-- account, one org, so login can resolve a user by email before the org context is known. The pre
-- existing per org unique index is kept (it is implied by the global one but other code may name it).
-- ---------------------------------------------------------------------------
ALTER TABLE users ADD COLUMN mfa_enrolled boolean NOT NULL DEFAULT false;
CREATE UNIQUE INDEX uq_users_global_email ON users (lower(email));

-- ---------------------------------------------------------------------------
-- auth_credentials: one password credential per user. OUTSIDE RLS, no app table grants. The composite
-- FK pins the org to the user's org; the global unique email reconciles with users so a second org
-- cannot shadow the first.
-- ---------------------------------------------------------------------------
CREATE TABLE auth_credentials (
    user_id             uuid PRIMARY KEY,
    organization_id     uuid NOT NULL,
    email_normalized    text NOT NULL,
    password_hash       text NOT NULL,
    pepper_version      int  NOT NULL,
    failed_streak       int  NOT NULL DEFAULT 0,
    locked_until        timestamptz,
    password_changed_at timestamptz NOT NULL DEFAULT now(),
    created_at          timestamptz NOT NULL DEFAULT now(),
    updated_at          timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_cred_email_global UNIQUE (email_normalized),
    CONSTRAINT fk_cred_user_org FOREIGN KEY (organization_id, user_id)
        REFERENCES users (organization_id, id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------------
-- auth_sessions: opaque server side sessions, stored only as a token hash. OUTSIDE RLS, no app table
-- grants. organization_id is a convenience copy; auth_validate_session JOINs users and returns the
-- authoritative org, failing closed on a mismatch.
-- ---------------------------------------------------------------------------
CREATE TABLE auth_sessions (
    id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    token_hash          bytea NOT NULL UNIQUE,
    user_id             uuid NOT NULL,
    organization_id     uuid NOT NULL,
    state               text NOT NULL CHECK (state IN ('auth_pending', 'active')),
    mfa_satisfied       boolean NOT NULL DEFAULT false,
    created_at          timestamptz NOT NULL DEFAULT now(),
    last_seen_at        timestamptz NOT NULL DEFAULT now(),
    idle_expires_at     timestamptz NOT NULL,
    absolute_expires_at timestamptz NOT NULL,
    revoked_at          timestamptz,
    rotated_from        uuid,
    ip_address          text,
    user_agent          text,
    CONSTRAINT fk_session_user_org FOREIGN KEY (organization_id, user_id)
        REFERENCES users (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_auth_sessions_user_live ON auth_sessions (user_id) WHERE revoked_at IS NULL;
CREATE INDEX idx_auth_sessions_sweep ON auth_sessions (absolute_expires_at);

-- ---------------------------------------------------------------------------
-- mfa_totp: one TOTP secret per user, AES 256 GCM encrypted at rest with a key_version for rotation.
-- last_used_step is the strictly monotonic replay marker. OUTSIDE RLS, no app table grants, reached
-- only by the auth definers (MFA enroll and verify run pre org on an auth_pending session).
-- ---------------------------------------------------------------------------
CREATE TABLE mfa_totp (
    user_id           uuid PRIMARY KEY,
    organization_id   uuid NOT NULL,
    secret_ciphertext bytea NOT NULL,
    secret_nonce      bytea NOT NULL,
    key_version       int  NOT NULL,
    status            text NOT NULL CHECK (status IN ('pending', 'active')),
    last_used_step    bigint NOT NULL DEFAULT 0,
    created_at        timestamptz NOT NULL DEFAULT now(),
    activated_at      timestamptz,
    CONSTRAINT fk_mfa_user_org FOREIGN KEY (organization_id, user_id)
        REFERENCES users (organization_id, id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------------
-- mfa_recovery_codes: single use backup codes. code_hash is PBKDF2, lookup_tag is a non secret HMAC
-- so a recovery attempt runs at most one PBKDF2 verify. OUTSIDE RLS, no app table grants.
-- ---------------------------------------------------------------------------
CREATE TABLE mfa_recovery_codes (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         uuid NOT NULL,
    organization_id uuid NOT NULL,
    code_hash       text  NOT NULL,
    pepper_version  int   NOT NULL,
    lookup_tag      bytea NOT NULL,
    used_at         timestamptz,
    created_at      timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT fk_recovery_user_org FOREIGN KEY (organization_id, user_id)
        REFERENCES users (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_recovery_lookup_live ON mfa_recovery_codes (user_id, lookup_tag) WHERE used_at IS NULL;

-- ---------------------------------------------------------------------------
-- user_invites: single use, time boxed invite tokens (stored hashed) redeemed before the invitee has
-- a session. OUTSIDE RLS, no app table grants. role bounds are enforced in the API and re asserted at
-- accept; the CHECK permits owner only because the migrator provisioning path issues the owner's
-- activation through this same table.
-- ---------------------------------------------------------------------------
CREATE TABLE user_invites (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id  uuid NOT NULL,
    email_normalized text NOT NULL,
    invited_user_id  uuid NOT NULL,
    token_hash       bytea NOT NULL UNIQUE,
    role             text NOT NULL CHECK (role IN ('owner', 'admin', 'recruiter', 'member', 'viewer')),
    expires_at       timestamptz NOT NULL,
    accepted_at      timestamptz,
    created_by       uuid NOT NULL,
    created_at       timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT fk_invite_user_org FOREIGN KEY (organization_id, invited_user_id)
        REFERENCES users (organization_id, id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------------
-- login_attempts: brute force counter. OUTSIDE RLS but the ONE auth table with a narrow app grant
-- (no secrets). A row is written for known AND unknown emails so lock behavior is not an enumeration
-- oracle.
-- ---------------------------------------------------------------------------
CREATE TABLE login_attempts (
    id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email_normalized text NOT NULL,
    organization_id  uuid,
    ip_address       text,
    succeeded        boolean NOT NULL,
    reason           text,
    created_at       timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_login_attempts_email ON login_attempts (email_normalized, created_at DESC);
CREATE INDEX idx_login_attempts_ip ON login_attempts (ip_address, created_at DESC);

-- ===========================================================================
-- SECURITY DEFINER functions. Owned by the migrator (this script runs as migrator), search_path
-- locked, EXECUTE revoked from PUBLIC below and granted narrowly.
-- ===========================================================================

-- +goose StatementBegin
CREATE FUNCTION auth_lookup_login(p_email text)
RETURNS TABLE (
    user_id uuid, organization_id uuid, role text, user_status text,
    password_hash text, pepper_version int, mfa_enrolled boolean,
    mfa_status text, mfa_secret_ciphertext bytea, mfa_secret_nonce bytea,
    mfa_key_version int, last_used_step bigint
)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
    SELECT u.id, u.organization_id, u.role, u.status,
           c.password_hash, c.pepper_version, u.mfa_enrolled,
           m.status, m.secret_ciphertext, m.secret_nonce, m.key_version, m.last_used_step
    FROM users u
    JOIN auth_credentials c ON c.user_id = u.id
    LEFT JOIN mfa_totp m ON m.user_id = u.id
    WHERE c.email_normalized = lower(p_email)
    LIMIT 1;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_create_session(
    p_token_hash bytea, p_user_id uuid, p_org_id uuid, p_state text,
    p_mfa_satisfied boolean, p_idle_seconds int, p_absolute_seconds int, p_ip text, p_ua text)
RETURNS TABLE (session_id uuid, idle_expires_at timestamptz, absolute_expires_at timestamptz)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
    INSERT INTO auth_sessions (
        token_hash, user_id, organization_id, state, mfa_satisfied,
        idle_expires_at, absolute_expires_at, ip_address, user_agent)
    VALUES (
        p_token_hash, p_user_id, p_org_id, p_state, p_mfa_satisfied,
        now() + make_interval(secs => p_idle_seconds),
        now() + make_interval(secs => p_absolute_seconds), p_ip, p_ua)
    RETURNING id, idle_expires_at, absolute_expires_at;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- The single atomic validate, slide, and revocation check. Returns the AUTHORITATIVE users.org (the
-- JOIN, never the stored session copy) and fails closed on a session.org to users.org mismatch, so a
-- moved org user's stale session is rejected, not silently re scoped. A revoked or expired row can
-- never be slid because the guard and the update are one statement. The org is returned for both
-- auth_pending and active sessions because the pre MFA enroll and verify flows need the user's own
-- org for their definer writes; the request resolver grants tenant context ONLY when state is active
-- and mfa_satisfied is true, and RunInTenantTx plus RLS still fail closed if that gate is ever missed.
CREATE FUNCTION auth_validate_session(p_token_hash bytea, p_idle_seconds int)
RETURNS TABLE (
    session_id uuid, user_id uuid, organization_id uuid, role text,
    state text, mfa_satisfied boolean, user_status text)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
#variable_conflict use_column
BEGIN
    RETURN QUERY
    UPDATE auth_sessions s
       SET last_seen_at = now(),
           idle_expires_at = LEAST(now() + make_interval(secs => p_idle_seconds), s.absolute_expires_at)
      FROM users u
     WHERE s.token_hash = p_token_hash
       AND s.user_id = u.id
       AND s.revoked_at IS NULL
       AND now() < s.absolute_expires_at
       AND now() < s.idle_expires_at
       AND u.status = 'active'
       AND s.organization_id = u.organization_id
    RETURNING s.id, s.user_id, u.organization_id, u.role, s.state, s.mfa_satisfied, u.status;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- Privilege transition: revoke the old token and mint a new one carrying the same user and org, in
-- one statement pair, so the pre transition token is invalid in the same request cycle.
CREATE FUNCTION auth_rotate_session(
    p_old_token_hash bytea, p_new_token_hash bytea, p_new_state text, p_mfa_satisfied boolean,
    p_idle_seconds int, p_absolute_seconds int, p_ip text, p_ua text)
RETURNS TABLE (session_id uuid, idle_expires_at timestamptz, absolute_expires_at timestamptz)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
#variable_conflict use_column
DECLARE v_old_id uuid; v_user uuid; v_org uuid;
BEGIN
    UPDATE auth_sessions SET revoked_at = now()
     WHERE token_hash = p_old_token_hash AND revoked_at IS NULL
    RETURNING id, user_id, organization_id INTO v_old_id, v_user, v_org;
    IF v_user IS NULL THEN
        RETURN;
    END IF;
    RETURN QUERY
    INSERT INTO auth_sessions (
        token_hash, user_id, organization_id, state, mfa_satisfied,
        idle_expires_at, absolute_expires_at, rotated_from, ip_address, user_agent)
    VALUES (
        p_new_token_hash, v_user, v_org, p_new_state, p_mfa_satisfied,
        now() + make_interval(secs => p_idle_seconds),
        now() + make_interval(secs => p_absolute_seconds), v_old_id, p_ip, p_ua)
    RETURNING id, idle_expires_at, absolute_expires_at;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_revoke_session(p_token_hash bytea)
RETURNS integer
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE n integer;
BEGIN
    UPDATE auth_sessions SET revoked_at = now()
     WHERE token_hash = p_token_hash AND revoked_at IS NULL;
    GET DIAGNOSTICS n = ROW_COUNT;
    RETURN n;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- Cascade revoke. p_except_token_hash keeps the caller's current session alive on a password change.
CREATE FUNCTION auth_revoke_user_sessions(p_user_id uuid, p_except_token_hash bytea)
RETURNS integer
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE n integer;
BEGIN
    UPDATE auth_sessions SET revoked_at = now()
     WHERE user_id = p_user_id AND revoked_at IS NULL
       AND (p_except_token_hash IS NULL OR token_hash <> p_except_token_hash);
    GET DIAGNOSTICS n = ROW_COUNT;
    RETURN n;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_set_credential(p_user_id uuid, p_org_id uuid, p_email text, p_hash text, p_pepper_version int)
RETURNS void
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
    INSERT INTO auth_credentials (user_id, organization_id, email_normalized, password_hash, pepper_version)
    VALUES (p_user_id, p_org_id, lower(p_email), p_hash, p_pepper_version)
    ON CONFLICT (user_id) DO UPDATE
        SET password_hash = EXCLUDED.password_hash,
            pepper_version = EXCLUDED.pepper_version,
            password_changed_at = now(),
            updated_at = now();
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- Read a credential by user id (for the authenticated password change path, where email is unknown
-- but the validated session gives the user id).
CREATE FUNCTION auth_get_credential(p_user_id uuid)
RETURNS TABLE (organization_id uuid, email_normalized text, password_hash text, pepper_version int)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
    SELECT organization_id, email_normalized, password_hash, pepper_version
    FROM auth_credentials WHERE user_id = p_user_id;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- Enroll writes a pending secret. The ON CONFLICT update is scoped to rows that are still pending, so
-- it can NEVER overwrite an already active authenticator. That closes the account takeover where an
-- attacker holding the password re enrolls their own secret from a pre MFA session; a genuine reset
-- must go through the authenticated admin auth_mfa_reset path. Returns true only when a pending row
-- was written.
CREATE FUNCTION auth_mfa_enroll(p_user_id uuid, p_org_id uuid, p_ciphertext bytea, p_nonce bytea, p_key_version int)
RETURNS boolean
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE n integer;
BEGIN
    INSERT INTO mfa_totp (user_id, organization_id, secret_ciphertext, secret_nonce, key_version, status)
    VALUES (p_user_id, p_org_id, p_ciphertext, p_nonce, p_key_version, 'pending')
    ON CONFLICT (user_id) DO UPDATE
        SET secret_ciphertext = EXCLUDED.secret_ciphertext,
            secret_nonce = EXCLUDED.secret_nonce,
            key_version = EXCLUDED.key_version,
            status = 'pending',
            last_used_step = 0,
            created_at = now(),
            activated_at = NULL
        WHERE mfa_totp.status = 'pending';
    GET DIAGNOSTICS n = ROW_COUNT;
    RETURN n > 0;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_mfa_activate(p_user_id uuid)
RETURNS boolean
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE n integer;
BEGIN
    UPDATE mfa_totp SET status = 'active', activated_at = now()
     WHERE user_id = p_user_id AND status = 'pending';
    GET DIAGNOSTICS n = ROW_COUNT;
    IF n = 0 THEN
        RETURN false;
    END IF;
    UPDATE users SET mfa_enrolled = true, updated_at = now() WHERE id = p_user_id;
    RETURN true;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_mfa_get(p_user_id uuid)
RETURNS TABLE (secret_ciphertext bytea, secret_nonce bytea, key_version int, status text, last_used_step bigint)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
    SELECT secret_ciphertext, secret_nonce, key_version, status, last_used_step
    FROM mfa_totp WHERE user_id = p_user_id;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- Monotonic advance: only moves the marker forward, so a consumed or earlier step cannot be reused.
CREATE FUNCTION auth_mfa_advance_step(p_user_id uuid, p_step bigint)
RETURNS integer
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE n integer;
BEGIN
    UPDATE mfa_totp SET last_used_step = p_step
     WHERE user_id = p_user_id AND last_used_step < p_step;
    GET DIAGNOSTICS n = ROW_COUNT;
    RETURN n;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_mfa_reset(p_user_id uuid)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
BEGIN
    DELETE FROM mfa_totp WHERE user_id = p_user_id;
    DELETE FROM mfa_recovery_codes WHERE user_id = p_user_id;
    UPDATE users SET mfa_enrolled = false, updated_at = now() WHERE id = p_user_id;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_recovery_store(p_user_id uuid, p_org_id uuid, p_hashes text[], p_versions int[], p_tags bytea[])
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
BEGIN
    DELETE FROM mfa_recovery_codes WHERE user_id = p_user_id;
    INSERT INTO mfa_recovery_codes (user_id, organization_id, code_hash, pepper_version, lookup_tag)
    SELECT p_user_id, p_org_id, h, v, t
    FROM unnest(p_hashes, p_versions, p_tags) AS x(h, v, t);
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_recovery_lookup(p_user_id uuid, p_tag bytea)
RETURNS TABLE (id uuid, code_hash text, pepper_version int)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
    SELECT id, code_hash, pepper_version
    FROM mfa_recovery_codes
    WHERE user_id = p_user_id AND lookup_tag = p_tag AND used_at IS NULL
    LIMIT 1;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- Atomic single use claim: a second concurrent claim of the same row affects zero rows.
CREATE FUNCTION auth_recovery_claim(p_id uuid)
RETURNS integer
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
DECLARE n integer;
BEGIN
    UPDATE mfa_recovery_codes SET used_at = now() WHERE id = p_id AND used_at IS NULL;
    GET DIAGNOSTICS n = ROW_COUNT;
    RETURN n;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE FUNCTION auth_create_invite(
    p_org_id uuid, p_email text, p_invited_user_id uuid, p_token_hash bytea,
    p_role text, p_expires_seconds int, p_created_by uuid)
RETURNS uuid
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
    INSERT INTO user_invites (
        organization_id, email_normalized, invited_user_id, token_hash, role, expires_at, created_by)
    VALUES (
        p_org_id, lower(p_email), p_invited_user_id, p_token_hash, p_role,
        now() + make_interval(secs => p_expires_seconds), p_created_by)
    RETURNING id;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- Accept asserts token.org == users.org AND the invite email matches the users row, takes the org
-- from the authoritative users row, creates the credential, and activates the user, all in one tx.
-- Any mismatch returns no rows so a tampered or replayed invite cannot plant a cross org login.
CREATE FUNCTION auth_accept_invite(p_token_hash bytea, p_password_hash text, p_pepper_version int)
RETURNS TABLE (user_id uuid, organization_id uuid)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
#variable_conflict use_column
DECLARE v_inv user_invites%ROWTYPE; v_user users%ROWTYPE;
BEGIN
    SELECT * INTO v_inv FROM user_invites
     WHERE token_hash = p_token_hash AND accepted_at IS NULL AND now() < expires_at;
    IF v_inv.id IS NULL THEN
        RETURN;
    END IF;
    SELECT * INTO v_user FROM users WHERE id = v_inv.invited_user_id;
    IF v_user.id IS NULL
       OR v_user.organization_id <> v_inv.organization_id
       OR lower(v_user.email) <> v_inv.email_normalized THEN
        RETURN;
    END IF;
    INSERT INTO auth_credentials (user_id, organization_id, email_normalized, password_hash, pepper_version)
    VALUES (v_user.id, v_user.organization_id, lower(v_user.email), p_password_hash, p_pepper_version)
    ON CONFLICT (user_id) DO UPDATE
        SET password_hash = EXCLUDED.password_hash, pepper_version = EXCLUDED.pepper_version, updated_at = now();
    UPDATE user_invites SET accepted_at = now() WHERE id = v_inv.id;
    UPDATE users SET status = 'active', updated_at = now() WHERE id = v_user.id;
    RETURN QUERY SELECT v_user.id, v_user.organization_id;
END;
$$;
-- +goose StatementEnd

-- +goose StatementBegin
-- Genesis: migrator only. Creates the org, the owner user (invited, MFA not yet enrolled), and the
-- owner's one time activation invite, in one tx. EXECUTE is revoked from app and PUBLIC below.
CREATE FUNCTION provision_org(
    p_org_name text, p_org_slug text, p_owner_email text, p_owner_name text,
    p_token_hash bytea, p_invite_expires_seconds int)
RETURNS TABLE (organization_id uuid, owner_user_id uuid, invite_id uuid)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
#variable_conflict use_column
DECLARE v_org uuid; v_user uuid; v_inv uuid;
BEGIN
    INSERT INTO organizations (name, slug) VALUES (p_org_name, p_org_slug) RETURNING id INTO v_org;
    INSERT INTO users (organization_id, email, full_name, role, status, mfa_enrolled)
    VALUES (v_org, p_owner_email, p_owner_name, 'owner', 'invited', false)
    RETURNING id INTO v_user;
    INSERT INTO user_invites (organization_id, email_normalized, invited_user_id, token_hash, role, expires_at, created_by)
    VALUES (v_org, lower(p_owner_email), v_user, p_token_hash, 'owner',
            now() + make_interval(secs => p_invite_expires_seconds), v_user)
    RETURNING id INTO v_inv;
    RETURN QUERY SELECT v_org, v_user, v_inv;
END;
$$;
-- +goose StatementEnd

-- ===========================================================================
-- Grants. The app role gets ONLY EXECUTE on the auth definers plus the login_attempts counter. By
-- default CREATE FUNCTION grants EXECUTE to PUBLIC, so each function is revoked from PUBLIC first.
-- ===========================================================================
GRANT SELECT, INSERT ON login_attempts TO missionmatch_app;

REVOKE EXECUTE ON FUNCTION auth_lookup_login(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_create_session(bytea, uuid, uuid, text, boolean, int, int, text, text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_validate_session(bytea, int) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_rotate_session(bytea, bytea, text, boolean, int, int, text, text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_revoke_session(bytea) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_revoke_user_sessions(uuid, bytea) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_set_credential(uuid, uuid, text, text, int) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_get_credential(uuid) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_mfa_enroll(uuid, uuid, bytea, bytea, int) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_mfa_activate(uuid) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_mfa_get(uuid) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_mfa_advance_step(uuid, bigint) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_mfa_reset(uuid) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_recovery_store(uuid, uuid, text[], int[], bytea[]) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_recovery_lookup(uuid, bytea) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_recovery_claim(uuid) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_create_invite(uuid, text, uuid, bytea, text, int, uuid) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION auth_accept_invite(bytea, text, int) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION provision_org(text, text, text, text, bytea, int) FROM PUBLIC;

GRANT EXECUTE ON FUNCTION auth_lookup_login(text) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_create_session(bytea, uuid, uuid, text, boolean, int, int, text, text) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_validate_session(bytea, int) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_rotate_session(bytea, bytea, text, boolean, int, int, text, text) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_revoke_session(bytea) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_revoke_user_sessions(uuid, bytea) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_set_credential(uuid, uuid, text, text, int) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_get_credential(uuid) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_mfa_enroll(uuid, uuid, bytea, bytea, int) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_mfa_activate(uuid) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_mfa_get(uuid) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_mfa_advance_step(uuid, bigint) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_mfa_reset(uuid) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_recovery_store(uuid, uuid, text[], int[], bytea[]) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_recovery_lookup(uuid, bytea) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_recovery_claim(uuid) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_create_invite(uuid, text, uuid, bytea, text, int, uuid) TO missionmatch_app;
GRANT EXECUTE ON FUNCTION auth_accept_invite(bytea, text, int) TO missionmatch_app;
-- provision_org is intentionally NOT granted to missionmatch_app; the migrator executes it as owner.

-- +goose Down
DROP FUNCTION IF EXISTS provision_org(text, text, text, text, bytea, int);
DROP FUNCTION IF EXISTS auth_accept_invite(bytea, text, int);
DROP FUNCTION IF EXISTS auth_create_invite(uuid, text, uuid, bytea, text, int, uuid);
DROP FUNCTION IF EXISTS auth_recovery_claim(uuid);
DROP FUNCTION IF EXISTS auth_recovery_lookup(uuid, bytea);
DROP FUNCTION IF EXISTS auth_recovery_store(uuid, uuid, text[], int[], bytea[]);
DROP FUNCTION IF EXISTS auth_mfa_reset(uuid);
DROP FUNCTION IF EXISTS auth_mfa_advance_step(uuid, bigint);
DROP FUNCTION IF EXISTS auth_mfa_get(uuid);
DROP FUNCTION IF EXISTS auth_mfa_activate(uuid);
DROP FUNCTION IF EXISTS auth_mfa_enroll(uuid, uuid, bytea, bytea, int);
DROP FUNCTION IF EXISTS auth_get_credential(uuid);
DROP FUNCTION IF EXISTS auth_set_credential(uuid, uuid, text, text, int);
DROP FUNCTION IF EXISTS auth_revoke_user_sessions(uuid, bytea);
DROP FUNCTION IF EXISTS auth_revoke_session(bytea);
DROP FUNCTION IF EXISTS auth_rotate_session(bytea, bytea, text, boolean, int, int, text, text);
DROP FUNCTION IF EXISTS auth_validate_session(bytea, int);
DROP FUNCTION IF EXISTS auth_create_session(bytea, uuid, uuid, text, boolean, int, int, text, text);
DROP FUNCTION IF EXISTS auth_lookup_login(text);
DROP TABLE IF EXISTS login_attempts;
DROP TABLE IF EXISTS user_invites;
DROP TABLE IF EXISTS mfa_recovery_codes;
DROP TABLE IF EXISTS mfa_totp;
DROP TABLE IF EXISTS auth_sessions;
DROP TABLE IF EXISTS auth_credentials;
DROP INDEX IF EXISTS uq_users_global_email;
ALTER TABLE users DROP COLUMN IF EXISTS mfa_enrolled;