"""Load the seed CSVs into the database. Idempotent - safe to re-run.

Runs inside the container after migrations:
    python -m scripts.seed

Sources are the committed fixtures in main/seed/, extracted from Mike's real
spreadsheets by scripts/extract_seed.py.
"""

from __future__ import annotations

import csv
from pathlib import Path

from sqlalchemy import select

from app.db import SessionLocal
from app.models.tables import Asset, Part, Technician

SEED_DIR = Path(__file__).resolve().parent.parent / "seed"

# Placeholder crew so the name picker is usable on first run. Real names arrive
# with the plant; at P2 this table becomes a projection of BW accounts.
DEFAULT_TECHNICIANS = [
    ("Mike McPhee", "planner"),
    ("Maintenance Planner", "planner"),
    ("Technician A", "tech"),
    ("Technician B", "tech"),
    ("Technician C", "tech"),
]


def _to_int(value: str) -> int:
    try:
        return int(float(value))
    except (TypeError, ValueError):
        return 0


def _to_float(value: str) -> float | None:
    try:
        return float(value)
    except (TypeError, ValueError):
        return None


def _blank_to_none(value: str) -> str | None:
    value = (value or "").strip()
    return value or None


def seed_assets(db) -> int:
    path = SEED_DIR / "assets.csv"
    existing = {a.equipment_no for a in db.scalars(select(Asset))}
    added = 0
    with path.open(encoding="utf-8") as handle:
        for row in csv.DictReader(handle):
            equipment_no = row["equipment_no"].strip()
            if not equipment_no or equipment_no in existing:
                continue
            db.add(
                Asset(
                    equipment_no=equipment_no,
                    description=row["description"].strip(),
                    line=_blank_to_none(row["line"]),
                    manufacturer=_blank_to_none(row["manufacturer"]),
                )
            )
            existing.add(equipment_no)
            added += 1
    db.commit()
    return added


def seed_parts(db) -> int:
    path = SEED_DIR / "parts.csv"
    existing = {p.part_number for p in db.scalars(select(Part))}
    added = 0
    with path.open(encoding="utf-8") as handle:
        for row in csv.DictReader(handle):
            part_number = row["part_number"].strip()
            if not part_number or part_number in existing:
                continue
            db.add(
                Part(
                    part_number=part_number,
                    part_name=row["part_name"].strip(),
                    description=_blank_to_none(row["description"]),
                    uom=_blank_to_none(row["uom"]),
                    location=_blank_to_none(row["location"]),
                    area=_blank_to_none(row["area"]),
                    part_type=_blank_to_none(row["part_type"]),
                    barcode=_blank_to_none(row["barcode"]),
                    unit_cost=_to_float(row["unit_cost"]),
                    quantity=_to_int(row["quantity"]),
                )
            )
            existing.add(part_number)
            added += 1
    db.commit()
    return added


def seed_technicians(db) -> int:
    existing = {t.name for t in db.scalars(select(Technician))}
    added = 0
    for name, role in DEFAULT_TECHNICIANS:
        if name in existing:
            continue
        db.add(Technician(name=name, role=role))
        added += 1
    db.commit()
    return added


def main() -> int:
    with SessionLocal() as db:
        assets = seed_assets(db)
        parts = seed_parts(db)
        technicians = seed_technicians(db)
    print(f"seeded: {assets} assets, {parts} parts, {technicians} technicians")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())