"""ORM rows for the multi-country **public holiday** calendar.

HR maintains one company-wide holiday calendar per country/region per year.
Everything else in the platform reads it: the employee-facing公示 page, the
holiday-notice generator, the Word/Excel exports, the External API, and MCP.

Deliberately **separate from** :class:`app.domains.finance.close_models.FinanceCloseHoliday`.
That table is the month-end book-close engine's own China-only workday
calendar, already live in production and load-bearing for close-date maths.
Merging the two is a real convergence question, not a drive-by — it needs its
own ADR, so this domain owns its data and leaves finance alone.

Tables
------
* ``hr_public_holidays`` — one row per (country, calendar day, holiday name).
  Multi-day holidays are stored **one row per day**, which is what makes the
  month grid, the date-range API and the exports trivial; the multi-day shape
  is recovered by grouping on ``group_key``.
* ``hr_public_holiday_calendars`` — per (country, year) metadata: HR's headline
  day count, the Google Calendar subscription link, and the regional-holiday
  advisory. Its existence is also the **coverage signal**: a country/year with
  no row means "not maintained", which the External API must not collapse into
  "no holidays" (mirrors the three-state contract in ``open_leave_router``).

Person references (``created_by_id`` / ``updated_by_id``) are plain id strings
with no DB-level FK — the platform-wide TiDB-FK-safe convention.
"""
from __future__ import annotations

from datetime import date, datetime

from sqlalchemy import (
    BigInteger,
    Date,
    DateTime,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql import func

from app.core.database import Base
from app.domains.hr.holiday_keys import (
    GROUP_KEY_LEN,
    NAME_KEY_LEN,
    name_key_for,
    normalize_country,
)

# Re-exported so callers keep importing the whole vocabulary from one place;
# the key rules themselves live in the import-free ``holiday_keys`` module so
# migration 462 can share them without dragging in ``app.core.config``.
__all__ = [
    "CALENDARS_TABLE",
    "DAY_TYPES",
    "GROUP_KEY_LEN",
    "HOLIDAYS_TABLE",
    "MAKEUP_WORKDAY",
    "NAME_KEY_LEN",
    "PUBLIC_HOLIDAY",
    "SOURCES",
    "SOURCE_AI",
    "SOURCE_IMPORT",
    "SOURCE_MANUAL",
    "SOURCE_SEED",
    "HrPublicHoliday",
    "HrPublicHolidayCalendar",
    "name_key_for",
    "normalize_country",
]

HOLIDAYS_TABLE = "hr_public_holidays"
CALENDARS_TABLE = "hr_public_holiday_calendars"

# ── day_type ──
# A paid day off.
PUBLIC_HOLIDAY = "public_holiday"
# 调休上班日 — a weekend day officially designated a workday to offset a long
# holiday. China-only in the 2026 seed, but the column is not China-specific.
MAKEUP_WORKDAY = "makeup_workday"
DAY_TYPES = (PUBLIC_HOLIDAY, MAKEUP_WORKDAY)

# ── source (provenance) ──
SOURCE_SEED = "seed"          # shipped 2026 baseline from the HR document
SOURCE_MANUAL = "manual"      # typed into the HR console
SOURCE_IMPORT = "import"      # Excel bulk import
SOURCE_AI = "ai_import"       # AI-parsed from pasted text, HR-confirmed
SOURCES = (SOURCE_SEED, SOURCE_MANUAL, SOURCE_IMPORT, SOURCE_AI)


class HrPublicHoliday(Base):
    """One country's one calendar day of one named holiday (or makeup workday)."""

    __tablename__ = HOLIDAYS_TABLE
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "country", "day", "name_key",
            name="uq_hr_public_holiday_country_day_name",
        ),
        # Employee/API reads are always "this country, this date window".
        Index("ix_hr_public_holiday_country_day", "country", "day"),
        # Group aggregation (Word export, notice generator, group edits).
        Index("ix_hr_public_holiday_country_group", "country", "group_key"),
        # Cross-country "what is coming up everywhere" reads.
        Index("ix_hr_public_holiday_day", "day"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    country: Mapped[str] = mapped_column(String(8), nullable=False)
    day: Mapped[date] = mapped_column(Date, nullable=False)
    # All days of one holiday (plus its makeup workdays) share this key.
    group_key: Mapped[str] = mapped_column(String(GROUP_KEY_LEN), nullable=False)
    name_en: Mapped[str] = mapped_column(String(255), nullable=False)
    name_local: Mapped[str | None] = mapped_column(String(255))
    # Slug of name_en, maintained by the service layer — never set by callers.
    name_key: Mapped[str] = mapped_column(String(NAME_KEY_LEN), nullable=False)
    day_type: Mapped[str] = mapped_column(
        String(24), nullable=False,
        default=PUBLIC_HOLIDAY, server_default=PUBLIC_HOLIDAY,
    )
    note: Mapped[str | None] = mapped_column(String(500))
    source: Mapped[str] = mapped_column(
        String(16), nullable=False, default=SOURCE_MANUAL, server_default=SOURCE_MANUAL,
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False,
    )
    created_by_id: Mapped[str | None] = mapped_column(String(36))
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(),
        onupdate=func.now(), nullable=False,
    )
    updated_by_id: Mapped[str | None] = mapped_column(String(36))

    @property
    def is_makeup(self) -> bool:
        return self.day_type == MAKEUP_WORKDAY


class HrPublicHolidayCalendar(Base):
    """Per (country, year) calendar metadata — and the coverage signal.

    ``stated_days`` is HR's published headline count ("Australia Holidays - 8
    days"), stored rather than derived: it counts *observed* days off (holidays
    falling Mon–Fri, net of makeup workdays), which no single formula reproduces
    across every country in the source document. The API returns a derived count
    next to it so the console can flag a divergence for HR to resolve, instead of
    silently overwriting what HR published.
    """

    __tablename__ = CALENDARS_TABLE
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("country", "year", name="uq_hr_public_holiday_cal_country_year"),
        Index("ix_hr_public_holiday_cal_year", "year"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    country: Mapped[str] = mapped_column(String(8), nullable=False)
    year: Mapped[int] = mapped_column(Integer, nullable=False)
    # English display label ("Hong Kong"); localised names come from FE i18n.
    label: Mapped[str | None] = mapped_column(String(64))
    stated_days: Mapped[int | None] = mapped_column(Integer)
    gcal_url: Mapped[str | None] = mapped_column(String(512))
    region_note: Mapped[str | None] = mapped_column(Text)
    source: Mapped[str] = mapped_column(
        String(16), nullable=False, default=SOURCE_MANUAL, server_default=SOURCE_MANUAL,
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False,
    )
    created_by_id: Mapped[str | None] = mapped_column(String(36))
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(),
        onupdate=func.now(), nullable=False,
    )
    updated_by_id: Mapped[str | None] = mapped_column(String(36))