<?php
require 'db.php'; // Your DB connection

$from = $_POST['from_date'];
$to = $_POST['to_date'];

// Get all distinct service names
$services = [];
$service_q = mysqli_query($conn, "
    SELECT DISTINCT servicename 
    FROM assdt_service_consumption_table
    WHERE req_dt BETWEEN '$from' AND '$to'
");
while ($row = mysqli_fetch_assoc($service_q)) {
    $services[] = $row['servicename'];
}

// Get distinct user IDs
$users = [];
$user_q = mysqli_query($conn, "
    SELECT DISTINCT user_id 
    FROM assdt_service_consumption_table
    WHERE req_dt BETWEEN '$from' AND '$to'
");
while ($row = mysqli_fetch_assoc($user_q)) {
    $users[] = $row['user_id'];
}

// Prepare result data
$data = [];

foreach ($users as $user_id) {
    $row = ['user_id' => $user_id];

    foreach ($services as $servicename) {
        $sum_q = mysqli_query($conn, "
            SELECT SUM(transamt) as total 
            FROM assdt_service_consumption_table 
            WHERE user_id = '$user_id' 
              AND servicename = '$servicename' 
              AND req_dt BETWEEN '$from' AND '$to'
        ");
        $sum_data = mysqli_fetch_assoc($sum_q);
        $row[$servicename] = $sum_data['total'] ?? 0;
    }

    $data[] = $row;
}

// Output JSON for frontend
echo json_encode([
    'services' => $services,
    'data' => $data
]);
