"""agent lifecycle

Revision ID: 064_agent_lifecycle
Revises: 063_invite_code_admin_quota
Create Date: 2026-06-21
"""

import sqlalchemy as sa
from alembic import op


revision = "064_agent_lifecycle"
down_revision = "063_invite_code_admin_quota"
branch_labels = None
depends_on = None


def _tables(bind) -> set[str]:
    return set(sa.inspect(bind).get_table_names())


def _columns(bind, table_name: str) -> set[str]:
    return {column["name"] for column in sa.inspect(bind).get_columns(table_name)}


def _index_names(bind, table_name: str) -> set[str]:
    return {index["name"] for index in sa.inspect(bind).get_indexes(table_name)}


def _add_column_if_missing(table: str, column: sa.Column) -> None:
    bind = op.get_bind()
    if table in _tables(bind) and column.name not in _columns(bind, table):
        op.add_column(table, column)


def _create_index_if_missing(table: str, index_name: str, columns: list[str]) -> None:
    bind = op.get_bind()
    if table in _tables(bind) and index_name not in _index_names(bind, table):
        op.create_index(index_name, table, columns)


def upgrade() -> None:
    bind = op.get_bind()
    tables = _tables(bind)

    if "user_agents" in tables:
        op.execute(
            """
            ALTER TABLE user_agents
            MODIFY status ENUM('pending','active','observing','risk_locked','suspended','rejected','terminated')
            NOT NULL DEFAULT 'pending'
            """
        )

    for column in (
        sa.Column("monthly_sales_required", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("monthly_active_customers_required", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("max_refund_rate_bps", sa.Integer(), nullable=False, server_default="5000"),
        sa.Column("max_monthly_subsidy_cents", sa.Integer(), nullable=False, server_default="10000"),
        sa.Column("min_balance_for_subsidy_cents", sa.Integer(), nullable=False, server_default="12800"),
        sa.Column("observe_days", sa.Integer(), nullable=False, server_default="7"),
        sa.Column("freeze_days", sa.Integer(), nullable=False, server_default="7"),
        sa.Column("auto_downgrade_enabled", sa.Boolean(), nullable=False, server_default=sa.true()),
    ):
        _add_column_if_missing("agent_tiers", column)

    for column in (
        sa.Column("observe_started_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("observe_until", sa.DateTime(timezone=True), nullable=True),
        sa.Column("risk_locked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("risk_reason", sa.Text(), nullable=True),
        sa.Column("last_evaluated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_evaluation_snapshot", sa.JSON(), nullable=True),
    ):
        _add_column_if_missing("user_agents", column)
    _create_index_if_missing("user_agents", "ix_user_agents_observe_until", ["observe_until"])
    _create_index_if_missing("user_agents", "ix_user_agents_last_evaluated_at", ["last_evaluated_at"])

    if "agent_evaluation_logs" not in tables:
        op.create_table(
            "agent_evaluation_logs",
            sa.Column("agent_id", sa.Integer(), nullable=False),
            sa.Column("user_id", sa.BigInteger(), nullable=False),
            sa.Column("tier_id", sa.Integer(), nullable=True),
            sa.Column("period_start", sa.DateTime(timezone=True), nullable=False),
            sa.Column("period_end", sa.DateTime(timezone=True), nullable=False),
            sa.Column("net_sales", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("gross_sales", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("refund_amount", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("refund_rate_bps", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("active_customers", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("subsidy_loss", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("agent_balance", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("passed", sa.Integer(), nullable=False, server_default="1"),
            sa.Column("risk_locked", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("action", sa.String(length=32), nullable=False, server_default="none"),
            sa.Column("reason", sa.Text(), nullable=True),
            sa.Column("snapshot", sa.JSON(), nullable=True),
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.ForeignKeyConstraint(["agent_id"], ["user_agents.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_agent_evaluation_logs_agent_id", "agent_evaluation_logs", ["agent_id"])
        op.create_index("ix_agent_evaluation_logs_user_id", "agent_evaluation_logs", ["user_id"])
        op.create_index("ix_agent_evaluation_logs_tier_id", "agent_evaluation_logs", ["tier_id"])
        op.create_index("ix_agent_evaluation_logs_action", "agent_evaluation_logs", ["action"])

    if "agent_status_logs" not in tables:
        op.create_table(
            "agent_status_logs",
            sa.Column("agent_id", sa.Integer(), nullable=False),
            sa.Column("user_id", sa.BigInteger(), nullable=False),
            sa.Column("operator_id", sa.BigInteger(), nullable=True),
            sa.Column("source", sa.String(length=24), nullable=False, server_default="system"),
            sa.Column("action", sa.String(length=32), nullable=False),
            sa.Column("before_status", sa.String(length=32), nullable=True),
            sa.Column("after_status", sa.String(length=32), nullable=True),
            sa.Column("before_tier_id", sa.Integer(), nullable=True),
            sa.Column("after_tier_id", sa.Integer(), nullable=True),
            sa.Column("reason", sa.Text(), nullable=False),
            sa.Column("snapshot", sa.JSON(), nullable=True),
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.ForeignKeyConstraint(["agent_id"], ["user_agents.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_agent_status_logs_agent_id", "agent_status_logs", ["agent_id"])
        op.create_index("ix_agent_status_logs_user_id", "agent_status_logs", ["user_id"])
        op.create_index("ix_agent_status_logs_operator_id", "agent_status_logs", ["operator_id"])
        op.create_index("ix_agent_status_logs_source", "agent_status_logs", ["source"])
        op.create_index("ix_agent_status_logs_action", "agent_status_logs", ["action"])

    if "agent_risk_events" not in tables:
        op.create_table(
            "agent_risk_events",
            sa.Column("agent_id", sa.Integer(), nullable=False),
            sa.Column("user_id", sa.BigInteger(), nullable=False),
            sa.Column("event_type", sa.String(length=32), nullable=False),
            sa.Column("severity", sa.String(length=16), nullable=False, server_default="warning"),
            sa.Column("status", sa.String(length=24), nullable=False, server_default="open"),
            sa.Column("reason", sa.Text(), nullable=False),
            sa.Column("snapshot", sa.JSON(), nullable=True),
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.ForeignKeyConstraint(["agent_id"], ["user_agents.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_agent_risk_events_agent_id", "agent_risk_events", ["agent_id"])
        op.create_index("ix_agent_risk_events_user_id", "agent_risk_events", ["user_id"])
        op.create_index("ix_agent_risk_events_event_type", "agent_risk_events", ["event_type"])
        op.create_index("ix_agent_risk_events_severity", "agent_risk_events", ["severity"])
        op.create_index("ix_agent_risk_events_status", "agent_risk_events", ["status"])


def downgrade() -> None:
    bind = op.get_bind()
    tables = _tables(bind)

    for table in ("agent_risk_events", "agent_status_logs", "agent_evaluation_logs"):
        if table in tables:
            op.drop_table(table)

    if "user_agents" in tables:
        for column in (
            "last_evaluation_snapshot",
            "last_evaluated_at",
            "risk_reason",
            "risk_locked_at",
            "observe_until",
            "observe_started_at",
        ):
            if column in _columns(bind, "user_agents"):
                op.drop_column("user_agents", column)
        op.execute(
            """
            ALTER TABLE user_agents
            MODIFY status ENUM('pending','active','suspended','rejected','terminated')
            NOT NULL DEFAULT 'pending'
            """
        )

    if "agent_tiers" in tables:
        for column in (
            "auto_downgrade_enabled",
            "freeze_days",
            "observe_days",
            "min_balance_for_subsidy_cents",
            "max_monthly_subsidy_cents",
            "max_refund_rate_bps",
            "monthly_active_customers_required",
            "monthly_sales_required",
        ):
            if column in _columns(bind, "agent_tiers"):
                op.drop_column("agent_tiers", column)
