import Ticket from "../../models/Ticket";
import AppError from "../../errors/AppError";
import Contact from "../../models/Contact";
import User from "../../models/User";
import Queue from "../../models/Queue";

import Message from "../../models/Message";
import { userInfo } from "os";

import { Op, QueryTypes, json, where } from "sequelize";

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

import { startOfDay, endOfDay, parseISO, getDate } from "date-fns";
import { string } from "yup/lib/locale";
import Whatsapp from "../../models/Whatsapp";
import Query from "mysql2/typings/mysql/lib/protocol/sequences/Query";
import { te } from "date-fns/locale";

interface Request {
  userId: string | number;
  startDate: string;
  endDate: string;
  createdOrUpdated?: string;
  queueId?: string;
  pageNumber?: string;
}

interface Response {
  tickets: Ticket[];
  count: number;
  hasMore: boolean;
}

//Report by user, startDate, endDate
const ShowTicketReport = async ({
  userId,
  startDate,
  endDate,
  pageNumber = "1",
  createdOrUpdated = "created",
  queueId
}: Request): Promise<Response> => {
  // let where_clause: any = {};
  // let query = "";

  // if (userId !== "0") {
  //   where_clause.userid = userId;
  //   query = `AND t.userId = ${userId}`;
  // }

  // if (queueId) {
  //   where_clause.queueId = queueId;
  //   query = `AND t.queueId = ${queueId}`;
  // }

  const createdAtOrUpdatedAt =
    createdOrUpdated == "created" ? "createdAt" : "updatedAt";

  let where_clause = {};

  if (queueId) {
    where_clause = {
      queueId: queueId,
      [createdAtOrUpdatedAt]: {
        [Op.gte]: startDate + " 00:00:00.000000",
        [Op.lte]: endDate + " 23:59:59.999999"
      }
    };
  } else if (userId == "0") {
    where_clause = {
      [createdAtOrUpdatedAt]: {
        [Op.gte]: startDate + " 00:00:00.000000",
        [Op.lte]: endDate + " 23:59:59.999999"
      }
    };
  } else if (userId != "0") {
    where_clause = {
      userid: userId,
      [createdAtOrUpdatedAt]: {
        [Op.gte]: startDate + " 00:00:00.000000",
        [Op.lte]: endDate + " 23:59:59.999999"
      }
    };
  }

  const limit = 40;
  const offset = limit * (+pageNumber - 1);

  let { count, rows: tickets }: any = await Ticket.findAndCountAll({
    where: where_clause,
    limit,
    offset,
    attributes: [
      "id",
      "status",
      "statusChatEnd",
      "isRemote",
      [
        Sequelize.fn(
          "DATE_FORMAT",
          Sequelize.col("Ticket.createdAt"),
          "%d/%m/%Y %H:%i:%s"
        ),
        "createdAt"
      ],
      [
        Sequelize.fn(
          "DATE_FORMAT",
          Sequelize.col("Ticket.updatedAt"),
          "%d/%m/%Y %H:%i:%s"
        ),
        "updatedAt"
      ]
    ],

    include: [
      {
        model: Message,
        required: true,
        separate: true,

        attributes: [
          "body",
          "read",
          "mediaType",
          "fromMe",
          "mediaUrl",
          [
            Sequelize.fn(
              "DATE_FORMAT",
              Sequelize.col("createdAt"),
              "%d/%m/%Y %H:%i:%s"
            ),
            "createdAt"
          ]
        ],
        order: [["createdAt", "ASC"]]
      },
      {
        model: Contact,
        attributes: ["name", "number"]
      },
      {
        model: User,
        attributes: ["name", "email"]
      },
      {
        model: Queue,
        attributes: ["name"]
      },
      {
        model: Whatsapp,
        attributes: ["name"]
      }
    ],
    order: [["updatedAt", "DESC"]]
  });

  const hasMore = count > offset + tickets.length;

  if (!tickets) {
    throw new AppError("ERR_NO_TICKET_FOUND", 404);
  }

  if (tickets.length > 0) {
    const waiting_time: any = await sequelize.query(
      `SELECT t.id as ticketId, t.status, 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 t.id IN (${tickets.map((t: any) => t.id).join()})
      AND m.createdAt = (SELECT MIN(createdAt) FROM Messages WHERE ticketId = t.id)
      AND m.fromMe = 0
      AND t.status IN ('open', 'closed')
      HAVING WAITING_TIME IS NOT NULL
     ORDER BY
        WAITING_TIME;`,
      { type: QueryTypes.SELECT }
    );

    for (let w of waiting_time) {
      const { ticketId, status, WAITING_TIME } = w;

      const index = tickets.findIndex((t: any) => +t?.id == +ticketId);

      if (index != -1) {
        tickets[index].dataValues.waiting_time = WAITING_TIME;
      }
    }
  }

  return { tickets, count, hasMore };
};

export default ShowTicketReport;
