import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import extraChargesTypes from "../schemas/extraCharge.schema";
import utilityBillTypes from "../schemas/utilityBill.schema";
import extraChargesPropertyTypes from "../schemas/extraChargeProperty.schema";

const extraChargeDB: any = {};

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

extraChargeDB.getByClientId = async ({
  clientId,
  limit,
  pageNum,
}: extraChargesTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select * from ExtraCharges where clientId = ? order by id desc  limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

extraChargeDB.getByClientIdAndPropId = async ({
  clientId,
  propId,
  limit,
  pageNum,
}: extraChargesTypes &
  extraChargesPropertyTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select E.id, E.name, E.type, E.amount, E.repetitionType, E.createdAt from ExtraCharges as E inner join ExtraChargesProperty as EP on E.id = EP.extraChargeId where EP.clientId = ? and EP.propId = ? order by E.id desc  limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, propId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

extraChargeDB.getAllByClientIdAndPropId = async ({
  clientId,
  propId,
}: extraChargesTypes &
  extraChargesPropertyTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select E.id, E.name, E.type, E.amount, E.repetitionType, E.createdAt from ExtraCharges as E inner join ExtraChargesProperty as EP on E.id = EP.extraChargeId where EP.clientId = ? and EP.propId = ? order by E.id desc";
  const data = [clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

extraChargeDB.getByPropId = async ({ propId }: extraChargesPropertyTypes) => {
  const query =
    "Select E.id, E.name, E.type, E.amount, E.repetitionType, E.createdAt from ExtraChargesProperty as EP inner join ExtraCharges as E on E.id = EP.extraChargeId where EP.propId = ? order by E.id";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

extraChargeDB.getProperties = async ({
  extraChargeId,
}: extraChargesPropertyTypes) => {
  const query =
    "Select P.name, P.id  from ExtraChargesProperty as EP inner join Properties as P on P.id = EP.propId where EP.extraChargeId = ? order by EP.id desc";
  const data = [extraChargeId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

extraChargeDB.getElectricityPrice = async ({
  clientId,
  propId,
  type,
}: extraChargesPropertyTypes & extraChargesTypes) => {
  const query =
    "Select E.amount as pricePerUnit from ExtraChargesProperty as EP inner join ExtraCharges as E on E.id = EP.extraChargeId where EP.clientId = ? and EP.propId = ? and E.type = ?  order by EP.id desc";
  const data = [clientId, propId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return { pricePerUnit: 0 };
};

extraChargeDB.getByPropAndType = async ({
  clientId,
  propId,
  type,
}: extraChargesPropertyTypes & extraChargesTypes) => {
  const query =
    "Select * from ExtraChargesProperty as EP inner join ExtraCharges as E on E.id = EP.extraChargeId where EP.clientId = ? and EP.propId = ? and E.type = ?  order by EP.id desc limit 1";
  const data = [clientId, propId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

extraChargeDB.getLinkedProperties = async ({
  extraChargeId,
  clientId,
}: extraChargesTypes & extraChargesPropertyTypes) => {
  const query =
    "Select P.id, P.name, 1 as isLinked from ExtraChargesProperty as EP inner join Properties as P on P.id = EP.propId where EP.extraChargeId = ? and EP.clientId =? order by EP.id desc";
  const data = [extraChargeId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

extraChargeDB.getLinkedChargesByPropIdandType = async ({
  propId,
  clientId,
  type,
}: extraChargesTypes & extraChargesPropertyTypes) => {
  const query =
    "Select P.id, P.name, 1 as isLinked from ExtraChargesProperty as EP inner join Properties as P on P.id = EP.propId inner join ExtraCharges as E on E.id = EP.extraChargeId where EP.propId = ? and EP.clientId =? and E.type = ?  order by EP.id desc";
  const data = [propId, clientId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

extraChargeDB.create = async ({
  type,
  repetitionType,
  amount,
  name,
  clientId,
}: extraChargesTypes) => {
  const query =
    "Insert into ExtraCharges (type, repetitionType, amount,name, clientId) values (?,?,?,?,?)";
  const data = [type, repetitionType, amount, name, clientId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

extraChargeDB.update = async ({
  repetitionType,
  amount,
  clientId,
  id,
}: extraChargesTypes) => {
  const query =
    "Update ExtraCharges set repetitionType = ?, amount = ?, clientId = ? where id = ?";
  const data = [repetitionType, amount, clientId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

extraChargeDB.addProperty = async ({
  clientId,
  propId,
  extraChargeId,
}: extraChargesPropertyTypes) => {
  const query =
    "Insert into ExtraChargesProperty (clientId, propId, extraChargeId) values (?,?,?)";
  const data = [clientId, propId, extraChargeId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

extraChargeDB.removeProperty = async ({
  clientId,
  propId,
  extraChargeId,
}: extraChargesPropertyTypes) => {
  const query =
    "Delete from ExtraChargesProperty where clientId = ? and propId = ? and extraChargeId = ?";
  const data = [clientId, propId, extraChargeId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

extraChargeDB.deleteFromProperties = async ({
  extraChargeId,
}: extraChargesPropertyTypes) => {
  const query =
    "Delete from ExtraChargesProperty where extraChargeId = ?";
  const data = [extraChargeId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

extraChargeDB.delete = async ({
  id,
}: extraChargesTypes) => {
  const query =
    "Delete from ExtraCharges where id = ?";
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

extraChargeDB.getByPropAndTypeX = async ({
  clientId,
  propId,
  type,
}: extraChargesPropertyTypes & extraChargesTypes) => {
  const query =
    "Select E.amount, E.type, E.clientId, E.repetitionType, E.name, EP.propId from ExtraChargesProperty as EP inner join ExtraCharges as E on E.id = EP.extraChargeId where EP.clientId = ? and EP.propId = ? and E.type = ?  order by EP.id desc limit 1";
  const data = [clientId, propId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

extraChargeDB.addUtilityBill = async ({
  amount,
  roomFlatId,
  clientId,
  propId,
  startDate,
  endDate,
  noOfTenant,
  type,
}: utilityBillTypes) => {
  const query =
    `Insert into UtilityBill (clientId, propId, roomFlatId, amount, type, noOfTenant, startDate, endDate) values (?, ?, ?, ?, ?, ?, ?, ?)`

  const data = [
    clientId, 
    propId, 
    roomFlatId, 
    amount, 
    type, 
    noOfTenant, 
    startDate, 
    endDate
  ];

  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

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

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

extraChargeDB.getPreviousBill = async ({ propId, type }: utilityBillTypes) => {
  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.docs FROM Flats f INNER JOIN UtilityBill eb ON f.id = eb.roomFlatId WHERE f.propId = ? and eb.type = ? order by eb.id desc";
  const data = [propId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

export default extraChargeDB;