// v8: notebooks 테이블 + notes.notebook_id (FK) + archived → completed 정리.
// CHECK 제약 없는 status 컬럼이라 SQL 변경은 데이터 정리만, enum 단속은 TypeScript 측에서.
import type Database from 'better-sqlite3';
import { v7 as uuidv7 } from 'uuid';

export const version = 8;

const DEFAULT_NOTEBOOK_NAME = '기본';

export function up(db: Database.Database): void {
  const now = new Date().toISOString();
  const defaultId = uuidv7();

  db.exec(`
    CREATE TABLE notebooks (
      id         TEXT PRIMARY KEY,
      name       TEXT NOT NULL,
      color      TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE UNIQUE INDEX idx_notebooks_name ON notebooks(name COLLATE NOCASE);

    ALTER TABLE notes ADD COLUMN notebook_id TEXT
      REFERENCES notebooks(id) ON DELETE RESTRICT;
    CREATE INDEX idx_notes_notebook_id ON notes(notebook_id);
  `);

  db.prepare(
    `INSERT INTO notebooks (id, name, created_at, updated_at) VALUES (?, ?, ?, ?)`
  ).run(defaultId, DEFAULT_NOTEBOOK_NAME, now, now);

  db.prepare(`UPDATE notes SET notebook_id = ? WHERE notebook_id IS NULL`).run(defaultId);

  // archived 잔류 (dogfood 0건 확인됐지만 defensive) → completed 로 통합.
  db.prepare(`UPDATE notes SET status='completed' WHERE status='archived'`).run();
}
