import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import electricityTypes from "../schemas/electricity.schema";
import electricityBillTypes from "../schemas/electricityBill.schema";
import CONSTANTS from "../config/constants";

const electricityDB: any = {};

electricityDB.getByClientId = async ({ clientId }: electricityTypes) => {
  const query =
    "Select * from ElectricityUnits where clientId = ? order by id desc";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

electricityDB.getForCurrentMonth = async ({
  clientId,
  propId,
  roomId,
}: electricityTypes) => {
  const query =
    "Select * from ElectricityUnits where clientId = ? and propId = ? and roomId = ? and Month(createdAt) = Month(now()) order by id desc";
  const data = [clientId, propId, roomId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

electricityDB.getForPreviousMonth = async ({
  clientId,
  propId,
  roomId,
}: electricityTypes) => {
  const query =
    "Select * from ElectricityUnits where clientId = ? and propId = ? and roomId = ? and Month(createdAt) = MONTH(DATE_ADD(now(), INTERVAL -1 MONTH)) order by id desc";
  const data = [clientId, propId, roomId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].units;
  else return false;
};

electricityDB.getRoomsForElectricity = async ({ propId }: electricityTypes) => {
  const query =
    "Select R.id, R.roomNum, R.floor, (Select units from ElectricityUnits as E where E.propId = ? and R.id = E.roomId and MONTH(E.createdAt) = MONTH(DATE_ADD(now(), INTERVAL -1 MONTH)) order by E.id limit 1) as lastMonthUnits, (Select units from ElectricityUnits as E where E.propId = ? and R.id = E.roomId and MONTH(E.createdAt) = MONTH(now()) order by E.id limit 1) as currentMonthUnits from Rooms as R where R.propId = ? and R.status in (?, ?) order by R.id";
  const data = [
    propId,
    propId,
    propId,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

electricityDB.add = async ({
  units,
  clientId,
  roomId,
  propId,
}: electricityTypes) => {
  const query =
    "INSERT INTO ElectricityUnits (units, clientId, roomId, propId) VALUES (?, ?, ?, ?)";
  const data = [units, clientId, roomId, propId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};
electricityDB.addPrevious = async ({
  units,
  clientId,
  roomId,
  propId,
}: electricityTypes) => {
  const query =
    "INSERT INTO ElectricityUnits (units, clientId, roomId, propId, createdAt) VALUES (?, ?, ?, ?, DATE_ADD(now(), INTERVAL -1 MONTH))";
  const data = [units, clientId, roomId, propId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

electricityDB.getFlatRoomsForElectricity = async ({
  propId,
}: electricityBillTypes) => {
  const query =
    "Select R.id, R.roomNum, F.name as floor, (Select amount from ElectricityBill as E where E.propId = ? and R.id = E.roomFlatId and MONTH(E.createdAt) = MONTH(now()) order by E.id limit 1) as billAmount from Rooms as R INNER JOIN Flats as F ON F.id=R.flatId where R.propId = ? and R.status in (?, ?) order by R.id";
  const data = [
    propId,
    propId,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

electricityDB.getAllFlats = async ({ propId }: electricityBillTypes) => {
  const query =
    "SELECT concat('Flat ',f.name) as name, f.id, COUNT(r.id) AS roomCount FROM Flats f LEFT JOIN Rooms r ON f.id = r.flatId WHERE f.propId = ? GROUP BY f.id, f.name order by f.id";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

electricityDB.getPreviousBill = async ({ propId }: electricityBillTypes) => {
  const query =
    "SELECT f.id, eb.amount, eb.startDate, eb.endDate, concat('Flat ',f.name) as name, eb.noOfTenant as tenantCount, eb.createdAt as generateDate, eb.currentReading as previousReading, eb.docs FROM Flats f INNER JOIN ElectricityBill eb ON f.id = eb.roomFlatId WHERE f.propId = ? order by eb.id desc";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

electricityDB.getPgRoomsForElectricity = async ({
  propId,
}: electricityBillTypes) => {
  const query =
    "Select R.id, R.roomNum, R.floor, (Select amount from ElectricityBill as E where E.propId = ? and R.id = E.roomFlatId and MONTH(E.createdAt) = MONTH(now()) order by E.id limit 1) as billAmount from Rooms as R where R.propId = ? and R.status in (?, ?) order by R.id";
  const data = [
    propId,
    propId,
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

electricityDB.addAmount = async ({
  amount,
  clientId,
  roomFlatId,
  propId,
  startDate,
  endDate,
  noOfTenant,
  previousReading,
  currentReading,
}: electricityBillTypes) => {
  const query =
    "INSERT INTO ElectricityBill (amount, clientId, roomFlatId, propId, startDate, endDate, noOfTenant, previousReading, currentReading) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    amount,
    clientId,
    roomFlatId,
    propId,
    startDate,
    endDate,
    noOfTenant,
    previousReading,
    currentReading,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

electricityDB.getBillCurrentMonth = async ({
  clientId,
  propId,
  roomFlatId,
}: electricityBillTypes) => {
  const query =
    "Select * from ElectricityBill where clientId = ? and propId = ? and roomFlatId = ? and Month(createdAt) = Month(now()) order by id desc";
  const data = [clientId, propId, roomFlatId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

electricityDB.getAllFloors = async ({ propId }: electricityBillTypes) => {
  const query =
    "select floor , count(id) as roomCount from Rooms where propId = ? group by floor order by  CASE WHEN floor = 'G' THEN 0 ELSE 1 END, floor + 0";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

electricityDB.getAllRooms = async ({ propId }: electricityBillTypes) => {
  const query =
    "select R.id, R.floor, R.roomNum, RO.amenities from Rooms as R join RoomOptions as RO on R.roomOptionId = RO.id where R.propId = ? order by R.floor, R.roomNum";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

electricityDB.getAllRoomsForFlat = async ({ propId }: electricityBillTypes) => {
  const query =
    "select R.id, R.flatId, R.roomNum, RO.amenities from Rooms as R join RoomOptions as RO on R.roomOptionId = RO.id where R.propId = ? order by R.roomNum";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

electricityDB.getPreviousBillForRoom = async ({
  propId,
}: electricityBillTypes) => {
  const query =
    "SELECT r.id,r.roomNum AS roomNumber, eb.amount, eb.noOfTenant as tenantCount, eb.startDate,eb.endDate, eb.createdAt as generateDate, eb.currentReading as previousReading, eb.docs FROM Rooms r JOIN ElectricityBill eb ON r.id = eb.roomFlatId where r.propId = ? ORDER BY r.floor, r.roomNum, eb.id desc";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

electricityDB.updateUploadedDoc = async ({
  id,
  docs,
}: electricityBillTypes) => {
  const query = 
    "Update ElectricityBill set docs = ? where id = ?";
  const data = [
    docs,
    id,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
}

export default electricityDB;