<?php
// Daily aggregation from analytics_events to analytics_daily_table_stats
// Usage:
//   php scripts/analytics_aggregate_daily.php
//   php scripts/analytics_aggregate_daily.php 2026-05-28

require_once __DIR__ . '/../config/database.php';

$targetDate = $argv[1] ?? date('Y-m-d', strtotime('-1 day'));

if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $targetDate)) {
    fwrite(STDERR, "Invalid date format. Use YYYY-MM-DD\n");
    exit(1);
}

try {
    $pdo = getAnalyticsPdo();
    $pdo->beginTransaction();

    $deleteStmt = $pdo->prepare("DELETE FROM analytics_daily_table_stats WHERE stat_date = :stat_date");
    $deleteStmt->execute([':stat_date' => $targetDate]);

    $insertSql = "
        INSERT INTO analytics_daily_table_stats (
            stat_date,
            table_id,
            zone,
            qr_scans,
            sessions,
            unique_devices,
            geo_pass,
            geo_fail,
            bill_opened,
            bill_completed
        )
        SELECT
            DATE(created_at) AS stat_date,
            COALESCE(NULLIF(table_id, ''), 'unknown') AS table_id,
            COALESCE(NULLIF(zone, ''), 'unknown') AS zone,
            SUM(CASE WHEN event_name = 'qr_scan' THEN 1 ELSE 0 END) AS qr_scans,
            SUM(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 END) AS sessions,
            COUNT(DISTINCT ip_hash) AS unique_devices,
            SUM(CASE WHEN event_name = 'geo_check_passed' THEN 1 ELSE 0 END) AS geo_pass,
            SUM(CASE WHEN event_name = 'geo_check_failed' THEN 1 ELSE 0 END) AS geo_fail,
            SUM(CASE WHEN event_name = 'bill_dialog_opened' THEN 1 ELSE 0 END) AS bill_opened,
            SUM(CASE WHEN event_name = 'bill_request_sent' THEN 1 ELSE 0 END) AS bill_completed
        FROM analytics_events
        WHERE DATE(created_at) = :stat_date
        GROUP BY DATE(created_at), COALESCE(NULLIF(table_id, ''), 'unknown'), COALESCE(NULLIF(zone, ''), 'unknown')
    ";

    $insertStmt = $pdo->prepare($insertSql);
    $insertStmt->execute([':stat_date' => $targetDate]);

    $pdo->commit();
    echo "OK: Aggregation completed for {$targetDate}\n";
} catch (Throwable $e) {
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    fwrite(STDERR, "Aggregation failed: {$e->getMessage()}\n");
    exit(1);
}