<?php

namespace App\Master\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Models\TenantDailyMetric;
use Carbon\Carbon;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;
use Mtc\MercuryDataModels\Tenant;

class SystemDashboardController extends Controller
{
    /**
     * Get system dashboard data including live site statistics.
     */
    public function index(): JsonResponse
    {
        return response()->json([
            'live_sites_count' => $this->getLiveSitesCount(),
            'in_development_count' => $this->getInDevelopmentCount(),
            'sites_by_month' => $this->getSitesByMonth(),
            'daily_metrics' => $this->getDailyMetrics(),
            'metrics_by_day' => $this->getMetricsByDay(),
        ]);
    }

    /**
     * Get the current count of live sites (not suspended).
     */
    protected function getLiveSitesCount(): int
    {
        return Tenant::query()
            ->whereNotNull('live_at')
            ->whereNull('suspended_at')
            ->count();
    }

    /**
     * Get the current count of sites in development (not live and not suspended).
     */
    protected function getInDevelopmentCount(): int
    {
        return Tenant::query()
            ->whereNull('live_at')
            ->whereNull('suspended_at')
            ->count();
    }

    /**
     * Get the count of live sites for each month over the last 12 months.
     * Shows cumulative count of sites that were live by end of each month.
     */
    protected function getSitesByMonth(): array
    {
        $months = [];
        $now = Carbon::now();

        // Generate the last 12 months
        for ($i = 11; $i >= 0; $i--) {
            $date = $now->copy()->subMonths($i);
            $monthKey = $date->format('Y-m');
            $months[$monthKey] = [
                'month' => $date->format('M Y'),
                'count' => 0,
            ];
        }

        // Get sites that were live by the end of each month
        foreach ($months as $monthKey => &$monthData) {
            $endOfMonth = Carbon::createFromFormat('Y-m', $monthKey)->endOfMonth();

            $monthData['count'] = Tenant::query()
                ->whereNotNull('live_at')
                ->where('live_at', '<=', $endOfMonth)
                ->count();
        }

        return array_values($months);
    }

    /**
     * Get aggregated daily metrics for the last 90 days.
     */
    protected function getDailyMetrics(): array
    {
        $ninetyDaysAgo = Carbon::now()->subDays(90)->startOfDay();

        $totals = TenantDailyMetric::query()
            ->where('date', '>=', $ninetyDaysAgo)
            ->selectRaw('
                SUM(enquiries_count) as enquiries,
                SUM(service_bookings_count) as service_bookings,
                SUM(reservations_count) as reservations,
                SUM(valuations_count) as valuations,
                SUM(vehicles_added) as vehicles_added,
                SUM(vehicles_exited) as vehicles_exited,
                SUM(visitors) as visitors,
                SUM(revenue) as revenue
            ')
            ->first();

        $visitors = (int) ($totals->visitors ?? 0);
        $enquiries = (int) ($totals->enquiries ?? 0);
        $serviceBookings = (int) ($totals->service_bookings ?? 0);
        $reservations = (int) ($totals->reservations ?? 0);

        return [
            'period' => 'Last 90 days',
            'enquiries' => $enquiries,
            'service_bookings' => $serviceBookings,
            'reservations' => $reservations,
            'valuations' => (int) ($totals->valuations ?? 0),
            'vehicles_added' => (int) ($totals->vehicles_added ?? 0),
            'vehicles_exited' => (int) ($totals->vehicles_exited ?? 0),
            'visitors' => $visitors,
            'revenue' => (float) ($totals->revenue ?? 0),
            'enquiry_conversion_rate' => $visitors > 0 ? round(($enquiries / $visitors) * 100, 2) : 0,
            'booking_conversion_rate' => $visitors > 0 ? round(($serviceBookings / $visitors) * 100, 2) : 0,
            'reservation_conversion_rate' => $visitors > 0 ? round(($reservations / $visitors) * 100, 2) : 0,
        ];
    }

    /**
     * Get metrics grouped by day for the last 90 days.
     */
    protected function getMetricsByDay(): array
    {
        $ninetyDaysAgo = Carbon::now()->subDays(90)->startOfDay();

        return TenantDailyMetric::query()
            ->where('date', '>=', $ninetyDaysAgo)
            ->selectRaw('
                date,
                SUM(enquiries_count) as enquiries,
                SUM(service_bookings_count) as service_bookings,
                SUM(reservations_count) as reservations,
                SUM(valuations_count) as valuations,
                SUM(vehicles_added) as vehicles_added,
                SUM(vehicles_exited) as vehicles_exited,
                SUM(visitors) as visitors,
                SUM(revenue) as revenue
            ')
            ->groupBy('date')
            ->orderBy('date')
            ->get()
            ->map(fn ($row) => [
                'date' => Carbon::parse($row->date)->format('d M'),
                'enquiries' => (int) $row->enquiries,
                'service_bookings' => (int) $row->service_bookings,
                'reservations' => (int) $row->reservations,
                'valuations' => (int) $row->valuations,
                'vehicles_added' => (int) $row->vehicles_added,
                'vehicles_exited' => (int) $row->vehicles_exited,
                'visitors' => (int) $row->visitors,
                'revenue' => (float) $row->revenue,
            ])
            ->toArray();
    }
}
