<?php

namespace Inside\DESK\Jobs;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Inside\DESK\Models\NewsletterComputedStatistics;
use Inside\DESK\Models\NewsletterStatistic;
use Inside\Jobs\Job;
use Symfony\Component\Console\Output\OutputInterface;

final class ComputeNewslettersStatistics extends Job
{
    public function __construct(
        private ?Carbon $from = null,
        private ?Carbon $to = null,
        private ?OutputInterface $output = null
    ) {
    }

    public function handle(): void
    {
        $this->truncateTable();

        $newslettersQuery = DB::table('desk_newsletters_sent')
            ->select(['date_sent', 'langcode'])
            ->groupBy('date_sent', 'langcode')
            ->orderBy('date_sent')
            ->when($this->from, static fn (QueryBuilder $query, Carbon $from) => $query->whereDate('date_sent', '>=', $from->setTime(0, 0)->toDateString()))
            ->when($this->to, static fn (QueryBuilder $query, Carbon $to) => $query->whereDate('date_sent', '<=', $to->setTime(23, 59, 59)->toDateString()));

        $total = $this->getQueryCount($newslettersQuery);

        $progressBar = $this->output?->createProgressBar($total);

        $newslettersQuery->each(function ($newsletter) use ($progressBar) {
            $this->computeNewsletterStatistics($newsletter->date_sent, $newsletter->langcode);
            $progressBar?->advance();
        });

        $progressBar?->finish();
        $this->output?->writeln('');
    }

    private function computeNewsletterStatistics(string $newsletterDate, ?string $langcode): void
    {
        $statistics = NewsletterStatistic::query()
            ->selectRaw("COUNT(CASE WHEN statistic_type = '".NewsletterStatistic::TYPE_CLICKED."' THEN user_uuid END) as clicked")
            ->selectRaw("COUNT(DISTINCT CASE WHEN statistic_type = '".NewsletterStatistic::TYPE_CLICKED."'  THEN user_uuid END) as unique_clicked")
            ->selectRaw("COUNT(DISTINCT CASE WHEN statistic_type = '".NewsletterStatistic::TYPE_SENT."'  THEN user_uuid END) as sent")
            ->selectRaw("COUNT(DISTINCT CASE WHEN statistic_type = '".NewsletterStatistic::TYPE_VIEWED."'  THEN user_uuid END) as viewed")
            ->where('newsletter_date', $newsletterDate)
            ->whereIn('user_uuid', function (QueryBuilder $query) use ($newsletterDate, $langcode) {
                $query->select('user_uuid')
                    ->from('desk_newsletters_sent')
                    ->where('date_sent', $newsletterDate)
                    ->when($langcode, fn (QueryBuilder $query, string $langcode) => $query->where('langcode', $langcode));
            })
            ->havingRaw('COUNT(*) > 0')
            ->first()
            ?->toArray();

        if (!$statistics) {
            return;
        }

        $statistics['date_sent'] = $newsletterDate;
        $statistics['langcode'] = $langcode;

        NewsletterComputedStatistics::insert($statistics);
    }

    private function getQueryCount(QueryBuilder $query): int
    {
        return DB::table(DB::raw("({$query->toSql()}) as query"))
            ->mergeBindings($query)
            ->count();
    }

    private function truncateTable(): void
    {
        if (!$this->from && !$this->to) {
            NewsletterComputedStatistics::truncate();
            return;
        }

        NewsletterComputedStatistics::query()
            ->when($this->from, static fn (Builder $query, Carbon $from) => $query->whereDate('date_sent', '>=', $from->setTime(0, 0)->toDateString()))
            ->when($this->to, static fn (Builder $query, Carbon $to) => $query->whereDate('date_sent', '<=', $to->setTime(23, 59, 59)->toDateString()))
            ->delete();
    }
}
