import { ResultSetHeader, RowDataPacket } from "mysql2";
import moment from "moment";

import DB from "../config/database/db";
import bedTypes from "../schemas/bed.schema";
import roomsTypes from "../schemas/room.schema";
import CONSTANTS from "../config/constants";

const bedDB: any = {};

bedDB.getById = async ({ id }: bedTypes) => {
  const query = "Select * from Beds  where id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

bedDB.getByRoomId = async ({ roomId }: bedTypes) => {
  const query = "Select * from Beds  where roomId = ?";
  const data = [roomId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

bedDB.getVacantBed = async ({ roomId, status }: bedTypes) => {
  const query =
    "Select * from Beds  where roomId = ? and status=? order by id limit 1";
  const data = [roomId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

bedDB.getVacantBeds = async ({ roomId, status }: bedTypes) => {
  const query = "Select * from Beds where roomId = ? and status=? order by id";
  const data = [roomId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

bedDB.getDetailsByRoomId = async ({ roomId }: bedTypes) => {
  const query =
    "Select B.id, B.status, B.isExtraBed, B.bunkPosition, R.id as roomId, R.roomNum, R.floor, RO.rent, RO.type as roomOptionType, RO.amenities, RO.name as roomOptionName from Beds as B join Rooms as R on R.id = B.roomId join RoomOptions as RO on RO.id = R.roomOptionId where B.roomId = ? order by B.id";
  const data = [roomId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

bedDB.getDetailsByPropId = async ({ propId }: {propId: any}) => {
  const query =
    "Select B.id, B.status, B.isExtraBed, B.bunkPosition, R.id as roomId, R.roomNum, R.floor, RO.rent, RO.type as roomOptionType, RO.amenities, RO.name as roomOptionName from Beds as B join Rooms as R on R.id = B.roomId join RoomOptions as RO on RO.id = R.roomOptionId where R.propId = ? order by B.id";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

bedDB.getDetailsGroupByRoomForClient = async ({ clientId }: {clientId: any}) => {
  const query =
    "Select B.id, B.status, B.isExtraBed, B.bunkPosition, R.id as roomId, R.roomNum, R.floor, RO.rent, RO.type as roomOptionType, RO.amenities, RO.name as roomOptionName from Beds as B join Rooms as R on R.id = B.roomId join RoomOptions as RO on RO.id = R.roomOptionId join Properties as P on R.propId = P.id where P.clientId = ? and P.type = ? order by B.id";
  const data = [
    clientId,
    CONSTANTS.PROPERTY_TYPE.FLAT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

bedDB.getCountsByPropId = async ({ propId }: roomsTypes) => {
  // const query =
  //   "Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant, (Select COUNT(id) from Beds where status != ? and roomId = R.id) as occupied from Rooms as R where R.propId = ? and R.status != ?";
  const query =
    "SELECT  COUNT(B.id) AS total, SUM(B.status = ?) AS vacant, SUM(B.status != ?) AS occupied, SUM(B.status = ?) as movingOut FROM Rooms R LEFT JOIN Beds B ON B.roomId = R.id WHERE R.propId = ? AND R.status != ? GROUP BY R.id;";
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    propId,
    CONSTANTS.ROOM_STATUS.INACTIVE
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    return {
      total: rows.reduce((a, b) => Number(a) + Number(b.total), 0),
      vacant: rows.reduce((a, b) => Number(a) + Number(b.vacant), 0),
      occupied: rows.reduce((a, b) => Number(a) + Number(b.occupied), 0),
    };
  } else return false;
};

bedDB.getCountsByPropIdForReport = async ({ propId }: roomsTypes) => {
  // const query =
  //   "Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant, (Select COUNT(id) from Beds where status != ? and roomId = R.id) as occupied from Rooms as R where R.propId = ? and R.status != ?";
  const query =
    "SELECT  COUNT(B.id) AS total, SUM(B.status = ?) AS vacant, SUM(B.status not in (?, ?)) AS occupied FROM Rooms R LEFT JOIN Beds B ON B.roomId = R.id WHERE R.propId = ? AND R.status != ? GROUP BY R.id;";
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT_RESERVED,
    propId,
    CONSTANTS.ROOM_STATUS.INACTIVE
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    return {
      total: rows.reduce((a, b) => Number(a) + Number(b.total), 0),
      vacant: rows.reduce((a, b) => Number(a) + Number(b.vacant), 0),
      occupied: rows.reduce((a, b) => Number(a) + Number(b.occupied), 0),
    };
  } else return false;
};

bedDB.getCountsByClientId = async ({ clientId }: any) => {
  // const query =
  //   "Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant, (Select COUNT(id) from Beds where status != ? and roomId = R.id) as occupied, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as movingOut from Rooms as R Join Properties as P on R.propId = P.id where P.clientId = ? and R.status != ? and P.status = ?";
  const query =
  "Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant, (Select COUNT(id) from Beds where status != ? and roomId = R.id) as occupied, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as movingOut from Rooms as R Join Properties as P on R.propId = P.id Left Join Flats as F on R.flatId = F.id where P.clientId = ? and R.status != ? and P.status = ? and (R.flatId IS NULL OR F.status = ?)";
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    clientId,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.FLAT_STATUS.ACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    return {
      total: rows.reduce((a, b) => a + b.total, 0),
      vacant: rows.reduce((a, b) => a + b.vacant, 0),
      occupied: rows.reduce((a, b) => a + b.occupied, 0),
      movingOut: rows.reduce((a, b) => a + b.movingOut, 0),
    };
  } else return false;
};

bedDB.getCountsByClientIdForWeb = async ({ clientId, propIds, }: any) => {
  // let query =
  //   "Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant, (Select COUNT(id) from Beds where status != ? and roomId = R.id) as occupied, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as movingOut from Rooms as R Join Properties as P on R.propId = P.id where P.clientId = ? and R.status != ? and P.status = ?";
  let query =
    "Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant, (Select COUNT(id) from Beds where status != ? and roomId = R.id) as occupied, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as movingOut from Rooms as R Join Properties as P on R.propId = P.id Left Join Flats as F on R.flatId = F.id where P.clientId = ? and R.status != ? and P.status = ? and (R.flatId IS NULL OR F.status = ?)";
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    clientId,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.FLAT_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 {
      total: rows.reduce((a, b) => a + b.total, 0),
      vacant: rows.reduce((a, b) => a + b.vacant, 0),
      occupied: rows.reduce((a, b) => a + b.occupied, 0),
      movingOut: rows.reduce((a, b) => a + b.movingOut, 0),
    };
  } else return false;
};

bedDB.getStatsForStaff = async ({ clientId, propertiesIds }: any) => {
  const query =
    `Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant, (Select COUNT(id) from Beds where status != ? and roomId = R.id) as occupied, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as movingOut from Rooms as R Join Properties as P on R.propId = P.id where P.clientId = ? and P.id in (${propertiesIds}) and R.status != ? and P.status = ?`;
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    clientId,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    return {
      total: rows.reduce((a, b) => a + b.total, 0),
      vacant: rows.reduce((a, b) => a + b.vacant, 0),
      occupied: rows.reduce((a, b) => a + b.occupied, 0),
      movingOut: rows.reduce((a, b) => a + b.movingOut, 0),
    };
  } else return false;
};

bedDB.getStatsForWebStaff = async ({ clientId, propertiesIds, propIds, }: any) => {
  let query =
    `Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant, (Select COUNT(id) from Beds where status != ? and roomId = R.id) as occupied, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as movingOut from Rooms as R Join Properties as P on R.propId = P.id where P.clientId = ? and P.id in (${propertiesIds}) and R.status != ? and P.status = ?`;
  const data = [
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    clientId,
    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 {
      total: rows.reduce((a, b) => a + b.total, 0),
      vacant: rows.reduce((a, b) => a + b.vacant, 0),
      occupied: rows.reduce((a, b) => a + b.occupied, 0),
      movingOut: rows.reduce((a, b) => a + b.movingOut, 0),
    };
  } else return false;
};

bedDB.vacantBeds = async ({ 
  clientId,
  propertyId, 
  locationId 
}: any) => {
  let query = `SELECT COUNT(B.id) AS vacantBeds FROM Beds AS B JOIN Rooms AS R ON B.roomId = R.id JOIN Properties AS P ON R.propId = P.id WHERE P.clientId = ? AND B.status = ? AND R.status != ? AND P.status = ?`;

  const data = [
    clientId,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

  if (propertyId && Number(propertyId) > 0) {
    query += ` AND P.id = ?`;
    data.push(Number(propertyId));
  }

  if (locationId && Number(locationId) > 0) {
    query += ` AND P.locationId = ?`;
    data.push(Number(locationId));
  }

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

bedDB.getStatsForSalesHead = async ({
  clientId,
  propertyId,
  locationId,
}: any) => {
  let query = `SELECT COUNT(B.id) AS total, SUM(B.status = ?) AS vacant, SUM(B.status != ? AND B.status != ?) AS occupied, SUM(B.status = ?) AS movingOut FROM Rooms R JOIN Properties P ON R.propId = P.id LEFT JOIN Beds B ON B.roomId = R.id WHERE P.clientId = ? AND R.status != ? AND P.status = ?`;

  let data = [
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    CONSTANTS.BED_STATUS.MOVING_OUT,
    clientId,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

  if (propertyId && Number(propertyId) > 0) {
    query += ` AND P.id = ?`;
    data.push(Number(propertyId));
  }

  if (locationId && Number(locationId) > 0) {
    query += ` AND P.locationId = ?`;
    data.push(Number(locationId));
  }

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) {
    return {
      total: Number(rows[0].total) || 0,
      vacant: Number(rows[0].vacant) || 0,
      occupied: Number(rows[0].occupied) || 0,
      movingOut: Number(rows[0].movingOut) || 0,
    };
  }

  return false;
};

bedDB.vacantBedsForSalesHead = async ({ 
  clientId, 
  staffId, 
  propertyId, 
  locationId 
}: any) => {
  let query = `SELECT COUNT(B.id) AS vacantBeds FROM Beds AS B JOIN Rooms AS R ON B.roomId = R.id JOIN Properties AS P ON R.propId = P.id WHERE P.clientId = ? AND B.status = ? AND R.status != ? AND P.status = ? AND EXISTS (SELECT 1 FROM PropertyStaff AS PS WHERE PS.propId = P.id AND PS.staffId = ?)`;

  const data = [
    clientId,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    staffId,
  ];

  if (propertyId && Number(propertyId) > 0) {
    query += ` AND P.id = ?`;
    data.push(Number(propertyId));
  }

  if (locationId && Number(locationId) > 0) {
    query += ` AND P.locationId = ?`;
    data.push(Number(locationId));
  }

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

bedDB.create = async ({ roomId, isExtraBed }: bedTypes) => {
  const query = "Insert into Beds (roomId, isExtraBed) values (?, ?)";
  const data = [roomId, isExtraBed];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

bedDB.updateStatus = async ({ id, status }: bedTypes) => {
  const query = "Update Beds set status = ? where id = ?";
  const data = [status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

bedDB.removeVacantBeds = async ({
  roomId,
  count,
  status,
}: bedTypes & { count: number }) => {
  const query = "Delete from Beds where roomId = ? and status = ? limit ?";
  const data = [roomId, status, count];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

bedDB.getBedOccupancyCount = async ({ clientId }: any) => {
  // const query =
  //   "select status, count(*) as total from Beds where roomId In (select id from Rooms where status != ? and propId IN (select id from Properties where clientId =? and status = ?)) group by status";
  const query =
    "select status, count(*) as total from Beds where roomId in (select R.id from Rooms R left join Flats F on R.flatId = F.id where R.status != ? and R.propId in (select id from Properties where clientId = ? and status = ?) and (R.flatId is null or F.status = ?)) group by status";
  //const data = [CONSTANTS.ROOM_STATUS.INACTIVE, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const data = [CONSTANTS.ROOM_STATUS.INACTIVE, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, CONSTANTS.FLAT_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    let occupied = 0;
    let vacant = 0;
    for (let row of rows) {
      if (1 == row.status) {
        vacant = vacant + row.total;
      } else {
        occupied = occupied + row.total;
      }
    }
    return {
      occupied: occupied,
      vacant: vacant,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
  } else
    return {
      occupied: 0,
      vacant: 0,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
};

bedDB.getBedOccupancyCountForStaff = async ({ clientId, staffId }: any) => {
  //const query =
    //"select status, count(*) as total from Beds where roomId In (select id from Rooms where status != ? and propId IN (select P.id from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId =? and PS.staffId = ? and P.status = ?)) group by status";
  const query =
  "select status, count(*) as total from Beds where roomId in (select R.id from Rooms R left join Flats F on R.flatId = F.id where R.status != ? and R.propId in (select P.id from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status = ?) and (R.flatId is null or F.status = ?)) group by status";
  //const data = [CONSTANTS.ROOM_STATUS.INACTIVE, clientId, staffId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const data = [CONSTANTS.ROOM_STATUS.INACTIVE, clientId, staffId, CONSTANTS.PROPERTY_STATUS.ACTIVE, CONSTANTS.FLAT_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    let occupied = 0;
    let vacant = 0;
    for (let row of rows) {
      if (1 == row.status) {
        vacant = vacant + row.total;
      } else {
        occupied = occupied + row.total;
      }
    }
    return {
      occupied: occupied,
      vacant: vacant,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
  } else
    return {
      occupied: 0,
      vacant: 0,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
};

bedDB.getBedOccupancyCountForWeb = async ({ clientId, propIds }: any) => {
  // let query =
  //   "select status, count(*) as total from Beds where roomId In (select id from Rooms where status != ? and propId IN (select id from Properties where clientId =? and status = ?";
  let query =
    "select status, count(*) as total from Beds where roomId in (select R.id from Rooms R left join Flats F on R.flatId = F.id where R.status != ? and (R.flatId is null or F.status = ?) and R.propId in (select P.id from Properties as P where P.clientId = ? and P.status = ?";
  //const data = [CONSTANTS.ROOM_STATUS.INACTIVE, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const data = [CONSTANTS.ROOM_STATUS.INACTIVE, CONSTANTS.FLAT_STATUS.ACTIVE, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];

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

  query += ` )) group by status`

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    let occupied = 0;
    let vacant = 0;
    for (let row of rows) {
      if (1 == row.status) {
        vacant = vacant + row.total;
      } else {
        occupied = occupied + row.total;
      }
    }
    return {
      occupied: occupied,
      vacant: vacant,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
  } else
    return {
      occupied: 0,
      vacant: 0,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
};

bedDB.getBedCountByPropIds = async ({ propIds }: any) => {
  const query =
    `select count(*) as total from Beds where roomId In (select id from Rooms where status != ? and propId IN (select id from Properties where status = ? and id in (${propIds.map(() => '?').join(',')})))`
  const data = [CONSTANTS.ROOM_STATUS.INACTIVE, CONSTANTS.PROPERTY_STATUS.ACTIVE, ...propIds];

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

bedDB.getBedCountByFlatIds = async ({ flatIds }: any) => {
  const query =
    `select count(*) as total from Beds where roomId In (select id from Rooms where status != ? and flatId IN (${flatIds.map(() => '?').join(',')}))`
  const data = [CONSTANTS.ROOM_STATUS.INACTIVE, ...flatIds];

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

bedDB.getBedOccupancyCountForWebStaff = async ({ clientId, staffId, propIds, }: any) => {
  // let query =
  //   "select status, count(*) as total from Beds where roomId In (select id from Rooms where status != ? and propId IN (select P.id from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId =? and PS.staffId = ? and P.status = ?";
  let query =
  "select status, count(*) as total from Beds where roomId in (select R.id from Rooms R left join Flats F on R.flatId = F.id where R.status != ? and (R.flatId is null or F.status = ?) and R.propId in (select P.id from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status = ?";
  //const data = [CONSTANTS.ROOM_STATUS.INACTIVE, clientId, staffId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const data = [CONSTANTS.ROOM_STATUS.INACTIVE, CONSTANTS.FLAT_STATUS.ACTIVE, clientId, staffId, CONSTANTS.PROPERTY_STATUS.ACTIVE];

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

  query += ` )) group by status`

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    let occupied = 0;
    let vacant = 0;
    for (let row of rows) {
      if (1 == row.status) {
        vacant = vacant + row.total;
      } else {
        occupied = occupied + row.total;
      }
    }
    return {
      occupied: occupied,
      vacant: vacant,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
  } else
    return {
      occupied: 0,
      vacant: 0,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
};

bedDB.getBedOccupancyCountForProp = async ({ propId }: any) => {
  // const query =
  //   "select status, count(*) as total from Beds where roomId In (select id from Rooms where propId = ? and status != ?) group by status";
  const query =
  "select status, count(*) as total from Beds where roomId in (select R.id from Rooms R left join Flats F on R.flatId = F.id where R.propId = ? and R.status != ? and (R.flatId is null or F.status != ?)) group by status";
  //const data = [propId, CONSTANTS.ROOM_STATUS.INACTIVE];
  const data = [propId, CONSTANTS.ROOM_STATUS.INACTIVE, CONSTANTS.FLAT_STATUS.INACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    let occupied = 0;
    let vacant = 0;
    for (let row of rows) {
      if (1 == row.status) {
        vacant = vacant + row.total;
      } else {
        occupied = occupied + row.total;
      }
    }
    return {
      occupied: occupied,
      vacant: vacant,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
  } else
    return {
      occupied: 0,
      vacant: 0,
      month: Number(moment().format("MM")),
      year: Number(moment().format("YYYY")),
    };
};

bedDB.getVacantBedsForFlat = async ({ flatId }: roomsTypes) => {
  const query =
    "Select B.id, B.roomId, B.status, B.createdAt, B.updatedAt from Beds as B left join Rooms as R on R.id = B.roomId where B.status = ? and R.flatId = ?";
  const data = [CONSTANTS.BED_STATUS.VACANT, flatId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

bedDB.getCountsByRoomId = async ({ id }: roomsTypes) => {
  const query =
    "Select (Select COUNT(id) from Beds where roomId = R.id) as total, (Select COUNT(id) from Beds where status = ? and roomId = R.id) as vacant from Rooms as R where R.id = ?";
  const data = [CONSTANTS.BED_STATUS.VACANT, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    return {
      totalBeds: rows[0].total || 0,
      vacantBeds: rows[0].vacant || 0,
    };
  } else return false;
};

bedDB.getCountsByRoomIdForProp = async ({ propId }: roomsTypes) => {
  const query =
    "Select Count(B.id) as total, Sum(CASE WHEN B.status = ? THEN 1 ELSE 0 END) as vacant, R.id as roomId from Rooms as R join Beds as B on B.roomId = R.id where R.propId = ? group by R.id";
  const data = [CONSTANTS.BED_STATUS.VACANT, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

bedDB.getCountByClientId = async ({ clientId }: any) => {
  const query =
    "Select Count(B.id) as totalBeds from Beds as B join Rooms as R on R.id = B.roomId join Properties as P on P.id = R.propId where P.clientId = ? and P.status = ? and R.status != ?;";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, CONSTANTS.ROOM_STATUS.INACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    return rows[0].totalBeds;
  } else return false;
};

bedDB.getCountByClientIdForStaff = async ({ clientId, staffId }: any) => {
  const query =
    "Select Count(B.id) as totalBeds from Beds as B join Rooms as R on R.id = B.roomId join Properties as P on P.id = R.propId join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and P.status = ? and R.status != ? and PS.staffId = ?;";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, CONSTANTS.ROOM_STATUS.INACTIVE, staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    return rows[0].totalBeds;
  } else return false;
};

bedDB.getOccupiedCountByClientId = async ({ clientId }: any) => {
  const query =
    "Select Count(B.id) as count from Beds as B join Rooms as R on R.id = B.roomId join Properties as P on P.id = R.propId where P.clientId = ? and P.status = ? and R.status != ? and B.status != ?;";
  const data = [
    clientId, 
    CONSTANTS.PROPERTY_STATUS.ACTIVE, 
    CONSTANTS.ROOM_STATUS.INACTIVE, 
    CONSTANTS.BED_STATUS.VACANT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    return rows[0].count;
  } else return false;
};

bedDB.delete = async ({ id }: bedTypes) => {
  const query = "Delete from Beds where id = ? limit 1";
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

bedDB.removeAllExtraBedByRoomId = async ({ roomId }: bedTypes) => {
  const query = "Delete from Beds where roomId = ? and isExtraBed = ? and status=?";
  const data = [roomId, 1, CONSTANTS.BED_STATUS.VACANT];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

bedDB.updateBunkPosition = async ({
  id,
  bunkPosition,
}: bedTypes) => {
  const query = "Update Beds set bunkPosition = ? where id = ? limit 1";
  const data = [bunkPosition, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

// bedDB.getByPropIdWithDateRange = async ({ propId, endDate }: any) => {
//   const query =
//     "Select B.* from Beds as B join Rooms as R on R.id = B.roomId join Properties as P on P.id = R.propId where P.id = ? and P.status = ? and R.status != ? and B.status != ? and Date(B.createdAt) < ?;";
//   const data = [
//     propId, 
//     CONSTANTS.PROPERTY_STATUS.ACTIVE, 
//     CONSTANTS.ROOM_STATUS.INACTIVE, 
//     CONSTANTS.BED_STATUS.VACANT,
//     endDate,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

// bedDB.getByClientIdWithDateRange = async ({ clientId, endDate }: any) => {
//   const query =
//     "Select B.* from Beds as B join Rooms as R on R.id = B.roomId join Properties as P on P.id = R.propId where P.clientId = ? and P.status = ? and R.status != ? and B.status != ? and Date(B.createdAt) < ?;";
//   const data = [
//     clientId, 
//     CONSTANTS.PROPERTY_STATUS.ACTIVE, 
//     CONSTANTS.ROOM_STATUS.INACTIVE, 
//     CONSTANTS.BED_STATUS.VACANT,
//     endDate,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

bedDB.getAvailableBedsByRoomId = async ({
  roomId,
}: bedTypes) => {
  //const query = `Select * from Beds where roomId = ? and status in (?, ?) order by id asc`;
  const query = `Select * from Beds where roomId = ? and status in (?) order by id asc`;
  const data = [
    roomId,
    CONSTANTS.BED_STATUS.VACANT,
    //CONSTANTS.BED_STATUS.MOVING_OUT,
  ];

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

export default bedDB;