import type Database from 'better-sqlite3';
import { v7 as uuidv7 } from 'uuid';
import type { Notebook } from '@shared/types';

export class NotebookRepository {
  constructor(private db: Database.Database) {}

  list(): Notebook[] {
    const rows = this.db.prepare(
      `SELECT nb.id, nb.name, nb.color, nb.created_at, nb.updated_at,
              (SELECT COUNT(*) FROM notes n
                WHERE n.notebook_id = nb.id AND n.status = 'active') AS note_count
         FROM notebooks nb
        ORDER BY nb.name ASC`
    ).all() as Array<Record<string, unknown>>;
    return rows.map((r) => this.hydrate(r));
  }

  findById(id: string): Notebook | null {
    const r = this.db.prepare(
      `SELECT nb.id, nb.name, nb.color, nb.created_at, nb.updated_at,
              (SELECT COUNT(*) FROM notes n
                WHERE n.notebook_id = nb.id AND n.status = 'active') AS note_count
         FROM notebooks nb WHERE nb.id = ?`
    ).get(id) as Record<string, unknown> | undefined;
    return r ? this.hydrate(r) : null;
  }

  /** name 은 COLLATE NOCASE UNIQUE — case-insensitive 중복 거부. */
  create(input: { name: string; color?: string | null }): Notebook {
    const id = uuidv7();
    const now = new Date().toISOString();
    this.db.prepare(
      `INSERT INTO notebooks(id, name, color, created_at, updated_at) VALUES(?,?,?,?,?)`
    ).run(id, input.name, input.color ?? null, now, now);
    return { id, name: input.name, color: input.color ?? null, createdAt: now, updatedAt: now, noteCount: 0 };
  }

  rename(id: string, name: string): void {
    const now = new Date().toISOString();
    this.db.prepare(`UPDATE notebooks SET name=?, updated_at=? WHERE id=?`).run(name, now, id);
  }

  setColor(id: string, color: string | null): void {
    const now = new Date().toISOString();
    this.db.prepare(`UPDATE notebooks SET color=?, updated_at=? WHERE id=?`).run(color, now, id);
  }

  /** FK RESTRICT 가 메모 잔류 시 throw — ok:false 로 변환. */
  delete(id: string): { ok: true } | { ok: false; reason: 'has_notes' | 'not_found' } {
    const exists = this.db.prepare(`SELECT 1 FROM notebooks WHERE id=?`).get(id);
    if (!exists) return { ok: false, reason: 'not_found' };
    try {
      this.db.prepare(`DELETE FROM notebooks WHERE id=?`).run(id);
      return { ok: true };
    } catch (e) {
      const msg = (e as Error).message;
      if (msg.includes('FOREIGN KEY')) return { ok: false, reason: 'has_notes' };
      throw e;
    }
  }

  /** notes.notebook_id 갱신만 (status 등은 보존). */
  moveNote(noteId: string, notebookId: string): void {
    this.db.prepare(`UPDATE notes SET notebook_id=?, updated_at=? WHERE id=?`)
      .run(notebookId, new Date().toISOString(), noteId);
  }

  private hydrate(r: Record<string, unknown>): Notebook {
    return {
      id: r.id as string,
      name: r.name as string,
      color: (r.color as string | null) ?? null,
      createdAt: r.created_at as string,
      updatedAt: r.updated_at as string,
      noteCount: r.note_count as number
    };
  }
}
