import { Sequelize } from "sequelize";

const dbConfig = require("../../config/database");
const sequelize = new Sequelize(dbConfig);
const { QueryTypes } = require("sequelize");

import { splitDateTime } from "../../helpers/SplitDateTime";
import format from "date-fns/format";
import ptBR from "date-fns/locale/pt-BR";
import Whatsapp from "../../models/Whatsapp";
import { number } from "yup";
import ShowWhatsAppService from "../WhatsappService/ShowWhatsAppService";

interface Request {
  startDate: string | number;
  endDate: string;
  queue?: boolean;
  isRemote?: boolean;
}

const ReportByNumberQueueService = async ({
  startDate,
  endDate,
  queue = false,
  isRemote = false
}: Request): Promise<any[]> => {
  let reportServiceData: any[] = [];
  const includeIsRemote = isRemote ? "t.isRemote = true AND" : "";

  const whatsapps = await Whatsapp.findAll();

  if (!queue) {
    for (const whatsapp of whatsapps) {
      const { id, name, number } = whatsapp;

      let startedByClient: any;
      let avgChatWaitingTime: any;
      let pendingChat: any;
      let closedChat: any;

      if (
        !number ||
        reportServiceData.findIndex((w: any) => w?.number == number) != -1
      )
        continue;

      console.log("NUMBER: ", number);

      // CHAT STARTED BY AGENT
      const startedByAgent: any = await sequelize.query(
        `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE ${includeIsRemote} DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999' 
        AND m.createdAt = (SELECT MIN(createdAt) FROM Messages WHERE ticketId = t.id) 
        AND m.fromAgent = 1 
        AND w.number = ${number};`,
        { type: QueryTypes.SELECT }
      );

      if (!isRemote) {
        // CHAT STARTED BY CLIENT
        startedByClient = await sequelize.query(
          `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND m.createdAt = (SELECT MIN(createdAt) FROM Messages WHERE ticketId = t.id) 
        AND m.fromMe = 0 
        AND w.number = ${number};`,
          { type: QueryTypes.SELECT }
        );
      } else {
        // CHAT RESPONSE BY CLIENT
        startedByClient = await sequelize.query(
          `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE ${includeIsRemote} DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND m.createdAt = (SELECT MAX(createdAt) FROM Messages WHERE ticketId = t.id) 
        AND m.fromMe = 0 
        AND w.number = ${number};`,
          { type: QueryTypes.SELECT }
        );
      }

      if (!isRemote) {
        // CHAT CLOSED
        closedChat = await sequelize.query(
          `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND t.status = 'closed' 
        AND w.number = ${number};`,
          { type: QueryTypes.SELECT }
        );
      } else {
        // CHAT CLOSED
        closedChat = await sequelize.query(
          `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t  
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE ${includeIsRemote} DATE(t.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND t.status = 'closed' 
        AND w.number = ${number};`,
          { type: QueryTypes.SELECT }
        );
      }

      if (!isRemote) {
        // CHAT WAINTING TIME
        avgChatWaitingTime = await sequelize.query(
          `
        SELECT  TIME_FORMAT(
        SEC_TO_TIME( 
                TIMESTAMPDIFF(
                    SECOND,
                    (
                        SELECT createdAt 
                        FROM Messages 
                        WHERE ticketId = m.ticketId 
                        AND fromMe = 0 
                        ORDER BY createdAt ASC 
                        LIMIT 1
                    ), 
                    (
                        SELECT createdAt 
                        FROM Messages 
                        WHERE ticketId = m.ticketId 
                        AND fromAgent = 1 
                        ORDER BY createdAt ASC 
                        LIMIT 1
                    )
                ) 
        ), '%H:%i:%s') AS WAITING_TIME      
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
          AND m.createdAt = (SELECT MIN(createdAt) FROM Messages WHERE ticketId = t.id) 
          AND m.fromMe = 0 
          -- AND q.id = 2  
          AND w.number = ${number} 
          AND t.status IN ('open', 'closed')
          HAVING WAITING_TIME IS NOT NULL
        ORDER BY 
            WAITING_TIME;`,
          { type: QueryTypes.SELECT }
        );
      }

      if (!isRemote) {
        // CHAT PENDING
        pendingChat = await sequelize.query(
          `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND t.status = 'pending' 
        AND w.number = ${number};`,

          { type: QueryTypes.SELECT }
        );
      } else {
        // CHAT PENDING REMOTE
        pendingChat = await sequelize.query(
          `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t  
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE ${includeIsRemote} DATE(t.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND t.status = 'pending' 
        AND w.number = ${number};`,

          { type: QueryTypes.SELECT }
        );
      }

      reportServiceData.push({
        id,
        name,
        number,
        startedByAgent: startedByAgent[0]?.ticket_count,
        startedByClient: startedByClient ? startedByClient[0]?.ticket_count : 0,
        closedChat: closedChat[0]?.ticket_count,
        avgChatWaitingTime: avgChatWaitingTime ? avg(avgChatWaitingTime) : 0,
        pendingChat: pendingChat[0]?.ticket_count
      });
    }
  } else {
    for (const whatsapp of whatsapps) {
      const { id, name, number } = whatsapp;
      let startedByClient: any;
      let avgChatWaitingTime: any;
      let pendingChat: any;
      let closedChat: any;

      if (
        !number ||
        reportServiceData.findIndex((w: any) => w?.number == number) != -1
      )
        continue;

      const data = await ShowWhatsAppService(id);

      const queues: any = data.queues.map((q: any) => {
        const { id, name, color } = q;
        return { id, name, color };
      });

      console.log("NUMBER 2: ", number);

      for (const q of queues) {
        // CHAT STARTED BY AGENT
        const startedByAgent: any = await sequelize.query(
          `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE ${includeIsRemote} DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999' 
        AND m.createdAt = (SELECT MIN(createdAt) FROM Messages WHERE ticketId = t.id) 
        AND m.fromAgent = 1 
        AND q.id = ${q.id};`,
          { type: QueryTypes.SELECT }
        );

        if (!isRemote) {
          // CHAT STARTED BY CLIENT
          startedByClient = await sequelize.query(
            `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND m.createdAt = (SELECT MIN(createdAt) FROM Messages WHERE ticketId = t.id) 
        AND m.fromMe = 0 
        AND q.id = ${q.id};`,
            { type: QueryTypes.SELECT }
          );
        } else {
          // CHAT RESPONSE BY CLIENT
          startedByClient = await sequelize.query(
            `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE ${includeIsRemote} DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND m.createdAt = (SELECT MAX(createdAt) FROM Messages WHERE ticketId = t.id) 
        AND m.fromMe = 0 
        AND q.id = ${q.id};`,
            { type: QueryTypes.SELECT }
          );
        }

        if (!isRemote) {
          // CHAT CLOSED
           closedChat = await sequelize.query(
            `SELECT COUNT(DISTINCT t.id) AS ticket_count       
            FROM Tickets t 
            JOIN Messages m ON t.id = m.ticketId 
            JOIN Whatsapps w ON t.whatsappId = w.id
            JOIN Queues q ON q.id = t.queueId
            WHERE DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
            AND t.status = 'closed' 
          AND q.id = ${q.id};`,
                { type: QueryTypes.SELECT }
          );
        }
        else{
          // CHAT CLOSED REMOTE
          closedChat = await sequelize.query(
            `SELECT COUNT(DISTINCT t.id) AS ticket_count       
            FROM Tickets t  
            JOIN Whatsapps w ON t.whatsappId = w.id
            JOIN Queues q ON q.id = t.queueId
            WHERE ${includeIsRemote} DATE(t.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
            AND t.status = 'closed' 
            AND q.id = ${q.id};`,
            { type: QueryTypes.SELECT }
          );
        }

        if (!isRemote) {
          // CHAT WAINTING TIME
          avgChatWaitingTime = await sequelize.query(
            `SELECT TIME_FORMAT(
              SEC_TO_TIME(
                  TIMESTAMPDIFF(
                          SECOND,
                          (
                              SELECT createdAt 
                              FROM Messages 
                              WHERE ticketId = m.ticketId 
                              AND fromMe = 0 
                              ORDER BY createdAt ASC 
                              LIMIT 1
                          ), 
                          (
                              SELECT createdAt 
                              FROM Messages 
                              WHERE ticketId = m.ticketId 
                              AND fromAgent = 1 
                              ORDER BY createdAt ASC 
                              LIMIT 1
                          )
                      ) 
              ), '%H:%i:%s') AS WAITING_TIME      
          FROM Tickets t 
          JOIN Messages m ON t.id = m.ticketId
          JOIN Whatsapps w ON t.whatsappId = w.id
          JOIN Queues q ON q.id = t.queueId
          WHERE DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
          AND m.createdAt = (SELECT MIN(createdAt) FROM Messages WHERE ticketId = t.id) 
          AND m.fromMe = 0  
          AND q.id = ${q.id}
          AND t.status IN ('open', 'closed')
          HAVING WAITING_TIME IS NOT NULL
          ORDER BY 
              WAITING_TIME;`,
            { type: QueryTypes.SELECT }
          );
        }

        if (!isRemote) {
          // CHAT PENDING
          pendingChat = await sequelize.query(
            `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t 
        JOIN Messages m ON t.id = m.ticketId 
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE DATE(m.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND t.status = 'pending' 
        AND q.id = ${q.id};`,

            { type: QueryTypes.SELECT }
          );
        } else {
          // CHAT PENDING REMOTE
          pendingChat = await sequelize.query(
            `SELECT COUNT(DISTINCT t.id) AS ticket_count       
        FROM Tickets t  
        JOIN Whatsapps w ON t.whatsappId = w.id
        JOIN Queues q ON q.id = t.queueId
        WHERE ${includeIsRemote} DATE(t.createdAt) BETWEEN '${startDate} 00:00:00.000000' AND '${endDate} 23:59:59.999999'  
        AND t.status = 'pending' 
        AND q.id = ${q.id};`,

            { type: QueryTypes.SELECT }
          );
        }

        reportServiceData.push({
          id,
          name,
          number,
          queueName: q.name,
          queueColor: q.color,
          startedByAgent: startedByAgent[0]?.ticket_count,
          startedByClient: startedByClient
            ? startedByClient[0]?.ticket_count
            : 0,
          closedChat: closedChat[0]?.ticket_count,
          avgChatWaitingTime: avgChatWaitingTime ? avg(avgChatWaitingTime) : 0,
          pendingChat: pendingChat[0]?.ticket_count
        });
      }
    }
  }

  return reportServiceData;
};

export default ReportByNumberQueueService;

function avg(avgChatWaitingTime: any) {
  let waitingAVG: any = avgChatWaitingTime
    .filter((t: any) => t?.WAITING_TIME)
    .map((t: any) => t.WAITING_TIME);

  if (waitingAVG.length > 0) {
    let midIndex = Math.floor((0 + waitingAVG.length) / 2);

    if (waitingAVG.length % 2 == 1) {
      waitingAVG = waitingAVG[midIndex];
    } else {
      waitingAVG = calculateAverageTime(
        waitingAVG[midIndex - 1],
        waitingAVG[midIndex]
      );
    }
  } else {
    waitingAVG = 0;
  }
  return waitingAVG;
}

function calculateAverageTime(time1: string, time2: string) {
  // Function to parse time string to seconds
  function timeStringToSeconds(timeString: string) {
    const [hours, minutes, seconds] = timeString.split(":").map(Number);
    return hours * 3600 + minutes * 60 + seconds;
  }

  // Function to convert seconds to time string
  function secondsToTimeString(seconds: number) {
    const hours = Math.floor(seconds / 3600);
    const minutes = Math.floor((seconds % 3600) / 60);
    const remainingSeconds = seconds % 60;
    return `${hours.toString().padStart(2, "0")}:${minutes
      .toString()
      .padStart(2, "0")}:${remainingSeconds.toString().padStart(2, "0")}`;
  }

  // Convert time strings to seconds
  const time1Seconds = timeStringToSeconds(time1);
  const time2Seconds = timeStringToSeconds(time2);

  // Calculate average seconds
  const averageSeconds = Math.round((time1Seconds + time2Seconds) / 2);

  // Convert average seconds back to time string
  const averageTime = secondsToTimeString(averageSeconds);

  return averageTime;
}
