import DB from "../config/database/db";
import { ResultSetHeader, RowDataPacket } from "mysql2";
import tenantNotesTypes from "../schemas/tenantNotes.schema";

const tenantNotesDB: any = {};

/**
 * Add Tenant Note
 */
tenantNotesDB.addNote = async ({
  clientId,
  tenantId,
  addedBy,
  type,
  note,
}: tenantNotesTypes) => {
  const query =
    "INSERT INTO TenantNotes (clientId, tenantId, addedBy, note, type) VALUES (?,?,?,?,?)";
  const data = [clientId, tenantId, addedBy, note, type];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

/**
 * Fetch Tenant Notes by clientId
 */
tenantNotesDB.getByClientId = async ({
  clientId,
}: tenantNotesTypes) => {
  const query =
    "SELECT * FROM TenantNotes WHERE clientId = ? ORDER BY id DESC";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

/**
 * Fetch Tenant Notes by tenantId and clientId
 */
tenantNotesDB.getByTenantAndClientId = async ({
  tenantId,
  clientId,
  pageNum = 0,
  limit= 10
}: tenantNotesTypes & {pageNum: number, limit: number}) => {
  const offset: number = (pageNum - 1) * limit;
  const query =
    "SELECT * FROM TenantNotes WHERE tenantId = ? AND clientId = ? ORDER BY id DESC limit ?, ?";
  const data = [tenantId, clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

tenantNotesDB.getById = async ({
  id
}: tenantNotesTypes) => {
  const query =
    "SELECT * FROM TenantNotes WHERE id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

tenantNotesDB.delete = async ({
  id
}: tenantNotesTypes) => {
  const query =
    "Delete FROM TenantNotes WHERE id = ?";
  const data = [id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

export default tenantNotesDB;