
<?php
/**
 * REAL-TIME ANALYTICS
 * Live campaign monitoring and performance tracking
 */
header('Content-Type: application/json');
$pdo = new PDO("pgsql:host=localhost;dbname=adx_system", "admin", "admin123", [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$pdo->exec("
CREATE TABLE IF NOT EXISTS admin.realtime_events (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER,
    event_type VARCHAR(50),
    email VARCHAR(255),
    ip_address VARCHAR(50),
    user_agent TEXT,
    country VARCHAR(10),
    device VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin.hourly_stats (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER,
    hour TIMESTAMP,
    sent INTEGER DEFAULT 0,
    delivered INTEGER DEFAULT 0,
    opened INTEGER DEFAULT 0,
    clicked INTEGER DEFAULT 0,
    bounced INTEGER DEFAULT 0,
    complained INTEGER DEFAULT 0,
    UNIQUE(campaign_id, hour)
);

CREATE INDEX IF NOT EXISTS idx_realtime_events_time ON admin.realtime_events(created_at);
CREATE INDEX IF NOT EXISTS idx_realtime_events_campaign ON admin.realtime_events(campaign_id);
");

class RealtimeAnalytics {
    private $pdo;
    
    public function __construct($pdo) { $this->pdo = $pdo; }
    
    public function recordEvent($data) {
        $this->pdo->prepare("INSERT INTO admin.realtime_events (campaign_id, event_type, email, ip_address, user_agent, country, device) VALUES (?, ?, ?, ?, ?, ?, ?)")
            ->execute([
                $data['campaign_id'] ?? 0, $data['event_type'],
                $data['email'] ?? '', $data['ip'] ?? $_SERVER['REMOTE_ADDR'] ?? '',
                $data['user_agent'] ?? $_SERVER['HTTP_USER_AGENT'] ?? '',
                $data['country'] ?? $this->detectCountry($data['ip'] ?? ''),
                $data['device'] ?? $this->detectDevice($data['user_agent'] ?? '')
            ]);
        
        // Update hourly stats
        $hour = date('Y-m-d H:00:00');
        $campaignId = $data['campaign_id'] ?? 0;
        $eventType = $data['event_type'];
        $column = $this->eventToColumn($eventType);
        
        if ($column) {
            $this->pdo->exec("INSERT INTO admin.hourly_stats (campaign_id, hour, $column) VALUES ($campaignId, '$hour', 1) ON CONFLICT (campaign_id, hour) DO UPDATE SET $column = admin.hourly_stats.$column + 1");
        }
        
        return ['success' => true];
    }
    
    private function eventToColumn($event) {
        $map = ['send' => 'sent', 'deliver' => 'delivered', 'open' => 'opened', 'click' => 'clicked', 'bounce' => 'bounced', 'complaint' => 'complained'];
        return $map[$event] ?? null;
    }
    
    private function detectCountry($ip) {
        // Simple GeoIP (would use MaxMind in production)
        return 'US';
    }
    
    private function detectDevice($ua) {
        if (stripos($ua, 'mobile') !== false) return 'mobile';
        if (stripos($ua, 'tablet') !== false) return 'tablet';
        return 'desktop';
    }
    
    public function getLiveStats($campaignId = null, $minutes = 60) {
        $where = $campaignId ? "AND campaign_id = $campaignId" : "";
        
        return [
            'period' => "$minutes minutes",
            'events' => $this->pdo->query("SELECT event_type, COUNT(*) as count FROM admin.realtime_events WHERE created_at > NOW() - INTERVAL '$minutes minutes' $where GROUP BY event_type")->fetchAll(PDO::FETCH_ASSOC),
            'by_minute' => $this->pdo->query("SELECT DATE_TRUNC('minute', created_at) as minute, event_type, COUNT(*) as count FROM admin.realtime_events WHERE created_at > NOW() - INTERVAL '$minutes minutes' $where GROUP BY minute, event_type ORDER BY minute DESC LIMIT 100")->fetchAll(PDO::FETCH_ASSOC),
            'unique_opens' => $this->pdo->query("SELECT COUNT(DISTINCT email) FROM admin.realtime_events WHERE event_type = 'open' AND created_at > NOW() - INTERVAL '$minutes minutes' $where")->fetchColumn(),
            'unique_clicks' => $this->pdo->query("SELECT COUNT(DISTINCT email) FROM admin.realtime_events WHERE event_type = 'click' AND created_at > NOW() - INTERVAL '$minutes minutes' $where")->fetchColumn()
        ];
    }
    
    public function getCampaignPerformance($campaignId) {
        $campaign = $this->pdo->query("SELECT * FROM admin.campaigns WHERE id = $campaignId")->fetch(PDO::FETCH_ASSOC);
        $hourly = $this->pdo->query("SELECT * FROM admin.hourly_stats WHERE campaign_id = $campaignId ORDER BY hour DESC LIMIT 24")->fetchAll(PDO::FETCH_ASSOC);
        
        $totals = $this->pdo->query("SELECT 
            SUM(sent) as sent, SUM(delivered) as delivered, SUM(opened) as opened, 
            SUM(clicked) as clicked, SUM(bounced) as bounced, SUM(complained) as complained 
            FROM admin.hourly_stats WHERE campaign_id = $campaignId")->fetch(PDO::FETCH_ASSOC);
        
        $sent = $totals['sent'] ?? 1;
        
        return [
            'campaign' => $campaign,
            'totals' => $totals,
            'rates' => [
                'delivery_rate' => round(($totals['delivered'] ?? 0) / max(1, $sent) * 100, 2),
                'open_rate' => round(($totals['opened'] ?? 0) / max(1, $sent) * 100, 2),
                'click_rate' => round(($totals['clicked'] ?? 0) / max(1, $sent) * 100, 2),
                'bounce_rate' => round(($totals['bounced'] ?? 0) / max(1, $sent) * 100, 2),
                'complaint_rate' => round(($totals['complained'] ?? 0) / max(1, $sent) * 100, 4)
            ],
            'hourly' => $hourly,
            'by_device' => $this->pdo->query("SELECT device, COUNT(*) as count FROM admin.realtime_events WHERE campaign_id = $campaignId GROUP BY device")->fetchAll(PDO::FETCH_ASSOC),
            'by_country' => $this->pdo->query("SELECT country, COUNT(*) as count FROM admin.realtime_events WHERE campaign_id = $campaignId GROUP BY country ORDER BY count DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC)
        ];
    }
    
    public function getGlobalStats() {
        return [
            'last_hour' => [
                'sent' => $this->pdo->query("SELECT COUNT(*) FROM admin.realtime_events WHERE event_type = 'send' AND created_at > NOW() - INTERVAL '1 hour'")->fetchColumn(),
                'opens' => $this->pdo->query("SELECT COUNT(*) FROM admin.realtime_events WHERE event_type = 'open' AND created_at > NOW() - INTERVAL '1 hour'")->fetchColumn(),
                'clicks' => $this->pdo->query("SELECT COUNT(*) FROM admin.realtime_events WHERE event_type = 'click' AND created_at > NOW() - INTERVAL '1 hour'")->fetchColumn()
            ],
            'today' => [
                'sent' => $this->pdo->query("SELECT COUNT(*) FROM admin.realtime_events WHERE event_type = 'send' AND DATE(created_at) = CURRENT_DATE")->fetchColumn(),
                'opens' => $this->pdo->query("SELECT COUNT(*) FROM admin.realtime_events WHERE event_type = 'open' AND DATE(created_at) = CURRENT_DATE")->fetchColumn(),
                'clicks' => $this->pdo->query("SELECT COUNT(*) FROM admin.realtime_events WHERE event_type = 'click' AND DATE(created_at) = CURRENT_DATE")->fetchColumn()
            ],
            'active_campaigns' => $this->pdo->query("SELECT COUNT(DISTINCT campaign_id) FROM admin.realtime_events WHERE created_at > NOW() - INTERVAL '1 hour'")->fetchColumn()
        ];
    }
}

$analytics = new RealtimeAnalytics($pdo);
$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch ($action) {
    case 'event':
        echo json_encode($analytics->recordEvent($_POST));
        break;
    case 'live':
        echo json_encode($analytics->getLiveStats($_GET['campaign_id'] ?? null, $_GET['minutes'] ?? 60));
        break;
    case 'campaign':
        echo json_encode($analytics->getCampaignPerformance($_GET['campaign_id']));
        break;
    case 'global':
        echo json_encode($analytics->getGlobalStats());
        break;
    default:
        echo json_encode(['name' => 'Realtime Analytics', 'actions' => ['event','live','campaign','global']]);
}