<?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
    $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)){
        // Build Query
        $queryData = buildParameterizedQuery($type, $startDate, $endDate, $year, $minMarge, $maxMarge);

        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();
                $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

                $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';
    }
}