"""coupon claim method, threshold fixed, agent balance and mall tables

Revision ID: 054_coupon_balance
Revises: 053_promo_code_point_refunded
Create Date: 2026-06-20
"""

import sqlalchemy as sa
from alembic import op


revision = "054_coupon_balance"
down_revision = "053_promo_code_point_refunded"
branch_labels = None
depends_on = None


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


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

    # 1. promo_codes: add claim_method
    promo_columns = _columns(bind, "promo_codes")
    if "claim_method" not in promo_columns:
        op.add_column(
            "promo_codes",
            sa.Column("claim_method", sa.String(length=16), nullable=False, server_default="code"),
        )

    # 2. user_agents: add agent_balance / agent_frozen_balance
    agent_columns = _columns(bind, "user_agents")
    if "agent_balance" not in agent_columns:
        op.add_column(
            "user_agents",
            sa.Column("agent_balance", sa.Integer(), nullable=False, server_default="0"),
        )
    if "agent_frozen_balance" not in agent_columns:
        op.add_column(
            "user_agents",
            sa.Column("agent_frozen_balance", sa.Integer(), nullable=False, server_default="0"),
        )

    # 3. agent_balance_logs
    op.create_table(
        "agent_balance_logs",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("user_id", sa.BigInteger(), nullable=False),
        sa.Column("change_type", sa.String(length=32), nullable=False),
        sa.Column("delta", sa.Integer(), nullable=False),
        sa.Column("balance_after", sa.Integer(), nullable=False),
        sa.Column("frozen_balance_after", sa.Integer(), nullable=False),
        sa.Column("order_id", sa.Integer(), nullable=True),
        sa.Column("ref_no", sa.String(length=64), nullable=True),
        sa.Column("remark", sa.Text(), nullable=True),
        sa.Column("operator_id", sa.BigInteger(), 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.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_agent_balance_logs_change_type"), "agent_balance_logs", ["change_type"], unique=False)
    op.create_index(op.f("ix_agent_balance_logs_order_id"), "agent_balance_logs", ["order_id"], unique=False)
    op.create_index(op.f("ix_agent_balance_logs_ref_no"), "agent_balance_logs", ["ref_no"], unique=False)
    op.create_index(op.f("ix_agent_balance_logs_user_id"), "agent_balance_logs", ["user_id"], unique=False)

    # 4. coupon_mall_items
    op.create_table(
        "coupon_mall_items",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("promo_code_id", sa.Integer(), nullable=False),
        sa.Column("issuer_user_id", sa.BigInteger(), nullable=False),
        sa.Column("points_price", sa.Integer(), nullable=False),
        sa.Column("stock_total", sa.Integer(), nullable=False),
        sa.Column("stock_remaining", sa.Integer(), nullable=False),
        sa.Column("status", sa.String(length=16), nullable=False),
        sa.Column("description", sa.Text(), 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.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_coupon_mall_items_issuer_user_id"), "coupon_mall_items", ["issuer_user_id"], unique=False)
    op.create_index(op.f("ix_coupon_mall_items_promo_code_id"), "coupon_mall_items", ["promo_code_id"], unique=False)
    op.create_index(op.f("ix_coupon_mall_items_status"), "coupon_mall_items", ["status"], unique=False)

    # 5. coupon_mall_claims
    op.create_table(
        "coupon_mall_claims",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("mall_item_id", sa.Integer(), nullable=False),
        sa.Column("promo_code_id", sa.Integer(), nullable=False),
        sa.Column("user_id", sa.BigInteger(), nullable=False),
        sa.Column("points_paid", sa.Integer(), nullable=False),
        sa.Column("code", sa.String(length=64), nullable=True),
        sa.Column("status", sa.String(length=16), nullable=False),
        sa.Column("used_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("order_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.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_coupon_mall_claims_code"), "coupon_mall_claims", ["code"], unique=False)
    op.create_index(op.f("ix_coupon_mall_claims_mall_item_id"), "coupon_mall_claims", ["mall_item_id"], unique=False)
    op.create_index(op.f("ix_coupon_mall_claims_order_id"), "coupon_mall_claims", ["order_id"], unique=False)
    op.create_index(op.f("ix_coupon_mall_claims_promo_code_id"), "coupon_mall_claims", ["promo_code_id"], unique=False)
    op.create_index(op.f("ix_coupon_mall_claims_status"), "coupon_mall_claims", ["status"], unique=False)
    op.create_index(op.f("ix_coupon_mall_claims_user_id"), "coupon_mall_claims", ["user_id"], unique=False)


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

    op.drop_index(op.f("ix_coupon_mall_claims_user_id"), table_name="coupon_mall_claims")
    op.drop_index(op.f("ix_coupon_mall_claims_status"), table_name="coupon_mall_claims")
    op.drop_index(op.f("ix_coupon_mall_claims_promo_code_id"), table_name="coupon_mall_claims")
    op.drop_index(op.f("ix_coupon_mall_claims_order_id"), table_name="coupon_mall_claims")
    op.drop_index(op.f("ix_coupon_mall_claims_mall_item_id"), table_name="coupon_mall_claims")
    op.drop_index(op.f("ix_coupon_mall_claims_code"), table_name="coupon_mall_claims")
    op.drop_table("coupon_mall_claims")

    op.drop_index(op.f("ix_coupon_mall_items_status"), table_name="coupon_mall_items")
    op.drop_index(op.f("ix_coupon_mall_items_promo_code_id"), table_name="coupon_mall_items")
    op.drop_index(op.f("ix_coupon_mall_items_issuer_user_id"), table_name="coupon_mall_items")
    op.drop_table("coupon_mall_items")

    op.drop_index(op.f("ix_agent_balance_logs_user_id"), table_name="agent_balance_logs")
    op.drop_index(op.f("ix_agent_balance_logs_ref_no"), table_name="agent_balance_logs")
    op.drop_index(op.f("ix_agent_balance_logs_order_id"), table_name="agent_balance_logs")
    op.drop_index(op.f("ix_agent_balance_logs_change_type"), table_name="agent_balance_logs")
    op.drop_table("agent_balance_logs")

    agent_columns = _columns(bind, "user_agents")
    if "agent_frozen_balance" in agent_columns:
        op.drop_column("user_agents", "agent_frozen_balance")
    if "agent_balance" in agent_columns:
        op.drop_column("user_agents", "agent_balance")

    promo_columns = _columns(bind, "promo_codes")
    if "claim_method" in promo_columns:
        op.drop_column("promo_codes", "claim_method")
