import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import CONSTANTS from "../config/constants";
import log from "../config/log";
import moment from "moment";
import hiddenDuesTypes from "../schemas/hiddenDues.schema";

const hiddenDuesDB: any = {};

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

hiddenDuesDB.add = async ({
  tenantId,
  amount,
  occupancyId,
  roomId,
  propId,
  clientId,
  rentStartDate,
  rentEndDate,
  dueDate,
  type,
  balance,
  ledgerReferenceId,
  description,
  title,
  discount,
  remarks,
}: hiddenDuesTypes) => {
  const query =
    "Insert into HiddenDues (tenantId, amount, occupancyId, roomId, propId, clientId, dueDate, type,rentStartDate, rentEndDate, balance , ledgerReferenceId, description, title, discount, remarks) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    amount,
    occupancyId,
    roomId,
    propId,
    clientId,
    dueDate,
    type,
    rentStartDate,
    rentEndDate,
    balance,
    ledgerReferenceId,
    description,
    title,
    discount,
    remarks,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

hiddenDuesDB.removeById = async ({ id }: hiddenDuesTypes) => {
  const query = "Delete from HiddenDues where id = ?";
  const data = [id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

hiddenDuesDB.removeByTenantIdAndClientIdAndOccupancyId = async ({
  tenantId,
  clientId,
  occupancyId,
}: hiddenDuesTypes) => {
  const query = "Delete from HiddenDues where clientId = ? and tenantId = ? and occupancyId = ?";
  const data = [clientId, tenantId, occupancyId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

hiddenDuesDB.removeByTenantIdAndClientId = async ({
  tenantId,
  clientId,
}: hiddenDuesTypes) => {
  const query = "Delete from HiddenDues where clientId = ? and tenantId = ?";
  const data = [clientId, tenantId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

hiddenDuesDB.updateOccupancyByTenantIdAndClientId = async ({
  tenantId,
  clientId,
  occupancyId,
}: hiddenDuesTypes) => {
  const query = "Update HiddenDues set occupancyId = ? where clientId = ? and tenantId = ?";
  const data = [occupancyId, clientId, tenantId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

hiddenDuesDB.getByClientId = async ({ clientId }: hiddenDuesTypes) => {
  const query =
    "Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from HiddenDues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? order by D.id desc";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

hiddenDuesDB.getTotalByClientId = async ({ clientId }: hiddenDuesTypes) => {
  const query =
    "Select sum(balance) as total from HiddenDues where clientId = ?";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total;
  else return false;
};

hiddenDuesDB.getByClientIdForStaff = async ({
  clientId,
  propertiesIds,
}: hiddenDuesTypes & { propertiesIds: string }) => {
  const query = `Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from HiddenDues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) order by D.id desc`;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

hiddenDuesDB.getTotalByClientIdForStaff = async ({
  clientId,
  propertiesIds,
}: hiddenDuesTypes & { propertiesIds: string }) => {
  const query = `Select sum(balance) as total from HiddenDues where clientId = ? and propId in (${propertiesIds})`;
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total;
  else return false;
};

hiddenDuesDB.getByPropId = async ({ clientId, propId }: hiddenDuesTypes) => {
  const query = `Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from HiddenDues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId = ? order by D.id desc`;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

hiddenDuesDB.getByLocationId = async ({ clientId, locationId }: hiddenDuesTypes & {locationId: any;}) => {
  const query = `Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from HiddenDues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and P.locationId = ? order by D.id desc`;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, locationId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

hiddenDuesDB.getByClientIdAndTenantId = async ({ clientId, tenantId }: hiddenDuesTypes) => {
  const query =
    "Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from HiddenDues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.tenantId = ? order by D.id desc";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

hiddenDuesDB.getTotalByClientIdAndTenantId = async ({ clientId, tenantId }: hiddenDuesTypes) => {
  const query =
    "Select sum(balance) as total from HiddenDues where clientId = ? and tenantId = ?";
  const data = [clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total;
  else return false;
};

export default hiddenDuesDB;