// v5: ai_status enum 에 'disabled' 추가 (v0.2.9 Cut B). settings.ai_enabled=false 일 때
// CaptureService 가 새 노트를 ai_status='disabled' 로 insert + pending_jobs enqueue skip.
//
// SQLite 는 ALTER COLUMN ... CHECK 미지원 → table recreate 패턴.
// 외래키 (note_tags / media / pending_jobs) 는 notes.id 를 참조 + ON DELETE CASCADE 라
// FK off + DROP/RENAME 시 데이터 보존 위해 새 테이블 생성 → INSERT SELECT → DROP old → RENAME new.
// PRAGMA foreign_keys=OFF 안에서 single transaction (runMigrations 가 transaction 으로 감쌈).
import type Database from 'better-sqlite3';

export const version = 5;

export function up(db: Database.Database): void {
  // 기존 인덱스/CHECK 제약을 그대로 유지하되 ai_status 만 'disabled' 추가.
  db.exec(`
    PRAGMA foreign_keys=OFF;
    CREATE TABLE notes_new (
      id                       TEXT PRIMARY KEY,
      raw_text                 TEXT NOT NULL,
      ai_title                 TEXT,
      ai_summary               TEXT,
      ai_status                TEXT NOT NULL
                               CHECK (ai_status IN ('pending','done','failed','disabled')),
      ai_error                 TEXT,
      ai_provider              TEXT,
      ai_generated_at          TEXT,
      title_edited_by_user     INTEGER NOT NULL DEFAULT 0
                               CHECK (title_edited_by_user IN (0,1)),
      summary_edited_by_user   INTEGER NOT NULL DEFAULT 0
                               CHECK (summary_edited_by_user IN (0,1)),
      user_intent              TEXT,
      intent_prompted_at       TEXT,
      created_at               TEXT NOT NULL,
      updated_at               TEXT NOT NULL,
      due_date                 TEXT,
      due_date_edited_by_user  INTEGER NOT NULL DEFAULT 0
                               CHECK (due_date_edited_by_user IN (0,1)),
      deleted_at               TEXT,
      last_recalled_at         TEXT,
      recall_dismissed_at      TEXT,
      status                   TEXT NOT NULL DEFAULT 'active',
      status_changed_at        TEXT,
      move_reason              TEXT
    );
    INSERT INTO notes_new (
      id, raw_text, ai_title, ai_summary, ai_status, ai_error, ai_provider, ai_generated_at,
      title_edited_by_user, summary_edited_by_user, user_intent, intent_prompted_at,
      created_at, updated_at, due_date, due_date_edited_by_user,
      deleted_at, last_recalled_at, recall_dismissed_at,
      status, status_changed_at, move_reason
    )
    SELECT
      id, raw_text, ai_title, ai_summary, ai_status, ai_error, ai_provider, ai_generated_at,
      title_edited_by_user, summary_edited_by_user, user_intent, intent_prompted_at,
      created_at, updated_at, due_date, due_date_edited_by_user,
      deleted_at, last_recalled_at, recall_dismissed_at,
      status, status_changed_at, move_reason
    FROM notes;
    DROP TABLE notes;
    ALTER TABLE notes_new RENAME TO notes;
    CREATE INDEX idx_notes_created_at ON notes(created_at DESC);
    CREATE INDEX idx_notes_ai_status  ON notes(ai_status);
    CREATE INDEX IF NOT EXISTS idx_notes_deleted_at ON notes(deleted_at);
    PRAGMA foreign_keys=ON;
  `);
}
