<?php
include 'admin_header.php';
require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

ini_set('log_errors', 1);
ini_set('error_log', './debug.log');

date_default_timezone_set('Asia/Kolkata');

/**
 * ============================
 * HANDLE FORM SUBMISSION
 * ============================
 */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_POST['doc_no']) && !empty($_POST['operator_id'])) {

    $doc_nos = $_POST['doc_no'];
    $operator_id = (int) $_POST['operator_id'];

    // ✅ Fetch existing assignments ONCE
    $existing = [];
    $res = $conn->query("SELECT assignment_no FROM assignments");
    while ($row = $res->fetch_assoc()) {
        $existing[$row['assignment_no']] = true;
    }

    $conn->begin_transaction();

    try {

        $stmt = $conn->prepare("
            INSERT INTO assignments 
            (assignment_no, no_of_record, assignment_date, assignment_status_id, user_id, created_at, updated_at)
            VALUES (?, 0, CURDATE(), 1, ?, NOW(), NOW())
        ");

        foreach ($doc_nos as $doc_no) {

            if (isset($existing[$doc_no])) continue;

            $zipFile = "../../../1610709752415-sh.hostgator.in/dataftp/data/a/$doc_no.zip";
            $extractPath = "../docs/$doc_no";

            // ✅ Extract ZIP only if needed
            if (file_exists($zipFile) && (!is_dir($extractPath) || count(glob("$extractPath/*")) === 0)) {
                $zip = new ZipArchive;
                if ($zip->open($zipFile) === TRUE) {
                    $zip->extractTo($extractPath);
                    $zip->close();
                }
            }

            // ✅ Insert assignment
            $stmt->bind_param("si", $doc_no, $operator_id);
            $stmt->execute();
            $assignment_id = $stmt->insert_id;

            // ✅ Detect Excel file
            $xls = "$extractPath/$doc_no.xls";
            $xlsx = "$extractPath/$doc_no.xlsx";

            $inputFile = file_exists($xls) ? $xls : (file_exists($xlsx) ? $xlsx : null);
            if (!$inputFile) continue;

            // ✅ Streaming Excel reader (LOW MEMORY)
            $reader = IOFactory::createReaderForFile($inputFile);
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($inputFile);
            $sheet = $spreadsheet->getActiveSheet();

            $batch = [];
            $batchSize = 500;
            $totalRecords = 0;

            foreach ($sheet->getRowIterator(2) as $row) {

                $cell = $sheet->getCell('A' . $row->getRowIndex());
                $value = trim((string)$cell->getValue());

                if ($value === '') continue;

                $batch[] = "($assignment_id,'" . $conn->real_escape_string($value) . "')";
                $totalRecords++;

                // ✅ Bulk insert in chunks
                if (count($batch) >= $batchSize) {
                    $conn->query("INSERT INTO assignment_details (assignment_id, doc_id) VALUES " . implode(',', $batch));
                    $batch = [];
                }
            }

            // Remaining batch
            if (!empty($batch)) {
                $conn->query("INSERT INTO assignment_details (assignment_id, doc_id) VALUES " . implode(',', $batch));
            }

            // ✅ Update record count
            $update = $conn->prepare("UPDATE assignments SET no_of_record = ? WHERE id = ?");
            $update->bind_param("ii", $totalRecords, $assignment_id);
            $update->execute();
        }

        $conn->commit();

    } catch (Exception $e) {
        $conn->rollback();
        error_log($e->getMessage());
        http_response_code(500);
        exit("Something went wrong");
    }
}

/**
 * ============================
 * FETCH EXISTING ASSIGNMENTS (ONCE)
 * ============================
 */
$assigned = [];
$res = $conn->query("SELECT assignment_no FROM assignments");
while ($row = $res->fetch_assoc()) {
    $assigned[$row['assignment_no']] = true;
}

/**
 * ============================
 * GET FILE LIST
 * ============================
 */
$total = 0;
$fileList = array_reverse(glob('../../../1610709752415-sh.hostgator.in/dataftp/data/a/*.zip'));
?>

<div class="container" style="background: #fff; position:relative; margin-top:42px">
    <div class="assignment-dashboard">
        <form method="POST">
            <div class="row">

                <!-- LEFT SIDE -->
                <div class="col-sm-8 border-right">
                    <h5 class="mt-3 mb-3">
                        NEW FOLDERS 
                        <span class="font-weight-light small text-info total_count">[0]</span>
                        <div class="pull-right">
                            <strong class="badge badge-secondary checked_count">0</strong>
                        </div>
                    </h5>

                    <input type="text" class="mb-3 assignment_new" placeholder="Search: [Folder Index]" id="search" style="width:100%; text-align:center;">

                    <div class="table-responsive">
                        <table id="new_assignment_table" class="table table-striped table-sm table-bordered">
                            <thead>
                                <tr>
                                    <th>Action</th>
                                    <th>Folder Index</th>
                                    <th>Data Count</th>
                                    <th>Upload Date</th>
                                </tr>
                            </thead>
                            <tbody>

                                <?php
                                foreach ($fileList as $filename) {

                                    $file = basename($filename, ".zip");

                                    if (isset($assigned[$file])) continue;

                                    // ✅ Open ZIP only when needed
                                    $za = new ZipArchive();
                                    if ($za->open($filename) === TRUE) {

                                        $count = $za->numFiles - 1;
                                        $za->close();

                                        if ($count > 0) {
                                            $total += $count;
                                ?>
                                            <tr>
                                                <td>
                                                    <input type="checkbox" name="doc_no[]" value="<?= htmlspecialchars($file) ?>" file_count="<?= $count ?>" class="chk_doc_no">
                                                </td>
                                                <td><?= htmlspecialchars($file) ?></td>
                                                <td><?= $count ?></td>
                                                <td><?= date("m/d/Y", filemtime($filename)); ?></td>
                                            </tr>
                                <?php
                                        }
                                    }
                                }
                                ?>

                            </tbody>
                        </table>
                    </div>
                </div>

                <!-- RIGHT SIDE -->
                <div class="col-sm-4">
                    <h5 class="mt-3 mb-3">ASSIGN TO:</h5>

                    <?php
                    $sql = "
                        SELECT u.id, u.name, COALESCE(SUM(a.no_of_record),0) as no_of_pending
                        FROM users u
                        JOIN role_user r ON r.user_id = u.id
                        LEFT JOIN assignments a 
                            ON a.user_id = u.id AND a.assignment_status_id = 1
                        WHERE r.role_id IN (2,3)
                        GROUP BY u.id
                    ";

                    $result = $conn->query($sql);

                    while ($row = $result->fetch_assoc()) {
                    ?>
                        <div class="card mb-3">
                            <div class="card-body">
                                <div class="card-text pull-left">
                                    <img src="../images/user.png" class="rounded-circle border mr-3" width="36">
                                    <strong><?= htmlspecialchars($row["name"]) ?></strong>
                                    <span class="badge badge-info"><?= $row["no_of_pending"] ?></span>
                                </div>
                                <div class="card-link pull-right" style="margin-top: 6px;">
                                    <input type="radio" name="operator_id" value="<?= $row["id"] ?>">
                                </div>
                            </div>
                        </div>
                    <?php } ?>

                    <div class="text-right">
                        <input class="btn btn-primary" type="submit" name="save" value="Assign">
                    </div>
                </div>

            </div>
        </form>
    </div>
</div>

<script>
document.addEventListener("DOMContentLoaded", function () {

    document.querySelector(".total_count").innerText = "[<?= $total ?>]";

    document.querySelectorAll(".chk_doc_no").forEach(function(el) {
        el.addEventListener("change", function() {
            let total = 0;
            document.querySelectorAll("input[name='doc_no[]']:checked").forEach(function(chk) {
                total += parseInt(chk.getAttribute("file_count")) || 0;
            });
            document.querySelector(".checked_count").innerText = total;
        });
    });

});
</script>

<?php $conn->close(); ?>