<?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_date_value($value, $fallback)
{
    $value = clean_excel_cell($value);
    $timestamp = strtotime($value);
    if ($timestamp === false || $timestamp < 0) {
        return $fallback;
    }

    return date('Y-m-d', $timestamp);
}

function parse_pack_price($text)
{
    $text = clean_excel_cell($text);
    if ($text === '') {
        return 0.00;
    }

    if (preg_match('/(\d+(?:[.,]\d+)?)/', $text, $matches)) {
        return floatval(str_replace(',', '.', $matches[1]));
    }

    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('cust_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;
                        }

                        $rawId = clean_excel_cell($Row[0] ?? '');
                        $customerName = clean_excel_cell($Row[1] ?? '');
                        $acNo = clean_excel_cell($Row[2] ?? '');
                        $area = clean_excel_cell($Row[3] ?? 'UNKNOWN');
                        $lineman = clean_excel_cell($Row[4] ?? '');
                        $phone = clean_excel_cell($Row[5] ?? '0');
                        $msoNo = clean_excel_cell($Row[6] ?? '');
                        $fullAddress = clean_excel_cell($Row[7] ?? '');
                        $companyName = clean_excel_cell($Row[8] ?? 'CABLE BILL');
                        $companyAdd = clean_excel_cell($Row[9] ?? '');
                        $companyMob = clean_excel_cell($Row[10] ?? '');
                        $companyStax = clean_excel_cell($Row[11] ?? '');
                        $specialMonth = clean_excel_cell($Row[12] ?? '');
                        $msoName = clean_excel_cell($Row[13] ?? 'SITI');
                        $vc = clean_excel_cell($Row[14] ?? '0');
                        $stb = clean_excel_cell($Row[15] ?? '0');
                        $payChannel = clean_excel_cell($Row[16] ?? '');
                        $startDt = parse_date_value($Row[17] ?? '', date('Y-m-d'));
                        $endDt = parse_date_value($Row[18] ?? '', date('Y-m-d', strtotime('+30 days')));

                        $custid = $acNo !== '' ? $acNo : ($rawId !== '' ? $rawId : "CC" . $rowNumber);

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

                        // Normalize phone numbers
                        $phone = preg_replace('/[^0-9]/', '', $phone);
                        if ($phone === '') {
                            $phone = '0';
                        }

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

                        $packname = $payChannel !== '' ? $payChannel : 'EXCEL IMPORT';
                        $packpr = parse_pack_price($payChannel);

                        $mnth = $specialMonth !== '' ? $specialMonth : date('M');
                        $yrs = date('Y');
                        $actv = 1;
                        $prevdue = '0.00';
                        $ncf = 0;
                        $packid = 0;
                        $packpr_id = 0;
                        $line_ids = 10;
                        $sdhd = 'HD';
                        $msoacno = $custid;
                        $landmark = '';
                        $father_name = '';
                        $note = '';
                        $safno = '';
                        $discounts = 0;
                        $dob = '2020-01-01';
                        $gender = '';
                        $houseno = '';
                        $email = '';

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

                        // MSO
                        $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'];
                        }

                        // Company
                        $getcomp = f(q("SELECT * FROM tbl_company WHERE c_name='$companyName'"));
                        if (empty($getcomp['c_name'])) {
                            mysqli_query($con, "INSERT INTO tbl_company SET c_name='$companyName', c_phone='$companyMob', c_mobile='$companyMob', c_email='na', c_emailalt='na', c_address='$companyAdd', c_gstin='$companyStax', isgst='1'");
                            $compid = mysqli_insert_id($con);
                        } else {
                            $compid = $getcomp['c_id'];
                        }

                        $compid=53;

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

                        if (empty($checkc['name'])) {
                            $insertSql = "INSERT INTO `adduser` SET
                                `name`='$customerName',
                                `custid`='$custid',
                                `off_id`='$rawId',
                                `address`='$fullAddress',
                                `phone`='$phone',
                                `phone_off`='$phone',
                                `area_id`='$arid',
                                `area_name`='$area',
                                `price`='$packpr',
                                `vc`='$vc',
                                `stb`='$stb',
                                `pay_ch`='$payChannel',
                                `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`='$packname',
                                `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`='$customerName',
                                `off_id`='$rawId',
                                `address`='$fullAddress',
                                `phone`='$phone',
                                `phone_off`='$phone',
                                `area_id`='$arid',
                                `area_name`='$area',
                                `price`='$packpr',
                                `vc`='$vc',
                                `stb`='$stb',
                                `pay_ch`='$payChannel',
                                `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>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 Subscriber Master</h1>

            <div class="upload">
                <form action="" 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 Subscribers</button>
                    <a href="importfiles/3_CUSTOMER (1).xls" class="btn-submit" style="background:#0d6efd; border: #0a58ca 1px solid; margin-left:10px;" 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)): ?>
                <div>
                    <h2>Skipped Rows</h2>
                    <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'][2]) ? $skipped['data'][2] : 'N/A'; ?></td>
                                    <td><?php echo isset($skipped['data'][1]) ? $skipped['data'][1] : 'N/A'; ?></td>
                                    <td><?php echo $skipped['reason']; ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            <?php endif; ?>

            <div class="instructions">
                <strong>Upload format</strong>
                <ul>
                    <li>File: <code>3_CUSTOMER (1).xls</code>; structure used to populate <code>adduser</code> as defined in <code>adduser.sql</code>.</li>
                    <li>Column A: <strong>cust_id</strong> (fallback for the customer ID).</li>
                    <li>Column B: <strong>cust_name</strong> (name of the subscriber).</li>
                    <li>Column C: <strong>AC NO</strong> (preferred <code>custid</code> value).</li>
                    <li>Column D: <strong>area</strong>; if missing, a new area is created with the literal name you provide.</li>
                    <li>Columns H–K supply company metadata (name, address, mobile, GST). Empty values default to placeholders.</li>
                    <li>Column N: <strong>mso_name</strong> (MSO is created if missing).</li>
                    <li>Column O: <strong>VC</strong>; Column P: <strong>STB</strong>; Column Q: <strong>pay_channel</strong>.</li>
                    <li>Columns R+S: <strong>start_dt</strong> and <strong>end_dt</strong> (stored as dates; missing values default to today and today+30d).</li>
                </ul>
                <p class="note">Existing customer rows are updated. Skipped rows (missing name/ID or DB errors) are listed above.</p>
            </div>

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