'use strict';
const Database = require('better-sqlite3');
const { checkAgainstCap } = require('./lib/favorites-cap');
const { likePatterns } = require('./lib/search-plan');

function open(dbPath) {
  const db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  db.pragma('synchronous = NORMAL');
  db.exec(`
    CREATE TABLE IF NOT EXISTS favorites (
      id             INTEGER PRIMARY KEY AUTOINCREMENT,
      type           TEXT NOT NULL,
      text           TEXT,
      image          BLOB,
      preview        TEXT,
      source_clip_id INTEGER,
      created_at     REAL NOT NULL,
      favorited_at   REAL NOT NULL,
      UNIQUE(source_clip_id)
    );
    CREATE INDEX IF NOT EXISTS idx_fav_favorited_at ON favorites(favorited_at DESC);
  `);
  return db;
}

function close(db) {
  if (db) db.close();
}

// 列表不取 image BLOB —— 图片走 getImage() 按需拉。
const LIST_COLS = `id, type, text, preview, source_clip_id, created_at, favorited_at,
                   (image IS NOT NULL) AS has_image`;

// 每个连接一套预编译语句，避免每次调用都重新 prepare
function stmts(db) {
  if (!db._stmt) {
    db._stmt = {
      insert: db.prepare(`
        INSERT INTO favorites(type, text, image, preview, source_clip_id, created_at, favorited_at)
        VALUES(?, ?, ?, ?, ?, ?, ?)
      `),
      getMeta: db.prepare(`SELECT ${LIST_COLS} FROM favorites WHERE id = ?`),
      getImage: db.prepare('SELECT image FROM favorites WHERE id = ?'),
      getRaw: db.prepare('SELECT * FROM favorites WHERE id = ?'),
      bySource: db.prepare('SELECT id FROM favorites WHERE source_clip_id = ?'),
      remove: db.prepare('DELETE FROM favorites WHERE id = ?'),
      count: db.prepare('SELECT COUNT(*) AS n FROM favorites'),
      list: db.prepare(`SELECT ${LIST_COLS} FROM favorites ORDER BY favorited_at DESC LIMIT ?`),
    };
  }
  return db._stmt;
}

function rowToClip(row) {
  if (!row) return null;
  return {
    id: row.id,
    type: row.type,
    text: row.text || '',
    hasImage: row.has_image != null ? row.has_image === 1 : row.image != null,
    preview: row.preview || '',
    sourceClipId: row.source_clip_id,
    createdAt: row.created_at,
    favoritedAt: row.favorited_at,
  };
}

function add(db, sourceClip) {
  const cap = checkAgainstCap(db);
  if (!cap.ok) {
    const err = new Error(cap.message);
    err.ok = false; err.code = 'FAV_LIMIT';
    throw err;
  }
  const now = Date.now() / 1000;
  const info = stmts(db).insert.run(
    sourceClip.type,
    sourceClip.type === 'text' ? (sourceClip.text || '') : null,
    sourceClip.type === 'image' ? (sourceClip.image || null) : null,
    sourceClip.preview || '',
    sourceClip.id,
    sourceClip.createdAt || now,
    now,
  );
  return rowToClip(stmts(db).getMeta.get(info.lastInsertRowid));
}

function isFavorited(db, clipId) {
  const row = stmts(db).bySource.get(clipId);
  return row ? row.id : null;
}

function remove(db, favId) {
  return stmts(db).remove.run(favId).changes > 0;
}

function get(db, favId) {
  return rowToClip(stmts(db).getMeta.get(favId));
}

function getImage(db, favId) {
  const row = stmts(db).getImage.get(favId);
  if (!row || !row.image) return null;
  return Buffer.from(row.image).toString('base64');
}

// 原始行（含 image BLOB），仅供主进程"复制收藏到系统剪贴板"用。
// 字段形状（type / text / image）与 clips 表一致，可直接喂 lib/clip-payload。
// 渲染进程不要用它 —— 图片走 getImage() 按需拉。
function getRaw(db, favId) {
  return stmts(db).getRaw.get(favId) || null;
}

function count(db) {
  return stmts(db).count.get().n;
}

function list(db, query = '', limit = 5000) {
  const s = stmts(db);
  // 与历史搜索同语义：每个 token 独立子串 AND，% _ \ 已转义（lib/search-plan）。
  // 不转义时搜 "100%" 里的 % 会当通配符命中一片无关记录。
  const patterns = likePatterns(query);
  if (!patterns.length) return s.list.all(limit).map(rowToClip);
  const where = patterns.map(() => `text LIKE ? ESCAPE '\\'`).join(' AND ');
  const rows = db.prepare(
    `SELECT ${LIST_COLS} FROM favorites WHERE ${where} ORDER BY favorited_at DESC LIMIT ?`
  ).all(...patterns, limit);
  return rows.map(rowToClip);
}

module.exports = {
  open, close, rowToClip, add, isFavorited, remove, get, getImage, getRaw, count, list,
};