<?php
include "includes/db_connect.php";

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="limit_violations.csv"');

$out = fopen('php://output', 'w');
fputcsv($out, ['MSISDN','User Type','Daily Used','Daily Limit','Cumulative Used','Cumulative Limit','Blocked','Reason']);

// Optional search filter
$search = $_GET['search'] ?? '';
$where = "1=1";
if ($search) {
    $where .= " AND t.sender_msisdn LIKE '%$search%'";
}

// SQL with user-type aware limits
$sql = "
SELECT 
    t.sender_msisdn AS msisdn,
    u.user_type,
    SUM(CASE WHEN DATE(t.created_at)=CURDATE() THEN t.send_amount ELSE 0 END) AS daily_used,
    SUM(t.send_amount) AS cumulative_used,
    
    COALESCE(
        (SELECT max_amount FROM msisdn_limits WHERE applies_to='MSISDN' AND msisdn=t.sender_msisdn AND limit_type='DAILY' LIMIT 1),
        (SELECT max_amount FROM msisdn_limits WHERE applies_to='USER_TYPE' AND msisdn=u.user_type AND limit_type='DAILY' LIMIT 1),
        (SELECT max_amount FROM msisdn_limits WHERE applies_to='ALL' AND limit_type='DAILY' LIMIT 1)
    ) AS daily_limit,
    
    COALESCE(
        (SELECT max_amount FROM msisdn_limits WHERE applies_to='MSISDN' AND msisdn=t.sender_msisdn AND limit_type='CUMULATIVE' LIMIT 1),
        (SELECT max_amount FROM msisdn_limits WHERE applies_to='USER_TYPE' AND msisdn=u.user_type AND limit_type='CUMULATIVE' LIMIT 1),
        (SELECT max_amount FROM msisdn_limits WHERE applies_to='ALL' AND limit_type='CUMULATIVE' LIMIT 1)
    ) AS cumulative_limit,

    k.blocked,
    k.reason
FROM transactions t
LEFT JOIN users u ON u.msisdn = t.sender_msisdn
LEFT JOIN msisdn_kill_switch k ON k.msisdn = t.sender_msisdn
WHERE t.status='SETTLED' AND $where
GROUP BY t.sender_msisdn
ORDER BY t.sender_msisdn
";

$res = mysqli_query($conn, $sql);

while($r=mysqli_fetch_assoc($res)){
    fputcsv($out, [
        $r['msisdn'],
        $r['user_type'],
        $r['daily_used'],
        $r['daily_limit'],
        $r['cumulative_used'],
        $r['cumulative_limit'],
        $r['blocked'] ? 'YES' : 'NO',
        $r['reason']
    ]);
}

fclose($out);
exit;
