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

const moveOutDuesDB: any = {};


moveOutDuesDB.create = async ({
  moveOutId,
  tenantId,
  roomId,
  propId,
  clientId,
  amount,
  dueDate,
  type
}: any) => {
  const query =
    "Insert into MoveOutDues (moveOutId, tenantId, roomId, propId, clientId, amount, dueDate, type) values (?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [moveOutId, tenantId, roomId, propId, clientId, amount, dueDate, type];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDuesDB.removeAllByTenantId = async ({ tenantId }: any) => {
  const query = "Delete from MoveOutDues where tenantId = ?";
  const data = [tenantId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

moveOutDuesDB.removeAllByClientIdAndTenantId = async ({ tenantId, clientId }: any) => {
  const query = "Delete from MoveOutDues where tenantId = ? and clientId = ?";
  const data = [tenantId, clientId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

moveOutDuesDB.getByTenantId = async ({ tenantId }: any) => {
  const query = "select MD.id, MD.discount, MD.ledgerReferenceId, MD.remarks, MD.title, MD.moveOutId, MD.tenantId, MD.roomId, MD.propId, MD.clientId, MD.balance, MD.amount, MD.dueDate, MD.type, MD.rentStartDate, MD.rentEndDate, MD.createdAt, MD.updatedAt, T.name as tenantName, R.roomNum from MoveOutDues as MD left join Tenants as T on MD.tenantId = T.id left join Rooms as R on MD.roomId = R.id where MD.tenantId = ? order by id desc";
  const data = [tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDuesDB.getByTenantIdAndClientId = async ({ tenantId, clientId }: moveOutDuesTypes) => {
  const query = 
    "Select  MD.id, MD.title, MD.tallyBillRef, MD.discount, MD.ledgerReferenceId, P.id as propId, P.name as propertyName, MD.remarks, MD.rentStartDate, MD.rentEndDate, MD.moveOutId, MD.tenantId, MD.propId, MD.roomId, MD.clientId, MD.amount, MD.balance, MD.dueDate, MD.type, MD.createdAt, MD.updatedAt, T.name as tenantName, R.roomNum from MoveOutDues as MD left join Tenants as T on MD.tenantId = T.id left join Rooms as R on MD.roomId = R.id INNER Join Properties as P on MD.propId = P.id where MD.tenantId = ? and MD.clientId = ? order by MD.type ";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};


moveOutDuesDB.getTotalDuesByTenantId = async ({ tenantId, propId }: any) => {
  const query =
    "Select SUM(balance) as totalDues from MoveOutDues where tenantId = ? and propId = ?";
  const data = [tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDuesDB.getTotalRentDuesByTenantId = async ({ tenantId, propId }: any) => {
  const query =
    "Select SUM(balance) as totalDues from MoveOutDues where tenantId = ? and propId = ? and type = ?";
  const data = [tenantId, propId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDuesDB.add = async ({
  moveOutId,
  tenantId,
  roomId,
  propId,
  clientId,
  amount,
  balance,
  ledgerReferenceId,
  dueDate,
  type,
  rentStartDate,
  rentEndDate,
  description,
  title,
  discount = 0,
}: moveOutDuesTypes) => {
  const query =
    "Insert into MoveOutDues (moveOutId, tenantId, roomId, propId, clientId, amount, balance, ledgerReferenceId, dueDate, type, rentStartDate, rentEndDate, description, title, discount) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    moveOutId,
    tenantId,
    roomId,
    propId,
    clientId,
    amount,
    balance,
    ledgerReferenceId,
    dueDate,
    type,
    rentStartDate,
    rentEndDate,
    description,
    title,
    discount,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

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

moveOutDuesDB.getByIdAndClientId = async ({ id, clientId }: moveOutDuesTypes) => {
  const query = "Select * from MoveOutDues where id = ? and clientId = ?";
  const data = [id, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDuesDB.getByIdForTransaction = async ({ id }: moveOutDuesTypes) => {
  const query =
    "Select  MD.id, MD.ledgerReferenceId, MD.tallyBillRef, MD.description, MD.discount, MD.rentStartDate, MD.rentEndDate, MD.moveOutId, MD.tenantId, MD.propId, MD.roomId, MD.clientId, MD.amount, MD.balance, MD.dueDate, MD.type, MD.title, MD.createdAt, MD.updatedAt from MoveOutDues as MD where MD.id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDuesDB.removeDue = async ({ id }: moveOutDuesTypes) => {
  const query = "Delete from MoveOutDues where id = ?";
  const data = [id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

moveOutDuesDB.updateBalance = async ({ id, balance }: moveOutDuesTypes) => {
  const query = "Update MoveOutDues set balance = ? where id = ?";
  const data = [balance, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

moveOutDuesDB.updateAmount = async ({ amount, id }: moveOutDuesTypes) => {
  const query = "Update MoveOutDues set amount = ? where id = ?";
  const data = [amount, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

moveOutDuesDB.getByTenantIdAndLedgerReferenceId = async ({
  tenantId,
  ledgerReferenceId,
}: moveOutDuesTypes) => {
  const query =
    "Select  MD.id, MD.ledgerReferenceId, MD.tallyStatus, MD.tallyBillRef, MD.rentStartDate, MD.rentEndDate, MD.moveOutId, MD.tenantId, MD.propId, MD.roomId, MD.clientId, MD.amount, MD.balance, MD.dueDate, MD.type, MD.title, MD.createdAt, MD.updatedAt from MoveOutDues as MD where MD.tenantId = ? and MD.ledgerReferenceId = ? order by MD.type ";
  const data = [tenantId, ledgerReferenceId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDuesDB.getByTenantIdAndPropId = async ({ tenantId, propId }: moveOutDuesTypes) => {
  const query = 
    "Select  MD.id, MD.title, MD.discount, MD.ledgerReferenceId, MD.rentStartDate, MD.rentEndDate, MD.moveOutId, MD.tenantId, MD.propId, MD.roomId, MD.clientId, MD.amount, MD.balance, MD.dueDate, MD.type, MD.createdAt, MD.updatedAt from MoveOutDues as MD where MD.tenantId = ? and MD.propId = ? order by MD.type ";
  const data = [tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDuesDB.getAllDues = async ({ ids }: any) => {
  const query = `Select * from MoveOutDues where id in (${ids}) order by type`;
  const [rows] = await DB.execute<RowDataPacket[]>(query);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDuesDB.getTenantAllDues = async ({ tenantIds, clientId }: any) => {
  const query = `Select * from MoveOutDues where clientId=? and tenantId in (${tenantIds}) order by type`;
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDuesDB.updateRemarks = async ({
  tenantId,
  clientId,
  remarks
}: moveOutDuesTypes) => {
  const query =
    `Update MoveOutDues set remarks = ? where tenantId = ? and clientId = ?;`;
  const data = [
    remarks,
    tenantId,
    clientId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

moveOutDuesDB.addWithStartEndDate = async ({
  tenantId,
  amount,
  moveOutId,
  roomId,
  propId,
  clientId,
  rentStartDate,
  rentEndDate,
  dueDate,
  type,
  balance,
  ledgerReferenceId,
}: moveOutDuesTypes) => {
  const query =
    "Insert into MoveOutDues (tenantId, amount, moveOutId, roomId, propId, clientId, dueDate, type,rentStartDate, rentEndDate, balance , ledgerReferenceId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    amount,
    moveOutId,
    roomId,
    propId,
    clientId,
    dueDate,
    type,
    rentStartDate,
    rentEndDate,
    balance,
    ledgerReferenceId,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

moveOutDuesDB.updateTallyStatus = async ({ id, tallyStatus }: moveOutDuesTypes) => {
  const query = "update MoveOutDues set tallyStatus = ? where id = ? limit 1";
  const data = [tallyStatus, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDuesDB.updateTallyInfo = async ({ id, tallyBillRef, tallyGuid }: moveOutDuesTypes) => {
  const query = "update MoveOutDues set tallyBillRef = ?, tallyGuid = ? where id = ? limit 1";
  const data = [tallyBillRef, tallyGuid, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDuesDB.syncDuesTally = async ({ clientId }: moveOutDuesTypes) => {
  const query = "update MoveOutDues set tallyStatus = ? where clientId = ? and tallyStatus = ?";
  const data = [
    CONSTANTS.TALLY_STATUS.RETRY,
    clientId,
    CONSTANTS.TALLY_STATUS.FAILED,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

export default moveOutDuesDB;