const fs = require('fs');
const path = require('path');
const initSqlJs = require('sql.js');

const DATA_DIR = process.env.DATA_DIR || './data';
fs.mkdirSync(DATA_DIR, { recursive: true });
const DB_PATH = path.join(DATA_DIR, 'app.sqlite3');

let _db = null;

function getDb() {
  if (_db) return _db;
  throw new Error('DB not initialized. Call initDb() first.');
}

async function initDb() {
  const SQL = await initSqlJs();
  let buf;
  if (fs.existsSync(DB_PATH)) {
    buf = fs.readFileSync(DB_PATH);
  }
  _db = buf ? new SQL.Database(buf) : new SQL.Database();
  _db.run(`
    CREATE TABLE IF NOT EXISTS cache (
      key TEXT PRIMARY KEY, namespace TEXT NOT NULL,
      value TEXT NOT NULL, expires_at INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_cache_ns ON cache(namespace);
    CREATE TABLE IF NOT EXISTS jobs (
      id TEXT PRIMARY KEY, status TEXT NOT NULL, progress TEXT,
      request_json TEXT NOT NULL, result_json TEXT,
      error_code TEXT, error_message TEXT,
      created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS usage (
      month TEXT PRIMARY KEY,
      apify_calls INTEGER NOT NULL DEFAULT 0,
      cache_hits INTEGER NOT NULL DEFAULT 0,
      estimated_apify_usd REAL NOT NULL DEFAULT 0.0
    );
  `);
  saveDb();
  return _db;
}

function saveDb() {
  if (!_db) return;
  const data = _db.export();
  fs.writeFileSync(DB_PATH, Buffer.from(data));
}

// Cache
const crypto = require('crypto');
function cacheKey(namespace, keyParts) {
  const canonical = JSON.stringify(keyParts, Object.keys(keyParts).sort());
  const digest = crypto.createHash('sha256').update(canonical).digest('hex').slice(0, 32);
  return `${namespace}:${digest}`;
}

function getCached(namespace, keyParts) {
  const key = cacheKey(namespace, keyParts);
  const now = Math.floor(Date.now() / 1000);
  const db = getDb();
  const rows = db.exec('SELECT value, expires_at FROM cache WHERE key = ?', [key]);
  if (!rows.length || !rows[0].values.length) return null;
  const [value, expires_at] = rows[0].values[0];
  if (expires_at < now) {
    db.run('DELETE FROM cache WHERE key = ?', [key]);
    saveDb();
    return null;
  }
  try { return JSON.parse(value); } catch {
    db.run('DELETE FROM cache WHERE key = ?', [key]);
    saveDb();
    return null;
  }
}

function setCached(namespace, keyParts, value, ttlSeconds) {
  const key = cacheKey(namespace, keyParts);
  const expires_at = Math.floor(Date.now() / 1000) + ttlSeconds;
  const db = getDb();
  db.run('INSERT OR REPLACE INTO cache(key,namespace,value,expires_at) VALUES (?,?,?,?)',
    [key, namespace, JSON.stringify(value), expires_at]);
  saveDb();
}

async function cachedCall(namespace, keyParts, ttlSeconds, fetchFn, onHit) {
  const hit = getCached(namespace, keyParts);
  if (hit !== null) { if (onHit) onHit(); return hit; }
  const value = await fetchFn();
  setCached(namespace, keyParts, value, ttlSeconds);
  return value;
}

// Jobs
function createJob(id, payload) {
  const now = Math.floor(Date.now() / 1000);
  getDb().run('INSERT INTO jobs(id,status,progress,request_json,created_at,updated_at) VALUES (?,?,?,?,?,?)',
    [id, 'queued', null, JSON.stringify(payload), now, now]);
  saveDb();
}

function getJob(id) {
  const rows = getDb().exec('SELECT * FROM jobs WHERE id = ?', [id]);
  if (!rows.length || !rows[0].values.length) return null;
  const cols = rows[0].columns;
  const vals = rows[0].values[0];
  const obj = {};
  cols.forEach((c, i) => obj[c] = vals[i]);
  if (obj.result_json) obj.result = JSON.parse(obj.result_json);
  return obj;
}

function updateJob(id, fields) {
  const now = Math.floor(Date.now() / 1000);
  const sets = ['updated_at = ?'];
  const vals = [now];
  if (fields.status) { sets.push('status = ?'); vals.push(fields.status); }
  if (fields.progress !== undefined) { sets.push('progress = ?'); vals.push(fields.progress); }
  if (fields.result) { sets.push('result_json = ?'); vals.push(JSON.stringify(fields.result)); }
  if (fields.error_code) { sets.push('error_code = ?'); vals.push(fields.error_code); }
  if (fields.error_message) { sets.push('error_message = ?'); vals.push(fields.error_message); }
  vals.push(id);
  getDb().run(`UPDATE jobs SET ${sets.join(', ')} WHERE id = ?`, vals);
  saveDb();
}

// Usage
function monthKey() {
  return new Date().toISOString().slice(0, 7);
}
function ensureUsageRow(db, month) {
  db.run('INSERT OR IGNORE INTO usage(month,apify_calls,cache_hits,estimated_apify_usd) VALUES (?,0,0,0.0)', [month]);
}
function recordApifyCall(usd) {
  const month = monthKey();
  const db = getDb();
  ensureUsageRow(db, month);
  db.run('UPDATE usage SET apify_calls = apify_calls + 1, estimated_apify_usd = estimated_apify_usd + ? WHERE month = ?', [usd, month]);
  saveDb();
}
function recordCacheHit() {
  const month = monthKey();
  const db = getDb();
  ensureUsageRow(db, month);
  db.run('UPDATE usage SET cache_hits = cache_hits + 1 WHERE month = ?', [month]);
  saveDb();
}
function getUsage() {
  const month = monthKey();
  const db = getDb();
  ensureUsageRow(db, month);
  saveDb();
  const rows = db.exec('SELECT * FROM usage WHERE month = ?', [month]);
  if (!rows.length || !rows[0].values.length) return { month, apify_calls: 0, cache_hits: 0, cache_hit_rate: 0, estimated_apify_usd: 0 };
  const cols = rows[0].columns;
  const vals = rows[0].values[0];
  const r = {};
  cols.forEach((c, i) => r[c] = vals[i]);
  const total = r.apify_calls + r.cache_hits;
  return {
    month: r.month,
    apify_calls: r.apify_calls,
    cache_hits: r.cache_hits,
    cache_hit_rate: total ? Math.round(r.cache_hits / total * 1000) / 1000 : 0,
    estimated_apify_usd: Math.round(r.estimated_apify_usd * 10000) / 10000,
  };
}

module.exports = { initDb, getCached, setCached, cachedCall, createJob, getJob, updateJob, recordApifyCall, recordCacheHit, getUsage };
