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

const occupancyReportDB: any = {};

occupancyReportDB.getOccupancyReport = async ({ clientId }: any) => {
  const query =
    "select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month  from OccupancyReports where clientId=? and year=year(curdate()) group by month";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

occupancyReportDB.getOccupancyReportByYearMonth = async ({ clientId, month, year }: any) => {
  const query =
    "select (sum(occupied) + sum(onHold)) as occupied, sum(total) as total, sum(vacant) as vacant, month, year from OccupancyReports where clientId=? and year = ? and month = ? group by year, month";
  const data = [clientId, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

occupancyReportDB.getOccupancyReportByYearMonthForWeb = async ({ clientId, month, year, propIds, }: any) => {
  let query =
    "select (sum(occupied) + sum(onHold)) as occupied, sum(total) as total, sum(vacant) as vacant, month, year from OccupancyReports where clientId=? and year = ? and month = ? ";
  const data = [clientId, year, month];

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

  query += ` group by year, month`

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

occupancyReportDB.getOccupancyReportForStaffForWeb = async ({ clientId, staffId }: any) => {
  const query =
    "select (any_value(sum(OCR.occupied)) + any_value(sum(OCR.onHold))) as occupied, any_value(sum(OCR.total)) as total, any_value(sum(OCR.vacant)) as vacant, OCR.month  from OccupancyReports as OCR join PropertyStaff as PS on OCR.propId = PS.propId where OCR.clientId=? and PS.staffId = ? and OCR.year=year(curdate()) group by OCR.month";
  const data = [clientId, staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

occupancyReportDB.getOccupancyReportForDate = async ({ clientId, date }: any) => {
  const query =
    "select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month  from OccupancyReports where clientId=? and year=year(?) and month = month(?) group by month";
  const data = [clientId, date, date];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

occupancyReportDB.getOccupancyReportForProp = async ({ propId }: any) => {
  const query =
    "select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month  from OccupancyReports where propId=? and year=year(curdate()) group by month";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

occupancyReportDB.getOccupancyReportForPropByYearMonth = async ({ propId, year, month }: any) => {
  const query =
    "select (sum(occupied) + sum(onHold)) as occupied, sum(total) as total, sum(vacant) as vacant, month, year from OccupancyReports where propId=? and year = ? and month = ? group by year, month";
  const data = [propId, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

occupancyReportDB.getOccupancyReportForStaff = async ({ clientId, propertiesIds }: any) => {
  const query =
    `select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month from OccupancyReports where clientId = ? and propId in (${propertiesIds}) and year=year(curdate()) group by month`;
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

occupancyReportDB.getOccupancyReportForStaffByYearMonth = async ({ clientId, propertiesIds, year, month }: any) => {
  const query =
    `select (sum(occupied) + sum(onHold)) as occupied, sum(total) as total, sum(vacant) as vacant, month, year from OccupancyReports where clientId = ? and propId in (${propertiesIds}) and year = ? and month = ? group by year, month`;
  const data = [clientId, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

occupancyReportDB.getOccupancyReportByYearMonthForWebStaff = async ({ clientId, propertiesIds, year, month, propIds, }: any) => {
  let query =
    `select (sum(occupied) + sum(onHold)) as occupied, sum(total) as total, sum(vacant) as vacant, month, year from OccupancyReports where clientId = ? and propId in (${propertiesIds}) and year = ? and month = ? `;
  const data = [clientId, year, month];

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

  query += ` group by year, month`

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

occupancyReportDB.getOccupancyReportFY = async ({
  clientId,
  startYear,
  endYear,
}: any) => {
  const query =
    "select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month, year from OccupancyReports where clientId = ? and (month <= 3 and year = ? OR month > 3 and year = ?) group by year, month";
  const data = [clientId, endYear, startYear];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

occupancyReportDB.getOccupancyReportFYForStaff = async ({
  clientId,
  propertiesIds,
  startYear,
  endYear,
}: any) => {
  const query =
    `select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month, year from OccupancyReports where clientId = ? and propId in (${propertiesIds}) and (month <= 3 and year = ? OR month > 3 and year = ?) group by year, month`;
  const data = [clientId, endYear, startYear];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

occupancyReportDB.getOccupancyReportFYForWeb = async ({
  clientId,
  propIds,
  startYear,
  endYear,
}: any) => {
  let query =
    "select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month, year from OccupancyReports where clientId = ? and (month <= 3 and year = ? OR month > 3 and year = ?)";
  const data = [clientId, endYear, startYear];

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

  query += ` group by year, month`

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

occupancyReportDB.getOccupancyReportFYForWebStaff = async ({
  clientId,
  propIds,
  propertiesIds,
  startYear,
  endYear,
}: any) => {
  let query =
    `select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month, year from OccupancyReports where clientId = ? and propId in (${propertiesIds}) and (month <= 3 and year = ? OR month > 3 and year = ?)`;
  const data = [clientId, endYear, startYear];

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

  query += ` group by year, month`

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

occupancyReportDB.getOccupancyReportFYForProp = async ({
  propId,
  endYear,
  startYear,
}: any) => {
  const query =
    "select (any_value(sum(occupied)) + any_value(sum(onHold))) as occupied, any_value(sum(total)) as total, any_value(sum(vacant)) as vacant, month, year from OccupancyReports where propId = ? and (month <= 3 and year = ? OR month > 3 and year = ?) group by year, month";
  const data = [propId, endYear, startYear];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

occupancyReportDB.getOccupancyAndVacanyRateByClientIdAndDateRange = async ({
  clientId,
  startDate,
  endDate,
}: any) => {
  const query = 
    `Select (SUM(occupancyRate)/Count(*)) as occupancyRate, (100 - SUM(occupancyRate)/Count(*)) as vacancyRate from (Select ((SUM(occupied) + SUM(onHold))/SUM(total))*100 as occupancyRate from OccupancyReports where clientId = ? and DATE(createdAt) BETWEEN ? and ? group by year, month) as V`;
  const data = [
    clientId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
}

occupancyReportDB.getBedCountByClientIdAndYearMonth = async ({
  clientId,
  month,
  year,
}: any) => {
  const query =
    `Select IFNULL(SUM(total), 0) as totalBeds, IFNULL(sum(occupied) + sum(onHold), 0) as occupiedBeds from OccupancyReports where clientId = ? and month = ? and year = ?`;

  const data = [
    clientId,
    month,
    year
  ];

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

export default occupancyReportDB;