<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();

$con = new mysqli("localhost", "underwat_uplinepj", "Police2345@1", "underwat_jetinstantTQ");
if ($con->connect_error) die("Connection failed: " . $con->connect_error);
$con->set_charset('utf8mb4'); // safer text handling

// Small helper to replace fetch_all(MYSQLI_ASSOC)
function result_to_array($res) {
    $rows = [];
    if ($res instanceof mysqli_result) {
        while ($row = $res->fetch_assoc()) {
            $rows[] = $row;
        }
        $res->free();
    }
    return $rows;
}

$PID = $_POST['pid'] ?? $_SESSION['pid'] ?? '';
if (isset($_POST['pid'])) $_SESSION['pid'] = $PID;

/*
 NOTE: Your UPDATEs use WHERE PID='$id'. In your form, the hidden id uses the column you pass as $id_key.
 Currently you pass 'PID' (the project ref), so each loop re-updates rows for that project. If your tables
 have a real row primary key like 'id', switch the hidden field + WHERE to use that instead.
*/

if (isset($_POST['update_materials'])) {
    if (!empty($_POST['materials_id'])) {
        foreach ($_POST['materials_id'] as $i => $id) {
            $qty   = $con->real_escape_string($_POST['materials_qty'][$i]);
            $extra = $con->real_escape_string($_POST['materials_extra'][$i]);
            $price = $con->real_escape_string($_POST['materials_price'][$i]);
            $con->query("UPDATE foundation_materials SET COEQuantity='$qty', Extra='$extra', Price='$price' WHERE PID='$id'");
        }
    }
}
if (isset($_POST['update_labour'])) {
    if (!empty($_POST['labour_id'])) {
        foreach ($_POST['labour_id'] as $i => $id) {
            $qty   = $con->real_escape_string($_POST['labour_qty'][$i]);
            $price = $con->real_escape_string($_POST['labour_price'][$i]);
            $con->query("UPDATE foundation_labourer SET COEQuantity='$qty', Price='$price' WHERE PID='$id'");
        }
    }
}
if (isset($_POST['update_equipment'])) {
    if (!empty($_POST['equipment_id'])) {
        foreach ($_POST['equipment_id'] as $i => $id) {
            $qty   = $con->real_escape_string($_POST['equipment_qty'][$i]);
            $price = $con->real_escape_string($_POST['equipment_price'][$i]);
            $con->query("UPDATE foundation_equipment SET Quantity='$qty', Price='$price' WHERE PID='$id'");
        }
    }
}

$project_info_res = $con->query("SELECT * FROM upline_app_project_info WHERE project_ref='$PID'");
$project_info = $project_info_res ? $project_info_res->fetch_assoc() : null;

$materials_res = $con->query("SELECT * FROM foundation_materials WHERE PID='$PID'");
$labour_res    = $con->query("SELECT * FROM foundation_labourer WHERE PID='$PID'");
$equipment_res = $con->query("SELECT * FROM foundation_equipment WHERE PID='$PID'");

$material_data  = result_to_array($materials_res);
$labour_data    = result_to_array($labour_res);
$equipment_data = result_to_array($equipment_res);

?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Foundation Report</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
    <script src="https://cdn.tailwindcss.com"></script>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf-autotable/3.5.28/jspdf.plugin.autotable.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/xlsx.full.min.js"></script>
    <style>
        @media print { .no-print { display: none !important; } }
    </style>
</head>
<body class="bg-gray-100 p-6">
<form method="POST">
<div class="max-w-6xl mx-auto bg-white shadow p-6 rounded-xl" id="reportContent">

    <div class="flex justify-end mb-4">
        <a href="dashboard.php" class="no-print text-blue-600 hover:underline text-sm font-semibold">
            ← Back to Dashboard
        </a>
    </div>

    <h2 class="text-2xl font-bold mb-4">Foundation Report</h2>

    <?php if ($project_info): ?>
        <div class="mb-4" id="projectInfo">
            <p><strong>Project Name:</strong> <?= htmlspecialchars($project_info['project_name']) ?></p>
            <p><strong>Project Type:</strong> <?= htmlspecialchars($project_info['project_type']) ?></p>
            <p><strong>Developer:</strong> <?= htmlspecialchars($project_info['project_developer']) ?></p>
            <p><strong>Location:</strong> <?= htmlspecialchars($project_info['project_address']) ?></p>
            <p><strong>Project REF:</strong> <?= htmlspecialchars($project_info['project_ref']) ?></p>
        </div>
    <?php endif; ?>

    <?php
    // $section is "materials", "labour", or "equipment"
    // We'll add classes so JS can wire up events and recalc cleanly.
    function render_editable_section($section, $data, $desc_key, $qty_key, $extra_key, $price_key, $id_key) {
        $subtotal = 0;
        $has_extra = $extra_key ? '1' : '0';
        $html = "<h3 class='text-xl font-semibold mt-6 mb-2'>".ucfirst($section)."</h3>
                 <table class='w-full text-sm border calc-table' data-section='{$section}' data-has-extra='{$has_extra}'>
                   <thead>
                     <tr class='bg-gray-200'>
                       <th class='border px-2 py-1'>Description</th>
                       <th class='border px-2 py-1'>Quantity</th>";
        if ($extra_key) $html .= "<th class='border px-2 py-1'>Extra</th>";
        $html .= "    <th class='border px-2 py-1'>Price</th>
                       <th class='border px-2 py-1'>Total</th>
                     </tr>
                   </thead>
                   <tbody>";

        foreach ($data as $i => $row) {
            $desc  = isset($row[$desc_key]) ? $row[$desc_key] : '';
            $qty   = (float)($row[$qty_key] ?? 0);
            $extra = $extra_key ? (float)($row[$extra_key] ?? 0) : 0;
            $price = (float)($row[$price_key] ?? 0);
            $total = ($qty + $extra) * $price;
            $subtotal += $total;

            $html .= "<tr>
                <td class='border px-2 py-1'>".htmlspecialchars($desc)."</td>
                <td class='border px-2 py-1'>
                    <input name='{$section}_qty[]' value='".htmlspecialchars((string)$qty)."' class='qty w-full border rounded px-1'/>
                </td>";
            if ($extra_key) {
                $html .= "<td class='border px-2 py-1'>
                            <input name='{$section}_extra[]' value='".htmlspecialchars((string)$extra)."' class='extra w-full border rounded px-1'/>
                          </td>";
            }
            $html .= "<td class='border px-2 py-1'>
                        <input name='{$section}_price[]' value='".htmlspecialchars((string)$price)."' class='price w-full border rounded px-1'/>
                      </td>
                      <td class='border px-2 py-1 text-right row-total'>".number_format($total,2)."</td>
            </tr>";
            $row_id = $row[$id_key] ?? '';
            $html .= "<input type='hidden' name='{$section}_id[]' value='".htmlspecialchars((string)$row_id)."' />";
        }

        $contingency = $subtotal * 0.05;
        $grand = $subtotal + $contingency;
        $html .= "</tbody></table>
                  <div class='text-right mt-2 font-medium section-subtotal'>Subtotal: NGN ".number_format($subtotal, 2)."</div>
                  <div class='text-right font-medium section-contingency'>5% Contingency: NGN ".number_format($contingency, 2)."</div>
                  <div class='text-right font-bold section-total'>Total ($section): NGN ".number_format($grand, 2)."</div>
                  <div class='text-right mt-2'>
                    <button type='submit' name='update_$section' class='bg-yellow-500 text-white px-4 py-1 rounded'>Save ".ucfirst($section)."</button>
                  </div>";
        echo $html;
        return $grand;
    }

    $total_material   = render_editable_section('materials',  $material_data,  'MaterialDescription',  'COEQuantity', 'Extra', 'Price', 'PID');
    $total_labour     = render_editable_section('labour',     $labour_data,    'LabourerDescription',  'COEQuantity', null,    'Price', 'PID');
    $total_equipment  = render_editable_section('equipment',  $equipment_data, 'EquipmentDescription', 'Quantity',    null,    'Price', 'PID');

    $foundation_total = $total_material + $total_labour + $total_equipment;
    ?>
    <div class="mt-6 text-right text-xl font-bold" id="foundation-total">
        FOUNDATION TOTAL: NGN <?= number_format($foundation_total, 2) ?>
    </div>
</div>
</form>

<div class="mt-6 text-right no-print">
    <button onclick="exportToExcel()" class="bg-green-600 text-white px-4 py-2 rounded">Export to Excel</button>
    <button onclick="exportToPDF()"   class="bg-blue-600 text-white px-4 py-2 rounded">Export to PDF</button>
</div>

<script>
function exportToExcel() {
    const tables = document.querySelectorAll("#reportContent table");
    const wb = XLSX.utils.book_new();
    tables.forEach((table, index) => {
        // clone & swap inputs for values so Excel gets numbers, not input elements
        const clone = table.cloneNode(true);
        clone.querySelectorAll("input").forEach(input => {
            const text = document.createTextNode(input.value);
            input.parentNode.replaceChild(text, input);
        });
        const ws = XLSX.utils.table_to_sheet(clone);
        XLSX.utils.book_append_sheet(wb, ws, `Section ${index + 1}`);
    });
    XLSX.writeFile(wb, "Foundation_Report.xlsx");
}

async function exportToPDF() {
    const { jsPDF } = window.jspdf;
    const doc = new jsPDF('p', 'pt', 'a4');
    let y = 40;

    // Project Info (scoped to #projectInfo to avoid grabbing other <p>'s)
    const infoDiv = document.getElementById("projectInfo");
    if (infoDiv) {
        doc.setFontSize(12);
        infoDiv.querySelectorAll("p").forEach(p => {
            doc.text(p.innerText, 40, y);
            y += 15;
        });
        y += 10;
    }

    const tables = document.querySelectorAll("#reportContent table");

    for (const table of tables) {
        const sectionTitle = table.previousElementSibling?.innerText || "Section";
        doc.setFontSize(13);
        doc.text(sectionTitle, 40, y);
        y += 10;

        // Clone table & replace inputs with text
        const clone = table.cloneNode(true);
        clone.querySelectorAll("input").forEach(input => {
            const text = document.createTextNode(input.value);
            input.parentNode.replaceChild(text, input);
        });

        await doc.autoTable({
            html: clone,
            startY: y,
            theme: 'grid',
            headStyles: { fillColor: [100, 149, 237] },
            styles: { fontSize: 9, cellPadding: 3 },
            didDrawPage: function (data) {
                y = data.cursor.y + 10;
            }
        });

        // pull the three summary lines that follow each table
        let next = table.nextElementSibling;
        let count = 0;
        while (next && count < 3) {
            if (next.tagName === 'DIV' && next.innerText.includes("NGN")) {
                const text = next.innerText;
                doc.setFontSize(text.includes("Total") ? 11 : 10);
                const textWidth = doc.getTextWidth(text);
                doc.text(text, doc.internal.pageSize.getWidth() - 40 - textWidth, y);
                y += 14;
                count++;
            }
            next = next.nextElementSibling;
        }

        y += 20;
    }

    // FOUNDATION TOTAL at the end
    const finalTotal = document.getElementById("foundation-total")?.innerText;
    if (finalTotal) {
        doc.setFontSize(13);
        const textWidth = doc.getTextWidth(finalTotal);
        doc.text(finalTotal, doc.internal.pageSize.getWidth() - 40 - textWidth, y + 20);
    }

    doc.save("Foundation_Report.pdf");
}

/* -----------------------------
   Unified Live Recalculation
--------------------------------*/
(function() {
    function parseNum(v) {
        if (v == null) return 0;
        const n = parseFloat(String(v).toString().replace(/,/g,''));
        return isNaN(n) ? 0 : n;
    }
    function fmtNGN(n) {
        return 'NGN ' + Number(n).toLocaleString('en-NG', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
    }
    function fmt(n) {
        return Number(n).toLocaleString('en-NG', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
    }

    function recalcOneTable(table) {
        if (!table) return 0;
        const hasExtra = table.getAttribute('data-has-extra') === '1';
        let subtotal = 0;

        table.querySelectorAll('tbody tr').forEach(row => {
            const qtyEl   = row.querySelector('.qty');
            const priceEl = row.querySelector('.price');
            const extraEl = hasExtra ? row.querySelector('.extra') : null;

            const qty   = parseNum(qtyEl ? qtyEl.value : 0);
            const price = parseNum(priceEl ? priceEl.value : 0);
            const extra = hasExtra ? parseNum(extraEl ? extraEl.value : 0) : 0;

            const rowTotal = (qty + extra) * price;
            const totalCell = row.querySelector('.row-total');
            if (totalCell) totalCell.textContent = fmt(rowTotal);
            subtotal += rowTotal;
        });

        const cont = subtotal * 0.05;
        const sectionTotal = subtotal + cont;

        const subtotalDiv    = table.parentElement.querySelector('.section-subtotal');
        const contingencyDiv = table.parentElement.querySelector('.section-contingency');
        const totalDiv       = table.parentElement.querySelector('.section-total');

        if (subtotalDiv)    subtotalDiv.textContent    = `Subtotal: ${fmtNGN(subtotal)}`;
        if (contingencyDiv) contingencyDiv.textContent = `5% Contingency: ${fmtNGN(cont)}`;
        if (totalDiv)       totalDiv.textContent       = `Total (${table.dataset.section}): ${fmtNGN(sectionTotal)}`;

        return sectionTotal;
    }

    function recalcAll() {
        let foundation = 0;
        document.querySelectorAll('.calc-table').forEach(tbl => {
            foundation += recalcOneTable(tbl);
        });
        const f = document.getElementById('foundation-total');
        if (f) {
            f.textContent = `FOUNDATION TOTAL: ${fmtNGN(foundation)}`;
        }
    }

    // Event delegation for all inputs in all sections
    document.addEventListener('input', function(e) {
        if (e.target.matches('.qty, .price, .extra')) {
            const table = e.target.closest('.calc-table');
            const sectionTotal = recalcOneTable(table);
            // update foundation after a section changes
            let foundation = 0;
            document.querySelectorAll('.calc-table').forEach(tbl => {
                // If same table, we already have sectionTotal; otherwise recalc briefly
                foundation += (tbl === table) ? sectionTotal : recalcOneTable(tbl);
            });
            const f = document.getElementById('foundation-total');
            if (f) f.textContent = `FOUNDATION TOTAL: ${fmtNGN(foundation)}`;
        }
    });

    // Initial pass to normalize numbers and totals
    window.addEventListener('DOMContentLoaded', recalcAll);
})();
</script>
</body>
</html>
