<?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
{
    /**
     * ISO day-of-week (1 = Monday … 7 = Sunday) → label, so the weekday report always renders
     * Monday-first with every day present even when it has no enquiries.
     */
    private const WEEKDAY_LABELS = [
        1 => 'Monday',
        2 => 'Tuesday',
        3 => 'Wednesday',
        4 => 'Thursday',
        5 => 'Friday',
        6 => 'Saturday',
        7 => 'Sunday',
    ];

    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 day of the week (Monday … Sunday) for the given range.
     *
     * Buckets in PHP rather than SQL because the report runs on MariaDB in production but the test
     * suite runs on SQLite, whose date functions (DAYOFWEEK/strftime) differ — bucketing on the
     * parsed Carbon value keeps the result identical across both drivers.
     */
    public function weekdayBreakdown(Carbon $from, Carbon $to): Collection
    {
        $counts = array_fill_keys(range(1, 7), 0);

        Enquiry::query()
            ->whereBetween('ingested_at', [$from, $to])
            ->pluck('ingested_at')
            ->each(function ($ingestedAt) use (&$counts) {
                $counts[Carbon::parse($ingestedAt)->dayOfWeekIso]++;
            });

        return collect(self::WEEKDAY_LABELS)
            ->map(fn (string $label, int $iso) => [
                'label' => $label,
                'enquiry_count' => $counts[$iso],
            ])
            ->values();
    }

    /**
     * Enquiry counts grouped by hour of the day (00:00 … 23:00) for the given range. Bucketed in
     * PHP for the same MariaDB/SQLite parity reason as {@see weekdayBreakdown()}.
     */
    public function timeOfDayBreakdown(Carbon $from, Carbon $to): Collection
    {
        $counts = array_fill_keys(range(0, 23), 0);

        Enquiry::query()
            ->whereBetween('ingested_at', [$from, $to])
            ->pluck('ingested_at')
            ->each(function ($ingestedAt) use (&$counts) {
                $counts[(int) Carbon::parse($ingestedAt)->hour]++;
            });

        return collect(range(0, 23))->map(fn (int $hour) => [
            'label' => sprintf('%02d:00', $hour),
            'enquiry_count' => $counts[$hour],
        ]);
    }

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