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

const tenantMovementDB: any = {};

tenantMovementDB.getById = async ({ id }: tenantMovementTypes) => {
  const query = "SELECT * FROM TenantMovement WHERE id = ?";
  const [rows] = await DB.execute<RowDataPacket[]>(query, [id]);

  if (rows?.length > 0) return rows[0];
  return false;
};

tenantMovementDB.getByRequestId = async ({
  requestId,
}: tenantMovementTypes) => {
  const query =
    "SELECT * FROM TenantMovement WHERE requestId = ? ORDER BY id DESC";

  const [rows] = await DB.execute<RowDataPacket[]>(query, [requestId]);

  if (rows?.length > 0) return rows;
  return false;
};

tenantMovementDB.getByTenantId = async ({
  tenantId,
}: tenantMovementTypes) => {
  const query =
    "SELECT * FROM TenantMovement WHERE tenantId = ? ORDER BY id DESC";

  const [rows] = await DB.execute<RowDataPacket[]>(query, [tenantId]);

  if (rows?.length > 0) return rows;
  return false;
};

tenantMovementDB.getByClientId = async ({
  clientId,
}: tenantMovementTypes) => {
  const query =
    "SELECT * FROM TenantMovement WHERE clientId = ? ORDER BY id DESC";

  const [rows] = await DB.execute<RowDataPacket[]>(query, [clientId]);

  if (rows?.length > 0) return rows;
  return false;
};

tenantMovementDB.add = async ({requestId, clientId, propId, roomId, tenantId, type, fromDate, toDate, description, cabRequired}: tenantMovementTypes) => {
  const query = `INSERT INTO TenantMovement(requestId, clientId, propId, roomId, tenantId, type, fromDate, toDate, description, cabRequired) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  const data = [requestId, clientId, propId, roomId, tenantId, type, fromDate, toDate, description, cabRequired];

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

  return rows.insertId;
};

tenantMovementDB.removeById = async ({ id }: tenantMovementTypes) => {
  const query = "DELETE FROM TenantMovement WHERE id = ?";

  await DB.query<ResultSetHeader>(query, [id]);

  return true;
};

tenantMovementDB.getListForTenants = async ({
  tenantId,
  clientId,
  pageNum,
  limit,
}: tenantMovementTypes & { pageNum: number; limit: number;}) => {
  const query = `SELECT TM.type, TM.fromDate, TM.toDate, TM.description, TM.cabRequired, R.status, R.createdAt FROM Requests AS R INNER JOIN TenantMovement AS TM ON TM.requestId = R.id WHERE TM.tenantId = ? AND TM.clientId = ? ORDER BY TM.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;
  return false;
};

tenantMovementDB.getListForClient = async ({
  clientId,
  pageNum,
  limit,
}: tenantMovementTypes & { pageNum: number; limit: number;}) => {
  const query = `SELECT TM.type, TM.fromDate, TM.toDate, TM.cabRequired, TM.description, R.status, R.createdAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, P.id as propId, P.name as propName, concat(P.address, " ", P.streetAddress) as propAddress, RO.roomNum as roomNum, (select value from Documents where tenantId = T.id and clientId = TM.clientId and type = ? and moveOut=0 limit 1) as profilePicture FROM Requests AS R INNER JOIN TenantMovement AS TM ON TM.requestId = R.id join Tenants as T on TM.tenantId = T.id join Properties as P on TM.propId = P.id join Rooms as RO on TM.roomId = RO.id WHERE TM.clientId = ? ORDER BY TM.id DESC LIMIT ?, ?`;

  const offset = (pageNum - 1) * limit;

  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows;
  return false;
};

tenantMovementDB.getListForClientSearch = async ({
  clientId,
  pageNum,
  limit,
  searchVal,
}: tenantMovementTypes & { pageNum: number; limit: number; searchVal: string}) => {
  const query = `SELECT TM.type, TM.fromDate, TM.toDate, TM.cabRequired, TM.description, R.status, R.createdAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, P.id as propId, P.name as propName, concat(P.address, " ", P.streetAddress) as propAddress, RO.roomNum as roomNum, (select value from Documents where tenantId = T.id and clientId = TM.clientId and type = ? and moveOut=0 limit 1) as profilePicture FROM Requests AS R INNER JOIN TenantMovement AS TM ON TM.requestId = R.id join Tenants as T on TM.tenantId = T.id join Properties as P on TM.propId = P.id join Rooms as RO on TM.roomId = RO.id WHERE TM.clientId = ? and (T.name like ? OR T.mobile like ? OR RO.roomNum like ?) ORDER BY TM.id DESC LIMIT ?, ?`;

  const offset = (pageNum - 1) * limit;

  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, `%${searchVal}%`, `%${searchVal}%`, `%${searchVal}%`, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows;
  return false;
};

export default tenantMovementDB;