app/Domain/Analytics/AnalyticsAdminRepository.php

<?php

declare(strict_types=1);

namespace App\Domain\Analytics;

use App\Infrastructure\Database\BaseRepository;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Read-only query layer for the admin analytics dashboard.
 * Hits the rollup tables (analytics_daily_page_views, analytics_daily_funnels)
 * rather than the raw event log — queries are cheap and deterministic.
 *
 * All aggregate SQL uses ANSI CASE WHEN instead of MySQL IF() so it runs
 * correctly on both SQLite (HF runtime) and MySQL (cPanel production).
 */
final class AnalyticsAdminRepository extends BaseRepository
{
    protected function table(): string
    {
        return 'analytics_daily_page_views';
    }

    protected function hasDeletedAtColumn(): bool
    {
        return false;
    }

    /** Top-line acquisition metrics for the last $days calendar days. */
    public function acquisitionSummary(int $days = 30): object
    {
        if (! Schema::hasTable('analytics_daily_page_views')) {
            return (object) ['page_views' => 0, 'unique_visitors' => 0, 'sessions' => 0, 'avg_pages_per_session' => 0];
        }

        $since = now()->subDays($days)->toDateString();

        return DB::table('analytics_daily_page_views')
            ->selectRaw("
                COALESCE(SUM(page_views), 0)                                                 AS page_views,
                COALESCE(SUM(unique_visitors), 0)                                            AS unique_visitors,
                COALESCE(SUM(sessions), 0)                                                   AS sessions,
                CASE WHEN COALESCE(SUM(sessions), 0) > 0
                     THEN ROUND(CAST(SUM(page_views) AS REAL) / SUM(sessions), 1)
                     ELSE 0 END                                                              AS avg_pages_per_session
            ")
            ->where('summary_date', '>=', $since)
            ->first()
            ?? (object) ['page_views' => 0, 'unique_visitors' => 0, 'sessions' => 0, 'avg_pages_per_session' => 0];
    }

    /**
     * Traffic channel breakdown for the last $days days.
     * Falls back to an empty array when analytics_sessions doesn't exist yet.
     */
    public function channelBreakdown(int $days = 30): array
    {
        if (! Schema::hasTable('analytics_sessions')) {
            return [];
        }

        $since = now()->subDays($days)->toDateTimeString();

        return DB::table('analytics_sessions')
            ->selectRaw("
                COALESCE(channel_group, 'direct')   AS channel,
                COUNT(*)                            AS sessions,
                COUNT(DISTINCT visitor_id)          AS unique_visitors
            ")
            ->where('is_bot', 0)
            ->where('started_at', '>=', $since)
            ->groupByRaw("COALESCE(channel_group, 'direct')")
            ->orderByDesc('sessions')
            ->get()
            ->toArray();
    }

    /** Top pages by views for the last $days days. */
    public function topPages(int $days = 30, int $limit = 10): array
    {
        if (! Schema::hasTable('analytics_daily_page_views')) {
            return [];
        }

        $since = now()->subDays($days)->toDateString();

        return DB::table('analytics_daily_page_views')
            ->selectRaw("
                path,
                module,
                SUM(page_views)      AS page_views,
                SUM(unique_visitors) AS unique_visitors
            ")
            ->where('summary_date', '>=', $since)
            ->groupBy('path', 'module')
            ->orderByDesc(DB::raw('SUM(page_views)'))
            ->limit($limit)
            ->get()
            ->toArray();
    }

    /** Conversion summary per module for the last $days days. */
    public function conversionSummary(int $days = 30): array
    {
        if (! Schema::hasTable('analytics_daily_funnels')) {
            return [];
        }

        $since = now()->subDays($days)->toDateString();

        return DB::table('analytics_daily_funnels')
            ->selectRaw("
                module,
                SUM(starts)       AS starts,
                SUM(submissions)  AS submissions,
                SUM(abandonments) AS abandonments,
                CASE WHEN SUM(starts) > 0
                     THEN ROUND(CAST(SUM(submissions) AS REAL) / SUM(starts) * 100, 1)
                     ELSE 0 END   AS conversion_rate
            ")
            ->where('summary_date', '>=', $since)
            ->groupBy('module')
            ->orderByDesc(DB::raw('SUM(submissions)'))
            ->get()
            ->toArray();
    }

    /** Overall conversion KPIs across all modules for the last $days days. */
    public function conversionKpis(int $days = 30): object
    {
        if (! Schema::hasTable('analytics_daily_funnels')) {
            return (object) ['apply_starts' => 0, 'submissions' => 0, 'conversion_rate' => 0.0, 'top_module' => null];
        }

        $since = now()->subDays($days)->toDateString();

        $row = DB::table('analytics_daily_funnels')
            ->selectRaw("
                COALESCE(SUM(starts), 0)      AS apply_starts,
                COALESCE(SUM(submissions), 0) AS submissions,
                CASE WHEN COALESCE(SUM(starts), 0) > 0
                     THEN ROUND(CAST(SUM(submissions) AS REAL) / SUM(starts) * 100, 1)
                     ELSE 0 END               AS conversion_rate
            ")
            ->where('summary_date', '>=', $since)
            ->first();

        $topModule = DB::table('analytics_daily_funnels')
            ->selectRaw('module, SUM(submissions) AS total')
            ->where('summary_date', '>=', $since)
            ->groupBy('module')
            ->orderByDesc(DB::raw('SUM(submissions)'))
            ->value('module');

        return (object) [
            'apply_starts'    => (int) ($row->apply_starts ?? 0),
            'submissions'     => (int) ($row->submissions  ?? 0),
            'conversion_rate' => (float) ($row->conversion_rate ?? 0),
            'top_module'      => $topModule ?? null,
        ];
    }

    /** Daily page-view time-series for sparkline charts (last $days days). */
    public function dailyPageViews(int $days = 30): array
    {
        if (! Schema::hasTable('analytics_daily_page_views')) {
            return [];
        }

        $since = now()->subDays($days)->toDateString();

        return DB::table('analytics_daily_page_views')
            ->selectRaw('summary_date, SUM(page_views) AS page_views, SUM(sessions) AS sessions')
            ->where('summary_date', '>=', $since)
            ->groupBy('summary_date')
            ->orderBy('summary_date')
            ->get()
            ->toArray();
    }
}