import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import leadTypes from "../schemas/lead.schema";
import CONSTANTS from "../config/constants";
import propertyLeadTypes from "../schemas/propertyLead.schema";
import leadNotesTypes from "../schemas/leadNotes.schema";
import leadStaffTypes from "../schemas/leadStaff.schema";
import log from "../config/log";

const leadDB: any = {};

leadDB.create = async ({
  clientId,
  name,
  propId,
  mobile,
  rentRange,
  roomType,
  gender,
  visitType,
  visitDateTime,
  remarks,
  source,
  status,
  email
}: leadTypes) => {
  const query =
    "Insert into Leads (clientId, name, propId, mobile, rentRange, roomType, gender, visitDateTime, remarks, visitType, source, status, email) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    name,
    propId,
    mobile,
    rentRange,
    roomType,
    gender,
    visitDateTime,
    remarks,
    visitType,
    source,
    status,
    email,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

leadDB.getList = async ({
  clientId,
  pageNum,
  limit,
}: leadTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select L.* , Properties.name as propertyName, concat(Properties.streetAddress, ', ',  Properties.address) as address from Leads as L JOIN Properties ON Properties.id = L.propId where L.clientId = ? order by L.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getListX = async ({
  clientId,
  pageNum,
  limit,
}: leadTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select L.* from Leads as L where L.status != 12 and L.clientId = ? order by L.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getListForStaff = async ({
  clientId,
  staffId,
  pageNum,
  limit,
}: leadTypes & leadStaffTypes & { pageNum: number; limit: number; propertiesIds: any }) => {
  const query =
    "Select L.* from Leads as L join LeadStaff as LS on LS.leadId = L.id where L.status != 12 and L.clientId = ? and LS.staffId = ? group by L.id order by L.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, staffId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getSearchResults = async ({
  clientId,
  pageNum,
  limit,
  searchVal,
}: leadTypes & { pageNum: number; limit: number; searchVal: string }) => {
  const query =
    "Select L.* , Properties.name as propertyName, concat(Properties.streetAddress, ', ',  Properties.address) as address from Leads as L JOIN Properties ON Properties.id = L.propId where L.clientId = ? and(L.name like ? || L.mobile like ?) order by L.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getSearchResultsX = async ({
  clientId,
  pageNum,
  limit,
  searchVal,
}: leadTypes & { pageNum: number; limit: number; searchVal: string }) => {
  const query =
    "Select L.* from Leads as L where L.clientId = ? and(L.name like ? || L.mobile like ?) order by L.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getSearchResultsForStaff = async ({
  clientId,
  propertiesIds,
  staffId,
  pageNum,
  limit,
  searchVal,
}: leadTypes & { pageNum: number; limit: number; searchVal: string; propertiesIds: any; staffId: number }) => {
  const query =
    "Select L.* from Leads as L JOIN PropertyLead as PL ON PL.leadId = L.id left join LeadStaff as LS on LS.leadId = L.id where L.clientId = ? and LS.staffId = ? and (L.name like ? || L.mobile like ?) and PL.propId in ("+`${propertiesIds}`+") group by L.id order by L.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    staffId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.updateLead = async ({
  name,
  propId,
  mobile,
  rentRange,
  roomType,
  gender,
  visitType,
  visitDateTime,
  remarks,
  source,
  status,
}: leadTypes) => {
  const query =
    "Update Leads set name = ?, propId = ?, rentRange = ?, roomType = ?, gender = ?, visitDateTime = ?, remarks = ?, visitType = ?, source = ?, status = ? where mobile = ?";
  const data = [
    name,
    propId,
    rentRange,
    roomType,
    gender,
    visitDateTime,
    remarks,
    visitType,
    source,
    status,
    mobile,
  ];
  const [rows] = await DB.query<ResultSetHeader[]>(query, data);
  return true;
};

leadDB.getByMobile = async ({ mobile }: leadTypes) => {
  const query = "Select * from Leads where mobile = ?";
  const data = [mobile];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.remove = async ({ mobile }: leadTypes) => {
  const query = "Delete from Leads where mobile = ?";
  const data = [mobile];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

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

leadDB.editLead = async ({ id, visitDateTime, remarks, status }: leadTypes) => {
  const query =
    "Update Leads set visitDateTime = ?, remarks = ?, status = ? where id = ?";
  const data = [visitDateTime, remarks, status, id];
  const [rows] = await DB.query<ResultSetHeader[]>(query, data);
  return true;
};

leadDB.getSummaryByClientId = async ({ clientId } : leadTypes ) => {
  const query = `
    SELECT
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? and status != 12 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS totalLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 1 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS newLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 2 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS followUpLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 3 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitScheduled,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 4 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitedLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 5 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS convertedLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 6 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS notIntersted,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 12 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS lost,
      (SELECT COUNT(L.id) FROM Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.clientId = ? and L.status != 12 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId is null) AS unassignedLeads
    FROM Leads
    LIMIT 1
  `;
  const data = [clientId, clientId, clientId, clientId, clientId, clientId, clientId, clientId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getSummaryByClientIdForStaff = async ({ clientId, staffId } : leadTypes & leadStaffTypes) => {
  const query = `
    SELECT
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.status != 12 and L.clientId = ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ?) AS totalLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 1 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ?) AS newLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 2 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ?) AS followUpLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 3 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ?) AS visitScheduled,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 4 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ?) AS visitedLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 5 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ?) AS convertedLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 6 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ?) AS notIntersted,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 12 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ?) AS lost
    FROM Leads
    LIMIT 1
  `;
  const data = [clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getSummaryByClientIdForSalesHead = async ({ 
  clientId, 
  propertyId=null,
  locationId=null,
} : {
  clientId: number,
  propertyId: number | null,
  locationId: number | null,
}) => {
  let query = `Select 
    COUNT(DISTINCT L.id) AS totalLeads,
    SUM(CASE WHEN DATE(L.createdAt) = DATE(CURDATE()) THEN 1 ELSE 0 END) as todayLeads,
    SUM(CASE WHEN L.status = ? THEN 1 ELSE 0 END) as newLeads,
    SUM(CASE WHEN L.status not in (?, ?, ?, ?, ?, ?) THEN 1 ELSE 0 END) AS activeLeads,
    SUM(CASE WHEN L.status = ? and DATE(L.visitDateTime) = DATE(CURDATE()) THEN 1 ELSE 0 END) AS todayFollowUp,
    SUM(CASE WHEN L.status = ? and DATE(L.visitDateTime) = DATE(CURDATE()) THEN 1 ELSE 0 END) AS todayVisits
    from Leads as L where L.clientId = ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    `;
  
  let data: any = [
    CONSTANTS.LEAD_STATUS.NEW,
    CONSTANTS.LEAD_STATUS.INVALID,
    CONSTANTS.LEAD_STATUS.LOST,
    CONSTANTS.LEAD_STATUS.NOT_ANSWERED,
    CONSTANTS.LEAD_STATUS.NOT_INTERESTED,
    CONSTANTS.LEAD_STATUS.ONHOLD,
    CONSTANTS.LEAD_STATUS.CONVERTED,
    CONSTANTS.LEAD_STATUS.FOLLOW_UP,
    CONSTANTS.LEAD_STATUS.VISIT_SCHEDULED,
    clientId,
  ];

  if (Number(propertyId) && Number(propertyId) !== 0) {
    query += ` AND EXISTS (SELECT 1 FROM PropertyLead AS PL WHERE PL.leadId = L.id AND PL.propId = ?)`;
    data.push(propertyId);
  }

  if (Number(locationId) && Number(locationId) !== 0) {
    query += ` AND EXISTS (SELECT 1 FROM PropertyLead AS PL INNER JOIN Properties AS P ON P.id = PL.propId WHERE PL.leadId = L.id AND P.locationId = ?)`;
    data.push(locationId);
  }

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

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

leadDB.getByClientIdAndMobile = async ({ 
  mobile, 
  clientId 
}: leadTypes) => {
  const query = "Select * from Leads where mobile = ? and clientId = ?";
  const data = [
    mobile, 
    clientId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getByClientIdAndMobileAndPropId = async ({ 
  mobile, 
  clientId,
  propId,
}: leadTypes) => {
  const query = "Select L.* from Leads as L join PropertyLead as PL on PL.leadId = L.id where L.mobile = ? and L.clientId = ? and (PL.propId = ? OR L.propId = ?)";
  const data = [
    mobile, 
    clientId,
    propId,
    propId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getListByDateRange = async ({
  clientId,
  startDate,
  endDate,
}: leadTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select L.* , Properties.name as propertyName, concat(Properties.streetAddress, ', ',  Properties.address) as address from Leads as L JOIN Properties ON Properties.id = L.propId where L.clientId = ? and date(L.createdAt) BETWEEN ? and ? order by L.id desc";
  const data = [clientId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getListByDateRangeX = async ({
  clientId,
  startDate,
  endDate,
  locationFilters,
}: leadTypes & { startDate: string; endDate: string; locationFilters?: any[] | null }) => {
  let query =
    "Select L.* from Leads as L left join PropertyLead as PL on L.id = PL.leadId where L.clientId = ? and date(L.createdAt) BETWEEN ? and ? ";
  const data = [clientId, startDate, endDate];

  if (locationFilters && locationFilters.length > 0) {
    query += ` and PL.propId in (select id from Properties where locationId in (${locationFilters.map(() => '?').join(',')}))`;
    data.push(...locationFilters);
  }

  query += " group by L.id order by L.id desc";
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getListByDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  staffId,
}: leadTypes & { startDate: string; endDate: string; staffId: number }) => {
  const query =
    "Select L.* from Leads as L join LeadStaff as LS on LS.leadId = L.id where L.clientId = ? and LS.staffId = ? and date(L.createdAt) BETWEEN ? and ? group by L.id order by L.id desc";
  const data = [clientId, staffId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getListByPropIdAndDateRange = async ({
  clientId,
  propId,
  startDate,
  endDate,
}: leadTypes & { propId: any; startDate: string; endDate: string; }) => {
  const query =
    `Select L.* from Leads as L left join PropertyLead as PL on PL.leadId = L.id where L.clientId = ? and PL.propId in (${propId.map(() => '?').join(',')}) and date(L.createdAt) BETWEEN ? and ? order by L.id desc`;
  const data = [clientId, ...propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getByClientIdAndStatus = async ({
  clientId,
  status,
  startDate,
  endDate,
}: leadTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select L.*, P.name as propertyName, concat(P.streetAddress, ', ',  P.address) as address from Leads as L JOIN Properties as P ON P.id = L.propId where L.clientId = ? and L.status = ? and date(L.createdAt) BETWEEN ? and ? order by L.id desc";
  const data = [
    clientId,
    status,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getByClientIdAndStatusX = async ({
  clientId,
  status,
  startDate,
  endDate,
  locationFilters,
}: leadTypes & { startDate: string; endDate: string; locationFilters: any[] | null }) => {
  let query =
    "Select L.* from Leads as L left join PropertyLead as PL on L.id = PL.leadId where L.clientId = ? and L.status = ? and date(L.createdAt) BETWEEN ? and ? ";
  const data = [
    clientId,
    status,
    startDate,
    endDate,
  ];

  if (locationFilters && locationFilters.length > 0) {
    query += ` and PL.propId in (select id from Properties where locationId in (${locationFilters.map(() => '?').join(',')}))`;
    data.push(...locationFilters);
  }

  query += " group by L.id order by L.id desc";

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

leadDB.getByClientIdAndStatusForStaffX = async ({
  clientId,
  status,
  startDate,
  endDate,
  staffId,
}: leadTypes & { startDate: string; endDate: string; staffId: number }) => {
  const query =
    "Select L.* from Leads as L join LeadStaff as LS on LS.leadId = L.id where L.clientId = ? and L.status = ? and LS.staffId = ? and date(L.createdAt) BETWEEN ? and ? group by L.id order by L.id desc";
  const data = [
    clientId,
    status,
    staffId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getByClientIdAndStatusApp = async ({
  clientId,
  status,
  pageNum,
  limit,
}: leadTypes & { startDate: string; endDate: string; pageNum: any; limit: any; }) => {
  const query =
    "Select L.* from Leads as L where L.clientId = ? and L.status = ? order by L.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
    const data = [
    clientId,
    status,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

leadDB.getByClientIdAndStatusForStaff = async ({
  clientId,
  status,
  staffId,
  pageNum,
  limit,
}: leadTypes & { startDate: string; endDate: string; staffId: any; pageNum: any; limit: any; }) => {
  const query =
    `Select L.id, L.clientId, L.rentRange, L.roomType, L.name, L.mobile, L.gender, L.visitType, L.visitDateTime, L.source, L.status, L.remarks, L.createdAt, L.updatedAt, L.propId from Leads as L join LeadStaff as LS on LS.leadId = L.id where L.clientId = ? and L.status = ? and LS.staffId = ? group by L.id order by L.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    status,
    staffId,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

// leadDB.getSummaryByClientIdAndDate = async ({ 
//   clientId,
//   startDate,
//   endDate 
// } : leadTypes & { startDate: string; endDate: string }) => {
//   const query = `
//     SELECT
//       (SELECT COUNT(*) FROM Leads WHERE clientId = ? and date(createdAt) BETWEEN ? and ? and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS totalLeads,
//       (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 1 and date(createdAt) BETWEEN ? and ? and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS newLeads,
//       (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 2 and date(createdAt) BETWEEN ? and ? and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS followUpLeads,
//        (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 3 and date(createdAt) BETWEEN ? and ? and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitScheduled,
//       (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 4 and date(createdAt) BETWEEN ? and ? and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitedLeads,
//       (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND status = 5 and date(createdAt) BETWEEN ? and ? and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS convertedLeads
//     FROM Leads
//     LIMIT 1
//   `;
//   const data = [
//     clientId,
//     startDate,
//     endDate,
//     clientId, 
//     startDate,
//     endDate,
//     clientId,
//     startDate,
//     endDate, 
//     clientId,
//     startDate,
//     endDate,
//     clientId,
//     startDate,
//     endDate, 
//     clientId,
//     startDate,
//     endDate,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return false;
// };

leadDB.getSummaryByClientIdAndDate = async ({ 
  clientId,
  startDate,
  endDate,
  locationFilters=null,
} : leadTypes & { startDate: string; endDate: string; locationFilters?: any[] | null }) => {
  
  const data: any[] = [clientId, startDate, endDate];

  let locationJoin = '';
  let locationWhere = '';

  if (locationFilters && locationFilters.length > 0) {
    locationJoin = `LEFT JOIN PropertyLead as PL ON L.id = PL.leadId`;
    locationWhere = `AND PL.propId IN (SELECT id FROM Properties WHERE locationId IN (${locationFilters.map(() => '?').join(',')}))`;
    data.push(...locationFilters);
  }

  const query = `
    SELECT
      COUNT(DISTINCT L.id) AS totalLeads,
      COUNT(DISTINCT CASE WHEN L.status = 1 THEN L.id END) AS newLeads,
      COUNT(DISTINCT CASE WHEN L.status = 2 THEN L.id END) AS followUpLeads,
      COUNT(DISTINCT CASE WHEN L.status = 3 THEN L.id END) AS visitScheduled,
      COUNT(DISTINCT CASE WHEN L.status = 4 THEN L.id END) AS visitedLeads,
      COUNT(DISTINCT CASE WHEN L.status = 5 THEN L.id END) AS convertedLeads
    FROM Leads as L
    ${locationJoin}
    WHERE L.clientId = ? 
      AND DATE(L.createdAt) BETWEEN ? AND ? 
      AND L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
      ${locationWhere}
  `;

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

// leadDB.getSummaryByClientIdAndDateForStaff = async ({ 
//   clientId,
//   startDate,
//   endDate,
//   staffId,
// } : leadTypes & { startDate: string; endDate: string; staffId: number }) => {
//   const query = `
//     SELECT
//       (SELECT COUNT(L.*) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? and LS.staffId = ? and date(L.createdAt) BETWEEN ? and ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS totalLeads,
//       (SELECT COUNT(L.*) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? and LS.staffId = ? AND L.status = 1 and date(L.createdAt) BETWEEN ? and ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS newLeads,
//       (SELECT COUNT(L.*) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? and LS.staffId = ? AND L.status = 2 and date(L.createdAt) BETWEEN ? and ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS followUpLeads,
//       (SELECT COUNT(L.*) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? and LS.staffId = ? AND L.status = 3 and date(L.createdAt) BETWEEN ? and ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitScheduled,
//       (SELECT COUNT(L.*) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? and LS.staffId = ? AND L.status = 4 and date(L.createdAt) BETWEEN ? and ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitedLeads,
//       (SELECT COUNT(L.*) FROM Leads as L join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? and LS.staffId = ? AND L.status = 5 and date(L.createdAt) BETWEEN ? and ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS convertedLeads
//     FROM Leads
//     LIMIT 1
//   `;
//   const data = [
//     clientId,
//     staffId,
//     startDate,
//     endDate,
//     clientId, 
//     staffId,
//     startDate,
//     endDate,
//     clientId,
//     staffId,
//     startDate,
//     endDate, 
//     clientId,
//     staffId,
//     startDate,
//     endDate,
//     clientId,
//     staffId,
//     startDate,
//     endDate, 
//     clientId,
//     staffId,
//     startDate,
//     endDate,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return false;
// };

leadDB.getSummaryByClientIdAndDateForStaff = async ({ 
  clientId,
  startDate,
  endDate,
  staffId,
  locationFilters=null,
} : leadTypes & { 
  startDate: string; 
  endDate: string; 
  staffId: number; 
  locationFilters?: any[] | null 
}) => {
  
  const data: any[] = [clientId, staffId, startDate, endDate];

  let locationJoin = '';
  let locationWhere = '';

  if (locationFilters && locationFilters.length > 0) {
    locationJoin = `LEFT JOIN PropertyLead AS PL ON L.id = PL.leadId`;
    locationWhere = `AND PL.propId IN (SELECT id FROM Properties WHERE locationId IN (${locationFilters.map(() => '?').join(',')}))`;
    data.push(...locationFilters);
  }

  const query = `
    SELECT
      COUNT(DISTINCT L.id) AS totalLeads,
      COUNT(DISTINCT CASE WHEN L.status = 1 THEN L.id END) AS newLeads,
      COUNT(DISTINCT CASE WHEN L.status = 2 THEN L.id END) AS followUpLeads,
      COUNT(DISTINCT CASE WHEN L.status = 3 THEN L.id END) AS visitScheduled,
      COUNT(DISTINCT CASE WHEN L.status = 4 THEN L.id END) AS visitedLeads,
      COUNT(DISTINCT CASE WHEN L.status = 5 THEN L.id END) AS convertedLeads
    FROM Leads AS L 
    INNER JOIN LeadStaff AS LS ON LS.leadId = L.id 
    ${locationJoin}
    WHERE L.clientId = ? 
      AND LS.staffId = ? 
      AND DATE(L.createdAt) BETWEEN ? AND ? 
      AND L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
      ${locationWhere}
  `;

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

leadDB.isAlreadyLinked = async ({
  clientId,
  propId,
  leadId,
}: leadTypes & propertyLeadTypes) => {
  const query =
    "Select L.id, L.clientId, L.name, L.mobile, L.status, L.createdAt from Leads as L left join PropertyLead as PL on PL.leadId = L.id where PL.clientId=? and PL.propId = ? and PL.leadId =?";
  const data = [clientId, propId, leadId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.link = async ({
  clientId,
  propId,
  leadId,
}: leadTypes & propertyLeadTypes) => {
  const query =
    "Insert into PropertyLead (clientId, propId, leadId) values (?, ?, ?)";
  const data = [clientId, propId, leadId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

leadDB.unlink = async ({
  clientId,
  propId,
  leadId,
}: leadTypes & propertyLeadTypes) => {
  const query =
    "Delete from PropertyLead where clientId = ? and propId = ? and leadId = ?";
  const data = [clientId, propId, leadId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

leadDB.getLinkedProperties = async ({
  leadId,
  clientId,
}: leadTypes & propertyLeadTypes) => {
  const query =
    "Select P.id, P.name, 1 as isLinked from PropertyLead as PL inner join Properties as P on P.id = PL.propId where PL.leadId = ? and PL.clientId = ? order by PL.id desc";
  const data = [leadId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getLinkedStaffs = async ({
  leadId,
  clientId,
}: leadTypes & leadStaffTypes) => {
  const query =
    "Select S.id, S.name, 1 as isLinked from LeadStaff as LS inner join Staffs as S on S.id = LS.staffId where LS.leadId = ? and LS.clientId = ? order by LS.id desc";
  const data = [leadId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.recordNotes = async ({
  clientId,
  leadId,
  notes,
  doneByUserType,
  doneBy,
  doneByName,
}: leadNotesTypes) => {
  const query =
    "Insert into LeadNotes (clientId, leadId, notes, doneByUserType, doneBy, doneByName) values (?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    leadId,
    notes,
    doneByUserType,
    doneBy,
    doneByName,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

leadDB.isNoteExists = async ({
  clientId,
  leadId,
  notes,
}: leadNotesTypes) => {
  const query =
    "Select * from LeadNotes where clientId = ? and leadId = ? and notes = ?";
  const data = [clientId, leadId, notes];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getNotesByLeadId = async ({
  leadId,
  clientId,
}: leadNotesTypes) => {
  const query =
    "select * from LeadNotes where notes != '' and leadId = ? and clientId = ? order by id desc";
  const data = [leadId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getByPropIdAndClientId = async ({
  clientId,
  propId,
  pageNum,
  limit,
}: leadTypes & propertyLeadTypes & {pageNum: any; limit: any;}) => {
  const query = 
   `Select * from Leads where id in (select leadId from PropertyLead where propId in (${propId}) and clientId = ?) and status != 12 order by id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
   const data = [
    clientId,
    `${offset}`,
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getByPropIdAndClientIdAndStatus = async ({
  clientId,
  propId,
  status,
  pageNum,
  limit,
}: leadTypes & propertyLeadTypes & {pageNum: any; limit: any}) => {
  const query = 
   `Select * from Leads where id in (select leadId from PropertyLead where propId in (${propId}) and clientId = ?) and status = ? order by id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    status,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getByPropIdAndClientIdForStaff = async ({
  clientId,
  propId,
  staffId,
  pageNum,
  limit,
}: leadTypes & propertyLeadTypes & leadStaffTypes & {pageNum: any; limit: any}) => {
  const query = 
   `Select * from Leads where id in (select leadId from PropertyLead where propId in (${propId}) and clientId = ?) and id in (select leadId from LeadStaff where staffId = ? and clientId = ?) and status != 12 order by id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
   const data = [
    clientId,
    staffId,
    clientId,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getByPropIdAndClientIdAndStatusForStaff = async ({
  clientId,
  propId,
  status,
  staffId,
  pageNum,
  limit,
}: leadTypes & propertyLeadTypes & leadStaffTypes & {pageNum: any; limit: any}) => {
  const query = 
   `Select * from Leads where id in (select leadId from PropertyLead where propId in (${propId}) and clientId = ?) and id in (select leadId from LeadStaff where staffId = ? and clientId = ?) and status = ? order by id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
   const data = [
    clientId,
    staffId,
    clientId,
    status,
    `${offset}`,
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getByStaffIdAndClientId = async ({
  clientId,
  staffs,
  pageNum,
  limit,
}: leadTypes & propertyLeadTypes & {staffs: any; pageNum: any; limit: any}) => {
  const query = 
   `Select * from Leads where id in (select leadId from LeadStaff where staffId in (${staffs}) and clientId = ?) and status != 12 order by id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
   const data = [
    clientId,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getByStaffIdAndClientIdAndStatus = async ({
  clientId,
  staffs,
  status,
  pageNum,
  limit,
}: leadTypes & propertyLeadTypes & {staffs: any; pageNum: any; limit: any}) => {
  const query = 
   `Select * from Leads where id in (select leadId from LeadStaff where staffId in (${staffs}) and clientId = ?) and status = ? order by id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
   const data = [
    clientId,
    status,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.linkStaff = async ({
  clientId,
  staffId,
  leadId,
}: leadTypes & leadStaffTypes) => {
  const query =
    "Insert into LeadStaff (clientId, staffId, leadId) values (?, ?, ?)";
  const data = [clientId, staffId, leadId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

leadDB.unlinkStaff = async ({
  clientId,
  staffId,
  leadId,
}: leadTypes & leadStaffTypes) => {
  const query =
    "Delete from LeadStaff where clientId = ? and staffId = ? and leadId = ?";
  const data = [clientId, staffId, leadId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

leadDB.isStaffAlreadyLinked = async ({
  clientId,
  staffId,
  leadId,
}: leadTypes & leadStaffTypes) => {
  const query =
    "Select L.id, L.clientId, L.name, L.mobile, L.status, L.createdAt from Leads as L left join LeadStaff as LS on LS.leadId = L.id where LS.clientId=? and LS.staffId = ? and LS.leadId =?";
  const data = [clientId, staffId, leadId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getLinkedStaff = async ({
  leadId,
  clientId,
}: leadTypes & leadStaffTypes) => {
  const query =
    "Select S.id, S.name, 1 as isLinked from LeadStaff as LS inner join Staffs as S on S.id = LS.staffId where LS.leadId = ? and LS.clientId = ? order by LS.id desc";
  const data = [leadId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getSummaryByClientIdAndPropFilter = async ({ clientId, propIds } : leadTypes & { propIds: any; }) => {
  const query = `
    SELECT
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from PropertyLead where propId in (${propIds})) and status != 12 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS totalLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from PropertyLead where propId in (${propIds})) AND status = 1 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS newLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from PropertyLead where propId in (${propIds})) AND status = 2 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS followUpLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from PropertyLead where propId in (${propIds})) AND status = 3 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitScheduled,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from PropertyLead where propId in (${propIds})) AND status = 4 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitedLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from PropertyLead where propId in (${propIds})) AND status = 5 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS convertedLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from PropertyLead where propId in (${propIds})) AND status = 6 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS notIntersted,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from PropertyLead where propId in (${propIds})) AND status = 12 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS lost,
      (SELECT COUNT(L.id) FROM Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.clientId = ? and L.status != 12 and L.id in (select leadId from PropertyLead where propId in (${propIds})) and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.id is null) AS unassignedLeads
    FROM Leads
    LIMIT 1
  `;
  const data = [clientId, clientId, clientId, clientId, clientId, clientId, clientId, clientId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getSummaryByClientIdAndPropFilterForStaff = async ({ clientId, propIds, staffId } : leadTypes & leadStaffTypes & {propertiesIds: any; propIds: any} ) => {
  const query = `
    SELECT
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id join LeadStaff as LS on LS.leadId = L.id WHERE L.status != 12 and L.clientId = ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ? and PL.propId in (${propIds})) AS totalLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 1 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ? and PL.propId in (${propIds})) AS newLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 2 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ? and PL.propId in (${propIds})) AS followUpLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 3 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ? and PL.propId in (${propIds})) AS visitScheduled,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 4 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ? and PL.propId in (${propIds})) AS visitedLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 5 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ? and PL.propId in (${propIds})) AS convertedLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 6 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ? and PL.propId in (${propIds})) AS notIntersted,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id join LeadStaff as LS on LS.leadId = L.id WHERE L.clientId = ? AND L.status = 12 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId = ? and PL.propId in (${propIds})) AS lost
    FROM Leads
    LIMIT 1
  `;
  const data = [clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId, clientId, staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getSummaryByClientIdAndStaffFilter = async ({ clientId, staffs } : leadTypes & { staffs: any; }) => {
  const query = `
    SELECT
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from LeadStaff where staffId in (${staffs})) and status != 12 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS totalLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from LeadStaff where staffId in (${staffs})) AND status = 1 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS newLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from LeadStaff where staffId in (${staffs})) AND status = 2 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS followUpLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from LeadStaff where staffId in (${staffs})) AND status = 3 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitScheduled,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from LeadStaff where staffId in (${staffs})) AND status = 4 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS visitedLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from LeadStaff where staffId in (${staffs})) AND status = 5 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS convertedLeads,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from LeadStaff where staffId in (${staffs})) AND status = 6 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS notIntersted,
      (SELECT COUNT(*) FROM Leads WHERE clientId = ? AND id in (select leadId from LeadStaff where staffId in (${staffs})) AND status = 12 and createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AS lost
    FROM Leads
    LIMIT 1
  `;
  const data = [clientId, clientId, clientId, clientId, clientId, clientId, clientId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getSummaryByClientIdAndStaffFilterForStaff = async ({ clientId, propertiesIds, staffs } : leadTypes & {propertiesIds: any; staffs: any} ) => {
  const query = `
    SELECT
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id WHERE L.status != 12 and L.clientId = ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and L.id in (select leadId from LeadStaff where staffId in (${staffs}))) AS totalLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id WHERE L.clientId = ? AND L.status = 1 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and L.id in (select leadId from LeadStaff where staffId in (${staffs}))) AS newLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id WHERE L.clientId = ? AND L.status = 2 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and L.id in (select leadId from LeadStaff where staffId in (${staffs}))) AS followUpLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id WHERE L.clientId = ? AND L.status = 3 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and L.id in (select leadId from LeadStaff where staffId in (${staffs}))) AS visitScheduled,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id WHERE L.clientId = ? AND L.status = 4 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and L.id in (select leadId from LeadStaff where staffId in (${staffs}))) AS visitedLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id WHERE L.clientId = ? AND L.status = 5 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and L.id in (select leadId from LeadStaff where staffId in (${staffs}))) AS convertedLeads,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id WHERE L.clientId = ? AND L.status = 6 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and L.id in (select leadId from LeadStaff where staffId in (${staffs}))) AS notIntersted,
      (SELECT COUNT(DISTINCT(L.id)) FROM Leads as L join PropertyLead as PL on PL.leadId = L.id WHERE L.clientId = ? AND L.status = 12 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and L.id in (select leadId from LeadStaff where staffId in (${staffs}))) AS lost
    FROM Leads
    LIMIT 1
  `;
  const data = [clientId, clientId, clientId, clientId, clientId, clientId, clientId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

leadDB.getStaffUnassignedLeads = async ({
  clientId,
  pageNum,
  limit,
}: leadTypes & { pageNum: any; limit: any }) => {
  const query = 
    `Select L.* from Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.status != 12 and L.clientId = ? and LS.id is null order by L.id desc limit ?, ?`
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getStaffUnassignedLeadsByPropId = async ({
  clientId,
  propId,
  pageNum,
  limit,
}: leadTypes & propertyLeadTypes & {pageNum: any; limit: any;}) => {
  const query = 
   `Select L.* from Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.id in (select leadId from PropertyLead where propId in (${propId}) and clientId = ?) and L.status != 12 and LS.id is null order by L.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
   const data = [
    clientId,
    `${offset}`,
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

leadDB.getByClientIdAndFilters = async ({
  clientId,
  propIds,
  staffIds,
  sources,
  locations,
  status,
  startDate,
  endDate,
  pageNum,
  limit,
}: leadTypes & { pageNum: number; limit: number | null; propIds: any; locations: any; staffIds: any; sources: any; startDate: any; endDate: any;}) => {
  let query = `Select L.* from Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.status != 12 and L.clientId = ? and Date(L.createdAt) BETWEEN ? and ?`;
  let data = [clientId, startDate, endDate];

  if (propIds && propIds.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (${propIds.map(() => '?').join(',')}) and clientId = ?)`;
    data.push(...propIds, clientId);
  }

  if (locations && locations.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (Select id from Properties where locationId in (${locations.map(() => '?').join(',')})) and clientId = ?)`;
    data.push(...locations, clientId);
  }

  if (sources && sources.length > 0) {
    query += ` and L.source in (${sources.map(() => '?').join(',')})`;
    data.push(...sources);
  }

  if (staffIds && staffIds.length > 0) {
    query += ` and LS.staffId in (${staffIds.map(() => '?').join(',')})`;
    data.push(...staffIds);
  }

  if (Number(status) && Number(status) > 0) {
    query += ` and L.status = ?`;
    data.push(status);
  } else if (status && status === -1) {
    query += ` and LS.id is null`;
  }

  query += ` group by L.id order by L.id desc `

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

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

leadDB.getByClientIdAndFiltersWithoutDate = async ({
  clientId,
  propIds,
  staffIds,
  sources,
  status,
  pageNum,
  limit,
}: leadTypes & { pageNum: number; limit: number; propIds: any; staffIds: any; sources: any;}) => {
  let query = `Select L.* from Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.status != 12 and L.clientId = ?`;
  const offset: number = Number((pageNum - 1)) * Number(limit);
  
  let data: any = [clientId];

  if (propIds && propIds.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (${propIds.map(() => '?').join(',')}) and clientId = ?)`;
    data.push(...propIds, clientId);
  }

  if (sources && sources.length > 0) {
    query += ` and L.source in (${sources.map(() => '?').join(',')})`;
    data.push(...sources);
  }

  if (staffIds && staffIds.length > 0) {
    query += ` and LS.staffId in (${staffIds.map(() => '?').join(',')})`;
    data.push(...staffIds);
  }

  if (Number(status) && Number(status) > 0) {
    query += ` and L.status = ?`;
    data.push(status);
  } else if (status && status === -1) {
    query += ` and LS.id is null`;
  }

  query += ` group by L.id order by L.id desc limit ?, ?`
  data.push(`${offset}`);
  data.push(`${limit}`);

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

leadDB.getSummaryByClientIdAndFilters = async ({
  clientId,
  sources,
  propIds,
  locations,
  staffIds,
  startDate,
  endDate,
}: leadTypes & {propIds: any; locations: any; staffIds: any; sources: any; startDate: any; endDate: any;}) => {
  let query = 
    ` SELECT COUNT(*) AS totalLeads,
      COALESCE(SUM(CASE WHEN L.status = 1 THEN 1 ELSE 0 END), 0) AS newLeads, 
      COALESCE(SUM(CASE WHEN L.status = 2 THEN 1 ELSE 0 END), 0) AS followUpLeads,
      COALESCE(SUM(CASE WHEN L.status = 3 THEN 1 ELSE 0 END), 0) AS visitScheduled,
      COALESCE(SUM(CASE WHEN L.status = 4 THEN 1 ELSE 0 END), 0) AS visitedLeads,
      COALESCE(SUM(CASE WHEN L.status = 5 THEN 1 ELSE 0 END), 0) AS convertedLeads,
      COALESCE(SUM(CASE WHEN L.status = 6 THEN 1 ELSE 0 END), 0) AS notInterested,
      COALESCE(SUM(CASE WHEN L.status = 12 THEN 1 ELSE 0 END), 0) AS lostLeads,
      COALESCE((SELECT COUNT(L.id) FROM Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.clientId = ? and L.status != 12 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId is null), 0) AS unassignedLeads
      from Leads AS L left join LeadStaff AS LS ON LS.leadId = L.id where L.clientId = ? AND Date(L.createdAt) BETWEEN ? AND ?`
  
  let data = [
    clientId,
    clientId,
    startDate,
    endDate,
  ];

  if (propIds && propIds.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (${propIds.map(() => '?').join(',')}) and clientId = ?)`;
    data.push(...propIds, clientId);
  }

  if (locations && locations.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (Select id from Properties where locationId in (${locations.map(() => '?').join(',')})) and clientId = ?)`;
    data.push(...locations, clientId);
  }

  if (sources && sources.length > 0) {
    query += ` and L.source in (${sources.map(() => '?').join(',')})`;
    data.push(...sources);
  }

  if (staffIds && staffIds.length > 0) {
    query += ` and LS.staffId in (${staffIds.map(() => '?').join(',')})`;
    data.push(...staffIds);
  }
  
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  else return false;
};

leadDB.getByClientIdAndFiltersForStaff = async ({
  clientId,
  staffId,
  propIds,
  locations,
  staffIds,
  sources,
  status,
  startDate,
  endDate,
  pageNum,
  limit,
}: leadTypes & { pageNum: number; limit: number; propIds: any; locations: any; staffIds: any; sources: any; startDate: any; endDate: any; staffId: any}) => {
  let query = `Select L.* from Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.status != 12 and L.clientId = ? and LS.staffId = ? and Date(L.createdAt) BETWEEN ? and ?`;
  let data = [clientId, staffId, startDate, endDate];

  if (propIds && propIds.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (${propIds.map(() => '?').join(',')}) and clientId = ?)`;
    data.push(...propIds, clientId);
  }

  if (locations && locations.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (Select id from Properties where locationId in (${locations.map(() => '?').join(',')})) and clientId = ?)`;
    data.push(...locations, clientId);
  }

  if (sources && sources.length > 0) {
    query += ` and L.source in (${sources.map(() => '?').join(',')})`;
    data.push(...sources);
  }

  if (staffIds && staffIds.length > 0) {
    query += ` and LS.staffId in (${staffIds.map(() => '?').join(',')})`;
    data.push(...staffIds);
  }

  if (status && status > 0) {
    query += ` and L.status = ?`;
    data.push(status);
  } else if (status && status === -1) {
    query += ` and LS.id is null`;
  }

  query += ` order by L.id desc `

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

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

leadDB.getSummaryByClientIdAndFiltersForStaff = async ({
  clientId,
  staffId,
  sources,
  propIds,
  locations,
  staffIds,
  startDate,
  endDate,
}: leadTypes & {propIds: any; locations: any; staffIds: any; sources: any; startDate: any; endDate: any; staffId: any}) => {
  let query = 
    ` SELECT COUNT(*) AS totalLeads, 
      COALESCE(SUM(CASE WHEN L.status = 1 THEN 1 ELSE 0 END), 0) AS newLeads, 
      COALESCE(SUM(CASE WHEN L.status = 2 THEN 1 ELSE 0 END), 0) AS followUpLeads,
      COALESCE(SUM(CASE WHEN L.status = 3 THEN 1 ELSE 0 END), 0) AS visitScheduled,
      COALESCE(SUM(CASE WHEN L.status = 4 THEN 1 ELSE 0 END), 0) AS visitedLeads,
      COALESCE(SUM(CASE WHEN L.status = 5 THEN 1 ELSE 0 END), 0) AS convertedLeads,
      COALESCE(SUM(CASE WHEN L.status = 6 THEN 1 ELSE 0 END), 0) AS notInterested,
      COALESCE(SUM(CASE WHEN L.status = 12 THEN 1 ELSE 0 END), 0) AS lostLeads,
      COALESCE(SUM(CASE WHEN LS.staffId IS NULL THEN 1 ELSE 0 END), 0) AS unassignedLeads
      from Leads AS L left join LeadStaff AS LS ON LS.leadId = L.id where L.clientId = ? and LS.staffId = ? and Date(L.createdAt) BETWEEN ? AND ?`
  
  let data = [
    clientId,
    staffId,
    startDate,
    endDate,
  ];

  if (propIds && propIds.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (${propIds.map(() => '?').join(',')}) and clientId = ?)`;
    data.push(...propIds, clientId);
  }

  if (locations && locations.length > 0) {
    query += ` and L.id in (select leadId from PropertyLead where propId in (Select id from Properties where locationId in (${locations.map(() => '?').join(',')})) and clientId = ?)`;
    data.push(...locations, clientId);
  }

  if (sources && sources.length > 0) {
    query += ` and L.source in (${sources.map(() => '?').join(',')})`;
    data.push(...sources);
  }

  if (staffIds && staffIds.length > 0) {
    query += ` and LS.staffId in (${staffIds.map(() => '?').join(',')})`;
    data.push(...staffIds);
  }
  
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  else return false;
}

leadDB.getUnAssignedLeads = async ({ clientId } : leadTypes ) => {
  const query = `SELECT COUNT(L.id) as count FROM Leads as L left join LeadStaff as LS on LS.leadId = L.id where L.clientId = ? and L.status != 12 and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and LS.staffId is null
  `;
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].count;
  else return 0;
};

leadDB.getStaffWiseStatsForSalesHead = async ({ 
  clientId, 
  propertyId, 
  locationId 
} : leadTypes & { propertyId: number | null; locationId: number | null;} ) => {

  let propertyFilter = "";
  let locationFilter = "";
  let data: any = [
    CONSTANTS.LEAD_STATUS.INVALID,
    CONSTANTS.LEAD_STATUS.LOST,
    CONSTANTS.LEAD_STATUS.NOT_ANSWERED,
    CONSTANTS.LEAD_STATUS.NOT_INTERESTED,
    CONSTANTS.LEAD_STATUS.ONHOLD,
    CONSTANTS.LEAD_STATUS.CONVERTED,
    CONSTANTS.LEAD_STATUS.FOLLOW_UP,
    CONSTANTS.LEAD_STATUS.VISIT_SCHEDULED,
    CONSTANTS.LEAD_STATUS.CONVERTED,
  ];

  if (Number(propertyId) && Number(propertyId) > 0) {
    propertyFilter = " and P.id = ?";
    data.push(propertyId);
  }

  if (Number(locationId) && Number(locationId) > 0) {
    locationFilter = " and P.locationId = ?";
    data.push(locationId);
  }

  let query = `SELECT LS.staffId as staffId, S.name as staffName, S.mobile as staffMobile, COUNT(DISTINCT L.id) as totalLeads,
  SUM (CASE WHEN L.status not in (?, ?, ?, ?, ?, ?) THEN 1 ELSE 0 END) as activeLeads,
  SUM (CASE WHEN L.status = ? THEN 1 ELSE 0 END) as pendingFollowUps,
  SUM (CASE WHEN L.status = ? THEN 1 ELSE 0 END) as pendingVisits,
  SUM (CASE WHEN L.status = ? THEN 1 ELSE 0 END) as convertedLeads,
  (Select COUNT(*) from Bookings as B left join Properties as P on P.id = B.propId where B.bookedBy = LS.staffId and B.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) ${propertyFilter} ${locationFilter}) as totalBookings
  FROM Leads as L join LeadStaff as LS on LS.leadId = L.id join Staffs as S on S.id = LS.staffId left join Properties as P on L.propId = P.id where L.clientId = ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) `;

  data.push(clientId);

  if (Number(propertyId) && Number(propertyId) > 0) {
    query += " and P.id = ?";
    data.push(propertyId);
  }

  if (Number(locationId) && Number(locationId) > 0) {
    query += " and P.locationId = ?";
    data.push(locationId);
  }

  query += ` group by LS.staffId`;

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

leadDB.getSourceWiseStatsForSalesHead = async ({ 
  clientId, 
  propertyId, 
  locationId 
} : leadTypes & { propertyId: number | null; locationId: number | null;} ) => {

  let query = `SELECT L.source, COUNT(DISTINCT L.id) as totalLeads,
  SUM (CASE WHEN L.status not in (?, ?, ?, ?, ?, ?) THEN 1 ELSE 0 END) as activeLeads,
  SUM (CASE WHEN L.status = ? THEN 1 ELSE 0 END) as pendingFollowUps,
  SUM (CASE WHEN L.status = ? THEN 1 ELSE 0 END) as pendingVisits,
  SUM (CASE WHEN L.status = ? THEN 1 ELSE 0 END) as convertedLeads,
  SUM (CASE WHEN L.status in (?, ?) THEN 1 ELSE 0 END) as lostLeads
  FROM Leads as L where L.clientId = ? and L.createdAt >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) `;

  let data: any = [
    CONSTANTS.LEAD_STATUS.INVALID,
    CONSTANTS.LEAD_STATUS.LOST,
    CONSTANTS.LEAD_STATUS.NOT_ANSWERED,
    CONSTANTS.LEAD_STATUS.NOT_INTERESTED,
    CONSTANTS.LEAD_STATUS.ONHOLD,
    CONSTANTS.LEAD_STATUS.CONVERTED,
    CONSTANTS.LEAD_STATUS.FOLLOW_UP,
    CONSTANTS.LEAD_STATUS.VISIT_SCHEDULED,
    CONSTANTS.LEAD_STATUS.CONVERTED,
    CONSTANTS.LEAD_STATUS.LOST,
    CONSTANTS.LEAD_STATUS.NOT_INTERESTED,
    clientId,
  ];

  if (Number(propertyId) && Number(propertyId) > 0) {
    query += " AND EXISTS (SELECT 1 FROM PropertyLead AS PL WHERE PL.leadId = L.id AND PL.propId = ?)";
    data.push(propertyId);
  }

  if (Number(locationId) && Number(locationId) > 0) {
    query += " AND EXISTS (SELECT 1 FROM PropertyLead AS PL INNER JOIN Properties AS P ON P.id = PL.propId WHERE PL.leadId = L.id AND P.locationId = ?)";
    data.push(locationId);
  }

  query += ` group by L.source`;

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

export default leadDB;