<?php

declare(strict_types=1);

namespace Inside\DESK\Services;

use Carbon\Carbon;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Contracts\Pagination\Paginator;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Str;
use Inside\Content\Facades\ContentHelper;
use Inside\Content\Models\Contents\Alerts;
use Inside\Content\Models\Contents\Users;
use Inside\DESK\Contracts\DeskStatistics;
use Inside\DESK\Models\AlertStatistic;
use Inside\DESK\Models\NewsletterComputedStatistics;
use Inside\DESK\Models\NewsletterStatistic;
use Throwable;

class DeskStatisticsService implements DeskStatistics
{
    private function extractDateFromFilters(array $filters, string $filterName): ?Carbon
    {
        if (! isset($filters[$filterName])) {
            return null;
        }

        try {
            $date = $filters[$filterName];
            return Carbon::createFromFormat('Y-m-d', $date)->setTime(0, 0);
        } catch (Throwable $exception) {
            Log::error("[DeskStatistics::extractDateFromFilters] Failed to parse date from $date => {$exception->getMessage()}");
            return null;
        }
    }

    public function getAlertsStatisticsQuery(?string $search = null, array $filters = []): Builder
    {
        $alertsTable = class_to_table(Alerts::class);
        $query = AlertStatistic::join($alertsTable, "$alertsTable.uuid", '=', 'alert_uuid')
            ->select([
                "$alertsTable.uuid",
                "$alertsTable.title",
                "$alertsTable.published_at",
                "$alertsTable.langcode",
            ])
            ->selectRaw("COUNT(DISTINCT CASE WHEN statistic_type = '".AlertStatistic::TYPE_CLICKED."' THEN user_uuid END) as unique_clicked")
            ->selectRaw("COUNT(CASE WHEN statistic_type = '".AlertStatistic::TYPE_CLICKED."' THEN user_uuid END) as clicked")
            ->selectRaw("COUNT(DISTINCT CASE WHEN statistic_type = '".AlertStatistic::TYPE_VIEWED."' THEN user_uuid END) as viewed")
            ->selectRaw("COUNT(DISTINCT CASE WHEN statistic_type = '".AlertStatistic::TYPE_SENT."' THEN user_uuid END) as sent")
            ->groupBy('alert_uuid')
            ->withCasts([
                'published_at' => 'timestamp',
                'created_at' => 'timestamp',
            ]);

        $fromDate = $this->extractDateFromFilters($filters, 'from');
        $toDate = $fromDate === null ? null : $this->extractDateFromFilters($filters, 'to');
        $langcode = $filters['langcode'] ?? null;

        if ($fromDate !== null) {
            $query->whereDate('published_at', '>=', $fromDate->toDateString());
        }

        if ($toDate !== null) {
            $query->whereDate('published_at', '<=', $toDate->toDateString());
        }

        if (in_array($langcode, list_languages())) {
            $query->where('langcode', '=', $langcode);
        }

        if (! empty($search)) {
            $query->where('title', 'like', "%$search%");
        }

        ContentHelper::applySortToQuery($query, $filters, 'published_at', 'desc', [
            'uuid',
            'title',
            'published_at',
            'created_at',
            'unique_clicked',
            'clicked',
            'viewed',
            'sent',
        ]);

        return $query;
    }

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

    public function getAlertsStatistics(?string $search = null, array $filters = []): Paginator | Collection | LengthAwarePaginator | array
    {
        $query = $this->getAlertsStatisticsQuery($search, $filters);
        $count = $this->getQueryCount($query);

        return ContentHelper::getResultFromQuery($query, $filters, true, $count);
    }

    public function getNewslettersStatisticsQuery(array $filters = []): Builder
    {
        $fromDate = $this->extractDateFromFilters($filters, 'from');
        $toDate = $fromDate === null ? null : $this->extractDateFromFilters($filters, 'to');
        $langcode = $filters['langcode'] ?? null;

        $query = NewsletterComputedStatistics::query()
            ->when($fromDate, fn (Builder $query, Carbon $fromDate) => $query->whereDate('date_sent', '>=', $fromDate->setTime(0, 0)->toDateString()))
            ->when($toDate, fn (Builder $query, Carbon $toDate) => $query->whereDate('date_sent', '<=', $toDate->setTime(23, 59, 59)->toDateString()))
            ->when(in_array($langcode, list_languages()) ? $langcode : null, fn (Builder $query, string $langcode) => $query->where('langcode', '=', $langcode));

        ContentHelper::applySortToQuery($query, $filters, 'date_sent', 'desc', [
            'date_sent',
            'unique_clicked',
            'clicked',
            'viewed',
            'sent',
        ]);

        return $query;
    }

    public function getNewslettersStatistics(array $filters = []): Paginator | Collection | LengthAwarePaginator | array
    {
        $query = $this->getNewslettersStatisticsQuery($filters);
        $count = $this->getQueryCount($query);

        return ContentHelper::getResultFromQuery($query, $filters, true, $count);
    }

    public function getNewsletterViewsQuery(int $newsletterDate, ?string $search = null, array $filters = []): Builder
    {
        $fromDate = $this->extractDateFromFilters($filters, 'from');
        $toDate = $fromDate === null ? null : $this->extractDateFromFilters($filters, 'to');
        $langcode = $filters['langcode'] ?? null;
        $notViewed = in_array($filters['not_viewed'] ?? null, [true, 1, '1', 'true'], true);

        $query = Users::join('desk_newsletters_statistics', 'user_uuid', 'uuid')
            ->where('statistic_type', NewsletterStatistic::TYPE_VIEWED)
            ->where('newsletter_date', Carbon::createFromTimestamp($newsletterDate)->toDateTimeString())
            ->when(in_array($langcode, list_languages()) ? $langcode : null, fn (Builder $query, string $langcode) => $query->where('langcode', '=', $langcode))
            ->when($fromDate, fn (Builder $query, Carbon $fromDate) => $query->whereDate('desk_newsletters_statistics.created_at', '>=', $fromDate->setTime(0, 0)->toDateString()))
            ->when($toDate, fn (Builder $query, Carbon $toDate) => $query->whereDate('desk_newsletters_statistics.created_at', '<=', $toDate->setTime(23, 59, 59)->toDateString()))
            ->groupBy('user_uuid');

        if ($notViewed) {
            $query = Users::whereNotIn('uuid', $query->select('user_uuid'))
                ->where('status', true)
                ->where(
                    fn (Builder $query) => $query
                        ->where('is_maintenance', false)
                        ->orWhereNull('is_maintenance')
                );
        }

        $query
            ->select([
                'uuid',
                'email',
                'firstname',
                'lastname',
                'image',
                'fra_code',
            ])
            ->selectRaw("CONCAT(firstname, ' ', lastname) AS full_name");

        if (! empty($search)) {
            $query->having('full_name', 'like', "%$search%");
        }

        ContentHelper::applySortToQuery($query, $filters, 'lastname', 'asc', [
            'lastname',
            'email',
        ]);

        return $query;
    }

    public function getNewsletterViews(int $newsletterDate, ?string $search = null, array $filters = []): Paginator | Collection | LengthAwarePaginator | array
    {
        $query = $this->getNewsletterViewsQuery($newsletterDate, $search, $filters);
        $count = $this->getQueryCount($query);

        return tap(ContentHelper::getResultFromQuery($query, $filters, true, $count))
            ->transform(fn (Users $user) => $this->formatUser($user));
    }

    public function getAlertViewsQuery(string $alertUuid, ?string $search = null, array $filters = []): Builder
    {
        $fromDate = $this->extractDateFromFilters($filters, 'from');
        $toDate = $fromDate === null ? null : $this->extractDateFromFilters($filters, 'to');
        $notViewed = in_array($filters['not_viewed'] ?? null, [true, 1, '1', 'true'], true);

        $query = Users::join('desk_alerts_statistics', 'user_uuid', 'uuid')
            ->where('statistic_type', AlertStatistic::TYPE_VIEWED)
            ->where('alert_uuid', $alertUuid)
            ->when($fromDate, fn (Builder $query, Carbon $fromDate) => $query->whereDate('desk_alerts_statistics.created_at', '>=', $fromDate->setTime(0, 0)->toDateString()))
            ->when($toDate, fn (Builder $query, Carbon $toDate) => $query->whereDate('desk_alerts_statistics.created_at', '<=', $toDate->setTime(23, 59, 59)->toDateString()))
            ->groupBy('user_uuid');

        if ($notViewed) {
            $query = Users::whereNotIn('uuid', $query->select('user_uuid'))
                ->where('status', true)
                ->where(
                    fn (Builder $query) => $query
                        ->where('is_maintenance', false)
                        ->orWhereNull('is_maintenance')
                );
        }

        $query
            ->select([
                'uuid',
                'email',
                'firstname',
                'lastname',
                'image',
                'fra_code',
            ])
            ->selectRaw("CONCAT(firstname, ' ', lastname) AS full_name");

        if (! empty($search)) {
            $query->having('full_name', 'like', "%$search%");
        }

        ContentHelper::applySortToQuery($query, $filters, 'lastname', 'asc', [
            'lastname',
            'email',
        ]);

        return $query;
    }

    public function getAlertViews(string $alertUuid, ?string $search = null, array $filters = []): Paginator | Collection | LengthAwarePaginator | array
    {
        $query = $this->getAlertViewsQuery($alertUuid, $search, $filters);
        $count = $this->getQueryCount($query);

        return tap(ContentHelper::getResultFromQuery($query, $filters, true, $count))
            ->transform(fn (Users $user) => $this->formatUser($user));
    }

    private function formatUser(Users $user): array
    {
        return [
            'uuid' => $user->uuid,
            'full_name' => trim(Str::title($user->firstname).' '.Str::upper($user->lastname)) ?: null,
            'email' => $user->email,
            'image' => protected_file_url($user, 'image'),
            'fra_code' => $user->fra_code,
        ];
    }
}
