<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        if (Schema::getConnection()->getDriverName() !== 'mysql') {
            return;
        }

        if (!Schema::hasTable('forum_posts') || !Schema::hasTable('forum_threads')) {
            return;
        }

        if ($this->foreignKeyExists('forum_posts', 'forum_posts_thread_id_foreign')) {
            return;
        }

        $this->deleteOrphanedForumPosts();

        Schema::table('forum_posts', function (Blueprint $table) {
            $table->foreign('thread_id', 'forum_posts_thread_id_foreign')
                ->references('id')
                ->on('forum_threads')
                ->cascadeOnDelete();
        });
    }

    public function down(): void
    {
        if (Schema::getConnection()->getDriverName() !== 'mysql') {
            return;
        }

        if (!Schema::hasTable('forum_posts')) {
            return;
        }

        if (! $this->foreignKeyExists('forum_posts', 'forum_posts_thread_id_foreign')) {
            return;
        }

        Schema::table('forum_posts', function (Blueprint $table) {
            $table->dropForeign('forum_posts_thread_id_foreign');
        });
    }

    private function foreignKeyExists(string $tableName, string $constraintName): bool
    {
        $connection = Schema::getConnection();
        $driver = $connection->getDriverName();

        if ($driver === 'mysql') {
            $databaseName = $connection->getDatabaseName();

            return DB::table('information_schema.table_constraints')
                ->where('constraint_schema', $databaseName)
                ->where('table_name', $tableName)
                ->where('constraint_name', $constraintName)
                ->where('constraint_type', 'FOREIGN KEY')
                ->exists();
        }
        return false;
    }

    private function deleteOrphanedForumPosts(): void
    {
        $orphanedPostIds = DB::table('forum_posts')
            ->leftJoin('forum_threads', 'forum_threads.id', '=', 'forum_posts.thread_id')
            ->whereNull('forum_threads.id')
            ->pluck('forum_posts.id');

        if ($orphanedPostIds->isEmpty()) {
            return;
        }

        if (Schema::hasTable('forum_attachments')) {
            DB::table('forum_attachments')
                ->whereIn('post_id', $orphanedPostIds)
                ->delete();
        }

        if (Schema::hasTable('forum_post_reports')) {
            DB::table('forum_post_reports')
                ->whereIn('post_id', $orphanedPostIds)
                ->orWhereIn('thread_id', function ($query) {
                    $query->select('forum_posts.thread_id')
                        ->from('forum_posts')
                        ->leftJoin('forum_threads', 'forum_threads.id', '=', 'forum_posts.thread_id')
                        ->whereNull('forum_threads.id');
                })
                ->delete();
        }

        DB::table('forum_posts')
            ->whereIn('id', $orphanedPostIds)
            ->delete();
    }
};