<?php

namespace App\Models;

use App\Models\Admin;
use App\Models\Bank;
use App\Models\MessageSetting;
use App\Models\Organization;
use App\Models\Post;
use App\Models\User;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Facades\App;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Mcamara\LaravelLocalization\Facades\LaravelLocalization;

class Mailing extends Model
{
    use HasFactory;
    use SoftDeletes;

    protected $fillable = [
        'title', 'type', 'subject', 'content_blocks', 'scheduled_at', 'sent_at', 'sending_started_at',
        'status', 'recipients_count', 'sent_count', 'failed_count', 'skipped_count', 'aborted_count',
        'updated_by_user_id', 'filter_by_location',
        'location_country_id', 'location_country_ids',
        'location_division_id', 'location_division_ids',
        'location_city_id', 'location_city_ids',
        'location_district_id', 'location_district_ids',
        'filter_by_profile_type', 'selected_profile_types', 'fallback_locale',
        'filter_by_contacts', 'contact_profile_ids',
    ];

    protected $casts = [
        'content_blocks' => 'array',
        'subject' => 'array',
        'scheduled_at' => 'datetime',
        'sent_at' => 'datetime',
        'sending_started_at' => 'datetime',
        'filter_by_location' => 'boolean',
        'location_country_ids' => 'array',
        'location_division_ids' => 'array',
        'location_city_ids' => 'array',
        'location_district_ids' => 'array',
        'filter_by_profile_type' => 'boolean',
        'selected_profile_types' => 'array',
        'filter_by_contacts' => 'boolean',
        'contact_profile_ids' => 'array',
    ];

    protected $dates = ['scheduled_at', 'sent_at', 'sending_started_at', 'deleted_at'];

    /**
     * Get the user who last updated the mailing
     */
    public function updatedByUser()
    {
        return $this->belongsTo(\App\Models\User::class, 'updated_by_user_id');
    }

    /**
     * Get the bounces for this mailing
     */
    public function bounces()
    {
        return $this->hasMany(\App\Models\MailingBounce::class, 'mailing_id');
    }

    /**
     * Get the bounced count dynamically from the mailing_bounces table
     */
    public function getBouncedCountAttribute()
    {
        return $this->bounces()->count();
    }

    /**
     * Bounce stats for this mailing recorded after sending (SMTP DSN bounces).
     * These are distinct from skipped_count (pre-send suppression).
     */
    public function getBounceStats(): array
    {
        $bounces = $this->bounces()->get();
        return [
            'hard'      => $bounces->where('bounce_type', 'hard')->count(),
            'soft'      => $bounces->where('bounce_type', 'soft')->count(),
            'complaint' => $bounces->where('bounce_type', 'complaint')->count(),
            'unknown'   => $bounces->whereNull('bounce_type')->count(),
            'total'     => $bounces->count(),
        ];
    }

    /**
     * Get posts from content_blocks JSON
     */
    public function getSelectedPosts()
    {
        if (empty($this->content_blocks)) {
            return collect();
        }

        $postIds = collect($this->content_blocks)->pluck('post_id');

        return Post::whereIn('id', $postIds)->get()->sortBy(function ($post) {
            return array_search($post->id, collect($this->content_blocks)->pluck('post_id')->toArray());
        });
    }

    /**
     * Get posts with all their translations
     */
    public function getSelectedPostsWithTranslations()
    {
        return $this->getSelectedPosts()->load(['translations', 'meeting.location.city.translations', 'poll']);
    }

    /**
     * Get post translation for specific locale with fallback
     */
    public function getPostTranslationForLocale($postId, $locale = null)
    {
        $locale = $locale ?: App::getLocale();
        $now = now();

        $post = Post::find($postId);
        if (!$post) {
            return null;
        }

        // Try preferred locale first
        $translation = $post->translations()
            ->where('locale', $locale)
            ->where('status', 1)
            ->where('from', '<=', $now)
            ->where(fn($q) => $q->whereNull('till')->orWhere('till', '>=', $now))
            ->first();

        // Fall back to mailing's configured fallback locale if set
        if (!$translation && $this->fallback_locale && $locale !== $this->fallback_locale) {
            $translation = $post->translations()
                ->where('locale', $this->fallback_locale)
                ->where('status', 1)
                ->where('from', '<=', $now)
                ->where(fn($q) => $q->whereNull('till')->orWhere('till', '>=', $now))
                ->first();
        }

        return $translation;
    }

    /**
     * Apply the message_settings subscription filter for this mailing type.
     * general_newsletter_only requires general_newsletter=true AND local_newsletter=false.
     */
    protected function applySubscriptionFilter($query): void
    {
        if ($this->type === 'general_newsletter_only') {
            $query->where('general_newsletter', true)->where('local_newsletter', false);
        } else {
            $query->where($this->type, true);
        }
    }

    /**
     * Get recipients query based on mailing type
     */
    public function getRecipientsQuery()
    {
        $queries = [];

        // Initialize all profile type queries — exclude soft-deleted profiles
        $users = User::whereNull('deleted_at')->whereHas('message_settings', function ($query) {
            $this->applySubscriptionFilter($query);
        })->select('id', 'name', 'email', 'lang_preference');

        $organizations = Organization::whereNull('deleted_at')->whereHas('message_settings', function ($query) {
            $this->applySubscriptionFilter($query);
        })->select('id', 'name', 'email', 'lang_preference');

        $banks = Bank::whereNull('deleted_at')->whereHas('message_settings', function ($query) {
            $this->applySubscriptionFilter($query);
        })->select('id', 'name', 'email', 'lang_preference');

        $admins = Admin::whereNull('deleted_at')->whereHas('message_settings', function ($query) {
            $this->applySubscriptionFilter($query);
        })->select('id', 'name', 'email', 'lang_preference');

        // Apply profile type filtering if enabled
        if ($this->filter_by_profile_type && !empty($this->selected_profile_types)) {
            $selectedTypes = $this->selected_profile_types;

            // Only include selected profile types
            if (in_array('User', $selectedTypes)) {
                $queries[] = $users;
            }
            if (in_array('Organization', $selectedTypes)) {
                $queries[] = $organizations;
            }
            if (in_array('Bank', $selectedTypes)) {
                $queries[] = $banks;
            }
            if (in_array('Admin', $selectedTypes)) {
                $queries[] = $admins;
            }
        } else {
            // If no profile type filtering, include all types
            $queries = [$users, $organizations, $banks, $admins];
        }

        // Apply location filtering if enabled
        if ($this->filter_by_location) {
            $queries = array_map(function ($query) {
                return $this->applyLocationFilter($query);
            }, $queries);
        }

        // Apply contact filtering if enabled
        if ($this->filter_by_contacts && !empty($this->contact_profile_ids)) {
            $queries = array_map(fn($q) => $this->applyContactFilter($q), $queries);
        }

        // Get count before bounce filtering for logging
        $beforeFilteringCount = 0;
        foreach ($queries as $query) {
            $beforeFilteringCount += $query->count();
        }

        // Apply bounce filtering to all queries
        $queries = array_map(function ($query) {
            return $query->whereNotIn('email', function ($subquery) {
                $subquery->select('email')
                    ->from('mailing_bounces')
                    ->where('is_suppressed', true);
            });
        }, $queries);

        // Get count after bounce filtering for logging
        $afterFilteringCount = 0;
        foreach ($queries as $query) {
            $afterFilteringCount += $query->count();
        }

        // Log suppression statistics if any emails were excluded
        $suppressedCount = $beforeFilteringCount - $afterFilteringCount;
        if ($suppressedCount > 0) {
            Log::warning("MAILING RECIPIENTS: Excluded {$suppressedCount} suppressed emails from Mailing ID: {$this->id}");

            // Log which specific emails were suppressed
            $suppressedEmails = MailingBounce::where('is_suppressed', true)->pluck('email');
            foreach ($suppressedEmails as $email) {
                Log::warning("MAILING RECIPIENTS: Suppressed email excluded: {$email}");
            }
        }

        // Combine all queries with union
        if (empty($queries)) {
            // Return empty query if no profile types selected
            return User::whereRaw('1 = 0')->select('id', 'name', 'email', 'lang_preference');
        }

        $finalQuery = array_shift($queries);
        foreach ($queries as $query) {
            $finalQuery = $finalQuery->union($query);
        }

        // Order by email for deterministic, stable ordering across pause/resume cycles.
        // Deduplication by email is handled in getRecipientsCount() and
        // getRecipientsGroupedByAvailableLocales() — not here, so callers can
        // freely chain additional where/pluck/distinct constraints.
        return $finalQuery->orderBy('email');
    }

    /**
     * Count unique recipient email addresses.
     * ->count() on a union builder misaligns PDO bindings in Laravel, so we
     * wrap the union in a subquery and count that instead.
     */
    public function getRecipientsCount(): int
    {
        $union    = $this->getRecipientsQuery();
        $sql      = $union->toSql();
        $bindings = $union->getBindings();

        return DB::table(DB::raw("({$sql}) as recipients_union"))
            ->addBinding($bindings, 'where')
            ->distinct()
            ->count('email');
    }

    /**
     * Count recipients that match all active filters (type, profile type, location)
     * but are skipped because their email is in the hard-bounce suppression list.
     * This is the correct "Skipped (hard bounce)" number for the preview/report,
     * as opposed to the global suppression list size which ignores all filters.
     */
    public function getSuppressedRecipientsCount(): int
    {
        $queries = [];

        $users = User::whereNull('deleted_at')->whereHas('message_settings', function ($q) {
            $this->applySubscriptionFilter($q);
        })->select('id', 'name', 'email', 'lang_preference');

        $organizations = Organization::whereNull('deleted_at')->whereHas('message_settings', function ($q) {
            $this->applySubscriptionFilter($q);
        })->select('id', 'name', 'email', 'lang_preference');

        $banks = Bank::whereNull('deleted_at')->whereHas('message_settings', function ($q) {
            $this->applySubscriptionFilter($q);
        })->select('id', 'name', 'email', 'lang_preference');

        $admins = Admin::whereNull('deleted_at')->whereHas('message_settings', function ($q) {
            $this->applySubscriptionFilter($q);
        })->select('id', 'name', 'email', 'lang_preference');

        if ($this->filter_by_profile_type && !empty($this->selected_profile_types)) {
            $selectedTypes = $this->selected_profile_types;
            if (in_array('User', $selectedTypes))         $queries[] = $users;
            if (in_array('Organization', $selectedTypes)) $queries[] = $organizations;
            if (in_array('Bank', $selectedTypes))         $queries[] = $banks;
            if (in_array('Admin', $selectedTypes))        $queries[] = $admins;
        } else {
            $queries = [$users, $organizations, $banks, $admins];
        }

        if ($this->filter_by_location) {
            $queries = array_map(fn($q) => $this->applyLocationFilter($q), $queries);
        }

        if ($this->filter_by_contacts && !empty($this->contact_profile_ids)) {
            $queries = array_map(fn($q) => $this->applyContactFilter($q), $queries);
        }

        // Keep only those whose email IS suppressed (inverse of getRecipientsQuery)
        $queries = array_map(function ($q) {
            return $q->whereIn('email', function ($sub) {
                $sub->select('email')->from('mailing_bounces')->where('is_suppressed', true);
            });
        }, $queries);

        if (empty($queries)) {
            return 0;
        }

        $final = array_shift($queries);
        foreach ($queries as $q) {
            $final = $final->union($q);
        }

        // ->count() on a union builder misaligns bindings; fetch and count in PHP instead.
        return $final->get()->count();
    }

    /**
     * Apply location filtering to a query.
     * Reads from the array columns (location_country_ids, etc.) first.
     * Falls back to singular columns for records created before the multi-select migration.
     *
     * Matching is OR across all selected levels: a recipient matches if their
     * location falls under any selected country, division, city, or district.
     * This respects the hierarchy — selecting a country includes all recipients
     * in that country regardless of whether their city/district is also selected.
     */
    /**
     * Get account IDs of all profiles that have had a transaction with any of the
     * contact_profile_ids. Returns account IDs belonging to the *partner* side.
     */
    private function getContactPartnerAccountIds(): array
    {
        if (empty($this->contact_profile_ids)) {
            return [];
        }

        // Resolve the account IDs of the selected contact profiles
        $selectedAccountIds = collect($this->contact_profile_ids)
            ->map(function ($entry) {
                if (empty($entry['id']) || empty($entry['type'])) {
                    return null;
                }
                return DB::table('accounts')
                    ->where('accountable_type', $entry['type'])
                    ->where('accountable_id', $entry['id'])
                    ->whereNull('deleted_at')
                    ->pluck('id');
            })
            ->filter()
            ->flatten()
            ->unique()
            ->values()
            ->all();

        if (empty($selectedAccountIds)) {
            return [];
        }

        // Find all account IDs that transacted with the selected accounts (either direction)
        $fromIds = DB::table('transactions')
            ->whereIn('to_account_id', $selectedAccountIds)
            ->pluck('from_account_id');

        $toIds = DB::table('transactions')
            ->whereIn('from_account_id', $selectedAccountIds)
            ->pluck('to_account_id');

        return $fromIds->merge($toIds)
            ->merge($selectedAccountIds) // include the selected profiles themselves
            ->unique()
            ->values()
            ->all();
    }

    private function applyContactFilter($query)
    {
        if (!$this->filter_by_contacts || empty($this->contact_profile_ids)) {
            return $query;
        }

        $partnerAccountIds = $this->getContactPartnerAccountIds();

        if (empty($partnerAccountIds)) {
            return $query->whereRaw('1 = 0');
        }

        return $query->whereHas('accounts', function ($q) use ($partnerAccountIds) {
            $q->whereIn('id', $partnerAccountIds);
        });
    }

    private function applyLocationFilter($query)
    {
        $districtIds = !empty($this->location_district_ids) ? $this->location_district_ids : ($this->location_district_id ? [$this->location_district_id] : []);
        $cityIds     = !empty($this->location_city_ids)     ? $this->location_city_ids     : ($this->location_city_id     ? [$this->location_city_id]     : []);
        $divisionIds = !empty($this->location_division_ids) ? $this->location_division_ids : ($this->location_division_id ? [$this->location_division_id] : []);
        $countryIds  = !empty($this->location_country_ids)  ? $this->location_country_ids  : ($this->location_country_id  ? [$this->location_country_id]  : []);

        // Deepest selected level wins — see LocationFilterResolver.
        return \App\Services\Stats\LocationFilterResolver::apply(
            $query,
            $countryIds,
            $divisionIds,
            $cityIds,
            $districtIds
        );
    }

    /**
     * Get recipients grouped by language preference for efficient processing
     */
    public function getRecipientsByLanguage()
    {
        $recipients = $this->getRecipientsQuery()->get();

        return $recipients->groupBy(function ($recipient) {
            return $recipient->lang_preference ?: timebank_config('base_language', 'en');
        });
    }

    /**
     * Status checking methods
     */
    public function canBeSent()
    {
        $hasValidStatus = in_array($this->status, ['draft', 'scheduled']);
        $hasPosts = !empty($this->content_blocks) && count(array_filter($this->content_blocks, function ($block) {
            return !empty($block['post_id']);
        })) > 0;

        return $hasValidStatus && $hasPosts;
    }

    public function canBeEdited(): bool
    {
        return in_array($this->status, ['draft', 'scheduled']);
    }

    public function canBeScheduled()
    {
        return in_array($this->status, ['draft']);
    }

    public function canBeCancelled()
    {
        return in_array($this->status, ['scheduled', 'sending']);
    }

    /**
     * Scopes
     */
    public function scopeDraft($query)
    {
        return $query->where('status', 'draft');
    }

    public function scopeScheduled($query)
    {
        return $query->where('status', 'scheduled');
    }

    public function scopeSent($query)
    {
        return $query->where('status', 'sent');
    }

    public function scopeByType($query, $type)
    {
        return $query->where('type', $type);
    }

    /**
     * Get available locales for recipients of this mailing type
     */
    public function getAvailableRecipientLocales()
    {
        $recipientsQuery = $this->getRecipientsQuery();

        return $recipientsQuery
            ->whereNotNull('lang_preference')
            ->distinct()
            ->pluck('lang_preference')
            ->filter()
            ->toArray();
    }

    /**
     * Get locales that have a published, active translation for every selected post.
     * These are the only locales suitable as a fallback (a fallback locale that is
     * missing any post would still leave gaps in the mailing).
     */
    public function getFallbackEligibleLocales(): array
    {
        if (empty($this->content_blocks)) {
            return [];
        }

        $now = now();
        $postIds = collect($this->content_blocks)->pluck('post_id')->unique()->values();
        $postCount = $postIds->count();

        if ($postCount === 0) {
            return [];
        }

        // Count how many posts in the selection have a published active translation per locale
        $localeCoverage = \DB::table('post_translations')
            ->whereIn('post_id', $postIds)
            ->where('status', 1)
            ->where('from', '<=', $now)
            ->where(function ($q) use ($now) {
                $q->whereNull('till')->orWhere('till', '>=', $now);
            })
            ->groupBy('locale')
            ->select('locale', \DB::raw('COUNT(DISTINCT post_id) as covered'))
            ->get();

        return $localeCoverage
            ->filter(fn($row) => (int) $row->covered >= $postCount)
            ->pluck('locale')
            ->values()
            ->toArray();
    }

    /**
     * Get available locales from selected posts
     */
    public static function determinePostType($post): string
    {
        if ($post->category && $post->category->type) {
            if ($post->category->type === 'App\\Models\\Poll') {
                return 'poll';
            }
            if (str_starts_with($post->category->type, 'App\\Models\\ImagePost')) {
                return 'image';
            }
            if ($post->category->type === 'App\\Models\\Meeting') {
                return 'event';
            }
            if ($post->category->type === 'App\\Models\\News') {
                return 'news';
            }
        }

        // Fall back to meeting / poll relation
        if ($post->meeting) {
            return 'event';
        }
        if ($post->poll) {
            return 'poll';
        }

        return 'news';
    }

    /**
     * Email template of a content block type. Falls back to the default block view, so a type
     * added later (e.g. poll) works before config:merge has added its key to the active config.
     */
    public static function blockTemplate(string $postType): string
    {
        return timebank_config("mailing.templates.{$postType}_block") ?? "emails.newsletter.blocks.{$postType}";
    }

    /**
     * Extra data for a poll block: link to the poll page (not the post page) and its state
     * at the time the email is built. Empty for posts without a poll.
     */
    public static function pollBlockData($post, string $locale): array
    {
        $poll = $post->poll;
        if (!$poll) {
            return [];
        }

        $slug = $post->translations->firstWhere('locale', $locale)?->slug ?? $post->translations->first()?->slug;

        return [
            'url' => LaravelLocalization::getURLFromRouteNameTranslated($locale, 'routes.poll.show', ['slug' => $slug]),
            'poll_closed' => $poll->isClosed(),
            'poll_opens' => ($poll->opens_at && $poll->opens_at->isFuture())
                ? $poll->opens_at->locale($locale)->isoFormat('LLL') : null,
            'poll_closes' => $poll->closes_at ? $poll->closes_at->locale($locale)->isoFormat('LLL') : null,
        ];
    }

    public function resolvePreviewLocale(): string
    {
        $appLocale = app()->getLocale();

        if (!empty($this->getContentBlocksForLocale($appLocale))) {
            return $appLocale;
        }

        if ($this->type) {
            $countsByLocale = $this->getRecipientCountsByLocale();
            $best = collect($countsByLocale)
                ->filter(fn($d) => $d['has_content'])
                ->sortByDesc('count')
                ->keys()
                ->first();

            if ($best) {
                return $best;
            }
        }

        foreach ($this->getAvailablePostLocales() as $locale) {
            if (!empty($this->getContentBlocksForLocale($locale))) {
                return $locale;
            }
        }

        return timebank_config('base_language', 'en');
    }

    public function getAvailablePostLocales()
    {
        if (empty($this->content_blocks)) {
            return [];
        }

        $postIds = collect($this->content_blocks)->pluck('post_id');
        $locales = \DB::table('post_translations')
            ->whereIn('post_id', $postIds)
            ->where('status', 1) // Only published translations
            ->distinct()
            ->pluck('locale')
            ->toArray();

        // Always include base language first
        $baseLanguage = timebank_config('base_language', 'en');
        $locales = array_unique(array_merge([$baseLanguage], $locales));

        return array_values($locales);
    }

    /**
     * Get subject for a specific locale
     */
    public function getSubjectForLocale($locale = null)
    {
        $locale = $locale ?: timebank_config('base_language', 'en');
        $baseLanguage = timebank_config('base_language', 'en');

        // Handle legacy string subjects
        if (is_string($this->subject)) {
            return $this->subject;
        }

        // Handle multilingual JSON subjects
        $subjects = $this->subject ?? [];

        // Try preferred locale first
        if (isset($subjects[$locale])) {
            return $subjects[$locale];
        }

        // Fallback to base language
        if (isset($subjects[$baseLanguage])) {
            return $subjects[$baseLanguage];
        }

        // Fallback to first available subject
        return !empty($subjects) ? array_values($subjects)[0] : '';
    }

    /**
     * Set subject for a specific locale
     */
    public function setSubjectForLocale($locale, $subject)
    {
        $subjects = $this->subject ?? [];
        $subjects[$locale] = $subject;
        $this->subject = $subjects;
    }

    /**
     * Get all subjects with their locales
     */
    public function getAllSubjects()
    {
        return $this->subject ?? [];
    }

    /**
     * Check if subject exists for a locale
     */
    public function hasSubjectForLocale($locale)
    {
        $subjects = $this->subject ?? [];
        return isset($subjects[$locale]) && !empty($subjects[$locale]);
    }

    /**
     * Get recipients for a specific locale
     */
    public function getRecipientsForLocale($locale)
    {
        return $this->getRecipientsQuery()
            ->where('lang_preference', $locale);
    }

    /**
     * Get recipients grouped by available locales with fallback handling.
     * When fallback_locale is set on the mailing, recipients whose preferred
     * locale has no content are grouped under their preferred locale key so
     * that they still receive a mail (with fallback content for missing posts).
     */
    public function getRecipientsGroupedByAvailableLocales()
    {
        // Deduplicate by email in PHP: a person with multiple profiles (User + Org)
        // sharing the same address must only receive one email.
        $seenEmails    = [];
        $allRecipients = $this->getRecipientsQuery()->get()->filter(function ($r) use (&$seenEmails) {
            if (isset($seenEmails[$r->email])) return false;
            $seenEmails[$r->email] = true;
            return true;
        });

        $baseLanguage = timebank_config('base_language', 'en');
        $recipientsByLocale = [];

        // Cache content block results per locale to avoid repeated DB queries
        $contentBlockCache = [];

        foreach ($allRecipients as $recipient) {
            $preferredLocale = $recipient->lang_preference ?? $baseLanguage;

            if (!array_key_exists($preferredLocale, $contentBlockCache)) {
                $contentBlockCache[$preferredLocale] = $this->getContentBlocksForLocale($preferredLocale);
            }

            if (!empty($contentBlockCache[$preferredLocale])) {
                $recipientsByLocale[$preferredLocale][] = $recipient;
            }
        }

        return $recipientsByLocale;
    }

    /**
     * Filter content blocks to only include posts with published translations in the given locale.
     * When a fallback_locale is set on this mailing, posts missing the requested locale are
     * included using the fallback, and the block is annotated with 'uses_fallback_locale'.
     */
    public function getContentBlocksForLocale($locale)
    {
        if (empty($this->content_blocks)) {
            return [];
        }

        $now = now();
        $filteredBlocks = [];

        foreach ($this->content_blocks as $block) {
            $post = Post::withTrashed()->with('translations')->find($block['post_id']);

            if (!$post) {
                $annotated = $block;
                $annotated['post_missing'] = true;
                $filteredBlocks[] = $annotated;
                continue;
            }

            $hasInLocale = $post->translations->contains(function ($t) use ($locale, $now) {
                return $t->locale === $locale
                    && $t->status == 1
                    && $t->from <= $now
                    && ($t->till === null || $t->till >= $now);
            });

            if ($hasInLocale) {
                $filteredBlocks[] = $block;
            } elseif ($this->fallback_locale && $this->fallback_locale !== $locale) {
                $hasInFallback = $post->translations->contains(function ($t) use ($now) {
                    return $t->locale === $this->fallback_locale
                        && $t->status == 1
                        && $t->from <= $now
                        && ($t->till === null || $t->till >= $now);
                });
                if ($hasInFallback) {
                    $annotated = $block;
                    $annotated['uses_fallback_locale'] = $this->fallback_locale;
                    $filteredBlocks[] = $annotated;
                }
            }
        }

        return $filteredBlocks;
    }

    /**
     * Get recipient count grouped by locale with content availability.
     * Each entry includes 'fallback_posts' — the number of posts that will be
     * served in the fallback locale instead of the recipient's own locale.
     */
    public function getRecipientCountsByLocale()
    {
        $recipients = $this->getRecipientsQuery()->get();
        $baseLanguage = timebank_config('base_language', 'en');

        $recipientsByLocale = $recipients->groupBy(function ($recipient) use ($baseLanguage) {
            return $recipient->lang_preference ?: $baseLanguage;
        });

        $totalPosts = count($this->content_blocks ?? []);

        $result = [];
        foreach ($recipientsByLocale as $locale => $localeRecipients) {
            $contentBlocks = $this->getContentBlocksForLocale($locale);
            $receivedCount = count($contentBlocks);
            $fallbackCount = collect($contentBlocks)->filter(fn($b) => !empty($b['uses_fallback_locale']))->count();
            $result[$locale] = [
                'count' => $localeRecipients->count(),
                'content_blocks' => $receivedCount,
                'has_content' => $receivedCount > 0,
                'all_posts' => $totalPosts > 0 && $receivedCount >= $totalPosts,
                'fallback_posts' => $fallbackCount,
                'missing_posts' => max(0, $totalPosts - $receivedCount),
            ];
        }

        return $result;
    }

    /**
     * Get recipient counts grouped by locale AND smallest available location (using getLocationFirst).
     * Always groups by location regardless of whether a location filter is active.
     * Returns array keyed by "locale|location_name", each entry has locale, location, count, and post counts.
     */
    public function getRecipientCountsByLocaleAndLocation(): array
    {
        $baseLanguage = timebank_config('base_language', 'en');
        $totalPosts = count($this->content_blocks ?? []);

        // Load all recipients per model type with locations eager-loaded
        $locationsWith = ['locations.city.translations', 'locations.district.translations', 'locations.division.translations', 'locations.country.translations'];

        $modelTypes = [
            'User'         => User::class,
            'Organization' => Organization::class,
            'Bank'         => Bank::class,
            'Admin'        => Admin::class,
        ];

        $allRecipients = collect();

        foreach ($modelTypes as $type => $modelClass) {
            // Skip types not in filter
            if ($this->filter_by_profile_type && !empty($this->selected_profile_types)) {
                if (!in_array($type, $this->selected_profile_types)) {
                    continue;
                }
            }

            $query = $modelClass::whereNull('deleted_at')->whereHas('message_settings', function ($q) {
                $this->applySubscriptionFilter($q);
            })->whereNotIn('email', function ($sub) {
                $sub->select('email')->from('mailing_bounces')->where('is_suppressed', true);
            })->with($locationsWith);

            if ($this->filter_by_location) {
                $query = $this->applyLocationFilter($query);
            }

            if ($this->filter_by_contacts && !empty($this->contact_profile_ids)) {
                $query = $this->applyContactFilter($query);
            }

            $allRecipients = $allRecipients->concat($query->get());
        }

        // Group by locale + location name
        $result = [];
        foreach ($allRecipients as $recipient) {
            $locale = $recipient->lang_preference ?: $baseLanguage;
            $locationData = $recipient->getLocationFirst();
            $locationName = $locationData['name_short'] ?? '';
            $countryName  = $locationData['name'] ?? '';
            // Extract the country portion: it's the last comma-separated segment of the full name
            $countrySort  = trim(last(explode(',', $countryName)));

            $key = $locale . '|' . $locationName;

            if (!isset($result[$key])) {
                $contentBlocks = $this->getContentBlocksForLocale($locale);
                $receivedCount = count($contentBlocks);
                $fallbackCount = collect($contentBlocks)->filter(fn($b) => !empty($b['uses_fallback_locale']))->count();

                $result[$key] = [
                    'locale'         => $locale,
                    'location'       => $locationName,
                    'country_sort'   => $countrySort,
                    'count'          => 0,
                    'content_blocks' => $receivedCount,
                    'has_content'    => $receivedCount > 0,
                    'all_posts'      => $totalPosts > 0 && $receivedCount >= $totalPosts,
                    'fallback_posts' => $fallbackCount,
                    'missing_posts'  => max(0, $totalPosts - $receivedCount),
                ];
            }

            $result[$key]['count']++;
        }

        // Sort by locale, then country, then location name
        uasort($result, function ($a, $b) {
            return $a['locale'] <=> $b['locale']
                ?: $a['country_sort'] <=> $b['country_sort']
                ?: $a['location'] <=> $b['location'];
        });

        return $result;
    }

    /**
     * Get the most specific active location filter name.
     * Returns the name at the smallest level set: district > city > division > country.
     */
    public function getLocationFilterName(): ?string
    {
        if (!$this->filter_by_location) {
            return null;
        }

        if ($this->location_district_id) {
            $locale = \App\Models\Locations\DistrictLocale::where('district_id', $this->location_district_id)->first();
            return $locale?->name;
        }
        if ($this->location_city_id) {
            $locale = \App\Models\Locations\CityLocale::where('city_id', $this->location_city_id)->first();
            return $locale?->name;
        }
        if ($this->location_division_id) {
            $locale = \App\Models\Locations\DivisionLocale::where('division_id', $this->location_division_id)->first();
            return $locale?->name;
        }
        if ($this->location_country_id) {
            $locale = \App\Models\Locations\CountryLocale::where('country_id', $this->location_country_id)->first();
            return $locale?->name;
        }

        return null;
    }

    /**
     * Get formatted recipient counts display
     */
    public function getFormattedRecipientCounts()
    {
        $counts = $this->getRecipientCountsByLocale();
        $formatted = [];

        foreach ($counts as $locale => $data) {
            $language = \App\Models\Language::where('lang_code', $locale)->first();
            $flag = $language ? $language->flag : '🏳️';

            if ($data['has_content']) {
                $formatted[] = "{$flag} {$data['count']}";
            } else {
                $formatted[] = "{$flag} {$data['count']} (no content)";
            }
        }

        return implode(' ', $formatted);
    }

    /**
     * Get total recipients that will actually receive content
     */
    public function getEffectiveRecipientsCount()
    {
        $counts = $this->getRecipientCountsByLocale();

        return collect($counts)
            ->where('has_content', true)
            ->sum('count');
    }

    /**
     * Dispatch locale-specific email jobs for this mailing
     */
    public function dispatchLocaleSpecificJobs()
    {
        Log::info("MAILING: Starting dispatch process for Mailing ID: {$this->id}");

        $batchSize = timebank_config('mailing.batch_size', 10);
        $recipientsByLocale = $this->getRecipientsGroupedByAvailableLocales();
        $totalJobsDispatched = 0;

        foreach ($recipientsByLocale as $locale => $recipients) {
            $contentBlocks = $this->getContentBlocksForLocale($locale);

            if (!empty($contentBlocks) && !empty($recipients)) {
                $recipientBatches = collect($recipients)->chunk($batchSize);
                $batchCount = $recipientBatches->count();

                Log::info("MAILING: Dispatching {$batchCount} batch job(s) for Mailing ID: {$this->id}, Locale: {$locale}, Total Recipients: " . count($recipients) . ", Batch Size: {$batchSize}");

                foreach ($recipientBatches as $batchIndex => $batch) {
                    Log::info("MAILING: Dispatching batch " . ($batchIndex + 1) . "/{$batchCount} for Mailing ID: {$this->id}, Locale: {$locale}, Recipients in batch: " . $batch->count());

                    \App\Jobs\SendBulkMailJob::dispatch($this, $locale, [], $batch)
                        ->onQueue('mailing');

                    $totalJobsDispatched++;
                }
            }
        }

        $totalRecipients = collect($recipientsByLocale)->sum(fn($r) => count($r));

        $this->update(['recipients_count' => $totalRecipients]);

        $suppressedCount = MailingBounce::where('is_suppressed', true)->count();
        Log::info("MAILING: Completed dispatch for Mailing ID: {$this->id} - Jobs dispatched: {$totalJobsDispatched}, Locales: " . count($recipientsByLocale) . ", Recipients: {$totalRecipients}, Suppressed emails: {$suppressedCount}");

        if (empty($recipientsByLocale) || $totalRecipients === 0) {
            Log::warning("MAILING: No jobs dispatched for Mailing ID: {$this->id} - marking as sent immediately");
            $this->update([
                'status' => 'sent',
                'sent_at' => now()
            ]);
        }
    }
}