import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import kitchenInventoryItemsTypes from "../schemas/kitchenInventoryItems.schema";
import CONSTANTS from "../config/constants";
import log from "../config/log";
import kitchenInventoryTypes from "../schemas/kitchenInventory.schema";
import kitchenInventoryPurchaseTypes from "../schemas/kitchenInventoryPurchases.schema";
import kitchenInventoryItemTypes from "../schemas/kitchenInventoryItems.schema";
import kitchenInventoryDistributionTypes from "../schemas/kitchenInventoryDistribution.schema";
import kitchenTypes from "../schemas/kitchens.schema";

const kitchenInventoryDB: any = {};

kitchenInventoryDB.addItem = async ({
  clientId,
  categoryId,
  name,
  unitType,
  status,
}: kitchenInventoryItemsTypes) => {
  const query = `Insert into KitchenInventoryItems (clientId, categoryId, name, unitType, status) values (?, ?, ?, ?, ?)`;
  const data = [clientId, categoryId, name, unitType, status];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.getInventoryItemById = async ({
  id,
}: kitchenInventoryItemTypes) => {
  const query = `Select * from KitchenInventoryItems where id = ?`;
  const data = [id];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

kitchenInventoryDB.updateInventoryItemRecord = async ({
  id,
  name,
  categoryId,
  unitType,
}: kitchenInventoryItemTypes) => {
  const query = `Update KitchenInventoryItems set name = ?, categoryId = ?, unitType = ? where id = ?`;
  const data = [name, categoryId, unitType, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.updateInventoryItemStatus = async ({
  id,
  status,
}: kitchenInventoryItemTypes) => {
  const query = `Update KitchenInventoryItems set status = ? where id = ?`;
  const data = [status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.getInventoryRecordByClientIdAndItemId = async ({
  clientId,
  itemId,
}: kitchenInventoryTypes) => {
  const query = `Select * from KitchenInventory where clientId = ? and itemId = ?`;
  const data = [clientId, itemId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

kitchenInventoryDB.getInventoryRecordById = async ({
  id,
}: kitchenInventoryTypes) => {
  const query = `Select * from KitchenInventory where id = ?`;
  const data = [id];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

kitchenInventoryDB.addInventoryRecord = async ({
  clientId,
  itemId,
  currentCount,
}: kitchenInventoryTypes) => {
  const query = `Insert into KitchenInventory (clientId, itemId, currentCount) values (?, ?, ?)`;
  const data = [clientId, itemId, currentCount];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.updateInventoryRecord = async ({
  id,
  currentCount,
}: kitchenInventoryTypes) => {
  const query = `Update KitchenInventory set currentCount = ? where id = ?`;
  const data = [currentCount, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.updateInventoryTotalPurchased = async ({
  id,
  totalPurchased,
}: kitchenInventoryTypes) => {
  const query = `Update KitchenInventory set totalPurchased = ? where id = ?`;
  const data = [totalPurchased, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

kitchenInventoryDB.updateInventoryCurrentValue = async ({
  id,
  currentValue,
}: kitchenInventoryTypes) => {
  const query = `Update KitchenInventory set currentValue = ? where id = ?`;
  const data = [currentValue, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

kitchenInventoryDB.addPurchaseRecord = async ({
  categoryId,
  itemId,
  clientId,
  vendorId,
  price,
  quantity,
  unitType,
  purchaseDate,
  invoiceNo,
  recordedBy,
  description,
  invoice,
}: kitchenInventoryPurchaseTypes) => {
  const query = `Insert into KitchenInventoryPurchases (categoryId, itemId, clientId, vendorId, price, quantity, unitType, purchaseDate, invoiceNo, recordedBy, description, invoice) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [
    categoryId,
    itemId,
    clientId,
    vendorId,
    price,
    quantity,
    unitType,
    purchaseDate,
    invoiceNo,
    recordedBy,
    description,
    invoice,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.assignItem = async ({
  clientId,
  inventoryId,
  quantity,
  assignedTo,
  assignedBy,
  assignedPrice,
}: kitchenInventoryDistributionTypes) => {
  const query = `Insert into KitchenInventoryDistribution (clientId, inventoryId, quantity, assignedTo, assignedBy, assignedPrice) values (?, ?, ?, ?, ?, ?)`;
  const data = [clientId, inventoryId, quantity, assignedTo, assignedBy, assignedPrice];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.getItemsByClientId = async ({
  clientId,
  pageNum,
  limit,
}: {
  clientId: number;
  pageNum: number;
  limit: number;
}) => {
  let query = `Select * from KitchenInventoryItems where clientId = ? and status = ? order by id desc `;
  const data: any = [clientId, CONSTANTS.KITCHEN_ITEM_STATUS.ACTIVE];

  if(pageNum && limit) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += ` limit ?, ?`;
    data.push(offset);
    data.push(limit);
  }

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getItemsByClientIdAndSearch = async ({
  clientId,
  searchVal,
  pageNum,
  limit,
}: {
  clientId: number;
  searchVal: string;
  pageNum: number;
  limit: number;
}) => {
  let query = `Select * from KitchenInventoryItems where clientId = ? and name like ? and status = ? order by id desc `;
  const data: any = [clientId, `%${searchVal}%`, CONSTANTS.KITCHEN_ITEM_STATUS.ACTIVE];

  if(pageNum && limit) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += ` limit ?, ?`;
    data.push(offset);
    data.push(limit);
  }

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getInventoryStockByClientId = async ({
  clientId,
  categoryFilters,
  stockFilter=null,
  s = null,
  pageNum,
  limit,
}: {
  clientId: number;
  categoryFilters: any[] | null;
  stockFilter: string | null;
  s: string | null;
  pageNum: number;
  limit: number;
}) => {
  let query = `Select KI.*, KII.categoryId, KII.unitType, KII.name as itemName from KitchenInventory as KI join KitchenInventoryItems as KII on KI.itemId = KII.id where KI.clientId = ? `;
  const data: any = [clientId];

  if (s) {
    query += ` AND KII.name like ?`;
    data.push(`%${s}%`);
  }

  if (categoryFilters && categoryFilters.length > 0) {
    query += ` AND KII.categoryId IN (${categoryFilters.map((id) => "?").join(",")})`;
    data.push(...categoryFilters);
  }

  if(stockFilter == 'LS') {
    query += ` and KI.currentCount < 5 and KI.currentCount > 0`;
  } else if(stockFilter == 'OOS') {
    query += ` and KI.currentCount = 0`;
  }

  query += ` order by KI.id desc `;

  if(pageNum && limit) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += ` limit ?, ?`;
    data.push(offset);
    data.push(limit);
  }

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getInventoryStockValueByClientId = async ({
  clientId,
}: {
  clientId: number;
}) => {
  let query = `Select SUM(KI.currentValue) as totalInventoryValue from KitchenInventory as KI where KI.clientId = ? `;
  const data: any = [clientId];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].totalInventoryValue;
  else return 0;
};

kitchenInventoryDB.getInventoryStockForWebDashboard = async ({
  clientId,
  pageNum,
  limit,
}: {
  clientId: number;
  pageNum: number;
  limit: number;
}) => {
  let query = `Select KI.*, KII.categoryId, KII.unitType, KII.name as itemName from KitchenInventory as KI join KitchenInventoryItems as KII on KI.itemId = KII.id where KI.clientId = ? and ((KI.currentCount < 5 and KI.currentCount > 0) OR (KI.currentCount = 0)) order by KI.id desc`;
  const data: any = [clientId];

  if(pageNum && limit) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += ` limit ?, ?`;
    data.push(offset);
    data.push(limit);
  }

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

// kitchenInventoryDB.getInventoryDistributionByClientId = async ({
//   clientId,
//   categoryFilters,
//   kitchenFilters,
//   searchVal,
//   startDate,
//   endDate,
//   pageNum,
//   limit,
// }: {
//   clientId: number;
//   categoryFilters: any[] | null;
//   kitchenFilters: any[] | null;
//   searchVal: string | null;
//   startDate: string | null;
//   endDate: string | null;
//   pageNum: number;
//   limit: number;
// }) => {
//   let query = `Select KID.*, KII.unitType, KII.categoryId, KII.name as itemName from KitchenInventoryDistribution as KID join KitchenInventory as KI on KID.inventoryId = KI.id join KitchenInventoryItems as KII on KI.itemId = KII.id where KID.clientId = ? and DATE(KID.createdAt) BETWEEN ? and ? `;
//   const data: any = [clientId, startDate, endDate];

//   if (categoryFilters && categoryFilters.length > 0) {
//     query += ` AND KII.categoryId IN (${categoryFilters.map((id) => "?").join(",")})`;
//     data.push(...categoryFilters);
//   }

//   if (kitchenFilters && kitchenFilters.length > 0) {
//     query += ` AND KID.assignedTo IN (${kitchenFilters.map((id) => "?").join(",")})`;
//     data.push(...kitchenFilters);
//   }

//   if(searchVal && searchVal.trim() !== "" && searchVal.trim().toLowerCase() !== "null" && searchVal.trim().toLowerCase() !== "undefined") {
//     query += ` AND (KII.name LIKE ?)`;
//     data.push(`%${searchVal}%`);
//   }

//   query += ` order by KID.id desc `;

//   if(pageNum && limit) {
//     const offset = (Number(pageNum) - 1) * Number(limit);
//     query += ` limit ?, ?`;
//     data.push(offset);
//     data.push(limit);
//   }

//   const [rows] = await DB.query<RowDataPacket[]>(query, data);
//   if (rows && rows.length > 0) return rows;
//   else return false;
// };

kitchenInventoryDB.getInventoryDistributionByClientId = async ({
  clientId,
  categoryFilters,
  kitchenFilters,
  searchVal,
  startDate,
  endDate,
  pageNum,
  limit,
}: {
  clientId: number;
  categoryFilters: any[] | null;
  kitchenFilters: any[] | null;
  searchVal: string | null;
  startDate: string | null;
  endDate: string | null;
  pageNum: number;
  limit: number;
}) => {
  const whereClauses: string[] = ['KID.clientId = ?'];
  const baseData: any[] = [clientId];

  if (startDate && endDate) {
    whereClauses.push('DATE(KID.createdAt) BETWEEN ? AND ?');
    baseData.push(startDate, endDate);
  }

  if (categoryFilters && categoryFilters.length > 0) {
    whereClauses.push(`KII.categoryId IN (${categoryFilters.map(() => '?').join(',')})`);
    baseData.push(...categoryFilters);
  }

  if (kitchenFilters && kitchenFilters.length > 0) {
    whereClauses.push(`KID.assignedTo IN (${kitchenFilters.map(() => '?').join(',')})`);
    baseData.push(...kitchenFilters);
  }

  const hasSearch =
    searchVal &&
    searchVal.trim() !== '' &&
    searchVal.trim().toLowerCase() !== 'null' &&
    searchVal.trim().toLowerCase() !== 'undefined';

  if (hasSearch) {
    whereClauses.push('(KII.name LIKE ?)');
    baseData.push(`%${searchVal}%`);
  }

  const whereString = whereClauses.join(' AND ');

  let query = '';
  const finalData: any[] = [];

  if (pageNum && limit) {
    const offset = (Number(pageNum) - 1) * Number(limit);

    query = `
      SELECT KID.*, KII.unitType, KII.categoryId, KII.name as itemName, K.name as kitchenName 
      FROM KitchenInventoryDistribution AS KID
      JOIN KitchenInventory AS KI ON KID.inventoryId = KI.id
      JOIN KitchenInventoryItems AS KII ON KI.itemId = KII.id
      JOIN Kitchens AS K ON KID.assignedTo = K.id
      JOIN (
        SELECT DISTINCT KID.transferId, MAX(KID.id) as maxId
        FROM KitchenInventoryDistribution AS KID
        JOIN KitchenInventory AS KI ON KID.inventoryId = KI.id
        JOIN KitchenInventoryItems AS KII ON KI.itemId = KII.id
        JOIN Kitchens AS K ON KID.assignedTo = K.id
        WHERE ${whereString}
        GROUP BY KID.transferId
        ORDER BY maxId DESC
        LIMIT ?, ?
      ) AS DistinctTransfers ON KID.transferId = DistinctTransfers.transferId
      ORDER BY KID.id DESC
    `;

    finalData.push(...baseData, offset, Number(limit));
  } else {
    query = `
      SELECT KID.*, KII.unitType, KII.categoryId, KII.name as itemName, K.name as kitchenName 
      FROM KitchenInventoryDistribution AS KID
      JOIN KitchenInventory AS KI ON KID.inventoryId = KI.id
      JOIN KitchenInventoryItems AS KII ON KI.itemId = KII.id
      JOIN Kitchens AS K ON KID.assignedTo = K.id
      WHERE ${whereString}
      ORDER BY KID.id DESC
    `;

    finalData.push(...baseData);
  }

  const [rows] = await DB.query<RowDataPacket[]>(query, finalData);
  return rows && rows.length > 0 ? rows : false;
};

// kitchenInventoryDB.getPurchaseLedgerByClientId = async ({
//   clientId,
//   searchVal,
//   startDate,
//   endDate,
//   pageNum,
//   limit,
// }: {
//   clientId: number;
//   searchVal: string | null;
//   startDate: string | null;
//   endDate: string | null;
//   pageNum: number;
//   limit: number;
// }) => {
//   let query = `Select KIP.*, V.name as vendorName, KII.name as itemName from KitchenInventoryPurchases as KIP join KitchenInventoryItems as KII on KIP.itemId = KII.id join Vendors as V on V.id = KIP.vendorId where KIP.clientId = ? and DATE(KIP.purchaseDate) BETWEEN ? and ? `;
//   const data: any = [clientId, startDate, endDate];

//   // if (categoryFilters && categoryFilters.length > 0) {
//   //   query += ` AND KIP.categoryId IN (${categoryFilters.map((id) => "?").join(",")})`;
//   //   data.push(...categoryFilters);
//   // }

//   if(searchVal && searchVal.trim() !== "" && searchVal.trim().toLowerCase() !== "null" && searchVal.trim().toLowerCase() !== "undefined") {
//     query += ` AND (KIP.invoiceNo LIKE ? OR V.name LIKE ?)`;
//     data.push(`%${searchVal}%`);
//     data.push(`%${searchVal}%`);
//   }

  
//   if(pageNum && limit) {
//     const offset = (Number(pageNum) - 1) * Number(limit);
//     query += ` AND KIP.invoiceNo in (select invoiceNo from KitchenInventoryPurchases where clientId = ? and DATE(purchaseDate) BETWEEN ? and ? group by invoiceNo limit ?, ?)`;
//     data.push(clientId);
//     data.push(startDate);
//     data.push(endDate);
//     data.push(offset);
//     data.push(limit);
//   }

//   query += ` order by KIP.id desc `;

//   log.info(`Purchase Ledger Query [${mysql.format(query, data)}]`);

//   const [rows] = await DB.query<RowDataPacket[]>(query, data);
//   if (rows && rows.length > 0) return rows;
//   else return false;
// };

kitchenInventoryDB.getPurchaseLedgerByClientId = async ({
  clientId,
  searchVal,
  startDate,
  endDate,
  pageNum,
  limit,
}: {
  clientId: number;
  searchVal: string | null;
  startDate: string | null;
  endDate: string | null;
  pageNum: number;
  limit: number;
}) => {
  // 1. Build base WHERE conditions dynamically
  const whereClauses: string[] = ['KIP.clientId = ?'];
  const baseData: any[] = [clientId];

  if (startDate && endDate) {
    whereClauses.push('DATE(KIP.purchaseDate) BETWEEN ? AND ?');
    baseData.push(startDate, endDate);
  }

  const hasSearch =
    searchVal &&
    searchVal.trim() !== '' &&
    searchVal.trim().toLowerCase() !== 'null' &&
    searchVal.trim().toLowerCase() !== 'undefined';

  if (hasSearch) {
    whereClauses.push('(KIP.invoiceNo LIKE ? OR V.name LIKE ?)');
    baseData.push(`%${searchVal}%`, `%${searchVal}%`);
  }

  const whereString = whereClauses.join(' AND ');

  // 2. Build the query using JOIN on subquery instead of IN ()
  let query = '';
  const finalData: any[] = [];

  if (pageNum && limit) {
    const offset = (Number(pageNum) - 1) * Number(limit);

    query = `
      SELECT KIP.*, V.name as vendorName, KII.name as itemName 
      FROM KitchenInventoryPurchases AS KIP
      JOIN KitchenInventoryItems AS KII ON KIP.itemId = KII.id 
      JOIN Vendors AS V ON V.id = KIP.vendorId 
      JOIN (
        SELECT DISTINCT KIP.invoiceNo, MAX(KIP.id) as maxId
        FROM KitchenInventoryPurchases AS KIP
        JOIN KitchenInventoryItems AS KII ON KIP.itemId = KII.id
        JOIN Vendors AS V ON V.id = KIP.vendorId
        WHERE ${whereString}
        GROUP BY KIP.invoiceNo
        ORDER BY maxId DESC
        LIMIT ?, ?
      ) AS DistinctInvoices ON KIP.invoiceNo = DistinctInvoices.invoiceNo
      ORDER BY KIP.id DESC
    `;

    finalData.push(...baseData, offset, Number(limit));
  } else {
    query = `
      SELECT KIP.*, V.name as vendorName, KII.name as itemName 
      FROM KitchenInventoryPurchases AS KIP
      JOIN KitchenInventoryItems AS KII ON KIP.itemId = KII.id 
      JOIN Vendors AS V ON V.id = KIP.vendorId 
      WHERE ${whereString}
      ORDER BY KIP.id DESC
    `;

    finalData.push(...baseData);
  }
  //log.info(mysql.format(query, finalData))
  const [rows] = await DB.query<RowDataPacket[]>(query, finalData);
  return rows && rows.length > 0 ? rows : false;
};

kitchenInventoryDB.addKitchen = async ({
  clientId,
  name,
}: kitchenTypes) => {
  const query = `Insert into Kitchens (clientId, name) values (?, ?)`;
  const data: any = [clientId, name];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.linkKitchen = async ({
  kitchenId,
  propId,
}: {
  kitchenId: number;
  propId: number;
}) => {
  const query = `Insert into PropertyKitchen (kitchenId, propId) values (?, ?)`;
  const data: any = [kitchenId, propId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.unlinkKitchenAll = async ({
  kitchenId,
}: {
  kitchenId: number;
  propId: number;
}) => {
  const query = `Delete from PropertyKitchen where kitchenId = ?`;
  const data: any = [kitchenId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

kitchenInventoryDB.getKitchensByClientId = async ({
  clientId,
  propIds,
  searchVal,
  pageNum,
  limit,
}: {
  clientId: number;
  propIds: any[] | null;
  searchVal: string | null;
  pageNum: number;
  limit: number;
}) => {
  let query = `Select K.* from Kitchens as K where K.clientId = ? and status != ? `;
  const data: any = [clientId, CONSTANTS.KITCHEN_STATUS.DELETED];

  if (propIds && propIds.length > 0) {
    query += ` AND EXISTS (
      SELECT 1 FROM PropertyKitchen AS PK 
      WHERE PK.kitchenId = K.id AND PK.propId IN (${propIds.map((id) => "?").join(",")})
    )`;
    data.push(...propIds);
  }

  if(searchVal && searchVal.trim() !== '' && searchVal.trim().toLowerCase() !== 'undefined' && searchVal.trim().toLowerCase() !== 'null') {
    query += ` AND K.name LIKE ?`;
    data.push(`%${searchVal}%`);
  }

  query += ` order by K.id desc `;

  if(pageNum && limit) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += ` limit ?, ?`;
    data.push(offset);
    data.push(limit);
  }

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getLinkedPropertiesByKitchenId = async ({
  kitchenId
}: {
  kitchenId: number;
}) => {
  const query = `Select P.id, P.name from PropertyKitchen PK join Properties P on PK.propId = P.id where PK.kitchenId = ?`;
  const data: any = [kitchenId];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getKitchensByClientIdForDropdown = async ({
  clientId
}: {
  clientId: number;
}) => {
  const query = `Select K.* from Kitchens as K where K.clientId = ? and status != ? order by K.id desc`;
  const data: any = [clientId, CONSTANTS.KITCHEN_STATUS.DELETED];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getInventoryItemsByClientIdForDropdown = async ({
  clientId
}: {
  clientId: number;
}) => {
  const query = `Select * from KitchenInventoryItems where clientId = ? and status = ? order by id desc`;
  const data: any = [clientId, CONSTANTS.KITCHEN_ITEM_STATUS.ACTIVE];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getPurchaseRecordsByItemId = async ({
  itemId
}: {
  itemId: number;
}) => {
  const query = `Select * from KitchenInventoryPurchases where itemId = ? order by DATE(purchaseDate) asc`;
  const data: any = [itemId];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getInventoryAssignedAmountByDateRange = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  const query = `Select sum(assignedPrice) as totalAssignedPrice from KitchenInventoryDistribution where clientId = ? and DATE(createdAt) >= ? and DATE(createdAt) <= ?`;
  const data: any = [clientId, startDate, endDate];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].totalAssignedPrice;
  else return 0;
};

kitchenInventoryDB.getAssignedPriceByKitchenIdAndDateRange = async ({
  clientId,
  kitchenId,
  startDate,
  endDate,
}: {
  clientId: number;
  kitchenId: number;
  startDate: string;
  endDate: string;
}) => {
  const query = `Select sum(assignedPrice) as totalAssignedPrice from KitchenInventoryDistribution where clientId = ? and assignedTo = ? and DATE(createdAt) >= ? and DATE(createdAt) <= ?`;
  const data: any = [clientId, kitchenId, startDate, endDate];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].totalAssignedPrice;
  else return 0;
};

kitchenInventoryDB.getAssignedPriceByKitchenId = async ({
  clientId,
  kitchenId,
}: {
  clientId: number;
  kitchenId: number;
}) => {
  const query = `Select sum(assignedPrice) as totalAssignedPrice from KitchenInventoryDistribution where clientId = ? and assignedTo = ?`;
  const data: any = [clientId, kitchenId];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].totalAssignedPrice;
  else return 0;
};

kitchenInventoryDB.getInventoryStockPurchaseAmountByDateRange = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  const query = `Select SUM(price) as totalPurchase from KitchenInventoryPurchases where clientId = ? and DATE(purchaseDate) >= ? and DATE(purchaseDate) <= ?`;
  const data: any = [clientId, startDate, endDate];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].totalPurchase;
  else return 0;
};

kitchenInventoryDB.getCurrentStockValueByClientId = async ({
  clientId,
}: {
  clientId: number;
}) => {
  const query = `Select sum(currentValue) as totalValue from KitchenInventory where clientId = ?`;
  const data: any = [clientId];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].totalValue;
  else return 0;
};

kitchenInventoryDB.updateInvoiceNo = async ({
  id,
  invoiceNo,
}: {
  id: number;
  invoiceNo: string;
}) => {
  const query = `Update KitchenInventoryPurchases set invoiceNo = ? where id = ?`;
  const data: any = [invoiceNo, id];

  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

kitchenInventoryDB.getKitchenInventoryItemByName = async ({
  clientId,
  name,
}: {
  clientId: number;
  name: string;
}) => {
  const query = `Select * from KitchenInventoryItems where clientId = ? and name = ? and status != ?`;
  const data = [
    clientId,
    name,
    CONSTANTS.KITCHEN_ITEM_STATUS.DELETED
  ];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getKitchenInventoryItemByNameForEdit = async ({
  clientId,
  name,
  id,
}: {
  clientId: number;
  id: number;
  name: string;
}) => {
  const query = `Select * from KitchenInventoryItems where clientId = ? and name = ? and status != ? and id !=?`;
  const data = [
    clientId,
    name,
    CONSTANTS.KITCHEN_ITEM_STATUS.DELETED,
    id,
  ];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};
kitchenInventoryDB.getLastDistributionTrasferId = async () => {
  const query = `Select transferId from KitchenInventoryDistribution order by id desc limit 1`;
  const [rows] = await DB.query<RowDataPacket[]>(query);
  if (rows && rows.length > 0) return rows[0].transferId;
  else return 1;
};

kitchenInventoryDB.updateDistributionTrasferId = async ({
  id,
  transferId,
}: kitchenInventoryDistributionTypes) => {
  const query = `Update KitchenInventoryDistribution set transferId = ? where id = ?`;
  const data: any = [transferId, id];

  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

kitchenInventoryDB.editKitchen = async ({
  id,
  name,
}: {
  id: number;
  name: string;
}) => {
  const query = `Update Kitchens set name = ? where id = ?`;
  const data: any = [name, id];

  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

kitchenInventoryDB.updateKitchenStatus = async ({
  id,
  status,
}: {
  id: number;
  status: number;
}) => {
  const query = `Update Kitchens set status = ? where id = ?`;
  const data: any = [status, id];

  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

kitchenInventoryDB.getKitchenById = async ({
  id
}: {id: number}) => {
  const query = "Select * from Kitchens where id = ?";
  const data = [id];
  
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};


kitchenInventoryDB.getKitchenByName = async ({
  name,
  clientId,
}: {
  clientId: number;
  name: string;

}) => {
  const query = "Select * from Kitchens where clientId=? and name = ? and status != ?";
  const data = [
    clientId,
    name,
    CONSTANTS.KITCHEN_STATUS.DELETED
  ];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

kitchenInventoryDB.getKitchenByNameForEdit = async ({
  name,
  clientId,
  id,
}: {
  id: number;
  clientId: number;
  name: string;

}) => {
  const query = "Select * from Kitchens where clientId=? and name = ? and status != ? and id != ?";
  const data = [
    clientId,
    name,
    CONSTANTS.KITCHEN_STATUS.DELETED,
    id
  ];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

kitchenInventoryDB.getPurchaseTotalByClientIdForWebGraph = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  const query = `Select IFNULL(SUM(price),0) as totalPurchase, MONTH(purchaseDate) as month, YEAR(purchaseDate) as year from KitchenInventoryPurchases where clientId = ? and DATE(purchaseDate) BETWEEN ? and ? GROUP BY YEAR(purchaseDate), MONTH(purchaseDate)`;
  const data: any = [clientId, startDate, endDate];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getTransferTotalByClientIdForWebGraph = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  const query = `Select IFNULL(SUM(assignedPrice),0) as totalTransfer, MONTH(createdAt) as month, YEAR(createdAt) as year from KitchenInventoryDistribution where clientId = ? and DATE(createdAt) BETWEEN ? and ? GROUP BY YEAR(createdAt), MONTH(createdAt)`;
  const data: any = [clientId, startDate, endDate];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

kitchenInventoryDB.getInventoryItemSummaryForWeb = async ({
  clientId,
}: {
  clientId: number;
}) => {
  const query = `
    SELECT 
      SUM(CASE WHEN currentCount < 5 AND currentCount > 0 THEN 1 ELSE 0 END) AS lowStock,
      SUM(CASE WHEN currentCount = 0 THEN 1 ELSE 0 END) AS outOfStock,
      SUM(CASE WHEN currentCount >= 5 THEN 1 ELSE 0 END) AS healthyStock
    FROM KitchenInventory 
    WHERE clientId = ?
  `;
  const data: any = [clientId];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0]; // Return the single summary object directly
  else return false;
};

kitchenInventoryDB.getPurchaseSummaryByClientId = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  const query = `Select 
  IFNULL(SUM(price),0) as totalPurchase, 
  COUNT(*) as totalItemPurchased, 
  COUNT(DISTINCT invoiceNo) as totalPurchases from KitchenInventoryPurchases where clientId = ? and DATE(purchaseDate) BETWEEN ? and ?`;
  const data: any = [clientId, startDate, endDate];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

kitchenInventoryDB.getTransferSummaryByClientId = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  const query = `Select 
  IFNULL(SUM(assignedPrice),0) as totalTransferAmount, 
  COUNT(*) as totalItemTransfer, 
  COUNT(DISTINCT transferId) as totalTransfer,
  COUNT(DISTINCT assignedTo) as totalKitchenTransferredTo from KitchenInventoryDistribution where clientId = ? and DATE(createdAt) BETWEEN ? and ?`;
  const data: any = [clientId, startDate, endDate];

  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

export default kitchenInventoryDB;