import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import CONSTANTS from "../config/constants";
import log from "../config/log";
import moment from "moment";
import hiddenDuesTypes from "../schemas/hiddenDues.schema";
import tenantHouseInspectionTypes from "../schemas/tenantHouseInspection.schema";
import tenantInspectionItemsTypes from "../schemas/tenantInspectionItems.schema";
import propertyInspectionItemListTypes from "../schemas/propertyInspectionItemList.schema";
import inspectionImagesTypes from "../schemas/inspectionImages.schema";

const houseInspectionDB: any = {};

houseInspectionDB.getInspectionRecordByGlobalId = async ({
  clientId,
  globalId,
}: tenantHouseInspectionTypes) => {
  const query = `Select * from TenantHouseInspection where clientId = ? and globalId = ?`;
  const data = [clientId, globalId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

houseInspectionDB.addMoveInInspection = async ({
  globalId,
  isEvicted,
  tenantId,
  clientId,
  propId,
  roomId,
  status,
  moveInInspectionDate,
  moveOutInspectionDate,
  moveInRemarks,
  moveOutRemarks,
  damageCharges,
}: tenantHouseInspectionTypes) => {
  const query =
    "Insert into TenantHouseInspection (globalId, isEvicted, tenantId, clientId, propId, roomId, status, moveInInspectionDate, moveOutInspectionDate, moveInRemarks, moveOutRemarks, damageCharges) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    globalId,
    isEvicted,
    tenantId,
    clientId,
    propId,
    roomId,
    status,
    moveInInspectionDate,
    moveOutInspectionDate,
    moveInRemarks,
    moveOutRemarks,
    damageCharges,
  ];

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

houseInspectionDB.addInspectionRecord = async ({
  tenantId,
  clientId,
  tenantInspectionId,
  itemType,
  type,
  moveInStatus,
  moveOutStatus,
  moveInCount,
  moveOutCount,
}: tenantInspectionItemsTypes) => {
  const query =
    "Insert into TenantInspectionItems (tenantId, clientId, tenantInspectionId, itemType, type, moveInStatus, moveOutStatus, moveInCount, moveOutCount) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    clientId,
    tenantInspectionId,
    itemType,
    type,
    moveInStatus,
    moveOutStatus,
    moveInCount,
    moveOutCount,
  ];

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

houseInspectionDB.addMoveOutInspection = async ({
  id,
  moveOutInspectionDate,
  moveOutRemarks,
  damageCharges,
  moveOutInspectionDoneBy,
  moveOutInspectionDoneByUserType,
}: tenantHouseInspectionTypes) => {
  const query =
    "Update TenantHouseInspection set moveOutInspectionDate = ?, moveOutRemarks = ?, damageCharges = ?, moveOutInspectionDoneBy = ?, moveOutInspectionDoneByUserType = ? where id = ?";
  const data = [
    moveOutInspectionDate,
    moveOutRemarks,
    damageCharges,
    moveOutInspectionDoneBy,
    moveOutInspectionDoneByUserType,
    id,
  ];

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

houseInspectionDB.updateInspectionRecord = async ({
  id,
  moveOutStatus,
  moveOutCount,
}: tenantInspectionItemsTypes) => {
  const query =
    "Update TenantInspectionItems set moveOutStatus = ?, moveOutCount = ? where id = ?";
  const data = [
    moveOutStatus,
    moveOutCount,
    id,
  ];

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

houseInspectionDB.linkInspectionItem = async ({
  clientId,
  propId,
  inspectionItemId,
}: propertyInspectionItemListTypes) => {
  const query =
    "Insert into PropertyInspectionItemList (clientId, propId, inspectionItemId) values (?, ?, ?)";
  const data = [
    clientId,
    propId,
    inspectionItemId,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

houseInspectionDB.unlinkInspectionItem = async ({ clientId, propId, inspectionItemId }: propertyInspectionItemListTypes) => {
  const query = "Delete from PropertyInspectionItemList where clientId = ? and propId = ? and inspectionItemId = ?";
  const data = [clientId, propId, inspectionItemId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

houseInspectionDB.getInspectionItemList = async () => {
  const query =
  "Select * from InspectionItemList";
  const [rows] = await DB.execute<RowDataPacket[]>(query);
  if (rows?.length > 0) return rows;
  else return false;
};

houseInspectionDB.getInspectionItemListByPropId = async ({ propId, clientId } : propertyInspectionItemListTypes) => {
  const query =
  "Select * from PropertyInspectionItemList where clientId = ? and propId = ?";
  const data = [clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

houseInspectionDB.getInspectionItemByIdAndPropId = async ({ propId, clientId, inspectionItemId } : propertyInspectionItemListTypes) => {
  const query =
  "Select * from PropertyInspectionItemList where clientId = ? and propId = ? and inspectionItemId = ?";
  const data = [clientId, propId, inspectionItemId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

houseInspectionDB.getHouseInspectionByClientIdAndTenantIdAndGlobalId = async ({
  clientId,
  tenantId,
  globalId
 } : tenantHouseInspectionTypes) => {
  const query =
  "Select * from TenantHouseInspection where clientId = ? and tenantId = ? and globalId = ?";
  const data = [clientId, tenantId, globalId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

houseInspectionDB.getInspectionItemsByClientIdAndTenantIdAndTenantInspectionId = async ({
  clientId,
  tenantId,
  tenantInspectionId
 } : tenantInspectionItemsTypes) => {
  const query =
  "Select * from TenantInspectionItems where clientId = ? and tenantId = ? and tenantInspectionId = ?";
  const data = [clientId, tenantId, tenantInspectionId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

houseInspectionDB.getInspectionImagesByInspectionId = async ({
  tenantInspectionId
 } : tenantInspectionItemsTypes) => {
  const query =
  "Select * from InspectionsImages where tenantInspectionId = ?";
  const data = [tenantInspectionId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

houseInspectionDB.addImage = async ({
  clientId,
  tenantId,
  tenantInspectionId,
  value,
  isMovedOut
}: inspectionImagesTypes) => {
  const query =
  "INSERT into InspectionsImages (clientId, tenantId, tenantInspectionId, value, isMovedOut) values (?, ?, ?, ?, ?)";
  const data = [clientId, tenantId, tenantInspectionId, value, isMovedOut];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.insertId;
};

houseInspectionDB.getInspectionRecordByClientIdAndTenantInspectionIdAndItemType = async ({
  clientId,
  tenantId,
  tenantInspectionId,
  itemType,
}: tenantInspectionItemsTypes) => {
  const query =
    "Select * from TenantInspectionItems where clientId = ? and tenantId = ? and tenantInspectionId = ? and itemType = ?";
  const data = [clientId, tenantId, tenantInspectionId, itemType];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

export default houseInspectionDB;