<?php

include "top.php";

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

$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_rate_value($value)
{
    $value = str_replace(',', '.', $value);
    $clean = preg_replace('/[^0-9.\-]/', '', $value);

    return is_numeric($clean) ? floatval($clean) : 0;
}

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

    if (!isset($_FILES['file']) || empty($_FILES['file']['name'])) {
        $type = 'error';
        $message = 'Please choose an Excel file to import.';
    } elseif ($_FILES['file']['error'] !== UPLOAD_ERR_OK) {
        $type = 'error';
        $message = 'Upload failed with error code ' . $_FILES['file']['error'] . '.';
    } else {
        $fileName = basename($_FILES['file']['name']);
        $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
        $fileType = $_FILES['file']['type'];

        if (!in_array($fileExtension, $allowedExtensions, true) && !in_array($fileType, $allowedFileTypes, true)) {
            $type = 'error';
            $message = 'Invalid file type. Please upload an Excel file (.xls or .xlsx).';
        } else {
            $targetName = uniqid('channel_import_', true) . '.' . $fileExtension;
            $targetPath = 'image/' . $targetName;

            if (move_uploaded_file($_FILES['file']['tmp_name'], $targetPath)) {
                $importedCount = $updatedCount = $skippedCount = $errorCount = 0;
                $excelProcessed = false;

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

                    for ($sheetIndex = 0; $sheetIndex < $sheetCount; $sheetIndex++) {
                        $Reader->ChangeSheet($sheetIndex);
                        $currentRow = 0;

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

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

                            $channelName = clean_excel_cell($Row[1] ?? '');
                            $channelType = clean_excel_cell($Row[2] ?? '');
                            $rateValue = clean_excel_cell($Row[3] ?? '');
                            $bouquet = clean_excel_cell($Row[4] ?? '');
                            $channelId = clean_excel_cell($Row[0] ?? '');

                            $channelName = preg_replace('/\s*\[[^\]]+\]\s*$/', '', $channelName);
                            $channelName = trim($channelName);

                            $baseRate = parse_rate_value($rateValue);

                            if ($channelName === '' || $baseRate <= 0) {
                                $skippedCount++;
                                continue;
                            }

                            $detailsParts = [];

                            if ($channelType !== '') {
                                $detailsParts[] = 'Type: ' . $channelType;
                            }

                            if ($bouquet !== '') {
                                $detailsParts[] = 'Bouquet: ' . $bouquet;
                            }

                            if ($channelId !== '') {
                                $detailsParts[] = 'Channel ID: ' . preg_replace('/[^0-9]/', '', $channelId);
                            }

                            $details = $channelName;

                            if (!empty($detailsParts)) {
                                $details .= ' (' . implode(' | ', $detailsParts) . ')';
                            }

                            $normalizedType = strtolower($channelType);

                            switch ($normalizedType) {
                                case 'pay':
                                    $ptype = 4;
                                    break;
                                case 'bouquet':
                                    $ptype = 3;
                                    break;
                                case 'free':
                                    $ptype = 2;
                                    break;
                                default:
                                    $ptype = 4;
                                    break;
                            }

                            $finalPrice = round($baseRate * 1.18, 2);
                            $pmso = 10;
                            $pnochannel = 1;

                            $channelNameEsc = mysqli_real_escape_string($con, $channelName);
                            $detailsEsc = mysqli_real_escape_string($con, $details);

                            $checkPackage = f(q("SELECT pid FROM tbl_pack WHERE pname='$channelNameEsc' LIMIT 1"));

                            if (!empty($checkPackage['pid'])) {
                                $updateSql = "UPDATE tbl_pack SET details='$detailsEsc', base_rate='$baseRate', prc='$finalPrice', ptype='$ptype', pmso='$pmso', pnochannel='$pnochannel', pdt=CURDATE() WHERE pid='" . (int)$checkPackage['pid'] . "'";
                                $result = mysqli_query($con, $updateSql);

                                if ($result) {
                                    $updatedCount++;
                                } else {
                                    $errorCount++;
                                }
                            } else {
                                $insertSql = "INSERT INTO tbl_pack SET pname='$channelNameEsc', details='$detailsEsc', base_rate='$baseRate', prc='$finalPrice', ptype='$ptype', pmso='$pmso', pnochannel='$pnochannel', pdt=CURDATE()";
                                $result = mysqli_query($con, $insertSql);

                                if ($result) {
                                    $importedCount++;
                                } else {
                                    $errorCount++;
                                }
                            }
                        }
                    }

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

                if ($excelProcessed && $type !== 'error') {
                    $summary = '<ul>';
                    $summary .= '<li>New packages added: ' . $importedCount . '</li>';
                    $summary .= '<li>Existing packages updated: ' . $updatedCount . '</li>';
                    $summary .= '<li>Rows skipped (missing name or invalid rate): ' . $skippedCount . '</li>';

                    if ($errorCount > 0) {
                        $summary .= '<li>Database errors: ' . $errorCount . '</li>';
                    }

                    $summary .= '</ul>';

                    if (($importedCount + $updatedCount) > 0) {
                        $type = 'success';
                        $message = '<strong>Import completed.</strong><br>' . $summary;
                    } else {
                        $type = 'error';
                        $message = '<strong>No packages were added or updated.</strong><br>' . $summary;
                        $message .= '<p>Please verify that the Excel file uses valid channel names and numeric rates.</p>';
                    }
                }
            } else {
                $type = 'error';
                $message = 'Unable to move the uploaded file for processing.';
            }
        }
    }
}
?>

<!DOCTYPE html>
<html>

<head>

<style>
    body {
        font-family: Arial, sans-serif;
        max-width: 720px;
        margin: 0 auto;
        padding: 20px 15px;
    }

    h2 {
        margin-top: 0;
    }

    .outer-container {
        background: #F7F7F7;
        border: #e0dfdf 1px solid;
        padding: 30px 25px;
        border-radius: 4px;
    }

    .btn-submit {
        background: #333;
        border: #1d1d1d 1px solid;
        border-radius: 2px;
        color: #f0f0f0;
        cursor: pointer;
        padding: 8px 20px;
        font-size: 0.9em;
        margin-right: 10px;
    }

    .btn-submit.secondary {
        background: #0d6efd;
        border: #0a58ca 1px solid;
    }

    .btn-submit:disabled {
        opacity: 0.5;
        cursor: not-allowed;
    }

    .tutorial-table {
        margin-top: 30px;
        font-size: 0.9em;
        border-collapse: collapse;
        width: 100%;
    }

    .instruction-box {
        margin-top: 30px;
        padding: 20px;
        border-radius: 3px;
        background: #f0f7ff;
        border: 1px solid #d0e7ff;
    }

    .instruction-box ul {
        margin-top: 10px;
        padding-left: 18px;
    }

    .note {
        font-size: 0.85em;
        color: #555;
        margin-top: 15px;
    }

    #response {
        padding: 12px;
        margin-top: 20px;
        border-radius: 2px;
        display: none;
    }

    .success {
        background: #c7efd9;
        border: #bbe2cd 1px solid;
    }

    .error {
        background: #fbcfcf;
        border: #f3c6c7 1px solid;
    }

    div#response.display-block {
        display: block;
    }

    #response ul {
        margin: 5px 0 0 18px;
    }

    #response p {
        margin: 5px 0 0;
    }

    small.external-link {
        font-size: 0.75em;
        color: #333;
    }
</style>
</head>

<body>

    <h2>Import Channel / Package Master</h2>
    <p>Use <code>1_channel_master.xls</code> to seed <code>tbl_pack</code> (<a href="importfiles/tcv_therm.sql" target="_blank">current schema</a>).</p>

    <div class="outer-container">
        <form action="" method="post" name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">
            <div>
                <label><strong>Select Excel file</strong></label>
                <input type="file" name="file" id="file" accept=".xls,.xlsx" required>
            </div>
            <div style="margin-top: 20px;">
                <button type="submit" id="submit" name="import" class="btn-submit">Import Channels</button>
                <a href="importfiles/1_channel_master.xls" class="btn-submit secondary" download="1_channel_master.xls">Download sample</a>
            </div>
        </form>
    </div>

    <div id="response" class="<?php if (!empty($type)) {
        echo $type . ' display-block';
    } ?>">
        <?php if (!empty($message)) {
            echo $message;
            echo '<p><a href="index.php">&lt;&lt; Back to Dashboard</a></p>';
        } ?>
    </div>

    <div class="instruction-box">
        <strong>File format &amp; expectations</strong>
        <ul>
            <li>Column A: Channel ID (used in the details field, can be alphanumeric).</li>
            <li>Column B: Channel Name (required, used as <code>pname</code>).</li>
            <li>Column C: Channel Type (e.g., PAY, Bouquet, Free).</li>
            <li>Column D: Rate in INR; the script stores the base rate and also adds 18% GST for <code>prc</code>.</li>
            <li>Column E: Bouquet name (optional).</li>
            <li>First row is assumed to be the header row and is skipped.</li>
            <li>Existing packages with the same name are updated; others are inserted.</li>
            <li>Supported formats: .xls and .xlsx.</li>
        </ul>
        <p class="note">Imported rows populate <code>tbl_pack</code> as defined in <code>importfiles/tcv_therm.sql</code>. The script also logs skipped rows when the name is missing or the rate is not numeric.</p>
    </div>

</body>

</html>
