package com.social.media.infrastructure.database;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * Database migration utility to fix authentication fields
 * 
 * Run with: java -jar app.jar --spring.profiles.active=migrate
 */
@Component
@Profile("migrate")
public class AuthenticationFieldsMigration implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) throws Exception {
        System.out.println("=== Starting Authentication Fields Migration ===");
        
        try {
            // Add missing columns
            System.out.println("Adding failed_login_attempts column...");
            jdbcTemplate.execute("ALTER TABLE core_business.users ADD COLUMN IF NOT EXISTS failed_login_attempts INTEGER NOT NULL DEFAULT 0");
            
            System.out.println("Adding locked_until column...");
            jdbcTemplate.execute("ALTER TABLE core_business.users ADD COLUMN IF NOT EXISTS locked_until TIMESTAMP");
            
            System.out.println("Adding last_login_at column...");
            jdbcTemplate.execute("ALTER TABLE core_business.users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP");
            
            System.out.println("Adding password_reset_token column...");
            jdbcTemplate.execute("ALTER TABLE core_business.users ADD COLUMN IF NOT EXISTS password_reset_token VARCHAR(255)");
            
            System.out.println("Adding password_reset_expires_at column...");
            jdbcTemplate.execute("ALTER TABLE core_business.users ADD COLUMN IF NOT EXISTS password_reset_expires_at TIMESTAMP");
            
            System.out.println("Adding email_verification_token column...");
            jdbcTemplate.execute("ALTER TABLE core_business.users ADD COLUMN IF NOT EXISTS email_verification_token VARCHAR(255)");
            
            // Add constraints
            System.out.println("Adding constraints...");
            try {
                jdbcTemplate.execute("ALTER TABLE core_business.users ADD CONSTRAINT chk_users_failed_login_attempts_non_negative CHECK (failed_login_attempts >= 0)");
            } catch (Exception e) {
                System.out.println("Constraint might already exist: " + e.getMessage());
            }
            
            // Add indexes
            System.out.println("Adding indexes...");
            try {
                jdbcTemplate.execute("CREATE INDEX IF NOT EXISTS idx_users_failed_login_attempts ON core_business.users(failed_login_attempts)");
                jdbcTemplate.execute("CREATE INDEX IF NOT EXISTS idx_users_locked_until ON core_business.users(locked_until) WHERE locked_until IS NOT NULL");
                jdbcTemplate.execute("CREATE INDEX IF NOT EXISTS idx_users_last_login_at ON core_business.users(last_login_at)");
                jdbcTemplate.execute("CREATE INDEX IF NOT EXISTS idx_users_password_reset_token ON core_business.users(password_reset_token) WHERE password_reset_token IS NOT NULL");
                jdbcTemplate.execute("CREATE INDEX IF NOT EXISTS idx_users_email_verification_token ON core_business.users(email_verification_token) WHERE email_verification_token IS NOT NULL");
            } catch (Exception e) {
                System.out.println("Index creation note: " + e.getMessage());
            }
            
            // Verify the changes
            System.out.println("Verifying changes...");
            Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'core_business' AND table_name = 'users' AND column_name IN ('failed_login_attempts', 'locked_until', 'last_login_at', 'password_reset_token', 'password_reset_expires_at', 'email_verification_token')",
                Integer.class
            );
            
            System.out.println("✅ Migration completed successfully!");
            System.out.println("Found " + count + " authentication columns (expected: 6)");
            
            if (count == 6) {
                System.out.println("🎉 All required columns are now present!");
            } else {
                System.out.println("⚠️ Some columns may be missing. Please check manually.");
            }
            
            System.out.println("\n📋 Next steps:");
            System.out.println("1. Update UserEntity.java column mappings:");
            System.out.println("   - @Column(name = \"name\")          // was \"full_name\"");
            System.out.println("   - @Column(name = \"email_verified\") // was \"is_email_verified\"");
            System.out.println("   - @Column(name = \"type\")           // was \"user_type\"");
            System.out.println("2. Restart the application with normal profile");
            
        } catch (Exception e) {
            System.err.println("❌ Migration failed: " + e.getMessage());
            e.printStackTrace();
            System.exit(1);
        }
        
        System.out.println("=== Migration process completed ===");
        System.exit(0);
    }
}