<?php

namespace App\Master\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Models\TenantDailyMetric;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
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(),
            'upcoming_launches' => $this->getUpcomingLaunches(),
            'sites_by_month' => $this->getSitesByMonth(),
            'predicted_sites_by_month' => $this->getPredictedSitesByMonth(),
            'revenue_by_month' => $this->getRevenueByMonth(),
            'predicted_revenue_by_month' => $this->getPredictedRevenueByMonth(),
            '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) => $this->excludeListingType($query)
            )
            ->orderBy('name')
            ->get(['id', 'name']);

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

    /**
     * Exclude listing-type tenants from query.
     */
    protected function excludeListingType(Builder $query): Builder
    {
        return $query->where(function ($q) {
            $q->where('type', '!=', TenantType::LISTING->value)
                ->orWhereNull('type');
        });
    }

    /**
     * Apply listing exclusion scope based on includeListing flag.
     */
    protected function withListingExclusion(Builder $query): Builder
    {
        return $query->when(
            !$this->includeListing,
            fn ($q) => $this->excludeListingType($q)
        );
    }

    /**
     * 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();
    }

    /**
     * Build a base query for live, non-suspended tenants.
     */
    protected function liveTenantQuery(): Builder
    {
        return $this->withListingExclusion(
            Tenant::query()
                ->whereNotNull('live_at')
                ->whereNull('suspended_at')
        );
    }

    /**
     * Get the current count of live sites (not suspended).
     */
    protected function getLiveSitesCount(): int
    {
        return $this->liveTenantQuery()->count();
    }

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

    /**
     * Get sites planning to go live, soonest first, including overdue
     * sites that have a launch date but have not yet been marked live.
     */
    protected function getUpcomingLaunches(): array
    {
        $today = Carbon::now()->startOfDay();

        return $this->withListingExclusion(
            Tenant::query()
                ->whereNull('live_at')
                ->whereNull('suspended_at')
                ->whereNotNull('proposed_launch_date')
        )
            ->orderBy('proposed_launch_date')
            ->get(['id', 'name', 'proposed_launch_date', 'launch_confidence'])
            ->map(function (Tenant $tenant) use ($today) {
                $launchDate = Carbon::parse($tenant->proposed_launch_date)
                    ->startOfDay();

                return [
                    'id' => $tenant->id,
                    'name' => $tenant->name,
                    'proposed_launch_date' => $launchDate->format('Y-m-d'),
                    'launch_confidence' => $tenant->launch_confidence,
                    'days_until' => (int) $today->diffInDays($launchDate, false),
                    'is_overdue' => $launchDate->lt($today),
                ];
            })
            ->toArray();
    }

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

        for ($i = 11; $i >= 0; $i--) {
            $date = $now->copy()->subMonths($i);
            $endOfMonth = $date->copy()->endOfMonth();

            $count = $this->withListingExclusion(
                Tenant::query()
                    ->whereNotNull('live_at')
                    ->where('live_at', '<=', $endOfMonth)
            )->count();

            $months[] = [
                'month' => $date->format('M Y'),
                'count' => $count,
            ];
        }

        return $months;
    }

    /**
     * Get predicted site counts by month using proposed_launch_date.
     */
    protected function getPredictedSitesByMonth(): array
    {
        $now = Carbon::now();
        $currentLiveCount = $this->getLiveSitesCount();

        $upcomingLaunches = $this->getUpcomingSites(['proposed_launch_date']);

        if ($upcomingLaunches->isEmpty()) {
            return [];
        }

        $lastLaunch = Carbon::parse(
            $upcomingLaunches->max('proposed_launch_date')
        );

        return $this->buildMonthlyRange(
            $now->copy()->addMonth()->startOfMonth(),
            $lastLaunch->copy()->endOfMonth(),
            function (
                Carbon $endOfMonth,
                Carbon $current
            ) use (
                $upcomingLaunches,
                $currentLiveCount
            ) {
                $launchCount = $upcomingLaunches
                    ->filter(
                        fn ($t) => Carbon::parse($t->proposed_launch_date)
                            ->lte($endOfMonth)
                    )
                    ->count();

                return [
                    'month' => $current->format('M Y'),
                    'count' => $currentLiveCount + $launchCount,
                ];
            }
        );
    }

    /**
     * Get upcoming sites that are not yet live with a future launch date.
     */
    protected function getUpcomingSites(array $columns): Collection
    {
        return $this->withListingExclusion(
            Tenant::query()
                ->whereNull('live_at')
                ->whereNull('suspended_at')
                ->whereNotNull('proposed_launch_date')
                ->where('proposed_launch_date', '>', Carbon::now())
        )
            ->orderBy('proposed_launch_date')
            ->get($columns);
    }

    /**
     * Check if a tenant's retainer is active for a given month.
     */
    protected function isRetainerActive(
        mixed $tenant,
        Carbon $endOfMonth,
        Carbon $startOfMonth
    ): bool {
        if (!$tenant->has_retainer) {
            return false;
        }

        if ($tenant->retainer_from) {
            $from = Carbon::parse($tenant->retainer_from)->startOfMonth();
            if ($from->gt($endOfMonth)) {
                return false;
            }
        }

        if ($tenant->retainer_to) {
            $to = Carbon::parse($tenant->retainer_to)->endOfMonth();
            if ($to->lt($startOfMonth)) {
                return false;
            }
        }

        return true;
    }

    /**
     * Get monthly revenue breakdown.
     */
    protected function getRevenueByMonth(): array
    {
        $months = [];
        $now = Carbon::now();

        for ($i = 11; $i >= 0; $i--) {
            $date = $now->copy()->subMonths($i);
            $endOfMonth = $date->copy()->endOfMonth();
            $startOfMonth = $date->copy()->startOfMonth();

            $tenants = $this->withListingExclusion(
                Tenant::query()
                    ->whereNotNull('live_at')
                    ->where('live_at', '<=', $endOfMonth)
                    ->whereNull('suspended_at')
            )->get([
                'live_at',
                'license_fee_amount',
                'has_retainer',
                'retainer_amount',
                'retainer_hours',
                'retainer_from',
                'retainer_to',
                'other_monthly_income',
            ]);

            $licenseFees = $tenants->sum('license_fee_amount');
            $otherIncome = $tenants->sum('other_monthly_income');

            $activeRetainers = $tenants->filter(
                fn ($t) => $this->isRetainerActive(
                    $t,
                    $endOfMonth,
                    $startOfMonth
                )
            );

            $retainerAmount = $activeRetainers->sum('retainer_amount');
            $retainerHours = $activeRetainers->sum('retainer_hours');

            $months[] = [
                'month' => $date->format('M Y'),
                'license_fees' => round((float) $licenseFees, 2),
                'retainer_amount' => round((float) $retainerAmount, 2),
                'retainer_hours' => round((float) $retainerHours, 2),
                'other_income' => round((float) $otherIncome, 2),
                'mrr' => round(
                    (float) $licenseFees
                    + (float) $retainerAmount
                    + (float) $otherIncome,
                    2
                ),
            ];
        }

        return $months;
    }

    /**
     * Get predicted revenue by month based on proposed_launch_date.
     */
    protected function getPredictedRevenueByMonth(): array
    {
        $now = Carbon::now();

        $upcomingSites = $this->getUpcomingSites([
            'proposed_launch_date',
            'license_fee_amount',
            'has_retainer',
            'retainer_amount',
            'retainer_from',
            'retainer_to',
            'other_monthly_income',
        ]);

        if ($upcomingSites->isEmpty()) {
            return [];
        }

        $currentRevenue = collect(last($this->getRevenueByMonth()));
        $baseLicenseFees = (float) ($currentRevenue['license_fees'] ?? 0);
        $baseRetainer = (float) ($currentRevenue['retainer_amount'] ?? 0);
        $baseOther = (float) ($currentRevenue['other_income'] ?? 0);

        $lastLaunch = Carbon::parse(
            $upcomingSites->max('proposed_launch_date')
        );

        return $this->buildMonthlyRange(
            $now->copy()->addMonth()->startOfMonth(),
            $lastLaunch->copy()->endOfMonth(),
            function (
                Carbon $endOfMonth,
                Carbon $current
            ) use (
                $upcomingSites,
                $baseLicenseFees,
                $baseRetainer,
                $baseOther
            ) {
                $launched = $upcomingSites->filter(
                    fn ($t) => Carbon::parse($t->proposed_launch_date)
                        ->lte($endOfMonth)
                );

                $additionalFees = $launched->sum('license_fee_amount');
                $additionalOther = $launched->sum('other_monthly_income');
                $additionalRetainer = $launched
                    ->filter(
                        fn ($t) => $this->isRetainerActive(
                            $t,
                            $endOfMonth,
                            $current->copy()->startOfMonth()
                        )
                    )
                    ->sum('retainer_amount');

                $fees = round($baseLicenseFees + (float) $additionalFees, 2);
                $retainer = round($baseRetainer + (float) $additionalRetainer, 2);
                $other = round($baseOther + (float) $additionalOther, 2);

                return [
                    'month' => $current->format('M Y'),
                    'license_fees' => $fees,
                    'mrr' => round($fees + $retainer + $other, 2),
                ];
            }
        );
    }

    /**
     * Iterate over a monthly range and build results via callback.
     */
    protected function buildMonthlyRange(
        Carbon $start,
        Carbon $end,
        callable $callback
    ): array {
        $months = [];
        $current = $start->copy();

        while ($current->lte($end)) {
            $endOfMonth = $current->copy()->endOfMonth();
            $months[] = $callback($endOfMonth, $current->copy());
            $current->addMonth();
        }

        return $months;
    }

    /**
     * Get the base selectRaw for metric aggregation queries.
     */
    protected function metricsSelectRaw(): string
    {
        return '
            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
        ';
    }

    /**
     * Build a base query for daily metrics within the date range.
     */
    protected function metricsQuery(): Builder
    {
        $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
                )
            );
    }

    /**
     * Calculate conversion rates from visitor and action counts.
     */
    protected function conversionRates(
        int $visitors,
        int $enquiries,
        int $serviceBookings,
        int $reservations
    ): array {
        if ($visitors === 0) {
            return [
                'enquiry_conversion_rate' => 0,
                'booking_conversion_rate' => 0,
                'reservation_conversion_rate' => 0,
            ];
        }

        return [
            'enquiry_conversion_rate' => round(
                ($enquiries / $visitors) * 100,
                2
            ),
            'booking_conversion_rate' => round(
                ($serviceBookings / $visitors) * 100,
                2
            ),
            'reservation_conversion_rate' => round(
                ($reservations / $visitors) * 100,
                2
            ),
        ];
    }

    /**
     * Get aggregated daily metrics for the selected date range.
     */
    protected function getDailyMetrics(): array
    {
        $totals = $this->metricsQuery()
            ->selectRaw($this->metricsSelectRaw())
            ->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),
            ...$this->conversionRates(
                $visitors,
                $enquiries,
                $serviceBookings,
                $reservations
            ),
        ];
    }

    /**
     * Get average metrics across all sites for comparison.
     */
    protected function getAverageMetrics(): array
    {
        $siteCount = $this->liveTenantQuery()->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($this->metricsSelectRaw())
            ->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
            ),
            ...$this->conversionRates(
                $visitors,
                $enquiries,
                $serviceBookings,
                $reservations
            ),
            'site_count' => $siteCount,
        ];
    }

    /**
     * Get metrics grouped by day for the selected date range.
     */
    protected function getMetricsByDay(): array
    {
        return $this->metricsQuery()
            ->selectRaw('date, ' . $this->metricsSelectRaw())
            ->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();
    }
}
