<?php

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

return new class extends Migration
{
    private array $sectionColumns = ['title', 'subtitle', 'content', 'button_text'];

    private array $itemColumns = ['title', 'description'];

    /**
     * Run the migrations.
     */
    public function up(): void
    {
        $this->wrapAsJson('sections', $this->sectionColumns);
        $this->wrapAsJson('section_items', $this->itemColumns);
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        $this->unwrapFromJson('sections', $this->sectionColumns);
        $this->unwrapFromJson('section_items', $this->itemColumns);
    }

    private function wrapAsJson(string $table, array $columns): void
    {
        foreach ($columns as $column) {
            // Bundle the existing plain-text value as the `id` locale while the
            // column is still VARCHAR/TEXT, so no data is lost in the conversion.
            DB::statement("UPDATE `{$table}` SET `{$column}` = JSON_OBJECT('id', `{$column}`) WHERE `{$column}` IS NOT NULL");
            DB::statement("ALTER TABLE `{$table}` MODIFY `{$column}` JSON NULL");
        }
    }

    private function unwrapFromJson(string $table, array $columns): void
    {
        foreach ($columns as $column) {
            DB::statement("ALTER TABLE `{$table}` MODIFY `{$column}` TEXT NULL");
            DB::statement("UPDATE `{$table}` SET `{$column}` = JSON_UNQUOTE(JSON_EXTRACT(`{$column}`, '$.id')) WHERE `{$column}` IS NOT NULL");
        }
    }
};
