<?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
    {
        foreach (['categories', 'brands', 'colors', 'sizes', 'units'] as $table) {
            $this->normalizeMasterTable($table);
        }

        $this->normalizeSuppliersTable();
        $this->normalizeCustomersTable();
        $this->normalizeProductsTable();
    }

    public function down(): void
    {
        // Safe no-op rollback. This migration normalizes live schema/data.
    }

    private function normalizeMasterTable(string $table): void
    {
        if (!Schema::hasTable($table)) {
            return;
        }

        Schema::table($table, function (Blueprint $blueprint) use ($table) {
            if (!Schema::hasColumn($table, 'code')) {
                $blueprint->string('code', 50)->nullable()->after('name');
            }

            if (!Schema::hasColumn($table, 'status')) {
                $blueprint->string('status', 20)->default('Active')->after('code');
            }
        });

        $this->normalizeStatusColumn($table);
    }

    private function normalizeSuppliersTable(): void
    {
        if (!Schema::hasTable('suppliers')) {
            return;
        }

        Schema::table('suppliers', function (Blueprint $table) {
            if (!Schema::hasColumn('suppliers', 'code')) {
                $table->string('code', 50)->nullable()->after('name');
            }
            if (!Schema::hasColumn('suppliers', 'status')) {
                $table->string('status', 20)->default('Active')->after('code');
            }
            if (!Schema::hasColumn('suppliers', 'phone')) {
                $table->string('phone', 30)->nullable()->after('status');
            }
            if (!Schema::hasColumn('suppliers', 'email')) {
                $table->string('email', 150)->nullable()->after('phone');
            }
            if (!Schema::hasColumn('suppliers', 'contact_person')) {
                $table->string('contact_person', 120)->nullable()->after('email');
            }
            if (!Schema::hasColumn('suppliers', 'address')) {
                $table->string('address', 255)->nullable()->after('contact_person');
            }
            if (!Schema::hasColumn('suppliers', 'opening_balance')) {
                $table->decimal('opening_balance', 12, 2)->default(0)->after('address');
            }
            if (!Schema::hasColumn('suppliers', 'rating')) {
                $table->unsignedTinyInteger('rating')->nullable()->after('opening_balance');
            }
            if (!Schema::hasColumn('suppliers', 'notes')) {
                $table->text('notes')->nullable()->after('rating');
            }
            if (!Schema::hasColumn('suppliers', 'document_path')) {
                $table->string('document_path', 255)->nullable()->after('notes');
            }
        });

        $this->normalizeStatusColumn('suppliers');
    }

    private function normalizeCustomersTable(): void
    {
        if (!Schema::hasTable('customers')) {
            return;
        }

        Schema::table('customers', function (Blueprint $table) {
            if (!Schema::hasColumn('customers', 'email')) {
                $table->string('email', 150)->nullable()->after('phone_number');
            }
            if (!Schema::hasColumn('customers', 'customer_group')) {
                $table->string('customer_group', 50)->default('Retail')->after('address');
            }
            if (!Schema::hasColumn('customers', 'opening_balance')) {
                $table->decimal('opening_balance', 12, 2)->default(0)->after('customer_group');
            }
            if (!Schema::hasColumn('customers', 'points')) {
                $table->integer('points')->default(0)->after('opening_balance');
            }
            if (!Schema::hasColumn('customers', 'wallet_balance')) {
                $table->decimal('wallet_balance', 12, 2)->default(0)->after('points');
            }
            if (!Schema::hasColumn('customers', 'membership_no')) {
                $table->string('membership_no', 60)->nullable()->after('wallet_balance');
            }
            if (!Schema::hasColumn('customers', 'membership_tier')) {
                $table->string('membership_tier', 40)->nullable()->after('membership_no');
            }
            if (!Schema::hasColumn('customers', 'special_discount_percent')) {
                $table->decimal('special_discount_percent', 5, 2)->default(0)->after('membership_tier');
            }
            if (!Schema::hasColumn('customers', 'sms_opt_in')) {
                $table->boolean('sms_opt_in')->default(false)->after('special_discount_percent');
            }
            if (!Schema::hasColumn('customers', 'status')) {
                $table->string('status', 20)->default('Active')->after('sms_opt_in');
            }
        });

        $this->normalizeStatusColumn('customers');
    }

    private function normalizeProductsTable(): void
    {
        if (!Schema::hasTable('products')) {
            return;
        }

        Schema::table('products', function (Blueprint $table) {
            if (!Schema::hasColumn('products', 'category_id')) {
                $table->unsignedBigInteger('category_id')->nullable()->after('category');
            }
            if (!Schema::hasColumn('products', 'brand_id')) {
                $table->unsignedBigInteger('brand_id')->nullable()->after('brand');
            }
            if (!Schema::hasColumn('products', 'size_id')) {
                $table->unsignedBigInteger('size_id')->nullable()->after('size');
            }
            if (!Schema::hasColumn('products', 'color_id')) {
                $table->unsignedBigInteger('color_id')->nullable()->after('color');
            }
            if (!Schema::hasColumn('products', 'supplier_id')) {
                $table->unsignedBigInteger('supplier_id')->nullable()->after('supplier_name');
            }
            if (!Schema::hasColumn('products', 'unit_id')) {
                $table->unsignedBigInteger('unit_id')->nullable()->after('unit');
            }
            if (!Schema::hasColumn('products', 'status')) {
                $table->string('status', 20)->default('Active')->after('stock_quantity');
            }
            if (!Schema::hasColumn('products', 'base_unit')) {
                $table->string('base_unit', 20)->default('pcs')->after('unit');
            }
            if (!Schema::hasColumn('products', 'unit_value_in_base')) {
                $table->unsignedInteger('unit_value_in_base')->default(1)->after('base_unit');
            }
        });

        $this->normalizeStatusColumn('products');
    }

    private function normalizeStatusColumn(string $table): void
    {
        if (!Schema::hasTable($table) || !Schema::hasColumn($table, 'status')) {
            return;
        }

        $driver = Schema::getConnection()->getDriverName();

        if ($driver === 'mysql') {
            DB::statement("UPDATE `{$table}` SET `status` = CASE WHEN `status` IS NULL THEN 'Active' WHEN CAST(`status` AS CHAR) IN ('1', 'true', 'TRUE', 'True', 'Active', 'ACTIVE', 'active') THEN 'Active' ELSE 'Inactive' END");
            DB::statement("ALTER TABLE `{$table}` MODIFY `status` VARCHAR(20) NOT NULL DEFAULT 'Active'");
            return;
        }

        DB::table($table)
            ->whereNull('status')
            ->update(['status' => 'Active']);

        DB::table($table)
            ->whereIn('status', [1, '1', true, 'true', 'TRUE', 'True'])
            ->update(['status' => 'Active']);

        DB::table($table)
            ->whereNotIn('status', ['Active'])
            ->where(function ($query) {
                $query->whereNotNull('status');
            })
            ->update(['status' => 'Inactive']);
    }
};
