"""prd010: add dunning/retry fields to invoices table.

Adds retry_count, max_retries, next_retry_at, last_retry_at, and
retry_exhausted_at to support the Payment Scheduler's automated
dunning (retry-on-failure) workflow for overdue invoices.

Revision ID: prd010_invoice_dunning_fields
Revises: global001_provider_global_creds
Create Date: 2026-04-22
"""
from typing import Union, Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.engine.reflection import Inspector


revision: str = "prd010_invoice_dunning_fields"
down_revision: Union[str, Sequence[str], None] = "global001_provider_global_creds"
branch_labels = None
depends_on = None


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

    # Guard: only add columns if they do not already exist (makes re-runs safe).
    existing_columns = {col["name"] for col in inspector.get_columns("invoices")}

    if "retry_count" not in existing_columns:
        op.add_column(
            "invoices",
            sa.Column(
                "retry_count",
                sa.Integer(),
                nullable=False,
                server_default="0",
            ),
        )

    if "max_retries" not in existing_columns:
        op.add_column(
            "invoices",
            sa.Column(
                "max_retries",
                sa.Integer(),
                nullable=False,
                server_default="3",
            ),
        )

    if "next_retry_at" not in existing_columns:
        op.add_column(
            "invoices",
            sa.Column("next_retry_at", sa.DateTime(), nullable=True),
        )

    if "last_retry_at" not in existing_columns:
        op.add_column(
            "invoices",
            sa.Column("last_retry_at", sa.DateTime(), nullable=True),
        )

    if "retry_exhausted_at" not in existing_columns:
        op.add_column(
            "invoices",
            sa.Column("retry_exhausted_at", sa.DateTime(), nullable=True),
        )

    # Partial index: only rows where next_retry_at is not NULL matter for the
    # scheduler's queue scan, so we keep the index tight.
    existing_indexes = {idx["name"] for idx in inspector.get_indexes("invoices")}
    if "ix_invoices_next_retry_at" not in existing_indexes:
        op.create_index(
            "ix_invoices_next_retry_at",
            "invoices",
            ["next_retry_at"],
            postgresql_where=sa.text("next_retry_at IS NOT NULL"),
        )


def downgrade() -> None:
    op.drop_index("ix_invoices_next_retry_at", table_name="invoices")
    op.drop_column("invoices", "retry_exhausted_at")
    op.drop_column("invoices", "last_retry_at")
    op.drop_column("invoices", "next_retry_at")
    op.drop_column("invoices", "max_retries")
    op.drop_column("invoices", "retry_count")