spb/toit-ka Public

1# Author: Simon-Pierre Boucher2# Contact: contact@spboucher.ai3# Project: Toit-Ka4# -----------------------------------------------------------------------------5# db.py : BD unifiée toitka.db (SQLite WAL) — construite par l'ETL, lue par l'API.6#   Une seule table `listings` bi-modale : la colonne `transaction_type`7#   ('louer' | 'acheter') distingue les univers Lou-Ka et Immo-Ka.8#   Les BD sources (répliques louka.db / immoka.db) ne sont JAMAIS écrites.9# -----------------------------------------------------------------------------10from __future__ import annotations1112import os13import sqlite314from pathlib import Path1516ROOT = Path(__file__).resolve().parent.parent17DATA = ROOT / "data"18DB_PATH = Path(os.environ.get("TOITKA_DB", DATA / "toitka.db"))1920# Répliques lecture seule des BD de prod (voir toitka/replicate.py).21# En dev, pointer directement les BD locales via .env :22#   TOITKA_LOUKA_DB=/Users/.../Desktop/lou-ka/data/louka.db23#   TOITKA_IMMOKA_DB=/Users/.../Desktop/agent-courtage/data/immoka.db24LOUKA_DB = Path(os.environ.get("TOITKA_LOUKA_DB", DATA / "replicas" / "louka.db"))25IMMOKA_DB = Path(os.environ.get("TOITKA_IMMOKA_DB", DATA / "replicas" / "immoka.db"))2627SCHEMA = """28CREATE TABLE IF NOT EXISTS listings (29    uid              TEXT PRIMARY KEY,   -- "{origin}:{source}:{external_id}"30    origin           TEXT NOT NULL,      -- 'louka' | 'immoka'31    transaction_type TEXT NOT NULL,      -- 'louer' | 'acheter'32    source           TEXT NOT NULL,33    external_id      TEXT NOT NULL,34    url              TEXT,35    title            TEXT,36    address          TEXT,37    sector           TEXT,38    city             TEXT,               -- ville CANONIQUE (table de mapping)39    city_raw         TEXT,               -- valeur brute de la source (audit)40    type             TEXT,               -- type unifié (3½, 4½, Condo, Maison, Terrain…)41    price            REAL,               -- loyer mensuel (louer) ou prix demandé (acheter)42    price_label      TEXT,43    bedrooms         INTEGER,44    bathrooms        INTEGER,45    area_sqft        REAL,46    lot_sqft         REAL,47    year_built       INTEGER,48    pets             TEXT,               -- oui | non | conditions (louer)49    furnished        INTEGER,            -- 1/0 (louer)50    availability_date TEXT,              -- ISO ou 'now' (louer)51    mls              TEXT,52    broker_name      TEXT,53    agency           TEXT,54    description      TEXT,55    images           TEXT,               -- JSON [url…]56    lat              REAL,57    lng              REAL,58    first_seen       REAL,59    updated_at       REAL,60    etl_run          INTEGER,            -- id de la passe ETL qui a vu l'annonce61    active           INTEGER DEFAULT 162);63CREATE INDEX IF NOT EXISTS idx_l_tx        ON listings (transaction_type, active);64CREATE INDEX IF NOT EXISTS idx_l_city      ON listings (transaction_type, city);65CREATE INDEX IF NOT EXISTS idx_l_type      ON listings (transaction_type, type);66CREATE INDEX IF NOT EXISTS idx_l_source    ON listings (source);67CREATE INDEX IF NOT EXISTS idx_l_geo       ON listings (lat, lng);68CREATE INDEX IF NOT EXISTS idx_l_price     ON listings (transaction_type, price);6970CREATE TABLE IF NOT EXISTS city_map (71    raw       TEXT PRIMARY KEY,          -- valeur brute rencontrée dans les sources72    canonical TEXT NOT NULL,             -- ville canonique ('' = irrécupérable)73    sector    TEXT DEFAULT '',           -- secteur extrait de la valeur brute74    n         INTEGER DEFAULT 0          -- occurrences à la dernière passe (audit)75);7677CREATE TABLE IF NOT EXISTS etl_log (78    id      INTEGER PRIMARY KEY AUTOINCREMENT,79    ts      REAL,80    origin  TEXT,81    found   INTEGER,82    kept    INTEGER,83    ok      INTEGER,84    message TEXT85);86"""878889def connect(path: Path | None = None) -> sqlite3.Connection:90    """Connexion à toitka.db (créée au besoin), Row factory + WAL."""91    p = path or DB_PATH92    p.parent.mkdir(parents=True, exist_ok=True)93    con = sqlite3.connect(p, timeout=60)94    con.row_factory = sqlite3.Row95    con.execute("PRAGMA journal_mode=WAL")96    con.execute("PRAGMA synchronous=NORMAL")97    con.execute("PRAGMA busy_timeout=120000")98    con.executescript(SCHEMA)99    return con100101102def connect_source(path: Path) -> sqlite3.Connection:103    """Connexion LECTURE SEULE à une réplique source (louka.db / immoka.db)."""104    con = sqlite3.connect(f"file:{path}?mode=ro", uri=True, timeout=60)105    con.row_factory = sqlite3.Row106    return con107