package com.social.media.application.service;

import com.social.media.interfaces.web.dto.analytics.*;
import org.springframework.stereotype.Service;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

/**
 * Service for followers analytics queries
 */
@Service
public class FollowersAnalyticsService {
    
    private static final Logger logger = LoggerFactory.getLogger(FollowersAnalyticsService.class);
    
    private final JdbcTemplate jdbcTemplate;
    
    public FollowersAnalyticsService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    /**
     * Option 1: Search followers through user lists
     */
    public List<FollowersByListResponse> getFollowersByLists(String socialAccountId) {
        logger.info("Getting followers by lists for social account: {}", socialAccountId);
        
        String sql = """
            SELECT 
                ul.name as lista_nome,
                ul.description as descricao,
                unnest(ul.usernames) as username_seguidor,
                ul.total_users as total_seguidores
            FROM automation.user_lists ul
            JOIN core_business.social_accounts sa ON sa.company_id = ul.company_id
            WHERE sa.id = ?
            AND ul.name LIKE '%Seguidores%'
            ORDER BY ul.name, username_seguidor
            """;
        
        RowMapper<FollowersByListResponse> rowMapper = (rs, rowNum) -> 
            new FollowersByListResponse(
                rs.getString("lista_nome"),
                rs.getString("descricao"),
                rs.getString("username_seguidor"),
                rs.getInt("total_seguidores")
            );
        
        try {
            return jdbcTemplate.query(sql, rowMapper, socialAccountId);
        } catch (Exception e) {
            logger.error("Error getting followers by lists for account {}: {}", socialAccountId, e.getMessage(), e);
            throw new RuntimeException("Error retrieving followers by lists", e);
        }
    }
    
    /**
     * Option 2: Search specifically for followers list
     */
    public List<SpecificFollowersResponse> getSpecificFollowers(String socialAccountId) {
        logger.info("Getting specific followers for social account: {}", socialAccountId);
        
        String sql = """
            SELECT 
                'Seguidor' as tipo,
                unnest(usernames) as username,
                row_number() OVER (ORDER BY unnest(usernames)) as posicao
            FROM automation.user_lists ul
            JOIN core_business.social_accounts sa ON sa.company_id = ul.company_id
            WHERE sa.id = ?
            AND ul.name ILIKE '%seguidores%'
            AND ul.name NOT ILIKE '%seguindo%'
            """;
        
        RowMapper<SpecificFollowersResponse> rowMapper = (rs, rowNum) -> 
            new SpecificFollowersResponse(
                rs.getString("tipo"),
                rs.getString("username"),
                rs.getInt("posicao")
            );
        
        try {
            return jdbcTemplate.query(sql, rowMapper, socialAccountId);
        } catch (Exception e) {
            logger.error("Error getting specific followers for account {}: {}", socialAccountId, e.getMessage(), e);
            throw new RuntimeException("Error retrieving specific followers", e);
        }
    }
    
    /**
     * Option 3: Complete query with account information and followers
     */
    public List<CompleteAccountFollowersResponse> getCompleteAccountFollowers(String socialAccountId) {
        logger.info("Getting complete account followers for social account: {}", socialAccountId);
        
        String sql = """
            SELECT 
                sa.username as conta_instagram,
                sa.display_name as nome_conta,
                sa.metrics->>'followers' as total_seguidores_reais,
                ul.name as nome_lista,
                ul.total_users as total_na_lista,
                unnest(ul.usernames) as username_seguidor
            FROM core_business.social_accounts sa
            JOIN automation.user_lists ul ON ul.company_id = sa.company_id
            WHERE sa.id = ?
            AND ul.name ILIKE '%seguidores%'
            AND ul.name NOT ILIKE '%seguindo%'
            ORDER BY ul.name, username_seguidor
            """;
        
        RowMapper<CompleteAccountFollowersResponse> rowMapper = (rs, rowNum) -> 
            new CompleteAccountFollowersResponse(
                rs.getString("conta_instagram"),
                rs.getString("nome_conta"),
                rs.getString("total_seguidores_reais"),
                rs.getString("nome_lista"),
                rs.getInt("total_na_lista"),
                rs.getString("username_seguidor")
            );
        
        try {
            return jdbcTemplate.query(sql, rowMapper, socialAccountId);
        } catch (Exception e) {
            logger.error("Error getting complete account followers for account {}: {}", socialAccountId, e.getMessage(), e);
            throw new RuntimeException("Error retrieving complete account followers", e);
        }
    }
    
    /**
     * Option 4: Count followers
     */
    public List<FollowersCountResponse> getFollowersCount(String socialAccountId) {
        logger.info("Getting followers count for social account: {}", socialAccountId);
        
        String sql = """
            SELECT 
                sa.username as conta,
                ul.name as lista,
                ul.total_users as total_seguidores_lista,
                sa.metrics->>'followers' as total_seguidores_real,
                array_length(ul.usernames, 1) as seguidores_armazenados
            FROM core_business.social_accounts sa
            JOIN automation.user_lists ul ON ul.company_id = sa.company_id
            WHERE sa.id = ?
            AND ul.name ILIKE '%seguidores%'
            AND ul.name NOT ILIKE '%seguindo%'
            """;
        
        RowMapper<FollowersCountResponse> rowMapper = (rs, rowNum) -> 
            new FollowersCountResponse(
                rs.getString("conta"),
                rs.getString("lista"),
                rs.getInt("total_seguidores_lista"),
                rs.getString("total_seguidores_real"),
                rs.getInt("seguidores_armazenados")
            );
        
        try {
            return jdbcTemplate.query(sql, rowMapper, socialAccountId);
        } catch (Exception e) {
            logger.error("Error getting followers count for account {}: {}", socialAccountId, e.getMessage(), e);
            throw new RuntimeException("Error retrieving followers count", e);
        }
    }
}