"""Book-close orchestration service — DB-backed wrapper over the pure engine.

Loads the persisted calendar config / holidays / makeup days, computes the
close window for a period via :mod:`app.domains.finance.close_calendar`, and
owns the close-run lifecycle (anchors, manual override, open → in_progress →
soft_closed → closed, reopen) plus the calendar-config CRUD and the
"restore PingCAP/CN defaults" action.

All mutations are idempotent where it matters and emit no I/O beyond the DB;
HTTP concerns (auth, audit, envelope) stay in :mod:`close_router`.
"""
from __future__ import annotations

import calendar as _calendar
from datetime import UTC, date, datetime

from sqlalchemy import delete, select
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.asyncio import AsyncSession

from app.core.exceptions import NotFoundError, ValidationError
from app.domains.finance import close_calendar as engine
from app.domains.finance.close_calendar_defaults import (
    DEFAULT_HOLIDAY_COUNTRY,
    DEFAULT_HOLIDAYS,
    DEFAULT_MAKEUP_WORKDAYS,
    DEFAULT_TARGET_WORKDAY,
    DEFAULT_WORK_WEEKEND_DAY,
)
from app.domains.finance.close_models import (
    RUN_CLOSED,
    RUN_FROZEN_STATUSES,
    RUN_IN_PROGRESS,
    RUN_OPEN,
    RUN_SOFT_CLOSED,
    RUN_STATUSES,
    SOURCE_DEFAULT,
    SOURCE_MANUAL,
    WORK_WEEKEND_DAYS,
    FinanceCloseCalendarConfig,
    FinanceCloseHoliday,
    FinanceCloseMakeupDay,
    FinanceCloseRun,
)

_CONFIG_ID = 1
_MONTH_NAMES = list(_calendar.month_name)  # ['', 'January', ...]

# Valid forward lifecycle transitions (reopen handled separately).
_FORWARD = {
    RUN_OPEN: {RUN_IN_PROGRESS, RUN_SOFT_CLOSED, RUN_CLOSED},
    RUN_IN_PROGRESS: {RUN_OPEN, RUN_SOFT_CLOSED, RUN_CLOSED},
    RUN_SOFT_CLOSED: {RUN_CLOSED, RUN_IN_PROGRESS, RUN_OPEN},
    RUN_CLOSED: {RUN_SOFT_CLOSED, RUN_IN_PROGRESS, RUN_OPEN},
}


def _now() -> datetime:
    return datetime.now(UTC)


def validate_period(period: str) -> str:
    """Validate + normalise a ``YYYY-MM`` period, raising on bad input."""
    try:
        engine.parse_period(period)
    except ValueError as exc:
        raise ValidationError(str(exc)) from exc
    return period


def period_label(period: str) -> str:
    """``"2026-06"`` → ``"June 2026"``."""
    year, month = engine.parse_period(period)
    return f"{_MONTH_NAMES[month]} {year}"


# ── calendar config / holidays / makeup loading ──

async def load_config(db: AsyncSession) -> FinanceCloseCalendarConfig:
    """Return persisted config or an in-memory default without writing."""
    row = await db.get(FinanceCloseCalendarConfig, _CONFIG_ID)
    if row is None:
        row = FinanceCloseCalendarConfig(
            id=_CONFIG_ID,
            target_workday=DEFAULT_TARGET_WORKDAY,
            work_weekend_day=DEFAULT_WORK_WEEKEND_DAY,
        )
    return row


async def ensure_config(db: AsyncSession) -> FinanceCloseCalendarConfig:
    """Materialise the singleton config for an explicit mutation path."""
    row = await db.get(FinanceCloseCalendarConfig, _CONFIG_ID)
    if row is not None:
        return row
    row = FinanceCloseCalendarConfig(
        id=_CONFIG_ID,
        target_workday=DEFAULT_TARGET_WORKDAY,
        work_weekend_day=DEFAULT_WORK_WEEKEND_DAY,
    )
    try:
        async with db.begin_nested():
            db.add(row)
            await db.flush()
    except IntegrityError:
        # Two first-time writers may race after a legacy/manual delete. Use a
        # locking current-read so MySQL/TiDB REPEATABLE READ sees the winner.
        existing = (
            await db.execute(
                select(FinanceCloseCalendarConfig)
                .where(FinanceCloseCalendarConfig.id == _CONFIG_ID)
                .with_for_update()
                .execution_options(populate_existing=True)
            )
        ).scalar_one_or_none()
        if existing is None:  # pragma: no cover - non-PK integrity failure
            raise
        return existing
    return row


async def lock_config_for_mutation(
    db: AsyncSession,
) -> FinanceCloseCalendarConfig:
    """Return the singleton under an exclusive transaction-scoped row lock.

    Besides calendar edits, the explicit close-template seed commands use
    this stable singleton as their serialization point.  That keeps initial
    entity/template materialisation idempotent without connection-scoped
    advisory locks or a new schema object.  The caller must commit or roll
    back promptly after its mutation and audit work.
    """
    await ensure_config(db)
    return (
        await db.execute(
            select(FinanceCloseCalendarConfig)
            .where(FinanceCloseCalendarConfig.id == _CONFIG_ID)
            .with_for_update()
            .execution_options(populate_existing=True)
        )
    ).scalar_one()


async def load_holidays(db: AsyncSession) -> set[date]:
    rows = (await db.execute(select(FinanceCloseHoliday.day))).scalars().all()
    return set(rows)


async def load_makeup(db: AsyncSession) -> set[date]:
    rows = (await db.execute(select(FinanceCloseMakeupDay.day))).scalars().all()
    return set(rows)


# ── window computation ──

async def compute_window(db: AsyncSession, run: FinanceCloseRun) -> engine.CloseWindow:
    """Compute the close window for ``run`` using the persisted calendar.

    A frozen (soft/hard-closed) run recomputes with the *snapshotted* target
    workday + weekend rule so a later config change cannot move it; an open
    run uses the live config.
    """
    config = await load_config(db)
    holidays = await load_holidays(db)
    makeup = await load_makeup(db)
    if run.status in RUN_FROZEN_STATUSES and run.frozen_target_workday:
        target = run.frozen_target_workday
        weekend = run.frozen_work_weekend_day or config.work_weekend_day
    else:
        target = config.target_workday
        weekend = config.work_weekend_day
    return engine.compute_close_window(
        run.period,
        anchor_minus_1=run.anchor_minus_1,
        anchor_plus_1=run.anchor_plus_1,
        target_workday=target,
        work_weekend_day=weekend,
        holidays=holidays,
        makeup_workdays=makeup,
    )


def effective_close_date(run: FinanceCloseRun, window: engine.CloseWindow) -> date:
    """Manual override wins; else the (possibly frozen) computed close date."""
    if run.override_close_date is not None:
        return run.override_close_date
    if run.frozen_close_date is not None and run.status in RUN_FROZEN_STATUSES:
        return run.frozen_close_date
    return window.close_date


# ── close-run lifecycle ──

async def get_run(db: AsyncSession, period: str) -> FinanceCloseRun | None:
    return (
        await db.execute(
            select(FinanceCloseRun).where(FinanceCloseRun.period == period)
        )
    ).scalar_one_or_none()


async def ensure_run(
    db: AsyncSession, period: str, *, actor_id: str | None = None
) -> FinanceCloseRun:
    """Get the run for ``period``, creating it with default anchors if absent.

    Idempotent under concurrency: get-then-insert races (double-click, two
    admins on the same period) would otherwise both see ``None`` and both
    INSERT, tripping ``uq_finance_close_run_period`` on the second flush and
    poisoning the async session (→ PendingRollbackError → 500). The INSERT is
    wrapped in a SAVEPOINT so an IntegrityError rolls back only the nested
    write; we then re-select the row the winner created and return it.
    """
    validate_period(period)
    run = await get_run(db, period)
    if run is not None:
        return run
    anchor_minus_1, anchor_plus_1 = engine.default_anchors(period)
    run = FinanceCloseRun(
        period=period,
        label=period_label(period),
        anchor_minus_1=anchor_minus_1,
        anchor_plus_1=anchor_plus_1,
        status=RUN_OPEN,
        created_by_id=actor_id,
    )
    try:
        async with db.begin_nested():
            db.add(run)
            await db.flush()
    except IntegrityError:
        # A concurrent caller won the INSERT. Re-select the surviving row
        # with a LOCKING current-read — NOT a plain snapshot read. Under
        # MySQL/TiDB REPEATABLE READ this transaction's MVCC snapshot was
        # fixed at the `get_run` above (before the winner committed), so a
        # plain `SELECT` (get_run) still sees no row → `None` → we'd re-raise
        # the very 500 this guard exists to prevent. `FOR UPDATE` performs a
        # current read that sees the freshly-committed winner; `populate_
        # existing` bypasses the identity-map cache so we don't get a stale
        # (or expunged-None) object back. Same idiom as workflow_journal.
        existing = (
            await db.execute(
                select(FinanceCloseRun)
                .where(FinanceCloseRun.period == period)
                .with_for_update()
                .execution_options(populate_existing=True)
            )
        ).scalar_one_or_none()
        if existing is None:  # pragma: no cover — a non-period IntegrityError
            raise
        return existing
    return run


async def update_anchors(
    db: AsyncSession,
    run: FinanceCloseRun,
    *,
    anchor_minus_1: date | None,
    anchor_plus_1: date | None,
) -> FinanceCloseRun:
    if run.status in RUN_FROZEN_STATUSES:
        raise ValidationError("cannot edit anchors on a soft/hard-closed run; reopen first")
    if anchor_minus_1 is not None:
        run.anchor_minus_1 = anchor_minus_1
    if anchor_plus_1 is not None:
        run.anchor_plus_1 = anchor_plus_1
    if run.anchor_plus_1 <= run.anchor_minus_1:
        raise ValidationError("Day+1 anchor must fall after the Day-1 anchor")
    await db.flush()
    return run


async def set_override(
    db: AsyncSession,
    run: FinanceCloseRun,
    *,
    override_close_date: date | None,
    reason: str | None,
    actor_id: str | None,
) -> FinanceCloseRun:
    """Set or clear the manual close-date override. Reason is mandatory when setting."""
    if run.status in RUN_FROZEN_STATUSES:
        # A soft/hard-closed run is frozen; its close date must not move
        # (effective_close_date prefers the override even when frozen, so an
        # override here would silently defeat the freeze invariant). Reopen first.
        raise ValidationError("cannot override the close date on a soft/hard-closed run; reopen first")
    if override_close_date is None:
        run.override_close_date = None
        run.override_reason = None
        run.override_by_id = None
        run.override_at = None
    else:
        clean_reason = (reason or "").strip()
        if not clean_reason:
            raise ValidationError("an audit reason is required to override the close date")
        run.override_close_date = override_close_date
        run.override_reason = clean_reason
        run.override_by_id = actor_id
        run.override_at = _now()
    await db.flush()
    return run


async def set_status(
    db: AsyncSession,
    run: FinanceCloseRun,
    *,
    new_status: str,
    actor_id: str | None,
) -> FinanceCloseRun:
    """Transition the close-run lifecycle, freezing/unfreezing the snapshot."""
    if new_status not in RUN_STATUSES:
        raise ValidationError(f"unknown close status {new_status!r}")
    # Re-fetch under a row lock so two admins (or a double-click)
    # transitioning the same period serialize and each re-checks the CURRENT
    # status atomically. Without it both read the same ``old`` status, both
    # pass the ``_FORWARD`` check, and both write (duplicate timestamps /
    # racing freeze+reopen). ``ensure_run`` only locks the creation race, not
    # the already-exists path this receives.
    locked = await db.scalar(
        select(FinanceCloseRun)
        .where(FinanceCloseRun.id == run.id)
        .with_for_update()
    )
    if locked is not None:
        run = locked
    old = run.status
    if new_status == old:
        return run
    if new_status not in _FORWARD.get(old, set()):
        raise ValidationError(f"invalid close transition {old} → {new_status}")

    now = _now()
    becoming_frozen = new_status in RUN_FROZEN_STATUSES and old not in RUN_FROZEN_STATUSES
    leaving_frozen = old in RUN_FROZEN_STATUSES and new_status not in RUN_FROZEN_STATUSES

    if becoming_frozen:
        # Snapshot the close date + calendar knobs so it can never drift.
        config = await load_config(db)
        window = await compute_window(db, run)
        run.frozen_close_date = effective_close_date(run, window)
        run.frozen_target_workday = config.target_workday
        run.frozen_work_weekend_day = config.work_weekend_day
    if leaving_frozen:
        run.frozen_close_date = None
        run.frozen_target_workday = None
        run.frozen_work_weekend_day = None
        run.reopened_at = now
        run.reopened_by_id = actor_id

    if new_status == RUN_SOFT_CLOSED:
        run.soft_closed_at = now
        run.soft_closed_by_id = actor_id
    elif new_status == RUN_CLOSED:
        run.closed_at = now
        run.closed_by_id = actor_id
        if run.soft_closed_at is None:
            run.soft_closed_at = now
            run.soft_closed_by_id = actor_id

    run.status = new_status
    await db.flush()
    return run


async def mark_in_progress(db: AsyncSession, run: FinanceCloseRun) -> None:
    """Bump an ``open`` run to ``in_progress`` (called when work starts; idempotent)."""
    if run.status == RUN_OPEN:
        run.status = RUN_IN_PROGRESS
        await db.flush()


# ── calendar-config CRUD ──

async def update_config(
    db: AsyncSession,
    *,
    target_workday: int | None,
    work_weekend_day: str | None,
    actor_id: str | None,
) -> FinanceCloseCalendarConfig:
    config = await ensure_config(db)
    if target_workday is not None:
        if not 1 <= target_workday <= 20:
            raise ValidationError("target_workday must be between 1 and 20")
        config.target_workday = target_workday
    if work_weekend_day is not None:
        if work_weekend_day not in WORK_WEEKEND_DAYS:
            raise ValidationError("work_weekend_day must be 'saturday' or 'sunday'")
        config.work_weekend_day = work_weekend_day
    config.updated_by_id = actor_id
    await db.flush()
    return config


async def add_holiday(
    db: AsyncSession, *, day: date, country: str, name: str | None
) -> FinanceCloseHoliday:
    existing = (
        await db.execute(
            select(FinanceCloseHoliday).where(
                FinanceCloseHoliday.day == day,
                FinanceCloseHoliday.country == country,
            )
        )
    ).scalar_one_or_none()
    if existing is not None:
        existing.name = name or existing.name
        # An admin re-adding/editing a holiday claims it as manual so a later
        # restore_defaults (which deletes only source='default' rows) cannot
        # silently drop the edit.
        existing.source = SOURCE_MANUAL
        await db.flush()
        return existing
    row = FinanceCloseHoliday(
        day=day, country=country, name=name, source=SOURCE_MANUAL,
    )
    db.add(row)
    await db.flush()
    return row


async def remove_holiday(db: AsyncSession, *, holiday_id: int) -> None:
    row = await db.get(FinanceCloseHoliday, holiday_id)
    if row is None:
        raise NotFoundError("holiday not found")
    await db.delete(row)
    await db.flush()


async def list_holidays(
    db: AsyncSession, *, year: int | None = None
) -> list[FinanceCloseHoliday]:
    stmt = select(FinanceCloseHoliday).order_by(FinanceCloseHoliday.day)
    rows = (await db.execute(stmt)).scalars().all()
    if year is not None:
        rows = [r for r in rows if r.day.year == year]
    return list(rows)


async def add_makeup(
    db: AsyncSession, *, day: date, note: str | None
) -> FinanceCloseMakeupDay:
    existing = (
        await db.execute(
            select(FinanceCloseMakeupDay).where(FinanceCloseMakeupDay.day == day)
        )
    ).scalar_one_or_none()
    if existing is not None:
        return existing
    row = FinanceCloseMakeupDay(day=day, note=note, source=SOURCE_MANUAL)
    db.add(row)
    await db.flush()
    return row


async def remove_makeup(db: AsyncSession, *, makeup_id: int) -> None:
    row = await db.get(FinanceCloseMakeupDay, makeup_id)
    if row is None:
        raise NotFoundError("makeup day not found")
    await db.delete(row)
    await db.flush()


async def list_makeup(db: AsyncSession) -> list[FinanceCloseMakeupDay]:
    return list(
        (await db.execute(select(FinanceCloseMakeupDay).order_by(FinanceCloseMakeupDay.day)))
        .scalars()
        .all()
    )


async def restore_defaults(db: AsyncSession, *, actor_id: str | None) -> dict:
    """Re-seed the built-in PingCAP/CN holidays + makeup days, reset config.

    Removes only ``source='default'`` rows then re-inserts the bundled
    defaults, so admin-added (``manual``) holidays/makeup days are preserved.
    Idempotent.
    """
    await db.execute(
        delete(FinanceCloseHoliday).where(FinanceCloseHoliday.source == SOURCE_DEFAULT)
    )
    await db.execute(
        delete(FinanceCloseMakeupDay).where(FinanceCloseMakeupDay.source == SOURCE_DEFAULT)
    )
    manual_h = {
        (r.day, r.country)
        for r in (await db.execute(select(FinanceCloseHoliday))).scalars().all()
    }
    manual_m = {
        r.day for r in (await db.execute(select(FinanceCloseMakeupDay))).scalars().all()
    }
    added_h = 0
    for iso in DEFAULT_HOLIDAYS:
        d = date.fromisoformat(iso)
        if (d, DEFAULT_HOLIDAY_COUNTRY) in manual_h:
            continue
        db.add(FinanceCloseHoliday(
            day=d, country=DEFAULT_HOLIDAY_COUNTRY, name=None, source=SOURCE_DEFAULT,
        ))
        added_h += 1
    added_m = 0
    for iso in DEFAULT_MAKEUP_WORKDAYS:
        d = date.fromisoformat(iso)
        if d in manual_m:
            continue
        db.add(FinanceCloseMakeupDay(day=d, source=SOURCE_DEFAULT))
        added_m += 1
    config = await ensure_config(db)
    config.target_workday = DEFAULT_TARGET_WORKDAY
    config.work_weekend_day = DEFAULT_WORK_WEEKEND_DAY
    config.updated_by_id = actor_id
    await db.flush()
    return {"holidays_added": added_h, "makeup_added": added_m}