"""Human-readable reference numbers: APP-202608-0001, CUS-…, CON-…, TKT-….

Sequences are derived from the existing rows rather than a Postgres SEQUENCE so that
numbers stay gap-free per month and survive a database restore without a separate
setval step. Concurrency is handled with a transaction-scoped advisory lock keyed on
the prefix, so two simultaneous creates cannot claim the same suffix.
"""
from sqlalchemy import text
from sqlalchemy.orm import Session

from app.core.time import today_local

#: kind -> (table, column). Values are module constants, never user input, so
#: interpolating them into the SQL below is safe.
_SERIES: dict[str, tuple[str, str]] = {
    "APP": ("connection_applications", "application_no"),
    "CUS": ("customers", "customer_code"),
    "CON": ("connections", "connection_code"),
    "TKT": ("complaints", "ticket_no"),
    "REQ": ("requisitions", "req_no"),
}


def next_number(db: Session, kind: str) -> str:
    """Allocate the next reference number for `kind` within the current Dhaka month."""
    try:
        table, column = _SERIES[kind]
    except KeyError:
        raise ValueError(f"Unknown numbering series {kind!r}") from None

    prefix = f"{kind}-{today_local():%Y%m}-"

    # Serialise concurrent allocations for this prefix until the transaction ends.
    db.execute(text("SELECT pg_advisory_xact_lock(hashtext(:key))"), {"key": prefix})

    # Soft-deleted rows are included deliberately: reusing a retired number would make
    # the audit trail ambiguous.
    highest = db.scalar(
        text(
            f"SELECT max(substring({column} FROM '[0-9]+$')::int) "  # noqa: S608 - constants
            f"FROM {table} WHERE {column} LIKE :prefix"
        ),
        {"prefix": f"{prefix}%"},
    )
    return f"{prefix}{(highest or 0) + 1:04d}"