<?php

namespace Win7\Phone\Service;

use Carbon\Carbon;
use DateTime;
use DateTimeZone;
use Illuminate\Database\Capsule\Manager;
use Illuminate\Database\Connection as EloquentConnection;
use Illuminate\Database\Eloquent\Collection;
use ReflectionException;
use stdClass;
use Throwable;
use Win7\Application\Common\StringUtils;
use Win7\Application\Repository\Repository;
use Win7\Application\Repository\RepositoryManager;
use Win7\Phone\Connection;
use Win7\Phone\Constants;
use Win7\Phone\Entity\Call;
use Win7\Phone\Entity\Segment;
use Win7\Phone\Exception\PhoneException;
use Win7\Phone\Repository\CallRepository;
use Win7\Phone\Repository\ParameterRepository;

/**
 * Serviço responsável por atualizar o banco de dados local.
 *
 * @namespace Win7\Phone\Service
 * @author Thiago Daher
 */
class DatabaseUpdater
{

    /**
     * @var EloquentConnection
     */
    private EloquentConnection $localConnection;

    /**
     * @var Connection
     */
    private Connection $connection;

    /**
     * @var int
     */
    private int $talkingDuration;

    /**
     * @var int
     */
    private int $ringingDuration;

    /**
     * @var Call|null
     */
    private ?Call $currentCall;

    /**
     * @var Segment[]
     */
    private array $segments;

    /**
     * @var string
     */
    private string $currentId;

    /**
     * @var float
     */
    private float $cost;

    /**
     * @var Segment|null
     */
    private ?Segment $currentSegment;

    /**
     * @var DateTimeZone
     */
    private DateTimeZone $timezone;

    /**
     * @var CallRepository|Repository
     */
    private Repository $repository;

    /**
     * @var Repository|ParameterRepository
     */
    private Repository $paramaterRepository;

    /**
     * Construtor
     *
     * @throws ReflectionException
     */
    public function __construct(RepositoryManager $repositoryManager)
    {
        $this->timezone = new DateTimeZone('America/Sao_Paulo');
        $this->repository = $repositoryManager->get(CallRepository::class);
        $this->paramaterRepository = $repositoryManager->get(ParameterRepository::class);
    }

    /**
     * @return void
     * @throws \DateMalformedStringException
     * @noinspection PhpFullyQualifiedNameUsageInspection
     */
    public function updateAllCalls()
    {
        $dataAtual = new DateTime();
        $startingDate = new DateTime('2016-06-06 00:00:00');
        $finalDate = clone $startingDate;
        $finalDate->modify('+1 week');

        while ($startingDate <= $dataAtual) {
            $calls = $this->repository->findCallsByPeriod(
                $startingDate->format('Y-m-d 00:00:00'),
                $finalDate->format('Y-m-d 00:00:00')
            );

            $this->updateCalls($calls);
            $startingDate->modify('+1 week');
            $finalDate->modify('+1 week');
            $this->clearData();
            gc_collect_cycles();
        }
    }

    /**
     * @param Segment[]|Collection|mixed $segments
     * @return object
     */
    private function findDadosCliente($segments, string $data): ?object
    {
        $query = $this->getLocalConnection()->query();
        $query->select('TBL_VONOIDS.NU_IDCLIENTE');
        $query->addSelect('TBL_VONOIDS.TXT_NOME');
        $query->addSelect('TC.NU_PRECO_LIGACAO');
        $query->from('TBL_VONOIDS');
        $query->join('TBL_CLIENTE as TC', 'TBL_VONOIDS.NU_IDCLIENTE', '=', 'TC.ID_CLIENTE');

        foreach ($segments as $segment) {
            if (!empty($segment->source_filter) && strlen($segment->source_filter) >= 8) {
                $query->orWhere('TBL_VONOIDS.NU_VONOID', 'like', '%' . strrev($segment->source_filter) . '%');
            }

            if (!empty($segment->destination_filter) && strlen($segment->destination_filter) >= 8) {
                $query->orWhere('TBL_VONOIDS.NU_VONOID', 'like', '%' . strrev($segment->destination_filter) . '%');
            }

            if (!empty($segment->source_ramal)) {
                $query->orWhere('TBL_VONOIDS.NU_VONOID', '=', $segment->source_ramal);
            }

            if (!empty($segment->destination_ramal)) {
                $query->orWhere('TBL_VONOIDS.NU_VONOID', '=', $segment->destination_ramal);
            }
        }

        $query->where('DT_VONOINICIO', '<=', $data);
        $query->orderBy('NU_CLIENTE_ATIVO', 'DESC');
        $query->orderBy('DT_VONOINICIO', 'DESC');
        $query->orderBy('ID_CLIENTE', 'DESC');
        $query->limit(1);
        $result = $query->get();

        if ($result->isEmpty()) {
            $object = new stdClass();
            $object->NU_IDCLIENTE = 1;
            $object->TXT_NOME = 'Sem origem';
            $object->NU_PRECO_LIGACAO = '0.00,0.00';

            return $object;
        }

        return $result->first();
    }

    /**
     * @param Call[]|mixed $calls
     * @return void
     */
    private function updateCalls($calls)
    {
        foreach ($calls as $call) {
            foreach ($call->segments as $segment) {
                $save = false;

                if (strlen($segment->source_filter) <= 5 && ctype_digit($segment->source_filter) && empty(trim($segment->source_ramal))) {
                    $segment->source_ramal = strrev($segment->source_filter);
                    $save = true;
                }

                if (strlen($segment->destination_filter) <= 5 && ctype_digit($segment->destination_filter) && empty(trim($segment->destination_ramal))) {
                    $segment->destination_ramal = strrev($segment->destination_filter);
                    $save = true;
                }

                if ($save) {
                    $segment->save();
                }
            }

            if ($call->cliente_id === null && ($cliente = $this->findDadosCliente($call->segments, $call->start_time->format('Y-m-d')))) {
                $this->atualizarCliente($call, $cliente);
            }

            if (empty($call->real_talking_duration)) {
                $call->real_talking_duration = StringUtils::convertDurationSeconds($call->total_talking_duration);
            }

            if (empty($call->real_total_duration)) {
                $call->real_total_duration = StringUtils::convertDurationSeconds($call->total_talking_duration + $call->total_ringing_duration);
            }

            if (empty($call->real_ringing_duration)) {
                $call->real_ringing_duration = StringUtils::convertDurationSeconds($call->total_ringing_duration);
            }

            $call->save();
        }
    }

    /**
     * @param Call $call
     * @param object $cliente
     * @return void
     */
    private function atualizarCliente(Call $call, object $cliente)
    {
        $call->cliente_id = $cliente->NU_IDCLIENTE;
        $call->category = trim($cliente->TXT_NOME);
        $call->source = StringUtils::filter3cxNumber($call->getSourceNumberFromData(), $call->source_display_name);
        $call->destination = StringUtils::filter3cxNumber($call->getDestinationNumberFromData(), $call->destination_caller_id);
        $valores = $cliente->NU_PRECO_LIGACAO;
        $valorLigacoes = explode(",", $valores);

        for ($i = 0; $i < count($valorLigacoes); $i++) {
            $valorLigacao = $valorLigacoes[$i];
            $valorLigacoes[$i] = (float)$valorLigacao;
        }

        $valorCelular = $valorLigacoes[0] ?? 0.0;
        $valorFixo = $valorLigacoes[1] ?? 0.0;

        if ($call->charge_type === Call::NO_CHARGE) {
            $call->real_cost = '0.00';
        } elseif ($call->charge_type === Call::CHARGE_LANDLINE) {
            $call->real_cost = number_format($call->charge_minutes * $valorCelular, 2, '.', '');
        } else {
            $call->real_cost = number_format($call->charge_minutes * $valorFixo, 2, '.', '');
        }
    }

    /**
     * Insere novas ligações com base na última data de atualização na tabela call_parameter.
     * Data base de início do 3cx: 2016-06-06 00:00:00
     *
     * @param string|null $startDate
     * @return void
     * @throws Throwable
     * @throws PhoneException
     */
    public function insertNewCalls(?string $startDate = null)
    {
        if ($startDate === null) {
            $parameter = $this->paramaterRepository->findById(1);
            $startDate = $parameter->last_update;
            $startDate->modify('-1 hour');
        } else {
            $startDate = new DateTime($startDate);
        }

        $dataAtual = new DateTime();
        $finalDate = clone $startDate;
        $finalDate->modify('+1 week');

        while ($startDate <= $dataAtual) {
            $this->insertCDRCallsByDate(
                $startDate->format('Y-m-d 00:00:00-03'),
                $finalDate->format('Y-m-d 00:00:00-03'),
            );

            $startDate->modify('+1 week');
            $finalDate->modify('+1 week');
            $this->clearData();
            gc_collect_cycles();
        }

        if (isset($parameter)) {
            $parameter->last_update = new Carbon();
            $parameter->save();
        }
    }

    /**
     * @param array $calls
     * @return array
     */
    private function organizeCDRById(array $calls): array
    {
        $result = [];

        foreach ($calls as $call) {
            $cdrId = $call['cdr_id'];
            $result[$cdrId] = $call;
        }

        return $result;
    }

    /**
     * Funciona apenas para o banco de dados no formato novo.
     *
     * @param string $startDate
     * @param string $endDate
     * @return void
     * @throws PhoneException
     * @throws Throwable
     */
    public function insertCDRCallsByDate(string $startDate, string $endDate)
    {
        $calls = $this->getConnection()->getCDRCalls($startDate, $endDate);
        $this->clearData();

        foreach ($calls as $call) {
            $callId = $call['call_history_id'];
            $originalCallId = $callId;

            if ($this->repository->entityExists(['external_id' => $callId])) {
                continue;
            }

            $callSegments = $this->getConnection()->getCDRCall($callId);

            if (empty($callSegments)) {
                continue;
            }

            $callSegments = $this->organizeCDRById($callSegments);
            $currentSegment = $call;
            unset($callSegments[$call['cdr_id']]);
            $orderedSegments = [$currentSegment];

            while (!empty($currentSegment['continued_in_cdr_id'])) {
                $callId = $currentSegment['continued_in_cdr_id'];
                $currentSegment = $callSegments[$callId] ?? null;

                if ($currentSegment === null) {
                    break;
                }

                unset($callSegments[$callId]);
                $orderedSegments[] = $currentSegment;
            }

            // o sistema propositalmente ignora alguns caminhos da chamada que sobraram na array
            // $callSegments... pode ser necessário mudar isso no futuro
            //            foreach ($callSegments as $callSegment) {
            //                $orderedSegments[] = $callSegment;
            //            }

            $rowNumber = 0;

            foreach ($orderedSegments as $data) {
                $segment = new Segment();
                $segment->indent = 1;

                $started = $this->convertDateCdr($data['cdr_started_at']);
                $ended = $this->convertDateCdr($data['cdr_ended_at']);
                $answered = $this->convertDateCdr($data['cdr_answered_at']);
                $diferencaTotal = $ended->getTimestamp() - $started->getTimestamp();
                $diferencaToque = $answered ? ($answered->getTimestamp() - $started->getTimestamp()) : ($diferencaTotal);
                $tempoFala = $diferencaTotal - $diferencaToque;
                $segment->start_time = $started;
                $segment->source_type = $data['source_dn_type'];
                $segment->source_display_name = $data['source_participant_name'];
                $segment->source_number = $data['source_dn_number'];
                $segment->source_caller_id = $data['source_participant_phone_number'];
                $segment->destination_type = $data['destination_dn_type'];
                $segment->destination_number = $data['destination_dn_number'];
                $segment->destination_caller_id = $data['destination_participant_phone_number'];
                $segment->destination_display_name = $data['destination_dn_name'];
                $segment->action_type = 1;
                $segment->action_dn_type = 1;
                $segment->action_dn_dn = '';
                $segment->action_dn_caller_id = '';
                $segment->action_dn_display_name = '';

                $segment->ringing_duration = $diferencaToque;
                $segment->talking_duration = $tempoFala;
                $segment->cost = '0.00';
                $segment->answered = (bool)$answered;
                $segment->recording_url = $data['recording_url'] ?? '';
                $segment->subrow_desc_number = $rowNumber;
                $segment->source_ramal = strlen($segment->source_number) <= 5 ? $segment->source_number : '';
                $segment->destination_ramal = strlen($segment->destination_number) <= 5 ? $segment->destination_number : '';

                $segment->source_filter = $segment->getSourceNumberFromData();
                $segment->destination_filter = $segment->getDestinationNumberFromData();

                if (strlen($segment->source_number) <= 5 && ctype_digit($segment->source_number)) {
                    $segment->source_ramal = $segment->source_number;
                }

                if (strlen($segment->destination_number) <= 5 && ctype_digit($segment->destination_number)) {
                    $segment->destination_ramal = $segment->destination_number;
                }

                $segment->creation_method = $data['creation_method'];
                $segment->creation_forward_reason = $data['creation_forward_reason'];
                $segment->end_time = $ended;
                $segment->termination_reason = $data['termination_reason'];
                $segment->termination_reason_details = $data['termination_reason_details'];
                $segment->destination_dn_type = $data['destination_dn_type'];
                $this->talkingDuration += $segment->talking_duration;
                $this->ringingDuration += $segment->ringing_duration;
                $rowNumber++;
                $this->segments[] = $segment;
                $this->currentSegment = $segment;

                if (!isset($this->currentCall)) {
                    $call = new Call();
                    $call->external_id = $originalCallId;
                    $call->source_caller_id = $segment->source_caller_id;
                    $call->source_display_name = $segment->source_display_name;
                    $call->source_number = $segment->source_number;
                    $call->source_type = $segment->source_type;
                    $call->start_time = $segment->start_time;
                    $this->currentCall = $call;
                }
            }

            $this->saveCurrentCall();
        }
    }

    /**
     * Obtém a lista de ligações do 3cx na data especificada, e então insere os dados
     * no banco de dados local.
     *
     * Funciona apenas para o banco de dados no formato antigo.
     *
     * @param string $startDate
     * @param string $endDate
     * @return void
     * @throws Throwable
     * @throws PhoneException
     */
    public function insertNewCallsByDate(string $startDate, string $endDate)
    {
        $calls = $this->getConnection()->getCalls($startDate, $endDate);
        $this->clearData();

        foreach ($calls as $callData) {
            if (isset($this->currentCall) && $callData['call_id'] !== $this->currentId) {
                $this->saveCurrentCall();
            }

            if (!isset($this->currentCall) && $this->repository->entityExists(['external_id' => $callData['call_id']])) {
                continue;
            }

            $this->createNewSegment($callData);

            if (!isset($this->currentCall)) {
                $this->createNewCall($callData);
            }

            $this->currentId = $callData['call_id'];
            $this->talkingDuration += $this->currentSegment->talking_duration;
            $this->ringingDuration += $this->currentSegment->ringing_duration;
            $this->cost += (float)$this->currentSegment->cost;
        }

        if (isset($this->currentCall)) {
            $this->saveCurrentCall();
        }
    }

    /**
     * @return void
     */
    private function clearData()
    {
        $this->currentId = '';
        $this->segments = [];
        $this->currentCall = null;
        $this->currentSegment = null;
        $this->talkingDuration = 0;
        $this->ringingDuration = 0;
        $this->cost = 0.0;
    }

    /**
     * @param array $callData
     * @return void
     */
    private function createNewCall(array $callData)
    {
        $segment = $this->currentSegment;
        $call = new Call();
        $call->external_id = $callData['call_id'];
        $call->source_caller_id = $segment->source_caller_id;
        $call->source_display_name = $segment->source_display_name;
        $call->source_number = $segment->source_number;
        $call->source_type = $segment->source_type;
        $call->start_time = $segment->start_time;
        $this->currentCall = $call;
    }

    /**
     * @return void
     * @throws Throwable
     */
    private function saveCurrentCall()
    {
        try {
            $call = $this->currentCall;
            $this->getLocalConnection()->beginTransaction();
            $this->currentCall->cost = number_format($this->cost, 2, '.', '');
            $this->currentCall->last_ringing_duration = $this->currentSegment->ringing_duration;
            $this->currentCall->last_talking_duration = $this->currentSegment->talking_duration;
            $this->currentCall->total_ringing_duration = $this->ringingDuration;
            $this->currentCall->total_talking_duration = $this->talkingDuration;
            $this->currentCall->answered = $this->currentSegment->answered;
            $this->currentCall->destination_caller_id = $this->currentSegment->destination_caller_id;
            $this->currentCall->destination_display_name = $this->currentSegment->destination_display_name;
            $this->currentCall->destination_number = $this->currentSegment->destination_number;
            $this->currentCall->destination_type = $this->currentSegment->destination_type;
            $this->currentCall->charge_type = $this->getChargeType($this->currentCall->getDestinationNumberFromData());
            $this->currentCall->charge_minutes = floor($this->talkingDuration / 60);
            $this->currentCall->antiga = false;

            if ($this->talkingDuration % 60 !== 0) {
                $this->currentCall->charge_minutes += 1;
            }

            if ($cliente = $this->findDadosCliente($this->segments, $this->currentCall->start_time->format('Y-m-d'))) {
                $this->atualizarCliente($this->currentCall, $cliente);
            }

            $call->real_talking_duration = StringUtils::convertDurationSeconds($call->total_talking_duration);
            $call->real_total_duration = StringUtils::convertDurationSeconds($call->total_talking_duration + $call->total_ringing_duration);
            $call->real_ringing_duration = StringUtils::convertDurationSeconds($call->total_ringing_duration);

            $this->currentCall->save();

            foreach ($this->segments as $segment) {
                $this->currentCall->segments()->save($segment);
            }

            $this->getLocalConnection()->commit();
        } catch (Throwable $exception) {
            $this->getLocalConnection()->rollBack();
        } finally {
            $this->clearData();
        }
    }

    /**
     * @param array $data
     * @return void
     */
    private function createNewSegment(array $data)
    {
        $segment = new Segment();
        $segment->indent = $data['indent'];
        $segment->start_time = $this->convertDate($data['start_time']);
        $segment->source_type = $data['source_type'];
        $segment->source_display_name = $data['source_display_name'];
        $segment->source_number = $data['source_dn'];
        $segment->source_caller_id = $data['source_caller_id'];
        $segment->destination_type = $data['destination_type'];
        $segment->destination_number = $data['destination_dn'];
        $segment->destination_caller_id = $data['destination_caller_id'];
        $segment->destination_display_name = $data['destination_display_name'];
        $segment->action_type = $data['action_type'];
        $segment->action_dn_type = $data['action_dn_type'];
        $segment->action_dn_dn = $data['action_dn_dn'];
        $segment->action_dn_caller_id = $data['action_dn_caller_id'];
        $segment->action_dn_display_name = $data['action_dn_display_name'];
        $segment->ringing_duration = $this->convertDuration($data['ringing_duration']);
        $segment->talking_duration = $this->convertDuration($data['talking_duration']);
        $segment->cost = $this->convertCost($data['call_cost']);
        $segment->answered = $data['answered'] === 't';
        $segment->recording_url = $data['recording_url'];
        $segment->subrow_desc_number = $data['subrow_desc_number'];
        $segment->source_ramal = strlen($segment->source_number) <= 5 ? $segment->source_number : '';
        $segment->destination_ramal = strlen($segment->destination_number) <= 5 ? $segment->destination_number : '';

        $segment->source_filter = $this->getFilter(
            $data['source_type'],
            $data['source_caller_id'],
            $data['source_display_name'],
            $data['source_dn']
        );

        $segment->destination_filter = $this->getFilter(
            $data['destination_type'],
            $data['destination_caller_id'],
            $data['destination_display_name'],
            $data['destination_dn']
        );

        if (strlen($segment->source_filter) <= 5 && ctype_digit($segment->source_filter) && empty(trim($segment->source_ramal))) {
            $segment->source_ramal = strrev($segment->source_filter);
        }

        if (strlen($segment->destination_filter) <= 5 && ctype_digit($segment->destination_filter) && empty(trim($segment->destination_ramal))) {
            $segment->destination_ramal = strrev($segment->destination_filter);
        }

        $this->segments[] = $segment;
        $this->currentSegment = $segment;
    }

    /**
     * @param $type
     * @param $callerNumber
     * @param $displayName
     * @param $dn
     * @return string
     */
    private function getFilter($type, $callerNumber, $displayName, $dn): string
    {
        $type = (int)$type;
        $callerNumber = strrev(preg_replace("/[^0-9]/", '', $callerNumber));
        $dn = strrev(preg_replace("/[^0-9]/", '', $dn));
        $displayName = strrev(preg_replace("/[^0-9]/", '', $displayName));

        if (in_array($type, Constants::EXTERNAL_CALLS) && !empty($callerNumber)) {
            return $callerNumber;
        }

        if ($type === 12 && !empty($displayName)) {
            return $displayName;
        }

        if (!empty($dn)) {
            return $dn;
        }

        return $displayName;
    }

    /**
     * @param $cost
     * @return string
     */
    private function convertCost($cost): string
    {
        if (empty($cost)) {
            return '0.00';
        }

        return number_format($cost, 2, '.', '');
    }

    /**
     * @param mixed $date
     * @return DateTime|null
     */
    private function convertDateCdr($date): ?DateTime
    {
        if (empty($date)) {
            return null;
        }

        $date = DateTime::createFromFormat('Y-m-d H:i:s.uP', $date);
        $date->setTimezone($this->timezone);

        return $date;
    }

    /**
     * @param mixed $date
     * @return Carbon|null
     */
    private function convertDate($date): ?Carbon
    {
        if (empty($data)) {
            return null;
        }

        $date = new Carbon($date);
        $date->setTimezone($this->timezone);

        return $date;
    }

    /**
     * @param $duration
     * @return int
     */
    private function convertDuration($duration)
    {
        $duration = explode(":", $duration);
        $segundos = intval($duration[2] ?? 0);
        $minutos = intval($duration[1] ?? 0);
        $horas = intval($duration[0] ?? 0);

        return $segundos + ($minutos * 60) + ($horas * 60 * 60);
    }

    /**
     * @return EloquentConnection
     */
    private function getLocalConnection(): EloquentConnection
    {
        if (!isset($this->localConnection)) {
            $this->localConnection = Manager::connection('default');
        }

        return $this->localConnection;
    }

    /**
     * Copiado do código do sistema win7 original, filtra o número 3cx  e obtém o tipo de cobrança.
     *
     * @param $numero
     * @return int
     */
    public function getChargeType($numero): int
    {
        $chargeType = Call::NO_CHARGE;

        $posicaoDoisPontos = strpos($numero, ":");
        if (strpos($numero, ":") !== false) {
            $numero = substr($numero, 0, $posicaoDoisPontos);
        }

        if (substr($numero, 0, 4) === "Ext.") {
            return $chargeType;
        }

        if (is_numeric($numero)) {
            $size = strlen($numero);
            if ($size == 11 & $numero[0] == "0") {
                $primeiro_numero = $numero[3];
                $chargeType = $primeiro_numero == "9" ? Call::CHARGE_CELL_PHONE : Call::CHARGE_LANDLINE;
            } elseif ($size == 12 & $numero[0] == "0") {
                $primeiro_numero = $numero[3];
                $chargeType = $primeiro_numero == "9" ? Call::CHARGE_CELL_PHONE : Call::CHARGE_LANDLINE;
            } elseif (($size == 12 || $size == 13) & substr($numero, 0, 2) == "55") {
                $primeiro_numero = $numero[4];
                $chargeType = $primeiro_numero == "9" ? Call::CHARGE_CELL_PHONE : Call::CHARGE_LANDLINE;
            } elseif ($size == 8 || $size == 9) {
                $primeiro_numero = $numero[0];
                $chargeType = $primeiro_numero == "9" ? Call::CHARGE_CELL_PHONE : Call::CHARGE_LANDLINE;
            } elseif ($size == 10 || $size == 11) {
                $primeiro_numero = $numero[2];
                $chargeType = $primeiro_numero == "9" ? Call::CHARGE_CELL_PHONE : Call::CHARGE_LANDLINE;
            } elseif ($size >= 14) {
                if (substr($numero, 0, 4) == "0055") {
                    $primeiro_numero = $numero[6];
                    $chargeType = $primeiro_numero == "9" ? Call::CHARGE_CELL_PHONE : Call::CHARGE_LANDLINE;
                }
            }
        }

        return $chargeType;
    }

    /**
     * @return Connection
     * @throws PhoneException
     */
    private function getConnection(): Connection
    {
        if (!isset($this->connection)) {
            $this->connection = Connection::getConnection();
        }

        return $this->connection;
    }
}