CREATE TABLE IF NOT EXISTS jobs (
  serial TEXT PRIMARY KEY,
  so_id TEXT, so_number TEXT,
  customer_name TEXT, contact_name TEXT,
  phone TEXT, email TEXT,
  status TEXT NOT NULL DEFAULT 'Received',
  due TEXT, items_json TEXT,
  created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
  synced_at TEXT, zoho_mtime TEXT, last_update TEXT
);
CREATE TABLE IF NOT EXISTS events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  serial TEXT NOT NULL, status TEXT NOT NULL, note TEXT,
  public INTEGER NOT NULL DEFAULT 1, at TEXT NOT NULL,
  src TEXT NOT NULL DEFAULT 'staff'   -- zoho = from the intake page via Zoho, auto = created on import
);
CREATE INDEX IF NOT EXISTS events_serial ON events(serial);
CREATE TABLE IF NOT EXISTS photos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  serial TEXT NOT NULL, r2_key TEXT NOT NULL, caption TEXT,
  public INTEGER NOT NULL DEFAULT 0, content_type TEXT, at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS photos_serial ON photos(serial);
CREATE TABLE IF NOT EXISTS notices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  serial TEXT NOT NULL, channel TEXT NOT NULL, recipient TEXT,
  ok INTEGER NOT NULL, detail TEXT, at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS kv (k TEXT PRIMARY KEY, v TEXT, exp INTEGER);