import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import requestTypes from "../schemas/request.schema";
import CONSTANTS from "../config/constants";

const requestDB: any = {};

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

requestDB.getRoomReqByClientId = async ({ clientId, status }: requestTypes) => {
  const query =
    `Select RE.id, RE.tenantId, RE.clientId, RE.floor, RE.title, RE.description, RE.type, RE.status, RE.createdAt, R.roomNum, R.id as roomId, R.floor, O.noticePeriod, O.lockInPeriod, O.moveOutDate, O.agreementStartDate, O.agreementPeriod, O.moveInDate, O.moveOutReason, O.flatId,  T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, T.gender as tenantGender, P.name as propertyName, P.id as propertyId, P.type as propType from Requests as RE inner join Tenants as T on T.id = RE.tenantId inner join Rooms as R on R.id = RE.roomId inner join Properties as P on P.id = RE.propId inner join Occupancies as O on O.id = RE.occupancyId where RE.clientId = ? and RE.status = ? and RE.isMoveOutId = 0 and RE.type != ?
    UNION
    Select RE.id, RE.tenantId, RE.clientId, RE.floor, RE.title, RE.description, RE.type, RE.status, RE.createdAt, R.roomNum, R.id as roomId, R.floor, M.noticePeriod, M.lockInPeriod, M.moveOutDate, M.agreementStartDate, M.agreementPeriod, M.moveInDate, M.moveOutReason, M.flatId,  T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, T.gender as tenantGender, P.name as propertyName, P.id as propertyId, P.type as propType from Requests as RE inner join Tenants as T on T.id = RE.tenantId inner join Rooms as R on R.id = RE.roomId inner join Properties as P on P.id = RE.propId inner join MoveOut as M on M.id = RE.occupancyId where RE.clientId = ? and RE.status = ? and RE.isMoveOutId = 1 and RE.type != ?
    order by id desc`;
  const data = [
    clientId, 
    status, 
    CONSTANTS.REQUEST_TYPE.TENANT_MOVEMENT, 
    clientId, 
    status, 
    CONSTANTS.REQUEST_TYPE.TENANT_MOVEMENT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

requestDB.getRoomReqByClientIdWithRequestType = async ({ clientId, status, type }: requestTypes) => {
  const query =
    `Select RE.id, RE.tenantId, RE.clientId, RE.floor, RE.title, RE.description, RE.type, RE.status, RE.createdAt, R.roomNum, R.id as roomId, R.floor, O.noticePeriod, O.lockInPeriod, O.moveOutDate, O.agreementStartDate, O.agreementPeriod, O.moveInDate, O.moveOutReason, O.flatId,  T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, T.gender as tenantGender, P.name as propertyName, P.id as propertyId, P.type as propType from Requests as RE inner join Tenants as T on T.id = RE.tenantId inner join Rooms as R on R.id = RE.roomId inner join Properties as P on P.id = RE.propId inner join Occupancies as O on O.id = RE.occupancyId where RE.clientId = ? and RE.status = ? and RE.isMoveOutId = 0 and RE.type = ?
    UNION
    Select RE.id, RE.tenantId, RE.clientId, RE.floor, RE.title, RE.description, RE.type, RE.status, RE.createdAt, R.roomNum, R.id as roomId, R.floor, M.noticePeriod, M.lockInPeriod, M.moveOutDate, M.agreementStartDate, M.agreementPeriod, M.moveInDate, M.moveOutReason, M.flatId,  T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, T.gender as tenantGender, P.name as propertyName, P.id as propertyId, P.type as propType from Requests as RE inner join Tenants as T on T.id = RE.tenantId inner join Rooms as R on R.id = RE.roomId inner join Properties as P on P.id = RE.propId inner join MoveOut as M on M.id = RE.occupancyId where RE.clientId = ? and RE.status = ? and RE.isMoveOutId = 1 and RE.type = ? 
    order by id desc`;
  const data = [clientId, status, type, clientId, status, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

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

requestDB.getPendingReqByClientId = async ({ clientId }: requestTypes) => {
  const query =
    "Select COUNT(id) as count from Requests where clientId = ? and status = ?";
  const data = [clientId, CONSTANTS.REQUEST_STATUS.PENDING];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].count;
  else return false;
};
requestDB.getPendingReqForStaff = async ({
  clientId,
  propertiesIds,
}: requestTypes & { propertiesIds: string }) => {
  const query =
    "Select COUNT(id) as count from Requests where clientId = ? and propId in (" +
    `${propertiesIds}` +
    ") and status = ? and type != ?";
  //const data = [clientId, propertiesIds, CONSTANTS.REQUEST_STATUS.PENDING];
  const data = [clientId, CONSTANTS.REQUEST_STATUS.PENDING, CONSTANTS.REQUEST_TYPE.TENANT_MOVEMENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].count;
  else return false;
};

requestDB.getPendingReqByPropId = async ({ propId }: requestTypes) => {
  const query =
    "Select COUNT(id) as count from Requests where propId = ? and status = ?";
  const data = [propId, CONSTANTS.REQUEST_STATUS.PENDING];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].count;
  else return false;
};

requestDB.getStatusById = async ({ id }: requestTypes) => {
  const query = "Select id, status, type, propId from Requests where id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

requestDB.getByTenantId = async ({ tenantId, status, type }: requestTypes) => {
  const query =
    "Select * from Requests where tenantId = ? and status = ? and type = ?";
  const data = [tenantId, status, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

requestDB.getByTenantIdAndClientId = async ({ tenantId, clientId, status, type }: requestTypes) => {
  const query =
    "Select * from Requests where tenantId = ? and clientId = ? and status = ? and type = ?";
  const data = [tenantId, clientId, status, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

requestDB.getByTenantIdAndType = async ({ tenantId, type }: requestTypes) => {
  const query =
    "Select * from Requests where tenantId = ? and type = ?";
  const data = [tenantId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

requestDB.getByTenantIdAndOccupancyIdAndType = async ({ clientId, tenantId, occupancyId, type }: requestTypes) => {
  const query =
    "Select * from Requests where clientId = ? and tenantId = ? and occupancyId = ? and type = ?";
  const data = [clientId, tenantId, occupancyId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

requestDB.updateStatus = async ({ status, id }: requestTypes) => {
  const query = "Update Requests set status = ? where id = ?";
  const data = [status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

requestDB.updateStatusWithReason = async ({ status, reason, id }: requestTypes) => {
  const query = "Update Requests set status = ?, reason = ? where id = ?";
  const data = [status, reason, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

requestDB.updateOccupancyId = async ({ occupancyId, id }: requestTypes) => {
  const query = "Update Requests set occupancyId = ? where id = ?";
  const data = [occupancyId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

requestDB.updateStatusByTenantId = async ({
  status,
  tenantId,
}: requestTypes) => {
  const query = "Update Requests set status = ? where tenantId = ?";
  const data = [status, tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

requestDB.removeByOccupancyId = async ({ occupancyId }: requestTypes) => {
  const query = "Delete from Requests where occupancyId = ?";
  const data = [occupancyId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

requestDB.removeByTenantId = async ({ tenantId }: requestTypes) => {
  const query = "Delete from Requests where tenantId = ?";
  const data = [tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

requestDB.removeByClientIdAndTenantId = async ({ clientId, tenantId }: requestTypes) => {
  const query = "Delete from Requests where clientId = ? and tenantId = ?";
  const data = [clientId, tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

requestDB.getByOccupancyAndType = async ({
  occupancyId,
  type,
}: requestTypes) => {
  const query = "Select * from Requests where occupancyId = ? and type = ?";
  const data = [occupancyId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

requestDB.create = async ({
  clientId,
  occupancyId,
  tenantId,
  propId,
  roomId,
  floor,
  title,
  description,
  type,
}: requestTypes) => {
  const query =
    "Insert into Requests (clientId,occupancyId,tenantId,propId,roomId,floor,title,description,type) values (?,?,?,?,?,?,?,?,?)";
  const data = [
    clientId,
    occupancyId,
    tenantId,
    propId,
    roomId,
    floor,
    title,
    description,
    type,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

requestDB.updateOccupancyByTenant = async ({
  tenantId,
  occupancyId,
}: requestTypes) => {
  const query = "update Requests set occupancyId = ? where tenantId = ?";
  const data = [occupancyId, tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

requestDB.updateIsMoveOutId = async ({
  id,
  isMoveOutId,
}: requestTypes) => {
  const query = "Update Requests set isMoveOutId = ? where id = ?";
  const data = [isMoveOutId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
}

requestDB.getPendingReqByClientIdWithType = async ({ clientId, type }: requestTypes) => {
  const query =
    "Select COUNT(id) as count from Requests where clientId = ? and status = ? and type = ?";
  const data = [clientId, CONSTANTS.REQUEST_STATUS.PENDING, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].count;
  else return 0;
};

requestDB.getPendingReqByClientIdWithTypeForStaff = async ({ 
  clientId, 
  type, 
  propertiesIds 
}: requestTypes & {propertiesIds: string;}) => {
  const query =
    `Select COUNT(id) as count from Requests where clientId = ? and propId in (${propertiesIds}) and status = ? and type = ?`;
  const data = [clientId, CONSTANTS.REQUEST_STATUS.PENDING, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].count;
  else return 0;
};

requestDB.getRefundRequestByTenantAndClient = async ({ tenantId, clientId }: requestTypes) => {
  const query = "Select * from Requests where tenantId = ? AND clientId = ? and type = ?";
  const data = [tenantId, clientId, CONSTANTS.REQUEST_TYPE.REFUND];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

export default requestDB;