package com.saas.admin.service;

import com.saas.shared.annotation.TenantSchemaEntity;
import com.saas.shared.core.TenantContext;
import com.saas.tenant.entity.SchemaVersion;
import jakarta.persistence.*;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.service.ServiceRegistry;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.config.BeanDefinition;
import org.springframework.context.annotation.ClassPathScanningCandidateComponentProvider;
import org.springframework.core.type.filter.AnnotationTypeFilter;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
public class TenantSchemaMigrationService {
    
    @Autowired
    private DataSource dataSource;
    
    @Autowired
    private EntityManagerFactory entityManagerFactory;
    
    private static final String TENANT_ENTITY_PACKAGE = "com.saas.tenant.entity";
    private static final String ADMIN_ENTITY_PACKAGE = "com.saas.admin.entity";
    
    public boolean needsMigration(String tenantSchemaName) {
        String currentHash = calculateSchemaVersionHash();
        String dbHash = getLatestSchemaVersionHash(tenantSchemaName);
        
        if (dbHash == null) {
            log.info("No schema version found in tenant '{}'. First migration needed.", tenantSchemaName);
            return true;
        }
        
        boolean needsMigration = !currentHash.equals(dbHash);
        
        if (needsMigration) {
            log.info("Schema version mismatch for tenant '{}'. Current: {}, DB: {}. Migration needed.", 
                    tenantSchemaName, currentHash, dbHash);
        } else {
            log.debug("Schema version up-to-date for tenant '{}'. Hash: {}", tenantSchemaName, currentHash);
        }
        
        return needsMigration;
    }
    
    public void migrateIfNeeded(String tenantSchemaName) {
        if (!needsMigration(tenantSchemaName)) {
            log.debug("Tenant '{}' schema is up-to-date. Skipping migration.", tenantSchemaName);
            return;
        }
        
        log.info("Starting automatic schema migration for tenant '{}'...", tenantSchemaName);
        
        try {
            performMigration(tenantSchemaName);
            log.info("Schema migration completed successfully for tenant '{}'", tenantSchemaName);
            
        } catch (Exception e) {
            log.error("CRITICAL: Schema migration failed for tenant '{}'. Application may be in inconsistent state!", 
                    tenantSchemaName, e);
            throw new RuntimeException("Schema migration failed for tenant: " + tenantSchemaName + 
                    ". Please check logs and resolve schema conflicts before retrying.", e);
        }
    }
    
    public void recordInitialVersion(String tenantSchemaName) {
        TenantContext.setTenantId(tenantSchemaName);
        
        try {
            EntityManager em = entityManagerFactory.createEntityManager();
            try {
                em.getTransaction().begin();
                
                Set<Class<?>> entities = scanTenantEntities();
                String currentHash = calculateSchemaVersionHash();
                String entityNames = entities.stream()
                        .map(Class::getSimpleName)
                        .sorted()
                        .collect(Collectors.joining(", "));
                
                SchemaVersion version = new SchemaVersion();
                version.setVersionHash(currentHash);
                version.setEntityCount(entities.size());
                version.setEntityNames(entityNames);
                version.setMigrationType("INITIAL");
                version.setMigrationStatus("SUCCESS");
                version.setMigrationNotes("Initial schema creation");
                version.setIsCurrent(true);
                version.setAppliedBy("SYSTEM");
                version.setAppliedAt(LocalDateTime.now());
                
                em.persist(version);
                em.getTransaction().commit();
                
                log.info("Initial schema version recorded for tenant '{}'. Hash: {}, Entities: {}", 
                        tenantSchemaName, currentHash, entities.size());
                
            } catch (Exception e) {
                if (em.getTransaction().isActive()) {
                    em.getTransaction().rollback();
                }
                log.error("Failed to record initial schema version for tenant '{}'", tenantSchemaName, e);
            } finally {
                em.close();
            }
        } finally {
            TenantContext.clear();
        }
    }
    
    private void performMigration(String tenantSchemaName) {
        TenantContext.setTenantId(tenantSchemaName);
        ServiceRegistry serviceRegistry = null;
        org.hibernate.SessionFactory sessionFactory = null;
        Session session = null;
        Transaction transaction = null;
        
        try {
            log.info("Building Hibernate metadata for schema migration...");
            
            Map<String, Object> settings = new HashMap<>();
            settings.put("hibernate.connection.datasource", dataSource);
            settings.put("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");
            settings.put("hibernate.default_catalog", tenantSchemaName);
            settings.put("hibernate.hbm2ddl.auto", "update");
            settings.put("hibernate.physical_naming_strategy", "org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy");
            
            serviceRegistry = new StandardServiceRegistryBuilder()
                    .applySettings(settings)
                    .build();
            
            MetadataSources metadataSources = new MetadataSources(serviceRegistry);
            
            Set<Class<?>> tenantEntities = scanTenantEntities();
            log.info("Migrating {} entity tables...", tenantEntities.size());
            
            for (Class<?> entityClass : tenantEntities) {
                log.debug("Adding entity for migration: {}", entityClass.getSimpleName());
                metadataSources.addAnnotatedClass(entityClass);
            }
            
            Metadata metadata = metadataSources.buildMetadata();
            sessionFactory = metadata.buildSessionFactory();
            
            log.info("Hibernate DDL update completed. Recording migration version...");
            
            recordMigrationVersion(tenantSchemaName, sessionFactory);
            
        } catch (Exception e) {
            log.error("Migration failed for tenant '{}'", tenantSchemaName, e);
            throw new RuntimeException("Schema migration failed", e);
        } finally {
            if (sessionFactory != null) {
                sessionFactory.close();
            }
            if (serviceRegistry != null) {
                org.hibernate.boot.registry.StandardServiceRegistryBuilder.destroy(serviceRegistry);
            }
            TenantContext.clear();
        }
    }
    
    private void recordMigrationVersion(String tenantSchemaName, org.hibernate.SessionFactory sessionFactory) {
        Session session = null;
        Transaction transaction = null;
        
        try {
            session = sessionFactory.openSession();
            transaction = session.beginTransaction();
            
            String query = "UPDATE SchemaVersion SET isCurrent = false WHERE isCurrent = true";
            session.createQuery(query).executeUpdate();
            
            Set<Class<?>> entities = scanTenantEntities();
            String currentHash = calculateSchemaVersionHash();
            String entityNames = entities.stream()
                    .map(Class::getSimpleName)
                    .sorted()
                    .collect(Collectors.joining(", "));
            
            SchemaVersion version = new SchemaVersion();
            version.setVersionHash(currentHash);
            version.setEntityCount(entities.size());
            version.setEntityNames(entityNames);
            version.setMigrationType("AUTO");
            version.setMigrationStatus("SUCCESS");
            version.setMigrationNotes("Automatic migration on connection");
            version.setIsCurrent(true);
            version.setAppliedBy("SYSTEM");
            version.setAppliedAt(LocalDateTime.now());
            
            session.persist(version);
            transaction.commit();
            
            log.info("Migration version recorded for tenant '{}'. Hash: {}, Entities: {}", 
                    tenantSchemaName, currentHash, entities.size());
            
        } catch (Exception e) {
            if (transaction != null && transaction.isActive()) {
                transaction.rollback();
            }
            log.warn("Could not record migration version (non-critical): {}", e.getMessage());
        } finally {
            if (session != null) {
                session.close();
            }
        }
    }
    
    private String getLatestSchemaVersionHash(String tenantSchemaName) {
        TenantContext.setTenantId(tenantSchemaName);
        
        try {
            EntityManager em = entityManagerFactory.createEntityManager();
            try {
                TypedQuery<String> query = em.createQuery(
                        "SELECT sv.versionHash FROM SchemaVersion sv WHERE sv.isCurrent = true", 
                        String.class
                );
                query.setMaxResults(1);
                List<String> results = query.getResultList();
                
                return results.isEmpty() ? null : results.get(0);
                
            } finally {
                em.close();
            }
        } catch (Exception e) {
            log.debug("Could not retrieve schema version for tenant '{}' (table may not exist yet): {}", 
                    tenantSchemaName, e.getMessage());
            return null;
        } finally {
            TenantContext.clear();
        }
    }
    
    private String calculateSchemaVersionHash() {
        try {
            Set<Class<?>> entities = scanTenantEntities();
            
            StringBuilder fullSignature = new StringBuilder();
            
            List<Class<?>> sortedEntities = entities.stream()
                    .sorted(Comparator.comparing(Class::getName))
                    .collect(Collectors.toList());
            
            for (Class<?> entityClass : sortedEntities) {
                fullSignature.append("CLASS:").append(entityClass.getName()).append("|");
                
                Table tableAnnotation = entityClass.getAnnotation(Table.class);
                if (tableAnnotation != null) {
                    fullSignature.append("TABLE:").append(tableAnnotation.name())
                            .append(",schema=").append(tableAnnotation.schema())
                            .append(",catalog=").append(tableAnnotation.catalog());
                    
                    if (tableAnnotation.uniqueConstraints() != null && tableAnnotation.uniqueConstraints().length > 0) {
                        fullSignature.append(",UNIQUE_CONSTRAINTS:[");
                        for (UniqueConstraint uc : tableAnnotation.uniqueConstraints()) {
                            fullSignature.append("(name=").append(uc.name())
                                    .append(",cols=").append(String.join(",", uc.columnNames()))
                                    .append(")");
                        }
                        fullSignature.append("]");
                    }
                    
                    if (tableAnnotation.indexes() != null && tableAnnotation.indexes().length > 0) {
                        fullSignature.append(",INDEXES:[");
                        for (Index idx : tableAnnotation.indexes()) {
                            fullSignature.append("(name=").append(idx.name())
                                    .append(",cols=").append(idx.columnList())
                                    .append(",unique=").append(idx.unique())
                                    .append(")");
                        }
                        fullSignature.append("]");
                    }
                    
                    fullSignature.append("|");
                }
                
                List<java.lang.reflect.Field> allFields = getAllFields(entityClass);
                List<String> fieldSignatures = new ArrayList<>();
                
                for (java.lang.reflect.Field field : allFields) {
                    StringBuilder fieldSig = new StringBuilder();
                    
                    fieldSig.append("FIELD:").append(field.getName())
                            .append(":").append(field.getType().getSimpleName());
                    
                    Column columnAnnotation = field.getAnnotation(Column.class);
                    if (columnAnnotation != null) {
                        fieldSig.append(":COL(")
                                .append("name=").append(columnAnnotation.name())
                                .append(",nullable=").append(columnAnnotation.nullable())
                                .append(",unique=").append(columnAnnotation.unique())
                                .append(",length=").append(columnAnnotation.length())
                                .append(",precision=").append(columnAnnotation.precision())
                                .append(",scale=").append(columnAnnotation.scale())
                                .append(",insertable=").append(columnAnnotation.insertable())
                                .append(",updatable=").append(columnAnnotation.updatable())
                                .append(",columnDef=").append(columnAnnotation.columnDefinition())
                                .append(",table=").append(columnAnnotation.table())
                                .append(")");
                    }
                    
                    Id idAnnotation = field.getAnnotation(Id.class);
                    if (idAnnotation != null) {
                        fieldSig.append(":PK");
                    }
                    
                    GeneratedValue genAnnotation = field.getAnnotation(GeneratedValue.class);
                    if (genAnnotation != null) {
                        fieldSig.append(":GEN(strategy=").append(genAnnotation.strategy())
                                .append(",generator=").append(genAnnotation.generator())
                                .append(")");
                    }
                    
                    ManyToOne manyToOneAnnotation = field.getAnnotation(ManyToOne.class);
                    if (manyToOneAnnotation != null) {
                        fieldSig.append(":MANYTOONE(")
                                .append("fetch=").append(manyToOneAnnotation.fetch())
                                .append(",optional=").append(manyToOneAnnotation.optional())
                                .append(",cascade=").append(Arrays.toString(manyToOneAnnotation.cascade()))
                                .append(")");
                    }
                    
                    OneToMany oneToManyAnnotation = field.getAnnotation(OneToMany.class);
                    if (oneToManyAnnotation != null) {
                        fieldSig.append(":ONETOMANY(")
                                .append("fetch=").append(oneToManyAnnotation.fetch())
                                .append(",mappedBy=").append(oneToManyAnnotation.mappedBy())
                                .append(",cascade=").append(Arrays.toString(oneToManyAnnotation.cascade()))
                                .append(",orphanRemoval=").append(oneToManyAnnotation.orphanRemoval())
                                .append(")");
                    }
                    
                    ManyToMany manyToManyAnnotation = field.getAnnotation(ManyToMany.class);
                    if (manyToManyAnnotation != null) {
                        fieldSig.append(":MANYTOMANY(")
                                .append("fetch=").append(manyToManyAnnotation.fetch())
                                .append(",mappedBy=").append(manyToManyAnnotation.mappedBy())
                                .append(",cascade=").append(Arrays.toString(manyToManyAnnotation.cascade()))
                                .append(")");
                    }
                    
                    OneToOne oneToOneAnnotation = field.getAnnotation(OneToOne.class);
                    if (oneToOneAnnotation != null) {
                        fieldSig.append(":ONETOONE(")
                                .append("fetch=").append(oneToOneAnnotation.fetch())
                                .append(",optional=").append(oneToOneAnnotation.optional())
                                .append(",mappedBy=").append(oneToOneAnnotation.mappedBy())
                                .append(",cascade=").append(Arrays.toString(oneToOneAnnotation.cascade()))
                                .append(",orphanRemoval=").append(oneToOneAnnotation.orphanRemoval())
                                .append(")");
                    }
                    
                    JoinColumn joinColumnAnnotation = field.getAnnotation(JoinColumn.class);
                    if (joinColumnAnnotation != null) {
                        fieldSig.append(":JOIN(")
                                .append("name=").append(joinColumnAnnotation.name())
                                .append(",ref=").append(joinColumnAnnotation.referencedColumnName())
                                .append(",nullable=").append(joinColumnAnnotation.nullable())
                                .append(",unique=").append(joinColumnAnnotation.unique())
                                .append(",insertable=").append(joinColumnAnnotation.insertable())
                                .append(",updatable=").append(joinColumnAnnotation.updatable())
                                .append(",columnDef=").append(joinColumnAnnotation.columnDefinition())
                                .append(",table=").append(joinColumnAnnotation.table())
                                .append(",fk=(name=").append(joinColumnAnnotation.foreignKey().name())
                                        .append(",def=").append(joinColumnAnnotation.foreignKey().value())
                                        .append(")")
                                .append(")");
                    }
                    
                    JoinTable joinTableAnnotation = field.getAnnotation(JoinTable.class);
                    if (joinTableAnnotation != null) {
                        fieldSig.append(":JOINTABLE(")
                                .append("name=").append(joinTableAnnotation.name())
                                .append(",schema=").append(joinTableAnnotation.schema())
                                .append(",catalog=").append(joinTableAnnotation.catalog())
                                .append(",fk=(name=").append(joinTableAnnotation.foreignKey().name())
                                        .append(",def=").append(joinTableAnnotation.foreignKey().value())
                                        .append(")")
                                .append(",inverseFk=(name=").append(joinTableAnnotation.inverseForeignKey().name())
                                        .append(",def=").append(joinTableAnnotation.inverseForeignKey().value())
                                        .append(")")
                                .append(",joinCols=[");
                        
                        for (JoinColumn jc : joinTableAnnotation.joinColumns()) {
                            fieldSig.append("(name=").append(jc.name())
                                    .append(",ref=").append(jc.referencedColumnName())
                                    .append(",nullable=").append(jc.nullable())
                                    .append(",unique=").append(jc.unique())
                                    .append(",insertable=").append(jc.insertable())
                                    .append(",updatable=").append(jc.updatable())
                                    .append(",columnDef=").append(jc.columnDefinition())
                                    .append(",table=").append(jc.table())
                                    .append(",fk=(name=").append(jc.foreignKey().name())
                                            .append(",def=").append(jc.foreignKey().value())
                                            .append(")")
                                    .append(")");
                        }
                        fieldSig.append("],inverseJoinCols=[");
                        
                        for (JoinColumn ijc : joinTableAnnotation.inverseJoinColumns()) {
                            fieldSig.append("(name=").append(ijc.name())
                                    .append(",ref=").append(ijc.referencedColumnName())
                                    .append(",nullable=").append(ijc.nullable())
                                    .append(",unique=").append(ijc.unique())
                                    .append(",insertable=").append(ijc.insertable())
                                    .append(",updatable=").append(ijc.updatable())
                                    .append(",columnDef=").append(ijc.columnDefinition())
                                    .append(",table=").append(ijc.table())
                                    .append(",fk=(name=").append(ijc.foreignKey().name())
                                            .append(",def=").append(ijc.foreignKey().value())
                                            .append(")")
                                    .append(")");
                        }
                        fieldSig.append("]");
                        
                        if (joinTableAnnotation.uniqueConstraints() != null && joinTableAnnotation.uniqueConstraints().length > 0) {
                            fieldSig.append(",uniqueConstraints=[");
                            for (UniqueConstraint uc : joinTableAnnotation.uniqueConstraints()) {
                                fieldSig.append("(cols=").append(String.join(",", uc.columnNames())).append(")");
                            }
                            fieldSig.append("]");
                        }
                        
                        if (joinTableAnnotation.indexes() != null && joinTableAnnotation.indexes().length > 0) {
                            fieldSig.append(",indexes=[");
                            for (Index idx : joinTableAnnotation.indexes()) {
                                fieldSig.append("(name=").append(idx.name())
                                        .append(",cols=").append(idx.columnList())
                                        .append(",unique=").append(idx.unique())
                                        .append(")");
                            }
                            fieldSig.append("]");
                        }
                        
                        fieldSig.append(")");
                    }
                    
                    fieldSignatures.add(fieldSig.toString());
                }
                
                fieldSignatures.sort(String::compareTo);
                fullSignature.append(String.join("|", fieldSignatures)).append("||");
            }
            
            String signature = fullSignature.toString();
            log.debug("Full entity signature for hashing: {}", signature.substring(0, Math.min(200, signature.length())));
            
            MessageDigest digest = MessageDigest.getInstance("SHA-256");
            byte[] hashBytes = digest.digest(signature.getBytes(StandardCharsets.UTF_8));
            
            StringBuilder hexString = new StringBuilder();
            for (byte b : hashBytes) {
                String hex = Integer.toHexString(0xff & b);
                if (hex.length() == 1) hexString.append('0');
                hexString.append(hex);
            }
            
            return hexString.toString();
            
        } catch (Exception e) {
            log.error("Failed to calculate schema version hash", e);
            throw new RuntimeException("Hash calculation failed", e);
        }
    }
    
    private Set<Class<?>> scanTenantEntities() {
        ClassPathScanningCandidateComponentProvider scanner = 
                new ClassPathScanningCandidateComponentProvider(false);
        scanner.addIncludeFilter(new AnnotationTypeFilter(Entity.class));
        
        Set<Class<?>> entityClasses = new HashSet<>();
        
        try {
            // Scan tenant entity package (all entities included)
            Set<BeanDefinition> tenantCandidates = scanner.findCandidateComponents(TENANT_ENTITY_PACKAGE);
            
            for (BeanDefinition bd : tenantCandidates) {
                try {
                    Class<?> clazz = Class.forName(bd.getBeanClassName());
                    entityClasses.add(clazz);
                    log.debug("Including tenant entity: {}", clazz.getSimpleName());
                } catch (ClassNotFoundException e) {
                    log.error("Could not load entity class: {}", bd.getBeanClassName(), e);
                }
            }
            
            // Scan admin entity package (only @TenantSchemaEntity annotated entities)
            Set<BeanDefinition> adminCandidates = scanner.findCandidateComponents(ADMIN_ENTITY_PACKAGE);
            
            for (BeanDefinition bd : adminCandidates) {
                try {
                    Class<?> clazz = Class.forName(bd.getBeanClassName());
                    
                    // Only include admin entities marked with @TenantSchemaEntity
                    if (clazz.isAnnotationPresent(TenantSchemaEntity.class)) {
                        entityClasses.add(clazz);
                        TenantSchemaEntity annotation = clazz.getAnnotation(TenantSchemaEntity.class);
                        log.info("Including shared admin entity in tenant schema: {} - {}", 
                                clazz.getSimpleName(), annotation.value());
                    }
                } catch (ClassNotFoundException e) {
                    log.error("Could not load entity class: {}", bd.getBeanClassName(), e);
                }
            }
            
            log.info("Total entities for tenant schema migration: {}", entityClasses.size());
            
        } catch (Exception e) {
            log.error("Error scanning tenant entities", e);
            throw new RuntimeException("Failed to scan tenant entities", e);
        }
        
        return entityClasses;
    }
    
    private List<java.lang.reflect.Field> getAllFields(Class<?> clazz) {
        List<java.lang.reflect.Field> fields = new ArrayList<>();
        Class<?> currentClass = clazz;
        
        while (currentClass != null && currentClass != Object.class) {
            java.lang.reflect.Field[] declaredFields = currentClass.getDeclaredFields();
            for (java.lang.reflect.Field field : declaredFields) {
                if (!java.lang.reflect.Modifier.isStatic(field.getModifiers()) && 
                    !java.lang.reflect.Modifier.isTransient(field.getModifiers())) {
                    fields.add(field);
                }
            }
            currentClass = currentClass.getSuperclass();
        }
        
        return fields;
    }
}