// v7: notes_fts FTS5 virtual table + trigger 3개 + 기존 notes (status != 'trashed') backfill.
// raw_text/ai_title/ai_summary 는 trigger 자동 sync. tags 는 note_tags JOIN 결과를
// NoteRepository 의 명시 헬퍼 (rebuildFtsTagsForNote) 로 갱신 — Cut D 의 single write path.
import type Database from 'better-sqlite3';

export const version = 7;

export function up(db: Database.Database): void {
  db.exec(`
    CREATE VIRTUAL TABLE notes_fts USING fts5(
      note_id UNINDEXED,
      raw_text,
      ai_title,
      ai_summary,
      tags,
      tokenize='unicode61'
    );

    CREATE TRIGGER notes_fts_ai AFTER INSERT ON notes BEGIN
      INSERT INTO notes_fts (note_id, raw_text, ai_title, ai_summary, tags)
        VALUES (NEW.id, NEW.raw_text, COALESCE(NEW.ai_title, ''), COALESCE(NEW.ai_summary, ''), '');
    END;

    CREATE TRIGGER notes_fts_ad AFTER DELETE ON notes BEGIN
      DELETE FROM notes_fts WHERE note_id = OLD.id;
    END;

    CREATE TRIGGER notes_fts_au AFTER UPDATE ON notes BEGIN
      UPDATE notes_fts
         SET raw_text   = NEW.raw_text,
             ai_title   = COALESCE(NEW.ai_title, ''),
             ai_summary = COALESCE(NEW.ai_summary, '')
       WHERE note_id = NEW.id;
    END;

    INSERT INTO notes_fts (note_id, raw_text, ai_title, ai_summary, tags)
      SELECT
        n.id,
        n.raw_text,
        COALESCE(n.ai_title, ''),
        COALESCE(n.ai_summary, ''),
        COALESCE((SELECT GROUP_CONCAT(t.name, ' ')
                    FROM note_tags nt JOIN tags t ON t.id = nt.tag_id
                   WHERE nt.note_id = n.id), '')
      FROM notes n
      WHERE n.status != 'trashed';
  `);
}
