// latest backup of chat bot using gpt open ai 22/10/25

const { OpenAI } = require("openai");
const {
  OPENAI_API_KEY,
  PHONE_SALES,
  baseUrl,
  DEEPSEEK_API_KEY,
} = require("../constants/constant");
const sequelize = require("../config/dbconnection");
const axios = require("axios");
const { empty } = require("../Helpers/Common");
const moment = require("moment");
const Settings = require("../models/settings");


const configuration = {
  organization: "org-i9we2mCl1OrkcQrbdceZph1l",
  apiKey: OPENAI_API_KEY,
};

const openai = new OpenAI(configuration);

let includeLabourInstallService = true;
let laborInstallWhereCondition = includeLabourInstallService ? '' : ' AND rss.labour_cost_service_id != 10'

// const openai = new OpenAI({
//   baseURL: 'https://api.deepseek.com',
//   apiKey: DEEPSEEK_API_KEY
// });

//function to get the status of an order
const getOrderStatus = async (data) => {
  const orderNumber = data.orderNumber;

  try {
    const [results] = await sequelize.query(
      `
      SELECT
        r.id AS request_id,
        co.id AS order_id,
        r.status AS request_status,
        r.min_payment_status,
        r.installation_acceptance,
        pc.name AS project_category,
        i.status AS installation_status,
        i.complete_status,
        COALESCE(cop.product_count, 0) AS product_count,
        COALESCE(rss.service_count, 0) AS service_count,
        COALESCE(inst_counts.installation_count, 0) AS installation_count,
        COALESCE(total_inst_counts.total_installation_count, 0) AS total_installation_count
      FROM customer_orders co
      LEFT JOIN requests r ON co.request_id = r.id
      LEFT JOIN project_categories pc ON pc.id = r.project_category
      LEFT JOIN (
        SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status
        FROM installations
        GROUP BY request_id
      ) i ON i.request_id = r.id
      LEFT JOIN (
        SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, CAST(SUM(cop.location_quantity) AS UNSIGNED) AS product_count
        FROM customer_orders co
        JOIN customer_order_products cop ON co.id = cop.order_id
        WHERE cop.material = 0
        GROUP BY co.request_id
      ) cop ON r.id = cop.request_id
      LEFT JOIN (
        SELECT co.request_id, SUM(rss.qty) AS location_quantity, COUNT(rss.qty) AS service_count
        FROM customer_orders co
        JOIN request_services rss ON co.request_id = rss.request_id
        GROUP BY co.request_id
      ) rss ON r.id = rss.request_id
      LEFT JOIN (
        SELECT request_id, COUNT(*) AS installation_count
        FROM installations
        WHERE status = 4
        GROUP BY request_id
      ) inst_counts ON inst_counts.request_id = r.id
      LEFT JOIN (
        SELECT request_id, COUNT(*) AS total_installation_count
        FROM installations
        GROUP BY request_id
      ) total_inst_counts ON total_inst_counts.request_id = r.id
      WHERE co.id = :orderNumber
      `,
      {
        replacements: { orderNumber },
        type: sequelize.QueryTypes.SELECT,
      }
    );

    const data = results;

    if (!data) return "Order not found.";

    // Evaluate the new status logic
    if (
      data.request_status === 2 ||
      (data.request_status === 3 && data.min_payment_status === 0)
    ) {
      return "Open";
    }

    if (data.request_status === 3 && data.min_payment_status === 1) {
      return "Approved";
    }

    if (data.request_status === 4 && data.installation_status === null) {
      return "In Production";
    }

    if (
      data.installation_status !== null &&
      ![4, 5].includes(data.installation_status) &&
      ["Service Only"].includes(data.project_category) &&
      data.installation_count < data.service_count
    ) {
      return "Installation";
    }

    if (
      data.installation_status !== null &&
      ![4, 5].includes(data.installation_status) &&
      data.complete_status === 2 &&
      data.installation_count < data.product_count
    ) {
      return "Pickup";
    }

    if (
      data.installation_status !== null &&
      ![4, 5].includes(data.installation_status) &&
      data.complete_status === 3 &&
      data.installation_count < data.product_count
    ) {
      return "Shipping";
    }

    if (
      data.installation_status !== null &&
      data.installation_acceptance !== null &&
      (data.installation_count >= data.product_count ||
        data.installation_count >= data.service_count)
    ) {
      return "Completed";
    }

    if (
      data.installation_status !== null &&
      data.installation_acceptance === null &&
      (data.installation_count >= data.product_count ||
        data.installation_count >= data.service_count)
    ) {
      return "Invoicing";
    }

    return "Order in unknown state.";
  } catch (error) {
    console.error(error);
    return "Error getting order status.";
  }
};

// function to invite a client to Signwise
const inviteClient = async (email) => {
  try {
    const result = await axios.post(baseUrl + "/client/invite", email);
    return result.data.message;
  } catch (error) {
    console.error(error);
    return "Error inviting client";
  }
};

// function to check if the user is admin
const checkAdminAccess = async (user) => {
  if (user && user.user_type == 1) {
    return "user is admin";
  } else {
    return "user is not admin";
  }
};

// function to  get the sample images of signboards
const fetchSampleImages = async () => {
  try {
    const result = await sequelize.query(`SELECT * FROM sample_images`, {
      type: sequelize.QueryTypes.SELECT,
    });
    if (result) {
      return result;
    } else {
      return "Error in fetching the details";
    }
  } catch (error) {
    console.log(error);
  }
};

//Get total sales order count
const getSalesOrderCount = async () => {
  const [results] = await sequelize.query(
    "SELECT COUNT(*) AS count FROM requests where quotation = 0 AND status != 0"
  );
  return `There are ${results[0].count} total sales orders.`;
};

const getEstimateCount = async () => {
  const [results] = await sequelize.query(
    "SELECT COUNT(*) AS count FROM requests WHERE quotation = 1 AND status = 8 AND status != 0"
  );
  return `There are ${results[0].count} total estimates.`;
};

const getDesignOrderCount = async () => {
  const [results] = await sequelize.query(
    "SELECT COUNT(*) AS count FROM design_orders WHERE status != 3 AND status != 0"
  );
  return `There are ${results[0].count} total design orders.`;
};

const getJobCount = async () => {
  const [results] = await sequelize.query(`
    SELECT COUNT(*) AS count FROM projects
    WHERE status != 3
  `);
  return `There are ${results[0].count} total jobs.`;
};

//Get customer/client by order number
const getCustomerByOrder = async ({ orderNumber }) => {
  const [results] = await sequelize.query(
    `SELECT c.name FROM customer_orders so 
    JOIN partners c ON so.customer_id = c.id 
    LEFT JOIN requests r ON so.request_id = r.id 
    WHERE r.status != 0 AND so.id = :orderNumber`,
    { replacements: { orderNumber }, type: sequelize.QueryTypes.SELECT }
  );

  if (results.length === 0) {
    return "No such order found.";
  }
  return `Order #${orderNumber} is for customer: ${results?.name}.`;
};

// Count of sales orders by status
const getSalesOrderCountByStatus = async ({ status }) => {
  console.log("getSalesOrderCountByStatus", status);
  const normalizedStatuses = status.map((s) => s.toLowerCase());
  const results = {};

  for (const currentStatus of normalizedStatuses) {
    let whereClause = " r.status <> 0 AND r.status <> 8 AND r.quotation = 0 ";
    if (currentStatus === "open") {
      whereClause += ` AND ((r.status=2) OR (r.status=3 AND r.min_payment_status=0)) `;
    } else if (["accepted", "approved"].includes(currentStatus)) {
      whereClause += ` AND (r.status=3 AND r.min_payment_status =1)  `;
    } else if (currentStatus === "in production") {
      whereClause += ` AND r.status=4 and i.status IS NULL `;
    } else if (["installation", "pickup", "shipping"].includes(currentStatus)) {
      const completeStatus =
        currentStatus === "installation"
          ? 1
          : currentStatus === "pickup"
            ? 2
            : 3;
      whereClause += ` AND i.status IS NOT NULL AND i.status != 4 AND i.status != 5 AND i.complete_status = ${completeStatus}  AND ( 
            COALESCE(inst_counts.installation_count, 0) < COALESCE(cop.product_count, 0) 
            OR (pc.name = 'Service Only' AND COALESCE(inst_counts.installation_count, 0) < COALESCE(rss.service_count, 0))
            OR (COALESCE(cop.product_count, 0) = 0 AND COALESCE(rss.service_count, 0) > 0 AND COALESCE(inst_counts.installation_count, 0) < COALESCE(rss.service_count, 0))
          ) `;
    } else if (currentStatus === "completed") {
      whereClause += ` AND i.status IS NOT NULL AND ( inst_counts.installation_count >= cop.product_count OR inst_counts.installation_count >= rss.service_count ) AND r.installation_acceptance IS NOT NULL `;
    } else if (currentStatus === "invoicing") {
      whereClause += ` AND i.status IS NOT NULL AND ( inst_counts.installation_count >= cop.product_count OR inst_counts.installation_count >= rss.service_count ) AND r.installation_acceptance IS NULL `;
    } else {
      results[currentStatus] = 0;
      continue;
    }
    let WhereCondition = ` r.status <> 0 and r.status <> 8 `;
    if (!empty(whereClause)) {
      WhereCondition = whereClause;
    }
    const [countRes] = await sequelize.query(`
          SELECT COUNT(r.id) as totalCount FROM requests r
          LEFT JOIN project_categories pc ON pc.id = r.project_category 
          LEFT JOIN (select count(c.id) as cnt,c.request_id from request_messages c 
          left join partners p on p.id=c.partner_id where c.timestamp>=COALESCE((select rml.timestamp from request_message_last_seen rml where rml.request_id=c.request_id and rml.user_id=1 and rml.partner_id=c.partner_id),0) and c.user_id!=1 group by c.request_id) rm ON r.id=rm.request_id
          LEFT JOIN (SELECT COUNT(id) as cnt,request_id FROM request_proposals where status=1 group by request_id) rps ON rps.request_id=r.id 
          left join customer_orders c_ord on c_ord.request_id = r.id 
          LEFT JOIN  community com ON com.id=r.community and FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1
          JOIN request_partners rp ON rp.request_id=r.id
          left join partners p2 on p2.id = c_ord.customer_id 
          left join customer_type ct on p2.customerTypeId = ct.id 
          left join (SELECT SUM(amount) AS totalAmount,request_id FROM request_payments WHERE status = 1 GROUP BY request_id) reqPay on reqPay.request_id = r.id 
          left join users u2 on u2.id = p2.user_id 
          LEFT JOIN (SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status FROM installations GROUP BY 
          request_id) i ON i.request_id = r.id 
          LEFT JOIN (
            SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, CAST(SUM(cop.location_quantity) AS UNSIGNED) AS product_count
            FROM customer_orders co
            JOIN customer_order_products cop ON co.id = cop.order_id
            WHERE cop.material = 0
            GROUP BY co.request_id
          ) cop ON r.id = cop.request_id 
          LEFT JOIN (
            SELECT co.request_id, SUM(rss.qty) AS location_quantity, COUNT(rss.qty) AS service_count
            FROM customer_orders co
            JOIN  request_services rss ON co.request_id = rss.request_id
            GROUP BY co.request_id
          ) rss ON r.id = rss.request_id 
          LEFT JOIN (SELECT request_id, COUNT(*) AS installation_count FROM installations WHERE status = 4 GROUP BY request_id) inst_counts ON inst_counts.request_id = r.id 
          LEFT JOIN (SELECT request_id, COUNT(*) AS total_installation_count FROM installations GROUP BY request_id) total_inst_counts ON total_inst_counts.request_id = r.id 
          JOIN users u ON r.added_by=u.id 
          WHERE ${WhereCondition} `);
    results[currentStatus] = countRes[0].totalCount || 0;
  }

  return results;
};

const getEstimateCountByStatus = async ({ status }) => {
  const normalizedStatuses = status.map((s) => s.toLowerCase());
  const results = {};

  for (const currentStatus of normalizedStatuses) {
    let whereClause = "r.status <> 0 AND r.quotation = 1";

    if (currentStatus === "open") {
      whereClause += ` AND r.status = 8 AND r.quotation_status = 2`;
    } else if (currentStatus === "approved") {
      whereClause += ` AND r.status = 8 AND r.quotation_status = 4`;
    } else if (currentStatus === "archive") {
      whereClause = `r.quotation = 0 AND r.quotation_status = 4`;
    } else if (currentStatus === "all") {
      whereClause = `r.status = 8`;
    } else {
      results[currentStatus.toUpperCase()] = 0;
      continue;
    }

    const [countRes] = await sequelize.query(`
      SELECT COUNT(r.id) AS totalCount FROM requests r
      LEFT JOIN project_categories pc ON pc.id = r.project_category
      LEFT JOIN (
        SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status
        FROM installations
        GROUP BY request_id
      ) i ON i.request_id = r.id
      LEFT JOIN (
        SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity,
               CAST(SUM(cop.location_quantity) AS UNSIGNED) AS product_count
        FROM customer_orders co
        JOIN customer_order_products cop ON co.id = cop.order_id
        WHERE cop.material = 0
        GROUP BY co.request_id
      ) cop ON r.id = cop.request_id
      LEFT JOIN (
        SELECT co.request_id, SUM(rss.qty) AS location_quantity, COUNT(rss.qty) AS service_count
        FROM customer_orders co
        JOIN request_services rss ON co.request_id = rss.request_id
        GROUP BY co.request_id
      ) rss ON r.id = rss.request_id
      LEFT JOIN (
        SELECT request_id, COUNT(*) AS installation_count
        FROM installations
        WHERE status = 4
        GROUP BY request_id
      ) inst_counts ON inst_counts.request_id = r.id
      LEFT JOIN (
        SELECT request_id, COUNT(*) AS total_installation_count
        FROM installations
        GROUP BY request_id
      ) total_inst_counts ON total_inst_counts.request_id = r.id
      WHERE ${whereClause}
    `);

    results[currentStatus.toUpperCase()] = countRes[0]?.totalCount || 0;
  }

  return results;
};

const getDesignOrderCountByStatus = async ({ status }) => {
  const normalizedStatuses = (status && status.length)
    ? status.map((s) => s.toUpperCase())
    : ["OPEN", "APPROVED", "REJECTED", "HOLD"];

  const results = {};

  for (const currentStatus of normalizedStatuses) {
    let whereClause = "status != 0";

    if (currentStatus === "OPEN") {
      whereClause += " AND status = 1 AND hold = 0";
    } else if (currentStatus === "APPROVED") {
      whereClause += " AND status = 2 AND hold = 0";
    } else if (currentStatus === "REJECTED") {
      whereClause += " AND status = 6 AND hold = 0";
    } else if (currentStatus === "HOLD") {
      whereClause += " AND hold = 1";
    } else {
      results[currentStatus] = 0;
      continue;
    }

    const [countRes] = await sequelize.query(`
      SELECT COUNT(*) AS totalCount FROM design_orders
      WHERE ${whereClause}
    `);

    results[currentStatus] = countRes[0]?.totalCount || 0;
  }

  return results;
};

//jobs
const getJobCountByStatus = async ({ status }) => {
  const results = {};
  if (!status || !status.length) {
    return { error: "Status is required" };
  }

  const normalized = status.map(s => s.toLowerCase());

  for (const current of normalized) {
    let whereClause = "p.status IS NOT NULL";
    let groupHaving = "";

    const completionCondition = `
      (
        CASE 
          WHEN p.project_category = 12 THEN 
            CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) + COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END
          WHEN p.project_category = 11 THEN 
            CASE WHEN inst.installation_count >= COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END 
          WHEN p.project_category = 14 THEN 
            CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) + COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END 
          ELSE 
            CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) THEN 1 ELSE 0 END 
        END
      )
    `;

    switch (current) {
      case "pre-production":
        whereClause += " AND (p.status = 0 OR p.status = 1) AND p.hold = 0";
        break;
      case "production":
        whereClause += " AND p.status = 2 AND p.hold = 0";
        break;
      case "post production":
        whereClause += " AND p.status = 3 AND p.hold = 0";
        break;
      case "hold":
        whereClause += " AND p.hold = 1";
        break;

      // ✅ Updated install-related tabs with correct complete_status values
      case "installs":
        whereClause += ` AND i.complete_status = 1 AND ${completionCondition} != 1`;
        break;
      case "pickup":
        whereClause += ` AND i.complete_status = 2 AND ${completionCondition} != 1`;
        break;
      case "shipping":
        whereClause += ` AND i.complete_status = 3 AND ${completionCondition} != 1`;
        break;
      case "completed":
        whereClause += ` AND ${completionCondition} = 1`;
        break;

      default:
        results[current.toUpperCase()] = 0;
        continue;
    }

    const sql = `
      SELECT COUNT(*) AS totalCount
      FROM (
        SELECT p.id
        FROM projects p
        LEFT JOIN requests r ON r.id = p.request_id
        LEFT JOIN (
          SELECT request_id, MAX(complete_status) AS complete_status
          FROM installations
          GROUP BY request_id
        ) i ON i.request_id = p.request_id
        LEFT JOIN (
          SELECT request_id, COUNT(*) AS installation_count
          FROM installations
          WHERE status = 4
          GROUP BY request_id
        ) inst ON inst.request_id = p.request_id
        LEFT JOIN (
          SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity,
                 CAST(SUM(cop.location_quantity) AS UNSIGNED) AS product_count
          FROM customer_orders co
          JOIN customer_order_products cop ON co.id = cop.order_id
          WHERE cop.material = 0
          GROUP BY co.request_id
        ) cop ON r.id = cop.request_id
        LEFT JOIN (
          SELECT request_id, SUM(rss.qty) AS location_quantity,
                 COUNT(rss.qty) AS service_count
          FROM request_services rss
          WHERE rss.recipe_id IS NULL ${laborInstallWhereCondition}
          GROUP BY request_id
        ) rss ON r.id = rss.request_id
        WHERE ${whereClause}
        GROUP BY p.id
      ) filtered_projects;
    `;

    try {
      const [countRes] = await sequelize.query(sql);
      results[current.toUpperCase()] = countRes[0]?.totalCount || 0;
    } catch (err) {
      console.error(`❌ SQL error in tab ${current.toUpperCase()}:`, err);
      results[current.toUpperCase()] = 0;
    }
  }

  return results;
};



// const getJobDetails = async ({
//   fields = ["name", "status", "timestamp"],
//   status = "ALL",
//   search = "",
//   types = [],
//   categories = [],
//   communities = [],
//   clients = []
// }) => {
//   const allowedFields = {
//     name: "p.name AS job_name",
//     status: `CASE
//       WHEN p.hold = 1 THEN 'Hold'
//       WHEN p.status = 3 THEN 'Post Production'
//       WHEN p.status IN (0, 1) THEN 'Pre-Production'
//       ELSE 'Production'
//     END AS status`,
//     timestamp: "UNIX_TIMESTAMP(p.timestamp) AS timestamp",
//     short_code: "p.short_code",
//     description: "r.description AS description",
//     quickbook_id: "r.doc_number AS quickbook_id",
//     project_type: "p.project_type",
//     project_category: "p.project_category",
//     community: "p.community",
//     customer_id: "p.project_lead AS customer_id",
//     job_category_name: "pc.name AS job_category_name",
//     community_name: "com.name AS community_name",
//     client_name: "c_ord.customer_name AS client_name",
//     total: "c_ord.total"
//   };

//   const selectedFields = fields
//     .filter(f => allowedFields[f])
//     .map(f => allowedFields[f])
//     .join(", ");

//   if (!selectedFields) return "Invalid fields requested.";

//   let whereClause = "p.status IS NOT NULL";
//   const lowerStatus = status.toLowerCase();

//   if (["pre-production", "production", "post production", "hold"].includes(lowerStatus)) {
//     if (lowerStatus === "pre-production")
//       whereClause += " AND (p.status = 0 OR p.status = 1) AND p.hold = 0";
//     else if (lowerStatus === "production")
//       whereClause += " AND p.status IN (1, 2) AND p.hold = 0";
//     else if (lowerStatus === "post production")
//       whereClause += " AND p.status = 3 AND p.hold = 0";
//     else if (lowerStatus === "hold")
//       whereClause += " AND p.hold = 1";
//   }

//   if (types.length)
//     whereClause += ` AND p.project_type IN (${types.join(",")})`;

//   if (categories.length)
//     whereClause += ` AND p.project_category IN (${categories.join(",")})`;

//   if (communities.length) {
//     const includeOther = communities.includes("other");
//     const idsOnly = communities.filter(id => id !== "other").join(",");
//     whereClause += includeOther
//       ? ` AND (p.community IN (${idsOnly}) OR p.community IS NULL)`
//       : ` AND p.community IN (${idsOnly})`;
//   }

//   if (clients.length) {
//     const includeOther = clients.includes("other");
//     const idsOnly = clients.filter(id => id !== "other").join(",");
//     whereClause += includeOther
//       ? ` AND (p.project_lead IN (${idsOnly}) OR p.project_lead IS NULL)`
//       : ` AND p.project_lead IN (${idsOnly})`;
//   }

//   if (search && search.trim()) {
//     whereClause += ` AND (
//       p.name LIKE :likeStr OR
//       r.description LIKE :likeStr OR
//       c_ord.customer_name LIKE :likeStr
//     )`;
//   }

//   const completionCondition = `
//     (
//       CASE 
//         WHEN p.project_category = 12 THEN 
//           CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) + COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END
//         WHEN p.project_category = 11 THEN 
//           CASE WHEN inst.installation_count >= COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END 
//         WHEN p.project_category = 14 THEN 
//           CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) + COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END 
//         ELSE 
//           CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) THEN 1 ELSE 0 END 
//       END
//     )
//   `;

//   if (["installs", "pickup", "shipping", "completed"].includes(lowerStatus)) {
//     const statusMap = {
//       installs: 1,
//       pickup: 2,
//       shipping: 3
//     };

//     if (lowerStatus === "completed") {
//       whereClause += ` AND ${completionCondition} = 1`;
//     } else {
//       const completeStatus = statusMap[lowerStatus];
//       whereClause += ` AND i.complete_status = ${completeStatus} AND ${completionCondition} != 1`;
//     }
//   }

//   const query = `
//     SELECT ${selectedFields},
//       i.complete_status AS InstallCompleteStatus,
//       ${completionCondition} AS allInstallationCompleted
//     FROM projects p
//     LEFT JOIN requests r ON r.id = p.request_id
//     LEFT JOIN (
//       SELECT request_id, MAX(complete_status) AS complete_status
//       FROM installations
//       GROUP BY request_id
//     ) i ON i.request_id = p.request_id
//     LEFT JOIN (
//       SELECT request_id, COUNT(*) AS installation_count
//       FROM installations
//       WHERE status = 4
//       GROUP BY request_id
//     ) inst ON inst.request_id = p.request_id
//     LEFT JOIN (
//       SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity,
//              CAST(SUM(cop.location_quantity) AS UNSIGNED) AS product_count
//       FROM customer_orders co
//       JOIN customer_order_products cop ON co.id = cop.order_id
//       WHERE cop.material = 0
//       GROUP BY co.request_id
//     ) cop ON r.id = cop.request_id
//     LEFT JOIN (
//       SELECT request_id, SUM(rss.qty) AS location_quantity,
//              COUNT(rss.qty) AS service_count
//       FROM request_services rss
//       WHERE rss.recipe_id IS NULL AND rss.labour_cost_service_id != 10
//       GROUP BY request_id
//     ) rss ON r.id = rss.request_id
//     LEFT JOIN project_categories pc ON pc.id = p.project_category
//     LEFT JOIN customer_orders c_ord ON c_ord.request_id = p.request_id
//     LEFT JOIN community com ON com.id = p.community AND FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1
//     WHERE ${whereClause}
//     GROUP BY p.id
//   `;

//   const rawResults = await sequelize.query(query, {
//     replacements: { likeStr: `%${search}%` },
//     type: sequelize.QueryTypes.SELECT
//   });

//   const results = Array.isArray(rawResults) ? rawResults : [];

//   // ✅ Smart single field returns
//   if (fields.length === 1) {
//     if (fields[0] === "description") return results.map(r => r.description || "");
//     if (fields[0] === "client_name") return results.map(r => r.client_name || "");
//     if (fields[0] === "quickbook_id") return results.map(r => r.quickbook_id || "");
//     if (fields[0] === "name") return results.map(r => r.job_name || "");
//   }

//   return results.map(r => ({
//     ...r,
//     timestamp: r.timestamp ? moment.unix(r.timestamp).format("YYYY-MM-DD HH:mm:ss") : null
//   }));
// };

const getJobDetails = async ({
  fields = ["name", "status", "timestamp"],
  status = "ALL",
  search = "",
  types = [],
  categories = [],
  communities = [],
  clients = []
}) => {
  const allowedFields = {
    name: "p.name AS job_name",
    status: `CASE
      WHEN p.hold = 1 THEN 'Hold'
      WHEN p.status = 3 THEN 'Post Production'
      WHEN p.status IN (0, 1) THEN 'Pre-Production'
      ELSE 'Production'
    END AS status`,
    timestamp: "UNIX_TIMESTAMP(p.timestamp) AS timestamp",
    short_code: "p.short_code",
    description: "r.description AS description",
    quickbook_id: "r.doc_number AS quickbook_id",
    project_type: "p.project_type",
    project_category: "p.project_category",
    community: "p.community",
    customer_id: "p.project_lead AS customer_id",
    job_category_name: "pc.name AS job_category_name",
    community_name: "com.name AS community_name",
    client_name: "c_ord.customer_name AS client_name",
    total: "c_ord.total",
    team_members: "GROUP_CONCAT(DISTINCT CONCAT(sm.first_name, ' ', sm.last_name) SEPARATOR ', ') AS team_members"

  };

  const selectedFields = fields
    .filter(f => allowedFields[f])
    .map(f => allowedFields[f])
    .join(", ");

  if (!selectedFields) return "Invalid fields requested.";

  let whereClause = "p.status IS NOT NULL";
  const lowerStatus = status.toLowerCase();

  if (["pre-production", "production", "post production", "hold"].includes(lowerStatus)) {
    if (lowerStatus === "pre-production")
      whereClause += " AND (p.status = 0 OR p.status = 1) AND p.hold = 0";
    else if (lowerStatus === "production")
      whereClause += " AND p.status IN (1, 2) AND p.hold = 0";
    else if (lowerStatus === "post production")
      whereClause += " AND p.status = 3 AND p.hold = 0";
    else if (lowerStatus === "hold")
      whereClause += " AND p.hold = 1";
  }

  if (types.length)
    whereClause += ` AND p.project_type IN (${types.join(",")})`;

  if (categories.length)
    whereClause += ` AND p.project_category IN (${categories.join(",")})`;

  if (communities.length) {
    const includeOther = communities.includes("other");
    const idsOnly = communities.filter(id => id !== "other").join(",");
    whereClause += includeOther
      ? ` AND (p.community IN (${idsOnly}) OR p.community IS NULL)`
      : ` AND p.community IN (${idsOnly})`;
  }

  if (clients.length) {
    const includeOther = clients.includes("other");
    const idsOnly = clients.filter(id => id !== "other").join(",");
    whereClause += includeOther
      ? ` AND (p.project_lead IN (${idsOnly}) OR p.project_lead IS NULL)`
      : ` AND p.project_lead IN (${idsOnly})`;
  }

  if (search && search.trim()) {
    whereClause += ` AND (
      p.name LIKE :likeStr OR
      r.description LIKE :likeStr OR
      c_ord.customer_name LIKE :likeStr
    )`;
  }

  const completionCondition = `
    (
      CASE 
        WHEN p.project_category = 12 THEN 
          CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) + COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END
        WHEN p.project_category = 11 THEN 
          CASE WHEN inst.installation_count >= COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END 
        WHEN p.project_category = 14 THEN 
          CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) + COALESCE(rss.service_count, 0) THEN 1 ELSE 0 END 
        ELSE 
          CASE WHEN inst.installation_count >= COALESCE(cop.product_count, 0) THEN 1 ELSE 0 END 
      END
    )
  `;

  if (["installs", "pickup", "shipping", "completed"].includes(lowerStatus)) {
    const statusMap = {
      installs: 1,
      pickup: 2,
      shipping: 3
    };

    if (lowerStatus === "completed") {
      whereClause += ` AND ${completionCondition} = 1`;
    } else {
      const completeStatus = statusMap[lowerStatus];
      whereClause += ` AND i.complete_status = ${completeStatus} AND ${completionCondition} != 1`;
    }
  }

  const query = `
    SELECT ${selectedFields},
      i.complete_status AS InstallCompleteStatus,
      ${completionCondition} AS allInstallationCompleted
    FROM projects p
    LEFT JOIN requests r ON r.id = p.request_id
    LEFT JOIN (
      SELECT request_id, MAX(complete_status) AS complete_status
      FROM installations
      GROUP BY request_id
    ) i ON i.request_id = p.request_id
    LEFT JOIN (
      SELECT request_id, COUNT(*) AS installation_count
      FROM installations
      WHERE status = 4
      GROUP BY request_id
    ) inst ON inst.request_id = p.request_id
    LEFT JOIN (SELECT request_id, COUNT(*) AS total_installation_count FROM installations 
    GROUP BY request_id) total_inst ON total_inst.request_id = p.request_id
    LEFT JOIN (
      SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity,
             CAST(SUM(cop.location_quantity) AS UNSIGNED) AS product_count
      FROM customer_orders co
      JOIN customer_order_products cop ON co.id = cop.order_id
      WHERE cop.material = 0
      GROUP BY co.request_id
    ) cop ON r.id = cop.request_id
    LEFT JOIN (
      SELECT request_id, SUM(rss.qty) AS location_quantity,
             COUNT(rss.qty) AS service_count
      FROM request_services rss
      WHERE rss.recipe_id IS NULL ${laborInstallWhereCondition}
      GROUP BY request_id
    ) rss ON r.id = rss.request_id
    LEFT JOIN project_categories pc ON pc.id = p.project_category
    LEFT JOIN customer_orders c_ord ON c_ord.request_id = p.request_id
    LEFT JOIN community com ON com.id = p.community AND FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1
    LEFT JOIN project_members pm ON pm.project_id = p.id
    LEFT JOIN staff_members sm ON sm.id = pm.staff_id
    WHERE ${whereClause}
    GROUP BY p.id
  `;

  const rawResults = await sequelize.query(query, {
    replacements: { likeStr: `%${search}%` },
    type: sequelize.QueryTypes.SELECT
  });

  const results = Array.isArray(rawResults) ? rawResults : [];

  if (fields.length === 1) {
    if (fields[0] === "description") return results.map(r => r.description || "");
    if (fields[0] === "client_name") return results.map(r => r.client_name || "");
    if (fields[0] === "quickbook_id") return results.map(r => r.quickbook_id || "");
    if (fields[0] === "name") return results.map(r => r.job_name || "");
    if (fields[0] === "team_members") return results.map(r => r.team_members || "");
  }

  return results.map(r => ({
    ...r,
    timestamp: r.timestamp ? moment.unix(r.timestamp).format("YYYY-MM-DD HH:mm:ss") : null
  }));
};


const getJobCostByJobName = async (jobName) => {
  try {
    const jobData = await sequelize.query(
      `
      SELECT 
        r.name,
        r.id,
        (
          SELECT COALESCE(SUM(jc.total), 0)
          FROM projects p
          INNER JOIN job_costs jc ON jc.project_id = p.id
          WHERE p.request_id = r.id AND jc.taken = 1
        ) AS productTotal,
        (
          SELECT COALESCE(SUM(rs.jobcost_total_cost), 0)
          FROM request_services rs
          WHERE rs.request_id = r.id AND rs.taken = 1
        ) AS serviceTotal
      FROM requests r
      WHERE r.name = :jobName
      LIMIT 1;
      `,
      {
        replacements: { jobName: String(jobName?.jobName).trim() },
        type: sequelize.QueryTypes.SELECT,
        plain: true
      }
    );

    console.log("Job Data from DB:", jobData); // ✅ DEBUG

    if (!jobData) {
      return { message: `❌ No job found with the name "${jobName}"` };
    }

    const productTotal = parseFloat(jobData.productTotal || 0);
    const serviceTotal = parseFloat(jobData.serviceTotal || 0);
    const totalCost = productTotal + serviceTotal;

    return {
      job_name: jobData.name,
      request_id: jobData.id,
      productTotal,
      serviceTotal,
      totalCost
    };
  } catch (error) {
    console.error("❌ Error fetching job cost:", error.message);
    throw new Error("Failed to fetch job cost");
  }
};


const getJobUtilization = async (input = null) => {
  try {
    const jobName = typeof input === "object" ? input?.jobName : input;

    // Fetch all projects (super admin context)
    const allProjects = await Settings.getAllProjects(true, null, 1, 1);

    if (!Array.isArray(allProjects) || allProjects.length === 0) {
      return { message: "No job data found." };
    }

    // ✅ Specific job utilization
    if (jobName) {
      const job = allProjects.find(j => j.name?.toLowerCase() === jobName.toLowerCase());
      if (!job) {
        return { message: `No job found with name "${jobName}"` };
      }

      const expense = parseFloat(job.expense || 0);
      const budget = parseFloat(job.cost || 0);
      const utilization = budget > 0 ? (expense / budget) * 100 : 0;

      return {
        job_name: job.name,
        total_expense: expense.toFixed(2),
        total_budget: budget.toFixed(2),
        utilization: `${utilization.toFixed(2)}%`
      };
    }

    // ✅ Overall utilization (all jobs)
    const totalExpense = allProjects.reduce((sum, p) => sum + (parseFloat(p.expense || 0)), 0);
    const totalBudget = allProjects.reduce((sum, p) => sum + (parseFloat(p.cost || 0)), 0);
    const utilization = totalBudget > 0 ? (totalExpense / totalBudget) * 100 : 0;

    return {
      total_expense: totalExpense.toFixed(2),
      total_budget: totalBudget.toFixed(2),
      utilization: `${utilization.toFixed(2)}%`
    };
  } catch (err) {
    console.error("❌ Utilization error:", err);
    return { message: "Server error while fetching job utilization." };
  }
};






// installation



const getInstallationCountByStatus = async ({
  userId,
  userType,
  hasPermission = false,
  fromDate = null,
  toDate = null,
  project_id = null,
  search = '',
  filterClient = []
}) => {
  try {
    const fromTimestamp = fromDate ? moment(fromDate).startOf('day').unix() : null;
    const toTimestamp = toDate ? moment(toDate).endOf('day').unix() : null;

    let whereParts = ["r.installation_acceptance IS NULL"];

    if (fromTimestamp && toTimestamp)
      whereParts.push(`I.installation_date BETWEEN ${fromTimestamp} AND ${toTimestamp}`);
    else if (fromTimestamp)
      whereParts.push(`I.installation_date >= ${fromTimestamp}`);
    else if (toTimestamp)
      whereParts.push(`I.installation_date <= ${toTimestamp}`);

    if (project_id)
      whereParts.push(`I.project_id = ${project_id}`);

    if (filterClient.length)
      whereParts.push(`c.customer_id IN (${filterClient.join(',')})`);

    if (search.trim()) {
      const sanitized = search.toLowerCase().trim();
      whereParts.push(`(
        I.name LIKE '%${sanitized}%' OR 
        r.description LIKE '%${sanitized}%' OR
        c.customer_name LIKE '%${sanitized}%'
      )`);
    }

    if (userType !== 1 && userType !== 8 && userType !== 24 && hasPermission) {
      whereParts.push(`EXISTS (
        SELECT 1 FROM project_members pm
        JOIN staff_members sm ON sm.id = pm.staff_id
        WHERE pm.project_id = I.project_id AND sm.user_id = ${userId}
      )`);
    }

    const combinedWhere = whereParts.length ? `WHERE ${whereParts.join(" AND ")}` : '';

    const query = `
      SELECT 
        I.complete_status,
        I.status,
        COALESCE(temp.published, 0) AS published,
        COUNT(*) AS count
      FROM installations I
      LEFT JOIN requests r ON r.id = I.request_id
      LEFT JOIN customer_orders c ON c.request_id = r.id
      LEFT JOIN (
        SELECT installation_id, MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS published
        FROM installation_temp_fields
        GROUP BY installation_id
      ) temp ON temp.installation_id = I.id
      ${combinedWhere}
      GROUP BY I.complete_status, I.status, temp.published
    `;

    const results = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT
    });

    const statusLabels = {
      1: 'Installs',
      2: 'Pickup',
      3: 'Shipping'
    };

    const formattedCounts = {
      Installs: { Staged: 0, Installing: 0, 'Completed & Published': 0, Returned: 0, Total: 0 },
      Pickup: { Staged: 0, 'Completed & Published': 0, Total: 0 },
      Shipping: { Staged: 0, 'Completed & Published': 0, Returned: 0, Total: 0 }
    };

    for (const row of results) {
      const completeStatus = parseInt(row.complete_status);
      const status = parseInt(row.status);
      const published = parseInt(row.published);
      const count = parseInt(row.count);

      const group = statusLabels[completeStatus];
      if (!group) continue;

      formattedCounts[group].Total += count;

      if (status === 1) formattedCounts[group].Staged += count;
      if (status === 2 && group === 'Installs') formattedCounts[group].Installing += count;
      if (status === 4 && published === 1) formattedCounts[group]['Completed & Published'] += count;
      if (status === 5 && group !== 'Pickup') formattedCounts[group].Returned += count;
    }

    return formattedCounts;
  } catch (error) {
    console.error('Error in getInstallationCountByStatus:', error);
    return {
      Installs: { Staged: 0, Installing: 0, 'Completed & Published': 0, Returned: 0, Total: 0 },
      Pickup: { Staged: 0, 'Completed & Published': 0, Total: 0 },
      Shipping: { Staged: 0, 'Completed & Published': 0, Returned: 0, Total: 0 },
      error: 'Failed to fetch installation counts.'
    };
  }
};











const getDesignOrderDetails = async ({
  fields = ["id", "name", "status", "client", "project_name", "created_at"],
  status = "ALL",
  search = ""
}) => {
  const allowedFields = {
    id: "id",
    name: "name",
    client: "p.name AS client",
    project_name: "com.name AS project_name",
    total_cost: "total_cost",
    status: `CASE
      WHEN status = 1 AND hold = 0 THEN 'OPEN'
      WHEN status = 2 AND hold = 0 THEN 'APPROVED'
      WHEN status = 6 AND hold = 0 THEN 'REJECTED'
      WHEN hold = 1 THEN 'HOLD'
      ELSE 'UNKNOWN'
    END AS status`,
    description: "description",
    subject: "subject",
    created_at: "UNIX_TIMESTAMP(timestamp) AS created_at"
  };

  // Always include essential metadata
  ["status", "name", "created_at"].forEach(f => {
    if (!fields.includes(f)) fields.push(f);
  });

  const selectedFields = fields
    .filter(field => allowedFields[field])
    .map(field => allowedFields[field])
    .join(", ");

  if (!selectedFields) return "Invalid fields requested.";

  const normalizedStatus = status.toUpperCase();
  let whereClause = "status != 0";

  if (normalizedStatus === "OPEN") {
    whereClause = "status = 1 AND hold = 0";
  } else if (normalizedStatus === "APPROVED") {
    whereClause = "status = 2 AND hold = 0";
  } else if (normalizedStatus === "REJECTED") {
    whereClause = "status = 6 AND hold = 0";
  } else if (normalizedStatus === "HOLD") {
    whereClause = "hold = 1";
  }

  if (search && search.trim() !== "") {
    whereClause += ` AND (
      name LIKE :likeStr OR
      description LIKE :likeStr OR
      subject LIKE :likeStr
    )`;
  }

  const query = `
    SELECT ${selectedFields}
    FROM design_orders d
    LEFT JOIN partners p ON d.client = p.id
    LEFT JOIN community com ON com.id = d.project_id
    WHERE ${whereClause}
  `;

  const results = await sequelize.query(query, {
    replacements: { likeStr: `%${search}%` },
    type: sequelize.QueryTypes.SELECT
  });

  return results.map(row => ({
    ...row,
    created_at: row.created_at
      ? moment.unix(row.created_at).format("YYYY-MM-DD HH:mm:ss")
      : null,
    days_since_created: row.created_at
      ? moment().diff(moment.unix(row.created_at), "days")
      : null
  }));

};

const getEstimateDetails = async ({ fields = ["r.id", "r.name"], status = "all", search = "" }) => {
  const allowedFields = {
    id: "r.id",
    name: "r.name",
    customer: "p2.name AS customer",
    status: `CASE
      WHEN r.status = 8 AND r.quotation_status = 2 THEN 'open'
      WHEN r.status = 8 AND r.quotation_status = 4 THEN 'approved'
      WHEN r.quotation = 0 AND r.quotation_status = 4 THEN 'archive'
      ELSE 'unknown'
    END AS status`,
    total: "c_ord.total",
    job_category: "pc.name AS job_category",
    job_type: "prt.name AS job_type",
    project_name: "com.name AS project_name",
    created_at: "UNIX_TIMESTAMP(r.timestamp) AS created_at",
    description: "r.description",
    tags: "r.tags",
    vpt_number: "r.vpt_number"
  };

  // Always include essential fields
  ["description", "created_at", "name", "status"].forEach(f => {
    if (!fields.includes(f)) fields.push(f);
  });

  const selectedFields = fields
    .filter(field => allowedFields[field])
    .map(field => allowedFields[field])
    .join(", ");

  if (!selectedFields) return "Invalid fields requested.";

  const normalizedStatuses = Array.isArray(status)
    ? status.map(s => s.toLowerCase())
    : [status.toLowerCase()];

  const conditions = [];

  if (normalizedStatuses.includes("open")) {
    conditions.push(`r.status = 8 AND r.quotation_status = 2`);
  }
  if (normalizedStatuses.includes("approved")) {
    conditions.push(`r.status = 8 AND r.quotation_status = 4`);
  }
  if (normalizedStatuses.includes("archive")) {
    conditions.push(`r.quotation = 0 AND r.quotation_status = 4`);
  }
  if (normalizedStatuses.includes("all")) {
    conditions.push(`r.status = 8`);
  }

  let whereClause = `r.status <> 0 AND r.quotation = 1`;
  if (conditions.length > 0) {
    whereClause = `(${conditions.join(" OR ")})`;
  }

  if (search && search.trim() !== "") {
    whereClause += ` AND (
      r.name LIKE :likeStr OR
      r.description LIKE :likeStr OR
      r.tags LIKE :likeStr OR
      r.vpt_number LIKE :likeStr OR
      pc.name LIKE :likeStr OR
      prt.name LIKE :likeStr OR
      com.name LIKE :likeStr OR
      p2.name LIKE :likeStr
    )`;
  }

  const query = `
    SELECT ${selectedFields}
    FROM requests r
    LEFT JOIN project_categories pc ON pc.id = r.project_category
    LEFT JOIN project_types prt ON prt.id = r.project_type
    LEFT JOIN customer_orders c_ord ON c_ord.request_id = r.id
    LEFT JOIN partners p2 ON p2.id = c_ord.customer_id
    LEFT JOIN (
      SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status
      FROM installations GROUP BY request_id
    ) i ON i.request_id = r.id
    LEFT JOIN community com ON com.id = r.community
    LEFT JOIN (
      SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity,
             CAST(SUM(cop.location_quantity) AS UNSIGNED) AS product_count
      FROM customer_orders co
      JOIN customer_order_products cop ON co.id = cop.order_id
      WHERE cop.material = 0
      GROUP BY co.request_id
    ) cop ON r.id = cop.request_id
    LEFT JOIN (
      SELECT co.request_id, SUM(rss.qty) AS location_quantity, COUNT(rss.qty) AS service_count
      FROM customer_orders co
      JOIN request_services rss ON co.request_id = rss.request_id
      GROUP BY co.request_id
    ) rss ON r.id = rss.request_id
    LEFT JOIN (
      SELECT request_id, COUNT(*) AS installation_count
      FROM installations
      WHERE status = 4
      GROUP BY request_id
    ) inst_counts ON inst_counts.request_id = r.id
    LEFT JOIN (
      SELECT request_id, COUNT(*) AS total_installation_count
      FROM installations
      GROUP BY request_id
    ) total_inst_counts ON total_inst_counts.request_id = r.id
    WHERE ${whereClause}
  `;

  const results = await sequelize.query(query, {
    replacements: { likeStr: `%${search}%` },
    type: sequelize.QueryTypes.SELECT
  });
  const formatted = results.map(row => ({
    ...row,
    created_at: row.created_at
      ? moment.unix(row.created_at).format("YYYY-MM-DD HH:mm:ss")
      : null,
    days_since_created: row.created_at
      ? moment().diff(moment.unix(row.created_at), "days")
      : null
  }));

  return formatted;
};

const getSalesOrdersDetails = async ({ fields = ["r.id", "r.name"], status = "all", search = "" }) => {
  const allowedFields = {
    id: "r.id",
    name: "r.name",
    customer: "p2.name AS customer",
    status: `CASE
      WHEN (r.status = 2 OR (r.status = 3 AND r.min_payment_status = 0)) THEN 'Open'
      WHEN (r.status = 3 AND r.min_payment_status = 1) THEN 'Approved'
      WHEN (r.status = 4 AND i.status IS NULL) THEN 'In Production'
      WHEN (i.status IS NOT NULL AND i.status NOT IN (4,5) AND i.complete_status = 1) THEN 'Installation'
      WHEN (i.status IS NOT NULL AND i.status NOT IN (4,5) AND i.complete_status = 2) THEN 'Pickup'
      WHEN (i.status IS NOT NULL AND i.status NOT IN (4,5) AND i.complete_status = 3) THEN 'Shipping'
      WHEN (i.status IS NOT NULL AND r.installation_acceptance IS NOT NULL AND (inst_counts.installation_count >= cop.product_count OR inst_counts.installation_count >= rss.service_count)) THEN 'Completed'
      WHEN (i.status IS NOT NULL AND r.installation_acceptance IS NULL AND (inst_counts.installation_count >= cop.product_count OR inst_counts.installation_count >= rss.service_count)) THEN 'Invoicing'
      ELSE 'Unknown'
    END AS status`,
    total: "c_ord.total",
    // total: "reqPay.totalAmount",
    job_category: "pc.name",
    job_type: "prt.name",
    project_name: "com.name",
    created_at: "r.timestamp",
  };

  ["status", "name", "created_at", "customer"].forEach(f => {
    if (!fields.includes(f)) fields.push(f);
  });

  const selectedFields = fields
    .filter((field) => allowedFields[field])
    .map((field) => allowedFields[field])
    .join(", ");

  if (!selectedFields) return "Invalid fields requested.";

  const normalizedStatuses = Array.isArray(status)
    ? status.map((s) => s.toLowerCase())
    : [status.toLowerCase()];

  const conditions = [];

  if (normalizedStatuses.includes("open")) {
    conditions.push(
      `((r.status=2) OR (r.status=3 AND r.min_payment_status=0))`
    );
  }

  if (
    normalizedStatuses.includes("accepted") ||
    normalizedStatuses.includes("approved")
  ) {
    conditions.push(`(r.status=3 AND r.min_payment_status =1)`);
  }

  if (normalizedStatuses.includes("in production")) {
    conditions.push(`r.status=4 AND i.status IS NULL`);
  }

  if (
    ["installation", "pickup", "shipping"].some((s) =>
      normalizedStatuses.includes(s)
    )
  ) {
    if (normalizedStatuses.includes("installation")) {
      conditions.push(`
        i.status IS NOT NULL AND i.status NOT IN (4,5) AND i.complete_status = 1
        AND (
          COALESCE(inst_counts.installation_count, 0) < COALESCE(cop.product_count, 0)
          OR (pc.name = 'Service Only' AND COALESCE(inst_counts.installation_count, 0) < COALESCE(rss.service_count, 0))
          OR (COALESCE(cop.product_count, 0) = 0 AND COALESCE(rss.service_count, 0) > 0 AND COALESCE(inst_counts.installation_count, 0) < COALESCE(rss.service_count, 0))
        )
      `);
    }

    if (normalizedStatuses.includes("pickup")) {
      conditions.push(`
        i.status IS NOT NULL AND i.status NOT IN (4,5) AND i.complete_status = 2
        AND (
          COALESCE(inst_counts.installation_count, 0) < COALESCE(cop.product_count, 0)
          OR (pc.name = 'Service Only' AND COALESCE(inst_counts.installation_count, 0) < COALESCE(rss.service_count, 0))
          OR (COALESCE(cop.product_count, 0) = 0 AND COALESCE(rss.service_count, 0) > 0 AND COALESCE(inst_counts.installation_count, 0) < COALESCE(rss.service_count, 0))
        )
      `);
    }

    if (normalizedStatuses.includes("shipping")) {
      conditions.push(`
        i.status IS NOT NULL AND i.status NOT IN (4,5) AND i.complete_status = 3
        AND (
          COALESCE(inst_counts.installation_count, 0) < COALESCE(cop.product_count, 0)
          OR (pc.name = 'Service Only' AND COALESCE(inst_counts.installation_count, 0) < COALESCE(rss.service_count, 0))
          OR (COALESCE(cop.product_count, 0) = 0 AND COALESCE(rss.service_count, 0) > 0 AND COALESCE(inst_counts.installation_count, 0) < COALESCE(rss.service_count, 0))
        )
      `);
    }
  }

  if (normalizedStatuses.includes("completed")) {
    conditions.push(
      `i.status IS NOT NULL AND (inst_counts.installation_count >= cop.product_count OR inst_counts.installation_count >= rss.service_count) AND r.installation_acceptance IS NOT NULL`
    );
  }

  if (normalizedStatuses.includes("invoicing")) {
    conditions.push(
      `i.status IS NOT NULL AND (inst_counts.installation_count >= cop.product_count OR inst_counts.installation_count >= rss.service_count) AND r.installation_acceptance IS NULL`
    );
  }

  let whereClause = `r.status <> 0 AND r.status <> 8`;
  if (conditions.length > 0) {
    whereClause = `${whereClause} AND (${conditions.join(" OR ")})`;
  }

  if (search && search.trim() !== "") {
    whereClause += ` AND (
      r.name LIKE :likeStr OR
      r.description LIKE :likeStr OR
      r.tags LIKE :likeStr OR
      r.vpt_number LIKE :likeStr OR
      pc.name LIKE :likeStr OR
      prt.name LIKE :likeStr OR
      com.name LIKE :likeStr OR
      p2.name LIKE :likeStr
    )`;
  }

  const query = `
    SELECT ${selectedFields}
    FROM requests r
    LEFT JOIN project_categories pc ON pc.id = r.project_category
    LEFT JOIN project_types prt ON prt.id = r.project_type
    LEFT JOIN customer_orders c_ord ON c_ord.request_id = r.id
    LEFT JOIN partners p2 ON p2.id = c_ord.customer_id
    LEFT JOIN (SELECT SUM(amount) AS totalAmount, request_id FROM request_payments WHERE status = 1 GROUP BY request_id) reqPay ON reqPay.request_id = r.id
    LEFT JOIN (
      SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status FROM installations GROUP BY request_id
    ) i ON i.request_id = r.id
    LEFT JOIN  community com ON com.id=r.community and FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1
    LEFT JOIN (
      SELECT co.request_id, SUM(cop.location_quantity) AS location_quantity, CAST(SUM(cop.location_quantity) AS UNSIGNED) AS product_count
      FROM customer_orders co
      JOIN customer_order_products cop ON co.id = cop.order_id
      WHERE cop.material = 0
      GROUP BY co.request_id
    ) cop ON r.id = cop.request_id
    LEFT JOIN (
      SELECT co.request_id, SUM(rss.qty) AS location_quantity, COUNT(rss.qty) AS service_count
      FROM customer_orders co
      JOIN request_services rss ON co.request_id = rss.request_id
      GROUP BY co.request_id
    ) rss ON r.id = rss.request_id
    LEFT JOIN (
      SELECT request_id, COUNT(*) AS installation_count FROM installations WHERE status = 4 GROUP BY request_id
    ) inst_counts ON inst_counts.request_id = r.id
    LEFT JOIN (
      SELECT request_id, COUNT(*) AS total_installation_count FROM installations GROUP BY request_id
    ) total_inst_counts ON total_inst_counts.request_id = r.id
    WHERE ${whereClause}
  `;

  const results = await sequelize.query(query, {
    replacements: { likeStr: `%${search}%` },
    type: sequelize.QueryTypes.SELECT,
  });

  console.log("getSalesOrdersDetailsresults", results);
  return results;
};

//get All Customer count
const getAllCustomerCount = async () => {
  try {
    const query = `
  SELECT COUNT(p.id) AS total FROM partners p
  LEFT JOIN users u ON p.user_id = u.id
  LEFT JOIN partner_types pt ON p.partner_type = pt.id
  LEFT JOIN ranks r ON p.rank_id = r.id
  LEFT JOIN states s ON p.state_id = s.id
  LEFT JOIN customer_type ct ON p.customerTypeId = ct.id
  LEFT JOIN (
    SELECT COUNT(rt.id) AS no_of_requests, rt.partner_id 
    FROM request_partners rt 
    LEFT JOIN requests r ON r.id = rt.request_id 
    WHERE rt.status = 1 AND rt.accept_status != 2 
    GROUP BY rt.partner_id
  ) rp ON p.id = rp.partner_id
  LEFT JOIN (
    SELECT COUNT(prt.id) AS no_of_projects, prt.partner_id 
    FROM request_partners prt 
    LEFT JOIN projects prj ON prj.request_id = prt.request_id 
    WHERE prt.status = 1 AND prt.accept_status != 2 
    GROUP BY prt.partner_id
  ) prp ON p.id = prp.partner_id
  LEFT JOIN (
    SELECT COUNT(id) AS no_of_reviews, partner_id 
    FROM partner_reviews 
    WHERE status = 1 
    GROUP BY partner_id
  ) pr ON p.id = pr.partner_id
  LEFT JOIN (
    SELECT (SUM(quality_rating) + SUM(delivery_rating) + SUM(value_for_money_rating)) / 3 AS rating, partner_id 
    FROM partner_reviews 
    WHERE status = 1 
    GROUP BY partner_id
  ) rr ON p.id = rr.partner_id
  WHERE p.status != 2 AND p.isVendor = 0
`;

    const [results] = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
    });
    console.log('getAllCustomerCountresults', results)
    return `There are ${results.total} clients.`;
  } catch (error) {
    console.log('Error get all customer count', error);
    return "Error getting customer count";
  }
};

//get All Customer details
const getAllCustomers = async ({
  fields = ["p.id", "u.username", "p.company"],
  filters = {},
}) => {
  try {
    const allowedFields = {
      id: "p.id",
      username: "u.username",
      company: "p.company",
      email: "u.email",
      status: "p.status",
      onboard_complete:
        "(CASE WHEN p.onboard_complete = 0 THEN 'N' ELSE 'Y' END) AS active",
      user_type: "u.user_type",
      partner_type_name: "pt.name AS partner_type_name",
      bg_color: "pt.bg_color",
      rank_name: "r.name AS rank_name",
      rank_image: "r.rank_image",
      profile_pic: "u.profile_pic",
      state: "s.short_name AS state",
      rating: "(COALESCE(rr.rating, 0)/COALESCE(pr.no_of_reviews, 1)) AS rating",
      no_of_reviews: "COALESCE(pr.no_of_reviews, 0) AS no_of_reviews",
      no_of_requests: "COALESCE(rp.no_of_requests, 0) AS no_of_requests",
      no_of_projects: "COALESCE(prp.no_of_projects, 0) AS no_of_projects",
      customer_type_name:
        "CONCAT(ct.name, ' ', ct.billing_type, '%') AS customer_type_name",
      billing_type: "ct.billing_type",
    };

    const selectedFields = fields
      .filter((f) => allowedFields[f])
      .map((f) => allowedFields[f])
      .join(", ");
    if (!selectedFields) return "Invalid fields requested.";
    const whereClauses = ["p.status != 2", "p.isVendor = 0"];
    const replacements = {};

    if (filters.status !== undefined) {
      whereClauses.push("p.status = :status");
      replacements.status = filters.status;
    }

    if (filters.partner_type_id) {
      whereClauses.push("p.partner_type = :partner_type_id");
      replacements.partner_type_id = filters.partner_type_id;
    }
    const query = `
  SELECT ${selectedFields} FROM partners p
  LEFT JOIN users u ON p.user_id = u.id
  LEFT JOIN partner_types pt ON p.partner_type = pt.id
  LEFT JOIN ranks r ON p.rank_id = r.id
  LEFT JOIN states s ON p.state_id = s.id
  LEFT JOIN customer_type ct ON p.customerTypeId = ct.id
  LEFT JOIN (
    SELECT COUNT(rt.id) AS no_of_requests, rt.partner_id 
    FROM request_partners rt 
    LEFT JOIN requests r ON r.id = rt.request_id 
    WHERE rt.status = 1 AND rt.accept_status != 2 
    GROUP BY rt.partner_id
  ) rp ON p.id = rp.partner_id
  LEFT JOIN (
    SELECT COUNT(prt.id) AS no_of_projects, prt.partner_id 
    FROM request_partners prt 
    LEFT JOIN projects prj ON prj.request_id = prt.request_id 
    WHERE prt.status = 1 AND prt.accept_status != 2 
    GROUP BY prt.partner_id
  ) prp ON p.id = prp.partner_id
  LEFT JOIN (
    SELECT COUNT(id) AS no_of_reviews, partner_id 
    FROM partner_reviews 
    WHERE status = 1 
    GROUP BY partner_id
  ) pr ON p.id = pr.partner_id
  LEFT JOIN (
    SELECT (SUM(quality_rating) + SUM(delivery_rating) + SUM(value_for_money_rating)) / 3 AS rating, partner_id 
    FROM partner_reviews 
    WHERE status = 1 
    GROUP BY partner_id
  ) rr ON p.id = rr.partner_id
  WHERE ${whereClauses.join(" AND ")}
  ORDER BY p.card_order ASC
`;

    const [results] = await sequelize.query(query, {
      replacements,
      type: sequelize.QueryTypes.SELECT,
    });
    return results;
  } catch (error) {
    console.log('Error get all customers', error);
    return false;
  }
};

// chatbot conversation
const chatbot = async (request, reply) => {
  try {
    const { prompt, user } = request.body;
    const adminAccess = () => checkAdminAccess(user);

    const tools = [
      {
        type: "function",
        function: {
          name: "get_order_status",
          description: "Get the status of a given order",
          parameters: {
            type: "object",
            properties: {
              orderNumber: {
                type: "string",
                description: "The order number, e.g., 3",
              },
            },
            required: ["orderNumber"],
          },
        },
      },
      {
        type: "function",
        function: {
          name: "invite_client",
          description: "Invite a client to Signwise",
          parameters: {
            type: "object",
            properties: {
              email: {
                type: "string",
                description: "Email address of the client",
              },
            },
            required: ["email"],
          },
        },
      },
      {
        type: "function",
        function: {
          name: "check_admin_access",
          description: "Check whether the user is admin or not",
          parameters: {
            type: "object",
            properties: {
              user: {
                type: "object",
                description: "Details of the user",
              },
            },
            // required: ["user"],
          },
        },
      },
      {
        type: "function",
        function: {
          name: "fetch_sample_image",
          description: "Fetch sample images of the signboards and designs",
          parameters: {
            type: "object",
            properties: {
              user: {
                type: "object",
                description: "Details of the user",
              },
            },
          },
        },
      },
      {
        type: "function",
        function: {
          name: "get_sales_order_count",
          description: "Get the total count of sales orders",
          parameters: {
            type: "object",
            properties: {},
          },
        },
      },
      {
        type: "function",
        function: {
          name: "get_customer_by_order",
          description: "Get customer name for a specific order number",
          parameters: {
            type: "object",
            properties: {
              orderNumber: {
                type: "string",
                description: "Sales order number",
              },
            },
            required: ["orderNumber"],
          },
        },
      },
      {
        type: "function",
        function: {
          name: "get_sales_order_count_by_status",
          description: "Get count of sales orders by one or more statuses",
          parameters: {
            type: "object",
            properties: {
              status: {
                type: "array",
                items: {
                  type: "string",
                },
                description:
                  "List of status values (e.g., ['open', 'accepted'])",
              },
            },
            required: ["status"],
          },
        },
      },
      {
        type: "function",
        function: {
          name: "get_estimate_count",
          description: "Get the total count of estimates",
          parameters: {
            type: "object",
            properties: {},
          },
        },
      },
      {
        type: "function",
        function: {
          name: "get_estimate_count_by_status",
          description: "Get count of estimate orders by one or more statuses",
          parameters: {
            type: "object",
            properties: {
              status: {
                type: "array",
                items: {
                  type: "string",
                },
                description:
                  "List of status values (e.g., ['open', 'accepted'])",
              },
            },
            required: ["status"],
          },
        },
      },
      {
        type: "function",
        function: {
          name: "get_sales_orders_details",
          description: "Get sales orders based on fields and status",
          parameters: {
            type: "object",
            properties: {
              fields: {
                type: "array",
                items: {
                  type: "string",
                  enum: ["id", "name", "customer", "status", "total", "job_category", "job_type", "project_name", "created_at"]
                },
                default: ["id", "name", "customer", "status", "created_at"]
              },
              status: {
                type: "string",
                description: "Status filter like open, approved, shipping"
              },
              search: {
                type: "string",
                description: "Search term to match in name, tags, customer, etc."
              }
            },
            required: ["fields"]
          },
        },
      },
      {
        type: "function",
        function: {
          name: "get_estimate_details",
          description: "Get estimate details by selected fields and status",
          parameters: {
            type: "object",
            properties: {
              fields: {
                type: "array",
                items: {
                  type: "string",
                  enum: ["id", "name", "customer", "total", "status", "created_at", "job_category", "job_type", "project_name"],
                },
                default: ["id", "name", "customer", "status", "created_at"]
              },
              status: {
                type: "string",
                description: "Status filter (e.g., open, approved, all)",
              },
            },
            required: ["fields"],
          },
        },
      },
      {
        type: "function",
        function: {
          name: "get_design_order_count",
          description: "Get the total count of design orders",
          parameters: {
            type: "object",
            properties: {}
          }
        }
      },
      {
        type: "function",
        function: {
          name: "get_design_order_count_by_status",
          description: "Get count of design orders by one or more statuses",
          parameters: {
            type: "object",
            properties: {
              status: {
                type: "array",
                items: { type: "string", enum: ["OPEN", "APPROVED", "REJECTED", "HOLD", "ALL"] },
                description: "Status values like ['OPEN', 'APPROVED', 'ALL']"
              }
            },
            required: ["status"]
          }
        }
      },
      {
        type: "function",
        function: {
          name: "get_design_order_details",
          description: "Get design order details by selected fields and status",
          parameters: {
            type: "object",
            properties: {
              fields: {
                type: "array",
                items: {
                  type: "string",
                  enum: [
                    "id", "name", "client", "project_name", "total_cost", "status",
                    "created_at", "description", "subject"
                  ]
                },
                default: ["id", "name", "status", "client", "project_name", "created_at"]
              },
              status: {
                type: "string",
                description: "Status filter (e.g., OPEN, APPROVED, ALL)"
              }
            },
            required: ["fields"]
          }
        }
      },
      {
        type: "function",
        function: {
          name: "get_job_count",
          description: "Get total number of jobs",
          parameters: { type: "object", properties: {} }
        }
      },
      {
        type: "function",
        function: {
          name: "get_job_count_by_status",
          description: "Get count of jobs by status tabs like Pre-Production, Production, etc.",
          parameters: {
            type: "object",
            properties: {
              status: {
                type: "array",
                items: { type: "string" },
                description: "Status values like ['PRE-PRODUCTION', 'HOLD', 'PRODUCTION', 'POST PRODUCTION']"
              }
            },
            required: ["status"]
          }
        }
      },  
      //job details    
      
      {
        type: "function",
        function: {
          name: "get_job_details",
          description: "Get job details by fields and filters",
          parameters: {
            type: "object",
            properties: {
              fields: {
                type: "array",
                items: {
                  type: "string",
                  enum: [
                    "name",               // job_name
                    "status",
                    "timestamp",
                    "short_code",
                    "description",
                    "quickbook_id",
                    "project_type",
                    "project_category",
                    "community",
                    "customer_id",
                    "job_category_name",
                    "community_name",
                    "client_name",
                    "total",
                     "team_members",
                     "working on"
                  ]
                },
                description: "Fields to include like ['name','status','timestamp']"
              },
              status: {
                type: "string",
                description: "Tab status like Production, Hold etc",
                enum: ["PRE-PRODUCTION", "PRODUCTION", "POST PRODUCTION", "HOLD", "INSTALLS", "PICKUP", "SHIPPING", "COMPLETED"]
              },
              search: {
                type: "string"
              },
              types: {
                type: "array",
                items: { type: "number" }
              },
              categories: {
                type: "array",
                items: { type: "number" }
              },
              communities: {
                type: "array",
                items: { type: "string" }
              },
              clients: {
                type: "array",
                items: { type: "string" }
              }
            },
            required: ["fields"]
          }
        }
      },
      {
        type: "function",
        function: {
          name: "get_job_cost_by_job_name",
          description: "Fetch job cost (product + service total) using the job name",
          parameters: {
            type: "object",
            properties: {
              jobName: {
                type: "string",
                description: "Exact name of the job (e.g. 'test job 123')"
              }
            },
            required: ["jobName"]
          }
        }
      },
      {
        type: "function",
        function: {
          name: "get_job_utilization",
          description: "Get total job utilization or for a specific job name",
          parameters: {
            type: "object",
            properties: {
              jobName: {
                type: "string",
                description: "Optional job name to fetch utilization for a specific job"
              }
            }
          }
        }
      },
      
      
      //installation
      {
        type: "function",
        function: {
          name: "get_installation_count_by_status",
          description: "Fetch the count of installation jobs by status (Staged, Installing, Delivered, etc.)",
          parameters: {
            type: "object",
            properties: {
              userId: {
                type: "number",
                description: "ID of the user making the request"
              },
              userType: {
                type: "number",
                description: "Type of the user making the request"
              },
              hasPermission: {
                type: "boolean",
                description: "Whether user has permission to access all jobs"
              },
              fromDate: {
                type: "string",
                description: "Filter installations starting from this date (optional)"
              },
              toDate: {
                type: "string",
                description: "Filter installations up to this date (optional)"
              },
              project_id: {
                type: "number",
                description: "Filter installations for a specific project (optional)"
              },
              search: {
                type: "string",
                description: "Keyword search across installation name and description"
              },
              filterClient: {
                type: "array",
                items: { type: "string" },
                description: "Filter installations by client ID (optional)"
              }
            },
            required: ["userId", "userType"]
          }
        }
      },
      
      
      
      {
        type: "function",
        function: {
          name: "get_all_customers",
          description: "Get a list of customers with optional fields and filters",
          parameters: {
            type: "object",
            properties: {
              fields: {
                type: "array",
                items: { type: "string" },
                description: "Array of fields to return (e.g., ['id', 'username', 'state'])"
              },
              filters: {
                type: "object",
                properties: {
                  status: { type: "integer" },
                  partner_type_id: { type: "integer" }
                }
              }
            }
          }
        }
      },
      {
        type: "function",
        function: {
          name: "get_all_customer_count",
          description: "Get the total number of customers in the system",
          parameters: {
            type: "object",
            properties: {},
            required: []
          }
        }
      },
    ];

    const messages = prompt;
    if (messages.length === 0) {
      messages.push({
        role: "assistant",
        content: "Hi, I'm Atom. How can I help you today?",
      });
      reply.send({ status: 1, message: messages });
      return;
    } else {
      // messages.push({
      //   role: "system",
      //   content: `Instructions:
      //   1. Your are a helpful assistant of the organization Signwise Solutions;
      //   2. Do answer the user's question in less than 40 words;
      //   3. Answer the user request if it is related to tracking the order,
      //      invite a client,about branding,about signboards etc.
      //   4. check if the user is admin , if the user is admin then the admin can send an invitation link to a client,
      //      otherwise a message will indicate that only administrators have this privilege.  
      //   5. Don't make assumptions about what values to plug into functions.
      //      Ask for clarification if a user request is ambiguous;
      //   6. Don't justify your answer . For random questions or out of context questions,
      //      reply: "Cannot answer that question.";
      //   7. If the user wishes to place/make a new order or inquire about the prices of any signboard-related products,
      //      or any kind of customizations kindly instruct them to contact the following number: ${PHONE_SALES}.;
      //   8. Don't answer general knowledge or mathematics questions`,
      // });
   
      messages.push({
        role: "system",
        content: `Instructions:
          1. You are a helpful assistant of the organization Signwise Solutions;
          2. Do answer the user's question in less than 40 words;
          3. Answer all user questions — including general knowledge, random, unrelated, or signboard-related queries;
          4. If the user is admin, allow them to send an invitation link to a client. If not, respond that only administrators have this privilege;
          5. Don't make assumptions about what values to plug into functions. Ask for clarification if a user request is ambiguous;
          6. If the user wants to place an order or inquire about pricing/customization, ask them to contact this number: ${PHONE_SALES}.`
      });
      
    }

    const response = await openai.chat.completions.create({
      messages: messages,
      model: "gpt-4-0613",
      // model: "deepseek-chat",
      temperature: 1,
      max_tokens: 150,
      tools: tools,
      tool_choice: "auto",
    });
    console.log("deepseekresponse", response);
    const responseMessage = response.choices[0].message;
    console.log("deepseekresponseMessage", responseMessage);
    if (responseMessage.tool_calls) {
      const availableFunctions = {
        //add the functions here
        get_order_status: getOrderStatus,
        check_admin_access: adminAccess,
        invite_client: inviteClient,
        fetch_sample_image: fetchSampleImages,
        get_sales_order_count: getSalesOrderCount,
        get_customer_by_order: getCustomerByOrder,
        get_sales_order_count_by_status: getSalesOrderCountByStatus,
        get_sales_orders_details: getSalesOrdersDetails,
        get_estimate_count: getEstimateCount,
        get_estimate_count_by_status: getEstimateCountByStatus,
        get_estimate_details: getEstimateDetails,
        get_design_order_count: getDesignOrderCount,
        get_design_order_count_by_status: getDesignOrderCountByStatus,
        get_design_order_details: getDesignOrderDetails,
        get_job_count: getJobCount,
        get_job_count_by_status: getJobCountByStatus,
        get_job_cost_by_job_name: getJobCostByJobName ,
        get_job_utilization: getJobUtilization ,
        get_installation_count_by_status: getInstallationCountByStatus ,
        get_job_details: getJobDetails,
        get_all_customers: getAllCustomers,
        get_all_customer_count: getAllCustomerCount,
      };
      messages.push(responseMessage);

      for (const toolCall of responseMessage.tool_calls) {
        const functionName = toolCall.function.name;
        console.log("deepseekfunctionName", functionName);
        const functionToCall = availableFunctions[functionName];
        const functionArgs = JSON.parse(toolCall.function.arguments);
        const functionResponse = await functionToCall(functionArgs);

        let functionResponseString;

        if (typeof functionResponse === "string") {
          functionResponseString = functionResponse;
        } else if (typeof functionResponse === "object") {
          functionResponseString = JSON.stringify(functionResponse);
        } else {
          functionResponseString = "server error";
        }
        messages.push({
          tool_call_id: toolCall.id,
          role: "tool",
          name: functionName,
          content: functionResponseString,
        });
      }

      const secondResponse = await openai.chat.completions.create({
        model: "gpt-4-0613",
        // model: "deepseek-chat",
        messages: messages,
      });

      messages.push(secondResponse.choices[0].message);
      const filteredMsg = messages.filter((msg) => msg.role !== "system");
      reply.send({ status: 1, message: filteredMsg });
    } else {
      messages.push(response.choices[0].message);
      const filteredMsg = messages.filter((msg) => msg.role !== "system");
      reply.send({ status: 1, message: filteredMsg });
    }
  } catch (error) {
    console.error(error);
    reply.status(500).send({ status: 0, message: error.message });
  }
};

// module.exports = { chatbot };



