<?php

namespace App\Console\Commands;

use App\Jobs\Contact\ExtractMessageAttachmentsJob;
use App\Models\Contact\Hub\ContactMessage;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Throwable;

class ImportInboundContactMessages extends Command
{
    protected $signature = 'contact-messages:import-inbound
        {--legacy-connection=legacy_mysql : Old database connection name}
        {--target-connection=mysql : New database connection name}
        {--legacy-solicitor-id= : Only import messages for this old solicitor ID}
        {--target-solicitor-id= : Force imported messages onto this new solicitor ID}
        {--limit= : Limit number of messages}
        {--dry-run : Show what would happen without importing}
        {--queue : Dispatch ExtractMessageAttachmentsJob}
        {--sync-jobs : Run ExtractMessageAttachmentsJob immediately}';

    protected $description = 'Import inbound contact messages from old DB into this DB, remapping IDs and extracting attachments.';

    public function handle(): int
    {
        $legacyConnection = (string) $this->option('legacy-connection');
        $targetConnection = (string) $this->option('target-connection');

        $dryRun = (bool) $this->option('dry-run');
        $limit = $this->option('limit') ? (int) $this->option('limit') : null;

        $query = DB::connection($legacyConnection)
            ->table('contact_messages')
            ->where('direction', 'inbound')
            ->orderBy('id');

        if ($this->option('legacy-solicitor-id')) {
            $query->where('solicitor_id', (int) $this->option('legacy-solicitor-id'));
        }

        if ($limit) {
            $query->limit($limit);
        }

        $messages = $query->get();

        $this->info("Found {$messages->count()} inbound message(s).");

        if ($dryRun) {
            $this->warn('Dry run mode. Nothing will be inserted.');
        }

        $imported = 0;
        $skipped = 0;
        $failed = 0;

        foreach ($messages as $legacyMessage) {
            try {
                $alreadyImported = DB::connection($targetConnection)
                    ->table('contact_messages')
                    ->where('legacy_contact_message_id', $legacyMessage->id)
                    ->exists();

                if ($alreadyImported) {
                    $skipped++;

                    $this->line("Skipping old message {$legacyMessage->id}; already imported.");

                    continue;
                }

                $newSolicitorId = $this->resolveTargetSolicitorId($legacyMessage);

                $newSenderEmailId = $this->mapSenderEmailId(
                    legacyConnection: $legacyConnection,
                    targetConnection: $targetConnection,
                    legacySenderEmailId: $legacyMessage->sender_email_id,
                    targetSolicitorId: $newSolicitorId
                );

                $attachmentCount = DB::connection($legacyConnection)
                    ->table('contact_message_attachments')
                    ->where('contact_message_id', $legacyMessage->id)
                    ->count();

                if ($dryRun) {
                    $this->line(
                        "Would import old message {$legacyMessage->id} " .
                        "for solicitor {$newSolicitorId}, sender {$newSenderEmailId}, " .
                        "with {$attachmentCount} attachment(s)."
                    );

                    continue;
                }

                DB::connection($targetConnection)->transaction(function () use (
                    $legacyConnection,
                    $targetConnection,
                    $legacyMessage,
                    $newSolicitorId,
                    $newSenderEmailId,
                    &$imported
                ) {
                    $newMessageId = $this->insertMessage(
                        targetConnection: $targetConnection,
                        legacyMessage: $legacyMessage,
                        newSolicitorId: $newSolicitorId,
                        newSenderEmailId: $newSenderEmailId
                    );

                    $this->insertAttachments(
                        legacyConnection: $legacyConnection,
                        targetConnection: $targetConnection,
                        legacyMessageId: $legacyMessage->id,
                        newMessageId: $newMessageId
                    );

                    $newMessage = ContactMessage::query()->findOrFail($newMessageId);

                    $this->runExtractionJob($newMessage);

                    $imported++;

                    $this->info("Imported old message {$legacyMessage->id} as new message {$newMessageId}.");
                });
            } catch (Throwable $e) {
                $failed++;

                $this->error("Failed old message {$legacyMessage->id}: {$e->getMessage()}");

                report($e);
            }
        }

        $this->newLine();
        $this->info("Imported: {$imported}");
        $this->info("Skipped: {$skipped}");
        $this->info("Failed: {$failed}");

        return self::SUCCESS;
    }

    private function insertMessage(
        string $targetConnection,
        object $legacyMessage,
        int $newSolicitorId,
        ?int $newSenderEmailId
    ): int {
        $data = (array) $legacyMessage;

        unset($data['id']);

        $data['solicitor_id'] = $newSolicitorId;
        $data['sender_email_id'] = $newSenderEmailId;

        /*
         * These IDs are dangerous to copy if the new database already has its own IDs.
         * Keep them null unless you have mapping tables for them.
         */
        $data['template_id'] = null;
        $data['sms_sender_id'] = null;
        $data['created_by'] = null;
        $data['updated_by'] = null;

        /*
         * Preserve old timestamps and message timeline exactly as imported.
         * Because this uses query builder, Laravel will not overwrite created_at / updated_at.
         */
        $data['created_at'] = $legacyMessage->created_at;
        $data['updated_at'] = $legacyMessage->updated_at;

        /*
         * Requires this column on the new DB.
         */
        $data['legacy_contact_message_id'] = $legacyMessage->id;

        return DB::connection($targetConnection)
            ->table('contact_messages')
            ->insertGetId($data);
    }

    private function insertAttachments(
        string $legacyConnection,
        string $targetConnection,
        int $legacyMessageId,
        int $newMessageId
    ): void {
        $attachments = DB::connection($legacyConnection)
            ->table('contact_message_attachments')
            ->where('contact_message_id', $legacyMessageId)
            ->orderBy('id')
            ->get();

        foreach ($attachments as $legacyAttachment) {
            $data = (array) $legacyAttachment;

            unset($data['id']);

            $data['contact_message_id'] = $newMessageId;

            /*
             * Requires this column on the new DB.
             */
            $data['legacy_contact_message_attachment_id'] = $legacyAttachment->id;

            /*
             * Preserve old attachment timestamps.
             */
            $data['created_at'] = $legacyAttachment->created_at;
            $data['updated_at'] = $legacyAttachment->updated_at;

            DB::connection($targetConnection)
                ->table('contact_message_attachments')
                ->insert($data);
        }
    }

    private function mapSenderEmailId(
        string $legacyConnection,
        string $targetConnection,
        ?int $legacySenderEmailId,
        int $targetSolicitorId
    ): ?int {
        if (!$legacySenderEmailId) {
            return null;
        }

        $legacySender = DB::connection($legacyConnection)
            ->table('solicitor_email_senders')
            ->where('id', $legacySenderEmailId)
            ->first();

        if (!$legacySender) {
            throw new \RuntimeException("Old sender email ID {$legacySenderEmailId} was not found.");
        }

        $emailAddress = strtolower(trim($legacySender->email_address ?? ''));

        if ($emailAddress === '') {
            throw new \RuntimeException("Old sender email ID {$legacySenderEmailId} has no email_address.");
        }

        $query = DB::connection($targetConnection)
            ->table('solicitor_email_senders')
            ->where('solicitor_id', $targetSolicitorId)
            ->whereRaw('LOWER(email_address) = ?', [$emailAddress]);

        /*
         * Match purpose too when available.
         * Example from your screenshot: dsar / general.
         */
        if (!empty($legacySender->purpose)) {
            $query->where('purpose', $legacySender->purpose);
        }

        $newSenderId = $query->value('id');

        if (!$newSenderId) {
            throw new \RuntimeException(
                "No matching sender in new solicitor_email_senders for {$emailAddress} " .
                "under solicitor {$targetSolicitorId}."
            );
        }

        return (int) $newSenderId;
    }

    private function resolveTargetSolicitorId(object $legacyMessage): int
    {
        if ($this->option('target-solicitor-id')) {
            return (int) $this->option('target-solicitor-id');
        }

        /*
         * If solicitor IDs are the same, this is okay.
         * If not, pass --target-solicitor-id=1 or add a proper solicitor mapping table.
         */
        return (int) $legacyMessage->solicitor_id;
    }

    private function runExtractionJob(ContactMessage $message): void
    {
        if ($this->option('sync-jobs')) {
            ExtractMessageAttachmentsJob::dispatchSync($message->id);
            return;
        }

        if ($this->option('queue')) {
            ExtractMessageAttachmentsJob::dispatch($message->id);
        }
    }
}