use votaciones
go

-- DROP PROCEDURE IF EXISTS VOT_get_elecciones_proceso_acta;
-- GO
-- CREATE PROCEDURE VOT_get_elecciones_proceso_acta
--     @varPK INT,
--     @opc INT
-- AS
-- BEGIN
--     SELECT *
--     FROM elecciones_proceso_acta
--     WHERE (@varPK = 0 OR pk_acta = @varPK)
--         AND (@opc = 0 OR estado <> 3);
-- END;
-- GO

DROP PROCEDURE IF EXISTS VOT_get_elecciones_proceso_acta;
GO
CREATE PROCEDURE VOT_get_elecciones_proceso_acta
    @varPK INT,
    @opc INT
AS
BEGIN
    SELECT 
        partido.pk_partido,
        proceso.pk_proceso,
        j.id AS pk_jrv,
        acta_tipo.nombre AS pk_tipo_nombre,
        eleccion_tipo.nombre AS pk_proceso_pk_eleccion_pk_tipo_nombre,
        eleccion_region.nombre AS pk_proceso_pk_eleccion_pk_region_nombre,
        CONCAT(proceso.dia, ' de ', 
               CASE proceso.mes 
                   WHEN '1' THEN 'Enero' 
                   WHEN '2' THEN 'Febrero' 
                   WHEN '3' THEN 'Marzo' 
                   WHEN '4' THEN 'Abril' 
                   WHEN '5' THEN 'Mayo' 
                   WHEN '6' THEN 'Junio' 
                   WHEN '7' THEN 'Julio' 
                   WHEN '8' THEN 'Agosto' 
                   WHEN '9' THEN 'Sprocesotiembre' 
                   WHEN '10' THEN 'Octubre' 
                   WHEN '11' THEN 'Noviembre' 
                   WHEN '12' THEN 'Diciembre' 
               END, ' del ', proceso.anio) AS pk_proceso_fecha,
        proceso.anio AS pk_proceso_anio,
        partido.nombre AS pk_partido_nombre,
        partido.abreviatura AS pk_partido_abreviatura,
        j.codigo AS pk_jrv_codigo
    FROM elecciones_proceso_acta acta
    LEFT JOIN elecciones_proceso_acta_tipo acta_tipo ON acta_tipo.pk_tipo = acta.pk_tipo AND acta_tipo.estado <> 3
    LEFT JOIN elecciones_proceso proceso ON acta.pk_proceso = proceso.pk_proceso AND proceso.estado <> 3
    LEFT JOIN elecciones eleccion ON proceso.pk_eleccion = eleccion.pk_eleccion AND eleccion.estado <> 3
    LEFT JOIN elecciones_tipo eleccion_tipo ON eleccion.pk_tipo = eleccion_tipo.pk_tipo AND eleccion_tipo.estado <> 3
    LEFT JOIN catalogo_regiones eleccion_region ON eleccion.pk_region = eleccion_region.pk_region AND eleccion_region.estado <> 3
    LEFT JOIN partidos partido ON acta.pk_partido = partido.pk_partido AND partido.estado <> 3
    LEFT JOIN jrv j ON j.id = acta.pk_jrv AND j.estado <> 3
    WHERE (@varPK = 0 OR acta.pk_acta = @varPK)
        AND (@opc = 0 OR acta.estado <> 3);
END;
GO



DROP PROCEDURE IF EXISTS VOT_get_elecciones_proceso_acta_tipo;
GO
CREATE PROCEDURE VOT_get_elecciones_proceso_acta_tipo
    @varPK INT,
    @opc INT
AS
BEGIN
    SELECT *
    FROM elecciones_proceso_acta_tipo
    WHERE (@varPK = 0 OR pk_tipo = @varPK)
        AND (@opc = 0 OR estado <> 3);
END;
GO


DROP PROCEDURE IF EXISTS VOT_get_elecciones_proceso_acta_conteo;
GO
CREATE PROCEDURE VOT_get_elecciones_proceso_acta_conteo
    @varPK INT,
    @opc INT
AS
BEGIN
    SELECT *
    FROM elecciones_proceso_acta_conteo
    WHERE (@varPK = 0 OR pk_conteo = @varPK)
        AND (@opc = 0 OR estado <> 3);
END;
GO


DROP PROCEDURE IF EXISTS VOT_get_centros_votaciones;
GO
CREATE PROCEDURE VOT_get_centros_votaciones
    @varPK INT,
    @opc INT
AS
BEGIN
    SELECT
        cv.id,
        cv.nombre,
        cv.ubicacion,
        cv.direccion,
        cv.descripcion,
        cv.estado,
        cv.empresa_id,
        cr1.pk_region AS pk_sector, cr1.nombre AS pk_sector_nombre, cr1.es_urbana AS pk_sector_es_urbana,
        cr2.pk_region AS pk_municipio, cr2.nombre AS pk_municipio_nombre,
        cr3.pk_region AS pk_departamento, cr3.nombre AS pk_departamento_nombre,
        cr4.pk_region AS pk_pais, cr4.nombre AS pk_pais_nombre
    FROM centro_votacion cv
        LEFT JOIN catalogo_regiones cr1 ON cv.pk_region = cr1.pk_region -- Sector
        LEFT JOIN catalogo_regiones cr2 ON cr1.pk_region_mayor = cr2.pk_region AND cr1.pk_tipo = 4 -- Municipio
        LEFT JOIN catalogo_regiones cr3 ON cr2.pk_region_mayor = cr3.pk_region AND cr2.pk_tipo = 3 -- Departamento
        LEFT JOIN catalogo_regiones cr4 ON cr3.pk_region_mayor = cr4.pk_region AND cr3.pk_tipo = 2
    -- País
    WHERE (@varPK = 0 OR cv.id = @varPK)
        AND (@opc = 0 OR cv.estado <> 3);
END;
GO



DROP PROCEDURE IF EXISTS VOT_get_jrv;
GO
CREATE PROCEDURE VOT_get_jrv
    @varPK INT,
    @opc INT
AS
BEGIN
    SELECT
        jrv.id,
        jrv.codigo,
        CASE 
            WHEN sector.nombre IS NOT NULL 
              THEN sector.nombre 
                   + CASE WHEN sector.pk_region_mayor IS NOT NULL THEN ' ' + municipio.nombre ELSE '' END 
                   + ' - ' + cv.nombre
            ELSE cv.nombre
        END AS ubicacion,
        jrv.estado,
        jrv.fecha_hora_creado,
        jrv.centro_votacion_id,
        cv.nombre AS centro_votacion_nombre,
        -- Jerarquía de regiones asociadas al centro de votación
        sector.pk_region AS pk_sector, sector.nombre AS pk_sector_nombre, sector.es_urbana AS pk_sector_es_urbana,
        municipio.pk_region AS pk_municipio, municipio.nombre AS pk_municipio_nombre,
        departamento.pk_region AS pk_departamento, departamento.nombre AS pk_departamento_nombre,
        pais.pk_region AS pk_pais, pais.nombre AS pk_pais_nombre
    FROM jrv jrv
        LEFT JOIN centro_votacion cv ON jrv.centro_votacion_id = cv.id
        LEFT JOIN catalogo_regiones sector ON cv.pk_region = sector.pk_region -- Sector
        LEFT JOIN catalogo_regiones municipio ON sector.pk_region_mayor = municipio.pk_region AND sector.pk_tipo = 4 -- Municipio
        LEFT JOIN catalogo_regiones departamento ON municipio.pk_region_mayor = departamento.pk_region AND municipio.pk_tipo = 3 -- Departamento
        LEFT JOIN catalogo_regiones pais ON departamento.pk_region_mayor = pais.pk_region AND departamento.pk_tipo = 2
    -- País
    WHERE (@varPK = 0 OR jrv.id = @varPK)
        AND (@opc = 0 OR jrv.estado <> 3);
END;
GO















DROP PROCEDURE IF EXISTS dbo.VOT_get_candidatos_x_partido;
GO

CREATE PROCEDURE VOT_get_candidatos_x_partido
    @varPKProceso INT,
    @varPKTipo INT,
    @varPKPartido INT,
    @varPKActa INT,
    @varPKJRV INT, -- Junta Receptora de Votos
    @varOpc INT
AS
BEGIN
    DECLARE 
        @regionSector INT,
        @regionMunicipio INT,
        @regionDepartamento INT,
        @regionPais INT,
        @regionFiltro INT,
        @regionTipoCandidato INT;

    -- Obtener la región SECTOR de la JRV (Tipo 4)
    SELECT @regionSector = cv.pk_region
    FROM jrv jrv
    LEFT JOIN centro_votacion cv ON cv.id = jrv.centro_votacion_id
    WHERE jrv.id = @varPKJRV;

    -- Obtener la región MUNICIPIO (Tipo 3) a la que pertenece el sector
    SELECT @regionMunicipio = reg_mun.pk_region
    FROM catalogo_regiones reg
    LEFT JOIN catalogo_regiones reg_mun ON reg.pk_region_mayor = reg_mun.pk_region
    WHERE reg.pk_region = @regionSector AND reg_mun.pk_tipo = 3;

    -- Obtener la región DEPARTAMENTO (Tipo 2) a la que pertenece el municipio
    SELECT @regionDepartamento = reg_dep.pk_region
    FROM catalogo_regiones reg
    LEFT JOIN catalogo_regiones reg_dep ON reg.pk_region_mayor = reg_dep.pk_region
    WHERE reg.pk_region = @regionMunicipio AND reg_dep.pk_tipo = 2;

    -- Obtener la región PAÍS (Tipo 1) a la que pertenece el departamento
    SELECT @regionPais = reg_pais.pk_region
    FROM catalogo_regiones reg
    LEFT JOIN catalogo_regiones reg_pais ON reg.pk_region_mayor = reg_pais.pk_region
    WHERE reg.pk_region = @regionDepartamento AND reg_pais.pk_tipo = 1;

    -- Obtener el tipo de región desde la tabla candidatos_tipo
    SELECT @regionTipoCandidato = pk_region_tipo
    FROM candidatos_tipo
    WHERE pk_tipo = @varPKTipo;

    -- Determinar la región de filtro de acuerdo al tipo de candidato
    IF @regionTipoCandidato = 1 
        SET @regionFiltro = @regionPais;         -- País
    ELSE IF @regionTipoCandidato = 2 
        SET @regionFiltro = @regionDepartamento; -- Departamento
    ELSE IF @regionTipoCandidato = 3 
        SET @regionFiltro = @regionMunicipio;    -- Municipio
    ELSE 
        SET @regionFiltro = @regionSector;       -- Sector (Default)

    -- Consultar los candidatos que pertenecen a la región determinada
    SELECT 
        cand.pk_candidato,
        cand.pk_tipo,
        cand.nombres,
        cand.apellidos,
        cand.url_foto_papeleta,
        cand.casilla,
        mov.nombre AS movimiento_nombre,
        mov.url_foto AS movimiento_url_foto,
        mov.pk_movimiento,
        conteo.cantidad AS votos
    FROM candidatos cand
    LEFT JOIN movimientos mov ON cand.pk_movimiento = mov.pk_movimiento AND mov.estado <> 3
    LEFT JOIN partidos part ON mov.pk_partido = part.pk_partido AND part.estado <> 3
    LEFT JOIN elecciones_proceso_acta_conteo conteo ON conteo.pk_acta = @varPKActa AND conteo.pk_candidato = cand.pk_candidato
    LEFT JOIN candidatos_tipo ct ON cand.pk_tipo = ct.pk_tipo
    WHERE cand.pk_eleccion_proceso = @varPKProceso
        AND cand.pk_tipo = @varPKTipo
        AND mov.pk_partido = @varPKPartido
        AND (@varOpc = 0 OR cand.estado <> 3)
        -- Filtrar por la región determinada
        AND cand.pk_region = @regionFiltro
    ORDER BY cand.casilla ASC;
END;
GO















-- PARA TRAER LOS ROLES DE JRV PARA UNA ACTA, SOLO ESTA EN TESTING PORQUE EN PRODUCCION DABA ERROR
-- 1 Procedure JRV_get_roles_x_mesa. Invalid column name 'id_tipo_rol'. SQL.sql 29 12 
-- en esta parte ON r.id_tipo_rol = tr.id

DROP PROCEDURE IF EXISTS JRV_get_roles_x_mesa;
GO
CREATE PROCEDURE JRV_get_roles_x_mesa
    @jrv_id INT  -- Parámetro para filtrar por JRV específica
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        aj.id AS AsignacionID,
        j.codigo AS CodigoJRV,
        j.carga,
        r.nombre_rol AS Rol,
        tr.nombre_tipo_rol AS TipoRol,
        aj.id_asignacion_padre AS PadreAsignacionID,
        m.nombres + ' ' + m.apellidos AS Miembro,
        m.dni AS MiembroDNI,
        aj.rol_jrv_id,
        aj.fecha_asignacion,
        aj.estado,
        aj.observaciones
    FROM dbo.asignacion_jrv aj
    JOIN dbo.jrv j 
      ON aj.jrv_id = j.id
    JOIN dbo.rol_jrv r 
      ON aj.rol_jrv_id = r.id
    -- Se une con tipo_rol usando id_tipo_rol
    JOIN dbo.tipo_rol tr 
      ON r.id_tipo_rol = tr.id
    LEFT JOIN dbo.miembros m 
      ON aj.id_miembro = m.id_miembro
    WHERE aj.estado <> 3
      AND (@jrv_id = 0 OR aj.jrv_id = @jrv_id)  -- Filtrar por JRV específica o mostrar todas si es 0
    ORDER BY aj.id;
END;
GO


DROP PROCEDURE IF EXISTS VOT_get_elecciones_proceso_acta_control;
GO
CREATE PROCEDURE VOT_get_elecciones_proceso_acta_control
    @varUsuario INT,  -- Usuario que hace la solicitud
    @varPkTipo INT,
    @varPkProceso INT,
    @varPkPartido INT,
    @varPkJrv INT,
    @opc INT
AS
BEGIN
    DECLARE @result NVARCHAR(200), @save INT, @usuario INT;

    -- Verificar si existen registros que coinciden con los filtros
    SELECT TOP 1 @usuario = pk_user
    FROM elecciones_proceso_acta_control
    WHERE (@varPkTipo = 0 OR pk_tipo = @varPkTipo)
        AND (@varPkProceso = 0 OR pk_proceso = @varPkProceso)
        AND (@varPkPartido = 0 OR pk_partido = @varPkPartido)
        AND (@varPkJrv = 0 OR pk_jrv = @varPkJrv)
        AND (@opc = 0 OR estado <> 3);

    -- Si no se encontró ningún registro o si el registro pertenece al mismo usuario
    IF @usuario IS NULL OR @usuario = @varUsuario
    BEGIN
        SET @save = 1;
        SET @result = 'No se encontraron registros o el registro pertenece al mismo usuario.';
        
        -- Si la opción es 2 y no hay registros existentes
        IF @opc = 2 AND @usuario IS NULL
        BEGIN
            -- Llamar al SP para agregar el registro
            EXEC VOT_insert_update_elecciones_proceso_acta_control 
                0, 
                @varUsuario = @varUsuario, 
                @varPkTipo = @varPkTipo, 
                @varPkProceso = @varPkProceso, 
                @varPkPartido = @varPkPartido, 
                @varPkJrv = @varPkJrv, 
                @varEstadoControl = 1,  -- Establecer el estado_control como 1 (si es necesario)
                @varOpc = 1;  -- La opción 1 para inserción
        END
    END
    ELSE
    BEGIN
        SET @save = 0;
        SET @result = 'Esta acta está siendo editada por otro usuario.';
    END

    -- Devolver el resultado
    SELECT @save AS 'save', @result AS result;
END;
GO







DROP PROCEDURE IF EXISTS sp_get_votos_candidatos;
GO

CREATE PROCEDURE sp_get_votos_candidatos @varPkTipoCandidato INT = NULL,
@varPkMovimiento INT = NULL,
@varPkPartido INT = NULL,
@varPkRegion INT = NULL,
@varTop INT = 100
AS
BEGIN
  SET NOCOUNT ON;

  SELECT TOP (@varTop)
    candidato_partido.pk_partido
   ,candidato_movimiento.pk_movimiento
   ,candidato.pk_candidato
   ,candidato.pk_tipo
   ,candidato.nombres AS candidato_nombres
   ,candidato.apellidos AS candidato_apellidos
   ,candidato_movimiento.nombre AS movimiento_nombre
   ,candidato_partido.nombre AS partido_nombre
   ,candidato_movimiento.hex_color
   ,SUM(conteo.cantidad) AS votos
   ,ranking.ranking AS ranking_general
  -- JOINS
  FROM elecciones_proceso_acta_conteo conteo
  LEFT JOIN candidatos candidato
    ON candidato.pk_candidato = conteo.pk_candidato
  LEFT JOIN movimientos candidato_movimiento
    ON candidato_movimiento.pk_movimiento = candidato.pk_movimiento
  LEFT JOIN partidos candidato_partido
    ON candidato_partido.pk_partido = candidato_movimiento.pk_partido
  -- TRAER EL RANKING GENERAL
  JOIN (SELECT
      RANK() OVER (ORDER BY SUM(conteo.cantidad) DESC) AS ranking
     ,SUM(conteo.cantidad) AS votos
     ,candidato.pk_candidato
    FROM elecciones_proceso_acta_conteo conteo
    LEFT JOIN candidatos candidato
      ON candidato.pk_candidato = conteo.pk_candidato
    LEFT JOIN movimientos candidato_movimiento
      ON candidato_movimiento.pk_movimiento = candidato.pk_movimiento
    LEFT JOIN partidos candidato_partido
      ON candidato_partido.pk_partido = candidato_movimiento.pk_partido
    WHERE conteo.pk_conteo <> 3
    AND (@varPkTipoCandidato IS NULL
    OR candidato.pk_tipo = @varPkTipoCandidato)          -- Si hay TIPO CANDIDATO se filtra
    AND (@varPkPartido IS NULL
    OR candidato_partido.pk_partido = @varPkPartido)   -- Si hay PARTIDO se filtra
    -- AND (@varPkMovimiento IS NULL
    -- OR candidato.pk_movimiento = @varPkMovimiento)  -- Si hay MOVIMIENTO se filtra
    AND (@varPkRegion IS NULL
    OR candidato.pk_region = @varPkRegion)  -- Si hay REGIÓN se filtra
    GROUP BY candidato.pk_candidato) AS ranking
    -- FIN DEL RANKING GENERAL
    ON ranking.pk_candidato = candidato.pk_candidato

  -- WHERE
  WHERE conteo.pk_conteo <> 3
  AND (@varPkTipoCandidato IS NULL
  OR candidato.pk_tipo = @varPkTipoCandidato)          -- Si hay TIPO CANDIDATO se filtra
  AND (@varPkPartido IS NULL
  OR candidato_partido.pk_partido = @varPkPartido)   -- Si hay PARTIDO se filtra
  AND (@varPkMovimiento IS NULL
  OR candidato.pk_movimiento = @varPkMovimiento)  -- Si hay MOVIMIENTO se filtra
  AND (@varPkRegion IS NULL
  OR candidato.pk_region = @varPkRegion)  -- Si hay REGIÓN se filtra

  GROUP BY candidato.pk_candidato
          ,candidato.pk_tipo
          ,candidato_movimiento.pk_movimiento
          ,candidato_partido.pk_partido
          ,candidato.nombres
          ,candidato.apellidos
          ,candidato_partido.nombre
          ,candidato_movimiento.nombre
          ,candidato_movimiento.hex_color
          ,ranking.ranking

  ORDER BY ranking.ranking
END;
GO