<?php

require_once 'vendor/autoload.php';
require_once 'database.php';
require_once 'helpers/core.inc.php';
require_once 'helpers/spreadsheet.inc.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


if (isset($_GET['export']) && !empty($_GET['export'])) {
    $pdo = getDatabaseConnection();
    // Validate and sanitize all inputs
    $all = validateAll($_GET['all'] ?? '');
    $user = validateUser($_GET['id_etab_user'] ?? '');
    $type = validateExportType($_GET['export'] ?? '');
    $startDate = validateDate($_GET['start_date'] ?? '');
    $endDate = validateDate($_GET['end_date'] ?? '');
    $year = validateYear($_GET['years'] ?? '');
    $minMarge = validateMargin($_GET['min_marge'] ?? '');
    $maxMarge = validateMargin($_GET['max_marge'] ?? '');

    if (canExport($type, $startDate, $endDate, $year, $minMarge, $maxMarge, $user, $all)) {
        // Build Query
        $queryData = buildParameterizedQuery($type, $startDate, $endDate, $year, $minMarge, $maxMarge, $user, $all);

        try {
            $stmt = $pdo->prepare($queryData['query']);
            $stmt->execute($queryData['params']);
            // Bind parameters
            foreach ($queryData['params'] as $param => $value) {
                $stmt->bindValue($param, $value);
            }
            $stmt->execute();

            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $filename = "export_smac_devis_" . $type . "_" . date('d_m_Y_H_i_s', time());

            if ($results) {
                $spreadsheet = new Spreadsheet();
                $spreadsheet->getDefaultStyle()->getFont()->setName('Calibri');
                $sheet = $spreadsheet->getActiveSheet();
                // Adding title
                $title = cleanTitleFromFilename($filename);
                // Append filter in title
                $title .= isset($startDate) && !empty($startDate) ? 'DEPUIS ' . $startDate . ' ' : ' ';
                $title .= isset($endDate) && !empty($endDate) ? 'JUSQU\'À ' .  $endDate . ' ' : ' ';
                $title .= isset($year) && !empty($year) ? 'DE ' . $year . ' ' : ' ';
                $title .= isset($minMarge) && !empty($minMarge)  ? 'DE ' . $minMarge . '% DE MARGE ' : ' ';
                $title .= isset($maxMarge) && !empty($maxMarge) ? 'À ' . $maxMarge . '% DE MARGE ' : ' ';
                $error = false;
                // Data
                $firstColumn = 'A';
                $lastColumn = $sheet->getHighestColumn(); // Last filled column

                if ($type === 'etablissement') {
                    // Get all etablissements
                    $etablissements = [];
                    foreach ($results as $result) {
                        $etablissements[$result['ETABLISSEMENT']] = $result;
                    }
                    if (!empty($etablissements)) {
                        $escapedKeys = array_map(function ($key) {
                            return str_replace("'", "''", $key);
                        }, array_keys($etablissements));
                        $etablissementList = implode("', '", $escapedKeys);

                        // Query for agencies
                        $query = "SELECT 
                                        t_parent.libelle as agence,
                                        GROUP_CONCAT(DISTINCT t_structure.libelle SEPARATOR ', ') AS etablissement,
                                        COUNT(DISTINCT t_structure.libelle) AS nb_etablissement
                                  FROM t_structure 
                                  INNER JOIN t_structure AS t_parent on t_parent.id = t_structure.parent_id 
                                  WHERE t_structure.libelle IN ('$etablissementList') AND t_parent.niveau_id = 2
                                  GROUP BY t_parent.libelle ORDER BY t_parent.libelle ASC ";
                        try {
                            $stmt = $pdo->prepare($query);
                            $stmt->execute();
                            $agencies = $stmt->fetchAll(PDO::FETCH_ASSOC);
                        } catch (PDOException $e) {
                            // Can't export
                            $error = true;
                            $_SESSION['return'] = 3;
                            $_SESSION['return_message'] = '<strong>DB</strong> Le système n\'a pas pu exporter<br>' . $e->getMessage();
                            echo $e->getMessage();
                            die();
                        }
                    }
                    if (!empty($agencies)) {
                        // flatten and regroup the agencies data
                        $agenciesList = [];
                        foreach ($agencies as $agency) {
                            foreach (explode(', ', $agency['etablissement']) as $detailsEtablissement) {
                                $agenciesList[$agency['agence']][] = $etablissements[$detailsEtablissement];
                            }
                        }
                    }
                    // Format the data
                    $sheet->fromArray(array_keys($results[0]), NULL, 'A2'); // Columns
                    $row = 3;
                    // Agencies
                    $headers = array_keys(reset($etablissements));
                    $sheet->fromArray(array_merge(['AGENCE'], $headers), NULL, 'A2');
                    $sheet->getStyle('A2:' . chr(65 + count($headers)) . '2')->getFont()->setBold(true);

                    foreach ($agenciesList as $agence => $etabs) {
                        // Cumuler les valeurs numériques
                        $sums = [];
                        $counts = [];
                        foreach ($etabs as $etab) {
                            foreach ($etab as $key => $val) {
                                if ($key !== 'ETABLISSEMENT') {
                                    $sums[$key] = ($sums[$key] ?? 0) + $val;
                                    $counts[$key] = ($counts[$key] ?? 0) + 1;
                                }
                            }
                        }

                        // Agencies Row
                        $sheet->setCellValue("A{$row}", $agence);
                        $col = 'B';
                        foreach ($headers as $head) {
                            if ($head === 'ETABLISSEMENT') {
                                $value = '';
                            } elseif ($head === 'MARGE_%') {
                                // Average
                                $value = ($sums['CA_COMMANDÉ'] != 0) ? round(($sums['MARGE_VALEUR'] / $sums['CA_COMMANDÉ']) * 100, 2) : 0; // Eviter le diviser par 0
                            } else {
                                // Total
                                $value = $sums[$head] ?? '';
                            }
                            $sheet->setCellValue("{$col}{$row}", $value);
                            $col++;
                        }
                        $lastColumn = $sheet->getHighestColumn(); // Last filled column
                        $sheet->getStyle("A{$row}:" . $lastColumn . "{$row}")->getFont()->setBold(true);
                        applyMainColor($sheet, $firstColumn, $lastColumn, $row);
                        $row++;

                        // Etablissements Rows
                        foreach ($etabs as $etab) {
                            $sheet->setCellValue("A{$row}", '');
                            $col = 'B';
                            foreach ($headers as $head) {
                                $sheet->setCellValue("{$col}{$row}", $etab[$head]);
                                $col++;
                            }
                            $row++;
                        }
                        applyColorAlterning($sheet, $startRow = $row - count($etabs), $endRow = $row, $lastColumn); // Apply color
                    }
                    applyMonetaryFormat($sheet, ['D', 'F', 'H', 'J']);
                } else if ($type === 'charge_affaire') {
                    // Get all persons
                    $persons = [];
                    foreach ($results as $result) {
                        $persons[$result['ID']] = $result;
                    }

                    if (!empty($persons)) {
                        $personsList = implode("', '", array_keys($persons));
                        // Query for agencies
                        $query = "SELECT 
                                        COALESCE(t_structure.libelle, 'PAS D\'ETABLISSEMENT') as etablissement,
                                        GROUP_CONCAT(DISTINCT t_user.id SEPARATOR ', ') AS persons,
                                        COUNT(DISTINCT t_user.id) AS nb_person
                                  FROM t_user 
                                  LEFT JOIN t_structure on t_structure.id = t_user.structure_id 
                                  WHERE t_user.id IN ('$personsList') AND t_structure.niveau_id = 3
                                  GROUP BY t_structure.libelle ORDER BY t_structure.libelle ASC ";
                        try {
                            $stmt = $pdo->prepare($query);
                            $stmt->execute();
                            $person_etablissements = $stmt->fetchAll(PDO::FETCH_ASSOC);
                        } catch (PDOException $e) {
                            // Can't export
                            $error = true;
                            $_SESSION['return'] = 3;
                            $_SESSION['return_message'] = '<strong>DB</strong> Le système n\'a pas pu exporter<br>' . $e->getMessage();
                            echo $e->getMessage();
                            die();
                        }
                    }
                    if (!empty($person_etablissements)) {
                        // flatten and regroup the agencies data
                        $etablissementList = [];
                        foreach ($person_etablissements as $person_etablissement) {
                            foreach (explode(', ', $person_etablissement['persons']) as $detailPerson) {
                                $etablissementList[$person_etablissement['etablissement']][] = $persons[$detailPerson];
                            }
                        }
                    }

                    // Format the data
                    $sheet->fromArray(array_keys($results[0]), NULL, 'A2'); // Columns
                    $row = 3;
                    // Persons etablissement
                    $headers = array_keys(reset($persons));
                    // Remove ID
                    $keyToRemove = 'ID';
                    $headers = array_filter($headers, function ($key) use ($keyToRemove) {
                        return $key != $keyToRemove;
                    });
                    $sheet->fromArray(array_merge(['ETABLISSEMENT'], $headers), NULL, 'A2');
                    $sheet->getStyle('A2:' . chr(65 + count($headers)) . '2')->getFont()->setBold(true);

                    foreach ($etablissementList as $etablissement => $etabs) {
                        // Cumuler les valeurs numériques
                        $sums = [];
                        $counts = [];
                        foreach ($etabs as $etab) {
                            foreach ($etab as $key => $val) {
                                if ($key !== 'CHARGÉ_AFFAIRE' && $key !== 'ID') {
                                    $sums[$key] = ($sums[$key] ?? 0) + $val;
                                    $counts[$key] = ($counts[$key] ?? 0) + 1;
                                }
                            }
                        }

                        // Agencies Row
                        $sheet->setCellValue("A{$row}", $etablissement);
                        $col = 'B';
                        foreach ($headers as $head) {
                            if ($head === 'CHARGÉ_AFFAIRE') {
                                $value = '';
                            } elseif ($head === 'MARGE_%') {
                                // Average
                                $value = ($sums['CA_COMMANDÉ'] != 0) ? round(($sums['MARGE_VALEUR'] / $sums['CA_COMMANDÉ']) * 100, 2) : 0; // Eviter le diviser par 0
                            } else {
                                // Total
                                $value = $sums[$head] ?? '';
                            }
                            if ($head !== 'ID') {
                                $sheet->setCellValue("{$col}{$row}", $value);
                            }
                            $col++;
                        }
                        $lastColumn = $sheet->getHighestColumn(); // Last filled column
                        $sheet->getStyle("A{$row}:" . $lastColumn . "{$row}")->getFont()->setBold(true);
                        applyMainColor($sheet, $firstColumn, $lastColumn, $row);
                        $row++;

                        // Etablissements Rows
                        foreach ($etabs as $etab) {
                            $sheet->setCellValue("A{$row}", '');
                            $col = 'B';
                            foreach ($headers as $head) {
                                $sheet->setCellValue("{$col}{$row}", $etab[$head]);
                                $col++;
                            }
                            $row++;
                        }
                        applyColorAlterning($sheet, $startRow = $row - count($etabs), $endRow = $row, $lastColumn); // Apply color
                    }
                    applyMonetaryFormat($sheet, ['D', 'F', 'H', 'J']);
                } else {
                    $sheet->fromArray(array_keys($results[0]), NULL, 'A2'); // Columns
                    $sheet->fromArray($results, NULL, 'A3');
                    $lastColumn = $sheet->getHighestColumn(); // Last filled column
                    applyColorAlterning($sheet, $startRow = 3, $endRow = $startRow + count($results) - 1, $lastColumn); // Apply color
                    // Apply Format
                    applyMonetaryFormat($sheet, ['C', 'E', 'G', 'I']);
                }
                //Apply Filter
                applyFilterAndColor($sheet, $firstColumn, $lastColumn, 2);
                // Title
                applyTitleAndStyle($sheet, $firstColumn, $lastColumn, 1, $title);
                // Auto Sizing
                applyAutoColumnSizing($sheet, $lastColumn);

                $writer = new Xlsx($spreadsheet);
                $filePath = "exports/" . $filename . ".xlsx";
                $writer->save($filePath);

                if (file_exists($filePath) && !$error) {
                    $_SESSION['return'] = 1;
                    $_SESSION['file'] = $filePath;
                    $_SESSION['filename'] = $filePath;
                    $_SESSION['return_message'] = 'Export effectué.';
                } else {
                    // Can't export
                    $_SESSION['return'] = 3;
                    $_SESSION['return_message'] = '<strong>Fichier</strong> Le système n\'a pas pu exporter.';
                }
            } else {
                // Not data found
                $_SESSION['return'] = 2;
                $_SESSION['return_message'] = 'Pas de données trouvées.';
            }
        } catch (PDOException $e) {
            // Can't export
            $_SESSION['return'] = 3;
            $_SESSION['return_message'] = '<strong>DB</strong> Le système n\'a pas pu exporter<br>';
        }
    } else {
        // Can't export
        $_SESSION['return'] = 3;
        // $_SESSION['return_message'] = 'Le système n\'a pas pu exporter';
    }
}
