<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

/**
 * One-shot cleanup for the "same email synced thousands of times" bug.
 *
 * Before the fix in EmailSyncService::isAlreadySynced(), emails with a
 * missing/empty RFC Message-ID header bypassed the unique constraint on
 * (workspace_id, message_id_header) — because MySQL treats NULL != NULL
 * on unique indexes — so every sync inserted another row.
 *
 * This seeder identifies duplicate groups by:
 *   • Same workspace_id + non-NULL message_id_header  (groups of "real" dupes)
 *   • Same workspace_id + channel_message_id          (Gmail / Outlook id dupes)
 *   • Same workspace_id + email_account_id + imap_folder + imap_uid
 *                                                     (NULL Message-ID IMAP dupes)
 *   • Same workspace_id + NULL message_id_header + same from_email + same
 *     subject + same sent_at  (worst case — no identifier at all; the
 *     real bug's footprint)
 *
 * For each group, the OLDEST row (lowest id) is kept and the rest are
 * deleted. Conversation message_count is recalculated afterwards so the
 * unread badges + sidebar counts stay accurate.
 *
 * Usage:
 *   php artisan db:seed --class=DuplicateMessagesCleanup --force
 *
 *   # dry-run (count only, no deletes):
 *   DUPCLEANUP_DRY_RUN=1 php artisan db:seed --class=DuplicateMessagesCleanup --force
 *
 *   # scope to a single workspace:
 *   DUPCLEANUP_WORKSPACE_ID=2 php artisan db:seed --class=DuplicateMessagesCleanup --force
 */
class DuplicateMessagesCleanup extends Seeder
{
    public function run(): void
    {
        $dryRun = (bool) env('DUPCLEANUP_DRY_RUN', false);
        $scopeWorkspaceId = (int) env('DUPCLEANUP_WORKSPACE_ID', 0) ?: null;

        $this->command?->info($dryRun
            ? '[DRY RUN] No rows will be deleted. Counts only.'
            : 'Cleaning up duplicate synced messages. Keeping the oldest row in each duplicate group.'
        );
        if ($scopeWorkspaceId) {
            $this->command?->info("Scoped to workspace #{$scopeWorkspaceId}.");
        }

        $totalDeleted = 0;
        $affectedConversationIds = [];

        // ── Group 1: same message_id_header (the "real" duplicates that
        //    slipped in before the unique constraint migration ran) ──
        $totalDeleted += $this->deleteDupes(
            label: 'same Message-ID header',
            groupKeys: ['workspace_id', 'message_id_header'],
            extraWhere: fn ($q) => $q->whereNotNull('message_id_header')->where('message_id_header', '!=', ''),
            scopeWorkspaceId: $scopeWorkspaceId,
            dryRun: $dryRun,
            affectedConversationIds: $affectedConversationIds,
        );

        // ── Group 2: same channel_message_id (Gmail / Outlook id) ──
        $totalDeleted += $this->deleteDupes(
            label: 'same channel_message_id (Gmail/Outlook)',
            groupKeys: ['workspace_id', 'channel_message_id'],
            extraWhere: fn ($q) => $q->whereNotNull('channel_message_id')->where('channel_message_id', '!=', ''),
            scopeWorkspaceId: $scopeWorkspaceId,
            dryRun: $dryRun,
            affectedConversationIds: $affectedConversationIds,
        );

        // ── Group 3: same imap_uid + imap_folder (per email account) ──
        // Scope via conversation->email_account_id by joining first.
        $totalDeleted += $this->deleteImapUidDupes(
            scopeWorkspaceId: $scopeWorkspaceId,
            dryRun: $dryRun,
            affectedConversationIds: $affectedConversationIds,
        );

        // ── Group 4: the worst case — NULL message_id_header AND NULL
        //    channel_message_id AND NULL imap_uid. Fall back to the
        //    triple (from_email, subject, sent_at) within the same
        //    conversation. Same conversation_id catches the actual
        //    "synced 5000 times" footprint where the loop hammered one
        //    row repeatedly. ──
        $totalDeleted += $this->deleteDupes(
            label: 'same from_email + subject + sent_at within conversation',
            groupKeys: ['conversation_id', 'from_email', 'subject', 'sent_at'],
            extraWhere: fn ($q) => $q
                ->whereNull('message_id_header')
                ->whereNull('channel_message_id')
                ->whereNull('imap_uid'),
            scopeWorkspaceId: $scopeWorkspaceId,
            dryRun: $dryRun,
            affectedConversationIds: $affectedConversationIds,
        );

        // Recalculate conversation message counts so unread badges stay accurate.
        if (!$dryRun && !empty($affectedConversationIds)) {
            $this->command?->info('Recalculating conversation message counts for ' . count($affectedConversationIds) . ' affected conversations...');
            foreach (array_chunk(array_unique($affectedConversationIds), 500) as $chunk) {
                DB::statement('
                    UPDATE conversations
                    SET messages_count = (
                        SELECT COUNT(*) FROM messages WHERE messages.conversation_id = conversations.id
                    )
                    WHERE id IN (' . implode(',', array_map('intval', $chunk)) . ')
                ');
            }
        }

        $verb = $dryRun ? 'would delete' : 'deleted';
        $this->command?->info("Done. {$verb} {$totalDeleted} duplicate message row(s).");
        if ($dryRun) {
            $this->command?->info('Re-run without DUPCLEANUP_DRY_RUN=1 to actually delete.');
        }
    }

    /**
     * Delete duplicate rows where all $groupKeys match, keeping the row with
     * the smallest id in each group. Returns the count of deleted rows.
     */
    private function deleteDupes(
        string $label,
        array $groupKeys,
        ?\Closure $extraWhere,
        ?int $scopeWorkspaceId,
        bool $dryRun,
        array &$affectedConversationIds,
    ): int {
        $groupBy = implode(', ', $groupKeys);

        $query = DB::table('messages')
            ->selectRaw($groupBy . ', MIN(id) as keeper_id, COUNT(*) as cnt')
            ->groupByRaw($groupBy)
            ->havingRaw('COUNT(*) > 1');

        if ($scopeWorkspaceId) {
            $query->where('workspace_id', $scopeWorkspaceId);
        }
        if ($extraWhere) {
            $extraWhere($query);
        }

        $groups = $query->get();

        if ($groups->isEmpty()) {
            $this->command?->info("  [{$label}] no duplicate groups found.");
            return 0;
        }

        $deleted = 0;
        foreach ($groups as $group) {
            $keeperId = (int) $group->keeper_id;

            $dupeQuery = DB::table('messages')->where('id', '!=', $keeperId);
            if ($scopeWorkspaceId) {
                $dupeQuery->where('workspace_id', $scopeWorkspaceId);
            }
            foreach ($groupKeys as $key) {
                $dupeQuery->where($key, $group->{$key});
            }
            if ($extraWhere) {
                $extraWhere($dupeQuery);
            }

            $convIds = $dupeQuery->pluck('conversation_id')->all();
            foreach ($convIds as $cid) {
                if ($cid) {
                    $affectedConversationIds[] = (int) $cid;
                }
            }

            $deleted += $dryRun ? $dupeQuery->count() : $dupeQuery->delete();
        }

        $verb = $dryRun ? 'would delete' : 'deleted';
        $this->command?->info("  [{$label}] {$groups->count()} group(s), {$verb} {$deleted} row(s).");

        return $deleted;
    }

    /**
     * IMAP UID dedupe needs to scope to the same email_account_id via the
     * conversation, so two mailboxes can have overlapping UIDs without
     * false positives.
     */
    private function deleteImapUidDupes(?int $scopeWorkspaceId, bool $dryRun, array &$affectedConversationIds): int
    {
        $base = DB::table('messages')
            ->join('conversations', 'conversations.id', '=', 'messages.conversation_id')
            ->whereNotNull('messages.imap_uid')
            ->whereNotNull('messages.imap_folder')
            ->whereNotNull('conversations.email_account_id');

        if ($scopeWorkspaceId) {
            $base->where('messages.workspace_id', $scopeWorkspaceId);
        }

        $groups = (clone $base)
            ->selectRaw('conversations.email_account_id, messages.imap_folder, messages.imap_uid, MIN(messages.id) as keeper_id, COUNT(*) as cnt')
            ->groupBy('conversations.email_account_id', 'messages.imap_folder', 'messages.imap_uid')
            ->havingRaw('COUNT(*) > 1')
            ->get();

        if ($groups->isEmpty()) {
            $this->command?->info('  [same imap_uid + folder per account] no duplicate groups found.');
            return 0;
        }

        $deleted = 0;
        foreach ($groups as $group) {
            $keeperId = (int) $group->keeper_id;

            $dupeMessageQuery = DB::table('messages')
                ->join('conversations', 'conversations.id', '=', 'messages.conversation_id')
                ->where('conversations.email_account_id', $group->email_account_id)
                ->where('messages.imap_folder', $group->imap_folder)
                ->where('messages.imap_uid', $group->imap_uid)
                ->where('messages.id', '!=', $keeperId);

            if ($scopeWorkspaceId) {
                $dupeMessageQuery->where('messages.workspace_id', $scopeWorkspaceId);
            }

            $idsToDelete = $dupeMessageQuery->pluck('messages.id')->all();
            $convIds = $dupeMessageQuery->pluck('messages.conversation_id')->all();
            foreach ($convIds as $cid) {
                if ($cid) {
                    $affectedConversationIds[] = (int) $cid;
                }
            }

            if (!empty($idsToDelete)) {
                $deleted += $dryRun
                    ? count($idsToDelete)
                    : DB::table('messages')->whereIn('id', $idsToDelete)->delete();
            }
        }

        $verb = $dryRun ? 'would delete' : 'deleted';
        $this->command?->info("  [same imap_uid + folder per account] {$groups->count()} group(s), {$verb} {$deleted} row(s).");

        return $deleted;
    }
}
