import DB from "../config/database/db";
import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import payoutBeneficiariesTypes from "../schemas/payoutBeneficiaries.schema";
import CONSTANTS from "../config/constants";
import log from "../config/log";

const payoutBeneficiaryDB: any = {};

payoutBeneficiaryDB.getById = async ({
  id,
}: payoutBeneficiariesTypes) => {
  const query =
    "Select * from PayoutBeneficiaries where id = ?";

  const data = [id];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows[0];
  else return false;
};

payoutBeneficiaryDB.getByClientId = async ({
  clientId,
}: payoutBeneficiariesTypes) => {
  const query = `
    select id, name, holderName as accountName, accountNum as accountNumber, ifsc, cfBeneficiaryId, mobile, status, createdAt  from PayoutBeneficiaries where clientId =? order by createdAt desc
  `;

  const data = [clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return false;
};

// payoutBeneficiaryDB.getByClientIdForDropDown = async ({
//   clientId,
// }: payoutBeneficiariesTypes) => {
//   const query = `
//     select id, name, cfBeneficiaryId, mobile, status, accountNum, upiId, ifsc, createdAt, userId, userType  from PayoutBeneficiaries where clientId =?  and status = ? order by createdAt desc
//   `;

//   const data = [clientId, CONSTANTS.BENEFICIARY_STATUS.VERIFIED];

//   const [rows] = await DB.execute<RowDataPacket[]>(
//     query,
//     data
//   );

//   if (rows?.length > 0) return rows;
//   else return false;
// };

payoutBeneficiaryDB.getByClientIdForDropDown = async ({
  clientId,
}: payoutBeneficiariesTypes) => {
  const query = `SELECT PB.id, PB.name, PB.cfBeneficiaryId, PB.mobile, PB.status, PB.accountNum, PB.upiId, PB.ifsc, PB.createdAt, PB.userId, PB.userType, PT.lastPaidAmount, PT.lastPaidDate FROM PayoutBeneficiaries AS PB LEFT JOIN (SELECT payoutBeneficiaryId, SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN status = 3 THEN amount END ORDER BY createdAt DESC), ',', 1) AS lastPaidAmount, MAX(CASE WHEN status = 3 THEN createdAt END) AS lastPaidDate FROM PayoutTransactions WHERE clientId = ? GROUP BY payoutBeneficiaryId) AS PT ON PT.payoutBeneficiaryId = PB.id WHERE PB.clientId = ? AND PB.status = ? ORDER BY PB.createdAt DESC`;

  const data = [clientId, clientId, CONSTANTS.BENEFICIARY_STATUS.VERIFIED];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows;
  else return false;
};

payoutBeneficiaryDB.updateAddedByById = async ({
  id,
  addedBy,
  addedByType,
}: payoutBeneficiariesTypes) => {
  const query = `UPDATE PayoutBeneficiaries SET addedBy = ?, addedByType = ? WHERE id = ?`;
  const data = [addedBy, addedByType, id];
  
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  if (rows.affectedRows > 0) return true;
  else return false;
};

payoutBeneficiaryDB.getByAccountNumberIfsc = async ({
  clientId,
  accountNum,
  ifsc,
}: payoutBeneficiariesTypes) => {
  const query = `
    Select * 
    from PayoutBeneficiaries 
    where clientId = ?
    and accountNum = ? 
    and ifsc = ?
    and status NOT IN (${CONSTANTS.BENEFICIARY_STATUS.DELETED}, ${CONSTANTS.BENEFICIARY_STATUS.HIDDEN})
    order by id desc
  `;

  const data = [clientId, accountNum, ifsc];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return false;
};

payoutBeneficiaryDB.getByAccountNumberIfscForTenant = async ({
  clientId,
  accountNum,
  ifsc,
}: payoutBeneficiariesTypes) => {
  const query = `
    Select * 
    from PayoutBeneficiaries 
    where clientId = ?
    and accountNum = ? 
    and ifsc = ?
    and status NOT IN (${CONSTANTS.BENEFICIARY_STATUS.DELETED})
    and userType = ?
    order by id desc
  `;

  const data = [clientId, accountNum, ifsc, CONSTANTS.USER_TYPE.TENANT];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows[0];
  else return false;
};

payoutBeneficiaryDB.getByUserId = async ({
  userId,
}: payoutBeneficiariesTypes) => {
  const query = `
    Select * 
    from PayoutBeneficiaries 
    where userId = ?
    and status = 1
    order by id desc
  `;

  const data = [userId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return false;
};

payoutBeneficiaryDB.getByUserIdAndUserType = async ({
  userId,
  userType,
  clientId,
}: payoutBeneficiariesTypes) => {
  const query = `
    Select * 
    from PayoutBeneficiaries 
    where userId = ?
    and userType = ?
    and clientId = ?
    and status = 2
    order by id desc
  `;

  const data = [userId, userType, clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows[0];
  else return false;
};

payoutBeneficiaryDB.getAllByUserIdAndUserType = async ({
  userId,
  userType,
  clientId,
}: payoutBeneficiariesTypes) => {
  const query = `
    Select * 
    from PayoutBeneficiaries 
    where userId = ?
    and userType = ?
    and clientId = ?
    and status = 2
    order by id desc
  `;

  const data = [userId, userType, clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return false;
};


// payoutBeneficiaryDB.getAllByUserIdAndUserType = async ({
//   userId,
//   userType,
//   clientId,
// }: payoutBeneficiariesTypes) => {
//   const query = `
//     Select * 
//     from PayoutBeneficiaries 
//     where userId = ?
//     and userType = ?
//     and clientId = ?
//     and status = 2
//     order by id desc
//   `;

//   const data = [userId, userType, clientId];

//   const [rows] = await DB.execute<RowDataPacket[]>(
//     query,
//     data
//   );

//   if (rows?.length > 0) return rows;
//   else return false;
// };

payoutBeneficiaryDB.getAllByUserIdAndUserType = async ({
  userId,
  userType,
  clientId,
}: payoutBeneficiariesTypes) => {
  const query = `SELECT PB.*, PT.lastPaidAmount, PT.lastPaidDate FROM PayoutBeneficiaries AS PB LEFT JOIN (SELECT payoutBeneficiaryId, SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN status = 3 THEN amount END ORDER BY createdAt DESC), ',', 1) AS lastPaidAmount, MAX(CASE WHEN status = 3 THEN createdAt END) AS lastPaidDate FROM PayoutTransactions WHERE clientId = ? GROUP BY payoutBeneficiaryId) AS PT ON PT.payoutBeneficiaryId = PB.id WHERE PB.userId = ? AND PB.userType = ? AND PB.clientId = ? AND PB.status = 2 ORDER BY PB.id DESC`;

  const data = [clientId, userId, userType, clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows;
  else return false;
};

payoutBeneficiaryDB.add = async ({
  clientId,
  userId,
  userType,
  name,
  bankName,
  holderName,
  type,
  accountNum,
  ifsc,
  upiId,
  cfBeneficiaryId,
  mobile,
  status = 1,
}: payoutBeneficiariesTypes) => {
  const query = `
    INSERT INTO PayoutBeneficiaries (
      clientId,
      userId,
      userType,
      name,
      bankName,
      holderName,
      type,
      accountNum,
      ifsc,
      upiId,
      cfBeneficiaryId,
      status,
      mobile
    ) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const data = [
    clientId,
    userId,
    userType,
    name,
    bankName,
    holderName,
    type,
    accountNum,
    ifsc,
    upiId,
    cfBeneficiaryId,
    status,
    mobile,
  ];

  const [rows] = await DB.query<ResultSetHeader>(
    query,
    data
  );

  return rows.insertId;
};

payoutBeneficiaryDB.updateStatus = async ({
  id,
  status,
}: payoutBeneficiariesTypes) => {
  const query = `
    UPDATE PayoutBeneficiaries 
    SET status = ?
    WHERE id = ?
  `;

  const data = [status, id];

  const [rows] = await DB.query<ResultSetHeader>(
    query,
    data
  );

  if (rows.affectedRows > 0) return true;
  else return false;
};

payoutBeneficiaryDB.getByClientIdWithSearch = async ({
  clientId,
  searchVal,
  searchType,
  pageNum,
  limit,
}: payoutBeneficiariesTypes & { searchVal: string; searchType: number; pageNum:any; limit: any}) => {

  let searchQuery = "";
  const data: any[] = [clientId];

  // Search by Mobile = 1
  if (Number(searchType) === 1 && searchVal) {
    searchQuery = ` AND PB.mobile LIKE ? `;
    data.push(`%${searchVal}%`);
  }

  // Search by Name = 2
  if (Number(searchType) === 2 && searchVal) {
    searchQuery = ` AND PB.name LIKE ? `;
    data.push(`%${searchVal}%`);
  }
  
  // Search by Account = 3
  if (Number(searchType) === 3 && searchVal) {
    searchQuery = ` AND PB.accountNum LIKE ? `;
    data.push(`%${searchVal}%`);
  }

  let query = `
    SELECT
      PB.id,
      PB.userId,
      PB.userType,
      PB.name,
      PB.bankName,
      PB.holderName,
      PB.type,
      PB.accountNum,
      PB.ifsc,
      PB.upiId,
      PB.cfBeneficiaryId,
      PB.mobile,
      PB.status,
      PB.createdAt,
      PB.updatedAt,
      PB.addedBy, PB.addedByType,
      CASE WHEN PB.addedByType IS NULL OR PB.addedByType = 1 THEN C.name WHEN PB.addedByType = 3 THEN S.name ELSE NULL END AS addedByName,
      COALESCE(PT.totalAmountPaid, 0) AS totalAmountPaid,
      COALESCE(PT.totalTransactions, 0) AS totalTransactions,
      PT.lastPaidDate,
      PT.lastPaidAmount

    FROM PayoutBeneficiaries AS PB

    LEFT JOIN Clients AS C ON ((PB.addedByType IS NULL  AND PB.clientId = C.id) OR (PB.addedByType = 1 AND PB.addedBy = C.id)) LEFT JOIN Staffs AS S ON PB.addedByType = 3 AND PB.addedBy = S.id

    LEFT JOIN (
      SELECT
        payoutBeneficiaryId,

        SUM(
          CASE
            WHEN status = 3 THEN amount
            ELSE 0
          END
        ) AS totalAmountPaid,

        COUNT(
          CASE
            WHEN status = 3 THEN id
            ELSE NULL
          END
        ) AS totalTransactions,

        MAX(
          CASE
            WHEN status = 3 THEN createdAt
            ELSE NULL
          END
        ) AS lastPaidDate,

        SUBSTRING_INDEX(
            GROUP_CONCAT(
                CASE
                    WHEN status = 3 THEN amount
                END
                ORDER BY createdAt DESC
            ),
            ',',
            1
        ) AS lastPaidAmount

      FROM PayoutTransactions

      WHERE clientId = ?

      GROUP BY payoutBeneficiaryId
    ) AS PT
    ON PT.payoutBeneficiaryId = PB.id

    WHERE
      PB.clientId = ?
      AND PB.status NOT IN (${CONSTANTS.BENEFICIARY_STATUS.DELETED}, ${CONSTANTS.BENEFICIARY_STATUS.HIDDEN})
      ${searchQuery}

  `;
  data.unshift(clientId);

  query += ` ORDER BY PB.id DESC`;
  if(pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  return rows?.length > 0 ? rows : false;
};

payoutBeneficiaryDB.getByClientIdAppSearch = async ({
  clientId,
  searchVal,
  pageNum,
  limit,
}: payoutBeneficiariesTypes & { searchVal: string; searchType: number; pageNum:any; limit: any}) => {

  let searchQuery = "";
  const data: any[] = [clientId];

  
  searchQuery = ` AND (PB.name LIKE ? OR PB.mobile LIKE ? OR PB.accountNum LIKE ?) `;
  data.push(`%${searchVal}%`);
  data.push(`%${searchVal}%`);
  data.push(`%${searchVal}%`);

  let query = `
    SELECT
      PB.id,
      PB.userId,
      PB.userType,
      PB.name,
      PB.bankName,
      PB.holderName,
      PB.type,
      PB.accountNum,
      PB.ifsc,
      PB.upiId,
      PB.cfBeneficiaryId,
      PB.mobile,
      PB.status,
      PB.createdAt,
      PB.updatedAt,

      COALESCE(PT.totalAmountPaid, 0) AS totalAmountPaid,
      COALESCE(PT.totalTransactions, 0) AS totalTransactions,
      PT.lastPaidDate,
      PT.lastPaidAmount

    FROM PayoutBeneficiaries AS PB

    LEFT JOIN (
      SELECT
        payoutBeneficiaryId,

        SUM(
          CASE
            WHEN status = 3 THEN amount
            ELSE 0
          END
        ) AS totalAmountPaid,

        COUNT(
          CASE
            WHEN status = 3 THEN id
            ELSE NULL
          END
        ) AS totalTransactions,

        MAX(
          CASE
            WHEN status = 3 THEN createdAt
            ELSE NULL
          END
        ) AS lastPaidDate,

        SUBSTRING_INDEX(
            GROUP_CONCAT(
                CASE
                    WHEN status = 3 THEN amount
                END
                ORDER BY createdAt DESC
            ),
            ',',
            1
        ) AS lastPaidAmount

      FROM PayoutTransactions

      WHERE clientId = ?

      GROUP BY payoutBeneficiaryId
    ) AS PT
    ON PT.payoutBeneficiaryId = PB.id

    WHERE
      PB.clientId = ?
      AND PB.status NOT IN (${CONSTANTS.BENEFICIARY_STATUS.DELETED}, ${CONSTANTS.BENEFICIARY_STATUS.HIDDEN})
      ${searchQuery}

  `;
  data.unshift(clientId);

  query += ` ORDER BY PB.id DESC`;
  if(pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  return rows?.length > 0 ? rows : false;
};

payoutBeneficiaryDB.getByClientIdWithDate = async ({
  clientId,
  startDate,
  endDate,
  pageNum,
  limit
}: payoutBeneficiariesTypes & { startDate: string; endDate: string; pageNum: any; limit: number}) => {

  let query = `
    SELECT
      PB.id,
      PB.userId,
      PB.userType,
      PB.name,
      PB.bankName,
      PB.holderName,
      PB.type,
      PB.accountNum,
      PB.ifsc,
      PB.upiId,
      PB.cfBeneficiaryId,
      PB.mobile,
      PB.status,
      PB.createdAt,
      PB.updatedAt,
      PB.addedBy, PB.addedByType,
      CASE WHEN PB.addedByType IS NULL OR PB.addedByType = 1 THEN C.name WHEN PB.addedByType = 3 THEN S.name ELSE NULL END AS addedByName,
      COALESCE(PT.totalAmountPaid, 0) AS totalAmountPaid,
      COALESCE(PT.totalTransactions, 0) AS totalTransactions,
      PT.lastPaidDate,
      PT.lastPaidAmount

    FROM PayoutBeneficiaries AS PB

    LEFT JOIN Clients AS C ON ((PB.addedByType IS NULL  AND PB.clientId = C.id) OR (PB.addedByType = 1 AND PB.addedBy = C.id)) LEFT JOIN Staffs AS S ON PB.addedByType = 3 AND PB.addedBy = S.id

    LEFT JOIN (
      SELECT
        payoutBeneficiaryId,

        SUM(
          CASE
            WHEN status = 3 THEN amount
            ELSE 0
          END
        ) AS totalAmountPaid,

        COUNT(
          CASE
            WHEN status = 3 THEN id
            ELSE NULL
          END
        ) AS totalTransactions,

        MAX(
          CASE
            WHEN status = 3 THEN createdAt
            ELSE NULL
          END
        ) AS lastPaidDate,

        SUBSTRING_INDEX(
            GROUP_CONCAT(
                CASE
                    WHEN status = 3 THEN amount
                END
                ORDER BY createdAt DESC
            ),
            ',',
            1
        ) AS lastPaidAmount

      FROM PayoutTransactions

      WHERE clientId = ?

      GROUP BY payoutBeneficiaryId
    ) AS PT
    ON PT.payoutBeneficiaryId = PB.id

    WHERE
      PB.clientId = ?
      AND PB.status NOT IN (${CONSTANTS.BENEFICIARY_STATUS.DELETED}, ${CONSTANTS.BENEFICIARY_STATUS.HIDDEN})
      AND DATE(PB.createdAt) BETWEEN ? AND ?
      
  `;

  let data = [
    clientId,
    clientId,
    startDate,
    endDate,
  ];

  query += ` ORDER BY PB.id DESC`;
  if(pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }


  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  return rows?.length > 0 ? rows : false;
};

payoutBeneficiaryDB.getSummaryByClientId = async ({
  clientId,
}: payoutBeneficiariesTypes) => {

  const query = `
    SELECT

      -- Total Beneficiaries
      (
        SELECT COUNT(PB.id)
        FROM PayoutBeneficiaries PB
        WHERE PB.clientId = ? AND PB.status NOT IN (${CONSTANTS.BENEFICIARY_STATUS.DELETED}, ${CONSTANTS.BENEFICIARY_STATUS.HIDDEN})
      ) AS totalBeneficiaries,

      -- Added This Week
      (
        SELECT COUNT(PB.id)
        FROM PayoutBeneficiaries PB
        WHERE
          PB.clientId = ?
          AND PB.status NOT IN (${CONSTANTS.BENEFICIARY_STATUS.DELETED}, ${CONSTANTS.BENEFICIARY_STATUS.HIDDEN})
          AND YEARWEEK(PB.createdAt, 1) = YEARWEEK(CURDATE(), 1)
      ) AS addedThisWeek,

      -- Active This Month
      (
        SELECT COUNT(DISTINCT PT.payoutBeneficiaryId)
        FROM PayoutTransactions PT
        WHERE
          PT.clientId = ?
          AND PT.status = 1
          AND MONTH(PT.createdAt) = MONTH(CURDATE())
          AND YEAR(PT.createdAt) = YEAR(CURDATE())
      ) AS activeThisMonth,

      -- Rejected Beneficiaries
      (
        SELECT COUNT(PB.id)
        FROM PayoutBeneficiaries PB
        WHERE
          PB.clientId = ?
          AND PB.status = 0
      ) AS rejectedBeneficiaries;
  `;

  const data = [
    clientId,
    clientId,
    clientId,
    clientId,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  const summary = rows?.[0] || {};

  return {
    totalBeneficiaries:
      Number(summary.totalBeneficiaries || 0),

    addedThisWeek:
      Number(summary.addedThisWeek || 0),

    activeThisMonth:
      Number(summary.activeThisMonth || 0),

    rejectedBeneficiaries:
      Number(summary.rejectedBeneficiaries || 0),
  };
};

payoutBeneficiaryDB.getByCfBeneIdExceptClient = async ({
  cfBeneficiaryId,
  clientId,
}: payoutBeneficiariesTypes) => {
  const query = `
    SELECT *
    FROM PayoutBeneficiaries
    WHERE cfBeneficiaryId = ?
    AND clientId != ?
  `;

  const data = [cfBeneficiaryId, clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows[0];
  else return false;
};
export default payoutBeneficiaryDB;