-- token_lineage: 2026-10 pre-deploy fixes (AUTH-P2, AUTH-P4).

-- AUTH-P2: SQLite recursive triggers are off, so deleting a child inside the trigger never fires the trigger
-- for the child's own children. The trigger therefore resolves every descendant (REST-minted children via
-- access_tokens.parent_token_id, MCP delegations via mcp_token_agents.parent_token_id) in one recursive query.
DROP TRIGGER access_tokens_revoke_mcp;

CREATE TRIGGER access_tokens_revoke_mcp BEFORE DELETE ON access_tokens
BEGIN
  DELETE FROM oauth_refresh_tokens WHERE access_token_id = OLD.id OR access_token_id IN (
    WITH RECURSIVE edges(child, parent) AS (
      SELECT id, parent_token_id FROM access_tokens WHERE parent_token_id IS NOT NULL
      UNION SELECT token_id, parent_token_id FROM mcp_token_agents WHERE parent_token_id IS NOT NULL
    ), descendants(id) AS (
      SELECT child FROM edges WHERE parent = OLD.id
      UNION SELECT edges.child FROM edges JOIN descendants ON edges.parent = descendants.id
    ) SELECT id FROM descendants);
  DELETE FROM deploy_tokens WHERE parent_token_id = OLD.id OR parent_token_id IN (
    WITH RECURSIVE edges(child, parent) AS (
      SELECT id, parent_token_id FROM access_tokens WHERE parent_token_id IS NOT NULL
      UNION SELECT token_id, parent_token_id FROM mcp_token_agents WHERE parent_token_id IS NOT NULL
    ), descendants(id) AS (
      SELECT child FROM edges WHERE parent = OLD.id
      UNION SELECT edges.child FROM edges JOIN descendants ON edges.parent = descendants.id
    ) SELECT id FROM descendants);
  DELETE FROM access_tokens WHERE id IN (
    WITH RECURSIVE edges(child, parent) AS (
      SELECT id, parent_token_id FROM access_tokens WHERE parent_token_id IS NOT NULL
      UNION SELECT token_id, parent_token_id FROM mcp_token_agents WHERE parent_token_id IS NOT NULL
    ), descendants(id) AS (
      SELECT child FROM edges WHERE parent = OLD.id
      UNION SELECT edges.child FROM edges JOIN descendants ON edges.parent = descendants.id
    ) SELECT id FROM descendants);
END;

-- AUTH-P2: delegated tokens minted before this migration only recorded their parent in mcp_token_agents.
UPDATE access_tokens SET parent_token_id = (
  SELECT m.parent_token_id FROM mcp_token_agents m WHERE m.token_id = access_tokens.id
) WHERE parent_token_id IS NULL
  AND EXISTS (SELECT 1 FROM mcp_token_agents m WHERE m.token_id = access_tokens.id AND m.parent_token_id IS NOT NULL);

-- AUTH-P4: wrong TOTP codes are counted per user (not per pending login) so they survive new sign-in attempts.
ALTER TABLE users ADD COLUMN totp_failures INTEGER NOT NULL DEFAULT 0;

ALTER TABLE users ADD COLUMN totp_locked_until INTEGER;

-- AUTH-P3/P9: deploy tokens are revoked by their creator.
CREATE INDEX deploy_tokens_creator ON deploy_tokens(created_by);

-- AUTH-P9: a deploy token is repo-wide access delegated by an admin, so it goes when its creator stops being
-- an admin of the repo (collaborator removed or downgraded, org owner removed or demoted). Repo owners and org
-- owners hold admin through ownership; site admins keep theirs.
CREATE TRIGGER collaborators_revoke_deploy_on_delete AFTER DELETE ON collaborators
WHEN OLD.permission = 'admin'
BEGIN
  DELETE FROM deploy_tokens WHERE repo_id = OLD.repo_id AND created_by = OLD.user_id
    AND NOT EXISTS (SELECT 1 FROM repos r WHERE r.id = OLD.repo_id AND r.owner_id = OLD.user_id)
    AND NOT EXISTS (SELECT 1 FROM repos r JOIN org_members m ON m.org_id = r.owner_id
      WHERE r.id = OLD.repo_id AND m.user_id = OLD.user_id AND m.role = 'owner')
    AND NOT EXISTS (SELECT 1 FROM users u WHERE u.account_id = OLD.user_id AND u.is_admin = 1);
END;

CREATE TRIGGER collaborators_revoke_deploy_on_downgrade AFTER UPDATE OF permission ON collaborators
WHEN OLD.permission = 'admin' AND NEW.permission != 'admin'
BEGIN
  DELETE FROM deploy_tokens WHERE repo_id = OLD.repo_id AND created_by = OLD.user_id
    AND NOT EXISTS (SELECT 1 FROM repos r WHERE r.id = OLD.repo_id AND r.owner_id = OLD.user_id)
    AND NOT EXISTS (SELECT 1 FROM repos r JOIN org_members m ON m.org_id = r.owner_id
      WHERE r.id = OLD.repo_id AND m.user_id = OLD.user_id AND m.role = 'owner')
    AND NOT EXISTS (SELECT 1 FROM users u WHERE u.account_id = OLD.user_id AND u.is_admin = 1);
END;

CREATE TRIGGER org_members_revoke_deploy_on_delete AFTER DELETE ON org_members
WHEN OLD.role = 'owner'
BEGIN
  DELETE FROM deploy_tokens WHERE created_by = OLD.user_id
    AND repo_id IN (SELECT id FROM repos WHERE owner_id = OLD.org_id)
    AND NOT EXISTS (SELECT 1 FROM collaborators c WHERE c.repo_id = deploy_tokens.repo_id
      AND c.user_id = OLD.user_id AND c.permission = 'admin')
    AND NOT EXISTS (SELECT 1 FROM users u WHERE u.account_id = OLD.user_id AND u.is_admin = 1);
END;

CREATE TRIGGER org_members_revoke_deploy_on_demote AFTER UPDATE OF role ON org_members
WHEN OLD.role = 'owner' AND NEW.role != 'owner'
BEGIN
  DELETE FROM deploy_tokens WHERE created_by = OLD.user_id
    AND repo_id IN (SELECT id FROM repos WHERE owner_id = OLD.org_id)
    AND NOT EXISTS (SELECT 1 FROM collaborators c WHERE c.repo_id = deploy_tokens.repo_id
      AND c.user_id = OLD.user_id AND c.permission = 'admin')
    AND NOT EXISTS (SELECT 1 FROM users u WHERE u.account_id = OLD.user_id AND u.is_admin = 1);
END;