<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class ValidateOldDatabaseCommand extends Command
{
    /**
     * The name and signature of the console command.
     */
    protected $signature = 'validate:old-database 
                           {--connection=old_database : Old database connection name}
                           {--limit=10 : Number of sample records to validate}';

    /**
     * The console command description.
     */
    protected $description = 'Validate old database structure and show sample data mapping';

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        $this->info('🔍 Validating old database structure...');

        try {
            $connection = $this->option('connection');
            $limit = (int) $this->option('limit');
            $oldDb = DB::connection($connection);

            // Check database connection
            $this->info("Testing connection to: {$connection}");
            $oldDb->getPdo();
            $this->info('✅ Database connection successful');

            // Validate associations table
            $this->validateAssociationsTable($oldDb, $limit);

            // Validate users table
            $this->validateUsersTable($oldDb, $limit);

            $this->info('✅ Validation completed successfully');

            return Command::SUCCESS;
        } catch (\Exception $e) {
            $this->error('❌ Validation failed: '.$e->getMessage());

            return Command::FAILURE;
        }
    }

    /**
     * Validate associations table structure and data
     */
    protected function validateAssociationsTable($oldDb, int $limit): void
    {
        $this->info("\n👥 Validating associations table...");

        // Check if table exists
        if (! $oldDb->getSchemaBuilder()->hasTable('sek_associations')) {
            $this->error('❌ sek_associations table not found');

            return;
        }

        // Get table structure
        $columns = $oldDb->getSchemaBuilder()->getColumnListing('sek_associations');
        $this->info('📋 Available columns: '.implode(', ', $columns));

        // Check for expected columns
        $expectedColumns = ['id', 'name_ar', 'name_en', 'license_number', 'association_admin_id'];
        $missingColumns = array_diff($expectedColumns, $columns);

        if (! empty($missingColumns)) {
            $this->warn('⚠️  Missing expected columns: '.implode(', ', $missingColumns));
        }

        // Get sample data with joins
        $count = $oldDb->table('sek_associations')->count();
        $this->info("📊 Total associations: {$count}");

        if ($count > 0) {
            $samples = $oldDb->table('sek_associations')
                ->leftJoin('sek_users', 'sek_associations.association_admin_id', '=', 'sek_users.id')
                ->leftJoin('sek_addresses', 'sek_users.id', '=', 'sek_addresses.user_id')
                ->leftJoin('sek_user_bank_information', 'sek_users.id', '=', 'sek_user_bank_information.user_id')
                ->select(
                    'sek_associations.*',
                    'sek_users.entered_association_owner_fullname',
                    'sek_users.mobile_number',
                    'sek_users.email',
                    'sek_addresses.city_ar',
                    'sek_user_bank_information.iban'
                )
                ->limit($limit)
                ->get();

            $this->info("\n📝 Sample association data with joins:");
            $headers = ['ID', 'Name AR', 'Name EN', 'License', 'Admin Name', 'Phone', 'Email', 'City'];
            $rows = [];

            foreach ($samples as $assoc) {
                $rows[] = [
                    $assoc->id,
                    $assoc->name_ar ?? 'NULL',
                    $assoc->name_en ?? 'NULL',
                    $assoc->license_number ?? 'NULL',
                    $assoc->entered_association_owner_fullname ?? 'NULL',
                    $assoc->mobile_number ?? 'NULL',
                    $assoc->email ?? 'NULL',
                    $assoc->city_ar ?? 'NULL',
                ];
            }

            $this->table($headers, $rows);
        }
    }

    /**
     * Validate users table structure and data
     */
    protected function validateUsersTable($oldDb, int $limit): void
    {
        $this->info("\n👤 Validating users table...");

        // Check if table exists
        if (! $oldDb->getSchemaBuilder()->hasTable('sek_users')) {
            $this->error('❌ sek_users table not found');

            return;
        }

        // Get table structure
        $columns = $oldDb->getSchemaBuilder()->getColumnListing('sek_users');
        $this->info('📋 Available columns: '.implode(', ', $columns));

        // Check for expected columns
        $expectedColumns = ['id', 'email', 'mobile_number', 'association_id'];
        $missingColumns = array_diff($expectedColumns, $columns);

        if (! empty($missingColumns)) {
            $this->warn('⚠️  Missing expected columns: '.implode(', ', $missingColumns));
        }

        // Get sample data
        $count = $oldDb->table('sek_users')->count();
        $this->info("📊 Total users: {$count}");

        if ($count > 0) {
            $samples = $oldDb->table('sek_users')->limit($limit)->get();

            $this->info("\n📝 Sample user data:");
            $headers = ['ID', 'Email', 'Mobile', 'Association ID', 'Mapped Name'];
            $rows = [];

            foreach ($samples as $user) {
                // Build name like in migration
                $nameComponents = array_filter([
                    $user->first_name_en ?? $user->first_name_ar ?? null,
                    $user->second_name_en ?? $user->second_name_ar ?? null,
                    $user->third_name_en ?? $user->third_name_ar ?? null,
                    $user->last_name_en ?? $user->last_name_ar ?? null,
                ]);

                $mappedName = ! empty($nameComponents)
                    ? implode(' ', $nameComponents)
                    : ($user->full_name ?? "User {$user->id}");

                $rows[] = [
                    $user->id,
                    $user->email ?? 'NULL',
                    $user->mobile_number ?? 'NULL',
                    $user->association_id ?? 'NULL',
                    $mappedName,
                ];
            }

            $this->table($headers, $rows);

            // Show data quality statistics
            $this->showDataQualityStats($samples);
        }
    }

    /**
     * Show data quality statistics
     */
    protected function showDataQualityStats($users): void
    {
        $stats = [
            'with_email' => 0,
            'with_mobile' => 0,
            'with_both' => 0,
            'with_neither' => 0,
            'with_association' => 0,
            'email_verified' => 0,
            'mobile_verified' => 0,
        ];

        foreach ($users as $user) {
            $hasEmail = ! empty($user->email);
            $hasMobile = ! empty($user->mobile_number);

            if ($hasEmail) {
                $stats['with_email']++;
            }
            if ($hasMobile) {
                $stats['with_mobile']++;
            }
            if ($hasEmail && $hasMobile) {
                $stats['with_both']++;
            }
            if (! $hasEmail && ! $hasMobile) {
                $stats['with_neither']++;
            }
            if (! empty($user->association_id)) {
                $stats['with_association']++;
            }
            if (! empty($user->email_is_verified) && $user->email_is_verified == 1) {
                $stats['email_verified']++;
            }
            if (! empty($user->mobile_number_is_verified) && $user->mobile_number_is_verified == 1) {
                $stats['mobile_verified']++;
            }
        }

        $this->info("\n📈 Data Quality Statistics (sample):");
        $this->info("• Users with email: {$stats['with_email']}");
        $this->info("• Users with mobile: {$stats['with_mobile']}");
        $this->info("• Users with both: {$stats['with_both']}");
        $this->info("• Users with neither: {$stats['with_neither']}");
        $this->info("• Users with association: {$stats['with_association']}");
        $this->info("• Email verified: {$stats['email_verified']}");
        $this->info("• Mobile verified: {$stats['mobile_verified']}");

        if ($stats['with_neither'] > 0) {
            $this->warn("⚠️  {$stats['with_neither']} users have no email or mobile - these will be skipped");
        }
    }
}
