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

const activityLogsDB: any = {};

activityLogsDB.logActivity = async ({
  tenantId,
  clientId,
  propId,
  doneByUserType,
  doneBy,
  doneByName,
  doneByRole,
  activity,
  description,
  device,
}: activityLogsTypes) => {
  const query =
    "Insert into ActivityLogs (tenantId, clientId, propId, doneByUserType, doneBy, doneByName, doneByRole, activity, description, device) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    clientId,
    propId,
    doneByUserType,
    doneBy,
    doneByName,
    doneByRole,
    activity,
    description,
    device,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

activityLogsDB.getByClientId = async ({ clientId }: activityLogsTypes) => {
  const query =
    "Select * from ActivityLogs where clientId = ? order by id desc";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

activityLogsDB.getByClientIdAndDateRange = async ({ 
  clientId, 
  startDate, 
  endDate,
  staffFilters,
}: activityLogsTypes & { startDate: string; endDate: string; staffFilters: any }) => {
  let query =
    "Select * from ActivityLogs where clientId = ? and DATE(createdAt) BETWEEN ? and ?";
  const data = [clientId, startDate, endDate];

  if (staffFilters && staffFilters.length > 0) {
    query += ` and doneBy in (${staffFilters.map(() => '?').join(',')})`;
    data.push(...staffFilters);
  }

  query += " order by id desc";

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

activityLogsDB.getByDoneByAndDoneByUserType = async ({ 
  clientId,
  doneBy,
  doneByUserType,
}: activityLogsTypes & { startDate: string; endDate: string; }) => {
  let query =
    `Select * from ActivityLogs where clientId = ? and doneBy = ? and doneByUserType = ? order by createdAt desc, id desc limit 50`;
  
  const data = [
    clientId,
    doneBy,
    doneByUserType,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

activityLogsDB.getByClientIdAndDateRangeForStaff = async ({ 
  clientId, 
  startDate, 
  endDate,
  propertiesIds, 
  staffFilters,
}: activityLogsTypes & { startDate: string; endDate: string; propertiesIds: string; staffFilters: any }) => {
  let query =
    `Select * from ActivityLogs where clientId = ? and DATE(createdAt) BETWEEN ? and ? and propId in (${propertiesIds})`;
  const data = [clientId, startDate, endDate];

  if (staffFilters && staffFilters.length > 0) {
    query += ` and doneBy in (${staffFilters.map(() => '?').join(',')})`;
    data.push(...staffFilters);
  }

  query += " order by id desc";

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

activityLogsDB.getByPropIdandDateRange = async ({ propId, startDate, endDate }: activityLogsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select * from ActivityLogs where propId = ? and DATE(createdAt) BETWEEN ? and ? order by id desc";
  const data = [propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

activityLogsDB.getByTenantId = async ({ tenantId, clientId}: activityLogsTypes) => {
  const query =
    "Select * from ActivityLogs where tenantId = ? and clientId=? order by id desc";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};

activityLogsDB.getBySearchValAndClientId = async ({
  clientId,
  searchVal,
}: activityLogsTypes & {searchVal: string}) => {
  const query = 
    "Select * from ActivityLogs where clientId = ? and (doneByName like ? OR tenantId in (Select T.id from Tenants as T join Occupancies as O on T.id = O.tenantId where O.clientId = ? and T.name like ?))";
  const data = [
    clientId,
    `%${searchVal}%`,
    clientId,
    `%${searchVal}%`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

activityLogsDB.getBySearchValAndClientIdForStaff = async ({
  clientId,
  searchVal,
  propertiesIds,
}: activityLogsTypes & {searchVal: string; propertiesIds: string}) => {
  const query = 
    `Select * from ActivityLogs where clientId = ? and propId in (${propertiesIds}) and (doneByName like ? OR tenantId in (Select T.id from Tenants as T join Occupancies as O on T.id = O.tenantId where O.clientId = ? and T.name like ?))`;
  const data = [
    clientId,
    `%${searchVal}%`,
    clientId,
    `%${searchVal}%`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

activityLogsDB.getByDoneByNameAndClientId = async ({
  clientId,
  searchVal,
}: activityLogsTypes & {searchVal: string}) => {
  const query = 
    "Select * from ActivityLogs where clientId = ? and doneByName like ? order by id desc";
  const data = [
    clientId,
    `%${searchVal}%`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

activityLogsDB.getByTenantNameAndClientId = async ({
  clientId,
  searchVal,
}: activityLogsTypes & {searchVal: string}) => {
  const query = 
    "Select * from ActivityLogs where clientId = ? and tenantId in (Select T.id from Tenants as T join Occupancies as O on T.id = O.tenantId where O.clientId = ? and T.name like ?)";
  const data = [
    clientId,
    clientId,
    `%${searchVal}%`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

activityLogsDB.getByDescriptionAndClientId = async ({
  clientId,
  searchVal,
}: activityLogsTypes & {searchVal: string}) => {
  const query = 
    "Select * from ActivityLogs where clientId = ? and description like ? order by id desc";
  const data = [
    clientId,
    `%${searchVal}%`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

activityLogsDB.getByClientIdAndActivity = async ({
  clientId,
  activity,
  staffFilters,
}: activityLogsTypes & {staffFilters: any;}) => {
  let query = 
    "Select * from ActivityLogs where clientId = ? and activity = ?";
  const data = [
    clientId,
    activity,
  ];

  if (staffFilters && staffFilters.length > 0) {
    query += ` and doneBy in (${staffFilters.map(() => '?').join(',')})`;
    data.push(...staffFilters);
  }

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

activityLogsDB.getByClientIdAndActivityForStaff = async ({
  clientId,
  activity,
  propertiesIds,
  staffFilters,
}: activityLogsTypes & {propertiesIds: string; staffFilters: any}) => {
  let query = 
    `Select * from ActivityLogs where clientId = ? and activity = ? and propId in (${propertiesIds})`;
  const data = [
    clientId,
    activity,
  ];

  if (staffFilters && staffFilters.length > 0) {
    query += ` and doneBy in (${staffFilters.map(() => '?').join(',')})`;
    data.push(...staffFilters);
  }

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

activityLogsDB.getByClientIdForReport = async ({
  clientId,
  startDate,
  endDate,
}: activityLogsTypes & {endDate: string; startDate: string}) => {
  const query = 
    `Select AL.*, T.name as tenantName, T.mobile as tenantMobile, P.name as propName from ActivityLogs as AL LEFT JOIN Tenants as T on AL.tenantId = T.id LEFT JOIN Properties as P on AL.propId = P.id where AL.clientId = ? and DATE(AL.createdAt) BETWEEN ? and ? order by AL.id desc`;

  const data = [
    clientId,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

activityLogsDB.getByPropIdForReport = async ({
  propId,
  startDate,
  endDate,
}: { propId: any; endDate: string; startDate: string}) => {
  const query = 
    `Select AL.*, T.name as tenantName, T.mobile as tenantMobile, P.name as propName from ActivityLogs as AL LEFT JOIN Tenants as T on AL.tenantId = T.id LEFT JOIN Properties as P on AL.propId = P.id where AL.propId in (${propId.map(() => '?').join(',')}) and DATE(AL.createdAt) BETWEEN ? and ? order by AL.id desc`;

  const data = [
    ...propId,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

export default activityLogsDB;