# Author: Simon-Pierre Boucher
# Contact: contact@spboucher.ai
# Project: Toit-Ka
# -----------------------------------------------------------------------------
# db.py : BD unifiée toitka.db (SQLite WAL) — construite par l'ETL, lue par l'API.
#   Une seule table `listings` bi-modale : la colonne `transaction_type`
#   ('louer' | 'acheter') distingue les univers Lou-Ka et Immo-Ka.
#   Les BD sources (répliques louka.db / immoka.db) ne sont JAMAIS écrites.
# -----------------------------------------------------------------------------
from __future__ import annotations

import os
import sqlite3
from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
DATA = ROOT / "data"
DB_PATH = Path(os.environ.get("TOITKA_DB", DATA / "toitka.db"))

# Répliques lecture seule des BD de prod (voir toitka/replicate.py).
# En dev, pointer directement les BD locales via .env :
#   TOITKA_LOUKA_DB=/Users/.../Desktop/lou-ka/data/louka.db
#   TOITKA_IMMOKA_DB=/Users/.../Desktop/agent-courtage/data/immoka.db
LOUKA_DB = Path(os.environ.get("TOITKA_LOUKA_DB", DATA / "replicas" / "louka.db"))
IMMOKA_DB = Path(os.environ.get("TOITKA_IMMOKA_DB", DATA / "replicas" / "immoka.db"))

SCHEMA = """
CREATE TABLE IF NOT EXISTS listings (
    uid              TEXT PRIMARY KEY,   -- "{origin}:{source}:{external_id}"
    origin           TEXT NOT NULL,      -- 'louka' | 'immoka'
    transaction_type TEXT NOT NULL,      -- 'louer' | 'acheter'
    source           TEXT NOT NULL,
    external_id      TEXT NOT NULL,
    url              TEXT,
    title            TEXT,
    address          TEXT,
    sector           TEXT,
    city             TEXT,               -- ville CANONIQUE (table de mapping)
    city_raw         TEXT,               -- valeur brute de la source (audit)
    type             TEXT,               -- type unifié (3½, 4½, Condo, Maison, Terrain…)
    price            REAL,               -- loyer mensuel (louer) ou prix demandé (acheter)
    price_label      TEXT,
    bedrooms         INTEGER,
    bathrooms        INTEGER,
    area_sqft        REAL,
    lot_sqft         REAL,
    year_built       INTEGER,
    pets             TEXT,               -- oui | non | conditions (louer)
    furnished        INTEGER,            -- 1/0 (louer)
    availability_date TEXT,              -- ISO ou 'now' (louer)
    mls              TEXT,
    broker_name      TEXT,
    agency           TEXT,
    description      TEXT,
    images           TEXT,               -- JSON [url…]
    lat              REAL,
    lng              REAL,
    first_seen       REAL,
    updated_at       REAL,
    etl_run          INTEGER,            -- id de la passe ETL qui a vu l'annonce
    active           INTEGER DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_l_tx        ON listings (transaction_type, active);
CREATE INDEX IF NOT EXISTS idx_l_city      ON listings (transaction_type, city);
CREATE INDEX IF NOT EXISTS idx_l_type      ON listings (transaction_type, type);
CREATE INDEX IF NOT EXISTS idx_l_source    ON listings (source);
CREATE INDEX IF NOT EXISTS idx_l_geo       ON listings (lat, lng);
CREATE INDEX IF NOT EXISTS idx_l_price     ON listings (transaction_type, price);

CREATE TABLE IF NOT EXISTS city_map (
    raw       TEXT PRIMARY KEY,          -- valeur brute rencontrée dans les sources
    canonical TEXT NOT NULL,             -- ville canonique ('' = irrécupérable)
    sector    TEXT DEFAULT '',           -- secteur extrait de la valeur brute
    n         INTEGER DEFAULT 0          -- occurrences à la dernière passe (audit)
);

CREATE TABLE IF NOT EXISTS etl_log (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    ts      REAL,
    origin  TEXT,
    found   INTEGER,
    kept    INTEGER,
    ok      INTEGER,
    message TEXT
);
"""


def connect(path: Path | None = None) -> sqlite3.Connection:
    """Connexion à toitka.db (créée au besoin), Row factory + WAL."""
    p = path or DB_PATH
    p.parent.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(p, timeout=60)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA synchronous=NORMAL")
    con.execute("PRAGMA busy_timeout=120000")
    con.executescript(SCHEMA)
    return con


def connect_source(path: Path) -> sqlite3.Connection:
    """Connexion LECTURE SEULE à une réplique source (louka.db / immoka.db)."""
    con = sqlite3.connect(f"file:{path}?mode=ro", uri=True, timeout=60)
    con.row_factory = sqlite3.Row
    return con