<?php
// Get month and year from URL parameters or use current month
$month = isset($_GET['month']) ? intval($_GET['month']) : date('n');
$year  = isset($_GET['year'])  ? intval($_GET['year'])  : date('Y');

// Validate month
if ($month < 1 || $month > 12) {
    $month = date('n');
}

// Get number of days in the month
$days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);

// Fetch productivity data from daily_production_summary
$productivity_sql = "
    SELECT 
        DAY(record_date) as day,
        DAYNAME(record_date) as day_name,
        record_date,
        clock_hours_vac,
        clock_hours_cnc,
        clock_hours_pu,
        clock_hours_assbly,
        std_hours_vac,
        std_hours_cnc,
        std_hours_pu,
        std_hours_assbly,
        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
";

$productivity_result = mysqli_query($conn, $productivity_sql);
$productivity_data = [];

while ($row = mysqli_fetch_assoc($productivity_result)) {
    $productivity_data[$row['day']] = $row;
}

// Month name
$month_name = date('F Y', strtotime("$year-$month-01"));
?>

    <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;
        }
        .month-selector {
            margin-bottom: 20px;
            padding: 15px;
            background-color: #f8f9fa;
            border-radius: 5px;
            text-align: center;
        }
        .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;
        }
        .table-responsive {
            overflow-x: auto;
            margin-top: 20px;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            min-width: 2000px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: center;
            font-size: 11px;
        }
        th {
            background-color: #4472C4;
            color: white;
            font-weight: bold;
            position: sticky;
            top: 0;
            z-index: 10;
        }
        .date-header {
            font-weight: bold;
            font-size: 10px;
            writing-mode: horizontal-tb;
        }
        .day-header {
            font-size: 9px;
            background-color: #f0f0f0;
			color: black !important;
        }
        .dept-label {
            background-color: #f8f9fa;
            font-weight: bold;
            text-align: left;
            padding-left: 10px;
        }
        .row-label {
            background-color: #ffffff;
            text-align: left;
            padding-left: 20px;
            font-size: 10px;
        }
        .weekend {
            background-color: #e8e8e8;
        }
        .productivity-cell {
            background-color: #fff2cc;
        }
        .no-data {
            color: #999;
        }
    </style>
    <div class="container">
        <h2>PRODUCTIVITY REPORT - <?php echo strtoupper($month_name); ?></h2>
        
        <div class="month-selector">
            <form method="GET" action="">
                <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>
                
                <button type="submit">View Report</button>
            </form>
        </div>

        <div class="table-responsive">
            <table>
                <thead>
                    <tr>
                        <th colspan="2"></th>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                        ?>
                            <th class="date-header"><?php echo str_pad($day, 2, '0', STR_PAD_LEFT) . '-' . date('M', strtotime($date_str)); ?></th>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <th colspan="2"></th>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $day_name = date('l', strtotime($date_str));
                        ?>
                            <th class="day-header"><?php echo $day_name; ?></th>
                        <?php endfor; ?>
                    </tr>
                </thead>
                <tbody>
                    <!-- VAC SECTION -->
                    <tr>
                        <td colspan="<?php echo $days_in_month + 2; ?>" class="dept-label">VAC</td>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Req</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Actual</td>
                        <?php 
                        for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $std_hours = $prod ? floatval($prod['std_hours_vac']) : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $std_hours > 0 ? number_format($std_hours, 1) : '0.0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Clock Hours</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $clock_hours = $prod ? floatval($prod['clock_hours_vac']) : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $clock_hours > 0 ? number_format($clock_hours, 1) : '0.0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Stock</td>
                        <?php 
                        for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $vac_wip = $prod ? floatval($prod['vac_wip']) : 0;
                            $std_hours_stock = ($vac_wip > 0) ? $vac_wip / 200 : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $std_hours_stock > 0 ? number_format($std_hours_stock, 0) : '0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Productivity actual</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $std_hours = $prod ? floatval($prod['std_hours_vac']) : 0;
                            $clock_hours = $prod ? floatval($prod['clock_hours_vac']) : 0;
                            $productivity = ($clock_hours > 0) ? ($std_hours / $clock_hours) : 0;
                            $class = $is_weekend ? 'weekend productivity-cell' : 'productivity-cell';
                        ?>
                            <td class="<?php echo $class; ?>">
                                <?php 
                                if ($clock_hours > 0) {
                                    echo number_format($productivity * 100, 0) . '%';
                                } else {
                                    echo '<span class="no-data">0</span>';
                                }
                                ?>
                            </td>
                        <?php endfor; ?>
                    </tr>

                    <!-- CNC SECTION -->
                    <tr>
                        <td colspan="<?php echo $days_in_month + 2; ?>" class="dept-label">CNC</td>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Req</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Actual</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $std_hours = $prod ? floatval($prod['std_hours_cnc']) : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $std_hours > 0 ? number_format($std_hours, 1) : '0.0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Clock Hours</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $clock_hours = $prod ? floatval($prod['clock_hours_cnc']) : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $clock_hours > 0 ? number_format($clock_hours, 1) : '0.0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Stock</td>
                        <?php 
                        for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $cnc_wip = $prod ? floatval($prod['cnc_wip']) : 0;
                            $std_hours_stock = ($cnc_wip > 0) ? $cnc_wip / 200 : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $std_hours_stock > 0 ? number_format($std_hours_stock, 0) : '0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Productivity actual</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $std_hours = $prod ? floatval($prod['std_hours_cnc']) : 0;
                            $clock_hours = $prod ? floatval($prod['clock_hours_cnc']) : 0;
                            $productivity = ($clock_hours > 0) ? ($std_hours / $clock_hours) : 0;
                            $class = $is_weekend ? 'weekend productivity-cell' : 'productivity-cell';
                        ?>
                            <td class="<?php echo $class; ?>">
                                <?php 
                                if ($clock_hours > 0) {
                                    echo number_format($productivity * 100, 0) . '%';
                                } else {
                                    echo '<span class="no-data">0</span>';
                                }
                                ?>
                            </td>
                        <?php endfor; ?>
                    </tr>

                    <!-- assbly SECTION -->
                    <tr>
                        <td colspan="<?php echo $days_in_month + 2; ?>" class="dept-label">assbly</td>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Req</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Actual</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $std_hours = $prod ? floatval($prod['std_hours_assbly']) : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $std_hours > 0 ? number_format($std_hours, 1) : '0.0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Clock Hours</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $clock_hours = $prod ? floatval($prod['clock_hours_assbly']) : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $clock_hours > 0 ? number_format($clock_hours, 1) : '0.0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Productivity</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $std_hours = $prod ? floatval($prod['std_hours_assbly']) : 0;
                            $clock_hours = $prod ? floatval($prod['clock_hours_assbly']) : 0;
                            $productivity = ($clock_hours > 0) ? ($std_hours / $clock_hours) : 0;
                            $class = $is_weekend ? 'weekend productivity-cell' : 'productivity-cell';
                        ?>
                            <td class="<?php echo $class; ?>">
                                <?php 
                                if ($clock_hours > 0) {
                                    echo number_format($productivity * 100, 0) . '%';
                                } else {
                                    echo '<span class="no-data">0</span>';
                                }
                                ?>
                            </td>
                        <?php endfor; ?>
                    </tr>

                    <!-- PU ML SECTION -->
                    <tr>
                        <td colspan="<?php echo $days_in_month + 2; ?>" class="dept-label">PU ML</td>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Req</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Std Hours Actual</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $std_hours = $prod ? floatval($prod['std_hours_pu']) : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $std_hours > 0 ? number_format($std_hours, 1) : '0.0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Clock Hours</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $clock_hours = $prod ? floatval($prod['clock_hours_pu']) : 0;
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"><?php echo $clock_hours > 0 ? number_format($clock_hours, 1) : '0.0'; ?></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Downtime</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $class = $is_weekend ? 'weekend' : '';
                        ?>
                            <td class="<?php echo $class; ?>"></td>
                        <?php endfor; ?>
                    </tr>
                    <tr>
                        <td colspan="2" class="row-label">Productivity</td>
                        <?php for ($day = 1; $day <= $days_in_month; $day++): 
                            $date_str = sprintf("%04d-%02d-%02d", $year, $month, $day);
                            $is_weekend = (date('N', strtotime($date_str)) >= 6);
                            $prod = isset($productivity_data[$day]) ? $productivity_data[$day] : null;
                            $std_hours = $prod ? floatval($prod['std_hours_pu']) : 0;
                            $clock_hours = $prod ? floatval($prod['clock_hours_pu']) : 0;
                            $productivity = ($clock_hours > 0) ? ($std_hours / $clock_hours) : 0;
                            $class = $is_weekend ? 'weekend productivity-cell' : 'productivity-cell';
                        ?>
                            <td class="<?php echo $class; ?>">
                                <?php 
                                if ($clock_hours > 0) {
                                    echo number_format($productivity * 100, 0) . '%';
                                } else {
                                    echo '<span class="no-data">0</span>';
                                }
                                ?>
                            </td>
                        <?php endfor; ?>
                    </tr>
                </tbody>
            </table>
        </div>
    </div>