import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import CONSTANTS from "../config/constants";
import staffTasksTypes from "../schemas/staffTasks.schema";
import log from "../config/log";
import staffTaskDocumentsTypes from "../schemas/staffTaskDocuments.schema";

const staffTasksDB: any = {};

staffTasksDB.record = async ({
  clientId,
  propId,
  roomId,
  type,
  doneBy,
  taskCompleted,
  taskDate,
  recordedById,
  recordedByUserType,
  remarks,
}: staffTasksTypes) => {
  const query =
    "Insert into StaffTasks (clientId, propId, roomId, type, taskCompleted, taskDate, doneBy, recordedById, recordedByUserType, remarks) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    propId,
    roomId,
    type,
    taskCompleted,
    taskDate,
    doneBy,
    recordedById,
    recordedByUserType,
    remarks,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffTasksDB.AddImage = async ({
  clientId,
  propId,
  roomId,
  staffTaskId,
  value,
}: staffTaskDocumentsTypes) => {
  const query =
    "Insert into StaffTaskDocuments (clientId, propId, roomId, staffTaskId, value) values (?, ?, ?, ?, ?)";
  const data = [
    clientId,
    propId,
    roomId,
    staffTaskId,
    value
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

staffTasksDB.getByImagesByTaskId = async ({
    staffTaskId,
    clientId,
}: staffTaskDocumentsTypes) => {
  let query =
    `SELECT * FROM StaffTaskDocuments WHERE staffTaskId = ? and clientId = ?`

  let data: any = [
    staffTaskId,
    clientId,
  ];

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

staffTasksDB.getRoomTasksByClientIdAndFilters = async ({
  clientId,
  taskDate,
  staffFilters=null,
  searchVal=null,
  propFilters,
  pageNum,
  limit,
}: staffTasksTypes & {
  staffFilters: any[] | null;
  searchVal: string | null;
  propFilters: any[];
  pageNum: number | null;
  limit: number;
}) => {
  let query =
    `SELECT ST.*, S.name AS staffName, S.mobile AS staffMobile, IF (ST.recordedByUserType = ?, (select name from Clients where id = ST.recordedById), (select name from Staffs where id = ST.recordedById)) AS recordedByName, P.name AS propName, R.roomNum FROM StaffTasks as ST join Staffs as S on S.id = ST.doneBy join Rooms as R on R.id = ST.roomId join Properties as P on P.id = ST.propId WHERE ST.clientId = ? and ST.type = ? and DATE(ST.taskDate) = ? `

  let data: any = [
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
    CONSTANTS.STAFF_TASK_TYPE.ROOM_TASK,
    taskDate,
  ];

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

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

  if (searchVal && searchVal.trim() !== "" && searchVal.trim().toLowerCase() != 'null' && searchVal.trim().toLowerCase() != 'undefined') {
    query += ` and R.roomNum like ? `;
    data.push(`%${searchVal}%`);
  }

  query += ` order by id desc`

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

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

staffTasksDB.getCommonAreaTasksByClientIdAndFilters = async ({
  clientId,
  taskDate,
  staffFilters=null,
  searchVal=null,
  propFilters,
  pageNum,
  limit,
}: staffTasksTypes & {
  staffFilters: any[] | null;
  searchVal: string | null;
  propFilters: any[];
  pageNum: number | null;
  limit: number;
}) => {
  let query =
    `SELECT ST.*, S.name AS staffName, S.mobile AS staffMobile, IF (ST.recordedByUserType = ?, (select name from Clients where id = ST.recordedById), (select name from Staffs where id = ST.recordedById)) AS recordedByName, P.name AS propName FROM StaffTasks as ST join Staffs as S on S.id = ST.doneBy join Properties as P on P.id = ST.propId WHERE ST.clientId = ? and ST.type = ? and DATE(ST.taskDate) = ? `

  let data: any = [
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
    CONSTANTS.STAFF_TASK_TYPE.COMMON_AREA_TASK,
    taskDate,
  ];

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

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

  if (searchVal && searchVal.trim() !== "" && searchVal.trim().toLowerCase() != 'null' && searchVal.trim().toLowerCase() != 'undefined') {
    query += ` and R.roomNum like ? `;
    data.push(`%${searchVal}%`);
  }

  query += ` order by id desc`

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

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

staffTasksDB.getSummaryByClientIdAndFilters = async ({
  clientId,
  taskDate,
  propFilters,
}: staffTasksTypes & {
    propFilters: any[] | null;
}) => {
  let query =
    `SELECT count(DISTINCT R.id) as totalRooms, count(DISTINCT CASE WHEN ST.taskCompleted is not null and ST.taskCompleted > 0 THEN R.id END) as cleanedRooms, (count(DISTINCT R.id) - count(DISTINCT CASE WHEN ST.taskCompleted is not null and ST.taskCompleted > 0 THEN R.id END)) as uncleanedRooms from Rooms as R join Properties as P on R.propId = P.id left join StaffTasks as ST on R.id = ST.roomId and DATE(ST.taskDate) = ? WHERE P.clientId = ? and P.status = ? and R.status != ? `

  let data: any = [
    taskDate,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];

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

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

staffTasksDB.getSummaryByClientIdAndFiltersForStaff = async ({
  clientId,
  propertiesIds,
  taskDate,
  propFilters,
}: staffTasksTypes & {
    propFilters: any[] | null;
    propertiesIds: any[] | null;
}) => {
  let query =
    `SELECT count(DISTINCT R.id) as totalRooms, SUM(CASE WHEN ST.taskCompleted is not null and ST.taskCompleted > 0 THEN 1 ELSE 0 END) as cleanedRooms, SUM(CASE WHEN ST.taskCompleted is null or ST.taskCompleted = 0 THEN 1 ELSE 0 END) as uncleanedRooms from Rooms as R join Properties as P on R.propId = P.id left join StaffTasks as ST on R.id = ST.roomId and DATE(ST.taskDate) = ? WHERE P.clientId = ? and P.status = ? and R.status != ? and R.propId in (${propertiesIds})`

  let data: any = [
    taskDate,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
  ];

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

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

export default staffTasksDB;