<?php
require('connection.inc.php');
// Include PhpSpreadsheet autoload file
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;



// Check if form is submitted and file is uploaded
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_FILES["excel_file"])) {
    // Create connection

    // Directory where uploaded files will be saved
    $target_dir = "uploads/";
    // File name with timestamp to make it unique
    $target_file = $target_dir . basename($_FILES["excel_file"]["name"]);

    $uploadOk = 1;
    $imageFileType = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));

    // Check if file already exists
    if (file_exists($target_file)) {
        echo "Sorry, file already exists.";
        $uploadOk = 0;
    }

    // Check file size
    if ($_FILES["excel_file"]["size"] > 500000) {
        echo "Sorry, your file is too large.";
        $uploadOk = 0;
    }

    // Allow certain file formats
    if ($imageFileType != "xlsx" && $imageFileType != "xls" && $imageFileType != "csv") {
        echo "Sorry, only XLSX, XLS, and CSV files are allowed.";
        $uploadOk = 0;
    }

    // Check if $uploadOk is set to 0 by an error
    if ($uploadOk == 0) {
        echo "Sorry, your file was not uploaded.";
    // if everything is ok, try to upload file
    } else {
        if (move_uploaded_file($_FILES["excel_file"]["tmp_name"], $target_file)) {
            echo "The file " . htmlspecialchars(basename($_FILES["excel_file"]["name"])) . " has been uploaded.";

            // Load Excel file
            $spreadsheet = IOFactory::load($target_file);

            // Select active sheet
            $sheet = $spreadsheet->getActiveSheet();

            // Get highest row (assuming headers are in the first row)
            $highestRow = $sheet->getHighestRow();

            // Prepare statement for inserting data
            $stmt = $con->prepare("INSERT INTO 500_ap (Contact_No, Buyer_name, Address_Line_1, Address_Line_2, City, State, PIN_Code, Product, upi_id, payment_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            // Bind parameters
            $stmt->bind_param("ssssssssss", $contact_no, $buyer_name, $address_line_1, $address_line_2, $city, $state, $pin_code, $product, $upi_id, $payment_amount);

            for ($row = 2; $row <= $highestRow; $row++) {
                // Read cell data (Mapping based on schema)
                $contact_no = $sheet->getCell('A' . $row)->getValue();
                $buyer_name = $sheet->getCell('B' . $row)->getValue();
                $address_line_1 = $sheet->getCell('C' . $row)->getValue();
                $address_line_2 = $sheet->getCell('D' . $row)->getValue();
                $city = $sheet->getCell('E' . $row)->getValue();
                $state = $sheet->getCell('F' . $row)->getValue();
                $pin_code = $sheet->getCell('G' . $row)->getValue();
                $product = $sheet->getCell('H' . $row)->getValue();
                $upi_id = $sheet->getCell('I' . $row)->getValue();
                $payment_amount = $sheet->getCell('J' . $row)->getValue();

                // Execute the statement
                if ($stmt->execute()) {
                    echo "Inserted row $row successfully<br>";
                } else {
                    echo "Error inserting row $row: " . $con->error . "<br>";
                }
            }

            // Close statement
            $stmt->close();
        } else {
            echo "Sorry, there was an error uploading your file.";
        }
    }

    // Close connection
    $con->close();
}

// Include footer after processing
require('footer.inc.php');
?>
