/**
 * Test Deposit Database Structure
 * Run with: node test-deposit-db.js
 */

require('dotenv').config();
const mysql = require('mysql2/promise');

async function testDepositDatabase() {
    console.log('\n🔍 Testing Deposit Database Structure\n');
    console.log('='.repeat(60));

    let connection;

    try {
        // Connect to database
        connection = await mysql.createConnection({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_NAME,
            port: process.env.DB_PORT
        });

        console.log('✅ Connected to database\n');

        // Check deposits table structure
        console.log('📋 Checking deposits table structure:\n');
        const [columns] = await connection.execute(`
            DESCRIBE deposits
        `);

        console.log('Columns in deposits table:');
        console.table(columns.map(c => ({
            Field: c.Field,
            Type: c.Type,
            Null: c.Null,
            Key: c.Key,
            Default: c.Default
        })));

        // Check required columns exist
        const requiredColumns = [
            'id', 'user_id', 'payment_method_id', 'reference',
            'amount', 'fee', 'amount_credited', 'status', 'currency',
            'expires_at', 'created_at', 'updated_at'
        ];

        const existingColumns = columns.map(c => c.Field);
        const missingColumns = requiredColumns.filter(col => !existingColumns.includes(col));

        console.log('\n✅ Column Check:');
        if (missingColumns.length === 0) {
            console.log('   All required columns exist!');
        } else {
            console.log('   ❌ Missing columns:', missingColumns.join(', '));
            console.log('\n   Run migration:');
            console.log('   node scripts/run-migrations.js');
        }

        // Test deposit insert
        console.log('\n🔄 Testing deposit insert:\n');

        try {
            const [testInsert] = await connection.execute(`
                INSERT INTO deposits (
                    user_id,
                    payment_method_id,
                    reference,
                    amount,
                    fee,
                    amount_credited,
                    status,
                    currency,
                    expires_at,
                    created_at,
                    updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, 'pending', 'NGN', DATE_ADD(NOW(), INTERVAL 30 MINUTE), NOW(), NOW())
            `, [1, 9, `TEST_${Date.now()}`, 1000, 15, 985]);

            const testDepositId = testInsert.insertId;
            console.log('✅ Test deposit created successfully!');
            console.log('   Deposit ID:', testDepositId);

            // Clean up test deposit
            await connection.execute('DELETE FROM deposits WHERE id = ?', [testDepositId]);
            console.log('✅ Test deposit cleaned up\n');

        } catch (insertError) {
            console.error('❌ Deposit insert failed:', insertError.message);
            console.log('\nError details:', insertError.sqlMessage || insertError.message);

            if (insertError.code === 'ER_NO_SUCH_TABLE') {
                console.log('\n⚠️  deposits table does not exist!');
                console.log('   Run: node scripts/run-migrations.js');
            } else if (insertError.code === 'ER_BAD_FIELD_ERROR') {
                console.log('\n⚠️  Column missing in deposits table!');
                console.log('   Run migration: database/migrations/009_update_deposits_table.sql');
            }
        }

        // Check payment methods
        console.log('📋 Checking payment methods:\n');
        const [methods] = await connection.execute(`
            SELECT id, name, code, is_active
            FROM payment_methods
            WHERE code IN ('korapay', 'cryptomus')
        `);

        console.table(methods);

        if (methods.length === 0) {
            console.log('❌ No payment methods found!');
            console.log('   Run: node scripts/add-payment-methods.js\n');
        } else {
            console.log(`✅ ${methods.length} payment methods configured\n`);
        }

        // Check users table
        console.log('📋 Checking users table:\n');
        const [users] = await connection.execute(`
            SELECT id, email, first_name, last_name, mobile_number, balance
            FROM users
            LIMIT 1
        `);

        if (users.length > 0) {
            console.log('✅ Sample user exists:');
            console.table(users);
        } else {
            console.log('⚠️  No users found in database');
        }

        console.log('\n' + '='.repeat(60));
        console.log('✅ Database check complete!\n');

    } catch (error) {
        console.error('\n❌ Database error:', error.message);

        if (error.code === 'ECONNREFUSED') {
            console.log('\n⚠️  Cannot connect to MySQL server');
            console.log('   - Check XAMPP is running');
            console.log('   - Verify DB_HOST in .env (currently:', process.env.DB_HOST + ')');
        } else if (error.code === 'ER_BAD_DB_ERROR') {
            console.log('\n⚠️  Database does not exist');
            console.log('   - Create database: wakatraffic');
            console.log('   - Run migrations');
        }

        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// Run test
testDepositDatabase();
