<?php
ini_set('max_execution_time', 300);

include "top.php";

if ($_SESSION['murgibhai'] == '') {
    if (isset($_COOKIE["user_login"])) {
        $_SESSION['murgibhai'] = $_COOKIE["user_login"];
    } else {
        header("Location:login.php");
        exit;
    }
}

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

$skippedRows = [];
$importedCount = 0;
$updatedCount = 0;
$skippedCount = 0;
$type = "";
$message = "";

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_siti_pack_price($text)
{
    $text = clean_excel_cell($text);
    if (preg_match('/\b\d{3}\b/', $text, $matches)) {
        return floatval($matches[0]);
    }
    return 0.00;
}

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

    if (!isset($_FILES['file']['name']) || $_FILES['file']['name'] === '') {
        $type = "error";
        $message = "Please select an Excel file to import.";
    } elseif (!in_array($_FILES["file"]["type"], $allowedFileTypes)) {
        $type = "error";
        $message = "Invalid file type. Upload Excel file (.xls or .xlsx).";
    } else {
        $targetPath = 'image/' . uniqid('siti_import_', true) . '_' . basename($_FILES['file']['name']);
        if (!move_uploaded_file($_FILES['file']['tmp_name'], $targetPath)) {
            $type = "error";
            $message = "Failed to move uploaded file for processing.";
        } else {
            $processed = false;

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

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

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

                        if ($rowNumber === 1) {
                            continue; // Skip header row
                        }

                        $sNo = clean_excel_cell($Row[0] ?? '');
                        $smsId = clean_excel_cell($Row[1] ?? '');
                        $custName = clean_excel_cell($Row[2] ?? '');
                        $address = clean_excel_cell($Row[3] ?? '');
                        $vc = clean_excel_cell($Row[4] ?? '');
                        $stb = clean_excel_cell($Row[5] ?? '');
                        $lcoId = clean_excel_cell($Row[6] ?? '');
                        $dealerName = clean_excel_cell($Row[7] ?? '');
                        $packageName = clean_excel_cell($Row[8] ?? '');
                        $linemanId = clean_excel_cell($Row[9] ?? '');
                        $linemanName = clean_excel_cell($Row[10] ?? '');
                        $subStatus = clean_excel_cell($Row[11] ?? '');

                        $custid = $smsId;

                        if ($custName === '' || $custid === '') {
                            $skippedRows[] = [
                                'row' => $rowNumber,
                                'data' => $Row,
                                'reason' => 'Missing subscriber name or SMS ID'
                            ];
                            $skippedCount++;
                            continue;
                        }

                        // Clean VC and STB
                        $vc = preg_replace('/[^a-zA-Z0-9]/', '', $vc);
                        $stb = preg_replace('/[^a-zA-Z0-9]/', '', $stb);

                        // Parse Area Name from the Address before the first comma
                        $parts = explode(',', $address);
                        $areaName = trim($parts[0] ?? '');
                        if ($areaName === '') {
                            $areaName = 'UNKNOWN';
                        }

                        // Area
                        $getarea = f(q("SELECT * FROM tbl_area WHERE arname='$areaName'"));
                        if (empty($getarea['arname'])) {
                            mysqli_query($con, "INSERT INTO tbl_area SET arname='$areaName'");
                            $arid = mysqli_insert_id($con);
                        } else {
                            $arid = $getarea['arid'];
                        }

                        // MSO: "SITI Networks"
                        $msoName = 'SITI Networks';
                        $getmso = f(q("SELECT * FROM tbl_mso WHERE m_name='$msoName'"));
                        if (empty($getmso['m_name'])) {
                            mysqli_query($con, "INSERT INTO tbl_mso SET m_name='$msoName'");
                            $mid = mysqli_insert_id($con);
                        } else {
                            $mid = $getmso['m_id'];
                        }

                        // Lineman Name: "If lineman name is not present add them"
                        $line_ids = 0;
                        if ($linemanName !== '') {
                            $getlineman = f(q("SELECT * FROM tbl_login WHERE l_type='2' AND l_name='$linemanName'"));
                            if (empty($getlineman['l_name'])) {
                                $l_read = strtolower(preg_replace('/[^a-zA-Z0-9]/', '', $linemanName));
                                if ($l_read === '') {
                                    $l_read = 'lineman_' . rand(100, 999);
                                }
                                $chk_uname = f(q("SELECT * FROM tbl_login WHERE l_read='$l_read'"));
                                if (!empty($chk_uname['l_read'])) {
                                    $l_read = $l_read . rand(10, 99);
                                }
                                $l_uid = md5($l_read);
                                $l_pass = md5('123456');
                                mysqli_query($con, "INSERT INTO tbl_login SET l_name='$linemanName', l_read='$l_read', l_uid='$l_uid', l_pass='$l_pass', l_type='2', l_act='1'");
                                $line_ids = mysqli_insert_id($con);
                            } else {
                                $line_ids = $getlineman['l_id'];
                            }
                        }

                        // Package Name: "if Package name is not present add the package to the package master"
                        $packid = 0;
                        $packpr = 0.00;
                        if ($packageName !== '') {
                            $getpack = f(q("SELECT * FROM tbl_pack WHERE pname='$packageName'"));
                            if (empty($getpack['pname'])) {
                                $packpr = parse_siti_pack_price($packageName);
                                $base_rate = round($packpr / 1.18, 2);
                                mysqli_query($con, "INSERT INTO tbl_pack SET pname='$packageName', details='$packageName', base_rate='$base_rate', prc='$packpr', ptype='2', pmso='$mid', pnochannel='0', pdt=CURDATE()");
                                $packid = mysqli_insert_id($con);
                            } else {
                                $packid = $getpack['pid'];
                                $packpr = floatval($getpack['prc']);
                            }
                        }

                        // Extra properties
                        $phone = '0';
                        $mnth = date('M');
                        $yrs = date('Y');
                        $actv = (strtolower($subStatus) === 'active') ? 1 : 0;
                        $prevdue = '0.00';
                        $ncf = 0;
                        $packpr_id = 0;
                        $sdhd = 'HD';
                        $msoacno = $custid;
                        $landmark = '';
                        $father_name = '';
                        $note = '';
                        $safno = '';
                        $discounts = 0;
                        $dob = '2020-01-01';
                        $gender = '';
                        $houseno = '';
                        $email = '';
                        $compid = isset($_SESSION['compids']) && $_SESSION['compids'] !== '' ? intval($_SESSION['compids']) : 53;

                        $startDt = date('Y-m-d');
                        $endDt = date('Y-m-d', strtotime('+30 days'));

                        $checkc = f(q("SELECT * FROM adduser WHERE custid='$custid'"));

                        if (empty($checkc['name'])) {
                            $insertSql = "INSERT INTO `adduser` SET
                                `name`='$custName',
                                `custid`='$custid',
                                `off_id`='$sNo',
                                `address`='$address',
                                `phone`='$phone',
                                `phone_off`='$phone',
                                `area_id`='$arid',
                                `area_name`='$areaName',
                                `price`='$packpr',
                                `vc`='$vc',
                                `stb`='$stb',
                                `pay_ch`='$packageName',
                                `is_cable`='1',
                                `mnth`='$mnth',
                                `yrs`='$yrs',
                                `pexpiry`='$endDt',
                                `actv`='$actv',
                                `lpaydt`='$startDt',
                                `lpayamt`='$packpr',
                                `mso`='$mid',
                                `sdhd`='$sdhd',
                                `boxdate`='$startDt',
                                `msoacno`='$msoacno',
                                `prevdue`='$prevdue',
                                `ncf`='$ncf',
                                `packid`='$packid',
                                `packname`='$packageName',
                                `packpr`='$packpr',
                                `packpr_id`='$packpr_id',
                                `com_id`='$compid',
                                `line_ids`='$line_ids',
                                `landmark`='$landmark',
                                `discounts`='$discounts',
                                `safno`='$safno',
                                `father_name`='$father_name',
                                `note`='$note',
                                `dob`='$dob',
                                `gender`='$gender',
                                `houseno`='$houseno',
                                `email`='$email'";

                            $result = mysqli_query($con, $insertSql);
                            if ($result) {
                                $importedCount++;
                            } else {
                                $skippedRows[] = [
                                    'row' => $rowNumber,
                                    'data' => $Row,
                                    'reason' => 'Insert failed: ' . mysqli_error($con)
                                ];
                                $skippedCount++;
                            }
                        } else {
                            $updateSql = "UPDATE adduser SET
                                `name`='$custName',
                                `off_id`='$sNo',
                                `address`='$address',
                                `phone`='$phone',
                                `phone_off`='$phone',
                                `area_id`='$arid',
                                `area_name`='$areaName',
                                `price`='$packpr',
                                `vc`='$vc',
                                `stb`='$stb',
                                `pay_ch`='$packageName',
                                `mnth`='$mnth',
                                `yrs`='$yrs',
                                `pexpiry`='$endDt',
                                `actv`='$actv',
                                `lpaydt`='$startDt',
                                `lpayamt`='$packpr',
                                `mso`='$mid',
                                `sdhd`='$sdhd',
                                `boxdate`='$startDt',
                                `msoacno`='$msoacno',
                                `com_id`='$compid',
                                `line_ids`='$line_ids',
                                `landmark`='$landmark',
                                `discounts`='$discounts',
                                `safno`='$safno',
                                `father_name`='$father_name',
                                `note`='$note',
                                `email`='$email'
                                WHERE custid='$custid'";

                            $result = mysqli_query($con, $updateSql);
                            if ($result) {
                                $updatedCount++;
                            } else {
                                $skippedRows[] = [
                                    'row' => $rowNumber,
                                    'data' => $Row,
                                    'reason' => 'Update failed: ' . mysqli_error($con)
                                ];
                                $skippedCount++;
                            }
                        }
                    }
                }

                $processed = true;
            } catch (Exception $ex) {
                $type = "error";
                $message = "Unable to read Excel file: " . $ex->getMessage();
            } finally {
                if (file_exists($targetPath)) {
                    unlink($targetPath);
                }
            }

            if ($processed && $type !== "error") {
                $totalEffective = $importedCount + $updatedCount;
                $type = $totalEffective > 0 ? "success" : "error";
                $message = "<strong>Import finished.</strong><br>";
                $message .= "Inserted: $importedCount, Updated: $updatedCount, Skipped: $skippedCount.";
                if ($totalEffective === 0) {
                    $message .= "<br>No new/updated subscribers found in the uploaded file.";
                }
            }
        }
    }
}
?>

<!DOCTYPE html>
<html>
    <head>
        <title>SITI Networks Subscriber Import</title>
        <style>
            body {
                font-family: Arial, sans-serif;
                line-height: 1.6;
                margin: 0;
                padding: 20px;
                background-color: #f5f5f5;
            }
            .card {
                max-width: 1100px;
                margin: 0 auto;
                background: white;
                padding: 30px;
                border-radius: 8px;
                box-shadow: 0 0 12px rgba(0,0,0,0.1);
            }
            h1 {
                margin-top: 0;
            }
            .upload {
                background: #f9f9f9;
                padding: 20px;
                border-radius: 6px;
                border: 1px solid #ddd;
                margin-bottom: 20px;
            }
            .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;
            }
            .alert {
                padding: 15px;
                border-radius: 4px;
                margin-top: 20px;
                border: 1px solid transparent;
            }
            .success {
                background: #dff0d8;
                border-color: #d6e9c6;
                color: #3c763d;
            }
            .error {
                background: #f2dede;
                border-color: #ebccd1;
                color: #a94442;
            }
            .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: #f2f2f2;
            }
            .skipped-table tr:nth-child(even) {
                background: #fafafa;
            }
            .instructions {
                margin-top: 30px;
                padding: 20px;
                border-radius: 6px;
                background: #eef5ff;
                border: 1px solid #c7d8ff;
            }
            .instructions ul {
                margin-top: 10px;
                padding-left: 18px;
            }
            .note {
                font-size: 0.9em;
                color: #555;
                margin-top: 10px;
            }
            .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="card">
            <h1>Import SITI Networks Subscribers</h1>

            <div class="upload">
                <form action="" method="post" enctype="multipart/form-data">
                    <label for="file"><strong>Select Excel file (SITI Networks format)</strong></label><br>
                    <input type="file" name="file" id="file" accept=".xls,.xlsx" required>
                    <button type="submit" name="import" class="btn-submit">Import Subscribers</button>
                </form>
            </div>

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

            <?php if (!empty($skippedRows)): ?>
                <div>
                    <h2>Skipped Rows</h2>
                    <table class="skipped-table">
                        <thead>
                            <tr>
                                <th>Row Number</th>
                                <th>SMS 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]) ? htmlspecialchars($skipped['data'][1]) : 'N/A'; ?></td>
                                    <td><?php echo isset($skipped['data'][2]) ? htmlspecialchars($skipped['data'][2]) : 'N/A'; ?></td>
                                    <td><?php echo htmlspecialchars($skipped['reason']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            <?php endif; ?>

            <div class="instructions">
                <strong>Expected Excel format columns</strong>
                <ul>
                    <li>Column A: <strong>S No</strong></li>
                    <li>Column B: <strong>SMS Id</strong> (Imported as <code>custid</code> and <code>msoacno</code>)</li>
                    <li>Column C: <strong>Subscriber Name</strong></li>
                    <li>Column D: <strong>Subscriber Address</strong> (First part before comma used as area)</li>
                    <li>Column E: <strong>VC No</strong></li>
                    <li>Column F: <strong>STB No</strong></li>
                    <li>Column I: <strong>Package Name</strong> (Added to package master if new, price parsed from name)</li>
                    <li>Column K: <strong>Lineman Name</strong> (Added to lineman database if new)</li>
                    <li>Column L: <strong>Subscriber Status</strong> (E.g. Active/Deactive)</li>
                </ul>
                <p class="note">This import matches subscribers by their SMS ID (mapped as account number). Existing matches will be updated, and others will be inserted.</p>
            </div>

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