import Database from 'better-sqlite3'

export function initSchema(db: Database.Database): void {
  db.exec(`
    -- =============================================
    -- KERN-TABELLEN
    -- =============================================

    CREATE TABLE IF NOT EXISTS entries (
      id         INTEGER PRIMARY KEY AUTOINCREMENT,
      category   TEXT NOT NULL CHECK(category IN ('idea','project','task','event','note','contact','finance')),
      title      TEXT NOT NULL,
      raw_input  TEXT NOT NULL,
      status     TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','completed','archived')),
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    -- =============================================
    -- KATEGORIE-TABELLEN (1:1 zu entries)
    -- =============================================

    CREATE TABLE IF NOT EXISTS ideas (
      entry_id    INTEGER PRIMARY KEY REFERENCES entries(id) ON DELETE CASCADE,
      description TEXT,
      potential   TEXT,
      next_steps  TEXT
    );

    CREATE TABLE IF NOT EXISTS projects (
      entry_id INTEGER PRIMARY KEY REFERENCES entries(id) ON DELETE CASCADE,
      goal     TEXT,
      deadline DATE,
      budget   REAL,
      status   TEXT DEFAULT 'planning' CHECK(status IN ('planning','active','on_hold','completed'))
    );

    CREATE TABLE IF NOT EXISTS tasks (
      entry_id    INTEGER PRIMARY KEY REFERENCES entries(id) ON DELETE CASCADE,
      description TEXT,
      due_date    DATE,
      assigned_to TEXT,
      priority    TEXT DEFAULT 'medium' CHECK(priority IN ('low','medium','high')),
      project_id  INTEGER REFERENCES entries(id) ON DELETE SET NULL
    );

    CREATE TABLE IF NOT EXISTS events (
      entry_id     INTEGER PRIMARY KEY REFERENCES entries(id) ON DELETE CASCADE,
      date         DATE,
      time         TEXT,
      location     TEXT,
      participants TEXT,
      agenda       TEXT
    );

    CREATE TABLE IF NOT EXISTS notes (
      entry_id INTEGER PRIMARY KEY REFERENCES entries(id) ON DELETE CASCADE,
      body     TEXT
    );

    CREATE TABLE IF NOT EXISTS contacts (
      entry_id INTEGER PRIMARY KEY REFERENCES entries(id) ON DELETE CASCADE,
      name     TEXT,
      role     TEXT,
      company  TEXT,
      phone    TEXT,
      email    TEXT,
      notes    TEXT
    );

    CREATE TABLE IF NOT EXISTS finances (
      entry_id     INTEGER PRIMARY KEY REFERENCES entries(id) ON DELETE CASCADE,
      type         TEXT CHECK(type IN ('income','expense')),
      amount       REAL,
      currency     TEXT DEFAULT 'EUR',
      date         DATE,
      fin_category TEXT,
      description  TEXT
    );

    -- =============================================
    -- TAGS
    -- =============================================

    CREATE TABLE IF NOT EXISTS tags (
      id   INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT UNIQUE NOT NULL
    );

    CREATE TABLE IF NOT EXISTS entry_tags (
      entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
      tag_id   INTEGER NOT NULL REFERENCES tags(id)   ON DELETE CASCADE,
      PRIMARY KEY (entry_id, tag_id)
    );

    -- =============================================
    -- GRAPH-VERKNÜPFUNGEN
    -- =============================================

    CREATE TABLE IF NOT EXISTS links (
      id         INTEGER PRIMARY KEY AUTOINCREMENT,
      source_id  INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
      target_id  INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
      relation   TEXT NOT NULL DEFAULT 'related_to',
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    -- =============================================
    -- EINSTELLUNGEN (keine API-Keys)
    -- =============================================

    CREATE TABLE IF NOT EXISTS settings (
      key   TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );

  `)

  // ─── FTS5-MIGRATION: content=entries war falsch, einmalig reparieren ──────
  const ftsRow = db.prepare(
    "SELECT sql FROM sqlite_master WHERE type='table' AND name='entries_fts'"
  ).get() as { sql: string } | undefined

  if (ftsRow?.sql?.includes('content=')) {
    db.exec(`
      DROP TRIGGER IF EXISTS entries_ai;
      DROP TRIGGER IF EXISTS entries_ad;
      DROP TRIGGER IF EXISTS entries_au;
      DROP TRIGGER IF EXISTS entry_tags_ai;
      DROP TRIGGER IF EXISTS entry_tags_ad;
      DROP TABLE IF EXISTS entries_fts;
    `)
  }

  db.exec(`
    -- =============================================
    -- FTS5 VOLLTEXTSUCHE (eigenständig, kein content=)
    -- =============================================

    CREATE VIRTUAL TABLE IF NOT EXISTS entries_fts USING fts5(
      title,
      raw_input,
      tags_text
    );

    -- =============================================
    -- FTS5-TRIGGER: entries
    -- =============================================

    CREATE TRIGGER IF NOT EXISTS entries_ai
    AFTER INSERT ON entries BEGIN
      INSERT INTO entries_fts(rowid, title, raw_input, tags_text)
      VALUES (new.id, new.title, new.raw_input, '');
    END;

    CREATE TRIGGER IF NOT EXISTS entries_ad
    AFTER DELETE ON entries BEGIN
      DELETE FROM entries_fts WHERE rowid = old.id;
    END;

    CREATE TRIGGER IF NOT EXISTS entries_au
    AFTER UPDATE ON entries BEGIN
      UPDATE entries_fts
      SET title = new.title, raw_input = new.raw_input
      WHERE rowid = new.id;
    END;

    -- =============================================
    -- FTS5-TRIGGER: tags (tags_text aktuell halten)
    -- =============================================

    CREATE TRIGGER IF NOT EXISTS entry_tags_ai
    AFTER INSERT ON entry_tags BEGIN
      UPDATE entries_fts
      SET tags_text = (
        SELECT GROUP_CONCAT(t.name, ' ')
        FROM tags t
        JOIN entry_tags et ON t.id = et.tag_id
        WHERE et.entry_id = new.entry_id
      )
      WHERE rowid = new.entry_id;
    END;

    CREATE TRIGGER IF NOT EXISTS entry_tags_ad
    AFTER DELETE ON entry_tags BEGIN
      UPDATE entries_fts
      SET tags_text = (
        SELECT COALESCE(GROUP_CONCAT(t.name, ' '), '')
        FROM tags t
        JOIN entry_tags et ON t.id = et.tag_id
        WHERE et.entry_id = old.entry_id
      )
      WHERE rowid = old.entry_id;
    END;

    -- =============================================
    -- UPDATED_AT TRIGGER
    -- =============================================

    CREATE TRIGGER IF NOT EXISTS entries_update_ts
    AFTER UPDATE ON entries BEGIN
      UPDATE entries SET updated_at = CURRENT_TIMESTAMP WHERE id = new.id;
    END;
  `)
}