<?php
session_start();
include "../config.php";

// manager only
if (!isset($_SESSION['email']) || $_SESSION['role'] != 'manager') {
    header("Location: ../login.php");
    exit();
}

$manager = $_SESSION['email'];
$success = $error = "";

// fetch projects & items for selects
$projects = $conn->query("SELECT id, name FROM projects ORDER BY name ASC");
$items    = $conn->query("SELECT id, item_name FROM items ORDER BY item_name ASC");

// levels (you had levels before — keep them; change if you want to remove)
$levels = [
    "foundation" => "Foundation",
    "walls" => "Walls",
    "roofing" => "Roofing",
    "floorfinishes" => "Floor Finishes",
    "windows_doors" => "Windows & Doors"
];

/**
 * Load BOQ with its items and sub-items
 */
function loadBoq($conn, $id) {
    $id = (int)$id;
    $boqQ = $conn->prepare("SELECT * FROM bill_of_quantities WHERE id = ?");
    $boqQ->bind_param("i", $id);
    $boqQ->execute();
    $boq = $boqQ->get_result()->fetch_assoc();
    if (!$boq) return null;

    // Load items
    $items = [];
    $stmt = $conn->prepare("SELECT bi.*, i.item_name AS item_lookup_name FROM boq_items bi LEFT JOIN items i ON bi.item_id = i.id WHERE bi.boq_id = ? ORDER BY bi.id ASC");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $res = $stmt->get_result();
    while ($r = $res->fetch_assoc()) {
        // load subitems
        $r['sub_items'] = [];
        $stmt2 = $conn->prepare("SELECT id, sub_item_name FROM boq_sub_items WHERE boq_item_id = ? ORDER BY id ASC");
        $stmt2->bind_param("i", $r['id']);
        $stmt2->execute();
        $res2 = $stmt2->get_result();
        while ($s = $res2->fetch_assoc()) $r['sub_items'][] = $s;
        $items[] = $r;
    }
    $boq['items'] = $items;
    return $boq;
}

// If editing existing BOQ (id provided)
$editing = false;
$boq = null;
if (isset($_GET['id'])) {
    $boq = loadBoq($conn, intval($_GET['id']));
    if ($boq) $editing = true;
    else $error = "BOQ not found.";
}

// Handle POST save (create new or update existing)
if ($_SERVER['REQUEST_METHOD'] === 'POST') {

    $boq_name   = trim($_POST['boq_name'] ?? '');
    $project_id = intval($_POST['project_id'] ?? 0);
    $dbl_number = trim($_POST['dbl_number'] ?? '');
    $item_ids   = $_POST['item_id'] ?? [];         // may be empty strings
    $item_names = $_POST['item_name'] ?? [];       // fallback manual names if used
    $quantities = $_POST['quantity'] ?? [];
    $unit_costs = $_POST['unit_cost'] ?? [];
    $levels_selected = $_POST['level'] ?? [];
    $sub_items  = $_POST['sub_item'] ?? []; // sub_item[name][rowIndex][]

    // Basic validation
    if ($boq_name === '') {
        $error = "BOQ name is required.";
    } elseif ($project_id <= 0) {
        $error = "Please select a project.";
    } else {
        // If editing existing, update header; else insert header
        if (!empty($_POST['boq_id'])) {
            $boq_id = (int)$_POST['boq_id'];
            $stmt = $conn->prepare("UPDATE bill_of_quantities SET project_id=?, boq_name=?, dbl_number=?, created_by=? WHERE id=?");
            $stmt->bind_param("isssi", $project_id, $boq_name, $dbl_number, $manager, $boq_id);
            $ok = $stmt->execute();
        } else {
            $stmt = $conn->prepare("INSERT INTO bill_of_quantities (project_id, boq_name, dbl_number, created_by, created_at) VALUES (?, ?, ?, ?, NOW())");
            $stmt->bind_param("isss", $project_id, $boq_name, $dbl_number, $manager);
            $ok = $stmt->execute();
            $boq_id = $stmt->insert_id;
        }

        if (!$ok) {
            $error = "Failed saving BOQ header: " . $conn->error;
        } else {
            // Transaction: delete existing items/subitems for this BOQ then insert posted rows
            $conn->begin_transaction();
            try {
                // delete existing sub-items -> items if editing
                $delSub = $conn->prepare("DELETE s FROM boq_sub_items s JOIN boq_items bi ON s.boq_item_id = bi.id WHERE bi.boq_id = ?");
                $delSub->bind_param("i", $boq_id);
                $delSub->execute();

                $delItems = $conn->prepare("DELETE FROM boq_items WHERE boq_id = ?");
                $delItems->bind_param("i", $boq_id);
                $delItems->execute();

                // Insert rows posted (respect array lengths)
                $rows = max(count($item_ids), count($item_names), count($quantities), count($unit_costs));
                for ($i = 0; $i < $rows; $i++) {
                    // prefer item_id if numeric >0, else use posted item_name (manual)
                    $item_id = isset($item_ids[$i]) && intval($item_ids[$i])>0 ? intval($item_ids[$i]) : null;
                    $manual_name = trim($item_names[$i] ?? '');
                    $qty = isset($quantities[$i]) ? floatval($quantities[$i]) : 0;
                    $cost = isset($unit_costs[$i]) ? floatval($unit_costs[$i]) : 0;
                    $level = $levels_selected[$i] ?? '';

                    // determine whether row is valid (require either item_id or manual_name)
                    if ((empty($item_id) && $manual_name === '') || ($qty <= 0 && $cost <= 0 && $manual_name === '' && empty($item_id))) {
                        // skip completely empty rows
                        continue;
                    }

                    // Insert item record. Save item_id as NULL if manual name used; we'll store manual_name in 'manual_item_name' column
                    // (assumes column 'manual_item_name' exists on boq_items; if not, you can add it)
                    $stmtItem = $conn->prepare("INSERT INTO boq_items (boq_id, item_id, manual_item_name, quantity, unit_cost, level) VALUES (?, ?, ?, ?, ?, ?)");
                    // bind types: i (boq_id), i (item_id or null), s (manual name), d (qty), d (cost), s (level)
                    // MySQLi requires null substitution; pass null for item_id when manual_name used
                    if ($item_id === null) {
                        $nullItemId = null;
                        $stmtItem->bind_param("iisdss", $boq_id, $nullItemId, $manual_name, $qty, $cost, $level);
                    } else {
                        $stmtItem->bind_param("iisdss", $boq_id, $item_id, $manual_name, $qty, $cost, $level);
                    }
                    // Above bind_param with null for integer field works in mysqli if using variables; to be safe we set item_id var
                    // We'll use a safer approach: set $iid variable (int or NULL) and use mysqli_stmt::bind_param with 'i' but NULL must be passed correctly.
                    // To handle NULL properly, re-prepare with two paths:
                    $stmtItem->close();
                    if ($item_id === null) {
                        $stmtItem = $conn->prepare("INSERT INTO boq_items (boq_id, item_id, manual_item_name, quantity, unit_cost, level) VALUES (?, NULL, ?, ?, ?, ?)");
                        $stmtItem->bind_param("idds", $boq_id, $manual_name, $qty, $cost, $level);
                    } else {
                        $stmtItem = $conn->prepare("INSERT INTO boq_items (boq_id, item_id, manual_item_name, quantity, unit_cost, level) VALUES (?, ?, ?, ?, ?, ?)");
                        $stmtItem->bind_param("iissd s", $boq_id, $item_id, $manual_name, $qty, $cost, $level); // note: we'll not use this path to avoid confusion
                        // but to be precise, use the following correct bind for item_id present:
                        $stmtItem->close();
                        $stmtItem = $conn->prepare("INSERT INTO boq_items (boq_id, item_id, manual_item_name, quantity, unit_cost, level) VALUES (?, ?, ?, ?, ?, ?)");
                        $stmtItem->bind_param("iissds", $boq_id, $item_id, $manual_name, $qty, $cost, $level);
                    }

                    // execute item insert
                    if (!$stmtItem->execute()) {
                        throw new Exception("Failed to insert BOQ item: " . $stmtItem->error);
                    }
                    $boq_item_id = $stmtItem->insert_id;
                    $stmtItem->close();

                    // sub-items (only names required) - support multiple sub-items per item
                    if (!empty($sub_items['name'][$i]) && is_array($sub_items['name'][$i])) {
                        foreach ($sub_items['name'][$i] as $sub_name) {
                            $sub_name = trim($sub_name);
                            if ($sub_name === '') continue;
                            $stmtSub = $conn->prepare("INSERT INTO boq_sub_items (boq_item_id, sub_item_name) VALUES (?, ?)");
                            $stmtSub->bind_param("is", $boq_item_id, $sub_name);
                            if (!$stmtSub->execute()) {
                                throw new Exception("Failed to insert sub-item: " . $stmtSub->error);
                            }
                            $stmtSub->close();
                        }
                    }
                }

                $conn->commit();
                $success = "BOQ saved successfully.";
                // reload BOQ for display
                $boq = loadBoq($conn, $boq_id);
                $editing = true;
            } catch (Exception $e) {
                $conn->rollback();
                $error = "Failed saving BOQ items: " . $e->getMessage();
            }
        }
    }
}
?>
<!doctype html>
<html>
<head>
    <meta charset="utf-8">
    <title><?php echo $editing ? "Edit BOQ" : "Create BOQ"; ?></title>
    <link href="../assets/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body{ background:#f7f8fa; }
        .sub-item-box{ margin-top:6px; padding:6px; background:#fff; border-left:3px solid #007bff; border-radius:4px; }
        table#boqTable input, table#boqTable select { width:100%; }
        .item-total { font-weight:700; }
    </style>
</head>
<body>
<div class="container mt-4 mb-5">
    <h3 class="text-center mb-3"><?php echo $editing ? "Edit BOQ" : "Create Bill of Quantities (BOQ)"; ?></h3>

    <?php if ($success): ?><div class="alert alert-success"><?= htmlspecialchars($success) ?></div><?php endif; ?>
    <?php if ($error): ?><div class="alert alert-danger"><?= htmlspecialchars($error) ?></div><?php endif; ?>

    <div class="card p-3 mb-4 shadow-sm">
        <form method="post" id="boqForm">
            <input type="hidden" name="boq_id" value="<?= $editing ? (int)$boq['id'] : '' ?>">

            <div class="row g-2 mb-2">
                <div class="col-md-4">
                    <label class="form-label">BOQ Name</label>
                    <input type="text" name="boq_name" class="form-control" required value="<?= $editing ? htmlspecialchars($boq['boq_name']) : '' ?>">
                </div>
                <div class="col-md-4">
                    <label class="form-label">DBL Number</label>
                    <input type="text" name="dbl_number" class="form-control" value="<?= $editing ? htmlspecialchars($boq['dbl_number'] ?? '') : '' ?>" placeholder="Donor Budget Line (DBL)">
                </div>
                <div class="col-md-4">
                    <label class="form-label">Project</label>
                    <select name="project_id" class="form-control" required>
                        <option value="">-- select project --</option>
                        <?php
                        $projects->data_seek(0);
                        while ($p = $projects->fetch_assoc()):
                        ?>
                            <option value="<?= $p['id'] ?>" <?= ($editing && $boq['project_id'] == $p['id']) ? 'selected' : '' ?>><?= htmlspecialchars($p['name']) ?></option>
                        <?php endwhile; ?>
                    </select>
                </div>
            </div>

            <hr>

            <table class="table table-bordered" id="boqTable">
                <thead class="table-dark">
                    <tr>
                        <th>Item (select existing or leave blank and type manual name below)</th>
                        <th>Level</th>
                        <th style="width:110px">Qty</th>
                        <th style="width:130px">Unit Cost</th>
                        <th style="width:110px">Item Total</th>
                        <th>Sub Items (options / types only)</th>
                        <th style="width:40px">Remove</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                // render rows: if editing, populate from $boq['items'], else one empty row
                if ($editing && !empty($boq['items'])):
                    foreach ($boq['items'] as $rowIndex => $row):
                        $item_total = (float)$row['quantity'] * (float)$row['unit_cost'];
                    ?>
                    <tr>
                        <td>
                            <select name="item_id[]" class="form-control">
                                <option value="">-- choose existing item --</option>
                                <?php $items->data_seek(0); while ($it = $items->fetch_assoc()): ?>
                                    <option value="<?= $it['id'] ?>" <?= ($it['id'] == $row['item_id']) ? 'selected' : '' ?>><?= htmlspecialchars($it['item_name']) ?></option>
                                <?php endwhile; ?>
                            </select>
                            <input type="text" name="item_name[]" class="form-control mt-1" placeholder="Or type item name" value="<?= htmlspecialchars($row['manual_item_name'] ?? $row['item_lookup_name'] ?? '') ?>">
                        </td>
                        <td>
                            <select name="level[]" class="form-control">
                                <?php foreach ($levels as $k=>$v): ?>
                                    <option value="<?= $k ?>" <?= ($row['level'] == $k) ? 'selected' : '' ?>><?= $v ?></option>
                                <?php endforeach; ?>
                            </select>
                        </td>
                        <td><input type="number" name="quantity[]" class="form-control qty" step="0.01" value="<?= (float)$row['quantity'] ?>"></td>
                        <td><input type="number" name="unit_cost[]" class="form-control cost" step="0.01" value="<?= (float)$row['unit_cost'] ?>"></td>
                        <td class="text-end item-total"><?= number_format($item_total,2) ?></td>
                        <td>
                            <button type="button" class="btn btn-sm btn-outline-primary addSubItem">+ Add Sub</button>
                            <div class="sub-items">
                                <?php if (!empty($row['sub_items'])): foreach ($row['sub_items'] as $si): ?>
                                    <div class="sub-item-box">
                                        <input type="text" name="sub_item[name][<?= $rowIndex ?>][]" class="form-control mb-1" value="<?= htmlspecialchars($si['sub_item_name']) ?>" placeholder="Sub item option (e.g. 32.5 / 42.5)">
                                    </div>
                                <?php endforeach; endif; ?>
                            </div>
                        </td>
                        <td class="text-center"><span class="remove-row text-danger" style="cursor:pointer">&times;</span></td>
                    </tr>
                <?php
                    endforeach;
                else:
                ?>
                    <tr>
                        <td>
                            <select name="item_id[]" class="form-control">
                                <option value="">-- choose existing item --</option>
                                <?php $items->data_seek(0); while ($it = $items->fetch_assoc()): ?>
                                    <option value="<?= $it['id'] ?>"><?= htmlspecialchars($it['item_name']) ?></option>
                                <?php endwhile; ?>
                            </select>
                            <input type="text" name="item_name[]" class="form-control mt-1" placeholder="Or type item name">
                        </td>
                        <td>
                            <select name="level[]" class="form-control">
                                <?php foreach ($levels as $k=>$v): ?><option value="<?= $k ?>"><?= $v ?></option><?php endforeach; ?>
                            </select>
                        </td>
                        <td><input type="number" name="quantity[]" class="form-control qty" step="0.01" value="0"></td>
                        <td><input type="number" name="unit_cost[]" class="form-control cost" step="0.01" value="0"></td>
                        <td class="text-end item-total">0.00</td>
                        <td>
                            <button type="button" class="btn btn-sm btn-outline-primary addSubItem">+ Add Sub</button>
                            <div class="sub-items"></div>
                        </td>
                        <td class="text-center"><span class="remove-row text-danger" style="cursor:pointer">&times;</span></td>
                    </tr>
                <?php endif; ?>
                </tbody>
            </table>

            <div class="mb-3">
                <button type="button" id="addRow" class="btn btn-secondary btn-sm">+ Add Row</button>
            </div>

            <div class="mb-3 text-end">
                <h5>BOQ Grand Total: <span id="boqTotal">0.00</span></h5>
            </div>

            <div>
                <button type="submit" class="btn btn-primary w-100"><?= $editing ? "Update BOQ" : "Create BOQ" ?></button>
            </div>
        </form>
    </div>

    <?php if ($editing): ?>
        <div class="mb-2">
            <a href="view_boq.php?id=<?= (int)$boq['id'] ?>" class="btn btn-outline-secondary">View / Print BOQ</a>
            <a href="delete_boq.php?id=<?= (int)$boq['id'] ?>" class="btn btn-outline-danger" onclick="return confirm('Delete this BOQ and all its items?');">Delete BOQ</a>
        </div>
    <?php endif; ?>

    <!-- Optionally list other BOQs created by this manager -->
    <div class="card p-3 shadow-sm mt-4">
        <h5>Your BOQs</h5>
        <?php
        $list = $conn->prepare("SELECT b.id, b.boq_name, b.dbl_number, p.name as project_name, b.created_at FROM bill_of_quantities b JOIN projects p ON b.project_id = p.id WHERE b.created_by = ? ORDER BY b.created_at DESC");
        $list->bind_param("s", $manager);
        $list->execute();
        $resList = $list->get_result();
        ?>
        <table class="table table-sm">
            <thead class="table-dark"><tr><th>#</th><th>Name</th><th>DBL</th><th>Project</th><th>Created</th><th>Action</th></tr></thead>
            <tbody>
            <?php $i=1; while ($r = $resList->fetch_assoc()): ?>
                <tr>
                    <td><?= $i++ ?></td>
                    <td><?= htmlspecialchars($r['boq_name']) ?></td>
                    <td><?= htmlspecialchars($r['dbl_number'] ?? '-') ?></td>
                    <td><?= htmlspecialchars($r['project_name']) ?></td>
                    <td><?= htmlspecialchars($r['created_at']) ?></td>
                    <td>
                        <a href="edit_boq.php?id=<?= (int)$r['id'] ?>" class="btn btn-sm btn-warning">Edit</a>
                        <a href="view_boq.php?id=<?= (int)$r['id'] ?>" class="btn btn-sm btn-secondary">View</a>
                        <a href="delete_boq.php?id=<?= (int)$r['id'] ?>" class="btn btn-sm btn-danger" onclick="return confirm('Delete BOQ?');">Delete</a>
                    </td>
                </tr>
            <?php endwhile; ?>
            </tbody>
        </table>
    </div>
</div>

<script>
// utilities
function formatNumber(n){ return Number(n).toLocaleString(undefined, {minimumFractionDigits:2, maximumFractionDigits:2}); }

function recalcRow(tr){
    const qty = parseFloat(tr.querySelector('.qty').value || 0);
    const cost = parseFloat(tr.querySelector('.cost').value || 0);
    const total = qty * cost;
    tr.querySelector('.item-total').textContent = formatNumber(total);
    return total;
}

function recalcAll(){
    let total = 0;
    document.querySelectorAll('#boqTable tbody tr').forEach(tr=>{
        total += recalcRow(tr);
    });
    document.getElementById('boqTotal').textContent = formatNumber(total);
}

// initial calc
recalcAll();

// Add row
document.getElementById('addRow').addEventListener('click', function(){
    const table = document.querySelector('#boqTable tbody');
    const newRow = table.querySelector('tr').cloneNode(true);
    // clear inputs
    newRow.querySelectorAll('input').forEach(i=>{
        if (i.type === 'number') i.value = 0;
        else i.value = '';
    });
    newRow.querySelectorAll('select').forEach(s=> s.selectedIndex = 0);
    newRow.querySelector('.sub-items').innerHTML = '';
    newRow.querySelector('.item-total').textContent = '0.00';
    table.appendChild(newRow);
    recalcAll();
});

// remove row
document.addEventListener('click', function(e){
    if (e.target.classList.contains('remove-row')){
        const tbody = document.querySelector('#boqTable tbody');
        if (tbody.querySelectorAll('tr').length > 1){
            e.target.closest('tr').remove();
            recalcAll();
        } else {
            alert('At least one row is required.');
        }
    }
});

// add sub item (only name)
document.addEventListener('click', function(e){
    if (e.target.classList.contains('addSubItem')){
        const parentCell = e.target.closest('td').querySelector('.sub-items');
        const tableRows = [...document.querySelectorAll('#boqTable tbody tr')];
        const rowIndex = tableRows.indexOf(e.target.closest('tr'));
        const html = `<div class="sub-item-box">
            <input type="text" name="sub_item[name][${rowIndex}][]" class="form-control mb-1" placeholder="Sub item option (e.g. 32.5 / 42.5)">
        </div>`;
        parentCell.insertAdjacentHTML('beforeend', html);
    }
});

// recalc on change
document.addEventListener('input', function(e){
    if (e.target.classList.contains('qty') || e.target.classList.contains('cost')){
        recalcAll();
    }
});
</script>
</body>
</html>