<?php
ini_set('max_execution_time', 300); // 300 seconds = 5 minutes

include "top.php";

require_once('excel/php-excel-reader/excel_reader2.php');
require_once('excel/SpreadsheetReader.php');

// Initialize variables
$skippedRows = [];
$updatedCount = 0;
$skippedCount = 0;
$type = "";
$message = "";

if (isset($_POST["import"])) {
    $allowedFileType = [
        'application/vnd.ms-excel',
        'text/xls',
        'text/xlsx',
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    ];

    if (in_array($_FILES["file"]["type"], $allowedFileType)) {
        $targetPath = 'image/' . $_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

        $Reader = new SpreadsheetReader($targetPath);
        $sheetCount = count($Reader->sheets());

        for ($i = 0; $i < $sheetCount; $i++) {
            $Reader->ChangeSheet($i);
            $rowNumber = 0;

            foreach ($Reader as $Row) {
                $rowNumber++;

                // Skip header row
                if (!isset($Row[0]) || $Row[0] === 'CUSTOMER NAME') continue;

                // Map Excel columns
                $name = isset($Row[0]) ? mysqli_real_escape_string($con, trim($Row[0])) : '';
                $custid = isset($Row[1]) ? mysqli_real_escape_string($con, trim($Row[1])) : '';
                $vc = isset($Row[2]) ? mysqli_real_escape_string($con, trim($Row[2])) : '';
                $amount = isset($Row[3]) ? mysqli_real_escape_string($con, trim($Row[3])) : '';
                $last_pay_month = isset($Row[4]) ? mysqli_real_escape_string($con, trim($Row[4])) : '';

                // Check if required fields are present
                if (empty($custid) || empty($amount)) {
                    $skippedRows[] = [
                        'row' => $rowNumber,
                        'data' => $Row,
                        'reason' => 'Missing customer ID or amount'
                    ];
                    $skippedCount++;
                    continue;
                }

                // Check if customer exists
                $checkc = f(q("SELECT * FROM adduser WHERE custid='$custid'"));
                if (!empty($checkc['name'])) {
                    // Update price
                    $query = "UPDATE adduser SET price='$amount' WHERE custid='$custid'";
                    $result = mysqli_query($con, $query);

                    if (!$result) {
                        $skippedRows[] = [
                            'row' => $rowNumber,
                            'data' => $Row,
                            'reason' => 'Database error: ' . mysqli_error($con)
                        ];
                        $skippedCount++;
                    } else {
                        $updatedCount++;
                    }
                } else {
                    $skippedRows[] = [
                        'row' => $rowNumber,
                        'data' => $Row,
                        'reason' => 'Customer not found: ' . $custid
                    ];
                    $skippedCount++;
                }
            }
        }

        // Prepare final message
        if ($updatedCount > 0) {
            $type = "success";
            $message = "Successfully updated $updatedCount records.";

            if ($skippedCount > 0) {
                $message .= " $skippedCount records were skipped.";
            }
        } else {
            $type = "error";
            $message = "No records were updated. $skippedCount records were skipped.";
        }
    } else {
        $type = "error";
        $message = "Invalid File Type. Upload Excel File.";
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Update Price Import Utility</title>
    <style>
    body {
        font-family: Arial, sans-serif;
        line-height: 1.6;
        margin: 0;
        padding: 20px;
        background-color: #f5f5f5;
    }

    .container {
        max-width: 1200px;
        margin: 0 auto;
        background: white;
        padding: 20px;
        border-radius: 8px;
        box-shadow: 0 0 10px rgba(0,0,0,0.1);
    }

    h1, h2 {
        color: #333;
    }

    .upload-section {
        background: #f9f9f9;
        padding: 20px;
        border-radius: 5px;
        margin-bottom: 20px;
        border: 1px solid #ddd;
    }

    .btn-submit {
        background: #4CAF50;
        border: none;
        border-radius: 4px;
        color: white;
        cursor: pointer;
        padding: 10px 20px;
        font-size: 16px;
        margin-top: 10px;
    }

    .btn-submit:hover {
        background: #45a049;
    }

    .results {
        margin-top: 30px;
    }

    .success {
        background: #dff0d8;
        color: #3c763d;
        padding: 15px;
        border-radius: 4px;
        margin-bottom: 20px;
        border: 1px solid #d6e9c6;
    }

    .error {
        background: #f2dede;
        color: #a94442;
        padding: 15px;
        border-radius: 4px;
        margin-bottom: 20px;
        border: 1px solid #ebccd1;
    }

    .skipped-table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 20px;
    }

    .skipped-table th, .skipped-table td {
        border: 1px solid #ddd;
        padding: 10px;
        text-align: left;
    }

    .skipped-table th {
        background-color: #f2f2f2;
    }

    .skipped-table tr:nth-child(even) {
        background-color: #f9f9f9;
    }

    .skipped-table tr:hover {
        background-color: #f1f1f1;
    }

    .summary {
        font-size: 18px;
        margin-bottom: 20px;
        padding: 10px;
        background: #e9ecef;
        border-radius: 4px;
    }

    .back-link {
        display: inline-block;
        margin-top: 20px;
        padding: 10px 15px;
        background: #6c757d;
        color: white;
        text-decoration: none;
        border-radius: 4px;
    }

    .back-link:hover {
        background: #5a6268;
    }
    </style>
</head>

<body>
    <div class="container">
        <h1>Update Price Import Utility</h1>

        <div class="upload-section">
            <h2>Upload Excel File</h2>
            <form action="" method="post" name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">
                <div>
                    <label for="file">Choose Excel File:</label>
                    <input type="file" name="file" id="file" accept=".xls,.xlsx" required>
                    <button type="submit" id="submit" name="import" class="btn-submit">Update Prices</button>
                </div>
            </form>
        </div>

        <?php if (!empty($message)): ?>
            <div class="<?php echo $type; ?>">
                <?php echo $message; ?>
            </div>

            <?php if ($updatedCount > 0): ?>
                <div class="summary">
                    <strong>Update Summary:</strong><br>
                    Records successfully updated: <?php echo $updatedCount; ?><br>
                    Records skipped: <?php echo $skippedCount; ?>
                </div>
            <?php endif; ?>

            <?php if (!empty($skippedRows)): ?>
                <div class="skipped-section">
                    <h2>Skipped Rows</h2>
                    <p>The following rows were skipped during update:</p>
                    <table class="skipped-table">
                        <thead>
                            <tr>
                                <th>Row Number</th>
                                <th>Customer ID</th>
                                <th>Name</th>
                                <th>Reason</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($skippedRows as $skipped): ?>
                                <tr>
                                    <td><?php echo $skipped['row']; ?></td>
                                    <td><?php echo isset($skipped['data'][1]) ? $skipped['data'][1] : 'N/A'; ?></td>
                                    <td><?php echo isset($skipped['data'][0]) ? $skipped['data'][0] : 'N/A'; ?></td>
                                    <td><?php echo $skipped['reason']; ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            <?php endif; ?>

            <a href="index.php" class="back-link">&laquo; Back to Subscriber Page</a>
        <?php endif; ?>
    </div>
</body>
</html>