package flyway_package;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.MigrationInfo;
import org.flywaydb.core.api.MigrationInfoService;

import main_package.DataBaseConnection;

public class FlywayMigrationListener implements ServletContextListener 
{
	//Devloped By Taheer
    private static final String SKIPPED_TABLE = "flyway_skipped_migrations";
    
    
    
    @Override
    public void contextInitialized(ServletContextEvent event) 
    {
        System.out.println("==============================================");
        System.out.println("Starting Flyway Database Migration");
        System.out.println("==============================================");
        
        Flyway flyway = null;

        try 
        {
            try 
            {
                Connection connection = DataBaseConnection.JAVAConnection();

                if (connection != null) 
                {
                    try 
                    {
                        createSkippedMigrationTable(connection);
                    } 
                    finally 
                    {
                        connection.close();
                    }
                } 
                else 
                {
                    System.err.println("Could not connect to database to create " + SKIPPED_TABLE);
                }
            } 
            catch (Exception e) 
            {
                System.err.println("Could not create skipped migration table.");
                e.printStackTrace();
            }
            
            
            
            flyway = Flyway.configure().dataSource(DataBaseConnection.getDatabaseUrl(),
						                           DataBaseConnection.getUsername(),
						                           DataBaseConnection.getPassword())
					                   .locations("classpath:db/migration")
					                   .baselineOnMigrate(true)
					                   .load();

            try 
            {
                markPreviouslySkippedMigrationsAsHandled(flyway);
            } 
            catch (Exception e) 
            {
                System.err.println("Could not process previously skipped migrations.");
                e.printStackTrace();
            }

            int maxAttempts = 7;
            int attempt = 0;

            while (attempt < maxAttempts)
            {
                attempt++;

                try
                {
                    System.out.println("----------------------------------------------");
                    System.out.println("Flyway migration attempt: " + attempt);
                    System.out.println("----------------------------------------------");

                    flyway.migrate();

                    System.out.println("----------------------------------------------");
                    System.out.println("Flyway migration completed successfully.");
                    System.out.println("----------------------------------------------");

                    break;
                }
                catch (Exception migrationException)
                {
                    System.err.println("----------------------------------------------");
                    System.err.println("Flyway migration error on attempt: " + attempt);
                    System.err.println("----------------------------------------------");

                    handleFlywayError(flyway, migrationException);

                    System.out.println("----------------------------------------------");
                    System.out.println("Migration error handled/skipped.");
                    System.out.println("Trying remaining migrations...");
                    System.out.println("----------------------------------------------");
                }
            }

            if (attempt >= maxAttempts)
            {
                System.err.println("----------------------------------------------");
                System.err.println("Maximum Flyway migration attempts reached.");
                System.err.println("Application will continue startup.");
                System.err.println("----------------------------------------------");
            }
        } 
        catch (Exception e) 
        {
            System.err.println("==============================================");
            System.err.println("Unexpected Flyway listener error.");
            System.err.println("Application will continue startup.");
            System.err.println("==============================================");

            e.printStackTrace();
        }

        System.out.println("==============================================");
        System.out.println("Flyway Listener Finished");
        System.out.println("Application startup will continue.");
        System.out.println("==============================================");
    }
    
    
    
    private void handleFlywayError(Flyway flyway, Exception migrationException) 
    {
        System.err.println("----------------------------------------------");
        System.err.println("Flyway migration/validation error occurred.");
        System.err.println("----------------------------------------------");

        migrationException.printStackTrace();

        MigrationInfo migration = findMigrationRelatedToError(flyway, migrationException);

        String version = "";
        String description = "";
        String script = "";

        if (migration != null) 
        {
            if (migration.getVersion() != null) 
            {
                version = migration.getVersion().toString();
            }

            description = migration.getDescription();
            script = migration.getScript();
        }
        else 
        {
            version = "UNKNOWN";
            description = "Unknown migration";
            script = "UNKNOWN";
        }

        String errorMessage = getFullErrorMessage(migrationException);

        System.err.println("----------------------------------------------");
        System.err.println("Migration Error Details");
        System.err.println("Version     : " + version);
        System.err.println("Description : " + description);
        System.err.println("Script      : " + script);
        System.err.println("----------------------------------------------");

        try 
        {
            saveSkippedMigration(version, description, script, errorMessage);
            
            System.out.println("Flyway error saved in " + SKIPPED_TABLE);
        } 
        catch (Exception saveException) 
        {
            System.err.println("Could not save Flyway error in database.");
            
            saveException.printStackTrace();
        }

        try 
        {
            flyway.repair();
            
            System.out.println("Flyway repair completed.");
        } 
        catch (Exception repairException) 
        {
            System.err.println("Flyway repair failed.");
            
            repairException.printStackTrace();
        }

        if (migration != null) 
        {
            try 
            {
                markMigrationAsHandled(migration);

                System.out.println("----------------------------------------------");
                System.out.println("Migration marked as handled/skipped.");
                System.out.println("Version : " + version);
                System.out.println("Script  : " + script);
                System.out.println("----------------------------------------------");
            } 
            catch (Exception historyException) 
            {
                System.err.println("Could not mark migration as handled.");
                historyException.printStackTrace();
            }
        }
    }
    
    
    
    private void createSkippedMigrationTable(Connection connection) throws Exception 
    {
        Statement statement = null;

        try 
        {
            statement = connection.createStatement();

            String sql = "CREATE TABLE IF NOT EXISTS "
	                   + SKIPPED_TABLE
	                   + " ("
	                   + "id INT AUTO_INCREMENT PRIMARY KEY, "
	                   + "version VARCHAR(50) NOT NULL, "
	                   + "description VARCHAR(255), "
	                   + "script VARCHAR(255) NOT NULL, "
	                   + "error_message TEXT, "
	                   + "skipped_at TIMESTAMP "
	                   + "DEFAULT CURRENT_TIMESTAMP, "
	                   + "UNIQUE KEY uk_skipped_migration "
	                   + "(version, script)"
	                   + ")";

            statement.executeUpdate(sql);
        } 
        finally 
        {
            if (statement != null) 
            {
                try 
                {
                    statement.close();
                } 
                catch (Exception e) 
                {
                    e.printStackTrace();
                }
            }
        }
    }


    
    private void markPreviouslySkippedMigrationsAsHandled(Flyway flyway) throws Exception 
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try 
        {
            connection = DataBaseConnection.JAVAConnection();

            if (connection == null) 
            {
                System.err.println("Database connection is null.");
                return;
            }

            String sql = "SELECT version, script FROM " + SKIPPED_TABLE;

            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) 
            {
                String version = rs.getString("version");
                String script = rs.getString("script");

                MigrationInfo migration = findMigration(flyway, version, script);

                if (migration != null) 
                {
                    System.out.println("Previously skipped migration found: " + script);

                    insertIntoFlywayHistory(connection, migration);
                }
            }
        } 
        finally 
        {
            if (rs != null)
            {
                try 
                {
                    rs.close();
                } 
                catch (Exception e) 
                {
                    e.printStackTrace();
                }
            }

            if (ps != null) 
            {
                try 
                {
                    ps.close();
                } 
                catch (Exception e)
                {
                    e.printStackTrace();
                }
            }

            if (connection != null) 
            {
                try 
                {
                    connection.close();
                } 
                catch (Exception e) 
                {
                    e.printStackTrace();
                }
            }
        }
    }
    
    
    
    private MigrationInfo findMigrationRelatedToError(Flyway flyway, Exception exception) 
    {
        MigrationInfoService info = flyway.info();
        MigrationInfo[] migrations = info.all();

        if (migrations == null) 
        {
            return null;
        }

        for (MigrationInfo migration : migrations)
        {
            if (migration == null) 
            {
                continue;
            }

            String state = migration.getState() != null
                         ? migration.getState().getDisplayName() : "";

            if ("Failed".equalsIgnoreCase(state)) 
            {
                return migration;
            }
        }

        String error = getFullErrorMessage(exception).toLowerCase();

        for (MigrationInfo migration : migrations) 
        {
            if (migration == null) 
            {
                continue;
            }

            if (migration.getVersion() == null) 
            {
                continue;
            }

            String version = migration.getVersion().toString();

            if (error.contains("version " + version)) 
            {
                return migration;
            }
        }

        for (MigrationInfo migration : migrations) 
        {
            if (migration == null) 
            {
                continue;
            }

            String state = migration.getState() != null
                         ? migration.getState().getDisplayName() : "";

            if ("Pending".equalsIgnoreCase(state)) 
            {
                return migration;
            }
        }

        return null;
    }



    private MigrationInfo findMigration(Flyway flyway, String version, String script) 
    {
        MigrationInfoService info = flyway.info();
        MigrationInfo[] migrations = info.all();

        if (migrations == null) 
        {
            return null;
        }

        for (MigrationInfo migration : migrations) 
        {
            if (migration == null) 
            {
                continue;
            }

            if (migration.getVersion() == null) 
            {
                continue;
            }

            String migrationVersion = migration.getVersion().toString();
            String migrationScript = migration.getScript();

            if (version.equals(migrationVersion) && script.equals(migrationScript)) 
            {
                return migration;
            }
        }
        
        return null;
    }
    
    
    
    private void saveSkippedMigration(String version, String description, String script, String errorMessage) throws Exception 
    {
        Connection connection = null;
        PreparedStatement ps = null;

        try 
        {
            connection = DataBaseConnection.JAVAConnection();

            if (connection == null) 
            {
                throw new Exception("Database connection is null.");
            }

            String sql = "INSERT INTO "
	                   + SKIPPED_TABLE
	                   + " ("
	                   + "version, "
	                   + "description, "
	                   + "script, "
	                   + "error_message"
	                   + ") "
	                   + "VALUES (?, ?, ?, ?) "
	                   + "ON DUPLICATE KEY UPDATE "
	                   + "description = VALUES(description), "
	                   + "error_message = VALUES(error_message), "
	                   + "skipped_at = CURRENT_TIMESTAMP";

            ps = connection.prepareStatement(sql);

            ps.setString(1, version);
            ps.setString(2, description);
            ps.setString(3, script);
            ps.setString(4, errorMessage);

            ps.executeUpdate();
        } 
        finally 
        {
            if (ps != null) 
            {
                try 
                {
                    ps.close();
                }
                catch (Exception e) 
                {
                    e.printStackTrace();
                }
            }

            if (connection != null) 
            {
                try 
                {
                    connection.close();
                } 
                catch (Exception e) 
                {
                    e.printStackTrace();
                }
            }
        }
    }


    
    private void markMigrationAsHandled(MigrationInfo migration) throws Exception 
    {
        Connection connection = null;

        try 
        {
            connection = DataBaseConnection.JAVAConnection();

            if (connection == null) 
            {
                throw new Exception("Database connection is null.");
            }

            insertIntoFlywayHistory(connection, migration);
        }
        finally 
        {
            if (connection != null) 
            {
                try 
                {
                    connection.close();
                } 
                catch (Exception e)
                {
                    e.printStackTrace();
                }
            }
        }
    }


    
    private void insertIntoFlywayHistory(Connection connection, MigrationInfo migration) throws Exception 
    {
        PreparedStatement checkPs = null;
        PreparedStatement insertPs = null;
        ResultSet rs = null;

        try 
        {
            String version = migration.getVersion() != null
                           ? migration.getVersion().toString() : null;
            String description = migration.getDescription();
            String script = migration.getScript();
            Integer checksum = migration.getChecksum();

            String checkSql = "SELECT COUNT(*) "
		                    + "FROM flyway_schema_history "
		                    + "WHERE version = ? "
		                    + "AND script = ?";

            checkPs = connection.prepareStatement(checkSql);
            
            checkPs.setString(1, version);
            checkPs.setString(2, script);

            rs = checkPs.executeQuery();

            boolean exists = false;

            if (rs.next()) 
            {
                exists = rs.getInt(1) > 0;
            }

            rs.close();
            rs = null;

            checkPs.close();
            checkPs = null;

            if (exists) 
            {
                System.out.println("Flyway history already contains: " + script);
                return;
            }

            int installedRank = 1;

            Statement rankStatement = connection.createStatement();

            ResultSet rankResult = rankStatement.executeQuery("SELECT COALESCE("
								                            + "MAX(installed_rank), 0"
								                            + ") + 1 "
								                            + "FROM flyway_schema_history");

            if (rankResult.next()) 
            {
                installedRank = rankResult.getInt(1);
            }

            rankResult.close();
            rankStatement.close();

            String insertSql = "INSERT INTO flyway_schema_history "
		                     + "("
		                     + "installed_rank, "
		                     + "version, "
		                     + "description, "
		                     + "type, "
		                     + "script, "
		                     + "checksum, "
		                     + "installed_by, "
		                     + "installed_on, "
		                     + "execution_time, "
		                     + "success"
		                     + ") "
		                     + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            insertPs = connection.prepareStatement(insertSql);

            insertPs.setInt(1, installedRank);
            insertPs.setString(2, version);
            insertPs.setString(3, description);
            insertPs.setString(4, "SQL");
            insertPs.setString(5, script);

            if (checksum != null) 
            {
                insertPs.setInt(6, checksum);
            } 
            else 
            {
                insertPs.setNull(6, java.sql.Types.INTEGER);
            }

            insertPs.setString(7, DataBaseConnection.getUsername());
            insertPs.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
            insertPs.setInt(9, 0);
            insertPs.setBoolean(10, true);

            insertPs.executeUpdate();

            System.out.println("Inserted handled migration into flyway_schema_history: " + script);
        } 
        finally 
        {
            if (rs != null) 
            {
                try 
                {
                    rs.close();
                } 
                catch (Exception e) 
                {
                    e.printStackTrace();
                }
            }

            if (checkPs != null) 
            {
                try 
                {
                    checkPs.close();
                } 
                catch (Exception e) 
                {
                    e.printStackTrace();
                }
            }

            if (insertPs != null) 
            {
                try 
                {
                    insertPs.close();
                } 
                catch (Exception e) 
                {
                    e.printStackTrace();
                }
            }
        }
    }


    
    private String getFullErrorMessage(Exception exception) 
    {
        StringBuilder message = new StringBuilder();
        Throwable current = exception;

        while (current != null) 
        {
            if (current.getMessage() != null) 
            {
                message.append(current.getClass().getName());
                message.append(": ");
                message.append(current.getMessage());
                message.append("\n");
            }

            current = current.getCause();
        }

        return message.toString();
    }


    @Override
    public void contextDestroyed(ServletContextEvent event) 
    {

    }
    
}