<?php

namespace App\Support;

use App\Models\Lead;
use App\Models\Offerwall;
use App\Models\OfferwallClick;
use App\Models\User;
use App\Models\Withdrawal;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;

/**
 * Full date-range analytics for the dedicated admin analytics page. Everything
 * is read-only and scoped to [from, to]. Date grouping is portable across
 * SQLite (dev) and MySQL (cPanel).
 */
class AnalyticsReport
{
    public function __construct(
        private Carbon $from,
        private Carbon $to,
    ) {
        $this->from = $from->copy()->startOfDay();
        $this->to = $to->copy()->endOfDay();
    }

    public static function forRange(?string $from, ?string $to): self
    {
        $toDate = self::parse($to) ?? Carbon::today();
        $fromDate = self::parse($from) ?? $toDate->copy()->subDays(29);

        if ($fromDate->gt($toDate)) {
            [$fromDate, $toDate] = [$toDate, $fromDate];
        }

        // Cap the window so a huge range can't hammer the DB.
        if ($fromDate->diffInDays($toDate) > 366) {
            $fromDate = $toDate->copy()->subDays(366);
        }

        return new self($fromDate, $toDate);
    }

    private static function parse(?string $value): ?Carbon
    {
        if (!$value) {
            return null;
        }

        try {
            return Carbon::parse($value);
        } catch (\Throwable $e) {
            return null;
        }
    }

    public function label(): array
    {
        return [
            'from' => $this->from->toDateString(),
            'to' => $this->to->toDateString(),
            'days' => $this->from->diffInDays($this->to) + 1,
        ];
    }

    /* -------------------- Headline KPIs -------------------- */

    public function summary(): array
    {
        $leadsInRange = Lead::whereBetween('created_at', [$this->from, $this->to]);
        $approvedLeads = (clone $leadsInRange)->where('status', 'approved');
        $creditedLeads = (clone $leadsInRange)->where('credited_points', '>', 0);

        return [
            'signups' => User::whereBetween('created_at', [$this->from, $this->to])->count(),
            'active_users' => User::whereBetween('last_seen_at', [$this->from, $this->to])->count(),
            'leads' => (clone $leadsInRange)->count(),
            'approved_leads' => (clone $approvedLeads)->count(),
            'revenue' => round((float) (clone $leadsInRange)->sum('payout'), 2),
            'coins_awarded' => round((float) $creditedLeads->sum('credited_points'), 2),
            'clicks' => OfferwallClick::whereBetween('created_at', [$this->from, $this->to])->count(),
            'cashout_requested' => round((float) Withdrawal::whereBetween('created_at', [$this->from, $this->to])->sum('amount'), 2),
            'cashout_paid' => round((float) Withdrawal::whereBetween('created_at', [$this->from, $this->to])->where('status', 'approved')->sum('amount'), 2),
            'new_cashouts' => Withdrawal::whereBetween('created_at', [$this->from, $this->to])->count(),
        ];
    }

    /* -------------------- Time series -------------------- */

    public function timeSeries(): array
    {
        $days = $this->from->diffInDays($this->to) + 1;
        $labels = [];
        for ($i = 0; $i < $days; $i++) {
            $labels[] = $this->from->copy()->addDays($i)->format('M j');
        }

        return [
            'labels' => $labels,
            'signups' => $this->fillDays(User::whereBetween('created_at', [$this->from, $this->to]), 'count'),
            'leads' => $this->fillDays(Lead::whereBetween('created_at', [$this->from, $this->to]), 'count'),
            'revenue' => $this->fillDays(Lead::whereBetween('created_at', [$this->from, $this->to]), 'sum', 'payout'),
            'cashouts' => $this->fillDays(Withdrawal::whereBetween('created_at', [$this->from, $this->to]), 'sum', 'amount'),
        ];
    }

    /* -------------------- Offerwall performance -------------------- */

    /**
     * Per-offerwall clicks, leads, revenue, coins and conversion rate.
     */
    public function offerwallPerformance(): Collection
    {
        $offerwalls = Offerwall::query()->get(['id', 'name', 'type', 'is_active']);

        $clicks = OfferwallClick::whereBetween('created_at', [$this->from, $this->to])
            ->selectRaw('offerwall_id, COUNT(*) as total')
            ->groupBy('offerwall_id')
            ->pluck('total', 'offerwall_id');

        $leadStats = Lead::whereBetween('created_at', [$this->from, $this->to])
            ->selectRaw('company, COUNT(*) as leads, SUM(payout) as revenue, SUM(credited_points) as coins,
                         SUM(CASE WHEN status = \'approved\' THEN 1 ELSE 0 END) as approved')
            ->groupBy('company')
            ->get()
            ->keyBy(fn ($row) => mb_strtolower((string) $row->company));

        return $offerwalls->map(function (Offerwall $wall) use ($clicks, $leadStats) {
            $lead = $leadStats[mb_strtolower($wall->name)] ?? null;
            $clickCount = (int) ($clicks[$wall->id] ?? 0);
            $leadCount = (int) ($lead->leads ?? 0);

            return [
                'id' => $wall->id,
                'name' => $wall->name,
                'type' => $wall->type,
                'is_active' => (bool) $wall->is_active,
                'clicks' => $clickCount,
                'leads' => $leadCount,
                'approved' => (int) ($lead->approved ?? 0),
                'revenue' => round((float) ($lead->revenue ?? 0), 2),
                'coins' => round((float) ($lead->coins ?? 0), 2),
                'conversion' => $clickCount > 0 ? round(($leadCount / $clickCount) * 100, 1) : 0.0,
            ];
        })->sortByDesc('revenue')->values();
    }

    /* -------------------- Top offers -------------------- */

    public function topOffers(int $limit = 15): Collection
    {
        return Lead::whereBetween('created_at', [$this->from, $this->to])
            ->whereNotNull('offer_name')
            ->where('offer_name', '!=', '')
            ->selectRaw('offer_name, company, COUNT(*) as conversions, SUM(payout) as revenue, SUM(credited_points) as coins')
            ->groupBy('offer_name', 'company')
            ->orderByDesc('revenue')
            ->limit($limit)
            ->get()
            ->map(fn ($row) => [
                'offer' => html_entity_decode((string) $row->offer_name),
                'provider' => $row->company,
                'conversions' => (int) $row->conversions,
                'revenue' => round((float) $row->revenue, 2),
                'coins' => round((float) $row->coins, 2),
            ]);
    }

    /* -------------------- Breakdowns -------------------- */

    public function countryBreakdown(int $limit = 10): array
    {
        $rows = Lead::whereBetween('created_at', [$this->from, $this->to])
            ->whereNotNull('country')->where('country', '!=', '')
            ->selectRaw('country, COUNT(*) as total, SUM(payout) as revenue')
            ->groupBy('country')
            ->orderByDesc('total')
            ->limit($limit)
            ->get();

        return [
            'labels' => $rows->pluck('country')->map(fn ($c) => strtoupper((string) $c))->all(),
            'values' => $rows->pluck('total')->map(fn ($v) => (int) $v)->all(),
            'revenue' => $rows->pluck('revenue')->map(fn ($v) => round((float) $v, 2))->all(),
        ];
    }

    public function leadStatusBreakdown(): array
    {
        $counts = Lead::whereBetween('created_at', [$this->from, $this->to])
            ->selectRaw('status, COUNT(*) as total')
            ->groupBy('status')
            ->pluck('total', 'status');

        return [
            'labels' => ['Approved', 'Held', 'Rejected'],
            'values' => [
                (int) ($counts['approved'] ?? 0),
                (int) ($counts['held'] ?? 0),
                (int) ($counts['rejected'] ?? 0),
            ],
        ];
    }

    public function cashoutBreakdown(): array
    {
        $counts = Withdrawal::whereBetween('created_at', [$this->from, $this->to])
            ->selectRaw('status, COUNT(*) as total, SUM(amount) as amount')
            ->groupBy('status')
            ->get()
            ->keyBy('status');

        return [
            'labels' => ['Pending', 'Approved', 'Rejected'],
            'values' => [
                (int) ($counts['pending']->total ?? 0),
                (int) ($counts['approved']->total ?? 0),
                (int) ($counts['rejected']->total ?? 0),
            ],
        ];
    }

    /**
     * Top earning users in the range (by approved lead points).
     */
    public function topUsers(int $limit = 10): Collection
    {
        $rows = Lead::whereBetween('created_at', [$this->from, $this->to])
            ->where('credited_points', '>', 0)
            ->whereNotNull('user_id')
            ->selectRaw('user_id, COUNT(*) as conversions, SUM(credited_points) as coins, SUM(payout) as revenue')
            ->groupBy('user_id')
            ->orderByDesc('coins')
            ->limit($limit)
            ->get();

        $users = User::whereIn('id', $rows->pluck('user_id'))->get(['id', 'username'])->keyBy('id');

        return $rows->map(fn ($row) => [
            'username' => optional($users[$row->user_id] ?? null)->username ?? '—',
            'conversions' => (int) $row->conversions,
            'coins' => round((float) $row->coins, 2),
            'revenue' => round((float) $row->revenue, 2),
        ]);
    }

    /* -------------------- helpers -------------------- */

    private function fillDays($query, string $agg, string $column = 'id'): array
    {
        $expr = $agg === 'sum' ? "SUM({$column})" : 'COUNT(*)';

        $rows = (clone $query)
            ->selectRaw($this->dateExpr().' as d, '.$expr.' as total')
            ->groupBy('d')
            ->pluck('total', 'd');

        $days = $this->from->diffInDays($this->to) + 1;
        $out = [];
        for ($i = 0; $i < $days; $i++) {
            $key = $this->from->copy()->addDays($i)->format('Y-m-d');
            $value = (float) ($rows[$key] ?? 0);
            $out[] = $agg === 'sum' ? round($value, 2) : (int) $value;
        }

        return $out;
    }

    private function dateExpr(): string
    {
        $driver = config('database.connections.'.config('database.default').'.driver');

        return $driver === 'sqlite'
            ? "strftime('%Y-%m-%d', created_at)"
            : 'DATE(created_at)';
    }
}
