-- Toveli — base canonique (reconstruite chaque nuit par scripts/sync.ts)
-- Séparation : données source (source_record) / entités canonisées (property, listing)
-- / historique (price_event) / résultats de modèles (valuation) / marché (market_metric).
PRAGMA journal_mode = OFF;
PRAGMA synchronous = OFF;

CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT);

CREATE TABLE source (
  id TEXT PRIMARY KEY,            -- 'immo-ka' | 'lou-ka' | 'vrai-prix'
  label TEXT NOT NULL,
  kind TEXT NOT NULL,             -- 'listings-sale' | 'listings-rent' | 'valuation' | 'transactions'
  node TEXT, path TEXT,
  snapshot_at TEXT,               -- ISO : prise de l'instantané
  rows INTEGER,
  licence TEXT, notes TEXT
);

CREATE TABLE property (
  id TEXT PRIMARY KEY,            -- identifiant interne stable (hash de la clé canonique)
  key_kind TEXT NOT NULL,         -- 'role' (id_provinc MAMH) | 'address' | 'coord'
  key_value TEXT NOT NULL,
  address TEXT, unit TEXT, city TEXT, region TEXT, postal_code TEXT,
  lat REAL, lng REAL,
  category TEXT,                  -- maison | condo | plex | terrain | commercial | chalet | autre | logement
  bedrooms REAL, bathrooms REAL, area_sqft REAL, lot_sqft REAL, year_built INTEGER,
  role_value REAL, role_land REAL, role_building REAL, role_unit_id TEXT,
  n_listings INTEGER DEFAULT 0, n_sale INTEGER DEFAULT 0, n_rent INTEGER DEFAULT 0,
  min_price REAL, max_price REAL,
  attr_quality REAL,              -- 0..1 : complétude des attributs
  dedup_confidence REAL,          -- 0..1 : confiance du rapprochement des annonces
  updated_at TEXT
);
CREATE INDEX idx_property_geo ON property(lat, lng);
CREATE INDEX idx_property_city ON property(city);
CREATE INDEX idx_property_key ON property(key_kind, key_value);

CREATE TABLE listing (
  uid TEXT PRIMARY KEY,           -- '<source_id>:<connector>:<external_id>'
  property_id TEXT NOT NULL REFERENCES property(id),
  source_id TEXT NOT NULL,        -- immo-ka | lou-ka
  connector TEXT NOT NULL,        -- remax_quebec, duproprio, kangalou…
  external_id TEXT,
  market TEXT NOT NULL,           -- 'sale' | 'rent'
  url TEXT,
  status TEXT,                    -- a-vendre | a-louer
  title TEXT, description TEXT,
  price REAL, price_label TEXT,
  address TEXT, unit TEXT, city TEXT, region TEXT, sector TEXT,
  lat REAL, lng REAL,
  category TEXT, type_label TEXT, -- catégorie canonique + libellé source (Maison, 4½…)
  bedrooms REAL, bathrooms REAL, powder_rooms REAL,
  area_sqft REAL, lot_sqft REAL, year_built INTEGER,
  features TEXT,                  -- JSON []
  details TEXT,                   -- JSON {}
  images TEXT,                    -- JSON [] (URL hotlinkées, crédit = connecteur source)
  image_count INTEGER,
  agency TEXT, broker TEXT,       -- affichés seulement si fournis par la source
  mls TEXT,
  availability TEXT, furnished INTEGER, pets TEXT,
  first_seen TEXT, last_seen TEXT, updated_at TEXT,
  quality_score REAL,
  dup_of TEXT,                    -- uid canonique si doublon intra-source conservé pour trace
  provenance TEXT                 -- JSON : {source_uid, collected_at, terms}
);
CREATE INDEX idx_listing_property ON listing(property_id);
CREATE INDEX idx_listing_market_price ON listing(market, price);
CREATE INDEX idx_listing_city ON listing(city);
CREATE INDEX idx_listing_geo ON listing(lat, lng);
CREATE INDEX idx_listing_cat ON listing(market, category);
CREATE INDEX idx_listing_first_seen ON listing(first_seen);

CREATE TABLE price_event (
  listing_uid TEXT NOT NULL REFERENCES listing(uid),
  ts TEXT NOT NULL,
  price REAL,
  kind TEXT DEFAULT 'price'       -- price | first_seen
);
CREATE INDEX idx_price_event_uid ON price_event(listing_uid, ts);

CREATE TABLE valuation (
  listing_uid TEXT PRIMARY KEY REFERENCES listing(uid),
  property_id TEXT,
  model TEXT NOT NULL,            -- 'vrai-prix-hybride' | 'lou-ka-fairvalue'
  model_version TEXT,
  estimate REAL, low REAL, high REAL,
  confidence_pct REAL, confidence TEXT,
  method_detail TEXT,             -- JSON : composantes (model_est, comps_est, cost_est, role_est, n_comps, match_m…)
  ratio REAL,                     -- estimate / prix demandé
  diff REAL,                      -- prix demandé − estimate
  verdict TEXT,                   -- sous | aligne | sur | na
  evaluated_at TEXT
);
CREATE INDEX idx_valuation_property ON valuation(property_id);

CREATE TABLE neighbourhood_score (
  coord_key TEXT PRIMARY KEY,
  lat REAL, lng REAL,
  walk REAL, transit REAL, bike REAL, calm REAL, services REAL, global REAL,
  version TEXT, computed_at TEXT, source_id TEXT DEFAULT 'lou-ka'
);

CREATE TABLE sale_transaction (
  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, land_area REAL, role_unit_id TEXT, role_value REAL
);
CREATE INDEX idx_tx_geo ON sale_transaction(lat, lng);
CREATE INDEX idx_tx_date ON sale_transaction(date);
CREATE INDEX idx_tx_role ON sale_transaction(role_unit_id);

CREATE TABLE role_unit (                -- rôle d'évaluation MAMH (Données Québec, licence ouverte) + estimations Vrai-Prix pré-calculées
  id TEXT PRIMARY KEY,
  address TEXT, unit TEXT, city TEXT, code_mun TEXT,
  lat REAL, lng REAL,
  type_prop TEXT, cubf INTEGER, cubf_label TEXT,
  year_built INTEGER, floor_area_m2 REAL, land_area_m2 REAL, n_units INTEGER, n_floors INTEGER,
  role_land REAL, role_building REAL, role_value REAL, role_market_date TEXT,
  est REAL, p10 REAL, p90 REAL
);
CREATE INDEX idx_role_unit_geo ON role_unit(lat, lng);
CREATE INDEX idx_role_unit_city ON role_unit(city);

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

CREATE TABLE market_metric (
  scope TEXT NOT NULL,            -- 'province' | 'region' | 'city'
  scope_value TEXT NOT NULL,
  market TEXT NOT NULL,           -- sale | rent
  category TEXT NOT NULL,         -- 'all' | maison | condo | plex | logement…
  n INTEGER, median_price REAL, p25_price REAL, p75_price REAL,
  median_ppsf REAL, median_days INTEGER, new_30d INTEGER,
  median_ratio REAL,              -- estimation / prix demandé (si valuation)
  computed_at TEXT,
  PRIMARY KEY (scope, scope_value, market, category)
);

CREATE TABLE place (                    -- autocomplétion : municipalités, régions
  kind TEXT NOT NULL,             -- city | region
  name TEXT NOT NULL,
  region TEXT,
  lat REAL, lng REAL,
  n_sale INTEGER DEFAULT 0, n_rent INTEGER DEFAULT 0,
  PRIMARY KEY (kind, name)
);