import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import propertyAssetsTypes from "../schemas/propertyAssets.schema";
import property from "../controllers/property.controller";

const propertyAssetsDB: any = {};

propertyAssetsDB.add = async ({
  clientId,
  assetId,
  propId,
  status,
  unitCount,
  assignedTo,
  assignedToId,
}: propertyAssetsTypes) => {
  const query =
    "Insert into PropertyAssets (clientId, assetId, propId, status, unitCount, assignedTo, assignedToId) values (?, ?, ?, ?, ?, ?, ?)";
  const data = [clientId, assetId, propId, status, unitCount, assignedTo, assignedToId];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

propertyAssetsDB.getById = async ({ id }: propertyAssetsTypes) => {
  const query =
    "Select PA.id, PA.assetId, PA.clientId, PA.propId, PA.status, PA.unitCount, PA.assignedTo, PA.createdAt, P.name from PropertyAssets as PA join Properties as P on P.id = PA.propId where PA.id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  else return false;
};

propertyAssetsDB.getByAssetId = async ({ assetId }: propertyAssetsTypes) => {
  const query =
    "Select PA.id, PA.assetId, PA.clientId, PA.propId, PA.status, PA.unitCount, PA.assignedTo, PA.assignedToId, CASE WHEN PA.assignedTo = 2 and PA.assignedToId is not null THEN (Select name from Staffs where id = PA.assignedToId) ELSE null END as assignedToName, CASE WHEN PA.assignedTo = 2 and PA.assignedToId is not null THEN (Select mobile from Staffs where id = PA.assignedToId) ELSE null END as assignedToMobile, PA.createdAt, P.name from PropertyAssets as PA join Properties as P on P.id = PA.propId where PA.assetId = ?";
  const data = [assetId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return false;
};

propertyAssetsDB.update = async ({
  id,
  propId,
  status,
  assignedTo,
  assignedToId,
}: propertyAssetsTypes) => {
  const query =
    "Update PropertyAssets set propId = ?, status = ?, assignedTo = ?, assignedToId = ? where id = ?";
  const data = [propId, status, assignedTo, assignedToId, id];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

propertyAssetsDB.updateUnitCount = async ({
  id,
  unitCount,
}: propertyAssetsTypes) => {
  const query = "Update PropertyAssets set unitCount = ? where id = ?";
  const data = [unitCount, id];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

propertyAssetsDB.getByPropId = async ({
  propId,
  assetId,
}: propertyAssetsTypes) => {
  const query = "Select * from PropertyAssets where propId = ? and assetId = ?";
  const data = [propId, assetId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0];
  else return false;
};

propertyAssetsDB.delete = async ({ id }: propertyAssetsTypes) => {
  const query = "Delete from PropertyAssets where id = ? limit 1";
  const data = [id];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

export default propertyAssetsDB;