"""Invoices table: add purchaser info, Stripe, and PDF columns + indexes

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

Changes:
  1. ALTER invoices — add purchaser_name, purchaser_email,
     stripe_payment_link, stripe_payment_intent_id, pdf_s3_key.
  2. Add indexes: ix_invoices_status, ix_invoices_due_date,
     ix_invoices_purchaser_email.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy import inspect

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


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


def upgrade() -> None:

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

    op.add_column(
        "invoices",
        sa.Column("purchaser_name", sa.String(255), nullable=True),
    )
    op.add_column(
        "invoices",
        sa.Column("purchaser_email", sa.String(255), nullable=True),
    )
    op.add_column(
        "invoices",
        sa.Column("stripe_payment_link", sa.Text, nullable=True),
    )
    op.add_column(
        "invoices",
        sa.Column("stripe_payment_intent_id", sa.String(255), nullable=True),
    )
    op.add_column(
        "invoices",
        sa.Column("pdf_s3_key", sa.Text, nullable=True),
    )

    # ── 2. Indexes ────────────────────────────────────────────────────────────

    # Get existing indexes to avoid duplicate creation errors
    bind = op.get_bind()
    inspector = inspect(bind)
    existing_indexes = {idx["name"] for idx in inspector.get_indexes("invoices")}

    # Create indexes only if they don't exist
    if "ix_invoices_status" not in existing_indexes:
        op.create_index("ix_invoices_status", "invoices", ["status"])
    
    if "ix_invoices_due_date" not in existing_indexes:
        op.create_index("ix_invoices_due_date", "invoices", ["due_date"])
    
    if "ix_invoices_purchaser_email" not in existing_indexes:
        op.create_index("ix_invoices_purchaser_email", "invoices", ["purchaser_email"])


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


def downgrade() -> None:

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

    # Get existing indexes to avoid errors when dropping
    bind = op.get_bind()
    inspector = inspect(bind)
    existing_indexes = {idx["name"] for idx in inspector.get_indexes("invoices")}

    # Drop indexes only if they exist
    if "ix_invoices_purchaser_email" in existing_indexes:
        op.drop_index("ix_invoices_purchaser_email", table_name="invoices")
    
    if "ix_invoices_due_date" in existing_indexes:
        op.drop_index("ix_invoices_due_date", table_name="invoices")
    
    if "ix_invoices_status" in existing_indexes:
        op.drop_index("ix_invoices_status", table_name="invoices")

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

    op.drop_column("invoices", "pdf_s3_key")
    op.drop_column("invoices", "stripe_payment_intent_id")
    op.drop_column("invoices", "stripe_payment_link")
    op.drop_column("invoices", "purchaser_email")
    op.drop_column("invoices", "purchaser_name")