-- Bike-Tinder v1 schema (SQLite). Locked per docs/DATA-MODEL.md.
-- Applied by app.db.init_db(); WAL mode and busy_timeout are set there.

CREATE TABLE IF NOT EXISTS listings (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  region         TEXT NOT NULL CHECK (region IN ('ns','nb')),
  src_id         TEXT NOT NULL,                    -- source ad id (Kijiji id in v1)
  title          TEXT NOT NULL,
  price_display  TEXT,                             -- "$5,000"; "Please Contact"
  price_num      INTEGER,                          -- NULL when unpackable
  km             INTEGER,                          -- NULL when not stated
  location       TEXT,
  url            TEXT NOT NULL,                    -- source ad page
  img_url        TEXT,                             -- source CDN card image
  desc           TEXT,                             -- card description snippet
  family         TEXT,                             -- top rubric family label (taste signal)
  score          INTEGER NOT NULL DEFAULT 0,       -- rubric score, recomputed each import
  status         TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','gone','dup')),
  dup_of         INTEGER,                          -- canonical row id when status='dup'
  first_seen     TEXT,
  last_seen      TEXT,
  created_at     TEXT DEFAULT (datetime('now')),
  updated_at     TEXT,
  UNIQUE (region, src_id)
);

CREATE INDEX IF NOT EXISTS idx_listings_deck ON listings (status, score DESC);
CREATE INDEX IF NOT EXISTS idx_listings_dup  ON listings (dup_of) WHERE status = 'dup';

CREATE TABLE IF NOT EXISTS swipes (
  listing_id  INTEGER PRIMARY KEY REFERENCES listings(id) ON DELETE CASCADE,
  action      TEXT NOT NULL CHECK (action IN ('like','nope','maybe')),
  created_at  TEXT DEFAULT (datetime('now')),
  updated_at  TEXT
);

CREATE TABLE IF NOT EXISTS import_runs (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  started_at     TEXT NOT NULL,
  ok             INTEGER NOT NULL,
  partial        INTEGER NOT NULL DEFAULT 0,
  src_fetched_ns INTEGER,
  src_fetched_nb INTEGER,
  total          INTEGER,
  new            INTEGER,
  gone           INTEGER,
  dup            INTEGER,
  error          TEXT
);

CREATE TABLE IF NOT EXISTS prefs (
  key   TEXT PRIMARY KEY,
  value TEXT
);