"""Parts data layer.

Loads the parts CSV into a local SQLite cache (one table, one meta row),
and provides a fuzzy search API powered by rapidfuzz.

Design notes
------------
- We rebuild the table from scratch whenever the CSV's mtime changes. The
  CSV is ~2k rows, so a full reload takes well under a second and avoids any
  diff/upsert bugs.
- Fuzzy search runs entirely in memory over a tiny tuple of (sku, description,
  interchange) for each row. With ~2k rows this is fast enough to run on every
  keystroke without debouncing.
- We expose Part as a plain dict so the UI layer doesn't need to import this
  module's internals.
"""

from __future__ import annotations

import csv
import logging
import re
import sqlite3
import threading
from typing import Iterable

try:
    import rapidfuzz.fuzz as fuzz
    import rapidfuzz.process as process
    _HAS_RAPIDFUZZ = True
except ImportError:
    _HAS_RAPIDFUZZ = False

log = logging.getLogger(__name__)

# ── Make detection ────────────────────────────────────────────────────────────

_MAKE_PATTERNS: dict[str, list[str]] = {
    "Acura":          ["Acura"],
    "Audi":           ["Audi"],
    "BMW":            ["BMW", "B.M.W"],
    "Buick":          ["Buick"],
    "Cadillac":       ["Cadillac", "Cad"],
    "Chevrolet":      ["Chevrolet", "Chevy", "Chev"],
    "Chrysler":       ["Chrysler"],
    "Dodge":          ["Dodge"],
    "Ford":           ["Ford"],
    "GMC":            ["GMC"],
    "Honda":          ["Honda"],
    "Hyundai":        ["Hyundai"],
    "Infiniti":       ["Infiniti", "Infinity"],
    "Isuzu":          ["Isuzu"],
    "Jeep":           ["Jeep"],
    "Kia":            ["Kia"],
    "Lexus":          ["Lexus"],
    "Lincoln":        ["Lincoln"],
    "Mazda":          ["Mazda"],
    "Mercedes-Benz":  ["Mercedes", "Mercedes-Benz", "Benz"],
    "Mercury":        ["Mercury"],
    "Mitsubishi":     ["Mitsubishi", "Mitsu"],
    "Nissan":         ["Nissan"],
    "Oldsmobile":     ["Oldsmobile", "Olds"],
    "Plymouth":       ["Plymouth"],
    "Pontiac":        ["Pontiac"],
    "Ram":            ["Ram"],
    "Saturn":         ["Saturn"],
    "Subaru":         ["Subaru"],
    "Suzuki":         ["Suzuki"],
    "Toyota":         ["Toyota"],
    "Volkswagen":     ["Volkswagen", "VW"],
    "Volvo":          ["Volvo"],
}

_COMPILED_MAKE_PATTERNS: dict[str, re.Pattern] = {
    make: re.compile(
        r"\b(" + "|".join(re.escape(p) for p in patterns) + r")\b",
        re.IGNORECASE,
    )
    for make, patterns in _MAKE_PATTERNS.items()
}


def _detect_makes(description: str) -> list[str]:
    """Return the canonical car makes that appear in `description`."""
    found: list[str] = []
    for make, pat in _COMPILED_MAKE_PATTERNS.items():
        if pat.search(description):
            found.append(make)
    return found


# ── Model detection ───────────────────────────────────────────────────────────

_MODEL_PATTERNS: dict[str, list[str]] = {
    # Honda
    "Accord":     ["Accord"],
    "Civic":      ["Civic"],
    "CR-V":       ["CR-V", "CRV"],
    "Fit":        ["Fit"],
    "HR-V":       ["HR-V", "HRV"],
    "Odyssey":    ["Odyssey"],
    "Pilot":      ["Pilot"],
    "Ridgeline":  ["Ridgeline"],
    # Toyota
    "4Runner":    ["4Runner", "4-Runner"],
    "Camry":      ["Camry"],
    "Corolla":    ["Corolla"],
    "Highlander": ["Highlander"],
    "Prius":      ["Prius"],
    "RAV4":       ["RAV4", "RAV-4"],
    "Sienna":     ["Sienna"],
    "Tacoma":     ["Tacoma"],
    "Tundra":     ["Tundra"],
    # Ford
    "Bronco":     ["Bronco"],
    "Escape":     ["Escape"],
    "Explorer":   ["Explorer"],
    "F-150":      ["F-150", "F150", "F 150"],
    "F-250":      ["F-250", "F250"],
    "F-350":      ["F-350", "F350"],
    "Focus":      ["Focus"],
    "Fusion":     ["Fusion"],
    "Mustang":    ["Mustang"],
    "Ranger":     ["Ranger"],
    # Chevy / GMC
    "Blazer":     ["Blazer"],
    "Colorado":   ["Colorado"],
    "Equinox":    ["Equinox"],
    "Malibu":     ["Malibu"],
    "Silverado":  ["Silverado"],
    "Suburban":   ["Suburban"],
    "Tahoe":      ["Tahoe"],
    "Traverse":   ["Traverse"],
    "Trax":       ["Trax"],
    "Sierra":     ["Sierra"],
    "Yukon":      ["Yukon"],
    # Dodge / Ram / Jeep / Chrysler
    "Challenger": ["Challenger"],
    "Charger":    ["Charger"],
    "Durango":    ["Durango"],
    "Grand Cherokee": ["Grand Cherokee"],
    "Wrangler":   ["Wrangler"],
    "Ram 1500":   ["Ram 1500", "1500"],
    "Ram 2500":   ["Ram 2500", "2500"],
    # Nissan
    "Altima":     ["Altima"],
    "Frontier":   ["Frontier"],
    "Maxima":     ["Maxima"],
    "Murano":     ["Murano"],
    "Pathfinder": ["Pathfinder"],
    "Rogue":      ["Rogue"],
    "Sentra":     ["Sentra"],
    "Titan":      ["Titan"],
    "Versa":      ["Versa"],
    # Hyundai / Kia
    "Elantra":    ["Elantra"],
    "Santa Fe":   ["Santa Fe"],
    "Sonata":     ["Sonata"],
    "Tucson":     ["Tucson"],
    "Soul":       ["Soul"],
    "Sportage":   ["Sportage"],
    "Sorento":    ["Sorento"],
    "Telluride":  ["Telluride"],
    # Subaru
    "Forester":   ["Forester"],
    "Impreza":    ["Impreza"],
    "Legacy":     ["Legacy"],
    "Outback":    ["Outback"],
    "WRX":        ["WRX"],
}

_COMPILED_MODEL_PATTERNS: dict[str, re.Pattern] = {
    model: re.compile(
        r"\b(" + "|".join(re.escape(p) for p in patterns) + r")\b",
        re.IGNORECASE,
    )
    for model, patterns in _MODEL_PATTERNS.items()
}


def _detect_models(description: str) -> list[str]:
    """Return the canonical models found in `description`."""
    found: list[str] = []
    for model, pat in _COMPILED_MODEL_PATTERNS.items():
        if pat.search(description):
            found.append(model)
    return found


# ── Side detection ────────────────────────────────────────────────────────────

_SIDE_SUFFIX_RE = re.compile(
    r"[\s_-]?(LH?|RH?|LEFT|RIGHT|BOTH|DRIVER|PASSENGER|DR|PASS)\b",
    re.IGNORECASE,
)


def _detect_side(sku: str) -> str:
    """Return 'left', 'right', 'both', or 'unknown' based on the SKU suffix."""
    m = _SIDE_SUFFIX_RE.search(sku)
    if not m:
        return "unknown"
    token = m.group(1).upper()
    if token in ("L", "LH", "LEFT", "DRIVER", "DR"):
        return "left"
    if token in ("R", "RH", "RIGHT", "PASSENGER", "PASS"):
        return "right"
    if token in ("BOTH",):
        return "both"
    return "unknown"


# ── SQLite schema ─────────────────────────────────────────────────────────────

COLUMNS = (
    "stock_number", "description", "customer_price", "list_price",
    "part_type", "grade", "yard_location", "available", "quantity",
    "bin_location", "interchange", "warranty_type", "warranty_length",
    "warranty_info", "image_url",
)

_CSV_TO_COL: dict[str, str] = {
    "STOCK NUMBER":    "stock_number",
    "DESCRIPTION":     "description",
    "Customer Price":  "customer_price",
    "LIST":            "list_price",
    "PART TYPE":       "part_type",
    "GRADE":           "grade",
    "YARD LOCATION":   "yard_location",
    "AVAILABLE":       "available",
    "QUANTITY":        "quantity",
    "BIN LOCATION":    "bin_location",
    "INTERCHANGE":     "interchange",
    "WARRANTY TYPE":   "warranty_type",
    "WARRANTY LENGTH": "warranty_length",
    "WARRANTY INFO":   "warranty_info",
    "Image URL":       "image_url",
}

_CREATE_PARTS = """
CREATE TABLE IF NOT EXISTS parts (
    id              INTEGER PRIMARY KEY,
    stock_number    TEXT,
    description     TEXT,
    customer_price  TEXT,
    list_price      TEXT,
    part_type       TEXT,
    grade           TEXT,
    yard_location   TEXT,
    available       TEXT,
    quantity        TEXT,
    bin_location    TEXT,
    interchange     TEXT,
    warranty_type   TEXT,
    warranty_length TEXT,
    warranty_info   TEXT,
    image_url       TEXT
)
"""

_CREATE_META = """
CREATE TABLE IF NOT EXISTS meta (
    key   TEXT PRIMARY KEY,
    value TEXT
)
"""


class PartsRepo:
    """CSV-backed parts repository with fuzzy search."""

    def __init__(self, csv_path: str = "", sqlite_path: str = "") -> None:
        self._csv_path    = csv_path
        self._sqlite_path = sqlite_path or ":memory:"
        self._conn: sqlite3.Connection | None = None
        self._lock = threading.Lock()
        self._rows: list[dict] = []
        self._search_keys: list[str] = []

        self._init_schema()
        self.reload_if_changed()

    # ── Public API ────────────────────────────────────────────────────────────

    def count(self) -> int:
        return len(self._rows)

    def available_makes(self) -> list[str]:
        """Return the alphabetical list of canonical makes present in the
        loaded parts data."""
        seen: set[str] = set()
        for row in self._rows:
            for make in _detect_makes(row.get("description", "")):
                seen.add(make)
        return sorted(seen)

    def available_models(self) -> list[str]:
        """Return the alphabetical list of canonical models present in the
        loaded parts data."""
        seen: set[str] = set()
        for row in self._rows:
            for model in _detect_models(row.get("description", "")):
                seen.add(model)
        return sorted(seen)

    def get(self, sku: str) -> dict | None:
        """Return the single part matching `sku`, or None."""
        for row in self._rows:
            if row.get("stock_number", "").strip() == sku.strip():
                return row
        conn = self._conn
        if conn is None:
            return None
        r = conn.execute(
            "SELECT * FROM parts WHERE stock_number = ? LIMIT 1", (sku,)
        ).fetchone()
        return dict(r) if r else None

    def search(
        self,
        query: str,
        *,
        makes: Iterable[str] = (),
        models: Iterable[str] = (),
        sides: Iterable[str] = (),
        limit: int = 20,
    ) -> list[dict]:
        """Fuzzy search across stock_number, description, and interchange.

        Optional `makes`, `models`, `sides` narrow the result set before
        scoring so the caller can implement filter chips.
        """
        query = query.strip()
        active_makes  = set(makes)
        active_models = set(models)
        active_sides  = set(sides)

        pool = self._rows
        if active_makes:
            pool = [r for r in pool
                    if active_makes & set(_detect_makes(r.get("description", "")))]
        if active_models:
            pool = [r for r in pool
                    if active_models & set(_detect_models(r.get("description", "")))]
        if active_sides:
            pool = [r for r in pool
                    if _detect_side(r.get("stock_number", "")) in active_sides]

        if not query:
            return pool[:limit]

        if _HAS_RAPIDFUZZ:
            keys = [
                f"{r.get('stock_number','')} {r.get('description','')} {r.get('interchange','')}".lower()
                for r in pool
            ]
            hits = process.extract(
                query.lower(),
                keys,
                scorer=fuzz.WRatio,
                limit=limit,
                score_cutoff=30,
            )
            return [pool[idx] for _, _, idx in hits]

        # Fallback: simple LIKE filter when rapidfuzz is absent
        q = query.lower()
        results = [
            r for r in pool
            if q in (r.get("stock_number") or "").lower()
            or q in (r.get("description") or "").lower()
            or q in (r.get("interchange") or "").lower()
        ]
        return results[:limit]

    def reload_if_changed(self) -> bool:
        """Reload the CSV into SQLite if its mtime is newer than the cached one.

        Returns True if a reload happened.
        """
        import os
        if not self._csv_path or not os.path.exists(self._csv_path):
            if not self._rows:
                self._load_from_db()
            return False

        csv_mtime = str(os.path.getmtime(self._csv_path))
        cached    = self._get_meta("csv_mtime")
        if cached == csv_mtime and self._rows:
            return False

        self._reload()
        self._set_meta("csv_mtime", csv_mtime)
        return True

    def close(self) -> None:
        if self._conn:
            self._conn.close()
            self._conn = None

    # ── Internal ──────────────────────────────────────────────────────────────

    def _init_schema(self) -> None:
        try:
            self._conn = sqlite3.connect(
                self._sqlite_path, check_same_thread=False
            )
            self._conn.row_factory = sqlite3.Row
            self._conn.execute("PRAGMA journal_mode=WAL")
            self._conn.execute(_CREATE_PARTS)
            self._conn.execute(_CREATE_META)
            self._conn.commit()
        except Exception as exc:
            log.exception("Could not open SQLite database: %s", exc)
            self._conn = None

    def _get_meta(self, key: str) -> str:
        conn = self._conn
        if conn is None:
            return ""
        try:
            row = conn.execute(
                "SELECT value FROM meta WHERE key = ?", (key,)
            ).fetchone()
            return row[0] if row else ""
        except Exception:
            return ""

    def _set_meta(self, key: str, value: str) -> None:
        conn = self._conn
        if conn is None:
            return
        conn.execute(
            "INSERT INTO meta (key, value) VALUES (?, ?) "
            "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
            (key, value),
        )
        conn.commit()

    def _reload(self) -> None:
        conn = self._conn
        if conn is None:
            return

        import sys
        try:
            csv.field_size_limit(sys.maxsize)
        except OverflowError:
            csv.field_size_limit(2 ** 31 - 1)

        conn.execute("DELETE FROM parts")
        conn.commit()

        cols_db       = list(_CSV_TO_COL.values())
        placeholders  = ", ".join("?" * len(cols_db))
        insert_sql    = (
            f"INSERT INTO parts ({', '.join(cols_db)}) VALUES ({placeholders})"
        )

        rows_loaded: list[dict] = []
        try:
            with open(self._csv_path, newline="", encoding="utf-8-sig",
                      errors="replace") as f:
                reader = csv.DictReader(f)
                batch: list[tuple] = []
                for csv_row in reader:
                    values = tuple(
                        str(csv_row.get(csv_col, "") or "").strip()
                        for csv_col in _CSV_TO_COL
                    )
                    batch.append(values)
                    rows_loaded.append(dict(zip(cols_db, values)))
                    if len(batch) >= 500:
                        conn.executemany(insert_sql, batch)
                        batch.clear()
                if batch:
                    conn.executemany(insert_sql, batch)
            conn.commit()
        except Exception as exc:
            log.exception("CSV import failed: %s", exc)

        log.info("Loaded %d parts from %s", len(rows_loaded), self._csv_path)
        self._rows = rows_loaded

    def _load_from_db(self) -> None:
        conn = self._conn
        if conn is None:
            return
        try:
            rows = conn.execute("SELECT * FROM parts").fetchall()
            self._rows = [dict(r) for r in rows]
        except Exception:
            self._rows = []

    def _row_count(self) -> int:
        conn = self._conn
        if conn is None:
            return 0
        try:
            return conn.execute("SELECT COUNT(*) FROM parts").fetchone()[0]
        except Exception:
            return 0

    def _rebuild_search_index(self) -> None:
        self._search_keys = [
            f"{r.get('stock_number','')} {r.get('description','')} {r.get('interchange','')}".lower()
            for r in self._rows
        ]


# Backwards-compat alias so any code that uses DataLoader still works.
DataLoader = PartsRepo