<?php
/** KPI Daily Aggregation Cron */
require_once dirname(__DIR__) . '/config.php';
$yesterday = date('Y-m-d', strtotime('-1 day'));

try {
    // AI metrics
    $ai = db()->prepare("SELECT COUNT(*) t, SUM(ie_hit) ie, SUM(cache_hit) c, SUM(ai_used) a, SUM(cost_usd) cost, AVG(latency_ms) lat, SUM(model_used='gemini-1.5-flash') gem, SUM(model_used LIKE '%claude%') cl, SUM(model_used LIKE '%gpt%') gpt FROM ai_requests WHERE DATE(created_at)=?");
    $ai->execute([$yesterday]); $aiRow = $ai->fetch();

    $users = (int)db()->prepare("SELECT COUNT(*) FROM users WHERE DATE(created_at)=?")->execute([$yesterday]) ? db()->prepare("SELECT COUNT(*) FROM users WHERE DATE(created_at)=?")->execute([$yesterday]) : 0;
    $newUsers = db()->prepare("SELECT COUNT(*) FROM users WHERE DATE(created_at)=?"); $newUsers->execute([$yesterday]); $nu = (int)$newUsers->fetchColumn();
    $newSubs  = db()->prepare("SELECT COUNT(*) FROM subscription_requests WHERE status='approved' AND DATE(updated_at)=?"); $newSubs->execute([$yesterday]); $ns = (int)$newSubs->fetchColumn();
    $revenue  = db()->prepare("SELECT SUM(amount_sdg) FROM subscription_requests WHERE status='approved' AND DATE(updated_at)=?"); $revenue->execute([$yesterday]); $rev = (float)$revenue->fetchColumn();
    $bkCrt    = db()->prepare("SELECT COUNT(*) FROM bookings WHERE DATE(created_at)=?"); $bkCrt->execute([$yesterday]); $bc = (int)$bkCrt->fetchColumn();
    $bkCmp    = db()->prepare("SELECT COUNT(*) FROM bookings WHERE status='completed' AND DATE(completed_at)=?"); $bkCmp->execute([$yesterday]); $bcomp = (int)$bkCmp->fetchColumn();
    $comm     = db()->prepare("SELECT SUM(commission_sdg) FROM provider_earnings WHERE DATE(created_at)=?"); $comm->execute([$yesterday]); $co = (float)$comm->fetchColumn();

    $total = (int)($aiRow['t'] ?? 0);
    db()->prepare("INSERT INTO kpi_daily (kpi_date,total_requests,ie_hits,cache_hits,ai_calls,ai_calls_gemini,ai_calls_claude,ai_calls_gpt,cost_usd_total,avg_latency_ms,new_users,new_subscriptions,revenue_sdg,bookings_created,bookings_completed,commission_sdg)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        ON DUPLICATE KEY UPDATE total_requests=VALUES(total_requests),ie_hits=VALUES(ie_hits),cache_hits=VALUES(cache_hits)"
    )->execute([$yesterday,$total,(int)($aiRow['ie']??0),(int)($aiRow['c']??0),(int)($aiRow['a']??0),(int)($aiRow['gem']??0),(int)($aiRow['cl']??0),(int)($aiRow['gpt']??0),round((float)($aiRow['cost']??0),6),(int)($aiRow['lat']??0),$nu,$ns,$rev,$bc,$bcomp,$co]);

    echo date('Y-m-d H:i:s') . " KPI aggregated for $yesterday\n";
} catch (Throwable $e) { echo "ERROR: ".$e->getMessage()."\n"; }
