"""Base LWIN locale — import XLSX → SQLite FTS5, recherche rapide."""
import os
import re
import sqlite3
import threading
from pathlib import Path

LWIN_XLSX = Path(os.getenv("LWIN_XLSX", "/Users/nburma/Documents/LWINdatabase.xlsx"))
LWIN_DB   = Path(os.getenv("LWIN_DB",   "lwin_cache.db"))

_ready = threading.Event()   # levé quand la base est prête
_lock  = threading.Lock()

SCHEMA_VERSION = 2  # incrémenter pour forcer un rebuild du cache

COLOUR_MAP = {
    "Red":   "rouge",
    "White": "blanc",
    "Rose":  "rosé",
    "Mixed": "autre",
    "NA":    "autre",
    "None":  "autre",
}


def _map_couleur(colour: str, sub_type: str) -> str:
    if sub_type == "Sparkling":
        return "effervescent"
    return COLOUR_MAP.get(str(colour), "autre")


def _build():
    """Construit le cache SQLite depuis le XLSX (exécuté dans un thread)."""
    try:
        import openpyxl
    except ImportError:
        print("[LWIN] openpyxl non installé — import ignoré.")
        _ready.set()
        return

    if not LWIN_XLSX.exists():
        print(f"[LWIN] Fichier introuvable : {LWIN_XLSX}")
        _ready.set()
        return

    # Cache à jour ? (vérifier aussi la version de schéma)
    if LWIN_DB.exists() and LWIN_DB.stat().st_mtime >= LWIN_XLSX.stat().st_mtime:
        try:
            c = sqlite3.connect(str(LWIN_DB))
            v = c.execute("SELECT version FROM _meta").fetchone()
            c.close()
            if v and v[0] == SCHEMA_VERSION:
                print("[LWIN] Cache à jour.")
                _ready.set()
                return
        except Exception:
            pass  # cache invalide → rebuild

    print(f"[LWIN] Import {LWIN_XLSX.name} → {LWIN_DB} …")
    wb = openpyxl.load_workbook(str(LWIN_XLSX), read_only=True)
    ws = wb.active

    tmp = Path(str(LWIN_DB) + ".tmp")
    conn = sqlite3.connect(str(tmp))
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=OFF")
    conn.execute("CREATE TABLE _meta (version INTEGER)")
    conn.execute("INSERT INTO _meta VALUES (?)", (SCHEMA_VERSION,))
    conn.execute("""
        CREATE TABLE lwin (
            id            INTEGER PRIMARY KEY,
            lwin          TEXT,
            display_name  TEXT,
            producer_name TEXT,
            wine_name     TEXT,
            country       TEXT,
            region        TEXT,
            sub_region    TEXT,
            couleur       TEXT,
            sous_type     TEXT,
            designation   TEXT,
            classification TEXT,
            first_vintage  INTEGER,
            final_vintage  INTEGER
        )
    """)

    batch = []
    n = 0
    for row in ws.iter_rows(min_row=2, values_only=True):
        if row[1] != "Live":
            continue
        if str(row[12]) not in ("Wine", "Fortified Wine"):
            continue

        fv = row[17]; lv = row[18]
        sous_type = str(row[13]) if str(row[13]) not in ("NA", "None", "Still") else None
        batch.append((
            str(int(row[0])) if row[0] else None,
            row[2],
            row[4]  if str(row[4])  != "NA" else None,
            row[5],
            row[6],
            row[7]  if str(row[7])  != "NA" else None,
            row[8]  if str(row[8])  != "NA" else None,
            _map_couleur(row[11], row[13]),
            sous_type,
            row[14] if str(row[14]) != "NA" else None,
            row[15] if str(row[15]) != "NA" else None,
            int(fv) if fv and str(fv) not in ("NA", "None") else None,
            int(lv) if lv and str(lv) not in ("NA", "None") else None,
        ))
        n += 1
        if len(batch) >= 2000:
            conn.executemany(
                "INSERT INTO lwin(lwin,display_name,producer_name,wine_name,"
                "country,region,sub_region,couleur,sous_type,designation,classification,"
                "first_vintage,final_vintage) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)",
                batch
            )
            batch.clear()

    if batch:
        conn.executemany(
            "INSERT INTO lwin(lwin,display_name,producer_name,wine_name,"
            "country,region,sub_region,couleur,sous_type,designation,classification,"
            "first_vintage,final_vintage) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)",
            batch
        )

    conn.execute("""
        CREATE VIRTUAL TABLE lwin_fts USING fts5(
            display_name, producer_name, wine_name, region,
            content=lwin, content_rowid=id
        )
    """)
    conn.execute("INSERT INTO lwin_fts(lwin_fts) VALUES('rebuild')")
    conn.commit()
    conn.close()

    tmp.replace(LWIN_DB)
    print(f"[LWIN] {n} vins importés — prêt.")
    _ready.set()


def init_lwin(background: bool = True):
    """Lance l'import (en arrière-plan par défaut)."""
    if background:
        t = threading.Thread(target=_build, daemon=True)
        t.start()
    else:
        _build()


def is_ready() -> bool:
    return _ready.is_set()


def search(q: str, limit: int = 20) -> list[dict]:
    """Recherche FTS5 dans la base LWIN. Retourne [] si pas encore prête."""
    if not LWIN_DB.exists():
        return []

    # Nettoyage query pour FTS5
    q_clean = re.sub(r'["\'\*\(\)\[\]]', '', q).strip()
    terms = " ".join(
        f'"{t}"' if len(t) > 2 else t
        for t in q_clean.split() if t
    )
    if not terms:
        return []

    conn = sqlite3.connect(str(LWIN_DB))
    conn.row_factory = sqlite3.Row
    try:
        rows = conn.execute(
            """
            SELECT l.display_name, l.producer_name, l.wine_name,
                   l.country, l.region, l.sub_region,
                   l.couleur, l.sous_type, l.designation, l.classification,
                   l.first_vintage, l.final_vintage
            FROM lwin_fts f
            JOIN lwin l ON l.id = f.rowid
            WHERE lwin_fts MATCH ?
            ORDER BY rank
            LIMIT ?
            """,
            (terms, limit)
        ).fetchall()
    except sqlite3.OperationalError:
        like = f"%{q_clean}%"
        rows = conn.execute(
            """
            SELECT display_name, producer_name, wine_name,
                   country, region, sub_region,
                   couleur, sous_type, designation, classification,
                   first_vintage, final_vintage
            FROM lwin
            WHERE display_name LIKE ? OR producer_name LIKE ?
            LIMIT ?
            """,
            (like, like, limit)
        ).fetchall()
    finally:
        conn.close()

    return [
        {
            "nom":            r["display_name"],
            "producteur":     r["producer_name"],
            "appellation":    r["sub_region"] or r["region"],
            "region":         r["region"],
            "pays":           r["country"],
            "couleur":        r["couleur"],
            "sous_type":      r["sous_type"],
            "designation":    r["designation"],
            "classification": r["classification"],
            "millesime":      None,
        }
        for r in rows
    ]