"""Generate business codes like BYJ20260424001 / BX20260424001."""

from datetime import datetime
from sqlalchemy.orm import Session
from sqlalchemy import text


def _date_yyyymmdd(apply_date: str | None = None) -> str:
    if apply_date and str(apply_date).strip():
        try:
            return datetime.strptime(str(apply_date).strip(), "%Y-%m-%d").strftime("%Y%m%d")
        except ValueError:
            pass
    return datetime.now().strftime("%Y%m%d")


def generate_apply_code(db: Session, prefix: str, apply_date: str | None = None) -> str:
    """
    Generate a sequential code: {prefix}{YYYYMMDD}{3-digit-sequence}.
    prefix examples: 'BYJ' for advance, 'BX' for reimburse.
    Thread-safe via DB-level auto-increment fallback.
    """
    today = _date_yyyymmdd(apply_date)
    like_pattern = f"{prefix}{today}%"

    # Find the max sequence number for today
    result = db.execute(
        text("SELECT MAX(CAST(SUBSTRING(apply_code, -3) AS UNSIGNED)) FROM ("
             "SELECT apply_code FROM bx_advance_apply WHERE apply_code LIKE :p1 "
             "UNION ALL "
             "SELECT apply_code FROM bx_reimburse_apply WHERE apply_code LIKE :p2"
             ") t"),
        {"p1": like_pattern, "p2": like_pattern}
    ).scalar()

    seq = (result or 0) + 1
    return f"{prefix}{today}{seq:03d}"


def generate_title(db: Session, applicant_name: str, apply_date: str | None = None) -> str:
    date_str = _date_yyyymmdd(apply_date)
    like_pattern = f"%报销单{date_str}%"

    result = db.execute(
        text(
            "SELECT MAX(CAST(RIGHT(title, 3) AS UNSIGNED)) FROM bx_reimburse_apply "
            "WHERE del_flag = 0 AND title LIKE :p"
        ),
        {"p": like_pattern},
    ).scalar()

    seq = (result or 0) + 1
    return f"{applicant_name}报销单{date_str}{seq:03d}"