package com.social.media.application.service;

import com.social.media.domain.analytics.repository.AccountMetricsRepository;
import com.social.media.infrastructure.persistence.entity.SocialAccountEntity;
import com.social.media.infrastructure.persistence.entity.SocialNetworkEntity;
import com.social.media.infrastructure.persistence.entity.PostEntity;
import com.social.media.interfaces.web.dto.analytics.DashboardAnalyticsResponse;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Service for dashboard analytics operations using real database data
 */
@Service
public class DashboardAnalyticsServiceReal {

    private static final Logger logger = LoggerFactory.getLogger(DashboardAnalyticsServiceReal.class);

    private final AccountMetricsRepository accountMetricsRepository;
    private final PostsAnalyticsService postsAnalyticsService;

    @PersistenceContext
    private EntityManager entityManager;

    public DashboardAnalyticsServiceReal(AccountMetricsRepository accountMetricsRepository,
                                        PostsAnalyticsService postsAnalyticsService) {
        this.accountMetricsRepository = accountMetricsRepository;
        this.postsAnalyticsService = postsAnalyticsService;
    }

    /**
     * Get dashboard analytics data for a company
     */
    public DashboardAnalyticsResponse getDashboardAnalytics(Long companyId) {
        try {
            logger.info("Getting real dashboard analytics for company: {}", companyId);
            
            // Get real data from database
            return getRealDashboardResponse(companyId);
            
        } catch (Exception e) {
            logger.error("Error getting dashboard analytics for company {}: {}", companyId, e.getMessage(), e);
            // Return empty analytics in case of error
            return new DashboardAnalyticsResponse(
                    0L, // totalPosts
                    0L, // publishedPosts
                    0L, // scheduledPosts
                    0, // totalAccounts
                    0, // activeAccounts
                    new ArrayList<>(), // platformStats
                    new ArrayList<>(), // accountMetrics
                    new ArrayList<>() // recentActivity
            );
        }
    }
    
    /**
     * Get real dashboard response from database
     */
    private DashboardAnalyticsResponse getRealDashboardResponse(Long companyId) {
        logger.info("Fetching real dashboard data for company: {}", companyId);
        
        // Get basic counts
        long totalPosts = getTotalPostsByCompanyId(companyId);
        long publishedPosts = getPublishedPostsByCompanyId(companyId);
        long scheduledPosts = getScheduledPostsByCompanyId(companyId);
        
        // Get social accounts data
        List<SocialAccountEntity> socialAccounts = getSocialAccountsByCompanyId(companyId);
        int totalAccounts = socialAccounts.size();
        int activeAccounts = (int) socialAccounts.stream()
                .filter(SocialAccountEntity::getActive)
                .filter(account -> !account.getDeleted())
                .count();
        
        // Calculate platform stats
        List<DashboardAnalyticsResponse.PlatformStatsDto> platformStats = calculatePlatformStats(socialAccounts);
        
        // Get account metrics summary
        List<DashboardAnalyticsResponse.AccountMetricsSummaryDto> accountMetrics = getAccountMetricsSummary(socialAccounts);
        
        // Get recent activity
        List<DashboardAnalyticsResponse.ActivityLogDto> recentActivity = getRecentActivity(companyId);
        
        return new DashboardAnalyticsResponse(
                totalPosts,
                publishedPosts,
                scheduledPosts,
                totalAccounts,
                activeAccounts,
                platformStats,
                accountMetrics,
                recentActivity
        );
    }
    
    /**
     * Get social accounts by company ID
     */
    private List<SocialAccountEntity> getSocialAccountsByCompanyId(Long companyId) {
        String query = """
                SELECT sa FROM SocialAccountEntity sa 
                WHERE sa.companyId = :companyId
                AND sa.deleted = false
                ORDER BY sa.createdAt DESC
                """;

        return entityManager.createQuery(query, SocialAccountEntity.class)
                .setParameter("companyId", companyId)
                .getResultList();
    }
    
    /**
     * Get total posts by company ID
     */
    private long getTotalPostsByCompanyId(Long companyId) {
        String query = """
                SELECT COUNT(p)
                FROM PostEntity p
                WHERE p.companyId = :companyId
                """;

        try {
            Long result = entityManager.createQuery(query, Long.class)
                    .setParameter("companyId", companyId)
                    .getSingleResult();
            return result != null ? result : 0L;
        } catch (Exception e) {
            logger.warn("Could not get total posts for company {}: {}", companyId, e.getMessage());
            return 0L;
        }
    }
    
    /**
     * Get published posts by company ID
     */
    private long getPublishedPostsByCompanyId(Long companyId) {
        String query = """
                SELECT COUNT(p)
                FROM PostEntity p
                WHERE p.companyId = :companyId
                AND p.status = 'PUBLISHED'
                """;

        try {
            Long result = entityManager.createQuery(query, Long.class)
                    .setParameter("companyId", companyId)
                    .getSingleResult();
            return result != null ? result : 0L;
        } catch (Exception e) {
            logger.warn("Could not get published posts for company {}: {}", companyId, e.getMessage());
            return 0L;
        }
    }
    
    /**
     * Get scheduled posts by company ID
     */
    private long getScheduledPostsByCompanyId(Long companyId) {
        String query = """
                SELECT COUNT(p)
                FROM PostEntity p
                WHERE p.companyId = :companyId
                AND p.status = 'SCHEDULED'
                """;

        try {
            Long result = entityManager.createQuery(query, Long.class)
                    .setParameter("companyId", companyId)
                    .getSingleResult();
            return result != null ? result : 0L;
        } catch (Exception e) {
            logger.warn("Could not get scheduled posts for company {}: {}", companyId, e.getMessage());
            return 0L;
        }
    }
    
    /**
     * Calculate platform statistics
     */
    private List<DashboardAnalyticsResponse.PlatformStatsDto> calculatePlatformStats(List<SocialAccountEntity> socialAccounts) {
        Map<String, PlatformStatsCalculator> platformCalculators = new HashMap<>();
        
        // Process each social account
        for (SocialAccountEntity account : socialAccounts) {
            String platformName = getPlatformName(account);
            
            platformCalculators.computeIfAbsent(platformName, k -> new PlatformStatsCalculator())
                    .addAccount(account);
        }
        
        // Convert to DTOs
        List<DashboardAnalyticsResponse.PlatformStatsDto> result = new ArrayList<>();
        for (Map.Entry<String, PlatformStatsCalculator> entry : platformCalculators.entrySet()) {
            PlatformStatsCalculator calc = entry.getValue();
            double percentage = socialAccounts.isEmpty() ? 0.0 : (calc.getAccountCount() * 100.0) / socialAccounts.size();
            
            result.add(new DashboardAnalyticsResponse.PlatformStatsDto(
                    entry.getKey(),
                    calc.getAccountCount(),
                    percentage,
                    calc.getAverageEngagement(),
                    calc.getTotalFollowers(),
                    calc.getAverageGrowthRate()
            ));
        }
        
        return result;
    }
    
    /**
     * Get account metrics summary
     */
    private List<DashboardAnalyticsResponse.AccountMetricsSummaryDto> getAccountMetricsSummary(List<SocialAccountEntity> socialAccounts) {
        List<DashboardAnalyticsResponse.AccountMetricsSummaryDto> result = new ArrayList<>();
        
        for (SocialAccountEntity account : socialAccounts) {
            if (!account.getActive() || account.getDeleted()) {
                continue;
            }
            
            String platformName = getPlatformName(account);
            long totalPosts = getTotalPostsByAccountId(account.getId());
            
            result.add(new DashboardAnalyticsResponse.AccountMetricsSummaryDto(
                    account.getId().toString(),
                    account.getUsername(),
                    platformName,
                    account.getFollowersCount(),
                    account.getEngagement(),
                    totalPosts,
                    0.0, // avgLikes - could be calculated if needed
                    0.0, // reachRate - could be calculated if needed
                    account.getLastUpdateDate()
            ));
        }
        
        return result;
    }
    
    /**
     * Get total posts by account ID
     */
    private long getTotalPostsByAccountId(Long accountId) {
        String query = """
                SELECT COUNT(p)
                FROM PostEntity p
                WHERE :accountId MEMBER OF p.targetSocialAccountIds
                """;

        try {
            Long result = entityManager.createQuery(query, Long.class)
                    .setParameter("accountId", accountId)
                    .getSingleResult();
            return result != null ? result : 0L;
        } catch (Exception e) {
            logger.warn("Could not get total posts for account {}: {}", accountId, e.getMessage());
            return 0L;
        }
    }
    
    /**
     * Get platform name for social account
     */
    private String getPlatformName(SocialAccountEntity account) {
        String query = """
                SELECT sn.name 
                FROM SocialNetworkEntity sn 
                WHERE sn.id = :socialNetworkId
                """;
        
        try {
            return entityManager.createQuery(query, String.class)
                    .setParameter("socialNetworkId", account.getSocialNetworkId())
                    .getSingleResult();
        } catch (Exception e) {
            logger.warn("Could not get platform name for social network {}: {}", account.getSocialNetworkId(), e.getMessage());
            return "Unknown";
        }
    }
    
    /**
     * Get recent activity
     */
    private List<DashboardAnalyticsResponse.ActivityLogDto> getRecentActivity(Long companyId) {
        String query = """
                SELECT al.id, al.action, al.description, al.timestamp, al.user_id, al.entity_type, al.entity_id
                FROM analytics.activity_logs al
                WHERE al.company_id = :companyId
                ORDER BY al.timestamp DESC
                LIMIT 10
                """;
        
        try {
            @SuppressWarnings("unchecked")
            List<Object[]> results = entityManager.createNativeQuery(query)
                    .setParameter("companyId", companyId)
                    .getResultList();
            
            List<DashboardAnalyticsResponse.ActivityLogDto> activities = new ArrayList<>();
            for (Object[] row : results) {
                activities.add(new DashboardAnalyticsResponse.ActivityLogDto(
                        row[0].toString(), // id
                        (String) row[1],   // action
                        (String) row[2],   // description
                        ((java.sql.Timestamp) row[3]).toLocalDateTime(), // timestamp
                        row[4] != null ? row[4].toString() : "SYSTEM", // userId
                        (String) row[5],   // entityType
                        row[6] != null ? row[6].toString() : null // entityId
                ));
            }
            
            return activities;
        } catch (Exception e) {
            logger.warn("Could not get recent activity for company {}: {}", companyId, e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * Helper class for calculating platform statistics
     */
    private static class PlatformStatsCalculator {
        private int accountCount = 0;
        private long totalFollowers = 0L;
        private double totalEngagement = 0.0;
        private double totalGrowthRate = 0.0;
        
        public void addAccount(SocialAccountEntity account) {
            accountCount++;
            totalFollowers += account.getFollowersCount();
            totalEngagement += account.getEngagement();
            // Growth rate could be calculated from metrics if available
        }
        
        public int getAccountCount() { return accountCount; }
        public long getTotalFollowers() { return totalFollowers; }
        public double getAverageEngagement() { 
            return accountCount > 0 ? totalEngagement / accountCount : 0.0; 
        }
        public double getAverageGrowthRate() { 
            return accountCount > 0 ? totalGrowthRate / accountCount : 0.0; 
        }
    }
}