const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.join(__dirname, '..', 'data', 'training.db');
let db = null;

function getDb() {
  if (!db) {
    db = new sqlite3.Database(dbPath);
    db.configure('busyTimeout', 5000);
  }
  return db;
}

function run(sql, params = []) {
  return new Promise((resolve, reject) => {
    getDb().run(sql, params, function(err) {
      if (err) reject(err);
      else resolve(this);
    });
  });
}

function get(sql, params = []) {
  return new Promise((resolve, reject) => {
    getDb().get(sql, params, (err, row) => {
      if (err) reject(err);
      else resolve(row);
    });
  });
}

function all(sql, params = []) {
  return new Promise((resolve, reject) => {
    getDb().all(sql, params, (err, rows) => {
      if (err) reject(err);
      else resolve(rows || []);
    });
  });
}

module.exports = {
  // Returns a Promise that resolves only after every required table exists.
  // await this before accepting any requests or running any scripts.
  init() {
    const queries = [
      `CREATE TABLE IF NOT EXISTS agencies (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        code TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      `CREATE TABLE IF NOT EXISTS learners (
        id INTEGER PRIMARY KEY,
        agency_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        email TEXT,
        started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        completed_at DATETIME,
        passed BOOLEAN DEFAULT 0,
        score_json TEXT,
        certificate_url TEXT,
        FOREIGN KEY (agency_id) REFERENCES agencies(id)
      )`,
      `CREATE TABLE IF NOT EXISTS module_progress (
        id INTEGER PRIMARY KEY,
        learner_id INTEGER NOT NULL,
        module_num INTEGER NOT NULL,
        completed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (learner_id) REFERENCES learners(id),
        UNIQUE(learner_id, module_num)
      )`,
      `CREATE TABLE IF NOT EXISTS admin_config (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL
      )`,
      // Admin users — replaces env-var / admin_config based auth
      `CREATE TABLE IF NOT EXISTS admin_users (
        id INTEGER PRIMARY KEY,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`
    ];

    // Run each CREATE TABLE in sequence so callers can await this method
    // and be certain every table exists before proceeding.
    return queries.reduce(
      (chain, query) => chain.then(() => run(query)),
      Promise.resolve()
    );
  },

  seedAgencies() {
    get('SELECT * FROM agencies WHERE code = ?', ['DEMO123'])
      .then(row => {
        if (!row) {
          run('INSERT INTO agencies (name, code) VALUES (?, ?)', ['Demo Agency', 'DEMO123']);
        }
      })
      .catch(err => console.error('Seed error:', err));

    get('SELECT * FROM agencies WHERE code = ?', ['TEST456'])
      .then(row => {
        if (!row) {
          run('INSERT INTO agencies (name, code) VALUES (?, ?)', ['Test Agency', 'TEST456']);
        }
      })
      .catch(err => console.error('Seed error:', err));
  },

  createLearner(agencyId, name, email = null) {
    return new Promise((resolve, reject) => {
      getDb().run(
        'INSERT INTO learners (agency_id, name, email) VALUES (?, ?, ?)',
        [agencyId, name, email || null],
        function(err) {
          if (err) reject(err);
          else resolve(this.lastID);
        }
      );
    });
  },

  getLearner(id) {
    return get(`
      SELECT l.*, a.name as agency_name, a.code as agency_code
      FROM learners l
      LEFT JOIN agencies a ON l.agency_id = a.id
      WHERE l.id = ?
    `, [id]);
  },

  getCompletedModules(learnerId) {
    return all('SELECT module_num FROM module_progress WHERE learner_id = ? ORDER BY module_num', [learnerId])
      .then(rows => rows.map(r => r.module_num));
  },

  completeModule(learnerId, moduleNum) {
    return run(
      'INSERT OR IGNORE INTO module_progress (learner_id, module_num) VALUES (?, ?)',
      [learnerId, moduleNum]
    );
  },

  setExamResult(learnerId, passed, scoreJson) {
    return run(
      'UPDATE learners SET passed = ?, score_json = ?, completed_at = CURRENT_TIMESTAMP WHERE id = ?',
      [passed ? 1 : 0, scoreJson, learnerId]
    );
  },

  setExamRetry(learnerId) {
    return run('UPDATE learners SET passed = 0, score_json = NULL WHERE id = ?', [learnerId]);
  },

  setCertificateUrl(learnerId, url) {
    return run('UPDATE learners SET certificate_url = ? WHERE id = ?', [url, learnerId]);
  },

  getAgencyByCode(code) {
    return get('SELECT * FROM agencies WHERE code = ?', [code]);
  },

  getAllAgencies() {
    return all('SELECT * FROM agencies ORDER BY name');
  },

  addAgency(name, code) {
    return new Promise((resolve, reject) => {
      getDb().run(
        'INSERT INTO agencies (name, code) VALUES (?, ?)',
        [name, code],
        function(err) {
          if (err) reject(err);
          else resolve(this.lastID);
        }
      );
    });
  },

  getAllLearners() {
    return all(`
      SELECT l.*, a.name as agency_name, a.code as agency_code
      FROM learners l
      LEFT JOIN agencies a ON l.agency_id = a.id
      ORDER BY l.completed_at DESC, l.started_at DESC
    `);
  },

  getLearnerStats() {
    return Promise.all([
      get('SELECT COUNT(*) as count FROM learners'),
      get('SELECT COUNT(*) as count FROM learners WHERE completed_at IS NOT NULL'),
      get('SELECT COUNT(*) as count FROM learners WHERE passed = 1')
    ]).then(([total, completed, passed]) => ({
      total: total?.count || 0,
      completed: completed?.count || 0,
      passed: passed?.count || 0
    }));
  },

  getAdminConfig(key) {
    return get('SELECT value FROM admin_config WHERE key = ?', [key])
      .then(row => row ? row.value : null);
  },

  setAdminConfig(key, value) {
    return run(
      'INSERT OR REPLACE INTO admin_config (key, value) VALUES (?, ?)',
      [key, value]
    );
  },

  // ── Admin user methods ────────────────────────────────────────────────────

  getAdminUserByUsername(username) {
    return get('SELECT * FROM admin_users WHERE username = ?', [username]);
  },

  getAdminUserById(id) {
    return get('SELECT * FROM admin_users WHERE id = ?', [id]);
  },

  createAdminUser(username, passwordHash) {
    return new Promise((resolve, reject) => {
      getDb().run(
        'INSERT INTO admin_users (username, password_hash) VALUES (?, ?)',
        [username, passwordHash],
        function(err) {
          if (err) reject(err);
          else resolve(this.lastID);
        }
      );
    });
  },

  updateAdminPassword(adminId, passwordHash) {
    return run(
      'UPDATE admin_users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [passwordHash, adminId]
    );
  },

  // Returns the first admin user found, or undefined if none exist.
  // Used by the bootstrap route to detect whether setup has already been done.
  getAnyAdminUser() {
    return get('SELECT id FROM admin_users LIMIT 1');
  }
};
