Bike-Tinder/app/db.py

"""SQLite connection + schema init for Bike-Tinder.

Stdlib only. The sqlite3 CLI is not installed on this host, so everything
goes through the sqlite3 module.

P3: init_db additionally creates the personalization tables
(swipe_events, sections, section_items, taste_profile, agent_runs),
migrates pre-P3 DBs in place (CREATE TABLE IF NOT EXISTS), and seeds
swipe_events from existing swipes rows exactly once (only-when-empty,
hence idempotent — see docs/DATA-MODEL.md "P3 additions").
"""
import os
import sqlite3

SCHEMA_PATH = os.path.join(os.path.dirname(__file__), 'schema.sql')

# P3 tables — guarded/idempotent; the locked v1 schema.sql stays untouched.
P3_SCHEMA = """
CREATE TABLE IF NOT EXISTS swipe_events (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  listing_id  INTEGER NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
  action      TEXT NOT NULL CHECK (action IN ('like','nope','maybe')),
  round       INTEGER NOT NULL DEFAULT 1,
  section_id  INTEGER,
  created_at  TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_events_listing ON swipe_events (listing_id, round);
CREATE INDEX IF NOT EXISTS idx_events_time    ON swipe_events (created_at);

CREATE TABLE IF NOT EXISTS sections (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  status          TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','report','closed')),
  size            INTEGER NOT NULL,
  profile_version INTEGER,
  recipe          TEXT,
  created_at      TEXT DEFAULT (datetime('now')),
  closed_at       TEXT
);

CREATE TABLE IF NOT EXISTS section_items (
  section_id  INTEGER NOT NULL REFERENCES sections(id) ON DELETE CASCADE,
  listing_id  INTEGER NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
  slot        TEXT NOT NULL CHECK (slot IN ('new','return','wildcard')),
  position    INTEGER NOT NULL,
  PRIMARY KEY (section_id, listing_id)
);
CREATE INDEX IF NOT EXISTS idx_section_items_section ON section_items (section_id);
CREATE INDEX IF NOT EXISTS idx_section_items_listing ON section_items (listing_id);

CREATE TABLE IF NOT EXISTS taste_profile (
  version    INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at TEXT DEFAULT (datetime('now')),
  source     TEXT NOT NULL CHECK (source IN ('agent','mock','fallback')),
  payload    TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS agent_runs (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  started_at TEXT, finished_at TEXT,
  ok         INTEGER NOT NULL DEFAULT 0,
  source     TEXT, model TEXT, cost_note TEXT, error TEXT,
  profile_version INTEGER
);
"""


def connect(db_path):
    """Open a connection with WAL mode and a busy_timeout, then ensure schema."""
    parent = os.path.dirname(os.path.abspath(db_path))
    if parent:
        os.makedirs(parent, exist_ok=True)
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA journal_mode=WAL')
    conn.execute('PRAGMA busy_timeout=5000')
    conn.execute('PRAGMA foreign_keys=ON')
    return conn


def init_db(conn):
    """Apply schema.sql (idempotent) + guarded P2/P3 migrations.

    P2 additions (docs/DATA-MODEL.md) are added with idempotent
    ``ALTER TABLE ... ADD COLUMN`` guarded by a PRAGMA check, so the locked
    v1 schema.sql stays untouched and existing DBs migrate in place.
    P3 additions create the personalization tables (idempotent) and seed
    swipe_events from swipes exactly once (when the events table is empty).
    """
    with open(SCHEMA_PATH, encoding='utf-8') as fh:
        conn.executescript(fh.read())
    cols = {r['name'] for r in conn.execute("PRAGMA table_info('listings')")}
    if 'year' not in cols:
        conn.execute('ALTER TABLE listings ADD COLUMN year INTEGER')
    if 'seller_type' not in cols:
        conn.execute('ALTER TABLE listings ADD COLUMN seller_type TEXT')
    if 'extras' not in cols:
        conn.execute('ALTER TABLE listings ADD COLUMN extras TEXT')
    # B1 (photo experience v2): gallery URLs + swipe-time photo index.
    if 'imgs' not in cols:
        conn.execute('ALTER TABLE listings ADD COLUMN imgs TEXT')
    sw_cols = {r['name'] for r in conn.execute(
        "PRAGMA table_info('swipes')")}
    if 'last_photo_idx' not in sw_cols:
        conn.execute('ALTER TABLE swipes ADD COLUMN last_photo_idx INTEGER')
    # P3 tables (idempotent on every call)
    conn.executescript(P3_SCHEMA)
    # P3 event seeding: one round-1 event per existing swipe, copied
    # created_at. Only when swipe_events is empty — hence idempotent.
    n_events = conn.execute(
        'SELECT COUNT(*) AS n FROM swipe_events').fetchone()['n']
    if n_events == 0:
        conn.execute(
            """INSERT INTO swipe_events
                   (listing_id, action, round, section_id, created_at)
               SELECT listing_id, action, 1, NULL, created_at
                 FROM swipes""")
    conn.commit()


def append_event(conn, listing_id, action, section_id=None, created_at=None):
    """Append one swipe event; round = prior events for this listing + 1.

    Returns the assigned round. Every swipe write path must call this once
    per verdict change (swipes stays the materialized latest verdict;
    swipe_events is the append-only learning log).
    """
    prior = conn.execute(
        'SELECT COUNT(*) AS n FROM swipe_events WHERE listing_id=?',
        (listing_id,)).fetchone()['n']
    round_ = prior + 1
    if created_at is None:
        created_at = None  # let the column default (datetime('now')) apply
        conn.execute(
            """INSERT INTO swipe_events (listing_id, action, round, section_id)
               VALUES (?,?,?,?)""",
            (listing_id, action, round_, section_id))
    else:
        conn.execute(
            """INSERT INTO swipe_events
                   (listing_id, action, round, section_id, created_at)
               VALUES (?,?,?,?,?)""",
            (listing_id, action, round_, section_id, created_at))
    return round_


def next_round(conn, listing_id):
    """Round the NEXT event on this listing will get (= prior events + 1)."""
    prior = conn.execute(
        'SELECT COUNT(*) AS n FROM swipe_events WHERE listing_id=?',
        (listing_id,)).fetchone()['n']
    return min(prior + 1, 2)