-- SQLite schema of the Vrai Prix snapshot (read-only dump)
CREATE INDEX idx_tx_city_date ON transactions(city COLLATE NOCASE, date DESC, amount DESC)

CREATE INDEX idx_tx_date ON transactions(date)

CREATE INDEX idx_tx_geo ON transactions(lat, lng)

CREATE INDEX idx_tx_prov ON transactions(id_provinc)

CREATE INDEX idx_tx_recent ON transactions(date DESC, amount DESC) WHERE id_provinc IS NOT NULL AND street IS NOT NULL AND amount >= 50000

CREATE INDEX idx_units_geo ON units(lat, lng)

CREATE INDEX idx_units_mun ON units(municipalite)

CREATE INDEX idx_units_mun_nocase_est ON units(municipalite COLLATE NOCASE, est_2026)

CREATE INDEX idx_units_mun_nocase_geo ON units(municipalite COLLATE NOCASE, lat, lng)

CREATE TABLE leads (
  id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT, unit_id TEXT,
  estimate REAL, created_at TEXT DEFAULT (datetime('now'))
)

CREATE TABLE market_index (
  month TEXT, type_prop TEXT, ppm2 REAL, idx REAL, n INTEGER,
  PRIMARY KEY (month, type_prop)
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE transactions (
  id TEXT PRIMARY KEY, date TEXT, amount REAL, street TEXT, city TEXT,
  lat REAL, lng REAL, property_type TEXT, year_built INTEGER,
  floor_area REAL, building_type TEXT, id_provinc TEXT, valeur_role REAL,
  land_area REAL
)

CREATE TABLE units (
  id_provinc TEXT PRIMARY KEY, adresse TEXT, apt TEXT, municipalite TEXT,
  arrond TEXT, code_mun TEXT, lat REAL, lng REAL, cubf INTEGER,
  cubf_libelle TEXT, type_prop TEXT, annee_construction INTEGER,
  annee_estimee TEXT, aire_etages_m2 REAL, superficie_terrain_m2 REAL,
  front_terrain_m REAL, nb_etages REAL, nb_logements INTEGER,
  nb_locaux_non_resid INTEGER, nb_chambres_locatives INTEGER,
  lien_physique TEXT, genre_construction TEXT,
  matricule TEXT, unite_voisinage TEXT, n_adresses INTEGER,
  dat_cond_marche TEXT,
  valeur_terrain REAL, valeur_batiment REAL, valeur_role REAL,
  valeur_anterieure REAL,
  est_2021 REAL, est_2022 REAL, est_2023 REAL, est_2024 REAL,
  est_2025 REAL, est_2026 REAL, p10 REAL, p90 REAL, est_hedo REAL
)

CREATE TABLE units_est_prev(
  id_provinc TEXT,
  est_2026 REAL,
  p10 REAL,
  p90 REAL
)

CREATE VIRTUAL TABLE units_fts USING fts5(
  adresse, municipalite, content='units', content_rowid='rowid',
  tokenize='unicode61 remove_diacritics 2')

CREATE TABLE 'units_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID

CREATE TABLE 'units_fts_data'(id INTEGER PRIMARY KEY, block BLOB)

CREATE TABLE 'units_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB)

CREATE TABLE 'units_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID