import type { Database } from 'bun:sqlite';

/**
 * Set up FTS5 virtual table and sync triggers for full-text search.
 * Drizzle Kit cannot manage FTS5 tables, so these are run as idempotent
 * SQL statements after the Drizzle migrations complete.
 */
export function setupFts5(db: Database): void {
  // FTS5 virtual table for full-text search
  db.run(
    "CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts USING fts5(title, description, content, content='articles', content_rowid='id')",
  );

  // Triggers to keep FTS in sync with articles table
  db.run(`CREATE TRIGGER IF NOT EXISTS articles_ai AFTER INSERT ON articles BEGIN
		INSERT INTO articles_fts(rowid, title, description, content) VALUES (new.id, new.title, new.description, new.content);
	END`);
  db.run(`CREATE TRIGGER IF NOT EXISTS articles_ad AFTER DELETE ON articles BEGIN
		INSERT INTO articles_fts(articles_fts, rowid, title, description, content) VALUES('delete', old.id, old.title, old.description, old.content);
	END`);
  db.run(`CREATE TRIGGER IF NOT EXISTS articles_au AFTER UPDATE ON articles BEGIN
		INSERT INTO articles_fts(articles_fts, rowid, title, description, content) VALUES('delete', old.id, old.title, old.description, old.content);
		INSERT INTO articles_fts(rowid, title, description, content) VALUES (new.id, new.title, new.description, new.content);
	END`);

  let needsRebuild = false;
  try {
    // Level 1 compares postings against the external articles content. The default
    // check only validates FTS internals, so a stale document with a valid docsize row
    // (but terms from an older article body) would otherwise go unnoticed.
    db.run("INSERT INTO articles_fts(articles_fts, rank) VALUES('integrity-check', 1)");
    needsRebuild = Boolean(
      db
        .query(
          'SELECT 1 FROM articles AS article WHERE NOT EXISTS (SELECT 1 FROM articles_fts_docsize AS docsize WHERE docsize.id = article.id) LIMIT 1',
        )
        .get(),
    );
  } catch {
    // Treat an unreadable or malformed index as incomplete and reconstruct it from articles.
    needsRebuild = true;
  }

  if (needsRebuild) {
    db.run("INSERT INTO articles_fts(articles_fts) VALUES('rebuild')");
  }
}