"""agent approvals

Revision ID: 000000000007
Revises: 000000000006
Create Date: 2026-02-11

Creates agents, agent_approvers, and agent_approvals tables for the
agent consent gateway feature.
"""

from alembic import op

revision = "000000000007"
down_revision = "000000000006"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # -----------------------------------------------------------------------
    # agents table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS agents (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            owner_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            name VARCHAR(100) NOT NULL,
            description VARCHAR(500),
            agent_secret_hash VARCHAR(128) NOT NULL,
            agent_secret_prefix VARCHAR(8) NOT NULL,
            scopes JSONB DEFAULT '[]'::jsonb,
            metadata JSONB DEFAULT '{}'::jsonb,
            is_active BOOLEAN NOT NULL DEFAULT true,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
        );
    """)

    # Unique name per application (active agents only)
    op.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS uq_agents_app_name_active
        ON agents (application_id, name)
        WHERE is_active = true;
    """)

    # Lookup indexes
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_agents_app_owner
        ON agents (application_id, owner_user_id);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_agents_owner
        ON agents (owner_user_id);
    """)

    # -----------------------------------------------------------------------
    # agent_approvers table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS agent_approvers (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
            approver_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
            approver_agent_id UUID REFERENCES agents(id) ON DELETE CASCADE,
            approver_type VARCHAR(10) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

            CONSTRAINT ck_agent_approvers_type
                CHECK (approver_type IN ('user', 'agent')),

            CONSTRAINT ck_agent_approvers_exactly_one_fk
                CHECK (
                    (approver_type = 'user' AND approver_user_id IS NOT NULL AND approver_agent_id IS NULL)
                    OR (approver_type = 'agent' AND approver_agent_id IS NOT NULL AND approver_user_id IS NULL)
                )
        );
    """)

    # Unique approver per agent (user)
    op.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS uq_agent_approvers_user
        ON agent_approvers (agent_id, approver_user_id)
        WHERE approver_type = 'user';
    """)

    # Unique approver per agent (agent)
    op.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS uq_agent_approvers_agent
        ON agent_approvers (agent_id, approver_agent_id)
        WHERE approver_type = 'agent';
    """)

    # Lookup indexes
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_agent_approvers_agent
        ON agent_approvers (agent_id);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_agent_approvers_user
        ON agent_approvers (approver_user_id)
        WHERE approver_user_id IS NOT NULL;
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_agent_approvers_agent_id
        ON agent_approvers (approver_agent_id)
        WHERE approver_agent_id IS NOT NULL;
    """)

    # -----------------------------------------------------------------------
    # agent_approvals table
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS agent_approvals (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
            code VARCHAR(9) NOT NULL UNIQUE,
            action VARCHAR(200) NOT NULL,
            resource_type VARCHAR(100),
            resource_id VARCHAR(500),
            reason VARCHAR(1000),
            metadata JSONB DEFAULT '{}'::jsonb,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            callback_url VARCHAR(2048),
            note VARCHAR(500),
            responded_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
            responded_by_agent_id UUID REFERENCES agents(id) ON DELETE SET NULL,
            responder_type VARCHAR(10),
            responded_at TIMESTAMPTZ,
            approval_token TEXT,
            token_expires_at TIMESTAMPTZ,
            expires_at TIMESTAMPTZ NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

            CONSTRAINT ck_agent_approvals_status
                CHECK (status IN ('pending', 'approved', 'denied', 'expired'))
        );
    """)

    # Composite index for agent polling
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_agent_approvals_app_agent_status
        ON agent_approvals (application_id, agent_id, status);
    """)

    # Partial index for expiration cleanup
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_agent_approvals_pending_expiry
        ON agent_approvals (status, expires_at)
        WHERE status = 'pending';
    """)


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS agent_approvals CASCADE;")
    op.execute("DROP TABLE IF EXISTS agent_approvers CASCADE;")
    op.execute("DROP TABLE IF EXISTS agents CASCADE;")