<?php

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

return new class extends Migration
{
    public function up(): void
    {
        DB::statement("SET SESSION sql_mode = ''");

        // Drop existing FK constraints (MySQL named them trades_ibfk_*)
        DB::statement('ALTER TABLE trades DROP FOREIGN KEY trades_ibfk_2');  // wallet_id
        DB::statement('ALTER TABLE trades DROP FOREIGN KEY trades_ibfk_3');  // asset_id

        // Make wallet_id and asset_id nullable
        DB::statement('ALTER TABLE trades MODIFY COLUMN wallet_id BIGINT UNSIGNED NULL');
        DB::statement('ALTER TABLE trades MODIFY COLUMN asset_id BIGINT UNSIGNED NULL');

        // Re-add FK constraints (SET NULL on delete so OTC nulls are valid)
        DB::statement('ALTER TABLE trades ADD CONSTRAINT trades_wallet_id_foreign FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE SET NULL');
        DB::statement('ALTER TABLE trades ADD CONSTRAINT trades_asset_id_foreign FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE SET NULL');

        // Add OTC-specific columns
        DB::statement('ALTER TABLE trades ADD COLUMN otc_asset_id BIGINT UNSIGNED NULL AFTER asset_id');
        DB::statement('ALTER TABLE trades ADD CONSTRAINT trades_otc_asset_id_foreign FOREIGN KEY (otc_asset_id) REFERENCES otc_assets(id) ON DELETE SET NULL');
        DB::statement("ALTER TABLE trades ADD COLUMN asset_symbol VARCHAR(50) NULL AFTER otc_asset_id");
        DB::statement("ALTER TABLE trades ADD COLUMN account_type ENUM('demo','real','bonus') NOT NULL DEFAULT 'demo' AFTER asset_symbol");
    }

    public function down(): void
    {
        DB::statement("SET SESSION sql_mode = ''");

        DB::statement('ALTER TABLE trades DROP FOREIGN KEY trades_otc_asset_id_foreign');
        DB::statement('ALTER TABLE trades DROP COLUMN otc_asset_id');
        DB::statement('ALTER TABLE trades DROP COLUMN asset_symbol');
        DB::statement('ALTER TABLE trades DROP COLUMN account_type');

        DB::statement('ALTER TABLE trades DROP FOREIGN KEY trades_wallet_id_foreign');
        DB::statement('ALTER TABLE trades DROP FOREIGN KEY trades_asset_id_foreign');
        DB::statement('ALTER TABLE trades MODIFY COLUMN wallet_id BIGINT UNSIGNED NOT NULL');
        DB::statement('ALTER TABLE trades MODIFY COLUMN asset_id BIGINT UNSIGNED NOT NULL');
        DB::statement('ALTER TABLE trades ADD CONSTRAINT trades_ibfk_2 FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE');
        DB::statement('ALTER TABLE trades ADD CONSTRAINT trades_ibfk_3 FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE');
    }
};

// Made with Bob
