package flyway_package;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

import main_package.DataBaseConnection;

public class SchemaUpdateManager 
{
	//Devloped By Taheer
    private static Connection getConnection() throws SQLException 
    {
        return DriverManager.getConnection(
	           DataBaseConnection.getDatabaseUrl(),
	           DataBaseConnection.getUsername(),
	           DataBaseConnection.getPassword()
        );
    }
    
    
    
    public static void updateSchema() 
    {
        Class<?>[] entities = 
        {
            entity.Employee.class
        };

        try (Connection conn = getConnection()) 
        {
            createLogTable(conn);

            for (Class<?> entityClass : entities) 
            {
                if (!entityClass.isAnnotationPresent(Entity.class)) 
                {
                    continue;
                }

                Table tableAnnotation = entityClass.getAnnotation(Table.class);

                String tableName = tableAnnotation != null && !tableAnnotation.name().trim().isEmpty()
                				 ? tableAnnotation.name() : entityClass.getSimpleName();

                if (!isSafeIdentifier(tableName)) 
                {
                    logError(conn, entityClass.getSimpleName(),
                        tableName, null, "VALIDATION",
                        "Unsafe table identifier");
                    continue;
                }

                if (!tableExists(conn, tableName)) 
                {
                    logError(conn, entityClass.getSimpleName(),
                        tableName, null, "VALIDATION",
                        "Table not found in current database");
                    continue;
                }

                Field[] fields = entityClass.getDeclaredFields();

                for (Field field : fields) 
                {
                    if (Modifier.isStatic(field.getModifiers()) || Modifier.isTransient(field.getModifiers()) || 
                    	field.isSynthetic()) 
                    {
                        continue;
                    }

                    Column column = field.getAnnotation(Column.class);

                    if (column == null) 
                    {
                        continue;
                    }

                    String columnName = column.name().trim();

                    if (columnName.isEmpty()) 
                    {
                        columnName = field.getName();
                    }

                    if (!isSafeIdentifier(columnName)) 
                    {
                        logError(conn, entityClass.getSimpleName(),
                            tableName, columnName, "VALIDATION",
                            "Unsafe column identifier");
                        continue;
                    }

                    try 
                    {
                        if (columnExists(conn, tableName, columnName)) 
                        {
                            continue;
                        }

                        String sqlType = getSqlType(field, column);

                        if (sqlType == null) 
                        {
                            logError(conn, entityClass.getSimpleName(),
                                tableName, columnName, "SKIPPED",
                                "Unsupported Java field type: "
                                    + field.getType().getName());
                            continue;
                        }

                        String sql = "ALTER TABLE `" + tableName
		                           + "` ADD COLUMN `" + columnName
		                           + "` " + sqlType;

                        try (Statement statement = conn.createStatement()) 
                        {
                            statement.executeUpdate(sql);
                        }

                        System.out.println("Added column " + tableName + "." + columnName);
                    } 
                    catch (Exception e) 
                    {
                        logError(conn, entityClass.getSimpleName(),
                            tableName, columnName, "FAILED",
                            e.toString());

                        System.err.println(
                            "Schema update skipped for "
                                + tableName + "." + columnName);

                        e.printStackTrace();
                    }
                }
            }

        } 
        catch (Exception e) 
        {
            System.err.println("Schema manager error: " + e);
            e.printStackTrace();
        }
    }

    
    
    private static String getSqlType(Field field, Column column)
    {
        String columnDefinition = column.columnDefinition().trim();

        if (!columnDefinition.isEmpty())
        {
            if (columnDefinition.matches("(?i)TEXT|MEDIUMTEXT|LONGTEXT|TINYTEXT"))
            {
                return columnDefinition.toUpperCase();
            }

            if (columnDefinition.matches("(?i)DATE|TIME|DATETIME|TIMESTAMP|INT|INTEGER|BIGINT|SMALLINT|DOUBLE|FLOAT|BOOLEAN|TINYINT\\(1\\)"))
            {
                return columnDefinition.toUpperCase();
            }

            if (columnDefinition.matches("(?i)VARCHAR\\(\\d+\\)|CHAR\\(\\d+\\)|DECIMAL\\(\\d+(,\\s*\\d+)?\\)"))
            {
                return columnDefinition.toUpperCase();
            }

            return null;
        }

        Class<?> type = field.getType();

        if (type == String.class)
        {
            int length = column.length() > 0 ? column.length() : 255;
            return "VARCHAR(" + length + ")";
        }

        if (type == Long.class || type == long.class)
            return "BIGINT";

        if (type == Integer.class || type == int.class)
            return "INT";

        if (type == Short.class || type == short.class)
            return "SMALLINT";

        if (type == Double.class || type == double.class)
            return "DOUBLE";

        if (type == Float.class || type == float.class)
            return "FLOAT";

        if (type == Boolean.class || type == boolean.class)
            return "TINYINT(1)";

        if (type == BigDecimal.class)
            return "DECIMAL(19,2)";

        if (type == java.sql.Date.class)
            return "DATE";

        if (type == java.sql.Time.class)
            return "TIME";

        if (type == java.sql.Timestamp.class || type == Date.class)
            return "TIMESTAMP NULL";

        return null;
    }

    
    
    private static boolean tableExists(Connection conn, String tableName) throws SQLException 
    {
        String sql = "SELECT COUNT(*) FROM information_schema.TABLES "
        		   + "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?";

        try (PreparedStatement ps = conn.prepareStatement(sql)) 
        {
            ps.setString(1, tableName);

            try (ResultSet rs = ps.executeQuery()) 
            {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }
    
    

    private static boolean columnExists(Connection conn, String tableName, String columnName) throws SQLException 
    {
        String sql = "SELECT COUNT(*) FROM information_schema.COLUMNS "
		           + "WHERE TABLE_SCHEMA = DATABASE() "
		           + "AND TABLE_NAME = ? AND COLUMN_NAME = ?";

        try (PreparedStatement ps = conn.prepareStatement(sql)) 
        {
            ps.setString(1, tableName);
            ps.setString(2, columnName);
            
            try (ResultSet rs = ps.executeQuery()) 
            {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    
    
    private static boolean isSafeIdentifier(String name) 
    {
        return name != null && name.matches("[A-Za-z_][A-Za-z0-9_]*");
    }

    
    
    private static void createLogTable(Connection conn) throws SQLException 
    {
        String sql = "CREATE TABLE IF NOT EXISTS eclipselink_schema_log ("
		           + "id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,"
		           + "entity_name VARCHAR(255),"
		           + "table_name VARCHAR(255),"
		           + "column_name VARCHAR(255),"
		           + "modification_type VARCHAR(50),"
		           + "status VARCHAR(30),"
		           + "error_message TEXT,"
		           + "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
		           + ") ENGINE=InnoDB";

        try (Statement st = conn.createStatement()) 
        {
            st.executeUpdate(sql);
        }
    }
    
    

    private static void logError(Connection conn, String entity, String table, String column, String type, String message)
    {
        String sql = "INSERT INTO eclipselink_schema_log "
		           + "(entity_name, table_name, column_name, "
		           + "modification_type, status, error_message) "
		           + "VALUES (?, ?, ?, ?, ?, ?)";

        try (PreparedStatement ps = conn.prepareStatement(sql)) 
        {
            ps.setString(1, entity);
            ps.setString(2, table);
            ps.setString(3, column);
            ps.setString(4, type);
            ps.setString(5, "FAILED");
            ps.setString(6, message);
            ps.executeUpdate();
        } 
        catch (Exception logException) 
        {
            System.err.println("Could not save schema error: " + logException);
        }
    }
    
}