const db = require('./config/database');
const fs = require('fs');

async function runMigration() {
  try {
    console.log('🔍 Checking current database schema...\n');
    
    // Check device_subscriptions table
    console.log('📋 device_subscriptions table:');
    const [subColumns] = await db.execute('DESCRIBE device_subscriptions');
    const existingSubColumns = subColumns.map(col => col.Field);
    console.log('Existing columns:', existingSubColumns.join(', '));
    
    // Check payment_transactions table
    console.log('\n📋 payment_transactions table:');
    const [txnColumns] = await db.execute('DESCRIBE payment_transactions');
    const existingTxnColumns = txnColumns.map(col => col.Field);
    console.log('Existing columns:', existingTxnColumns.join(', '));
    
    console.log('\n🚀 Running database migration...\n');
    
    // Add columns to device_subscriptions if they don't exist
    const subColumnsToAdd = [
      { name: 'razorpay_payment_id', sql: 'ADD COLUMN razorpay_payment_id VARCHAR(255) NULL' },
      { name: 'razorpay_order_id', sql: 'ADD COLUMN razorpay_order_id VARCHAR(255) NULL' },
      { name: 'start_date', sql: 'ADD COLUMN start_date TIMESTAMP NULL' },
      { name: 'updated_at', sql: 'ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' }
    ];
    
    for (const col of subColumnsToAdd) {
      if (!existingSubColumns.includes(col.name)) {
        console.log(`✅ Adding ${col.name} to device_subscriptions...`);
        await db.execute(`ALTER TABLE device_subscriptions ${col.sql}`);
      } else {
        console.log(`⏭️  ${col.name} already exists in device_subscriptions`);
      }
    }
    
    // Add columns to payment_transactions if they don't exist
    const txnColumnsToAdd = [
      { name: 'gateway_response', sql: 'ADD COLUMN gateway_response JSON NULL' },
      { name: 'processed_at', sql: 'ADD COLUMN processed_at TIMESTAMP NULL' },
      { name: 'metadata', sql: 'ADD COLUMN metadata JSON NULL' }
    ];
    
    for (const col of txnColumnsToAdd) {
      if (!existingTxnColumns.includes(col.name)) {
        console.log(`✅ Adding ${col.name} to payment_transactions...`);
        await db.execute(`ALTER TABLE payment_transactions ${col.sql}`);
      } else {
        console.log(`⏭️  ${col.name} already exists in payment_transactions`);
      }
    }
    
    console.log('\n✨ Migration completed successfully!\n');
    
    // Show final schema
    console.log('📊 Final device_subscriptions schema:');
    const [finalSubColumns] = await db.execute('DESCRIBE device_subscriptions');
    finalSubColumns.forEach(col => {
      const marker = subColumnsToAdd.some(c => c.name === col.Field) ? '🆕' : '  ';
      console.log(`${marker} ${col.Field} (${col.Type})`);
    });
    
    console.log('\n📊 Final payment_transactions schema:');
    const [finalTxnColumns] = await db.execute('DESCRIBE payment_transactions');
    finalTxnColumns.forEach(col => {
      const marker = txnColumnsToAdd.some(c => c.name === col.Field) ? '🆕' : '  ';
      console.log(`${marker} ${col.Field} (${col.Type})`);
    });
    
    console.log('\n✅ All database migrations completed successfully!');
    console.log('🎉 Backend is ready for payment verification and subscription activation!');
    
    process.exit(0);
  } catch (error) {
    console.error('❌ Migration error:', error.message);
    console.error(error);
    process.exit(1);
  }
}

runMigration();
