"""Leave-health insights — daily snapshot model (trend history).

The live insights endpoints compute everything on the fly from
``hr_leave_accounts`` (cheap at this scale). This table exists only so the
"is this team's leave culture improving or getting worse?" trend has data
to draw — there is **no historical backfill** (SF leave data is current-
state only), so the trend accumulates forward from the day the daily
``hr.leave_insight_snapshot`` cron first runs. One row per
(snapshot_date, dimension, group_key); the company baseline is stored as a
``dimension='company'`` row so a group line can be drawn against it.

FK note (TiDB): no FK to identity_persons — ``group_key`` holds a BU name /
department name / lead person-id string depending on ``dimension``, so it
is a plain string column (see the leave_models docstring for the TiDB
CHAR↔VARCHAR FK convention this repo follows).
"""
from __future__ import annotations

import uuid
from datetime import date, datetime

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

from app.core.database import Base
from app.core.sql_types import GUID as Uuid

_DAYS = Numeric(10, 2)


class HrLeaveInsightSnapshot(Base):
    """One daily aggregated leave-health row for a BU / dept / lead / company."""

    __tablename__ = "hr_leave_insight_snapshots"
    __table_args__ = (
        UniqueConstraint(
            "snapshot_date", "dimension", "group_key",
            name="uq_hr_leave_insight_snapshot",
        ),
        Index(
            "ix_hr_leave_insight_trend",
            "dimension", "group_key", "snapshot_date",
        ),
        Index("ix_hr_leave_insight_date", "snapshot_date"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)

    snapshot_date: Mapped[date] = mapped_column(Date, nullable=False)
    # 'company' | 'bu' | 'department' | 'lead'
    dimension: Mapped[str] = mapped_column(String(16), nullable=False)
    # BU name / department name / lead person-id / '__company__'
    group_key: Mapped[str] = mapped_column(String(255), nullable=False)
    group_label: Mapped[str | None] = mapped_column(String(255))

    fiscal_year: Mapped[int | None] = mapped_column(Integer)

    member_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    covered_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    annual_entitled_total: Mapped[float | None] = mapped_column(_DAYS)
    annual_used_total: Mapped[float | None] = mapped_column(_DAYS)
    annual_balance_total: Mapped[float | None] = mapped_column(_DAYS)
    forfeit_at_risk_total: Mapped[float | None] = mapped_column(_DAYS)
    comp_banked_total: Mapped[float | None] = mapped_column(_DAYS)

    # Stored as floats (ratios), nullable when the group has no quota.
    utilisation: Mapped[float | None] = mapped_column(Float)
    median_utilisation: Mapped[float | None] = mapped_column(Float)
    pace_index: Mapped[float | None] = mapped_column(Float)
    vs_company: Mapped[float | None] = mapped_column(Float)
    low_utiliser_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    health: Mapped[str | None] = mapped_column(String(24))

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )