"""create_pricing_control_tables

Creates pricing_templates, pricing_template_rates, merchant_pricing_assignments,
billing_records, and merchant_billing_methods tables for PRD-HWADM-002.

Revision ID: pricing001_core_tables
Revises: txn002_add_deleted_at
Create Date: 2026-04-29
"""
from alembic import op
import sqlalchemy as sa

revision = "pricing001_core_tables"
down_revision = "txn002_add_deleted_at"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # pricing_templates
    op.create_table(
        "pricing_templates",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("name", sa.String(255), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("pricing_type", sa.String(30), nullable=False),
        sa.Column("billing_cycle", sa.String(20), nullable=False),
        sa.Column("monthly_fee_cents", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("setup_fee_cents", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default="true"),
        sa.Column("parent_template_id", sa.Integer(), nullable=True),
        sa.Column("created_by_user_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), nullable=True),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(["parent_template_id"], ["pricing_templates.id"], name="fk_pricing_templates_parent"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"], name="fk_pricing_templates_created_by"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_pricing_templates_is_active", "pricing_templates", ["is_active"])

    # pricing_template_rates
    op.create_table(
        "pricing_template_rates",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("template_id", sa.Integer(), nullable=False),
        sa.Column("transaction_type", sa.String(30), nullable=False),
        sa.Column("rate_percentage", sa.Float(), nullable=True),
        sa.Column("fixed_fee_cents", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("tier_name", sa.String(100), nullable=True),
        sa.Column("interchange_basis_points", sa.Integer(), nullable=True),
        sa.Column("markup_basis_points", 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), nullable=True),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(["template_id"], ["pricing_templates.id"], name="fk_pricing_template_rates_template"),
        sa.UniqueConstraint("template_id", "transaction_type", name="uq_pricing_rate_template_type"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_pricing_template_rates_template_id", "pricing_template_rates", ["template_id"])

    # merchant_pricing_assignments
    op.create_table(
        "merchant_pricing_assignments",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("merchant_id", sa.Integer(), nullable=False),
        sa.Column("template_id", sa.Integer(), nullable=False),
        sa.Column("effective_date", sa.DateTime(timezone=True), nullable=False),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default="true"),
        sa.Column("assigned_by_user_id", sa.Integer(), nullable=True),
        sa.Column("notes", 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), nullable=True),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(["merchant_id"], ["merchants.id"], name="fk_merchant_pricing_assignments_merchant"),
        sa.ForeignKeyConstraint(["template_id"], ["pricing_templates.id"], name="fk_merchant_pricing_assignments_template"),
        sa.ForeignKeyConstraint(["assigned_by_user_id"], ["users.id"], name="fk_merchant_pricing_assignments_assigned_by"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_merchant_pricing_assignments_merchant_id", "merchant_pricing_assignments", ["merchant_id"])
    op.create_index("ix_merchant_pricing_assignments_template_id", "merchant_pricing_assignments", ["template_id"])
    op.create_index("ix_merchant_pricing_assignments_merchant_active", "merchant_pricing_assignments", ["merchant_id", "is_active"])

    # billing_records
    op.create_table(
        "billing_records",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("merchant_id", sa.Integer(), nullable=False),
        sa.Column("template_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("total_volume_cents", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("total_platform_fee_cents", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("monthly_fee_cents", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("total_due_cents", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("status", sa.String(20), nullable=False, server_default="pending"),
        sa.Column("charge_txn_id", sa.String(255), nullable=True),
        sa.Column("retry_count", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("last_error", 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), nullable=True),
        sa.ForeignKeyConstraint(["merchant_id"], ["merchants.id"], name="fk_billing_records_merchant"),
        sa.ForeignKeyConstraint(["template_id"], ["pricing_templates.id"], name="fk_billing_records_template"),
        sa.UniqueConstraint("merchant_id", "period_start", name="uq_billing_record_merchant_period"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_billing_records_merchant_id", "billing_records", ["merchant_id"])

    # merchant_billing_methods
    op.create_table(
        "merchant_billing_methods",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("merchant_id", sa.Integer(), nullable=False),
        sa.Column("tsep_token", sa.Text(), nullable=True),
        sa.Column("card_type", sa.String(50), nullable=True),
        sa.Column("masked_card", sa.String(20), nullable=True),
        sa.Column("billing_name", sa.String(255), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default="true"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(["merchant_id"], ["merchants.id"], name="fk_merchant_billing_methods_merchant"),
        sa.UniqueConstraint("merchant_id", name="uq_merchant_billing_methods_merchant"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_merchant_billing_methods_merchant_id", "merchant_billing_methods", ["merchant_id"])


def downgrade() -> None:
    op.drop_table("merchant_billing_methods")
    op.drop_table("billing_records")
    op.drop_table("merchant_pricing_assignments")
    op.drop_table("pricing_template_rates")
    op.drop_table("pricing_templates")