<?php
session_start();

/* ─────────────────────────────────────────────
   ACCESS CHECK
   ──────────────────────────────────────────────
   REMOVED: same pattern as the Board Meeting dashboard — a quotation
   id/paging block that concatenated $_REQUEST['id'] straight into a
   SQL string (injection hole) and fetched $client_info, none of which
   is used anywhere in this report. This page is a year-filtered
   Production/Purchasing/Productivity dashboard, not a single-quotation
   view. If access control is actually required here, use a real
   session/role check, or this SAFE escaped version if it must be tied
   to an id:

   $qot_id = utility::app_crypt($_REQUEST['qot_id'] ?? '', 'd');
   if (!isset($_REQUEST['id']) || $_REQUEST['id'] !== $qot_id) {
       die("Invalid URL");
   }
   $safe_id = mysqli_real_escape_string($conn, $_REQUEST['id']);
───────────────────────────────────────────── */

/* ─────────────────────────────────────────────
   COMMON FILTER
───────────────────────────────────────────── */
$year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');

/* Active tab — FIX: no persistence existed before (always defaulted to
   tab 0). Added the same validated ?red= pattern used in the sibling
   reports so selecting Purchasing/Productivity survives a year-filter
   submit instead of snapping back to Production Totals. */
$red = isset($_GET['red']) ? intval($_GET['red']) : 0;
if ($red < 0 || $red > 2) { $red = 0; }

/* ─────────────────────────────────────────────
   HELPERS
───────────────────────────────────────────── */

/* Build the list of calendar months to report on: Jan of (year-1)
   through Dec of (year+1), capped at the current month so we don't
   generate rows for months that haven't happened yet.
   FIX: the original derived its month list from
   `SELECT ... GROUP BY YEAR,MONTH ... LIMIT 18`, which (a) silently
   skips any month with zero rows in daily_production_summary instead
   of showing it as a blank/zero row, and (b) the LIMIT 18 cap doesn't
   cover a full 3-year window and doesn't match the ~26 rows in the
   reference screenshot. */
function build_month_range($year) {
    $start = new DateTime(sprintf('%04d-01-01', $year - 1));
    $cap   = new DateTime(sprintf('%04d-12-01', $year + 1));
    $today = new DateTime(date('Y-m-01'));
    $end   = ($cap < $today) ? $cap : $today;

    $months = [];
    $cursor = clone $start;
    while ($cursor <= $end) {
        $months[] = ['year' => (int)$cursor->format('Y'), 'month' => (int)$cursor->format('n')];
        $cursor->modify('+1 month');
    }
    return $months;
}

function fmt_gbp2($v, $dash_if_zero = false) {
    if ($dash_if_zero && $v == 0) return '<span class="no-data">-</span>';
    $sign = $v < 0 ? '-' : '';
    return $sign . '£ ' . number_format(abs($v), 2);
}
function fmt_pct($v, $has_data = true) {
    if (!$has_data) return '<span class="no-data">-</span>';
    return number_format($v, 0) . '%';
}

/* ─────────────────────────────────────────────
   TAB 0 DATA — Production Totals (monthly)
   FIX: the original built this SQL fresh inline in the tab-1 markup,
   with `echo $sales_sql = "...";` accidentally dumping raw SQL onto
   the page every month (debug leftover), and no fallback handling for
   months with no closing snapshot yet. Extracted into a function so
   Tabs 2 and 3 (Purchasing %, and — if you want it later — any other
   report) can reuse the same production-total figures instead of
   re-deriving them differently, which is exactly how the earlier
   reports in this set drifted out of sync with each other.
───────────────────────────────────────────── */
function get_month_production_actual($conn, $y, $m, &$prev_fg, &$prev_wip) {
    $sales_sql = "
        SELECT SUM(pp.req_qty * IFNULL(im.standard_cost_ebq, 0)) AS sales
        FROM production_planning pp
        LEFT JOIN item_management im ON im.id = pp.part_id
        WHERE YEAR(pp.req_date) = $y AND MONTH(pp.req_date) = $m
    ";
    $sales_res = mysqli_query($conn, $sales_sql);
    $sales_row = mysqli_fetch_assoc($sales_res);
    $sales = floatval($sales_row['sales'] ?? 0);

    $first_day = sprintf('%04d-%02d-01', $y, $m);
    $last_day  = date('Y-m-t', strtotime($first_day));

    $opening_sql = "
        SELECT fg, (vac_wip + cnc_wip + pu_wip) AS wip
        FROM daily_production_summary
        WHERE record_date < '$first_day'
        ORDER BY record_date DESC LIMIT 1
    ";
    $opening_res = mysqli_query($conn, $opening_sql);
    $opening = mysqli_fetch_assoc($opening_res);
    $opening_fg  = $opening ? floatval($opening['fg'])  : $prev_fg;
    $opening_wip = $opening ? floatval($opening['wip']) : $prev_wip;

    $closing_sql = "
        SELECT fg, (vac_wip + cnc_wip + pu_wip) AS wip
        FROM daily_production_summary
        WHERE record_date BETWEEN '$first_day' AND '$last_day'
        ORDER BY record_date DESC LIMIT 1
    ";
    $closing_res = mysqli_query($conn, $closing_sql);
    $closing = mysqli_fetch_assoc($closing_res);
    $has_closing = (bool)$closing;
    /* FIX: previously defaulted to 0 when there was no closing row yet
       (current/future month), which made fg_mov/wip_mov swing to a
       huge, meaningless negative number. Falls back to the opening
       value instead — i.e. "no movement recorded yet". */
    $closing_fg  = $has_closing ? floatval($closing['fg'])  : $opening_fg;
    $closing_wip = $has_closing ? floatval($closing['wip']) : $opening_wip;

    $fg_mov  = $closing_fg  - $opening_fg;
    $wip_mov = $closing_wip - $opening_wip;

    /* STD Hours = vac+cnc+pu+assbly, Clock Hours = vac+cnc+pu.
       FIX: the original also summed clock_hours_assbly into Clock
       Hours here. Every other report in this set (Sales Progress /
       Daily Progress / STD Hours) excludes Assembly from the Clock
       Hours total — aligned here for consistency. */
    $hours_sql = "
        SELECT
            SUM(std_hours_vac + std_hours_cnc + std_hours_pu + std_hours_assbly) AS std_hours,
            SUM(clock_hours_vac + clock_hours_cnc + clock_hours_pu) AS clock_hours
        FROM daily_production_summary
        WHERE YEAR(record_date) = $y AND MONTH(record_date) = $m
    ";
    $hours_res = mysqli_query($conn, $hours_sql);
    $hours = mysqli_fetch_assoc($hours_res);
    $std_hours   = floatval($hours['std_hours']   ?? 0);
    $clock_hours = floatval($hours['clock_hours'] ?? 0);

    $prod_total = $sales + $fg_mov + $wip_mov;
    $pound_per_std_hour = $std_hours > 0 ? $prod_total / $std_hours : 0;
    $productivity = $clock_hours > 0 ? ($std_hours / $clock_hours) * 100 : 0;

    $prev_fg  = $closing_fg;
    $prev_wip = $closing_wip;

    return [
        'sales' => $sales,
        'opening_fg' => $opening_fg, 'closing_fg' => $closing_fg, 'fg_mov' => $fg_mov,
        'opening_wip' => $opening_wip, 'closing_wip' => $closing_wip, 'wip_mov' => $wip_mov,
        'prod_total' => $prod_total,
        'std_hours' => $std_hours, 'pound_per_std_hour' => $pound_per_std_hour,
        'clock_hours' => $clock_hours, 'productivity' => $productivity,
        'has_closing' => $has_closing,
    ];
}

/* ─────────────────────────────────────────────
   TAB 1 DATA — Purchasing (monthly, RMPL / RMPU / BOP)
   ASSUMPTION: your screenshot has only 3 categories (RMPL, RMPU, BOP),
   unlike the 7-category breakdown used on the Sales Progress report's
   Purchasing tab. Summing 'BOP','BO','BI' together under one BOP
   bucket here so it works whether your product_group table uses a
   single 'BOP' code or the separate 'BI'/'BO' codes from that other
   report — please confirm which is actually the case in your DB.
   % = this month's Purchasing Total ÷ this month's Production Total,
   confirmed by cross-checking your screenshot numbers directly
   (e.g. Dec-23: £167,982 / £384,964 = 44%, matches exactly).
───────────────────────────────────────────── */
function get_month_purchasing_actual($conn, $y, $m) {
    $first_day = sprintf('%04d-%02d-01 00:00:00', $y, $m);
    $last_day  = date('Y-m-t 23:59:59', strtotime($first_day));

    $sql = "
        SELECT pg.product_group_name, SUM(ps.qty_received * poim.unit_price) AS grp_total
        FROM purchase_slips ps
        INNER JOIN purchase_order_items_mapping poim ON poim.id = ps.purchase_order_mapp_id
        INNER JOIN product_group pg ON pg.id = poim.nominal_code
        WHERE ps.slip_date BETWEEN '$first_day' AND '$last_day'
          AND pg.product_group_name IN ('RMPL','RMPU','BOP','BO','BI')
        GROUP BY pg.product_group_name
    ";
    $res = mysqli_query($conn, $sql);
    $rmpl = $rmpu = $bop = 0.0;
    if ($res) {
        while ($row = mysqli_fetch_assoc($res)) {
            $val = floatval($row['grp_total']);
            switch ($row['product_group_name']) {
                case 'RMPL': $rmpl += $val; break;
                case 'RMPU': $rmpu += $val; break;
                case 'BOP':
                case 'BO':
                case 'BI':   $bop  += $val; break;
            }
        }
    }
    $total = $rmpl + $rmpu + $bop;
    return ['rmpl' => $rmpl, 'rmpu' => $rmpu, 'bop' => $bop, 'total' => $total];
}

/* ─────────────────────────────────────────────
   TAB 2 DATA — Productivity by Section (monthly)
   Five sections confirmed from your screenshot: VAC, CNC, ASSY PL,
   PU ML, ASSY PU. Mapped to columns as:
     VAC     -> std_hours_vac / clock_hours_vac
     CNC     -> std_hours_cnc / clock_hours_cnc
     ASSY PL -> std_hours_assbly / clock_hours_assbly
     PU ML   -> std_hours_pu / clock_hours_pu
     ASSY PU -> std_hours_assmpu / clock_hours_assmpu
   NOTE: ASSY PU (assmpu) is NOT included in any org-wide STD/Clock
   Hours total elsewhere in the app (see the review notes above this
   file) — it only appears in this section-level breakdown.
───────────────────────────────────────────── */
function get_month_section_hours($conn, $y, $m) {
    $sql = "
        SELECT
            SUM(clock_hours_vac)    AS vac_clock,     SUM(std_hours_vac)    AS vac_std,
            SUM(clock_hours_cnc)    AS cnc_clock,     SUM(std_hours_cnc)    AS cnc_std,
            SUM(clock_hours_assbly) AS assypl_clock,  SUM(std_hours_assbly) AS assypl_std,
            SUM(clock_hours_pu)     AS puml_clock,    SUM(std_hours_pu)     AS puml_std,
            SUM(clock_hours_assmpu) AS assypu_clock,  SUM(std_hours_assmpu) AS assypu_std
        FROM daily_production_summary
        WHERE YEAR(record_date) = $y AND MONTH(record_date) = $m
    ";
    $row = mysqli_fetch_assoc(mysqli_query($conn, $sql));

    $map = [
        'VAC'     => ['vac_clock', 'vac_std'],
        'CNC'     => ['cnc_clock', 'cnc_std'],
        'ASSY PL' => ['assypl_clock', 'assypl_std'],
        'PU ML'   => ['puml_clock', 'puml_std'],
        'ASSY PU' => ['assypu_clock', 'assypu_std'],
    ];
    $sections = [];
    foreach ($map as $label => $keys) {
        $clock = floatval($row[$keys[0]] ?? 0);
        $std   = floatval($row[$keys[1]] ?? 0);
        $sections[$label] = [
            'clock' => $clock,
            'std'   => $std,
            'prod'  => $clock > 0 ? ($std / $clock) * 100 : null,
        ];
    }
    return $sections;
}

/* ─────────────────────────────────────────────
   BUILD ALL THREE DATASETS UP FRONT
───────────────────────────────────────────── */
$months = build_month_range($year);

$production_rows = [];
$prev_fg = 0; $prev_wip = 0;
foreach ($months as $mo) {
    $key = sprintf('%04d-%02d', $mo['year'], $mo['month']);
    $data = get_month_production_actual($conn, $mo['year'], $mo['month'], $prev_fg, $prev_wip);
    $data['label'] = date('M-y', strtotime(sprintf('%04d-%02d-01', $mo['year'], $mo['month'])));
    $data['year']  = $mo['year'];
    $data['month'] = $mo['month'];
    $production_rows[$key] = $data;
}

$purchasing_rows = [];
foreach ($months as $mo) {
    $key = sprintf('%04d-%02d', $mo['year'], $mo['month']);
    $p = get_month_purchasing_actual($conn, $mo['year'], $mo['month']);
    $prod_total_for_month = $production_rows[$key]['prod_total'] ?? 0;
    $p['pct'] = $prod_total_for_month > 0 ? ($p['total'] / $prod_total_for_month) * 100 : 0;
    $p['label'] = $production_rows[$key]['label'];
    $purchasing_rows[$key] = $p;
}

$section_rows = [];
foreach ($months as $mo) {
    $key = sprintf('%04d-%02d', $mo['year'], $mo['month']);
    $section_rows[$key] = [
        'label'    => $production_rows[$key]['label'],
        'sections' => get_month_section_hours($conn, $mo['year'], $mo['month']),
    ];
}

/* ── Trailing 6 months (most recent 6 *completed* months) for Tab 0 ── */
$completed = array_filter($production_rows, function($r) { return $r['has_closing']; });
$trailing6 = array_slice($completed, -6, 6, true);
$t6_sales = $t6_fg = $t6_wip = $t6_prod = $t6_std = $t6_clock = 0;
$t6_label_from = $t6_label_to = '';
if (count($trailing6) > 0) {
    $keys = array_keys($trailing6);
    $t6_label_from = $production_rows[$keys[0]]['label'];
    $t6_label_to   = $production_rows[end($keys)]['label'];
    foreach ($trailing6 as $r) {
        $t6_sales += $r['sales'];
        $t6_fg    += $r['fg_mov'];
        $t6_wip   += $r['wip_mov'];
        $t6_prod  += $r['prod_total'];
        $t6_std   += $r['std_hours'];
        $t6_clock += $r['clock_hours'];
    }
}
$t6_stock_mov = $t6_fg + $t6_wip;
$t6_pound_per_std = $t6_std > 0 ? $t6_prod / $t6_std : 0;
$t6_productivity  = $t6_clock > 0 ? ($t6_std / $t6_clock) * 100 : 0;

/* ── Grand totals for Tab 1 (Purchasing), same trailing range as Tab 0 for consistency ── */
$purch_trailing = array_slice($purchasing_rows, -6, 6, true);
$gt_rmpl = $gt_rmpu = $gt_bop = $gt_total = 0;
foreach ($purch_trailing as $r) {
    $gt_rmpl  += $r['rmpl'];
    $gt_rmpu  += $r['rmpu'];
    $gt_bop   += $r['bop'];
    $gt_total += $r['total'];
}
$gt_pct = $t6_prod > 0 ? ($gt_total / $t6_prod) * 100 : 0;

/* ── Averages by section for Tab 2 — sum(std)/sum(clock) ratio over the
      whole displayed range, confirmed against your screenshot
      (VAC: 6180/4261 = 145% ✓, CNC: 9630/8822 = 109% ✓) ── */
$section_totals = [];
foreach (['VAC','CNC','ASSY PL','PU ML','ASSY PU'] as $label) {
    $section_totals[$label] = ['clock' => 0, 'std' => 0];
}
foreach ($section_rows as $row) {
    foreach ($row['sections'] as $label => $vals) {
        $section_totals[$label]['clock'] += $vals['clock'];
        $section_totals[$label]['std']   += $vals['std'];
    }
}
foreach ($section_totals as $label => &$tot) {
    $tot['prod'] = $tot['clock'] > 0 ? ($tot['std'] / $tot['clock']) * 100 : null;
}
unset($tot);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Production Totals - <?= $year ?></title>
<!--
    FIX: same missing-includes issue as every other page in this set —
    Bootstrap classes and a $(document).ready() jQuery/Toastify call
    with none of Bootstrap CSS, jQuery, or Toastify's CSS/JS loaded.
-->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/toastify-js/src/toastify.min.css">
<style>
em { color: red; }
.card.card-purple .card-header { background-color: #9c4893; }

.no-data { color: #999; }

/* ── year filter ── */
.year-selector {
    display: flex; align-items: center; gap: 10px; flex-wrap: wrap;
    margin-bottom: 20px; padding: 14px; background-color: #f8f9fa; border-radius: 6px;
}
.year-selector label  { font-weight: bold; margin: 0; }
.year-selector select { padding: 5px 10px; border: 1px solid #ccc; border-radius: 4px; }
.year-selector button {
    padding: 6px 15px; background-color: #007bff; color: #fff;
    border: none; border-radius: 4px; cursor: pointer;
}
.year-selector button:hover { background-color: #0056b3; }

/* ── shared report styling ── */
.rpt-wrap { width: 100%; padding: 10px 0; }
.rpt-wrap h2 { color: #333; margin-bottom: 16px; text-align: center; font-size: 22px; }
.table-responsive { overflow-x: auto; margin-top: 10px; }
table.rpt-table { width: 100%; border-collapse: collapse; font-size: 11px; }
table.rpt-table.wide { min-width: 1800px; }
.rpt-table th, .rpt-table td { border: 1px solid #ddd; padding: 8px; text-align: right; }
.rpt-table th {
    background-color: #4472C4; color: #fff; font-weight: bold;
    position: sticky; top: 0; z-index: 10; font-size: 10px;
}
.rpt-table td:first-child { text-align: center; font-weight: bold; background-color: #f8f9fa; }
.rpt-table tr:nth-child(even) { background-color: #f9f9f9; }
.rpt-table tr:hover { background-color: #f0f0f0; }
.positive { color: #008000; }
.negative { color: #FF0000; }
.highlight { background-color: #fff2cc; font-weight: bold; }
.totals-row td { background-color: #e8f4f8 !important; border-top: 2px solid #333; font-weight: bold; }
.section-band td {
    background-color: #d2e8c4 !important; font-weight: bold; text-align: center;
    padding: 8px; border: 1px solid #ccc;
}
</style>
</head>
<body>

<section class="content-header">
<?php if (isset($_SESSION['info_message'])): ?>
    <script src="https://cdn.jsdelivr.net/npm/toastify-js"></script>
    <script>
    document.addEventListener('DOMContentLoaded', function () {
        Toastify({
            text: <?= json_encode($_SESSION['info_message']) ?>,
            duration: 2000, close: true, gravity: 'top', position: 'right',
            backgroundColor: '#28a745', style: { borderBottom: '3px solid white' }
        }).showToast();
    });
    </script>
    <?php unset($_SESSION['info_message']); ?>
<?php endif; ?>
    <div class="container-fluid">
        <div class="row"><div class="col-sm-8">
            <h1 style="float:left;clear:right;margin-top:0;padding-bottom:0;">View Production Totals</h1>
        </div></div>
    </div>
</section>

<section>
<div class="row"><div class="container-fluid">
<div class="col-md-12">

    <!-- ── YEAR FILTER ── -->
    <div class="year-selector">
        <form method="GET" action="" id="yearForm">
            <label for="year">Year:</label>
            <select name="year" id="year">
                <?php for ($y = date('Y') - 2; $y <= date('Y') + 1; $y++): ?>
                    <option value="<?= $y ?>" <?= ($y == $year) ? 'selected' : '' ?>><?= $y ?></option>
                <?php endfor; ?>
            </select>
            <input type="hidden" name="red" id="currentTab" value="<?= $red ?>">
            <button type="submit">View Report</button>
        </form>
    </div>

    <script>
    window._activeTab = '<?= $red ?>';
    document.addEventListener('DOMContentLoaded', function () {
        document.querySelectorAll('#custom-tabs-five-tab a').forEach(function(a, i) {
            a.addEventListener('shown.bs.tab', function () {
                window._activeTab = String(i);
                document.getElementById('currentTab').value = String(i);
            });
        });
    });
    </script>

    <div class="card card-purple card-tabs">
        <div class="card-header p-0 pt-1">
            <ul class="nav nav-tabs" id="custom-tabs-five-tab" role="tablist">
                <li class="nav-item">
                    <a class="nav-link <?= $red == 0 ? 'active' : '' ?>" id="boughtParts-tab" data-bs-toggle="pill" href="#boughtParts" role="tab">
                        <b>PRODUCTION TOTALS</b>
                    </a>
                </li>
                <li class="nav-item">
                    <a class="nav-link <?= $red == 1 ? 'active' : '' ?>" id="rawMaterials-tab" data-bs-toggle="pill" href="#rawMaterials" role="tab">
                        <b>PURCHASING</b>
                    </a>
                </li>
                <li class="nav-item">
                    <a class="nav-link <?= $red == 2 ? 'active' : '' ?>" id="productvity-tab" data-bs-toggle="pill" href="#productvity" role="tab">
                        <b>PRODUCTIVITY BY SECTION</b>
                    </a>
                </li>
            </ul>
        </div>

        <div class="card-body">
        <div class="tab-content" id="custom-tabs-five-tabContent">

            <!-- ══════════════════════════════════════════
                 TAB 0 – PRODUCTION TOTALS
            ══════════════════════════════════════════ -->
            <div class="tab-pane fade <?= $red == 0 ? 'active show' : '' ?>" id="boughtParts" role="tabpanel">
            <div class="rpt-wrap">
                <h2>PRODUCTION ACTUALS – <?= $year ?></h2>
                <div class="table-responsive">
                <table class="rpt-table wide">
                    <thead>
                        <tr>
                            <th rowspan="2"></th>
                            <th rowspan="2">Sales</th>
                            <th rowspan="2">Opening FG stock</th>
                            <th rowspan="2">Closing FG stock</th>
                            <th rowspan="2">FG Stock Mov</th>
                            <th rowspan="2">Opening WIP stock</th>
                            <th rowspan="2">Closing WIP stock</th>
                            <th rowspan="2">WIP Stock Mov</th>
                            <th rowspan="2">Prod Total</th>
                            <th rowspan="2">Std Hours</th>
                            <th colspan="2">£ / Std Hour</th>
                            <th rowspan="2">Clock Hours</th>
                            <th rowspan="2">Productivity</th>
                        </tr>
                        <tr><th>£</th><th></th></tr>
                    </thead>
                    <tbody>
                    <?php foreach ($production_rows as $r): ?>
                        <tr>
                            <td><?= $r['label'] ?></td>
                            <td><?= fmt_gbp2($r['sales']) ?></td>
                            <td><?= fmt_gbp2($r['opening_fg']) ?></td>
                            <td><?= fmt_gbp2($r['closing_fg']) ?></td>
                            <td class="<?= $r['fg_mov'] >= 0 ? 'positive' : 'negative' ?>"><?= fmt_gbp2($r['fg_mov']) ?></td>
                            <td><?= fmt_gbp2($r['opening_wip']) ?></td>
                            <td><?= fmt_gbp2($r['closing_wip']) ?></td>
                            <td class="<?= $r['wip_mov'] >= 0 ? 'positive' : 'negative' ?>"><?= fmt_gbp2($r['wip_mov']) ?></td>
                            <td class="highlight"><?= fmt_gbp2($r['prod_total']) ?></td>
                            <td><?= number_format($r['std_hours'], 0) ?></td>
                            <td>£</td>
                            <td><?= number_format($r['pound_per_std_hour'], 2) ?></td>
                            <td><?= number_format($r['clock_hours'], 0) ?></td>
                            <td><?= fmt_pct($r['productivity'], $r['clock_hours'] > 0) ?></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                    <?php if (count($trailing6) > 0): ?>
                    <tfoot>
                        <tr class="totals-row">
                            <td colspan="14" style="text-align:center;background:#dde8f9!important;">
                                6 MONTH TOTALS (<?= $t6_label_from ?> – <?= $t6_label_to ?>)
                            </td>
                        </tr>
                        <tr class="totals-row">
                            <td>Total</td>
                            <td><?= fmt_gbp2($t6_sales) ?></td>
                            <td colspan="2" style="text-align:center;">Stock Mov</td>
                            <td colspan="2"><?= fmt_gbp2($t6_stock_mov) ?></td>
                            <td></td>
                            <td><?= fmt_gbp2($t6_prod) ?></td>
                            <td><?= number_format($t6_std, 0) ?></td>
                            <td>£</td>
                            <td><?= number_format($t6_pound_per_std, 2) ?></td>
                            <td><?= number_format($t6_clock, 0) ?></td>
                            <td><?= number_format($t6_productivity, 0) ?>%</td>
                        </tr>
                    </tfoot>
                    <?php endif; ?>
                </table>
                </div>
                <p style="font-size:11px;color:#888;margin-top:10px;">
                    * "6 Month Totals" covers the most recent 6 completed months (the current in-progress month, if any, is excluded).
                    STOCK TAKE marker rows from the reference spreadsheet are not shown here — see note in the review above about wiring this to a real stocktake record if one exists.
                </p>
            </div>
            </div><!-- /boughtParts -->


            <!-- ══════════════════════════════════════════
                 TAB 1 – PURCHASING
            ══════════════════════════════════════════ -->
            <div class="tab-pane fade <?= $red == 1 ? 'active show' : '' ?>" id="rawMaterials" role="tabpanel">
            <div class="rpt-wrap">
                <h2>Purchasing – <?= $year ?></h2>
                <div class="table-responsive">
                <table class="rpt-table" style="max-width:700px;">
                    <thead>
                        <tr><th></th><th>RMPL</th><th>RMPU</th><th>BOP</th><th>TOTAL</th><th>%</th></tr>
                    </thead>
                    <tbody>
                    <?php foreach ($purchasing_rows as $r): ?>
                        <tr>
                            <td><?= $r['label'] ?></td>
                            <td><?= fmt_gbp2($r['rmpl'], true) ?></td>
                            <td><?= fmt_gbp2($r['rmpu'], true) ?></td>
                            <td><?= fmt_gbp2($r['bop'], true) ?></td>
                            <td class="highlight"><?= fmt_gbp2($r['total'], true) ?></td>
                            <td><?= fmt_pct($r['pct'], $r['total'] > 0) ?></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                    <tfoot>
                        <tr class="totals-row">
                            <td colspan="6" style="text-align:center;background:#dde8f9!important;">
                                Purchasing (<?= $t6_label_from ?> – <?= $t6_label_to ?>)
                            </td>
                        </tr>
                        <tr class="totals-row">
                            <td>TOTAL</td>
                            <td><?= fmt_gbp2($gt_rmpl) ?></td>
                            <td><?= fmt_gbp2($gt_rmpu) ?></td>
                            <td><?= fmt_gbp2($gt_bop) ?></td>
                            <td><?= fmt_gbp2($gt_total) ?></td>
                            <td><?= number_format($gt_pct, 0) ?>%</td>
                        </tr>
                    </tfoot>
                </table>
                </div>
                <p style="font-size:11px;color:#888;margin-top:10px;">
                    * BOP combines any of the codes 'BOP', 'BO', 'BI' found in product_group — please confirm which codes your data actually uses.
                    % = that month's Purchasing Total ÷ that month's Production Total (Tab 0), matching the reference figures exactly.
                </p>
            </div>
            </div><!-- /rawMaterials -->


            <!-- ══════════════════════════════════════════
                 TAB 2 – PRODUCTIVITY BY SECTION
            ══════════════════════════════════════════ -->
            <div class="tab-pane fade <?= $red == 2 ? 'active show' : '' ?>" id="productvity" role="tabpanel">
            <div class="rpt-wrap">
                <h2>PRODUCTIVITY BY SECTION – <?= $year ?></h2>
                <div class="table-responsive">
                <table class="rpt-table wide">
                    <thead>
                        <tr>
                            <th></th>
                            <?php foreach (['VAC','CNC','ASSY PL','PU ML','ASSY PU'] as $sec): ?>
                                <th colspan="3"><?= $sec ?></th>
                            <?php endforeach; ?>
                        </tr>
                        <tr>
                            <th></th>
                            <?php foreach (['VAC','CNC','ASSY PL','PU ML','ASSY PU'] as $sec): ?>
                                <th>Clock Hours</th><th>Std Hrs</th><th>Productivity</th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                    <?php foreach ($section_rows as $row): ?>
                        <tr>
                            <td><?= $row['label'] ?></td>
                            <?php foreach (['VAC','CNC','ASSY PL','PU ML','ASSY PU'] as $sec):
                                $v = $row['sections'][$sec];
                            ?>
                                <td><?= $v['clock'] > 0 ? number_format($v['clock'], 0) : '<span class="no-data">-</span>' ?></td>
                                <td><?= $v['std']   > 0 ? number_format($v['std'], 1)   : '<span class="no-data">-</span>' ?></td>
                                <td><?= $v['prod'] !== null ? number_format($v['prod'], 0) . '%' : '<span class="no-data">-</span>' ?></td>
                            <?php endforeach; ?>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
                </div>

                <h2 style="margin-top:30px;">AVERAGE BY SECTION</h2>
                <div class="table-responsive">
                <table class="rpt-table" style="max-width:1200px;">
                    <thead>
                        <tr>
                            <?php foreach (['VAC','CNC','ASSY PL','PU ML','ASSY PU'] as $sec): ?>
                                <th colspan="3"><?= $sec ?></th>
                            <?php endforeach; ?>
                        </tr>
                        <tr>
                            <?php foreach (['VAC','CNC','ASSY PL','PU ML','ASSY PU'] as $sec): ?>
                                <th>Clock Hours</th><th>Std Hrs</th><th>Productivity</th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <tr class="totals-row">
                            <?php foreach (['VAC','CNC','ASSY PL','PU ML','ASSY PU'] as $sec):
                                $t = $section_totals[$sec];
                                $n = count($section_rows) ?: 1;
                            ?>
                                <td><?= number_format($t['clock'] / $n, 0) ?></td>
                                <td><?= number_format($t['std']   / $n, 0) ?></td>
                                <td><?= $t['prod'] !== null ? number_format($t['prod'], 0) . '%' : '<span class="no-data">-</span>' ?></td>
                            <?php endforeach; ?>
                        </tr>
                    </tbody>
                </table>
                </div>
                <p style="font-size:11px;color:#888;margin-top:10px;">
                    * Average Clock Hours / Std Hrs are per-month averages over the displayed range; Productivity is sum(Std Hrs) ÷ sum(Clock Hours) across the whole range (not an average of the monthly percentages) — confirmed against the reference figures (VAC 6180/4261 = 145%, CNC 9630/8822 = 109%).
                    ASSY PU is not included in any org-wide STD/Clock Hours total elsewhere in the app — see the review note above this file.
                </p>
            </div>
            </div><!-- /productvity -->

        </div><!-- /.tab-content -->
        </div><!-- /.card-body -->
    </div><!-- /.card -->
</div><!-- /.col -->
</div></div><!-- /.container-fluid / .row -->
</section>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>