<?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
    {
        $this->preserveFixedWindows(
            'catalog_items',
            'minimum_use_date',
            'maximum_use_date',
        );
        $this->preserveFixedWindows('tickets', 'starts_at', 'expires_at');

        Schema::table('tickets', function (Blueprint $table): void {
            $table->dropColumn(['starts_at', 'expires_at']);
        });

        Schema::table('catalog_items', function (Blueprint $table): void {
            $table->dropColumn(['minimum_use_date', 'maximum_use_date']);
        });

        Schema::table('variantes', function (Blueprint $table): void {
            $table->dropColumn(['minimum_use_date', 'maximum_use_date']);
        });
    }

    public function down(): void
    {
        Schema::table('variantes', function (Blueprint $table): void {
            $table->dateTime('minimum_use_date')->nullable();
            $table->dateTime('maximum_use_date')->nullable();
        });

        Schema::table('catalog_items', function (Blueprint $table): void {
            $table->dateTime('minimum_use_date')->nullable();
            $table->dateTime('maximum_use_date')->nullable();
        });

        Schema::table('tickets', function (Blueprint $table): void {
            $table->dateTime('starts_at')->nullable();
            $table->dateTime('expires_at')->nullable();
        });
    }

    private function preserveFixedWindows(
        string $table,
        string $startsAtColumn,
        string $expiresAtColumn,
    ): void {
        DB::table($table)
            ->whereNull('validity_time_id')
            ->where(function ($query) use ($startsAtColumn, $expiresAtColumn): void {
                $query->whereNotNull($startsAtColumn)
                    ->orWhereNotNull($expiresAtColumn);
            })
            ->select([$startsAtColumn, $expiresAtColumn])
            ->distinct()
            ->get()
            ->each(function ($window) use ($table, $startsAtColumn, $expiresAtColumn): void {
                $startsAt = $window->{$startsAtColumn};
                $expiresAt = $window->{$expiresAtColumn};
                $validityTimeId = DB::table('validity_times')->insertGetId([
                    'type' => 'fixed_window',
                    'start_time' => null,
                    'end_time' => null,
                    'fixed_starts_at' => $startsAt,
                    'fixed_expires_at' => $expiresAt,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);

                DB::table($table)
                    ->whereNull('validity_time_id')
                    ->when(
                        $startsAt === null,
                        fn ($query) => $query->whereNull($startsAtColumn),
                        fn ($query) => $query->where($startsAtColumn, $startsAt),
                    )
                    ->when(
                        $expiresAt === null,
                        fn ($query) => $query->whereNull($expiresAtColumn),
                        fn ($query) => $query->where($expiresAtColumn, $expiresAt),
                    )
                    ->update(['validity_time_id' => $validityTimeId]);
            });
    }
};
