<?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->convertExistingTablesToInnoDb();
        $this->patchCoreTables();
        $this->createMissingOperationalTables();
        $this->seedMinimumTreasuryAndAccounts();
    }

    public function down(): void
    {
        // Stability patch only. Do not drop production data on rollback.
    }

    private function convertExistingTablesToInnoDb(): void
    {
        $database = DB::getDatabaseName();
        $tables = DB::select(
            'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE = ?',
            [$database, 'MyISAM']
        );

        foreach ($tables as $table) {
            $tableName = $table->TABLE_NAME;
            DB::statement("ALTER TABLE `{$tableName}` ENGINE=InnoDB");
        }
    }

    private function patchCoreTables(): void
    {
        if (Schema::hasTable('customers')) {
            Schema::table('customers', function (Blueprint $table) {
                if (!Schema::hasColumn('customers', 'wallet_balance')) {
                    $table->decimal('wallet_balance', 12, 2)->default(0)->after('discount_rate');
                }
                if (!Schema::hasColumn('customers', 'debt_balance')) {
                    $table->decimal('debt_balance', 12, 2)->default(0)->after('wallet_balance');
                }
                if (!Schema::hasColumn('customers', 'discount_percentage')) {
                    $table->decimal('discount_percentage', 5, 2)->default(0)->after('discount_rate');
                }
            });
        }

        if (Schema::hasTable('ingredients')) {
            Schema::table('ingredients', function (Blueprint $table) {
                if (!Schema::hasColumn('ingredients', 'section')) {
                    $table->string('section', 30)->default('kitchen')->after('unit');
                }
                if (!Schema::hasColumn('ingredients', 'expiry_date')) {
                    $table->date('expiry_date')->nullable()->after('section');
                }
                if (!Schema::hasColumn('ingredients', 'barista_stock')) {
                    $table->decimal('barista_stock', 12, 3)->default(0)->after('kitchen_stock');
                }
            });
        }

        if (Schema::hasTable('shifts')) {
            Schema::table('shifts', function (Blueprint $table) {
                if (!Schema::hasColumn('shifts', 'total_sales')) {
                    $table->decimal('total_sales', 12, 2)->default(0)->after('closing_amount');
                }
                if (!Schema::hasColumn('shifts', 'total_expense')) {
                    $table->decimal('total_expense', 12, 2)->default(0)->after('total_sales');
                }
            });
        }

        if (Schema::hasTable('orders')) {
            Schema::table('orders', function (Blueprint $table) {
                if (!Schema::hasColumn('orders', 'total_amount')) {
                    $table->decimal('total_amount', 12, 2)->default(0)->after('total_price');
                }
                if (!Schema::hasColumn('orders', 'total_cost')) {
                    $table->decimal('total_cost', 12, 2)->default(0)->after('total_amount');
                }
                if (!Schema::hasColumn('orders', 'net_profit')) {
                    $table->decimal('net_profit', 12, 2)->default(0)->after('total_cost');
                }
            });
            DB::statement('UPDATE `orders` SET `total_amount` = `total_price` WHERE `total_amount` = 0 OR `total_amount` IS NULL');
        }

        if (Schema::hasTable('order_items')) {
            try {
                DB::statement('ALTER TABLE `order_items` MODIFY `quantity` DECIMAL(12,3) NOT NULL DEFAULT 1');
            } catch (Throwable $e) {
                // Keep migration safe on hosts that reject MODIFY for any reason.
            }
        }

        if (Schema::hasTable('suppliers')) {
            Schema::table('suppliers', function (Blueprint $table) {
                if (!Schema::hasColumn('suppliers', 'balance')) {
                    $table->decimal('balance', 12, 2)->default(0)->after('phone');
                }
            });
        }

        if (Schema::hasTable('purchases')) {
            Schema::table('purchases', function (Blueprint $table) {
                if (!Schema::hasColumn('purchases', 'payment_type')) {
                    $table->string('payment_type', 30)->default('cash')->after('supplier_id');
                }
                if (!Schema::hasColumn('purchases', 'subtotal')) {
                    $table->decimal('subtotal', 12, 2)->default(0)->after('payment_type');
                }
                if (!Schema::hasColumn('purchases', 'tax_amount')) {
                    $table->decimal('tax_amount', 12, 2)->default(0)->after('subtotal');
                }
                if (!Schema::hasColumn('purchases', 'discount_amount')) {
                    $table->decimal('discount_amount', 12, 2)->default(0)->after('tax_amount');
                }
                if (!Schema::hasColumn('purchases', 'paid_amount')) {
                    $table->decimal('paid_amount', 12, 2)->default(0)->after('total_amount');
                }
                if (!Schema::hasColumn('purchases', 'due_amount')) {
                    $table->decimal('due_amount', 12, 2)->default(0)->after('paid_amount');
                }
                if (!Schema::hasColumn('purchases', 'invoice_file')) {
                    $table->string('invoice_file')->nullable()->after('due_amount');
                }
            });
        }
    }

    private function createMissingOperationalTables(): void
    {
        if (!Schema::hasTable('customer_transactions')) {
            Schema::create('customer_transactions', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('customer_id');
                $table->string('type', 50);
                $table->decimal('amount', 12, 2)->default(0);
                $table->text('notes')->nullable();
                $table->timestamps();
                $table->index('customer_id');
            });
        }

        if (!Schema::hasTable('expenses')) {
            Schema::create('expenses', function (Blueprint $table) {
                $table->id();
                $table->string('category', 100);
                $table->decimal('amount', 12, 2)->default(0);
                $table->text('notes')->nullable();
                $table->unsignedBigInteger('user_id')->nullable();
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('leave_requests')) {
            Schema::create('leave_requests', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('employee_id');
                $table->string('leave_type', 30);
                $table->date('start_date');
                $table->date('end_date');
                $table->integer('total_days')->default(1);
                $table->text('reason')->nullable();
                $table->string('status', 30)->default('pending');
                $table->timestamps();
                $table->index('employee_id');
            });
        }

        if (!Schema::hasTable('payrolls')) {
            Schema::create('payrolls', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('employee_id');
                $table->string('month_year', 7);
                $table->decimal('basic_salary', 12, 2)->default(0);
                $table->decimal('advances', 12, 2)->default(0);
                $table->decimal('bonuses', 12, 2)->default(0);
                $table->decimal('transport_allowance', 12, 2)->default(0);
                $table->decimal('deductions', 12, 2)->default(0);
                $table->decimal('net_salary', 12, 2)->default(0);
                $table->string('status', 30)->default('draft');
                $table->timestamp('paid_at')->nullable();
                $table->timestamps();
                $table->unique(['employee_id', 'month_year']);
            });
        }

        if (!Schema::hasTable('warehouses')) {
            Schema::create('warehouses', function (Blueprint $table) {
                $table->id();
                $table->string('name');
                $table->string('type', 50)->default('main');
                $table->boolean('is_active')->default(true);
                $table->timestamps();
            });
            DB::table('warehouses')->insert([
                ['name' => 'المخزن الرئيسي', 'type' => 'main', 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
                ['name' => 'مخزن المطبخ', 'type' => 'kitchen', 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
                ['name' => 'مخزن البار', 'type' => 'barista', 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
            ]);
        }

        if (!Schema::hasTable('stock_movements')) {
            Schema::create('stock_movements', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('ingredient_id')->nullable();
                $table->string('movement_type', 50);
                $table->decimal('quantity', 12, 3)->default(0);
                $table->string('from_location', 50)->nullable();
                $table->string('to_location', 50)->nullable();
                $table->decimal('unit_cost', 12, 2)->default(0);
                $table->text('notes')->nullable();
                $table->unsignedBigInteger('user_id')->nullable();
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('entertainment_devices')) {
            Schema::create('entertainment_devices', function (Blueprint $table) {
                $table->id();
                $table->string('name');
                $table->string('type', 80)->default('عام');
                $table->decimal('hourly_rate', 12, 2)->default(0);
                $table->string('status', 30)->default('available');
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('entertainment_sessions')) {
            Schema::create('entertainment_sessions', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('device_id');
                $table->timestamp('start_time')->nullable();
                $table->timestamp('end_time')->nullable();
                $table->timestamp('paused_at')->nullable();
                $table->integer('pause_seconds')->default(0);
                $table->integer('total_minutes')->default(0);
                $table->decimal('total_price', 12, 2)->default(0);
                $table->string('status', 30)->default('running');
                $table->timestamps();
                $table->index('device_id');
            });
        }

        if (!Schema::hasTable('gym_plans')) {
            Schema::create('gym_plans', function (Blueprint $table) {
                $table->id();
                $table->string('name');
                $table->integer('duration_days')->default(30);
                $table->decimal('price', 12, 2)->default(0);
                $table->timestamps();
            });
        } else {
            Schema::table('gym_plans', function (Blueprint $table) {
                if (!Schema::hasColumn('gym_plans', 'created_at')) {
                    $table->timestamp('created_at')->nullable();
                }
                if (!Schema::hasColumn('gym_plans', 'updated_at')) {
                    $table->timestamp('updated_at')->nullable();
                }
            });
        }

        if (!Schema::hasTable('gym_subscriptions')) {
            Schema::create('gym_subscriptions', function (Blueprint $table) {
                $table->id();
                $table->string('customer_name');
                $table->string('phone')->nullable();
                $table->unsignedBigInteger('plan_id');
                $table->date('start_date');
                $table->date('end_date');
                $table->decimal('price_paid', 12, 2)->default(0);
                $table->decimal('paid_amount', 12, 2)->default(0);
                $table->string('status', 30)->default('active');
                $table->timestamps();
                $table->index('plan_id');
            });
        } else {
            Schema::table('gym_subscriptions', function (Blueprint $table) {
                if (!Schema::hasColumn('gym_subscriptions', 'status')) {
                    $table->string('status', 30)->default('active');
                }
                if (!Schema::hasColumn('gym_subscriptions', 'paid_amount')) {
                    $table->decimal('paid_amount', 12, 2)->default(0);
                }
                if (!Schema::hasColumn('gym_subscriptions', 'created_at')) {
                    $table->timestamp('created_at')->nullable();
                }
                if (!Schema::hasColumn('gym_subscriptions', 'updated_at')) {
                    $table->timestamp('updated_at')->nullable();
                }
            });
        }

        if (!Schema::hasTable('currencies')) {
            Schema::create('currencies', function (Blueprint $table) {
                $table->id();
                $table->string('code', 10)->unique();
                $table->string('name');
                $table->decimal('exchange_rate', 12, 4)->default(1);
                $table->boolean('is_active')->default(true);
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('accounts_treasury')) {
            Schema::create('accounts_treasury', function (Blueprint $table) {
                $table->id();
                $table->string('name');
                $table->string('type', 30)->default('cash');
                $table->string('currency', 10)->default('EGP');
                $table->decimal('balance', 14, 2)->default(0);
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('treasury_balances')) {
            Schema::create('treasury_balances', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('treasury_id');
                $table->string('currency_code', 10)->default('EGP');
                $table->decimal('balance', 14, 2)->default(0);
                $table->timestamps();
                $table->unique(['treasury_id', 'currency_code']);
            });
        }

        if (!Schema::hasTable('treasury_transactions')) {
            Schema::create('treasury_transactions', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('treasury_id')->nullable();
                $table->string('type', 50);
                $table->decimal('amount', 14, 2)->default(0);
                $table->string('currency')->nullable();
                $table->string('payment_method')->nullable();
                $table->string('reference_type')->nullable();
                $table->unsignedBigInteger('reference_id')->nullable();
                $table->text('description')->nullable();
                $table->unsignedBigInteger('user_id')->nullable();
                $table->unsignedBigInteger('created_by')->nullable();
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('treasury_transactions_new')) {
            Schema::create('treasury_transactions_new', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('treasury_id')->nullable();
                $table->string('type', 50);
                $table->decimal('amount', 14, 2)->default(0);
                $table->string('currency_code', 10)->default('EGP');
                $table->text('notes')->nullable();
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('production_orders')) {
            Schema::create('production_orders', function (Blueprint $table) {
                $table->id();
                $table->string('reference_no')->nullable();
                $table->string('type', 30)->nullable();
                $table->string('status', 30)->default('completed');
                $table->text('notes')->nullable();
                $table->unsignedBigInteger('user_id')->nullable();
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('production_inputs')) {
            Schema::create('production_inputs', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('production_order_id');
                $table->unsignedBigInteger('ingredient_id');
                $table->decimal('quantity', 12, 3)->default(0);
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('production_outputs')) {
            Schema::create('production_outputs', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('production_order_id');
                $table->unsignedBigInteger('ingredient_id')->nullable();
                $table->string('name')->nullable();
                $table->decimal('quantity', 12, 3)->default(0);
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('audit_logs')) {
            Schema::create('audit_logs', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('user_id')->nullable();
                $table->string('action');
                $table->string('table_name')->nullable();
                $table->unsignedBigInteger('record_id')->nullable();
                $table->json('old_values')->nullable();
                $table->json('new_values')->nullable();
                $table->string('ip_address')->nullable();
                $table->timestamps();
            });
        }
    }

    private function seedMinimumTreasuryAndAccounts(): void
    {
        if (Schema::hasTable('treasuries') && DB::table('treasuries')->count() === 0) {
            DB::table('treasuries')->insert([
                'name' => 'الخزينة الرئيسية',
                'balance' => 0,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }

        if (Schema::hasTable('accounts_treasury') && DB::table('accounts_treasury')->count() === 0) {
            DB::table('accounts_treasury')->insert([
                ['name' => 'الخزينة النقدية (جنيه)', 'type' => 'cash', 'currency' => 'EGP', 'balance' => 0, 'created_at' => now(), 'updated_at' => now()],
                ['name' => 'حساب البنك (فيزا)', 'type' => 'bank', 'currency' => 'EGP', 'balance' => 0, 'created_at' => now(), 'updated_at' => now()],
                ['name' => 'محفظة إلكترونية', 'type' => 'wallet', 'currency' => 'EGP', 'balance' => 0, 'created_at' => now(), 'updated_at' => now()],
            ]);
        }

        if (Schema::hasTable('currencies')) {
            foreach ([['EGP', 'الجنيه المصري', 1], ['USD', 'US Dollar', 50], ['EUR', 'Euro', 55]] as [$code, $name, $rate]) {
                if (!DB::table('currencies')->where('code', $code)->exists()) {
                    DB::table('currencies')->insert([
                        'code' => $code,
                        'name' => $name,
                        'exchange_rate' => $rate,
                        'is_active' => true,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                }
            }
        }
    }
};