spb/lou-ka

1# -----------------------------------------------------------------------------2# Lou-Ka — Location court terme3# db.py : persistance SQLite SÉPARÉE (data/louka_ct.db) — upsert avec détection4#         de changements, cycle de vie avec délai de grâce, cache détail.5# -----------------------------------------------------------------------------6from __future__ import annotations78import json9import sqlite310import time11from pathlib import Path1213from .schema import StListing1415DB_PATH = Path(__file__).resolve().parent.parent.parent / "data" / "louka_ct.db"1617MISS_GRACE = 2   # absences consécutives tolérées avant désactivation1819_SCHEMA = """20CREATE TABLE IF NOT EXISTS st_listings (21    uid           TEXT PRIMARY KEY,22    source        TEXT NOT NULL,23    external_id   TEXT NOT NULL,24    url           TEXT,25    title         TEXT,26    property_type TEXT,27    address       TEXT,28    city          TEXT,29    region        TEXT,30    price_night   REAL,31    price_label   TEXT,32    capacity      REAL,33    bedrooms      REAL,34    beds          REAL,35    bathrooms     REAL,36    pets          TEXT,37    citq          TEXT,38    rating        REAL,39    reviews       INTEGER,40    description   TEXT,41    amenities     TEXT,   -- JSON42    details       TEXT,   -- JSON43    images        TEXT,   -- JSON44    lat           REAL,45    lng           REAL,46    content_hash  TEXT,47    first_seen    REAL,48    last_seen     REAL,49    updated_at    REAL,50    miss_count    INTEGER DEFAULT 0,51    active        INTEGER DEFAULT 152);53CREATE INDEX IF NOT EXISTS idx_st_source ON st_listings(source);54CREATE INDEX IF NOT EXISTS idx_st_region ON st_listings(region);55CREATE INDEX IF NOT EXISTS idx_st_active ON st_listings(active);56CREATE INDEX IF NOT EXISTS idx_st_geo    ON st_listings(lat, lng);5758CREATE TABLE IF NOT EXISTS st_sync_log (59    id        INTEGER PRIMARY KEY AUTOINCREMENT,60    source    TEXT,61    ts        REAL,62    found     INTEGER,63    added     INTEGER,64    updated   INTEGER,65    removed   INTEGER,66    ok        INTEGER,67    message   TEXT68);6970CREATE TABLE IF NOT EXISTS st_detail_cache (71    source      TEXT NOT NULL,72    external_id TEXT NOT NULL,73    key         TEXT,74    payload     TEXT,75    fetched_at  REAL,76    PRIMARY KEY (source, external_id)77);78"""798081def connect() -> sqlite3.Connection:82    DB_PATH.parent.mkdir(parents=True, exist_ok=True)83    con = sqlite3.connect(DB_PATH)84    con.row_factory = sqlite3.Row85    con.executescript(_SCHEMA)86    con.commit()87    con.execute("PRAGMA journal_mode=WAL")88    con.execute("PRAGMA busy_timeout=30000")89    return con909192def sync_source(con: sqlite3.Connection, source: str,93                listings: list[StListing]) -> dict:94    """Synchronise les hébergements d'une source (ajout / màj / retrait)."""95    now = time.time()96    added = updated = 097    seen = set()98    for lst in listings:99        seen.add(lst.uid)100        h = lst.content_hash()101        row = con.execute("SELECT content_hash FROM st_listings WHERE uid=?",102                          (lst.uid,)).fetchone()103        params = dict(104            uid=lst.uid, source=lst.source, external_id=str(lst.external_id),105            url=lst.url, title=lst.title, property_type=lst.property_type,106            address=lst.address, city=lst.city, region=lst.region,107            price_night=lst.price_night, price_label=lst.price_label,108            capacity=lst.capacity, bedrooms=lst.bedrooms, beds=lst.beds,109            bathrooms=lst.bathrooms, pets=lst.pets, citq=lst.citq,110            rating=lst.rating, reviews=lst.reviews,111            description=lst.description,112            amenities=json.dumps(lst.amenities, ensure_ascii=False),113            details=json.dumps(lst.details, ensure_ascii=False),114            images=json.dumps(lst.images, ensure_ascii=False),115            lat=lst.lat, lng=lst.lng, content_hash=h, now=now,116        )117        if row is None:118            con.execute(119                """INSERT INTO st_listings (uid, source, external_id, url,120                   title, property_type, address, city, region, price_night,121                   price_label, capacity, bedrooms, beds, bathrooms, pets,122                   citq, rating, reviews, description, amenities, details,123                   images, lat, lng, content_hash, first_seen, last_seen,124                   updated_at, miss_count, active)125                   VALUES (:uid,:source,:external_id,:url,:title,126                   :property_type,:address,:city,:region,:price_night,127                   :price_label,:capacity,:bedrooms,:beds,:bathrooms,:pets,128                   :citq,:rating,:reviews,:description,:amenities,:details,129                   :images,:lat,:lng,:content_hash,:now,:now,:now,0,1)""",130                params)131            added += 1132        elif row["content_hash"] != h:133            con.execute(134                """UPDATE st_listings SET url=:url, title=:title,135                   property_type=:property_type, address=:address, city=:city,136                   region=:region, price_night=:price_night,137                   price_label=:price_label, capacity=:capacity,138                   bedrooms=:bedrooms, beds=:beds, bathrooms=:bathrooms,139                   pets=:pets, citq=:citq, rating=:rating, reviews=:reviews,140                   description=:description, amenities=:amenities,141                   details=:details, images=:images,142                   lat=COALESCE(:lat, lat), lng=COALESCE(:lng, lng),143                   content_hash=:content_hash, last_seen=:now,144                   updated_at=:now, miss_count=0, active=1145                   WHERE uid=:uid""", params)146            updated += 1147        else:148            con.execute("UPDATE st_listings SET last_seen=?, miss_count=0,"149                        " active=1 WHERE uid=?", (now, lst.uid))150151    removed = 0152    for r in con.execute("SELECT uid, miss_count FROM st_listings"153                         " WHERE source=? AND active=1", (source,)).fetchall():154        if r["uid"] in seen:155            continue156        if r["miss_count"] + 1 >= MISS_GRACE:157            con.execute("UPDATE st_listings SET active=0, miss_count=?,"158                        " updated_at=? WHERE uid=?",159                        (r["miss_count"] + 1, now, r["uid"]))160            removed += 1161        else:162            con.execute("UPDATE st_listings SET miss_count=miss_count+1"163                        " WHERE uid=?", (r["uid"],))164165    con.execute("INSERT INTO st_sync_log (source, ts, found, added, updated,"166                " removed, ok, message) VALUES (?,?,?,?,?,?,1,'ok')",167                (source, now, len(listings), added, updated, removed))168    con.commit()169    return {"source": source, "found": len(listings), "added": added,170            "updated": updated, "removed": removed}171172173def log_failure(con: sqlite3.Connection, source: str, message: str) -> None:174    con.execute("INSERT INTO st_sync_log (source, ts, found, added, updated,"175                " removed, ok, message) VALUES (?,?,0,0,0,0,0,?)",176                (source, time.time(), message))177    con.commit()178179180def get_cached_detail(con: sqlite3.Connection, source: str,181                      external_id: str, key: str) -> dict | None:182    row = con.execute("SELECT key, payload FROM st_detail_cache"183                      " WHERE source=? AND external_id=?",184                      (source, external_id)).fetchone()185    if row and row["key"] == key and row["payload"]:186        try:187            return json.loads(row["payload"])188        except ValueError:189            return None190    return None191192193def put_cached_detail(con: sqlite3.Connection, source: str,194                      external_id: str, key: str, payload: dict) -> None:195    con.execute("INSERT INTO st_detail_cache (source, external_id, key,"196                " payload, fetched_at) VALUES (?,?,?,?,?)"197                " ON CONFLICT(source, external_id) DO UPDATE SET"198                " key=excluded.key, payload=excluded.payload,"199                " fetched_at=excluded.fetched_at",200                (source, external_id, key,201                 json.dumps(payload, ensure_ascii=False), time.time()))202    con.commit()203