using Dapper;
using Microsoft.AspNetCore.Connections;
using MieSystem.Data.Interfaces;
using MieSystem.Models;

namespace MieSystem.Data.Repositories
{
    public class AsistenciaRepository : IAsistenciaRepository
    {

        private readonly IDatabaseConnectionFactory _connectionFactory;

        public AsistenciaRepository(IDatabaseConnectionFactory databaseConnectionFactory)
        {
            _connectionFactory = databaseConnectionFactory;
        }

        #region CRUD Básico

        public async Task<Asistencia?> GetByIdAsync(int id)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            SELECT 
                id,
                expediente_id as ExpedienteId,
                fecha,
                estado,
                hora_entrada as HoraEntrada,
                hora_salida as HoraSalida,
                observaciones,
                fecha_registro as FechaRegistro,
                usuario_registro as UsuarioRegistro
            FROM asistencia 
            WHERE id = @Id";

            return await connection.QueryFirstOrDefaultAsync<Asistencia>(sql, new { Id = id });
        }

        public async Task<IEnumerable<Asistencia?>> GetAllAsync()
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            SELECT 
                id,
                expediente_id as ExpedienteId,
                fecha,
                estado,
                hora_entrada as HoraEntrada,
                hora_salida as HoraSalida,
                observaciones,
                fecha_registro as FechaRegistro,
                usuario_registro as UsuarioRegistro
            FROM asistencia 
            ORDER BY fecha DESC, expediente_id";

            return await connection.QueryAsync<Asistencia>(sql);
        }

        public async Task<int> CreateAsync(Asistencia asistencia)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            INSERT INTO asistencia (
                expediente_id,
                fecha,
                estado,
                hora_entrada,
                hora_salida,
                observaciones,
                usuario_registro
            ) VALUES (
                @ExpedienteId,
                @Fecha,
                @Estado,
                @HoraEntrada,
                @HoraSalida,
                @Observaciones,
                @UsuarioRegistro
            )
            RETURNING id";

            var parameters = new
            {
                asistencia.ExpedienteId,
                Fecha = asistencia.Fecha.Date,
                asistencia.Estado,
                asistencia.HoraEntrada,
                asistencia.HoraSalida,
                asistencia.Observaciones,
                asistencia.UsuarioRegistro
            };

            return await connection.ExecuteScalarAsync<int>(sql, parameters);
        }

        public async Task<bool> UpdateAsync(Asistencia asistencia)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            UPDATE asistencia SET
                estado = @Estado,
                hora_entrada = @HoraEntrada,
                hora_salida = @HoraSalida,
                observaciones = @Observaciones,
                usuario_registro = @UsuarioRegistro
            WHERE id = @Id";

            var parameters = new
            {
                asistencia.Id,
                asistencia.Estado,
                asistencia.HoraEntrada,
                asistencia.HoraSalida,
                asistencia.Observaciones,
                asistencia.UsuarioRegistro
            };

            var affectedRows = await connection.ExecuteAsync(sql, parameters);
            return affectedRows > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = "DELETE FROM asistencia WHERE id = @Id";
            var affected = await connection.ExecuteAsync(sql, new { Id = id });

            return affected > 0;
        }

        #endregion

        #region Métodos específicos de la interfaz

        public async Task<bool> GuardarAsistenciaAsync(Asistencia asistencia)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            // Usar UPSERT (INSERT ... ON CONFLICT ... UPDATE)
            var sql = @"
            INSERT INTO asistencia (
                expediente_id,
                fecha,
                estado,
                hora_entrada,
                hora_salida,
                observaciones,
                usuario_registro
            ) VALUES (
                @ExpedienteId,
                @Fecha,
                @Estado,
                @HoraEntrada,
                @HoraSalida,
                @Observaciones,
                @UsuarioRegistro
            )
            ON CONFLICT (expediente_id, fecha) 
            DO UPDATE SET
                estado = EXCLUDED.estado,
                hora_entrada = EXCLUDED.hora_entrada,
                hora_salida = EXCLUDED.hora_salida,
                observaciones = EXCLUDED.observaciones,
                usuario_registro = EXCLUDED.usuario_registro,
                fecha_registro = CURRENT_TIMESTAMP
            RETURNING id";

            var parameters = new
            {
                asistencia.ExpedienteId,
                Fecha = asistencia.Fecha.Date,
                asistencia.Estado,
                asistencia.HoraEntrada,
                asistencia.HoraSalida,
                asistencia.Observaciones,
                asistencia.UsuarioRegistro
            };

            var result = await connection.ExecuteScalarAsync<int?>(sql, parameters);
            return result.HasValue;
        }

        public async Task<IEnumerable<Asistencia>> GetByExpedienteAsync(int expedienteId, DateTime? fechaDesde = null, DateTime? fechaHasta = null)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            SELECT 
                id,
                expediente_id as ExpedienteId,
                fecha,
                estado,
                hora_entrada as HoraEntrada,
                hora_salida as HoraSalida,
                observaciones,
                fecha_registro as FechaRegistro,
                usuario_registro as UsuarioRegistro
            FROM asistencia 
            WHERE expediente_id = @ExpedienteId";

            var parameters = new DynamicParameters();
            parameters.Add("ExpedienteId", expedienteId);

            if (fechaDesde.HasValue)
            {
                sql += " AND fecha >= @FechaDesde";
                parameters.Add("FechaDesde", fechaDesde.Value.Date);
            }

            if (fechaHasta.HasValue)
            {
                sql += " AND fecha <= @FechaHasta";
                parameters.Add("FechaHasta", fechaHasta.Value.Date);
            }

            sql += " ORDER BY fecha DESC";

            return await connection.QueryAsync<Asistencia>(sql, parameters);
        }

        public async Task<IEnumerable<Asistencia>> GetAsistenciasPorMesAsync(int año, int mes)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            SELECT 
                id,
                expediente_id as ExpedienteId,
                fecha,
                estado,
                hora_entrada as HoraEntrada,
                hora_salida as HoraSalida,
                observaciones,
                fecha_registro as FechaRegistro,
                usuario_registro as UsuarioRegistro
            FROM asistencia 
            WHERE EXTRACT(YEAR FROM fecha) = @Año 
                AND EXTRACT(MONTH FROM fecha) = @Mes
            ORDER BY fecha, expediente_id";

            return await connection.QueryAsync<Asistencia>(sql, new { Año = año, Mes = mes });
        }

        public async Task<bool> GuardarAsistenciasMasivasAsync(IEnumerable<Asistencia> asistencias)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            INSERT INTO asistencia (
                expediente_id,
                fecha,
                estado,
                hora_entrada,
                hora_salida,
                observaciones,
                usuario_registro
            ) VALUES (
                @ExpedienteId,
                @Fecha,
                @Estado,
                @HoraEntrada,
                @HoraSalida,
                @Observaciones,
                @UsuarioRegistro
            )
            ON CONFLICT (expediente_id, fecha) 
            DO UPDATE SET
                estado = EXCLUDED.estado,
                hora_entrada = EXCLUDED.hora_entrada,
                hora_salida = EXCLUDED.hora_salida,
                observaciones = EXCLUDED.observaciones,
                usuario_registro = EXCLUDED.usuario_registro,
                fecha_registro = CURRENT_TIMESTAMP";

            var exitosas = 0;
            foreach (var asistencia in asistencias)
            {
                var parameters = new
                {
                    asistencia.ExpedienteId,
                    Fecha = asistencia.Fecha.Date,
                    asistencia.Estado,
                    asistencia.HoraEntrada,
                    asistencia.HoraSalida,
                    asistencia.Observaciones,
                    asistencia.UsuarioRegistro
                };

                var affected = await connection.ExecuteAsync(sql, parameters);
                if (affected > 0) exitosas++;
            }

            return exitosas > 0;
        }

        public async Task<bool> EliminarAsistenciaAsync(int id)
        {
            return await DeleteAsync(id);
        }

        public async Task<bool> EliminarAsistenciasPorFechaAsync(DateTime fecha)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = "DELETE FROM asistencia WHERE fecha = @Fecha";
            var affected = await connection.ExecuteAsync(sql, new { Fecha = fecha.Date });

            return affected > 0;
        }

        public async Task<EstadisticasMes> GetEstadisticasMesAsync(int año, int mes)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
SELECT 
    COALESCE(COUNT(*), 0) as Total,
    COALESCE(COUNT(CASE WHEN estado = 'P' THEN 1 END), 0) as Presentes,
    COALESCE(COUNT(CASE WHEN estado = 'T' THEN 1 END), 0) as Tardes,
    COALESCE(COUNT(CASE WHEN estado = 'F' THEN 1 END), 0) as Faltas,
    COALESCE(ROUND(
        COUNT(CASE WHEN estado = 'P' THEN 1 END) * 100.0 / 
        NULLIF(COUNT(*), 0), 
        2
    ), 0) as PorcentajePresentes,
    COALESCE(ROUND(
        COUNT(CASE WHEN estado = 'T' THEN 1 END) * 100.0 / 
        NULLIF(COUNT(*), 0), 
        2
    ), 0) as PorcentajeTardes,
    COALESCE(ROUND(
        COUNT(CASE WHEN estado = 'F' THEN 1 END) * 100.0 / 
        NULLIF(COUNT(*), 0), 
        2
    ), 0) as PorcentajeFaltas
FROM asistencia 
WHERE EXTRACT(YEAR FROM fecha) = @Año 
    AND EXTRACT(MONTH FROM fecha) = @Mes";

            var result = await connection.QueryFirstOrDefaultAsync<EstadisticasMes>(sql, new { Año = año, Mes = mes });

            // Siempre devolver un objeto, incluso si es nulo
            return result ?? new EstadisticasMes();
        }



        public async Task<Dictionary<int, decimal>> GetPorcentajesAsistenciaAsync(int año, int mes)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            SELECT 
                expediente_id,
                ROUND(
                    COUNT(CASE WHEN estado = 'P' THEN 1 END) * 100.0 / 
                    NULLIF(COUNT(*), 0), 
                    2
                ) as porcentaje_asistencia
            FROM asistencia 
            WHERE EXTRACT(YEAR FROM fecha) = @Año 
                AND EXTRACT(MONTH FROM fecha) = @Mes
            GROUP BY expediente_id";

            var resultados = await connection.QueryAsync<(int, decimal)>(sql, new { Año = año, Mes = mes });

            return resultados.ToDictionary(
                r => r.Item1,
                r => r.Item2
            );
        }

        #endregion

        #region Métodos adicionales útiles

        public async Task<bool> ExisteAsistenciaAsync(int expedienteId, DateTime fecha)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = "SELECT COUNT(1) FROM asistencia WHERE expediente_id = @ExpedienteId AND fecha = @Fecha";
            var count = await connection.ExecuteScalarAsync<int>(sql, new
            {
                ExpedienteId = expedienteId,
                Fecha = fecha.Date
            });

            return count > 0;
        }

        public async Task<IEnumerable<Asistencia>> GetAsistenciasPorRangoAsync(DateTime fechaDesde, DateTime fechaHasta)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = @"
            SELECT 
                id,
                expediente_id as ExpedienteId,
                fecha,
                estado,
                hora_entrada as HoraEntrada,
                hora_salida as HoraSalida,
                observaciones,
                fecha_registro as FechaRegistro,
                usuario_registro as UsuarioRegistro
            FROM asistencia 
            WHERE fecha BETWEEN @FechaDesde AND @FechaHasta
            ORDER BY fecha, expediente_id";

            return await connection.QueryAsync<Asistencia>(sql, new
            {
                FechaDesde = fechaDesde.Date,
                FechaHasta = fechaHasta.Date
            });
        }

        public async Task<int> GetTotalAsistenciasPorExpedienteAsync(int expedienteId)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = "SELECT COUNT(*) FROM asistencia WHERE expediente_id = @ExpedienteId";
            return await connection.ExecuteScalarAsync<int>(sql, new { ExpedienteId = expedienteId });
        }

        public async Task<int> GetAsistenciasPresentesPorExpedienteAsync(int expedienteId)
        {
            using var connection = await _connectionFactory.CreateConnectionAsync();

            var sql = "SELECT COUNT(*) FROM asistencia WHERE expediente_id = @ExpedienteId AND estado = 'P'";
            return await connection.ExecuteScalarAsync<int>(sql, new { ExpedienteId = expedienteId });
        }

        #endregion
    }

}