/**
 * SQLite database layer for UniFi Portal (replaces JSON files).
 * DB file: data/portal.db
 */

const path = require('path');
const fs = require('fs');

const DATA_DIR = path.join(__dirname, 'data');
const DB_PATH = path.join(DATA_DIR, 'portal.db');

let db = null;

function getDb() {
  if (!db) {
    const Database = require('better-sqlite3');
    if (!fs.existsSync(DATA_DIR)) {
      fs.mkdirSync(DATA_DIR, { recursive: true });
    }
    db = new Database(DB_PATH);
    db.pragma('journal_mode = WAL');
    initSchema(db);
    migrateFromJson(db);
  }
  return db;
}

function initSchema(database) {
  database.exec(`
    CREATE TABLE IF NOT EXISTS codes (
      code TEXT PRIMARY KEY,
      createdAt TEXT NOT NULL,
      expiresAt TEXT,
      useCount INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS packages (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      durationMinutes INTEGER NOT NULL,
      price REAL NOT NULL,
      currency TEXT NOT NULL,
      active INTEGER NOT NULL DEFAULT 1,
      sortOrder INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS pending_orders (
      orderId TEXT PRIMARY KEY,
      guestMac TEXT NOT NULL,
      packageId TEXT NOT NULL,
      createdAt TEXT NOT NULL
    );
  `);
}

function migrateFromJson(database) {
  const codesPath = path.join(DATA_DIR, 'codes.json');
  const packagesPath = path.join(DATA_DIR, 'packages.json');
  const ordersPath = path.join(DATA_DIR, 'pending-orders.json');

  const hasCodes = database.prepare('SELECT 1 FROM codes LIMIT 1').get();
  if (!hasCodes && fs.existsSync(codesPath)) {
    try {
      const codes = JSON.parse(fs.readFileSync(codesPath, 'utf8'));
      const insert = database.prepare(
        'INSERT OR IGNORE INTO codes (code, createdAt, expiresAt, useCount) VALUES (?, ?, ?, ?)'
      );
      for (const c of codes) {
        insert.run(
          c.code,
          c.createdAt || new Date().toISOString(),
          c.expiresAt || null,
          c.useCount ?? 0
        );
      }
      console.log(`Migrated ${codes.length} codes from JSON to SQLite`);
    } catch (err) {
      console.error('Migration codes.json:', err.message);
    }
  }

  const hasPackages = database.prepare('SELECT 1 FROM packages LIMIT 1').get();
  if (!hasPackages && fs.existsSync(packagesPath)) {
    try {
      const packages = JSON.parse(fs.readFileSync(packagesPath, 'utf8'));
      const insert = database.prepare(
        'INSERT OR IGNORE INTO packages (id, name, durationMinutes, price, currency, active, sortOrder) VALUES (?, ?, ?, ?, ?, ?, ?)'
      );
      for (const p of packages) {
        insert.run(
          p.id,
          p.name,
          p.durationMinutes ?? 0,
          p.price ?? 0,
          p.currency || 'EUR',
          p.active !== false ? 1 : 0,
          p.sortOrder ?? 0
        );
      }
      console.log(`Migrated ${packages.length} packages from JSON to SQLite`);
    } catch (err) {
      console.error('Migration packages.json:', err.message);
    }
  }

  const hasOrders = database.prepare('SELECT 1 FROM pending_orders LIMIT 1').get();
  if (!hasOrders && fs.existsSync(ordersPath)) {
    try {
      const orders = JSON.parse(fs.readFileSync(ordersPath, 'utf8'));
      const insert = database.prepare(
        'INSERT OR IGNORE INTO pending_orders (orderId, guestMac, packageId, createdAt) VALUES (?, ?, ?, ?)'
      );
      for (const [orderId, o] of Object.entries(orders)) {
        insert.run(orderId, o.guestMac, o.packageId, o.createdAt || new Date().toISOString());
      }
      const count = Object.keys(orders).length;
      if (count) console.log(`Migrated ${count} pending orders from JSON to SQLite`);
    } catch (err) {
      console.error('Migration pending-orders.json:', err.message);
    }
  }
}

// --- Codes ---

function loadCodes() {
  const rows = getDb().prepare('SELECT code, createdAt, expiresAt, useCount FROM codes').all();
  return rows.map((r) => ({
    code: r.code,
    createdAt: r.createdAt,
    expiresAt: r.expiresAt,
    useCount: r.useCount ?? 0
  }));
}

function saveCodes(codes) {
  const database = getDb();
  database.prepare('DELETE FROM codes').run();
  const insert = database.prepare(
    'INSERT INTO codes (code, createdAt, expiresAt, useCount) VALUES (?, ?, ?, ?)'
  );
  for (const c of codes) {
    insert.run(c.code, c.createdAt, c.expiresAt ?? null, c.useCount ?? 0);
  }
}

function findAndValidateCode(codeInput) {
  const normalized = String(codeInput || '').trim().toUpperCase();
  if (!normalized) return null;
  const row = getDb()
    .prepare('SELECT code, createdAt, expiresAt, useCount FROM codes WHERE code = ?')
    .get(normalized);
  if (!row) return null;
  if (row.expiresAt && new Date(row.expiresAt) < new Date()) return null;
  return {
    code: row.code,
    createdAt: row.createdAt,
    expiresAt: row.expiresAt,
    useCount: row.useCount ?? 0
  };
}

function incrementCodeUseCount(codeInput) {
  const normalized = String(codeInput || '').trim().toUpperCase();
  getDb().prepare('UPDATE codes SET useCount = useCount + 1 WHERE code = ?').run(normalized);
}

const CODE_CHARS = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';

function generateCode() {
  let code = '';
  for (let i = 0; i < 8; i++) {
    code += CODE_CHARS.charAt(Math.floor(Math.random() * CODE_CHARS.length));
  }
  return code;
}

function addCode(expiresInMinutes) {
  const code = generateCode();
  const now = new Date().toISOString();
  const expiresAt = expiresInMinutes
    ? new Date(Date.now() + expiresInMinutes * 60 * 1000).toISOString()
    : null;
  getDb()
    .prepare('INSERT INTO codes (code, createdAt, expiresAt, useCount) VALUES (?, ?, ?, 0)')
    .run(code, now, expiresAt);
  return { code, createdAt: now, expiresAt, useCount: 0 };
}

function deleteCode(codeInput) {
  const normalized = String(codeInput || '').trim().toUpperCase();
  const result = getDb().prepare('DELETE FROM codes WHERE code = ?').run(normalized);
  return result.changes > 0;
}

// --- Packages ---

function loadPackages() {
  const rows = getDb()
    .prepare(
      'SELECT id, name, durationMinutes, price, currency, active, sortOrder FROM packages'
    )
    .all();
  return rows.map((r) => ({
    id: r.id,
    name: r.name,
    durationMinutes: r.durationMinutes,
    price: r.price,
    currency: r.currency,
    active: Boolean(r.active),
    sortOrder: r.sortOrder ?? 0
  }));
}

function savePackages(packages) {
  const database = getDb();
  database.prepare('DELETE FROM packages').run();
  const insert = database.prepare(
    'INSERT INTO packages (id, name, durationMinutes, price, currency, active, sortOrder) VALUES (?, ?, ?, ?, ?, ?, ?)'
  );
  for (const p of packages) {
    insert.run(
      p.id,
      p.name,
      p.durationMinutes,
      p.price,
      p.currency || 'EUR',
      p.active !== false ? 1 : 0,
      p.sortOrder ?? 0
    );
  }
}

function findPackageById(id) {
  const row = getDb()
    .prepare(
      'SELECT id, name, durationMinutes, price, currency, active, sortOrder FROM packages WHERE id = ?'
    )
    .get(id);
  if (!row) return null;
  return {
    id: row.id,
    name: row.name,
    durationMinutes: row.durationMinutes,
    price: row.price,
    currency: row.currency,
    active: Boolean(row.active),
    sortOrder: row.sortOrder ?? 0
  };
}

function addPackage({ name, durationMinutes, price, currency, active = true }) {
  const crypto = require('crypto');
  const id = crypto.randomUUID();
  const database = getDb();
  const maxOrder = database.prepare('SELECT COALESCE(MAX(sortOrder), 0) AS m FROM packages').get();
  const sortOrder = (maxOrder?.m ?? 0) + 1;
  database
    .prepare(
      'INSERT INTO packages (id, name, durationMinutes, price, currency, active, sortOrder) VALUES (?, ?, ?, ?, ?, ?, ?)'
    )
    .run(
      id,
      String(name || '').trim(),
      parseInt(durationMinutes, 10) || 60,
      parseFloat(price) || 0,
      String(currency || 'EUR').toUpperCase().slice(0, 3),
      active ? 1 : 0,
      sortOrder
    );
  return findPackageById(id);
}

function updatePackage(id, updates) {
  const current = findPackageById(id);
  if (!current) return null;
  const next = {
    ...current,
    ...(updates.name !== undefined && { name: String(updates.name).trim() }),
    ...(updates.durationMinutes !== undefined && {
      durationMinutes: parseInt(updates.durationMinutes, 10) || current.durationMinutes
    }),
    ...(updates.price !== undefined && { price: parseFloat(updates.price) ?? current.price }),
    ...(updates.currency !== undefined && {
      currency: String(updates.currency).toUpperCase().slice(0, 3)
    }),
    ...(updates.active !== undefined && { active: Boolean(updates.active) }),
    ...(updates.sortOrder !== undefined && { sortOrder: parseInt(updates.sortOrder, 10) })
  };
  getDb()
    .prepare(
      'UPDATE packages SET name = ?, durationMinutes = ?, price = ?, currency = ?, active = ?, sortOrder = ? WHERE id = ?'
    )
    .run(
      next.name,
      next.durationMinutes,
      next.price,
      next.currency,
      next.active ? 1 : 0,
      next.sortOrder,
      id
    );
  return findPackageById(id);
}

function deletePackage(id) {
  const result = getDb().prepare('DELETE FROM packages WHERE id = ?').run(id);
  return result.changes > 0;
}

// --- Pending orders ---

const PENDING_ORDER_TTL_MS = 30 * 60 * 1000;

function addPendingOrder(orderId, { guestMac, packageId }) {
  getDb()
    .prepare(
      'INSERT OR REPLACE INTO pending_orders (orderId, guestMac, packageId, createdAt) VALUES (?, ?, ?, ?)'
    )
    .run(orderId, guestMac, packageId, new Date().toISOString());
}

function getAndRemovePendingOrder(orderId) {
  const row = getDb()
    .prepare('SELECT orderId, guestMac, packageId, createdAt FROM pending_orders WHERE orderId = ?')
    .get(orderId);
  if (!row) return null;
  const created = new Date(row.createdAt).getTime();
  if (Date.now() - created > PENDING_ORDER_TTL_MS) {
    getDb().prepare('DELETE FROM pending_orders WHERE orderId = ?').run(orderId);
    return null;
  }
  getDb().prepare('DELETE FROM pending_orders WHERE orderId = ?').run(orderId);
  return {
    guestMac: row.guestMac,
    packageId: row.packageId,
    createdAt: row.createdAt
  };
}

module.exports = {
  getDb,
  loadCodes,
  saveCodes,
  findAndValidateCode,
  incrementCodeUseCount,
  addCode,
  deleteCode,
  loadPackages,
  savePackages,
  findPackageById,
  addPackage,
  updatePackage,
  deletePackage,
  addPendingOrder,
  getAndRemovePendingOrder,
  PENDING_ORDER_TTL_MS
};