"""Fixture catalog for the planner eval — grounded in Sofhia's real test file
`PA Data Dummy.xlsx` (mining equipment physical-availability data, 9729 daily
rows, single site, April 2026).

Column ids are readable (`c_<snake_name>`) on purpose: the planner only echoes
whatever ids the summary gives it, and readable ids make the result files easy
to diff. `name_to_id()` maps a column NAME back to its id so the assertions in
`run_eval.py` can be written against names.

NOTE: date columns are typed `date` here — i.e. the *post-fix* catalog. The live
system currently mis-types Excel date serials as `int` (a Go-ingest bug, see the
date-handling note), which is an INGEST issue, not a planner one. Typing them
correctly here keeps this eval about planner logic, not the ingest bug.
"""

from __future__ import annotations

from datetime import UTC, datetime
from typing import Any

from src.catalog.models import Catalog, Column, ColumnStats, DataType, Source, Table

SOURCE_ID = "src_pa"
TABLE_ID = "t_pa"
ROW_COUNT = 9729

# (name, data_type, sample_values, top_values)
_COLUMNS: list[tuple[str, DataType, list[Any] | None, list[Any] | None]] = [
    ("KeyId", "int", [895272, 895245, 895285], None),
    ("Month_ID", "int", [202604], [202604]),
    ("Site_ID", "int", [2009], [2009]),
    ("From_Date", "date", ["2026-04-06", "2026-04-25"], None),
    ("To_Date", "date", ["2026-04-06", "2026-04-25"], None),
    ("Time_Description", "string", ["Daily"], ["Daily"]),
    # High-cardinality but NOT unique — a valid ranking/group dimension.
    ("Model_Unit", "string", ["777E", "777D", "777", "785D", "789C", "HD785-7", "PC2000-8", "EX3600-6"], None),
    # Per-unit identifier (high cardinality). Ranking BY unit must still group by it.
    ("Equipment_Number", "string", ["HDCT77457", "HDCT77455", "EXHC36007"], None),
    ("Equipment_Group_ID", "string", ["Main Hauler", "Main Loader"], ["Main Hauler", "Main Loader"]),
    ("Unit_Status", "string", ["INPR"], ["INPR"]),
    ("Total_Breakdown_Schedule_Hour", "decimal", [19.416, 0.0], None),
    ("Total_Breakdown_Unschedule_Hour", "decimal", [0.0, 3.728], None),
    ("Total_Adj_Breakdown_Schedule_Hour", "decimal", [19.416, 0.0], None),
    ("Total_Adj_Breakdown_Unschedule_Hour", "decimal", [0.0, 2.982], None),
    ("Total_MTC_Hour", "decimal", [19.4164, 0.0], None),
    ("Total_Down_Hour", "decimal", [19.4164, 0.0], None),
    ("Total_Frequency_Breakdown_Schedule", "int", [1, 0], None),
    ("Total_Frequency_Breakdown_Unschedule", "int", [0, 3], None),
    ("Total_Frequency_Maintenance", "int", [1, 3], None),
    ("Total_Frequency_Tire", "int", [0], None),
    ("Total_Frequency_Down", "int", [1, 3], None),
    ("Total_Hours", "int", [24], [24]),
    ("Total_INPR_Hour", "int", [24, 0], None),
    ("Total_Record_HM_Hour", "decimal", [0.0], None),
    ("Total_HM_Mtc_Down_Hour", "int", [0, 20], None),
    ("Plan_PA_Percent", "decimal", [100.0, 91.46], None),
    ("PA_Percent", "decimal", [19.0984, 100.0, 84.4676], None),
    ("MTBS", "decimal", [0.0, 5.4667], None),
    ("MTTR", "decimal", [19.4164, 1.2426, 0.0], None),
    ("SM_Percent", "decimal", [100.0, 0.0], None),
    ("Unschedule_SM_Percent", "decimal", [0.0, 100.0], None),
    ("IsDeleted", "int", [0], [0]),
    ("Section", "string", ["OB HAULER", "OB LOADER"], ["OB HAULER", "OB LOADER"]),
    ("Week_ID", "int", [202617], None),
    ("Plan_PA_Percent_2", "decimal", [88.0, 91.0], None),
    ("Updated_Date", "datetime", ["2026-04-25T00:45:17"], None),
]


def name_to_id() -> dict[str, str]:
    return {name: f"c_{name.lower()}" for name, *_ in _COLUMNS}


def build_pa_catalog() -> Catalog:
    columns = [
        Column(
            column_id=f"c_{name.lower()}",
            name=name,
            data_type=dtype,
            nullable=False,
            pii_flag=False,
            sample_values=samples,
            stats=ColumnStats(distinct_count=len(top) if top else None, top_values=top),
        )
        for name, dtype, samples, top in _COLUMNS
    ]
    table = Table(table_id=TABLE_ID, name="PA Data Dummy", row_count=ROW_COUNT, columns=columns, foreign_keys=[])
    source = Source(
        source_id=SOURCE_ID,
        source_type="tabular",
        name="PA Data Dummy.xlsx",
        location_ref="object_storage://eval/pa",
        updated_at=datetime.now(UTC),
        tables=[table],
    )
    return Catalog(user_id="eval-user", sources=[source], generated_at=datetime.now(UTC))