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

const complaintDB: any = {};

complaintDB.getById = async ({ id }: complaintsTypes) => {
  const query =
    "Select C.id, C.reopenReason, C.raisedFor, C.closingRemarks, C.staffNote, C.raisedBy, C.propId, C.img, C.resolvedAt, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.coAssignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as StaffRole, S.mobile as staffMobile, S2.name as secondStaffName, S2.role as secondStaffRole, S2.mobile as secondStaffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo left join Staffs as S2 on S2.id = C.coAssignedTo where C.id = ? order by C.id desc";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

complaintDB.getByIdForProp = async ({ id }: complaintsTypes) => {
  const query =
    "Select C.id, C.raisedFor, C.reopenReason, C.closingRemarks, C.raisedBy, C.staffNote, C.propId, C.img, C.resolvedAt, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.coAssignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, S2.name as secondStaffName, S2.role as secondStaffRole, S2.mobile as secondStaffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo left join Staffs as S2 on S2.id = C.coAssignedTo where C.id = ? and C.raisedFor=2 and raisedBy=3 UNION  Select C.id, C.raisedFor, C.reopenReason, C.closingRemarks, C.raisedBy, C.staffNote, C.propId, C.img, C.resolvedAt, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.coAssignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, S2.name as secondStaffName, S2.role as secondStaffRole, S2.mobile as secondStaffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo left join Staffs as S2 on S2.id = C.coAssignedTo where C.id = ? and C.raisedFor=2 and raisedBy=1";

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

complaintDB.getByTenantId = async ({ tenantId }: complaintsTypes) => {
  const query =
    "Select C.id, C.clientId, C.closingRemarks, C.reopenReason, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.propId, C.roomId, C.floor, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt,S.role as staffRole, S.name as staffName, S.mobile as staffMobile, O.name as ownerName, R.roomNum from Complaints as C left join Staffs as S on S.id = C.assignedTo left join Clients as O on O.id = C.clientId inner join Rooms as R on R.id = C.roomId where C.tenantId = ? order by C.id desc";
  const data = [tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByTenantIdAndClientId = async ({ tenantId, clientId }: complaintsTypes) => {
  const query =
    "Select C.id, C.clientId, C.closingRemarks, C.reopenReason, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.propId, C.roomId, C.floor, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt,S.role as staffRole, S.name as staffName, S.mobile as staffMobile, O.name as ownerName, R.roomNum from Complaints as C left join Staffs as S on S.id = C.assignedTo left join Clients as O on O.id = C.clientId inner join Rooms as R on R.id = C.roomId where C.tenantId = ? and C.clientId = ? order by C.id desc";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getAssignedByStaffId = async ({
  clientId,
  propId,
  assignedTo,
}: complaintsTypes) => {
  const query =
    "Select C.id, C.clientId, C.closingRemarks, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.propId, C.roomId, C.floor, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt from Complaints as C where C.clientId = ? and C.propId = ? and C.assignedTo = ? and C.status = ? order by C.id desc";
  const data = [
    clientId,
    propId,
    assignedTo,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByClientId = async ({ clientId }: complaintsTypes) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, R.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ?  order by C.id desc";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByClientIdAndPage = async ({
  clientId,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ?  order by C.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;
};

complaintDB.getByAdminIdAndPage = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.staffNote, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propertiesIds})  order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByStaffIdAndPage = async ({
  assignedTo,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title,C.closingRemarks, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ?  order by C.id desc  limit ?, ? ";

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

complaintDB.getPendingComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? order by C.id desc  limit ?, ? ";

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getAdminPendingComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  const query = `Select C.id,C.closingRemarks, C.title, C.img, C.propId, C.staffNote, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? and C.propId in (${propertiesIds}) order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getPendingComplaints = async ({ clientId }: complaintsTypes) => {
  const query =
    "Select COUNT(C.id) as count from Complaints as C where C.clientId = ? and C.status = ?";

  const data = [clientId, CONSTANTS.COMPLAINT_STATUS.PENDING];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].count;
  else return false;
};
complaintDB.getPendingComplaintsForStaff = async ({
  clientId,
  propertiesIds,
}: complaintsTypes & { propertiesIds: string }) => {
  const query = `Select COUNT(C.id) as count from Complaints as C where C.clientId = ?  and C.propId IN (${propertiesIds})  and C.status = ?`;

  const data = [clientId, CONSTANTS.COMPLAINT_STATUS.PENDING];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].count;
  else return false;
};
complaintDB.getPendingComplaintsByPropId = async ({
  propId,
}: complaintsTypes) => {
  const query =
    "Select COUNT(C.id) as count from Complaints as C where C.propId = ? and C.status = ?";

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

complaintDB.getComplaintCountForStaff = async ({
  assignedTo,
  status,
}: complaintsTypes) => {
  const query =
    "Select COUNT(C.id) as count from Complaints as C where C.assignedTo = ? and C.status = ?";

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

complaintDB.getResolvedComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? order by C.id desc  limit ?, ? ";

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getAdminResolvedComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? and C.propId in (${propertiesIds}) order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getStaffResolvedComplaintsByPage = async ({
  assignedTo,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.status = ? order by C.id desc  limit ?, ? ";

  const offset: number = (pageNum - 1) * limit;
  const data = [
    assignedTo,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};
complaintDB.getStaffPendingComplaintsByPage = async ({
  assignedTo,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.status = ? order by C.id desc  limit ?, ? ";

  const offset: number = (pageNum - 1) * limit;
  const data = [
    assignedTo,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getStaffResolvedPropComplaintsByPage = async ({
  assignedTo,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor from Complaints as C inner join Properties as P on P.id = C.propId left join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.status = ? and C.tenantId is NULL order by C.id desc  limit ?, ? ";

  const offset: number = (pageNum - 1) * limit;
  const data = [
    assignedTo,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getAssignedComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? order by C.id desc  limit ?, ? ";

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getAdminAssignedComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? and C.propId in (${propertiesIds}) order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  // const data = [
  //   clientId,
  //   CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
  //   propertiesIds,
  //   `${offset}`,
  //   `${limit}`,
  // ];
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getStaffAssignedComplaintsByPage = async ({
  assignedTo,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.staffNote, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.status = ? order by C.id desc  limit ?, ? ";

  const offset: number = (pageNum - 1) * limit;
  const data = [
    assignedTo,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getStaffAssignedPropComplaintsByPage = async ({
  assignedTo,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.staffNote, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor from Complaints as C inner join Properties as P on P.id = C.propId left join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.status = ? and C.tenantId is NULL order by C.id desc  limit ?, ? ";

  const offset: number = (pageNum - 1) * limit;
  const data = [
    assignedTo,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getRecentAssignedComplaintsByStaffId = async ({
  assignedTo,
  limit,
}: complaintsTypes & { limit: number }) => {
  const query =
    "Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.staffNote, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, R.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.status = ? order by C.id desc  limit ? ";

  const data = [assignedTo, CONSTANTS.COMPLAINT_STATUS.ASSIGNED, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.create = async ({
  clientId,
  tenantId,
  propId,
  roomId,
  raisedBy,
  raisedFor,
  floor,
  img,
  title,
  raisedById,
  description,
}: complaintsTypes) => {
  const query =
    "Insert into Complaints (clientId, tenantId, propId, roomId, floor, img, title, description,raisedBy,raisedFor,raisedById) values (?,?,?,?,?, ?,?,?,?,?,?)";
  const data = [
    clientId,
    tenantId,
    propId,
    roomId,
    floor,
    img,
    title,
    description,
    raisedBy,
    raisedFor,
    raisedById,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.assignStaffDB = async ({
  id,
  assignedTo,
  status,
}: complaintsTypes) => {
  const query = "Update Complaints set assignedTo = ?, status = ? where id = ?";
  const data = [assignedTo, status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.secondAssignStaffDB = async ({
  id,
  coAssignedTo,
  status,
}: complaintsTypes) => {
  const query = "Update Complaints set coAssignedTo = ?, status = ? where id = ?";
  const data = [coAssignedTo, status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.removeSecondAssignStaffDB = async ({
  id
}: complaintsTypes) => {
  const query = "Update Complaints set coAssignedTo = null where id = ?";
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.updateStatus = async ({ id, status }: complaintsTypes) => {
  const query = "Update Complaints set status = ? where id = ?";
  const data = [status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.updateDescription = async ({ id, description }: complaintsTypes) => {
  const query = "Update Complaints set description = ? where id = ?";
  const data = [description, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.updateImg = async ({ id, img }: complaintsTypes) => {
  const query = "Update Complaints set img = ? where id = ?";
  const data = [img, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.updateResolvedAt = async ({ id, resolvedAt }: complaintsTypes) => {
  const query = "Update Complaints set resolvedAt = ? where id = ?";
  const data = [resolvedAt, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.updateFeedback = async ({
  feedback,
  rating,
  id,
}: complaintsTypes) => {
  const query = "Update Complaints set feedback = ?, rating=? where id = ?";
  const data = [feedback, rating, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.removeByTenantId = async ({ tenantId }: complaintsTypes) => {
  const query = "Delete from Complaints where tenantId = ?";
  const data = [tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.removeByClientIdAndTenantId = async ({ tenantId, clientId }: complaintsTypes) => {
  const query = "Delete from Complaints where tenantId = ? and clientId = ?";
  const data = [tenantId, clientId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.getComplaints = async (
  { clientId }: complaintsTypes,
  month: number,
  year: number
) => {
  const query =
    "Select title, count(title) as value from Complaints where clientId = ? and month(createdAt) = ? and year(createdAt) = ? group by title order by value desc";
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

complaintDB.getComplaintsWithDates = async (
  { clientId, startDate, endDate, limit }: complaintsTypes & {startDate: string, endDate: string, limit: number},
) => {
  const query =
    "Select title, count(title) as value from Complaints where clientId = ? and date(createdAt) between ? and ? group by title order by value desc limit ?";
  const data = [clientId, startDate, endDate, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

complaintDB.getComplaintsByProperty = async ({
  clientId,
  month,
  year,
}: complaintsTypes & {month: number; year: number}) => {
  const query =
    "Select Count(C.title) as value, COALESCE(C.propId, O.propId) AS propId, P.name as propName from Complaints as C left join (select o1.* from Occupancies o1 inner join (select tenantId, Min(id) as minId from Occupancies group by tenantId) o2 on o1.tenantId = o2.tenantId and o1.id = o2.minId) as O on C.tenantId = O.tenantId AND C.propId is null left join Properties as P on P.id = COALESCE(C.propId, O.propId) where C.clientId = ? and month(C.createdAt) = ? and year(C.createdAt) = ? group by COALESCE(C.propId, O.propId), P.name order by value desc";
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

complaintDB.getComplaintsByPropertyWithDate = async ({
  clientId,
  startDate,
  endDate,
  limit,
}: complaintsTypes & {startDate: string; endDate: string, limit: number}) => {
  const query =
    "Select Count(C.title) as value, COALESCE(C.propId, O.propId) AS propId, P.name as propName from Complaints as C left join (select o1.* from Occupancies o1 inner join (select tenantId, Min(id) as minId from Occupancies group by tenantId) o2 on o1.tenantId = o2.tenantId and o1.id = o2.minId) as O on C.tenantId = O.tenantId AND C.propId is null left join Properties as P on P.id = COALESCE(C.propId, O.propId) where C.clientId = ? and date(C.createdAt) between ? and ? group by COALESCE(C.propId, O.propId), P.name order by value desc limit ?";
  const data = [clientId, startDate, endDate, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

complaintDB.getComplaintsByTenantWithDate = async ({
  clientId,
  startDate,
  endDate,
  limit,
}: complaintsTypes & {startDate: string; endDate: string; limit: number}) => {
  const query =
    "select * from (Select T.name, T.mobile, C.tenantId , count(C.id) as total, SUM(CASE WHEN C.status =? THEN 1 ELSE 0 END) AS resolved from Complaints as C INNER JOIN Tenants as T On T.id=C.tenantId where C.tenantId IS NOT null and C.clientId = ? and date(C.createdAt) between ? and ? group by C.tenantId ) as v order by total desc limit ?";
  const data = [CONSTANTS.COMPLAINT_STATUS.RESOLVED, clientId, startDate, endDate, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

complaintDB.graphData = async (
  { clientId }: complaintsTypes,
  month: number,
  year: number
) => {
  const query =
    "Select count(title) as totalComplaints, (select count(title) from Complaints where status = 3 and clientId = ? and month(createdAt) = ? and year(createdAt) = ?) as resolved, (select count(title) from Complaints where status = 1 and clientId = ? and month(createdAt) = ? and year(createdAt) = ?) as pending, (select count(title) from Complaints where status = 2 and clientId = ? and month(createdAt) = ? and year(createdAt) = ?) as assigned from Complaints where clientId = ? and month(createdAt) = ? and year(createdAt) = ?;";
  const data = [
    clientId,
    month,
    year,
    clientId,
    month,
    year,
    clientId,
    month,
    year,
    clientId,
    month,
    year,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.graphDataByDate = async ({ 
  clientId,
  startDate,
  endDate
}: complaintsTypes & {startDate: string, endDate: string},) => {
  const query =
    "Select count(title) as totalComplaints, (select count(title) from Complaints where status = 3 and clientId = ? and date(createdAt) between ? and ?) as resolved, (select count(title) from Complaints where status = 1 and clientId = ? and date(createdAt) between ? and ?) as pending, (select count(title) from Complaints where status = 2 and clientId = ? and date(createdAt) between ? and ?) as assigned from Complaints where clientId = ? and date(createdAt) between ? and ?;";
  const data = [
    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 [];
};

complaintDB.graphDataForWeb = async ({ 
  clientId,
  month,
  year,
  propIds,
}: complaintsTypes & {month: string; year: string; propIds: any;}) => {
  let query =
    "Select count(*) as totalComplaints, SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as resolved, SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as pending, SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as assigned from Complaints where clientId = ? and month(createdAt) = ? and year(createdAt) = ?";
  const data = [
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    clientId,
    month,
    year,
  ];

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

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

complaintDB.getAvgResolutionTime = async (
  { clientId }: complaintsTypes,
  month: number,
  year: number
) => {
  const query =
    "Select AVG(timestampdiff(SECOND, createdAt, updatedAt))/3600 as avgResolution from Complaints where clientId = ? and month(createdAt) = ? and year(createdAt) = ? and status = 3;";
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.graphDataForStaff = async (
  { clientId }: complaintsTypes,
  month: number,
  year: number,
  propertiesIds: string,
) => {
  const query =
    `Select count(title) as totalComplaints, (select count(title) from Complaints where status = 3 and clientId = ? and propId in (${propertiesIds}) and month(createdAt) = ? and year(createdAt) = ?) as resolved, (select count(title) from Complaints where status = 1 and clientId = ? and propId in (${propertiesIds}) and month(createdAt) = ? and year(createdAt) = ?) as pending, (select count(title) from Complaints where status = 2 and clientId = ? and propId in (${propertiesIds}) and month(createdAt) = ? and year(createdAt) = ?) as assigned from Complaints where clientId = ? and propId in (${propertiesIds}) and month(createdAt) = ? and year(createdAt) = ?;`;
  const data = [
    clientId,
    month,
    year,
    clientId,
    month,
    year,
    clientId,
    month,
    year,
    clientId,
    month,
    year,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.graphDataForWebStaff = async ({ 
  clientId,
  propIds,
  propertiesIds,
  month,
  year,
}: complaintsTypes & {month: string; year: string; propIds: any; propertiesIds: string}) => {
  let query =
    `Select count(*) as totalComplaints, SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as resolved, SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as pending, SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as assigned from Complaints where clientId = ? and propId in (${propertiesIds}) and month(createdAt) = ? and year(createdAt) = ?`;
  const data = [
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    clientId,
    month,
    year,
  ];

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

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

complaintDB.getAvgResolutionTimeForStaff = async (
  { clientId }: complaintsTypes,
  month: number,
  year: number,
  propertiesIds: string,
) => {
  const query =
    `Select AVG(timestampdiff(SECOND, createdAt, updatedAt))/3600 as avgResolution from Complaints where clientId = ? and propId in (${propertiesIds}) and month(createdAt) = ? and year(createdAt) = ? and status = 3;`;
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.graphDataForProp = async (
  { clientId }: complaintsTypes,
  month: number,
  year: number,
  propId: number,
) => {
  const query =
    `Select count(title) as totalComplaints, (select count(title) from Complaints where status = 3 and clientId = ? and propId = ? and month(createdAt) = ? and year(createdAt) = ?) as resolved, (select count(title) from Complaints where status = 1 and clientId = ? and propId = ? and month(createdAt) = ? and year(createdAt) = ?) as pending, (select count(title) from Complaints where status = 2 and clientId = ? and propId = ? and month(createdAt) = ? and year(createdAt) = ?) as assigned from Complaints where clientId = ? and propId = ? and month(createdAt) = ? and year(createdAt) = ?;`;
  const data = [
    clientId,
    propId,
    month,
    year,
    clientId,
    propId,
    month,
    year,
    clientId,
    propId,
    month,
    year,
    clientId,
    propId,
    month,
    year,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.getAvgResolutionTimeForProp = async (
  { clientId }: complaintsTypes,
  month: number,
  year: number,
  propId: number,
) => {
  const query =
    `Select AVG(timestampdiff(SECOND, createdAt, updatedAt))/3600 as avgResolution from Complaints where clientId = ? and propId = ? and month(createdAt) = ? and year(createdAt) = ? and status = 3;`;
  const data = [clientId, propId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.getTopTotal = async (
  { clientId }: complaintsTypes,
  month: any,
  year: any,
  limit: number
) => {
  const query = `Select SUM(value) as total From (Select title, count(title) as value from Complaints where clientId = ? and month(createdAt) = ? and year(createdAt) = ? group by title order by value desc limit ${limit}) AS Subquery`;
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.getTopTotalByDate = async (
  { clientId, startDate, endDate, limit }: complaintsTypes & { startDate: any, endDate: any, limit: number}
) => {
  const query = `Select SUM(value) as total From (Select title, count(title) as value from Complaints where clientId = ? and date(createdAt) between ? and ? group by title order by value desc limit ?) AS Subquery`;
  const data = [clientId, startDate, endDate, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.getTopTotalByProp = async ({
  clientId,
  month,
  year,
  limit
}: complaintsTypes & {month: any; year: any; limit: number}) => {
  const query = `Select SUM(value) as total From (Select Count(C.title) as value, COALESCE(C.propId, O.propId) AS propId, P.name as propName from Complaints as C left join (select o1.* from Occupancies o1 inner join (select tenantId, Min(id) as minId from Occupancies group by tenantId) o2 on o1.tenantId = o2.tenantId and o1.id = o2.minId) as O on C.tenantId = O.tenantId AND C.propId is null left join Properties as P on P.id = COALESCE(C.propId, O.propId) where C.clientId = ? and month(C.createdAt) = ? and year(C.createdAt) = ? group by COALESCE(C.propId, O.propId), P.name order by value desc limit ${limit}) AS Subquery`;
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.getYearlyComplaints = async (
  { clientId }: complaintsTypes,
  year: number
) => {
  const query =
    "Select title, count(title) as value from Complaints where clientId = ? and year(createdAt) = ? group by title order by value desc";
  const data = [clientId, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

complaintDB.yearlyGraphData = async (
  { clientId }: complaintsTypes,
  year: number
) => {
  const query =
    "Select count(title) as totalComplaints, (select count(title) from Complaints where status = 3 and clientId = ? and year(createdAt) = ?) as resolved, (select count(title) from Complaints where status = 1 and clientId = ? and year(createdAt) = ?) as pending, (select count(title) from Complaints where status = 2 and clientId = ? and year(createdAt) = ?) as assigned from Complaints where clientId = ? and year(createdAt) = ?;";
  const data = [clientId, year, clientId, year, clientId, year, clientId, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.getYearlyAvgResolutionTime = async (
  { clientId }: complaintsTypes,
  year: number
) => {
  const query =
    "Select AVG(timestampdiff(SECOND, createdAt, updatedAt))/3600 as avgResolution from Complaints where clientId = ? and year(createdAt) = ? and status = 3;";
  const data = [clientId, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.getYearlyTopTotal = async (
  { clientId }: complaintsTypes,
  year: number,
  limit: number
) => {
  const query =
    "Select SUM(value) as total From (Select title, count(title) as value from Complaints where clientId = ? and year(createdAt) = ? group by title order by value desc limit ?) AS Subquery;";
  const data = [clientId, year, limit];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

complaintDB.getForPropByClientIdAndPage = async ({
  clientId,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  // const query =
  //   "Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, C.floor from Complaints as C inner join Properties as P on P.id = C.propId  where C.raisedFor=2 AND C.clientId = ?  order by C.id desc  limit ?, ? ";
  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${staff} UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${client}) as v order by id desc limit ?, ?`;

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

complaintDB.getForPropAssignedComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  // const query =
  //   "Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, C.floor from Complaints as C inner join Properties as P on P.id = C.propId  where C.raisedFor=2 and C.clientId = ? and C.status = ? order by C.id desc  limit ?, ? ";
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${staff} and C.status = ? UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${client} and C.status = ?) as v order by id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    clientId,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getForPropPendingComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  // const query =
  //   "Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, C.floor from Complaints as C inner join Properties as P on P.id = C.propId  where C.raisedFor=2 and C.clientId = ? and C.status = ? order by C.id desc  limit ?, ? ";
  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${staff} and C.status = ? UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${client} and C.status = ?) as v order by id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    clientId,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    `${offset}`,
    `${limit}`,
  ];

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

complaintDB.getForPropResolvedComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  // const query =
  //   "Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, C.floor from Complaints as C inner join Properties as P on P.id = C.propId where C.raisedFor=2 and  C.clientId = ? and C.status = ? order by C.id desc  limit ?, ? ";
  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${staff} and C.status = ? UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${client} and C.status = ?) as v order by id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    clientId,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getForPropAdminPendingComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  // const query = `Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor from Complaints as C inner join Properties as P on P.id = C.propId where C.raisedFor=2 AND C.clientId = ? and C.status = ? and C.propId in (${propertiesIds}) order by C.id desc  limit ?, ?`;

  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.status = ? and C.raisedFor=2 and raisedBy=${staff} and C.propId in (${propertiesIds}) UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.status = ? and C.raisedFor=2 and raisedBy=${client} and C.propId in (${propertiesIds})) as v order by id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    clientId,
    CONSTANTS.COMPLAINT_STATUS.PENDING,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getForPropAdminResolvedComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  // const query = `Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor from Complaints as C inner join Properties as P on P.id = C.propId where C.raisedFor=2 AND C.clientId = ? and C.status = ? and C.propId in (${propertiesIds}) order by C.id desc  limit ?, ?`;
  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.status = ? and C.raisedFor=2 and raisedBy=${staff} and C.propId in (${propertiesIds}) UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.status = ? and C.raisedFor=2 and raisedBy=${client} and C.propId in (${propertiesIds})) as v order by id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    clientId,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getForPropAdminAssignedComplaintsByPage = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  // const query = `Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor from Complaints as C inner join Properties as P on P.id = C.propId  where C.raisedFor=2 AND C.clientId = ? and C.status = ? and C.propId in (${propertiesIds}) order by C.id desc  limit ?, ?`;

  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.status = ? and C.raisedFor=2 and raisedBy=${staff} and C.propId in (${propertiesIds}) UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.status = ? and C.raisedFor=2 and raisedBy=${client} and C.propId in (${propertiesIds})) as v order by id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;

  const data = [
    clientId,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    clientId,
    CONSTANTS.COMPLAINT_STATUS.ASSIGNED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getForPropByAdminIdAndPage = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  // const query = `Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor from Complaints as C inner join Properties as P on P.id = C.propId  where C.raisedFor=2 AND C.clientId = ? and C.propId in (${propertiesIds})  order by C.id desc  limit ?, ?`;

  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${staff} and C.propId in (${propertiesIds}) UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${client} and C.propId in (${propertiesIds})) as v order by id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getComplaintCountsForClient = async ({
  clientId,
}: complaintsTypes) => {
  //const query =
  //"select  status, count(*) as total from Complaints where clientId=? group by status";
  const query =
    "SELECT (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and status = 1) AS new, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? AND status = 2) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and  clientId = ? AND status = 3) AS closed FROM Complaints LIMIT 1";

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

complaintDB.getPropComplaintCountsForClient = async ({
  clientId,
}: complaintsTypes) => {
  //const query =
  //"select  status, count(*) as total from Complaints where clientId=? group by status";
  const query =
    "SELECT (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NULL and clientId = ? and status = 1) AS new, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NULL and clientId = ? AND status = 2) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NULL and  clientId = ? AND status = 3) AS closed FROM Complaints LIMIT 1";

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

complaintDB.getComplaintCountsByPropForClient = async ({
  clientId,
  propIds,
}: complaintsTypes & {propIds: any}) => {
  //const query =
  //"select  status, count(*) as total from Complaints where clientId=? group by status";
  const query =
    `SELECT (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and propId in (${propIds}) and status = 1) AS new, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and propId in (${propIds}) AND status = 2) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and propId in (${propIds}) AND status = 3) AS closed FROM Complaints LIMIT 1`;

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

complaintDB.getComplaintCountsByStaffForClient = async ({
  clientId,
  staffs,
}: complaintsTypes & {staffs: any}) => {
  //const query =
  //"select  status, count(*) as total from Complaints where clientId=? group by status";
  const query =
    `SELECT (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and assignedTo in (${staffs}) and status = 1) AS new, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and assignedTo in (${staffs}) AND status = 2) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and assignedTo in (${staffs}) AND status = 3) AS closed FROM Complaints LIMIT 1`;

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

complaintDB.getComplaintCountsByCategoryForClient = async ({
  clientId,
  titles,
}: complaintsTypes & {titles: any}) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query =
    `SELECT (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and title in (${placeholders}) and status = 1) AS new, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and title in (${placeholders}) AND status = 2) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and title in (${placeholders}) AND status = 3) AS closed FROM Complaints LIMIT 1`;

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

complaintDB.getComplaintCountsForStaff = async ({
  clientId,
  propertiesIds,
}: complaintsTypes & { propertiesIds: string }) => {
  const query = `select  status, count(*) as total from Complaints where tenantId IS NOT NULL and clientId=? and propId in (${propertiesIds}) group by status`;

  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getPropComplaintCountsForStaff = async ({
  clientId,
  propertiesIds,
}: complaintsTypes & { propertiesIds: string }) => {
  const query = `select  status, count(*) as total from Complaints where tenantId IS NULL and clientId=? and propId in (${propertiesIds}) group by status`;

  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getComplaintCountsByPropForStaff = async ({
  clientId,
  propertiesIds,
  propIds
}: complaintsTypes & { propertiesIds: string; propIds: any }) => {
  const query = `select  status, count(*) as total from Complaints where tenantId IS NOT NULL and clientId=? and propId in (${propIds}) and propId in (${propertiesIds}) group by status`;

  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getComplaintCountsByStaffForStaff = async ({
  clientId,
  propertiesIds,
  staffs
}: complaintsTypes & { propertiesIds: string; staffs: any }) => {
  const query = `select  status, count(*) as total from Complaints where tenantId IS NOT NULL and clientId=? and assignedTo in (${staffs}) and propId in (${propertiesIds}) group by status`;

  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getComplaintCountsByCategoryForStaff = async ({
  clientId,
  propertiesIds,
  titles
}: complaintsTypes & { propertiesIds: string; titles: any }) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query = `select  status, count(*) as total from Complaints where tenantId IS NOT NULL and clientId=? and title in (${placeholders}) and propId in (${propertiesIds}) group by status`;

  const data = [clientId, ...titles];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getComplaintCountsForRegularStaff = async ({
  clientId,
  assignedTo,
  propertiesIds,
}: complaintsTypes & { propertiesIds: string }) => {
  const query = `select status, count(*) as total from Complaints where clientId=? and tenantId IS NOT NULL and propId in (${propertiesIds}) and assignedTo = ? group by status`;

  const data = [clientId, assignedTo];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getPropComplaintCountsForRegularStaff = async ({
  clientId,
  assignedTo,
  propertiesIds,
}: complaintsTypes & { propertiesIds: string }) => {
  const query = `select status, count(*) as total from Complaints where clientId=? and tenantId IS NULL and propId in (${propertiesIds}) and assignedTo = ? group by status`;

  const data = [clientId, assignedTo];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getComplaintCountsByPropForRegularStaff = async ({
  clientId,
  assignedTo,
  propertiesIds,
  propIds,
}: complaintsTypes & { propertiesIds: string; propIds: any }) => {
  const query = `select  status, count(*) as total from Complaints where clientId=? and tenantId IS NOT NULL and propId in (${propIds}) and propId in (${propertiesIds}) and assignedTo = ? group by status`;

  const data = [clientId, assignedTo];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};
complaintDB.getComplaintCountsByCategoryForRegularStaff = async ({
  clientId,
  assignedTo,
  propertiesIds,
  titles,
}: complaintsTypes & { propertiesIds: string; titles: any }) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query = `select  status, count(*) as total from Complaints where clientId=? and tenantId IS NOT NULL and title in (${placeholders}) and propId in (${propertiesIds}) and assignedTo = ? group by status`;

  const data = [clientId, ...titles, assignedTo];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getComplaintCountsForTenant = async ({
  tenantId,
  clientId,
}: complaintsTypes) => {
  const query =
    "select  status, count(*) as total from Complaints where tenantId=? and clientId = ? group by status";

  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getComplaintCountsForStaffForYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: complaintsTypes & { propertiesIds: string; year: any; month: any }) => {
  const query = `select  status, count(*) as total from Complaints where clientId=? and propId in (${propertiesIds}) and YEAR(createdAt) = ? and MONTH(createdAt)=? group by status`;

  const data = [clientId, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.getComplaintCountsByStaffIdForYearMonth = async ({
  clientId,
  assignedTo,
  year,
  month,
}: complaintsTypes & { year: any; month: any }) => {
  const query = `select  status, count(*) as total from Complaints where clientId=? and assignedTo =? and YEAR(createdAt) = ? and MONTH(createdAt)=? group by status`;

  const data = [clientId, assignedTo, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  let newRequest = 0;
  let assignedRequest = 0;
  let closedRequest = 0;
  if (rows?.length > 0) {
    for (let row of rows) {
      if (CONSTANTS.COMPLAINT_STATUS.PENDING == row.status) {
        newRequest = newRequest + Number(row.total);
      } else if (CONSTANTS.COMPLAINT_STATUS.ASSIGNED == row.status) {
        assignedRequest = assignedRequest + Number(row.total);
      } else {
        closedRequest = closedRequest + Number(row.total);
      }
    }
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  } else {
    return {
      new: newRequest,
      assigned: assignedRequest,
      closed: closedRequest,
    };
  }
};

complaintDB.removeByComplainId = async ({ id }: complaintsTypes) => {
  const query = "Delete from Complaints where id = ?";
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.getByPropIdandDateRangeAndFilter = async ({
  propId,
  startDate,
  endDate,
  status,
  assignedTo,
  title,
}: complaintsTypes & { startDate: string; endDate: string; title: any }) => {
  let query =
    "Select C.id, C.closingRemarks, C.reopenReason, C.raisedFor, C.raisedBy, C.staffNote, C.propId, C.img, C.resolvedAt, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.coAssignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as staffRole, S.mobile as staffMobile, S2.name as secondStaffName, S2.role as secondStaffRole, S2.mobile as secondStaffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo left join Staffs as S2 on S2.id = C.coAssignedTo where C.propId = ? and DATE(C.createdAt) BETWEEN ? and ? ";
  const data = [propId, startDate, endDate];

  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and C.title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

  if (assignedTo && assignedTo !== null && assignedTo !== undefined) {
    query += ` and (C.assignedTo = ? OR C.coAssignedTo = ?)`;
    data.push(assignedTo);
    data.push(assignedTo);
  }

  if (status && status !== null && status !== undefined) {
    query += ` and C.status = ?`;
    data.push(status);
  }

  query += ` order by C.id desc`;

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

complaintDB.getByPropIdandDateRangeandAssigned = async ({
  propId,
  startDate,
  endDate,
  assignedTo,
}: complaintsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select C.id, C.closingRemarks, C.raisedFor, C.raisedBy, C.staffNote, C.propId, C.img, C.resolvedAt, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as staffRole, S.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo where C.propId = ? and C.assignedTo = ? and DATE(C.createdAt) BETWEEN ? and ? order by C.id desc";
  const data = [propId, assignedTo, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByPropIdandDateRangeandStatus = async ({
  propId,
  status,
  startDate,
  endDate,
}: complaintsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select C.id, C.closingRemarks, C.raisedFor, C.raisedBy, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, R.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as StaffRole, S.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo where C.propId = ? and DATE(C.createdAt) BETWEEN ? and ? and C.status = ? order by C.id desc";
  const data = [propId, startDate, endDate, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByClientIdandDateRangeAndFilters = async ({
  clientId,
  startDate,
  endDate,
  title,
  assignedTo,
  status,
}: complaintsTypes & { startDate: string; endDate: string; title: any; }) => {
  let query =
    "Select C.id, C.closingRemarks, C.reopenReason, C.raisedFor, C.raisedBy, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.coAssignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as staffRole, S.mobile as staffMobile, S2.name as secondStaffName, S2.role as secondStaffRole, S2.mobile as secondStaffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo left join Staffs as S2 on S2.id = C.coAssignedTo where C.clientId = ? and DATE(C.createdAt) BETWEEN ? and ? ";
  let data = [clientId, startDate, endDate];

  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and C.title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

  if (assignedTo && assignedTo !== null && assignedTo !== undefined && assignedTo !== 0) {
    query += ` and (C.assignedTo = ? OR C.coAssignedTo = ?)`;
    data.push(assignedTo);
    data.push(assignedTo);
  }

  if (status && status !== null && status !== undefined) {
    query += ` and C.status = ?`;
    data.push(status);
  }

  query += ` order by C.id desc`;

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

complaintDB.getByClientIdAndFilters = async ({
  clientId,
  title,
  assignedTo,
  status,
  propId,
  pageNum,
  limit,
}: complaintsTypes & { startDate: string; endDate: string; title: any; pageNum: number; limit: number; }) => {
  //Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.assignedTo in (${staffs}) order by C.id desc  limit ?, ? `;
  let query =
    "Select C.id, C.closingRemarks, C.raisedFor, C.raisedBy, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.coAssignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile, S.name as staffName, S.role as staffRole, S.mobile as staffMobile, S2.name as secondStaffName, S2.role as secondStaffRole, S2.mobile as secondStaffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo left join Staffs as S2 on S2.id = C.coAssignedTo where C.clientId = ? ";
  let data: any = [clientId];

  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and C.title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

  if (propId && Array.isArray(propId) && propId.length > 0) {
    query += ` and C.propId IN (${propId.map(() => "?").join(",")})`;
    data.push(...propId);
  }

  // if (assignedTo && Array.isArray(assignedTo) && assignedTo.length > 0) {
  //   query += ` and C.assignedTo IN (${assignedTo.map(() => "?").join(",")})`;
  //   data.push(...assignedTo);
  // }

  if (assignedTo && Array.isArray(assignedTo) && assignedTo.length > 0) {
    query += ` AND (C.assignedTo IN (${assignedTo.map(() => "?").join(",")}) OR C.coAssignedTo IN (${assignedTo.map(() => "?").join(",")}))`;
    data.push(...assignedTo, ...assignedTo);
  }

  if (status && status !== null && status !== undefined) {
    query += ` and C.status = ?`;
    data.push(status);
  }

  const offset = (pageNum - 1) * limit;

  query += ` order by C.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;
};

complaintDB.getByClientIdandDateRangeandAssigned = async ({
  clientId,
  startDate,
  endDate,
  assignedTo,
}: complaintsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select C.id, C.closingRemarks, C.raisedFor, C.raisedBy, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as staffRole, S.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo where C.clientId = ? and C.assignedTo = ? and DATE(C.createdAt) BETWEEN ? and ? order by C.id desc";
  const data = [clientId, assignedTo, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByClientIdandDateRangeandStatus = async ({
  clientId,
  status,
  startDate,
  endDate,
}: complaintsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select C.id, C.closingRemarks, C.raisedFor, C.raisedBy, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, R.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as StaffRole, S.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo where C.clientId = ? and DATE(C.createdAt) BETWEEN ? and ? and C.status = ? order by C.id desc";
  const data = [clientId, startDate, endDate, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getCountByPropIdandDateRangeAndFilters = async ({
  propId,
  startDate,
  endDate,
  assignedTo,
  title,
}: complaintsTypes & { startDate: any; endDate: any; title: any; }) => {
  let query = `Select SUM(IF(status=1,1,0)) as new, SUM(IF(status=2,1,0)) as assigned, SUM(IF(status=4,1,0)) as reopened, SUM(IF(status=3,1,0)) as closed FROM Complaints WHERE propId = ? and date(createdAt) between ? and ? and tenantId is not null`;

  let data = [
    propId,
    startDate,
    endDate,
  ]
  
  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

  if (assignedTo && assignedTo !== null && assignedTo !== undefined && assignedTo !== 0) {
    query += ` and assignedTo = ?`;
    data.push(assignedTo);
  }
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

complaintDB.getCountByPropIdandDateRangeAndAssigned = async ({
  propId,
  startDate,
  endDate,
  assignedTo,
}: complaintsTypes & { startDate: any; endDate: any }) => {
  const query =
    "SELECT (SELECT COUNT(*) FROM Complaints WHERE propId = ? and assignedTo = ? and status = 1 and date(createdAt) between ? and ? and tenantId is not null) AS new, (SELECT COUNT(*) FROM Complaints WHERE propId = ? and assignedTo = ? AND status = 2 and date(createdAt) between ? and ? and tenantId is not null) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE propId = ? and assignedTo = ? AND status = 3 and date(createdAt) between ? and ? and tenantId is not null) AS closed FROM Complaints LIMIT 1";

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

complaintDB.getCountByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: complaintsTypes & { startDate: any; endDate: any }) => {
  const query =
    "SELECT (SELECT COUNT(*) FROM Complaints WHERE clientId = ? and status = 1 and date(createdAt) between ? and ? and tenantId is not null) AS new, (SELECT COUNT(*) FROM Complaints WHERE clientId = ? AND status = 2 and date(createdAt) between ? and ? and tenantId is not null) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE clientId = ? AND status = 3 and date(createdAt) between ? and ? and tenantId is not null) AS closed FROM Complaints LIMIT 1";

  const data = [
    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;
};

complaintDB.getCountByClientIdandDateRangeAndAssigned = async ({
  clientId,
  startDate,
  endDate,
  assignedTo,
}: complaintsTypes & { startDate: any; endDate: any }) => {
  const query =
    "SELECT (SELECT COUNT(*) FROM Complaints WHERE clientId = ? and assignedTo = ? and status = 1 and date(createdAt) between ? and ? and tenantId is not null) AS new, (SELECT COUNT(*) FROM Complaints WHERE clientId = ? and assignedTo = ? AND status = 2 and date(createdAt) between ? and ? and tenantId is not null) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE clientId = ? and assignedTo = ? AND status = 3 and date(createdAt) between ? and ? and tenantId is not null) AS closed FROM Complaints LIMIT 1";

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

complaintDB.getCountByClientIdandDateRangeAndFilters = async ({
  clientId,
  startDate,
  endDate,
  assignedTo,
  title,
}: complaintsTypes & { startDate: any; endDate: any; title: any }) => {

  let query = `Select SUM(IF(status=1,1,0)) as new, SUM(IF(status=2,1,0)) as assigned, SUM(IF(status=4,1,0)) as reopened, SUM(IF(status=3,1,0)) as closed FROM Complaints WHERE clientId = ? and date(createdAt) between ? and ? and tenantId is not null`;

  let data = [
    clientId,
    startDate,
    endDate,
  ]
  
  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

  if (assignedTo && assignedTo !== null && assignedTo !== undefined && assignedTo !== 0) {
    query += ` and assignedTo = ?`;
    data.push(assignedTo);
  }

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

complaintDB.getCountByClientIdFilters = async ({
  clientId,
  assignedTo,
  title,
  propId,
}: complaintsTypes & { startDate: any; endDate: any; title: any }) => {

  let query = `Select SUM(IF(status=1,1,0)) as new, SUM(IF(status=2,1,0)) as assigned, SUM(IF(status=4,1,0)) as reopened, SUM(IF(status=3,1,0)) as closed FROM Complaints WHERE clientId = ? and tenantId is not null`;

  let data = [
    clientId,
  ]
  
  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

  // if (assignedTo && Array.isArray(assignedTo) && assignedTo.length > 0) {
  //   query += ` and assignedTo IN (${assignedTo.map(() => "?").join(",")})`;
  //   data.push(...assignedTo);
  // }

  if (assignedTo && Array.isArray(assignedTo) && assignedTo.length > 0) {
  query += ` AND (assignedTo IN (${assignedTo.map(() => "?").join(",")}) OR coAssignedTo IN (${assignedTo.map(() => "?").join(",")}))`;
  data.push(...assignedTo, ...assignedTo);
}

  if (propId && Array.isArray(propId) && propId.length > 0) {
    query += ` and propId IN (${propId.map(() => "?").join(",")})`;
    data.push(...propId);
  }

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

complaintDB.getByRaisedByMobile = async ({
  clientId,
  searchVal,
}: complaintsTypes & { searchVal: string }) => {
  const query =
    "Select C.id, C.closingRemarks, C.reopenReason, C.raisedFor, C.raisedBy, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.coAssignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, R.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as StaffRole, S.mobile as staffMobile, S2.name as secondStaffName, S2.role as secondStaffRole, S2.mobile as secondStaffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo left join Staffs as S2 on S2.id = C.coAssignedTo where C.clientId = ? and T.mobile like ? order by C.id desc";
  const data = [clientId, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByRaisedByName = async ({
  clientId,
  searchVal,
}: complaintsTypes & { searchVal: string }) => {
  const query =
    "Select C.id, C.closingRemarks, C.reopenReason, C.raisedFor, C.raisedBy, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.coAssignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, R.floor, T.name as raisedByName, T.mobile as raisedByMobile, S.name as staffName, S.role as StaffRole, S.mobile as staffMobile, S2.name as secondStaffName, S2.role as secondStaffRole, S2.mobile as secondStaffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.assignedTo left join Staffs as S2 on S2.id = C.coAssignedTo where C.clientId = ? and T.name like ? order by C.id desc";
  const data = [clientId, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getComplaintCountForMobileSearch = async ({
  clientId,
  searchVal,
}: complaintsTypes & { searchVal: string }) => {
  const query =
    "Select (Select COUNT(C.id) as new from Complaints as C join Tenants as T on T.id = C.tenantId where C.clientId = ? and T.mobile like ? and C.status = 1) as new, (Select COUNT(C.id) as assigned from Complaints as C join Tenants as T on T.id = C.tenantId where C.clientId = ? and T.mobile like ? and C.status = 2) as assigned, (Select COUNT(C.id) as closed from Complaints as C join Tenants as T on T.id = C.tenantId where C.clientId = ? and T.mobile like ? and C.status = 3) as closed";
  const data = [
    clientId,
    `%${searchVal}%`,
    clientId,
    `%${searchVal}%`,
    clientId,
    `%${searchVal}%`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

complaintDB.getComplaintCountForNameSearch = async ({
  clientId,
  searchVal,
}: complaintsTypes & { searchVal: string }) => {
  const query =
    "Select (Select COUNT(C.id) as new from Complaints as C join Tenants as T on T.id = C.tenantId where C.clientId = ? and T.name like ? and C.status = 1) as new, (Select COUNT(C.id) as assigned from Complaints as C join Tenants as T on T.id = C.tenantId where C.clientId = ? and T.name like ? and C.status = 2) as assigned, (Select COUNT(C.id) as closed from Complaints as C join Tenants as T on T.id = C.tenantId where C.clientId = ? and T.name like ? and C.status = 3) as closed";
  const data = [
    clientId,
    `%${searchVal}%`,
    clientId,
    `%${searchVal}%`,
    clientId,
    `%${searchVal}%`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

complaintDB.getPropComplaintsByStaffIdAndPage = async ({
  assignedTo,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, C.floor from Complaints as C inner join Properties as P on P.id = C.propId where C.assignedTo = ? and C.tenantId is NULL order by C.id desc limit ?, ? ";

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

complaintDB.getByStaffFilterAndPageForClient = async ({
  clientId,
  staffs,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number; staffs: string }) => {
  const query =
    `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.assignedTo in (${staffs}) order by C.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;
};

complaintDB.getByCategoryFilterAndPageForClient = async ({
  clientId,
  titles,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number; titles: any }) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query =
    `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.title in (${placeholders}) order by C.id desc  limit ?, ? `;

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

complaintDB.getByPropertyFilterAndPageForClient = async ({
  clientId,
  propIds,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number; propIds: string }) => {
  const query =
    `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propIds}) order by C.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;
};

complaintDB.getByStaffFilterAndPageForAdmin = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  staffs
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  staffs: string;
}) => {
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propertiesIds}) and C.assignedTo in (${staffs}) order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByCategoryFilterAndPageForAdmin = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  titles
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  titles: any;
}) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propertiesIds}) and C.title in (${placeholders}) order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, ...titles, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByPropertyFilterAndPageForAdmin = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  propIds,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  propIds: string;
}) => {
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propertiesIds}) and C.propId in (${propIds}) order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByCategoryFilterAndPageForStaff = async ({
  assignedTo,
  titles,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number; titles: any; }) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query =
    `Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.title in (${placeholders}) order by C.id desc  limit ?, ? `;

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

complaintDB.getByPropertyFilterAndPageForStaff = async ({
  assignedTo,
  propIds,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number; propIds: string; }) => {
  const query =
    `Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.propId in (${propIds}) order by C.id desc  limit ?, ? `;

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

complaintDB.getComplaintsByPropStatusForClient = async ({
  clientId,
  status,
  propIds,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number; propIds: any }) => {
  const query =
    `Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? and C.propId in (${propIds}) order by C.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;
};

complaintDB.getComplaintsByStaffStatusForClient = async ({
  clientId,
  status,
  staffs,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number; staffs: any }) => {
  const query =
    `Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? and C.assignedTo in (${staffs}) order by C.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;
};

complaintDB.getComplaintsByCategoryStatusForClient = async ({
  clientId,
  status,
  titles,
  pageNum,
  limit,
}: complaintsTypes & { pageNum: number; limit: number; titles: any }) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query =
    `Select C.id, C.title, C.closingRemarks, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.status = ? and C.title in (${placeholders}) order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    status,
    ...titles,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByStaffFilterAndStatusAndPageForAdmin = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  staffs,
  status,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  staffs: string;
}) => {
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propertiesIds}) and C.assignedTo in (${staffs}) and C.status = ? order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, status, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByPropertyFilterAndStatusAndPageForAdmin = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  propIds,
  status,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  propIds: string;
}) => {
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propertiesIds}) and C.propId in (${propIds}) and C.status = ? order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, status, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByCategoryFilterAndStatusAndPageForAdmin = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  titles,
  status,
}: complaintsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  titles: any;
}) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query = `Select C.id, C.closingRemarks, C.title, C.img, C.propId, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propertiesIds}) and C.title in (${placeholders}) and C.status = ? order by C.id desc  limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, ...titles, status, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByPropertyFilterAndStatusAndPageForStaff = async ({
  assignedTo,
  propIds,
  pageNum,
  limit,
  status,
}: complaintsTypes & { pageNum: number; limit: number; propIds: string; }) => {
  const query =
    `Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.propId in (${propIds}) and C.status = ? order by C.id desc  limit ?, ? `;

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

complaintDB.getByCategoryFilterAndStatusAndPageForStaff = async ({
  assignedTo,
  titles,
  pageNum,
  limit,
  status,
}: complaintsTypes & { pageNum: number; limit: number; titles: any; }) => {
  const placeholders = titles.map(() => '?').join(', ');
  const query =
    `Select C.id, C.title, C.img, C.propId, C.resolvedAt, C.feedback, C.staffNote, C.rating, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, R.roomNum, C.floor, T.name as tenantName, T.mobile as tenantMobile from Complaints as C inner join Properties as P on P.id = C.propId inner join Tenants as T on T.id = C.tenantId inner join Rooms as R on R.id = C.roomId where C.assignedTo = ? and C.title in (${placeholders}) and C.status = ? order by C.id desc  limit ?, ? `;

  const offset: number = (pageNum - 1) * limit;
  const data = [assignedTo, ...titles, status, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.updateStaffNote = async ({
  id,
  staffNote,
}: complaintsTypes) => {
  const query =
    `Update Complaints set staffNote = ? where id = ?`;
  const data = [
    staffNote,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

complaintDB.getByClientIdAndDateRangeForReport = async ({
  clientId,
  startDate,
  endDate
}: complaintsTypes & {startDate: any; endDate: any}) => {
  const query =
    `Select C.id, C.clientId, C.tenantId, C.propId, C.roomId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.resolvedAt, C.raisedFor, C.staffNote, P.name as propName, S.name as assignedToName, T.name as tenantName, T.mobile as tenantMobile, R.roomNum from Complaints as C left join Properties as P on P.id = C.propId left join Staffs as S on S.id = C.assignedTo left join Tenants as T on T.id = C.tenantId left join Rooms as R on R.id = C.roomId where C.clientId = ? and DATE(C.createdAt) BETWEEN ? and ?`
  const data = [
    clientId,
    startDate,
    endDate
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getByClientIdAndProperyAndDateRangeForReport = async ({
  clientId,
  propId,
  startDate,
  endDate
}: complaintsTypes & { propId: any; startDate: any; endDate: any}) => {
  const query =
    `Select C.id, C.clientId, C.tenantId, C.propId, C.roomId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.resolvedAt, C.raisedFor, C.staffNote, P.name as propName, S.name as assignedToName, T.name as tenantName, T.mobile as tenantMobile, R.roomNum from Complaints as C left join Properties as P on P.id = C.propId left join Staffs as S on S.id = C.assignedTo left join Tenants as T on T.id = C.tenantId left join Rooms as R on R.id = C.roomId where C.clientId = ? and C.propId in (${propId.map(() => '?').join(',')}) and DATE(C.createdAt) BETWEEN ? and ?`
  const data = [
    clientId,
    ...propId,
    startDate,
    endDate
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

complaintDB.getForPropByClientIdAndDateRangeAndFilters = async ({
  clientId,
  startDate,
  endDate,
  assignedTo,
  title,
}: complaintsTypes & { startDate: string; endDate: string }) => {
  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  let query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${staff} and DATE(C.createdAt) BETWEEN ? and ? UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.raisedFor=2 and raisedBy=${client} and DATE(C.createdAt) BETWEEN ? and ? `;

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

  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and C.title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

  if (assignedTo && assignedTo !== null && assignedTo !== undefined && assignedTo !== 0) {
    query += ` and C.assignedTo = ?`;
    data.push(assignedTo);
  }

  query += ` ) as v order by id desc`;

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

complaintDB.getPropComplaintCountsForClientByDateRangeAndFilters = async ({
  clientId,
  startDate,
  endDate,
  assignedTo,
  title,
}: complaintsTypes & {startDate: string; endDate: string; title: any;}) => {
  let query = "Select SUM(IF(status=1,1,0)) as new, SUM(IF(status=2,1,0)) as assigned, SUM(IF(status=4,1,0)) as reopened, SUM(IF(status=3,1,0)) as closed FROM Complaints WHERE clientId = ? and DATE(createdAt) BETWEEN ? and ? and tenantId is NULL";

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

  if (assignedTo && assignedTo !== null && assignedTo !== undefined && assignedTo !== 0) {
    query += ` and assignedTo = ?`;
    data.push(assignedTo);
  }

  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

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

complaintDB.getForPropByClientIdAndDateRangeAndAssigned = async ({
  clientId,
  startDate,
  endDate,
  assignedTo,
}: complaintsTypes & { startDate: string; endDate: string; }) => {
  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  const query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.assignedTo = ? and C.raisedFor=2 and raisedBy=${staff} and DATE(C.createdAt) BETWEEN ? and ? UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.assignedTo = ? and C.raisedFor=2 and raisedBy=${client} and DATE(C.createdAt) BETWEEN ? and ?) as v order by id desc`;

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

complaintDB.getPropComplaintCountsForClientByDateRangeAndAssigned = async ({
  clientId,
  startDate,
  endDate,
  assignedTo,
}: complaintsTypes & {startDate: string; endDate: string;}) => {
  const query =
    "SELECT (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NULL and clientId = ? and assignedTo = ? and status = 1 and DATE(createdAt) BETWEEN ? and ?) AS new, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NULL and clientId = ? and assignedTo = ? AND status = 2 and DATE(createdAt) BETWEEN ? and ?) AS assigned, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NULL and  clientId = ? and assignedTo = ? AND status = 3 and DATE(createdAt) BETWEEN ? and ?) AS closed FROM Complaints LIMIT 1";

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

complaintDB.getForPropByPropIdAndDateRangeAndFilters = async ({
  clientId,
  propId,
  startDate,
  endDate,
  assignedTo,
  title,
}: complaintsTypes & { startDate: string; endDate: string; title: any; }) => {
  let client = CONSTANTS.USER_TYPE.CLIENT;
  let staff = CONSTANTS.USER_TYPE.STAFF;
  let query = `select * from (Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, S.role as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Staffs as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.propId = ? and C.raisedFor=2 and raisedBy=${staff} and DATE(C.createdAt) BETWEEN ? and ? UNION  Select C.id, C.closingRemarks, C.propId, C.img, C.resolvedAt, C.staffNote, C.feedback, C.rating, C.tenantId, C.title, C.description, C.assignedTo, C.status, C.createdAt, C.updatedAt, P.name as propertyName, S.name as raisedByName, 'NA' as raisedByRole, S.mobile as raisedByMobile, S1.name as staffName, S1.role as StaffRole, S1.mobile as staffMobile, O.name as ownerName from Complaints as C inner join Properties as P on P.id = C.propId  left join Clients as O on O.id = C.clientId left join Clients as S on S.id = C.raisedById left join Staffs as S1 on S1.id = C.assignedTo where C.clientId = ? and C.propId = ? and C.raisedFor=2 and raisedBy=${client} and DATE(C.createdAt) BETWEEN ? and ? `;

  const data = [
    clientId, 
    propId,
    startDate, 
    endDate,
    clientId, 
    propId,
    startDate, 
    endDate,
  ];

  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and C.title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

  if (assignedTo && assignedTo !== null && assignedTo !== undefined && assignedTo !== 0) {
    query += ` and C.assignedTo = ?`;
    data.push(assignedTo);
  }

  query += ` ) as v order by id desc`;

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

complaintDB.getPropComplaintCountsForPropertyByDateRangeAndFilters = async ({
  clientId,
  propId,
  startDate,
  endDate,
  assignedTo,
  title,
}: complaintsTypes & {startDate: string; endDate: string; title: any;}) => {
  let query = "Select SUM(IF(status=1,1,0)) as new, SUM(IF(status=2,1,0)) as assigned, SUM(IF(status=4,1,0)) as reopened, SUM(IF(status=3,1,0)) as closed FROM Complaints WHERE clientId = ? and propId = ? and DATE(createdAt) BETWEEN ? and ? and tenantId is NULL";

  const data = [
    clientId, 
    propId,
    startDate,
    endDate,
  ];

  if (assignedTo && assignedTo !== null && assignedTo !== undefined && assignedTo !== 0) {
    query += ` and assignedTo = ?`;
    data.push(assignedTo);
  }

  if (title && Array.isArray(title) && title.length > 0) {
    query += ` and title IN (${title.map(() => "?").join(",")})`;
    data.push(...title);
  }

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

complaintDB.getTenantAndPropPendingComplaintCountsForClient = async ({
  clientId,
}: complaintsTypes) => {
  //const query =
  //"select  status, count(*) as total from Complaints where clientId=? group by status";
  const query =
    "SELECT (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and status = 1) AS tenantPending, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NULL and clientId = ? and status = 1) AS propPending FROM Complaints LIMIT 1";

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

complaintDB.getTenantAndPropPendingComplaintCountsForStaff = async ({
  clientId,
  propertiesIds,
}: complaintsTypes & {propertiesIds: string;}) => {
  const query =
    `SELECT (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NOT NULL and clientId = ? and propId in (${propertiesIds}) and status = 1) AS tenantPending, (SELECT COUNT(*) FROM Complaints WHERE tenantId IS NULL and clientId = ? and propId in (${propertiesIds}) and status = 1) AS propPending FROM Complaints LIMIT 1`;

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

complaintDB.updateClosingRemarks = async ({
  id,
  closingRemarks,
}: complaintsTypes & { closingRemarks: string }) => {
  const query =
    "UPDATE Complaints SET closingRemarks = ? WHERE id = ?";
  const data = [closingRemarks, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

complaintDB.updateReopenReason = async ({
  id,
  reopenReason,
}: complaintsTypes & { reopenReason: string }) => {
  const query =
    "UPDATE Complaints SET reopenReason = ? WHERE id = ?";
  const data = [reopenReason, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};


complaintDB.getComplaintCountToStaffByStatus = async ({
  clientId,
  assignedTo,
  status
}: complaintsTypes) => {
  const query =
    "SELECT count(*) as total from Complaints where clientId=? and assignedTo=? and status=?";

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

complaintDB.getByClientIdAndTitle = async ({
  clientId,
  title,
  propId,
}: complaintsTypes) => {
  let query =
    "SELECT count(*) as total from Complaints where clientId=? and title=? and status != ?";

  const data = [
    clientId,
    title,
    CONSTANTS.COMPLAINT_STATUS.RESOLVED,
  ];

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

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

export default complaintDB;