import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import leadActivityLogsTypes from "../schemas/leadActivityLogs.schema";
import CONSTANTS from "../config/constants";
import log from "../config/log";

const leadActivityLogsDB: any = {};

leadActivityLogsDB.logActivity = async ({
  leadId,
  clientId,
  status,
  doneByUserType,
  doneBy,
  doneByName,
  doneByRole,
  activity,
  description,
  device,
}: leadActivityLogsTypes) => {
  const query =
    "Insert into LeadActivityLogs (leadId, clientId, status, doneByUserType, doneBy, doneByName, doneByRole, activity, description, device) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    leadId,
    clientId,
    status,
    doneByUserType,
    doneBy,
    doneByName,
    doneByRole,
    activity,
    description,
    device,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

leadActivityLogsDB.getLeadStatsForStaffByYearMonth = async ({
  staffId,
  month,
  year,
}: {
  staffId: number;
  month: number;
  year: number;
}) => {
  const query = `Select SUM(contacted) AS contacted, SUM(visitDone) AS visitDone, SUM(booked) as booked, SUM(movedIn) as movedIn FROM (Select AL.leadId, MAX(CASE WHEN AL.status = ? THEN 1 ELSE 0 END) as contacted, MAX(CASE WHEN AL.status = ? THEN 1 ELSE 0 END) as visitDone, MAX(CASE WHEN AL.status = ? THEN 1 ELSE 0 END) as booked, MAX(CASE WHEN AL.status = ? THEN 1 ELSE 0 END) as movedIn FROM LeadActivityLogs as AL JOIN Leads as L on AL.leadId = L.id where AL.doneBy = ? and AL.doneByUserType = ? and MONTH(AL.createdAt) = ? and Year(AL.createdAt) = ? group by AL.leadId) as V;`;
  const data = [
    CONSTANTS.LEAD_ACTIVITY_TYPES.CONTACTED,
    CONSTANTS.LEAD_ACTIVITY_TYPES.VISIT_DONE,
    CONSTANTS.LEAD_ACTIVITY_TYPES.CONVERTED,
    CONSTANTS.LEAD_ACTIVITY_TYPES.MOVED_IN,
    staffId,
    CONSTANTS.USER_TYPE.STAFF,
    month,
    year,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  return {
    contacted: 0,
    visitDone: 0,
    booked: 0,
    movedIn: 0,
  };
};

export default leadActivityLogsDB;