Untitled

<?php

namespace App\Http\Livewire\Front\Performance\Publish\Research;

use App\Http\Livewire\Front\Performance\Publish\Components\CardComponent;
use App\Models\PostFacebookDailyMetric;
use App\Services\Posts\GetPostDailyMetricsTableNamesFromDateRange;
use Carbon\Carbon;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

class Total3SecViewsFromChannels extends CardComponent
{
    public $name = 'Views on Channels you Manage';

    public $description = '';

    public $key = '3_sec_views_on_channels';

    public $icon = 'clock';

    public function mountStats(): void
    {
        $dailyMetrics = $this->dailyMetrics();

        $this->data = $this->getCardData();

        $data = $this->calculateData($dailyMetrics);

        $this->stats = [
            'labels' => $this->getLastSixMonths(),
            'data' => $data,
            'total' => $this->numberFormatShort(last($dailyMetrics)),
            'avg_description' => '',
            'projection' => $this->currentMonthProjection($data),

        ];
    }


    protected function dailyMetrics(): array
    {

        $currentMonth = Carbon::createFromFormat('Y-m-d', $this->filters['months'] . '-01');
        $subMonths = []; // To store ranges for 5 submonths

        // Calculate ranges for the current month and 5 previous months
        for ($i = 0; $i < 6; $i++) {
            $startOfMonth = $currentMonth->copy()->subMonths($i)->startOfMonth()->addDay(1); // Start on the 2nd
            $endOfMonth = $currentMonth->copy()->subMonths($i)->addMonth()->startOfMonth()->endOfDay(); // End on the 1st of the next month

            $subMonths[] = [
                'start' => $startOfMonth,
                'end' => $endOfMonth,
            ];
        }

        $tableNames = (new GetPostDailyMetricsTableNamesFromDateRange(
            startDate: $subMonths[5]['start'],
            endDate: $subMonths[0]['end'],
        ))->get();

        return (new PostFacebookDailyMetric())
            ->setConnection('big_query')
            ->newQuery()
            ->from(function ($builder) use ($tableNames, $subMonths) {
                foreach ($tableNames as $key => $tableName) {
                    foreach ($subMonths as $range) {
                        if ($key === 0) {
                            $this->buildBuilder($builder, $tableName, $range['start'], $range['end']);
                        } else {
                            $builder->unionAll(function ($builder1) use ($tableName, $range) {
                                $this->buildBuilder($builder1, $tableName, $range['start'], $range['end']);
                            });
                        }
                    }
                }
            }, 't1')
            ->select([
                DB::raw('SUM(post_video_views) AS total'),
                DB::raw('FORMAT_DATE("%Y-%m", CASE WHEN FORMAT_DATE("%d", date) = "01" THEN DATE_SUB(date, INTERVAL 1 DAY) ELSE date END) as grouped_date'),
            ])
            ->groupBy('grouped_date')
            ->orderBy('grouped_date')
            ->withCasts(['grouped_date' => 'date', 'total' => 'integer'])
            ->get()
            ->mapWithKeys(fn(PostFacebookDailyMetric $dailyMetric) => [
                $dailyMetric->grouped_date->format('Y-m') => $dailyMetric->total,
            ])
            ->all();
    }


    private function buildBuilder(Builder $builder, string $tableName, Carbon $startDate, Carbon $endDate): void
    {
        (new PostFacebookDailyMetric())
            ->setTable($tableName)
            ->newQuery()
            ->setQuery($builder)
            ->from($tableName)
            ->whereBetween('date', [$startDate, $endDate])
            ->whereHas('post', fn($builder) => $builder
                ->whereHas('channel', function ($query) {
                    $query->when(auth()->user()->isAdminOrOrganisationAdminOrHead($this->stage),
                        fn($query) => $query->whereIn('manager_id', $this->getUsersToFilter($this->stage)),
                        fn($query) => $query->where('manager_id', auth()->id())
                    );
                })
            );
    }

    protected function startDate(): Carbon
    {
        // Set the start date to the 2nd of the current month
        return Carbon::createFromFormat('Y-m-d', $this->filters['months'] . '-02')->startOfDay();
    }

    protected function endDate(): Carbon
    {
        // Set the end date to the 1st day of the next month at the end of the day
        return Carbon::createFromFormat('Y-m-d', $this->filters['months'] . '-01')
            ->addMonth() // Move to the next month
            ->startOfMonth() // Go to the 1st day of the next month
            ->endOfDay(); // End of the day
    }

}