"""Contracts table: add 12 new columns and missing indexes

Revision ID: 0010
Revises: 0009
Create Date: 2026-06-25 00:00:00.000000

Changes:
  1. ALTER contracts — add contract_type, total_amount, deleted_at,
     section_id (FK), created_by (FK), purchaser_name, purchaser_email,
     purchaser_phone, purchaser_address, payment_plan_type, deposit_amount,
     payment_schedule (JSONB).
  2. Add FK constraints for section_id → sections.id and created_by → users.id.
  3. Add indexes: ix_contracts_deleted_at, ix_contracts_section_id,
     ix_contracts_created_by, ix_contracts_status (was missing).
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "0010"
down_revision: Union[str, None] = "0009"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


# ---------------------------------------------------------------------------
# upgrade
# ---------------------------------------------------------------------------


def upgrade() -> None:

    # ── 1. Add new scalar columns ────────────────────────────────────────────

    op.add_column(
        "contracts",
        sa.Column("contract_type", sa.String(50), nullable=True),
    )
    op.add_column(
        "contracts",
        sa.Column(
            "total_amount",
            sa.Numeric(12, 2),
            nullable=False,
            server_default=sa.text("0"),
        ),
    )
    op.add_column(
        "contracts",
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
    )
    op.add_column(
        "contracts",
        sa.Column(
            "section_id",
            postgresql.UUID(as_uuid=True),
            nullable=True,
        ),
    )
    op.add_column(
        "contracts",
        sa.Column(
            "created_by",
            postgresql.UUID(as_uuid=True),
            nullable=True,
        ),
    )
    op.add_column(
        "contracts",
        sa.Column("purchaser_name", sa.String(255), nullable=True),
    )
    op.add_column(
        "contracts",
        sa.Column("purchaser_email", sa.String(255), nullable=True),
    )
    op.add_column(
        "contracts",
        sa.Column("purchaser_phone", sa.String(50), nullable=True),
    )
    op.add_column(
        "contracts",
        sa.Column("purchaser_address", sa.Text, nullable=True),
    )
    op.add_column(
        "contracts",
        sa.Column(
            "payment_plan_type",
            sa.String(50),
            nullable=True,
            server_default=sa.text("'full'"),
        ),
    )
    op.add_column(
        "contracts",
        sa.Column("deposit_amount", sa.Numeric(10, 2), nullable=True),
    )
    op.add_column(
        "contracts",
        sa.Column("payment_schedule", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    )

    # ── 2. FK constraints (Alembic best-practice: separate from add_column) ──

    op.create_foreign_key(
        "fk_contracts_section_id",
        "contracts",
        "sections",
        ["section_id"],
        ["id"],
        ondelete="SET NULL",
    )
    op.create_foreign_key(
        "fk_contracts_created_by",
        "contracts",
        "users",
        ["created_by"],
        ["id"],
        ondelete="SET NULL",
    )

    # ── 3. Indexes ────────────────────────────────────────────────────────────

    op.create_index("ix_contracts_deleted_at", "contracts", ["deleted_at"])
    op.create_index("ix_contracts_section_id", "contracts", ["section_id"])
    op.create_index("ix_contracts_created_by", "contracts", ["created_by"])
    op.create_index("ix_contracts_status", "contracts", ["status"])


# ---------------------------------------------------------------------------
# downgrade
# ---------------------------------------------------------------------------


def downgrade() -> None:

    # ── Drop indexes ──────────────────────────────────────────────────────────

    op.drop_index("ix_contracts_status", table_name="contracts")
    op.drop_index("ix_contracts_created_by", table_name="contracts")
    op.drop_index("ix_contracts_section_id", table_name="contracts")
    op.drop_index("ix_contracts_deleted_at", table_name="contracts")

    # ── Drop FK constraints ───────────────────────────────────────────────────

    op.drop_constraint("fk_contracts_created_by", "contracts", type_="foreignkey")
    op.drop_constraint("fk_contracts_section_id", "contracts", type_="foreignkey")

    # ── Drop columns (reverse order of addition) ──────────────────────────────

    op.drop_column("contracts", "payment_schedule")
    op.drop_column("contracts", "deposit_amount")
    op.drop_column("contracts", "payment_plan_type")
    op.drop_column("contracts", "purchaser_address")
    op.drop_column("contracts", "purchaser_phone")
    op.drop_column("contracts", "purchaser_email")
    op.drop_column("contracts", "purchaser_name")
    op.drop_column("contracts", "created_by")
    op.drop_column("contracts", "section_id")
    op.drop_column("contracts", "deleted_at")
    op.drop_column("contracts", "total_amount")
    op.drop_column("contracts", "contract_type")