<?php
ini_set('max_execution_time', 700); // ~12 minutes

include "top.php";

// Simple import log (fresh each run)
$logFile = __DIR__ . '/import-cust-pay-channel.log';
file_put_contents($logFile, "=== Import run at " . date('Y-m-d H:i:s') . " ===\n");
if (!function_exists('log_line')) {
    function log_line($file, $msg) {
        file_put_contents($file, $msg . "\n", FILE_APPEND);
    }
}

$action = isset($_REQUEST['action']) ? $_REQUEST['action'] : '';
$flashMessage = '';
$flashType = 'info';

if ($action == '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);

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

            $Reader = new SpreadsheetReader($targetPath);
            $sheetCount = count($Reader->sheets());
            log_line($logFile, "Total sheets found: $sheetCount");

            $customers = []; // group rows by adduser.id
            $skipped = ['missing_user' => 0, 'missing_pack' => 0, 'invalid' => 0];
            $detailCount = 0;

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

                foreach ($Reader as $Row) {
                    if ($firstRow) { // header
                        $firstRow = false;
                        continue;
                    }

                    // Normalize inputs
                    $custCode = isset($Row[0]) ? trim(str_replace(',', '', $Row[0])) : '';
                    $packName = isset($Row[1]) ? trim($Row[1]) : '';
                    $assignDt = isset($Row[2]) ? trim($Row[2]) : '';

                    if ($custCode === '' || $packName === '') {
                        $skipped['invalid']++;
                        log_line($logFile, "Row skipped invalid (blank off_id or pack): off_id='{$custCode}' pack='{$packName}'");
                        continue;
                    }
                    log_line($logFile, "Row read: off_id={$custCode} pack={$packName}");

                    // Column A now matches adduser.off_id
                    $user = f(q("SELECT id, custid, off_id FROM adduser WHERE off_id='" . mysqli_real_escape_string($con, $custCode) . "' LIMIT 1"));
                    if (empty($user['id'])) {
                        $skipped['missing_user']++;
                        log_line($logFile, "  No matching adduser.off_id for '{$custCode}'");
                        continue;
                    }

                    // Find pack by name (case-insensitive); fallback to prefix match
                    $pack = f(q("SELECT pid, pname, prc, ptype FROM tbl_pack WHERE LOWER(pname)=LOWER('" . mysqli_real_escape_string($con, $packName) . "') LIMIT 1"));
                    if (empty($pack['pid'])) {
                        $pack = f(q("SELECT pid, pname, prc, ptype FROM tbl_pack WHERE pname LIKE '" . mysqli_real_escape_string($con, $packName) . "%' LIMIT 1"));
                    }
                    if (empty($pack['pid'])) {
                        $skipped['missing_pack']++;
                        log_line($logFile, "  No matching package for '{$packName}'");
                        continue;
                    }

                    $uid = $user['id'];
                    if (!isset($customers[$uid])) {
                        $customers[$uid] = [
                            'custid' => $user['custid'],
                            'off_id' => $user['off_id'],
                            'rows' => []
                        ];
                    }
                    log_line($logFile, "  Found user ID: $uid, Pack ID: " . $pack['pid']);

                    $custDate = date('Y-m-d', strtotime($assignDt ?: 'now'));

                    $customers[$uid]['rows'][] = [
                        'packid' => $pack['pid'],
                        'packname' => $pack['pname'],
                        'price' => floatval($pack['prc']),
                        'cust_date' => $custDate,
                        'pac_type' => $pack['ptype'] ?: 4
                    ];

                    $detailCount++;
                }
            }

            // Apply changes per customer
            $processedCustomers = 0;
            foreach ($customers as $uid => $data) {
                $rows = $data['rows'];
                if (empty($rows)) {
                    continue;
                }

                // wipe previous change package data for this user
                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'");
                if (mysqli_error($con)) {
                    log_line($logFile, "  ERROR deleting old data for uid=$uid : " . mysqli_error($con));
                }

                $total = 0;
                $names = [];
                foreach ($rows as $r) {
                    $total += $r['price'];
                    $names[] = $r['packname'];
                }
                $names = array_unique($names);
                $pay_ch = implode(', ', $names);
                $alano = count($rows);

                mysqli_query($con, "INSERT INTO tbl_change_package SET 
                    userid='$uid',
                    price='$total',
                    pay_ch='" . mysqli_real_escape_string($con, $pay_ch) . "',
                    packpr='$total',
                    ncf='0',
                    chp='0',
                    ala='$total',
                    dbp='0',
                    chno='0',
                    alano='$alano',
                    bpno='0',
                    totalc='0'");
                    if (mysqli_error($con)) {
                        log_line($logFile, "  ERROR inserting tbl_change_package uid=$uid : " . mysqli_error($con));
                    }
                    $insql="INSERT INTO tbl_change_package SET 
                    userid='$uid',
                    price='$total',
                    pay_ch='" . mysqli_real_escape_string($con, $pay_ch) . "',
                    packpr='$total',
                    ncf='0',
                    chp='0',
                    ala='$total',
                    dbp='0',
                    chno='0',
                    alano='$alano',
                    bpno='0',
                    totalc='0'";
                    log_line($logFile, "  Inserted change package for user ID $uid with total $total and packages: $pay_ch". " | SQL: $insql");

                $chid = mysqli_insert_id($con);

                foreach ($rows as $r) {
                    $detailSql = "INSERT INTO tbl_change_package_details SET 
                        chid='$chid',
                        pac_type='4',
                        cust_date='" . $r['cust_date'] . "',
                        pac_id='" . $r['packid'] . "',
                        customer_id='$uid'";
                    mysqli_query($con, $detailSql);
                    if (mysqli_error($con)) {
                        log_line($logFile, "  ERROR inserting detail uid=$uid : " . mysqli_error($con) . " | SQL: $detailSql");
                    } else {
                        log_line($logFile, "    Detail inserted: uid=$uid pack_id={$r['packid']} date={$r['cust_date']}");
                    }
                }

                mysqli_query($con, "UPDATE adduser SET price='$total', pay_ch='" . mysqli_real_escape_string($con, $pay_ch) . "' WHERE id='$uid'");
                if (mysqli_error($con)) {
                    log_line($logFile, "  ERROR updating adduser uid=$uid : " . mysqli_error($con));
                } else {
                    log_line($logFile, "  Updated adduser price/pay_ch uid=$uid");
                }

                $processedCustomers++;
            }

            unlink($targetPath);

            log_line($logFile, "Summary: processed=$processedCustomers details=$detailCount missing_user={$skipped['missing_user']} missing_pack={$skipped['missing_pack']} invalid={$skipped['invalid']}");

            $message = "<strong>Import completed.</strong><br>";
            $message .= "Customers updated: $processedCustomers<br>";
            $message .= "Packages assigned: $detailCount<br>";
            if ($skipped['missing_user'] > 0) $message .= "Missing customers: {$skipped['missing_user']}<br>";
            if ($skipped['missing_pack'] > 0) $message .= "Missing packages: {$skipped['missing_pack']}<br>";
            if ($skipped['invalid'] > 0) $message .= "Invalid rows: {$skipped['invalid']}<br>";
            $flashType = 'success';
            $flashMessage = $message;
        } else {
            $flashType = 'error';
            $flashMessage = "Invalid File Type. Please upload Excel file only (.xls or .xlsx)";
        }
    
}
?>
<!DOCTYPE html>
<html>
<head>
    <?php include('include/head.php'); ?>
    <title>Import Customer Package Assignments</title>
    <link rel="stylesheet" type="text/css" href="src/plugins/datatables/media/css/jquery.dataTables.css">
    <link rel="stylesheet" type="text/css" href="src/plugins/datatables/media/css/dataTables.bootstrap4.css">
    <link rel="stylesheet" type="text/css" href="src/plugins/datatables/media/css/responsive.dataTables.css">
    <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/sweetalert2@9"></script>
</head>
<body>
    <?php include('include/header.php'); ?>
    <?php include('include/sidebar.php'); ?>

    <div class="main-container">
        <div class="pd-ltr-20 customscroll customscroll-10-p height-100-p xs-pd-20-10">
            <div class="min-height-200px">
                <div class="page-header">
                    <div class="row">
                        <div class="col-md-6 col-sm-12">
                            <div class="title">
                                <h4>Import Customer Packages</h4>
                            </div>
                            <nav aria-label="breadcrumb" role="navigation">
                                <ol class="breadcrumb">
                                    <li class="breadcrumb-item"><a href="index.php">Home</a></li>
                                    <li class="breadcrumb-item"><a href="customerchannels.php">Customer Channels</a></li>
                                    <li class="breadcrumb-item active" aria-current="page">Import</li>
                                </ol>
                            </nav>
                        </div>
                        <div class="col-md-6 col-sm-12 text-right">
                            <a href="customerchannels.php" class="btn btn-primary">
                                <i class="fa fa-arrow-left"></i> Back to Customer Channels
                            </a>
                        </div>
                    </div>
                </div>

        <div class="pd-20 bg-white border-radius-4 box-shadow mb-30">
            <div class="alert alert-secondary">
                <strong>Log file:</strong> <?php echo basename($logFile); ?> (latest run appended). Check it if rows are skipped.
            </div>
                    <?php if (!empty($flashMessage)): ?>
                        <div class="alert alert-<?php echo $flashType == 'success' ? 'success' : 'danger'; ?>">
                            <?php echo $flashMessage; ?>
                        </div>
                    <?php endif; ?>
                    <div class="clearfix mb-20">
                        <div class="pull-left">
                            <h5 class="text-blue">Upload Excel File</h5>
                        </div>
                    </div>

                    <div class="alert alert-info">
                        <strong>Instructions:</strong>
                        <ul style="margin-top:10px;">
                            <li><strong>Column A:</strong> Customer Office ID (matches <code>adduser.off_id</code>)</li>
                            <li><strong>Column B:</strong> Package Name (must exist in <code>tbl_pack.pname</code>)</li>
                            <li><strong>Column C:</strong> Assign Date (goes to <code>tbl_change_package_details.cust_date</code>)</li>
                            <li><strong>Column D:</strong> Not used (price pulled from <code>tbl_pack.prc</code>)</li>
                            <li>Existing change-package entries for a customer will be replaced.</li>
                            <li>Supported formats: .xls, .xlsx</li>
                        </ul>
                    </div>

                    <form action="import-cust-pay-channel.php?action=import" method="POST" enctype="multipart/form-data">
                        <div class="form-group">
                            <label>Select Excel File:</label>
                            <input type="file" name="file" class="form-control" accept=".xls,.xlsx" required>
                        </div>
                        <div class="form-group">
                            <button type="submit" class="btn btn-success">
                                <i class="fa fa-upload"></i> Import Packages
                            </button>
                            <a href="4_cust_pay_channel.xls" class="btn btn-info" download>
                                <i class="fa fa-download"></i> Download Sample File
                            </a>
                        </div>
                    </form>
                </div>
            </div>
        </div>
    </div>

    <?php include('include/footer.php'); ?>
</body>
</html>
