<?php

namespace App\Reporting\Repositories;

use App\Reporting\StatsModels\EnquiryUtmBreakdown;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Mtc\MercuryDataModels\Enquiry;
use Mtc\MercuryDataModels\ReportingStat;

class CRMReportRepository
{
    public function yearlyBreakdown(): Collection
    {
        $data = collect();
        $periods = collect(range(11, 0, -1))->map(fn ($months) => Carbon::now()->subMonths($months)->startOfMonth());

        foreach ($periods as $periodStart) {
            $periodEnd = $periodStart->copy()->endOfMonth();
            $data = $data->merge(Enquiry::query()
                ->addSelect([
                    DB::raw('count(id) as enquiry_count'),
                    'type_id',
                ])
                ->whereBetween('ingested_at', [
                    $periodStart,
                    $periodEnd,
                ])
                ->with('type')
                ->groupBy('type_id')
                ->get()
                ->map(fn ($data) => [
                    'period' => $periodStart->format('M y'),
                    'enquiry_count' => $data->enquiry_count,
                    'type_id' => $data->type_id,
                    'type' => $data->type?->name ?? __('labels.unspecified'),
                ]));
        }

        $data = $data->groupBy('type')
            ->map(function ($items) use ($periods) {
                $countsByPeriod = [];

                // Pre-fill with zeros for all periods
                foreach ($periods as $p) {
                    $label = $p->format('M y');
                    $countsByPeriod[$label] = 0;
                }

                // Overwrite with actual counts
                foreach ($items as $item) {
                    $countsByPeriod[$item['period']] = $item['enquiry_count'];
                }

                $countsByPeriod[__('labels.total')] = array_sum($countsByPeriod);

                return $countsByPeriod;
            })
            ->sortByDesc(fn ($row) => $row[__('labels.total')]);

        // No enquiries in any of the 12 periods: return empty so the frontend can
        // render a "no data" state rather than 500ing on array_keys(null) below.
        if ($data->isEmpty()) {
            return $data;
        }

        $periodNames = array_keys($data->first());

        $data[__('labels.total')] = collect($periodNames)->flip()
            ->map(fn ($value, $key) => $data->pluck($key)->sum());

        return $data;
    }

    public function typeBreakdown(Carbon $from, Carbon $to): Collection
    {
        $data = Enquiry::query()
            ->addSelect([
                DB::raw('count(id) as enquiry_count'),
                'type_id',
            ])
            ->whereBetween('ingested_at', [
                $from,
                $to,
            ])
            ->with('type')
            ->groupBy('type_id')
            ->orderByDesc('enquiry_count')
            ->get()
            ->map(fn ($data) => [
                'type' => $data->type?->name ?? __('labels.unspecified'),
                'enquiry_count' => $data->enquiry_count,
            ]);

        $data->push([
            'type' => __('labels.total'),
            'enquiry_count' => $data->sum('enquiry_count'),
        ]);

        return $data;
    }

    /**
     * Enquiry counts grouped by a UTM dimension, read from the pre-aggregated reporting stats.
     */
    public function utmBreakdown(string $dimension, Carbon $from, Carbon $to): Collection
    {
        $dimension = $this->resolveDimension($dimension);
        $prefix = EnquiryUtmBreakdown::KEY_PREFIX . ':' . $dimension . ':';

        return ReportingStat::query()
            ->where('key', 'like', $prefix . '%')
            ->whereBetween('date', [$from->copy()->startOfDay(), $to->copy()->endOfDay()])
            ->get(['key', 'value'])
            ->groupBy(fn (ReportingStat $stat) => Str::after($stat->key, $prefix))
            ->map(fn (Collection $rows, string $bucket) => [
                'type' => $bucket,
                'enquiry_count' => (int) $rows->sum(fn (ReportingStat $stat) => (int) $stat->value),
            ])
            ->sortByDesc('enquiry_count')
            ->values();
    }

    public function resolveDimension(?string $dimension): string
    {
        return in_array($dimension, EnquiryUtmBreakdown::DIMENSIONS, true)
            ? $dimension
            : EnquiryUtmBreakdown::DIMENSIONS[0];
    }
}
