import DB from "../config/database/db";
import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import staffCommissionTypes from "../schemas/staffCommission.schema";
import log from "../config/log";

const staffCommissionDB: any = {};

staffCommissionDB.create = async ({
  clientId,
  propId,
  staffId,
  tenantId,
  occupancyId,
  amount,
  balance,
  paidDate,
  isPaid,
}: staffCommissionTypes) => {
  const query = `INSERT INTO StaffCommission (clientId, propId, staffId, tenantId, occupancyId, amount, balance, paidDate, isPaid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [clientId, propId, staffId, tenantId, occupancyId, amount, balance, paidDate, isPaid];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffCommissionDB.getById = async ({ id }: staffCommissionTypes) => {
  const query = `SELECT * FROM StaffCommission WHERE id = ?`;
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

staffCommissionDB.list = async ({ 
  clientId, 
  staffFilters,
  propFilters, 
  isPaid,
  startDate,
  endDate,
  pageNum, 
  limit,
  searchValue,
}: staffCommissionTypes & { pageNum: number, limit: number, staffFilters: any, propFilters: any; startDate: string; endDate: string; searchValue: string; }) => {
  let query = `SELECT SC.*, S.name as staffName, S.role as staffRole, S.mobile as staffMobile, P.name as propName, T.name as tenantName from StaffCommission as SC join Staffs as S on SC.staffId = S.id join Properties as P on P.id = SC.propId join Tenants as T on T.id = SC.tenantId WHERE SC.clientId = ? AND SC.isPaid = 0`;

  let data: any = [
    clientId,
  ];

  if (Number(isPaid) === 1) {
    query = `Select SC.id, SC.clientId, SC.staffId, SC.propId, SC.tenantId, SC.occupancyId, SC.balance, E.id as expenseId, E.amount, E.paidTo, E.paidToUserType, E.paidBy, E.paidByUserType, E.repetitionType, E.noOfMonths, E.paidDate, E.paymentMethod, E.type, E.recurringExpenseId, E.description, E.supportDoc, 1 as isPaid, S.name as staffName, S.role as staffRole, S.mobile as staffMobile, P.name as propName, T.name as tenantName from StaffCommission as SC join Staffs as S on SC.staffId = S.id join Properties as P on P.id = SC.propId join Tenants as T on T.id = SC.tenantId join Expenses as E on E.recurringExpenseId = SC.id WHERE SC.clientId = ? and E.type = ? `;

    data = [
      clientId,
      54, //Commission type
    ];
  }

  const offset = (pageNum - 1) * limit;

  if (staffFilters && staffFilters.length > 0) {
    query += ` AND SC.staffId IN (${staffFilters.map(() => '?').join(',')})`;
    data.push(...staffFilters);
  } 

  if (startDate && endDate) {
    if (Number(isPaid) === 1) {
      query += ` AND DATE(E.paidDate) BETWEEN ? and ?`
      data.push(startDate);
      data.push(endDate);
    } else {
      query += ` AND DATE(SC.createdAt) BETWEEN ? and ?`;
      data.push(startDate);
      data.push(endDate);
    }
  }

  if (propFilters && propFilters.length > 0) {
    query += ` AND SC.propId IN (${propFilters.map(() => '?').join(',')})`;
    data.push(...propFilters);
  }

  if (searchValue && searchValue.trim() !== "" && searchValue !== 'undefined') {
    query += ` AND (S.name like ? OR S.mobile like ?)`;
    data.push(`%${searchValue}%`);
    data.push(`%${searchValue}%`);
  }

  query += ` ORDER BY SC.id DESC limit ?, ?`;
  data.push(`${offset}`);
  data.push(`${limit}`);

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffCommissionDB.getAmountByClientIdAndFilters = async ({ 
  clientId, 
  staffFilters,
  propFilters, 
  isPaid,
  startDate,
  endDate,
  searchValue,
}: staffCommissionTypes & { staffFilters: any, propFilters: any; startDate: string; endDate: string; searchValue: string; }) => {
  let query = `SELECT SUM(SC.balance) as total from StaffCommission as SC join Staffs as S on SC.staffId = S.id join Properties as P on P.id = SC.propId WHERE SC.clientId = ? AND SC.isPaid = 0`;

  let data: any = [
    clientId,
  ];

  if (Number(isPaid) === 1) {
    query = `Select SUM(E.amount) as total from StaffCommission as SC join Staffs as S on SC.staffId = S.id join Properties as P on P.id = SC.propId join Expenses as E on E.recurringExpenseId = SC.id WHERE SC.clientId = ? and E.type = ? `;

    data = [
      clientId,
      54, //Commission type
    ];
  }

  if (staffFilters && staffFilters.length > 0) {
    query += ` AND SC.staffId IN (${staffFilters.map(() => '?').join(',')})`;
    data.push(...staffFilters);
  } 

  if (startDate && endDate) {
    if (Number(isPaid) === 1) {
      query += ` AND DATE(E.paidDate) BETWEEN ? and ?`
      data.push(startDate);
      data.push(endDate);
    } else {
      query += ` AND DATE(SC.createdAt) BETWEEN ? and ?`;
      data.push(startDate);
      data.push(endDate);
    }
  }

  if (propFilters && propFilters.length > 0) {
    query += ` AND SC.propId IN (${propFilters.map(() => '?').join(',')})`;
    data.push(...propFilters);
  }

  if (searchValue && searchValue.trim() !== "" && searchValue !== 'undefined') {
    query += ` AND (S.name like ? OR S.mobile like ?)`;
    data.push(`%${searchValue}%`);
    data.push(`%${searchValue}%`);
  }

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total;
  else return 0;
};

staffCommissionDB.updateBalance = async ({
  id,
  balance,
}: staffCommissionTypes) => {
  const query = `UPDATE StaffCommission SET balance = ? WHERE id = ?`;
  const data = [balance, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffCommissionDB.updateAmount = async ({
  id,
  amount,
}: staffCommissionTypes) => {
  const query = `UPDATE StaffCommission SET amount = ? WHERE id = ?`;
  const data = [amount, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffCommissionDB.updateIsPaid = async ({
  id,
  isPaid,
  paidDate,
}: staffCommissionTypes) => {
  const query = `UPDATE StaffCommission SET isPaid = ?, paidDate = ? WHERE id = ?`;
  const data = [isPaid, paidDate, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffCommissionDB.updateStaffId = async ({
  id,
  staffId,
}: staffCommissionTypes) => {
  const query = `UPDATE StaffCommission SET staffId = ? WHERE id = ?`;
  const data = [staffId, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffCommissionDB.delete = async ({
  id,
}: staffCommissionTypes) => {
  const query = `Delete from StaffCommission WHERE id = ?`;
  const data = [id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

staffCommissionDB.getByClientIdAndTenantId = async ({
  clientId,
  tenantId,
  occupancyId,
}: staffCommissionTypes) => {
  const query = `Select SC.*, S.name as staffName, S.role as staffRole, S.mobile as staffMobile, P.name as propName from StaffCommission as SC join Staffs as S on SC.staffId = S.id join Properties as P on P.id = SC.propId where SC.clientId = ? and SC.tenantId = ? and SC.occupancyId = ?`

  const data = [
    clientId,
    tenantId,
    occupancyId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
}

export default staffCommissionDB;