<?php
require 'vendor/autoload.php'; // Load PHPSpreadsheet classes

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as ExcelReader;

// Check if form is submitted
if (isset($_POST['upload'])) {
    // Capture the selected operation from the dropdown
    $operation = $_POST['operation'];

    // Check if both files are uploaded and valid
    if (isset($_FILES['csv_file']['tmp_name']) && $_FILES['csv_file']['type'] == 'text/csv' &&
        isset($_FILES['excel_file']['tmp_name']) && $_FILES['excel_file']['type'] == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
        
        // Get the file paths from the uploaded files
        $csvFilePath = $_FILES['csv_file']['tmp_name'];
        $excelFilePath = $_FILES['excel_file']['tmp_name'];

        // Load CSV File
        $csvReader = new Csv();
        $csvSpreadsheet = $csvReader->load($csvFilePath);
        $csvSheet = $csvSpreadsheet->getActiveSheet();
        $csvData = [];

        // Read CSV Data into an array
        foreach ($csvSheet->getRowIterator() as $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            $rowData = [];
            foreach ($cellIterator as $cell) {
                $rowData[] = $cell->getValue();
            }
            $csvData[] = $rowData;
        }

        // Load Excel File
        $excelReader = new ExcelReader();
        $spreadsheet = $excelReader->load($excelFilePath);
        $sheet = $spreadsheet->getActiveSheet();
        
        // Handle different operations based on the selected dropdown option
        if ($operation == 'compare_update') {
            // Compare and update the Excel file based on CSV data
            foreach ($csvData as $csvRowIndex => $csvRowData) {
                $csvKey = $csvRowData[0]; // Assuming the first column (A) in the CSV file is the key

                $highestRow = $sheet->getHighestRow(); // Get the number of rows in the Excel sheet

                // Compare each row in Excel
                for ($row = 1; $row <= $highestRow; $row++) {
                    $excelKey = $sheet->getCell('A' . $row)->getValue(); // Assuming first column (A) in Excel is the key
                    
                    if ($csvKey == $excelKey) {
                        // Update the matching Excel row with CSV data
                        for ($col = 0; $col < count($csvRowData); $col++) {
                            $excelColumnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col + 1);
                            $sheet->setCellValue($excelColumnLetter . $row, $csvRowData[$col]);
                        }
                        break;
                    }
                }
            }

        } elseif ($operation == 'append_data') {
            // Append new data from CSV to the Excel file
            $highestRow = $sheet->getHighestRow();
            foreach ($csvData as $csvRowData) {
                $highestRow++;
                for ($col = 0; $col < count($csvRowData); $col++) {
                    $excelColumnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col + 1);
                    $sheet->setCellValue($excelColumnLetter . $highestRow, $csvRowData[$col]);
                }
            }

        } elseif ($operation == 'replace_data') {
            // Clear existing Excel data and replace it entirely with CSV data
            $sheet->removeRow(1, $sheet->getHighestRow());
            foreach ($csvData as $csvRowIndex => $csvRowData) {
                for ($col = 0; $col < count($csvRowData); $col++) {
                    $excelColumnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col + 1);
                    $sheet->setCellValue($excelColumnLetter . ($csvRowIndex + 1), $csvRowData[$col]);
                }
            }
        }

        // Save the updated Excel file
        $writer = new Xlsx($spreadsheet);
        $updatedExcelFilePath = 'updated_excel_file.xlsx';
        $writer->save($updatedExcelFilePath);

        echo "Excel file successfully updated based on CSV data and selected operation!<br>";
        echo "<a href='$updatedExcelFilePath'>Download Updated Excel File</a>";
    } else {
        echo "Please upload valid CSV and Excel files.";
    }
} else {
    echo "No files uploaded.";
}