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 'template-manager'
    )
  `);
}

async function isMigrationApplied(version) {
  const result = await database.query(
    'SELECT version FROM schema_migrations WHERE version = $1 AND service = $2',
    [version, 'template-manager']
  );
  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, 'template-manager']
  );
}

async function runMigrations() {
  console.log('🚀 Starting template-manager database migrations...');

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

    // Get all migration files in order
    // Reordered to ensure custom_templates table exists before admin_approval_workflow
    const migrationFiles = [
      '001_initial_schema.sql',
      '003_custom_templates.sql',           // Moved earlier since others depend on it
      '002_admin_approval_workflow.sql',    // Now runs after custom_templates is created
      '004_add_is_custom_flag.sql',
      '004_add_user_id_to_custom_templates.sql',
      '005_fix_custom_features_foreign_key.sql',
      // Intentionally skip feature_rules migrations per updated design
      '008_feature_business_rules.sql'
    ];

    let appliedCount = 0;
    let skippedCount = 0;

    for (const migrationFile of migrationFiles) {
      const migrationPath = path.join(__dirname, migrationFile);

      // Check if migration file exists
      if (!fs.existsSync(migrationPath)) {
        console.log(`⚠️ Migration file not found: ${migrationFile}`);
        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');

      // Skip destructive migrations unless explicitly allowed
      const containsDrop = /\bdrop\s+table\b/i.test(migrationSQL);
      const allowDestructive = String(process.env.ALLOW_DESTRUCTIVE_MIGRATIONS || '').toLowerCase() === 'true';
      if (containsDrop && !allowDestructive) {
        console.log(`⏭️  Skipping potentially destructive migration (set ALLOW_DESTRUCTIVE_MIGRATIONS=true to run): ${migrationFile}`);
        skippedCount++;
        continue;
      }

      console.log(`📄 Running migration: ${migrationFile}`);

      // Execute the migration
      await database.query(migrationSQL);
      await markMigrationApplied(migrationFile);

      console.log(`✅ Migration ${migrationFile} completed!`);
      appliedCount++;
    }

    console.log(`📊 Migration summary: ${appliedCount} applied, ${skippedCount} skipped`);

    // Verify tables were created
    const result = await database.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' 
      AND table_name IN ('templates', 'template_features', 'feature_business_rules', 'feature_usage', 'custom_features', 'custom_templates', 'feature_synonyms', 'admin_notifications')
      ORDER BY table_name
    `);

    console.log('🔍 Verified tables:', result.rows.map(row => row.table_name));

  } 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 };