import type Database from 'better-sqlite3';

export const version = 1;

export function up(db: Database.Database): void {
  db.exec(`
    CREATE TABLE notes (
      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')),
      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
    );
    CREATE INDEX idx_notes_created_at ON notes(created_at DESC);
    CREATE INDEX idx_notes_ai_status  ON notes(ai_status);

    CREATE TABLE tags (
      id    INTEGER PRIMARY KEY AUTOINCREMENT,
      name  TEXT NOT NULL UNIQUE COLLATE NOCASE
    );
    CREATE TABLE note_tags (
      note_id TEXT NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
      tag_id  INTEGER NOT NULL REFERENCES tags(id),
      source  TEXT NOT NULL CHECK (source IN ('ai','user')),
      PRIMARY KEY (note_id, tag_id)
    );

    CREATE TABLE media (
      id         TEXT PRIMARY KEY,
      note_id    TEXT NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
      kind       TEXT NOT NULL CHECK (kind IN ('image')),
      rel_path   TEXT NOT NULL,
      mime       TEXT NOT NULL,
      bytes      INTEGER NOT NULL,
      created_at TEXT NOT NULL
    );
    CREATE INDEX idx_media_note_id ON media(note_id);

    CREATE TABLE pending_jobs (
      note_id     TEXT PRIMARY KEY REFERENCES notes(id) ON DELETE CASCADE,
      attempts    INTEGER NOT NULL DEFAULT 0,
      next_run_at TEXT NOT NULL,
      last_error  TEXT
    );
  `);
}
