package com.saas.admin.service;

import com.saas.admin.entity.Tenant;
import com.saas.admin.entity.User;
import com.saas.shared.audit.Auditable;
import com.saas.shared.core.TenantContext;
import com.saas.tenant.entity.TenantInfo;
import com.saas.tenant.entity.TenantUser;
import jakarta.persistence.Entity;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
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.annotation.Value;
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.sql.Connection;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

@Service
@Slf4j
public class TenantSchemaService {
    
    @Autowired
    private DataSource dataSource;
    
    @Autowired
    private EntityManagerFactory entityManagerFactory;
    
    @Autowired
    private TenantSchemaMigrationService migrationService;
    
    @Value("${multitenancy.tenant-schema-prefix}")
    private String tenantSchemaPrefix;
    
    @Auditable(action = "CREATE_TENANT_SCHEMA", entityType = "TENANT_SCHEMA")
    public void createTenantDatabaseAndTables(Tenant tenant, User adminUser) {
        String databaseName = tenant.getSchemaName();
        
        try {
            log.info("Creating tenant database: {}", databaseName);
            createDatabase(databaseName);
            
            log.info("Creating tables and copying data to tenant database...");
            createTablesAndCopyData(databaseName, tenant, adminUser);
            
            log.info("Recording initial schema version for tenant database...");
            migrationService.recordInitialVersion(databaseName);
            
            log.info("Tenant database setup completed successfully for: {}", databaseName);
            
        } catch (Exception e) {
            log.error("Failed to create tenant database: {}", databaseName, e);
            throw new RuntimeException("Failed to create tenant database", e);
        }
    }
    
    private void createDatabase(String databaseName) {
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            
            String createDbSQL = String.format("CREATE DATABASE IF NOT EXISTS `%s`", databaseName);
            statement.execute(createDbSQL);
            log.info("Database '{}' created successfully", databaseName);
            
        } catch (Exception e) {
            log.error("Failed to create database: {}", databaseName, e);
            throw new RuntimeException("Database creation failed", e);
        }
    }
    
    private void createTablesAndCopyData(String databaseName, Tenant tenant, User adminUser) {
        TenantContext.setTenantId(databaseName);
        ServiceRegistry serviceRegistry = null;
        org.hibernate.SessionFactory sessionFactory = null;
        Session session = null;
        Transaction transaction = null;
        
        try {
            log.info("Building Hibernate metadata for tenant entities...");
            
            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", databaseName);
            settings.put("hibernate.hbm2ddl.auto", "create");
            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("Found {} tenant entities to create tables for", tenantEntities.size());
            
            for (Class<?> entityClass : tenantEntities) {
                log.info("Adding entity class: {}", entityClass.getSimpleName());
                metadataSources.addAnnotatedClass(entityClass);
            }
            
            log.info("Creating tables in tenant database '{}' using Hibernate...", databaseName);
            
            Metadata metadata = metadataSources.buildMetadata();
            sessionFactory = metadata.buildSessionFactory();
            
            log.info("Tables created successfully, now copying data...");
            
            session = sessionFactory.openSession();
            transaction = session.beginTransaction();
            
            TenantInfo tenantInfo = new TenantInfo();
            tenantInfo.setTenantId(tenant.getTenantId());
            tenantInfo.setTenantName(tenant.getTenantName());
            tenantInfo.setStatus(tenant.getStatus());
            tenantInfo.setCreatedAt(tenant.getCreatedAt());
            tenantInfo.setUpdatedAt(tenant.getUpdatedAt());
            session.persist(tenantInfo);
            log.info("Tenant info copied to tenant database");
            
            TenantUser tenantUser = new TenantUser();
            tenantUser.setEmail(adminUser.getEmail());
            tenantUser.setFirstName(adminUser.getFirstName());
            tenantUser.setLastName(adminUser.getLastName());
            tenantUser.setPassword(adminUser.getPassword());
            tenantUser.setStatus("ACTIVE");
            session.persist(tenantUser);
            log.info("Admin user copied to tenant database");
            
            transaction.commit();
            log.info("Data copied successfully to tenant database: {}", databaseName);
            
        } catch (Exception e) {
            if (transaction != null && transaction.isActive()) {
                transaction.rollback();
            }
            log.error("Failed to create tables or copy data", e);
            throw new RuntimeException("Tenant setup failed", e);
        } finally {
            if (session != null) {
                session.close();
            }
            if (sessionFactory != null) {
                sessionFactory.close();
            }
            if (serviceRegistry != null) {
                org.hibernate.boot.registry.StandardServiceRegistryBuilder.destroy(serviceRegistry);
            }
            TenantContext.clear();
        }
    }
    
    private Set<Class<?>> scanTenantEntities() {
        ClassPathScanningCandidateComponentProvider scanner = 
                new ClassPathScanningCandidateComponentProvider(false);
        scanner.addIncludeFilter(new AnnotationTypeFilter(Entity.class));
        
        Set<Class<?>> entityClasses = new java.util.HashSet<>();
        String basePackage = "com.saas.tenant.entity";
        
        try {
            Set<BeanDefinition> candidates = scanner.findCandidateComponents(basePackage);
            
            for (BeanDefinition bd : candidates) {
                try {
                    Class<?> clazz = Class.forName(bd.getBeanClassName());
                    entityClasses.add(clazz);
                    log.debug("Discovered tenant entity: {}", clazz.getSimpleName());
                } catch (ClassNotFoundException e) {
                    log.error("Could not load entity class: {}", bd.getBeanClassName(), e);
                }
            }
            
            log.info("Successfully scanned {} tenant entities from package: {}", 
                    entityClasses.size(), basePackage);
            
        } catch (Exception e) {
            log.error("Error scanning tenant entities from package: {}", basePackage, e);
            throw new RuntimeException("Failed to scan tenant entities", e);
        }
        
        return entityClasses;
    }
}