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

const roomOptionDB: any = {};

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

roomOptionDB.getByPropIdAndType = async ({
  propId,
  type,
}: roomsOptionTypes) => {
  const query =
    "Select * from RoomOptions where propId =? and type =? order by id desc";
  const data = [propId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomOptionDB.getByPropId = async ({
  propId,
}: roomsOptionTypes) => {
  const query =
    "Select * from RoomOptions 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;
};

roomOptionDB.getByName = async ({ propId, name, type }: roomsOptionTypes) => {
  const query =
    "Select * from RoomOptions where propId =? and name =? and type=? order by id desc";
  const data = [propId, name, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

roomOptionDB.getCountByPropId = async ({ propId }: roomsOptionTypes) => {
  const query =
    "Select Count(id) as count, type from RoomOptions where propId = ? GROUP BY type ";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomOptionDB.getByPropIdForShare = async ({ propId }: roomsOptionTypes) => {
  const query =
    "SELECT V1.id, V1.propId, V1.type, V1.rent, V1.security, V1.amenities, (select count(R.id) from Rooms as R where R.status in (?, ?) and R.propId = ? and R.roomOptionId = V1.id) as availableRooms FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY type ORDER BY rent ASC, id ASC) as rn from RoomOptions where propId = ?) as V1 where rn = 1; ";
  const data = [CONSTANTS.ROOM_STATUS.VACANT, CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED, propId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

roomOptionDB.create = async ({
  propId,
  rent,
  security,
  type,
  name,
  amenities,
  totalBedCount,
}: roomsOptionTypes) => {
  const query =
    "Insert into RoomOptions (propId, rent, security, type, name, amenities, totalBedCount) values (?,?,?,?,?,?,?)";
  const data = [propId, rent, security, type, name, amenities, totalBedCount];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

roomOptionDB.update = async ({
  id,
  rent,
  security,
  name,
  amenities,
}: roomsOptionTypes) => {
  const query =
    "Update RoomOptions set rent =?, name =?, amenities =?, security=? where id =?";
  const data = [rent, name, amenities, security, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

roomOptionDB.delete = async ({ id }: roomsOptionTypes) => {
  const query = "Delete from RoomOptions where id = ? limit 1";
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

export default roomOptionDB;