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

const flatDB: any = {};

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

flatDB.getByClientId = async ({ clientId }: flatTypes) => {
  const query = "Select * from Flats where clientId = ? ORDER BY CASE WHEN name LIKE 'G%' THEN 0 ELSE 1 END,name ASC;";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

flatDB.getByPropId = async ({ propId }: flatTypes) => {
  const query = "Select * from Flats where propId = ? ORDER BY CASE WHEN name LIKE 'G%' THEN 0 ELSE 1 END,name ASC;";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

flatDB.getByPropIds = async ({ propIds }: {propIds: any[]}) => {
  const query = `Select * from Flats where propId in (${propIds.map(() => '?').join(',')}) ORDER BY CASE WHEN name LIKE 'G%' THEN 0 ELSE 1 END,name ASC;`;
  const data = [...propIds];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

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

flatDB.getCountsByPropId = async ({ propId }: flatTypes) => {
  const query = "Select Count(id) as totalCount from Flats where propId = ?";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

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

flatDB.create = async ({ clientId, propId, name }: flatTypes) => {
  const query = "Insert into Flats (clientId, propId, name) values (?,?,?)";
  const data = [clientId, propId, name];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

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

flatDB.updateName = async ({ id, name }: flatTypes) => {
  const query = "Update Flats set name =? where id =?";
  const data = [name, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

flatDB.isFlatFullyVacantOld = async ({ id }: flatTypes) => {
  const query =
  "Select Count(id) as notVacant from Rooms where flatId = ? and status != ?";
  const data = [id, CONSTANTS.ROOM_STATUS.VACANT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows[0].notVacant === 0) return true;
  else return false;
};

flatDB.isFlatFullyOccupied = async ({ id }: flatTypes) => {
  const query =
  "Select Count(id) as totalRooms, SUM(case when status = ? then 1 else 0 end) as occupiedRooms from Rooms where flatId = ?";
  const data = [CONSTANTS.ROOM_STATUS.OCCUPIED, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (Number(rows[0].totalRooms) === Number(rows[0].occupiedRooms)) return true;
  else return false;
};

flatDB.isFlatFullyVacant = async ({ id }: flatTypes) => {
  const query =
  "Select Count(id) as totalRooms, SUM(case when status = ? then 1 else 0 end) as vacantRooms from Rooms where flatId = ? and status != ? union select Count(distinct(R.id)) as totalRooms, SUM(CASE WHEN R.status = ? THEN 1 ELSE 0 END) from Rooms as R inner join Beds as B on R.id = B.roomId where R.flatId = ? and R.status != ?";
  const data = [
    CONSTANTS.ROOM_STATUS.VACANT, 
    id, 
    CONSTANTS.ROOM_STATUS.INACTIVE, 
    CONSTANTS.ROOM_STATUS.VACANT, 
    id, 
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length < 2){
    return false;
  }
  if (Number(rows[0].totalRooms) === Number(rows[1].totalRooms) && Number(rows[0].totalRooms) === Number(rows[0].vacantRooms)) return true;
  else return false;
};

flatDB.updateGender = async ({ id, gender }: flatTypes) => {
  const query = "Update Flats set gender =? where id =?";
  const data = [gender, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

flatDB.updateGenderByPropId = async ({ propId, gender }: flatTypes) => {
  const query = "Update Flats set gender =? where propId =?";
  const data = [gender, propId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

flatDB.updateWifiPassword = async ({ id, wifiPassword }: flatTypes) => {
  const query = "Update Flats set wifiPassword =? where id =?";
  const data = [wifiPassword, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

flatDB.updateWifiPasswordByPropId = async ({ propId, wifiPassword }: flatTypes) => {
  const query = "Update Flats set wifiPassword =? where propId =?";
  const data = [wifiPassword, propId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

export default flatDB;