import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import Database from 'better-sqlite3';
import { runMigrations } from '../../src/main/db/migrations/index.js';
import * as m001 from '../../src/main/db/migrations/m001_initial.js';
import * as m002 from '../../src/main/db/migrations/m002_due_date.js';
import * as m003 from '../../src/main/db/migrations/m003_soft_delete.js';
import * as m004 from '../../src/main/db/migrations/m004_status.js';
import * as m005 from '../../src/main/db/migrations/m005_ai_disabled.js';
import * as m006 from '../../src/main/db/migrations/m006_revisions.js';
import * as m007 from '../../src/main/db/migrations/m007_fts.js';

/** m001~m007 을 수동 적용하고 user_version=7 로 설정 (m008 만 미적용 상태) */
function applyUpTo7(db: Database.Database): void {
  m001.up(db);
  m002.up(db);
  m003.up(db);
  m004.up(db);
  m005.up(db);
  m006.up(db);
  m007.up(db);
  db.pragma('user_version = 7');
}

describe('m008 notebooks migration', () => {
  let db: Database.Database;
  beforeEach(() => { db = new Database(':memory:'); db.pragma('foreign_keys = ON'); });
  afterEach(() => { db.close(); });

  it('fresh DB: notebooks 테이블 + default "기본" notebook 생성', () => {
    runMigrations(db);
    const row = db.prepare(`SELECT name FROM notebooks`).get() as { name: string };
    expect(row.name).toBe('기본');
  });

  it('fresh insert 는 notebook_id NULL 유지 (migration UPDATE 는 migration 시점 행만 채움)', () => {
    runMigrations(db);
    db.prepare(`INSERT INTO notes(id,raw_text,ai_status,created_at,updated_at,status) VALUES('n1','t','pending','2026-05-14','2026-05-14','active')`).run();
    const r = db.prepare(`SELECT notebook_id FROM notes WHERE id='n1'`).get() as { notebook_id: string | null };
    expect(r.notebook_id).toBeNull();  // m008 의 UPDATE 는 migration 시점의 NULL 만 채움
  });

  it('pre-migration 노트가 runMigrations 후 default notebook 으로 backfill 됨', () => {
    applyUpTo7(db);
    // m008 적용 전 상태에서 노트 삽입 (notebook_id 컬럼 없음)
    db.prepare(`INSERT INTO notes(id,raw_text,ai_status,created_at,updated_at,status) VALUES('pre1','hello','pending','2026-05-14','2026-05-14','active')`).run();
    // m008 만 적용
    runMigrations(db);
    const defaultId = (db.prepare(`SELECT id FROM notebooks`).get() as { id: string }).id;
    const r = db.prepare(`SELECT notebook_id FROM notes WHERE id='pre1'`).get() as { notebook_id: string | null };
    expect(r.notebook_id).toBe(defaultId);
  });

  it('pre-migration archived 노트가 runMigrations 후 completed 로 변환됨', () => {
    applyUpTo7(db);
    // m008 적용 전 상태에서 archived 노트 삽입
    db.prepare(`INSERT INTO notes(id,raw_text,ai_status,created_at,updated_at,status) VALUES('arc1','bye','pending','2026-05-14','2026-05-14','archived')`).run();
    // m008 만 적용
    runMigrations(db);
    const r = db.prepare(`SELECT status FROM notes WHERE id='arc1'`).get() as { status: string };
    expect(r.status).toBe('completed');
  });

  it('UNIQUE index 가 같은 이름 중복 INSERT 거부', () => {
    runMigrations(db);
    expect(() =>
      db.prepare(`INSERT INTO notebooks(id,name,created_at,updated_at) VALUES('x','기본','t','t')`).run()
    ).toThrow();
  });

  it('UNIQUE index 가 대소문자 다른 이름도 중복으로 거부 (COLLATE NOCASE)', () => {
    runMigrations(db);
    // 영문 notebook 추가 후 대소문자 변형 삽입 시도
    db.prepare(`INSERT INTO notebooks(id,name,created_at,updated_at) VALUES('nb1','Default','t','t')`).run();
    expect(() =>
      db.prepare(`INSERT INTO notebooks(id,name,created_at,updated_at) VALUES('nb2','default','t','t')`).run()
    ).toThrow();
  });
});
