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

const migrationsDir = path.join(__dirname);

async function runMigrations() {
  console.log('🚀 Starting Git Integration database migration...');

  try {
    // Connect to database
    await database.testConnection();
    console.log('✅ Database connected successfully');

    // Get list of migration files
    const migrationFiles = fs.readdirSync(migrationsDir)
      .filter(file => file.endsWith('.sql'))
      .sort();

    console.log(`📄 Found ${migrationFiles.length} migration files:`, migrationFiles);

    for (const migrationFile of migrationFiles) {
      console.log(`🚀 Running migration: ${migrationFile}`);

      const migrationPath = path.join(migrationsDir, migrationFile);
      const migrationSQL = fs.readFileSync(migrationPath, 'utf8');

      try {
        await database.query(migrationSQL);
        console.log(`✅ Migration ${migrationFile} completed successfully!`);
      } catch (err) {
        const message = (err && err.message) ? err.message.toLowerCase() : '';
        const code = err && err.code ? err.code : '';
        // Continue on idempotency-safe errors (objects already exist)
        const isIdempotentError =
          message.includes('already exists') ||
          code === '42710' /* duplicate_object */ ||
          code === '42P07' /* duplicate_table */ ||
          code === '42701' /* duplicate_column */ ||
          code === '42P06' /* duplicate_schema */ ||
          code === '42723' /* duplicate_function */;

        if (isIdempotentError) {
          console.warn(`⚠️  Skipping idempotent error for ${migrationFile}:`, err.message);
          continue;
        }

        throw err; // rethrow non-idempotent errors
      }
    }

    // Verify tables were created
    const tablesQuery = `
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' 
      AND table_name NOT LIKE 'pg_%'
      ORDER BY table_name
    `;

    const tablesResult = await database.query(tablesQuery);
    const tableNames = tablesResult.rows.map(row => row.table_name);

    console.log('🔍 Verified tables:', tableNames);
    console.log('🎉 All migrations completed successfully!');
    
  } catch (error) {
    console.error('❌ Migration failed:', error);
    process.exit(1);
  } finally {
    await database.close();
    console.log('🔌 Database connection closed');
  }
}

// Run migrations if this file is executed directly
if (require.main === module) {
  runMigrations();
}

module.exports = { runMigrations };
