#!/usr/bin/env node

/**
 * Payment System Migration Runner
 * Executes database migrations for new payment integration
 *
 * Usage: node run-payment-migrations.js
 */

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

const config = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'wakatraffic',
  multipleStatements: true
};

const migrations = [
  '008_create_payment_methods_table.sql',
  '009_update_deposits_table.sql',
  '010_create_payment_webhooks_table.sql'
];

const seeders = [
  'payment_methods_seeder.sql'
];

async function runMigrations() {
  let connection;

  try {
    console.log('='.repeat(60));
    console.log('PAYMENT SYSTEM DATABASE MIGRATION');
    console.log('='.repeat(60));
    console.log(`\nConnecting to database: ${config.database}@${config.host}\n`);

    // Create connection
    connection = await mysql.createConnection(config);
    console.log('✅ Database connection established\n');

    // Run migrations
    console.log('Running migrations...\n');

    for (let i = 0; i < migrations.length; i++) {
      const migrationFile = migrations[i];
      const migrationPath = path.join(__dirname, 'database', 'migrations', migrationFile);

      try {
        console.log(`[${i + 1}/${migrations.length}] Executing: ${migrationFile}`);

        const sql = await fs.readFile(migrationPath, 'utf8');
        await connection.query(sql);

        console.log(`✅ ${migrationFile} - SUCCESS\n`);
      } catch (error) {
        console.error(`❌ ${migrationFile} - FAILED`);
        console.error(`   Error: ${error.message}\n`);

        // Ask if user wants to continue
        if (error.code !== 'ER_DUP_FIELDNAME' && error.code !== 'ER_TABLE_EXISTS_ERROR') {
          throw error;
        } else {
          console.log(`   ⚠️  Skipping (table/column already exists)\n`);
        }
      }
    }

    console.log('All migrations completed!\n');

    // Run seeders
    console.log('Running seeders...\n');

    for (let i = 0; i < seeders.length; i++) {
      const seederFile = seeders[i];
      const seederPath = path.join(__dirname, 'database', 'seeders', seederFile);

      try {
        console.log(`[${i + 1}/${seeders.length}] Executing: ${seederFile}`);

        const sql = await fs.readFile(seederPath, 'utf8');
        const [rows] = await connection.query(sql);

        console.log(`✅ ${seederFile} - SUCCESS\n`);

        // Display seeded data
        if (rows && rows.length > 0 && Array.isArray(rows[rows.length - 1])) {
          console.log('   Seeded Payment Methods:');
          const results = rows[rows.length - 1];
          results.forEach(row => {
            console.log(`   - ${row.name} (${row.code}) - ${row.status} - ${row.mode}`);
          });
          console.log('');
        }
      } catch (error) {
        console.error(`❌ ${seederFile} - FAILED`);
        console.error(`   Error: ${error.message}\n`);

        if (error.code === 'ER_DUP_ENTRY') {
          console.log(`   ⚠️  Skipping (data already exists)\n`);
        } else {
          throw error;
        }
      }
    }

    console.log('All seeders completed!\n');

    // Verify installation
    console.log('Verifying installation...\n');

    // Check payment_methods table
    const [methods] = await connection.query('SELECT COUNT(*) as count FROM payment_methods');
    console.log(`✅ payment_methods table: ${methods[0].count} payment methods configured`);

    // Check deposits table structure
    const [deposits] = await connection.query(`
      SELECT COUNT(*) as count
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = ?
      AND TABLE_NAME = 'deposits'
      AND COLUMN_NAME = 'payment_method_id'
    `, [config.database]);
    console.log(`✅ deposits table: ${deposits[0].count > 0 ? 'Updated successfully' : 'ERROR - Not updated'}`);

    // Check payment_webhooks table
    const [webhooks] = await connection.query(`
      SELECT COUNT(*) as count
      FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = ?
      AND TABLE_NAME = 'payment_webhooks'
    `, [config.database]);
    console.log(`✅ payment_webhooks table: ${webhooks[0].count > 0 ? 'Created successfully' : 'ERROR - Not created'}`);

    console.log('\n' + '='.repeat(60));
    console.log('✅ PHASE 1 COMPLETE: DATABASE SETUP SUCCESSFUL');
    console.log('='.repeat(60));
    console.log('\nNext Steps:');
    console.log('1. ✅ Database tables created and configured');
    console.log('2. ✅ Payment methods seeded');
    console.log('3. ⏳ Ready for Phase 2: Payment Service Classes');
    console.log('\nRun: Tell Claude "Start Phase 2: Payment Services"\n');

  } catch (error) {
    console.error('\n❌ MIGRATION FAILED\n');
    console.error('Error:', error.message);
    console.error('\nPlease fix the error and try again.\n');
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Database connection closed.\n');
    }
  }
}

// Run migrations
runMigrations().catch(error => {
  console.error('Fatal error:', error);
  process.exit(1);
});
