package com.social.media.application.service;

import com.social.media.interfaces.web.dto.analytics.*;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Service for posts and likes analytics operations
 */
@Service
public class PostsAnalyticsService {

    private static final Logger logger = LoggerFactory.getLogger(PostsAnalyticsService.class);
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @PersistenceContext
    private EntityManager entityManager;

    /**
     * Get total followers count for a social account
     */
    public List<TotalFollowersResponse> getTotalFollowers(String socialAccountId) {
        try {
            String sql = """
                SELECT 
                    sa.username as conta,
                    sa.display_name as nome_conta,
                    ul.name as lista,
                    ul.total_users as total_seguindo_lista,
                    sa.metrics->>'following' as total_seguindo_real,
                    array_length(ul.usernames, 1) as seguindo_armazenados,
                    ul.last_processed_at as ultima_atualizacao
                FROM core_business.social_accounts sa
                JOIN automation.user_lists ul ON ul.company_id = sa.company_id
                WHERE sa.id = :socialAccountId
                AND ul.name ILIKE '%seguindo%'
                AND ul.name NOT ILIKE '%seguidores%'
                """;

            Query query = entityManager.createNativeQuery(sql);
            query.setParameter("socialAccountId", socialAccountId);

            List<Object[]> results = query.getResultList();
            List<TotalFollowersResponse> followers = new ArrayList<>();

            for (Object[] row : results) {
                followers.add(new TotalFollowersResponse(
                    (String) row[0], // conta
                    (String) row[1], // nome_conta
                    (String) row[2], // lista
                    row[3] != null ? ((Number) row[3]).intValue() : 0, // total_seguindo_lista
                    (String) row[4], // total_seguindo_real
                    row[5] != null ? ((Number) row[5]).intValue() : 0, // seguindo_armazenados
                    row[6] != null ? ((Timestamp) row[6]).toString() : null // ultima_atualizacao
                ));
            }

            logger.info("Retrieved {} followers records for social account: {}", followers.size(), socialAccountId);
            return followers;

        } catch (Exception e) {
            logger.error("Error getting total followers for account {}: {}", socialAccountId, e.getMessage(), e);
            throw new RuntimeException("Failed to get total followers", e);
        }
    }

    /**
     * Get total posts summary for a social account
     */
    public TotalPostsResponse getTotalPosts(String socialAccountId) {
        try {
            String sql = """
                SELECT 
                    COUNT(*) as total_posts,
                    COUNT(CASE WHEN status = 'PUBLISHED' THEN 1 END) as posts_publicados,
                    COUNT(CASE WHEN status = 'SCHEDULED' THEN 1 END) as posts_agendados,
                    COUNT(CASE WHEN status = 'DRAFT' THEN 1 END) as rascunhos,
                    COALESCE(SUM(like_count), 0) as total_likes,
                    COALESCE(SUM(comment_count), 0) as total_comentarios,
                    COALESCE(SUM(share_count), 0) as total_compartilhamentos,
                    COALESCE(SUM(view_count), 0) as total_visualizacoes,
                    COALESCE(AVG(like_count + comment_count + share_count), 0) as media_engajamento,
                    MIN(published_at) as primeiro_post,
                    MAX(published_at) as ultimo_post
                FROM core_business.posts p
                WHERE :socialAccountId = ANY(p.target_social_accounts)
                """;

            Query query = entityManager.createNativeQuery(sql);
            query.setParameter("socialAccountId", socialAccountId);

            Object[] result = (Object[]) query.getSingleResult();

            return new TotalPostsResponse(
                result[0] != null ? ((BigInteger) result[0]).longValue() : 0L,
                result[1] != null ? ((BigInteger) result[1]).longValue() : 0L,
                result[2] != null ? ((BigInteger) result[2]).longValue() : 0L,
                result[3] != null ? ((BigInteger) result[3]).longValue() : 0L,
                result[4] != null ? ((BigDecimal) result[4]).longValue() : 0L,
                result[5] != null ? ((BigDecimal) result[5]).longValue() : 0L,
                result[6] != null ? ((BigDecimal) result[6]).longValue() : 0L,
                result[7] != null ? ((BigDecimal) result[7]).longValue() : 0L,
                result[8] != null ? ((BigDecimal) result[8]).doubleValue() : 0.0,
                result[9] != null ? ((Timestamp) result[9]).toString() : null,
                result[10] != null ? ((Timestamp) result[10]).toString() : null
            );

        } catch (Exception e) {
            logger.error("Error getting total posts for account {}: {}", socialAccountId, e.getMessage(), e);
            throw new RuntimeException("Failed to get total posts", e);
        }
    }

    /**
     * Get total likes summary for a social account
     */
    public TotalLikesResponse getTotalLikes(String socialAccountId) {
        try {
            String sql = """
                SELECT 
                    COUNT(*) as total_posts_publicados,
                    COUNT(CASE WHEN p.like_count > 0 THEN 1 END) as posts_com_curtidas,
                    COUNT(CASE WHEN p.like_count = 0 THEN 1 END) as posts_sem_curtidas,
                    COALESCE(SUM(p.like_count), 0) as total_curtidas_geral,
                    COALESCE(AVG(p.like_count), 0) as media_curtidas_por_post,
                    COALESCE(MAX(p.like_count), 0) as max_curtidas_post,
                    COALESCE(MIN(p.like_count), 0) as min_curtidas_post,
                    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY p.like_count) as mediana_curtidas,
                    COUNT(CASE WHEN p.like_count >= 10 THEN 1 END) as posts_acima_10_curtidas,
                    COUNT(CASE WHEN p.like_count >= 50 THEN 1 END) as posts_acima_50_curtidas,
                    COUNT(CASE WHEN p.like_count >= 100 THEN 1 END) as posts_acima_100_curtidas,
                    COALESCE(ROUND(AVG(CASE WHEN p.view_count > 0 THEN (p.like_count * 100.0 / p.view_count) ELSE 0 END), 2), 0) as taxa_curtidas_media_pct
                FROM core_business.posts p
                WHERE :socialAccountId = ANY(p.target_social_accounts)
                AND p.status = 'PUBLISHED'
                """;

            Query query = entityManager.createNativeQuery(sql);
            query.setParameter("socialAccountId", socialAccountId);

            Object[] result = (Object[]) query.getSingleResult();

            return new TotalLikesResponse(
                result[0] != null ? ((BigInteger) result[0]).longValue() : 0L,
                result[1] != null ? ((BigInteger) result[1]).longValue() : 0L,
                result[2] != null ? ((BigInteger) result[2]).longValue() : 0L,
                result[3] != null ? ((BigDecimal) result[3]).longValue() : 0L,
                result[4] != null ? ((BigDecimal) result[4]).doubleValue() : 0.0,
                result[5] != null ? ((BigDecimal) result[5]).longValue() : 0L,
                result[6] != null ? ((BigDecimal) result[6]).longValue() : 0L,
                result[7] != null ? ((BigDecimal) result[7]).doubleValue() : 0.0,
                result[8] != null ? ((BigInteger) result[8]).longValue() : 0L,
                result[9] != null ? ((BigInteger) result[9]).longValue() : 0L,
                result[10] != null ? ((BigInteger) result[10]).longValue() : 0L,
                result[11] != null ? ((BigDecimal) result[11]).doubleValue() : 0.0
            );

        } catch (Exception e) {
            logger.error("Error getting total likes for account {}: {}", socialAccountId, e.getMessage(), e);
            throw new RuntimeException("Failed to get total likes", e);
        }
    }

    /**
     * Get likes by post for a social account
     */
    public List<LikesByPostResponse> getLikesByPost(String socialAccountId) {
        try {
            String sql = """
                SELECT 
                    p.id as post_id,
                    p.post_code,
                    LEFT(p.content_text, 150) as conteudo,
                    p.like_count as curtidas,
                    p.comment_count as comentarios,
                    p.share_count as compartilhamentos,
                    p.view_count as visualizacoes,
                    (p.like_count + p.comment_count + p.share_count) as engajamento_total,
                    p.published_at,
                    sa.username as conta_instagram
                FROM core_business.posts p
                LEFT JOIN core_business.social_accounts sa ON sa.id = ANY(p.target_social_accounts)
                WHERE :socialAccountId = ANY(p.target_social_accounts)
                AND p.status = 'PUBLISHED'
                ORDER BY p.like_count DESC
                """;

            Query query = entityManager.createNativeQuery(sql);
            query.setParameter("socialAccountId", socialAccountId);

            List<Object[]> results = query.getResultList();
            List<LikesByPostResponse> likesByPost = new ArrayList<>();

            for (Object[] row : results) {
                likesByPost.add(new LikesByPostResponse(
                    row[0] != null ? row[0].toString() : null, // post_id
                    (String) row[1], // post_code
                    (String) row[2], // conteudo
                    row[3] != null ? ((Number) row[3]).longValue() : 0L, // curtidas
                    row[4] != null ? ((Number) row[4]).longValue() : 0L, // comentarios
                    row[5] != null ? ((Number) row[5]).longValue() : 0L, // compartilhamentos
                    row[6] != null ? ((Number) row[6]).longValue() : 0L, // visualizacoes
                    row[7] != null ? ((Number) row[7]).longValue() : 0L, // engajamento_total
                    row[8] != null ? ((Timestamp) row[8]).toString() : null, // published_at
                    (String) row[9] // conta_instagram
                ));
            }

            logger.info("Retrieved {} likes by post records for social account: {}", likesByPost.size(), socialAccountId);
            return likesByPost;

        } catch (Exception e) {
            logger.error("Error getting likes by post for account {}: {}", socialAccountId, e.getMessage(), e);
            throw new RuntimeException("Failed to get likes by post", e);
        }
    }

    /**
     * Get total followers count by company ID (for dashboard)
     */
    public Long getTotalFollowersByCompanyId(Long companyId) {
        try {
            String sql = """
                SELECT COALESCE(SUM(CAST(sa.metrics->>'followers' AS INTEGER)), 0) as total_followers
                FROM core_business.social_accounts sa
                WHERE sa.company_id = :companyId
                AND sa.metrics->>'followers' IS NOT NULL
                """;

            Query query = entityManager.createNativeQuery(sql);
            query.setParameter("companyId", companyId);

            Object result = query.getSingleResult();
            return result != null ? ((Number) result).longValue() : 0L;

        } catch (Exception e) {
            logger.error("Error getting total followers for company {}: {}", companyId, e.getMessage(), e);
            return 0L;
        }
    }

    /**
     * Get total posts count by company ID (for dashboard)
     */
    public Long getTotalPostsByCompanyId(Long companyId) {
        try {
            String sql = """
                SELECT COUNT(p.id) as total_posts
                FROM core_business.posts p
                JOIN core_business.social_accounts sa ON sa.id = ANY(p.target_social_accounts)
                WHERE sa.company_id = :companyId
                """;

            Query query = entityManager.createNativeQuery(sql);
            query.setParameter("companyId", companyId);

            Object result = query.getSingleResult();
            return result != null ? ((BigInteger) result).longValue() : 0L;

        } catch (Exception e) {
            logger.error("Error getting total posts for company {}: {}", companyId, e.getMessage(), e);
            return 0L;
        }
    }

    /**
     * Get total likes count by company ID (for dashboard)
     */
    public Long getTotalLikesByCompanyId(Long companyId) {
        try {
            String sql = """
                SELECT COALESCE(SUM(p.like_count), 0) as total_likes
                FROM core_business.posts p
                JOIN core_business.social_accounts sa ON sa.id = ANY(p.target_social_accounts)
                WHERE sa.company_id = :companyId
                AND p.status = 'PUBLISHED'
                """;

            Query query = entityManager.createNativeQuery(sql);
            query.setParameter("companyId", companyId);

            Object result = query.getSingleResult();
            return result != null ? ((BigDecimal) result).longValue() : 0L;

        } catch (Exception e) {
            logger.error("Error getting total likes for company {}: {}", companyId, e.getMessage(), e);
            return 0L;
        }
    }
}