<?php
ini_set('max_execution_time', 1020);
include "top.php";

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

$type = '';
$message = '';
$skippedRows = [];
$processedCustomers = 0;
$packageAssignments = 0;
$skippedStats = ['missing_user' => 0, 'package_error' => 0, 'invalid_row' => 0];
$customers = [];

function clean_excel_cell($value)
{
    if ($value === null || $value === false) {
        return '';
    }

    $value = (string)$value;
    $value = preg_replace('/[\x00-\x1F]+/u', '', $value);
    $value = trim(preg_replace('/\s+/u', ' ', $value));

    return $value;
}

function parse_numeric_rate($value)
{
    $value = clean_excel_cell($value);
    $value = str_replace(',', '.', $value);
    $clean = preg_replace('/[^0-9.\-]/', '', $value);

    return $clean === '' ? 0.0 : floatval($clean);
}

function find_customer($identifiers)
{
    global $con;

    foreach ($identifiers as $field => $val) {
        $val = clean_excel_cell($val);
        if ($val === '') {
            continue;
        }

        $safe = mysqli_real_escape_string($con, $val);
        $row = f(q("SELECT id, custid, off_id FROM adduser WHERE $field='$safe' LIMIT 1"));

        if (!empty($row['id'])) {
            return $row;
        }
    }

    return [];
}

function ensure_package($name, $rate)
{
    global $con;
    static $cache = [];

    $name = clean_excel_cell($name);
    if ($name === '') {
        return [];
    }

    $normalized = mb_strtolower($name);
    if (isset($cache[$normalized])) {
        return $cache[$normalized];
    }

    $safeName = mysqli_real_escape_string($con, $name);
    $row = f(q("SELECT pid, pname, ptype, prc FROM tbl_pack WHERE LOWER(pname)=LOWER('$safeName') LIMIT 1"));

    if (empty($row['pid'])) {
        $row = f(q("SELECT pid, pname, ptype, prc FROM tbl_pack WHERE pname LIKE '$safeName%' LIMIT 1"));
    }

    $rateValue = number_format($rate, 2, '.', '');

    if (empty($row['pid'])) {
        $insertSql = "INSERT INTO tbl_pack SET pname='$safeName', details='$safeName', base_rate='$rateValue', prc='$rateValue', ptype='4', pmso='2', pnochannel='0', pdt=CURDATE()";
        if (!mysqli_query($con, $insertSql)) {
            return [];
        }

        $pid = mysqli_insert_id($con);
        $row = ['pid' => $pid, 'pname' => $name, 'ptype' => 4, 'prc' => $rateValue];
    } else {
        $row['ptype'] = empty($row['ptype']) ? 4 : $row['ptype'];

        if (abs(floatval($row['prc']) - $rate) > 0.01) {
            mysqli_query($con, "UPDATE tbl_pack SET prc='$rateValue', base_rate='$rateValue', pdt=CURDATE() WHERE pid='" . (int)$row['pid'] . "'");
            $row['prc'] = $rateValue;
        }

        if (trim($row['pname']) === '') {
            mysqli_query($con, "UPDATE tbl_pack SET pname='$safeName' WHERE pid='" . (int)$row['pid'] . "'");
            $row['pname'] = $name;
        }
    }

    $cache[$normalized] = $row;
    return $row;
}

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

    if (empty($_FILES['file']['name'])) {
        $type = 'error';
        $message = 'Please select an Excel file before importing.';
    } elseif (!in_array($_FILES['file']['type'], $allowedFileType)) {
        $type = 'error';
        $message = 'Invalid file type. Upload an Excel (.xls or .xlsx) file.';
    } else {
        $targetPath = 'image/' . uniqid('custpay_', true) . '_' . basename($_FILES['file']['name']);

        if (!move_uploaded_file($_FILES['file']['tmp_name'], $targetPath)) {
            $type = 'error';
            $message = 'Unable to move uploaded file to the server.';
        } else {
            $totalRows = 0;

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

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

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

                        if (!$headerSkipped) {
                            $headerSkipped = true;
                            continue;
                        }

                        $totalRows++;

                        $custId = clean_excel_cell($Row[0] ?? '');
                        $acNo = clean_excel_cell($Row[1] ?? '');
                        $custName = clean_excel_cell($Row[2] ?? '');
                        $channelRate = parse_numeric_rate($Row[3] ?? '');
                        $channelName = clean_excel_cell($Row[4] ?? '');

                        if ($custName === '' || $channelName === '') {
                            $skippedStats['invalid_row']++;
                            $skippedRows[] = [
                                'row' => $rowNumber,
                                'customer' => $custName ?: ($acNo ?: 'N/A'),
                                'reason' => 'Missing customer name or channel name',
                            ];
                            continue;
                        }

                        $customer = find_customer([
                            'msoacno' => $acNo,
                            'custid' => $custId,
                            'off_id' => $custId,
                        ]);

                        if (empty($customer['id'])) {
                            $skippedStats['missing_user']++;
                            $skippedRows[] = [
                                'row' => $rowNumber,
                                'customer' => $custName,
                                'reason' => 'No matching subscriber for AC No / cust_id',
                            ];
                            continue;
                        }

                        $pack = ensure_package($channelName, $channelRate);

                        if (empty($pack['pid'])) {
                            $skippedStats['package_error']++;
                            $skippedRows[] = [
                                'row' => $rowNumber,
                                'customer' => $custName,
                                'reason' => 'Unable to insert or find package "' . $channelName . '"',
                            ];
                            continue;
                        }

                        $uid = $customer['id'];

                        $customers[$uid]['rows'][] = [
                            'packid' => $pack['pid'],
                            'packname' => $pack['pname'],
                            'price' => number_format($channelRate, 2, '.', ''),
                            'pac_type' => empty($pack['ptype']) ? 4 : $pack['ptype'],
                            'cust_date' => date('Y-m-d'),
                        ];

                        $packageAssignments++;
                    }
                }
            } catch (Exception $ex) {
                $type = 'error';
                $message = 'Unable to read Excel file: ' . $ex->getMessage();
            }

            unlink($targetPath);

            if (empty($customers) && empty($type)) {
                $type = 'error';
                $message = 'No valid rows were found in the uploaded spreadsheet.';
            }

            if (empty($type)) {
                foreach ($customers as $uid => $data) {
                    $rows = $data['rows'];
                    if (empty($rows)) {
                        continue;
                    }

                    mysqli_query($con, "DELETE FROM tbl_change_package WHERE userid='$uid'");
                    mysqli_query($con, "DELETE FROM tbl_change_package_details WHERE customer_id='$uid' AND pac_type='4'");

                    $totalPrice = 0;
                    $names = [];

                    foreach ($rows as $entry) {
                        $totalPrice += floatval($entry['price']);
                        $names[] = $entry['packname'];
                    }

                    $names = array_unique($names);
                    $pay_ch = implode(', ', $names);
                    $escapedPayCh = mysqli_real_escape_string($con, $pay_ch);
                    $alano = count($rows);
                    $priceStr = number_format($totalPrice, 2, '.', '');

                    $insertPackageSql = "INSERT INTO tbl_change_package SET 
                        userid='$uid',
                        price='$priceStr',
                        pay_ch='$escapedPayCh',
                        packpr='$priceStr',
                        ncf='0',
                        chp='0',
                        ala='$priceStr',
                        dbp='0',
                        chno='0',
                        alano='$alano',
                        bpno='0',
                        totalc='0'";

                    if (!mysqli_query($con, $insertPackageSql)) {
                        $skippedRows[] = [
                            'row' => 'N/A',
                            'customer' => $uid,
                            'reason' => 'Unable to insert change package summary: ' . mysqli_error($con)
                        ];
                        continue;
                    }

                    $chid = mysqli_insert_id($con);

                    foreach ($rows as $entry) {
                        $detailSql = "INSERT INTO tbl_change_package_details SET 
                            chid='$chid',
                            pac_type='4',
                            cust_date='{$entry['cust_date']}',
                            pac_id='{$entry['packid']}',
                            customer_id='$uid'";
                        mysqli_query($con, $detailSql);
                    }

                    mysqli_query($con, "UPDATE adduser SET price='$priceStr', pay_ch='$escapedPayCh' WHERE id='$uid'");

                    $processedCustomers++;
                }

                $summaryParts = [
                    "Customers updated: $processedCustomers",
                    "Package rows processed: $packageAssignments",
                ];

                if ($skippedStats['missing_user'] > 0) {
                    $summaryParts[] = "Missing customers: {$skippedStats['missing_user']}";
                }
                if ($skippedStats['package_error'] > 0) {
                    $summaryParts[] = "Package errors: {$skippedStats['package_error']}";
                }
                if ($skippedStats['invalid_row'] > 0) {
                    $summaryParts[] = "Invalid rows skipped: {$skippedStats['invalid_row']}";
                }

                $type = $processedCustomers > 0 ? 'success' : 'error';
                $message = '<strong>Import finished.</strong><br>' . implode('<br>', $summaryParts);

                if ($processedCustomers === 0) {
                    $message .= '<br>No customers were updated. Please verify the subscriber IDs in the spreadsheet.';
                }
            }
        }
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Customer Package Import</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 0;
            padding: 20px;
            background: #f4f6fb;
        }
        .card {
            max-width: 1100px;
            margin: 0 auto;
            background: #fff;
            border-radius: 8px;
            padding: 30px;
            box-shadow: 0 3px 20px rgba(0,0,0,0.08);
        }
        h1 {
            margin-top: 0;
        }
        .upload-box {
            border: 1px solid #d7dfe7;
            border-radius: 6px;
            padding: 20px;
            background: #fbfcff;
        }
        .upload-box input[type="file"] {
            margin-top: 10px;
        }
        .btn-submit {
            background: #4caf50;
            color: #fff;
            border: none;
            border-radius: 4px;
            padding: 10px 18px;
            margin-top: 12px;
            font-size: 16px;
            cursor: pointer;
        }
        .btn-submit.secondary {
            background: #0d6efd;
            margin-left: 12px;
        }
        .alert {
            padding: 15px;
            border-radius: 5px;
            margin-top: 20px;
            border: 1px solid transparent;
        }
        .alert.success {
            background: #dff0d8;
            border-color: #d6e9c6;
            color: #3c763d;
        }
        .alert.error {
            background: #f2dede;
            border-color: #ebccd1;
            color: #a94442;
        }
        .instructions {
            margin-top: 30px;
            padding: 20px;
            background: #eef5ff;
            border: 1px solid #c7d8ff;
            border-radius: 6px;
        }
        .instructions ul {
            padding-left: 18px;
            margin: 10px 0 0;
        }
        .skipped-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        .skipped-table th,
        .skipped-table td {
            border: 1px solid #ddd;
            padding: 10px;
        }
        .skipped-table th {
            background: #f6f7fa;
            text-align: left;
        }
        .back-link {
            display: inline-block;
            margin-top: 25px;
            padding: 10px 18px;
            background: #6c757d;
            color: #fff;
            text-decoration: none;
            border-radius: 4px;
        }
        .back-link:hover {
            background: #5a6268;
        }
    </style>
</head>
<body>
    <div class="card">
        <h1>Import Customer Channel Assignments</h1>
        <p>Upload the <code>@4_cust_pay_channel (2).xls</code> sheet to update <code>tbl_change_package</code> and <code>tbl_change_package_details</code> for subscribers.</p>

        <div class="upload-box">
            <form method="post" enctype="multipart/form-data">
                <label for="file"><strong>Select Excel file:</strong></label><br>
                <input type="file" name="file" id="file" accept=".xls,.xlsx" required>
                <button type="submit" name="import" class="btn-submit">Import Assignments</button>
                <a href="importfiles/4_cust_pay_channel (2).xls" class="btn-submit secondary" download>Download Sample</a>
            </form>
        </div>

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

        <?php if (!empty($skippedRows)): ?>
            <h2>Skipped Rows</h2>
            <table class="skipped-table">
                <thead>
                    <tr>
                        <th>Row</th>
                        <th>Customer</th>
                        <th>Reason</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($skippedRows as $skip): ?>
                        <tr>
                            <td><?php echo $skip['row']; ?></td>
                            <td><?php echo htmlspecialchars($skip['customer']); ?></td>
                            <td><?php echo htmlspecialchars($skip['reason']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>

        <div class="instructions">
            <strong>Spreadsheet layout</strong>
            <ul>
                <li>Column A: <code>cust_id</code> (used as fallback if <code>A/C NO</code> is unavailable).</li>
                <li>Column B: <code>A/C NO</code> (matches <code>adduser.msoacno</code> or <code>adduser.custid</code>).</li>
                <li>Column C: <code>cust_name</code> (required for reporting).</li>
                <li>Column D: <code>channel_rate</code> (numeric value used as <code>tbl_pack.prc</code> and <code>adduser.price</code>).</li>
                <li>Column E: <code>channel_name</code> (inserted/updated in <code>tbl_pack</code> if missing).</li>
                <li>The import regenerates <code>tbl_change_package</code>/<code>tbl_change_package_details</code> per subscriber and updates their <code>price/pay_ch</code>.</li>
                <li>Supported formats: <code>.xls</code> and <code>.xlsx</code>.</li>
            </ul>
        </div>

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