"""
logistics_store.py — SQLite store for the Togen Logistics tool (DVI-1387 P1
[DVI-1397], board-approved plan rev b8a5ca65).

Backs the Logistics coordinator: Resources (drivers/trucks/trailers) and the
Settings → Events catalog now; Loads / assignments / event timelines land in
P2 (their tables are created here so the schema is in place, but P1 exercises
only the resource + event-type + availability tables).

Flask-independent (mirrors qc_store.py / assets_store.py) so app.py, scripts,
and tests can all import it. logistics.db lives alongside the app's other
state files.

Design decisions (from the approved DVI-1387 plan + board answers 2026-08-12):
- **Drivers are a designated subset of Personnel (D7).** A driver row is keyed
  by the Personnel record id (`per_*`) — name/email live only on the Personnel
  record (personnel.json), never duplicated here. This table holds the
  logistics-only fields: max hours/day + hours/week planning knobs and the
  availability calendar. Removing a driver deletes the row (app.py also clears
  the `driver` designation) but never the Personnel record.
- **Availability is normalized (D2 groundwork).** Driver time-off and truck /
  trailer out-of-service dates are rows in `availability_windows` keyed by
  (resource_type, resource_id). One table = one place for P2's overlap engine
  to query "is this resource free for this window?". P1 stores + displays them;
  the conflict engine arrives in P2.
- **Trucks / trailers are soft-deleted** so a future load that referenced one
  keeps a stable id; drivers hard-delete the logistics row (the person stays in
  Personnel).
- **Hours are a planning aid, NOT legal HOS** (D3) — surfaced in the UI.

Schema
------
drivers(person_id PK [per_*], max_hours_day, max_hours_week, active,
        created_at/by, updated_at) — logistics fields for a designated person.
trucks(name, truck_number, asset_number, in_service, gross_weight,
        features JSON, active, deleted, created_at/by, updated_at) — a truck.
trailers(...) — same shape (gross_weight = weight it can carry; features []).
event_types(name, typical_minutes, sort, active, created_at, updated_at) —
        the reusable load-timeline events (Settings → Events).
availability_windows(resource_type [driver|truck|trailer], resource_id, kind
        [unavailable|out_of_service], start_date, end_date, note) — a blocked
        date range for one resource.
loads / load_assignments / load_events — created for P2 (empty in P1).
meta(key, value) — bookkeeping.
"""

import json
import os
import re
import sqlite3
import threading
from datetime import datetime
from pathlib import Path

LOGISTICS_DB_FILE = Path(
    os.environ.get("LOGISTICS_DB_FILE",
                   Path(__file__).resolve().parent / "logistics.db"))

_write_lock = threading.Lock()

_DATE_RE = re.compile(r"^\d{4}-\d{2}-\d{2}$")
_PERSON_ID_RE = re.compile(r"^per_[0-9a-f]{6,}$")

RESOURCE_TYPES = ("driver", "truck", "trailer")

# Truck special features (board answer: only Extending Bucket Boom for now).
# Trailer features are intentionally empty this phase (extension point kept).
TRUCK_FEATURES = (("extending_bucket_boom", "Extending Bucket Boom"),)
TRAILER_FEATURES = ()
_TRUCK_FEATURE_IDS = {f[0] for f in TRUCK_FEATURES}
_TRAILER_FEATURE_IDS = {f[0] for f in TRAILER_FEATURES}

# Seeded into an EMPTY event_types table only (never re-adds an admin-deleted
# row on restart). These are the plan's example load-timeline events.
_SEED_EVENTS = (
    ("Hook Trailer", 15),
    ("Load", 45),
    ("Fuel", 20),
    ("Drive", 60),
    ("Unload", 45),
)


def _connect():
    conn = sqlite3.connect(str(LOGISTICS_DB_FILE), timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA busy_timeout=10000")
    conn.execute("PRAGMA foreign_keys=ON")
    conn.row_factory = sqlite3.Row
    return conn


def _now():
    return datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")


def init_logistics_db():
    """Create tables/indexes if missing and seed defaults. Idempotent."""
    with _write_lock, _connect() as conn:
        conn.execute(
            """CREATE TABLE IF NOT EXISTS drivers (
                   person_id TEXT PRIMARY KEY,
                   max_hours_day REAL NOT NULL DEFAULT 11,
                   max_hours_week REAL NOT NULL DEFAULT 60,
                   active INTEGER NOT NULL DEFAULT 1,
                   created_at TEXT, created_by TEXT, updated_at TEXT)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS trucks (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL,
                   truck_number TEXT NOT NULL DEFAULT '',
                   asset_number TEXT NOT NULL DEFAULT '',
                   in_service INTEGER NOT NULL DEFAULT 1,
                   gross_weight REAL,
                   features TEXT NOT NULL DEFAULT '[]',
                   active INTEGER NOT NULL DEFAULT 1,
                   deleted INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT, created_by TEXT, updated_at TEXT)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS trailers (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL,
                   trailer_number TEXT NOT NULL DEFAULT '',
                   asset_number TEXT NOT NULL DEFAULT '',
                   in_service INTEGER NOT NULL DEFAULT 1,
                   gross_weight REAL,
                   features TEXT NOT NULL DEFAULT '[]',
                   active INTEGER NOT NULL DEFAULT 1,
                   deleted INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT, created_by TEXT, updated_at TEXT)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS event_types (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL,
                   typical_minutes INTEGER NOT NULL DEFAULT 0,
                   sort INTEGER NOT NULL DEFAULT 0,
                   active INTEGER NOT NULL DEFAULT 1,
                   created_at TEXT, updated_at TEXT)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS availability_windows (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   resource_type TEXT NOT NULL,
                   resource_id TEXT NOT NULL,
                   kind TEXT NOT NULL DEFAULT 'unavailable',
                   start_date TEXT NOT NULL,
                   end_date TEXT NOT NULL,
                   note TEXT NOT NULL DEFAULT '',
                   created_at TEXT)""")
        conn.execute(
            """CREATE INDEX IF NOT EXISTS idx_avail_resource
                   ON availability_windows(resource_type, resource_id)""")
        # ---- P2 tables (created now so the schema is in place; unused in P1) --
        conn.execute(
            """CREATE TABLE IF NOT EXISTS loads (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL DEFAULT '',
                   ltl_load_id INTEGER,
                   status TEXT NOT NULL DEFAULT 'planned',
                   start_date TEXT, start_time TEXT,
                   end_date TEXT, end_time TEXT,
                   destination TEXT NOT NULL DEFAULT '',
                   miles REAL,
                   budget_amount REAL,
                   cost_notes TEXT NOT NULL DEFAULT '',
                   travel_minutes INTEGER NOT NULL DEFAULT 0,
                   notes TEXT NOT NULL DEFAULT '',
                   deleted INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT, created_by TEXT, updated_at TEXT)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS load_assignments (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   load_id INTEGER NOT NULL,
                   resource_type TEXT NOT NULL,
                   resource_id TEXT NOT NULL)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS load_events (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   load_id INTEGER NOT NULL,
                   event_type_id INTEGER,
                   name TEXT NOT NULL DEFAULT '',
                   minutes INTEGER NOT NULL DEFAULT 0,
                   sort INTEGER NOT NULL DEFAULT 0)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS ltl_loads (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL DEFAULT '',
                   notes TEXT NOT NULL DEFAULT '',
                   deleted INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT, created_by TEXT, updated_at TEXT)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS meta (
                   key TEXT PRIMARY KEY, value TEXT)""")
        _seed_defaults(conn)
        conn.commit()


def _seed_defaults(conn):
    now = _now()
    if conn.execute("SELECT COUNT(*) FROM event_types").fetchone()[0] == 0:
        for i, (name, minutes) in enumerate(_SEED_EVENTS):
            conn.execute(
                """INSERT INTO event_types(name, typical_minutes, sort, active,
                       created_at, updated_at) VALUES(?,?,?,1,?,?)""",
                (name, minutes, (i + 1) * 10, now, now))


# ---------------------------------------------------------------------------
# Sanitizers / helpers
# ---------------------------------------------------------------------------

def _num_or_none(value):
    """Coerce to a non-negative float, else None (blank clears the field)."""
    if value is None or value == "":
        return None
    try:
        n = float(value)
    except (TypeError, ValueError):
        return None
    return n if n >= 0 else None


def _sanitize_features(raw, allowed):
    if not isinstance(raw, (list, tuple)):
        return []
    out = []
    for f in raw:
        key = str(f or "").strip()
        if key in allowed and key not in out:
            out.append(key)
    return out


def _sanitize_windows(raw):
    """Normalize a list of {start_date,end_date,note} blocks. Drops entries
    without two valid dates; swaps a reversed range; caps note length."""
    if not isinstance(raw, (list, tuple)):
        return []
    out = []
    for w in raw:
        if not isinstance(w, dict):
            continue
        start = str(w.get("start_date") or "").strip()
        end = str(w.get("end_date") or "").strip() or start
        if not _DATE_RE.match(start) or not _DATE_RE.match(end):
            continue
        if end < start:
            start, end = end, start
        out.append({"start_date": start, "end_date": end,
                    "note": str(w.get("note") or "").strip()[:200]})
    return out


# ---------------------------------------------------------------------------
# Availability windows
# ---------------------------------------------------------------------------

def list_windows(resource_type, resource_id):
    with _connect() as conn:
        rows = conn.execute(
            """SELECT start_date, end_date, note FROM availability_windows
                   WHERE resource_type=? AND resource_id=?
                   ORDER BY start_date, end_date""",
            (resource_type, str(resource_id))).fetchall()
    return [dict(r) for r in rows]


def _set_windows(conn, resource_type, resource_id, kind, windows):
    conn.execute(
        "DELETE FROM availability_windows WHERE resource_type=? AND resource_id=?",
        (resource_type, str(resource_id)))
    now = _now()
    for w in _sanitize_windows(windows):
        conn.execute(
            """INSERT INTO availability_windows(resource_type, resource_id, kind,
                   start_date, end_date, note, created_at)
                   VALUES(?,?,?,?,?,?,?)""",
            (resource_type, str(resource_id), kind, w["start_date"],
             w["end_date"], w["note"], now))


# ---------------------------------------------------------------------------
# Drivers (keyed by Personnel id; app.py owns the Personnel roster write)
# ---------------------------------------------------------------------------

def _driver_row(conn, row):
    d = dict(row)
    d["active"] = bool(d.get("active", 1))
    d["windows"] = [dict(w) for w in conn.execute(
        """SELECT start_date, end_date, note FROM availability_windows
               WHERE resource_type='driver' AND resource_id=?
               ORDER BY start_date, end_date""", (d["person_id"],)).fetchall()]
    return d


def list_drivers():
    """All logistics driver rows (person_id + logistics fields + windows).
    The caller joins these to Personnel for names."""
    with _connect() as conn:
        rows = conn.execute(
            "SELECT * FROM drivers ORDER BY person_id").fetchall()
        return [_driver_row(conn, r) for r in rows]


def get_driver(person_id):
    with _connect() as conn:
        row = conn.execute(
            "SELECT * FROM drivers WHERE person_id=?", (person_id,)).fetchone()
        return _driver_row(conn, row) if row else None


def upsert_driver(person_id, fields, actor=""):
    """Create or update a driver row (keyed by Personnel `per_*` id). Returns
    (driver, error). `windows` in fields replaces the driver's time-off blocks."""
    person_id = str(person_id or "").strip()
    if not _PERSON_ID_RE.match(person_id):
        return None, "A valid personnel id is required."
    fields = fields or {}
    max_day = _num_or_none(fields.get("max_hours_day"))
    max_week = _num_or_none(fields.get("max_hours_week"))
    now = _now()
    with _write_lock, _connect() as conn:
        existing = conn.execute(
            "SELECT person_id FROM drivers WHERE person_id=?",
            (person_id,)).fetchone()
        if existing:
            sets, params = [], []
            if "max_hours_day" in fields:
                sets.append("max_hours_day=?")
                params.append(max_day if max_day is not None else 11)
            if "max_hours_week" in fields:
                sets.append("max_hours_week=?")
                params.append(max_week if max_week is not None else 60)
            if "active" in fields:
                sets.append("active=?")
                params.append(1 if fields.get("active") else 0)
            sets.append("updated_at=?")
            params.append(now)
            params.append(person_id)
            conn.execute("UPDATE drivers SET %s WHERE person_id=?"
                         % ", ".join(sets), params)
        else:
            conn.execute(
                """INSERT INTO drivers(person_id, max_hours_day, max_hours_week,
                       active, created_at, created_by, updated_at)
                       VALUES(?,?,?,1,?,?,?)""",
                (person_id, max_day if max_day is not None else 11,
                 max_week if max_week is not None else 60, now, actor, now))
        if "windows" in fields:
            _set_windows(conn, "driver", person_id, "unavailable",
                         fields.get("windows"))
        conn.commit()
        row = conn.execute(
            "SELECT * FROM drivers WHERE person_id=?", (person_id,)).fetchone()
        return _driver_row(conn, row), None


def remove_driver(person_id):
    """Delete the logistics driver row + its windows. Returns True if a row was
    removed. The Personnel record is untouched here (app.py clears the
    designation)."""
    with _write_lock, _connect() as conn:
        cur = conn.execute("DELETE FROM drivers WHERE person_id=?", (person_id,))
        conn.execute(
            "DELETE FROM availability_windows WHERE resource_type='driver' AND resource_id=?",
            (person_id,))
        conn.commit()
        return cur.rowcount > 0


# ---------------------------------------------------------------------------
# Trucks / trailers (shared shape)
# ---------------------------------------------------------------------------

def _vehicle_row(conn, row, resource_type, number_field):
    v = dict(row)
    v["in_service"] = bool(v.get("in_service", 1))
    v["active"] = bool(v.get("active", 1))
    v["deleted"] = bool(v.get("deleted", 0))
    try:
        v["features"] = json.loads(v.get("features") or "[]")
    except (json.JSONDecodeError, TypeError):
        v["features"] = []
    v["windows"] = [dict(w) for w in conn.execute(
        """SELECT start_date, end_date, note FROM availability_windows
               WHERE resource_type=? AND resource_id=?
               ORDER BY start_date, end_date""",
        (resource_type, str(v["id"]))).fetchall()]
    return v


def _save_vehicle(table, resource_type, number_field, feature_ids, fields,
                  vid=None, actor=""):
    fields = fields or {}
    name = str(fields.get("name") or "").strip()
    if not name:
        return None, "A name is required."
    number = str(fields.get(number_field) or "").strip()
    asset_number = str(fields.get("asset_number") or "").strip()
    in_service = 1 if fields.get("in_service", True) else 0
    gross = _num_or_none(fields.get("gross_weight"))
    features = json.dumps(_sanitize_features(fields.get("features"), feature_ids))
    now = _now()
    with _write_lock, _connect() as conn:
        if vid:
            row = conn.execute(
                "SELECT id FROM %s WHERE id=? AND deleted=0" % table,
                (vid,)).fetchone()
            if not row:
                return None, "Not found."
            conn.execute(
                """UPDATE %s SET name=?, %s=?, asset_number=?, in_service=?,
                       gross_weight=?, features=?, updated_at=? WHERE id=?"""
                % (table, number_field),
                (name, number, asset_number, in_service, gross, features, now,
                 vid))
        else:
            cur = conn.execute(
                """INSERT INTO %s(name, %s, asset_number, in_service,
                       gross_weight, features, active, deleted, created_at,
                       created_by, updated_at) VALUES(?,?,?,?,?,?,1,0,?,?,?)"""
                % (table, number_field),
                (name, number, asset_number, in_service, gross, features, now,
                 actor, now))
            vid = cur.lastrowid
        if "windows" in fields:
            _set_windows(conn, resource_type, vid, "out_of_service",
                         fields.get("windows"))
        conn.commit()
        row = conn.execute(
            "SELECT * FROM %s WHERE id=?" % table, (vid,)).fetchone()
        return _vehicle_row(conn, row, resource_type, number_field), None


def _delete_vehicle(table, vid):
    with _write_lock, _connect() as conn:
        cur = conn.execute(
            "UPDATE %s SET deleted=1, updated_at=? WHERE id=? AND deleted=0"
            % table, (_now(), vid))
        conn.commit()
        return cur.rowcount > 0


def list_trucks(include_deleted=False):
    with _connect() as conn:
        q = "SELECT * FROM trucks"
        if not include_deleted:
            q += " WHERE deleted=0"
        q += " ORDER BY name COLLATE NOCASE"
        return [_vehicle_row(conn, r, "truck", "truck_number")
                for r in conn.execute(q).fetchall()]


def get_truck(vid):
    with _connect() as conn:
        row = conn.execute("SELECT * FROM trucks WHERE id=?", (vid,)).fetchone()
        return _vehicle_row(conn, row, "truck", "truck_number") if row else None


def save_truck(fields, vid=None, actor=""):
    return _save_vehicle("trucks", "truck", "truck_number", _TRUCK_FEATURE_IDS,
                         fields, vid, actor)


def delete_truck(vid):
    return _delete_vehicle("trucks", vid)


def list_trailers(include_deleted=False):
    with _connect() as conn:
        q = "SELECT * FROM trailers"
        if not include_deleted:
            q += " WHERE deleted=0"
        q += " ORDER BY name COLLATE NOCASE"
        return [_vehicle_row(conn, r, "trailer", "trailer_number")
                for r in conn.execute(q).fetchall()]


def get_trailer(vid):
    with _connect() as conn:
        row = conn.execute(
            "SELECT * FROM trailers WHERE id=?", (vid,)).fetchone()
        return (_vehicle_row(conn, row, "trailer", "trailer_number")
                if row else None)


def save_trailer(fields, vid=None, actor=""):
    return _save_vehicle("trailers", "trailer", "trailer_number",
                         _TRAILER_FEATURE_IDS, fields, vid, actor)


def delete_trailer(vid):
    return _delete_vehicle("trailers", vid)


# ---------------------------------------------------------------------------
# Event types (Settings → Events)
# ---------------------------------------------------------------------------

def _event_row(row):
    e = dict(row)
    e["active"] = bool(e.get("active", 1))
    return e


def list_event_types(include_inactive=False):
    with _connect() as conn:
        q = "SELECT * FROM event_types"
        if not include_inactive:
            q += " WHERE active=1"
        q += " ORDER BY sort, name COLLATE NOCASE"
        return [_event_row(r) for r in conn.execute(q).fetchall()]


def get_event_type(eid):
    with _connect() as conn:
        row = conn.execute(
            "SELECT * FROM event_types WHERE id=?", (eid,)).fetchone()
        return _event_row(row) if row else None


def save_event_type(fields, eid=None):
    fields = fields or {}
    name = str(fields.get("name") or "").strip()
    if not name:
        return None, "A name is required."
    try:
        minutes = int(float(fields.get("typical_minutes") or 0))
    except (TypeError, ValueError):
        minutes = 0
    if minutes < 0:
        minutes = 0
    now = _now()
    with _write_lock, _connect() as conn:
        if eid:
            row = conn.execute(
                "SELECT id FROM event_types WHERE id=?", (eid,)).fetchone()
            if not row:
                return None, "Not found."
            conn.execute(
                """UPDATE event_types SET name=?, typical_minutes=?,
                       updated_at=? WHERE id=?""", (name, minutes, now, eid))
        else:
            nxt = conn.execute(
                "SELECT COALESCE(MAX(sort),0)+10 FROM event_types").fetchone()[0]
            cur = conn.execute(
                """INSERT INTO event_types(name, typical_minutes, sort, active,
                       created_at, updated_at) VALUES(?,?,?,1,?,?)""",
                (name, minutes, nxt, now, now))
            eid = cur.lastrowid
        conn.commit()
        row = conn.execute(
            "SELECT * FROM event_types WHERE id=?", (eid,)).fetchone()
        return _event_row(row), None


def delete_event_type(eid):
    """Hard delete an event type. A load snapshots its events (name+minutes) at
    save time, so deleting a type never rewrites a planned load's timeline."""
    with _write_lock, _connect() as conn:
        cur = conn.execute("DELETE FROM event_types WHERE id=?", (eid,))
        conn.commit()
        return cur.rowcount > 0


# ===========================================================================
# Loads + availability engine (DVI-1398 P2)
# ===========================================================================
#
# A load occupies its WHOLE window (conservative overlap rule this phase, D2).
# The window is plant-local (start_date+start_time .. end_date+end_time). All
# overlap math is done on the sortable "YYYY-MM-DD HH:MM" strings — date/time
# comparison, never epoch arithmetic — so it is immune to timezone/DST shifts
# (the DVI-1364 regional layer only formats these for display).
#
# A resource is UNAVAILABLE for a window when any of:
#   (a) an out-of-service / time-off availability_window overlaps it,
#   (b) the vehicle is flagged not-in-service (driver row inactive) — a global
#       block that P1 exposes as the "In service" checkbox,
#   (c) it is assigned to another non-cancelled load whose window overlaps.
# There is no separate positive "availability calendar" this phase — a resource
# is available unless a window (a) blocks it, so (a) and the plan's (b) are one
# mechanism here.

LOAD_STATUSES = ("planned", "scheduled", "completed", "cancelled")
# Statuses whose loads still OCCUPY their resources for conflict detection.
# A cancelled load frees its resources; a soft-deleted load likewise.
_OCCUPYING_STATUSES = ("planned", "scheduled", "completed")

_TIME_RE = re.compile(r"^\d{2}:\d{2}$")


def _sanitize_time(value):
    """Return a valid HH:MM (24h) or '' (blank = an open end of the window)."""
    s = str(value or "").strip()
    if not _TIME_RE.match(s):
        return ""
    hh, mm = s.split(":")
    try:
        if 0 <= int(hh) <= 23 and 0 <= int(mm) <= 59:
            return s
    except ValueError:
        pass
    return ""


def _win_lo(t):
    return t if t else "00:00"


def _win_hi(t):
    return t if t else "23:59"


def load_window(load):
    """(start_dt, end_dt) as sortable 'YYYY-MM-DD HH:MM' strings, or None when
    the load has no start date. A blank start time floors to 00:00, a blank end
    time ceils to 23:59 (whole-window occupancy)."""
    sd = str(load.get("start_date") or "").strip()
    if not _DATE_RE.match(sd):
        return None
    ed = str(load.get("end_date") or "").strip()
    if not _DATE_RE.match(ed) or ed < sd:
        ed = sd
    st = _sanitize_time(load.get("start_time"))
    et = _sanitize_time(load.get("end_time"))
    return (sd + " " + _win_lo(st), ed + " " + _win_hi(et))


def _load_date_range(load):
    sd = str(load.get("start_date") or "").strip()
    ed = str(load.get("end_date") or "").strip()
    if not _DATE_RE.match(ed) or (ed and ed < sd):
        ed = sd
    return sd, ed


def _overlaps(a_start, a_end, b_start, b_end):
    """Inclusive interval overlap (works for both datetime and date strings)."""
    return a_start <= b_end and b_start <= a_end


def load_duration_minutes(load):
    """Computed total = sum of the load's event minutes + manual travel time."""
    events = load.get("events") or []
    total = sum(_int(e.get("minutes")) for e in events)
    return total + _int(load.get("travel_minutes"))


def _int(value, default=0):
    try:
        return int(float(value))
    except (TypeError, ValueError):
        return default


# ---- read helpers ----------------------------------------------------------

def _load_assignments(conn, load_id):
    return [dict(r) for r in conn.execute(
        """SELECT resource_type, resource_id FROM load_assignments
               WHERE load_id=? ORDER BY id""", (load_id,)).fetchall()]


def _load_events(conn, load_id):
    return [dict(r) for r in conn.execute(
        """SELECT id, event_type_id, name, minutes, sort FROM load_events
               WHERE load_id=? ORDER BY sort, id""", (load_id,)).fetchall()]


def _load_row(conn, row):
    d = dict(row)
    d["deleted"] = bool(d.get("deleted", 0))
    d["assignments"] = _load_assignments(conn, d["id"])
    d["events"] = _load_events(conn, d["id"])
    d["duration_minutes"] = load_duration_minutes(d)
    return d


def list_loads(include_cancelled=True, include_deleted=False):
    with _connect() as conn:
        q = "SELECT * FROM loads WHERE 1=1"
        if not include_deleted:
            q += " AND deleted=0"
        if not include_cancelled:
            q += " AND status!='cancelled'"
        q += " ORDER BY COALESCE(start_date,''), COALESCE(start_time,''), id DESC"
        return [_load_row(conn, r) for r in conn.execute(q).fetchall()]


def get_load(load_id, include_deleted=False):
    with _connect() as conn:
        q = "SELECT * FROM loads WHERE id=?"
        if not include_deleted:
            q += " AND deleted=0"
        row = conn.execute(q, (load_id,)).fetchone()
        return _load_row(conn, row) if row else None


# ---- availability engine ---------------------------------------------------

def _resource_is_globally_unavailable(conn, resource_type, resource_id):
    """(b): a vehicle flagged not-in-service / a deleted or inactive resource
    is unavailable for every window. Returns a conflict dict or None."""
    if resource_type == "driver":
        row = conn.execute(
            "SELECT active FROM drivers WHERE person_id=?",
            (str(resource_id),)).fetchone()
        if row is None:
            return {"kind": "missing", "detail": "driver not found"}
        if not row["active"]:
            return {"kind": "inactive", "detail": "driver inactive"}
        return None
    table = "trucks" if resource_type == "truck" else "trailers"
    row = conn.execute(
        "SELECT in_service, deleted FROM %s WHERE id=?" % table,
        (resource_id,)).fetchone()
    if row is None or row["deleted"]:
        return {"kind": "missing", "detail": "%s not found" % resource_type}
    if not row["in_service"]:
        return {"kind": "out_of_service_flag",
                "detail": "marked not in service"}
    return None


def _window_conflicts(conn, resource_type, resource_id, sd, ed):
    """(a): out-of-service / time-off windows overlapping [sd, ed] (date-only)."""
    out = []
    for r in conn.execute(
            """SELECT kind, start_date, end_date, note FROM availability_windows
                   WHERE resource_type=? AND resource_id=?""",
            (resource_type, str(resource_id))).fetchall():
        if _overlaps(sd, ed, r["start_date"], r["end_date"]):
            out.append({"kind": r["kind"], "start_date": r["start_date"],
                        "end_date": r["end_date"], "note": r["note"]})
    return out


def _load_conflicts(conn, resource_type, resource_id, start_dt, end_dt,
                    exclude_load_id=None):
    """(c): other occupying loads whose window overlaps [start_dt, end_dt]."""
    out = []
    for r in conn.execute(
            """SELECT l.id, l.name, l.status, l.start_date, l.start_time,
                          l.end_date, l.end_time
                   FROM load_assignments a JOIN loads l ON l.id=a.load_id
                   WHERE a.resource_type=? AND a.resource_id=? AND l.deleted=0
                     AND l.status IN ('planned','scheduled','completed')""",
            (resource_type, str(resource_id))).fetchall():
        if exclude_load_id and r["id"] == exclude_load_id:
            continue
        w = load_window(dict(r))
        if w and _overlaps(start_dt, end_dt, w[0], w[1]):
            out.append({"kind": "load", "load_id": r["id"],
                        "load_name": r["name"] or ("Load #%d" % r["id"]),
                        "start_date": r["start_date"],
                        "end_date": r["end_date"]})
    return out


def _conflicts_for(conn, resource_type, resource_id, window_dt, date_range,
                   exclude_load_id=None):
    conflicts = []
    g = _resource_is_globally_unavailable(conn, resource_type, resource_id)
    if g:
        conflicts.append(g)
    sd, ed = date_range
    conflicts += _window_conflicts(conn, resource_type, resource_id, sd, ed)
    conflicts += _load_conflicts(conn, resource_type, resource_id,
                                 window_dt[0], window_dt[1], exclude_load_id)
    return conflicts


def check_availability(resource_type, resource_id, window, exclude_load_id=None):
    """Return (available, conflicts) for one resource against a window dict
    {start_date,start_time,end_date,end_time}."""
    window_dt = load_window(window)
    if not window_dt:
        return True, []  # no window yet → nothing to conflict with
    date_range = _load_date_range(window)
    with _connect() as conn:
        conflicts = _conflicts_for(conn, resource_type, str(resource_id),
                                   window_dt, date_range, exclude_load_id)
    return (not conflicts), conflicts


def resources_availability(resource_type, window, exclude_load_id=None):
    """Every non-deleted resource of a type + its availability for `window`.
    Driver rows carry person_id + hour knobs; vehicle rows carry name/number so
    the caller can render them (drivers still need the Personnel join for a
    display name)."""
    window_dt = load_window(window)
    date_range = _load_date_range(window)
    out = []
    with _connect() as conn:
        if resource_type == "driver":
            rows = conn.execute(
                "SELECT * FROM drivers ORDER BY person_id").fetchall()
            base = [_driver_row(conn, r) for r in rows]
            key = "person_id"
        elif resource_type == "truck":
            rows = conn.execute(
                "SELECT * FROM trucks WHERE deleted=0 ORDER BY name COLLATE NOCASE"
            ).fetchall()
            base = [_vehicle_row(conn, r, "truck", "truck_number") for r in rows]
            key = "id"
        else:
            rows = conn.execute(
                "SELECT * FROM trailers WHERE deleted=0 ORDER BY name COLLATE NOCASE"
            ).fetchall()
            base = [_vehicle_row(conn, r, "trailer", "trailer_number")
                    for r in rows]
            key = "id"
        for res in base:
            rid = res[key]
            conflicts = ([] if not window_dt else
                         _conflicts_for(conn, resource_type, str(rid),
                                        window_dt, date_range, exclude_load_id))
            res["resource_type"] = resource_type
            res["resource_id"] = rid
            res["available"] = not conflicts
            res["conflicts"] = conflicts
            out.append(res)
    return out


def driver_assigned_minutes(person_id, date, exclude_load_id=None):
    """(D3 planning aid) sum of computed load durations already assigned to this
    driver on `date` (day) and in the same ISO week (week). Cancelled/deleted
    loads excluded. Returns {day, week}."""
    day = 0
    week = 0
    target_week = _iso_week(date)
    with _connect() as conn:
        for r in conn.execute(
                """SELECT l.id, l.start_date, l.travel_minutes
                       FROM load_assignments a JOIN loads l ON l.id=a.load_id
                       WHERE a.resource_type='driver' AND a.resource_id=?
                         AND l.deleted=0
                         AND l.status IN ('planned','scheduled','completed')""",
                (str(person_id),)).fetchall():
            if exclude_load_id and r["id"] == exclude_load_id:
                continue
            dur = _int(r["travel_minutes"]) + (conn.execute(
                "SELECT COALESCE(SUM(minutes),0) FROM load_events WHERE load_id=?",
                (r["id"],)).fetchone()[0] or 0)
            if r["start_date"] == date:
                day += dur
            if r["start_date"] and _iso_week(r["start_date"]) == target_week:
                week += dur
    return {"day": day, "week": week}


def _iso_week(date):
    """(year, week) for a YYYY-MM-DD string; '' for a bad date. Uses date math,
    not epoch, so no timezone drift."""
    if not _DATE_RE.match(str(date or "")):
        return ""
    try:
        y, m, d = (int(x) for x in date.split("-"))
        iso = datetime(y, m, d).isocalendar()
        return (iso[0], iso[1])
    except (ValueError, TypeError):
        return ""


# ---- sanitizers ------------------------------------------------------------

def _sanitize_status(value):
    s = str(value or "").strip().lower()
    return s if s in LOAD_STATUSES else "planned"


def _sanitize_assignments(raw):
    """Normalize [{resource_type, resource_id}] — one per (type,id), types
    limited to driver/truck/trailer. Driver ids stay strings (per_*); vehicle
    ids coerce to int."""
    if not isinstance(raw, (list, tuple)):
        return []
    seen, out = set(), []
    for a in raw:
        if not isinstance(a, dict):
            continue
        rtype = str(a.get("resource_type") or "").strip()
        if rtype not in RESOURCE_TYPES:
            continue
        rid_raw = a.get("resource_id")
        if rtype == "driver":
            rid = str(rid_raw or "").strip()
            if not _PERSON_ID_RE.match(rid):
                continue
        else:
            try:
                rid = int(rid_raw)
            except (TypeError, ValueError):
                continue
        keyt = (rtype, str(rid))
        if keyt in seen:
            continue
        seen.add(keyt)
        out.append({"resource_type": rtype, "resource_id": str(rid)})
    return out


def _sanitize_load_events(raw):
    """Normalize a load's ordered event timeline. Each entry snapshots a
    name+minutes (from a picked event_type or hand-entered) so later Settings
    edits never rewrite a planned load."""
    if not isinstance(raw, (list, tuple)):
        return []
    out = []
    for i, e in enumerate(raw):
        if not isinstance(e, dict):
            continue
        name = str(e.get("name") or "").strip()[:120]
        etid = e.get("event_type_id")
        try:
            etid = int(etid) if etid not in (None, "") else None
        except (TypeError, ValueError):
            etid = None
        minutes = _int(e.get("minutes"))
        if minutes < 0:
            minutes = 0
        if not name and etid is None:
            continue
        out.append({"event_type_id": etid, "name": name, "minutes": minutes,
                    "sort": (i + 1) * 10})
    return out


class LoadConflictError(Exception):
    """Raised when a save would double-book an unavailable resource."""

    def __init__(self, conflicts):
        super().__init__("resource conflict")
        self.conflicts = conflicts


def save_load(fields, load_id=None, actor="", revalidate=True):
    """Create or update a load (header + ordered assignments + events). On save,
    re-validate every assigned resource against the load's window (the
    double-booking race guard, D2) unless revalidate=False. Returns
    (load, error). Raises LoadConflictError with a list of
    {resource_type, resource_id, conflicts} when an assignment is unavailable."""
    fields = fields or {}
    name = str(fields.get("name") or "").strip()[:200]
    start_date = str(fields.get("start_date") or "").strip()
    if not _DATE_RE.match(start_date):
        return None, "A start date is required."
    end_date = str(fields.get("end_date") or "").strip()
    if not _DATE_RE.match(end_date) or end_date < start_date:
        end_date = start_date
    start_time = _sanitize_time(fields.get("start_time"))
    end_time = _sanitize_time(fields.get("end_time"))
    status = _sanitize_status(fields.get("status"))
    destination = str(fields.get("destination") or "").strip()[:300]
    cost_notes = str(fields.get("cost_notes") or "").strip()[:1000]
    notes = str(fields.get("notes") or "").strip()[:2000]
    miles = _num_or_none(fields.get("miles"))
    budget = _num_or_none(fields.get("budget_amount"))
    travel = _int(fields.get("travel_minutes"))
    if travel < 0:
        travel = 0
    ltl_id = fields.get("ltl_load_id")
    try:
        ltl_id = int(ltl_id) if ltl_id not in (None, "") else None
    except (TypeError, ValueError):
        ltl_id = None
    assignments = _sanitize_assignments(fields.get("assignments"))
    events = _sanitize_load_events(fields.get("events"))
    window = {"start_date": start_date, "start_time": start_time,
              "end_date": end_date, "end_time": end_time}

    # Race guard: re-validate assignments against the CURRENT store just before
    # writing. A cancelled load never occupies, so cancelling can't conflict.
    if revalidate and assignments and status != "cancelled":
        blocked = []
        for a in assignments:
            ok, conflicts = check_availability(
                a["resource_type"], a["resource_id"], window,
                exclude_load_id=load_id)
            if not ok:
                blocked.append({"resource_type": a["resource_type"],
                                "resource_id": a["resource_id"],
                                "conflicts": conflicts})
        if blocked:
            raise LoadConflictError(blocked)

    now = _now()
    with _write_lock, _connect() as conn:
        if load_id:
            row = conn.execute(
                "SELECT id FROM loads WHERE id=? AND deleted=0",
                (load_id,)).fetchone()
            if not row:
                return None, "Not found."
            conn.execute(
                """UPDATE loads SET name=?, ltl_load_id=?, status=?,
                       start_date=?, start_time=?, end_date=?, end_time=?,
                       destination=?, miles=?, budget_amount=?, cost_notes=?,
                       travel_minutes=?, notes=?, updated_at=? WHERE id=?""",
                (name, ltl_id, status, start_date, start_time, end_date,
                 end_time, destination, miles, budget, cost_notes, travel,
                 notes, now, load_id))
        else:
            cur = conn.execute(
                """INSERT INTO loads(name, ltl_load_id, status, start_date,
                       start_time, end_date, end_time, destination, miles,
                       budget_amount, cost_notes, travel_minutes, notes,
                       deleted, created_at, created_by, updated_at)
                       VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,0,?,?,?)""",
                (name, ltl_id, status, start_date, start_time, end_date,
                 end_time, destination, miles, budget, cost_notes, travel,
                 notes, now, actor, now))
            load_id = cur.lastrowid
        conn.execute("DELETE FROM load_assignments WHERE load_id=?", (load_id,))
        for a in assignments:
            conn.execute(
                """INSERT INTO load_assignments(load_id, resource_type,
                       resource_id) VALUES(?,?,?)""",
                (load_id, a["resource_type"], a["resource_id"]))
        conn.execute("DELETE FROM load_events WHERE load_id=?", (load_id,))
        for e in events:
            conn.execute(
                """INSERT INTO load_events(load_id, event_type_id, name,
                       minutes, sort) VALUES(?,?,?,?,?)""",
                (load_id, e["event_type_id"], e["name"], e["minutes"],
                 e["sort"]))
        conn.commit()
        row = conn.execute("SELECT * FROM loads WHERE id=?", (load_id,)).fetchone()
        return _load_row(conn, row), None


def set_load_status(load_id, status):
    status = _sanitize_status(status)
    with _write_lock, _connect() as conn:
        cur = conn.execute(
            "UPDATE loads SET status=?, updated_at=? WHERE id=? AND deleted=0",
            (status, _now(), load_id))
        conn.commit()
        if cur.rowcount == 0:
            return None
        return get_load(load_id)


def delete_load(load_id):
    with _write_lock, _connect() as conn:
        cur = conn.execute(
            "UPDATE loads SET deleted=1, updated_at=? WHERE id=? AND deleted=0",
            (_now(), load_id))
        conn.commit()
        return cur.rowcount > 0


# ---- LTL built loads (DVI-1399 P3) -----------------------------------------
# A "built load" is a named, free-text-noted load template produced by the LTL
# Load Builder. Load Manager -> Add Load references one by id (loads.ltl_load_id)
# so a coordinator can turn a built load into a scheduled delivery. NetSuite
# product wiring is a gated later phase; a built load is just name + notes now.

def _ltl_row(row):
    d = dict(row)
    d["deleted"] = bool(d.get("deleted", 0))
    return d


def list_ltl_loads(include_deleted=False):
    with _connect() as conn:
        q = "SELECT * FROM ltl_loads"
        if not include_deleted:
            q += " WHERE deleted=0"
        q += " ORDER BY LOWER(name), id DESC"
        return [_ltl_row(r) for r in conn.execute(q).fetchall()]


def get_ltl_load(ltl_id, include_deleted=False):
    with _connect() as conn:
        q = "SELECT * FROM ltl_loads WHERE id=?"
        if not include_deleted:
            q += " AND deleted=0"
        row = conn.execute(q, (ltl_id,)).fetchone()
        return _ltl_row(row) if row else None


def save_ltl_load(fields, ltl_id=None, actor=""):
    """Create or update a built load. Returns (ltl_load, error)."""
    fields = fields or {}
    name = str(fields.get("name") or "").strip()[:200]
    if not name:
        return None, "A load name is required."
    notes = str(fields.get("notes") or "").strip()[:4000]
    now = _now()
    with _write_lock, _connect() as conn:
        if ltl_id:
            row = conn.execute(
                "SELECT id FROM ltl_loads WHERE id=? AND deleted=0",
                (ltl_id,)).fetchone()
            if not row:
                return None, "Not found."
            conn.execute(
                "UPDATE ltl_loads SET name=?, notes=?, updated_at=? WHERE id=?",
                (name, notes, now, ltl_id))
        else:
            cur = conn.execute(
                """INSERT INTO ltl_loads(name, notes, deleted, created_at,
                       created_by, updated_at) VALUES(?,?,0,?,?,?)""",
                (name, notes, now, actor, now))
            ltl_id = cur.lastrowid
        conn.commit()
        row = conn.execute(
            "SELECT * FROM ltl_loads WHERE id=?", (ltl_id,)).fetchone()
        return _ltl_row(row), None


def delete_ltl_load(ltl_id):
    """Soft-delete a built load. Loads that already reference it keep their
    ltl_load_id (the name is resolved via get_ltl_load(..., include_deleted=True)
    for display) — deleting a built load never rewrites a planned load."""
    with _write_lock, _connect() as conn:
        cur = conn.execute(
            "UPDATE ltl_loads SET deleted=1, updated_at=? WHERE id=? AND deleted=0",
            (_now(), ltl_id))
        conn.commit()
        return cur.rowcount > 0