import Database from 'better-sqlite3';
import { config } from '../config.js';

let db: Database.Database | null = null;

export function getDb(): Database.Database {
  if (!db) {
    db = new Database(config.databasePath);
    db.pragma('journal_mode = WAL');
    db.pragma('foreign_keys = ON');
    migrate(db);
  }
  return db;
}

export function closeDb(): void {
  if (db) {
    db.close();
    db = null;
  }
}

function migrate(database: Database.Database): void {
  database.exec(`
    CREATE TABLE IF NOT EXISTS intake_items (
      id TEXT PRIMARY KEY,
      source_type TEXT NOT NULL,
      source_account TEXT,
      source_external_id TEXT NOT NULL,
      source_url TEXT,
      sender_id TEXT,
      sender_name TEXT,
      sender_email TEXT,
      client_project TEXT,
      title TEXT NOT NULL,
      body TEXT NOT NULL DEFAULT '',
      metadata TEXT NOT NULL DEFAULT '{}',
      attachments TEXT NOT NULL DEFAULT '[]',
      parse_warnings TEXT NOT NULL DEFAULT '[]',
      raw_payload TEXT,
      received_at TEXT NOT NULL,
      ingested_at TEXT NOT NULL,
      decision TEXT NOT NULL DEFAULT 'pending',
      status TEXT NOT NULL DEFAULT 'none',
      maj_wp_ticket_id TEXT,
      remote_status TEXT,
      remote_message TEXT,
      last_sync_at TEXT,
      error TEXT,
      claim_token TEXT,
      claim_expires_at TEXT,
      claimed_by TEXT,
      idempotency_key TEXT NOT NULL,
      UNIQUE(source_type, source_account, source_external_id)
    );

    CREATE INDEX IF NOT EXISTS idx_intake_decision ON intake_items(decision);
    CREATE INDEX IF NOT EXISTS idx_intake_status ON intake_items(status);
    CREATE INDEX IF NOT EXISTS idx_intake_idempotency ON intake_items(idempotency_key);

    CREATE TABLE IF NOT EXISTS ignored_senders (
      id TEXT PRIMARY KEY,
      sender_type TEXT NOT NULL,
      sender_key TEXT NOT NULL,
      sender_label TEXT,
      reason TEXT,
      created_at TEXT NOT NULL,
      active INTEGER NOT NULL DEFAULT 1,
      UNIQUE(sender_type, sender_key)
    );

    CREATE TABLE IF NOT EXISTS gmail_tokens (
      id TEXT PRIMARY KEY DEFAULT 'default',
      account_email TEXT,
      access_token TEXT NOT NULL,
      refresh_token TEXT,
      expiry_date INTEGER,
      scope TEXT,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS extension_tokens (
      id TEXT PRIMARY KEY,
      label TEXT,
      token_hash TEXT NOT NULL UNIQUE,
      created_at TEXT NOT NULL,
      last_used_at TEXT,
      active INTEGER NOT NULL DEFAULT 1
    );

    CREATE TABLE IF NOT EXISTS pairing_codes (
      code TEXT PRIMARY KEY,
      expires_at TEXT NOT NULL,
      used INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS sync_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      intake_id TEXT NOT NULL,
      action TEXT NOT NULL,
      payload TEXT,
      created_at TEXT NOT NULL
    );
  `);
}

export function resetDbForTests(): void {
  if (db) {
    db.close();
    db = null;
  }
  const database = getDb();
  database.exec(`
    DELETE FROM intake_items;
    DELETE FROM ignored_senders;
    DELETE FROM extension_tokens;
    DELETE FROM pairing_codes;
    DELETE FROM sync_log;
  `);
}