import { commentGrowth, seriesGrowth, isViral, DEFAULT_VIRAL_THRESHOLD, type ViralConfig } from '@insta-monitor/core';
import type { Account, DailyStat, Group, IgAccount, Post, PostType } from '@insta-monitor/core';
import { SCHEMA } from './schema.js';
import { openNodeSqlite } from './driver.js';
import type { Driver } from './driver.js';

/** Factory that opens a SQLite driver at a path (node:sqlite or better-sqlite3). */
export type DriverFactory = (path: string) => Driver;

const DEFAULT_GROUP = '미분류';

// ---- row shapes (snake_case as stored) ----
interface GroupRow { id: number; name: string }
/** Hard cap on priority (daily-collected) channels — bounds single-account daily load. */
export const PRIORITY_CAP = 100;

interface AccountRow {
  id: number; username: string; display_name: string | null;
  follower_count: number; post_count: number; group_id: number | null;
  auto_collect: number; priority: number; last_collected_at: string | null;
  backfill_state: string; last_refresh_at: string | null;
  last_success_at: string | null; consecutive_failures: number; last_error: string | null;
}
interface PostRow {
  id: number; account_id: number; shortcode: string; type: string; url: string;
  thumbnail: string | null; caption: string | null; uploaded_at: string;
  view_count: number | null; is_benchmark: number; script_text: string | null;
}
interface DailyRow {
  post_id: number; date: string; comment_count: number; comment_delta: number | null;
  like_count: number | null; like_delta: number | null;
  view_count: number | null; view_delta: number | null;
  follower_count: number | null; follower_delta: number | null;
}

const toAccount = (r: AccountRow): Account => ({
  id: r.id, username: r.username, displayName: r.display_name,
  followerCount: r.follower_count, postCount: r.post_count, groupId: r.group_id,
  autoCollect: !!r.auto_collect, priority: !!r.priority, lastCollectedAt: r.last_collected_at,
  backfillState: (r.backfill_state as Account['backfillState']) ?? 'pending',
  lastRefreshAt: r.last_refresh_at, lastSuccessAt: r.last_success_at,
  consecutiveFailures: r.consecutive_failures ?? 0, lastError: r.last_error,
});
const toPost = (r: PostRow): Post => ({
  id: r.id, accountId: r.account_id, shortcode: r.shortcode, type: r.type as PostType,
  url: r.url, thumbnail: r.thumbnail, caption: r.caption, uploadedAt: r.uploaded_at,
  viewCount: r.view_count, isBenchmark: !!r.is_benchmark, scriptText: r.script_text,
});
const toDailyStat = (r: DailyRow): DailyStat => ({
  postId: r.post_id, date: r.date, commentCount: r.comment_count, commentDelta: r.comment_delta,
  likeCount: r.like_count, likeDelta: r.like_delta, viewCount: r.view_count, viewDelta: r.view_delta,
  followerCount: r.follower_count, followerDelta: r.follower_delta,
});

export interface PostWithGrowth extends Post {
  /** Comment count from the latest snapshot (0 if none). */
  commentCount: number;
  /** Like/view from the latest snapshot; null when hidden/unavailable. */
  likeCount: number | null;
  viewCount: number | null;
  growth1d: number;
  growth3d: number;
  growth7d: number;
  /** Like growth over the same windows — feeds the "터진" signal alongside comments. */
  likeGrowth1d: number;
  likeGrowth7d: number;
  /** View growth over the first 1/2/3 days (the client tracks early momentum only). */
  viewGrowth1d: number;
  viewGrowth2d: number;
  viewGrowth3d: number;
  isViral: boolean;
  /** Number of daily_stats snapshots for this post. < 2 means growth figures are not yet meaningful. */
  daysTracked: number;
}

/** A newly-viral reel worth pushing a notification about. */
export interface ViralAlert {
  id: number;
  username: string;
  url: string;
  viewCount: number | null;
  commentCount: number;
}

export interface NewAccount {
  username: string;
  displayName?: string | null;
  groupId?: number | null;
  autoCollect?: boolean;
}

export interface UpsertPost {
  accountId: number;
  shortcode: string;
  type: PostType;
  url: string;
  uploadedAt: string;
  thumbnail?: string | null;
  caption?: string | null;
  viewCount?: number | null;
}

export interface NewSnapshot {
  postId: number;
  date: string;
  commentCount: number;
  likeCount?: number | null;
  viewCount?: number | null;
  followerCount?: number | null;
}

class GroupRepo {
  constructor(private d: Driver) {}
  list(): Group[] {
    return this.d.prepare('SELECT id, name FROM groups ORDER BY id').all<GroupRow>();
  }
  create(name: string): number {
    this.d.prepare('INSERT OR IGNORE INTO groups(name) VALUES (?)').run(name);
    return this.d.prepare('SELECT id FROM groups WHERE name = ?').get<{ id: number }>(name)!.id;
  }
  delete(id: number): void {
    this.d.prepare('DELETE FROM groups WHERE id = ?').run(id);
  }
}

class AccountRepo {
  constructor(private d: Driver) {}
  create(a: NewAccount): number {
    const res = this.d
      .prepare('INSERT INTO accounts(username, display_name, group_id, auto_collect) VALUES (?, ?, ?, ?)')
      .run(a.username, a.displayName ?? null, a.groupId ?? null, a.autoCollect ? 1 : 0);
    return Number(res.lastInsertRowid);
  }
  /** Insert many new accounts (auto-collect on); skips ones that already exist. Returns counts.
   *  ponytail: per-row inserts, no transaction (the Driver doesn't expose one) — fine for a
   *  one-time bulk import; wrap in a tx if import latency ever matters. */
  createMany(usernames: string[]): { added: number; duplicate: number } {
    let added = 0;
    let duplicate = 0;
    for (const username of usernames) {
      if (this.getByUsername(username)) {
        duplicate += 1;
        continue;
      }
      this.create({ username, autoCollect: true });
      added += 1;
    }
    return { added, duplicate };
  }
  getByUsername(username: string): Account | undefined {
    const r = this.d.prepare('SELECT * FROM accounts WHERE username = ?').get<AccountRow>(username);
    return r ? toAccount(r) : undefined;
  }
  getById(id: number): Account | undefined {
    const r = this.d.prepare('SELECT * FROM accounts WHERE id = ?').get<AccountRow>(id);
    return r ? toAccount(r) : undefined;
  }
  list(): Account[] {
    return this.d.prepare('SELECT * FROM accounts ORDER BY username').all<AccountRow>().map(toAccount);
  }
  rename(id: number, newUsername: string): void {
    this.d.prepare('UPDATE accounts SET username = ? WHERE id = ?').run(newUsername, id);
  }
  setGroup(id: number, groupId: number | null): void {
    this.d.prepare('UPDATE accounts SET group_id = ? WHERE id = ?').run(groupId, id);
  }
  setAutoCollect(id: number, on: boolean): void {
    this.d.prepare('UPDATE accounts SET auto_collect = ? WHERE id = ?').run(on ? 1 : 0, id);
  }
  /** Pin/unpin for daily collection. Pinning forces auto-collect on and is rejected
   *  (returns false) once PRIORITY_CAP channels are already pinned. */
  setPriority(id: number, on: boolean): boolean {
    if (on) {
      if (!this.getById(id)?.priority && this.countPriority() >= PRIORITY_CAP) return false;
      this.d.prepare('UPDATE accounts SET priority = 1, auto_collect = 1 WHERE id = ?').run(id);
    } else {
      this.d.prepare('UPDATE accounts SET priority = 0 WHERE id = ?').run(id);
    }
    return true;
  }
  countPriority(): number {
    return this.d.prepare('SELECT COUNT(*) AS n FROM accounts WHERE priority = 1').get<{ n: number }>()!.n;
  }
  /** Priority (daily) channels, oldest-refresh first. Inactive (not_found/private) accounts are excluded. */
  listPriority(): Account[] {
    return this.d
      .prepare("SELECT * FROM accounts WHERE priority = 1 AND auto_collect = 1 AND backfill_state NOT IN ('not_found', 'private') ORDER BY (last_refresh_at IS NOT NULL), last_refresh_at, id")
      .all<AccountRow>()
      .map(toAccount);
  }
  updateMeta(id: number, m: { followerCount: number; postCount: number; lastCollectedAt: string }): void {
    this.d
      .prepare('UPDATE accounts SET follower_count = ?, post_count = ?, last_collected_at = ? WHERE id = ?')
      .run(m.followerCount, m.postCount, m.lastCollectedAt, id);
  }
  recordCollectSuccess(id: number, at: string): void {
    this.d.prepare('UPDATE accounts SET last_success_at = ?, consecutive_failures = 0, last_error = NULL WHERE id = ?').run(at, id);
  }
  recordCollectFailure(id: number, at: string, error: string): void {
    this.d.prepare('UPDATE accounts SET consecutive_failures = consecutive_failures + 1, last_error = ? WHERE id = ?').run(error, id);
    void at; // reserved for a future last_attempt_at; failure time not stored yet
  }
  markRefreshed(id: number, at: string): void {
    this.d.prepare('UPDATE accounts SET last_refresh_at = ? WHERE id = ?').run(at, id);
  }
  setBackfillState(id: number, state: 'pending' | 'done' | 'unavailable' | 'not_found' | 'private'): void {
    this.d.prepare('UPDATE accounts SET backfill_state = ? WHERE id = ?').run(state, id);
  }
  listBackfillPending(): Account[] {
    return this.d.prepare("SELECT * FROM accounts WHERE backfill_state = 'pending' ORDER BY id").all<AccountRow>().map(toAccount);
  }
  /** Inactive (not_found/private) auto-collect accounts whose last probe is older than
   *  `beforeIso` — the weekly liveness re-check that revives a reactivated / now-public account. */
  listInactiveRetryDue(beforeIso: string): Account[] {
    return this.d
      .prepare("SELECT * FROM accounts WHERE backfill_state IN ('not_found', 'private') AND auto_collect = 1 AND (last_refresh_at IS NULL OR last_refresh_at < ?) ORDER BY (last_refresh_at IS NOT NULL), last_refresh_at, id")
      .all<AccountRow>(beforeIso)
      .map(toAccount);
  }
  listRefreshDue(beforeIso: string, autoOnly: boolean): Account[] {
    const sql =
      "SELECT * FROM accounts WHERE (last_refresh_at IS NULL OR last_refresh_at < ?) AND backfill_state NOT IN ('not_found', 'private')" +
      (autoOnly ? ' AND auto_collect = 1' : '') + ' ORDER BY (last_refresh_at IS NOT NULL), last_refresh_at, id';
    return this.d.prepare(sql).all<AccountRow>(beforeIso).map(toAccount);
  }
  delete(id: number): void {
    this.d.prepare('DELETE FROM accounts WHERE id = ?').run(id);
  }
}

class PostRepo {
  constructor(private d: Driver, private settings: SettingsRepo) {}
  upsert(p: UpsertPost): number {
    this.d
      .prepare(
        `INSERT INTO posts(account_id, shortcode, type, url, thumbnail, caption, uploaded_at, view_count)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)
         ON CONFLICT(account_id, shortcode) DO UPDATE SET
           type = excluded.type, url = excluded.url, thumbnail = excluded.thumbnail,
           caption = excluded.caption, uploaded_at = excluded.uploaded_at,
           -- Never wipe a captured view count with a missed one (null/0): a crawl that
           -- fails to read views must not clobber a good value (competitor pattern).
           view_count = CASE WHEN excluded.view_count > 0 THEN excluded.view_count ELSE view_count END`,
      )
      .run(
        p.accountId, p.shortcode, p.type, p.url,
        p.thumbnail ?? null, p.caption ?? null, p.uploadedAt, p.viewCount ?? null,
      );
    return this.d
      .prepare('SELECT id FROM posts WHERE account_id = ? AND shortcode = ?')
      .get<{ id: number }>(p.accountId, p.shortcode)!.id;
  }
  getById(id: number): Post | undefined {
    const r = this.d.prepare('SELECT * FROM posts WHERE id = ?').get<PostRow>(id);
    return r ? toPost(r) : undefined;
  }
  /** Retention cleanup: delete posts uploaded before `cutoffIso` (their daily_stats
   *  cascade via the FK), so the local DB doesn't grow unbounded. Benchmarked posts
   *  are the user's explicit picks and are never auto-purged. Returns rows deleted. */
  purgeUploadedBefore(cutoffIso: string): number {
    return Number(this.d.prepare('DELETE FROM posts WHERE uploaded_at < ? AND is_benchmark = 0').run(cutoffIso).changes);
  }
  listByAccount(accountId: number): Post[] {
    return this.d
      .prepare('SELECT * FROM posts WHERE account_id = ? ORDER BY uploaded_at DESC')
      .all<PostRow>(accountId)
      .map(toPost);
  }
  listBenchmarks(): Post[] {
    return this.d
      .prepare('SELECT * FROM posts WHERE is_benchmark = 1 ORDER BY uploaded_at DESC')
      .all<PostRow>()
      .map(toPost);
  }
  setBenchmark(id: number, on: boolean): void {
    this.d.prepare('UPDATE posts SET is_benchmark = ? WHERE id = ?').run(on ? 1 : 0, id);
  }
  setScript(id: number, scriptText: string): void {
    this.d.prepare('UPDATE posts SET script_text = ? WHERE id = ?').run(scriptText, id);
  }
  /** Reels that are viral (per cfg, at nowIso) and not yet notified — for push alerts.
   *  Uses the latest daily snapshot's comment/view counts (view falls back to the
   *  post's own view_count). */
  listNewlyViral(cfg: ViralConfig, nowIso: string): ViralAlert[] {
    const rows = this.d
      .prepare(
        `SELECT p.id, a.username, p.url, p.uploaded_at, p.view_count AS post_view,
                ds.comment_count, ds.view_count AS snap_view
         FROM posts p
         JOIN accounts a ON a.id = p.account_id
         LEFT JOIN daily_stats ds ON ds.post_id = p.id
           AND ds.date = (SELECT MAX(date) FROM daily_stats WHERE post_id = p.id)
         WHERE p.notified_at IS NULL`,
      )
      .all<{ id: number; username: string; url: string; uploaded_at: string; post_view: number | null; comment_count: number | null; snap_view: number | null }>();
    const out: ViralAlert[] = [];
    for (const r of rows) {
      const viewCount = r.snap_view ?? r.post_view;
      const commentCount = r.comment_count ?? 0;
      if (isViral(r.uploaded_at, viewCount, commentCount, nowIso, cfg)) {
        out.push({ id: r.id, username: r.username, url: r.url, viewCount, commentCount });
      }
    }
    return out;
  }
  /** Stamp posts as notified so they don't alert again. No-op on empty input. */
  markNotified(ids: number[], nowIso: string): void {
    if (ids.length === 0) return;
    const placeholders = ids.map(() => '?').join(', ');
    this.d.prepare(`UPDATE posts SET notified_at = ? WHERE id IN (${placeholders})`).run(nowIso, ...ids);
  }
  withGrowth(accountId: number, cfg: ViralConfig = this.settings.getViralConfig(), nowIso: string = new Date().toISOString()): PostWithGrowth[] {
    const posts = this.listByAccount(accountId);
    const today = nowIso.slice(0, 10);
    const snaps = this.d
      .prepare(
        `SELECT ds.post_id, ds.date, ds.comment_count, ds.like_count, ds.view_count
         FROM daily_stats ds JOIN posts p ON p.id = ds.post_id
         WHERE p.account_id = ? ORDER BY ds.post_id, ds.date`,
      )
      .all<{ post_id: number; date: string; comment_count: number; like_count: number | null; view_count: number | null }>(accountId);

    const byPost = new Map<
      number,
      Array<{ date: string; commentCount: number; likeCount: number | null; viewCount: number | null }>
    >();
    for (const s of snaps) {
      const arr = byPost.get(s.post_id) ?? [];
      arr.push({ date: s.date, commentCount: s.comment_count, likeCount: s.like_count, viewCount: s.view_count });
      byPost.set(s.post_id, arr);
    }

    return posts.map((post) => {
      const series = byPost.get(post.id) ?? [];
      const latest = series.length ? series[series.length - 1]! : null;
      const commentCount = latest ? latest.commentCount : 0;
      const viewCount = latest ? latest.viewCount : post.viewCount;
      const likeSeries = series.map((s) => ({ date: s.date, value: s.likeCount }));
      const viewSeries = series.map((s) => ({ date: s.date, value: s.viewCount }));
      return {
        ...post,
        commentCount,
        likeCount: latest ? latest.likeCount : null,
        viewCount,
        growth1d: commentGrowth(series, 1),
        // Banner "지금 가장 빠르게 크는 · 최근 3일" — anchored on today so a reel not
        // collected in the last 3 days scores 0 and drops out (no stale spike shown as current).
        growth3d: commentGrowth(series, 3, today),
        growth7d: commentGrowth(series, 7),
        likeGrowth1d: seriesGrowth(likeSeries, 1),
        likeGrowth7d: seriesGrowth(likeSeries, 7),
        viewGrowth1d: seriesGrowth(viewSeries, 1),
        viewGrowth2d: seriesGrowth(viewSeries, 2),
        viewGrowth3d: seriesGrowth(viewSeries, 3),
        isViral: isViral(post.uploadedAt, viewCount, commentCount, nowIso, cfg),
        daysTracked: series.length,
      };
    });
  }
}

class DailyStatRepo {
  constructor(private d: Driver) {}
  record(s: NewSnapshot): void {
    const prior = this.d
      .prepare(
        'SELECT date, comment_count, like_count, view_count, follower_count FROM daily_stats WHERE post_id = ? AND date < ? ORDER BY date DESC LIMIT 1',
      )
      .get<{ date: string; comment_count: number; like_count: number | null; view_count: number | null; follower_count: number | null }>(s.postId, s.date);

    // Only treat the prior snapshot as a true 1-day baseline when it is literally
    // yesterday; across a collection gap the delta isn't a real daily change, so
    // leave it null rather than presenting an accumulated jump as one day's growth.
    const consecutive = !!prior && prior.date === dateMinusOneDay(s.date);
    const likeCount = s.likeCount ?? null;
    const viewCount = s.viewCount ?? null;
    const followerCount = s.followerCount ?? null;
    // Day-over-day change, only when both ends are present and consecutive.
    const delta = (now: number | null, was: number | null | undefined): number | null =>
      consecutive && now !== null && was !== null && was !== undefined ? now - was : null;
    const commentDelta = consecutive ? s.commentCount - prior!.comment_count : null;
    const likeDelta = delta(likeCount, prior?.like_count);
    const viewDelta = delta(viewCount, prior?.view_count);
    const followerDelta = delta(followerCount, prior?.follower_count);

    this.d
      .prepare(
        `INSERT OR REPLACE INTO daily_stats(post_id, date, comment_count, comment_delta, like_count, like_delta, view_count, view_delta, follower_count, follower_delta)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      )
      .run(s.postId, s.date, s.commentCount, commentDelta, likeCount, likeDelta, viewCount, viewDelta, followerCount, followerDelta);
  }
  listByPost(postId: number): DailyStat[] {
    return this.d
      .prepare('SELECT * FROM daily_stats WHERE post_id = ? ORDER BY date')
      .all<DailyRow>(postId)
      .map(toDailyStat);
  }
}

/** 'YYYY-MM-DD' shifted back one calendar day (UTC, DST-safe). */
function dateMinusOneDay(date: string): string {
  const d = new Date(`${date}T00:00:00Z`);
  d.setUTCDate(d.getUTCDate() - 1);
  return d.toISOString().slice(0, 10);
}

class SettingsRepo {
  constructor(private d: Driver) {}
  get(key: string): string | undefined {
    return this.d.prepare('SELECT value FROM settings WHERE key = ?').get<{ value: string }>(key)?.value;
  }
  set(key: string, value: string): void {
    this.d.prepare('INSERT OR REPLACE INTO settings(key, value) VALUES (?, ?)').run(key, value);
  }
  getViralThreshold(): number {
    const v = this.get('viral_threshold');
    return v ? Number(v) : DEFAULT_VIRAL_THRESHOLD;
  }
  setViralThreshold(n: number): void {
    this.set('viral_threshold', String(n));
  }
  getViralWindowHours(): number { const v = this.get('viral_window_hours'); return v ? Number(v) : 24; }
  setViralWindowHours(h: number): void { this.set('viral_window_hours', String(h)); }
  getViralMinViews(): number { const v = this.get('viral_min_views'); return v ? Number(v) : 50_000; }
  setViralMinViews(n: number): void { this.set('viral_min_views', String(n)); }
  getViralMinComments(): number { const v = this.get('viral_min_comments'); return v ? Number(v) : 500; }
  setViralMinComments(n: number): void { this.set('viral_min_comments', String(n)); }
  getViralConfig(): ViralConfig {
    return { windowHours: this.getViralWindowHours(), minViews: this.getViralMinViews(), minComments: this.getViralMinComments() };
  }
  /** Collection look-back window in days (how far back each scrape goes); default 7
   *  — a shorter window means fewer timeline scrolls per channel = lower ban risk. */
  getCollectDays(): number {
    const v = this.get('collect_days');
    return v ? Number(v) : 7;
  }
  setCollectDays(days: number): void {
    this.set('collect_days', String(days));
  }
  /** Max channels the daily AUTO pass refreshes; default 300, user-tunable up to 500.
   *  (The manual "지금 수집" button ignores this and runs the full list.) */
  getDailyCap(): number {
    const v = this.get('daily_cap');
    return v ? Number(v) : 300;
  }
  setDailyCap(n: number): void {
    this.set('daily_cap', String(n));
  }
  /** Hour (0-23) of the daily auto-collect; defaults to 23 (오후 11시). */
  getAutoCollectHour(): number {
    const v = this.get('auto_collect_hour');
    return v ? Number(v) : 23;
  }
  setAutoCollectHour(hour: number): void {
    this.set('auto_collect_hour', String(hour));
  }
  /** Master daily auto-collect switch; defaults to enabled (registering channels = track them). */
  isAutoCollectEnabled(): boolean {
    return this.get('auto_collect_enabled') !== '0';
  }
  setAutoCollectEnabled(on: boolean): void {
    this.set('auto_collect_enabled', on ? '1' : '0');
  }
  /** Local YYYY-MM-DD of the last successful auto-collect; null if never run. */
  getLastAutoCollectDate(): string | null {
    return this.get('last_auto_collect_date') ?? null;
  }
  setLastAutoCollectDate(date: string): void {
    this.set('last_auto_collect_date', date);
  }
  /** Launch the app on OS login; defaults to enabled (tray-resident model). */
  isLaunchAtLoginEnabled(): boolean {
    return this.get('launch_at_login') !== '0';
  }
  setLaunchAtLoginEnabled(on: boolean): void {
    this.set('launch_at_login', on ? '1' : '0');
  }
  /** Collect via an OS scheduled task even when the app is fully closed; defaults to
   *  enabled (Windows-only effect; a no-op where Task Scheduler isn't supported). */
  isOsSchedulerEnabled(): boolean {
    return this.get('os_scheduler_enabled') !== '0';
  }
  setOsSchedulerEnabled(on: boolean): void {
    this.set('os_scheduler_enabled', on ? '1' : '0');
  }
  /**
   * Whether the stored Instagram session is still logged in. Defaults to true so
   * a fresh install shows no false "expired" alarm; collection flips it to false
   * when a logged-out state is detected, and a successful login flips it back.
   */
  isSessionValid(): boolean {
    return this.get('session_valid') !== '0';
  }
  setSessionValid(on: boolean): void {
    this.set('session_valid', on ? '1' : '0');
  }
  /** The Instagram account the app is logged in as (captured at login), or null. */
  getLoggedInAccount(): IgAccount | null {
    const v = this.get('logged_in_account');
    if (!v) return null;
    try {
      return JSON.parse(v) as IgAccount;
    } catch {
      return null;
    }
  }
  setLoggedInAccount(acct: IgAccount | null): void {
    if (acct) this.set('logged_in_account', JSON.stringify(acct));
    else this.d.prepare('DELETE FROM settings WHERE key = ?').run('logged_in_account');
  }
  /** Telegram bot token (from @BotFather) for 떡상 push alerts; '' = not configured. */
  getTelegramToken(): string {
    return this.get('telegram_token') ?? '';
  }
  setTelegramToken(token: string): void {
    this.set('telegram_token', token.trim());
  }
  /** Telegram chat id the alerts are sent to; '' = not configured. */
  getTelegramChatId(): string {
    return this.get('telegram_chat_id') ?? '';
  }
  setTelegramChatId(chatId: string): void {
    this.set('telegram_chat_id', chatId.trim());
  }
  /** When one collect surfaces MORE than this many newly-viral reels, send a single
   *  digest instead of one message each; default 5. Set high to always send individual. */
  getNotifyDigestOver(): number {
    const v = this.get('notify_digest_over');
    return v ? Number(v) : 5;
  }
  setNotifyDigestOver(n: number): void {
    this.set('notify_digest_over', String(n));
  }
  /** ISO time until which the deep (logged-in) collect path is paused after a rate-limit; null if not. */
  getCollectCooldownUntil(): string | null {
    return this.get('collect_cooldown_until') ?? null;
  }
  setCollectCooldownUntil(iso: string | null): void {
    if (iso) this.set('collect_cooldown_until', iso);
    else this.d.prepare('DELETE FROM settings WHERE key = ?').run('collect_cooldown_until');
  }
}

export interface Database {
  groups: GroupRepo;
  accounts: AccountRepo;
  posts: PostRepo;
  dailyStats: DailyStatRepo;
  settings: SettingsRepo;
  close(): void;
}

class DatabaseImpl implements Database {
  groups: GroupRepo;
  accounts: AccountRepo;
  posts: PostRepo;
  dailyStats: DailyStatRepo;
  settings: SettingsRepo;
  constructor(private driver: Driver) {
    this.settings = new SettingsRepo(driver);
    this.groups = new GroupRepo(driver);
    this.accounts = new AccountRepo(driver);
    this.posts = new PostRepo(driver, this.settings);
    this.dailyStats = new DailyStatRepo(driver);
  }
  close(): void {
    this.driver.close();
  }
}

/**
 * Open (or create) a database at `path` (':memory:' for tests), apply schema,
 * seed defaults. Defaults to node:sqlite; pass `openBetterSqlite` in Electron.
 */
export function openDatabase(path: string, driverFactory: DriverFactory = openNodeSqlite): Database {
  const driver = driverFactory(path);
  driver.exec(SCHEMA);
  migrate(driver);
  driver.prepare('INSERT OR IGNORE INTO groups(name) VALUES (?)').run(DEFAULT_GROUP);
  return new DatabaseImpl(driver);
}

/** Idempotent column additions for DBs created before a column existed. */
function migrate(driver: Driver): void {
  const cols = new Set(
    driver.prepare('PRAGMA table_info(daily_stats)').all<{ name: string }>().map((r) => r.name),
  );
  for (const c of ['like_count', 'like_delta', 'view_count', 'view_delta']) {
    if (!cols.has(c)) driver.exec(`ALTER TABLE daily_stats ADD COLUMN ${c} INTEGER`);
  }
  const postCols = new Set(
    driver.prepare('PRAGMA table_info(posts)').all<{ name: string }>().map((r) => r.name),
  );
  if (!postCols.has('notified_at')) driver.exec('ALTER TABLE posts ADD COLUMN notified_at TEXT');

  const acc = new Set(
    driver.prepare('PRAGMA table_info(accounts)').all<{ name: string }>().map((r) => r.name),
  );
  const addAcc: Array<[string, string]> = [
    ['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'],
    ['priority', 'INTEGER NOT NULL DEFAULT 0'],
  ];
  for (const [name, def] of addAcc) {
    if (!acc.has(name)) driver.exec(`ALTER TABLE accounts ADD COLUMN ${name} ${def}`);
  }
  // Accounts that already have data predate the backfill model — treat them as done.
  if (!acc.has('backfill_state')) {
    driver.exec("UPDATE accounts SET backfill_state = 'done' WHERE last_collected_at IS NOT NULL");
  }
}