1"""CLEARLY LABELLED SYNTHETIC TEST FIXTURE — not empirical data.23Generates a small SQLite file with the Auto-Ka schema (vehicles, price_log,4sync_log, dealers) so that the pipeline and tests run without the restricted5snapshot. Values are random; nothing here is a finding.6"""7from __future__ import annotations89import json10import random11import sqlite312from pathlib import Path1314SCHEMA = """15CREATE TABLE vehicles (uid TEXT PRIMARY KEY, source TEXT NOT NULL, external_id TEXT NOT NULL, url TEXT, title TEXT,16 make TEXT, model TEXT, trim TEXT, year INTEGER, price REAL, price_label TEXT, mileage_km REAL, mileage_label TEXT,17 transmission TEXT, fuel TEXT, drivetrain TEXT, body_type TEXT, exterior_color TEXT, interior_color TEXT, engine TEXT,18 doors INTEGER, seats INTEGER, vin TEXT, stock_number TEXT, dealer_name TEXT, city TEXT, region TEXT, description TEXT,19 features TEXT, details TEXT, images TEXT, carfax_url TEXT, content_hash TEXT, first_seen REAL, last_seen REAL,20 updated_at REAL, miss_count INTEGER DEFAULT 0, active INTEGER DEFAULT 1, kind TEXT DEFAULT 'auto', lat REAL, lng REAL,21 dup_of TEXT, dup_sources TEXT);22CREATE TABLE sync_log (id INTEGER PRIMARY KEY AUTOINCREMENT, source TEXT, ts REAL, found INTEGER, added INTEGER,23 updated INTEGER, removed INTEGER, ok INTEGER, message TEXT, stats TEXT);24CREATE TABLE price_log (uid TEXT NOT NULL, ts REAL NOT NULL, price REAL);25CREATE TABLE dealers (source TEXT PRIMARY KEY, name TEXT, address TEXT, phone TEXT, website TEXT, city TEXT, lat REAL, lng REAL, fetched_at REAL);26CREATE TABLE vin_decode (vin TEXT PRIMARY KEY, data TEXT, fetched_at REAL);27"""2829T0 = 1786537835.0 # same window start as the real panel (for code paths only)303132def make(path: Path, n_listings: int = 3000, seed: int = 36) -> Path:33 rng = random.Random(seed)34 path.parent.mkdir(parents=True, exist_ok=True)35 if path.exists():36 path.unlink()37 con = sqlite3.connect(path)38 con.executescript(SCHEMA)39 makes = {"Honda": ["Civic", "CR-V"], "Toyota": ["Corolla", "RAV4"], "Ford": ["F-150", "Escape"], "Hyundai": ["Elantra", "Tucson"]}40 regions = {"Montréal": (45.5, -73.6), "Capitale-Nationale": (46.8, -71.2), "Outaouais": (45.4, -75.7), "Estrie": (45.4, -71.9)}41 sources = [f"synthdealer{i:02d}" for i in range(20)] + ["otogo"]42 dealer_geo = {}43 for s in sources[:-1]:44 reg = rng.choice(list(regions))45 lat, lng = regions[reg]46 dealer_geo[s] = (reg, lat + rng.uniform(-0.3, 0.3), lng + rng.uniform(-0.3, 0.3))47 con.execute("INSERT INTO dealers VALUES (?,?,?,?,?,?,?,?,?)", (s, s, "", "", "", reg, dealer_geo[s][1], dealer_geo[s][2], T0))48 for s in sources:49 for c in range(585):50 con.execute("INSERT INTO sync_log (source, ts, found, added, updated, removed, ok, message, stats) VALUES (?,?,?,?,?,?,?,?,?)",51 (s, T0 + c * 3.3 * 3600, 100, 5, 10, 4, 1, "ok", json.dumps({"null_price_rate": 0.0})))52 end = T0 + 81 * 8640053 for i in range(n_listings):54 src = rng.choice(sources)55 if src == "otogo":56 d = rng.choice(sources[:-1]); reg, lat, lng = dealer_geo[d]; dealer_name = d.upper()57 else:58 reg, lat, lng = dealer_geo[src]; dealer_name = src59 mk = rng.choice(list(makes)); md = rng.choice(makes[mk]); yr = rng.randint(2012, 2025)60 km = max(2000, rng.gauss(18000 * (2026 - yr), 20000))61 price = max(3000, 38000 * (0.86 ** (2026 - yr)) * (1 - 0.000002 * km) * rng.lognormvariate(0, 0.12))62 entry = T0 + rng.uniform(0, 75 * 86400)63 dur = rng.expovariate(1 / 20) * 8640064 exit_ts = entry + dur65 active = 1 if exit_ts > end - 2 * 86400 else 066 last_seen = min(exit_ts, end)67 vin = "".join(rng.choice("ABCDEFGHJKLMNPRSTUVWXYZ0123456789") for _ in range(17))68 uid = f"{src}:{i}"69 con.execute("""INSERT INTO vehicles (uid, source, external_id, title, make, model, trim, year, price, mileage_km, transmission, fuel,70 drivetrain, body_type, vin, dealer_name, city, region, first_seen, last_seen, updated_at, miss_count, active, kind, lat, lng)71 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",72 (uid, src, str(i), f"{mk} {md} {yr}", mk, md, "LX", yr, round(price), round(km), "Automatique", "Essence",73 "Traction", "VUS" if md in {"CR-V", "RAV4", "Escape", "Tucson"} else "Berline", vin, dealer_name, "Ville", reg,74 entry, last_seen, last_seen if active == 0 else entry, 0, active, "auto", lat, lng))75 p = price76 con.execute("INSERT INTO price_log VALUES (?,?,?)", (uid, entry, round(p)))77 t = entry78 while rng.random() < 0.35 and t + 5 * 86400 < last_seen:79 t += rng.uniform(5, 15) * 8640080 p *= 1 - rng.uniform(0.01, 0.06)81 con.execute("INSERT INTO price_log VALUES (?,?,?)", (uid, t, round(p)))82 con.execute("UPDATE vehicles SET price=? WHERE uid=?", (round(p), uid))83 con.commit(); con.close()84 (path.parent / "README.md").write_text("# SYNTHETIC FIXTURE\n\nRandomly generated data with the Auto-Ka schema for tests only. "85 "No empirical content. Generated by `ucml.synthetic.make`.\n")86 return path878889if __name__ == "__main__":90 from . import config as C91 print(make(C.SYNTHETIC / "autoka-synthetic.db"))92