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

const staffLedgerDB: any = {};

staffLedgerDB.add = async ({
  staffId,
  amount,
  type,
  mode,
  description,
  transactionId,
  paidDate,
}: staffLedgerTypes) => {
  const query =
    "Insert into StaffLedger (staffId, amount, type, mode, description, transactionId, paidDate) values (?,?,?,?,?,?,?)";
  const data = [staffId, amount, type, mode, description, transactionId, paidDate];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffLedgerDB.addExpense = async ({
  staffId,
  amount,
  type,
  mode,
  description,
  expenseId,
  paidDate,
  dueDate,
  commissionId = null,
}: staffLedgerTypes) => {
  const query =
    "Insert into StaffLedger (staffId, amount, type, mode, description, expenseId, paidDate, dueDate, commissionId) values (?,?,?,?,?,?,?,?,?)";
  const data = [staffId, amount, type, mode, description, expenseId, paidDate, dueDate, commissionId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

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

staffLedgerDB.getByStaffId = async ({ 
  staffId,
  pageNum,
  limit,
}: staffLedgerTypes & { pageNum: number, limit: number }) => {
  const offset = (pageNum - 1) * limit;
  const query = "Select sl.*, s.name from StaffLedger as sl  LEFT JOIN Staffs s ON sl.staffId = s.id WHERE sl.staffId = ? ORDER BY sl.id DESC LIMIT ?, ?;";
  const data = [
    staffId,
    `${offset}`,
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffLedgerDB.getByStaffIdWithoutSalary = async ({ 
  staffId,
  pageNum,
  limit,
}: staffLedgerTypes & { pageNum: number, limit: number }) => {
  const offset = (pageNum - 1) * limit;
  const query = "Select sl.*, s.name from StaffLedger as sl  LEFT JOIN Staffs s ON sl.staffId = s.id WHERE sl.staffId = ? and sl.type not in (?, ?) ORDER BY sl.id DESC LIMIT ?, ?;";
  const data = [
    staffId,
    CONSTANTS.STAFF_LEDGER_TYPES.SALARY,
    CONSTANTS.STAFF_LEDGER_TYPES.EXPENSE_RETURN,
    `${offset}`,
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffLedgerDB.getByStaffIdSalary = async ({ 
  staffId,
  pageNum,
  limit,
}: staffLedgerTypes & { pageNum: number, limit: number }) => {
  const offset = (pageNum - 1) * limit;
  const query = "Select sl.amount, sl.type, sl.mode, sl.description, sl.paidDate as createdAt, s.name from StaffLedger as sl  LEFT JOIN Staffs s ON sl.staffId = s.id WHERE sl.staffId = ? and sl.type in (?, ?) and amount > 0 ORDER BY sl.paidDate DESC LIMIT ?, ?;";
  const data = [
    staffId,
    CONSTANTS.STAFF_LEDGER_TYPES.SALARY,
    CONSTANTS.STAFF_LEDGER_TYPES.SALARY_ADVANCE,
    // CONSTANTS.STAFF_LEDGER_TYPES.EXPENSE_RETURN,
    `${offset}`,
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffLedgerDB.getRecentTransactions = async ({ staffId }: staffLedgerTypes) => {
  const query =
    "SELECT sl.*, t.name AS tenantName, s.name AS staffName, r.roomNum as roomNum, p.name as propertyName FROM StaffLedger sl LEFT JOIN Transactions tr ON sl.transactionId = tr.id LEFT JOIN Tenants t ON tr.tenantId = t.id LEFT JOIN Staffs s ON sl.staffId = s.id LEFT JOIN Occupancies as o ON o.tenantId = t.id LEFT JOIN Properties as p on o.propId = p.id LEFT JOIN Rooms as r on o.roomId = r.id WHERE sl.staffId = ? AND sl.type = 1 ORDER BY sl.createdAt DESC LIMIT 10;";
  const data = [staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffLedgerDB.getSearchResultByStaffId = async ({
  staffId,
  pageNum,
  limit,
  searchVal,
  staffLedgerType
}: staffLedgerTypes & { pageNum: number; limit: number; searchVal: string; staffLedgerType: any }) => {
  const offset = (pageNum - 1) * limit;
  const query =
    "SELECT sl.amount, sl.type, sl.mode, sl.description, sl.paidDate as createdAt, s.name AS staffName FROM StaffLedger sl LEFT JOIN Staffs s ON sl.staffId = s.id WHERE sl.staffId = ? AND (sl.description LIKE ? OR sl.type LIKE ?) ORDER BY sl.paidDate DESC LIMIT ?, ?;";
  const data = [
    staffId, 
    `%${searchVal}%`,
    `%${staffLedgerType}%`,
    `${offset}`,
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffLedgerDB.getTotalByStaffId = async ({ staffId }: staffLedgerTypes) => {
  const query =
    "SELECT SUM(CASE WHEN type = ? THEN amount ELSE 0 END) AS totalCollection, SUM(CASE WHEN type = ? THEN amount ELSE 0 END) AS totalExpense, SUM(CASE WHEN type = ? THEN amount ELSE 0 END) AS totalGivenToClient, SUM(CASE WHEN type IN (?, ?) THEN amount ELSE 0 END) AS totalReimbursed FROM StaffLedger WHERE staffId = ?;";
  const data = [
    CONSTANTS.STAFF_LEDGER_TYPES.CASH,
    CONSTANTS.STAFF_LEDGER_TYPES.EXPENSES,
    CONSTANTS.STAFF_LEDGER_TYPES.PAY_OUT,
    CONSTANTS.STAFF_LEDGER_TYPES.REIMBURSEMENT,
    CONSTANTS.STAFF_LEDGER_TYPES.EXPENSE_RETURN,
    staffId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

staffLedgerDB.deleteExpense = async ({
  staffId,
  expenseId,
}: staffLedgerTypes) => {

  const query = "delete from StaffLedger where staffId = ? and expenseId = ?";
  const data = [
    staffId,
    expenseId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  if (rows) return true;
  else return false;
}

staffLedgerDB.updateExpenseAmount = async ({
  staffId,
  expenseId,
  amount
}: staffLedgerTypes) => {

  const query = "update StaffLedger set amount = CASE WHEN amount < 0 THEN -? ELSE ? END where staffId = ? and expenseId = ?";
  const data = [
    amount,
    amount,
    staffId,
    expenseId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  if (rows) return true;
  else return false;
};

staffLedgerDB.updateAmount = async ({
  id,
  staffId,
  amount
}: staffLedgerTypes) => {

  const query = "update StaffLedger set amount = amount + ? where id = ? and staffId = ?";
  const data = [
    amount,
    id,
    staffId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  if (rows) return true;
  else return false;
};

staffLedgerDB.getTotalSalaryPaid = async ({ staffId }: staffLedgerTypes) => {
  const query = "SELECT SUM(ABS(amount)) AS total FROM StaffLedger WHERE staffId = ? AND type in (?, ?) and amount > 0";
  const data = [
    staffId, 
    CONSTANTS.STAFF_LEDGER_TYPES.SALARY,
    CONSTANTS.STAFF_LEDGER_TYPES.SALARY_ADVANCE,
    // CONSTANTS.STAFF_LEDGER_TYPES.EXPENSE_RETURN,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total;
  else return 0;
};

staffLedgerDB.getCurrentMonthSalaryPaid = async ({ staffId }: staffLedgerTypes) => {
  const query = "SELECT SUM(ABS(amount)) AS total FROM StaffLedger WHERE staffId = ? AND type in (?, ?) AND MONTH(paidDate) = MONTH(CURRENT_DATE()) AND YEAR(paidDate) = YEAR(CURRENT_DATE()) and amount > 0";
  const data = [
    staffId,
    CONSTANTS.STAFF_LEDGER_TYPES.SALARY,
    CONSTANTS.STAFF_LEDGER_TYPES.SALARY_ADVANCE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total;
  else return 0;
};

staffLedgerDB.getCurrentMonthReimbursmentPaid = async ({ staffId }: staffLedgerTypes) => {
  const query = "SELECT SUM(ABS(amount)) AS total FROM StaffLedger WHERE staffId = ? AND type in (?, ?) AND MONTH(paidDate) = MONTH(CURRENT_DATE()) AND YEAR(paidDate) = YEAR(CURRENT_DATE()) and amount > 0";
  const data = [
    staffId,
    CONSTANTS.STAFF_LEDGER_TYPES.EXPENSE_RETURN,
    CONSTANTS.STAFF_LEDGER_TYPES.REIMBURSEMENT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total;
  else return 0;
};

staffLedgerDB.getUnPaidByExpenseId = async ({ expenseId, staffId }: staffLedgerTypes) => {
  const query = "Select * from StaffLedger where expenseId = ? and staffId = ? order by id desc limit 1";
  const data = [
    expenseId,
    staffId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};

staffLedgerDB.getTotalBalanceByStaffId = async ({ staffId }: staffLedgerTypes) => {
  const query = "Select SUM(amount) as ledgerBalance from StaffLedger where staffId = ?";
  const data = [
    staffId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].ledgerBalance;
  else return 0;
};

staffLedgerDB.deleteLedgerByExpenseId = async ({ expenseId }: staffLedgerTypes) => {
  const query = "Delete from StaffLedger where expenseId = ?";
  const data = [
    expenseId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffLedgerDB.deleteLedgerByCommissionId = async ({ commissionId }: staffLedgerTypes) => {
  const query = "Delete from StaffLedger where commissionId = ?";
  const data = [
    commissionId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffLedgerDB.updateStaffByExpenseId = async ({ staffId, expenseId }: staffLedgerTypes) => {
  const query = "Update StaffLedger set staffId = ? where expenseId = ?";
  const data = [
    staffId,
    expenseId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffLedgerDB.updateStaffByCommissionId = async ({ staffId, commissionId }: staffLedgerTypes) => {
  const query = "Update StaffLedger set staffId = ? where commissionId = ?";
  const data = [
    staffId,
    commissionId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffLedgerDB.deleteByCommissionId = async ({ commissionId }: staffLedgerTypes) => {
  const query = "Delete from StaffLedger where commissionId = ?";
  const data = [
    commissionId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffLedgerDB.deleteByCommissionIdAndExpenseId = async ({ commissionId, expenseId }: staffLedgerTypes) => {
  const query = "Delete from StaffLedger where commissionId = ? and expenseId = ?";
  const data = [
    commissionId,
    expenseId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffLedgerDB.updateInitalAmount = async ({ commissionId, amount }: staffLedgerTypes) => {
  const query = "Update StaffLedger set amount = ? where commissionId = ? and amount < 0";
  const data = [
    amount,
    commissionId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffLedgerDB.getByStaffIdAndCommissionId = async ({ staffId, commissionId }: staffLedgerTypes) => {
  const query = "Select * from StaffLedger where staffId = ? and commissionId = ? order by id asc limit 1";
  const data = [
    staffId,
    commissionId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};

export default staffLedgerDB;