<?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 the current date or use month end if viewing past months
$today = date('Y-m-d');
$current_month = date('n');
$current_year = date('Y');

// If viewing current month, use today's date, otherwise use last day of selected month
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));
}

// Calculate working days (excluding Friday, Saturday, Sunday)
function countWorkingDays($start_date, $end_date) {
    $working_days = 0;
    $current = strtotime($start_date);
    $end = strtotime($end_date);
    
    while ($current <= $end) {
        $day_of_week = date('N', $current); // 1 (Mon) to 7 (Sun)
        // Exclude Friday(5), Saturday(6), Sunday(7)
        if ($day_of_week < 5) {
            $working_days++;
        }
        $current = strtotime('+1 day', $current);
    }
    
    return $working_days;
}

// Calculate total working days in the month
$first_day = "$year-$month-01";
$last_day = date('Y-m-t', strtotime($first_day));
$total_working_days = countWorkingDays($first_day, $last_day);

// Calculate working days elapsed (from start of month to today/end_date)
$working_days_elapsed = countWorkingDays($first_day, $end_date);

// Monthly sales plan/target (you can adjust this or make it dynamic from a targets table)
$monthly_plan = 355000.00;

// Calculate Target Month-to-Date based on working days
$target_mtd = ($total_working_days > 0) ? ($monthly_plan / $total_working_days) * $working_days_elapsed : 0;

// Fetch Actual Month-to-Date sales
$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']);

// Calculate variance
$variance = $actual_mtd - $target_mtd;

// 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: 30px;
            max-width: 1200px;
            margin: 0 auto;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            border-radius: 5px;
        }
        h2 {
            color: #333;
            margin-bottom: 20px;
            text-align: center;
            font-size: 24px;
        }
        .month-selector {
            margin-bottom: 30px;
            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;
        }
        .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;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 15px;
            text-align: center;
            font-size: 16px;
        }
        th {
            background-color: #4472C4;
            color: white;
            font-weight: bold;
        }
        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;
        }
    </style>

    <div class="container">
        <h2>SALES TARGET 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="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-Thursday)</p>
            <p><strong>Working Days Elapsed:</strong> <?php echo $working_days_elapsed; ?> days</p>
        </div>

        <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>