const db = require("../../../managers/dbConfig");
const { transformDateTimeField } = require("../../../managers/formatDateTime");

// Function to fetch HMOs (with optional filtering)
const filterHMOs = async (req, res) => {
  const { name } = req.query; // Get 'name' filter from client if provided

  let sql = `SELECT * FROM hmo_reg`;

  const values = [];

  if (name) {
    sql += ` WHERE code_name LIKE ?`; // Allow filtering by HMO name
    values.push(`%${name}%`);
  }

  //sql += ` ORDER BY name ASC`; // Always sort alphabetically

  let [rows] = await db.execute(sql, values);

  if (rows.length === 0) throw "No HMOs found";

  rows = transformDateTimeField(rows);

  res.status(200).json({ status: "success", data: rows });
};

const hmoCounter = async (req, res) => {
  const countSql = `SELECT COUNT(*) FROM hmo_reg`;
  const [hmoCount] = await db.execute(countSql);
  const total_hmo = hmoCount[0]["COUNT(*)"];
  //console.log(total_hmo);
  res.status(200).json({
    status: "success",
    message: "counted successfully",
    total_hmo,
  });
};

const getFamiliesAndGroup = async (req, res) => {
  let { name, limit, offset, page } = req.pagination;

  const sql1 = `SELECT *  FROM families WHERE name LIKE ? OR phone_num LIKE ?`;
  const sql = `SELECT * FROM families ORDER BY id DESC LIMIT ? OFFSET ?`;
  let details;

  if (name) {
    const [validName] = await db.execute(sql1, [`%${name}%`, `%${name}%`]);

    if (validName.length === 0) throw "No matching family or group found";
    id = validName[0]["id"];

    details = validName;
  } else {
    [details] = await db.execute(sql, [limit, offset]);
  }

  const data = transformDateTimeField(details);
  //console.log(staffDetails);

  res.status(200).json({
    status: "success",
    page,
    data,
  });
};

module.exports = { filterHMOs, hmoCounter, getFamiliesAndGroup };
