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

const tenantAttendanceDB: any = {};

tenantAttendanceDB.getByTenantIdForMonth = async ({
  tenantId,
  date,
}: tenantAttendanceTypes & { date: string }) => {
  const query =
    "Select TA.id, TA.clientId, TA.tenantId, TA.propId as propId, TA.roomId, IFNULL(TA.attendance, 0) as status, DATE_FORMAT(TA.attendanceDate, '%Y-%m-%d') as attendanceDate, Time(TA.attendanceDate) as attendanceTime , T.name, P.name as propName, (Select value from Documents where tenantId = TA.tenantId and propId = P.id and type = ? order by id desc limit 1) as profilePicture from TenantAttendance as TA join Tenants as T on TA.tenantId = T.id join Properties as P on TA.propId = P.id where TA.tenantId = ? and MONTH(TA.attendanceDate) = MONTH(?) and YEAR(TA.attendanceDate) = YEAR(?) order by TA.attendanceDate desc";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI,tenantId, date, date];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

// tenantAttendanceDB.getByClientIdAndDate = async ({
//   clientId,
//   date,
// }: tenantAttendanceTypes & { date: string }) => {
//   const query =
//     "Select TA.id, TA.clientId, TA.tenantId, TA.propId, TA.attendance, DATE(TA.createdAt) as attendanceDate, Time(TA.createdAt) as attendanceTime, P.name as propName from tenantAttendance as TA join Properties as P on TA.propId = P.id where TA.clientId = ? and Date(TA.createdAt) = ? order by TA.createdAt desc";
//   const data = [clientId, date];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows.length > 0) return rows;
//   else return [];
// };

tenantAttendanceDB.markAttendance = async ({
  clientId,
  tenantId,
  propId,
  roomId,
  attendance,
  attendanceDate,
}: tenantAttendanceTypes & {
  attendance: number;
}) => {
  const query =
    "INSERT INTO TenantAttendance (clientId, tenantId, propId, roomId, attendance, attendanceDate) VALUES (?, ?, ?, ?, ?, ?);";
  const data = [clientId, tenantId, propId, roomId, attendance, attendanceDate];
  const [result] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

tenantAttendanceDB.updateAttendance = async ({
  attendance,
  id,
}: tenantAttendanceTypes & {
  attendance: number;
}) => {
  const query = `
    UPDATE TenantAttendance
    SET attendance = ?
    WHERE id= ?;
  `;
  const data = [
    attendance,
    id,
  ];

  await DB.execute<ResultSetHeader>(query, data);
  return true;
};

tenantAttendanceDB.isMarkedToday = async ({
  tenantId,
  attendanceDate,
}: tenantAttendanceTypes) => {
  const query =
    "SELECT id, attendance FROM TenantAttendance WHERE tenantId = ? AND DATE(attendanceDate) = DATE(?)";
  const data = [tenantId, attendanceDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  else return false;
};

tenantAttendanceDB.getAttendanceStatsForClientAndDate = async ({
  clientId,
  date,
  pageNum,
  limit,
}: tenantAttendanceTypes & { date: string; pageNum: any; limit: any }) => {
  const query = `SELECT 
    SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
    SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
    SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
    SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
    COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent, 
    Count(distinct(O.tenantId)) as totalTenants, 
    P.name AS propName, P.streetAddress as address, P.id as id FROM (select DISTINCT tenantId, propId from Occupancies where clientId = ?) as O JOIN Properties AS P ON O.propId = P.id LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) GROUP BY P.id ORDER BY P.name limit ?, ?;`;
  const offset: number = (pageNum - 1) * limit;
  const data =[ 
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId, 
    date, 
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.getAttendanceStatsForClientAndDateForWeb = async ({
  clientId,
  date,
}: tenantAttendanceTypes & { date: string; }) => {
  const query = `SELECT 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
  COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent, 
  Count(distinct(O.tenantId)) as totalTenants, 
  P.name AS propName, P.address as address, P.id as id FROM (select DISTINCT tenantId, propId from Occupancies where clientId = ?) AS O JOIN Properties AS P ON O.propId = P.id LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) GROUP BY P.id ORDER BY P.name;`;
  const data =[ 
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId,
    date, 
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.getAttendanceStatsForClientAndDateAndSearchVal = async ({
  clientId,
  date,
  pageNum,
  limit,
  searchVal,
}: tenantAttendanceTypes & { date: string; pageNum: any; limit: any; searchVal: any }) => {
  const query = `SELECT 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
  COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent, 
  Count(distinct(O.tenantId)) as totalTenants, 
  P.name AS propName, P.streetAddress as address, P.id as id FROM (select DISTINCT tenantId, propId from Occupancies where clientId = ?) AS O JOIN Properties AS P ON O.propId = P.id LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE P.name like ? GROUP BY P.id ORDER BY P.name limit ?, ?;`;
  const offset: number = (pageNum - 1) * limit;
  const data =[ 
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId, 
    date, 
    `%${searchVal}%`, 
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.getAttendanceStatsForClientAndDateAndSearchValForWeb = async ({
  clientId,
  date,
  searchVal,
}: tenantAttendanceTypes & { date: string; searchVal: any }) => {
  const query = `SELECT 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
  COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent, 
  Count(distinct(O.tenantId)) as totalTenants, 
  P.name AS propName, P.address as address, P.id as id FROM (select DISTINCT tenantId, propId from Occupancies where clientId = ?) AS O JOIN Properties AS P ON O.propId = P.id LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE P.name like ? GROUP BY P.id ORDER BY P.name;`;
  const data =[ 
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId, 
    date, 
    `%${searchVal}%`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.getAttendanceStatsForStaffAndDate = async ({
  clientId,
  staffId,
  date,
  pageNum,
  limit,
}: tenantAttendanceTypes & {
  staffId: number;
  date: string;
  pageNum: any;
  limit: any;
}) => {
  const query = `SELECT 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
  COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent, 
  Count(distinct(O.tenantId)) as totalTenants, 
  P.name AS propName, P.streetAddress as address, P.id as id FROM (select DISTINCT tenantId, propId from Occupancies where clientId = ?) AS O JOIN Properties AS P ON O.propId = P.id join PropertyStaff as PS on P.id = PS.propId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE PS.staffId = ? GROUP BY P.id ORDER BY P.name limit ?, ?;`;
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId, 
    date, 
    staffId, 
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.getAttendanceStatsForStaffAndDateForWeb = async ({
  clientId,
  staffId,
  date,
}: tenantAttendanceTypes & {
  staffId: number;
  date: string;
}) => {
  const query = `SELECT 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
  COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent, 
  Count(distinct(O.tenantId)) as totalTenants, 
  P.name AS propName, P.address as address, P.id as id FROM (select DISTINCT tenantId, propId from Occupancies where clientId = ?) AS O JOIN Properties AS P ON O.propId = P.id join PropertyStaff as PS on P.id = PS.propId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE PS.staffId = ? GROUP BY P.id ORDER BY P.name;`;
  const data = [
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId, 
    date, 
    staffId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.getAttendanceStatsForStaffAndDateAndSearchVal = async ({
  clientId,
  staffId,
  date,
  pageNum,
  limit,
  searchVal,
}: tenantAttendanceTypes & {
  staffId: number;
  date: string;
  pageNum: any;
  limit: any;
  searchVal: any;
}) => {
  const query = `SELECT 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
  COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent, 
  Count(distinct(O.tenantId)) as totalTenants, 
  P.name AS propName, P.streetAddress as address, P.id as id FROM (select DISTINCT tenantId, propId from Occupancies where clientId = ?) AS O JOIN Properties AS P ON O.propId = P.id join PropertyStaff as PS on P.id = PS.propId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE PS.staffId = ? and P.name like ? GROUP BY P.id ORDER BY P.name limit ?, ?;`;
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId, 
    date, 
    staffId, 
    `%${searchVal}%`, 
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.getAttendanceStatsForStaffAndDateAndSearchValForWeb = async ({
  clientId,
  staffId,
  date,
  searchVal,
}: tenantAttendanceTypes & {
  staffId: number;
  date: string;
  searchVal: any;
}) => {
  const query = `SELECT 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
  COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent, 
  Count(distinct(O.tenantId)) as totalTenants, 
  P.name AS propName, P.address as address, P.id as id FROM (select DISTINCT tenantId, propId from Occupancies where clientId = ?) AS O JOIN Properties AS P ON O.propId = P.id join PropertyStaff as PS on P.id = PS.propId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE PS.staffId = ? and P.name like ? GROUP BY P.id ORDER BY P.name;`;
  const data = [
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId, 
    date, 
    staffId, 
    `%${searchVal}%`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.ListAttendanceForProperty = async ({
  clientId,
  propId,
  date,
  pageNum,
  limit,
}: tenantAttendanceTypes & { date: string; pageNum: any; limit: any }) => {
  // const query = `SELECT DISTINCT T.id as tenantId, T.mobile as tenantMobile, T.name AS tenantName, IFNULL(TA.attendance, 0) as status, CASE WHEN TA.attendance is null THEN 0 ELSE 1 END as isAttendanceMarked, P.type as propType, R.roomNum as roomNum, O.kycStatus, O.floor as floor, O.flatId as flatId, IFNULL(F.name, O.floor) as flatFloorName, F.name as flatName, (Select value from Documents where tenantId = O.tenantId and propId = P.id and type = ? order by id desc limit 1) as profilePicture, P.name as propName FROM Occupancies AS O JOIN Properties AS P ON O.propId = P.id join Rooms as R on R.id = O.roomId left join Flats as F on F.id = O.flatId join Tenants as T on T.id = O.tenantId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE O.clientId = ? and O.propId = ? order by CASE WHEN O.flatId IS NOT NULL THEN CASE WHEN F.name REGEXP '^[0-9]+$' THEN CAST(F.name AS UNSIGNED) ELSE 999999 END ELSE CAST(O.floor AS UNSIGNED) END, F.name, R.roomNum limit ?, ?;`;
  const query = `WITH RankedOccupancies AS (SELECT O.*, R.roomNum, F.name AS flatName, ROW_NUMBER() OVER (PARTITION BY O.tenantId ORDER BY O.id DESC) AS rn FROM Occupancies O JOIN Rooms R ON R.id = O.roomId LEFT JOIN Flats F ON F.id = O.flatId WHERE O.clientId = ? AND O.propId = ?), PagedOccupancies AS (SELECT * FROM RankedOccupancies WHERE rn = 1 ORDER BY CASE WHEN flatId IS NOT NULL THEN CASE WHEN flatName REGEXP '^[0-9]+$' THEN CAST(flatName AS UNSIGNED) ELSE 999999 END ELSE CAST(floor AS UNSIGNED) END, flatName, roomNum, tenantId LIMIT ?, ?) SELECT T.id AS tenantId, T.mobile AS tenantMobile, T.name AS tenantName, IFNULL(TA.attendance,0) AS status, CASE WHEN TA.attendance IS NULL THEN 0 ELSE 1 END AS isAttendanceMarked, P.type AS propType, O.roomNum, O.kycStatus, O.floor, O.flatId, IFNULL(O.flatName,O.floor) AS flatFloorName, O.flatName, (SELECT value FROM Documents WHERE tenantId = O.tenantId AND propId = P.id AND type = ? ORDER BY id DESC LIMIT 1) AS profilePicture, P.name AS propName FROM PagedOccupancies O JOIN Properties P ON P.id = O.propId JOIN Tenants T ON T.id = O.tenantId LEFT JOIN TenantAttendance TA ON TA.tenantId = O.tenantId AND DATE(TA.attendanceDate) = DATE(?)`;
  const offset: number = (pageNum - 1) * limit;
  //const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, date, clientId, propId, `${offset}`, `${limit}`];
  const data = [clientId, propId, `${offset}`, `${limit}`, CONSTANTS.DOCUMENT_TYPES.SELFI, date];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.ListAttendanceForPropertyForWeb = async ({
  clientId,
  propId,
  date,
}: tenantAttendanceTypes & { date: string; }) => {
  const query = `SELECT DISTINCT T.id as tenantId, T.name AS tenantName, IFNULL(TA.attendance, 0) as status, CASE WHEN TA.attendance is null THEN 0 ELSE 1 END as isAttendanceMarked, P.type as propType, R.roomNum as roomNum, O.floor as floor, O.flatId as flatId, (Select value from Documents where tenantId = O.tenantId and propId = P.id and type = ? order by id desc limit 1) as profilePicture, P.name as propName FROM Occupancies AS O JOIN Properties AS P ON O.propId = P.id join Rooms as R on R.id = O.roomId join Tenants as T on T.id = O.tenantId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE O.clientId = ? and O.propId = ? order by T.name;`;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, date, clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.getAttendanceCountForProperty = async ({
  clientId,
  propId,
  date,
}: tenantAttendanceTypes & { date: string; pageNum: any; limit: any }) => {
  const query = `SELECT 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS present, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS lateNight, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS outOfStation, 
  SUM(CASE WHEN TA.attendance = ? THEN 1 ELSE 0 END) AS nightOut, 
  COUNT(DISTINCT CASE WHEN (TA.id IS NULL OR TA.attendance = ?) THEN O.tenantId END) AS absent FROM (select DISTINCT tenantId, propId, roomId from Occupancies where clientId = ?) AS O JOIN Properties AS P ON O.propId = P.id join Rooms as R on R.id = O.roomId join Tenants as T on T.id = O.tenantId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE O.propId = ?;`;
  const data = [
    CONSTANTS.ATTENDANCE.PRESENT,
    CONSTANTS.ATTENDANCE.LATE_NIGHT,
    CONSTANTS.ATTENDANCE.OUT_OF_STATION,
    CONSTANTS.ATTENDANCE.NIGHT_OUT,
    CONSTANTS.ATTENDANCE.ABSENT,
    clientId, 
    date, 
    propId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  else return [];
};

tenantAttendanceDB.ListAttendanceForPropertyWithSearch = async ({
  clientId,
  propId,
  date,
  pageNum,
  limit,
  searchVal,
}: tenantAttendanceTypes & {
  date: string;
  pageNum: any;
  limit: any;
  searchVal: string;
}) => {
  //const query = `SELECT DISTINCT T.id as tenantId, T.name AS tenantName, IFNULL(TA.attendance, 0) as status, P.type as propType, R.roomNum as roomNum, O.floor as floor, O.flatId as flatId, (Select value from Documents where tenantId = O.tenantId and propId = P.id and type = ? order by id desc limit 1) as profilePicture, P.name as propName FROM Occupancies AS O JOIN Properties AS P ON O.propId = P.id join Rooms as R on R.id = O.roomId join Tenants as T on T.id = O.tenantId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE O.clientId = ? and O.propId = ? and T.name like ? order by T.name limit ?, ?;`;
  const query = `WITH RankedOccupancies AS (SELECT O.*, ROW_NUMBER() OVER (PARTITION BY O.tenantId ORDER BY O.id DESC) AS rn FROM Occupancies O WHERE O.clientId = ? AND O.propId = ?) SELECT T.id AS tenantId, T.name AS tenantName, IFNULL(TA.attendance, 0) AS status, P.type AS propType, R.roomNum AS roomNum, O.floor AS floor, O.flatId AS flatId, (SELECT value FROM Documents WHERE tenantId = O.tenantId AND propId = P.id AND type = ? ORDER BY id DESC LIMIT 1) AS profilePicture, P.name AS propName FROM RankedOccupancies O JOIN Properties P ON O.propId = P.id JOIN Rooms R ON R.id = O.roomId JOIN Tenants T ON T.id = O.tenantId LEFT JOIN TenantAttendance TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE O.rn = 1 AND T.name LIKE ? ORDER BY T.name LIMIT ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  // const data = [
  //   CONSTANTS.DOCUMENT_TYPES.SELFI,
  //   date,
  //   clientId,
  //   propId,
  //   `%${searchVal}%`,
  //   `${offset}`,
  //   `${limit}`,
  // ];
  const data = [
    clientId,
    propId,
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    date,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.ListAttendanceForPropertyWithSearchForWeb = async ({
  clientId,
  propId,
  date,
  searchVal,
}: tenantAttendanceTypes & {
  date: string;
  searchVal: string;
}) => {
  const query = `SELECT DISTINCT T.id as tenantId, T.name AS tenantName, IFNULL(TA.attendance, 0) as status, P.type as propType, R.roomNum as roomNum, O.floor as floor, O.flatId as flatId, (Select value from Documents where tenantId = O.tenantId and propId = P.id and type = ? order by id desc limit 1) as profilePicture, P.name as propName FROM Occupancies AS O JOIN Properties AS P ON O.propId = P.id join Rooms as R on R.id = O.roomId join Tenants as T on T.id = O.tenantId LEFT JOIN TenantAttendance AS TA ON O.tenantId = TA.tenantId AND DATE(TA.attendanceDate) = DATE(?) WHERE O.clientId = ? and O.propId = ? and T.name like ? order by T.name;`;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    date,
    clientId,
    propId,
    `%${searchVal}%`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

tenantAttendanceDB.deleteTodayAttendance = async ({
  tenantId,
  clientId,
}: tenantAttendanceTypes) => {
  const query = "DELETE FROM TenantAttendance WHERE tenantId = ? and clientId = ? and DATE(attendanceDate) = CURDATE()";
  const data = [tenantId, clientId];
  const [result] = await DB.execute<ResultSetHeader>(query, data);
  if (result.affectedRows > 0) return true;
  else return false;
};

tenantAttendanceDB.deleteAllByClientIdAndTenantId = async ({
  tenantId,
  clientId,
}: tenantAttendanceTypes) => {
  const query = "DELETE FROM TenantAttendance WHERE tenantId = ? and clientId = ?";
  const data = [tenantId, clientId];
  const [result] = await DB.execute<ResultSetHeader>(query, data);
  if (result.affectedRows > 0) return true;
  else return false;
};

export default tenantAttendanceDB;