<?php
session_start();

$month = isset($_GET['month']) ? intval($_GET['month']) : date('n');
$year  = isset($_GET['year'])  ? intval($_GET['year'])  : date('Y');
$active_tab = isset($_GET['tab']) ? preg_replace('/[^a-z]/', '', $_GET['tab']) : 'sales';
$valid_tabs = ['sales','fgmovement','working','purchasing','graph'];
if (!in_array($active_tab, $valid_tabs, true)) { $active_tab = 'sales'; }
if ($month < 1 || $month > 12) { $month = date('n'); }

$today         = date('Y-m-d');
$current_month = date('n');
$current_year  = date('Y');

if ($month == $current_month && $year == $current_year) {
    $end_date    = $today;
    $current_day = date('j');
} else {
    $end_date    = date('Y-m-t', strtotime("$year-$month-01"));
    $current_day = date('j', strtotime($end_date));
}

/* ── yesterday clamped to selected month ── */
$yesterday_date = date('Y-m-d', strtotime('-1 day'));
if ($yesterday_date > $end_date) { $yesterday_date = $end_date; }

function countWorkingDays($start_date, $end_date) {
    $working_days = 0;
    $current = strtotime($start_date);
    $end     = strtotime($end_date);
    while ($current <= $end) {
        if (date('N', $current) < 6) { $working_days++; }
        $current = strtotime('+1 day', $current);
    }
    return $working_days;
}

$first_day           = "$year-$month-01";
$last_day            = date('Y-m-t', strtotime($first_day));
$total_working_days  = countWorkingDays($first_day, $last_day);
$working_days_elapsed= countWorkingDays($first_day, $end_date);
$month_key           = sprintf('%04d-%02d', $year, $month);

/* ── Sales Report MTD ── */
$kpi_sql    = "SELECT sales, working_days FROM monthly_kpi WHERE month_key='$month_key' LIMIT 1";
$kpi_result = mysqli_query($conn, $kpi_sql);
$monthly_plan = $kpi_working_days = 0;
if ($kpi_result && mysqli_num_rows($kpi_result) > 0) {
    $kpi_row          = mysqli_fetch_assoc($kpi_result);
    $monthly_plan     = floatval($kpi_row['sales']);
    $kpi_working_days = floatval($kpi_row['working_days']);
}
$sales_daily_target = ($kpi_working_days > 0) ? $monthly_plan / $kpi_working_days : 0;
$target_mtd         = $sales_daily_target * $working_days_elapsed;

$sales_sql    = "SELECT SUM(pp.req_qty * IFNULL(im.standard_cost_ebq,0)) AS actual_sales
                 FROM production_planning pp
                 LEFT JOIN item_management im ON im.id=pp.part_id
                 WHERE YEAR(pp.req_date)=$year AND MONTH(pp.req_date)=$month
                   AND pp.req_date<='$end_date'";
$sales_result = mysqli_query($conn, $sales_sql);
$sales_row    = mysqli_fetch_assoc($sales_result);
$actual_mtd   = floatval($sales_row['actual_sales']);
$variance     = $actual_mtd - $target_mtd;

/* ── Graph data ── */
$days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$production_daily_target = $SALES_DAILY_TARGET = $FG_DAILY_TARGET = 0;
$STD_HOURS_TARGET = $CLOCK_HOURS_TARGET = 0;

$sql    = "SELECT sales,fg_movement,rev_per_std_hours,working_days,no_of_directs,no_of_tpl_directs
           FROM monthly_kpi WHERE month_key='$month_key' LIMIT 1";
$result = mysqli_query($conn, $sql);
if ($result && mysqli_num_rows($result) > 0) {
    $row               = mysqli_fetch_assoc($result);
    $sales             = (float)$row['sales'];
    $fg_movement       = (float)$row['fg_movement'];
    $rev_per_std_hours = (float)$row['rev_per_std_hours'];
    $working_days      = (float)$row['working_days'];
    $no_of_directs = (float)$row['no_of_directs'];
    if ($working_days > 0) {
        $production_daily_target = ($sales + $fg_movement) / $working_days;
        $SALES_DAILY_TARGET      = $sales / $working_days;
        $FG_DAILY_TARGET         = $fg_movement / $working_days;
    }
    if ($rev_per_std_hours > 0 && $working_days > 0) {
        $STD_HOURS_TARGET = (($sales + $fg_movement) / $rev_per_std_hours) / $working_days;
    }
    if ($working_days > 0 && $no_of_directs > 0) {
        $B22 = $working_days * 9.5 * $no_of_directs;
        $CLOCK_HOURS_TARGET = ($B22 - 0.03*$B22 - 0.013*$B22) / $working_days;
    }
}

/* ── Production data for charts ── */
$production_sql    = "SELECT DAY(record_date) as day, DATE_FORMAT(record_date,'%d-%b') as date_label,
                             DAYNAME(record_date) as day_name, fg_difference
                      FROM daily_production_summary
                      WHERE MONTH(record_date)=$month AND YEAR(record_date)=$year ORDER BY record_date ASC";
$production_result = mysqli_query($conn, $production_sql);
$production_data   = [];
while ($row = mysqli_fetch_assoc($production_result)) { $production_data[$row['day']] = $row; }

$sales_graph_sql    = "SELECT DAY(pp.req_date) as day, DATE_FORMAT(pp.req_date,'%d-%b') as date_label,
    DAYNAME(pp.req_date) as day_name,
    SUM((IFNULL(pp.req_qty,0)+IFNULL(pp.shipped_qty,0))*
        CASE WHEN pp.item_price IS NOT NULL AND pp.item_price<>'' AND pp.item_price>0
             THEN pp.item_price ELSE IFNULL(im.standard_cost_ebq,0) END) AS daily_sales
    FROM production_planning pp LEFT JOIN item_management im ON im.id=pp.part_id
    WHERE MONTH(pp.req_date)=$month AND YEAR(pp.req_date)=$year
    GROUP BY DAY(pp.req_date),DATE_FORMAT(pp.req_date,'%d-%b'),DAYNAME(pp.req_date)
    ORDER BY pp.req_date ASC";
$sales_graph_result = mysqli_query($conn, $sales_graph_sql);
$sales_graph_data   = [];
while ($row = mysqli_fetch_assoc($sales_graph_result)) { $sales_graph_data[$row['day']] = $row; }

$stock_sql    = "SELECT DAY(record_date) as day,DATE_FORMAT(record_date,'%d-%b') as date_label,
                        fg,rm,vac_wip,cnc_wip,pu_wip
                 FROM daily_production_summary
                 WHERE MONTH(record_date)=$month AND YEAR(record_date)=$year ORDER BY record_date ASC";
$stock_result = mysqli_query($conn, $stock_sql);
$stock_data   = [];
while ($row = mysqli_fetch_assoc($stock_result)) { $stock_data[$row['day']] = $row; }

$production_chart_data = ['labels'=>[],'target'=>[],'actual'=>[]];
$sales_chart_data      = ['labels'=>[],'target'=>[],'actual'=>[]];
$warehouse_chart_data  = ['labels'=>[],'fg'=>[],'rm'=>[],'vac_wip'=>[],'cnc_wip'=>[],'pu_wip'=>[]];
$overall_chart_data    = ['labels'=>[],'total'=>[]];
$production_cumv_target = $production_cumv_actual = 0;
$sales_cumv_target      = $sales_cumv_actual = 0;
$latest_total = 0;

/* Only accumulate TARGET lines up to today for the current month, so the
   graph doesn't draw a target line running into future, not-yet-happened days. */
$is_selected_month_current = ($month == $current_month && $year == $current_year);
$target_cutoff_day = $is_selected_month_current ? intval($current_day) : $days_in_month;

for ($day = 1; $day <= $days_in_month; $day++) {
    $ts         = strtotime("$year-$month-$day");
    $day_name   = date('l', $ts);
    $date_label = sprintf('%02d-%s', $day, date('M', $ts));
    $is_weekend = in_array($day_name, ['Saturday','Sunday']);

    $prod        = $production_data[$day] ?? null;
    $prod_actual = $prod ? floatval($prod['fg_difference']) : 0;
    if ($day <= $target_cutoff_day) {
        $production_cumv_target += $is_weekend ? 0 : $production_daily_target;
    }
    $production_cumv_actual += $prod_actual;
    $production_chart_data['labels'][] = $date_label;
    $production_chart_data['target'][] = ($day <= $target_cutoff_day || !$is_selected_month_current) ? $production_cumv_target : null;
    $production_chart_data['actual'][] = $prod ? $production_cumv_actual : ($day <= $target_cutoff_day ? $production_cumv_actual : null);

    $sal          = $sales_graph_data[$day] ?? null;
    $sales_actual = $sal ? floatval($sal['daily_sales']) : 0;
    if ($day <= $target_cutoff_day) {
        $sales_cumv_target += $is_weekend ? 0 : $SALES_DAILY_TARGET;
    }
    $sales_cumv_actual += $sales_actual;
    $sales_chart_data['labels'][] = $date_label;
    $sales_chart_data['target'][] = ($day <= $target_cutoff_day || !$is_selected_month_current) ? $sales_cumv_target : null;
    $sales_chart_data['actual'][] = $sal ? $sales_cumv_actual : ($day <= $target_cutoff_day ? $sales_cumv_actual : null);

    $stock  = $stock_data[$day] ?? null;
    $fg     = $stock ? floatval($stock['fg'])      : 0;
    $rm     = $stock ? floatval($stock['rm'])      : 0;
    $vac_wip= $stock ? floatval($stock['vac_wip']) : 0;
    $cnc_wip= $stock ? floatval($stock['cnc_wip']) : 0;
    $pu_wip = $stock ? floatval($stock['pu_wip'])  : 0;
    $total  = $fg + $rm + $vac_wip + $cnc_wip + $pu_wip;
    if ($total > 0) { $latest_total = $total; }
    $warehouse_chart_data['labels'][]  = $date_label;
    $warehouse_chart_data['fg'][]      = $fg      > 0 ? $fg      : null;
    $warehouse_chart_data['rm'][]      = $rm      > 0 ? $rm      : null;
    $warehouse_chart_data['vac_wip'][] = $vac_wip > 0 ? $vac_wip : null;
    $warehouse_chart_data['cnc_wip'][] = $cnc_wip > 0 ? $cnc_wip : null;
    $warehouse_chart_data['pu_wip'][]  = $pu_wip  > 0 ? $pu_wip  : null;
    $overall_chart_data['labels'][]    = $date_label;
    $overall_chart_data['total'][]     = $total   > 0 ? $total   : null;
}

$month_name = date('F Y', strtotime("$year-$month-01"));
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Sales Progress Report - <?php echo $month_name; ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/toastify-js/src/toastify.min.css">
    <style>
        *{box-sizing:border-box}
        body{font-family:Arial,sans-serif;background-color:#f5f5f5;padding:20px}
        em{color:red}
        .card.card-purple .card-header{background-color:#9c4893;color:white}
        .status-box{display:inline-block;padding:5px 10px;border-radius:5px;color:white;font-size:13px;font-weight:bold;margin-right:5px}
        .Complete{background-color:green}.inprogress{background-color:#9c4893;color:white}.Incomplete{background-color:#e31414}
        .progress-icon{display:inline-block;width:10px;height:10px;border-radius:50%;margin-left:5px}
        .progress-icon.red{background-color:red}.progress-icon.yellow{background-color:#9c4893}.progress-icon.green{background-color:green}
        .month-selector{margin-bottom:30px;padding:15px;background-color:#f8f9fa;border-radius:5px}
        .month-selector label{margin-right:10px;font-weight:bold}
        .month-selector select{padding:5px 10px;margin-right:10px;border:1px solid #ccc;border-radius:4px}
        .month-selector button{padding:6px 15px;background-color:#007bff;color:white;border:none;border-radius:4px;cursor:pointer}
        .month-selector button:hover{background-color:#0056b3}
        .info-box{background-color:#e8f4f8;padding:15px;margin-bottom:20px;border-radius:5px;text-align:center}
        .info-box p{margin:5px 0;font-size:14px}
        .sales-table{width:100%;border-collapse:collapse;margin-top:20px}
        .sales-table th,.sales-table td{border:1px solid #ddd;padding:15px;text-align:center;font-size:16px}
        .sales-table th{background-color:#4472C4;color:white;font-weight:bold}
        .sales-table td:first-child{text-align:left;font-weight:bold;background-color:#f8f9fa;font-size:18px}
        .plan-col{background-color:#fff2cc;font-weight:bold;font-size:18px}
        .target-col{background-color:#ffffff;font-size:18px}
        .actual-col{background-color:#ffffff;font-size:18px}
        .variance-positive{background-color:#c6efce;color:#006100;font-weight:bold;font-size:18px}
        .variance-negative{background-color:#ffc7ce;color:#9c0006;font-weight:bold;font-size:18px}
        .charts-container{display:grid;grid-template-columns:repeat(auto-fit,minmax(600px,1fr));gap:30px;margin-top:30px}
        .chart-wrapper{background:linear-gradient(180deg,#f3e5f5 0%,#e1bee7 100%);padding:20px;border-radius:8px;box-shadow:0 2px 8px rgba(0,0,0,.1)}
        .chart-header{display:flex;justify-content:space-between;align-items:center;margin-bottom:15px}
        .chart-title{color:#6a1b9a;font-size:20px;font-weight:bold}
        .chart-value{color:#6a1b9a;font-size:18px;font-weight:bold}
        .chart-canvas{background-color:white;padding:15px;border-radius:5px;width:100%;height:300px;position:relative}
        .section-title{color:#4a148c;font-size:26px;text-align:center;margin:40px 0 20px;padding-bottom:10px;border-bottom:2px solid #e1bee7}
        @media(max-width:768px){.charts-container{grid-template-columns:1fr}.chart-canvas{height:250px}}
    </style>
</head>
<body>

<?php if(isset($_SESSION['info_message'])): ?>
    <script src="https://cdn.jsdelivr.net/npm/toastify-js"></script>
    <script>
    document.addEventListener('DOMContentLoaded',function(){
        Toastify({text:"<?php echo htmlspecialchars($_SESSION['info_message'], ENT_QUOTES);?>",duration:2000,close:true,gravity:"top",
            position:'right',backgroundColor:"#28a745",style:{borderBottom:"3px solid white"}}).showToast();
    });
    </script>
    <?php unset($_SESSION['info_message']); ?>
<?php endif; ?>

<section class="content-header">
    <div class="container-fluid">
        <div class="row"><div class="col-sm-8">
            <h1 style="margin-top:0;padding-bottom:0;">View Sales Progress</h1>
        </div></div>
    </div>
</section>

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

    <!-- Month/Year Filter -->
    <div class="card mb-3"><div class="card-body">
        <div class="month-selector">
            <form method="GET" action="index" id="filterForm">
                <input type="hidden" name="page" value="view_sales_purchasing">
                <label for="month">Month:</label>
                <select name="month" id="month">
                    <?php for($m=1;$m<=12;$m++): ?>
                    <option value="<?php echo $m;?>" <?php echo $m==$month?'selected':'';?>>
                        <?php echo date('F',mktime(0,0,0,$m,1));?>
                    </option>
                    <?php endfor; ?>
                </select>
                <label for="year">Year:</label>
                <select name="year" id="year">
                    <?php for($y=date('Y')-2;$y<=date('Y')+2;$y++): ?>
                    <option value="<?php echo $y;?>" <?php echo $y==$year?'selected':'';?>><?php echo $y;?></option>
                    <?php endfor; ?>
                </select>
                <input type="hidden" name="tab" id="currentTab" value="<?php echo htmlspecialchars($active_tab, ENT_QUOTES);?>">
                <button type="submit" class="btn btn-primary">Apply Filter</button>
            </form>
        </div>
    </div></div>

    <!-- Tabs -->
    <div class="card card-purple card-tabs">
        <div class="card-header p-0 pt-1">
            <ul class="nav nav-tabs" id="salesProgressTabs" role="tablist">
                <?php $tab_defs=[
                    ['sales','salesReport','Sales Report'],
                    ['fgmovement','fgMovement','FG Movement'],
                    ['working','workingDays','Working Days'],
                    ['purchasing','purchasing','Purchasing'],
                    ['graph','graph','Graph'],
                ];
                foreach($tab_defs as $td): ?>
                <li class="nav-item">
                    <a class="nav-link <?php echo $active_tab==$td[0]?'active':'';?>"
                       id="<?php echo $td[0];?>-tab" data-bs-toggle="tab"
                       href="#<?php echo $td[1];?>" role="tab"
                       aria-selected="<?php echo $active_tab==$td[0]?'true':'false';?>">
                        <?php echo $td[2];?>
                    </a>
                </li>
                <?php endforeach; ?>
            </ul>
        </div>

        <div class="card-body">
        <div class="tab-content" id="salesProgressTabContent">

        <!-- ═══════════════════════════════════════
             SALES REPORT TAB
        ═══════════════════════════════════════ -->
        <div class="tab-pane fade <?php echo $active_tab=='sales'?'show active':'';?>"
             id="salesReport" role="tabpanel">
            <h2 class="text-center mb-4">SALES TARGET REPORT - <?php echo strtoupper($month_name);?></h2>
            <div class="info-box">
                <p><strong>Reporting Period:</strong> <?php echo date('d M Y',strtotime($first_day));?> to <?php echo date('d M Y',strtotime($end_date));?></p>
                <p><strong>Total Working Days in Month:</strong> <?php echo $total_working_days;?> days (Monday-Friday)</p>
                <p><strong>Working Days Elapsed:</strong> <?php echo $working_days_elapsed;?> days</p>
            </div>
            <table class="sales-table">
                <thead><tr><th></th><th>Plan</th><th>Target<br>Month-to-Date</th><th>Actual Month-to-Date</th><th>Variance</th></tr></thead>
                <tbody>
                    <tr>
                        <td>Sales</td>
                        <td class="plan-col">£ <?php echo number_format($monthly_plan,2);?></td>
                        <td class="target-col">£ <?php echo number_format($target_mtd,2);?></td>
                        <td class="actual-col">£ <?php echo number_format($actual_mtd,2);?></td>
                        <td class="<?php echo $variance>=0?'variance-positive':'variance-negative';?>">
                            <?php echo ($variance>=0?'':'-').'£ '.number_format(abs($variance),2);?>
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>

        <!-- ═══════════════════════════════════════
             FG MOVEMENT TAB
        ═══════════════════════════════════════ -->
        <div class="tab-pane fade <?php echo $active_tab=='fgmovement'?'show active':'';?>"
             id="fgMovement" role="tabpanel">
<?php
/* ══════════════════════════════════════════════
   FG MOVEMENT — DATA
══════════════════════════════════════════════ */

/* ── KPI plans ── */
$fg_kpi_sql = "SELECT sales,fg_movement,rev_per_std_hours,working_days,no_of_tpl_directs,no_of_directs
               FROM monthly_kpi WHERE month_key='$month_key' LIMIT 1";
$fg_kpi_res = mysqli_query($conn, $fg_kpi_sql);
$fg_kpi     = $fg_kpi_res ? mysqli_fetch_assoc($fg_kpi_res) : null;

$plan_sales        = $fg_kpi ? floatval($fg_kpi['sales'])            : 0;
$plan_fg           = $fg_kpi ? floatval($fg_kpi['fg_movement'])       : 0;
$plan_rev_std      = $fg_kpi ? floatval($fg_kpi['rev_per_std_hours']) : 0;
$plan_working_days = $fg_kpi ? floatval($fg_kpi['working_days'])      : 0;
$plan_tpl_directs  = $fg_kpi ? floatval($fg_kpi['no_of_directs']) : 0;

$plan_prod_no_wip  = $plan_sales + $plan_fg;
$plan_std_hours    = ($plan_rev_std > 0) ? $plan_prod_no_wip / $plan_rev_std : 0;
$plan_scrap        = $plan_prod_no_wip * 0.008;

/* ── Working Days plan calculations ── */
$plan_hrs_per_direct  = $plan_working_days * 9.5;
$plan_labour_avail    = $plan_hrs_per_direct * $plan_tpl_directs;
$plan_absenteeism     = $plan_labour_avail * 0.03;
$plan_holiday         = $plan_labour_avail * 0.013;
$plan_clock_hrs       = $plan_labour_avail - $plan_absenteeism - $plan_holiday;
$plan_labour_prod     = ($plan_clock_hrs > 0) ? ($plan_std_hours / $plan_clock_hrs) * 100 : 0;

/* ── FG daily target divisor = monthly_kpi.working_days ── */
function fg_daily_tgt2($plan, $kpi_wd) {
    return ($kpi_wd > 0) ? $plan / $kpi_wd : 0;
}
$tgt_fg_daily    = fg_daily_tgt2($plan_fg,         $plan_working_days);
$tgt_sales_daily = fg_daily_tgt2($plan_sales,       $plan_working_days);
$tgt_std_daily   = fg_daily_tgt2($plan_std_hours,   $plan_working_days);

$tgt_fg_mtd      = $tgt_fg_daily    * $working_days_elapsed;   // FG Movement Target MTD
$tgt_sales_mtd   = $tgt_sales_daily * $working_days_elapsed;   // Sales Target MTD

function countWorkingDaysMThu($start, $end) {
    $c = 0; $cur = strtotime($start); $e = strtotime($end);
    while ($cur <= $e) { $dow = date('N',$cur); if ($dow < 5) $c++; $cur = strtotime('+1 day',$cur); }
    return $c;
}
$prod_wd_elapsed = countWorkingDaysMThu($first_day, $yesterday_date);

$prod_daily_tgt_val = ($plan_working_days > 0) ? $plan_prod_no_wip / $plan_working_days : 0;
$tgt_prod_no_wip    = $prod_daily_tgt_val * $prod_wd_elapsed;

/* ── Actual Sales MTD ── */
$act_sales_mtd = $actual_mtd;

/* ── Actual FG Movement MTD = SUM(fg_difference) up to yesterday ── */
$fg_act_sql = "SELECT IFNULL(SUM(fg_difference),0) AS fg_act
               FROM daily_production_summary
               WHERE MONTH(record_date)=$month AND YEAR(record_date)=$year
                 AND record_date<='$yesterday_date'";
$fg_act_res  = mysqli_query($conn, $fg_act_sql);
$fg_act_row  = mysqli_fetch_assoc($fg_act_res);
$act_fg_mtd  = floatval($fg_act_row['fg_act']);

/* ── WIP Movement = WIP Snapshot (yesterday) − Month Opening WIP (day 1) ── */
$wip_snap_sql = "SELECT IFNULL(vac_wip,0)+IFNULL(cnc_wip,0)+IFNULL(pu_wip,0) AS wip_total
                 FROM daily_production_summary WHERE record_date='$yesterday_date' LIMIT 1";
$wip_snap_res = mysqli_query($conn, $wip_snap_sql);
$wip_snap_row = $wip_snap_res ? mysqli_fetch_assoc($wip_snap_res) : null;
$wip_yesterday = $wip_snap_row ? floatval($wip_snap_row['wip_total']) : 0;

$wip_open_date = sprintf('%04d-%02d-01', $year, $month);
$wip_open_sql  = "SELECT IFNULL(vac_wip,0)+IFNULL(cnc_wip,0)+IFNULL(pu_wip,0) AS wip_total
                  FROM daily_production_summary WHERE record_date='$wip_open_date' LIMIT 1";
$wip_open_res  = mysqli_query($conn, $wip_open_sql);
$wip_open_row  = $wip_open_res ? mysqli_fetch_assoc($wip_open_res) : null;
$wip_month_open = $wip_open_row ? floatval($wip_open_row['wip_total']) : 0;

$act_wip_mtd = $wip_yesterday - $wip_month_open;

/* ── Production Total Without WIP (actual) ── */
$act_prod_no_wip   = $act_sales_mtd + $act_fg_mtd;
$act_prod_with_wip = $act_prod_no_wip + $act_wip_mtd;

$plan_scrap_val = $plan_prod_no_wip * 0.008;
$tgt_scrap_val  = $tgt_prod_no_wip  * 0.008;

$scrap_sql = "
    SELECT ROUND(SUM(scw.weight_kg * sp.price_per_kg),2) AS total_value
    FROM scrap_collections sc
    JOIN scrap_collection_weights scw ON scw.collection_id = sc.id
    JOIN scrap_prices sp ON sp.material_slug = scw.material_slug
        AND sp.id = (
            SELECT id FROM scrap_prices sp2
            WHERE sp2.material_slug = scw.material_slug
              AND DATE(sp2.updated_at) <= sc.collection_date
            ORDER BY sp2.id DESC LIMIT 1
        )
    WHERE YEAR(sc.collection_date) = $year AND MONTH(sc.collection_date) = $month
";
$scrap_res = mysqli_query($conn, $scrap_sql);
$scrap_row = $scrap_res ? mysqli_fetch_assoc($scrap_res) : null;
$act_scrap = $scrap_row ? floatval($scrap_row['total_value']) : 0;
$var_scrap = $act_scrap - $tgt_scrap_val;

$cumv_std_target = 0;
$cumv_std_actual = 0;

$std_daily_sql = "
    SELECT DAY(record_date) AS day, DAYNAME(record_date) AS day_name,
           IFNULL(std_hours_vac,0)+IFNULL(std_hours_cnc,0)+IFNULL(std_hours_pu,0)+IFNULL(std_hours_assbly,0) AS std_act
    FROM daily_production_summary
    WHERE MONTH(record_date)=$month AND YEAR(record_date)=$year
    ORDER BY record_date ASC
";
$std_daily_res = mysqli_query($conn, $std_daily_sql);
$std_daily_data = [];
while ($r = mysqli_fetch_assoc($std_daily_res)) { $std_daily_data[$r['day']] = $r; }

$yesterday_day_num = intval(date('j', strtotime($yesterday_date)));
for ($d = 1; $d <= $days_in_month; $d++) {
    if ($d > $yesterday_day_num) break;
    $ds       = sprintf('%04d-%02d-%02d', $year, $month, $d);
    $dname    = date('l', strtotime($ds));
    $is_wknd  = in_array($dname, ['Saturday','Sunday']);
    $daily_act= isset($std_daily_data[$d]) ? floatval($std_daily_data[$d]['std_act']) : 0;
    if (!$is_wknd)      { $cumv_std_target += $STD_HOURS_TARGET; }
    if ($daily_act > 0) { $cumv_std_actual  += $daily_act; }
}

$cumv_clock_target = 0;
$cumv_clock_actual = 0;

$clock_daily_sql = "
    SELECT DAY(record_date) AS day, DAYNAME(record_date) AS day_name,
           IFNULL(clock_hours_vac,0)+IFNULL(clock_hours_cnc,0)+IFNULL(clock_hours_pu,0) AS clock_act
    FROM daily_production_summary
    WHERE MONTH(record_date)=$month AND YEAR(record_date)=$year
    ORDER BY record_date ASC
";
$clock_daily_res  = mysqli_query($conn, $clock_daily_sql);
$clock_daily_data = [];
while ($r = mysqli_fetch_assoc($clock_daily_res)) { $clock_daily_data[$r['day']] = $r; }

for ($d = 1; $d <= $days_in_month; $d++) {
    if ($d > $yesterday_day_num) break;
    $ds        = sprintf('%04d-%02d-%02d', $year, $month, $d);
    $dname     = date('l', strtotime($ds));
    $is_wknd   = in_array($dname, ['Friday','Saturday','Sunday']);
    $daily_act = isset($clock_daily_data[$d]) ? floatval($clock_daily_data[$d]['clock_act']) : 0;
    if (!$is_wknd)      { $cumv_clock_target += $CLOCK_HOURS_TARGET; }
    if ($daily_act > 0) { $cumv_clock_actual  += $daily_act; }
}

$act_rev_std = ($cumv_std_actual > 0) ? $act_prod_no_wip / $cumv_std_actual : 0;
$var_rev_std = $act_rev_std - $plan_rev_std;

$var_fg           = $act_fg_mtd      - $tgt_fg_mtd;
$var_prod_no_wip  = $act_prod_no_wip - $tgt_prod_no_wip;
$var_wip          = $act_wip_mtd;
$var_prod_w_wip   = $var_prod_no_wip + $act_wip_mtd;

$var_std          = $cumv_std_actual   - $cumv_std_target;
$var_clock        = $cumv_clock_actual - $cumv_clock_target;
$act_labour_prod  = ($cumv_clock_actual > 0) ? ($cumv_std_actual / $cumv_clock_actual) * 100 : 0;
$var_labour_prod  = $act_labour_prod - $plan_labour_prod;

$var_std_row = $cumv_std_actual - $cumv_std_target;

function fg_row2($label, $plan, $tgt, $act, $var,
                 $is_currency=true, $bold=false, $border_top=false,
                 $plan_dec=2, $other_dec=2, $is_pct=false) {
    $style = $border_top ? ' style="border-top:2px solid #555;"' : '';
    $b_o = $bold ? '<strong>' : '';
    $b_c = $bold ? '</strong>' : '';
    $fmt = function($v, $dec, $cur, $pct) {
        if ($v === null) return '<span style="color:#999">-</span>';
        $prefix = $cur ? '£ ' : '';
        $suffix = $pct ? '%' : '';
        $sign   = $v < 0 ? '-' : '';
        return $sign . $prefix . number_format(abs($v), $dec) . $suffix;
    };
    $var_cls = ($var === null) ? '' : ($var >= 0 ? 'variance-positive' : 'variance-negative');
    $var_str = ($var === null) ? '<span style="color:#999">-</span>'
                               : $fmt($var, $other_dec, $is_currency, $is_pct);
    echo "<tr$style>
        <td>$b_o$label$b_c</td>
        <td class='plan-col'>"   . ($plan===null?'<span style="color:#999">-</span>':$fmt($plan,$plan_dec,$is_currency,$is_pct)) . "</td>
        <td class='target-col'>" . ($tgt===null ?'<span style="color:#999">-</span>':$fmt($tgt,$other_dec,$is_currency,$is_pct))  . "</td>
        <td class='actual-col'>" . ($act===null ?'<span style="color:#999">-</span>':$fmt($act,$other_dec,$is_currency,$is_pct))  . "</td>
        <td class='$var_cls'>$var_str</td>
    </tr>";
}
?>

    <h2 class="text-center mb-4">FG MOVEMENT REPORT – <?php echo strtoupper($month_name);?></h2>

    <div class="info-box">
        <p><strong>Reporting Period:</strong> <?php echo date('d M Y',strtotime($first_day));?> to <?php echo date('d M Y',strtotime($end_date));?></p>
        <p><strong>WIP Snapshot Date:</strong> <?php echo date('d M Y',strtotime($yesterday_date));?> &nbsp;|&nbsp;
           <strong>Month Opening WIP Date:</strong> <?php echo date('d M Y',strtotime($wip_open_date));?></p>
        <p><strong>Total Working Days in Month:</strong> <?php echo $total_working_days;?> days (Monday–Friday) &nbsp;|&nbsp;
           <strong>Working Days Elapsed:</strong> <?php echo $working_days_elapsed;?> days</p>
    </div>

    <div style="overflow-x:auto;">
    <table class="sales-table" style="min-width:750px;">
        <thead>
            <tr>
                <th style="text-align:left;width:30%"></th>
                <th>Plan</th>
                <th>Target MTD</th>
                <th>Actual MTD</th>
                <th>Variance</th>
            </tr>
        </thead>
        <tbody>
        <?php
        fg_row2('FG Movement',
            $plan_fg, $tgt_fg_mtd, $act_fg_mtd, $var_fg);

        echo "<tr>
            <td>WIP Movement
                <br><small style='color:#777;font-weight:normal;'>
                    WIP Snapshot (".date('d M',strtotime($yesterday_date)).")
                    − Month Opening (".date('d M',strtotime($wip_open_date)).")
                </small>
            </td>
            <td class='plan-col'><span style='color:#999'>-</span></td>
            <td class='target-col'><span style='color:#999'>-</span></td>
            <td class='actual-col'>£&nbsp;".number_format($act_wip_mtd,2)."</td>
            <td class='".($act_wip_mtd>=0?'variance-positive':'variance-negative')."'>
                £&nbsp;".number_format($act_wip_mtd,2)."
            </td>
        </tr>";

        echo '<tr style="height:6px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        fg_row2('Production Total Without WIP',
            $plan_prod_no_wip, $tgt_prod_no_wip, $act_prod_no_wip, $var_prod_no_wip,
            true, true, true);

        echo "<tr>
            <td><strong>Production Total With WIP</strong></td>
            <td class='plan-col'><span style='color:#999'>-</span></td>
            <td class='target-col'><span style='color:#999'>-</span></td>
            <td class='actual-col'><span style='color:#999'>-</span></td>
            <td class='".($var_prod_w_wip>=0?'variance-positive':'variance-negative')."'>
                £&nbsp;".number_format($var_prod_w_wip,2)."
            </td>
        </tr>";

        echo '<tr style="height:10px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        fg_row2('Scrap Sales',
            $plan_scrap_val, $tgt_scrap_val, $act_scrap, $var_scrap);

        echo '<tr style="height:10px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        fg_row2('Revenue per Std Hour',
            $plan_rev_std, $plan_rev_std, $act_rev_std, $var_rev_std,
            true, false, false, 2, 2);

        echo '<tr><td colspan="5" style="font-size:11px;color:#777;border:none;padding:2px 8px;">* Based off 6 month Avg</td></tr>';

        echo '<tr style="height:10px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        fg_row2('Std Hours',
            $plan_std_hours, $cumv_std_target, $cumv_std_actual, $var_std_row,
            false, false, false, 0, 0);

        echo '<tr style="height:10px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        echo '<tr style="border-top:2px solid #4472C4;">
            <td colspan="5" style="background:#e8f0ff;font-weight:700;font-size:14px;padding:8px 15px;">
                Labour & Working Days
            </td>
        </tr>';

        echo "<tr>
            <td>Working Days</td>
            <td class='plan-col'>".number_format($plan_working_days,0)."</td>
            <td class='target-col'><span style='color:#999'>-</span></td>
            <td class='actual-col'><span style='color:#999'>-</span></td>
            <td><span style='color:#999'>-</span></td>
        </tr>";

        echo '<tr style="height:6px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        echo "<tr>
            <td>Hours Available per Direct</td>
            <td class='plan-col'>".number_format($plan_hrs_per_direct,0)."</td>
            <td class='target-col'><span style='color:#999'>-</span></td>
            <td class='actual-col'><span style='color:#999'>-</span></td>
            <td><span style='color:#999'>-</span></td>
        </tr>";

        echo '<tr style="height:6px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        echo "<tr>
            <td>No. of Directs<br><small style='color:#777;font-weight:normal;'>* Excludes day time Leading Hands x 5. Excludes Michael.</small></td>
            <td class='plan-col'>".number_format($plan_tpl_directs,0)."</td>
            <td class='target-col'><span style='color:#999'>-</span></td>
            <td class='actual-col'><span style='color:#999'>-</span></td>
            <td><span style='color:#999'>-</span></td>
        </tr>";

        echo '<tr style="height:6px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        echo "<tr>
            <td>Labour Hours Available</td>
            <td class='plan-col'>".number_format($plan_labour_avail,0)."</td>
            <td class='target-col'><span style='color:#999'>-</span></td>
            <td class='actual-col'><span style='color:#999'>-</span></td>
            <td><span style='color:#999'>-</span></td>
        </tr>";

        echo "<tr>
            <td>Absenteeism @ 3%</td>
            <td class='plan-col'>".number_format($plan_absenteeism,0)."</td>
            <td class='target-col'><span style='color:#999'>-</span></td>
            <td class='actual-col'><span style='color:#999'>-</span></td>
            <td><span style='color:#999'>-</span></td>
        </tr>";

        echo "<tr>
            <td>Holiday @ 1%</td>
            <td class='plan-col'>".number_format($plan_holiday,0)."</td>
            <td class='target-col'><span style='color:#999'>-</span></td>
            <td class='actual-col'><span style='color:#999'>-</span></td>
            <td><span style='color:#999'>-</span></td>
        </tr>";

        echo '<tr style="height:6px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        fg_row2('Clock Hours',
            $plan_clock_hrs, $cumv_clock_target, $cumv_clock_actual, $var_clock,
            false, true, true, 0, 0);

        echo '<tr style="height:6px;"><td colspan="5" style="border:none;background:#fff;"></td></tr>';

        fg_row2('Labour Productivity',
            $plan_labour_prod, null, $act_labour_prod, $var_labour_prod,
            false, true, false, 1, 1, true);
        ?>
        </tbody>
    </table>
    </div>
</div><!-- /fgMovement -->

        <!-- ═══════════════════════════════════════
             WORKING DAYS TAB
        ═══════════════════════════════════════ -->
        <div class="tab-pane fade <?php echo $active_tab=='working'?'show active':'';?>"
             id="workingDays" role="tabpanel">
<?php
$wd_kpi_sql = "SELECT working_days,no_of_directs,no_of_tpl_directs,sales,fg_movement,rev_per_std_hours
               FROM monthly_kpi WHERE month_key='$month_key' LIMIT 1";
$wd_kpi_res = mysqli_query($conn, $wd_kpi_sql);
$wd_kpi     = $wd_kpi_res ? mysqli_fetch_assoc($wd_kpi_res) : null;

$wd_working_days  = $wd_kpi ? floatval($wd_kpi['working_days'])      : 0;
$wd_tpl_directs   = $wd_kpi ? floatval($wd_kpi['no_of_directs']) : 0;
$wd_sales_plan    = $wd_kpi ? floatval($wd_kpi['sales'])             : 0;
$wd_fg_plan       = $wd_kpi ? floatval($wd_kpi['fg_movement'])       : 0;
$wd_rev_std       = $wd_kpi ? floatval($wd_kpi['rev_per_std_hours']) : 0;

$hrs_per_direct   = $wd_working_days * 9.5;
$labour_hrs_avail = $hrs_per_direct * $wd_tpl_directs;
$absenteeism      = $labour_hrs_avail * 0.03;
$holiday          = $labour_hrs_avail * 0.013;
$clock_hrs_plan   = $labour_hrs_avail - $absenteeism - $holiday;
$prod_plan_wd     = $wd_sales_plan + $wd_fg_plan;
$std_hrs_plan     = ($wd_rev_std > 0) ? $prod_plan_wd / $wd_rev_std : 0;
$labour_prod_plan = ($clock_hrs_plan > 0) ? ($std_hrs_plan / $clock_hrs_plan) * 100 : 0;

$ratio = ($wd_working_days > 0) ? $working_days_elapsed / $wd_working_days : 0;
$clock_hrs_tgt_mtd = $clock_hrs_plan * $ratio;
$std_hrs_tgt_mtd   = $std_hrs_plan   * $ratio;

$clock_act_sql = "SELECT IFNULL(SUM(clock_hours_vac+clock_hours_cnc+clock_hours_pu),0) AS clock_act
                  FROM daily_production_summary WHERE MONTH(record_date)=$month AND YEAR(record_date)=$year AND record_date<='$end_date'";
$clock_act_res = mysqli_query($conn,$clock_act_sql);
$clock_act_row = mysqli_fetch_assoc($clock_act_res);
$act_clock_mtd = floatval($clock_act_row['clock_act']);

$std_act_sql = "SELECT IFNULL(SUM(std_hours_vac+std_hours_cnc+std_hours_pu+std_hours_assbly),0) AS std_act
                FROM daily_production_summary WHERE MONTH(record_date)=$month AND YEAR(record_date)=$year AND record_date<='$end_date'";
$std_act_res = mysqli_query($conn,$std_act_sql);
$std_act_row = mysqli_fetch_assoc($std_act_res);
$act_std_mtd_wd = floatval($std_act_row['std_act']);

$act_labour_prod_wd = ($act_clock_mtd > 0) ? ($act_std_mtd_wd / $act_clock_mtd) * 100 : 0;

$var_clock_wd      = $act_clock_mtd     - $clock_hrs_tgt_mtd;
$var_std_wd        = $act_std_mtd_wd    - $std_hrs_tgt_mtd;
$var_labour_prod_wd= $act_labour_prod_wd - $labour_prod_plan;

function wd_row2($label,$plan,$tgt,$act,$var,$is_currency=false,$is_pct=false,$bold=false,$border_top=false,$decimals=0,$note='') {
    $style=$border_top?' style="border-top:2px solid #555;"':'';
    $b_o=$bold?'<strong>':''; $b_c=$bold?'</strong>':'';
    $fmt=function($v,$dec,$cur,$pct){
        if($v===null) return '<span style="color:#999">-</span>';
        $sign=$v<0?'-':''; $abs=abs($v);
        if($cur) return $sign.'£ '.number_format($abs,$dec);
        if($pct) return $sign.number_format($abs,$dec).'%';
        return $sign.number_format($abs,$dec);
    };
    $vc=($var===null)?'':($var>=0?'variance-positive':'variance-negative');
    $vs=($var===null)?'<span style="color:#999">-</span>':'<span>'.$fmt($var,$decimals,$is_currency,$is_pct).'</span>';
    echo "<tr$style><td>$b_o$label$b_c".($note?"<br><small style='color:#777;font-weight:normal;'>$note</small>":'')."</td>
        <td class='plan-col'>".$fmt($plan,$decimals,$is_currency,$is_pct)."</td>
        <td class='target-col'>".($tgt===null?'<span style="color:#999">-</span>':$fmt($tgt,$decimals,$is_currency,$is_pct))."</td>
        <td class='actual-col'>".($act===null?'<span style="color:#999">-</span>':$fmt($act,$decimals,$is_currency,$is_pct))."</td>
        <td class='$vc'>$vs</td></tr>";
}
?>
    <h2 class="text-center mb-4">WORKING DAYS REPORT – <?php echo strtoupper($month_name);?></h2>
    <div class="info-box">
        <p><strong>Reporting Period:</strong> <?php echo date('d M Y',strtotime($first_day));?> to <?php echo date('d M Y',strtotime($end_date));?></p>
        <p><strong>Total Working Days in Month:</strong> <?php echo $total_working_days;?> days (Monday–Friday) &nbsp;|&nbsp;
           <strong>Working Days Elapsed:</strong> <?php echo $working_days_elapsed;?> days</p>
    </div>
    <div style="overflow-x:auto;">
    <table class="sales-table" style="min-width:750px;">
        <thead><tr>
            <th style="text-align:left;width:32%"></th><th>Plan</th>
            <th>Target<br>Month-to-Date</th><th>Actual<br>Month-to-Date</th><th>Variance</th>
        </tr></thead>
        <tbody>
            <tr><td><strong>Working Days</strong></td>
                <td class="plan-col"><?php echo number_format($wd_working_days,0);?></td>
                <td class="target-col"><span style="color:#999">-</span></td>
                <td class="actual-col"><span style="color:#999">-</span></td>
                <td><span style="color:#999">-</span></td></tr>
            <tr><td colspan="5" style="border:none;background:#fff;height:14px;"></td></tr>
            <tr><td>Hours Available per Direct</td>
                <td class="plan-col"><?php echo number_format($hrs_per_direct,0);?></td>
                <td class="target-col"><span style="color:#999">-</span></td>
                <td class="actual-col"><span style="color:#999">-</span></td>
                <td><span style="color:#999">-</span></td></tr>
            <tr><td colspan="5" style="border:none;background:#fff;height:14px;"></td></tr>
            <tr><td>No. of Directs<br><small style="color:#777;">* Excludes day time Leading Hands x 5. Excludes Michael.</small></td>
                <td class="plan-col"><?php echo number_format($wd_tpl_directs,0);?></td>
                <td class="target-col"><span style="color:#999">-</span></td>
                <td class="actual-col"><span style="color:#999">-</span></td>
                <td><span style="color:#999">-</span></td></tr>
            <tr><td colspan="5" style="border:none;background:#fff;height:14px;"></td></tr>
            <tr><td>Labour Hours Available</td>
                <td class="plan-col"><?php echo number_format($labour_hrs_avail,0);?></td>
                <td class="target-col"><span style="color:#999">-</span></td>
                <td class="actual-col"><span style="color:#999">-</span></td>
                <td><span style="color:#999">-</span></td></tr>
            <tr><td>Absenteeism @ 3%</td>
                <td class="plan-col"><?php echo number_format($absenteeism,0);?></td>
                <td class="target-col"><span style="color:#999">-</span></td>
                <td class="actual-col"><span style="color:#999">-</span></td>
                <td><span style="color:#999">-</span></td></tr>
            <tr><td>Holiday @ 1%</td>
                <td class="plan-col"><?php echo number_format($holiday,0);?></td>
                <td class="target-col"><span style="color:#999">-</span></td>
                <td class="actual-col"><span style="color:#999">-</span></td>
                <td><span style="color:#999">-</span></td></tr>
            <tr><td colspan="5" style="border:none;background:#fff;height:14px;"></td></tr>
            <?php wd_row2('Clock Hours',$clock_hrs_plan,$clock_hrs_tgt_mtd,$act_clock_mtd,$var_clock_wd,false,false,true,true,0); ?>
            <tr><td colspan="5" style="border:none;background:#fff;height:14px;"></td></tr>
            <?php wd_row2('Labour Productivity',$labour_prod_plan,null,$act_labour_prod_wd,$var_labour_prod_wd,false,true,true,false,1); ?>
        </tbody>
    </table>
    </div>
</div><!-- /workingDays -->

        <!-- ═══════════════════════════════════════
             PURCHASING TAB
        ═══════════════════════════════════════ -->
        <div class="tab-pane fade <?php echo $active_tab=='purchasing'?'show active':'';?>" id="purchasing" role="tabpanel">
<?php
/* ══════════════════════════════════════════════
   PURCHASING — DATA
══════════════════════════════════════════════ */

/* NOTE: requires a `purchase_overspend` column on monthly_kpi:
   ALTER TABLE monthly_kpi ADD COLUMN purchase_overspend DECIMAL(12,2) NOT NULL DEFAULT 0; */
$pur_kpi_sql = "SELECT sales, fg_movement, purchase_overspend
                 FROM monthly_kpi WHERE month_key='$month_key' LIMIT 1";
$pur_kpi_res = mysqli_query($conn, $pur_kpi_sql);
$pur_kpi     = $pur_kpi_res ? mysqli_fetch_assoc($pur_kpi_res) : null;

$pur_plan_sales = $pur_kpi ? floatval($pur_kpi['sales'])              : 0;
$pur_plan_fg    = $pur_kpi ? floatval($pur_kpi['fg_movement'])        : 0;
$pur_overspend  = $pur_kpi && isset($pur_kpi['purchase_overspend']) ? floatval($pur_kpi['purchase_overspend']) : 0;

$plan_production_total = $pur_plan_sales + $pur_plan_fg;
$purchasing_target      = ($plan_production_total * 0.38) - $pur_overspend;

/* NOTE: adjust these product_group_name codes to match your `product_group` table */
$group_labels = [
    'BI'     => 'Bought In Parts',
    'BO'     => 'Bought Out Parts',
    'PUCON'  => 'Polyurathane Consumables',
    'CNCCON' => 'CNC Consumables',
    'ASBCON' => 'Assembly Consumables',
    'RMPU'   => 'Raw Material PU',
    'RMPL'   => 'Raw Material Plastics',
];
$group_totals = array_fill_keys(array_keys($group_labels), 0.0);

$group_codes_sql = "'" . implode("','", array_map(function($c) use ($conn) {
    return mysqli_real_escape_string($conn, $c);
}, array_keys($group_labels))) . "'";

$receive_totals_sql = "
    SELECT pg.product_group_name,
           SUM(ps.qty_received * poim.unit_price) AS group_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 00:00:00' AND '$end_date 23:59:59'
      AND pg.product_group_name IN ($group_codes_sql)
    GROUP BY pg.product_group_name
";
$receive_totals_res = mysqli_query($conn, $receive_totals_sql);
if ($receive_totals_res) {
    while ($row = mysqli_fetch_assoc($receive_totals_res)) {
        if (isset($group_totals[$row['product_group_name']])) {
            $group_totals[$row['product_group_name']] = floatval($row['group_total']);
        }
    }
}
$purchasing_actual_mtd = array_sum($group_totals);
$purchasing_remaining  = $purchasing_target - $purchasing_actual_mtd;
$purchasing_pct        = ($purchasing_target > 0) ? ($purchasing_actual_mtd / $purchasing_target) * 100 : 0;
?>
    <h2 class="text-center mb-4">PURCHASING REPORT – <?php echo strtoupper($month_name);?></h2>
    <div class="info-box">
        <p><strong>Reporting Period:</strong> <?php echo date('d M Y',strtotime($first_day));?> to <?php echo date('d M Y',strtotime($end_date));?></p>
    </div>

    <table class="sales-table">
        <thead>
            <tr>
                <th style="text-align:left;">Purchasing</th>
                <th>Plan</th>
                <th>Actual Month-to-Date</th>
                <th>Remaining Budget</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Target (38% of production)</td>
                <td class="plan-col">£ <?php echo number_format($purchasing_target,2);?></td>
                <td class="actual-col">£ <?php echo number_format($purchasing_actual_mtd,2);?></td>
                <td class="<?php echo $purchasing_remaining>=0?'variance-positive':'variance-negative';?>">
                    £ <?php echo number_format(abs($purchasing_remaining),2);?>
                </td>
            </tr>
            <tr>
                <td colspan="4" style="background:#fff;border:none;padding:8px 15px;">
                    <div style="background:#eee;border-radius:4px;overflow:hidden;height:18px;">
                        <div style="background:<?php echo $purchasing_pct<=100?'#9c4893':'#e31414';?>;height:100%;
                             width:<?php echo min($purchasing_pct,100);?>%;text-align:center;color:#fff;font-size:12px;line-height:18px;">
                            <?php echo number_format($purchasing_pct,0);?>%
                        </div>
                    </div>
                </td>
            </tr>
        </tbody>
    </table>

    <table class="sales-table" style="margin-top:25px;max-width:600px;">
        <tbody>
        <?php $i = 0; foreach ($group_labels as $key => $label):
            $shaded = ($i % 2 === 1) ? 'background-color:#f2f2f2;' : '';
            $i++;
        ?>
            <tr>
                <td style="text-align:left;<?php echo $shaded;?>"><?php echo strtoupper($label);?></td>
                <td style="<?php echo $shaded;?>">£ <?php echo number_format($group_totals[$key],2);?></td>
            </tr>
        <?php endforeach; ?>
            <tr>
                <td style="text-align:left;font-weight:bold;background-color:#fff2cc;">TOTAL</td>
                <td style="font-weight:bold;background-color:#fff2cc;">£ <?php echo number_format($purchasing_actual_mtd,2);?></td>
            </tr>
        </tbody>
    </table>
</div><!-- /purchasing -->

        <!-- ═══════════════════════════════════════
             GRAPH TAB
        ═══════════════════════════════════════ -->
        <div class="tab-pane fade <?php echo $active_tab=='graph'?'show active':'';?>" id="graph" role="tabpanel">
            <h2 class="section-title">Production Target & Sales Analysis - <?php echo $month_name;?></h2>
            <div class="charts-container">
                <div class="chart-wrapper">
                    <div class="chart-title">Production Target</div>
                    <div class="chart-canvas"><canvas id="productionChart"></canvas></div>
                </div>
                <div class="chart-wrapper">
                    <div class="chart-title">Sales Analysis</div>
                    <div class="chart-canvas"><canvas id="salesChart"></canvas></div>
                </div>
            </div>
            <h2 class="section-title">Stock Value Analysis - <?php echo $month_name;?></h2>
            <div class="charts-container">
                <div class="chart-wrapper">
                    <div class="chart-header">
                        <div class="chart-title">Stock Value by Warehouse</div>
                        <div class="chart-value">£<?php echo number_format($latest_total,2);?></div>
                    </div>
                    <div class="chart-canvas"><canvas id="warehouseChart"></canvas></div>
                </div>
                <div class="chart-wrapper">
                    <div class="chart-header"><div class="chart-title">Overall Stock Value</div></div>
                    <div class="chart-canvas"><canvas id="overallChart"></canvas></div>
                </div>
            </div>
        </div>

        </div><!-- /.tab-content -->
        </div><!-- /.card-body -->
    </div><!-- /.card -->
</div></div></div>
</section>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script>
document.querySelectorAll('#salesProgressTabs a[data-bs-toggle="tab"]').forEach(function(tab){
    tab.addEventListener('shown.bs.tab',function(e){
        var id=e.target.getAttribute('href').replace('#','');
        var map={salesReport:'sales',fgMovement:'fgmovement',workingDays:'working',purchasing:'purchasing',graph:'graph'};
        document.getElementById('currentTab').value=map[id]||'sales';
    });
});

/* ══════════════════════════════════════════════════════════════
   GRAPH TAB — CHART DATA & INITIALIZATION
   FIX: this used to be wrapped in `<?php if($active_tab=='graph') ?>`
   which meant the chart script only existed in the page's HTML when
   the URL itself already had ?tab=graph. Since tab switching happens
   entirely client-side (Bootstrap data-bs-toggle, no page reload),
   navigating to the Graph tab from any other tab found no chart
   script at all -> blank canvases, regardless of month selected.

   Fix: always emit the data + script. Charts are created lazily
   the first time the Graph tab is actually shown (avoids sizing a
   canvas while its parent is display:none), and simply .resize()'d
   on every subsequent visit to correct Chart.js's 0x0 measurement
   of a canvas that was hidden at creation time.
   ══════════════════════════════════════════════════════════════ */
const productionData={labels:<?php echo json_encode($production_chart_data['labels']);?>,datasets:[
    {label:'TARGET',data:<?php echo json_encode($production_chart_data['target']);?>,borderColor:'#9e9e9e',backgroundColor:'transparent',borderWidth:3,pointRadius:0,tension:0,spanGaps:true},
    {label:'ACTUAL',data:<?php echo json_encode($production_chart_data['actual']);?>,borderColor:'#4caf50',backgroundColor:'transparent',borderWidth:3,pointRadius:0,tension:0,spanGaps:true}
]};
const salesData={labels:<?php echo json_encode($sales_chart_data['labels']);?>,datasets:[
    {label:'TARGET',data:<?php echo json_encode($sales_chart_data['target']);?>,borderColor:'#9e9e9e',backgroundColor:'transparent',borderWidth:3,pointRadius:0,tension:0,spanGaps:true},
    {label:'ACTUAL',data:<?php echo json_encode($sales_chart_data['actual']);?>,borderColor:'#4caf50',backgroundColor:'transparent',borderWidth:3,pointRadius:0,tension:0,spanGaps:true}
]};
const warehouseData={labels:<?php echo json_encode($warehouse_chart_data['labels']);?>,datasets:[
    {label:'FG',data:<?php echo json_encode($warehouse_chart_data['fg']);?>,borderColor:'#2196F3',backgroundColor:'transparent',borderWidth:2.5,pointRadius:0,tension:.1,spanGaps:true},
    {label:'RM',data:<?php echo json_encode($warehouse_chart_data['rm']);?>,borderColor:'#f44336',backgroundColor:'transparent',borderWidth:2.5,pointRadius:0,tension:.1,spanGaps:true},
    {label:'VAC WIP',data:<?php echo json_encode($warehouse_chart_data['vac_wip']);?>,borderColor:'#4CAF50',backgroundColor:'transparent',borderWidth:2.5,pointRadius:0,tension:.1,spanGaps:true},
    {label:'CNC WIP',data:<?php echo json_encode($warehouse_chart_data['cnc_wip']);?>,borderColor:'#FFC107',backgroundColor:'transparent',borderWidth:2.5,pointRadius:0,tension:.1,spanGaps:true},
    {label:'PU WIP',data:<?php echo json_encode($warehouse_chart_data['pu_wip']);?>,borderColor:'#9C27B0',backgroundColor:'transparent',borderWidth:2.5,pointRadius:0,tension:.1,spanGaps:true}
]};
const overallData={labels:<?php echo json_encode($overall_chart_data['labels']);?>,datasets:[
    {label:'Actual',data:<?php echo json_encode($overall_chart_data['total']);?>,borderColor:'#FF9800',backgroundColor:'transparent',borderWidth:3,pointRadius:0,tension:.1,spanGaps:true}
]};

const chartCfg={type:'line',options:{responsive:true,maintainAspectRatio:false,animation:{duration:300},
    plugins:{legend:{display:true,position:'bottom',labels:{usePointStyle:true,padding:15,font:{size:12}}},
    tooltip:{mode:'index',intersect:false,callbacks:{label:function(c){let l=c.dataset.label||'';if(l)l+=': ';if(c.parsed.y!==null)l+='£'+c.parsed.y.toLocaleString('en-GB',{minimumFractionDigits:2,maximumFractionDigits:2});return l;}}}},
    scales:{x:{ticks:{maxRotation:45,minRotation:45,font:{size:10}},grid:{display:false}},
            y:{beginAtZero:true,ticks:{callback:function(v){return'£'+(v/1000000).toFixed(2)+'M';},font:{size:11}},grid:{color:'#e0e0e0'}}},
    interaction:{mode:'nearest',axis:'x',intersect:false}}};

let graphsInitialized = false;
let productionChartInstance, salesChartInstance, warehouseChartInstance, overallChartInstance;

function initGraphs(){
    if (graphsInitialized) {
        // Tab was hidden (display:none) when charts were first created, so
        // Chart.js measured 0x0. Re-measure now that the pane is visible.
        [productionChartInstance, salesChartInstance, warehouseChartInstance, overallChartInstance]
            .forEach(function(c){ if (c) c.resize(); });
        return;
    }
    const prodCanvas = document.getElementById('productionChart');
    if (!prodCanvas) return; // graph tab markup not on this page for some reason
    graphsInitialized = true;

    productionChartInstance = new Chart(prodCanvas.getContext('2d'), {...chartCfg, data: productionData});
    salesChartInstance      = new Chart(document.getElementById('salesChart').getContext('2d'), {...chartCfg, data: salesData});
    warehouseChartInstance  = new Chart(document.getElementById('warehouseChart').getContext('2d'), {...chartCfg, data: warehouseData});

    const overallCfg = JSON.parse(JSON.stringify(chartCfg));
    overallCfg.options.scales.y.min = 6800000;
    overallCfg.options.scales.y.max = 7800000;
    overallChartInstance = new Chart(document.getElementById('overallChart').getContext('2d'), {...overallCfg, data: overallData});
}

// If the page loaded with the Graph tab already active (e.g. ?tab=graph),
// build the charts as soon as the DOM is ready.
<?php if ($active_tab === 'graph'): ?>
document.addEventListener('DOMContentLoaded', initGraphs);
<?php endif; ?>

// Whenever the user clicks into the Graph tab (client-side, no reload),
// build the charts the first time, or just resize them on later visits.
const graphTabEl = document.getElementById('graph-tab');
if (graphTabEl) {
    graphTabEl.addEventListener('shown.bs.tab', initGraphs);
}
</script>
</body>
</html>