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

const staffDB: any = {};

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

staffDB.getByPropId = async ({ propId }: staffTypes & propertyStaff) => {
  const query =
    "Select *  from PropertyStaff as PS inner join Staffs as S on S.id = PS.staffId where PS.propId = ? and S.status=1 order by PS.id desc";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getByPropIdForComplaints = async ({ propId }: staffTypes & propertyStaff) => {
  const query =
    "Select * from PropertyStaff as PS inner join Staffs as S on S.id = PS.staffId where PS.propId = ? and S.status=1 and S.role not in (?) order by PS.id desc";
  const data = [
    propId,
    CONSTANTS.STAFF_ROLES.SALESPERSON,
    // CONSTANTS.STAFF_ROLES.SECURITY_GUARD,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getByClientId = async ({ clientId }: staffTypes & propertyStaff) => {
  const query =
    "Select S.id, S.isSuperAdmin, S.isOnPayroll, S.lastLogin, S.clientId, S.role, S.reportingLongitude, S.commission, S.commissionType, S.reportingLatitude, S.name, S.mobile, S.status, S.gender, S.salary, S.permissions, S.createdAt, ? as userType from Staffs as S where  S.clientId=? order by S.id desc";
  const data = [CONSTANTS.USER_TYPE.STAFF, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getActiveByClientId = async ({
  clientId,
}: staffTypes & propertyStaff) => {
  const query =
    "Select S.id, S.isSuperAdmin, S.isOnPayroll, S.clientId, S.lastLogin, S.commission, S.commissionType, S.role, S.name, S.mobile, S.status, S.createdAt, ? as userType from Staffs as S where  S.clientId=? and S.status = ? order by S.id desc";
  const data = [
    CONSTANTS.USER_TYPE.STAFF,
    clientId,
    CONSTANTS.STAFF_STATUS.ACTIVE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getActiveByClientIdExcludingPartner = async ({
  clientId,
}: staffTypes & propertyStaff) => {
  const query =
    "Select S.id, S.isSuperAdmin, S.isOnPayroll, S.clientId, S.lastLogin, S.commission, S.commissionType, S.reportingLongitude, S.reportingLatitude, S.role, S.name, S.mobile, S.status, S.createdAt, ? as userType from Staffs as S where  S.clientId=? and S.status = ? and role != ? order by S.id desc";
  const data = [
    CONSTANTS.USER_TYPE.STAFF,
    clientId,
    CONSTANTS.STAFF_STATUS.ACTIVE,
    CONSTANTS.STAFF_ROLES.PARTNER,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getActiveByClientIdForAttendance = async ({
  clientId,
}: staffTypes & propertyStaff) => {
  const query =
    "Select S.id, S.isSuperAdmin, S.isOnPayroll, S.clientId, S.lastLogin, S.commission, S.commissionType, S.reportingLongitude, S.reportingLatitude, S.role, S.name, S.mobile, S.status, S.createdAt, ? as userType from Staffs as S where  S.clientId=? and S.status = ? and role != ? and isOnPayroll = 1 order by S.id desc";
  const data = [
    CONSTANTS.USER_TYPE.STAFF,
    clientId,
    CONSTANTS.STAFF_STATUS.ACTIVE,
    CONSTANTS.STAFF_ROLES.PARTNER,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getByClientIdAndPropId = async ({
  clientId,
  propId,
}: staffTypes & propertyStaff) => {
  const query =
    "Select S.id, S.isSuperAdmin, S.isOnPayroll, S.clientId, S.lastLogin, S.commission, S.commissionType, S.role, S.name, S.mobile, S.status, S.createdAt from Staffs as S left join PropertyStaff as PS on PS.staffId = S.id where PS.clientId=? and PS.propId = ? and S.status=1 and (S.permissions & 1024) != 0 order by S.id desc";
  const data = [clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.isAlreadyLinked = async ({
  clientId,
  propId,
  staffId,
}: staffTypes & propertyStaff) => {
  const query =
    "Select S.id, S.isSuperAdmin, S.isOnPayroll, S.clientId, S.lastLogin, S.commission, S.commissionType, S.role, S.name, S.mobile, S.status, S.createdAt from Staffs as S left join PropertyStaff as PS on PS.staffId = S.id where PS.clientId=? and PS.propId = ? and PS.staffId =?";
  const data = [clientId, propId, staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

staffDB.getAllByClientId = async ({ clientId, status }: staffTypes) => {
  const query =
    "Select * from Staffs where clientId = ? and status = ? order by id desc";
  const data = [clientId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getAllByClientIdForWeb = async ({ clientId, status, propIds, }: staffTypes & { propIds: any; }) => {
  let query =
    "Select * from Staffs as S where S.clientId = ? and S.status = ? ";
  const data = [clientId, status];

  if (propIds && propIds.length > 0) {
    query += ` and S.id in ( select PS.staffId from PropertyStaff as PS where PS.propId in (${propIds.map(() => '?').join(',')}))`;
    data.push(...propIds);
  }

  query += ` order by S.id desc`

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

staffDB.getAllByClientIdAndFilters = async ({
  clientId,
  status,
  roleFilters,
  propFilters
}: staffTypes & { roleFilters: any; propFilters: any }) => {
  // let query = `Select * from Staffs where clientId = ? and status = ?`;

  // let data = [clientId, status];

  let query = `SELECT * FROM Staffs WHERE clientId = ?`;
  let data: any[] = [clientId];

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

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

  if (propFilters && Array.isArray(propFilters) && propFilters.length > 0) {
    query += ` and (id IN (Select staffId from PropertyStaff where propId IN (${propFilters.map(() => '?').join(',')})) OR role = 7)`;
    data.push(...propFilters);
  }

  query += ` order by id desc`;

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

staffDB.getSearchResult = async ({
  clientId,
  status,
  id,
  searchVal,
}: staffTypes & { searchVal: string }) => {
  const query =
    "Select * from Staffs where clientId = ? and id != ? and status = ? and (name like ? or mobile like ?) order by id desc";
  const data = [clientId, id, status, `%${searchVal}%`, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getAllExcludingRequester = async ({
  clientId,
  id,
  status,
}: staffTypes) => {
  const query =
    "Select * from Staffs where clientId = ? and id != ? and status = ?  order by id desc";
  const data = [clientId, id, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getAllByFilterExcludingRequester = async ({
  clientId,
  id,
  status,
  roleFilters,
  propFilters,
}: staffTypes & { roleFilters: any; propFilters: any }) => {
  let query = `Select * from Staffs where clientId = ? and id != ? and status = ?`;
  const data = [clientId, id, status];

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

  if (propFilters && Array.isArray(propFilters) && propFilters.length > 0) {
    query += ` and (id IN (Select staffId from PropertyStaff where propId IN (${propFilters.map(() => '?').join(',')})) OR role = 7)`;
    data.push(...propFilters);
  }

  query += ` order by id desc`;

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

staffDB.getAllByClientIdExcludingRequester = async ({
  clientId,
  id,
}: staffTypes) => {
  const query =
    "Select * from Staffs where clientId = ? and id != ? order by id desc";
  const data = [clientId, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

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

staffDB.getPropertiesSearchResult = async ({
  staffId,
  clientId,
  pageNum,
  limit,
  searchVal,
}: staffTypes &
  propertyStaff & { pageNum: number; limit: number; searchVal: string }) => {
  const query =
    "Select P.id, P.gId, P.name, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from PropertyStaff as PS inner join Properties as P on P.id = PS.propId where PS.staffId = ? and PS.clientId =? and P.name like ? order by P.name asc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [staffId, clientId, `%${searchVal}%`, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getPropertiesByStatus = async ({
  staffId,
  clientId,
  pageNum,
  limit,
  status,
  locationId,
}: staffTypes & propertyStaff & { pageNum: number; limit: number; locationId: number }) => {
  let query =
    "Select P.id, P.gId, P.locationId, P.name, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from PropertyStaff as PS inner join Properties as P on P.id = PS.propId where PS.staffId = ? and PS.clientId =? and P.status = ? ";
  const offset: number = (pageNum - 1) * limit;
  const data: any = [staffId, clientId, status,];

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

  query += ` order by P.name asc limit ?, ?`;
  data.push(`${offset}`);
  data.push(`${limit}`);

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

staffDB.getPropertiesByStatusAndBank = async ({
  staffId,
  clientId,
  pageNum,
  limit,
  status,
}: staffTypes & propertyStaff & { pageNum: number; limit: number }) => {
  const query =
    "Select P.id, P.gId, P.name, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.longitude, P.latitude, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from PropertyStaff as PS inner join Properties as P on P.id = PS.propId where PS.staffId = ? and PS.clientId =? and P.status = ? and P.bankId IS NULL order by PS.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [staffId, clientId, status, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getProperties = async ({
  staffId,
  clientId,
  pageNum,
  limit,
  locationId,
}: staffTypes & propertyStaff & { pageNum: number; limit: number; locationId: any; }) => {
  let query =
    "Select P.id, P.gId, P.locationId, P.name, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from PropertyStaff as PS inner join Properties as P on P.id = PS.propId where PS.staffId = ? and PS.clientId =? and P.status != ? ";
  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    staffId,
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

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

  query += ` order by P.name asc limit ?, ?`;
  data.push(`${offset}`);
  data.push(`${limit}`);

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

staffDB.getByMobile = async ({ mobile }: staffTypes) => {
  mobile = mobile.toString().substring(mobile.length - 10);

  const query = "Select * from Staffs where mobile = ? order by status";
  const data = [mobile];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

staffDB.getByMobileAndClientId = async ({ mobile, clientId }: staffTypes) => {
  mobile = mobile
    .toString()
    .trim()
    .substring(mobile.length - 10);
  const query = "Select * from Staffs where mobile = ? and clientId = ?";
  const data = [mobile, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

staffDB.getActiveByMobileAndClientId = async ({ mobile, clientId }: staffTypes) => {
  mobile = mobile
    .toString()
    .trim()
    .substring(mobile.length - 10);
  const query = "Select * from Staffs where mobile = ? and clientId = ? and status = ?";
  const data = [mobile, clientId, CONSTANTS.STAFF_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

staffDB.getByMobileAndClientIdAndStatus = async ({ mobile, clientId, status }: staffTypes) => {
  mobile = mobile
    .toString()
    .trim()
    .substring(mobile.length - 10);
  const query = "Select * from Staffs where mobile = ? and clientId = ? and status = ?";
  const data = [mobile, clientId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.validateIfActiveWithOtherClient = async ({ mobile, clientId }: staffTypes) => {
  mobile = mobile
    .toString()
    .trim()
    .substring(mobile.length - 10);
  const query = "Select * from Staffs where mobile = ? and clientId != ? and status = ?";
  const data = [mobile, clientId, CONSTANTS.STAFF_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

  staffDB.create = async ({
    clientId,
    role,
    name,
    mobile,
    gender,
    salary,
    commission,
    commissionType,
    isOnPayroll,
    isSuperAdmin=0,
  }: staffTypes & { isOnPayroll?: number }) => {
  const query =
    "Insert into Staffs (clientId, role, isSuperAdmin, name, mobile, gender, salary, commission, commissionType, isOnPayroll) values (?,?,?,?,?,?,?,?,?,?)";
  const data = [clientId, role, isSuperAdmin, name, mobile, gender, salary, commission, commissionType, isOnPayroll];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffDB.edit = async ({
  clientId,
  role,
  isSuperAdmin,
  name,
  gender,
  salary,
  id,
  commission,
  commissionType,
  isOnPayroll
}: staffTypes& { isOnPayroll?: number }) => {
  const query =
    "Update Staffs set role=?, isSuperAdmin=?, name=?, gender=?, salary=?, commission=?, commissionType=?, isOnPayroll=? where clientId=? and id=?";
  const data = [role, isSuperAdmin, name, gender, salary, commission, commissionType, isOnPayroll, clientId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffDB.updateStatus = async ({ clientId, id, status }: staffTypes) => {
  const query = "Update Staffs set status = ? where clientId=? and id=?";
  const data = [status, clientId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffDB.updateAppVersion = async ({ id, currentAppVersion }: staffTypes) => {
  const query = "Update Staffs set currentAppVersion = ? where id = ?";
  const data = [currentAppVersion, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

staffDB.updateRegId = async ({ regId, id }: staffTypes) => {
  const query = "Update Staffs set regId = ? where id=?";
  const data = [regId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffDB.link = async ({
  clientId,
  propId,
  staffId,
}: staffTypes & propertyStaff) => {
  const query =
    "Insert into PropertyStaff (clientId, propId, staffId) values (?,?,?)";
  const data = [clientId, propId, staffId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffDB.unlink = async ({
  clientId,
  propId,
  staffId,
}: staffTypes & propertyStaff) => {
  const query =
    "Delete from PropertyStaff where clientId =? and propId =? and staffId =?";
  const data = [clientId, propId, staffId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffDB.unlinkAll = async ({
  clientId,
  staffId,
}: staffTypes & propertyStaff) => {
  const query =
    "Delete from PropertyStaff where clientId =? and staffId =?";
  const data = [clientId, staffId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

staffDB.getListWithStaffIdAndClientId = async ({
  id,
  clientId,
}: staffTypes) => {
  const query =
    "Select P.id, P.name, P.gId, P.type, P.tenantPreference from Properties as P inner join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status != ? order by P.name asc;";
  const data = [clientId, id, CONSTANTS.PROPERTY_STATUS.DELETED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getActiveListWithStaffIdAndClientId = async ({
  id,
  clientId,
}: staffTypes) => {
  const query =
    "Select P.id, P.name, P.gId, P.type, P.tenantPreference, P.locationId from Properties as P inner join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status = ? order by P.name asc;";
  const data = [clientId, id, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.updateStaffDevice = async ({ mobile, device }: staffTypes) => {
  const query = "Update Staffs set device = ? where mobile = ? order by status limit 1";
  const data = [device, mobile];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

staffDB.updateStaffPermissions = async ({ id, permissions }: staffTypes) => {
  const query = "Update Staffs set permissions = ? where id = ?";
  const data = [permissions, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

staffDB.getByClientIdAndRole = async ({ clientId, role }: staffTypes) => {
  const query = "Select * from Staffs where clientId = ? and role = ?";
  const data = [clientId, role];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

// staffDB.getByClientIdAndRoleWithStatus = async ({ clientId, role, status }: staffTypes) => {
//   const query = "Select * from Staffs where clientId = ? and role = ? and status = ?";
//   const data = [clientId, role, status];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

staffDB.getByClientIdAndRoleAndProperty = async ({ clientId, role, status, propId }: staffTypes & { propId: number }) => {
  const query = "Select S.* from Staffs as S inner join PropertyStaff as PS on S.id = PS.staffId where S.clientId = ? and S.role = ? and S.status = ? and PS.propId = ?";
  const data = [clientId, role, status, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getSearchResultByName = async ({ clientId, name }: staffTypes) => {
  const query =
    "Select * from Staffs where clientId = ? and name like ? order by id desc";
  const data = [clientId, `%${name}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getByDateAndAttendance = async ({
  clientId,
  date,
  attendance,
}: staffTypes & { date: string; attendance: number }) => {
  let query =
    "Select S.id, S.lastLogin, S.clientId, S.reportingLongitude, S.reportingLatitude, S.role, S.name, S.mobile, S.status, S.gender, S.salary, S.permissions, S.createdAt, 3 as userType from Staffs as S join StaffAttendance as SA on S.id = SA.staffId where S.clientId = ? and Date(SA.createdAt) = ?";
  let data = [clientId, date];
  if (attendance === 0) {
    query =
      "Select S.id, S.clientId, S.role, S.name, S.mobile, S.status, S.gender, S.salary, S.permissions, S.createdAt, 3 as userType from Staffs as S where S.clientId = ? and S.id NOT IN (select SA.staffId from StaffAttendance as SA where Date(SA.createdAt) = ?)";
    data = [clientId, date];
  }
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.updateLastLogin = async ({ id }: staffTypes) => {
  const query = "Update Staffs set lastLogin = now() where id = ?";
  const data = [id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

staffDB.updatePersonalInfo = async ({
  id,
  name,
  gender,
  address,
  aadharNumber,
}: staffTypes) => {
  const query =
    "Update Staffs set name = ?, gender = ?, address = ?, aadharNumber = ? where id = ?";
  const data = [name, gender, address, aadharNumber, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};


staffDB.updateIsVerified = async ({ id, isVerified }: staffTypes) => {
  const query = "Update Staffs set isVerified = ? where id = ?";
  const data = [isVerified, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

staffDB.editReportingLocation = async ({
  id,
  clientId,
  reportingLongitude,
  reportingLatitude
}: staffTypes) => {
  const query =
    "Update Staffs set reportingLongitude = ?, reportingLatitude = ? where clientId = ? and id = ?";
  const data = [reportingLongitude, reportingLatitude, clientId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffDB.updateDigiLockerIds = async ({
  verificationId,
  referenceId,
  id,
}: staffTypes) => {
  const query =
    "Update Staffs set verificationId = ?, referenceId = ? where id = ?";
  const data = [verificationId, referenceId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

staffDB.getByVerificationId = async ({ verificationId }: staffTypes) => {
  const query = "Select * from Staffs where verificationId = ?";
  const data = [verificationId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

staffDB.updatePersonalInfoX = async ({
  id,
  name,
  gender,
  address,
  aadharNumber,
  dob,
  fatherName
}: staffTypes) => {
  const query =
    "Update Staffs set name = ?, gender = ?, address = ?, aadharNumber = ?, dob=?, fatherName=? where id = ?";
  const data = [name, gender, address, aadharNumber, dob, fatherName, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

staffDB.getByClientIdAndSearchVal = async ({
  clientId,
  searchVal,
  searchType,
}: staffTypes & { searchVal: string; searchType: string; }) => {

  const data: any = [clientId]
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = ` and mobile like ?`;
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = ` and name like ?`;
    data.push(`%${searchVal}%`);
  }
  const query = `Select * from Staffs where clientId = ? ${filterCondition} order by id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getCurMonthPendingSalary = async ({
  clientId,
}: staffTypes) => {
  const query = `Select * from Staffs where clientId = ? and id not in (select paidTo from Expenses where clientId = ? and paidToUserType = ? and Month(dueDate) = Month(CURDATE()) and Year(dueDate) = Year(CURDATE()) and isPaid = 1) order by id desc`;

  const data = [
    clientId,
    clientId,
    CONSTANTS.USER_TYPE.STAFF
  ];

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

staffDB.getSummaryForClient = async ({
  clientId,
}: staffTypes) => {
  const query = `Select Count(id) as total, COALESCE(SUM(CASE WHEN status = ? THEN 1 ELSE 0 END), 0) AS active, COALESCE(SUM(CASE WHEN status = ? THEN 1 ELSE 0 END), 0) AS inactive from Staffs where clientId = ?`;

  const data = [
    CONSTANTS.STAFF_STATUS.ACTIVE,
    CONSTANTS.STAFF_STATUS.INACTIVE,
    clientId,
  ];

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

};

staffDB.getStaffAccounts = async ({
  mobile,
}: staffTypes) => {
  const query = `Select S.id, S.role, S.isSuperAdmin, IFNULL(C.businessName, C.name) as clientName, (select COUNT(P.id) from Properties as P join PropertyStaff as PS on PS.propId = P.id where P.clientId = S.clientId and PS.staffId = S.id and P.status = ?) AS propertyCount, (select Count(B.id) from Beds as B join Rooms as R on B.roomId = R.id join Properties as P on R.propId = P.id join PropertyStaff as PS on PS.propId = P.id where P.clientId = S.clientId and PS.staffId = S.id and P.status = ? and R.status != ?) as totalBeds, (select Count(*) from Beds as B join Rooms as R on B.roomId = R.id join Properties as P on R.propId = P.id join PropertyStaff as PS on PS.propId = P.id where P.clientId = S.clientId and PS.staffId = S.id and P.status = ? and R.status != ? and B.status != ?) as totalOccupiedBeds, (select Count(DISTINCT O.tenantId) from Occupancies as O join Properties as P on O.propId = P.id join PropertyStaff as PS on PS.propId = P.id where O.clientId = S.clientId and PS.staffId = S.id and O.status not in (?, ?)) as tenantCount from Staffs as S join Clients as C on S.clientId = C.id where S.mobile = ? and S.status = ?`;
  const data = [
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.BED_STATUS.VACANT,
    CONSTANTS.OCCUPANCY_STATUS.PENDING,
    CONSTANTS.OCCUPANCY_STATUS.REQUESTED,
    mobile,
    CONSTANTS.STAFF_STATUS.ACTIVE,
  ];

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

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

staffDB.getByPropertyId = async ({ landlordId }: staffTypes & { landlordId: number }) => {
  const query = "Select S.id, S.name, S.gender, S.mobile, S.role, P.name as propertyName, S.createdAt from Staffs as S inner join PropertyStaff as PS on S.id = PS.staffId INNER JOIN PropertyLease as PL on PL.propId = PS.propId INNER JOIN Properties as P ON P.id=PL.propId where PL.landlordId = ? and S.status = 1;";
  const data = [landlordId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};


staffDB.getSOSStaffsList = async ({ clientId, propId }: staffTypes & { propId: number }) => {
  const query = "select S.id, S.name, S.regId from PropertyStaff as PS INNER JOIN Staffs as S On S.id=PS.staffId  where PS.clientId =? and PS.propId = ? and S.isOnPayroll=1 and status=1 and role !=? UNION Select id, name, regId from Staffs where clientId =? and status=1 and role = ?";
  const data = [clientId, propId, CONSTANTS.STAFF_ROLES.PARTNER, clientId, CONSTANTS.STAFF_ROLES.PARTNER];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

staffDB.getSuperAdminByClientId = async ({ clientId }: staffTypes & { clientId: number }) => {
  const query = "Select id, name, regId from Staffs where clientId = ? and role = ? and status = ? and isSuperAdmin = ?";
  const data = [clientId, CONSTANTS.STAFF_ROLES.ADMIN, CONSTANTS.STAFF_STATUS.ACTIVE, 1];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};


export default staffDB;