<?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::hasTable('tbl_orders')) {
            return;
        }

        // Backfill IDs desde columnas legacy (si existen)
        if (
            Schema::hasColumn('tbl_orders', 'channel')
            && Schema::hasColumn('tbl_orders', 'channel_id')
            && Schema::hasTable('tbl_order_channels')
        ) {
            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_channels c ON c.code = o.channel\n".
                "SET o.channel_id = c.id_order_channel\n".
                "WHERE o.channel_id IS NULL AND o.channel IS NOT NULL"
            );

            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_channels c ON c.name = o.channel\n".
                "SET o.channel_id = c.id_order_channel\n".
                "WHERE o.channel_id IS NULL AND o.channel IS NOT NULL"
            );
        }

        if (
            Schema::hasColumn('tbl_orders', 'order_type')
            && Schema::hasColumn('tbl_orders', 'order_type_id')
            && Schema::hasTable('tbl_order_types')
        ) {
            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_types t ON t.code = o.order_type\n".
                "SET o.order_type_id = t.id_order_type\n".
                "WHERE o.order_type_id IS NULL AND o.order_type IS NOT NULL"
            );

            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_types t ON t.name = o.order_type\n".
                "SET o.order_type_id = t.id_order_type\n".
                "WHERE o.order_type_id IS NULL AND o.order_type IS NOT NULL"
            );
        }

        if (
            Schema::hasColumn('tbl_orders', 'status')
            && Schema::hasColumn('tbl_orders', 'status_id')
            && Schema::hasTable('tbl_order_statuses')
        ) {
            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_statuses s ON s.code = o.status\n".
                "SET o.status_id = s.id_order_status\n".
                "WHERE o.status_id IS NULL AND o.status IS NOT NULL"
            );

            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_statuses s ON s.name = o.status\n".
                "SET o.status_id = s.id_order_status\n".
                "WHERE o.status_id IS NULL AND o.status IS NOT NULL"
            );
        }

        if (
            Schema::hasColumn('tbl_orders', 'carrier')
            && Schema::hasColumn('tbl_orders', 'carrier_id')
            && Schema::hasTable('tbl_order_carriers')
        ) {
            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_carriers c ON c.code = o.carrier\n".
                "SET o.carrier_id = c.id_order_carrier\n".
                "WHERE o.carrier_id IS NULL AND o.carrier IS NOT NULL"
            );

            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_carriers c ON c.name = o.carrier\n".
                "SET o.carrier_id = c.id_order_carrier\n".
                "WHERE o.carrier_id IS NULL AND o.carrier IS NOT NULL"
            );
        }

        $hasChannel = Schema::hasColumn('tbl_orders', 'channel');
        $hasOrderType = Schema::hasColumn('tbl_orders', 'order_type');
        $hasStatus = Schema::hasColumn('tbl_orders', 'status');
        $hasCarrier = Schema::hasColumn('tbl_orders', 'carrier');

        if (! ($hasChannel || $hasOrderType || $hasStatus || $hasCarrier)) {
            return;
        }

        Schema::table('tbl_orders', function (Blueprint $table) use ($hasChannel, $hasOrderType, $hasStatus, $hasCarrier) {
            if ($hasChannel) {
                $table->dropColumn('channel');
            }
            if ($hasOrderType) {
                $table->dropColumn('order_type');
            }
            if ($hasStatus) {
                $table->dropColumn('status');
            }
            if ($hasCarrier) {
                $table->dropColumn('carrier');
            }
        });
    }

    public function down(): void
    {
        if (! Schema::hasTable('tbl_orders')) {
            return;
        }

        $hasChannelId = Schema::hasColumn('tbl_orders', 'channel_id');
        $hasOrderTypeId = Schema::hasColumn('tbl_orders', 'order_type_id');
        $hasStatusId = Schema::hasColumn('tbl_orders', 'status_id');
        $hasCarrierId = Schema::hasColumn('tbl_orders', 'carrier_id');

        $hasChannel = Schema::hasColumn('tbl_orders', 'channel');
        $hasOrderType = Schema::hasColumn('tbl_orders', 'order_type');
        $hasStatus = Schema::hasColumn('tbl_orders', 'status');
        $hasCarrier = Schema::hasColumn('tbl_orders', 'carrier');

        Schema::table('tbl_orders', function (Blueprint $table) use ($hasChannel, $hasOrderType, $hasStatus, $hasCarrier) {
            if (! $hasChannel) {
                $table->string('channel', 60)->nullable();
            }
            if (! $hasOrderType) {
                $table->string('order_type', 60)->nullable();
            }
            if (! $hasStatus) {
                $table->string('status', 60)->nullable();
            }
            if (! $hasCarrier) {
                $table->string('carrier', 60)->nullable();
            }
        });

        if ($hasChannelId && Schema::hasTable('tbl_order_channels')) {
            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_channels c ON c.id_order_channel = o.channel_id\n".
                "SET o.channel = c.code\n".
                "WHERE o.channel IS NULL AND o.channel_id IS NOT NULL"
            );
        }

        if ($hasOrderTypeId && Schema::hasTable('tbl_order_types')) {
            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_types t ON t.id_order_type = o.order_type_id\n".
                "SET o.order_type = t.code\n".
                "WHERE o.order_type IS NULL AND o.order_type_id IS NOT NULL"
            );
        }

        if ($hasStatusId && Schema::hasTable('tbl_order_statuses')) {
            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_statuses s ON s.id_order_status = o.status_id\n".
                "SET o.status = s.code\n".
                "WHERE o.status IS NULL AND o.status_id IS NOT NULL"
            );
        }

        if ($hasCarrierId && Schema::hasTable('tbl_order_carriers')) {
            DB::statement(
                "UPDATE tbl_orders o\n".
                "JOIN tbl_order_carriers c ON c.id_order_carrier = o.carrier_id\n".
                "SET o.carrier = c.code\n".
                "WHERE o.carrier IS NULL AND o.carrier_id IS NOT NULL"
            );
        }
    }
};
