Untitled

const validator = require('../helpers/validator');
const setting = require('../helpers/setting');
const constants = require('../helpers/constants');
const calculator = require('../helpers/calculator');
const googleAPIHelper = require('../helpers/googleapi');
const db = require('../models');
const md5 = require('md5');
const jwt = require('jsonwebtoken');
const axios = require('axios');
const empty = require('is-empty');
const isNumber = require('is-number');
const sequelize = require('sequelize');
const Op = sequelize.Op;

const User = db.User;
const Customer = db.Customer;
const Payment = db.Payment;
const Sales = db.Sales;
const Due = db.Due;
const Recommend = db.Recommend;
const Itinerary = db.Itinerary;
const ItineraryAllocation = db.ItineraryAllocation;

class ItineraryController {
  static fetchAll = async (req, res) => {
    if (req.auth === undefined || req.auth.id === undefined) {
      return res.status(401).send({
        message: 'JWT Token invalid',
      });
    }

    // Current month, year
    const currentDateString = new Date().toLocaleString('en-US', {
      timeZone: setting.DEFAULT_COUNTRY_TIME_ZONE,
    });
    let currentDate = new Date(currentDateString);

    let currentMonth = currentDate.getMonth() + 1;
    let currentYear = currentDate.getFullYear();
    let currentDay = currentDate.getDate();

    const user = await User.findOne({
      where: {
        id: req.auth.id,
      },
    });

    if (user === null) {
      return res.status(401).send({
        message: 'User does not exist',
      });
    }

    if (user.role !== 'M' && user.role !== 'C') {
      return res.status(401).send({
        message: 'Role does not have enough permission',
      });
    }

    const users = await User.findAll({
      where: {
        role: 'F',
      },
    });

    const customers = await Customer.findAll({ attributes: ['zipcode', 'city'], group: 'zipcode' });

    let zipCities = {};

    for (let i = 0; i < customers.length; i++) {
      const zipcode = Number(customers[i].zipcode);
      const city = customers[i].city;
      if (!empty(zipcode) && !isNaN(zipcode) && isNumber(zipcode) && !(zipcode in zipCities)) {
        zipCities[zipcode] = city;
      }
    }

    for (const user of users) {
      const token = this.generateAccessToken(user);

      // * call /month-summary API
      await axios({
        method: 'GET',
        url: constants.TVS_API_URL + '/v1/month-summary',
        headers: {
          'Content-Type': 'application/json',
          Authorization: 'Bearer ' + token,
        },
      })
        .then((response) => {
          if (response.status == 200) {
            return response.data;
          }
        })
        .then(async (data) => {
          let itinerary = null;
          let recal_ptp_visit_pending = 0;
          let recal_mandatory_visit_pending = 0;
          let recal_regular_visit_pending = 0;

          let ptp_visit_total_recal = 0;
          let mandatory_visit_total_recal = 0;
          let regular_visit_total_recal = 0;

          await Itinerary.findOrCreate({
            where: {
              month: currentMonth,
              year: currentYear,
              user_id: user.id,
              agent_id: user.agent_id,
              client_code: user.client_code,
            },
            defaults: {
              month: currentMonth,
              year: currentYear,
              user_id: user.id,
              agent_id: user.agent_id,
              client_code: user.client_code,
              is_ai: user.is_ai,
              cs_name: user.first_name + ' ' + user.last_name,
              ptp_visit_pending: 0,
              mandatory_visit_pending: 0,
              regular_visit_pending: 0,
            },
          })
            .then(function (result) {
              itinerary = result[0];
            })
            .catch((err) => {
              console.log('Error occurred while get itinerary ' + err.message);
            });

          for (const cluster of data.clusters) {
            const lat = cluster.cluster_coordinates[0];
            const lng = cluster.cluster_coordinates[1];

            if (empty(lat) || empty(lng)) {
              continue;
            }

            let itineraryAlloc = null;

            recal_ptp_visit_pending = recal_ptp_visit_pending + cluster.ptp_visit;
            recal_mandatory_visit_pending = recal_mandatory_visit_pending + cluster.mandatory_visit;
            recal_regular_visit_pending = recal_regular_visit_pending + cluster.regular_visit;

            await ItineraryAllocation.findOrCreate({
              where: {
                month: currentMonth,
                year: currentYear,
                itinerary_id: itinerary.id,
                user_id: user.id,
                agent_id: user.agent_id,
                client_code: user.client_code,
                cs_name: itinerary.cs_name,
                zipcode: cluster.zipcode,
              },
              defaults: {
                month: currentMonth,
                year: currentYear,
                itinerary_id: itinerary.id,
                user_id: user.id,
                agent_id: user.agent_id,
                client_code: user.client_code,
                cs_name: itinerary.cs_name,
                zipcode: cluster.zipcode,
                lat: lat,
                lng: lng,
                ptp_visit_pending: cluster.ptp_visit,
                ptp_visit_total: cluster.ptp_visit,
                ptp_visit_completed: 0,
                ptp_visit_percent: '0',
                mandatory_visit_pending: cluster.mandatory_visit,
                mandatory_visit_total: cluster.mandatory_visit,
                mandatory_visit_completed: 0,
                mandatory_visit_percent: '0',
                regular_visit_pending: cluster.regular_visit,
                regular_visit_total: cluster.regular_visit,
                regular_visit_completed: 0,
                regular_visit_percent: '0',
              },
            })
              .then(async function (result) {
                itineraryAlloc = result[0];
                let created = result[1];

                if (created) {
                  // * total
                  ptp_visit_total_recal = ptp_visit_total_recal + cluster.ptp_visit;
                  mandatory_visit_total_recal = mandatory_visit_total_recal + cluster.mandatory_visit;
                  regular_visit_total_recal = regular_visit_total_recal + cluster.regular_visit;

                  itinerary.ptp_visit_total = itinerary.ptp_visit_total + cluster.ptp_visit;
                  itinerary.mandatory_visit_total = itinerary.mandatory_visit_total + cluster.mandatory_visit;
                  itinerary.regular_visit_total = itinerary.regular_visit_total + cluster.regular_visit;

                  await itinerary.save();
                }
              })
              .catch((err) => {
                console.log('Error occurred while get itinerary allocation' + err.message);
              });

            // * use Google API
            await axios({
              method: 'GET',
              url:
                'https://maps.googleapis.com/maps/api/geocode/json?latlng=' +
                lat +
                ',' +
                lng +
                '&sensor=true&key=' +
                constants.GOOGLE_GEOCODING_API_KEY,
              headers: {
                'Content-Type': 'application/json',
              },
            })
              .then(async (response) => {
                if (response.status == 200) {
                  let geoData = googleAPIHelper.getAddressParts(response.data.results[0]);
                  if (empty(geoData.locality)) {
                    geoData = googleAPIHelper.getAddressParts(response.data.results[1]);
                  }

                  if (!empty(geoData.locality)) {
                    itineraryAlloc.city = geoData.locality;
                  } else {
                    // ! still cannot find city...
                    itineraryAlloc.city = zipCities[itineraryAlloc.zipcode];
                  }

                  await itineraryAlloc.save();

                  return;
                }
              })
              .catch((err) => {
                console.log('Error occurred while get geo data ' + err.message);
              });
          }

          itinerary.ptp_visit_pending = recal_ptp_visit_pending;
          itinerary.mandatory_visit_pending = recal_mandatory_visit_pending;
          itinerary.regular_visit_pending = recal_regular_visit_pending;

          await itinerary.save();
        })
        .catch(function (error) {
          console.log(error);
        });
    }

    return res.status(204).send({});
  };

  static fetch = async (req, res) => {
    if (req.auth === undefined || req.auth.id === undefined) {
      return res.status(401).send({
        message: 'JWT Token invalid',
      });
    }

    // Current month, year
    const currentDateString = new Date().toLocaleString('en-US', {
      timeZone: setting.DEFAULT_COUNTRY_TIME_ZONE,
    });
    const currentDate = new Date(currentDateString);

    const currentMonth = currentDate.getMonth() + 1;
    const currentYear = currentDate.getFullYear();
    const currentDay = currentDate.getDate();

    const de = new Date(currentDate.getFullYear(), currentMonth - 2, currentDay);
    const previousYear = de.getFullYear();
    const previousMonth = de.getMonth() + 1;

    const user = await User.findOne({
      where: {
        id: req.auth.id,
      },
    });

    const customers = await Customer.findAll({ attributes: ['zipcode', 'city'], group: 'zipcode' });

    let zipCities = {};

    for (let i = 0; i < customers.length; i++) {
      const zipcode = Number(customers[i].zipcode);
      const city = customers[i].city;
      if (!empty(zipcode) && !isNaN(zipcode) && isNumber(zipcode) && !(zipcode in zipCities)) {
        zipCities[zipcode] = city;
      }
    }

    const token = this.generateAccessToken(user);

    // * call /month-summary API
    await axios({
      method: 'GET',
      url: constants.TVS_API_URL + '/v1/month-summary',
      headers: {
        'Content-Type': 'application/json',
        Authorization: 'Bearer ' + token,
      },
    })
      .then((response) => {
        if (response.status == 200) {
          return response.data;
        }
      })
      .then(async (data) => {
        let itinerary = null;

        // * get recommend
        console.log('==== recommends');
        const recommends = await Recommend.findAll({
          attributes: ['id'],
          where: {
            [Op.and]: [
              sequelize.where(sequelize.col('year'), currentYear),
              sequelize.where(sequelize.col('month'), currentMonth),
            ],
            client_code: user.client_code,
          },
          include: [
            {
              model: Sales,
              attributes: ['id'],
              as: 'sales',
              required: false,
              include: [
                {
                  model: Due,
                  attributes: ['id', 'expected_amt'],
                  as: 'dues',
                  required: false,
                  where: {
                    month_due_date: {
                      [Op.and]: [
                        sequelize.where(sequelize.fn('MONTH', sequelize.col('month_due_date')), currentMonth),
                        sequelize.where(sequelize.fn('YEAR', sequelize.col('month_due_date')), currentYear),
                      ],
                    },
                  },
                  limit: 1,
                },
              ],
            },
          ],
        });

        // * get list payment
        console.log('==== all_current_not_visits');
        const all_current_not_visits = await Payment.findAll({
          raw: true,
          attributes: ['rec_amt', 'acct_number', 'payment_note'],
          where: {
            [Op.and]: [
              sequelize.where(sequelize.col('createdBy'), 'API'),
              sequelize.where(
                sequelize.fn(
                  'YEAR',
                  sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
                ),
                currentYear,
              ),
              sequelize.where(
                sequelize.fn(
                  'MONTH',
                  sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
                ),
                currentMonth,
              ),
              !validator.isLastDayOfMonth(currentDay)
                ? sequelize.where(
                    sequelize.fn(
                      'DAY',
                      sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
                    ),
                    {
                      [Op.lte]: currentDay,
                    },
                  )
                : '',
            ],
            payment_note: {
              [Op.in]: ['Skip Visit'],
            },
            client_code: user.client_code,
          },
        });

        console.log('==== all_current_with_not_visit_payments');
        const all_current_with_not_visit_payments = await Payment.findAll({
          raw: true,
          attributes: ['rec_amt', 'acct_number', 'payment_note', 'visit_type'],
          where: {
            [Op.and]: [
              sequelize.where(sequelize.col('createdBy'), 'API'),
              sequelize.where(
                sequelize.fn(
                  'YEAR',
                  sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
                ),
                currentYear,
              ),
              sequelize.where(
                sequelize.fn(
                  'MONTH',
                  sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
                ),
                currentMonth,
              ),
              !validator.isLastDayOfMonth(currentDay)
                ? sequelize.where(
                    sequelize.fn(
                      'DAY',
                      sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
                    ),
                    {
                      [Op.lte]: currentDay,
                    },
                  )
                : '',
            ],
            client_code: user.client_code,
          },
        });

        // * calculate recovery rate
        let completed_visits = 0;
        let expected_visits = 0;
        let collected_amount = 0;
        let expected_amount = 0;

        for (const p of all_current_with_not_visit_payments) {
          var actual_collection = parseFloat(p['rec_amt']);
          if (!empty(actual_collection) && p.payment_note != 'Skip Visit') {
            completed_visits++;
            collected_amount = collected_amount + actual_collection;
          }
        }

        for (const recommend of recommends) {
          const expectedl_collection = parseFloat(recommend.sales.dues[0].expected_amt);
          expected_visits++;
          expected_amount = expected_amount + expectedl_collection;
        }

        await Itinerary.findOrCreate({
          where: {
            month: currentMonth,
            year: currentYear,
            user_id: user.id,
            agent_id: user.agent_id,
            client_code: user.client_code,
          },
          defaults: {
            month: currentMonth,
            year: currentYear,
            user_id: user.id,
            agent_id: user.agent_id,
            client_code: user.client_code,
            is_ai: user.is_ai,
            cs_name: user.first_name + ' ' + user.last_name,
            itinerary_incompliant: 0.0,
            itinerary_incompliant_status: 'nil',
            skipped_visits: 0,
            collection_efficiency: 0.0,
            recovery_rate: 0.0,
            ptp_visit_pending: 0,
            ptp_visit_completed: 0,
            ptp_visit_percent: 0.0,
            mandatory_visit_pending: 0,
            mandatory_visit_completed: 0,
            mandatory_visit_percent: 0.0,
            regular_visit_pending: 0,
            regular_visit_completed: 0,
            regular_visit_percent: 0.0,
          },
          include: [
            {
              model: ItineraryAllocation,
              as: 'itinerary_allocations',
              require: false,
            },
          ],
        })
          .then(function (result) {
            itinerary = result[0];
          })
          .catch((err) => {
            console.log('Error occurred while get itinerary ' + err.message);
          });

        for (const cluster of data.clusters) {
          const lat = cluster.cluster_coordinates[0];
          const lng = cluster.cluster_coordinates[1];

          if (empty(lat) || empty(lng)) {
            continue;
          }

          let itineraryAlloc = null;

          await ItineraryAllocation.findOrCreate({
            where: {
              month: currentMonth,
              year: currentYear,
              itinerary_id: itinerary.id,
              user_id: user.id,
              agent_id: user.agent_id,
              client_code: user.client_code,
              zipcode: cluster.zipcode,
            },
            defaults: {
              month: currentMonth,
              year: currentYear,
              itinerary_id: itinerary.id,
              user_id: user.id,
              agent_id: user.agent_id,
              client_code: user.client_code,
              cs_name: itinerary.cs_name,
              zipcode: cluster.zipcode,
              lat: lat,
              lng: lng,
              ptp_visit_pending: cluster.ptp_visit,
              ptp_visit_total: cluster.ptp_visit,
              ptp_visit_completed: 0,
              ptp_visit_percent: 0.0,
              mandatory_visit_pending: cluster.mandatory_visit,
              mandatory_visit_total: cluster.mandatory_visit,
              mandatory_visit_completed: 0,
              mandatory_visit_percent: 0.0,
              regular_visit_pending: cluster.regular_visit,
              regular_visit_total: cluster.regular_visit,
              regular_visit_completed: 0,
              regular_visit_percent: 0.0,
            },
          })
            .then(async function (result) {
              itineraryAlloc = result[0];
              let created = result[1];

              if (created) {
                // * call Google API
                await axios({
                  method: 'GET',
                  url:
                    'https://maps.googleapis.com/maps/api/geocode/json?latlng=' +
                    lat +
                    ',' +
                    lng +
                    '&sensor=true&key=' +
                    constants.GOOGLE_GEOCODING_API_KEY,
                  headers: {
                    'Content-Type': 'application/json',
                  },
                })
                  .then(async (response) => {
                    if (response.status == 200) {
                      let geoData = googleAPIHelper.getAddressParts(response.data.results[0]);
                      if (empty(geoData.locality)) {
                        geoData = googleAPIHelper.getAddressParts(response.data.results[1]);
                      }

                      if (!empty(geoData.locality)) {
                        itineraryAlloc.city = geoData.locality;
                      } else {
                        // ! still cannot find city...
                        itineraryAlloc.city = zipCities[itineraryAlloc.zipcode];
                      }
                      return;
                    }
                  })
                  .catch((err) => {
                    console.log('Error occurred while get geo data ' + err.message);
                  });
              }

              itineraryAlloc.ptp_visit_pending = cluster.ptp_visit;
              itineraryAlloc.mandatory_visit_pending = cluster.mandatory_visit;
              itineraryAlloc.regular_visit_pending = cluster.regular_visit;

              itineraryAlloc.ptp_visit_total = itineraryAlloc.ptp_visit_pending + itineraryAlloc.ptp_visit_completed;
              itineraryAlloc.mandatory_visit_total =
                itineraryAlloc.mandatory_visit_pending + itineraryAlloc.mandatory_visit_completed;
              itineraryAlloc.regular_visit_total =
                itineraryAlloc.regular_visit_pending + itineraryAlloc.regular_visit_completed;

              // * calculate % completed
              if (itineraryAlloc.ptp_visit_pending == 0 && itineraryAlloc.ptp_visit_completed != 0) {
                itineraryAlloc.ptp_visit_percent = 100.0;
              } else {
                itineraryAlloc.ptp_visit_percent =
                  itineraryAlloc.ptp_visit_completed != 0 && itineraryAlloc.ptp_visit_total != 0
                    ? ((itineraryAlloc.ptp_visit_completed / itineraryAlloc.ptp_visit_total) * 100.0).toFixed(1)
                    : 0.0;
              }

              await itineraryAlloc.save();
            })
            .catch((err) => {
              console.log('Error occurred while get itinerary allocation' + err.message);
            });
        }

        await itinerary.reload();

        let recal_ptp_visit_total = 0;
        let recal_mandatory_visit_total = 0;
        let recal_regular_visit_total = 0;

        let recal_ptp_visit_pending = 0;
        let recal_mandatory_visit_pending = 0;
        let recal_regular_visit_pending = 0;

        for (const ia of itinerary.itinerary_allocations) {
          recal_ptp_visit_total = recal_ptp_visit_total + ia.ptp_visit_total;
          recal_mandatory_visit_total = recal_mandatory_visit_total + ia.mandatory_visit_total;
          recal_regular_visit_total = recal_regular_visit_total + ia.regular_visit_total;

          recal_ptp_visit_pending = recal_ptp_visit_pending + ia.ptp_visit_pending;
          recal_mandatory_visit_pending = recal_mandatory_visit_pending + ia.mandatory_visit_pending;
          recal_regular_visit_pending = recal_regular_visit_pending + ia.regular_visit_pending;
        }

        itinerary.ptp_visit_total = recal_ptp_visit_total;
        itinerary.mandatory_visit_total = recal_mandatory_visit_total;
        itinerary.regular_visit_total = recal_regular_visit_total;

        itinerary.ptp_visit_pending = recal_ptp_visit_pending;
        itinerary.mandatory_visit_pending = recal_mandatory_visit_pending;
        itinerary.regular_visit_pending = recal_regular_visit_pending;

        // * calculate % completed
        if (itinerary.ptp_visit_pending == 0 && itinerary.ptp_visit_completed != 0) {
          itinerary.ptp_visit_percent = 100.0;
        } else {
          itinerary.ptp_visit_percent =
            itinerary.ptp_visit_completed != 0 && itinerary.ptp_visit_total != 0
              ? ((itinerary.ptp_visit_completed / itinerary.ptp_visit_total) * 100.0).toFixed(1)
              : 0.0;
        }

        // * get previous itinerary
        const prev_itinerary = await Itinerary.findOne({
          where: {
            user_id: user.id,
            month: previousMonth,
            year: previousYear,
          },
        });

        // * calculate Itinerary Non-compliance
        const current_itinerary_non_compliance =
          all_current_not_visits.length != 0 && all_current_with_not_visit_payments.length != 0
            ? (all_current_not_visits.length / all_current_with_not_visit_payments.length) * 100
            : 0;
        const prev_itinerary_non_compliance = !empty(prev_itinerary) ? prev_itinerary.itinerary_incompliant : 0;

        // * recalculate collection efficiency, recovery rate, skipped visits
        itinerary.collection_efficiency = ((data.collected_amount / data.expected_amount) * 100.0).toFixed(1);
        if (!empty(completed_visits) && !empty(expected_visits)) {
          itinerary.recovery_rate = ((completed_visits / expected_visits) * 100.0).toFixed(1);
        }

        itinerary.itinerary_incompliant = current_itinerary_non_compliance.toFixed(1);
        itinerary.itinerary_incompliant_status = calculator.calculatePercentStatus(
          prev_itinerary_non_compliance,
          current_itinerary_non_compliance,
        );

        itinerary.skipped_visits = all_current_not_visits.length;

        await itinerary.save();
      })
      .catch(function (error) {
        console.log(error);
      });

    return res.status(204).send({});
  };

  static recalculateItinerary = async (payment, user, customer) => {
    // Current month, year
    const currentDateString = new Date().toLocaleString('en-US', {
      timeZone: setting.DEFAULT_COUNTRY_TIME_ZONE,
    });
    const currentDate = new Date(currentDateString);
    const currentMonth = currentDate.getMonth() + 1; // 0=January
    const currentYear = currentDate.getFullYear(); // 2021
    const currentDay = currentDate.getDate();

    const de = new Date(currentDate.getFullYear(), currentMonth - 2, currentDay);
    const previousYear = de.getFullYear();
    const previousMonth = de.getMonth() + 1;

    // * get itinerary allocation
    const itineraryAlloc = await ItineraryAllocation.findOne({
      where: {
        user_id: user.id,
        agent_id: user.agent_id,
        zipcode: Number(customer.zipcode),
        month: currentMonth,
        year: currentYear,
      },
    });

    // * calculate completed & total
    switch (payment.visit_type) {
      case 'regular':
        itineraryAlloc.regular_visit_pending = itineraryAlloc.regular_visit_pending - 1;

        if (payment.payment_note != 'Skip Visit') {
          itineraryAlloc.regular_visit_completed = itineraryAlloc.regular_visit_completed + 1;
        }

        itineraryAlloc.regular_visit_total =
          itineraryAlloc.regular_visit_pending + itineraryAlloc.regular_visit_completed;
        break;
      case 'mandatory':
        itineraryAlloc.mandatory_visit_pending = itineraryAlloc.mandatory_visit_pending - 1;

        if (payment.payment_note != 'Skip Visit') {
          itineraryAlloc.mandatory_visit_completed = itineraryAlloc.mandatory_visit_completed + 1;
        }

        itineraryAlloc.mandatory_visit_total =
          itineraryAlloc.mandatory_visit_pending + itineraryAlloc.mandatory_visit_completed;
        break;
      case 'ptp':
        itineraryAlloc.ptp_visit_pending = itineraryAlloc.ptp_visit_pending - 1;

        if (payment.payment_note != 'Skip Visit') {
          itineraryAlloc.ptp_visit_completed = itineraryAlloc.ptp_visit_completed + 1;
        }

        if (this.isIncreasedPTP(payment)) {
          itineraryAlloc.ptp_visit_pending = itineraryAlloc.ptp_visit_pending + 1;
          itinerary.ptp_visit_pending = itinerary.ptp_visit_pending + 1;
        }

        itineraryAlloc.ptp_visit_total = itineraryAlloc.ptp_visit_pending + itineraryAlloc.ptp_visit_completed;
        break;
    }

    // * calculate % completed itinerary allocation
    if (itineraryAlloc.ptp_visit_pending == 0 && itineraryAlloc.ptp_visit_completed != 0) {
      itineraryAlloc.ptp_visit_percent = 100.0;
    } else {
      itineraryAlloc.ptp_visit_percent =
        itineraryAlloc.ptp_visit_completed != 0 && itineraryAlloc.ptp_visit_total != 0
          ? ((itineraryAlloc.ptp_visit_completed / itineraryAlloc.ptp_visit_total) * 100.0).toFixed(1)
          : 0.0;
    }

    await itineraryAlloc.save();

    const token = this.generateAccessToken(user);

    // * call /month-summary API
    await axios({
      method: 'GET',
      url: constants.TVS_API_URL + '/v1/month-summary',
      headers: {
        'Content-Type': 'application/json',
        Authorization: 'Bearer ' + token,
      },
    })
      .then((response) => {
        if (response.status == 200) {
          return response.data;
        }
      })
      .then(async (data) => {
        for (const cluster of data.clusters) {
          await ItineraryAllocation.findOne({
            where: {
              month: currentMonth,
              year: currentYear,
              user_id: user.id,
              agent_id: user.agent_id,
              client_code: user.client_code,
              zipcode: cluster.zipcode,
            },
          })
            .then(async function (result) {
              itineraryAlloc = result;

              itineraryAlloc.ptp_visit_pending = cluster.ptp_visit;
              itineraryAlloc.mandatory_visit_pending = cluster.mandatory_visit;
              itineraryAlloc.regular_visit_pending = cluster.regular_visit;

              itineraryAlloc.ptp_visit_total = itineraryAlloc.ptp_visit_pending + itineraryAlloc.ptp_visit_completed;
              itineraryAlloc.mandatory_visit_total =
                itineraryAlloc.mandatory_visit_pending + itineraryAlloc.mandatory_visit_completed;
              itineraryAlloc.regular_visit_total =
                itineraryAlloc.regular_visit_pending + itineraryAlloc.regular_visit_completed;

              // * calculate % completed
              if (itineraryAlloc.ptp_visit_pending == 0 && itineraryAlloc.ptp_visit_completed != 0) {
                itineraryAlloc.ptp_visit_percent = 100.0;
              } else {
                itineraryAlloc.ptp_visit_percent =
                  itineraryAlloc.ptp_visit_completed != 0 && itineraryAlloc.ptp_visit_total != 0
                    ? ((itineraryAlloc.ptp_visit_completed / itineraryAlloc.ptp_visit_total) * 100.0).toFixed(1)
                    : 0.0;
              }

              await itineraryAlloc.save();
            })
            .catch((err) => {
              console.log('Error occurred while get itinerary allocation' + err.message);
            });
        }
      })
      .catch(function (error) {
        console.log(error);
      });

    // * get itinerary
    const itinerary = await Itinerary.findOne({
      include: [
        {
          model: ItineraryAllocation,
          as: 'itinerary_allocations',
          require: false,
        },
      ],
      where: {
        user_id: user.id,
        month: currentMonth,
        year: currentYear,
      },
    });

    // * get previous itinerary
    const prev_itinerary = await Itinerary.findOne({
      where: {
        user_id: user.id,
        month: previousMonth,
        year: previousYear,
      },
    });

    let recal_ptp_visit_total = 0;
    let recal_mandatory_visit_total = 0;
    let recal_regular_visit_total = 0;

    let recal_ptp_visit_completed = 0;
    let recal_mandatory_visit_completed = 0;
    let recal_regular_visit_completed = 0;

    let recal_ptp_visit_pending = 0;
    let recal_mandatory_visit_pending = 0;
    let recal_regular_visit_pending = 0;

    for (const ia of itinerary.itinerary_allocations) {
      recal_ptp_visit_total = recal_ptp_visit_total + ia.ptp_visit_total;
      recal_mandatory_visit_total = recal_mandatory_visit_total + ia.mandatory_visit_total;
      recal_regular_visit_total = recal_regular_visit_total + ia.regular_visit_total;

      recal_ptp_visit_completed = recal_ptp_visit_completed + ia.ptp_visit_completed;
      recal_mandatory_visit_completed = recal_mandatory_visit_completed + ia.mandatory_visit_completed;
      recal_regular_visit_completed = recal_regular_visit_completed + ia.regular_visit_completed;

      recal_ptp_visit_pending = recal_ptp_visit_pending + ia.ptp_visit_pending;
      recal_mandatory_visit_pending = recal_mandatory_visit_pending + ia.mandatory_visit_pending;
      recal_regular_visit_pending = recal_regular_visit_pending + ia.regular_visit_pending;
    }

    itinerary.ptp_visit_total = recal_ptp_visit_total;
    itinerary.mandatory_visit_total = recal_mandatory_visit_total;
    itinerary.regular_visit_total = recal_regular_visit_total;

    itinerary.ptp_visit_completed = recal_ptp_visit_completed;
    itinerary.mandatory_visit_completed = recal_mandatory_visit_completed;
    itinerary.regular_visit_completed = recal_regular_visit_completed;

    itinerary.ptp_visit_pending = recal_ptp_visit_pending;
    itinerary.mandatory_visit_pending = recal_mandatory_visit_pending;
    itinerary.regular_visit_pending = recal_regular_visit_pending;

    // * calculate % completed itinerary
    if (itinerary.ptp_visit_pending == 0 && itinerary.ptp_visit_completed != 0) {
      itinerary.ptp_visit_percent = 100.0;
    } else {
      itinerary.ptp_visit_percent =
        itinerary.ptp_visit_completed != 0 && itinerary.ptp_visit_total != 0
          ? ((itinerary.ptp_visit_completed / itinerary.ptp_visit_total) * 100.0).toFixed(1)
          : 0.0;
    }

    // TODO: recalculate collection efficiency, recovery rate, skipped visits
    // * get recommend
    console.log('==== recommends');
    const recommends = await Recommend.findAll({
      attributes: ['id'],
      where: {
        [Op.and]: [
          sequelize.where(sequelize.col('year'), currentYear),
          sequelize.where(sequelize.col('month'), currentMonth),
        ],
        client_code: user.client_code,
      },
      include: [
        {
          model: Sales,
          attributes: ['id'],
          as: 'sales',
          required: false,
          include: [
            {
              model: Due,
              attributes: ['id', 'expected_amt'],
              as: 'dues',
              required: false,
              where: {
                month_due_date: {
                  [Op.and]: [
                    sequelize.where(sequelize.fn('MONTH', sequelize.col('month_due_date')), currentMonth),
                    sequelize.where(sequelize.fn('YEAR', sequelize.col('month_due_date')), currentYear),
                  ],
                },
              },
              limit: 1,
            },
          ],
        },
      ],
    });

    // * get list payment
    console.log('==== all_current_not_visits');
    const all_current_not_visits = await Payment.findAll({
      raw: true,
      attributes: ['rec_amt', 'acct_number', 'payment_note'],
      where: {
        [Op.and]: [
          sequelize.where(sequelize.col('createdBy'), 'API'),
          sequelize.where(
            sequelize.fn(
              'YEAR',
              sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
            ),
            currentYear,
          ),
          sequelize.where(
            sequelize.fn(
              'MONTH',
              sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
            ),
            currentMonth,
          ),
          !validator.isLastDayOfMonth(currentDay)
            ? sequelize.where(
                sequelize.fn(
                  'DAY',
                  sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
                ),
                {
                  [Op.lte]: currentDay,
                },
              )
            : '',
        ],
        payment_note: {
          [Op.in]: ['Skip Visit'],
        },
        client_code: user.client_code,
      },
    });

    console.log('==== all_current_with_not_visit_payments');
    const all_current_with_not_visit_payments = await Payment.findAll({
      raw: true,
      attributes: ['rec_amt', 'acct_number', 'payment_note', 'visit_type'],
      where: {
        [Op.and]: [
          sequelize.where(sequelize.col('createdBy'), 'API'),
          sequelize.where(
            sequelize.fn(
              'YEAR',
              sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
            ),
            currentYear,
          ),
          sequelize.where(
            sequelize.fn(
              'MONTH',
              sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
            ),
            currentMonth,
          ),
          !validator.isLastDayOfMonth(currentDay)
            ? sequelize.where(
                sequelize.fn(
                  'DAY',
                  sequelize.fn('CONVERT_TZ', sequelize.col('rec_date'), 'UTC', setting.DEFAULT_COUNTRY_TIME_ZONE),
                ),
                {
                  [Op.lte]: currentDay,
                },
              )
            : '',
        ],
        client_code: user.client_code,
      },
    });

    // * calculate recovery rate
    let completed_visits = 0;
    let expected_visits = 0;
    let collected_amount = 0;
    let expected_amount = 0;

    for (const p of all_current_with_not_visit_payments) {
      var actual_collection = parseFloat(p['rec_amt']);
      if (!empty(actual_collection) && p.payment_note != 'Skip Visit') {
        completed_visits++;
        collected_amount = collected_amount + actual_collection;
      }
    }

    for (const recommend of recommends) {
      const expectedl_collection = parseFloat(recommend.sales.dues[0].expected_amt);
      expected_visits++;
      expected_amount = expected_amount + expectedl_collection;
    }

    // * calculate Itinerary Non-compliance
    const current_itinerary_non_compliance =
      all_current_not_visits.length != 0 && all_current_with_not_visit_payments.length != 0
        ? (all_current_not_visits.length / all_current_with_not_visit_payments.length) * 100
        : 0;
    const prev_itinerary_non_compliance = !empty(prev_itinerary) ? prev_itinerary.itinerary_incompliant : 0;

    // * recalculate collection efficiency, recovery rate, skipped visits
    itinerary.collection_efficiency = ((collected_amount / expected_amount) * 100.0).toFixed(1);
    if (!empty(completed_visits) && !empty(expected_visits)) {
      itinerary.recovery_rate = ((completed_visits / expected_visits) * 100.0).toFixed(1);
    }

    itinerary.itinerary_incompliant = current_itinerary_non_compliance.toFixed(1);
    itinerary.itinerary_incompliant_status = calculator.calculatePercentStatus(
      prev_itinerary_non_compliance,
      current_itinerary_non_compliance,
    );

    itinerary.skipped_visits = all_current_not_visits.length;

    await itinerary.save();

    return;
  };

  static listItinerary = async (req, res) => {
    if (req.auth === undefined || req.auth.id === undefined) {
      return res.status(401).send({
        message: 'JWT Token invalid',
      });
    }

    // Current month, year
    const currentDateString = new Date().toLocaleString('en-US', {
      timeZone: setting.DEFAULT_COUNTRY_TIME_ZONE,
    });
    let currentDate = new Date(currentDateString);

    let currentMonth = currentDate.getMonth() + 1;
    let currentYear = currentDate.getFullYear();

    const user = await User.findOne({
      where: {
        id: req.auth.id,
      },
    });

    if (user === null) {
      return res.status(401).send({
        message: 'User does not exist',
      });
    }

    if (user.role !== 'M' && user.role !== 'C') {
      return res.status(401).send({
        message: 'Role does not have enough permission',
      });
    }

    // TODO: Grab the list agents by branch
    let client_codes = [];
    const list_agents_by_branch = await User.findAll({
      raw: true,
      attributes: ['client_code', 'agent_id'],
      where: {
        [Op.and]: [
          sequelize.where(sequelize.col('role'), 'F'),
          validator.isBranchManager(req.auth) ? sequelize.where(sequelize.col('manager_id'), user.id) : '',
        ],
      },
    });

    for (const l of list_agents_by_branch) {
      client_codes.push(l['client_code']);
    }

    return Itinerary.findAll({
      include: [
        {
          model: ItineraryAllocation,
          as: 'itinerary_allocations',
          require: false,
        },
      ],
      where: {
        month: currentMonth,
        year: currentYear,
        [Op.and]: [
          validator.isBranchManager(req.auth)
            ? sequelize.where(sequelize.col('client_code'), {
                [Op.in]: client_codes,
              })
            : '',
        ],
      },
      order: sequelize.literal('collection_efficiency DESC'),
    })
      .then((itineraries) => res.status(200).send({ data: itineraries }))
      .catch((error) => res.status(400).send(error));
  };

  static viewItinerary = async (req, res) => {
    if (req.auth === undefined || req.auth.id === undefined) {
      return res.status(401).send({
        message: 'JWT Token invalid',
      });
    }

    const user = await User.findOne({
      where: {
        id: req.auth.id,
      },
    });

    if (user === null) {
      return res.status(401).send({
        message: 'User does not exist',
      });
    }

    if (user.role !== 'M' && user.role !== 'C') {
      return res.status(401).send({
        message: 'Role does not have enough permission',
      });
    }

    let itineraryID = req.params.id;

    if (empty(itineraryID)) {
      return res.status(400).send({
        message: 'Bad Request',
      });
    }

    return Itinerary.findAll({
      include: [
        {
          model: ItineraryAllocation,
          as: 'itinerary_allocations',
          require: false,
        },
      ],
      where: {
        id: itineraryID,
      },
    })
      .then((itinerary) => res.status(200).send({ data: itinerary }))
      .catch((error) => res.status(400).send(error));
  };

  static listItineraryAllocation = async (req, res) => {
    if (req.auth === undefined || req.auth.id === undefined) {
      return res.status(401).send({
        message: 'JWT Token invalid',
      });
    }

    // Current month, year
    const currentDateString = new Date().toLocaleString('en-US', {
      timeZone: setting.DEFAULT_COUNTRY_TIME_ZONE,
    });
    let currentDate = new Date(currentDateString);

    let currentMonth = currentDate.getMonth() + 1;
    let currentYear = currentDate.getFullYear();

    const user = await User.findOne({
      where: {
        id: req.auth.id,
      },
    });

    if (user === null) {
      return res.status(401).send({
        message: 'User does not exist',
      });
    }

    if (user.role !== 'M' && user.role !== 'C') {
      return res.status(401).send({
        message: 'Role does not have enough permission',
      });
    }

    // TODO: Grab the list agents by branch
    let client_codes = [];
    const list_agents_by_branch = await User.findAll({
      raw: true,
      attributes: ['client_code', 'agent_id'],
      where: {
        [Op.and]: [
          sequelize.where(sequelize.col('role'), 'F'),
          validator.isBranchManager(req.auth) ? sequelize.where(sequelize.col('manager_id'), user.id) : '',
        ],
      },
    });

    for (const l of list_agents_by_branch) {
      client_codes.push(l['client_code']);
    }

    return ItineraryAllocation.findAll({
      include: [
        {
          model: Itinerary,
          as: 'itinerary',
          attributes: ['id', 'is_ai'],
        },
      ],
      where: {
        month: currentMonth,
        year: currentYear,
        [Op.and]: [
          validator.isBranchManager(req.auth)
            ? sequelize.where(sequelize.col('ItineraryAllocation.client_code'), {
                [Op.in]: client_codes,
              })
            : '',
        ],
      },
      order: sequelize.literal('city ASC'),
    })
      .then((itineraryAllocations) => res.status(200).send({ data: itineraryAllocations }))
      .catch((error) => res.status(400).send(error));
  };

  static viewItineraryAllocation = async (req, res) => {
    if (req.auth === undefined || req.auth.id === undefined) {
      return res.status(401).send({
        message: 'JWT Token invalid',
      });
    }

    const user = await User.findOne({
      where: {
        id: req.auth.id,
      },
    });

    if (user === null) {
      return res.status(401).send({
        message: 'User does not exist',
      });
    }

    if (user.role !== 'M' && user.role !== 'C') {
      return res.status(401).send({
        message: 'Role does not have enough permission',
      });
    }

    let itineraryID = req.params.id;

    if (empty(itineraryID)) {
      return res.status(400).send({
        message: 'Bad Request',
      });
    }

    return ItineraryAllocation.findAll({
      include: [
        {
          model: Itinerary,
          as: 'itinerary',
          attributes: ['id', 'is_ai'],
        },
      ],
      where: {
        itinerary_id: itineraryID,
      },
    })
      .then((itineraryAllocation) => res.status(200).send({ data: itineraryAllocation }))
      .catch((error) => res.status(400).send(error));
  };

  static generateAccessToken = (user) => {
    let token = jwt.sign(
      {
        id: user.id,
        role: user.role,
        phone_number: user.phone_number,
        country_code: user.country_code,
        email: user.email,
      },
      constants.JWT_SECRET,
      {
        expiresIn: 900, // in second
      },
    );
    return token;
  };

  static isIncreasedTotal = (payment) => {
    if (
      setting.collectionStatus(payment.payment_note).payment_code === '04' ||
      setting.collectionStatus(payment.payment_note).payment_code === '05' ||
      setting.collectionStatus(payment.payment_note).payment_code === '06' ||
      (setting.collectionStatus(payment.payment_note).payment_code === '01' && payment.is_ptp === false) ||
      (setting.collectionStatus(payment.payment_note).payment_code === '01' &&
        payment.is_ptp === true &&
        payment.ptp_location === 'Field' &&
        new Date(payment.ptp_date).getMonth() === getCurrentDate().getMonth()) ||
      (payment.is_ptp === true && new Date(payment.ptp_date).getMonth() === getCurrentDate().getMonth())
    )
      return true;

    // if (setting.collectionStatus(payment.payment_note).payment_code === '01' && payment.is_ptp === false) return true;

    // if (
    //   setting.collectionStatus(payment.payment_note).payment_code === '01' &&
    //   payment.is_ptp === true &&
    //   payment.ptp_location === 'Field' &&
    //   new Date(payment.ptp_date).getMonth() === getCurrentDate().getMonth()
    // )
    //   return true;

    // if (payment.is_ptp === true && new Date(payment.ptp_date).getMonth() === getCurrentDate().getMonth()) return true;

    return false;
  };

  static isIncreasedPTP = (payment) => {
    if (
      (setting.collectionStatus(payment.payment_note).payment_code === '01' &&
        payment.is_ptp === true &&
        payment.ptp_location === 'Field' &&
        new Date(payment.ptp_date).getMonth() === getCurrentDate().getMonth()) ||
      (payment.is_ptp === true && new Date(payment.ptp_date).getMonth() === getCurrentDate().getMonth())
    )
      return true;
    return false;
  };
}

module.exports = ItineraryController;