import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import commonFacilitiesTypes from "../schemas/commonFacilities.schema";
import facilityBookingTypes from "../schemas/facilityBooking.schema";
import CONSTANTS from "../config/constants";

const facilityDB: any = {};

facilityDB.add = async ({
  clientId,
  propId,
  type,
  status,
  maxConcurrentCap,
  name,
  openingTime,
  closingTime,
  maxBookingHours,
  repeatBookingCooldown,
}: commonFacilitiesTypes) => {
  const query = `Insert into CommonFacilities (clientId, propId, type, status, maxConcurrentCap, name, openingTime, closingTime, maxBookingHours, repeatBookingCooldown) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  const data = [
    clientId,
    propId,
    type,
    status,
    maxConcurrentCap,
    name,
    openingTime,
    closingTime,
    maxBookingHours,
    repeatBookingCooldown,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

facilityDB.update = async ({
  id,
  status,
  maxConcurrentCap,
  name,
  openingTime,
  closingTime,
  maxBookingHours,
  repeatBookingCooldown,
}: commonFacilitiesTypes) => {
  const query = `UPDATE CommonFacilities SET status = ?, maxConcurrentCap = ?, name = ?, openingTime = ?, closingTime = ?, maxBookingHours = ?, repeatBookingCooldown = ? WHERE id = ?`;

  const data = [
    status,
    maxConcurrentCap,
    name,
    openingTime,
    closingTime,
    maxBookingHours,
    repeatBookingCooldown,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

facilityDB.getById = async ({ id }: commonFacilitiesTypes) => {
  const query = `SELECT * FROM CommonFacilities WHERE id = ?`;
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

facilityDB.getByClientIdAndFilters = async ({
  clientId,
  propFilter,
  typeFilter,
}: {
  clientId: number;
  propFilter: string[];
  typeFilter: string[];
}) => {
  let query = `SELECT * FROM CommonFacilities WHERE clientId = ?`;
  const data: any = [clientId];

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

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

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

facilityDB.getByClientIdAndFiltersForStaff = async ({
  clientId,
  propertiesIds,
  propFilter,
  typeFilter,
}: {
  clientId: number;
  propertiesIds: string[];
  propFilter: string[];
  typeFilter: string[];
}) => {
  let query = `SELECT * FROM CommonFacilities WHERE clientId = ? AND propId IN (${propertiesIds.map(() => "?").join(",")})`;
  const data: any = [clientId, ...propertiesIds];

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

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

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

facilityDB.getFutureBookedSlotsById = async ({ id }: facilityBookingTypes) => {
  const query = `SELECT FB.*, T.name as tenantName FROM FacilityBookings as FB join Tenants as T on T.id = FB.tenantId WHERE FB.commonFacilityId = ? AND DATE(bookingDate) > CURDATE() OR (DATE(bookingDate) = CURDATE() AND endTime > NOW())`;
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

facilityDB.getFutureBookedSlotsByBookingDate = async ({ id, bookingDate }: facilityBookingTypes) => {
  const query = `SELECT FB.*, T.name as tenantName FROM FacilityBookings as FB join Tenants as T on T.id = FB.tenantId WHERE FB.commonFacilityId = ? AND bookingDate = ?`;
  const data = [id, bookingDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

facilityDB.addBooking = async ({
  commonFacilityId,
  bookingDate,
  startTime,
  endTime,
  tenantId,
  clientId,
  propId,
  description,
  noOfPeople,
}: facilityBookingTypes) => {
  const query = `INSERT INTO FacilityBookings (commonFacilityId, bookingDate, startTime, endTime, tenantId, clientId, propId, description, noOfPeople) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [commonFacilityId, bookingDate, startTime, endTime, tenantId, clientId, propId, description, noOfPeople];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

facilityDB.getBookingById = async ({id}: facilityBookingTypes) => {
  const query = `SELECT * FROM FacilityBookings WHERE id = ?`;
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

facilityDB.getBookingsByTenantIdAndClientId = async ({
  tenantId,
  clientId,
  pageNum,
  limit,
}: facilityBookingTypes & {pageNum: number, limit: number}) => {
  let query = `SELECT FB.id, FB.clientId, FB.propId, FB.commonFacilityId, FB.tenantId, FB.status as bookingStatus, FB.cancelledByUserType, FB.cancelledBy, FB.bookingDate, TIME_FORMAT(FB.startTime, '%H:%i') as startTime, TIME_FORMAT(FB.endTime, '%H:%i') as endTime, FB.description, FB.noOfPeople, FB.createdAt, CF.name as facilityName, P.name as propertyName FROM FacilityBookings as FB join CommonFacilities as CF on CF.id = FB.commonFacilityId join Properties as P on P.id = FB.propId WHERE FB.tenantId = ? AND FB.clientId = ? ORDER BY id DESC limit ?, ?`;
  const offset = (pageNum - 1) * limit;
  const data: any = [
    tenantId, 
    clientId,
    `${offset}`,
    `${limit}`,
  ];

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

facilityDB.listBookingsForClient = async ({
  clientId,
  startDate,
  endDate,
  pageNum,
  limit,
}: facilityBookingTypes & {pageNum: number; limit: number; startDate: string; endDate: string;}) => {
  let query = `SELECT FB.id, FB.clientId, FB.propId, FB.commonFacilityId, FB.status as bookingStatus, FB.cancelledByUserType, FB.cancelledBy, FB.tenantId, FB.bookingDate, TIME_FORMAT(FB.startTime, '%H:%i') as startTime, TIME_FORMAT(FB.endTime, '%H:%i') as endTime, FB.description, FB.noOfPeople, CF.name as facilityName, P.name as propertyName, T.name as tenantName FROM FacilityBookings as FB join CommonFacilities as CF on CF.id = FB.commonFacilityId join Properties as P on P.id = FB.propId join Tenants as T on T.id = FB.tenantId WHERE FB.clientId = ? `;
  const offset = (pageNum - 1) * limit;
  const data: any = [ 
    clientId,
  ];

  if (startDate && endDate) {
    query += ` AND bookingDate BETWEEN ? AND ?`;
    data.push(startDate, endDate);
  }

  query += ` ORDER BY id DESC LIMIT ?, ?`;
  data.push(`${offset}`,`${limit}`);

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

facilityDB.deleteBooking = async ({ id, cancelledBy, cancelledByUserType }: facilityBookingTypes) => {
  const query = `Update FacilityBookings set status = ?, cancelledBy = ?, cancelledByUserType = ? WHERE id = ?`;
  const data = [ CONSTANTS.FACILITY_BOOKING_STATUS.CANCELLED, cancelledBy, cancelledByUserType, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

facilityDB.deleteFacility = async ({ id }: commonFacilitiesTypes) => {
  const query = `DELETE FROM CommonFacilities WHERE id = ?`;
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

export default facilityDB;