
<?php
header('Content-Type: application/json');
error_reporting(0);
$pdo = new PDO("pgsql:host=localhost;dbname=adx_system", "admin", "admin123", [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$action = $_GET['action'] ?? '';

switch ($action) {
    case 'overview':
        $today = date('Y-m-d');
        $week = date('Y-m-d', strtotime('-7 days'));
        $month = date('Y-m-d', strtotime('-30 days'));
        
        $todaySent = $pdo->query("SELECT COALESCE(SUM(total_sent), 0) FROM admin.campaigns WHERE DATE(started_at) = '$today'")->fetchColumn() ?: 0;
        $todayCamp = $pdo->query("SELECT COUNT(*) FROM admin.campaigns WHERE DATE(started_at) = '$today'")->fetchColumn() ?: 0;
        $weekSent = $pdo->query("SELECT COALESCE(SUM(total_sent), 0) FROM admin.campaigns WHERE started_at >= '$week'")->fetchColumn() ?: 0;
        $weekCamp = $pdo->query("SELECT COUNT(*) FROM admin.campaigns WHERE started_at >= '$week'")->fetchColumn() ?: 0;
        $monthSent = $pdo->query("SELECT COALESCE(SUM(total_sent), 0) FROM admin.campaigns WHERE started_at >= '$month'")->fetchColumn() ?: 0;
        $monthCamp = $pdo->query("SELECT COUNT(*) FROM admin.campaigns WHERE started_at >= '$month'")->fetchColumn() ?: 0;
        $totalSent = $pdo->query("SELECT COALESCE(SUM(total_sent), 0) FROM admin.campaigns")->fetchColumn() ?: 0;
        $totalCamp = $pdo->query("SELECT COUNT(*) FROM admin.campaigns")->fetchColumn() ?: 0;
        
        echo json_encode([
            'today' => ['sent' => $todaySent, 'campaigns' => $todayCamp],
            'week' => ['sent' => $weekSent, 'campaigns' => $weekCamp],
            'month' => ['sent' => $monthSent, 'campaigns' => $monthCamp],
            'total' => ['sent' => $totalSent, 'campaigns' => $totalCamp]
        ]);
        break;
        
    case 'campaign':
        $cid = intval($_GET['campaign_id'] ?? 0);
        $opens = $pdo->query("SELECT COUNT(*) FROM admin.tracking_opens WHERE campaign_id = $cid")->fetchColumn() ?: 0;
        $clicks = $pdo->query("SELECT COUNT(*) FROM admin.tracking_clicks WHERE campaign_id = $cid")->fetchColumn() ?: 0;
        $bounces = $pdo->query("SELECT COUNT(*) FROM admin.bounces WHERE campaign_id = $cid")->fetchColumn() ?: 0;
        $stmt = $pdo->prepare("SELECT * FROM admin.campaigns WHERE id = ?");
        $stmt->execute([$cid]);
        $campaign = $stmt->fetch(PDO::FETCH_ASSOC);
        $sent = $campaign['total_sent'] ?? 0;
        
        echo json_encode([
            'campaign' => $campaign,
            'sent' => $sent,
            'opens' => $opens,
            'open_rate' => $sent > 0 ? round(($opens / $sent) * 100, 2) : 0,
            'clicks' => $clicks,
            'click_rate' => $sent > 0 ? round(($clicks / $sent) * 100, 2) : 0,
            'bounces' => $bounces,
            'bounce_rate' => $sent > 0 ? round(($bounces / $sent) * 100, 2) : 0
        ]);
        break;
        
    case 'top':
        $campaigns = $pdo->query("SELECT id, name, total_sent, inbox_rate, created_at FROM admin.campaigns ORDER BY total_sent DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['campaigns' => $campaigns]);
        break;
        
    case 'by_isp':
        $data = $pdo->query("SELECT isp, COUNT(*) as campaigns, COALESCE(SUM(total_sent),0) as total_sent FROM admin.campaigns GROUP BY isp ORDER BY total_sent DESC")->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['data' => $data]);
        break;
        
    default:
        echo json_encode(['actions' => ['overview','campaign','top','by_isp']]);
}