<?php
// Ensure errors are displayed for debugging purposes
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
ini_set('max_execution_time', '0'); // for infinite time of execution
ini_set('memory_limit', '-1');

// Include PhpSpreadsheet library
require 'vendor/autoload.php';

// Check the request method and perform corresponding actions
if ($_SERVER["REQUEST_METHOD"] === "POST") {
    if (isset($_POST['map'])) {
        handleMap();
    } elseif (isset($_POST['export'])) {
        handleExport();
    } elseif (isset($_POST['import'])) {
        handleImport();
    } elseif (isset($_POST['delete'])) {
        deleteRecords();
    }
}

// Function to handle record deletion
function deleteRecords() {
    // Output confirmation modal and JavaScript
    echo <<<HTML
    <head>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

    <!-- Add this at the end of the body section before your script -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UoKc8Zy5v5pXfXzj/a7JsFq9vNlE9vVbLqUnuQ5ZB/Bb5dSqueO5q3dECe3fIM5" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
    </head>
    <script>
        // Function to show the confirmation dialog
        function showConfirmation() {
            $('#confirmationModal').modal('show');
        }

        // Call the showConfirmation function when the page loads
        window.onload = function () {
            showConfirmation();
        };
    </script>

    <!-- Add this modal at the end of your HTML body -->
    <div class="modal fade" id="confirmationModal" tabindex="-1" role="dialog" aria-labelledby="confirmationModalLabel" aria-hidden="true">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="confirmationModalLabel">Confirmation</h5>
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">&times;</span>
                    </button>
                </div>
                <div class="modal-body">
                    <p>Are you sure you want to delete all records?</p>
                    <p>Once you confirm, all the Data will be Deleted...</p>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-dismiss="modal" onclick="redirectNo()">No</button>
                    <button type="button" class="btn btn-primary" onclick="deleteConfirmed()">Yes</button>
                </div>
            </div>
        </div>
    </div>

    <script>
        // Function to handle delete confirmation
        function deleteConfirmed() {
            // Send an AJAX request to delete records
            var xhr = new XMLHttpRequest();
            xhr.open('POST', 'delete_records.php', true);
            xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');

            xhr.onreadystatechange = function () {
                if (xhr.readyState === XMLHttpRequest.DONE) {
                    if (xhr.status === 200) {
                        if (xhr.responseText.includes('Records deleted successfully')) {
                            // Deletion successful, redirect to text.php or do something else
                            alert('Records deleted successfully');
                            window.location.href = 'text.php';
                        } else {
                            // Display an error message
                            alert('Error during record deletion: ' + xhr.responseText);
                            window.location.href = 'text.php';
                        }
                    } else {
                        // Display an error message
                        alert('Error during record deletion. Status: ' + xhr.status);
                        window.location.href = 'text.php';
                    }
                }
            };

            // Send the request with a dummy parameter (e.g., delete=true)
            xhr.send('delete=true');
        }

        function redirectNo() {
            // Redirect to text.php when the user clicks "No"
            window.location.href = 'text.php';
        }
    </script>
HTML;
}

// Function to handle mapping
function handleMap() {
    // Check if a file is selected
    if (empty($_FILES['file']['name'])) {
        echo "<script>alert('Please select a file.'); window.location.href = 'text.php';</script>";
        return;
    }

    // Retrieve the uploaded file
    $uploadedFile = $_FILES['file'];

    // Check if file upload was successful
    if ($uploadedFile['error'] === UPLOAD_ERR_OK) {
        // Retrieve temporary file path
        $tempFilePath = $uploadedFile['tmp_name'];

        // Generate a unique file name for the CSV file
        $csvFilePath = 'uploads/' . uniqid() . '.csv';

        // Convert Excel to CSV
        if (convertExcelToCSV($tempFilePath, $csvFilePath)) {
            // CSV conversion successful, now you can import CSV data into the database
            echo "Excel file converted to CSV successfully. CSV file saved as: $csvFilePath";
            header("Location: mapping_page.php?file=$csvFilePath", true, 301);
            exit();
        } else {
            // Error occurred during CSV conversion
            echo "<script>alert('Select Proper Excel file .'); window.location.href = 'text.php';</script>";
        }
    } else {
        // Error uploading file
        echo "Error uploading file.";
    }
}

// Function to convert Excel file to CSV
function convertExcelToCSV($inputFilePath, $outputFilePath) {
    try {
        // Load the Excel file
        $reader = PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($inputFilePath);
        $spreadsheet = $reader->load($inputFilePath);

        // Save as CSV
        $writer = PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Csv');
        $writer->save($outputFilePath);

        return true; // Conversion successful
    } catch (\Exception $e) {
        return false; // Conversion failed
    }
}

// Function to handle exporting data from the database
function handleExport() {
    // Set up output buffering for streaming output
    ob_start();

    // Create a new Spreadsheet object
    $spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    // Set column headers
    $headers = ['Id', 'UPC', 'SKU', 'Product', 'Price(USD)', 'Brand', 'Stock', 'VendorName'];
    $sheet->fromArray([$headers], null, 'A1');

    // Establish a database connection
    $conn = new mysqli('localhost', 'root', '', 'shangrilaperfumes');

    // Check the database connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    // Query to fetch data from 'test_table' and 'vendor' table
    $sql = "SELECT test_table.Id, test_table.UPC, test_table.SKU, test_table.Product, 
                   test_table.Price, test_table.Brand, test_table.Stock, vendor.VendorName
            FROM test_table
            JOIN vendor ON test_table.Vendor = vendor.id
            ORDER BY test_table.UPC"; // Sort by UPC

    // Execute the query with a limit and offset
    $limit = 1000; // Adjust as needed
    $offset = 0;
    do {
        $result = $conn->query("$sql LIMIT $limit OFFSET $offset");
        if ($result->num_rows > 0) {
            $min_prices = [];
            $rowData = [];

            // Populate data into array for batch writing
            while ($row = $result->fetch_assoc()) {
                $rowData[] = [
                    $row['Id'], $row['UPC'], $row['SKU'], $row['Product'],
                    $row['Price'], $row['Brand'], $row['Stock'], $row['VendorName']
                ];

                // Track minimum prices
                if (!isset($min_prices[$row['UPC']]) || $row['Price'] < $min_prices[$row['UPC']]) {
                    $min_prices[$row['UPC']] = $row['Price'];
                }
            }

            // Batch writing data
            $sheet->fromArray($rowData, null, 'A' . ($offset + 2));

            // Highlight rows with minimum price for each UPC
            foreach ($min_prices as $upc => $min_price) {
                foreach ($rowData as $index => $row) {
                    if ($row[1] == $upc && $row[4] == $min_price) {
                        $sheet->getStyle('A' . ($index + $offset + 2) . ':H' . ($index + $offset + 2))->applyFromArray([
                            'fill' => [
                                'fillType' => PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                                'startColor' => ['rgb' => 'C1E1C1'], // Green color
                            ],
                        ]);
                    }
                }
            }

            // Increment offset
            $offset += $limit;
        }
    } while ($result->num_rows > 0);

    // Close the database connection
    $conn->close();

    // Save Excel file to a temporary file
    $tempFile = tempnam(sys_get_temp_dir(), 'export_');
    $writer = PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save($tempFile);

    // Set the filename for the exported file
    $file_name = 'exported_data.xlsx';

    // Send headers for file download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $file_name . '"');
    header('Cache-Control: max-age=0');
    header('Content-Length: ' . filesize($tempFile));

    // Flush the output buffer and send the Excel file
    ob_end_flush();
    readfile($tempFile);

    // Delete the temporary file
    unlink($tempFile);

    exit;
}

// Function to handle importing data from Excel
function handleImport() {
    $uploadedFile = $_FILES['file'];
    $selectedVendor = $_POST['vendor'];

    if ($uploadedFile['error'] === UPLOAD_ERR_OK) {
        $tempFilePath = $uploadedFile['tmp_name'];
        $newFilePath = 'uploads/' . uniqid() . '.xlsx';

        if (move_uploaded_file($tempFilePath, $newFilePath)) {
            if (file_exists($newFilePath)) {
                $command = "python import_excel.py $newFilePath $selectedVendor";
                $output = shell_exec($command);

                // Check the success condition (modify this based on your script's behavior)
                if (strpos($output, 'Success') !== false) {
                    $outputMessage = 'File inserted successfully.';
                } else {
                    $outputMessage = 'Error during import operation.';
                }
            } else {
                $outputMessage = "Error: File not found at $newFilePath";
            }
        } else {
            $outputMessage = "Error moving the uploaded file.";
        }
    } else {
        $outputMessage = "Error uploading file.";
    }
    header('Location:text.php');
}
?>
