import DB from "../config/database/db";
import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import roomsTypes from "../schemas/room.schema";
import CONSTANTS from "../config/constants";
import log from "../config/log";

const roomDB: any = {};

roomDB.getById = async ({ id }: roomsTypes) => {
  const query =
    "Select R.id, R.propId, R.roomOptionId, R.floor, R.roomNum, R.status, RO.rent, RO.name as optionName, RO.amenities, RO.totalBedCount, RO.type, R.flatId from Rooms as R left join RoomOptions as RO on R.roomOptionId = RO.id  where R.id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomDB.getByPropId = async ({ propId }: roomsTypes) => {
  const query = "Select * from Rooms where propId = ? order by id desc";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getByRoomNumAndPropId = async ({
  roomNum,
  propId,
  flatId,
}: roomsTypes) => {
  let query = "";
  let data;
  if (flatId === null) {
    query =
      "Select * from Rooms where BINARY roomNum = ? and propId = ? order by id desc";
    data = [roomNum, propId];
  } else {
    query =
      "Select * from Rooms where BINARY roomNum = ? and propId = ? and flatId = ? order by id desc";
    data = [roomNum, propId, flatId];
  }
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomDB.getByExtendedNameAndPropId = async ({
  extendedName,
  propId,
  flatId,
}: roomsTypes) => {
  let query = "";
  let data;
  if (flatId === null) {
    query =
      "Select * from Rooms where BINARY extendedName = ? and propId = ? order by id desc";
    data = [extendedName, propId];
  } else {
    query =
      "Select * from Rooms where BINARY extendedName = ? and propId = ? and flatId = ? order by id desc";
    data = [extendedName, propId, flatId];
  }
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomDB.getByPropIdAndFloor = async ({ propId, floor }: roomsTypes) => {
  const query =
    "Select * from Rooms where propId = ? and floor = ? order by id";
  const data = [propId, floor];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getLatestRoom = async ({ propId, floor }: roomsTypes) => {
  const query =
    "Select * from Rooms where propId = ? and floor = ? order by id desc limit 1";
  const data = [propId, floor];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomDB.getLatestRoomForFlat = async ({ propId, flatId }: roomsTypes) => {
  const query =
    "Select * from Rooms where propId = ? and flatId = ? order by id desc limit 1";
  const data = [propId, flatId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomDB.getCountsByPropId = async ({ propId }: roomsTypes) => {
  const query = "Select Count(id) as totalRooms from Rooms where propId = ?";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};
roomDB.getCountsByPropIdAndFloor = async ({ propId, floor }: roomsTypes) => {
  const query =
    "Select Count(id) as totalRooms from Rooms where propId = ? and floor = ?";
  const data = [propId, floor];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomDB.getCountsByPropIdAndFlatId = async ({ propId, flatId }: roomsTypes) => {
  const query =
    "Select Count(id) as totalRooms from Rooms where propId = ? and flatId = ?";
  const data = [propId, flatId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomDB.getVacantRoomsByPropId = async ({ propId }: roomsTypes) => {
  const query =
    "Select * from (Select R.id, R.propId, R.roomOptionId, R.floor, R.flatId, R.roomNum, R.status, RO.rent, RO.name as optionName, RO.amenities, RO.totalBedCount, RO.type, (select COUNT(id) from Beds where roomId= R.id and status = ?) as vacantBedCount from Rooms as R left join RoomOptions as RO on R.roomOptionId = RO.id  where R.propId = ? and R.status NOT IN (?,?)) as v where v.vacantBedCount > 0 order by v.id";
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    propId,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getRoomsByPropId = async ({ propId }: roomsTypes) => {
  const query =
    "Select R.id, R.propId, R.extendedName, R.roomOptionId, R.floor, R.roomNum, R.status, RO.totalBedCount, RO.type, RO.amenities from Rooms as R left join RoomOptions as RO on R.roomOptionId = RO.id  where R.propId = ? order by R.roomNum";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getRoomsByPropIdAndFlatId = async ({ propId, flatId }: roomsTypes) => {
  const query =
    "Select R.id, R.propId, R.extendedName, R.roomOptionId, R.floor, R.roomNum, R.status, R.flatId, RO.totalBedCount, RO.type from Rooms as R left join RoomOptions as RO on R.roomOptionId = RO.id  where R.propId = ? and R.flatId =? order by R.id";
  const data = [propId, flatId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getRoomsByPropIdForFlats = async ({ propId, flatId }: roomsTypes) => {
  const query =
    `Select R.id, R.propId, R.extendedName, R.roomOptionId, R.floor, R.roomNum, R.status, R.flatId, RO.totalBedCount, RO.type, RO.amenities from Rooms as R left join RoomOptions as RO on R.roomOptionId = RO.id  where R.propId = ? and R.flatId in (${flatId}) order by R.id`;
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getActiveAndVacantRoomsByPropId = async ({ propId }: roomsTypes) => {
  const query =
    "Select R.id, R.floor, R.flatId, R.roomNum, RO.type as roomType, RO.rent as roomPrice, RO.amenities from Rooms as R left join RoomOptions as RO on R.roomOptionId = RO.id  where R.propId = ? and R.status NOT IN (?,?) and R.id in (select roomId from Beds where roomId = R.id) order by (R.flatId IS NOT NULL), CASE WHEN R.flatId is NULL THEN ISNULL(REGEXP_SUBSTR(R.floor, '^[A-Za-z]+')) ELSE 0 END, CASE WHEN R.flatId is NULL THEN REGEXP_SUBSTR(R.floor, '^[A-Za-z]+') ELSE '' END, CASE WHEN R.flatId is NULL THEN CAST(REGEXP_SUBSTR(R.floor, '[0-9]+') AS UNSIGNED) ELSE 0 END, R.roomNum, R.id";
  const data = [
    propId,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

// roomDB.getRoomsWithAvailableBedsByPropId = async ({ propId }: roomsTypes) => {
//   const query =
//     "Select R.id, R.floor, R.flatId, R.roomNum, RO.type as roomType, RO.rent as roomPrice, RO.amenities from Rooms as R left join RoomOptions as RO on R.roomOptionId = RO.id where R.propId = ? and (R.status NOT IN (?,?) or R.id in (select roomId from Beds where status = ? )) and R.id in (select roomId from Beds where roomId = R.id) order by R.id";
//   const data = [
//     propId,
//     CONSTANTS.ROOM_STATUS.OCCUPIED,
//     CONSTANTS.ROOM_STATUS.INACTIVE,
//     CONSTANTS.BED_STATUS.MOVING_OUT,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };


roomDB.getRoomsWithAvailableBedsByPropId = async ({ propId }: roomsTypes) => {
  const query =
    "Select R.id, R.floor, R.flatId, R.roomNum, RO.type as roomType, RO.rent as roomPrice, RO.amenities from Rooms as R inner join RoomOptions as RO on R.roomOptionId = RO.id where R.propId = ? and R.status IN (?,?) order by R.id";
  const data = [
    propId,
    CONSTANTS.ROOM_STATUS.VACANT,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.create = async ({ propId, floor, roomNum }: roomsTypes) => {
  const query = "Insert into Rooms (propId,floor,roomNum) values (?,?,?)";
  const data = [propId, floor, roomNum];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

roomDB.AddForFlat = async ({ propId, flatId, roomNum }: roomsTypes) => {
  const query = "Insert into Rooms (propId,flatId,roomNum) values (?,?,?)";
  const data = [propId, flatId, roomNum];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

roomDB.updateStatus = async ({ id, status }: roomsTypes) => {
  const query = "Update Rooms set status =? where id =?";
  const data = [status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

roomDB.updateRoomOption = async ({ id, roomOptionId }: roomsTypes) => {
  const query = "Update Rooms set roomOptionId =? where id =?";
  const data = [roomOptionId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

roomDB.updateRoomNum = async ({ id, roomNum, extendedName }: roomsTypes) => {
  const query = "Update Rooms set roomNum =?, extendedName = ? where id =?";
  const data = [roomNum, extendedName, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

roomDB.getByFlatId = async ({ flatId }: roomsTypes) => {
  const query = "Select * from Rooms where flatId = ?";
  const data = [flatId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getRoomCountByPropIdAndStatus = async ({
  propId,
  status,
}: roomsTypes) => {
  const query =
    "Select Count(id) as totalRooms from Rooms where propId = ? and status = ?";
  const data = [propId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomDB.containExtraBed = async ({ id }: roomsTypes) => {
  const query = "Select * from Beds where roomId = ? and isExtraBed = 1";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return true;
  else return false;
};

roomDB.getVacancyLossForClient = async ({ clientId }: any) => {
  const query =
    "select B.id as bedId, RO.rent as rent, B.createdAt from Rooms as R join RoomOptions as RO on R.roomOptionId = RO.id join Beds as B on R.id = B.roomId join Properties as P on R.propId = P.id where P.clientId = ? and (B.status = ? OR B.status = ?) and R.status != ? and P.status = ?";
  const data = [
    clientId,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getVacancyLossForClientForWeb = async ({ clientId, propIds, }: any) => {
  let query =
    "select B.id as bedId, RO.rent as rent, B.createdAt from Rooms as R join RoomOptions as RO on R.roomOptionId = RO.id join Beds as B on R.id = B.roomId join Properties as P on R.propId = P.id where P.clientId = ? and (B.status = ? OR B.status = ?) and R.status != ? and P.status = ?";
  const data = [
    clientId,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

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

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

roomDB.getVacancyLossForProp = async ({ propId }: roomsTypes) => {
  const query =
    "select B.id as bedId, RO.rent as rent, B.createdAt from Rooms as R join RoomOptions as RO on R.roomOptionId = RO.id join Beds as B on R.id = B.roomId where R.propId = ? and (B.status = ? OR B.status = ?) and R.status != ?";
  const data = [
    propId,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getVacancyLossForStaff = async ({
  clientId,
  propertiesIds,
}: {
  clientId: any;
  propertiesIds: any;
}) => {
  const query = `select B.id as bedId, RO.rent as rent, B.createdAt from Rooms as R join RoomOptions as RO on R.roomOptionId = RO.id join Beds as B on R.id = B.roomId join Properties as P on R.propId = P.id where P.clientId = ? and R.propId in (${propertiesIds}) and (B.status = ? OR B.status = ?) and R.status != ? and P.status = ?`;
  const data = [
    clientId,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getVacancyLossForWebStaff = async ({
  clientId,
  propertiesIds,
  propIds,
}: {
  clientId: any;
  propertiesIds: any;
  propIds: any;
}) => {
  let query = `select B.id as bedId, RO.rent as rent, B.createdAt from Rooms as R join RoomOptions as RO on R.roomOptionId = RO.id join Beds as B on R.id = B.roomId join Properties as P on R.propId = P.id where P.clientId = ? and R.propId in (${propertiesIds}) and (B.status = ? OR B.status = ?) and R.status != ? and P.status = ?`;
  const data = [
    clientId,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

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

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

roomDB.deactivateByPropId = async ({ propId }: roomsTypes) => {
  const query = "Update Rooms set status = ? where propId = ? and status = ?";
  const data = [
    CONSTANTS.ROOM_STATUS.INACTIVE,
    propId,
    CONSTANTS.ROOM_STATUS.VACANT,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

roomDB.getByRoomOptionId = async ({ roomOptionId }: roomsTypes) => {
  const query = "Select * from Rooms where roomOptionId = ?";
  const data = [roomOptionId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForClientDetailedFlat = async ({ clientId }: any) => {
  const query = `SELECT P.id AS propId, P.type AS propType, P.name AS propName, COALESCE(F.name, roomSharing.floor) AS flatFloor, CASE WHEN roomSharing.bedCount = 1 THEN '1-sharing' WHEN roomSharing.bedCount = 2 THEN '2-sharing' WHEN roomSharing.bedCount = 3 THEN '3-sharing' ELSE '3+ sharing' END AS sharingType, COUNT(*) AS roomCount FROM (SELECT R.id AS roomId, R.propId, P.type, R.flatId, R.floor, COUNT(B.id) AS bedCount FROM Rooms R JOIN Beds B ON R.id = B.roomId JOIN Properties P ON R.propId = P.id WHERE P.clientId = ? AND P.status = ? AND P.type = ? AND B.status = ? and R.status != ? GROUP BY R.id) roomSharing JOIN Properties P ON roomSharing.propId = P.id LEFT JOIN Flats F ON roomSharing.flatId = F.id GROUP BY P.id, P.name, COALESCE(F.name, roomSharing.floor), sharingType ORDER BY propId, flatFloor, sharingType;`;
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_TYPE.FLAT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForClientDetailedX = async ({ clientId, checkDate }: any) => {
//OR (LO.agreementStartDate IS NOT NULL AND LO.agreementPeriod IS NOT NULL AND DATE(DATE_ADD(LO.agreementStartDate, INTERVAL LO.agreementPeriod MONTH)) <= ?)
const query = 
  `SELECT P.id AS propId, P.gId, P.type AS propType, concat(P.streetAddress, ', ',  P.address) as propAddress, P.tenantPreference, F.gender as gender, P.name AS propName, COALESCE(F.name, R.floor) AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveInDate) > ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveOutDate) <= ? THEN 1 WHEN ? > CURDATE() and B.status = ? and ((LO.status = ? and DATE(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND LO.status = ? and DATE(LO.moveOutDate) < ?)) THEN 1 ELSE 0 END) AS vacantBeds, SUM(CASE WHEN B.status = ? THEN 1 ELSE 0 END) as movingOutBeds, CASE WHEN (select COUNT(*) from Rooms where flatId = F.id and status in (?, ?, ?)) THEN 0 ELSE 1 END as isFullyVacant, 0 AS hasAC, RO.amenities AS amenities FROM Properties AS P JOIN Flats AS F ON F.propId = P.id JOIN Rooms AS R ON P.type != 1 AND R.flatId = F.id JOIN RoomOptions AS RO ON R.roomOptionId = RO.id JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.clientId = ? AND P.status = 3 AND R.status NOT IN (4) GROUP BY P.id, P.type, P.name, F.id, flatFloor, R.roomNum, RO.amenities HAVING totalBeds > 0 UNION SELECT P.id AS propId, P.gId, P.type AS propType, concat(P.streetAddress, ', ',  P.address) as propAddress, P.tenantPreference, P.tenantPreference as gender, P.name AS propName,  R.floor AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveInDate) > ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveOutDate) <= ? THEN 1 WHEN ? > CURDATE() and B.status = ? and ((LO.status = ? and DATE(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND LO.status = ? and DATE(LO.moveOutDate) < ?)) THEN 1 ELSE 0 END) AS vacantBeds, SUM(CASE WHEN B.status = ? THEN 1 ELSE 0 END) as movingOutBeds, CASE WHEN (select COUNT(*) from Rooms where propId = P.id and floor = R.floor and status in (?, ?, ?)) THEN 0 ELSE 1 END as isFullyVacant, 0 AS hasAC, RO.amenities AS amenities FROM Properties AS P JOIN Rooms AS R ON P.type = 1 AND R.propId = P.id JOIN RoomOptions AS RO ON R.roomOptionId = RO.id JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.clientId = ? AND P.status = 3 AND R.status NOT IN (4) GROUP BY P.id, P.type, P.name, flatFloor, R.roomNum, RO.amenities HAVING totalBeds > 0 ORDER BY propName, CASE WHEN flatFloor LIKE 'G%' THEN 0 ELSE 1 END, flatFloor ASC`
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    checkDate,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.RESERVED,
    CONSTANTS.OCCUPANCY_STATUS.RESERVED,
    checkDate,
    CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    clientId,
    CONSTANTS.BED_STATUS.VACANT,
    checkDate,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.RESERVED,
    CONSTANTS.OCCUPANCY_STATUS.RESERVED,
    checkDate,
    CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    clientId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForClientDetailedPg = async ({ clientId }: any) => {
  const query = `SELECT P.id AS propId, P.type AS propType, P.name AS propName, COALESCE(F.name, roomSharing.floor) AS flatFloor, CASE WHEN roomSharing.bedCount = 1 THEN '1-sharing' WHEN roomSharing.bedCount = 2 THEN '2-sharing' WHEN roomSharing.bedCount = 3 THEN '3-sharing' ELSE '3+ sharing' END AS sharingType, COUNT(*) AS roomCount FROM (SELECT R.id AS roomId, R.propId, P.type, R.flatId, R.floor, COUNT(B.id) AS bedCount FROM Rooms R JOIN Beds B ON R.id = B.roomId JOIN Properties P ON R.propId = P.id WHERE P.clientId = ? AND P.status = ? AND P.type = ? AND R.status not in (?, ?) GROUP BY R.id) roomSharing JOIN Properties P ON roomSharing.propId = P.id LEFT JOIN Flats F ON roomSharing.flatId = F.id GROUP BY P.id, P.name, COALESCE(F.name, roomSharing.floor), sharingType ORDER BY propId, flatFloor, sharingType;`;
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_TYPE.PG,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForStaffDetailedFlat = async ({ clientId, propertiesIds }: any) => {
  const query = `SELECT P.id AS propId, P.name AS propName, P.type AS propType, COALESCE(F.name, roomSharing.floor) AS flatFloor, CASE WHEN roomSharing.bedCount = 1 THEN '1-sharing' WHEN roomSharing.bedCount = 2 THEN '2-sharing' WHEN roomSharing.bedCount = 3 THEN '3-sharing' ELSE '3+ sharing' END AS sharingType, COUNT(*) AS roomCount FROM (SELECT R.id AS roomId, R.propId, P.type, R.flatId, R.floor, COUNT(B.id) AS bedCount FROM Rooms R JOIN Beds B ON R.id = B.roomId JOIN Properties P ON R.propId = P.id WHERE P.clientId = ? AND P.id in (${propertiesIds}) AND P.status = ? AND P.type = ? AND B.status = ? and R.status != ? GROUP BY R.id) roomSharing JOIN Properties P ON roomSharing.propId = P.id LEFT JOIN Flats F ON roomSharing.flatId = F.id GROUP BY P.id, P.name, COALESCE(F.name, roomSharing.floor), sharingType ORDER BY propId, flatFloor, sharingType;`;
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_TYPE.FLAT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForStaffDetailedX = async ({ clientId, propertiesIds, checkDate }: any) => {
  // const query = `SELECT P.id AS propId, P.type AS propType, P.name AS propName, COALESCE(F.name, R.floor) AS flatFloor, R.roomNum, (select count(B.id) from Beds as B where B.roomId = R.id) as totalBeds, (select count(B.id) from Beds as B where B.roomId = R.id and B.status = ?) as vacantBeds, CASE WHEN RO.amenities like "%AC%" THEN 1 else 0 END as hasAC, RO.amenities as amenities from Properties as P left join Flats as F on F.propId = P.id left join Rooms as R on ((P.type = ? and R.propId = P.id) OR (P.type != ? and R.flatId = F.id)) left join RoomOptions as RO on R.roomOptionId = RO.id where P.clientId = ? and P.id in (${propertiesIds}) and P.status = ? and R.status not in (?, ?) and (SELECT COUNT(B.id) FROM Beds AS B WHERE B.roomId = R.id) > 0`;
  // const query = 
  //   `SELECT P.id AS propId, P.gId, P.type AS propType, P.tenantPreference, P.name AS propName, concat(P.streetAddress, ', ',  P.address) as propAddress, COALESCE(F.name, R.floor) AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = 1 THEN 1 WHEN LO.bedId IS NOT NULL AND ((Date(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND DATE(LO.moveOutDate) <= ?)) THEN 1 ELSE 0 END) AS vacantBeds, 0 AS hasAC, RO.amenities AS amenities FROM Properties AS P JOIN Flats AS F ON F.propId = P.id JOIN Rooms AS R ON P.type != 1 AND R.flatId = F.id JOIN RoomOptions AS RO ON R.roomOptionId = RO.id JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.clientId = ? AND P.id in (${propertiesIds}) AND P.status = 3 AND R.status NOT IN (4) GROUP BY P.id, P.type, P.name, flatFloor, R.roomNum, RO.amenities HAVING totalBeds > 0 UNION SELECT P.id AS propId, P.gId, P.type AS propType, P.tenantPreference, P.name AS propName, concat(P.streetAddress, ', ',  P.address) as propAddress, R.floor AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = 1 THEN 1 WHEN LO.bedId IS NOT NULL AND ((Date(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND DATE(LO.moveOutDate) <= ?)) THEN 1 ELSE 0 END) AS vacantBeds, 0 AS hasAC, RO.amenities AS amenities FROM Properties AS P JOIN Rooms AS R ON P.type = 1 AND R.propId = P.id JOIN RoomOptions AS RO ON R.roomOptionId = RO.id JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.clientId = ? AND P.id in (${propertiesIds}) AND P.status = 3 AND R.status NOT IN (4) GROUP BY P.id, P.type, P.name, flatFloor, R.roomNum, RO.amenities HAVING totalBeds > 0 ORDER BY propName, CASE WHEN flatFloor LIKE 'G%' THEN 0 ELSE 1 END, flatFloor ASC`
  const query = 
    `SELECT P.id AS propId, P.gId, P.type AS propType, concat(P.streetAddress, ', ',  P.address) as propAddress, P.tenantPreference, F.gender as gender, P.name AS propName, COALESCE(F.name, R.floor) AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveInDate) > ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveOutDate) <= ? THEN 1 WHEN ? > CURDATE() and B.status = ? and ((LO.status = ? and DATE(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND LO.status = ? and DATE(LO.moveOutDate) < ?)) THEN 1 ELSE 0 END) AS vacantBeds, SUM(CASE WHEN B.status = ? THEN 1 ELSE 0 END) as movingOutBeds, CASE WHEN (select COUNT(*) from Rooms where flatId = F.id and status in (?, ?, ?)) THEN 0 ELSE 1 END as isFullyVacant, 0 AS hasAC, RO.amenities AS amenities FROM Properties AS P JOIN Flats AS F ON F.propId = P.id JOIN Rooms AS R ON P.type != 1 AND R.flatId = F.id JOIN RoomOptions AS RO ON R.roomOptionId = RO.id JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.clientId = ? and P.id in (${propertiesIds}) AND P.status = 3 AND R.status NOT IN (4) GROUP BY P.id, P.type, P.name, flatFloor, F.id, R.roomNum, RO.amenities HAVING totalBeds > 0 UNION SELECT P.id AS propId, P.gId, P.type AS propType, concat(P.streetAddress, ', ',  P.address) as propAddress, P.tenantPreference, P.tenantPreference as gender, P.name AS propName,  R.floor AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveInDate) > ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveOutDate) <= ? THEN 1 WHEN ? > CURDATE() and B.status = ? and ((LO.status = ? and DATE(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND LO.status = ? and DATE(LO.moveOutDate) < ?)) THEN 1 ELSE 0 END) AS vacantBeds, SUM(CASE WHEN B.status = ? THEN 1 ELSE 0 END) as movingOutBeds, CASE WHEN (select COUNT(*) from Rooms where propId = P.id and floor = R.floor and status in (?, ?, ?)) THEN 0 ELSE 1 END as isFullyVacant, 0 AS hasAC, RO.amenities AS amenities FROM Properties AS P JOIN Rooms AS R ON P.type = 1 AND R.propId = P.id JOIN RoomOptions AS RO ON R.roomOptionId = RO.id JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.clientId = ? and P.id in (${propertiesIds}) AND P.status = 3 AND R.status NOT IN (4) GROUP BY P.id, P.type, P.name, flatFloor, R.roomNum, RO.amenities HAVING totalBeds > 0 ORDER BY propName, CASE WHEN flatFloor LIKE 'G%' THEN 0 ELSE 1 END, flatFloor ASC`
  // const data = [
  //   checkDate,
  //   checkDate,
  //   clientId,
  //   checkDate,
  //   checkDate,
  //   clientId,
  // ];
    const data = [
    CONSTANTS.BED_STATUS.VACANT,
    checkDate,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.RESERVED,
    CONSTANTS.OCCUPANCY_STATUS.RESERVED,
    checkDate,
    CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    clientId,
    CONSTANTS.BED_STATUS.VACANT,
    checkDate,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.RESERVED,
    CONSTANTS.OCCUPANCY_STATUS.RESERVED,
    checkDate,
    CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    clientId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForStaffDetailedPg = async ({ clientId, propertiesIds }: any) => {
  const query = `SELECT P.id AS propId, P.name AS propName, P.type AS propType, COALESCE(F.name, roomSharing.floor) AS flatFloor, CASE WHEN roomSharing.bedCount = 1 THEN '1-sharing' WHEN roomSharing.bedCount = 2 THEN '2-sharing' WHEN roomSharing.bedCount = 3 THEN '3-sharing' ELSE '3+ sharing' END AS sharingType, COUNT(*) AS roomCount FROM (SELECT R.id AS roomId, R.propId, P.type, R.flatId, R.floor, COUNT(B.id) AS bedCount FROM Rooms R JOIN Beds B ON R.id = B.roomId JOIN Properties P ON R.propId = P.id WHERE P.clientId = ? AND P.id in (${propertiesIds}) AND P.status = ? AND P.type = ? AND R.status not in (?, ?) GROUP BY R.id) roomSharing JOIN Properties P ON roomSharing.propId = P.id LEFT JOIN Flats F ON roomSharing.flatId = F.id GROUP BY P.id, P.name, COALESCE(F.name, roomSharing.floor), sharingType ORDER BY propId, flatFloor, sharingType;`;
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_TYPE.PG,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForPropDetailedFlat = async ({ propId }: any) => {
  const query = `SELECT P.id AS propId, P.name AS propName, P.type AS propType, COALESCE(F.name, roomSharing.floor) AS flatFloor, CASE WHEN roomSharing.bedCount = 1 THEN '1-sharing' WHEN roomSharing.bedCount = 2 THEN '2-sharing' WHEN roomSharing.bedCount = 3 THEN '3-sharing' ELSE '3+ sharing' END AS sharingType, COUNT(*) AS roomCount FROM (SELECT R.id AS roomId, R.propId, R.flatId, R.floor, COUNT(B.id) AS bedCount FROM Rooms R JOIN Beds B ON R.id = B.roomId JOIN Properties P ON R.propId = P.id WHERE P.id= ? AND P.status = ? AND P.type = ? AND B.status = ? and R.status != ? GROUP BY R.id) roomSharing JOIN Properties P ON roomSharing.propId = P.id LEFT JOIN Flats F ON roomSharing.flatId = F.id GROUP BY P.id, P.name, COALESCE(F.name, roomSharing.floor), sharingType ORDER BY propId, flatFloor, sharingType;`;
  const data = [
    propId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_TYPE.FLAT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForPropDetailedX = async ({ propId, checkDate }: any) => {
  //Below condition to check agreementEndDate in future when more clear 
  //OR (LO.agreementStartDate IS NOT NULL AND LO.agreementPeriod IS NOT NULL AND DATE(DATE_ADD(LO.agreementStartDate, INTERVAL LO.agreementPeriod MONTH)) <= ?)
  const query = `SELECT P.id AS propId, P.gId, P.type AS propType, P.tenantPreference, COALESCE(F.gender, P.tenantPreference) AS gender, P.name AS propName, concat(P.streetAddress, ', ',  P.address) as propAddress, COALESCE(F.name, R.floor) AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveInDate) > ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveOutDate) <= ? THEN 1 WHEN ? > CURDATE() and B.status = ? and ((LO.status = ? and DATE(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND LO.status = ? and DATE(LO.moveOutDate) < ?)) THEN 1 ELSE 0 END) AS vacantBeds, SUM(CASE WHEN B.status = ? THEN 1 ELSE 0 END) as movingOutBeds, CASE WHEN P.type = ? THEN CASE WHEN (SELECT COUNT(*) FROM Rooms WHERE flatId = F.id AND status IN (?, ?, ?)) > 0 THEN 0 ELSE 1 END ELSE CASE WHEN (SELECT COUNT(*) FROM Rooms WHERE propId = P.id AND floor = R.floor AND status IN (?, ?, ?)) > 0 THEN 0 ELSE 1 END END AS isFullyVacant, CASE WHEN RO.amenities LIKE "%AC%" THEN 1 ELSE 0 END AS hasAC, RO.amenities AS amenities FROM Properties AS P LEFT JOIN Flats AS F ON F.propId = P.id LEFT JOIN Rooms AS R ON ((P.type = ? AND R.propId = P.id) OR (P.type != ? AND R.flatId = F.id)) LEFT JOIN RoomOptions AS RO ON R.roomOptionId = RO.id LEFT JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.id = ? AND P.status = ? AND R.status NOT IN (?) GROUP BY P.id, P.type, P.name, flatFloor, F.id, R.roomNum, R.floor, RO.amenities HAVING totalBeds > 0`;
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    checkDate,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.RESERVED,
    CONSTANTS.OCCUPANCY_STATUS.RESERVED,
    checkDate,
    CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    CONSTANTS.PROPERTY_TYPE.FLAT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    CONSTANTS.PROPERTY_TYPE.PG,
    CONSTANTS.PROPERTY_TYPE.PG,
    propId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForLocation = async ({ locationId, checkDate }: any) => {
  //Below condition to check agreementEndDate in future when more clear 
  //OR (LO.agreementStartDate IS NOT NULL AND LO.agreementPeriod IS NOT NULL AND DATE(DATE_ADD(LO.agreementStartDate, INTERVAL LO.agreementPeriod MONTH)) <= ?)
  const query = `SELECT P.id AS propId, P.gId, P.type AS propType, P.tenantPreference, COALESCE(F.gender, P.tenantPreference) AS gender, P.name AS propName, concat(P.streetAddress, ', ',  P.address) as propAddress, COALESCE(F.name, R.floor) AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveInDate) > ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveOutDate) <= ? THEN 1 WHEN ? > CURDATE() and B.status = ? and ((LO.status = ? and DATE(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND LO.status = ? and DATE(LO.moveOutDate) < ?)) THEN 1 ELSE 0 END) AS vacantBeds, SUM(CASE WHEN B.status = ? THEN 1 ELSE 0 END) as movingOutBeds, CASE WHEN P.type = ? THEN CASE WHEN (SELECT COUNT(*) FROM Rooms WHERE flatId = F.id AND status IN (?, ?, ?)) > 0 THEN 0 ELSE 1 END ELSE CASE WHEN (SELECT COUNT(*) FROM Rooms WHERE propId = P.id AND floor = R.floor AND status IN (?, ?, ?)) > 0 THEN 0 ELSE 1 END END AS isFullyVacant, CASE WHEN RO.amenities LIKE "%AC%" THEN 1 ELSE 0 END AS hasAC, RO.amenities AS amenities FROM Properties AS P LEFT JOIN Flats AS F ON F.propId = P.id LEFT JOIN Rooms AS R ON ((P.type = ? AND R.propId = P.id) OR (P.type != ? AND R.flatId = F.id)) LEFT JOIN RoomOptions AS RO ON R.roomOptionId = RO.id LEFT JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.locationId = ? AND P.status = ? AND R.status NOT IN (?) GROUP BY P.id, P.type, P.name, flatFloor, F.id, R.roomNum, R.floor, RO.amenities HAVING totalBeds > 0`;
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    checkDate,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.RESERVED,
    CONSTANTS.OCCUPANCY_STATUS.RESERVED,
    checkDate,
    CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    CONSTANTS.PROPERTY_TYPE.FLAT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    CONSTANTS.PROPERTY_TYPE.PG,
    CONSTANTS.PROPERTY_TYPE.PG,
    locationId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForLocationStaff = async ({ locationId, propertiesIds, checkDate }: any) => {
  //Below condition to check agreementEndDate in future when more clear 
  //OR (LO.agreementStartDate IS NOT NULL AND LO.agreementPeriod IS NOT NULL AND DATE(DATE_ADD(LO.agreementStartDate, INTERVAL LO.agreementPeriod MONTH)) <= ?)
  const query = `SELECT P.id AS propId, P.gId, P.type AS propType, P.tenantPreference, COALESCE(F.gender, P.tenantPreference) AS gender, P.name AS propName, concat(P.streetAddress, ', ',  P.address) as propAddress, COALESCE(F.name, R.floor) AS flatFloor, R.roomNum, COUNT(B.id) AS totalBeds, SUM(CASE WHEN B.status = ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveInDate) > ? THEN 1 WHEN ? > CURDATE() and B.status = ? and DATE(LO.moveOutDate) <= ? THEN 1 WHEN ? > CURDATE() and B.status = ? and ((LO.status = ? and DATE(LO.moveInDate) > ?) OR (LO.moveOutDate IS NOT NULL AND LO.status = ? and DATE(LO.moveOutDate) < ?)) THEN 1 ELSE 0 END) AS vacantBeds, SUM(CASE WHEN B.status = ? THEN 1 ELSE 0 END) as movingOutBeds, CASE WHEN P.type = ? THEN CASE WHEN (SELECT COUNT(*) FROM Rooms WHERE flatId = F.id AND status IN (?, ?, ?)) > 0 THEN 0 ELSE 1 END ELSE CASE WHEN (SELECT COUNT(*) FROM Rooms WHERE propId = P.id AND floor = R.floor AND status IN (?, ?, ?)) > 0 THEN 0 ELSE 1 END END AS isFullyVacant, CASE WHEN RO.amenities LIKE "%AC%" THEN 1 ELSE 0 END AS hasAC, RO.amenities AS amenities FROM Properties AS P LEFT JOIN Flats AS F ON F.propId = P.id LEFT JOIN Rooms AS R ON ((P.type = ? AND R.propId = P.id) OR (P.type != ? AND R.flatId = F.id)) LEFT JOIN RoomOptions AS RO ON R.roomOptionId = RO.id LEFT JOIN Beds AS B ON B.roomId = R.id LEFT JOIN (SELECT o1.* FROM Occupancies o1 JOIN (SELECT bedId, MAX(id) AS maxId FROM Occupancies GROUP BY bedId) o2 ON o1.bedId = o2.bedId AND o1.id = o2.maxId) AS LO ON LO.bedId = B.id WHERE P.id in (${propertiesIds}) and P.locationId = ? AND P.status = ? AND R.status NOT IN (?) GROUP BY P.id, P.type, P.name, flatFloor, F.id, R.roomNum, R.floor, RO.amenities HAVING totalBeds > 0`;
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    checkDate,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    checkDate,
    checkDate,
    CONSTANTS.BED_STATUS.RESERVED,
    CONSTANTS.OCCUPANCY_STATUS.RESERVED,
    checkDate,
    CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT,
    checkDate,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    CONSTANTS.PROPERTY_TYPE.FLAT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.PROPERTY_TYPE.FLAT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
    CONSTANTS.PROPERTY_TYPE.PG,
    CONSTANTS.PROPERTY_TYPE.PG,
    locationId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getBedStatsForPropDetailedPg = async ({ propId }: any) => {
  const query = `SELECT P.id AS propId, P.name AS propName, P.type AS propType, COALESCE(F.name, roomSharing.floor) AS flatFloor, CASE WHEN roomSharing.bedCount = 1 THEN '1-sharing' WHEN roomSharing.bedCount = 2 THEN '2-sharing' WHEN roomSharing.bedCount = 3 THEN '3-sharing' ELSE '3+ sharing' END AS sharingType, COUNT(*) AS roomCount FROM (SELECT R.id AS roomId, R.propId, R.flatId, R.floor, COUNT(B.id) AS bedCount FROM Rooms R JOIN Beds B ON R.id = B.roomId JOIN Properties P ON R.propId = P.id WHERE P.id= ? AND P.status = ? AND P.type = ? AND R.status not in (?, ?) GROUP BY R.id) roomSharing JOIN Properties P ON roomSharing.propId = P.id LEFT JOIN Flats F ON roomSharing.flatId = F.id GROUP BY P.id, P.name, COALESCE(F.name, roomSharing.floor), sharingType ORDER BY propId, flatFloor, sharingType;`;
  const data = [
    propId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_TYPE.PG,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomDB.getByClientIdAndFilter = async ({ 
  clientId, 
  propIds,
  searchVal=null,
  pageNum,
  limit, 
}: { clientId: number; propIds: any[]; pageNum: number | null; limit: number; searchVal: string | null; }) => {
  let query = `SELECT R.*, P.name as propName, P.type as propType, IF (P.type = ?, (select name from Flats where id = R.flatId), R.floor) AS flatFloorName from Rooms as R join Properties as P on R.propId = P.id where clientId = ? `;
  const data: any = [
    CONSTANTS.PROPERTY_TYPE.FLAT,
    clientId
  ];

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

  if (
    searchVal &&
    String(searchVal).trim() !== "" &&
    String(searchVal).trim().toLowerCase() !== "null" &&
    String(searchVal).trim().toLowerCase() !== "undefined"
  ) {
    query += ` and R.roomNum like ?`;
    data.push(`%${searchVal}%`);
  }

  query += ` order by R.id desc`

  if (pageNum && Number(pageNum) > 0) {
    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 [];
};

roomDB.getByClientIdAndFilterForTask = async ({ 
  clientId, 
  propIds,
  searchVal=null,
  taskDate,
  taskFilter,
  pageNum,
  limit, 
}: { clientId: number; propIds: any[]; pageNum: number | null; limit: number; searchVal: string | null; taskDate: string; taskFilter: string | null; }) => {
  let query = `SELECT R.*, P.name as propName, P.type as propType, IF (P.type = ?, (select name from Flats where id = R.flatId), R.floor) AS flatFloorName, COUNT(ST.id) as taskCount from Rooms as R join Properties as P on R.propId = P.id left join StaffTasks as ST on R.id = ST.roomId and DATE(ST.taskDate) = ? where P.clientId = ? `;
  const data: any = [
    CONSTANTS.PROPERTY_TYPE.FLAT,
    taskDate,
    clientId
  ];

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

  if (
    searchVal &&
    String(searchVal).trim() !== "" &&
    String(searchVal).trim().toLowerCase() !== "null" &&
    String(searchVal).trim().toLowerCase() !== "undefined"
  ) {
    query += ` and R.roomNum like ?`;
    data.push(`%${searchVal}%`);
  }

  query += ` group by R.id`

  if (taskFilter && taskFilter === "C") {
    query += ` having COUNT(ST.id) > 0`;
  } else if (taskFilter && taskFilter === "UC") {
    query += ` having COUNT(ST.id) = 0`;
  }

  query += ` order by (CASE WHEN COUNT(ST.id) > 0 THEN 0 ELSE 1 END) ASC, R.id desc`

  if (pageNum && Number(pageNum) > 0) {
    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 [];
};

export default roomDB;