<?php
// Get year range from URL parameters
$year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');

// Fetch production actuals data - monthly aggregation
 $actuals_sql = "
    SELECT 
        DATE_FORMAT(record_date, '%b-%y') as month_year,
        YEAR(record_date) as year,
        MONTH(record_date) as month,
        record_date
    FROM daily_production_summary
    WHERE YEAR(record_date) >= $year - 1 AND YEAR(record_date) <= $year + 1
    GROUP BY YEAR(record_date), MONTH(record_date)
    ORDER BY record_date DESC
    LIMIT 18
";

$actuals_result = mysqli_query($conn, $actuals_sql);
$months_data = [];

while ($row = mysqli_fetch_assoc($actuals_result)) {
    $months_data[] = $row;
}

// Reverse to show oldest first for proper calculation
$months_data = array_reverse($months_data);

$prev_fg = 0;
$prev_wip = 0;
$report_data = [];

foreach ($months_data as $month_row) {
    $year = $month_row['year'];
    $month = $month_row['month'];
    
    // Get sales for the month
  echo  $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) = $year AND MONTH(pp.req_date) = $month
    ";
    $sales_result = mysqli_query($conn, $sales_sql);
    $sales_row = mysqli_fetch_assoc($sales_result);
    $sales = floatval($sales_row['sales']);
    
    // Get first day of month for opening stock
    $first_day = sprintf("%04d-%02d-01", $year, $month);
    
    // Get opening stock (last day of previous month or first day of current month)
    $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_result = mysqli_query($conn, $opening_sql);
    $opening = mysqli_fetch_assoc($opening_result);
    $opening_fg = $opening ? floatval($opening['fg']) : $prev_fg;
    $opening_wip = $opening ? floatval($opening['wip']) : $prev_wip;
    
    // Get last day of month for closing stock
    $last_day = date('Y-m-t', strtotime($first_day));
    
    // Get closing stock (last day of the month)
    $closing_sql = "
        SELECT fg, (vac_wip + cnc_wip + pu_wip) as wip
        FROM daily_production_summary
        WHERE YEAR(record_date) = $year AND MONTH(record_date) = $month
        ORDER BY record_date DESC
        LIMIT 1
    ";
    $closing_result = mysqli_query($conn, $closing_sql);
    $closing = mysqli_fetch_assoc($closing_result);
    $closing_fg = $closing ? floatval($closing['fg']) : 0;
    $closing_wip = $closing ? floatval($closing['wip']) : 0;
    
    // Calculate movements
    $fg_mov = $closing_fg - $opening_fg;
    $wip_mov = $closing_wip - $opening_wip;
    
    // Get sum of standard hours and clock hours for the month
    $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 + clock_hours_assbly) as clock_hours
        FROM daily_production_summary
        WHERE YEAR(record_date) = $year AND MONTH(record_date) = $month
    ";
    $hours_result = mysqli_query($conn, $hours_sql);
    $hours = mysqli_fetch_assoc($hours_result);
    $std_hours = floatval($hours['std_hours']);
    $clock_hours = floatval($hours['clock_hours']);
    
    // Production Total = Sales + FG Movement + WIP Movement
    $prod_total = $sales + $fg_mov + $wip_mov;
    
    // £ per Standard Hour
    $pound_per_std_hour = ($std_hours > 0) ? $prod_total / $std_hours : 0;
    
    // Productivity
    $productivity = ($clock_hours > 0) ? ($std_hours / $clock_hours) * 100 : 0;
    
    $report_data[] = [
        'month_year' => $month_row['month_year'],
        '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
    ];
    
    // Update previous values
    $prev_fg = $closing_fg;
    $prev_wip = $closing_wip;
}

// Reverse to show most recent first
$report_data = array_reverse($report_data);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Production Actuals Report</title>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }
        body {
            font-family: Arial, sans-serif;
            background-color: #f5f5f5;
            padding: 20px;
        }
        .container {
            background-color: white;
            padding: 20px;
            width: 100%;
            max-width: 100%;
            min-height: 100vh;
            margin: 0 auto;
        }
        h2 {
            color: #333;
            margin-bottom: 20px;
            text-align: center;
            font-size: 24px;
        }
        .year-selector {
            margin-bottom: 20px;
            padding: 15px;
            background-color: #f8f9fa;
            border-radius: 5px;
            text-align: center;
        }
        .year-selector label {
            margin-right: 10px;
            font-weight: bold;
        }
        .year-selector select {
            padding: 5px 10px;
            margin-right: 10px;
            border: 1px solid #ccc;
            border-radius: 4px;
        }
        .year-selector button {
            padding: 6px 15px;
            background-color: #007bff;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
        }
        .year-selector button:hover {
            background-color: #0056b3;
        }
        .table-responsive {
            overflow-x: auto;
            margin-top: 20px;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            min-width: 1800px;
            font-size: 11px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: right;
        }
        th {
            background-color: #4472C4;
            color: white;
            font-weight: bold;
            position: sticky;
            top: 0;
            z-index: 10;
            font-size: 10px;
        }
        td:first-child {
            text-align: center;
            font-weight: bold;
            background-color: #f8f9fa;
        }
        tr:nth-child(even) {
            background-color: #f9f9f9;
        }
        tr:hover {
            background-color: #f0f0f0;
        }
        .positive {
            color: #008000;
        }
        .negative {
            color: #FF0000;
        }
        .highlight {
            background-color: #fff2cc;
            font-weight: bold;
        }
    </style>
</head>
<body>
    <div class="container">
        <h2>PRODUCTION ACTUALS</h2>

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

        <div class="table-responsive">
            <table>
                <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 ($report_data as $data): ?>
                        <tr>
                            <td><?php echo $data['month_year']; ?></td>
                            <td>£ <?php echo number_format($data['sales'], 2); ?></td>
                            <td>£ <?php echo number_format($data['opening_fg'], 2); ?></td>
                            <td>£ <?php echo number_format($data['closing_fg'], 2); ?></td>
                            <td class="<?php echo $data['fg_mov'] >= 0 ? 'positive' : 'negative'; ?>">
                                <?php echo ($data['fg_mov'] >= 0 ? '£ ' : '-£ ') . number_format(abs($data['fg_mov']), 2); ?>
                            </td>
                            <td>£ <?php echo number_format($data['opening_wip'], 2); ?></td>
                            <td>£ <?php echo number_format($data['closing_wip'], 2); ?></td>
                            <td class="<?php echo $data['wip_mov'] >= 0 ? 'positive' : 'negative'; ?>">
                                <?php echo ($data['wip_mov'] >= 0 ? '£ ' : '-£ ') . number_format(abs($data['wip_mov']), 2); ?>
                            </td>
                            <td class="highlight">£ <?php echo number_format($data['prod_total'], 2); ?></td>
                            <td><?php echo number_format($data['std_hours'], 0); ?></td>
                            <td>£</td>
                            <td><?php echo number_format($data['pound_per_std_hour'], 2); ?></td>
                            <td><?php echo number_format($data['clock_hours'], 0); ?></td>
                            <td><?php echo number_format($data['productivity'], 0); ?>%</td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</body>
</html>