<?php

namespace App\Master\Http\Controllers;

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

class SystemDashboardController extends Controller
{
    protected ?Carbon $fromDate = null;
    protected ?Carbon $toDate = null;
    protected ?string $tenantId = null;
    protected bool $includeListing = false;

    /**
     * Get system dashboard data including live site statistics.
     */
    public function index(Request $request): JsonResponse
    {
        $this->fromDate = $request->filled('from_date')
            ? Carbon::parse($request->input('from_date'))->startOfDay()
            : Carbon::now()->subDays(90)->startOfDay();

        $this->toDate = $request->filled('to_date')
            ? Carbon::parse($request->input('to_date'))->endOfDay()
            : Carbon::now()->endOfDay();

        $this->tenantId = $request->input('tenant_id');
        $this->includeListing = $request->boolean('include_listing', false);

        $response = [
            'live_sites_count' => $this->getLiveSitesCount(),
            'in_development_count' => $this->getInDevelopmentCount(),
            'sites_by_month' => $this->getSitesByMonth(),
            'daily_metrics' => $this->getDailyMetrics(),
            'metrics_by_day' => $this->getMetricsByDay(),
            'date_range' => [
                'from' => $this->fromDate->format('Y-m-d'),
                'to' => $this->toDate->format('Y-m-d'),
            ],
        ];

        if ($this->tenantId) {
            $response['average_metrics'] = $this->getAverageMetrics();
            $response['selected_tenant'] = Tenant::find($this->tenantId)?->only(['id', 'name']);
        }

        return response()->json($response);
    }

    /**
     * Get list of tenants for filtering.
     */
    public function tenants(Request $request): JsonResponse
    {
        $includeListing = $request->boolean('include_listing', false);

        $tenants = Tenant::query()
            ->whereNotNull('live_at')
            ->whereNull('suspended_at')
            ->when(!$includeListing, fn($query) => $query->where(function ($q) {
                $q->where('type', '!=', TenantType::LISTING->value)
                    ->orWhereNull('type');
            }))
            ->orderBy('name')
            ->get(['id', 'name']);

        return response()->json($tenants);
    }

    /**
     * Get tenant IDs to exclude (listing sites) for metric queries.
     */
    protected function getExcludedTenantIds(): array
    {
        if ($this->includeListing) {
            return [];
        }

        return Tenant::query()
            ->where('type', TenantType::LISTING->value)
            ->pluck('id')
            ->toArray();
    }

    /**
     * Get the current count of live sites (not suspended).
     */
    protected function getLiveSitesCount(): int
    {
        return Tenant::query()
            ->whereNotNull('live_at')
            ->whereNull('suspended_at')
            ->when(!$this->includeListing, fn($query) => $query->where(function ($q) {
                $q->where('type', '!=', TenantType::LISTING->value)
                    ->orWhereNull('type');
            }))
            ->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')
            ->when(!$this->includeListing, fn($query) => $query->where(function ($q) {
                $q->where('type', '!=', TenantType::LISTING->value)
                    ->orWhereNull('type');
            }))
            ->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)
                ->when(!$this->includeListing, fn($query) => $query->where(function ($q) {
                    $q->where('type', '!=', TenantType::LISTING->value)
                        ->orWhereNull('type');
                }))
                ->count();
        }

        return array_values($months);
    }

    /**
     * Get aggregated daily metrics for the selected date range.
     */
    protected function getDailyMetrics(): array
    {
        $excludedTenantIds = $this->getExcludedTenantIds();

        $totals = TenantDailyMetric::query()
            ->where('date', '>=', $this->fromDate)
            ->where('date', '<=', $this->toDate)
            ->when($this->tenantId, fn($query) => $query->where('tenant_id', $this->tenantId))
            ->when(!empty($excludedTenantIds), fn($query) => $query->whereNotIn('tenant_id', $excludedTenantIds))
            ->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);

        $periodLabel = $this->fromDate->format('d M Y') . ' - ' . $this->toDate->format('d M Y');

        return [
            'period' => $periodLabel,
            '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 average metrics across all sites for comparison.
     */
    protected function getAverageMetrics(): array
    {
        $siteCount = Tenant::query()
            ->whereNotNull('live_at')
            ->whereNull('suspended_at')
            ->when(!$this->includeListing, fn($query) => $query->where(function ($q) {
                $q->where('type', '!=', TenantType::LISTING->value)
                    ->orWhereNull('type');
            }))
            ->count();

        if ($siteCount === 0) {
            return [];
        }

        $excludedTenantIds = $this->getExcludedTenantIds();

        $totals = TenantDailyMetric::query()
            ->where('date', '>=', $this->fromDate)
            ->where('date', '<=', $this->toDate)
            ->when(!empty($excludedTenantIds), fn($query) => $query->whereNotIn('tenant_id', $excludedTenantIds))
            ->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 [
            'enquiries' => round($enquiries / $siteCount, 2),
            'service_bookings' => round($serviceBookings / $siteCount, 2),
            'reservations' => round($reservations / $siteCount, 2),
            'valuations' => round((int) ($totals->valuations ?? 0) / $siteCount, 2),
            'vehicles_added' => round((int) ($totals->vehicles_added ?? 0) / $siteCount, 2),
            'vehicles_exited' => round((int) ($totals->vehicles_exited ?? 0) / $siteCount, 2),
            'visitors' => round($visitors / $siteCount, 2),
            'revenue' => round((float) ($totals->revenue ?? 0) / $siteCount, 2),
            '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,
            'site_count' => $siteCount,
        ];
    }

    /**
     * Get metrics grouped by day for the selected date range.
     */
    protected function getMetricsByDay(): array
    {
        $excludedTenantIds = $this->getExcludedTenantIds();

        return TenantDailyMetric::query()
            ->where('date', '>=', $this->fromDate)
            ->where('date', '<=', $this->toDate)
            ->when($this->tenantId, fn($query) => $query->where('tenant_id', $this->tenantId))
            ->when(!empty($excludedTenantIds), fn($query) => $query->whereNotIn('tenant_id', $excludedTenantIds))
            ->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();
    }
}
