import DB from "../config/database/db";
import { ResultSetHeader, RowDataPacket } from "mysql2";
import staffBalanceTypes from "../schemas/staffBalance.schema";

const staffBalanceDB: any = {};

staffBalanceDB.add = async ({
  staffId,
  totalCollected,
  totalGivenToOwner,
  totalExpense,
}: staffBalanceTypes) => {
  const query =
    "Insert into StaffBalances (staffId, totalCollected, totalGivenToOwner, totalExpense) values (?,?,?,?)";
  const data = [
    staffId,
    totalCollected,
    totalGivenToOwner,
    totalExpense,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffBalanceDB.getByStaffId = async ({ staffId }: staffBalanceTypes) => {
  const query = "Select * from StaffBalances where staffId = ?";
  const data = [staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data); 
  if (rows?.length > 0) return rows[0];
  else return false;
};

staffBalanceDB.updateTotalCollected = async ({
    staffId,
    amount,
  }: staffBalanceTypes & { amount: number }) => {
    const query =
      "Update StaffBalances set totalCollected = totalCollected + ? where staffId = ?";
    const data = [amount, staffId];
    const [rows] = await DB.query<ResultSetHeader>(query, data);
    return rows.insertId;
};

staffBalanceDB.updateTotalGivenToOwner = async ({
    staffId,
    amount,
  }: staffBalanceTypes & { amount: number }) => {
    const query =
      "Update StaffBalances set totalGivenToOwner = totalGivenToOwner + ? where staffId = ?";
    const data = [amount, staffId];
    const [rows] = await DB.query<ResultSetHeader>(query, data);
    return rows.insertId;
};
  
staffBalanceDB.updateTotalExpense = async ({
    staffId,
    amount,
  }: staffBalanceTypes & { amount: number }) => {
    const query =
      "Update StaffBalances set totalExpense = totalExpense + ? where staffId = ?";
    const data = [amount, staffId];
    const [rows] = await DB.query<ResultSetHeader>(query, data);
    return rows.insertId;
};

staffBalanceDB.setTotalExpense = async ({
    staffId,
    amount,
  }: staffBalanceTypes & { amount: number }) => {
    const query =
      "Update StaffBalances set totalExpense = ? where staffId = ?";
    const data = [amount, staffId];
    const [rows] = await DB.query<ResultSetHeader>(query, data);
    return rows.insertId;
};

staffBalanceDB.getStaffSummaryData = async ({
    staffId,
  }: staffBalanceTypes ) => {
    const query =
      "Select totalCollected as collection, (totalCollected - totalGivenToOwner) as inhand, totalExpense as expenses, amountReimbursed as reimbursements from StaffBalances where staffId = ?;";
    const data = [staffId];
    const [rows] = await DB.execute<RowDataPacket[]>(query, data);
    if (rows?.length > 0) return rows[0];
    else return false;
};

staffBalanceDB.updateReimbursedAmount = async ({
  staffId,
  amount
}: staffBalanceTypes & {amount: number}) => {
  const query = "Update StaffBalances set amountReimbursed = amountReimbursed + ? where staffId = ?";
  const data = [amount, staffId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

export default staffBalanceDB;