<?php
session_start();
$con = new mysqli("localhost", "root", "", "transparentqdb");
if ($con->connect_error) die("Connection failed: " . $con->connect_error);

/* ---------- Default contingency % ---------- */
$contingency_percent_default = 5;

/* ---------- Resolve project ID ---------- */
$PID = $_GET['project_ref'] ?? $_POST['pid'] ?? $_SESSION['pid'] ?? '';
if (!empty($PID)) {
    $_SESSION['pid'] = $PID;
}

/* ---------- Handle Preliminaries save ---------- */
if (isset($_POST['save_preliminaries'])) {
    $_SESSION['preliminaries'] = [
        'descriptions' => $_POST['prelim_desc'] ?? [],
        'qty'          => $_POST['prelim_qty'] ?? [],
        'price'        => $_POST['prelim_price'] ?? []
    ];
}

/* ---------- Handle Excavation description save ---------- */
if (isset($_POST['save_excavation'])) {
    $_SESSION['excavation_desc_1'] = $_POST['excavation_desc_1'] ?? 'Ground Excavation';
    $_SESSION['excavation_desc_2'] = $_POST['excavation_desc_2'] ?? 'Basket Excavation';
}

/* ---------- Server-side PDF Export ---------- */
if (isset($_POST['export_pdf_v2'])) {

    $watermark_enabled = isset($_POST['enable_watermark']) && $_POST['enable_watermark'] == "1";
    $contingency_percent = isset($_POST['contingency_percent']) ? floatval($_POST['contingency_percent']) : $contingency_percent_default;

    // Fetch project info
    $project_info = $con->query("SELECT * FROM upline_app_project_info WHERE project_ref='$PID'")->fetch_assoc();

    // Fetch preliminaries (from session)
    $prelim_data = [];
    if (!empty($_SESSION['preliminaries']['descriptions'])) {
        foreach ($_SESSION['preliminaries']['descriptions'] as $i => $desc) {
            $prelim_data[] = [
                'description' => $desc,
                'quantity'    => floatval($_SESSION['preliminaries']['qty'][$i] ?? 0),
                'price'       => floatval($_SESSION['preliminaries']['price'][$i] ?? 0)
            ];
        }
    }

    /* ---------- Excavation, Earthworks and Others ---------- */
    $excavation_data = [];

    // === Ground Excavation ===
    $wall_data = $con->query("SELECT walllength FROM wall_calculation WHERE PID='$PID'");
    $total_walllength = 0;
    if ($wall_data && $wall_data->num_rows > 0) {
        while ($w = $wall_data->fetch_assoc()) {
            $total_walllength += floatval($w['walllength']);
        }
    }

    $labor_ground = $con->query("SELECT labourer_price, SomeLabourers_1C, SomeLabourers_1D 
        FROM upline_app_laboursettings WHERE labour_name='Ground Excavation'")->fetch_assoc();

    $ground_qty = 0;
    $ground_price = 0;
    if ($labor_ground) {
        $SomeLabourers_1C = floatval($labor_ground['SomeLabourers_1C'] ?? 0);
        $SomeLabourers_1D = floatval($labor_ground['SomeLabourers_1D'] ?? 0);
        $ground_qty = $total_walllength + ($SomeLabourers_1C * $SomeLabourers_1D);
        $ground_price = floatval($labor_ground['labourer_price']) * 2; // +100%
    }

    // === Basket Excavation ===
    $base_data = $con->query("SELECT Numb FROM basetypecal WHERE PID='$PID'");
    $total_numb = 0;
    if ($base_data && $base_data->num_rows > 0) {
        while ($b = $base_data->fetch_assoc()) {
            $total_numb += floatval($b['Numb']);
        }
    }

    $labor_basket = $con->query("SELECT labour_price 
        FROM upline_app_laboursettings WHERE labour_name='Basket Excavation'")->fetch_assoc();

    $basket_qty = $total_numb;
    $basket_price = 0;
    if ($labor_basket) {
        $basket_price = floatval($labor_basket['labourer_price']) * 2; // +100%
    }

    $desc1 = $_SESSION['excavation_desc_1'] ?? 'Ground Excavation';
    $desc2 = $_SESSION['excavation_desc_2'] ?? 'Basket Excavation';

    $excavation_data = [
        [
            'description' => $desc1,
            'quantity' => round($ground_qty, 2),
            'price' => round($ground_price, 2)
        ],
        [
            'description' => $desc2,
            'quantity' => round($basket_qty, 2),
            'price' => round($basket_price, 2)
        ]
    ];

    /* ---------- Reinforcement, Equipment, Blockwork, Formwork ---------- */
    $reinforcement_data = [];
    $reinforcement_query = $con->query("
        SELECT fm.MaterialDescription, fm.COEQuantity,
               sw.steels_wire_price, sw.steels_wire_per_ton, sw.Labour, sw.EngrFee
        FROM foundation_materials fm
        JOIN upline_app_steels_wire sw
          ON fm.MaterialDescription = sw.steels_wire_name
        WHERE fm.PID='$PID' AND fm.MaterialType='Steel'
    ");
    if ($reinforcement_query && $reinforcement_query->num_rows > 0) {
        while ($r = $reinforcement_query->fetch_assoc()) {
            $COEQuantity = floatval($r['COEQuantity'] ?? 0);
            $per_ton     = floatval($r['steels_wire_per_ton'] ?? 1);
            $price_total = (floatval($r['steels_wire_price']) + floatval($r['Labour']) + floatval($r['EngrFee'])) * $per_ton;
            $reinforcement_data[] = [
                'description' => $r['MaterialDescription'],
                'quantity'    => ($per_ton != 0) ? round($COEQuantity / $per_ton, 2) : 0,
                'price'       => $price_total
            ];
        }
    }

    $equipment_data = $con->query("SELECT * FROM foundation_equipment WHERE PID='$PID'")->fetch_all(MYSQLI_ASSOC);

    $block_query = $con->query("SELECT MaterialDescription, BOQQuantity AS Quantity, `BOQ-Price` AS Price
                                FROM foundation_materials 
                                WHERE PID='$PID' AND MaterialType='block'");
    $block_data = ($block_query && $block_query->num_rows > 0) ? $block_query->fetch_all(MYSQLI_ASSOC) : [];

    $formwork_data = [];
    $formwork_query = $con->query("
        SELECT p.plankorwood_name, p.plankorwood_qty,
               COALESCE(m.SquareM, 1) AS SquareM, COALESCE(m.pricepersq, 0) AS pricepersq
        FROM plankandwoodreport p
        LEFT JOIN upline_app_materials m
          ON TRIM(LOWER(p.plankorwood_name)) = TRIM(LOWER(m.material_name))
        WHERE p.PID='$PID'
    ");
    if ($formwork_query && $formwork_query->num_rows > 0) {
        while ($r = $formwork_query->fetch_assoc()) {
            $plank_qty  = floatval($r['plankorwood_qty'] ?? 0);
            $squareM    = floatval($r['SquareM'] ?? 1);
            $price_sq   = floatval($r['pricepersq'] ?? 0);
            $calc_qty = round($plank_qty * $squareM, 2);
            $formwork_data[] = [
                'description' => $r['plankorwood_name'],
                'quantity'    => $calc_qty,
                'price'       => $price_sq
            ];
        }
    }

    // === FPDF setup ===
    require_once __DIR__ . '/fpdf/fpdf.php';
    class PDF_WithAlpha extends FPDF {
        function Header() {
            $imgPath = __DIR__ . '/WhatsApp Image 2025-09-25 at 12.16.46.jpeg';
            if (file_exists($imgPath)) $this->Image($imgPath, 12, 10, 25);
            $this->SetFont('Arial','B',12);
            $this->SetXY(40,10);
            $this->Cell(0,6,'Upline Innovations Cycle Ltd.',0,1,'L');
            $this->SetFont('Arial','',9);
            $this->SetX(40);
            $this->MultiCell(0,5,"No.1, Boundary Between Otokutu & Egbo Uhurie Communities,\nAlong Otokutu - Egbo Road, Ughelli South L.G.A., Delta State, Nigeria.\nTel: +234 911 717 1920, +234 701 326 7663 | Web: www.upline.works | Email: we@upline.works");
            $this->Ln(2);
            $this->SetFont('Arial','I',8);
            $this->Cell(0,5,'Modern Architectural Designs • Complete Structural Designs • Planning of Environments • Modern Constructions • Project’s Costing • and a lot more…',0,1,'L');
            $this->SetDrawColor(30,144,255); $this->SetLineWidth(0.6);
            $this->Line(10,$this->GetY(),200,$this->GetY()); $this->Ln(4);
        }
        function Footer() {
            $this->SetDrawColor(200,200,200); $this->Line(10,$this->GetPageHeight()-20,200,$this->GetPageHeight()-20);
            $this->SetY(-15); $this->SetFont('Arial','I',9);
            $this->SetTextColor(100,100,100);
            $this->Cell(0,5,'Upline Innovations Cycle Ltd. — Confidential Document',0,0,'L');
            $this->Cell(0,5,'Page '.$this->PageNo().' of {nb}',0,0,'R');
        }
    }

    function renderTablePDF($pdf,$title,$rows,$contingency_percent){
        $pdf->SetFont('Arial','B',11);
        $pdf->SetFillColor(30,144,255);
        $pdf->SetTextColor(255,255,255);
        $pdf->Cell(0,8,'  '.strtoupper($title),0,1,'L',true);
        $pdf->SetFont('Arial','B',10);
        $pdf->SetFillColor(240,240,240);
        $pdf->SetTextColor(0,0,0);
        $pdf->Cell(10,7,'#',1,0,'C',true);
        $pdf->Cell(90,7,'Description',1,0,'L',true);
        $pdf->Cell(20,7,'Qty',1,0,'C',true);
        $pdf->Cell(30,7,'Unit Price',1,0,'R',true);
        $pdf->Cell(40,7,'Total',1,1,'R',true);
        $pdf->SetFont('Arial','',10);
        $subtotal=0; $i=1;
        foreach($rows as $r){
            $desc=$r['description']??'';
            $qty=floatval($r['quantity']??0);
            $price=floatval($r['price']??0);
            $total=$qty*$price;
            $subtotal+=$total;
            $pdf->Cell(10,7,$i++,1,0,'C');
            $pdf->Cell(90,7,substr($desc,0,60),1,0,'L');
            $pdf->Cell(20,7,$qty,1,0,'C');
            $pdf->Cell(30,7,number_format($price,2),1,0,'R');
            $pdf->Cell(40,7,number_format($total,2),1,1,'R');
        }
        $contingency=$subtotal*($contingency_percent/100);
        $grand=$subtotal+$contingency;
        $pdf->SetFont('Arial','B',10);
        $pdf->Cell(150,7,'Subtotal',1);
        $pdf->Cell(40,7,number_format($subtotal,2),1,1,'R');
        $pdf->Cell(150,7,$contingency_percent.'% Contingency',1);
        $pdf->Cell(40,7,number_format($contingency,2),1,1,'R');
        $pdf->Cell(150,7,'Total',1);
        $pdf->Cell(40,7,number_format($grand,2),1,1,'R');
        $pdf->Ln(5);
        return $grand;
    }

    $pdf=new PDF_WithAlpha('P','mm','A4');
    $pdf->AliasNbPages();
    $pdf->AddPage();
    $pdf->SetFont('Arial','B',12);
    $pdf->Cell(0,8,'BILL OF QUANTITIES (BOQ)',0,1,'C');
    $pdf->Ln(5);

    $prelimTotal = renderTablePDF($pdf,'Preliminaries',$prelim_data,$contingency_percent);
    $excavationTotal = renderTablePDF($pdf,'Excavation, Earthworks and Others',$excavation_data,$contingency_percent);
    $concreteTotal = renderTablePDF($pdf,'Concrete',[],$contingency_percent);
    $reinforcementTotal = renderTablePDF($pdf,'Reinforcement Work',$reinforcement_data,$contingency_percent);
    $equipmentTotal = renderTablePDF($pdf,'Construction Equipment',$equipment_data,$contingency_percent);
    $blockTotal = renderTablePDF($pdf,'Block Work',$block_data,$contingency_percent);
    $formworkTotal = renderTablePDF($pdf,'Form Works',$formwork_data,$contingency_percent);

    $grand=$prelimTotal+$excavationTotal+$concreteTotal+$reinforcementTotal+$equipmentTotal+$blockTotal+$formworkTotal;

    $pdf->AddPage();
    $pdf->SetFont('Arial','B',12);
    $pdf->Cell(0,8,'FINAL SUMMARY OF COSTS',0,1,'L');
    $pdf->SetFont('Arial','',10);
    $summary=[
        'Preliminaries'=>$prelimTotal,
        'Excavation, Earthworks and Others'=>$excavationTotal,
        'Concrete'=>$concreteTotal,
        'Reinforcement Work'=>$reinforcementTotal,
        'Construction Equipment'=>$equipmentTotal,
        'Block Work'=>$blockTotal,
        'Form Works'=>$formworkTotal
    ];
    foreach($summary as $title=>$val){
        $pdf->Cell(150,7,$title,1);
        $pdf->Cell(40,7,number_format($val,2),1,1,'R');
    }
    $pdf->SetFont('Arial','B',11);
    $pdf->Cell(150,8,'GRAND TOTAL',1);
    $pdf->Cell(40,8,number_format($grand,2),1,1,'R');
    $pdf->Output('I','BOQ_Report.pdf');
    exit;
}
?>

<!DOCTYPE html>
<html>
<head>
<title>BOQ Report</title>
<meta charset="utf-8">
<script src="https://cdn.tailwindcss.com"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body class="bg-gray-100 p-6">

<!-- Letterhead Header (Web View) -->
<div class="max-w-6xl mx-auto bg-white shadow p-6 rounded-xl mb-6">
  <div class="flex items-start">
    <img src="WhatsApp Image 2025-09-25 at 12.16.46.jpeg" alt="Company Logo" class="w-20 h-20 object-contain mr-4">
    <div>
      <h1 class="text-xl font-bold">Upline Innovations Cycle Ltd.</h1>
      <p class="text-sm">
        No.1, Boundary Between Otokutu & Egbo Uhurie Communities,<br>
        Along Otokutu - Egbo Road, Ughelli South L.G.A., Delta State, Nigeria.<br>
        Tel: +234 911 717 1920, +234 701 326 7663<br>
        Web: www.upline.works | Email: we@upline.works
      </p>
      <p class="italic text-gray-600 text-sm mt-1">
        Modern Architectural Designs • Complete Structural Designs • Planning of Environments • 
        Modern Constructions • Business Application • Project’s Costing • and a lot more…
      </p>
    </div>
  </div>
  <div class="flex justify-end mb-4">
      <a href="dashboard.php" class="bg-gray-600 text-white px-4 py-2 rounded">Back to Dashboard</a>
  </div>
  <hr class="border-t-2 border-blue-500 mt-4">
</div>

<form method="POST">
<div class="max-w-6xl mx-auto bg-white shadow p-6 rounded-xl">

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

  <!-- Summary Table at Top -->
  <div class="mb-8">
    <h3 class="text-xl font-bold mb-2">Summary of Costs</h3>
    <table class="w-full text-sm border bg-white shadow rounded">
      <thead class="bg-gray-200"><tr><th>Section</th><th class="text-right">Total (NGN)</th></tr></thead>
      <tbody>
        <tr><td>Preliminaries</td><td class="text-right summary-total" data-section="preliminaries">0.00</td></tr>
        <tr><td>Excavation, Earthworks and Others</td><td class="text-right summary-total" data-section="excavation">0.00</td></tr>
        <tr><td>Concrete</td><td class="text-right summary-total" data-section="concrete">0.00</td></tr>
        <tr><td>Reinforcement Work</td><td class="text-right summary-total" data-section="reinforcement">0.00</td></tr>
        <tr><td>Construction Equipment</td><td class="text-right summary-total" data-section="equipment">0.00</td></tr>
        <tr><td>Block Work</td><td class="text-right summary-total" data-section="blockwork">0.00</td></tr>
        <tr><td>Form Works</td><td class="text-right summary-total" data-section="formwork">0.00</td></tr>
        <tr class="bg-gray-100 font-bold"><td>GRAND TOTAL</td><td id="grandTotalCell" class="text-right">0.00</td></tr>
      </tbody>
    </table>
  </div>

  <!-- Contingency input -->
  <input type="number" id="contingencyInput" name="contingency_percent"
    value="<?= htmlspecialchars($contingency_percent_default) ?>" step="0.1" min="0"
    class="border px-2 py-1 w-20 mb-4"/>

  <?php
  render_preliminaries_section($contingency_percent_default);
  render_excavation_section($con, $PID, $contingency_percent_default);

  render_editable_section('concrete',[],'Description','Quantity',null,'UnitRate',$contingency_percent_default);
  ?>

  <div class="text-right mt-6">
    <label><input type="checkbox" name="enable_watermark" value="1" checked> Include Watermark</label>
    <button type="submit" name="export_pdf_v2" class="ml-4 bg-green-600 text-white px-4 py-2 rounded">Export to PDF</button>
  </div>
</div>
</form>

<script>
function getContingencyPercent() {
  return parseFloat($("#contingencyInput").val()) || 0;
}
function recalcSection($sectionDiv) {
  let subtotal=0, percent=getContingencyPercent();
  $sectionDiv.find("tbody tr").each(function(){
    let qty=parseFloat($(this).find(".qty-input").val())||0;
    let price=parseFloat($(this).find(".price-input").val())||0;
    let total=qty*price;
    $(this).find(".total-cell").text(total.toFixed(2));
    subtotal+=total;
  });
  let contingency=subtotal*(percent/100), grand=subtotal+contingency;
  $sectionDiv.find(".subtotal").text("Subtotal: NGN "+subtotal.toFixed(2));
  $sectionDiv.find(".contingency").text(percent+"% Contingency: NGN "+contingency.toFixed(2));
  $sectionDiv.find(".section-total").text("Total ("+$sectionDiv.data("section")+"): NGN "+grand.toFixed(2));
  $(".summary-total[data-section='"+$sectionDiv.data("section")+"']").text(grand.toFixed(2));
  return grand;
}
function recalcAll() {
  let grandTotal=0;
  $(".boq-table").each(function(){ grandTotal+=recalcSection($(this)); });
  $("#grandTotalCell").text(grandTotal.toFixed(2));
}
$(document).on("input",".qty-input,.price-input,.extra-input,#contingencyInput",recalcAll);
$(document).on("click",".boq-header",function(){
  let $table=$(this).next(".boq-table"), section=$table.data("section");
  $table.toggleClass("hidden");
  localStorage.setItem("boq_section_"+section,$table.hasClass("hidden")?"closed":"open");
});
$(document).ready(function(){
  $(".boq-table").each(function(){
    let section=$(this).data("section"), saved=localStorage.getItem("boq_section_"+section);
    if(saved==="open") $(this).removeClass("hidden");
  });
  recalcAll();
});
</script>
</body>
</html>
