// v6: note_revisions 테이블 + 기존 notes 의 raw_text 를 edited_by='capture' revision 으로 backfill.
// FK ON DELETE CASCADE — notes 영구 삭제 시 revision 도 함께 삭제.
import type Database from 'better-sqlite3';

export const version = 6;

export function up(db: Database.Database): void {
  db.exec(`
    CREATE TABLE note_revisions (
      rev_id     INTEGER PRIMARY KEY AUTOINCREMENT,
      note_id    TEXT NOT NULL,
      raw_text   TEXT NOT NULL,
      edited_at  TEXT NOT NULL,
      edited_by  TEXT NOT NULL DEFAULT 'user'
                 CHECK (edited_by IN ('user','capture')),
      FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE
    );
    CREATE INDEX idx_note_revisions_note_id ON note_revisions(note_id, edited_at DESC);

    INSERT INTO note_revisions (note_id, raw_text, edited_at, edited_by)
      SELECT id, raw_text, created_at, 'capture' FROM notes;
  `);
}
