cod-api/config/index.js

require("dotenv").config();
const { default: mqtt } = require("mqtt");
const sql = require("mssql");

const isProduction = process.env.NODE_ENV === "production";

const endPointWhatsapp = process.env.ENDPOINT_WHATSAPP;

// Config SQL Server
const config = {
  user: process.env.SQL_USERNAME,
  password: process.env.SQL_PASSWORD,
  database: process.env.SQL_DATABASE,
  server: process.env.SQL_HOST,
  port: parseInt(process.env.SQL_PORT, 10),
  options: {
    encrypt: false, // true kalau Azure
    trustServerCertificate: true,
  },
};

// Buat pool global
const poolPromise = new sql.ConnectionPool(config)
  .connect()
  .then(pool => {
    console.log("✅ Koneksi SQL Server berhasil");
    return pool;
  })
  .catch(err => {
    console.error("❌ Gagal koneksi SQL Server:", err);
    process.exit(1);
  });

async function checkConnection() {
  try {
    const pool = await poolPromise;
    await pool.request().query("SELECT 1 AS isConnected");
    console.log("🔍 SQL Server terkoneksi dengan baik");
    return true;
  } catch (error) {
    console.error("⚠️ Gagal cek koneksi SQL Server:", error);
    return false;
  }
}

/**
 * Wrapper query (auto konversi $1 → @p1)
 */
async function query(text, params = []) {
  const pool = await poolPromise;
  const request = pool.request();

  params.forEach((param, i) => {
    request.input(`p${i + 1}`, param);
  });

  // Ubah $1, $2 jadi @p1, @p2
  const sqlText = text.replace(/\$(\d+)/g, (_, num) => `@p${num}`);

  console.log(sqlText, params);
  return request.query(sqlText);
}

/**
 * Validasi tanggal
 */
function isValidDate(dateStr) {
  const d = new Date(dateStr);
  return !isNaN(d.getTime());
}

/**
 * Build filter query (AND)
 */
function buildFilterQuery(filterQuery = [], fixedParams = []) {
  let whereConditions = [];
  let queryParams = [...fixedParams];

  filterQuery.forEach((f) => {
    if (f.param === undefined || f.param === null || f.param === "") return;

    switch (f.type) {
      case "string":
        queryParams.push(`%${f.param}%`);
        whereConditions.push(
          `${f.column} LIKE $${queryParams.length} COLLATE SQL_Latin1_General_CP1_CI_AS`
        );
        break;

      case "number":
        queryParams.push(f.param);
        whereConditions.push(`${f.column} = $${queryParams.length}`);
        break;

      case "boolean":
        queryParams.push(f.param ? 1 : 0);
        whereConditions.push(`${f.column} = $${queryParams.length}`);
        break;

      case "between":
        if (Array.isArray(f.param) && f.param.length === 2) {
          const [from, to] = f.param;
          if (isValidDate(from) && isValidDate(to)) {
            queryParams.push(from);
            queryParams.push(to);
            whereConditions.push(
              `CAST(${f.column} AS DATE) BETWEEN $${queryParams.length - 1} AND $${queryParams.length}`
            );
          }
        }
        break;
    }
  });

  return { whereConditions, whereParamAnd: queryParams };
}

/**
 * Build OR ILIKE (SQL Server pakai LIKE + COLLATE)
 */
function buildStringOrIlike(columnParam, criteria, fixedParams = []) {
  if (!criteria) return { whereOrConditions: "", whereParamOr: fixedParams };

  let orStringConditions = [];
  let queryParams = [...fixedParams];

  columnParam.forEach((column) => {
    if (!column) return;
    queryParams.push(`%${criteria}%`);
    orStringConditions.push(
      `${column} LIKE $${queryParams.length} COLLATE SQL_Latin1_General_CP1_CI_AS`
    );
  });

  const whereClause = orStringConditions.length
    ? `AND (${orStringConditions.join(" OR ")})`
    : "";

  return { whereOrConditions: whereClause, whereParamOr: queryParams };
}

/**
 * Build Date Filter (harian / mingguan / bulanan)
 */
function buildDateFilter(column, type, dateValue, fixedParams = []) {
  let whereCondition = "";
  let queryParams = [...fixedParams];

  if (!dateValue && type !== "monthly") {
    return { whereDateCondition: "", whereDateParams: queryParams };
  }

  switch (type) {
    case "daily": {
      queryParams.push(dateValue);
      whereCondition = `CAST(${column} AS DATE) = $${queryParams.length}`;
      break;
    }

    case "weekly": {
      const startDate = new Date(dateValue);
      if (!isNaN(startDate.getTime())) {
        const endDate = new Date(startDate);
        endDate.setDate(startDate.getDate() + 6);

        queryParams.push(startDate.toISOString().split("T")[0]);
        queryParams.push(endDate.toISOString().split("T")[0]);

        whereCondition = `CAST(${column} AS DATE) BETWEEN $${queryParams.length - 1} AND $${queryParams.length}`;
      }
      break;
    }

    case "monthly": {
      const [year, month] = dateValue.split("-");
      if (year && month) {
        queryParams.push(parseInt(year), parseInt(month));
        whereCondition = `YEAR(${column}) = $${queryParams.length - 1} AND MONTH(${column}) = $${queryParams.length}`;
      }
      break;
    }

    default:
      whereCondition = "";
  }

  return { whereDateCondition: whereCondition, whereDateParams: queryParams };
}

/**
 * Build dynamic UPDATE
 */
function buildDynamicUpdate(table, data, where) {
  data.updated_by = data.userId;
  delete data.userId;

  const setParts = [];
  const values = [];
  let index = 1;

  for (const [key, value] of Object.entries(data)) {
    if (value !== undefined && value !== null) {
      setParts.push(`${key} = $${index++}`);
      values.push(value);
    }
  }

  if (setParts.length === 0) {
    throw new Error("Tidak ada kolom untuk diupdate");
  }

  setParts.push(`updated_at = CURRENT_TIMESTAMP`);

  const whereParts = [];
  for (const [key, value] of Object.entries(where)) {
    whereParts.push(`${key} = $${index++}`);
    values.push(value);
  }

  const query = `
    UPDATE ${table}
    SET ${setParts.join(", ")}
    WHERE ${whereParts.join(" AND ")}
  `;

  return { query, values };
}

/**
 * Build dynamic INSERT
 */
function buildDynamicInsert(table, data) {
  data.created_by = data.userId;
  data.updated_by = data.userId;
  delete data.userId;

  const columns = [];
  const placeholders = [];
  const values = [];
  let index = 1;

  for (const [key, value] of Object.entries(data)) {
    if (value !== undefined && value !== null) {
      columns.push(key);
      placeholders.push(`$${index++}`);
      values.push(value);
    }
  }

  if (columns.length === 0) {
    throw new Error("Tidak ada kolom untuk diinsert");
  }

  columns.push("created_at", "updated_at");
  placeholders.push("CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP");

  const query = `
    INSERT INTO ${table} (${columns.join(", ")})
    VALUES (${placeholders.join(", ")});
    SELECT SCOPE_IDENTITY() as inserted_id;
  `;

  return { query, values };
}

/**
 * Generate kode otomatis
 */
async function generateKode(prefix, tableName, columnName) {
  const pool = await poolPromise;
  const result = await pool.request()
    .input("prefix", sql.VarChar, prefix + "%")
    .query(`
      SELECT TOP 1 ${columnName} as kode
      FROM ${tableName}
      WHERE ${columnName} LIKE @prefix
      ORDER BY ${columnName} DESC
    `);

  let nextNumber = 1;
  if (result.recordset.length > 0) {
    const lastKode = result.recordset[0].kode;
    const lastNumber = parseInt(lastKode.replace(prefix, ""), 10);
    nextNumber = lastNumber + 1;
  }

  return prefix + String(nextNumber).padStart(3, "0");
}

// Koneksi ke broker MQTT
const mqttOptions = {
  clientId: 'express_mqtt_client_' + Math.random().toString(16).substr(2, 8),
  clean: true,
  connectTimeout: 4000,
  username: 'morekmorekmorek', // jika ada
  password: 'morek888', // jika ada
};

const mqttUrl = 'ws://117.102.231.130:7001'; // Ganti dengan broker kamu
const topic = process.env.TOPIC_COD ?? 'morek';

const mqttClient = mqtt.connect(mqttUrl, mqttOptions);

// Saat terkoneksi
mqttClient.on('connect', () => {
  console.log('MQTT connected');

  // Subscribe ke topik tertentu
  mqttClient.subscribe(topic, (err) => {
    if (!err) {
      console.log(`Subscribed to topic "${topic}"`);
    } else {
      console.error('Subscribe error:', err);
    }
  });
});

module.exports = {
  checkConnection,
  query,
  buildFilterQuery,
  buildDateFilter,
  buildStringOrIlike,
  buildDynamicInsert,
  buildDynamicUpdate,
  generateKode,
  endPointWhatsapp,
  mqttClient
};