<?php

namespace App\Reporting\Repositories;

use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Mtc\MercuryDataModels\Enquiry;

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')]);

        $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;
    }
}
