<?php

namespace Modules\ActivityLog\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

/**
 * TPS insights: an activity heat-map (per user & per business) built from
 * enhanced_activity_logs, plus a per-tenant trial "scorecard" that folds in the
 * activity_incidents ("mistakes") axis. Permission-gated — never visible to
 * ordinary tenants.
 */
class InsightsController extends Controller
{
    public function index()
    {
        $this->authorizeInsights();

        // ── Demo tenants + aggregate activity / incidents ────────────────
        $tenants = DB::table('business')
            ->whereNotNull('demo_expires_at')
            ->orderByDesc('demo_created_at')
            ->get(['id', 'name', 'demo_status', 'demo_created_at', 'demo_expires_at']);

        $activity = DB::table('enhanced_activity_logs')
            ->select('business_id', DB::raw('COUNT(*) as c'), DB::raw('MAX(created_at) as last_at'))
            ->groupBy('business_id')->pluck('c', 'business_id');
        $lastAt = DB::table('enhanced_activity_logs')
            ->select('business_id', DB::raw('MAX(created_at) as last_at'))
            ->groupBy('business_id')->pluck('last_at', 'business_id');
        $incidents = DB::table('activity_incidents')
            ->select('business_id', DB::raw('COUNT(*) as c'))
            ->groupBy('business_id')->pluck('c', 'business_id');

        $rows = $tenants->map(function ($t) use ($activity, $lastAt, $incidents) {
            return [
                'id'         => $t->id,
                'name'       => $t->name,
                'status'     => $t->demo_status,
                'created_at' => $t->demo_created_at,
                'expires_at' => $t->demo_expires_at,
                'activity'   => (int) ($activity[$t->id] ?? 0),
                'incidents'  => (int) ($incidents[$t->id] ?? 0),
                'last_at'    => $lastAt[$t->id] ?? null,
            ];
        });

        // ── Global day × hour heat-map (last 14 days) ────────────────────
        $heat = $this->heatGrid();

        // ── Top users by activity (user heat axis) ───────────────────────
        $topUsers = DB::table('enhanced_activity_logs as l')
            ->leftJoin('users as u', 'u.id', '=', 'l.user_id')
            ->select('l.user_id', DB::raw('COUNT(*) as c'),
                DB::raw("MAX(l.business_id) as business_id"),
                DB::raw("MAX(CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.surname,''))) as name"))
            ->groupBy('l.user_id')->orderByDesc('c')->limit(25)->get();

        return view('activityLog::insights.index', [
            'tenants'  => $rows,
            'heat'     => $heat,
            'topUsers' => $topUsers,
        ]);
    }

    /** Per-tenant trial scorecard. */
    public function tenant($businessId)
    {
        $this->authorizeInsights();

        $business = DB::table('business')->where('id', $businessId)->first();
        abort_if(! $business, 404);

        $logs = DB::table('enhanced_activity_logs')->where('business_id', $businessId);

        $total    = (clone $logs)->count();
        $logins   = (clone $logs)->where('action', 'login')->count();
        $lastAt   = (clone $logs)->max('created_at');
        $byAction = (clone $logs)->select('action', DB::raw('COUNT(*) c'))->groupBy('action')->pluck('c', 'action');
        $coverage = (clone $logs)->select('model_type', DB::raw('COUNT(*) c'))
            ->groupBy('model_type')->orderByDesc('c')->limit(40)->pluck('c', 'model_type');

        $perUser = DB::table('enhanced_activity_logs as l')
            ->leftJoin('users as u', 'u.id', '=', 'l.user_id')
            ->where('l.business_id', $businessId)
            ->select('l.user_id', DB::raw('COUNT(*) c'),
                DB::raw("MAX(CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.surname,''))) as name"),
                DB::raw('MAX(l.created_at) as last_at'))
            ->groupBy('l.user_id')->orderByDesc('c')->get();

        $incByType = DB::table('activity_incidents')->where('business_id', $businessId)
            ->select('type', DB::raw('COUNT(*) c'))->groupBy('type')->pluck('c', 'type');
        $recentIncidents = DB::table('activity_incidents')->where('business_id', $businessId)
            ->orderByDesc('created_at')->limit(30)->get();

        $waConfigured   = $this->tableHas('wa_channels', fn ($q) => $q->where('business_id', $businessId)->where('status', 'active'));
        $groqConfigured = $this->tableHas('ai_providers', fn ($q) => $q->where('business_id', $businessId)->whereNotNull('api_key')->where('api_key', '!=', ''));

        $firstAppt = $this->tableValue('vc_appointments', fn ($q) => $q->where('business_id', $businessId)->min('created_at'));

        return view('activityLog::insights.tenant', [
            'business'        => $business,
            'total'           => $total,
            'logins'          => $logins,
            'lastAt'          => $lastAt,
            'byAction'        => $byAction,
            'coverage'        => $coverage,
            'perUser'         => $perUser,
            'incByType'       => $incByType,
            'recentIncidents' => $recentIncidents,
            'waConfigured'    => $waConfigured,
            'groqConfigured'  => $groqConfigured,
            'firstAppt'       => $firstAppt,
        ]);
    }

    /* ─────────────────────────── helpers ──────────────────────────────── */

    /** @return array{days:array,grid:array} counts keyed [date][hour]. */
    protected function heatGrid(): array
    {
        $since = now()->subDays(13)->startOfDay();
        $raw = DB::table('enhanced_activity_logs')
            ->where('created_at', '>=', $since)
            ->select(
                DB::raw('DATE(created_at) as d'),
                DB::raw('HOUR(created_at) as h'),
                DB::raw('COUNT(*) as c')
            )->groupBy('d', 'h')->get();

        $grid = [];
        $max = 0;
        foreach ($raw as $r) {
            $grid[$r->d][$r->h] = (int) $r->c;
            $max = max($max, (int) $r->c);
        }

        $days = [];
        for ($i = 0; $i < 14; $i++) {
            $days[] = $since->copy()->addDays($i)->toDateString();
        }

        return ['days' => $days, 'grid' => $grid, 'max' => $max ?: 1];
    }

    protected function tableHas(string $table, \Closure $scope): bool
    {
        try {
            $q = DB::table($table);
            $scope($q);
            return $q->exists();
        } catch (\Throwable) {
            return false;
        }
    }

    protected function tableValue(string $table, \Closure $scope)
    {
        try {
            $q = DB::table($table);
            return $scope($q);
        } catch (\Throwable) {
            return null;
        }
    }

    protected function authorizeInsights(): void
    {
        $user = Auth::user();
        abort_unless($user !== null, 403);

        $perm = 'activitylog.tps_insights';
        $isOwner = false;
        try {
            $bizId = $user->business_id;
            $isOwner = $bizId && DB::table('business')->where('id', $bizId)->where('owner_id', $user->id)->exists();
        } catch (\Throwable) {
        }

        abort_unless($isOwner || $user->can($perm), 403);
    }
}
