using Dapper;
using Npgsql;
using OpenCand.Core.Models;

namespace OpenCand.Repository
{
    public class CandidatoRepository : BaseRepository
    {
        public CandidatoRepository(IConfiguration configuration) : base(configuration)
        {
        }

        public async Task<List<Candidato>> SearchCandidatosAsync(string query)
        {
            using (var connection = new NpgsqlConnection(ConnectionString))
            {
                return (await connection.QueryAsync<Candidato>(@"
                    SELECT *, 
                          CASE
                            WHEN lower(apelido) = lower(@query) THEN 0              -- apelido Exact match (case-insensitive)
                            WHEN lower(apelido) LIKE lower(@query) || '%' THEN 1    -- apelido Starts with match (case-insensitive)
                            WHEN lower(apelido) LIKE '%' || lower(@query) THEN 2    -- apelido Contains anywhere match (case-insensitive)
                            WHEN lower(nome) = lower(@query) THEN 0                 -- nome Exact match (case-insensitive)
                            WHEN lower(nome) LIKE lower(@query) || '%' THEN 1       -- nome Starts with match (case-insensitive)
                            WHEN lower(nome) LIKE '%' || lower(@query) THEN 2       -- nome Contains anywhere match (case-insensitive)
                            WHEN cpf = @query THEN 0                                -- cpf Exact match for CPF
                            WHEN cpf LIKE @query || '%' THEN 1                      -- cpf Starts with match for CPF
                            WHEN cpf LIKE '%' || @query THEN 2                      -- cpf Contains anywhere match for CPF
                            ELSE 3
                          END AS name_rank
                    FROM candidato
                    WHERE apelido ILIKE '%' || @query || '%' OR
                          nome ILIKE '%' || @query || '%' OR
                          cpf ILIKE '%' || @query || '%'
                    ORDER BY name_rank,
                            length(nome) ASC
                    LIMIT 10;",
                    new { query })).AsList();
            }
        }

        public async Task<Candidato?> GetCandidatoAsync(Guid idcandidato)
        {
            using (var connection = new NpgsqlConnection(ConnectionString))
            {
                return await connection.QueryFirstOrDefaultAsync<Candidato>(@"
                    SELECT * FROM candidato
                    WHERE idcandidato = @idcandidato;",
                    new { idcandidato });
            }
        }

        public async Task<string?> GetCandidatoCpfAsync(Guid idcandidato)
        {
            using (var connection = new NpgsqlConnection(ConnectionString))
            {
                return await connection.QueryFirstOrDefaultAsync<string>(@"
                    SELECT cpf FROM candidato
                    WHERE idcandidato = @idcandidato;",
                    new { idcandidato });
            }
        }

        public async Task<List<CandidatoMapping>?> GetCandidatoMappingById(Guid idcandidato)
        {
            using (var connection = new NpgsqlConnection(ConnectionString))
            {
                var query = @"
                    SELECT * FROM candidato_mapping
                    WHERE idcandidato = @idcandidato";
                return (await connection.QueryAsync<CandidatoMapping>(query, new { idcandidato })).AsList();
            }
        }

        public async Task<Partido?> GetPartidoBySigla(string sigla)
        {
            using (var connection = new NpgsqlConnection(ConnectionString))
            {
                var query = @"
                    SELECT * FROM partido
                    WHERE sigla = @sigla";
                return await connection.QueryFirstOrDefaultAsync<Partido>(query, new { sigla });
            }
        }

        public async Task<List<RedeSocial>?> GetCandidatoRedeSocialById(Guid idcandidato)
        {
            using (var connection = new NpgsqlConnection(ConnectionString))
            {
                var query = @"
                    SELECT * FROM rede_social
                    WHERE idcandidato = @idcandidato";
                return (await connection.QueryAsync<RedeSocial>(query, new { idcandidato })).AsList();
            }
        }
    }
}