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

const documentDB: any = {};

documentDB.getByTenantIdAndClientId = async ({ tenantId, clientId, moveOut }: documentTypes) => {
  const query = "Select * from Documents where tenantId = ? and clientId = ? and moveOut = ? and status != 3 and type != ? order by id desc";
  const data = [tenantId, clientId, moveOut, CONSTANTS.DOCUMENT_TYPES.DUE_IMAGE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

documentDB.getIDByType = async ({
  tenantId,
  clientId,
  type,
  moveOut,
}: documentTypes) => {
  const query =
    "Select * from Documents where tenantId = ? and clientId =? and type = ? and moveOut = ? and status != 3 and type != ?";
  const data = [tenantId, clientId, type, moveOut, CONSTANTS.DOCUMENT_TYPES.DUE_IMAGE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

documentDB.getByLedgerReferenceId = async ({
  ledgerReferenceId,
}: documentTypes) => {
  const query =
    "Select * from Documents where ledgerReferenceId = ? and status != 3";
  const data = [ledgerReferenceId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

documentDB.getByMultipleLedgerReferenceIds = async ({
  ledgerReferenceIds,
}: {
  ledgerReferenceIds: string[];
}) => {
  if (!ledgerReferenceIds || ledgerReferenceIds.length === 0) {
    return [];
  }

  const query = `SELECT * FROM Documents WHERE ledgerReferenceId IN (${ledgerReferenceIds.map(() => "?").join(", ")}) AND status != 3`;
  const data = [...ledgerReferenceIds];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

documentDB.getIDByTypeAndStatus = async ({
  tenantId,
  clientId,
  type,
  moveOut,
  status,
}: documentTypes) => {
  const query =
    "Select * from Documents where tenantId = ? and clientId =? and type = ? and moveOut = ? and status = ? and type != ? order by id desc";
  const data = [tenantId, clientId, type, moveOut, status, CONSTANTS.DOCUMENT_TYPES.DUE_IMAGE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

documentDB.getByTenantIdAndType = async ({ tenantId, type, moveOut }: documentTypes) => {
  const query = "Select * from Documents where tenantId = ? and type = ? and moveOut = ? and status != 3 and type != ? ";
  const data = [tenantId, type, moveOut, CONSTANTS.DOCUMENT_TYPES.DUE_IMAGE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

documentDB.addDoc = async ({
  tenantId,
  clientId,
  propId,
  roomId,
  type,
  value,
  ledgerReferenceId=null,
}: documentTypes) => {
  const query =
    "Insert into Documents (tenantId, clientId, propId, roomId, type, value, ledgerReferenceId) values (?,?,?,?,?,?,?)";
  const data = [tenantId, clientId, propId, roomId, type, value, ledgerReferenceId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

documentDB.updateDoc = async ({
  tenantId,
  clientId,
  propId,
  roomId,
  type,
  value,
  id,
}: documentTypes) => {
  const query =
    "Update Documents set tenantId =?, clientId =?, propId =?, roomId =?, type =?, value =? where id =?";
  const data = [tenantId, clientId, propId, roomId, type, value, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

documentDB.addDocWithStatus = async ({
  tenantId,
  clientId,
  propId,
  roomId,
  type,
  value,
  status,
}: documentTypes) => {
  const query =
    "Insert into Documents (tenantId, clientId, propId, roomId, type, value, status) values (?,?,?,?,?,?,?)";
  const data = [tenantId, clientId, propId, roomId, type, value, status];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

documentDB.updateStatus = async ({ id, status }: documentTypes) => {
  const query = "Update Documents set status =? where id =?";
  const data = [status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

documentDB.removeByTenantIdandClientId = async ({
  tenantId,
  clientId,
}: documentTypes) => {
  const query = "Delete from  Documents where tenantId =? and clientId = ? and moveOut = 0";
  const data = [tenantId, clientId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

documentDB.removeAllByTenantIdandClientId = async ({
  tenantId,
  clientId,
}: documentTypes) => {
  const query = "Delete from  Documents where tenantId =? and clientId = ?";
  const data = [tenantId, clientId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

documentDB.removeByIdAndTenantId = async ({ id, tenantId }: documentTypes) => {
  const query = "Delete from  Documents where id =? and tenantId = ? limit 1";
  const data = [id, tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

documentDB.updateMoveOutStatus = async({
  tenantId,
  clientId,
  moveOut,
}: documentTypes) => {
  const query = 
   `Update Documents set moveOut = ? where tenantId =? and clientId = ?`;
  const data = [moveOut, tenantId, clientId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

documentDB.updateMoveOutById = async({
  id,
  moveOut,
}: documentTypes) => {
  const query = 
   `Update Documents set moveOut = ? where id = ?`;
  const data = [moveOut, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

documentDB.updateMoveOutStatusByType = async({
  tenantId,
  clientId,
  type,
  moveOut,
}: documentTypes) => {
  const query = 
   `Update Documents set moveOut = ? where tenantId =? and clientId = ? and type = ?`;
  const data = [moveOut, tenantId, clientId, type];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

documentDB.getByTenantIdAndClientIdMovedOut = async ({ tenantId, clientId, moveOut }: documentTypes) => {
  const query = "SELECT D.* FROM Documents as D JOIN (SELECT type, MAX(id) AS maxId FROM Documents WHERE tenantId = ? AND clientId = ? AND moveOut = ? and status != 3 and type != ? GROUP BY type) as V1 ON D.id = V1.maxId;";
  const data = [tenantId, clientId, moveOut, CONSTANTS.DOCUMENT_TYPES.DUE_IMAGE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

documentDB.updateOldAgreementByTenantId = async ({ tenantId, clientId, status, type }: documentTypes) => {
  const query = "Update Documents set status = ? where tenantId = ? and clientId = ? and type = ? and status = 2 and moveOut = 0 order by id desc limit 1";
  const data = [status, tenantId, clientId, type];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

export default documentDB;