<?php

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

return new class extends Migration
{
    /**
     * Correction des problèmes SQLSTATE[42S22] - Column not found
     * 
     * Problèmes identifiés:
     * 1. wallet_transactions table - manque la colonne withdrawal_id
     * 2. referrals table - utilise sponsor_id et referred_id (pas referrer_id)
     * 3. orders table - utilise total_amount et total
     */
    public function up(): void
    {
        // 1. Ajouter withdrawal_id à wallet_transactions
        if (Schema::hasTable('wallet_transactions')) {
            if (!Schema::hasColumn('wallet_transactions', 'withdrawal_id')) {
                Schema::table('wallet_transactions', function (Blueprint $table) {
                    if (!Schema::hasColumn('wallet_transactions', 'withdrawal_id')) { $table->foreignId('withdrawal_id')->nullable()->constrained()->onDelete('set null'); }
                });
            }
        }

        // 2. Vérifier que referrals table existe et a les bonnes colonnes
        if (!Schema::hasTable('referrals')) {
            Schema::create('referrals', function (Blueprint $table) {
                $table->id();
                $table->foreignId('sponsor_id')->constrained('users')->onDelete('cascade');
                $table->foreignId('referred_id')->constrained('users')->onDelete('cascade');
                $table->integer('level')->default(1);
                $table->boolean('is_active')->default(true);
                $table->timestamps();
            });
        }

        // 3. Vérifier que orders table a la colonne total_amount
        if (Schema::hasTable('orders')) {
            if (!Schema::hasColumn('orders', 'total_amount')) {
                Schema::table('orders', function (Blueprint $table) {
                    if (!Schema::hasColumn('orders', 'total_amount')) { $table->decimal('total_amount', 15, 2)->default(0); }
                });
            }
        }

        // 4. Vérifier que wallet_transactions a les colonnes balance_before et balance_after
        if (Schema::hasTable('wallet_transactions')) {
            if (!Schema::hasColumn('wallet_transactions', 'balance_before')) {
                Schema::table('wallet_transactions', function (Blueprint $table) {
                    if (!Schema::hasColumn('wallet_transactions', 'balance_before')) { $table->decimal('balance_before', 15, 2)->nullable(); }
                });
            }
            if (!Schema::hasColumn('wallet_transactions', 'balance_after')) {
                Schema::table('wallet_transactions', function (Blueprint $table) {
                    if (!Schema::hasColumn('wallet_transactions', 'balance_after')) { $table->decimal('balance_after', 15, 2)->nullable(); }
                });
            }
        }

        // 5. Créer la table newsletter_subscribers si elle n'existe pas
        if (!Schema::hasTable('newsletter_subscribers')) {
            Schema::create('newsletter_subscribers', function (Blueprint $table) {
                $table->id();
                $table->string('email')->unique();
                $table->string('name')->nullable();
                $table->enum('status', ['active', 'inactive', 'unsubscribed'])->default('active');
                $table->string('source')->nullable();
                $table->json('preferences')->nullable();
                $table->string('unsubscribe_token')->unique();
                $table->timestamp('confirmed_at')->nullable();
                $table->timestamp('unsubscribed_at')->nullable();
                $table->timestamps();
                
                $table->index('email');
                $table->index('status');
            });
        }

        // 6. Ajouter colonnes manquantes à la table newsletter si elle existe
        if (Schema::hasTable('newsletters')) {
            if (!Schema::hasColumn('newsletters', 'name')) {
                Schema::table('newsletters', function (Blueprint $table) {
                    if (!Schema::hasColumn('newsletters', 'name')) { $table->string('name')->nullable(); }
                });
            }
            if (!Schema::hasColumn('newsletters', 'status')) {
                Schema::table('newsletters', function (Blueprint $table) {
                    if (!Schema::hasColumn('newsletters', 'status')) { $table->enum('status', ['active', 'inactive', 'unsubscribed'])->default('active'); }
                });
            }
            if (!Schema::hasColumn('newsletters', 'source')) {
                Schema::table('newsletters', function (Blueprint $table) {
                    if (!Schema::hasColumn('newsletters', 'source')) { $table->string('source')->nullable(); }
                });
            }
            if (!Schema::hasColumn('newsletters', 'preferences')) {
                Schema::table('newsletters', function (Blueprint $table) {
                    if (!Schema::hasColumn('newsletters', 'preferences')) { $table->json('preferences')->nullable(); }
                });
            }
            if (!Schema::hasColumn('newsletters', 'unsubscribe_token')) {
                Schema::table('newsletters', function (Blueprint $table) {
                    if (!Schema::hasColumn('newsletters', 'unsubscribe_token')) { $table->string('unsubscribe_token')->unique()->nullable(); }
                });
            }
            if (!Schema::hasColumn('newsletters', 'confirmed_at')) {
                Schema::table('newsletters', function (Blueprint $table) {
                    if (!Schema::hasColumn('newsletters', 'confirmed_at')) { $table->timestamp('confirmed_at')->nullable(); }
                });
            }
            if (!Schema::hasColumn('newsletters', 'unsubscribed_at')) {
                Schema::table('newsletters', function (Blueprint $table) {
                    if (!Schema::hasColumn('newsletters', 'unsubscribed_at')) { $table->timestamp('unsubscribed_at')->nullable(); }
                });
            }
        }
    }

    public function down(): void
    {
        if (Schema::hasTable('newsletter_subscribers')) {
            Schema::dropIfExists('newsletter_subscribers');
        }

        if (Schema::hasTable('wallet_transactions')) {
            Schema::table('wallet_transactions', function (Blueprint $table) {
                if (Schema::hasColumn('wallet_transactions', 'withdrawal_id')) {
                    $table->dropForeign(['withdrawal_id']);
                    $table->dropColumn('withdrawal_id');
                }
            });
        }
    }
};


