<?php
require('top.inc.php');
require 'vendor/autoload.php'; // PhpSpreadsheet autoload

use PhpOffice\PhpSpreadsheet\IOFactory;

// Error reporting for development
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Database connection
// $con variable available here from top.inc.php

if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_FILES["excel_file"])) {
    $target_dir = "uploads/";
    if (!is_dir($target_dir)) {
        mkdir($target_dir, 0755, true);
    }

    $fileName = basename($_FILES["excel_file"]["name"]);
    $target_file = $target_dir . time() . "_" . $fileName; // Unique file name
    $fileType = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));

    // Allowed file types
    $allowedFileTypes = ['csv', 'xls', 'xlsx'];
    if (!in_array($fileType, $allowedFileTypes)) {
        echo "<p style='color:red;'>Sorry, only CSV, XLS, and XLSX files are allowed.</p>";
        exit;
    }

    // File size check (max 5MB)
    if ($_FILES["excel_file"]["size"] > 5 * 1024 * 1024) {
        echo "<p style='color:red;'>Sorry, your file is too large. Max 5MB allowed.</p>";
        exit;
    }

    if (move_uploaded_file($_FILES["excel_file"]["tmp_name"], $target_file)) {
        echo "<p style='color:green;'>File uploaded successfully: " . htmlspecialchars($fileName) . "</p>";

        try {
            $spreadsheet = IOFactory::load($target_file);
            $worksheet = $spreadsheet->getActiveSheet();
            $data = $worksheet->toArray();

            $stmt = $con->prepare("INSERT INTO `500_ap` (`price`, `Contact_No`, `Buyer_name`, `Address_Line_1`, `Address_Line_2`, `City`, `State`, `PIN_Code`, `Product`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
            if (!$stmt) {
                die("Prepare failed: " . $con->error);
            }

            $stmt->bind_param("sssssssss", $price, $contact_no, $buyer_name, $address_line_1, $address_line_2, $city, $state, $pin_code, $product);

            $rowCount = 0;
            foreach ($data as $index => $row) {
                // Skip header
                if ($index == 0) continue;

                if (count($row) >= 9) {
                    $price = trim($row[0]);
                    $contact_no = trim($row[1]);
                    $buyer_name = trim($row[2]);
                    $address_line_1 = trim($row[3]);
                    $address_line_2 = trim($row[4]);
                    $city = trim($row[5]);
                    $state = trim($row[6]);
                    $pin_code = trim($row[7]);
                    $product = trim($row[8]);

                    if ($stmt->execute()) {
                        $rowCount++;
                    } else {
                        echo "<p style='color:red;'>Error inserting row " . ($index + 1) . ": " . $stmt->error . "</p>";
                    }
                }
            }

            echo "<p style='color:green;'>Total $rowCount rows inserted successfully.</p>";

            $stmt->close();

        } catch (Exception $e) {
            echo "<p style='color:red;'>Error reading file: " . $e->getMessage() . "</p>";
        }

    } else {
        echo "<p style='color:red;'>Sorry, there was an error uploading your file.</p>";
    }
}
?>

<div class="content pb-0">
    <div class="orders">
        <div class="row">
            <div class="col-xl-12">
                <div class="card">
                    <div class="card-body">
                        <h4 class="box-title">Upload Excel File</h4>
                        <form method="post" enctype="multipart/form-data">
                            <input type="file" name="excel_file" accept=".csv, .xls, .xlsx" required>
                            <button type="submit" class="btn btn-primary mt-3">Upload File</button>
                        </form>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<?php
require('footer.inc.php');
?>
