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

const parcelDB: any = {};

parcelDB.add = async ({
  clientId,
  tenantId,
  propId,
  roomId,
  deliveryPartner,
  description,
  status,
  document,
}: parcelTypes) => {
  const query =
    "Insert into Parcels (clientId, tenantId, propId, roomId, deliveryPartner, description, status, document) values (?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    tenantId,
    propId,
    roomId,
    deliveryPartner,
    description,
    status,
    document,
  ];
  await DB.query<ResultSetHeader>(query, data);
  return true;
};

parcelDB.getByClientId = async ({
    clientId,
    pageNum,
    limit
}: parcelTypes & {pageNum: any; limit: any;}) => {
    const query = 
        `Select P.*, T.name as tenantName, T.mobile as tenantMobile, PR.name as propName, R.roomNum as roomNum from Parcels as P join Tenants as T on P.tenantId = T.id join Properties as PR on P.propId = PR.id join Rooms as R on P.roomId = R.id where P.clientId = ? order by P.createdAt 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;
};

parcelDB.getByClientIdAndStatus = async ({
    clientId,
    status,
    pageNum,
    limit
}: parcelTypes & {limit: any; pageNum: any;}) => {
    const query = 
        `Select P.*, T.name as tenantName, T.mobile as tenantMobile, PR.name as propName, R.roomNum as roomNum from Parcels as P join Tenants as T on P.tenantId = T.id join Properties as PR on P.propId = PR.id join Rooms as R on P.roomId = R.id where P.clientId = ? and P.status = ? order by P.createdAt 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;
};

parcelDB.getById = async ({
    id,
}: parcelTypes) => {
    const query = 
        `Select P.*, T.name as tenantName, T.mobile as tenantMobile, PR.name as propName, R.roomNum as roomNum from Parcels as P join Tenants as T on P.tenantId = T.id join Properties as PR on P.propId = PR.id join Rooms as R on P.roomId = R.id where P.id = ? order by P.createdAt desc`;
    const data = [id];
    const [rows] = await DB.execute<RowDataPacket[]>(query, data);
    if (rows?.length > 0) return rows[0];
    else return false;
};

parcelDB.getByClientIdAndSearchVal = async ({
    clientId,
    searchVal,
}: parcelTypes & {searchVal: any}) => {
    const query = 
        `Select P.*, T.name as tenantName, T.mobile as tenantMobile, PR.name as propName, R.roomNum as roomNum from Parcels as P join Tenants as T on P.tenantId = T.id join Properties as PR on P.propId = PR.id join Rooms as R on P.roomId = R.id where P.clientId = ? and (T.name like ? OR T.mobile like ?) order by P.createdAt desc`;
    const data = [clientId, `%${searchVal}%`, `%${searchVal}%`];
    const [rows] = await DB.execute<RowDataPacket[]>(query, data);
    if (rows?.length > 0) return rows;
    else return false;
};

parcelDB.getForStaff = async ({
    clientId,
    propertiesIds,
    pageNum,
    limit,
}: parcelTypes & {propertiesIds: any; pageNum: any; limit: any;}) => {
    const query = 
        `Select P.*, T.name as tenantName, T.mobile as tenantMobile, PR.name as propName, R.roomNum as roomNum from Parcels as P join Tenants as T on P.tenantId = T.id join Properties as PR on P.propId = PR.id join Rooms as R on P.roomId = R.id where P.clientId = ? and P.propId in (${propertiesIds}) order by P.createdAt 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;
};

parcelDB.getForStaffByStatus = async ({
    clientId,
    propertiesIds,
    status,
    pageNum,
    limit
}: parcelTypes & {propertiesIds: any; limit: any; pageNum: any;}) => {
    const query = 
        `Select P.*, T.name as tenantName, T.mobile as tenantMobile, PR.name as propName, R.roomNum as roomNum from Parcels as P join Tenants as T on P.tenantId = T.id join Properties as PR on P.propId = PR.id join Rooms as R on P.roomId = R.id where P.clientId = ? and P.status = ? and P.propId in (${propertiesIds}) order by P.createdAt 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;
};

parcelDB.getForStaffBySearchVal = async ({
    clientId,
    propertiesIds,
    searchVal,
}: parcelTypes & {propertiesIds: any; searchVal: any}) => {
    const query = 
        `Select P.*, T.name as tenantName, T.mobile as tenantMobile, PR.name as propName, R.roomNum as roomNum from Parcels as P join Tenants as T on P.tenantId = T.id join Properties as PR on P.propId = PR.id join Rooms as R on P.roomId = R.id where P.clientId = ? and P.propId in (${propertiesIds}) and (T.name like ? OR T.mobile like ?) order by P.createdAt desc`;
    const data = [clientId, `%${searchVal}%`, `%${searchVal}%`];
    const [rows] = await DB.execute<RowDataPacket[]>(query, data);
    if (rows?.length > 0) return rows;
    else return false;
};

parcelDB.updateStatus = async ({
  id,
  status,
}: parcelTypes) => {
  const query =
    `Update  Parcels set status = ? where id = ? limit 1`;
  const data = [status, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

parcelDB.deleteAllByClientIdAndTenantId = async ({
  clientId,
  tenantId,
}: parcelTypes) => {
  const query =
    `Delete From Parcels where tenantId = ? and clientId = ?`;
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

parcelDB.getStatsByClientId = async ({
    clientId,
}: parcelTypes) => {
    const query = 
        `Select (select count(id) from Parcels where clientId = ?) as total,
         (select count(id) from Parcels where clientId = ? and status = ?) as pending,
         (select count(id) from Parcels where clientId = ? and status = ?) as delivered,
         (select count(id) from Parcels where clientId = ? and status = ?) as returned`;
    const data = [
        clientId,
        clientId,
        CONSTANTS.PARCEL_STATUS.PENDING,
        clientId,
        CONSTANTS.PARCEL_STATUS.DELIVERED,
        clientId,
        CONSTANTS.PARCEL_STATUS.RETURNED
    ];
    const [rows] = await DB.execute<RowDataPacket[]>(query, data);
    if (rows?.length > 0) return rows[0];
    else return false;
};

parcelDB.getStatsForStaff = async ({
    clientId,
    propertiesIds
}: parcelTypes & {propertiesIds: any;}) => {
    const query = 
        `Select (select count(id) from Parcels where clientId = ? and propId in (${propertiesIds})) as total,
         (select count(id) from Parcels where clientId = ? and status = ? and propId in (${propertiesIds})) as pending,
         (select count(id) from Parcels where clientId = ? and status = ? and propId in (${propertiesIds})) as delivered,
         (select count(id) from Parcels where clientId = ? and status = ? and propId in (${propertiesIds})) as returned`;
    const data = [
        clientId,
        clientId,
        CONSTANTS.PARCEL_STATUS.PENDING,
        clientId,
        CONSTANTS.PARCEL_STATUS.DELIVERED,
        clientId,
        CONSTANTS.PARCEL_STATUS.RETURNED
    ];
    const [rows] = await DB.execute<RowDataPacket[]>(query, data);
    if (rows?.length > 0) return rows[0];
    else return false;
};

export default parcelDB;