"""Phase PB1 platform SaaS billing foundation

Revision ID: 20260524_phase_pb1_platform_billing
Revises: 20260523_phase_b3_billing_generation
Create Date: 2026-05-24
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "20260524_phase_pb1_platform_billing"
down_revision = "20260523_phase_b3_billing_generation"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "platform_plans",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("code", sa.String(length=80), nullable=False),
        sa.Column("name", sa.String(length=200), nullable=False),
        sa.Column("target_account_type", sa.String(length=30), nullable=False, server_default="AUDIT_FIRM"),
        sa.Column("billing_cycle", sa.String(length=20), nullable=False, server_default="Monthly"),
        sa.Column("base_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("gst_rate", sa.Numeric(5, 2), nullable=False, server_default="18.00"),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint("code", name="uq_platform_plans_code"),
    )
    op.create_index("ix_platform_plans_code", "platform_plans", ["code"])
    op.create_index("ix_platform_plans_target_account_type", "platform_plans", ["target_account_type"])
    op.create_index("ix_platform_plans_billing_cycle", "platform_plans", ["billing_cycle"])
    op.create_index("ix_platform_plans_is_active", "platform_plans", ["is_active"])

    op.create_table(
        "platform_plan_features",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("plan_id", sa.Integer(), nullable=False),
        sa.Column("feature_code", sa.String(length=100), nullable=False),
        sa.Column("feature_name", sa.String(length=200), nullable=False),
        sa.Column("limit_value", sa.String(length=100), nullable=True),
        sa.Column("is_enabled", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="1"),
        sa.ForeignKeyConstraint(["plan_id"], ["platform_plans.id"], ondelete="CASCADE"),
    )
    op.create_index("ix_platform_plan_features_plan_id", "platform_plan_features", ["plan_id"])
    op.create_index("ix_platform_plan_features_feature_code", "platform_plan_features", ["feature_code"])

    op.create_table(
        "platform_billing_accounts",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("account_type", sa.String(length=30), nullable=False),
        sa.Column("account_code", sa.String(length=80), nullable=False),
        sa.Column("display_name", sa.String(length=220), nullable=False),
        sa.Column("tenant_id", sa.Integer(), nullable=True),
        sa.Column("client_id", sa.Integer(), nullable=True),
        sa.Column("consultant_id", sa.Integer(), nullable=True),
        sa.Column("email", sa.String(length=255), nullable=True),
        sa.Column("mobile", sa.String(length=20), nullable=True),
        sa.Column("gstin", sa.String(length=20), nullable=True),
        sa.Column("pan", sa.String(length=20), nullable=True),
        sa.Column("billing_address", sa.Text(), nullable=True),
        sa.Column("state", sa.String(length=100), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="ACTIVE"),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_by_user_id", sa.Integer(), nullable=True),
        sa.Column("updated_by_user_id", sa.Integer(), nullable=True),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id"], ["tenants.id"], ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["client_id"], ["clients.id"], ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["consultant_id"], ["consultant_profiles.id"], ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"]),
        sa.ForeignKeyConstraint(["updated_by_user_id"], ["users.id"]),
        sa.UniqueConstraint("account_type", "account_code", name="uq_platform_billing_accounts_type_code"),
    )
    op.create_index("ix_platform_billing_accounts_account_type", "platform_billing_accounts", ["account_type"])
    op.create_index("ix_platform_billing_accounts_account_code", "platform_billing_accounts", ["account_code"])
    op.create_index("ix_platform_billing_accounts_display_name", "platform_billing_accounts", ["display_name"])
    op.create_index("ix_platform_billing_accounts_tenant_id", "platform_billing_accounts", ["tenant_id"])
    op.create_index("ix_platform_billing_accounts_client_id", "platform_billing_accounts", ["client_id"])
    op.create_index("ix_platform_billing_accounts_consultant_id", "platform_billing_accounts", ["consultant_id"])
    op.create_index("ix_platform_billing_accounts_status", "platform_billing_accounts", ["status"])

    op.create_table(
        "platform_subscriptions",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("account_id", sa.Integer(), nullable=False),
        sa.Column("plan_id", sa.Integer(), nullable=False),
        sa.Column("subscription_code", sa.String(length=100), nullable=False),
        sa.Column("start_date", sa.Date(), nullable=False),
        sa.Column("end_date", sa.Date(), nullable=True),
        sa.Column("billing_cycle", sa.String(length=20), nullable=False, server_default="Monthly"),
        sa.Column("amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("gst_rate", sa.Numeric(5, 2), nullable=False, server_default="18.00"),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="ACTIVE"),
        sa.Column("auto_generate_invoice", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_by_user_id", sa.Integer(), nullable=True),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["platform_billing_accounts.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["plan_id"], ["platform_plans.id"], ondelete="RESTRICT"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"]),
    )
    op.create_index("ix_platform_subscriptions_account_id", "platform_subscriptions", ["account_id"])
    op.create_index("ix_platform_subscriptions_plan_id", "platform_subscriptions", ["plan_id"])
    op.create_index("ix_platform_subscriptions_subscription_code", "platform_subscriptions", ["subscription_code"])
    op.create_index("ix_platform_subscriptions_billing_cycle", "platform_subscriptions", ["billing_cycle"])
    op.create_index("ix_platform_subscriptions_status", "platform_subscriptions", ["status"])

    op.create_table(
        "platform_invoices",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("account_id", sa.Integer(), nullable=False),
        sa.Column("subscription_id", sa.Integer(), nullable=True),
        sa.Column("invoice_no", sa.String(length=80), nullable=False),
        sa.Column("invoice_date", sa.Date(), nullable=False),
        sa.Column("due_date", sa.Date(), nullable=True),
        sa.Column("billing_period_from", sa.Date(), nullable=True),
        sa.Column("billing_period_to", sa.Date(), nullable=True),
        sa.Column("tax_type", sa.String(length=20), nullable=False, server_default="CGST_SGST"),
        sa.Column("subtotal", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("discount_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("taxable_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("cgst_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("sgst_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("igst_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("total_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="DRAFT"),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_by_user_id", sa.Integer(), nullable=True),
        sa.Column("posted_by_user_id", sa.Integer(), nullable=True),
        sa.Column("posted_at_utc", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["platform_billing_accounts.id"], ondelete="RESTRICT"),
        sa.ForeignKeyConstraint(["subscription_id"], ["platform_subscriptions.id"], ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"]),
        sa.ForeignKeyConstraint(["posted_by_user_id"], ["users.id"]),
        sa.UniqueConstraint("invoice_no", name="uq_platform_invoices_invoice_no"),
    )
    op.create_index("ix_platform_invoices_account_id", "platform_invoices", ["account_id"])
    op.create_index("ix_platform_invoices_subscription_id", "platform_invoices", ["subscription_id"])
    op.create_index("ix_platform_invoices_invoice_no", "platform_invoices", ["invoice_no"])
    op.create_index("ix_platform_invoices_invoice_date", "platform_invoices", ["invoice_date"])
    op.create_index("ix_platform_invoices_due_date", "platform_invoices", ["due_date"])
    op.create_index("ix_platform_invoices_status", "platform_invoices", ["status"])

    op.create_table(
        "platform_invoice_lines",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("invoice_id", sa.Integer(), nullable=False),
        sa.Column("charge_type", sa.String(length=40), nullable=False, server_default="SUBSCRIPTION"),
        sa.Column("description", sa.String(length=500), nullable=False),
        sa.Column("reference_type", sa.String(length=60), nullable=True),
        sa.Column("reference_id", sa.Integer(), nullable=True),
        sa.Column("quantity", sa.Numeric(12, 2), nullable=False, server_default="1.00"),
        sa.Column("rate", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("discount_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("taxable_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("gst_rate", sa.Numeric(5, 2), nullable=False, server_default="18.00"),
        sa.Column("cgst_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("sgst_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("igst_amount", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("line_total", sa.Numeric(14, 2), nullable=False, server_default="0.00"),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="1"),
        sa.ForeignKeyConstraint(["invoice_id"], ["platform_invoices.id"], ondelete="CASCADE"),
    )
    op.create_index("ix_platform_invoice_lines_invoice_id", "platform_invoice_lines", ["invoice_id"])
    op.create_index("ix_platform_invoice_lines_charge_type", "platform_invoice_lines", ["charge_type"])

    op.create_table(
        "platform_payments",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("invoice_id", sa.Integer(), nullable=False),
        sa.Column("account_id", sa.Integer(), nullable=False),
        sa.Column("payment_date", sa.Date(), nullable=False),
        sa.Column("amount", sa.Numeric(14, 2), nullable=False),
        sa.Column("mode", sa.String(length=30), nullable=False, server_default="Bank"),
        sa.Column("reference_no", sa.String(length=100), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_by_user_id", sa.Integer(), nullable=True),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["invoice_id"], ["platform_invoices.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["account_id"], ["platform_billing_accounts.id"], ondelete="RESTRICT"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"]),
    )
    op.create_index("ix_platform_payments_invoice_id", "platform_payments", ["invoice_id"])
    op.create_index("ix_platform_payments_account_id", "platform_payments", ["account_id"])
    op.create_index("ix_platform_payments_payment_date", "platform_payments", ["payment_date"])


def downgrade() -> None:
    op.drop_index("ix_platform_payments_payment_date", table_name="platform_payments")
    op.drop_index("ix_platform_payments_account_id", table_name="platform_payments")
    op.drop_index("ix_platform_payments_invoice_id", table_name="platform_payments")
    op.drop_table("platform_payments")
    op.drop_index("ix_platform_invoice_lines_charge_type", table_name="platform_invoice_lines")
    op.drop_index("ix_platform_invoice_lines_invoice_id", table_name="platform_invoice_lines")
    op.drop_table("platform_invoice_lines")
    op.drop_index("ix_platform_invoices_status", table_name="platform_invoices")
    op.drop_index("ix_platform_invoices_due_date", table_name="platform_invoices")
    op.drop_index("ix_platform_invoices_invoice_date", table_name="platform_invoices")
    op.drop_index("ix_platform_invoices_invoice_no", table_name="platform_invoices")
    op.drop_index("ix_platform_invoices_subscription_id", table_name="platform_invoices")
    op.drop_index("ix_platform_invoices_account_id", table_name="platform_invoices")
    op.drop_table("platform_invoices")
    op.drop_index("ix_platform_subscriptions_status", table_name="platform_subscriptions")
    op.drop_index("ix_platform_subscriptions_billing_cycle", table_name="platform_subscriptions")
    op.drop_index("ix_platform_subscriptions_subscription_code", table_name="platform_subscriptions")
    op.drop_index("ix_platform_subscriptions_plan_id", table_name="platform_subscriptions")
    op.drop_index("ix_platform_subscriptions_account_id", table_name="platform_subscriptions")
    op.drop_table("platform_subscriptions")
    op.drop_index("ix_platform_billing_accounts_status", table_name="platform_billing_accounts")
    op.drop_index("ix_platform_billing_accounts_consultant_id", table_name="platform_billing_accounts")
    op.drop_index("ix_platform_billing_accounts_client_id", table_name="platform_billing_accounts")
    op.drop_index("ix_platform_billing_accounts_tenant_id", table_name="platform_billing_accounts")
    op.drop_index("ix_platform_billing_accounts_display_name", table_name="platform_billing_accounts")
    op.drop_index("ix_platform_billing_accounts_account_code", table_name="platform_billing_accounts")
    op.drop_index("ix_platform_billing_accounts_account_type", table_name="platform_billing_accounts")
    op.drop_table("platform_billing_accounts")
    op.drop_index("ix_platform_plan_features_feature_code", table_name="platform_plan_features")
    op.drop_index("ix_platform_plan_features_plan_id", table_name="platform_plan_features")
    op.drop_table("platform_plan_features")
    op.drop_index("ix_platform_plans_is_active", table_name="platform_plans")
    op.drop_index("ix_platform_plans_billing_cycle", table_name="platform_plans")
    op.drop_index("ix_platform_plans_target_account_type", table_name="platform_plans")
    op.drop_index("ix_platform_plans_code", table_name="platform_plans")
    op.drop_table("platform_plans")
