<?php
include '../config.php';
$data_col = ["sys_RespNum","nps_score","Q2A","Q2D","Good_Architect","Account_Type","Month","domain","verbatim","category","market"];

if(isset($_FILES['excelFile7']['name']) && $_FILES['excelFile7']['name'] != "") {
    $allowedExtensions = array("xls","xlsx");
    $ext = pathinfo($_FILES['excelFile7']['name'], PATHINFO_EXTENSION);
    if(in_array($ext, $allowedExtensions)) {
        $file_size = $_FILES['excelFile7']['size'] / 1024;
        if($file_size < 500000) {
            $fileTmpPath = $_FILES['excelFile7']['tmp_name'];
            $uploadFileDir = '../uploads/';
            $dest_path = $uploadFileDir . $_FILES['excelFile7']['name'];
            $file = $dest_path;
            $isUploaded = move_uploaded_file($fileTmpPath, $dest_path);
            
            if ($isUploaded) {
                require '../vendor/autoload.php';

                try {
                    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($dest_path);
                } catch (Exception $e) {
                    die('Error loading file "' . pathinfo($dest_path, PATHINFO_BASENAME) . '": ' . $e->getMessage());
                }

                $sheet = $spreadsheet->getActiveSheet();
                $total_rows = $sheet->getHighestRow();
                $highest_column = $sheet->getHighestColumn();
                $highest_col_index = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highest_column); // get last column index

                // Get the "month" value from the last column of the first data row (assuming row 2)
                $monthCell = $sheet->getCellByColumnAndRow($highest_col_index, 2)->getValue();
                $month = mysqli_real_escape_string($conn, $monthCell);

                // Check if month already exists in the database
                $month_col_name = $data_col[5]; // assumes 'month' is the last column in your $data_col array
                $check_query = "SELECT COUNT(*) as count FROM `voc` WHERE `$month_col_name` = '$month'";
                $check_result = mysqli_query($conn, $check_query);
                $check_data = mysqli_fetch_assoc($check_result);

                if ($check_data['count'] > 0) {
                    echo "Data is already present for this month";
                    unlink($dest_path);
                    return;
                }

                // Proceed with inserting if month is not already present
                $colX = "(`" . implode("`, `", $data_col) . "`)";
                $count_column = count($data_col);
                $successCount = 0;
                $errorCount = 0;

                for ($row = 2; $row <= $total_rows; $row++) {
                    $single_row = $sheet->rangeToArray('A' . $row . ':' . $highest_column . $row, NULL, TRUE, FALSE)[0];

                    if (count($single_row) == $count_column) {
                        $escaped_values = array_map(function($val) use ($conn) {
                            $cleaned = preg_replace('/_x([0-9a-fA-F]{4})_/', '&#x$1;', $val);
                            return "'" . mysqli_real_escape_string($conn, html_entity_decode($cleaned)) . "'";
                        }, $single_row);

                        $query = "INSERT INTO `voc` $colX VALUES (" . implode(", ", $escaped_values) . ")";
                        if (mysqli_query($conn, $query)) {
                            $successCount++;
                        } else {
                            $errorCount++;
                            error_log("Row $row error: " . mysqli_error($conn));
                        }
                    } else {
                        $errorCount++;
                        error_log("Row $row skipped due to column mismatch.");
                    }
                }
                echo "Success";
                if ($errorCount > 0) {
                    echo "Failed";
                }
                unlink($dest_path);
            } else {
                echo 'File not uploaded';
            }
        } else {
            echo 'Maximum file size should not cross 500 KB';
        }
    } else {
        echo 'This type of file is not allowed';
    }
} else {
    echo 'Select an Excel file first';
}

?>