<?php
session_start();

/* ─────────────────────────────────────────────
   ACCESS CHECK
   ──────────────────────────────────────────────
   REMOVED: the original file had a "SECURITY CHECK" block here that
   decrypted $_REQUEST['qot_id'] and compared it to $_REQUEST['id'],
   then ran a `SELECT * FROM quotation WHERE id = '$_REQUEST[id]'`
   (concatenated straight into the query — a SQL injection hole) plus
   a paging include. None of that data ($rs / $client_info / paging)
   was ever used anywhere else in this file. This page is a month/year
   filtered board dashboard, not a single-quotation view, so that block
   looked like leftover code copy-pasted from a different page.

   If this page genuinely needs to be gated behind a quotation id,
   uncomment and use this SAFE version instead (escaped input, no
   unused queries left dangling):

   $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']);
   // ... only fetch $client_info here if this page actually displays it ...
───────────────────────────────────────────── */

/* ─────────────────────────────────────────────
   COMMON FILTER
───────────────────────────────────────────── */
$month = isset($_GET['month']) ? intval($_GET['month']) : date('n');
$year  = isset($_GET['year'])  ? intval($_GET['year'])  : date('Y');
if ($month < 1 || $month > 12) { $month = date('n'); }

$days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$month_name    = date('F Y', strtotime("$year-$month-01"));

/* ─────────────────────────────────────────────
   SQL – production summary
───────────────────────────────────────────── */
$production_sql = "
    SELECT
        DAY(record_date)          AS day,
        DATE_FORMAT(record_date, '%d-%b') AS date_label,
        fg_difference,
        vac_wip,
        cnc_wip,
        pu_wip,
        clock_hours_vac,
        clock_hours_cnc,
        clock_hours_pu,
        std_hours_vac,
        std_hours_cnc,
        std_hours_pu,
        std_hours_assbly
    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;
}

/* ─────────────────────────────────────────────
   SQL – sales
───────────────────────────────────────────── */
$sales_sql = "
    SELECT
        DAY(pp.req_date)                                     AS day,
        SUM(pp.req_qty * IFNULL(im.standard_cost_ebq, 0))   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)
    ORDER BY pp.req_date ASC
";
$sales_result = mysqli_query($conn, $sales_sql);
$sales_data   = [];
while ($row = mysqli_fetch_assoc($sales_result)) {
    $sales_data[$row['day']] = floatval($row['daily_sales']);
}

/* ─────────────────────────────────────────────
   BUILD CHART ARRAYS

   NOTE ON FORMULA CHANGES vs. the original file (flagging clearly since
   these affect the actual numbers shown to the board):

   1. "FG Stock Movement" was computed as (sales - fg_difference). That
      formula doesn't appear anywhere else in the codebase. In both the
      Sales Progress report and the Daily Progress report, fg_difference
      IS the FG movement figure on its own — Sales and FG Movement are
      always kept as separate additive components, never subtracted from
      one another. Given this page is titled "Sales vs Stock Movement",
      it's now rendered as two side-by-side series (Sales, FG Movement)
      on one chart rather than one ambiguous subtracted number.

   2. STD Hours previously summed vac+cnc+pu+assbly+assmpu, and Clock
      Hours summed vac+cnc+pu+assbly+assmpu. Neither matches the other
      two reports, which use:
        STD Hours   = vac + cnc + pu + assbly   (no assmpu)
        Clock Hours = vac + cnc + pu            (no assbly, no assmpu)
      Aligned here so this board dashboard agrees with the detailed
      reports for the same month. If assmpu genuinely should be
      included, say so and we fix it in all three reports together.
───────────────────────────────────────────── */
$charts = [
    'sales'      => ['labels' => [], 'data' => []],
    'fg_move'    => ['labels' => [], 'data' => []],
    'vac_wip'    => ['labels' => [], 'data' => []],
    'cnc_wip'    => ['labels' => [], 'data' => []],
    'pu_wip'     => ['labels' => [], 'data' => []],
    'std_hours'  => ['labels' => [], 'data' => []],
    'clock_hours'=> ['labels' => [], 'data' => []],
];

for ($day = 1; $day <= $days_in_month; $day++) {
    $date_str = sprintf('%04d-%02d-%02d', $year, $month, $day);
    $lbl      = sprintf('%02d-%s', $day, date('M', strtotime($date_str)));
    $prod     = $production_data[$day] ?? null;
    $sales    = $sales_data[$day]      ?? 0;

    /* Sales */
    $charts['sales']['labels'][] = $lbl;
    $charts['sales']['data'][]   = $sales;

    /* FG Movement — fg_difference on its own, matching the FG Movement
       report's "Actual FG Movement MTD" definition. */
    $fg_diff = $prod ? floatval($prod['fg_difference']) : 0;
    $charts['fg_move']['labels'][] = $lbl;
    $charts['fg_move']['data'][]   = $fg_diff;

    /* WIP values */
    $charts['vac_wip']['labels'][]   = $lbl;
    $charts['vac_wip']['data'][]     = $prod ? floatval($prod['vac_wip']) : 0;

    $charts['cnc_wip']['labels'][]   = $lbl;
    $charts['cnc_wip']['data'][]     = $prod ? floatval($prod['cnc_wip']) : 0;

    $charts['pu_wip']['labels'][]    = $lbl;
    $charts['pu_wip']['data'][]      = $prod ? floatval($prod['pu_wip'])  : 0;

    /* STD hours total — vac + cnc + pu + assbly (matches the other two reports) */
    $std_total = $prod
        ? floatval($prod['std_hours_vac'])
          + floatval($prod['std_hours_cnc'])
          + floatval($prod['std_hours_pu'])
          + floatval($prod['std_hours_assbly'])
        : 0;
    $charts['std_hours']['labels'][] = $lbl;
    $charts['std_hours']['data'][]   = $std_total;

    /* Clock hours total — vac + cnc + pu only (matches the other two reports) */
    $clock_total = $prod
        ? floatval($prod['clock_hours_vac'])
          + floatval($prod['clock_hours_cnc'])
          + floatval($prod['clock_hours_pu'])
        : 0;
    $charts['clock_hours']['labels'][] = $lbl;
    $charts['clock_hours']['data'][]   = $clock_total;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Board Meeting – Sales vs Stock Movement - <?= htmlspecialchars($month_name, ENT_QUOTES) ?></title>
<!--
    FIX: this page used Bootstrap classes (container-fluid/row/col-sm-8)
    and a $(document).ready() jQuery call for Toastify, but loaded
    neither Bootstrap CSS, jQuery, nor Toastify's own CSS/JS. Same
    pattern fixed in the sibling report pages.
-->
<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; }

/* filter bar */
.filter-bar {
    display: flex;
    gap: 12px;
    align-items: center;
    flex-wrap: wrap;
    background: #f8f9fa;
    padding: 14px;
    border-radius: 6px;
    margin-bottom: 20px;
}
.filter-bar label  { font-weight: 600; margin: 0; }
.filter-bar select { padding: 5px 10px; border: 1px solid #ccc; border-radius: 4px; }
.filter-bar button {
    padding: 6px 16px;
    background: #007bff;
    color: #fff;
    border: none;
    border-radius: 4px;
    cursor: pointer;
}
.filter-bar button:hover { background: #0056b3; }
.filter-bar .showing { font-size: 13px; color: #555; }

/* charts grid */
.charts-grid {
    display: grid;
    grid-template-columns: 1fr 1fr;
    gap: 25px;
    margin-top: 20px;
}
@media (max-width: 1024px) { .charts-grid { grid-template-columns: 1fr; } }

.chart-wrapper {
    background: #fff;
    padding: 15px;
    border: 2px solid #e0e0e0;
    border-radius: 8px;
}
.chart-wrapper.wide { grid-column: 1 / -1; }
.chart-title {
    color: #333;
    font-size: 14px;
    font-weight: bold;
    text-align: center;
    margin-bottom: 10px;
    text-transform: uppercase;
}
.chart-canvas { position: relative; height: 250px; }
.chart-note {
    font-size: 11px;
    color: #666;
    text-align: center;
    margin-top: 8px;
    padding: 8px;
    background: #f8f9fa;
    border-radius: 4px;
    line-height: 1.4;
}
@media print {
    .filter-bar { display: none; }
    .chart-wrapper { page-break-inside: avoid; }
}
</style>
</head>
<body>

<!-- ── HEADER ── -->
<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;margin-top:0;padding-bottom:0;">Board Meeting – Sales vs Stock Movement</h1>
        </div></div>
    </div>
</section>

<section>
<div class="container-fluid" style="padding:16px;">

    <!-- ── COMMON FILTER ── -->
    <div class="filter-bar">
        <label for="f_month">Month:</label>
        <select id="f_month">
            <?php for ($m = 1; $m <= 12; $m++): ?>
                <option value="<?= $m ?>" <?= $m == $month ? 'selected' : '' ?>>
                    <?= date('F', mktime(0,0,0,$m,1)) ?>
                </option>
            <?php endfor; ?>
        </select>

        <label for="f_year">Year:</label>
        <select id="f_year">
            <?php for ($y = date('Y') - 2; $y <= date('Y') + 2; $y++): ?>
                <option value="<?= $y ?>" <?= $y == $year ? 'selected' : '' ?>><?= $y ?></option>
            <?php endfor; ?>
        </select>

        <button onclick="applyFilter()">View Dashboard</button>
        <span class="showing">Showing: <strong><?= htmlspecialchars($month_name, ENT_QUOTES) ?></strong></span>
    </div>

    <script>
    function applyFilter() {
        var url = new URL(window.location.href);
        url.searchParams.set('month', document.getElementById('f_month').value);
        url.searchParams.set('year',  document.getElementById('f_year').value);
        window.location.href = url.toString();
    }
    </script>

    <!-- ── CHARTS ── -->
    <div class="charts-grid">

        <!-- Chart 1: Sales vs FG Stock Movement (comparison chart, wide) -->
        <div class="chart-wrapper wide">
            <div class="chart-title">Sales vs FG Stock Movement</div>
            <div class="chart-canvas"><canvas id="salesVsFgChart"></canvas></div>
            <div class="chart-note">
                Daily sales alongside daily FG stock movement (fg_difference) — same figure used as "FG Movement" in the Sales Progress report.
            </div>
        </div>

        <!-- Chart 2: VAC WIP -->
        <div class="chart-wrapper">
            <div class="chart-title">VAC WIP</div>
            <div class="chart-canvas"><canvas id="vacWipChart"></canvas></div>
            <div class="chart-note">
                Daily VAC WIP stock value (£). Tracks vacuum department work-in-progress.
            </div>
        </div>

        <!-- Chart 3: CNC WIP -->
        <div class="chart-wrapper">
            <div class="chart-title">CNC WIP</div>
            <div class="chart-canvas"><canvas id="cncWipChart"></canvas></div>
            <div class="chart-note">
                Daily CNC WIP stock value (£). Tracks CNC department work-in-progress.
            </div>
        </div>

        <!-- Chart 4: PU WIP -->
        <div class="chart-wrapper">
            <div class="chart-title">PU WIP</div>
            <div class="chart-canvas"><canvas id="puWipChart"></canvas></div>
            <div class="chart-note">
                Daily PU WIP stock value (£). Tracks polyurethane department work-in-progress.
            </div>
        </div>

        <!-- Chart 5: STD Hours -->
        <div class="chart-wrapper">
            <div class="chart-title">STD Hours (Total)</div>
            <div class="chart-canvas"><canvas id="stdHoursChart"></canvas></div>
            <div class="chart-note">
                Total standard hours across all departments: VAC + CNC + PU + ASSY.
            </div>
        </div>

        <!-- Chart 6: Clock Hours -->
        <div class="chart-wrapper">
            <div class="chart-title">Clock Hours (Total)</div>
            <div class="chart-canvas"><canvas id="clockHoursChart"></canvas></div>
            <div class="chart-note">
                Total clock hours across all departments: VAC + CNC + PU.
            </div>
        </div>

    </div><!-- /.charts-grid -->
</div><!-- /.container-fluid -->
</section>

<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
<script>
/* ── chart data from PHP ── */
const chartData = {
    sales:       { labels: <?= json_encode($charts['sales']['labels'])      ?>, data: <?= json_encode($charts['sales']['data'])      ?> },
    fg_move:     { labels: <?= json_encode($charts['fg_move']['labels'])    ?>, data: <?= json_encode($charts['fg_move']['data'])    ?> },
    vac_wip:     { labels: <?= json_encode($charts['vac_wip']['labels'])     ?>, data: <?= json_encode($charts['vac_wip']['data'])     ?> },
    cnc_wip:     { labels: <?= json_encode($charts['cnc_wip']['labels'])     ?>, data: <?= json_encode($charts['cnc_wip']['data'])     ?> },
    pu_wip:      { labels: <?= json_encode($charts['pu_wip']['labels'])      ?>, data: <?= json_encode($charts['pu_wip']['data'])      ?> },
    std_hours:   { labels: <?= json_encode($charts['std_hours']['labels'])   ?>, data: <?= json_encode($charts['std_hours']['data'])   ?> },
    clock_hours: { labels: <?= json_encode($charts['clock_hours']['labels']) ?>, data: <?= json_encode($charts['clock_hours']['data']) ?> },
};

/* ── determine if a dataset contains hour values (not £) ── */
function isHours(key) {
    return key === 'std_hours' || key === 'clock_hours';
}

/* ── Chart 1: Sales vs FG Stock Movement (two-series comparison bar chart) ── */
function createComparisonChart(canvasId) {
    const ctx = document.getElementById(canvasId).getContext('2d');
    new Chart(ctx, {
        type: 'bar',
        data: {
            labels: chartData.sales.labels,
            datasets: [
                {
                    label: 'Sales',
                    data: chartData.sales.data,
                    backgroundColor: '#2196F3',
                    borderColor: '#2196F3',
                    borderWidth: 1
                },
                {
                    label: 'FG Movement',
                    data: chartData.fg_move.data,
                    backgroundColor: '#FF9800',
                    borderColor: '#FF9800',
                    borderWidth: 1
                }
            ]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: { display: true, position: 'bottom', labels: { usePointStyle: true, font: { size: 11 } } },
                tooltip: {
                    mode: 'index',
                    intersect: false,
                    callbacks: {
                        label: function(ctx) {
                            const v = ctx.parsed.y;
                            return ctx.dataset.label + ': £' + v.toLocaleString('en-GB', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
                        }
                    }
                }
            },
            scales: {
                x: {
                    ticks: { maxRotation: 45, minRotation: 45, font: { size: 9 } },
                    grid: { display: false }
                },
                y: {
                    ticks: {
                        callback: function(v) { return '£' + (v / 1000).toFixed(0) + 'k'; },
                        font: { size: 10 }
                    },
                    grid: { color: '#e0e0e0' }
                }
            }
        }
    });
}

/* ── generic single-series bar chart factory (WIP + hours charts) ── */
function createChart(canvasId, key, color) {
    const hours = isHours(key);
    const ctx   = document.getElementById(canvasId).getContext('2d');
    new Chart(ctx, {
        type: 'bar',
        data: {
            labels: chartData[key].labels,
            datasets: [{
                data: chartData[key].data,
                backgroundColor: color,
                borderColor: color,
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: { display: false },
                tooltip: {
                    callbacks: {
                        label: function(ctx) {
                            const v = ctx.parsed.y;
                            if (hours) {
                                return v.toLocaleString('en-GB', { minimumFractionDigits: 1, maximumFractionDigits: 1 }) + ' hrs';
                            }
                            return '£' + v.toLocaleString('en-GB', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
                        }
                    }
                }
            },
            scales: {
                x: {
                    ticks: { maxRotation: 45, minRotation: 45, font: { size: 9 } },
                    grid: { display: false }
                },
                y: {
                    beginAtZero: true,
                    ticks: {
                        callback: function(v) {
                            if (hours) { return v.toFixed(0) + ' h'; }
                            return '£' + (v / 1000).toFixed(0) + 'k';
                        },
                        font: { size: 10 }
                    },
                    grid: { color: '#e0e0e0' }
                }
            }
        }
    });
}

/* ── create all charts ── */
createComparisonChart('salesVsFgChart');
createChart('vacWipChart',    'vac_wip',     '#4CAF50');
createChart('cncWipChart',    'cnc_wip',     '#FF9800');
createChart('puWipChart',     'pu_wip',      '#9C27B0');
createChart('stdHoursChart',  'std_hours',   '#F44336');
createChart('clockHoursChart','clock_hours', '#00BCD4');
</script>

</body>
</html>