<?php
require_once '../connections/meekro/db.class.php';

// Month and quarter mappings
$monthOrder = [
  "January", "February", "March", "April", "May", "June",
  "July", "August", "September", "October", "November", "December"
];

$quarterMap = [
  "January" => "Q1", "February" => "Q1", "March" => "Q1",
  "April" => "Q2", "May" => "Q2", "June" => "Q2",
  "July" => "Q3", "August" => "Q3", "September" => "Q3",
  "October" => "Q4", "November" => "Q4", "December" => "Q4"
];

// Fetch monthly revenue
$rows = DB::query("SELECT year, month, amount 
                   FROM year_to_date 
                   ORDER BY year, 
                   FIELD(month, 'January','February','March','April','May','June','July','August','September','October','November','December')");

$monthly = [];
$yearly = [];
$quarterly = [];

foreach ($rows as $row) {
    $y = $row['year'];
    $m = $row['month'];
    $v = (float) $row['amount'];

    $monthly[$y][$m] = $v;
    $yearly[$y] = ($yearly[$y] ?? 0) + $v;
    $q = $quarterMap[$m];
    $quarterly[$y][$q] = ($quarterly[$y][$q] ?? 0) + $v;
}

// Weekly chart data (for current year if available)
$weeks = DB::query("
    SELECT WEEK(STR_TO_DATE(CONCAT(month, ' ', year), '%M %Y'), 1) AS week,
           SUM(amount) AS total
    FROM year_to_date
    GROUP BY week
    ORDER BY week
");

$weeklyData = [];
foreach ($weeks as $row) {
    $weeklyData[] = [
        'week' => (int)$row['week'],
        'total' => round((float)$row['total'], 2)
    ];
}

// Output final JSON
header('Content-Type: application/json');
echo json_encode([
    'monthly' => $monthly,
    'yearly' => $yearly,
    'quarterly' => $quarterly,
    'months' => $monthOrder,
    'weeks' => $weeklyData
]);
