import mysql,{ ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import walletTypes from "../schemas/wallet.schema";
import CONSTANTS from "../config/constants";
import walletTransfersTypes from "../schemas/walletTransfers.schema";
import log from "../config/log";

const walletDB: any = {};

walletDB.create = async ({
  clientId,
  staffId,
  amount,
  balance,
  subWalletId,
  maxTransLimit,
  dailyPayoutLimit,
}: walletTypes) => {
  const query =
    "Insert into Wallet (clientId, staffId, amount, balance, subWalletId, maxTransLimit, dailyPayoutLimit) values (?, ?, ?, ?, ?, ?, ?)";
  const data = [clientId, staffId, amount, balance, subWalletId, maxTransLimit, dailyPayoutLimit];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

walletDB.updateWalletLimits = async ({ clientId, staffId, maxTransLimit, dailyPayoutLimit }: walletTypes) => {
  let query =
    "Update Wallet set maxTransLimit = ?, dailyPayoutLimit = ? where clientId = ?";
  const data = [maxTransLimit, dailyPayoutLimit, clientId];

  if (staffId) {
    query += ` and staffId = ?`
    data.push(staffId);
  } else {
    query += ` and staffId is null`
  }

  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows.affectedRows > 0) return true;
  else return false;
};

walletDB.recordTransfer = async ({
  clientId,
  transferBy,
  transferByUserType,
  transferTo,
  transferToUserType,
  amount,
}: walletTransfersTypes) => {
  const query =
    "Insert into WalletTransfers (clientId, transferBy, transferByUserType, transferTo, transferToUserType, amount) values (?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    transferBy,
    transferByUserType,
    transferTo,
    transferToUserType,
    amount,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

walletDB.updateAmount = async ({ clientId, staffId, amount }: walletTypes) => {
  let query =
    "Update Wallet set amount = ? where clientId = ?";
  const data = [amount, clientId];

  if (staffId) {
    query += ` and staffId = ?`
    data.push(staffId);
  } else {
    query += ` and staffId is null`
  }

  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows.affectedRows > 0) return true;
  else return false;
};

walletDB.updateBalance = async ({
  clientId,
  staffId,
  balance,
}: walletTypes) => {
  let query =
    "Update Wallet set balance = ? where clientId = ?";
  const data = [balance, clientId];

  if (staffId) {
    query += ` and staffId = ?`
    data.push(staffId);
  } else {
    query += ` and staffId is null`
  }

  //log.info(`Query [${mysql.format(query, data)}]`);

  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows.affectedRows > 0) return true;
  else return false;
};

walletDB.getByClientIdAndStaffId = async ({
  clientId,
  staffId,
}: walletTypes) => {
  const query = "Select * from Wallet where staffId = ? and clientId = ?";
  const data = [staffId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

walletDB.getByClientId = async ({ clientId }: walletTypes) => {
  const query = "Select * from Wallet where clientId = ? and staffId is null";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

walletDB.getAllByClientId = async ({ 
  clientId,
  pageNum,
  limit,
 }: walletTypes & { pageNum: number | null; limit: number; }) => {
  let query =
    "Select W.*, if(W.staffId is null, (select name from Clients where id = W.clientId), (select name from Staffs where id = W.staffId)) as name, if(W.staffId is null, (select mobile from Clients where id = W.clientId), (select mobile from Staffs where id = W.staffId)) as mobile, if(W.staffId is null, (select count(*) from PayoutTransactions where clientId = W.clientId and doneById = W.clientId and doneByType = ?), (select count(*) from PayoutTransactions where clientId = W.clientId and doneById = W.staffId and doneByType = ?)) as totalPayouts from Wallet as W where W.clientId = ? order by W.id desc ";
  const data: any[] = [
    CONSTANTS.USER_TYPE.CLIENT,
    CONSTANTS.USER_TYPE.STAFF,
    clientId,
  ];

  if (pageNum && Number(pageNum) > 0) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += ` limit ?, ?`;
    data.push(`${offset}`);
    data.push(`${limit}`);
  }

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

walletDB.getBySubWalletId = async ({ subWalletId }: walletTypes) => {
  const query = "Select * from Wallet where subWalletId = ?";
  const data = [subWalletId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

walletDB.getStaffTransfers = async ({ 
  clientId, 
  staffId, 
  pageNum, 
  limit 
}: walletTypes & { pageNum: number | null; limit: number; }) => {
  let query =
    "Select WT.*, CASE WHEN WT.transferToUserType = ? THEN (select name from Staffs where id = WT.transferTo) WHEN WT.transferToUserType = ? THEN (select name from Clients where id = WT.transferTo) END as transferToName, if(WT.transferByUserType = ?, (select name from Clients where id = WT.transferBy), (select name from Staffs where id = WT.transferBy)) as transferByName from WalletTransfers as WT where WT.clientId = ? and (WT.transferBy = ? or WT.transferTo = ?) order by id desc";
  const data: any = [
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.CLIENT,
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
    staffId,
    staffId,
  ];

  if (pageNum && Number(pageNum) > 0) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += ` limit ?, ?`;
    data.push(`${offset}`);
    data.push(`${limit}`);
  }
  
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

walletDB.getTransfersByClientId = async ({
  clientId,
  startDate,
  endDate,
}: {
  clientId: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select WT.*, CASE WHEN WT.transferToUserType = ? THEN (select name from Staffs where id = WT.transferTo) WHEN WT.transferToUserType = ? THEN (select name from Clients where id = WT.transferTo) END as transferToName, if(WT.transferByUserType = ?, (select name from Clients where id = WT.transferBy), (select name from Staffs where id = WT.transferBy)) as transferByName from WalletTransfers as WT where WT.clientId = ? and DATE(WT.transferDate) BETWEEN ? AND ?";
  const data = [
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.CLIENT,
    CONSTANTS.USER_TYPE.CLIENT,
    CONSTANTS.USER_TYPE.STAFF,
    clientId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

walletDB.getStaffIdWithoutWallet = async ({ clientId }: walletTypes) => {
  const query =
    "Select id, name, mobile from Staffs where id not in (Select staffId from Wallet where clientId = ? and staffId is not null) and clientId = ? and status = ?";
  const data = [
    clientId, 
    clientId,
    CONSTANTS.STAFF_STATUS.ACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

walletDB.getWalletSummaryForStaff = async ({
  clientId,
  staffId,
}: {
  clientId: number;
  staffId: number;
}) => {
  //const query = "SELECT COALESCE(W.balance, 0) AS currentBalance,COALESCE(SUM(WT.amount), 0) AS totalTransfered FROM Wallet W LEFT JOIN WalletTransfers WT ON WT.clientId = W.clientId AND WT.transferTo = W.staffId AND WT.transferToUserType = ? WHERE W.clientId = ? AND W.staffId = ?";
  const query = "SELECT COALESCE(W.balance, 0) AS currentBalance, COALESCE((SELECT SUM(WT.amount) FROM WalletTransfers WT WHERE WT.clientId = W.clientId AND WT.transferTo = W.staffId AND WT.transferToUserType = ?), 0) AS totalTransfered FROM Wallet W WHERE W.clientId = ? AND W.staffId = ?";

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

walletDB.updateStatus = async ({
  id,
  status,
}: walletTypes) => {
  const query =
    "Update Wallet set status = ? where id = ?";
  const data = [status, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows.affectedRows > 0) return true;
  else return false;
};


export default walletDB;