"""hwadm001: Add admin_roles, admin_user_roles, admin_invites, agent_merchant_assignments tables

Revision ID: hwadm001
Revises: 9bbc7fe948a9
Create Date: 2026-04-29 00:00:00.000000

"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

# revision identifiers
revision = "hwadm001"
down_revision = "9bbc7fe948a9"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── admin_roles ────────────────────────────────────────────────────────
    op.create_table(
        "admin_roles",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("label", sa.String(100), nullable=False),
        sa.Column("slug", sa.String(50), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("role_rank", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("is_system", sa.Boolean(), nullable=False, server_default="true"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("slug"),
    )
    op.create_index("ix_admin_roles_slug", "admin_roles", ["slug"])

    # ── admin_user_roles ───────────────────────────────────────────────────
    op.create_table(
        "admin_user_roles",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("user_id", sa.Integer(), nullable=False),
        sa.Column("admin_role_id", sa.Integer(), nullable=False),
        sa.Column("assigned_by_id", sa.Integer(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["admin_role_id"], ["admin_roles.id"]),
        sa.ForeignKeyConstraint(["assigned_by_id"], ["users.id"]),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("user_id"),
    )
    op.create_index("ix_admin_user_roles_user_id", "admin_user_roles", ["user_id"])

    # ── admin_invites ──────────────────────────────────────────────────────
    op.create_table(
        "admin_invites",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("email", sa.String(255), nullable=False),
        sa.Column("admin_role_id", sa.Integer(), nullable=False),
        sa.Column("invited_user_id", sa.Integer(), nullable=False),
        sa.Column("invited_by_id", sa.Integer(), nullable=False),
        sa.Column("token_hash", sa.String(64), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("accepted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("cancelled_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.ForeignKeyConstraint(["admin_role_id"], ["admin_roles.id"]),
        sa.ForeignKeyConstraint(["invited_user_id"], ["users.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["invited_by_id"], ["users.id"]),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("token_hash"),
    )
    op.create_index("ix_admin_invites_email", "admin_invites", ["email"])
    op.create_index("ix_admin_invites_invited_user_id", "admin_invites", ["invited_user_id"])
    op.create_index("ix_admin_invites_token_hash", "admin_invites", ["token_hash"])

    # ── agent_merchant_assignments ─────────────────────────────────────────
    op.create_table(
        "agent_merchant_assignments",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("agent_user_id", sa.Integer(), nullable=False),
        sa.Column("merchant_id", sa.Integer(), nullable=False),
        sa.Column("assigned_by_id", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(["agent_user_id"], ["users.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["merchant_id"], ["merchants.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["assigned_by_id"], ["users.id"]),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("agent_user_id", "merchant_id", name="uq_agent_merchant"),
    )
    op.create_index("ix_agent_merchant_assignments_agent_user_id", "agent_merchant_assignments", ["agent_user_id"])
    op.create_index("ix_agent_merchant_assignments_merchant_id", "agent_merchant_assignments", ["merchant_id"])

    # ── Seed admin_roles ───────────────────────────────────────────────────
    op.execute("""
        INSERT INTO admin_roles (label, slug, description, role_rank, is_system, created_at)
        VALUES
          ('Admin',            'admin',            'Full platform access.',                    4, true, NOW()),
          ('Staff',            'staff',            'Read access to merchants and reports.',    2, true, NOW()),
          ('Customer Support', 'customer_support', 'View merchants, transactions, invoices.',  1, true, NOW()),
          ('Agent',            'agent',            'Access limited to assigned merchants.',    1, true, NOW())
        ON CONFLICT (slug) DO NOTHING
    """)

    # ── Backfill existing superusers to Admin role ─────────────────────────
    op.execute("""
        INSERT INTO admin_user_roles (user_id, admin_role_id, created_at)
        SELECT u.id, ar.id, NOW()
        FROM users u
        CROSS JOIN admin_roles ar
        WHERE u.is_superuser = TRUE
          AND u.deleted_at IS NULL
          AND ar.slug = 'admin'
          AND NOT EXISTS (
              SELECT 1 FROM admin_user_roles aur WHERE aur.user_id = u.id
          )
    """)


def downgrade() -> None:
    op.drop_table("agent_merchant_assignments")
    op.drop_table("admin_invites")
    op.drop_table("admin_user_roles")
    op.drop_table("admin_roles")