<?php

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

class CreateDocumentTopicPivotTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('document_topic', function (Blueprint $table) {
            $table->unsignedBigInteger('document_id');
            $table->unsignedBigInteger('topic_id');
            $table->integer('position')->nullable();
            $table->primary(['document_id', 'topic_id']);

            $table->foreign('document_id')->references('id')->on('documents')->onDelete('cascade');
            $table->foreign('topic_id')->references('id')->on('topics')->onDelete('cascade');
        });

        DB::transaction(function () {
            $documents = DB::table('documents')
                ->whereNotNull('topic_id')
                ->select('id', 'topic_id')
                ->get();

            foreach ($documents as $document) {
                DB::table('document_topic')->insert([
                    'document_id' => $document->id,
                    'topic_id' => $document->topic_id,
                ]);
            }
        });

        Schema::table('documents', function (Blueprint $table) {
            $table->dropForeign(['topic_id']);
            $table->dropColumn('topic_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('documents', function (Blueprint $table) {
            $table->unsignedBigInteger('topic_id')->nullable();
            $table->foreign('topic_id')->references('id')->on('topics')->onDelete('set null');
        });

        DB::transaction(function () {
            $pivotData = DB::table('document_topic')
                ->select('document_id', DB::raw('MIN(topic_id) as topic_id'))
                ->groupBy('document_id')
                ->get();

            foreach ($pivotData as $data) {
                DB::table('documents')
                    ->where('id', $data->document_id)
                    ->update(['topic_id' => $data->topic_id]);
            }
        });

        Schema::dropIfExists('document_topic');
    }
}