'use strict';
const Database = require('better-sqlite3');
const path = require('path');

let _db;

function getDb() {
  if (!_db) {
    _db = new Database(path.join(__dirname, '..', 'data', 'app.db'));
    _db.pragma('journal_mode = WAL');
    _db.pragma('foreign_keys = ON');
  }
  return _db;
}

function initDb() {
  const db = getDb();
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id            INTEGER PRIMARY KEY AUTOINCREMENT,
      username      TEXT UNIQUE NOT NULL COLLATE NOCASE,
      password_hash TEXT NOT NULL,
      name          TEXT NOT NULL,
      role          TEXT NOT NULL CHECK(role IN ('superduperadmin','admin','tech','client')),
      active        INTEGER NOT NULL DEFAULT 1,
      created_at    TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at    TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS sessions (
      sid     TEXT PRIMARY KEY NOT NULL,
      sess    TEXT NOT NULL,
      expired INTEGER NOT NULL
    );
  `);

  // Migrations
  const cols = db.prepare('PRAGMA table_info(users)').all().map(c => c.name);
  if (!cols.includes('company')) {
    db.exec("ALTER TABLE users ADD COLUMN company TEXT NOT NULL DEFAULT ''");
  }
  if (!cols.includes('syncro_customer_id')) {
    db.exec('ALTER TABLE users ADD COLUMN syncro_customer_id INTEGER DEFAULT NULL');
  }

  // Label queue migrations
  const lqCols = db.prepare('PRAGMA table_info(label_queue)').all().map(c => c.name);
  if (lqCols.length && !lqCols.includes('custom_line')) {
    db.exec("ALTER TABLE label_queue ADD COLUMN custom_line TEXT DEFAULT NULL");
  }

  // Label Center queue — cross-device label batching
  db.exec(`
    CREATE TABLE IF NOT EXISTS label_queue (
      id             INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id        INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      asset_id       INTEGER NOT NULL,
      asset_name     TEXT    NOT NULL,
      asset_serial   TEXT,
      customer_name  TEXT    NOT NULL,
      customer_phone TEXT,
      custom_line    TEXT,
      sheet_position INTEGER DEFAULT NULL,
      added_at       TEXT    NOT NULL DEFAULT (datetime('now')),
      UNIQUE(user_id, asset_id),
      UNIQUE(user_id, sheet_position)
    );
    CREATE INDEX IF NOT EXISTS idx_label_queue_user ON label_queue(user_id);
  `);
}

// ── User queries ──────────────────────────────────────────────────────────────

function getUserByUsername(username) {
  return getDb()
    .prepare('SELECT * FROM users WHERE username = ? AND active = 1')
    .get(username);
}

function getUserById(id) {
  return getDb()
    .prepare('SELECT id, username, name, company, syncro_customer_id, role, active, created_at FROM users WHERE id = ?')
    .get(id);
}

function getAllUsers() {
  return getDb()
    .prepare('SELECT id, username, name, company, syncro_customer_id, role, active, created_at FROM users ORDER BY company COLLATE NOCASE, username COLLATE NOCASE')
    .all();
}

function createUser(username, passwordHash, name, role, company = '', syncroCustomerId = null) {
  return getDb()
    .prepare('INSERT INTO users (username, password_hash, name, role, company, syncro_customer_id) VALUES (?, ?, ?, ?, ?, ?)')
    .run(username, passwordHash, name, role, company, syncroCustomerId);
}

function updateUser(id, fields) {
  const allowed = ['name', 'company', 'syncro_customer_id', 'role', 'active', 'password_hash'];
  const filtered = Object.fromEntries(
    Object.entries(fields).filter(([k]) => allowed.includes(k))
  );
  if (!Object.keys(filtered).length) return;
  const sets = Object.keys(filtered).map(k => `${k} = ?`).join(', ');
  return getDb()
    .prepare(`UPDATE users SET ${sets}, updated_at = datetime('now') WHERE id = ?`)
    .run(...Object.values(filtered), id);
}

module.exports = { initDb, getDb, getUserByUsername, getUserById, getAllUsers, createUser, updateUser };