package com.saas.shared.datasource;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

/**
 * Dynamic HikariCP DataSource Manager
 * 
 * Creates tenant-specific connection pools on demand and automatically closes
 * inactive pools to optimize resource usage.
 * 
 * Benefits:
 * - Lazy pool creation (only when tenant is accessed)
 * - Automatic pool eviction after inactivity
 * - Max pool limit (prevents resource exhaustion)
 * - Better isolation between tenants
 * 
 * Configuration:
 * - Max active pools: 100 (configurable)
 * - Pool eviction after: 30 minutes of inactivity
 * - Pool size per tenant: 5 connections (configurable)
 */
@Component
@Slf4j
public class DynamicHikariDataSourceManager {

    @Value("${spring.datasource.url}")
    private String baseJdbcUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${multitenancy.datasource.max-active-pools:100}")
    private int maxActivePools;

    @Value("${multitenancy.datasource.pool-size:5}")
    private int poolSize;

    @Value("${multitenancy.datasource.eviction-timeout-minutes:30}")
    private long evictionTimeoutMinutes;

    // Map of tenant -> HikariDataSource
    private final Map<String, TenantDataSourceWrapper> tenantDataSources = new ConcurrentHashMap<>();

    // Scheduler for automatic pool eviction
    private final ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);

    public DynamicHikariDataSourceManager() {
        // Schedule eviction task every 10 minutes
        scheduler.scheduleAtFixedRate(this::evictInactivePools, 10, 10, TimeUnit.MINUTES);
        log.info("🔧 DynamicHikariDataSourceManager initialized with auto-eviction");
    }

    /**
     * Get or create DataSource for a tenant (lazy loading)
     * 
     * @param tenantIdentifier Tenant database name (e.g., tenant_acme_corp)
     * @return HikariDataSource for the tenant
     */
    public DataSource getDataSource(String tenantIdentifier) {
        // Special case: admin database (default pool)
        if ("saas_db".equals(tenantIdentifier) || tenantIdentifier == null) {
            return getAdminDataSource();
        }

        // Check if pool already exists
        TenantDataSourceWrapper wrapper = tenantDataSources.get(tenantIdentifier);
        
        if (wrapper != null) {
            wrapper.updateLastAccess();
            log.debug("♻️ Reusing existing pool for tenant: {}", tenantIdentifier);
            return wrapper.getDataSource();
        }

        // Create new pool (synchronized to avoid duplicates)
        return createDataSourceForTenant(tenantIdentifier);
    }

    /**
     * Create new HikariDataSource for a tenant
     */
    private synchronized DataSource createDataSourceForTenant(String tenantIdentifier) {
        // Double-check (another thread might have created it)
        if (tenantDataSources.containsKey(tenantIdentifier)) {
            return tenantDataSources.get(tenantIdentifier).getDataSource();
        }

        // Check max pool limit
        if (tenantDataSources.size() >= maxActivePools) {
            log.warn("⚠️ Max active pools reached ({}). Evicting oldest inactive pool.", maxActivePools);
            evictOldestInactivePool();
        }

        // Create HikariCP configuration
        HikariConfig config = new HikariConfig();
        
        // Extract base URL (remove database name if present)
        String baseUrl = extractBaseUrl(baseJdbcUrl);
        String tenantJdbcUrl = baseUrl + "/" + tenantIdentifier + "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        
        config.setJdbcUrl(tenantJdbcUrl);
        config.setUsername(username);
        config.setPassword(password);
        config.setPoolName("HikariPool-" + tenantIdentifier);
        
        // Pool configuration (optimized for multi-tenancy)
        config.setMaximumPoolSize(poolSize);
        config.setMinimumIdle(1); // Keep 1 connection alive
        config.setConnectionTimeout(30000); // 30 seconds
        config.setIdleTimeout(600000); // 10 minutes idle timeout
        config.setMaxLifetime(1800000); // 30 minutes max lifetime
        config.setLeakDetectionThreshold(60000); // 60 seconds leak detection
        
        // Performance optimizations
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        // Create DataSource
        HikariDataSource dataSource = new HikariDataSource(config);
        
        // Wrap with metadata
        TenantDataSourceWrapper wrapper = new TenantDataSourceWrapper(dataSource, tenantIdentifier);
        tenantDataSources.put(tenantIdentifier, wrapper);
        
        log.info("✅ Created new HikariCP pool for tenant: {} (total active pools: {})", 
                tenantIdentifier, tenantDataSources.size());
        
        return dataSource;
    }

    /**
     * Get admin database DataSource (singleton, never evicted)
     */
    public DataSource getAdminDataSource() {
        return tenantDataSources.computeIfAbsent("saas_db", tenant -> {
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl(baseJdbcUrl);
            config.setUsername(username);
            config.setPassword(password);
            config.setPoolName("HikariPool-Admin");
            config.setMaximumPoolSize(10); // Admin pool is larger
            config.setMinimumIdle(2);
            
            HikariDataSource dataSource = new HikariDataSource(config);
            log.info("✅ Created admin HikariCP pool");
            
            return new TenantDataSourceWrapper(dataSource, "saas_db");
        }).getDataSource();
    }

    /**
     * Evict inactive pools (automatic cleanup)
     */
    private void evictInactivePools() {
        long now = System.currentTimeMillis();
        long evictionThreshold = TimeUnit.MINUTES.toMillis(evictionTimeoutMinutes);

        tenantDataSources.entrySet().removeIf(entry -> {
            String tenant = entry.getKey();
            TenantDataSourceWrapper wrapper = entry.getValue();

            // Never evict admin pool
            if ("saas_db".equals(tenant)) {
                return false;
            }

            long inactiveDuration = now - wrapper.getLastAccessTime();
            
            if (inactiveDuration > evictionThreshold) {
                log.info("🗑️ Evicting inactive pool for tenant: {} (inactive for {} minutes)", 
                        tenant, TimeUnit.MILLISECONDS.toMinutes(inactiveDuration));
                
                wrapper.close();
                return true;
            }
            
            return false;
        });

        log.debug("💾 Active pools: {}/{}", tenantDataSources.size(), maxActivePools);
    }

    /**
     * Evict oldest inactive pool (when max limit reached)
     */
    private void evictOldestInactivePool() {
        tenantDataSources.entrySet().stream()
                .filter(e -> !"saas_db".equals(e.getKey())) // Skip admin
                .min((e1, e2) -> Long.compare(e1.getValue().getLastAccessTime(), e2.getValue().getLastAccessTime()))
                .ifPresent(entry -> {
                    log.info("🗑️ Force evicting oldest pool: {}", entry.getKey());
                    entry.getValue().close();
                    tenantDataSources.remove(entry.getKey());
                });
    }

    /**
     * Extract base JDBC URL (remove database name)
     */
    private String extractBaseUrl(String jdbcUrl) {
        // Example: jdbc:mysql://localhost:3306/saas_db?... → jdbc:mysql://localhost:3306
        int dbNameStart = jdbcUrl.lastIndexOf('/');
        int queryStart = jdbcUrl.indexOf('?');
        
        if (dbNameStart > 0) {
            if (queryStart > 0) {
                return jdbcUrl.substring(0, dbNameStart);
            } else {
                return jdbcUrl.substring(0, dbNameStart);
            }
        }
        
        return jdbcUrl;
    }

    /**
     * Manually close pool for a tenant (for testing/admin operations)
     */
    public void closeDataSource(String tenantIdentifier) {
        TenantDataSourceWrapper wrapper = tenantDataSources.remove(tenantIdentifier);
        if (wrapper != null) {
            wrapper.close();
            log.info("✅ Manually closed pool for tenant: {}", tenantIdentifier);
        }
    }

    /**
     * Close all pools (shutdown hook)
     */
    public void closeAll() {
        log.info("🛑 Closing all HikariCP pools...");
        tenantDataSources.values().forEach(TenantDataSourceWrapper::close);
        tenantDataSources.clear();
        scheduler.shutdown();
    }

    /**
     * Get pool statistics
     */
    public PoolStatistics getStatistics() {
        return PoolStatistics.builder()
                .activePools(tenantDataSources.size())
                .maxPools(maxActivePools)
                .tenants(tenantDataSources.keySet())
                .build();
    }

    /**
     * Wrapper class to track last access time
     */
    @lombok.Data
    private static class TenantDataSourceWrapper {
        private final HikariDataSource dataSource;
        private final String tenantIdentifier;
        private volatile long lastAccessTime;

        public TenantDataSourceWrapper(HikariDataSource dataSource, String tenantIdentifier) {
            this.dataSource = dataSource;
            this.tenantIdentifier = tenantIdentifier;
            this.lastAccessTime = System.currentTimeMillis();
        }

        public void updateLastAccess() {
            this.lastAccessTime = System.currentTimeMillis();
        }

        public void close() {
            if (dataSource != null && !dataSource.isClosed()) {
                dataSource.close();
            }
        }
    }

    /**
     * Pool statistics DTO
     */
    @lombok.Builder
    @lombok.Data
    public static class PoolStatistics {
        private int activePools;
        private int maxPools;
        private java.util.Set<String> tenants;
    }
}