<?php
include('../include/headscript.php');
include("../class/company.php");

use PhpOffice\PhpSpreadsheet\IOFactory;

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

// Connessione al database
$conn = new mysqli($servername, $username, $password, $database);

// Controllo connessione
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['file'])) {
    $fileTmpPath = $_FILES['file']['tmp_name'];
    $fileName = $_FILES['file']['name'];
    $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));

    if ($fileExtension === 'xlsx' || $fileExtension === 'xls') {
        try {
            $spreadsheet = IOFactory::load($fileTmpPath);
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray();

            // Trova gli indici delle colonne dall'header
            $header = $rows[0]; // Prima riga
            $colIndex = array_flip($header); // Map header to column index

            $reportNoIndex = isset($colIndex['Report no.']) ? $colIndex['Report no.'] : 'S';
            $inDateIndex = isset($colIndex['IN Date']) ? $colIndex['IN Date'] : 'T';
            $dueDateIndex = isset($colIndex['DUE Date']) ? $colIndex['DUE Date'] : 'U';
            $outDateIndex = isset($colIndex['OUT Date']) ? $colIndex['OUT Date'] : 'V';

            foreach ($rows as $index => $row) {
                if ($index == 0) continue; // Salta l'intestazione

                // Ottieni i dati dalle colonne specificate
                $reportNo = $conn->real_escape_string(trim($row[$reportNoIndex]));
                $inDate = isset($row[$inDateIndex]) ? date('Y-m-d', strtotime(str_replace('/', '-', $row[$inDateIndex]))) : null;
                $dueDate = isset($row[$dueDateIndex]) ? date('Y-m-d', strtotime(str_replace('/', '-', $row[$dueDateIndex]))) : null;
                $outDate = isset($row[$outDateIndex]) ? date('Y-m-d', strtotime(str_replace('/', '-', $row[$outDateIndex]))) : null;

                // Controlla se il report esiste già
                $checkQuery = "SELECT id FROM reportsbydate WHERE report_no = '$reportNo'";
                $result = $conn->query($checkQuery);

                if ($result->num_rows == 0 && !empty($reportNo)) {
                    // Inserisci solo se il report non esiste e il Report No è valido
                    $insertQuery = "
                        INSERT INTO reportsbydate (report_no, in_date, due_date, out_date)
                        VALUES ('$reportNo', " .
                        ($inDate ? "'$inDate'" : "NULL") . ", " .
                        ($dueDate ? "'$dueDate'" : "NULL") . ", " .
                        ($outDate ? "'$outDate'" : "NULL") . ")";
                    $conn->query($insertQuery);
                }
            }

            echo "File imported successfully.";
        } catch (Exception $e) {
            echo "Error processing file: " . $e->getMessage();
        }
    } else {
        echo "Invalid file format. Please upload an Excel file.";
    }
} else {
    echo "No file uploaded.";
}

$conn->close();