<?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 Area 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;
                $importedAreas = array();
                
                for($i = 0; $i < $sheetCount; $i++) {
                    $Reader->ChangeSheet($i);
                    
                    $firstRow = true;
                    
                    foreach ($Reader as $Row) {
                        // Skip header row
                        if($firstRow) {
                            $firstRow = false;
                            continue;
                        }
                        
                        $areaName = "";
                        
                        if(isset($Row[1]) && trim($Row[1]) != '') {
                            $areaName = mysqli_real_escape_string($con, trim($Row[1]));
                        } elseif(isset($Row[0]) && trim($Row[0]) != '') {
                            // Fallback to first column if second column is empty
                            $areaName = mysqli_real_escape_string($con, trim($Row[0]));
                        }
                        
                        if($areaName != '') {
                            // Check if area already exists
                            $checkArea = f(q("SELECT * FROM tbl_area WHERE arname='$areaName'"));
                            
                            if($checkArea['arid'] == '') {
                                // Insert new area
                                $result = mysqli_query($con, "INSERT INTO tbl_area SET arname='$areaName'");
                                
                                if($result) {
                                    $importedCount++;
                                    $importedAreas[] = $areaName;
                                } else {
                                    $errorCount++;
                                }
                            } else {
                                $duplicateCount++;
                            }
                        }
                    }
                }
                
                // Delete uploaded file after processing
                unlink($targetPath);
                
                // Show success message
                $message = "Import completed successfully!<br>";
                $message .= "<strong>$importedCount</strong> areas imported<br>";
                
                if($duplicateCount > 0) {
                    $message .= "<strong>$duplicateCount</strong> duplicates skipped<br>";
                }
                
                if($errorCount > 0) {
                    $message .= "<strong>$errorCount</strong> errors occurred<br>";
                }
                
                ?>
                <script type="text/javascript">
                    Swal.fire({
                        icon: 'success',
                        title: 'Import Successful',
                        html: '<?php echo $message; ?>'
                    }).then(function() {
                        window.location.href = 'import-area-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 Area 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="areamaster.php">Area 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="areamaster.php" class="btn btn-primary">
                                <i class="fa fa-arrow-left"></i> Back to Area 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>Excel file should have area names in the <strong>second column (Column B)</strong></li>
                                    <li>First row will be treated as header and skipped</li>
                                    <li>Duplicate area names will be automatically skipped</li>
                                    <li>Supported formats: .xls, .xlsx</li>
                                </ul>
                            </div>
                            
                            <form action="import-area-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 Areas
                                    </button>
                                    <a href="1_area_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 Areas</h5>
                            <table class="table table-bordered table-striped">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Area Name</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $areas = q("SELECT * FROM tbl_area ORDER BY arname ASC");
                                    while($area = mysqli_fetch_assoc($areas)) {
                                    ?>
                                    <tr>
                                        <td><?php echo $area['arid']; ?></td>
                                        <td><?php echo $area['arname']; ?></td>
                                    </tr>
                                    <?php
                                    }
                                    ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

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