/** SQLite DDL. Portable across node:sqlite (dev/test) and better-sqlite3 (Electron). */
export const SCHEMA = `
CREATE TABLE IF NOT EXISTS groups (
  id   INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS accounts (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  username          TEXT NOT NULL UNIQUE,
  display_name      TEXT,
  follower_count    INTEGER NOT NULL DEFAULT 0,
  post_count        INTEGER NOT NULL DEFAULT 0,
  group_id          INTEGER REFERENCES groups(id) ON DELETE SET NULL,
  auto_collect      INTEGER NOT NULL DEFAULT 0,
  priority          INTEGER NOT NULL DEFAULT 0,
  last_collected_at TEXT,
  backfill_state       TEXT NOT NULL DEFAULT 'pending',
  last_refresh_at      TEXT,
  last_success_at      TEXT,
  consecutive_failures INTEGER NOT NULL DEFAULT 0,
  last_error           TEXT
);

CREATE TABLE IF NOT EXISTS posts (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  account_id   INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  shortcode    TEXT NOT NULL,
  type         TEXT NOT NULL,
  url          TEXT NOT NULL,
  thumbnail    TEXT,
  caption      TEXT,
  uploaded_at  TEXT NOT NULL,
  view_count   INTEGER,
  is_benchmark INTEGER NOT NULL DEFAULT 0,
  script_text  TEXT,
  notified_at  TEXT,
  UNIQUE(account_id, shortcode)
);

CREATE TABLE IF NOT EXISTS daily_stats (
  post_id        INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  date           TEXT NOT NULL,
  comment_count  INTEGER NOT NULL,
  comment_delta  INTEGER,
  like_count     INTEGER,
  like_delta     INTEGER,
  view_count     INTEGER,
  view_delta     INTEGER,
  follower_count INTEGER,
  follower_delta INTEGER,
  PRIMARY KEY (post_id, date)
);

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

CREATE INDEX IF NOT EXISTS idx_posts_account ON posts(account_id);
CREATE INDEX IF NOT EXISTS idx_daily_post_date ON daily_stats(post_id, date);
`;