import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import transitRequestTypes from "../schemas/transitRequest.schema";
import transitPointTypes from "../schemas/transitPoints.schema";
import CONSTANTS from "../config/constants";

let transitServiceDB: any = {};

transitServiceDB.add = async ({
  tenantId,
  clientId,
  transitDate,
  slot,
  pickupId,
  dropId,
  status,
}: transitRequestTypes) => {
  const query = `INSERT INTO TransitRequest (tenantId, clientId, transitDate, slot, pickupId, dropId, status) VALUES (?, ?, ?, ?, ?, ?, ?)`;
  const data = [
    tenantId,
    clientId,
    transitDate,
    slot,
    pickupId,
    dropId,
    status,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transitServiceDB.addTransitPoint = async ({
  clientId,
  locationName,
}: transitPointTypes) => {
  const query = `INSERT INTO TransitPoints (clientId, locationName) VALUES (?, ?)`;
  const data = [clientId, locationName];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transitServiceDB.getTransitPointsByClientId = async ({
  clientId,
}: transitPointTypes) => {
  const query = `SELECT * FROM TransitPoints WHERE clientId = ?`;
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

transitServiceDB.updateStatus = async ({ id, status }: transitRequestTypes) => {
  const query = `UPDATE TransitRequest SET status = ? WHERE id = ?`;
  const data = [status, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transitServiceDB.getBookingsByTenantIdAndClientId = async ({
  clientId,
  tenantId,
  pageNum,
  limit,
}: transitRequestTypes & { pageNum: number; limit: number }) => {
  const query = `SELECT TR.*, (Select TP.locationName from TransitPoints as TP where TR.pickupId = TP.id) as pickupLocation, (Select TP.locationName from TransitPoints as TP where TR.dropId = TP.id) as dropLocation FROM TransitRequest as TR WHERE TR.clientId = ? AND TR.tenantId = ? order by TR.transitDate desc, TR.id limit ?, ?`;
  const offset = (pageNum - 1) * limit;
  const data = [clientId, tenantId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

transitServiceDB.getBookingsByClientIdAndFilters = async ({
  clientId,
  transitDate,
  slot,
  pickupId,
  dropId,
  pageNum,
  limit,
}: transitRequestTypes & { pageNum: number; limit: number }) => {
  let query = `SELECT TR.*, (Select TP.locationName from TransitPoints as TP where TR.pickupId = TP.id) as pickupLocation, (Select TP.locationName from TransitPoints as TP where TR.dropId = TP.id) as dropLocation, T.name as tenantName, T.mobile as tenantMobile, O.kycStatus, P.name as propName, P.streetAddress as address, R.roomNum as roomNum, (select value from Documents where tenantId = TR.tenantId and clientId = TR.clientId and type = ? and moveOut=0 limit 1) as profilePicture FROM TransitRequest as TR LEFT JOIN Tenants as T ON T.id = TR.tenantId join Occupancies as O on O.tenantId = TR.tenantId and O.clientId = TR.clientId join Properties as P on P.id = O.propId join Rooms as R on O.roomId = R.id WHERE TR.clientId = ? `;
  const offset = (pageNum - 1) * limit;
  const data: any = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId];

  if (transitDate) {
    query += ` AND TR.transitDate = ?`;
    data.push(transitDate);
  }

  if (slot) {
    query += ` AND TR.slot = ? `;
    data.push(slot);
  }

  if (pickupId) {
    query += ` AND TR.pickupId = ?`;
    data.push(pickupId);
  }

  if (dropId) {
    query += ` AND TR.dropId = ?`;
    data.push(dropId);
  }

  query += ` order by TR.id desc limit ?, ?`;
  data.push(`${offset}`, `${limit}`);

  // log.info(`Booking Query [${mysql.format(query, data)}]`)

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

transitServiceDB.getBookingsCountByClientIdAndFilters = async ({
  clientId,
  transitDate,
  slot,
  pickupId,
  dropId,
}: transitRequestTypes) => {
  let query = `SELECT count(*) as count FROM TransitRequest as TR LEFT JOIN Tenants as T ON T.id = TR.tenantId join Occupancies as O on O.tenantId = TR.tenantId and O.clientId = TR.clientId join Properties as P on P.id = O.propId WHERE TR.clientId = ? `;
  const data: any = [clientId];

  if (transitDate) {
    query += ` AND TR.transitDate = ?`;
    data.push(transitDate);
  }

  if (slot) {
    query += ` AND TR.slot = ? `;
    data.push(slot);
  }

  if (pickupId) {
    query += ` AND TR.pickupId = ?`;
    data.push(pickupId);
  }

  if (dropId) {
    query += ` AND TR.dropId = ?`;
    data.push(dropId);
  }

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

export default transitServiceDB;