"""
Canteen Asset Tracker — Admin Server.

Standalone FastAPI server for admin-only operations.
Uses the same SQLite database as the main canteen-asset-tracker server
(via CANTEEN_DB_PATH env var), sharing assets.db for all CRUD operations
on users, customers, locations, rooms, geofences, settings, activity log,
stats, CSV exports, and database management.

Designed to run on a separate port from the main field-worker server.
"""
import csv
import hashlib
import httpx
import io
import json as _json
import os
import secrets
import sqlite3
import uuid
from contextlib import asynccontextmanager
from pathlib import Path
from typing import Optional

from fastapi import FastAPI, HTTPException, Query, Request, UploadFile, File
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import FileResponse, JSONResponse, StreamingResponse
from fastapi.staticfiles import StaticFiles
from pydantic import BaseModel

from cantaloupe_sync import router as cantaloupe_router, create_sync_tables
from exif_module import router as exif_router, create_exif_tables

# ─── Config ─────────────────────────────────────────────────────────────────

DB_PATH = os.environ.get("CANTEEN_DB_PATH", str(Path(__file__).parent / "assets.db"))
UPLOADS_DIR = Path(os.environ.get("CANTEEN_UPLOADS_DIR", str(Path(__file__).parent / ".." / "canteen-asset-tracker" / "uploads")))
ICON_MAX_SIZE = 2 * 1024 * 1024       # 2 MB
ICON_ALLOWED_EXTS = {".png", ".jpg", ".jpeg", ".svg"}
PHOTO_ALLOWED_EXTS = {".png", ".jpg", ".jpeg"}

def _load_categories() -> set:
    """Load valid category names from the categories lookup table."""
    try:
        conn = sqlite3.connect(DB_PATH)
        names = {r[0] for r in conn.execute("SELECT name FROM categories").fetchall()}
        conn.close()
        return names if names else {"Other"}
    except Exception:
        return {"Furniture", "Appliances", "Utensils & Serveware", "Equipment", "Other"}

VALID_CATEGORIES = _load_categories()
VALID_STATUSES = {"active", "maintenance", "retired"}
VALID_ROLES = {"admin", "technician", "readonly"}
# Helpers for comma-separated multi-role support
def roles_contain(role_str: str, target: str) -> bool:
    """Check if a comma-separated role string includes target."""
    return target in [r.strip() for r in (role_str or "").split(",")]

def validate_roles(role_str: str) -> str:
    """Accept comma-separated role combos. Normalise: strip whitespace, dedupe, sort."""
    parts = [r.strip().lower() for r in role_str.split(",")]
    parts = [r for r in parts if r]  # drop empties
    if not parts:
        raise HTTPException(status_code=422, detail="At least one role is required")
    invalid = [r for r in parts if r not in VALID_ROLES]
    if invalid:
        raise HTTPException(
            status_code=422,
            detail=f"Invalid role(s): {', '.join(invalid)}. Must be one of: {', '.join(sorted(VALID_ROLES))}",
        )
    return ",".join(sorted(set(parts)))


# ─── Database ───────────────────────────────────────────────────────────────


def get_db() -> sqlite3.Connection:
    """Return a new DB connection with WAL + foreign keys enabled."""
    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    conn.row_factory = sqlite3.Row
    return conn


def _create_tables(conn: sqlite3.Connection):
    """Create all tables if they don't exist (same schema as main server)."""
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS users (
            id              INTEGER PRIMARY KEY AUTOINCREMENT,
            username        TEXT    UNIQUE NOT NULL,
            password_hash   TEXT    NOT NULL,
            role            TEXT    NOT NULL DEFAULT 'technician',
            created_at      TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS customers (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            name        TEXT    UNIQUE NOT NULL,
            created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
            updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS customer_contacts (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
            name        TEXT,
            phone       TEXT,
            email       TEXT
        );

        CREATE TABLE IF NOT EXISTS locations (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id         INTEGER REFERENCES customers(id),
            name                TEXT,
            address             TEXT    DEFAULT '',
            building_name       TEXT    DEFAULT '',
            building_number     TEXT    DEFAULT '',
            floor               TEXT    DEFAULT '',
            trailer_number      TEXT    DEFAULT '',
            site_hours          TEXT    DEFAULT '',
            access_notes        TEXT    DEFAULT '',
            walking_directions  TEXT    DEFAULT '',
            map_link            TEXT    DEFAULT '',
            latitude            REAL    DEFAULT NULL,
            longitude           REAL    DEFAULT NULL,
            created_at          TEXT    NOT NULL DEFAULT (datetime('now')),
            updated_at          TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS rooms (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            location_id INTEGER NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
            name        TEXT,
            floor       TEXT    DEFAULT '',
            created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
            updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS categories (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT    UNIQUE NOT NULL,
            icon    TEXT    DEFAULT ''
        );

        CREATE TABLE IF NOT EXISTS key_names (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT    UNIQUE NOT NULL
        );

        CREATE TABLE IF NOT EXISTS key_types (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT    UNIQUE NOT NULL
        );

        CREATE TABLE IF NOT EXISTS badge_types (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT    UNIQUE NOT NULL
        );

        CREATE TABLE IF NOT EXISTS makes (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT    UNIQUE NOT NULL
        );

        CREATE TABLE IF NOT EXISTS models (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            make_id     INTEGER NOT NULL REFERENCES makes(id),
            name        TEXT    NOT NULL,
            icon_path   TEXT
        );

        CREATE TABLE IF NOT EXISTS assets (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            machine_id          TEXT    NOT NULL UNIQUE,
            serial_number       TEXT    DEFAULT '',
            name                TEXT    NOT NULL,
            description         TEXT    DEFAULT '',
            category            TEXT    NOT NULL DEFAULT 'Other',
            status              TEXT    NOT NULL DEFAULT 'active',
            make                TEXT    DEFAULT '',
            model               TEXT    DEFAULT '',
            address             TEXT    DEFAULT '',
            building_name       TEXT    DEFAULT '',
            building_number     TEXT    DEFAULT '',
            floor               TEXT    DEFAULT '',
            room                TEXT    DEFAULT '',
            trailer_number      TEXT    DEFAULT '',
            walking_directions  TEXT    DEFAULT '',
            map_link            TEXT    DEFAULT '',
            parking_location    TEXT    DEFAULT '',
            photo_path          TEXT,
            customer_id         INTEGER REFERENCES customers(id),
            location_id         INTEGER REFERENCES locations(id),
            assigned_to         INTEGER REFERENCES users(id),
            latitude            REAL    DEFAULT NULL,
            longitude           REAL    DEFAULT NULL,
            geofence_radius_meters INTEGER DEFAULT 50,
            created_at          TEXT    NOT NULL DEFAULT (datetime('now')),
            updated_at          TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS checkins (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            asset_id    INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
            user_id     INTEGER REFERENCES users(id),
            latitude    REAL,
            longitude   REAL,
            accuracy    REAL,
            photo_path  TEXT,
            notes       TEXT    DEFAULT '',
            created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS asset_keys (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            asset_id    INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
            key_name    TEXT,
            key_type    TEXT
        );

        CREATE TABLE IF NOT EXISTS asset_badges (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            asset_id    INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
            badge_name  TEXT
        );

        CREATE TABLE IF NOT EXISTS settings (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            key     TEXT    UNIQUE NOT NULL,
            value   TEXT
        );

        CREATE TABLE IF NOT EXISTS activity_log (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id     INTEGER REFERENCES users(id),
            action      TEXT,
            entity_type TEXT,
            entity_id   INTEGER,
            details     TEXT,
            created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS geofences (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            name        TEXT,
            points      TEXT,
            color       TEXT,
            created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
            updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS geofence_users (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            geofence_id INTEGER NOT NULL REFERENCES geofences(id) ON DELETE CASCADE,
            user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            UNIQUE(geofence_id, user_id)
        );

        CREATE TABLE IF NOT EXISTS visits (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id             INTEGER REFERENCES users(id),
            asset_id            INTEGER REFERENCES assets(id) ON DELETE CASCADE,
            checkin_time        TEXT,
            checkout_time       TEXT,
            duration_minutes    INTEGER,
            created_at          TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS sessions (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id     INTEGER REFERENCES users(id) ON DELETE CASCADE,
            token       TEXT    UNIQUE NOT NULL,
            created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS cantaloupe_sync_batches (
            id              INTEGER PRIMARY KEY AUTOINCREMENT,
            status          TEXT    NOT NULL DEFAULT 'pending',
            created_at      TEXT    NOT NULL DEFAULT (datetime('now')),
            approved_at     TEXT,
            file_path       TEXT,
            row_count       INTEGER DEFAULT 0,
            diff_summary    TEXT,
            raw_data        TEXT,
            error_message   TEXT
        );

        CREATE INDEX IF NOT EXISTS idx_checkins_asset_id ON checkins(asset_id);
        CREATE INDEX IF NOT EXISTS idx_checkins_created_at ON checkins(created_at);
        CREATE INDEX IF NOT EXISTS idx_assets_category ON assets(category);
        CREATE INDEX IF NOT EXISTS idx_assets_machine_id ON assets(machine_id);
        CREATE INDEX IF NOT EXISTS idx_csb_status ON cantaloupe_sync_batches(status);
        CREATE INDEX IF NOT EXISTS idx_csb_created ON cantaloupe_sync_batches(created_at);
    """)


def _seed_if_empty(conn: sqlite3.Connection, table: str, columns: tuple, rows: list):
    """Insert seed rows if table is empty."""
    existing = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    if existing == 0:
        placeholders = ", ".join(["?"] * len(columns))
        col_names = ", ".join(columns)
        conn.executemany(
            f"INSERT INTO {table} ({col_names}) VALUES ({placeholders})",
            rows,
        )


def _seed_data(conn: sqlite3.Connection):
    """Insert default seed data for lookup tables."""
    _seed_if_empty(conn, "categories", ("name", "icon"), [
        ("Furniture", "🪑"), ("Appliances", "🔌"),
        ("Utensils & Serveware", "🍽️"), ("Equipment", "⚙️"), ("Other", "📦"),
    ])
    _seed_if_empty(conn, "key_names", ("name",), [
        ("MK500",), ("Green Dot",), ("Red Key",), ("Blue Key",),
        ("Master Key",), ("Padlock Key",),
    ])
    _seed_if_empty(conn, "key_types", ("name",), [
        ("Round Short",), ("Barrel",), ("Standard",), ("Flat",), ("Tubular",),
    ])
    _seed_if_empty(conn, "badge_types", ("name",), [
        ("Disney Contractor Base",), ("Visitor Badge",), ("Employee Badge",),
        ("Contractor Badge",), ("Temporary Pass",),
    ])
    _seed_if_empty(conn, "makes", ("name",), [
        ("Canteen",), ("Hobart",), ("Vollrath",), ("Metro",),
        ("Rubbermaid",), ("Cambro",), ("Other",),
    ])
    # Ensure default admin user exists
    conn.execute(
        "INSERT OR IGNORE INTO users (username, password_hash, role) VALUES (?, ?, ?)",
        ("admin", "057ba03d6c44104863dc7361fe4578965d1887360f90a0895882e58a6248fc86", "admin"),
    )


def init_db(conn: sqlite3.Connection):
    """Ensure all tables exist, run migrations if needed, seed default data."""
    _create_tables(conn)
    create_sync_tables(conn)
    _seed_data(conn)
    # Add lat/lng columns if missing (v3 migration)
    cursor = conn.execute("PRAGMA table_info(assets)")
    asset_cols = {row[1] for row in cursor.fetchall()}
    if "latitude" not in asset_cols:
        conn.execute("ALTER TABLE assets ADD COLUMN latitude REAL DEFAULT NULL")
    if "longitude" not in asset_cols:
        conn.execute("ALTER TABLE assets ADD COLUMN longitude REAL DEFAULT NULL")
    if "geofence_radius_meters" not in asset_cols:
        conn.execute("ALTER TABLE assets ADD COLUMN geofence_radius_meters INTEGER DEFAULT 50")
    # v4 migration: dex_report_date, deployed, pulled_date
    if "dex_report_date" not in asset_cols:
        conn.execute("ALTER TABLE assets ADD COLUMN dex_report_date TEXT DEFAULT NULL")
    if "deployed" not in asset_cols:
        conn.execute("ALTER TABLE assets ADD COLUMN deployed TEXT DEFAULT NULL")
    if "pulled_date" not in asset_cols:
        conn.execute("ALTER TABLE assets ADD COLUMN pulled_date TEXT DEFAULT NULL")
    # v5 migration: install_date
    if "install_date" not in asset_cols:
        conn.execute("ALTER TABLE assets ADD COLUMN install_date TEXT DEFAULT NULL")
    cursor = conn.execute("PRAGMA table_info(locations)")
    loc_cols = {row[1] for row in cursor.fetchall()}
    if "latitude" not in loc_cols:
        conn.execute("ALTER TABLE locations ADD COLUMN latitude REAL DEFAULT NULL")
    if "longitude" not in loc_cols:
        conn.execute("ALTER TABLE locations ADD COLUMN longitude REAL DEFAULT NULL")
    conn.commit()


# ─── App / Middleware ───────────────────────────────────────────────────────


@asynccontextmanager
async def lifespan(app: FastAPI):
    UPLOADS_DIR.mkdir(parents=True, exist_ok=True)
    conn = get_db()
    init_db(conn)
    conn.close()
    create_exif_tables()
    yield


app = FastAPI(title="Canteen Asset Tracker — Admin API", lifespan=lifespan)

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# Mount Cantaloupe sync routes
app.include_router(cantaloupe_router)
app.include_router(exif_router)

# ─── Auth Middleware ────────────────────────────────────────────────────────


@app.middleware("http")
async def auth_middleware(request: Request, call_next):
    """Require valid Bearer token for all /api/* routes except login."""
    path = request.url.path

    if os.environ.get("CANTEEN_SKIP_AUTH") == "1":
        return await call_next(request)

    if not path.startswith("/api/") or path == "/api/auth/login" or path.startswith("/api/lookup-business") or path.startswith("/api/admin/reports/"):
        return await call_next(request)

    auth_header = request.headers.get("Authorization", "")
    if not auth_header.startswith("Bearer "):
        return JSONResponse(
            status_code=401,
            content={"detail": "Authentication required"},
        )

    token = auth_header[7:]
    conn = get_db()
    row = conn.execute(
        "SELECT u.id, u.username, u.role FROM users u "
        "JOIN sessions s ON u.id = s.user_id WHERE s.token = ? AND s.expires_at > datetime('now')",
        (token,),
    ).fetchone()
    conn.close()

    if row is None:
        return JSONResponse(
            status_code=401,
            content={"detail": "Invalid or expired token"},
        )

    request.state.current_user = {
        "id": row["id"],
        "username": row["username"],
        "role": row["role"],
    }
    request.state.user_id = row["id"]

    return await call_next(request)


# ─── Global Error Handling ──────────────────────────────────────────────────


@app.exception_handler(HTTPException)
async def http_exception_handler(request: Request, exc: HTTPException):
    return JSONResponse(
        status_code=exc.status_code,
        content={"detail": exc.detail},
    )


@app.exception_handler(Exception)
async def generic_exception_handler(request: Request, exc: Exception):
    import traceback
    traceback.print_exc()
    return JSONResponse(
        status_code=500,
        content={"detail": "Internal server error"},
    )


# ─── Helper functions ──────────────────────────────────────────────────────


def _hash_password(password: str) -> str:
    return hashlib.sha256(password.encode()).hexdigest()


def _generate_token() -> str:
    return secrets.token_hex(32)


def row_to_dict(row: sqlite3.Row) -> dict:
    return dict(row)


def _user_to_dict(row: sqlite3.Row) -> dict:
    d = row_to_dict(row)
    d.pop("password_hash", None)
    return d


def _log_activity(conn: sqlite3.Connection, action: str, entity_type: str,
                  entity_id: int, details: str = "", user_id: int = None):
    conn.execute(
        "INSERT INTO activity_log (user_id, action, entity_type, entity_id, details) "
        "VALUES (?, ?, ?, ?, ?)",
        (user_id, action, entity_type, entity_id, details),
    )


def _validate_ref(conn: sqlite3.Connection, table: str, id_val: int, label: str):
    if id_val is not None:
        row = conn.execute(f"SELECT id FROM {table} WHERE id = ?", (id_val,)).fetchone()
        if row is None:
            raise HTTPException(status_code=422, detail=f"{label} with id {id_val} not found")


def _validate_enum_table(conn: sqlite3.Connection, table: str, value: str, label: str):
    if value:
        row = conn.execute(
            f"SELECT id FROM {table} WHERE name = ?", (value,)
        ).fetchone()
        if row is None:
            raise HTTPException(
                status_code=422,
                detail=f"Invalid {label} '{value}'. Must exist in {table} table.",
            )


def _geofence_row(row: sqlite3.Row, conn: Optional[sqlite3.Connection] = None) -> dict:
    """Serialize a geofence row, parsing points JSON and including assigned users."""
    d = dict(row)
    if isinstance(d.get("points"), str):
        try:
            d["points"] = _json.loads(d["points"])
        except (_json.JSONDecodeError, TypeError):
            pass
    close_conn = False
    if conn is None:
        conn = get_db()
        close_conn = True
    try:
        user_rows = conn.execute(
            """SELECT u.id, u.username, u.role FROM geofence_users gu
               JOIN users u ON u.id = gu.user_id
               WHERE gu.geofence_id = ? ORDER BY u.username""",
            (d["id"],),
        ).fetchall()
        d["assigned_users"] = [dict(r) for r in user_rows]
    finally:
        if close_conn:
            conn.close()
    return d


def _sync_geofence_users(conn: sqlite3.Connection, geofence_id: int, user_ids: list[int]):
    """Replace assigned users for a geofence with the given list of user IDs."""
    if user_ids:
        placeholders = ", ".join(["?"] * len(user_ids))
        existing = conn.execute(
            f"SELECT id FROM users WHERE id IN ({placeholders})",
            user_ids,
        ).fetchall()
        existing_ids = {r["id"] for r in existing}
        for uid in user_ids:
            if uid not in existing_ids:
                raise HTTPException(
                    status_code=422,
                    detail=f"User with id {uid} not found",
                )

    conn.execute("DELETE FROM geofence_users WHERE geofence_id = ?", (geofence_id,))
    for uid in user_ids:
        conn.execute(
            "INSERT INTO geofence_users (geofence_id, user_id) VALUES (?, ?)",
            (geofence_id, uid),
        )


def _get_customer_contacts(conn: sqlite3.Connection, cust_id: int) -> list:
    rows = conn.execute(
        "SELECT id, customer_id, name, phone, email FROM customer_contacts WHERE customer_id = ?",
        (cust_id,),
    ).fetchall()
    return [row_to_dict(r) for r in rows]


def _get_location_rooms(conn: sqlite3.Connection, loc_id: int) -> list:
    rows = conn.execute(
        "SELECT id, location_id, name, floor, created_at, updated_at FROM rooms WHERE location_id = ? ORDER BY name",
        (loc_id,),
    ).fetchall()
    return [row_to_dict(r) for r in rows]


def _save_upload(upload: UploadFile, subdir: str, allowed_exts: set, max_size: int) -> str:
    """Save uploaded file to uploads/{subdir}/ with a UUID filename."""
    ext = Path(upload.filename or "").suffix.lower()
    if not ext or ext not in allowed_exts:
        allowed = ", ".join(sorted(allowed_exts))
        raise HTTPException(400, f"Invalid file type. Allowed: {allowed}")

    contents = upload.file.read()
    if len(contents) > max_size:
        mb = max_size // (1024 * 1024)
        raise HTTPException(413, f"File too large. Maximum size: {mb} MB")

    dest_dir = UPLOADS_DIR / subdir
    dest_dir.mkdir(parents=True, exist_ok=True)
    fname = f"{uuid.uuid4().hex}{ext}"
    (dest_dir / fname).write_bytes(contents)

    return f"/uploads/{subdir}/{fname}"


# ─── Pydantic Models ────────────────────────────────────────────────────────


class CustomerContact(BaseModel):
    name: Optional[str] = ""
    phone: Optional[str] = ""
    email: Optional[str] = ""


class CustomerCreate(BaseModel):
    name: str
    contacts: Optional[list[CustomerContact]] = []


class CustomerUpdate(BaseModel):
    name: Optional[str] = None
    contacts: Optional[list[CustomerContact]] = None


class LocationCreate(BaseModel):
    customer_id: Optional[int] = None
    name: str
    address: Optional[str] = ""
    building_name: Optional[str] = ""
    building_number: Optional[str] = ""
    floor: Optional[str] = ""
    trailer_number: Optional[str] = ""
    site_hours: Optional[str] = ""
    access_notes: Optional[str] = ""
    walking_directions: Optional[str] = ""
    map_link: Optional[str] = ""
    latitude: Optional[float] = None
    longitude: Optional[float] = None


class LocationUpdate(BaseModel):
    customer_id: Optional[int] = None
    name: Optional[str] = None
    address: Optional[str] = None
    building_name: Optional[str] = None
    building_number: Optional[str] = None
    floor: Optional[str] = None
    trailer_number: Optional[str] = None
    site_hours: Optional[str] = None
    access_notes: Optional[str] = None
    walking_directions: Optional[str] = None
    map_link: Optional[str] = None
    latitude: Optional[float] = None
    longitude: Optional[float] = None


class RoomCreate(BaseModel):
    location_id: int
    name: str
    floor: Optional[str] = ""


class RoomUpdate(BaseModel):
    name: Optional[str] = None
    floor: Optional[str] = None
    location_id: Optional[int] = None


class UserCreate(BaseModel):
    username: str
    password: str
    role: Optional[str] = "technician"


class UserUpdate(BaseModel):
    role: Optional[str] = None
    password: Optional[str] = None


class GeofenceCreate(BaseModel):
    name: str
    points: list
    color: Optional[str] = "#3388ff"
    user_ids: Optional[list[int]] = None


class GeofenceUpdate(BaseModel):
    name: Optional[str] = None
    points: Optional[list] = None
    color: Optional[str] = None
    user_ids: Optional[list[int]] = None


# ─── Health ─────────────────────────────────────────────────────────────────


@app.get("/health")
def health():
    return {"status": "ok"}


# ─── Users API ──────────────────────────────────────────────────────────────


@app.post("/api/users", status_code=201)
def create_user(body: UserCreate):
    username = body.username.strip()
    if not username:
        raise HTTPException(status_code=422, detail="Username must not be empty")
    password = body.password
    if not password:
        raise HTTPException(status_code=422, detail="Password must not be empty")
    role = validate_roles(body.role or "technician")

    conn = get_db()
    password_hash = _hash_password(password)
    try:
        cursor = conn.execute(
            "INSERT INTO users (username, password_hash, role) VALUES (?, ?, ?)",
            (username, password_hash, role),
        )
        conn.commit()
        uid = cursor.lastrowid
        _log_activity(conn, "created", "user", uid, f"User '{username}' created")
        conn.commit()
    except sqlite3.IntegrityError:
        conn.close()
        raise HTTPException(status_code=409, detail=f"User '{username}' already exists")

    row = conn.execute("SELECT * FROM users WHERE id = ?", (uid,)).fetchone()
    conn.close()
    return _user_to_dict(row)


@app.get("/api/users")
def list_users():
    conn = get_db()
    rows = conn.execute("SELECT * FROM users ORDER BY username").fetchall()
    conn.close()
    return [_user_to_dict(r) for r in rows]


@app.get("/api/users/{user_id}")
def get_user(user_id: int):
    conn = get_db()
    row = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
    if row is None:
        conn.close()
        raise HTTPException(status_code=404, detail="User not found")
    conn.close()
    return _user_to_dict(row)


@app.put("/api/users/{user_id}")
def update_user(user_id: int, body: UserUpdate):
    conn = get_db()
    existing = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="User not found")

    if body.role is not None:
        role = validate_roles(body.role)
        conn.execute("UPDATE users SET role = ? WHERE id = ?", (role, user_id))
        _log_activity(conn, "updated", "user", user_id,
                      f"User '{existing['username']}' role changed to '{role}'")
        conn.commit()

    if body.password is not None:
        pw = body.password.strip()
        if not pw:
            conn.close()
            raise HTTPException(status_code=422, detail="Password must not be empty")
        password_hash = _hash_password(pw)
        conn.execute("UPDATE users SET password_hash = ? WHERE id = ?", (password_hash, user_id))
        _log_activity(conn, "updated", "user", user_id,
                      f"User '{existing['username']}' password changed")
        conn.commit()

    conn.commit()
    row = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
    conn.close()
    return _user_to_dict(row)


@app.delete("/api/users/{user_id}", status_code=204)
def delete_user(user_id: int):
    conn = get_db()
    existing = conn.execute("SELECT id, username FROM users WHERE id = ?", (user_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="User not found")
    conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
    conn.commit()
    _log_activity(conn, "deleted", "user", user_id,
                  f"User '{existing['username']}' deleted")
    conn.commit()
    conn.close()


@app.get("/api/users/{user_id}/geofences")
def list_user_geofences(user_id: int):
    """List geofences assigned to a user (their service areas)."""
    conn = get_db()
    existing = conn.execute("SELECT id FROM users WHERE id = ?", (user_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="User not found")
    rows = conn.execute(
        """SELECT g.* FROM geofences g
           JOIN geofence_users gu ON gu.geofence_id = g.id
           WHERE gu.user_id = ? ORDER BY g.name""",
        (user_id,),
    ).fetchall()
    result = [_geofence_row(r, conn) for r in rows]
    conn.close()
    return result


# ─── Login (needed for token-based access) ──────────────────────────────────


@app.post("/api/auth/login")
def login(body: dict):
    """Authenticate a user and return a session token."""
    username = body.get("username", "")
    password = body.get("password", "")
    conn = get_db()
    row = conn.execute(
        "SELECT * FROM users WHERE username = ?", (username,)
    ).fetchone()
    if row is None:
        conn.close()
        raise HTTPException(status_code=401, detail="Invalid username or password")

    password_hash = _hash_password(password)
    if password_hash != row["password_hash"]:
        conn.close()
        raise HTTPException(status_code=401, detail="Invalid username or password")

    token = _generate_token()
    remember_me = body.get("remember_me", False)
    expiry = "+30 days" if remember_me else "+1 day"
    conn.execute(
        "INSERT INTO sessions (user_id, token, expires_at) VALUES (?, ?, datetime('now', ?))",
        (row["id"], token, expiry),
    )
    conn.commit()
    _log_activity(conn, "login", "user", row["id"],
                  f"User '{username}' logged in")
    conn.commit()
    conn.close()

    result = _user_to_dict(row)
    result["token"] = token
    return result


# ─── Auth: Me (validate token) ───────────────────────────────────────────────


@app.get("/api/auth/me")
def auth_me(request: Request):
    """Return the current authenticated user from the Authorization header."""
    auth_header = request.headers.get("Authorization", "")
    if not auth_header.startswith("Bearer "):
        raise HTTPException(status_code=401, detail="Missing or invalid Authorization header")
    token = auth_header[7:]
    conn = get_db()
    row = conn.execute(
        "SELECT u.* FROM users u JOIN sessions s ON u.id = s.user_id WHERE s.token = ? AND s.expires_at > datetime('now')",
        (token,),
    ).fetchone()
    conn.close()
    if row is None:
        raise HTTPException(status_code=401, detail="Invalid or expired token")
    return _user_to_dict(row)


# ─── Customers API ──────────────────────────────────────────────────────────


@app.post("/api/customers", status_code=201)
def create_customer(body: CustomerCreate):
    name = body.name.strip()
    if not name:
        raise HTTPException(status_code=422, detail="Customer name must not be empty")
    conn = get_db()
    try:
        cursor = conn.execute("INSERT INTO customers (name) VALUES (?)", (name,))
        cust_id = cursor.lastrowid
        if body.contacts:
            for c in body.contacts:
                conn.execute(
                    "INSERT INTO customer_contacts (customer_id, name, phone, email) VALUES (?, ?, ?, ?)",
                    (cust_id, c.name or "", c.phone or "", c.email or ""),
                )
        conn.commit()
        _log_activity(conn, "created", "customer", cust_id,
                      f"Customer '{name}' created")
        conn.commit()
    except sqlite3.IntegrityError:
        conn.close()
        raise HTTPException(status_code=409, detail=f"Customer '{name}' already exists")

    row = conn.execute("SELECT * FROM customers WHERE id = ?", (cust_id,)).fetchone()
    result = row_to_dict(row)
    result["contacts"] = _get_customer_contacts(conn, cust_id)
    conn.close()
    return result


@app.get("/api/customers")
def list_customers():
    conn = get_db()
    rows = conn.execute("SELECT * FROM customers ORDER BY name").fetchall()
    result = []
    for r in rows:
        d = row_to_dict(r)
        d["contacts"] = _get_customer_contacts(conn, r["id"])
        result.append(d)
    conn.close()
    return result


@app.get("/api/customers/{cust_id}")
def get_customer(cust_id: int):
    conn = get_db()
    row = conn.execute("SELECT * FROM customers WHERE id = ?", (cust_id,)).fetchone()
    if row is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Customer not found")
    result = row_to_dict(row)
    result["contacts"] = _get_customer_contacts(conn, cust_id)
    conn.close()
    return result


@app.put("/api/customers/{cust_id}")
def update_customer(cust_id: int, body: CustomerUpdate):
    conn = get_db()
    existing = conn.execute("SELECT id FROM customers WHERE id = ?", (cust_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Customer not found")

    if body.name is not None:
        name = body.name.strip()
        if not name:
            conn.close()
            raise HTTPException(status_code=422, detail="Customer name must not be empty")
        conn.execute(
            "UPDATE customers SET name = ?, updated_at = datetime('now') WHERE id = ?",
            (name, cust_id),
        )

    if body.contacts is not None:
        conn.execute("DELETE FROM customer_contacts WHERE customer_id = ?", (cust_id,))
        for c in body.contacts:
            conn.execute(
                "INSERT INTO customer_contacts (customer_id, name, phone, email) VALUES (?, ?, ?, ?)",
                (cust_id, c.name or "", c.phone or "", c.email or ""),
            )

    conn.commit()
    row = conn.execute("SELECT * FROM customers WHERE id = ?", (cust_id,)).fetchone()
    result = row_to_dict(row)
    result["contacts"] = _get_customer_contacts(conn, cust_id)
    _log_activity(conn, "updated", "customer", cust_id,
                  f"Customer '{result['name']}' updated")
    conn.commit()
    conn.close()
    return result


@app.delete("/api/customers/{cust_id}", status_code=204)
def delete_customer(cust_id: int):
    conn = get_db()
    existing = conn.execute("SELECT id FROM customers WHERE id = ?", (cust_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Customer not found")
    conn.execute("DELETE FROM customers WHERE id = ?", (cust_id,))
    _log_activity(conn, "deleted", "customer", cust_id,
                  f"Customer {cust_id} deleted")
    conn.commit()
    conn.close()


# ─── Locations API ──────────────────────────────────────────────────────────


@app.post("/api/locations", status_code=201)
def create_location(body: LocationCreate):
    name = body.name.strip()
    if not name:
        raise HTTPException(status_code=422, detail="Location name must not be empty")
    conn = get_db()
    if body.customer_id is not None:
        _validate_ref(conn, "customers", body.customer_id, "Customer")

    cursor = conn.execute(
        """INSERT INTO locations (customer_id, name, address, building_name, building_number,
           floor, trailer_number, site_hours, access_notes, walking_directions, map_link,
           latitude, longitude)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (body.customer_id, name, body.address or "", body.building_name or "",
         body.building_number or "", body.floor or "", body.trailer_number or "",
         body.site_hours or "", body.access_notes or "",
         body.walking_directions or "", body.map_link or "",
         body.latitude, body.longitude),
    )
    conn.commit()
    loc_id = cursor.lastrowid
    _log_activity(conn, "created", "location", loc_id,
                  f"Location '{name}' created")
    conn.commit()
    row = conn.execute("SELECT * FROM locations WHERE id = ?", (loc_id,)).fetchone()
    result = row_to_dict(row)
    result["rooms"] = []
    conn.close()
    return result


@app.get("/api/locations")
def list_locations(customer_id: Optional[int] = Query(None)):
    conn = get_db()
    if customer_id is not None:
        rows = conn.execute(
            "SELECT * FROM locations WHERE customer_id = ? ORDER BY name",
            (customer_id,),
        ).fetchall()
    else:
        rows = conn.execute("SELECT * FROM locations ORDER BY name").fetchall()
    result = []
    for r in rows:
        d = row_to_dict(r)
        d["rooms"] = _get_location_rooms(conn, r["id"])
        result.append(d)
    conn.close()
    return result


@app.get("/api/locations/{loc_id}")
def get_location(loc_id: int):
    conn = get_db()
    row = conn.execute("SELECT * FROM locations WHERE id = ?", (loc_id,)).fetchone()
    if row is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Location not found")
    result = row_to_dict(row)
    result["rooms"] = _get_location_rooms(conn, loc_id)
    conn.close()
    return result


_LOCATION_FIELDS = [
    "customer_id", "name", "address", "building_name", "building_number",
    "floor", "trailer_number", "site_hours", "access_notes",
    "walking_directions", "map_link", "latitude", "longitude",
]


@app.put("/api/locations/{loc_id}")
def update_location(loc_id: int, body: LocationUpdate):
    conn = get_db()
    existing = conn.execute("SELECT id, name FROM locations WHERE id = ?", (loc_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Location not found")

    updates = {}
    for field in _LOCATION_FIELDS:
        val = getattr(body, field, None)
        if val is not None:
            if field == "name":
                name = val.strip()
                if not name:
                    conn.close()
                    raise HTTPException(status_code=422, detail="Location name must not be empty")
                updates[field] = name
            elif field == "customer_id":
                if val is not None:
                    _validate_ref(conn, "customers", val, "Customer")
                updates[field] = val
            elif field in ("latitude", "longitude"):
                updates[field] = val
            else:
                updates[field] = val or ""

    if updates:
        updates["updated_at"] = "datetime('now')"
        set_clause = ", ".join(
            f"{k} = {v}" if k == "updated_at" else f"{k} = ?"
            for k, v in updates.items()
        )
        values = [v for k, v in updates.items() if k != "updated_at"]
        conn.execute(
            f"UPDATE locations SET {set_clause} WHERE id = ?",
            values + [loc_id],
        )
        conn.commit()
        _log_activity(conn, "updated", "location", loc_id,
                      f"Location '{existing['name']}' updated")
        conn.commit()

    row = conn.execute("SELECT * FROM locations WHERE id = ?", (loc_id,)).fetchone()
    result = row_to_dict(row)
    result["rooms"] = _get_location_rooms(conn, loc_id)
    conn.close()
    return result


@app.delete("/api/locations/{loc_id}", status_code=204)
def delete_location(loc_id: int):
    conn = get_db()
    existing = conn.execute("SELECT id, name FROM locations WHERE id = ?", (loc_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Location not found")
    conn.execute("DELETE FROM locations WHERE id = ?", (loc_id,))
    conn.commit()
    _log_activity(conn, "deleted", "location", loc_id,
                  f"Location '{existing['name']}' deleted")
    conn.commit()
    conn.close()


# ─── Rooms API ──────────────────────────────────────────────────────────────


@app.get("/api/rooms")
def list_rooms(location_id: Optional[int] = Query(None)):
    conn = get_db()
    if location_id is not None:
        rows = conn.execute(
            "SELECT * FROM rooms WHERE location_id = ? ORDER BY name",
            (location_id,),
        ).fetchall()
    else:
        rows = conn.execute("SELECT * FROM rooms ORDER BY name").fetchall()
    conn.close()
    return [row_to_dict(r) for r in rows]


@app.get("/api/rooms/{room_id}")
def get_room(room_id: int):
    conn = get_db()
    row = conn.execute("SELECT * FROM rooms WHERE id = ?", (room_id,)).fetchone()
    conn.close()
    if row is None:
        raise HTTPException(status_code=404, detail="Room not found")
    return row_to_dict(row)


@app.post("/api/rooms", status_code=201)
def create_room(body: RoomCreate):
    conn = get_db()
    _validate_ref(conn, "locations", body.location_id, "Location")

    cursor = conn.execute(
        "INSERT INTO rooms (location_id, name, floor) VALUES (?, ?, ?)",
        (body.location_id, body.name.strip(), body.floor or ""),
    )
    conn.commit()
    room_id = cursor.lastrowid
    _log_activity(conn, "created", "room", room_id,
                  f"Room '{body.name.strip()}' created in location {body.location_id}")
    conn.commit()
    row = conn.execute("SELECT * FROM rooms WHERE id = ?", (room_id,)).fetchone()
    conn.close()
    return row_to_dict(row)


@app.put("/api/rooms/{room_id}")
def update_room(room_id: int, body: RoomUpdate):
    conn = get_db()
    existing = conn.execute("SELECT id, name FROM rooms WHERE id = ?", (room_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Room not found")

    updates = {}
    if body.name is not None:
        updates["name"] = body.name.strip()
    if body.floor is not None:
        updates["floor"] = body.floor
    if body.location_id is not None:
        _validate_ref(conn, "locations", body.location_id, "Location")
        updates["location_id"] = body.location_id

    if updates:
        updates["updated_at"] = "datetime('now')"
        set_clause = ", ".join(
            f"{k} = {v}" if k == "updated_at" else f"{k} = ?"
            for k, v in updates.items()
        )
        values = [v for k, v in updates.items() if k != "updated_at"]
        conn.execute(
            f"UPDATE rooms SET {set_clause} WHERE id = ?",
            values + [room_id],
        )
        conn.commit()
        _log_activity(conn, "updated", "room", room_id,
                      f"Room '{existing['name']}' updated")
        conn.commit()

    row = conn.execute("SELECT * FROM rooms WHERE id = ?", (room_id,)).fetchone()
    conn.close()
    return row_to_dict(row)


@app.delete("/api/rooms/{room_id}", status_code=204)
def delete_room(room_id: int):
    conn = get_db()
    existing = conn.execute("SELECT id, name FROM rooms WHERE id = ?", (room_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Room not found")
    conn.execute("DELETE FROM rooms WHERE id = ?", (room_id,))
    conn.commit()
    _log_activity(conn, "deleted", "room", room_id,
                  f"Room '{existing['name']}' deleted")
    conn.commit()
    conn.close()


# ─── Settings API ───────────────────────────────────────────────────────────


_SETTINGS_SCHEMAS = {
    "categories":  {"table": "categories", "columns": ["name", "icon"]},
    "makes":       {"table": "makes", "columns": ["name"]},
    "models":      {"table": "models", "columns": ["make_id", "name", "icon_path"]},
    "key_names":   {"table": "key_names", "columns": ["name"]},
    "key_types":   {"table": "key_types", "columns": ["name"]},
    "badge_types": {"table": "badge_types", "columns": ["name"]},
}


def _settings_list(conn: sqlite3.Connection, table: str):
    if table == "models":
        rows = conn.execute("SELECT * FROM models ORDER BY name").fetchall()
    else:
        rows = conn.execute(f"SELECT * FROM {table} ORDER BY name").fetchall()
    return [row_to_dict(r) for r in rows]


def _settings_create(conn: sqlite3.Connection, entity: str, body: dict):
    schema = _SETTINGS_SCHEMAS[entity]
    table = schema["table"]
    columns = schema["columns"]
    values = []
    for col in columns:
        val = body.get(col, "" if col != "make_id" else None)
        if col == "make_id" and val is None:
            raise HTTPException(status_code=422, detail="make_id is required for models")
        values.append(val if val is not None else "")
    placeholders = ", ".join(["?"] * len(columns))
    col_names = ", ".join(columns)
    try:
        cursor = conn.execute(
            f"INSERT INTO {table} ({col_names}) VALUES ({placeholders})", values
        )
        conn.commit()
        eid = cursor.lastrowid
        _log_activity(conn, "created", entity, eid,
                      f"{entity[:-1].title()} '{body.get('name', '')}' created")
        conn.commit()
        row = conn.execute(f"SELECT * FROM {table} WHERE id = ?", (eid,)).fetchone()
        return row_to_dict(row)
    except sqlite3.IntegrityError:
        raise HTTPException(status_code=409, detail=f"Entry already exists in {entity}")


def _settings_update(conn: sqlite3.Connection, entity: str, eid: int, body: dict):
    schema = _SETTINGS_SCHEMAS[entity]
    table = schema["table"]
    columns = schema["columns"]

    existing = conn.execute(f"SELECT id, name FROM {table} WHERE id = ?", (eid,)).fetchone()
    if existing is None:
        raise HTTPException(status_code=404, detail=f"{entity[:-1]} not found")

    updates = {}
    for col in columns:
        if col in body:
            updates[col] = body[col]

    if updates:
        set_clause = ", ".join(f"{k} = ?" for k in updates)
        values = list(updates.values())
        conn.execute(
            f"UPDATE {table} SET {set_clause} WHERE id = ?", values + [eid]
        )
        conn.commit()
        _log_activity(conn, "updated", entity, eid,
                      f"{entity[:-1].title()} '{existing['name']}' updated")
        conn.commit()

    row = conn.execute(f"SELECT * FROM {table} WHERE id = ?", (eid,)).fetchone()
    return row_to_dict(row)


def _settings_get(conn: sqlite3.Connection, entity: str, eid: int):
    schema = _SETTINGS_SCHEMAS[entity]
    table = schema["table"]
    row = conn.execute(f"SELECT * FROM {table} WHERE id = ?", (eid,)).fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail=f"{entity[:-1]} not found")
    return row_to_dict(row)


def _settings_delete(conn: sqlite3.Connection, entity: str, eid: int):
    schema = _SETTINGS_SCHEMAS[entity]
    table = schema["table"]
    existing = conn.execute(f"SELECT id, name FROM {table} WHERE id = ?", (eid,)).fetchone()
    if existing is None:
        raise HTTPException(status_code=404, detail=f"{entity[:-1]} not found")
    conn.execute(f"DELETE FROM {table} WHERE id = ?", (eid,))
    conn.commit()
    _log_activity(conn, "deleted", entity, eid,
                  f"{entity[:-1].title()} '{existing['name']}' deleted")
    conn.commit()


_SETTINGS_ENTITIES = ["categories", "makes", "models", "key_names", "key_types", "badge_types"]

for _ent in _SETTINGS_ENTITIES:

    @app.get(f"/api/settings/{_ent}", name=f"list_{_ent}")
    def _list(entity=_ent):
        conn = get_db()
        result = _settings_list(conn, entity)
        conn.close()
        return result

    @app.get(f"/api/settings/{_ent}/{{eid}}", name=f"get_{_ent}")
    def _get(eid: int, entity=_ent):
        conn = get_db()
        try:
            result = _settings_get(conn, entity, eid)
            conn.close()
            return result
        except HTTPException:
            conn.close()
            raise

    @app.post(f"/api/settings/{_ent}", status_code=201, name=f"create_{_ent}")
    async def _create(request: Request, entity=_ent):
        body = await request.json()
        conn = get_db()
        try:
            result = _settings_create(conn, entity, body)
            conn.close()
            return result
        except HTTPException:
            conn.close()
            raise

    @app.put(f"/api/settings/{_ent}/{{eid}}", name=f"update_{_ent}")
    async def _update(eid: int, request: Request, entity=_ent):
        body = await request.json()
        conn = get_db()
        try:
            result = _settings_update(conn, entity, eid, body)
            conn.close()
            return result
        except HTTPException:
            conn.close()
            raise

    @app.delete(f"/api/settings/{_ent}/{{eid}}", status_code=204, name=f"delete_{_ent}")
    def _delete(eid: int, entity=_ent):
        conn = get_db()
        try:
            _settings_delete(conn, entity, eid)
            conn.close()
        except HTTPException:
            conn.close()
            raise


# ─── Geofences API ──────────────────────────────────────────────────────────


@app.post("/api/geofences", status_code=201)
def create_geofence(body: GeofenceCreate):
    conn = get_db()
    points_json = _json.dumps(body.points)
    cursor = conn.execute(
        "INSERT INTO geofences (name, points, color) VALUES (?, ?, ?)",
        (body.name, points_json, body.color or "#3388ff"),
    )
    gid = cursor.lastrowid

    if body.user_ids:
        try:
            _sync_geofence_users(conn, gid, body.user_ids)
        except Exception:
            conn.close()
            raise HTTPException(status_code=422, detail="One or more user IDs are invalid")

    _log_activity(conn, "created", "geofence", gid, f"Geofence '{body.name}' created")
    conn.commit()

    row = conn.execute("SELECT * FROM geofences WHERE id = ?", (gid,)).fetchone()
    result = _geofence_row(row, conn)
    conn.close()
    return result


@app.get("/api/geofences")
def list_geofences():
    conn = get_db()
    rows = conn.execute("SELECT * FROM geofences ORDER BY name").fetchall()
    result = [_geofence_row(r, conn) for r in rows]
    conn.close()
    return result


@app.put("/api/geofences/{geofence_id}")
def update_geofence(geofence_id: int, body: GeofenceUpdate):
    conn = get_db()
    existing = conn.execute(
        "SELECT id, name FROM geofences WHERE id = ?", (geofence_id,)
    ).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Geofence not found")

    updates = {}
    if body.name is not None:
        updates["name"] = body.name
    if body.points is not None:
        updates["points"] = _json.dumps(body.points)
    if body.color is not None:
        updates["color"] = body.color

    if updates:
        updates["updated_at"] = "datetime('now')"
        set_clause = ", ".join(
            f"{k} = {v}" if k == "updated_at" else f"{k} = ?"
            for k, v in updates.items()
        )
        values = [v for k, v in updates.items() if k != "updated_at"]
        conn.execute(
            f"UPDATE geofences SET {set_clause} WHERE id = ?",
            values + [geofence_id],
        )
        conn.commit()

    if body.user_ids is not None:
        _sync_geofence_users(conn, geofence_id, body.user_ids)
        conn.commit()

    _log_activity(conn, "updated", "geofence", geofence_id,
                  f"Geofence '{existing['name']}' updated")
    conn.commit()

    row = conn.execute(
        "SELECT * FROM geofences WHERE id = ?", (geofence_id,)
    ).fetchone()
    result = _geofence_row(row, conn)
    conn.close()
    return result


@app.delete("/api/geofences/{geofence_id}", status_code=204)
def delete_geofence(geofence_id: int):
    conn = get_db()
    existing = conn.execute(
        "SELECT id, name FROM geofences WHERE id = ?", (geofence_id,)
    ).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Geofence not found")
    conn.execute("DELETE FROM geofences WHERE id = ?", (geofence_id,))
    conn.commit()
    _log_activity(conn, "deleted", "geofence", geofence_id,
                  f"Geofence '{existing['name']}' deleted")
    conn.commit()
    conn.close()


# ─── Activity Feed API ──────────────────────────────────────────────────────


@app.get("/api/activity")
def list_activity(
    user_id: Optional[int] = Query(None),
    entity_type: Optional[str] = Query(None),
    date_from: Optional[str] = Query(None),
    date_to: Optional[str] = Query(None),
    limit: int = Query(100, ge=1, le=1000),
    offset: int = Query(0, ge=0),
):
    conn = get_db()
    conditions = []
    params = []

    if user_id is not None:
        conditions.append("a.user_id = ?")
        params.append(user_id)
    if entity_type is not None:
        conditions.append("a.entity_type = ?")
        params.append(entity_type)
    if date_from:
        conditions.append("a.created_at >= ?")
        params.append(date_from)
    if date_to:
        conditions.append("a.created_at <= ?")
        params.append(date_to + " 23:59:59")

    where = " AND ".join(conditions)
    sql = """SELECT a.*, u.username AS user_name
             FROM activity_log a
             LEFT JOIN users u ON a.user_id = u.id"""
    if where:
        sql += f" WHERE {where}"
    sql += " ORDER BY a.created_at DESC, a.id DESC LIMIT ? OFFSET ?"
    params.extend([limit, offset])

    rows = conn.execute(sql, params).fetchall()
    conn.close()
    return [row_to_dict(r) for r in rows]


# ─── Stats API ──────────────────────────────────────────────────────────────


@app.get("/api/stats")
def get_stats():
    conn = get_db()
    total_assets = conn.execute("SELECT COUNT(*) FROM assets").fetchone()[0]
    total_checkins = conn.execute("SELECT COUNT(*) FROM checkins").fetchone()[0]
    total_users = conn.execute("SELECT COUNT(*) FROM users").fetchone()[0]
    total_customers = conn.execute("SELECT COUNT(*) FROM customers").fetchone()[0]
    total_locations = conn.execute("SELECT COUNT(*) FROM locations").fetchone()[0]

    cats = conn.execute(
        "SELECT category, COUNT(*) AS cnt FROM assets GROUP BY category ORDER BY cnt DESC"
    ).fetchall()
    by_category = {r["category"]: r["cnt"] for r in cats}

    statuses = conn.execute(
        "SELECT status, COUNT(*) AS cnt FROM assets GROUP BY status ORDER BY cnt DESC"
    ).fetchall()
    by_status = {r["status"]: r["cnt"] for r in statuses}

    top_visited = conn.execute(
        """SELECT a.name, a.machine_id, COUNT(*) AS visit_count,
                  MAX(v.checkin_time) AS last_visit_date
           FROM visits v JOIN assets a ON v.asset_id = a.id
           GROUP BY v.asset_id ORDER BY visit_count DESC LIMIT 10"""
    ).fetchall()
    top_visited_list = [
        {"name": r["name"], "machine_id": r["machine_id"],
         "visit_count": r["visit_count"], "last_visit_date": r["last_visit_date"]}
        for r in top_visited
    ]

    time_on_site_rows = conn.execute(
        """SELECT u.username, COUNT(v.id) AS visit_count,
                  SUM(CASE WHEN v.checkout_time IS NOT NULL AND v.checkin_time IS NOT NULL
                      THEN (julianday(v.checkout_time) - julianday(v.checkin_time)) * 1440
                      ELSE 0 END) AS total_minutes
           FROM visits v JOIN users u ON v.user_id = u.id
           WHERE u.role LIKE '%technician%'
           GROUP BY v.user_id ORDER BY total_minutes DESC"""
    ).fetchall()
    time_on_site = [
        {"username": r["username"], "visit_count": r["visit_count"],
         "total_minutes": round(r["total_minutes"] or 0, 1)}
        for r in time_on_site_rows
    ]

    makes = conn.execute(
        "SELECT make, COUNT(*) AS cnt FROM assets WHERE make != '' GROUP BY make ORDER BY cnt DESC"
    ).fetchall()
    by_make = {r["make"]: r["cnt"] for r in makes}

    conn.close()
    return {
        "total_assets": total_assets,
        "total_checkins": total_checkins,
        "total_users": total_users,
        "total_customers": total_customers,
        "total_locations": total_locations,
        "by_category": by_category,
        "by_status": by_status,
        "top_visited": top_visited_list,
        "time_on_site": time_on_site,
        "by_make": by_make,
    }


# ─── CSV Export API ─────────────────────────────────────────────────────────


_ASSET_CSV_FIELDS = [
    "id", "machine_id", "serial_number", "name", "description", "category",
    "status", "make", "model", "address", "building_name", "building_number",
    "floor", "room", "trailer_number", "walking_directions", "map_link",
    "parking_location", "photo_path", "customer_id", "location_id",
    "assigned_to", "latitude", "longitude", "geofence_radius_meters",
    "created_at", "updated_at",
]

_CHECKIN_CSV_FIELDS = [
    "id", "asset_id", "user_id", "latitude", "longitude", "accuracy",
    "photo_path", "notes", "created_at",
]


def _generate_csv(rows, fieldnames):
    """Yield CSV rows as a string generator for StreamingResponse."""
    output = io.StringIO()
    writer = csv.DictWriter(output, fieldnames=fieldnames)
    writer.writeheader()
    yield output.getvalue()
    output.seek(0)
    output.truncate(0)
    for row in rows:
        writer.writerow(row_to_dict(row))
        yield output.getvalue()
        output.seek(0)
        output.truncate(0)


@app.get("/api/export/assets")
def export_assets_csv():
    conn = get_db()
    rows = conn.execute("SELECT * FROM assets ORDER BY created_at DESC").fetchall()
    conn.close()
    return StreamingResponse(
        _generate_csv(rows, _ASSET_CSV_FIELDS),
        media_type="text/csv",
        headers={"Content-Disposition": "attachment; filename=assets.csv"},
    )


@app.get("/api/export/checkins")
def export_checkins_csv(asset_id: Optional[int] = Query(None)):
    conn = get_db()
    if asset_id is not None:
        rows = conn.execute(
            "SELECT * FROM checkins WHERE asset_id = ? ORDER BY created_at DESC, id DESC",
            (asset_id,),
        ).fetchall()
    else:
        rows = conn.execute("SELECT * FROM checkins ORDER BY created_at DESC").fetchall()
    conn.close()
    return StreamingResponse(
        _generate_csv(rows, _CHECKIN_CSV_FIELDS),
        media_type="text/csv",
        headers={"Content-Disposition": "attachment; filename=checkins.csv"},
    )


# ─── Asset GPS Management ──────────────────────────────────────────────────


@app.get("/api/assets")
def list_assets_admin(
    q: Optional[str] = Query(None),
    status: Optional[str] = Query(None),
    category: Optional[str] = Query(None),
    customer_id: Optional[int] = Query(None),
    company: Optional[str] = Query(None),
    place: Optional[str] = Query(None),
    make: Optional[str] = Query(None),
    limit: int = Query(500, ge=1, le=5000),
    offset: int = Query(0, ge=0),
):
    """List all assets with optional filters and search."""
    conn = get_db()
    conditions = []
    params = []

    if q:
        conditions.append("(name LIKE ? OR machine_id LIKE ? OR company LIKE ? OR serial_number LIKE ?)")
        like = f"%{q}%"
        params.extend([like, like, like, like])
    if status:
        conditions.append("status = ?")
        params.append(status)
    if category:
        conditions.append("category = ?")
        params.append(category)
    if customer_id is not None:
        conditions.append("customer_id = ?")
        params.append(customer_id)
    if company:
        conditions.append("company = ?")
        params.append(company)
    if place:
        conditions.append("place = ?")
        params.append(place)
    if make:
        conditions.append("make = ?")
        params.append(make)

    where = " AND ".join(conditions)
    sql = "SELECT * FROM assets"
    count_sql = "SELECT COUNT(*) FROM assets"
    if where:
        sql += f" WHERE {where}"
        count_sql += f" WHERE {where}"

    total = conn.execute(count_sql, params).fetchone()[0]
    sql += " ORDER BY name ASC LIMIT ? OFFSET ?"
    rows = conn.execute(sql, params + [limit, offset]).fetchall()
    conn.close()

    result = [row_to_dict(r) for r in rows]
    return JSONResponse({
        "assets": result,
        "total": total,
        "limit": limit,
        "offset": offset,
    })


@app.put("/api/assets/{asset_id}")
def update_asset_admin(asset_id: int, body: dict):
    """Update any fields on an asset (admin). Body can include any asset column."""
    conn = get_db()
    existing = conn.execute("SELECT * FROM assets WHERE id = ?", (asset_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Asset not found")

    # Whitelist of editable fields
    EDITABLE_FIELDS = {
        "name", "machine_id", "serial_number", "description", "category", "status",
        "make", "model", "address", "building_name", "building_number", "floor", "room",
        "trailer_number", "walking_directions", "map_link", "parking_location",
        "company", "place", "location_area",
        "install_date", "dex_report_date", "pulled_date", "deployed",
        "customer_id", "location_id", "assigned_to",
        "latitude", "longitude", "geofence_radius_meters",
    }

    updates = {}
    for field, val in body.items():
        if field not in EDITABLE_FIELDS:
            continue
        if val is not None:
            updates[field] = val
        else:
            updates[field] = None

    if not updates:
        conn.close()
        raise HTTPException(status_code=422, detail="No valid fields to update")

    if "category" in updates and updates["category"]:
        row = conn.execute("SELECT id FROM categories WHERE name = ?", (updates["category"],)).fetchone()
        if row is None:
            conn.close()
            raise HTTPException(status_code=422, detail=f"Invalid category '{updates['category']}'")

    if "status" in updates and updates["status"]:
        if updates["status"] not in VALID_STATUSES:
            conn.close()
            raise HTTPException(status_code=422, detail=f"Invalid status. Must be one of: {', '.join(sorted(VALID_STATUSES))}")

    updates["updated_at"] = "datetime('now')"
    set_clause = ", ".join(
        f"{k} = {v}" if k == "updated_at" else f"{k} = ?"
        for k, v in updates.items()
    )
    values = [v for k, v in updates.items() if k != "updated_at"]
    conn.execute(
        f"UPDATE assets SET {set_clause} WHERE id = ?",
        values + [asset_id],
    )
    conn.commit()

    row = conn.execute("SELECT * FROM assets WHERE id = ?", (asset_id,)).fetchone()
    _log_activity(conn, "updated", "asset", asset_id,
                  f"Asset '{row['name']}' fields updated via admin")
    conn.commit()
    conn.close()
    return row_to_dict(row)


@app.get("/api/assets/search")
def search_assets_admin(machine_id: str = Query(...)):
    """Search for an asset by machine_id (admin)."""
    conn = get_db()
    row = conn.execute(
        "SELECT * FROM assets WHERE machine_id = ?", (machine_id,)
    ).fetchone()
    conn.close()
    if row is None:
        raise HTTPException(status_code=404, detail="Asset not found")
    return row_to_dict(row)


@app.get("/api/assets/{asset_id}")
def get_asset_admin(asset_id: int):
    """Get a single asset by ID (admin)."""
    conn = get_db()
    row = conn.execute("SELECT * FROM assets WHERE id = ?", (asset_id,)).fetchone()
    conn.close()
    if row is None:
        raise HTTPException(status_code=404, detail="Asset not found")
    return row_to_dict(row)

@app.delete("/api/assets/{asset_id}/gps", status_code=204)
def clear_asset_gps(asset_id: int):
    """Clear GPS coordinates from an asset (sets latitude/longitude to NULL)."""
    conn = get_db()
    existing = conn.execute("SELECT id, name FROM assets WHERE id = ?", (asset_id,)).fetchone()
    if existing is None:
        conn.close()
        raise HTTPException(status_code=404, detail="Asset not found")
    conn.execute(
        "UPDATE assets SET latitude = NULL, longitude = NULL, updated_at = datetime('now') WHERE id = ?",
        (asset_id,),
    )
    _log_activity(conn, "updated", "asset", asset_id,
                  f"GPS data cleared for '{existing['name']}'")
    conn.commit()
    conn.close()



# ─── Batch Status Update API ─────────────────────────────────────────────────


class BatchStatusUpdate(BaseModel):
    ids: list[int]
    status: str


@app.post("/api/assets/batch-status")
def batch_update_status(body: BatchStatusUpdate):
    """Update the status of multiple assets at once."""
    if not body.ids:
        raise HTTPException(status_code=422, detail="No asset IDs provided")
    if body.status not in VALID_STATUSES:
        raise HTTPException(
            status_code=422,
            detail=f"Invalid status. Must be one of: {', '.join(sorted(VALID_STATUSES))}",
        )

    conn = get_db()
    placeholders = ",".join(["?"] * len(body.ids))

    # Verify all assets exist
    existing = conn.execute(
        f"SELECT id, name FROM assets WHERE id IN ({placeholders})", body.ids
    ).fetchall()

    if len(existing) != len(body.ids):
        conn.close()
        raise HTTPException(status_code=404, detail="One or more asset IDs not found")

    # Update all assets
    conn.execute(
        f"UPDATE assets SET status = ?, updated_at = datetime('now') WHERE id IN ({placeholders})",
        [body.status] + body.ids,
    )
    _log_activity(conn, "updated", "asset", 0,
                  f"Batch status change: {len(body.ids)} assets set to '{body.status}'")
    conn.commit()
    conn.close()

    return {"updated": len(body.ids), "status": body.status}


class BatchAssetUpdate(BaseModel):
    ids: list[int]
    fields: dict  # { "column_name": value, ... }


@app.post("/api/assets/batch-update")
def batch_update_assets(body: BatchAssetUpdate):
    """Update the same fields on multiple assets at once. Used by Cleanup tool.
    
    Body: { ids: [1,2,3], fields: { make: "Crane", building_name: "B7", floor: "FL 1" } }
    Only updates non-null field values.
    """
    if not body.ids:
        raise HTTPException(status_code=422, detail="No asset IDs provided")
    if not body.fields:
        raise HTTPException(status_code=422, detail="No fields to update")

    ALLOWED_FIELDS = {
        "make", "model", "name", "company", "place", "building_name",
        "building_number", "floor", "room", "location_area",
        "address", "category", "status", "customer_name",
    }
    # Filter to allowed fields with non-null/empty values
    updates = {k: v for k, v in body.fields.items()
               if k in ALLOWED_FIELDS and v is not None and v != ""}
    if not updates:
        raise HTTPException(status_code=422, detail="No valid fields to update")

    conn = get_db()
    placeholders = ",".join("?" * len(body.ids))

    # Verify all IDs exist
    existing = conn.execute(
        f"SELECT COUNT(*) FROM assets WHERE id IN ({placeholders})",
        body.ids,
    ).fetchone()[0]
    if existing != len(body.ids):
        conn.close()
        raise HTTPException(status_code=404, detail="One or more asset IDs not found")

    set_clause = ", ".join(f"{col} = ?" for col in updates)
    values = list(updates.values())
    conn.execute(
        f"UPDATE assets SET {set_clause}, updated_at = datetime('now') "
        f"WHERE id IN ({placeholders})",
        values + body.ids,
    )
    _log_activity(conn, "updated", "asset", 0,
                  f"Batch update: {len(body.ids)} assets — {', '.join(updates.keys())}")
    conn.commit()
    conn.close()
    return {"updated": len(body.ids), "fields": list(updates.keys())}


# ─── Icon Upload API ────────────────────────────────────────────────────────


@app.post("/api/upload/icon", status_code=201)
async def upload_icon(file: UploadFile = File(...)):
    path = _save_upload(file, "icons", ICON_ALLOWED_EXTS, ICON_MAX_SIZE)
    return {"path": path}


# ─── Reset Database API ─────────────────────────────────────────────────────


@app.post("/api/reset-database")
def reset_database(request: Request):
    """Drop all data tables and recreate them. Requires admin role.
    
    Must include X-Confirm-Reset: yes header to prevent accidental triggers."""
    # Admin-only check
    user = getattr(request.state, "current_user", None)
    auth_skipped = os.environ.get("CANTEEN_SKIP_AUTH") == "1"
    if not auth_skipped and (not user or not roles_contain(user.get("role", ""), "admin")):
        raise HTTPException(status_code=403, detail="Only admins can reset the database")
    
    # Confirmation header safety check
    if request.headers.get("X-Confirm-Reset", "").lower() != "yes":
        raise HTTPException(
            status_code=400,
            detail="Must include X-Confirm-Reset: yes header to confirm database reset"
        )
    
    conn = get_db()
    try:
        # Disable FK checks for clean drop
        conn.execute("PRAGMA foreign_keys=OFF")
        username = user.get("username", "Unknown") if user else "Unknown"
        _log_activity(conn, "reset", "database", 0,
                      f"Database reset by '{username}'")
        conn.commit()
        tables = [
            "visits", "activity_log", "geofence_users", "geofences",
            "asset_badges", "asset_keys", "checkins", "sessions",
            "models", "makes", "badge_types", "key_types", "key_names",
            "categories", "rooms", "locations", "customer_contacts",
            "customers", "assets", "users", "settings",
        ]
        for t in tables:
            conn.execute(f"DROP TABLE IF EXISTS {t}")
        conn.execute("PRAGMA foreign_keys=ON")
        conn.commit()

        init_db(conn)
        conn.commit()
    except Exception as e:
        conn.close()
        raise HTTPException(status_code=500, detail=f"Reset failed: {str(e)}")
    conn.close()
    return {"detail": "Database reset complete — all tables recreated with seed data."}


# ─── Business Name Lookup (Google Maps via Playwright) ────────────────────

_playwright_browser = None

def _get_browser():
    """Lazy singleton Playwright browser (reuse across requests)."""
    global _playwright_browser
    if _playwright_browser is None or not _playwright_browser.is_connected():
        from playwright.sync_api import sync_playwright
        _pw = sync_playwright().start()
        _playwright_browser = _pw.chromium.launch(channel='chrome', headless=True)
    return _playwright_browser


def lookup_business_name(address: str) -> str:
    """Use headless Chrome + Google Maps to find the business at an address.
    
    Returns the business name from the 'At this place' section, or empty string.
    """
    if not address or not address.strip():
        return ""
    try:
        browser = _get_browser()
        context = browser.new_context(
            user_agent=(
                "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 "
                "(KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36"
            ),
            locale="en-US",
        )
        page = context.new_page()
        # Build the Google Maps search URL
        q = address.strip().replace(" ", "+")
        page.goto(f"https://www.google.com/maps/search/{q}", timeout=15000)
        # Wait for the page to settle — the "At this place" section appears
        page.wait_for_timeout(3000)

        # Try to find business name via several selectors
        business_name = ""
        try:
            # Method 1: article with aria-label in At this place section
            articles = page.query_selector_all('div[role="article"][aria-label]')
            for art in articles:
                label = art.get_attribute("aria-label") or ""
                label = label.strip()
                if label and label != address.strip():
                    business_name = label
                    break
        except Exception:
            pass

        if not business_name:
            try:
                # Method 2: look for the heading after "At this place"
                el = page.query_selector('h2:has-text("At this place") + div [role="article"]')
                if el:
                    business_name = (el.get_attribute("aria-label") or "")
            except Exception:
                pass

        if not business_name:
            try:
                # Method 3: page title sometimes has business name
                title = page.title()
                if title and "Google Maps" not in title:
                    business_name = title.replace(" - Google Maps", "").strip()
            except Exception:
                pass

        context.close()
        return business_name.strip()
    except Exception as e:
        print(f"[lookup] Error looking up '{address}': {e}")
        return ""


@app.post("/api/lookup-business")
def lookup_business(body: dict):
    """Look up business name at an address using Google Maps."""
    address = (body.get("address") or "").strip()
    if not address:
        raise HTTPException(status_code=422, detail="address is required")
    name = lookup_business_name(address)
    return {"address": address, "business_name": name}


@app.post("/api/lookup-business/batch")
def lookup_business_batch(body: dict):
    """Batch lookup — takes assets: [{id, address}] and returns results.
    
    Max 50 assets per call to keep response time reasonable.
    """
    assets = body.get("assets", [])
    if not assets:
        raise HTTPException(status_code=422, detail="assets array is required")
    # Limit to 50
    assets = assets[:50]
    results = []
    total = len(assets)
    for i, asset in enumerate(assets):
        aid = asset.get("id")
        addr = (asset.get("address") or "").strip()
        if not addr:
            results.append({"id": aid, "address": addr, "business_name": ""})
            continue
        name = lookup_business_name(addr)
        results.append({"id": aid, "address": addr, "business_name": name})
    return {"results": results, "total": total}


# ─── Machine Replacement History ───────────────────────────────────────────


@app.get("/api/admin/replacements/history")
def get_replacement_history():
    """Return historical machine replacement events.

    Detects replacements by:
    1) Parsing all sync batch diff_summaries for new_assets & removed_assets
       that share similar location names (same batch or adjacent batches).
    2) Cross-referencing assets grouped by location_id where one is active
       ("On") and another is inactive ("Incompatible", "Action Required"),
       suggesting a swap.
    """
    conn = get_db()
    try:
        db = conn  # alias for clarity

        # ── Method 1: Parse sync batch diff_summaries ──
        batch_replacements = []
        batches = db.execute(
            "SELECT id, created_at, diff_summary, status "
            "FROM cantaloupe_sync_batches ORDER BY id"
        ).fetchall()

        # All new_assets ever seen, keyed by batch id
        all_new_by_batch = {}
        all_removed_by_batch = {}
        for b in batches:
            diff = b["diff_summary"]
            if isinstance(diff, str):
                try:
                    diff = _json.loads(diff)
                except _json.JSONDecodeError:
                    continue
            if not isinstance(diff, dict):
                continue
            bid = b["id"]
            new_list = diff.get("new_assets", []) or []
            rem_list = diff.get("removed_assets", []) or []
            if new_list:
                all_new_by_batch[bid] = new_list
            if rem_list:
                all_removed_by_batch[bid] = rem_list

            # Also check inline replacements from diff
            inline_reps = diff.get("replacements", []) or []
            for rep in inline_reps:
                batch_replacements.append({
                    "batch_id": bid,
                    "batch_date": b["created_at"],
                    "batch_status": b["status"],
                    "old_machine_id": rep.get("old_machine_id", ""),
                    "new_machine_id": rep.get("new_machine_id", ""),
                    "location": rep.get("location_address", ""),
                    "customer": rep.get("customer_name", ""),
                    "old_name": rep.get("old_asset_name", ""),
                    "new_name": rep.get("new_asset_name", ""),
                    "confidence": rep.get("confidence", "unknown"),
                    "source": "inline_sync_replacements",
                })

        # Cross-reference: new_assets in batch N vs removed_assets in batch N (or N-1)
        batch_ids = sorted(set(list(all_new_by_batch.keys()) + list(all_removed_by_batch.keys())))
        for bid in batch_ids:
            new_items = all_new_by_batch.get(bid, [])
            rem_items = all_removed_by_batch.get(bid, [])

            # Also check previous batch for removals
            prev_rem_items = all_removed_by_batch.get(bid - 1, [])
            combined_rem = rem_items + prev_rem_items

            if not new_items or not combined_rem:
                continue

            # Build location name -> machines mapping
            new_by_loc = {}
            for n in new_items:
                loc = (n.get("location") or n.get("name", "")).strip().lower()
                if loc:
                    new_by_loc.setdefault(loc, []).append(n)

            rem_by_loc = {}
            for r in combined_rem:
                loc = (r.get("location") or r.get("name", "")).strip().lower()
                if loc:
                    rem_by_loc.setdefault(loc, []).append(r)

            # Try fuzzy match — share first 20+ chars
            for nloc, new_list in new_by_loc.items():
                for rloc, rem_list in rem_by_loc.items():
                    # Exact or prefix match
                    min_len = min(len(nloc), len(rloc))
                    if min_len < 10:
                        continue
                    match_ratio = sum(1 for a, b in zip(nloc, rloc) if a == b) / min_len
                    if match_ratio < 0.7:
                        continue

                    for new_item in new_list:
                        for rem_item in rem_list:
                            batch_replacements.append({
                                "batch_id": bid,
                                "batch_date": next(
                                    (b["created_at"] for b in batches if b["id"] == bid), ""),
                                "batch_status": next(
                                    (b["status"] for b in batches if b["id"] == bid), ""),
                                "old_machine_id": rem_item.get("machine_id", ""),
                                "new_machine_id": new_item.get("machine_id", ""),
                                "location": new_item.get("location") or new_item.get("name", ""),
                                "customer": new_item.get("customer", ""),
                                "old_name": rem_item.get("name", ""),
                                "new_name": new_item.get("name", ""),
                                "confidence": "medium" if match_ratio >= 0.85 else "low",
                                "source": "batch_diff_crossref",
                            })

        # ── Method 2: Location-group based (live assets at same location_id) ──
        # Only for small locations (2-5 machines) where it's likely a replacement,
        # not a multi-machine site like a large warehouse/hotel.
        loc_replacements = []
        locations_with_multi = db.execute("""
            SELECT location_id, COUNT(*) as cnt
            FROM assets
            WHERE location_id IS NOT NULL
            GROUP BY location_id
            HAVING cnt BETWEEN 2 AND 5
        """).fetchall()

        # Also get location names
        loc_names = {}
        loc_rows = db.execute("SELECT id, name FROM locations").fetchall()
        for lr in loc_rows:
            loc_names[lr["id"]] = lr["name"]

        for loc_row in locations_with_multi:
            lid = loc_row["location_id"]
            assets_at_loc = db.execute("""
                SELECT id, machine_id, name, status, created_at, updated_at,
                       install_date, pulled_date, deployed, building_name,
                       building_number, floor, room
                FROM assets
                WHERE location_id = ?
                ORDER BY machine_id
            """, (lid,)).fetchall()

            # Find pairs where one is active ("On") and another is inactive
            active = [a for a in assets_at_loc if a["status"] == "On"]
            inactive = [a for a in assets_at_loc
                        if a["status"] in ("Incompatible", "Off", "Action Required", "retired")]

            if not active or not inactive:
                continue

            # Only flag pairs where the building_name suggests the same physical spot
            for act in active:
                for inact in inactive:
                    # Check if they share building info (likely same spot)
                    same_building = (
                        act["building_name"] and inact["building_name"]
                        and act["building_name"].strip().lower() == inact["building_name"].strip().lower()
                    )
                    same_floor_room = (
                        act["floor"] and inact["floor"]
                        and act["floor"].strip() == inact["floor"].strip()
                        and act["room"] and inact["room"]
                        and act["room"].strip() == inact["room"].strip()
                    )

                    if not same_building and not same_floor_room:
                        continue

                    loc_name = loc_names.get(lid) or act["name"]
                    loc_replacements.append({
                        "batch_id": None,
                        "batch_date": None,
                        "batch_status": None,
                        "old_machine_id": inact["machine_id"],
                        "new_machine_id": act["machine_id"],
                        "location": loc_name,
                        "customer": "",
                        "old_name": inact["name"],
                        "new_name": act["name"],
                        "confidence": "low",
                        "source": "location_group",
                        "old_status": inact["status"],
                        "new_status": act["status"],
                    })

        # ── Merge & sort ──
        seen = set()
        all_reps = []

        for entry in batch_replacements + loc_replacements:
            key = (entry["old_machine_id"], entry["new_machine_id"])
            if key in seen:
                continue
            seen.add(key)
            all_reps.append(entry)

        # Sort: by batch_date descending, then by location
        all_reps.sort(key=lambda x: (
            x["batch_date"] or "9999-12-31",
            x["location"] or "",
        ), reverse=True)

        return {
            "replacements": all_reps,
            "total": len(all_reps),
            "sources": {
                "sync_batch_inline": len([r for r in all_reps if r["source"] == "inline_sync_replacements"]),
                "batch_crossref": len([r for r in all_reps if r["source"] == "batch_diff_crossref"]),
                "location_group": len([r for r in all_reps if r["source"] == "location_group"]),
            },
        }
    finally:
        conn.close()


# ═══════════════════════════════════════════════════════════════════════════
# WORK ORDER REPORTS — reads extraction DB directly
# ═══════════════════════════════════════════════════════════════════════════

EXTRACTION_PROJECT = Path.home() / "projects" / "ms-field-service-extraction"
EXTRACTION_DB = (
    EXTRACTION_PROJECT
    / "data-samples"
    / "msfs_backup"
    / "fieldservicecanteen.crm.dynamics.com_9fc6c50c-b097-f011-b4cb-7ced8d1b15a2_data.db"
)

STATUS_LABELS_REPORT = {
    "690970000": "Unscheduled",
    "690970001": "Scheduled",
    "690970002": "In Progress",
    "690970003": "Completed",
    "690970004": "Posted",
    "690970005": "Cancelled",
    "690959000": "Postponed",
}


def _get_extraction_db_report() -> sqlite3.Connection | None:
    """Return read-only connection to the MS Field Service extraction DB."""
    if not EXTRACTION_DB.exists():
        return None
    conn = sqlite3.connect(str(EXTRACTION_DB))
    conn.row_factory = sqlite3.Row
    return conn


def _status_label_report(code) -> str:
    return STATUS_LABELS_REPORT.get(str(code), f"Unknown ({code})")


@app.get("/api/admin/reports/workorder-kpis")
async def reports_workorder_kpis(
    date_from: str = Query("", description="Start date ISO (YYYY-MM-DD)"),
    date_to: str = Query("", description="End date ISO (YYYY-MM-DD)"),
    technician: str = Query("", description="Filter by technician name"),
    status: str = Query("", description="Filter by status code (comma-separated)"),
):
    """
    Return aggregated KPI data for field tech work orders.
    Filters: date_from, date_to (on createdon), technician, status.
    """
    conn = _get_extraction_db_report()
    if not conn:
        raise HTTPException(503, "Extraction DB not available")
    try:
        cur = conn.cursor()

        # ── Build WHERE clause ──
        where_parts = []
        params = []

        if date_from:
            where_parts.append("w.createdon >= ?")
            params.append(date_from)
        if date_to:
            where_parts.append("w.createdon < date(?, '+1 day')")
            params.append(date_to)
        if technician:
            tech_names = [t.strip() for t in technician.split(",") if t.strip()]
            if tech_names:
                tech_conditions = []
                for name in tech_names:
                    like = f"%{name}%"
                    tech_conditions.append(
                        "(COALESCE(w.\"hsl_bookedresource1!name\",'') LIKE ? "
                        "OR COALESCE(w.\"hsl_bookedresource2!name\",'') LIKE ? "
                        "OR COALESCE(w.\"hsl_bookedresource3!name\",'') LIKE ? "
                        "OR EXISTS (SELECT 1 FROM bookableresourcebooking b2 WHERE b2.\"msdyn_workorder!name\" = w.msdyn_name AND b2.\"resource!name\" LIKE ?))"
                    )
                    params.extend([like, like, like, like])
                where_parts.append("(" + " OR ".join(tech_conditions) + ")")
        if status:
            codes = [s.strip() for s in status.split(",") if s.strip()]
            if codes:
                placeholders = ", ".join(["?"] * len(codes))
                where_parts.append(f"w.msdyn_systemstatus IN ({placeholders})")
                params.extend(codes)

        where_sql = " AND ".join(where_parts) if where_parts else "1=1"

        # ── KPI 1: Total WOs ──
        cur.execute(
            f"SELECT COUNT(*) FROM msdyn_workorder w WHERE {where_sql}", params
        )
        total_workorders = cur.fetchone()[0]

        # ── KPI 2: By Status ──
        cur.execute(
            f"""
            SELECT w.msdyn_systemstatus, COUNT(*) as cnt
            FROM msdyn_workorder w
            WHERE {where_sql}
            GROUP BY w.msdyn_systemstatus
            ORDER BY cnt DESC
            """,
            params,
        )
        by_status_raw = cur.fetchall()
        by_status = []
        for r in by_status_raw:
            code = str(r["msdyn_systemstatus"])
            by_status.append({
                "code": code,
                "label": _status_label_report(code),
                "count": r["cnt"],
            })

        # ── KPI 3: WOs Over Time (daily) ──
        cur.execute(
            f"""
            SELECT date(w.createdon) as day, COUNT(*) as cnt
            FROM msdyn_workorder w
            WHERE {where_sql}
            GROUP BY day
            ORDER BY day
            """,
            params,
        )
        over_time = [{"day": r["day"], "count": r["cnt"]} for r in cur.fetchall()]

        # ── KPI 4: By Technician ──
        cur.execute(
            f"""
            SELECT t.technician, COUNT(*) as cnt
            FROM (
                SELECT COALESCE(w.\"hsl_bookedresource1!name\", w.\"hsl_bookedresource2!name\", w.\"hsl_bookedresource3!name\",
                    (SELECT b2.\"resource!name\" FROM bookableresourcebooking b2 WHERE b2.\"msdyn_workorder!name\" = w.msdyn_name AND b2.\"resource!name\" IS NOT NULL LIMIT 1)
                ) AS technician
                FROM msdyn_workorder w
                WHERE {where_sql}
            ) t
            WHERE t.technician IS NOT NULL AND t.technician != ''
            GROUP BY t.technician
            ORDER BY cnt DESC
            LIMIT 30
            """,
            params,
        )
        by_technician = [{"name": r["technician"], "count": r["cnt"]} for r in cur.fetchall()]

        # ── KPI 5: By Work Type ──
        cur.execute(
            f"""
            SELECT w.\"msdyn_workordertype!name\" AS work_type, COUNT(*) as cnt
            FROM msdyn_workorder w
            WHERE {where_sql}
            GROUP BY work_type
            ORDER BY cnt DESC
            """,
            params,
        )
        by_work_type = [{"name": r["work_type"] or "Unspecified", "count": r["cnt"]} for r in cur.fetchall()]

        # ── KPI 6: By Priority ──
        cur.execute(
            f"""
            SELECT w.\"msdyn_priority!name\" AS priority, COUNT(*) as cnt
            FROM msdyn_workorder w
            WHERE {where_sql}
            GROUP BY priority
            ORDER BY cnt DESC
            """,
            params,
        )
        by_priority = [{"name": r["priority"] or "Unspecified", "count": r["cnt"]} for r in cur.fetchall()]

        # ── KPI 7: Technician Performance (detailed) — use simpler subquery ──
        cur.execute(
            f"""
            SELECT
                t.technician,
                COUNT(*) as total,
                SUM(CASE WHEN t.msdyn_systemstatus IN (690970003, 690970004) THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN t.msdyn_systemstatus = 690970002 THEN 1 ELSE 0 END) as in_progress,
                SUM(CASE WHEN t.msdyn_systemstatus = 690970001 THEN 1 ELSE 0 END) as scheduled
            FROM (
                SELECT
                    COALESCE(w.\"hsl_bookedresource1!name\", w.\"hsl_bookedresource2!name\", w.\"hsl_bookedresource3!name\",
                        (SELECT b2.\"resource!name\" FROM bookableresourcebooking b2 WHERE b2.\"msdyn_workorder!name\" = w.msdyn_name AND b2.\"resource!name\" IS NOT NULL LIMIT 1)
                    ) AS technician,
                    w.msdyn_systemstatus
                FROM msdyn_workorder w
                WHERE {where_sql}
            ) t
            WHERE t.technician IS NOT NULL AND t.technician != ''
            GROUP BY t.technician
            ORDER BY total DESC
            LIMIT 30
            """,
            params,
        )
        tech_perf = []
        for r in cur.fetchall():
            tech_perf.append({
                "name": r["technician"],
                "total": r["total"],
                "completed": r["completed"],
                "in_progress": r["in_progress"],
                "scheduled": r["scheduled"],
            })

        # ── KPI 8: By Status per Tech (stacked) ──
        cur.execute(
            f"""
            SELECT
                t.technician,
                t.status_code,
                COUNT(*) as cnt
            FROM (
                SELECT
                    COALESCE(w.\"hsl_bookedresource1!name\", w.\"hsl_bookedresource2!name\", w.\"hsl_bookedresource3!name\",
                        (SELECT b2.\"resource!name\" FROM bookableresourcebooking b2 WHERE b2.\"msdyn_workorder!name\" = w.msdyn_name AND b2.\"resource!name\" IS NOT NULL LIMIT 1)
                    ) AS technician,
                    w.msdyn_systemstatus AS status_code
                FROM msdyn_workorder w
                WHERE {where_sql}
            ) t
            WHERE t.technician IS NOT NULL AND t.technician != ''
            GROUP BY t.technician, t.status_code
            ORDER BY t.technician, t.status_code
            """,
            params,
        )
        status_by_tech = {}
        for r in cur.fetchall():
            tech = r["technician"]
            if tech not in status_by_tech:
                status_by_tech[tech] = {}
            code = str(r["status_code"])
            status_by_tech[tech][code] = r["cnt"]

        # ── KPI 9: Summary stats ──
        cur.execute(
            f"""
            SELECT
                COUNT(*) as total,
                SUM(CASE WHEN w.msdyn_systemstatus IN (690970003, 690970004) THEN 1 ELSE 0 END) as completed_posted,
                SUM(CASE WHEN w.msdyn_systemstatus = 690970002 THEN 1 ELSE 0 END) as in_progress,
                SUM(CASE WHEN w.msdyn_systemstatus = 690970001 THEN 1 ELSE 0 END) as scheduled,
                COUNT(DISTINCT COALESCE(w.\"hsl_bookedresource1!name\", w.\"hsl_bookedresource2!name\", w.\"hsl_bookedresource3!name\",
                    (SELECT b2.\"resource!name\" FROM bookableresourcebooking b2 WHERE b2.\"msdyn_workorder!name\" = w.msdyn_name AND b2.\"resource!name\" IS NOT NULL LIMIT 1)
                )) as unique_techs
            FROM msdyn_workorder w
            WHERE {where_sql}
            """,
            params,
        )
        summary = dict(cur.fetchone())

        # ── KPI 10: Avg days to complete (for completed/posted) ──
        cur.execute(
            f"""
            SELECT AVG(julianday(w.msdyn_completedon) - julianday(w.createdon)) as avg_days
            FROM msdyn_workorder w
            WHERE {where_sql}
              AND w.msdyn_systemstatus IN (690970003, 690970004)
              AND w.msdyn_completedon IS NOT NULL
            """,
            params,
        )
        avg_days = cur.fetchone()[0]
        summary["avg_days_to_complete"] = round(avg_days, 1) if avg_days else None

        return {
            "total_workorders": total_workorders,
            "summary": summary,
            "by_status": by_status,
            "by_technician": by_technician,
            "by_work_type": by_work_type,
            "by_priority": by_priority,
            "over_time": over_time,
            "technician_performance": tech_perf,
            "status_by_tech": status_by_tech,
        }
    finally:
        conn.close()


# ═══════════════════════════════════════════════════════════════════════════
# WORK ORDERS LIST — reads extraction DB directly (same DB as reports)
# ═══════════════════════════════════════════════════════════════════════════


@app.get("/api/admin/workorders/list")
async def admin_workorders_list(
    q: str = Query("", description="Search term (WO#, account, city)"),
    status: str = Query("", description="Comma-separated status codes"),
    tech: str = Query("", description="Technician name filter"),
    date_from: str = Query("", description="Date filter start (YYYY-MM-DD)"),
    date_to: str = Query("", description="Date filter end (YYYY-MM-DD)"),
    limit: int = Query(50, ge=1, le=500),
    offset: int = Query(0, ge=0),
):
    """Fetch filtered, paginated work orders from the extraction DB."""
    conn = _get_extraction_db_report()
    if not conn:
        raise HTTPException(503, "Extraction DB not available")
    try:
        cur = conn.cursor()
        params: list = []
        where_clauses: list[str] = []

        if q:
            like = f"%{q}%"
            where_clauses.append(
                "(w.msdyn_name LIKE ? OR a.name LIKE ? OR a.address1_city LIKE ? OR w.\"msdyn_customerasset!name\" LIKE ? OR w.\"msdyn_serviceaccount!name\" LIKE ?)"
            )
            params.extend([like, like, like, like, like])

        if status:
            codes = [s.strip() for s in status.split(",") if s.strip()]
            if codes:
                placeholders = ",".join(["?" for _ in codes])
                where_clauses.append(f"w.msdyn_systemstatus IN ({placeholders})")
                params.extend(codes)

        if tech:
            like = f"%{tech}%"
            where_clauses.append(
                "(COALESCE(w.\"hsl_bookedresource1!name\",'') LIKE ? "
                "OR COALESCE(w.\"hsl_bookedresource2!name\",'') LIKE ? "
                "OR COALESCE(w.\"hsl_bookedresource3!name\",'') LIKE ? "
                "OR EXISTS (SELECT 1 FROM bookableresourcebooking b2 WHERE b2.\"msdyn_workorder!name\" = w.msdyn_name AND b2.\"resource!name\" LIKE ?))"
            )
            params.extend([like, like, like, like])

        if date_from:
            where_clauses.append("w.createdon >= ?")
            params.append(date_from)
        if date_to:
            where_clauses.append("w.createdon < date(?, '+1 day')")
            params.append(date_to)

        where_sql = " AND ".join(where_clauses) if where_clauses else "1=1"

        # Count
        cur.execute(
            f"SELECT COUNT(*) FROM msdyn_workorder w LEFT JOIN account a ON w.\"msdyn_serviceaccount!id\" = a.accountid WHERE {where_sql}",
            params,
        )
        total = cur.fetchone()[0]

        # Data
        data_sql = f"""
            SELECT
                w.msdyn_workorderid,
                w.msdyn_name,
                w."msdyn_serviceaccount!name" AS account_name,
                w.msdyn_workordersummary,
                w.createdon,
                w.msdyn_completedon,
                w.msdyn_systemstatus,
                w."msdyn_priority!name" AS priority,
                w."msdyn_workordertype!name" AS work_type,
                w."msdyn_primaryincidenttype!name" AS incident_type,
                w."msdyn_serviceterritory!name" AS territory,
                w."msdyn_customerasset!name" AS customer_asset_name,
                w.msdyn_city,
                w.msdyn_stateorprovince,
                (SELECT GROUP_CONCAT(DISTINCT t)
                 FROM (
                   SELECT b2."resource!name" AS t FROM bookableresourcebooking b2 WHERE b2."msdyn_workorder!name" = w.msdyn_name
                   UNION
                   SELECT w2."hsl_bookedresource1!name" FROM msdyn_workorder w2 WHERE w2.msdyn_workorderid = w.msdyn_workorderid AND w2."hsl_bookedresource1!name" IS NOT NULL
                   UNION
                   SELECT w2."hsl_bookedresource2!name" FROM msdyn_workorder w2 WHERE w2.msdyn_workorderid = w.msdyn_workorderid AND w2."hsl_bookedresource2!name" IS NOT NULL
                   UNION
                   SELECT w2."hsl_bookedresource3!name" FROM msdyn_workorder w2 WHERE w2.msdyn_workorderid = w.msdyn_workorderid AND w2."hsl_bookedresource3!name" IS NOT NULL
                 )) AS technicians,
                a.name AS account_display_name,
                a.address1_city,
                a.address1_stateorprovince
            FROM msdyn_workorder w
            LEFT JOIN account a ON w."msdyn_serviceaccount!id" = a.accountid
            WHERE {where_sql}
            ORDER BY w.createdon DESC, w.msdyn_name
            LIMIT ? OFFSET ?
        """
        data_params = params + [limit, offset]
        cur.execute(data_sql, data_params)
        rows = cur.fetchall()

        workorders = []
        for r in rows:
            workorders.append({
                "id": r["msdyn_workorderid"],
                "name": r["msdyn_name"],
                "account_name": r["account_name"],
                "account_display_name": r["account_display_name"],
                "summary": r["msdyn_workordersummary"],
                "status_code": str(r["msdyn_systemstatus"]),
                "status": _status_label_report(r["msdyn_systemstatus"]),
                "priority": r["priority"],
                "work_type": r["work_type"],
                "incident_type": r["incident_type"],
                "territory": r["territory"],
                "customer_asset_name": r["customer_asset_name"],
                "technicians": r["technicians"],
                "created_on": r["createdon"],
                "completed_on": r["msdyn_completedon"],
                "city": r["msdyn_city"] or r["address1_city"],
                "state": r["msdyn_stateorprovince"] or r["address1_stateorprovince"],
            })

        return {
            "results": workorders,
            "total": total,
            "offset": offset,
            "limit": limit,
        }
    finally:
        conn.close()


@app.get("/api/admin/workorders/lookup")
async def admin_workorders_lookup(name: str = Query("", description="Work order name/number")):
    """Return full details for a single work order by name."""
    if not name:
        raise HTTPException(400, "Work order name is required")
    conn = _get_extraction_db_report()
    if not conn:
        raise HTTPException(503, "Extraction DB not available")
    try:
        cur = conn.cursor()
        cur.execute("""
            SELECT
                w.*,
                a.name AS account_display_name,
                a.address1_line1,
                a.address1_city,
                a.address1_stateorprovince,
                a.address1_postalcode,
                a.telephone1,
                a.websiteurl,
                i.title AS incident_title,
                i.msdyn_servicerequesttype AS incident_type_code,
                (SELECT GROUP_CONCAT(DISTINCT t)
                 FROM (
                   SELECT b2."resource!name" AS t FROM bookableresourcebooking b2 WHERE b2."msdyn_workorder!name" = w.msdyn_name
                   UNION
                   SELECT w2."hsl_bookedresource1!name" FROM msdyn_workorder w2 WHERE w2.msdyn_workorderid = w.msdyn_workorderid AND w2."hsl_bookedresource1!name" IS NOT NULL
                   UNION
                   SELECT w2."hsl_bookedresource2!name" FROM msdyn_workorder w2 WHERE w2.msdyn_workorderid = w.msdyn_workorderid AND w2."hsl_bookedresource2!name" IS NOT NULL
                   UNION
                   SELECT w2."hsl_bookedresource3!name" FROM msdyn_workorder w2 WHERE w2.msdyn_workorderid = w.msdyn_workorderid AND w2."hsl_bookedresource3!name" IS NOT NULL
                 )) AS technicians
            FROM msdyn_workorder w
            LEFT JOIN account a ON w."msdyn_serviceaccount!id" = a.accountid
            LEFT JOIN msdyn_workorderincident woi ON woi."msdyn_workorder!id" = w.msdyn_workorderid
            LEFT JOIN incident i ON i.incidentid = woi."msdyn_incident!id"
            WHERE w.msdyn_name = ?
            LIMIT 1
        """, [name])
        row = cur.fetchone()
        if not row:
            raise HTTPException(404, f"Work order '{name}' not found")

        # Map row to dict (all columns)
        keys = [d[0] for d in cur.description]
        data = dict(zip(keys, row))

        # Add readable status
        data["status_label"] = _status_label_report(data.get("msdyn_systemstatus"))

        return {"found": True, "data": data}
    finally:
        conn.close()


@app.get("/api/admin/workorders/technicians")
async def admin_workorders_technicians():
    """Return list of all technicians found in work orders."""
    conn = _get_extraction_db_report()
    if not conn:
        raise HTTPException(503, "Extraction DB not available")
    try:
        cur = conn.cursor()
        cur.execute("""
            SELECT DISTINCT t FROM (
                SELECT b."resource!name" AS t FROM bookableresourcebooking b WHERE b."resource!name" IS NOT NULL AND b."resource!name" != ''
                UNION
                SELECT w."hsl_bookedresource1!name" FROM msdyn_workorder w WHERE w."hsl_bookedresource1!name" IS NOT NULL AND w."hsl_bookedresource1!name" != ''
                UNION
                SELECT w."hsl_bookedresource2!name" FROM msdyn_workorder w WHERE w."hsl_bookedresource2!name" IS NOT NULL AND w."hsl_bookedresource2!name" != ''
                UNION
                SELECT w."hsl_bookedresource3!name" FROM msdyn_workorder w WHERE w."hsl_bookedresource3!name" IS NOT NULL AND w."hsl_bookedresource3!name" != ''
            ) ORDER BY t
        """)
        techs = [r[0] for r in cur.fetchall()]
        return {"technicians": techs}
    finally:
        conn.close()


@app.get("/api/admin/workorders/by-asset")
async def admin_workorders_by_asset(
    name: str = Query("", description="Asset name (msdyn_customerasset!name) to search work orders for"),
):
    """Return work orders linked to a specific customer asset by name."""
    if not name:
        raise HTTPException(400, "Asset name is required")
    conn = _get_extraction_db_report()
    if not conn:
        raise HTTPException(503, "Extraction DB not available")
    try:
        cur = conn.cursor()
        cur.execute("""
            SELECT
                w.msdyn_name, w."msdyn_customerasset!name" AS asset_name,
                w."msdyn_serviceaccount!name" AS account_name,
                w.msdyn_workordersummary,
                w."msdyn_workordertype!name" AS work_type,
                w."msdyn_priority!name" AS priority,
                w.msdyn_systemstatus,
                w.createdon, w.msdyn_completedon,
                a.name AS account_display_name,
                a.address1_city, a.address1_stateorprovince
            FROM msdyn_workorder w
            LEFT JOIN account a ON w."msdyn_serviceaccount!id" = a.accountid
            WHERE w."msdyn_customerasset!name" = ?
            ORDER BY w.createdon DESC
        """, [name])
        rows = cur.fetchall()
        workorders = []
        for r in rows:
            workorders.append({
                "name": r["msdyn_name"],
                "asset_name": r["asset_name"],
                "account_name": r["account_name"] or r["account_display_name"],
                "summary": r["msdyn_workordersummary"],
                "work_type": r["work_type"],
                "priority": r["priority"],
                "status_code": str(r["msdyn_systemstatus"]),
                "status": _status_label_report(r["msdyn_systemstatus"]),
                "created_on": r["createdon"],
                "completed_on": r["msdyn_completedon"],
                "city": r["address1_city"],
                "state": r["address1_stateorprovince"],
            })
        return {"results": workorders, "total": len(workorders)}
    finally:
        conn.close()


# ═══════════════════════════════════════════════════════════════════════════
# ADB MSFS SYNC — proxies to the extraction viewer's sync API on port 8910
# ═══════════════════════════════════════════════════════════════════════════

EXTRACTION_SYNC_BASE = "http://localhost:8910"


@app.get("/api/admin/msfs-sync/info")
async def admin_msfs_sync_info():
    """Return ADB connection status, last sync metadata, and DB info."""
    try:
        async with httpx.AsyncClient(timeout=10) as client:
            resp = await client.get(f"{EXTRACTION_SYNC_BASE}/api/sync/info")
            return resp.json()
    except httpx.RequestError as e:
        return {
            "adb_connected": False,
            "extraction_server": False,
            "error": f"Cannot reach extraction server on port 8910: {e}",
        }


@app.post("/api/admin/msfs-sync/start")
async def admin_msfs_sync_start():
    """Start a background ADB sync task."""
    try:
        async with httpx.AsyncClient(timeout=30) as client:
            resp = await client.post(f"{EXTRACTION_SYNC_BASE}/api/sync/start")
            return resp.json()
    except httpx.RequestError as e:
        raise HTTPException(502, f"Extraction server unreachable: {e}")


@app.get("/api/admin/msfs-sync/task/{task_id}")
async def admin_msfs_sync_task(task_id: str):
    """Poll status of a running sync task."""
    try:
        async with httpx.AsyncClient(timeout=10) as client:
            resp = await client.get(f"{EXTRACTION_SYNC_BASE}/api/sync/task/{task_id}")
            if resp.status_code == 404:
                raise HTTPException(404, "Task not found")
            return resp.json()
    except httpx.RequestError as e:
        raise HTTPException(502, f"Extraction server unreachable: {e}")


@app.post("/api/admin/msfs-sync/launch-app")
async def admin_msfs_sync_launch():
    """Launch MS Field Service app, navigate to Check for Updates via UI nav.

    Taps globe icon in top-right, waits 15s, taps Check for Updates button,
    then waits for the phone's offline DB to stabilize.
    """
    try:
        async with httpx.AsyncClient(timeout=30) as client:
            resp = await client.post(f"{EXTRACTION_SYNC_BASE}/api/sync/check-for-updates")
            return resp.json()
    except httpx.RequestError as e:
        raise HTTPException(502, f"Extraction server unreachable: {e}")


@app.post("/api/admin/msfs-sync/cancel/{task_id}")
async def admin_msfs_sync_cancel(task_id: str):
    """Cancel a running sync task."""
    try:
        async with httpx.AsyncClient(timeout=10) as client:
            resp = await client.post(f"{EXTRACTION_SYNC_BASE}/api/sync/cancel/{task_id}")
            return resp.json()
    except httpx.RequestError as e:
        raise HTTPException(502, f"Extraction server unreachable: {e}")


# ─── SPA Frontend (catch-all — MUST be last route) ────────────────────────

FRONTEND_INDEX = Path(__file__).parent / "static" / "index.html"


@app.get("/")
@app.get("/{full_path:path}")
async def serve_frontend(full_path: str = ""):
    """Serve the admin SPA — all routes return index.html."""
    if FRONTEND_INDEX.is_file():
        return FileResponse(str(FRONTEND_INDEX))
    return JSONResponse({"detail": "Frontend not found"}, status_code=404)