karczma-aplikacja-stoliki/api/analytics_reports.php

<?php
header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../public/staff/auth.php';
require_once __DIR__ . '/message_text_helper.php';

if ($_SERVER['REQUEST_METHOD'] !== 'GET') {
    http_response_code(405);
    echo json_encode([
        'status' => 'error',
        'message' => 'Method not allowed'
    ], JSON_UNESCAPED_UNICODE);
    exit;
}

if (!isAdminLoggedIn()) {
    http_response_code(401);
    echo json_encode([
        'status' => 'error',
        'message' => 'Unauthorized'
    ], JSON_UNESCAPED_UNICODE);
    exit;
}

$range = isset($_GET['days']) ? trim((string)$_GET['days']) : '7';
$allowedRanges = ['today', '7', '30', '90', 'all', 'this_year', 'last_year'];
if (!in_array($range, $allowedRanges, true)) {
    $range = '7';
}

$whereWindow = '';
$baseParams = [];
$now = new DateTimeImmutable('now');

switch ($range) {
    case 'all':
        break;
    case 'today':
        $start = new DateTimeImmutable('today');
        $end = $start->modify('+1 day');
        $whereWindow = ' AND created_at >= :start_at AND created_at < :end_at';
        $baseParams = [
            ':start_at' => $start->format('Y-m-d H:i:s'),
            ':end_at' => $end->format('Y-m-d H:i:s'),
        ];
        break;
    case 'this_year':
        $start = new DateTimeImmutable(date('Y-01-01 00:00:00'));
        $end = $start->modify('+1 year');
        $whereWindow = ' AND created_at >= :start_at AND created_at < :end_at';
        $baseParams = [
            ':start_at' => $start->format('Y-m-d H:i:s'),
            ':end_at' => $end->format('Y-m-d H:i:s'),
        ];
        break;
    case 'last_year':
        $start = new DateTimeImmutable((date('Y') - 1) . '-01-01 00:00:00');
        $end = $start->modify('+1 year');
        $whereWindow = ' AND created_at >= :start_at AND created_at < :end_at';
        $baseParams = [
            ':start_at' => $start->format('Y-m-d H:i:s'),
            ':end_at' => $end->format('Y-m-d H:i:s'),
        ];
        break;
    default:
        $daysInt = (int)$range;
        if ($daysInt < 1) $daysInt = 1;
        if ($daysInt > 3650) $daysInt = 3650;
        $start = $now->modify("-{$daysInt} days");
        $whereWindow = ' AND created_at >= :start_at';
        $baseParams = [
            ':start_at' => $start->format('Y-m-d H:i:s'),
        ];
        break;
}

try {
    $pdo = getAnalyticsPdo();

    $sqlTopTables = "
        SELECT
            COALESCE(NULLIF(table_id, ''), 'unknown') AS table_id,
            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,
            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
        FROM analytics_events
        WHERE 1=1 {$whereWindow}
        GROUP BY COALESCE(NULLIF(table_id, ''), 'unknown')
        ORDER BY qr_scans DESC, sessions DESC
        LIMIT 20
    ";
    $stmtTopTables = $pdo->prepare($sqlTopTables);
    $stmtTopTables->execute($baseParams);
    $topTables = $stmtTopTables->fetchAll();

    $sqlByZone = "
        SELECT
            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,
            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
        FROM analytics_events
        WHERE 1=1 {$whereWindow}
        GROUP BY COALESCE(NULLIF(zone, ''), 'unknown')
        ORDER BY sessions DESC, qr_scans DESC
    ";
    $stmtByZone = $pdo->prepare($sqlByZone);
    $stmtByZone->execute($baseParams);
    $zoneStats = $stmtByZone->fetchAll();

    $sqlFunnel = "
        SELECT
            event_name,
            COUNT(*) AS total
        FROM analytics_events
        WHERE 1=1 {$whereWindow}
          AND event_name IN ('qr_scan','session_start','view_menu','bill_dialog_opened','bill_request_sent','waiter_call_requested','menu_only_entered','geo_gate_prompted','geo_retry_from_menu')
        GROUP BY event_name
    ";
    $stmtFunnel = $pdo->prepare($sqlFunnel);
    $stmtFunnel->execute($baseParams);
    $funnelRows = $stmtFunnel->fetchAll();
    $funnelMap = [];
    foreach ($funnelRows as $row) {
        $funnelMap[$row['event_name']] = (int)$row['total'];
    }

    $sqlGeo = "
        SELECT
            SUM(CASE WHEN event_name = 'geo_check_passed' THEN 1 ELSE 0 END) AS geo_passed,
            SUM(CASE WHEN event_name = 'geo_check_failed' THEN 1 ELSE 0 END) AS geo_failed,
            SUM(CASE WHEN event_name = 'geo_bypass_host' THEN 1 ELSE 0 END) AS geo_bypass
        FROM analytics_events
        WHERE 1=1 {$whereWindow}
    ";
    $stmtGeo = $pdo->prepare($sqlGeo);
    $stmtGeo->execute($baseParams);
    $geo = $stmtGeo->fetch() ?: ['geo_passed' => 0, 'geo_failed' => 0, 'geo_bypass' => 0];

    $sqlDeviceStats = "
        SELECT
            COALESCE(NULLIF(device_type, ''), 'other') AS device_type,
            COUNT(*) AS total
        FROM analytics_events
        WHERE event_name = 'qr_scan' {$whereWindow}
        GROUP BY COALESCE(NULLIF(device_type, ''), 'other')
    ";
    $stmtDeviceStats = $pdo->prepare($sqlDeviceStats);
    $stmtDeviceStats->execute($baseParams);
    $deviceRows = $stmtDeviceStats->fetchAll();
    $deviceStats = ['ios' => 0, 'android' => 0, 'other' => 0];
    foreach ($deviceRows as $row) {
        $key = (string) ($row['device_type'] ?? 'other');
        if (!isset($deviceStats[$key])) {
            $deviceStats['other'] += (int) $row['total'];
        } else {
            $deviceStats[$key] = (int) $row['total'];
        }
    }

    $sqlRecentOpens = "
        SELECT
            created_at,
            session_id,
            table_id,
            device_type,
            browser,
            JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.ipAddress')) AS ip_address
        FROM analytics_events
        WHERE event_name = 'qr_scan' {$whereWindow}
        ORDER BY created_at DESC
        LIMIT 50
    ";
    $stmtRecentOpens = $pdo->prepare($sqlRecentOpens);
    $stmtRecentOpens->execute($baseParams);
    $recentOpens = $stmtRecentOpens ? $stmtRecentOpens->fetchAll() : [];

    $sessionOutcomes = [];
    $sessionIds = [];
    foreach ($recentOpens as $openRow) {
        $sid = trim((string) ($openRow['session_id'] ?? ''));
        if ($sid !== '') {
            $sessionIds[$sid] = true;
        }
    }
    $sessionIds = array_keys($sessionIds);

    if (!empty($sessionIds)) {
        $placeholders = implode(',', array_fill(0, count($sessionIds), '?'));
        $sqlSessionFlags = "
            SELECT
                session_id,
                MAX(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 END) AS reached_app,
                MAX(CASE WHEN event_name = 'menu_only_entered' THEN 1 ELSE 0 END) AS menu_only,
                MAX(CASE WHEN event_name = 'view_menu' THEN 1 ELSE 0 END) AS entered_menu
            FROM analytics_events
            WHERE session_id IN ({$placeholders})
            GROUP BY session_id
        ";
        $stmtSessionFlags = $pdo->prepare($sqlSessionFlags);
        $stmtSessionFlags->execute($sessionIds);
        while ($flagRow = $stmtSessionFlags->fetch()) {
            $sessionOutcomes[$flagRow['session_id']] = [
                'reached_app' => (int) $flagRow['reached_app'],
                'menu_only' => (int) $flagRow['menu_only'],
                'entered_menu' => (int) $flagRow['entered_menu'],
            ];
        }

        $sqlVisitHistory = "
            SELECT session_id, created_at
            FROM analytics_events
            WHERE event_name = 'qr_scan'
              AND session_id IN ({$placeholders})
            ORDER BY session_id ASC, created_at ASC
        ";
        $stmtVisitHistory = $pdo->prepare($sqlVisitHistory);
        $stmtVisitHistory->execute($sessionIds);
        $visitHistoryBySession = [];
        while ($visitRow = $stmtVisitHistory->fetch()) {
            $sid = trim((string) ($visitRow['session_id'] ?? ''));
            if ($sid === '') {
                continue;
            }
            if (!isset($visitHistoryBySession[$sid])) {
                $visitHistoryBySession[$sid] = [];
            }
            $visitHistoryBySession[$sid][] = (string) $visitRow['created_at'];
        }
    } else {
        $visitHistoryBySession = [];
    }

    foreach ($recentOpens as &$openRow) {
        $sid = trim((string) ($openRow['session_id'] ?? ''));
        $flags = $sessionOutcomes[$sid] ?? ['reached_app' => 0, 'menu_only' => 0, 'entered_menu' => 0];
        $openRow['reached_app'] = $flags['reached_app'];
        $openRow['menu_only'] = $flags['menu_only'];
        $openRow['entered_menu'] = $flags['entered_menu'];

        $visitTimes = $visitHistoryBySession[$sid] ?? [];
        $openAt = (string) ($openRow['created_at'] ?? '');
        $visitNumber = 0;
        foreach ($visitTimes as $visitAt) {
            if ($visitAt <= $openAt) {
                $visitNumber++;
            }
        }
        if ($visitNumber < 1 && $openAt !== '') {
            $visitNumber = 1;
        }

        $openRow['visitor_id_short'] = $sid !== '' ? substr($sid, 0, 8) : null;
        $openRow['visitor_visit_number'] = $visitNumber;
        $openRow['visitor_total_visits'] = count($visitTimes);
        $openRow['visitor_first_seen_at'] = $visitTimes[0] ?? null;
        $openRow['visitor_is_returning'] = $visitNumber > 1 ? 1 : 0;
    }
    unset($openRow);

    $sqlVisitorSummary = "
        SELECT
            COUNT(DISTINCT session_id) AS unique_visitors,
            SUM(CASE WHEN scan_count > 1 THEN 1 ELSE 0 END) AS returning_visitors
        FROM (
            SELECT session_id, COUNT(*) AS scan_count
            FROM analytics_events
            WHERE event_name = 'qr_scan' {$whereWindow}
            GROUP BY session_id
        ) visitor_counts
    ";
    $stmtVisitorSummary = $pdo->prepare($sqlVisitorSummary);
    $stmtVisitorSummary->execute($baseParams);
    $visitorSummaryRow = $stmtVisitorSummary->fetch() ?: [
        'unique_visitors' => 0,
        'returning_visitors' => 0,
    ];
    $uniqueVisitors = (int) $visitorSummaryRow['unique_visitors'];
    $returningVisitors = (int) $visitorSummaryRow['returning_visitors'];

    $sqlQueueSummary = "
        SELECT
            COUNT(*) AS total_actions,
            SUM(CASE WHEN message_type = 'waiter_call' THEN 1 ELSE 0 END) AS waiter_calls,
            SUM(CASE WHEN message_type = 'bill_request' THEN 1 ELSE 0 END) AS bill_requests,
            SUM(CASE WHEN api_sent = 0 THEN 1 ELSE 0 END) AS pending_api,
            SUM(CASE WHEN status_kds = 0 THEN 1 ELSE 0 END) AS pending_kds,
            SUM(CASE WHEN status_kds = 1 THEN 1 ELSE 0 END) AS done_kds
        FROM guest_action_queue
        WHERE 1=1 {$whereWindow}
    ";
    $stmtQueueSummary = $pdo->prepare($sqlQueueSummary);
    $stmtQueueSummary->execute($baseParams);
    $queueSummary = $stmtQueueSummary->fetch() ?: [
        'total_actions' => 0,
        'waiter_calls' => 0,
        'bill_requests' => 0,
        'pending_api' => 0,
        'pending_kds' => 0,
        'done_kds' => 0,
    ];

    $sqlQueueItems = "
        SELECT
            id,
            table_id,
            message_type,
            message_text,
            otwierajacy_imie,
            otwierajacy_nazwisko,
            api_sent,
            status_kds,
            created_at
        FROM guest_action_queue
        WHERE 1=1 {$whereWindow}
        ORDER BY created_at DESC
        LIMIT 100
    ";
    $stmtQueueItems = $pdo->prepare($sqlQueueItems);
    $stmtQueueItems->execute($baseParams);
    $queueItems = $stmtQueueItems->fetchAll();
    foreach ($queueItems as &$queueRow) {
        $queueRow['message_text'] = normalizeQueueMessageText((string) ($queueRow['message_text'] ?? ''));
    }
    unset($queueRow);

    echo json_encode([
        'status' => 'success',
        'days' => $range,
        'topTables' => $topTables,
        'zoneStats' => $zoneStats,
        'funnel' => [
            'qr_scan' => (int)($funnelMap['qr_scan'] ?? 0),
            'session_start' => (int)($funnelMap['session_start'] ?? 0),
            'view_menu' => (int)($funnelMap['view_menu'] ?? 0),
            'bill_dialog_opened' => (int)($funnelMap['bill_dialog_opened'] ?? 0),
            'bill_request_sent' => (int)($funnelMap['bill_request_sent'] ?? 0),
            'waiter_call_requested' => (int)($funnelMap['waiter_call_requested'] ?? 0),
            'menu_only_entered' => (int)($funnelMap['menu_only_entered'] ?? 0),
            'geo_gate_prompted' => (int)($funnelMap['geo_gate_prompted'] ?? 0),
            'geo_retry_from_menu' => (int)($funnelMap['geo_retry_from_menu'] ?? 0),
        ],
        'geolocation' => [
            'passed' => (int)$geo['geo_passed'],
            'failed' => (int)$geo['geo_failed'],
            'bypass' => (int)$geo['geo_bypass'],
        ],
        'deviceStats' => $deviceStats,
        'visitorSummary' => [
            'uniqueVisitors' => $uniqueVisitors,
            'returningVisitors' => $returningVisitors,
        ],
        'recentOpens' => $recentOpens,
        'guestQueueSummary' => [
            'total' => (int)$queueSummary['total_actions'],
            'waiterCalls' => (int)$queueSummary['waiter_calls'],
            'billRequests' => (int)$queueSummary['bill_requests'],
            'pendingApi' => (int)$queueSummary['pending_api'],
            'pendingKds' => (int)$queueSummary['pending_kds'],
            'doneKds' => (int)$queueSummary['done_kds'],
        ],
        'guestQueue' => $queueItems,
    ], JSON_UNESCAPED_UNICODE);
} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode([
        'status' => 'error',
        'message' => 'Nie udało się pobrać raportów analitycznych.'
    ], JSON_UNESCAPED_UNICODE);
}