<?php
require 'vendor/autoload.php';

use MongoDB\BSON\UTCDateTime;
use League\Csv\Writer;


$client = new MongoDB\Client();
$database = $client->selectDatabase('freshdesk');
$ticketsCollection = $database->selectCollection('freshdeskTickets');
$agentsCollection = $database->selectCollection('freshdeskAgents');

$dateTimeNow = new DateTimeImmutable();
$dt30DaysAgo = $dateTimeNow->sub(new DateInterval('P30D'))->setTime(0, 0, 0);
$pipeline = [
    [
        '$match' => [
            'ticketCreatedAt' => [
                '$gte' => new UTCDateTime($dt30DaysAgo),
                '$lte' => new UTCDateTime($dateTimeNow),
            ]
        ]
    ],
    [
        '$project' => [
            'assunto' => '$subject',
            'ticket' => '$ticketId',
            'created_at' => '$ticketCreatedAt',
            'ticket_criado_em' => [ '$dateToString' => [ 'date' => '$ticketCreatedAt', 'format' => '%d-%m-%Y %H:%M:%S']],
            'primeira_resposta_vence_em' => ['$dateToString' => ['date' => '$ticketFrDueBy', 'format' => '%d-%m-%Y %H:%M:%S']],
            'data_primeira_resposta' => [ '$dateToString' => [ 'date' => '$ticketFirstRespondedAt', 'format' => '%d-%m-%Y %H:%M:%S']],
            'responder_id' => '$responder_id',
            'sla_violated' => [
                '$cond' => [
                    'if' => ['$ne' => ['$ticketFirstRespondedAt', null]],
                    'then' => ['$gt' => ['$ticketFirstRespondedAt', '$ticketFrDueBy']],
                    'else' => [
                        '$and' => [
                            ['$not' => ['$ticketFirstRespondedAt']], # não tem primeira resposta
                            ['$gt' => [new UTCDateTime($dateTimeNow), '$ticketFrDueBy']] # passou da data limite?
                        ]
                    ]
                ]
            ]
        ]
    ],
    [
        '$sort' => ['created_at' => -1],
    ],
    [
        '$match' => [
            'sla_violated' => true
        ]
    ],
];

$getAgent = function ($id) use ($agentsCollection) {
    $found = $agentsCollection->findOne(['userId' => $id]);

    if (!$id || !$found) {
        return 'Não identificado';
    }

    return sprintf('%s (%s)', $found->name, $found->email);
};



$csv = Writer::createFromPath('/tmp/relatorio_violacao_sla.csv', 'w');
$header = ['TICKET ID', 'ASSUNTO', 'AGENTE', 'DATA DE CRIAÇÃO', 'DATA LIMITE P/ PRIMEIRA RESPOSTA', 'DATA PRIMEIRA RESPOSTA', 'VIOLAÇÃO SLA?'];
$records = [];
$csv->insertOne($header);
/** @var MongoDB\Driver\Cursor $result */
$cursor = $ticketsCollection->aggregate($pipeline);

foreach ($cursor as $document) {
    $agente = $getAgent($document->responder_id);

    $records[] = [
        $document->ticket,
        $document->assunto,
        $agente,
        $document->ticket_criado_em,
        $document->primeira_resposta_vence_em,
        $document->data_primeira_resposta,
        $document->sla_violated ? 'SIM' : 'NAO',
    ];
}

$csv->insertAll($records);