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

const tenantGuestsDB: any = {};
tenantGuestsDB.create = async ({requestId, clientId, propId, roomId, tenantId, name, mobile, relation, noOfGuest, checkInDate, checkOutDate, vehicleNo, description}: tenantGuestTypes) => {
  const query = `INSERT INTO TenantGuests (requestId, clientId, propId, roomId, tenantId, name, mobile, relation, noOfGuest, checkInDate, checkOutDate, vehicleNo, description) VALUES (?, ?,?,?,?,?,?,?,?,?,?,?,?)`;

  const data = [requestId, clientId, propId, roomId, tenantId, name, mobile, relation, noOfGuest, checkInDate, checkOutDate, vehicleNo, description];

  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

tenantGuestsDB.getListForTenants = async ({ tenantId, clientId, pageNum, limit }: tenantGuestTypes & { pageNum: number; limit: number }) => {
  const query = " select TG.name, TG.mobile, TG.relation, TG.noOfGuest, TG.checkInDate, TG.checkOutDate, TG.vehicleNo, TG.description as reason, R.status, R.createdAt from Requests as R INNER JOIN TenantGuests as TG ON TG.requestId=R.id where TG.tenantId =? and TG.clientId =? order by TG.id desc LIMIT ?,?";
  const offset = (pageNum - 1) * limit;
  const data = [tenantId, clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

tenantGuestsDB.getGuestDetailByRequestId = async ({ requestId, clientId}: tenantGuestTypes) => {
  const query = " select TG.name, TG.mobile, TG.relation, TG.noOfGuest, TG.checkInDate, TG.checkOutDate, TG.vehicleNo, TG.description as reason, R.status, R.createdAt from Requests as R INNER JOIN TenantGuests as TG ON TG.requestId=R.id where TG.clientId =? and TG.requestId=? order by TG.id";
  const data = [clientId, requestId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

// vendorDB.getById = async ({ id }: vendorsTypes) => {
//   const query = "Select * from Vendors where id = ? order by id desc";
//   const data = [id];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return false;
// };

// vendorDB.getByMobile = async ({ mobile }: vendorsTypes) => {
//   const query = "Select * from Vendors where mobile = ? order by id desc";
//   const data = [mobile];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return false;
// };

// vendorDB.getByMobileAndClientId = async ({ mobile, clientId }: vendorsTypes) => {
//   const query = "Select * from Vendors where mobile = ? and clientId = ? order by id desc";
//   const data = [mobile, clientId];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return false;
// };

// vendorDB.getByClientId = async ({ clientId }: vendorsTypes) => {
//   const query =
//     "Select * , ? as userType from Vendors where clientId = ? and status != ? order by id desc";
//   const data = [CONSTANTS.USER_TYPE.VENDOR, clientId, CONSTANTS.VENDOR_STATUS.DELETED];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

// vendorDB.getByClientIdX = async ({ clientId }: vendorsTypes) => {
//   const query =
//     "Select * , ? as userType from Vendors where clientId = ? and status != ? and type != ? order by id desc";
//   const data = [
//     CONSTANTS.USER_TYPE.VENDOR, 
//     clientId, 
//     CONSTANTS.VENDOR_STATUS.DELETED, 
//     CONSTANTS.VENDOR_TYPES.LANDLORD
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

// vendorDB.create = async ({ mobile, name, clientId, type, }: vendorsTypes) => {
//   const query = "Insert into Vendors (clientId, name, mobile, type) values (?,?,?,?)";
//   const data = [clientId, name, mobile, type];
//   const [rows] = await DB.query<ResultSetHeader>(query, data);
//   return rows.insertId;
// };

// vendorDB.edit = async ({
//   id,
//   mobile,
//   name,
//   clientId,
//   type
// }: vendorsTypes) => {
//   const query = "Update Vendors set name = ?, mobile = ?, type = ? where id = ? and clientId = ?";
//   const data = [name, mobile, type, id, clientId];
//   const [rows] = await DB.execute<ResultSetHeader>(query, data);
//   if (rows.affectedRows > 0) return true;
//   else return false;
// };

// vendorDB.getByClientIdAndSearchVal = async ({
//   clientId,
//   searchVal,
// }: vendorsTypes & { searchVal: string }) => {
//   const query =
//     "Select * , ? as userType from Vendors where clientId = ? and status != ? and (name like ? or mobile like ?) order by id desc";
//   const data = [
//     CONSTANTS.USER_TYPE.VENDOR,
//     clientId,
//     CONSTANTS.VENDOR_STATUS.DELETED,
//     `%${searchVal}%`,
//     `%${searchVal}%`,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

// vendorDB.getByClientIdAndSearchValX = async ({
//   clientId,
//   searchVal,
// }: vendorsTypes & { searchVal: string }) => {
//   const query =
//     "Select * , ? as userType from Vendors where clientId = ? and status != ? and (name like ? or mobile like ?) order by id desc";
//   const data = [
//     CONSTANTS.USER_TYPE.VENDOR,
//     clientId,
//     CONSTANTS.VENDOR_STATUS.DELETED,
//     `%${searchVal}%`,
//     `%${searchVal}%`,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

// vendorDB.getVendorTypes = async () => {
//   const query =
//     "Select id, name from VendorTypes order by id asc";
//   const [rows] = await DB.execute<RowDataPacket[]>(query);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

// vendorDB.updateStatus = async ({
//   id,
//   status,
// }: vendorsTypes) => {
//   const query = `Update Vendors set status = ? where id = ?`;
//   const data = [status, id];
//   const [rows] = await DB.execute<ResultSetHeader>(query, data);
//   if (rows.affectedRows > 0) return true;
//   else return false;
// };

export default tenantGuestsDB;