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

// async function runMigrations() {
//   console.log('🚀 Starting User Auth database migration...');
  
//   try {
//     // Read the SQL migration file
//     const migrationPath = path.join(__dirname, '001_user_auth_schema.sql');
//     const migrationSQL = fs.readFileSync(migrationPath, 'utf8');
    
//     console.log('📄 Running migration: 001_user_auth_schema.sql');
    
//     // Execute the migration
//     await database.query(migrationSQL);
    
//     console.log('✅ User Auth migration completed successfully!');
//     console.log('📊 Database schema created:');
//     console.log('   - users table (with admin and test users)');
//     console.log('   - refresh_tokens table');
//     console.log('   - user_sessions table');
//     console.log('   - user_feature_preferences table');
//     console.log('   - user_projects table');
//     console.log('   - indexes and triggers');
//     console.log('   - cleanup functions');
    
//     // Verify tables were created
//     const result = await database.query(`
//       SELECT 
//         schemaname,
//         tablename,
//         tableowner
//       FROM pg_tables 
//       WHERE schemaname = 'public' 
//       AND tablename IN ('users', 'refresh_tokens', 'user_sessions', 'user_feature_preferences', 'user_projects')
//       ORDER BY tablename
//     `);
    
//     console.log('🔍 Verified tables:');
//     result.rows.forEach(row => {
//       console.log(`   - ${row.tablename} (owner: ${row.tableowner})`);
//     });
    
//     // Test initial users
//     const userCount = await database.query('SELECT COUNT(*) as count FROM users');
//     console.log(`👥 Initial users created: ${userCount.rows[0].count}`);
    
//     console.log('🔐 Default credentials:');
//     console.log('   Admin: admin@tech4biz.com / admin123');
//     console.log('   Test:  test@tech4biz.com / admin123');
//     console.log('   ⚠️  Change passwords in production!');
    
//   } catch (error) {
//     console.error('❌ Migration failed:', error.message);
//     console.error('📍 Error details:', error);
//     process.exit(1);
//   } finally {
//     await database.close();
//   }
// }

// // Run migration if called directly
// if (require.main === module) {
//   runMigrations();
// }

// module.exports = { runMigrations };

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

async function createMigrationsTable() {
  await database.query(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version VARCHAR(255) PRIMARY KEY,
      applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      service VARCHAR(100) DEFAULT 'user-auth'
    )
  `);
}

async function isMigrationApplied(version) {
  const result = await database.query(
    'SELECT version FROM schema_migrations WHERE version = $1 AND service = $2',
    [version, 'user-auth']
  );
  return result.rows.length > 0;
}

async function markMigrationApplied(version) {
  await database.query(
    'INSERT INTO schema_migrations (version, service) VALUES ($1, $2) ON CONFLICT (version) DO NOTHING',
    [version, 'user-auth']
  );
}

async function runMigrations() {
  console.log('🚀 Starting user-auth database migrations...');
  
  const migrations = [
    '001_user_auth_schema.sql',
    '002_email_verification_schema.sql'
  ];
  
  try {
    // Ensure required extensions exist before running migrations
    console.log('🔧 Ensuring required PostgreSQL extensions...');
    await database.query('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";');
    console.log('✅ Extensions ready');

    // Create migrations tracking table
    await createMigrationsTable();
    console.log('✅ Migration tracking table ready');

    let appliedCount = 0;
    let skippedCount = 0;

    for (const migrationFile of migrations) {
      const migrationPath = path.join(__dirname, migrationFile);
      if (!fs.existsSync(migrationPath)) {
        console.warn(`⚠️ Migration file ${migrationFile} not found, skipping...`);
        continue;
      }

      // Check if migration was already applied
      if (await isMigrationApplied(migrationFile)) {
        console.log(`⏭️  Migration ${migrationFile} already applied, skipping...`);
        skippedCount++;
        continue;
      }
      
      const migrationSQL = fs.readFileSync(migrationPath, 'utf8');
      console.log(`📄 Running migration: ${migrationFile}`);
      
      await database.query(migrationSQL);
      await markMigrationApplied(migrationFile);
      console.log(`✅ Migration ${migrationFile} completed!`);
      appliedCount++;
    }
    
    console.log(`📊 Migration summary: ${appliedCount} applied, ${skippedCount} skipped`);
    
    // Verify all tables
    const result = await database.query(`
      SELECT 
        schemaname,
        tablename,
        tableowner
      FROM pg_tables 
      WHERE schemaname = 'public' 
      AND tablename IN ('users', 'refresh_tokens', 'user_sessions', 'user_feature_preferences', 'user_projects', 'email_verification_tokens')
      ORDER BY tablename
    `);
    
    console.log('🔍 Verified tables:');
    result.rows.forEach(row => {
      console.log(`   - ${row.tablename} (owner: ${row.tableowner})`);
    });
    
  } catch (error) {
    console.error('❌ Migration failed:', error.message);
    console.error('📍 Error details:', error);
    process.exit(1);
  } finally {
    await database.close();  // Close connection via Database wrapper
  }
}

// Run migration if called directly
if (require.main === module) {
  runMigrations();
}

module.exports = { runMigrations };