<?php
function getExpenseReceiptStats($conn_rjllearning_specific, $institution_id, $branch_id, $mindate, $maxdate) {
    $date1 = new DateTime($mindate);
    $date2 = new DateTime($maxdate);
    $sameDay = $mindate === $maxdate;
    $sameMonth = $date1->format('Y-m') === $date2->format('Y-m');
    // $daysDiff = $date1->diff($date2)->days;

    $results = [
        'vendor_info' => [],
        'group_info' => []
    ];

    // Helper to map labels
    function getLabel($row, $sameDay, $sameMonth) {
        if ($sameDay) return $row['day'];
        if (!$sameMonth) return $row['month'];
        // if ($daysDiff > 7) return "Week " . ($row['week_number'] + 1);
        return $row['day'];
    }

    // ========================
    // 1. VENDOR GROUPED QUERY
    // ========================
    if (!$sameMonth) {
        $vendor_sql = "
            SELECT vendor_name, transaction_type_group,
                   DATE_FORMAT(STR_TO_DATE(date, '%Y-%m-%d'), '%Y-%m') AS month,
                   SUM(amount) as total
            FROM unmarked_transaction
            WHERE institution_id = ? AND transaction_type_group IN ('expense', 'receipt')";
        if($branch_id != "all") {
            $vendor_sql .= " AND branch_id = ?";
        }
        $vendor_sql .= "AND STR_TO_DATE(date, '%Y-%m-%d') BETWEEN ? AND ?
            GROUP BY month, vendor_name, transaction_type_group
        ";
    } else {
        $vendor_sql = "
            SELECT vendor_name, transaction_type_group,
                   DATE(date) as day,
                   SUM(amount) as total
            FROM unmarked_transaction
             WHERE institution_id = ? AND transaction_type_group IN ('expense', 'receipt')";
        if($branch_id != "all") {
            $vendor_sql .= " AND branch_id = ?";
        } 
        $vendor_sql .= " AND STR_TO_DATE(date, '%Y-%m-%d') BETWEEN ? AND ?
            GROUP BY day, vendor_name, transaction_type_group
        ";
    }

    $stmt = $conn_rjllearning_specific->prepare($vendor_sql);
    if($branch_id == "all") {
        $stmt->execute([$institution_id, $mindate, $maxdate]);
    } else {
        $stmt->execute([$institution_id, $branch_id, $mindate, $maxdate]);
    }    
    $vendor_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($vendor_data as $row) {
        $label = getLabel($row, $sameDay, $sameMonth);
        $type = $row['transaction_type_group'];
        $vendor = $row['vendor_name'];
        $results['vendor_info'][$label][$type][$vendor] = ($results['vendor_info'][$label][$type][$vendor] ?? 0) + $row['total'];
    }

    // ===============================
    // 2. GROUP_NAME (CATEGORY_ID) QUERY
    // ===============================
    if (!$sameMonth) {
        $group_sql = "
            SELECT g.group_name, ut.transaction_type_group,
                   DATE_FORMAT(STR_TO_DATE(ut.date, '%Y-%m-%d'), '%Y-%m') AS month,
                   SUM(ut.amount) as total
            FROM unmarked_transaction ut
            LEFT JOIN unmarked_transaction_type_group g ON ut.category_id = g.id
            WHERE ut.institution_id = ? AND transaction_type_group IN ('expense', 'receipt')";
        if($branch_id != "all") {
        $group_sql .= " AND ut.branch_id = ?";
        }
        $group_sql .= " AND STR_TO_DATE(ut.date, '%Y-%m-%d') BETWEEN ? AND ?
            GROUP BY month, g.group_name, ut.transaction_type_group
        ";
    } else {
        $group_sql = "
            SELECT g.group_name, ut.transaction_type_group,
                   DATE(ut.date) as day,
                   SUM(ut.amount) as total
            FROM unmarked_transaction ut
            LEFT JOIN unmarked_transaction_type_group g ON ut.category_id = g.id
            WHERE ut.institution_id = ? AND transaction_type_group IN ('expense', 'receipt')";
        if($branch_id != "all") {
        $group_sql .= " AND ut.branch_id = ?";
        }
        $group_sql .= " AND STR_TO_DATE(ut.date, '%Y-%m-%d') BETWEEN ? AND ?
            GROUP BY day, g.group_name, ut.transaction_type_group
        ";
    }

    $stmt = $conn_rjllearning_specific->prepare($group_sql);
    if($branch_id == "all") {
        $stmt->execute([$institution_id, $mindate, $maxdate]);
    } else {
        $stmt->execute([$institution_id, $branch_id, $mindate, $maxdate]);
    }
    $group_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($group_data as $row) {
        $label = getLabel($row, $sameDay, $sameMonth);
        $type = $row['transaction_type_group'];
        $group = $row['group_name'] ?? 'Unknown Group';
        $results['group_info'][$label][$type][$group] = ($results['group_info'][$label][$type][$group] ?? 0) + $row['total'];
    }

    return [
        'vendor_info' => transformStats($results['vendor_info']),
        'group_info' => transformStats($results['group_info']),
        // 'week_labels' => (!$sameDay && $daysDiff > 7 && $sameMonth) ? getWeeklyLabels($mindate, $maxdate) : []
        'week_labels' =>  []
    ];
}

function transformStats($rawStats) {
    $result = [];
    foreach ($rawStats as $label => $types) {
        $entry = ['label' => $label];
        foreach ($types as $type => $items) {
            $entry[$type . '_info'] = [];
            foreach ($items as $name => $amt) {
                $entry[$type . '_info'][] = ['name' => $name, 'amount' => $amt];
            }
        }
        $result[] = $entry;
    }
    return $result;
}

function getWeeklyLabels($mindate, $maxdate) {
    $start = new DateTime($mindate);
    $end = new DateTime($maxdate);
    $start->modify('first day of this month');
    $end->modify('last day of this month');

    $labels = [];
    $week = 1;
    while ($start <= $end) {
        $week_start = clone $start;
        $week_end = min((clone $start)->modify('+6 days'), $end);
        $labels[] = [
            'week_label' => "Week $week (" . $week_start->format('d M') . " - " . $week_end->format('d M') . ")",
            'from' => $week_start->format('Y-m-d'),
            'to' => $week_end->format('Y-m-d')
        ];
        $start = $week_end->modify('+1 day');
        $week++;
    }

    return $labels;
}
?>