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

include "top.php";
?>
<!DOCTYPE html>
<html>
<head>
    <?php include('include/head.php'); ?>
    <title>Import Channel/Package Master</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');

    $action = $_REQUEST['action'];
    
    if($action == 'import') {
        if($psave == 1) {
            
            $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());
                
                $importedCount = 0;
                $duplicateCount = 0;
                $errorCount = 0;
                $updateCount = 0;
                
                for($i = 0; $i < $sheetCount; $i++) {
                    $Reader->ChangeSheet($i);
                    
                    $firstRow = true;
                    
                    foreach ($Reader as $Row) {
                        // Skip header row
                        if($firstRow) {
                            $firstRow = false;
                            continue;
                        }
                        
                        $packageName = "";
                        $details = "";
                        $baseRate = 0;
                        $ptype = 4; // Default package type
                        $pmso = 10; // Default MSO
                        $pnochannel = 0;
                        
                        // Column A (1) - Package Name
                        if(isset($Row[1]) && trim($Row[1]) != '') {
                            $packageName = mysqli_real_escape_string($con, trim($Row[1]));
                        }
                        
                        // Column B (1) - Details/Description
                        if(isset($Row[1]) && trim($Row[1]) != '') {
                            $details = mysqli_real_escape_string($con, trim($Row[1]));
                        } else {
                            $details = $packageName; // Use package name if details empty
                        }
                        
                        // Column C (2) - Base Price (without GST)
                        if(isset($Row[2]) && is_numeric($Row[2])) {
                            $baseRate = floatval(trim($Row[2]));
                        }
                        
                        if($packageName != '' && $baseRate > 0) {
                            // Calculate price with 18% GST
                            // Formula: Final Price = Base Rate + 18% GST
                            $gstAmount = $baseRate * 0.18;
                            $priceWithGst = $baseRate + $gstAmount;
                            
                            // Check if package already exists
                            $checkPackage = f(q("SELECT * FROM tbl_pack WHERE pname='$packageName'"));
                            
                            if($checkPackage['pid'] != '') {
                                // Update existing package
                                $result = mysqli_query($con, "UPDATE tbl_pack SET 
                                    pname='$packageName',
                                    details='$details',
                                    base_rate='$baseRate',
                                    prc='$priceWithGst',
                                    ptype='$ptype',
                                    pmso='$pmso',
                                    pnochannel='$pnochannel',
                                    pdt=CURDATE()
                                    WHERE pid='" . $checkPackage['pid'] . "'");
                                
                                if($result) {
                                    $updateCount++;
                                } else {
                                    $errorCount++;
                                }
                            } else {
                                // Insert new package
                                $result = mysqli_query($con, "INSERT INTO tbl_pack SET 
                                    pname='$packageName',
                                    details='$details',
                                    base_rate='$baseRate',
                                    prc='$priceWithGst',
                                    ptype='$ptype',
                                    pmso='$pmso',
                                    pnochannel='$pnochannel',
                                    pdt=CURDATE()");
                                
                                if($result) {
                                    $importedCount++;
                                } else {
                                    $errorCount++;
                                }
                            }
                        } else {
                            if($packageName == '') {
                                $duplicateCount++; // Count empty names as skipped
                            }
                        }
                    }
                }
                
                // Delete uploaded file after processing
                unlink($targetPath);
                
                // Show success message
                $message = "<strong>Import completed successfully!</strong><br><br>";
                $message .= "<table class='table table-sm'>";
                $message .= "<tr><td><strong>New Packages Imported:</strong></td><td>$importedCount</td></tr>";
                
                if($updateCount > 0) {
                    $message .= "<tr><td><strong>Packages Updated:</strong></td><td>$updateCount</td></tr>";
                }
                
                if($duplicateCount > 0) {
                    $message .= "<tr><td><strong>Rows Skipped (Empty):</strong></td><td>$duplicateCount</td></tr>";
                }
                
                if($errorCount > 0) {
                    $message .= "<tr><td><strong>Errors:</strong></td><td>$errorCount</td></tr>";
                }
                
                $message .= "</table>";
                $message .= "<br><strong>Note:</strong> Price calculation:<br>";
                $message .= "<em>Base Rate + 18% GST = Final Price (prc)</em>";
                
                ?>
                <script type="text/javascript">
                    Swal.fire({
                        icon: 'success',
                        title: 'Import Successful',
                        html: '<?php echo $message; ?>',
                        width: '600px'
                    }).then(function() {
                        window.location.href = 'import-channel-master.php';
                    });
                </script>
                <?php
            } else {
                ?>
                <script type="text/javascript">
                    Swal.fire('Invalid File Type', 'Please upload Excel file only (.xls or .xlsx)', 'error');
                </script>
                <?php
            }
        } else {
            ?>
            <script type="text/javascript">
                Swal.fire('Permission Denied', '', 'error');
            </script>
            <?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 Channel/Package Master</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="package.php">Package Master</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="package.php" class="btn btn-primary">
                                <i class="fa fa-arrow-left"></i> Back to Package Master
                            </a>
                        </div>
                    </div>
                </div>

                <div class="pd-20 bg-white border-radius-4 box-shadow mb-30">
                    <div class="clearfix mb-20">
                        <div class="pull-left">
                            <h5 class="text-blue">Upload Excel File</h5>
                        </div>
                    </div>
                    
                    <div class="row">
                        <div class="col-md-12">
                            <div class="alert alert-info">
                                <strong>Instructions:</strong>
                                <ul style="margin-top: 10px;">
                                    <li><strong>Column A:</strong> Package Name</li>
                                    <li><strong>Column B:</strong> Details/Description (optional, uses package name if empty)</li>
                                    <li><strong>Column C:</strong> Base Price (without GST)</li>
                                    <li>First row will be treated as header and skipped</li>
                                    <li>Existing packages will be updated with new prices</li>
                                    <li>Price Calculation: <code>Base Rate + 18% GST = Final Price</code></li>
                                    <li>Supported formats: .xls, .xlsx</li>
                                </ul>
                            </div>
                            
                            <form action="import-channel-master.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="2_channel_master.xls" class="btn btn-info" download>
                                        <i class="fa fa-download"></i> Download Sample File
                                    </a>
                                </div>
                            </form>
                        </div>
                    </div>
                    
                    <div class="row mt-4">
                        <div class="col-md-12">
                            <h5 class="text-blue">Existing Packages (with Base Rate & GST)</h5>
                            <table class="table table-bordered table-striped table-sm">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Package Name</th>
                                        <th>Base Rate</th>
                                        <th>Price (with 18% GST)</th>
                                        <th>Type</th>
                                        <th>MSO</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $packages = q("SELECT * FROM tbl_pack ORDER BY pname ASC LIMIT 100");
                                    while($pkg = mysqli_fetch_assoc($packages)) {
                                        $baseRate = isset($pkg['base_rate']) ? $pkg['base_rate'] : 0;
                                        $prc = $pkg['prc'];
                                    ?>
                                    <tr>
                                        <td><?php echo $pkg['pid']; ?></td>
                                        <td><?php echo $pkg['pname']; ?></td>
                                        <td><?php echo number_format($baseRate, 2); ?></td>
                                        <td><?php echo number_format($prc, 2); ?></td>
                                        <td><?php echo $pkg['ptype']; ?></td>
                                        <td><?php echo $pkg['pmso']; ?></td>
                                    </tr>
                                    <?php
                                    }
                                    ?>
                                </tbody>
                            </table>
                            <small class="text-muted">Showing first 100 packages. Use Package Master page to view all.</small>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

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