import DB from "../config/database/db";
import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import dayMenuTypes from "../schemas/dayMenu.schema";
import propertyMenuTypes, { propertyMenuSchema } from "../schemas/propertyMenu.schema";
import log from "../config/log";
import tenantMealSelectionsTypes from "../schemas/tenantMealSelections.schema";
import CONSTANTS from "../config/constants";
import foodInventoryTypes from "../schemas/foodInventory.schema";

const foodDB: any = {};

foodDB.AddDayMenu = async ({
  clientId,
  dayOfWeek,
  breakfast,
  breakfastTiming,
  lunch,
  lunchNonVeg,
  lunchTiming,
  snack,
  snackTiming,
  dinner,
  dinnerNonVeg,
  dinnerTiming,
  supportDoc=null,
}: dayMenuTypes) => {
  const query =
    "Insert into DayMenu (clientId, dayOfWeek, breakfast, breakfastTiming, lunch, lunchNonVeg, lunchTiming, snack, snackTiming, dinner, dinnerNonVeg, dinnerTiming, supportDoc) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
  const data = [
    clientId,
    dayOfWeek,
    breakfast,
    breakfastTiming,
    lunch,
    lunchNonVeg,
    lunchTiming,
    snack,
    snackTiming,
    dinner,
    dinnerNonVeg,
    dinnerTiming,
    supportDoc,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return rows.insertId;
  else return false;
};

foodDB.addDayMenuToProperty = async ({
  propId,
  clientId,
  menuId,
  status,
}: propertyMenuTypes) => {
  const query =
    "Insert into PropertyMenu (propId, clientId, menuId, status) values (?,?,?,?)";
  const data = [propId, clientId, menuId, status];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return rows.insertId;
  else return false;
};

foodDB.getMenuByPropId = async ({ propId, clientId }: propertyMenuTypes) => {
  const query =
    "Select PM.status, DM.id, DM.dayOfWeek, DM.breakfast, DM.breakfastTiming, DM.lunch, DM.lunchNonVeg, DM.lunchTiming, DM.snack, DM.snackTiming, DM.dinner, DM.dinnerNonVeg, DM.dinnerTiming from PropertyMenu as PM join DayMenu as DM on DM.id = PM.menuId where PM.propId = ? and PM.clientId = ? order by PM.id desc";
  const data = [propId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

foodDB.getMenuByPropIdAndDayOfWeek = async ({ propId, clientId, dayOfWeek }: propertyMenuTypes & dayMenuTypes) => {
  const query =
    "Select PM.status, DM.id, DM.dayOfWeek, DM.breakfast, DM.breakfastTiming, DM.lunch, DM.lunchNonVeg, DM.lunchTiming, DM.snack, DM.snackTiming, DM.dinner, DM.dinnerNonVeg, DM.dinnerTiming from PropertyMenu as PM join DayMenu as DM on DM.id = PM.menuId where PM.propId = ? and PM.clientId = ? and DM.dayOfWeek = ? limit 1";
  const data = [propId, clientId, dayOfWeek];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

foodDB.updateStatusByPropId = async ({ propId, status }: propertyMenuTypes) => {
  const query = "Update PropertyMenu set status = ? where propId = ?";
  const data = [status, propId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

foodDB.updateDayMenu = async ({
  id,
  breakfast,
  breakfastTiming,
  lunch,
  lunchNonVeg,
  lunchTiming,
  snack,
  snackTiming,
  dinner,
  dinnerNonVeg,
  dinnerTiming,
  supportDoc=null,
}: dayMenuTypes) => {
  const query =
    "Update DayMenu set breakfast = ?, breakfastTiming = ?, lunch = ?, lunchNonVeg = ?, lunchTiming = ?, snack = ?, snackTiming = ?, dinner = ?, dinnerNonVeg = ?, dinnerTiming = ?, supportDoc = ? where id = ?";
  const data = [
    breakfast,
    breakfastTiming,
    lunch,
    lunchNonVeg,
    lunchTiming,
    snack,
    snackTiming,
    dinner,
    dinnerNonVeg,
    dinnerTiming,
    supportDoc,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

foodDB.getMenuByPropIdAndDay = async ({
  propId,
  dayOfWeek,
}: propertyMenuTypes & dayMenuTypes) => {
  const query =
    "Select PM.status, DM.dayOfWeek, DM.breakfast, DM.breakfastTiming, DM.lunch, DM.lunchNonVeg, DM.lunchTiming, DM.snack, DM.snackTiming, DM.dinner, DM.dinnerNonVeg, DM.dinnerTiming from PropertyMenu as PM join DayMenu as DM on DM.id = PM.menuId where PM.propId = ? and DM.dayOfWeek = ?";
  const data = [propId, dayOfWeek];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

foodDB.IsFoodPublishedForProp = async ({
  propId,
  clientId,
}: propertyMenuTypes) => {
  const query = 
    "Select status from PropertyMenu where propId = ? and clientId = ?";
  const data = [propId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0 && rows[0].status === 1) return true;
  else return false;
};

foodDB.isFoodAvailableForTenant = async ({
  propId,
  clientId,
}: propertyMenuTypes) => {
  const query = 
    "Select * from PropertyMenu where propId = ? and clientId = ? and status = ?";
  const data = [propId, clientId, 1];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

foodDB.addTenantMealSelection = async ({
  clientId,
  propId,
  roomId,
  tenantId,
  mealDate,
  mealType,
  mealDescription,
  dietPreference,
}: tenantMealSelectionsTypes) => {
  const query = 
    "Insert into TenantMealSelections (clientId, propId, roomId, tenantId, mealDate, mealType, mealDescription, dietPreference) values (?,?,?,?,?,?,?,?)";
  const data = [
    clientId,
    propId,
    roomId,
    tenantId,
    mealDate,
    mealType,
    mealDescription,
    dietPreference,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return rows.insertId;
  else return false;
};

foodDB.updateTenantMealSelection = async ({
  id,
  status,
  mealDate,
  mealType,
  mealDescription,
  dietPreference,
}: tenantMealSelectionsTypes) => {
  const query = 
    "Update TenantMealSelections set mealDate = ? , mealType = ?, mealDescription = ?, dietPreference = ?, status = ? where id = ?";
  const data = [
    mealDate,
    mealType,
    mealDescription,
    dietPreference,
    status,
    id,
  ];

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

  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

foodDB.getTenantMealSelectionByDate = async ({
  clientId,
  tenantId,
  mealDate,
}: tenantMealSelectionsTypes) => {
  const query = 
    "Select * from TenantMealSelections where clientId = ? and tenantId = ? and mealDate = ?";
  const data = [
    clientId,
    tenantId,
    mealDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

foodDB.getTenantMealSelectionByFilters = async ({
  clientId,
  propIds,
  tenantId,
  mealDate,
}: tenantMealSelectionsTypes & { propIds: any}) => {
  let query = 
    "Select * from TenantMealSelections where clientId = ? and tenantId = ? and mealDate = ?";
  const data = [
    clientId,
    tenantId,
    mealDate,
  ];

  if (propIds && Array.isArray(propIds) && propIds.length > 0) {
    query += ` and propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

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

foodDB.getMealSelectionByTenantIdAndDateAndType = async ({
  clientId,
  tenantId,
  mealDate,
  mealType,
}: tenantMealSelectionsTypes) => {
  const query = 
    "Select * from TenantMealSelections where clientId = ? and tenantId = ? and mealDate = ? and mealType = ?";
  const data = [
    clientId,
    tenantId,
    mealDate,
    mealType,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

foodDB.getMealSelectionByTenantIdAndFilters = async ({
  clientId,
  tenantId,
  mealDate,
  mealType,
  propIds,
}: tenantMealSelectionsTypes & { propIds: any }) => {
  let query = 
    "Select * from TenantMealSelections where clientId = ? and tenantId = ? and mealDate = ? and mealType = ?";
  const data = [
    clientId,
    tenantId,
    mealDate,
    mealType,
  ];

  if (propIds && Array.isArray(propIds) && propIds.length > 0) {
    query += ` and propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

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

foodDB.getTenantMealSelectionByClientIdAndFilters = async ({
  clientId,
  propId,
  mealDate,
  mealType,
  dietPreference,
  pageNum,
  limit,
}: tenantMealSelectionsTypes & { pageNum: number; limit: number; propId: any[] }) => {
  let query = 
    "Select TMS.*, T.name as tenantName, T.mobile as tenantMobile, R.roomNum as roomNum, P.name as propName, COALESCE(F.name, R.floor) as flatFloorName from TenantMealSelections as TMS join Tenants as T on TMS.tenantId = T.id join Properties as P on P.id = TMS.propId join Rooms as R on R.id = TMS.roomId left join Flats as F on F.id = R.flatId where TMS.clientId = ? and Date(TMS.mealDate) = ? and TMS.status != ?";
  const data: any = [
    clientId,
    mealDate,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
  ];

  if (Array.isArray(propId) && propId.length > 0) {
    query += ` and TMS.propId in (${propId.map(() => '?').join(',')})`;
    data.push(...propId);
  }

  if (Number(mealType) && Number(mealType) !== 0) {
    query += ` and TMS.mealType = ?`;
    data.push(mealType);
  }

  if (Number(dietPreference) && Number(dietPreference) !== 0) {
    query += ` and TMS.dietPreference = ?`;
    data.push(dietPreference);
  }

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

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

foodDB.getTenantMealSelectionByClientIdAndFiltersForWeb = async ({
  clientId,
  propId,
  mealDate,
  mealType,
  dietPreference,
}: tenantMealSelectionsTypes & { propId: any[] }) => {
  let query = 
    "Select TMS.*, T.name as tenantName, T.mobile as tenantMobile, R.roomNum as roomNum, P.name as propName, COALESCE(F.name, R.floor) as flatFloorName from TenantMealSelections as TMS join Tenants as T on TMS.tenantId = T.id join Properties as P on P.id = TMS.propId join Rooms as R on R.id = TMS.roomId left join Flats as F on F.id = R.flatId where TMS.clientId = ? and Date(TMS.mealDate) = ? and TMS.status != ?";
  const data: any = [
    clientId,
    mealDate,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
  ];

  if (Array.isArray(propId) && propId.length > 0) {
    query += ` and TMS.propId in (${propId.map(() => '?').join(',')})`;
    data.push(...propId);
  }

  if (Number(mealType) && Number(mealType) !== 0) {
    query += ` and TMS.mealType = ?`;
    data.push(mealType);
  }

  if (Number(dietPreference) && Number(dietPreference) !== 0) {
    query += ` and TMS.dietPreference = ?`;
    data.push(dietPreference);
  }

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

foodDB.getTenantMealSelectionSummaryByClientIdAndFilters = async ({
  clientId,
  propId,
  mealDate,
}: tenantMealSelectionsTypes & { propId: any[] }) => {
  let query = 
    `Select 
    IFNULL(SUM( CASE WHEN mealType = ? and status != ? then 1 else 0 end), 0) as breakfast, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as breakfastOptOut, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as breakfastPending, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as breakfastConsumed, 
    IFNULL(SUM( CASE WHEN mealType = ? and status != ? then 1 else 0 end), 0) as lunch, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as lunchOptOut, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as lunchPending, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as lunchConsumed, 
    IFNULL(SUM( CASE WHEN mealType = ? and status != ? and dietPreference = ? then 1 else 0 end), 0) as lunchVeg, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as lunchVegOptOut, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as lunchVegPending, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as lunchVegConsumed, 
    IFNULL(SUM( CASE WHEN mealType = ? and status != ? and dietPreference = ? then 1 else 0 end), 0) as lunchNonVeg, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as lunchNonVegOptOut, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as lunchNonVegPending, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as lunchNonVegConsumed,  
    IFNULL(SUM( CASE WHEN mealType = ? and status != ? then 1 else 0 end), 0) as snacks, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as snacksOptOut, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as snacksPending, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as snacksConsumed, 
    IFNULL(SUM( CASE WHEN mealType = ? and status != ? then 1 else 0 end), 0) as dinner, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as dinnerOptOut, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as dinnerPending, 
    IFNULL(SUM( CASE WHEN mealType = ? and status = ? then 1 else 0 end), 0) as dinnerConsumed,
    IFNULL(SUM( CASE WHEN mealType = ? and status != ? and dietPreference = ? then 1 else 0 end), 0) as dinnerVeg, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as dinnerVegOptOut, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as dinnerVegPending, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as dinnerVegConsumed, 
    IFNULL(SUM( CASE WHEN mealType = ? and status != ? and dietPreference = ? then 1 else 0 end), 0) as dinnerNonVeg, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as dinnerNonVegOptOut, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as dinnerNonVegPending, 
    IFNULL(SUM( CASE WHEN mealType = ? and dietPreference = ? and status = ? then 1 else 0 end), 0) as dinnerNonVegConsumed
    from TenantMealSelections where clientId = ? and Date(mealDate) = ?`;
    
  const data: any = [
    CONSTANTS.MEAL_TYPE.BREAKFAST,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.BREAKFAST,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.BREAKFAST,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.PENDING,
    CONSTANTS.MEAL_TYPE.BREAKFAST,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.CONSUMED,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.PENDING,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.CONSUMED,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.DIET_PREFERENCE.VEG,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.DIET_PREFERENCE.VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.DIET_PREFERENCE.VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.PENDING,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.DIET_PREFERENCE.VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.CONSUMED,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.DIET_PREFERENCE.NON_VEG,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.DIET_PREFERENCE.NON_VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.DIET_PREFERENCE.NON_VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.PENDING,
    CONSTANTS.MEAL_TYPE.LUNCH,
    CONSTANTS.DIET_PREFERENCE.NON_VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.CONSUMED,
    CONSTANTS.MEAL_TYPE.SNACKS,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.SNACKS,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.SNACKS,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.PENDING,
    CONSTANTS.MEAL_TYPE.SNACKS,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.CONSUMED,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.PENDING,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.CONSUMED,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.DIET_PREFERENCE.VEG,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.DIET_PREFERENCE.VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.DIET_PREFERENCE.VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.PENDING,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.DIET_PREFERENCE.VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.CONSUMED,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.DIET_PREFERENCE.NON_VEG,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.DIET_PREFERENCE.NON_VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.OPT_OUT,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.DIET_PREFERENCE.NON_VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.PENDING,
    CONSTANTS.MEAL_TYPE.DINNER,
    CONSTANTS.DIET_PREFERENCE.NON_VEG,
    CONSTANTS.MEAL_CONSUMPTION_STATUS.CONSUMED,
    clientId,
    mealDate,
  ];

  if (Array.isArray(propId) && propId.length > 0) {
    query += ` and propId in (${propId.map(() => '?').join(',')})`;
    data.push(...propId);
  }

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

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

foodDB.updateTenantMealStatus = async ({
  id,
  status
}: tenantMealSelectionsTypes) => {
  
  const query = 
    "Update TenantMealSelections set status = ? where id = ?";
  const data = [
    status,
    id,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return rows.affectedRows;
  else return false;
};

foodDB.addFoodInventory = async ({
  clientId,
  propId,
  mealDate,
  mealType,
  itemName,
  boughtWeight,
  leftoverWeight,
  preparedFoodImg,
  remainingFoodImg,
}: foodInventoryTypes) => {
  const query = 
    `INSERT INTO FoodInventory (clientId, propId, mealDate, mealType, itemName, boughtWeight, leftoverWeight, preparedFoodImg, remainingFoodImg) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
  const data = [
    clientId,
    propId,
    mealDate,
    mealType,
    itemName,
    boughtWeight,
    leftoverWeight,
    preparedFoodImg,
    remainingFoodImg,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return rows.affectedRows;
  else return false;
};

foodDB.updateFoodInventory = async ({
  id,
  mealDate,
  mealType,
  itemName,
  boughtWeight,
  leftoverWeight,
  preparedFoodImg,
  remainingFoodImg,
}: foodInventoryTypes) => {
  const query = 
    `UPDATE FoodInventory set mealDate = ?, mealType = ?, itemName = ?, boughtWeight = ?, leftoverWeight = ?, preparedFoodImg = ?, remainingFoodImg = ? where id = ?`
  const data = [
    mealDate,
    mealType,
    itemName,
    boughtWeight,
    leftoverWeight,
    preparedFoodImg,
    remainingFoodImg,
    id,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return rows.affectedRows;
  else return false;
};

foodDB.getFoodInventoryByDateAndMealType = async ({
  clientId,
  propId,
  mealDate,
  mealType,
}: foodInventoryTypes) => {
  const query = 
    `SELECT * FROM FoodInventory where propId = ? and clientId = ? and mealDate = ? and mealType = ?`
  const data = [
    propId,
    clientId,
    mealDate,
    mealType,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

foodDB.getFoodInventoryByDate = async ({
  clientId,
  propId,
  mealDate,
}: foodInventoryTypes) => {
  const query = 
    `SELECT * FROM FoodInventory where propId = ? and clientId = ? and mealDate = ?`
  const data = [
    propId,
    clientId,
    mealDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

foodDB.getFoodInventoryByDateAndMealTypeAndName = async ({
  clientId,
  propId,
  mealDate,
  mealType,
  itemName,
}: foodInventoryTypes) => {
  const query = 
    `SELECT * FROM FoodInventory where propId = ? and clientId = ? and mealDate = ? and mealType = ? and itemName = ?`
  const data = [
    propId,
    clientId,
    mealDate,
    mealType,
    itemName,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

foodDB.getStatsForStaff = async ({
  clientId,
  propIds,
  mealDate,
}: any) => {
  let query = 
    `SELECT COUNT(CASE WHEN status IN (1,2,3) THEN 1 END) AS totalMealOptIn, COUNT(CASE WHEN mealType=1 AND status IN (1,2,3) THEN 1 END) AS breakfastOptIn, COUNT(CASE WHEN mealType=1 AND status=2 THEN 1 END) AS breakfastServed, COUNT(CASE WHEN mealType=2 AND status IN (1,2,3) THEN 1 END) AS lunchOptIn, COUNT(CASE WHEN mealType=2 AND status=2 THEN 1 END) AS lunchServed, COUNT(CASE WHEN mealType=3 AND status IN (1,2,3) THEN 1 END) AS snacksOptIn, COUNT(CASE WHEN mealType=3 AND status=2 THEN 1 END) AS snacksServed, COUNT(CASE WHEN mealType=4 AND status IN (1,2,3) THEN 1 END) AS dinnerOptIn, COUNT(CASE WHEN mealType=4 AND status=2 THEN 1 END) AS dinnerServed, COUNT(CASE WHEN status=4 THEN 1 END) AS mealOptOut FROM TenantMealSelections WHERE clientId=? and mealDate = ?`
    let data = [
      clientId,
      mealDate
    ];
    if (Array.isArray(propIds) && propIds.length > 0) {
    query += ` and propId in (${propIds.map(() => '?').join(',')})`;
      data.push(...propIds);
    }
    
    query += ` GROUP BY clientId`;

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

export default foodDB;