<?php session_start();
/*      Copyright 2023 Flávio Ribeiro

        This file is part of OCOMON.

        OCOMON is free software; you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation; either version 3 of the License, or
        (at your option) any later version.
        OCOMON is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.

        You should have received a copy of the GNU General Public License
        along with Foobar; if not, write to the Free Software
        Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

if (!isset($_SESSION['s_logado']) || $_SESSION['s_logado'] == 0) {
    $_SESSION['session_expired'] = 1;
    echo "<script>top.window.location = '../../index.php'</script>";
    exit;
}

require_once __DIR__ . "/" . "../../includes/include_basics_only.php";
require_once __DIR__ . "/" . "../../includes/classes/ConnectPDO.php";
require_once __DIR__ . "/" . "../../includes/classes/worktime/Worktime.php";
include_once __DIR__ . "/" . "../../includes/functions/getWorktimeProfile.php";

use includes\classes\ConnectPDO;

$conn = ConnectPDO::getInstance();
$exception = "";
$criteria = [];
$criteriaText = "";
$data = [];
$data['success'] = true;
$data['message'] = "";
$data['total'] = 0;
$uareas = explode(',', (string)$_SESSION['s_uareas']);
$terms = "";
$termsTotal = "";
$holidays = getHolidays($conn);


$labelsByStates = [
    1 => TRANS('STATE_OPEN_CLOSE_IN_SEARCH_RANGE_MONTH'),
    2 => TRANS('STATE_OPEN_IN_SEARCH_RANGE'),
    3 => TRANS('STATE_OPEN_IN_SEARCH_RANGE_CLOSE_ANY_TIME'),
    4 => TRANS('STATE_OPEN_ANY_TIME_CLOSE_IN_SEARCH_RANGE_MONTH'),
    5 => TRANS('STATE_JUST_OPEN_IN_SEARCH_RANGE')
];

/* Inicialização dos Critérios da consulta */
$criteria['client'] = TRANS('CLIENT') . ':&nbsp;' . TRANS('ALL');
$criteria['treater'] = TRANS('WORKER') . ':&nbsp;' . TRANS('ALL');
$criteria['area'] = TRANS('AREA') . ':&nbsp;' . TRANS('ALL');
$criteria['state'] = TRANS('CONTEXT') . ':&nbsp;' . TRANS('STATE_OPEN_CLOSE_IN_SEARCH_RANGE_MONTH');

$post = $_POST;

$timeStart = ' 00:00:00';
$timeEnd = ' 23:59:59';

$data['include_current_month'] = (isset($post['include_current_month']) ? ($post['include_current_month'] == "yes" ? true : false) : false);
$data['number_of_months'] = (isset($post['number_of_months']) && $post['number_of_months'] > 0 && $post['number_of_months'] <= 12 ? (int)$post['number_of_months'] : 1);

$data['client'] = (isset($post['client']) ? (int)$post['client'] : "");
$data['area'] = (isset($post['area']) ? (int)$post['area'] : "");

$data['treater'] = (isset($post['treater']) ? (int)$post['treater'] : "");

$data['state'] = (isset($post['state']) && !empty($post['state']) ? (int)$post['state'] : 1);



if (!empty($data['client'])) {
    $terms .= " AND o.client = {$data['client']} ";
    $termsTotal .= $terms;
    $criteria['client'] = TRANS('CLIENT') . ':&nbsp;<b>' . getClients($conn, $data['client'])['nickname'] . '</b>';
}



$limited_areas = false;
$string_area_names = "";
if (empty($data['area']) && isAreasIsolated($conn) && $_SESSION['s_nivel'] != 1) {
    /* Visibilidade isolada entre áreas para usuários não admin */
    $limited_areas = true;
    $u_areas = $_SESSION['s_uareas'];

    $terms .= " AND o.sistema IN ({$u_areas}) ";
    $termsTotal .= $terms;

    $array_areas_names = getUserAreasNames($conn, $u_areas);
    $string_area_names = implode(", ", array_filter($array_areas_names));
} elseif (!empty($data['area'])) {

    $string_area_names = getAreaInfo($conn, $data['area'])['area_name'];

    $terms .= " AND o.sistema = {$data['area']} ";
    $termsTotal .= $terms;
    $criteria['area'] = TRANS('AREA') . ':&nbsp;<b>' . $string_area_names . '</b>';
}




if (!empty($data['treater'])) {
    $terms .= " AND u.user_id = {$data['treater']} ";
    $termsTotal .= $terms;
    $criteria['treater'] = TRANS('WORKER') . ':&nbsp;<b>' . getUserInfo($conn, $data['treater'])['nome'] . '</b>';
}








$client_filter = [];


// $termsTotal = "";
$chart_title_sufix = '_chart_title';
$chart_1_prefix = 'chart_01';
$chart_2_prefix = 'chart_02';
$chart_3_prefix = 'chart_03';
$chart_4_prefix = 'chart_04';
$chart_5_prefix = 'chart_05';


$rangeText = TRANS('LASTS') . ':&nbsp;<b>' . $data['number_of_months'] . '</b>&nbsp;' . TRANS('MONTHS');


/* Período */
// $criteria['range'] = TRANS('RANGE_FROM_TO') . ':&nbsp;<b>' . $post['d_ini'] . ' - ' . $post['d_fim'] . '</b>';
$criteria['range'] = TRANS('RANGE_FROM_TO') . ':&nbsp;' . $rangeText;

/* Contexto */
$criteria['state'] = TRANS('CONTEXT') . ':&nbsp;<b>' . $labelsByStates[$data['state']] . '</b>';



$_SESSION['s_rep_filters']['client'] = $data['client'];
$_SESSION['s_rep_filters']['area'] = $data['area'];
$_SESSION['s_rep_filters']['state'] = $data['state'];



/* Montagem do texto dos critérios */
$criteriaText = implode(' | ', $criteria);
if ($limited_areas) {
    $criteriaText .= " <br />(". TRANS('RESULT_LIMITED_BY_PERMISSIONS').")";
}
$data['criteria'] = $criteriaText;
/* Final da montagem dos critérios */



$data['table_1'] = [];

$data['table_2'] = [];
$data['months'] = [];
$data['operators'] = [];
$data['operators_totals'] = [];

$data['tickets'] = [];
$data['chart_1'] = [];


$monthsDates = getPreviousMonthsDates($data['number_of_months'], $data['include_current_month']);
$data['months_length'] = count($monthsDates);


foreach ($monthsDates as $monthDate) {
    $monthDate['start'] .= $timeStart;
    $monthDate['end'] .= $timeEnd;

    $clausulesByStates = [
        1 => " AND o.data_abertura >= '{$monthDate['start']}' AND o.data_abertura <= '{$monthDate['end']}' AND 
        o.data_fechamento IS NOT NULL AND o.data_fechamento >= '{$monthDate['start']}' AND o.data_fechamento <= '{$monthDate['end']}' ",
        2 => " AND o.data_abertura >= '{$monthDate['start']}' AND o.data_abertura <= '{$monthDate['end']}' AND 
        (o.data_fechamento >= '{$monthDate['end']}' OR o.data_fechamento IS NULL) ",
        3 => " AND o.data_abertura >= '{$monthDate['start']}' AND o.data_abertura <= '{$monthDate['end']}' AND 
        o.data_fechamento IS NOT NULL ",
        4 => " AND o.data_fechamento >= '{$monthDate['start']}' AND o.data_fechamento <= '{$monthDate['end']}' ",
        5 => " AND o.data_abertura >= '{$monthDate['start']}' AND o.data_abertura <= '{$monthDate['end']}' "
    ];

    $sql = "SELECT 
                o.numero,
                o.data_abertura,
                o.data_atendimento,
                o.data_fechamento,
                o.operador,
                u.nome,
                COALESCE (u.mvp_target, 0) AS mvp_target,
                p.problema,
                COALESCE (p.weight, '-') AS weight,
                COALESCE (tl.name, '-') AS level_name,
                COALESCE(tp.unit_price * p.weight, 0) AS ticket_price
            FROM
                ocorrencias o
                LEFT JOIN
                    problemas p ON o.problema = p.prob_id
                LEFT JOIN
                    tech_levels tl ON tl.id = p.tech_level
                LEFT JOIN
                    tech_prices tp ON tp.id = (
                    SELECT 
                        COALESCE(
                            (
                                SELECT MAX(id) 
                                FROM tech_prices 
                                WHERE tech_level_id = p.tech_level AND
                                    vigence_since <= o.data_abertura
                            ),
                            (
                                SELECT MIN(id) 
                                FROM tech_prices 
                                WHERE tech_level_id = p.tech_level
                            )
                        )
                ),
                usuarios u
            WHERE
                o.operador = u.user_id
                {$terms}
                {$clausulesByStates[$data['state']]}
            ";

    try {
        $res = $conn->prepare($sql);
        $res->execute();
        if ($res->rowCount()) {
            $data['table_1'][$monthDate['month_name']] = $res->fetchAll();
        } else {
            $data['table_1'][$monthDate['month_name']] = [];
        }
    } catch (PDOException $e) {
        $data['success'] = false;
        $data['message'] = message('warning', 'Ooops!', $e->getMessage(),'');
        echo json_encode($data);
        return false;
    }

}


    if (count($data['table_1']) == 0) {
        $data['success'] = false;
        $data['message'] = message('warning', 'Ooops!', TRANS('NO_RECORDS_FOUND') . $exception,'');
        echo json_encode($data);
        return false;
    }


    foreach ($data['table_1'] as $month => $row) {
        
        foreach ($row as $key => $value) {
            
            if (is_array($value) && array_key_exists('numero', $value)) {
                /* Utilizar a chave 'numero' como chave */
                $data['tickets'][$value['numero']] = $value;
                $data['tickets'][$value['numero']]['data_abertura_br'] = dateScreen($value['data_abertura']);
                $data['tickets'][$value['numero']]['data_atendimento_br'] = dateScreen($value['data_atendimento']);
                $data['tickets'][$value['numero']]['data_fechamento_br'] = dateScreen($value['data_fechamento']);
                $data['tickets'][$value['numero']]['ticket_price_brl'] = priceScreen($value['ticket_price']);
    
                if (array_key_exists($value['operador'], $data['operators'])) {
                    
                    /* Atualiza o operador */
                    $data['operators'][$value['operador']]['ust'] += $value['ticket_price'];
                    $data['operators'][$value['operador']]['ust_brl'] = priceScreen($data['operators'][$value['operador']]['ust']);
    
                } else {
                    /* Inicializa o operador */
                    $data['operators'][$value['operador']] = [
                            'ust' => $value['ticket_price'],
                            'ust_brl' => priceScreen($value['ticket_price']),
                            'name' => $value['nome'],
                            'area' => getMainAreaNameFromUserId($conn, $value['operador']),
                            'mvp_target' => $value['mvp_target']
                    ];
                }

            }
        }
        
        $data['months'][$month] = $data['operators'];
        $data['operators'] = [];

    }


    foreach ($data['months'] as $month => &$operatorsData) {

        foreach ($operatorsData as &$item) {
            
            $ust = (float)$item['ust'];
            $mvp_target = (float)$item['mvp_target'];

            // Evitar divisão por zero
            if ($mvp_target == 0) {
                $item['percentual_done'] = 0;
                $item['percentual_done_brl'] = 0;
            } else {
                $item['percentual_done'] = ($ust / $mvp_target) * 100 . '%';
                $item['percentual_done_brl'] = number_format(($ust / $mvp_target) * 100, 2, ',', '.') . '%';
            }
        }
        unset($item);

    }

    /* Preencher os operadores faltantes para todos os meses */
    // Passo 1: Identificar todos os operadores únicos
    $uniqueOperators = [];
    foreach ($data['months'] as $month => $monthData) {
        foreach ($monthData as $operatorId => $operatorData) {
            if (!isset($uniqueOperators[$operatorId])) {
                $uniqueOperators[$operatorId] = [
                    'id' => $operatorId,
                    'name' => $operatorData['name'],
                    'area' => $operatorData['area'],
                    'mvp_target' => $operatorData['mvp_target']
                ];
            }
        }
    }

    // Passo 2: Iterar sobre cada mês e verificar se o operador está presente
    foreach ($data['months'] as $month => &$monthData) {
        foreach ($uniqueOperators as $operatorId => $operatorInfo) {
            if (!isset($monthData[$operatorId])) {
                // Passo 3: Adicionar o operador com valores zerados
                $monthData[$operatorId] = [
                    'ust' => 0,
                    'ust_brl' => '0,00',
                    'name' => $operatorInfo['name'],
                    'area' => $operatorInfo['area'],
                    'mvp_target' => $operatorInfo['mvp_target'],
                    'percentual_done' => '0.00%',
                    'percentual_done_brl' => '0,00%'
                ];
            }
        }
    }


    $data['unique_operators'] = $uniqueOperators;
    $data['operators_length'] = count($data['unique_operators']);


    /* Totais em cada mês */
    foreach ($data['months'] as $month => $monthData2) {


        $data['months'][$month]['total_ust'] = array_sum(array_column($monthData2, 'ust'));
        $data['months'][$month]['total_ust_brl'] = priceScreen($data['months'][$month]['total_ust']);
    }


    // Arrays para os dados do gráfico
    $labels = [];
    // $operatorsData = [];
    $ustData = [];
    $mvpTargetData = [];


    $tmp_data['months'] = $data['months'];
    foreach ($tmp_data['months'] as $monthTmp => $monthTmpData) {
        $data['chart_1']['labels'][] = $monthTmp;
        $data['chart_1']['labels_brl'][] = TRANS(strtoupper($monthTmp));

        foreach ($monthTmpData as $operatorId => $operatorData) {

            // var_dump($operatorData);
            if (is_array($operatorData)) {
                $data['chart_1'][$monthTmp]['operators_ids'][] = $operatorId;
                $data['chart_1'][$monthTmp]['operators'][$operatorId] = $operatorData['name'];
                $data['chart_1'][$monthTmp]['usts'][$operatorId] = number_format($operatorData['ust'], 2);
                $data['chart_1'][$monthTmp]['mvp_targets'][$operatorId] = $operatorData['mvp_target'];
                $data['chart_1'][$monthTmp]['percentual_done'][$operatorId] = $operatorData['percentual_done'];
                $data['chart_1'][$monthTmp]['percentual_done_brl'][$operatorId] = $operatorData['percentual_done_brl']; 
                $data['chart_1'][$monthTmp]['total_ust'] = $monthTmpData['total_ust'];
                $data['chart_1'][$monthTmp]['total_ust_brl'] = $monthTmpData['total_ust_brl'];


                
                /* Totais gerais por operador */
                if (!isset($data['operators_totals'][$operatorId])) {
                    $data['operators_totals'][$operatorId] = [
                        'name' => $operatorData['name'],
                        'area' => $operatorData['area'],
                        'mvp_target' => $operatorData['mvp_target'],
                        'ust' => $operatorData['ust'],
                        'ust_brl' => $operatorData['ust_brl'],
                        'percentual_done' => $operatorData['percentual_done'],
                        'percentual_done_brl' => $operatorData['percentual_done_brl']
                    ];
                } else {
                    $data['operators_totals'][$operatorId]['ust'] += $operatorData['ust'];
                    $data['operators_totals'][$operatorId]['ust_brl'] = priceScreen($data['operators_totals'][$operatorId]['ust']);
                    
                    if ($data['operators_totals'][$operatorId]['mvp_target'] == 0) {
                        $data['operators_totals'][$operatorId]['percentual_done'] = 0;
                    } else {
                        $data['operators_totals'][$operatorId]['percentual_done'] = $data['operators_totals'][$operatorId]['ust'] / $operatorData['mvp_target'] * 100 / $data['number_of_months']; // média dos percentuais com base no número de meses do período
                    }
                    
                    $data['operators_totals'][$operatorId]['percentual_done_brl'] = number_format($data['operators_totals'][$operatorId]['percentual_done'], 2, ',', '.') . '%';

                }
            }

        }
    }


    $operators_usts = [];

    $data['chart_1']['operators'] = $uniqueOperators;

    foreach ($data['chart_1']['labels'] as $index => $month) {
        $month_data = $data['chart_1'][$month];
    
        // Percorre os operadores de cada mês
        foreach ($month_data['operators_ids'] as $operator_index => $operator_id) {
            // Verifica se o operador já tem um array de USTs inicializado
            if (!isset($operators_usts[$operator_id])) {
                $operators_usts[$operator_id] = [];
            }
            // Adiciona o UST do mês atual ao array do operador
            $operators_usts[$operator_id][$month] = $month_data['usts'][$operator_id];
        }
    }

    // Agora, percorre o array de operadores e adiciona os USTs mês a mês
    foreach ($data['chart_1']['operators'] as &$operator) {
        $operator_id = $operator['id'];

        // Adiciona o array de USTs mês a mês ao operador
        if (isset($operators_usts[$operator_id])) {
            $operator['data_usts_each_month'] = $operators_usts[$operator_id];
        } else {
            $operator['data_usts_each_month'] = [];
        }
    }


    $data['operators_usts'] = [];
    $data['operators_usts'] = removeSubarrayKeys($operators_usts);
    

    $data['tickets']['title'] = TRANS('REPORT_TICKETS_MVP_MONTHS');
    $data['tickets']['title_table_1'] = TRANS('REPORT_TICKETS_MVP_MONTHS') . ' - ' . TRANS('USTS');
    $data['tickets']['title_table_2'] = TRANS('PERCENT_OF_TARGET_MONTHS');
    $data['tickets']['footer_usts'] = TRANS('USTS');
    $data['tickets']['operators_title'] = TRANS('SUM_BY_WORKER');
    $data['tickets']['totalPrice'] = priceScreen(array_sum(array_column($data['tickets'], 'ticket_price')));
    $data['tickets']['totalWeight'] = array_sum(array_column($data['tickets'], 'weight'));


echo json_encode($data);

return true;
