/**
 * gatewayModel.js
 * Raw SQL queries only — uses existing project DB pool directly.
 */

const pool        = require("../configs/dbConnection");
const TABLE       = "payment_gateways";
const AUDIT_TABLE = "payment_gateway_audit";

// ── Helpers ───────────────────────────────────────────────────────────────────
async function query(sql, params = []) {
  const [rows] = await pool.execute(sql, params);
  return rows;
}

async function queryOne(sql, params = []) {
  const rows = await query(sql, params);
  return rows[0] || null;
}

// ── Read ──────────────────────────────────────────────────────────────────────
async function findAll() {
  return query(`SELECT * FROM ${TABLE} ORDER BY display_order ASC`);
}

async function findByKey(gateway_key) {
  return queryOne(`SELECT * FROM ${TABLE} WHERE gateway_key = ?`, [gateway_key]);
}

// ── Write ─────────────────────────────────────────────────────────────────────
async function create(data) {
  const {
    gateway_key, name, logo_url,
    availability_type = "permanent",
    is_enabled    = 1,
    is_default    = 0,
    display_order = 99,
    temp_start    = null,
    temp_end      = null,
    reason        = null,
    updated_by    = "system",
  } = data;

  await query(
    `INSERT INTO ${TABLE}
       (gateway_key, name, logo_url, availability_type,
        is_enabled, is_default, display_order,
        temp_start, temp_end, reason, updated_by)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      gateway_key, name, logo_url, availability_type,
      is_enabled, is_default, display_order,
      temp_start, temp_end, reason, updated_by,
    ]
  );

  return findByKey(gateway_key);
}

async function update(gateway_key, fields, values) {
  values.push(gateway_key);
  await query(
    `UPDATE ${TABLE} SET ${fields.join(", ")} WHERE gateway_key = ?`,
    values
  );
  return findByKey(gateway_key);
}

async function toggle(gateway_key, new_enabled, updated_by) {
  await query(
    `UPDATE ${TABLE} SET is_enabled = ?, updated_by = ? WHERE gateway_key = ?`,
    [new_enabled, updated_by, gateway_key]
  );
  return findByKey(gateway_key);
}

async function remove(gateway_key) {
  return query(`DELETE FROM ${TABLE} WHERE gateway_key = ?`, [gateway_key]);
}

async function resetAll(updated_by) {
  return query(
    `UPDATE ${TABLE}
     SET is_enabled = 1, availability_type = 'permanent',
         temp_start = NULL, temp_end = NULL,
         reason = NULL, updated_by = ?`,
    [updated_by]
  );
}

// ── Audit ─────────────────────────────────────────────────────────────────────
async function insertAudit(gateway_key, changed_by, action, old_values, new_values) {
  return query(
    `INSERT INTO ${AUDIT_TABLE}
       (gateway_key, changed_by, action, old_values, new_values)
     VALUES (?, ?, ?, ?, ?)`,
    [
      gateway_key, changed_by, action,
      old_values ? JSON.stringify(old_values) : null,
      new_values ? JSON.stringify(new_values) : null,
    ]
  );
}

async function getAudit(gateway_key = null, limit = 50) {
  if (gateway_key) {
    return query(
      `SELECT * FROM ${AUDIT_TABLE} WHERE gateway_key = ? ORDER BY changed_at DESC LIMIT ?`,
      [gateway_key, limit]
    );
  }
  return query(
    `SELECT * FROM ${AUDIT_TABLE} ORDER BY changed_at DESC LIMIT ?`,
    [limit]
  );
}

module.exports = {
  findAll,
  findByKey,
  create,
  update,
  toggle,
  remove,
  resetAll,
  insertAudit,
  getAudit,
};