USE testing_votaciones
GO

-- @varOpc INT = 1 para INSERT, 2 para UPDATE

-- # 1
DROP PROCEDURE IF EXISTS CAT_insert_update_regiones;
GO
CREATE PROCEDURE CAT_insert_update_regiones
    @varPkRegion INT OUTPUT,
    @varNombre NVARCHAR(100),
    @varPkTipo INT,
    @varEsUrbana INT,
    @varPkRegionMayor INT,
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- MAYÚSCULAS
    SET @varNombre = UPPER(@varNombre);

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM catalogo_regiones
    WHERE nombre = @varNombre
        AND pk_tipo = @varPkTipo
        AND estado <> 3
        AND (@varOpc = 1 OR pk_region <> @varPkRegion);

    IF @existe > 0
    BEGIN
        -- ADICIONALES
        DECLARE @tiporegion_nombre NVARCHAR(200);

        SELECT @tiporegion_nombre = nombre
        FROM catalogo_regiones_tipo
        WHERE pk_tipo = @varPkTipo;

        SET @result = CONCAT('El nombre de ese(a) ', @tiporegion_nombre ,' ya está en uso.');
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO catalogo_regiones
            (nombre, pk_tipo, es_urbana, pk_region_mayor, creado_por)
        VALUES
            (@varNombre, @varPkTipo, @varEsUrbana, @varPkRegionMayor, @varUsuario);

        SET @varPkRegion = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE catalogo_regiones
        SET nombre = @varNombre,
            pk_tipo = @varPkTipo,
            es_urbana = @varEsUrbana,
            pk_region_mayor = @varPkRegionMayor,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_region = @varPkRegion;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkRegion = @varPkRegion;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkRegion AS pk;
END;
GO

-- # 2
DROP PROCEDURE IF EXISTS CAT_insert_update_regiones_tipo;
GO
CREATE PROCEDURE CAT_insert_update_regiones_tipo
    @varPkTipo INT OUTPUT,
    @varNombre NVARCHAR(100),
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- MAYÚSCULAS
    SET @varNombre = UPPER(@varNombre);

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM catalogo_regiones_tipo
    WHERE nombre = @varNombre
        AND estado <> 3
        AND (@varOpc = 1 OR pk_tipo <> @varPkTipo);

    IF @existe > 0
    BEGIN
        SET @result = 'El nombre de ese tipo de región ya está en uso.';
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO catalogo_regiones_tipo
            (nombre, creado_por)
        VALUES
            (@varNombre, @varUsuario);

        SET @varPkTipo = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE catalogo_regiones_tipo
        SET nombre = @varNombre,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_tipo = @varPkTipo;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkTipo = @varPkTipo;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkTipo AS pk;
END;
GO

-- # 3
DROP PROCEDURE IF EXISTS VOT_insert_update_partidos;
GO
CREATE PROCEDURE VOT_insert_update_partidos
    @varPkPartido INT OUTPUT,
    @varNombre NVARCHAR(100),
    @varAbreviatura NVARCHAR(100),
    @varPkRegion INT,
    @varUrlFoto NVARCHAR(255),
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- MAYÚSCULAS
    SET @varNombre = UPPER(@varNombre);

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM partidos
    WHERE nombre = @varNombre
        AND pk_region = @varPkRegion
        AND estado <> 3
        AND (@varOpc = 1 OR pk_partido <> @varPkPartido);

    IF @existe > 0
    BEGIN
        -- ADICIONALES
        DECLARE @region_nombre NVARCHAR(200), @tiporegion_nombre NVARCHAR(200);

        SELECT @region_nombre = r.nombre, @tiporegion_nombre = t.nombre
        FROM catalogo_regiones r
            LEFT JOIN catalogo_regiones_tipo t ON t.pk_tipo = r.pk_tipo
        WHERE r.pk_region = @varPkRegion;

        SET @result = CONCAT('El nombre de ese partido ya está en uso en "', @region_nombre, ' (', @tiporegion_nombre,')".');
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO partidos
            (nombre, abreviatura, pk_region, url_foto, creado_por)
        VALUES
            (@varNombre, @varAbreviatura, @varPkRegion, @varUrlFoto, @varUsuario);

        SET @varPkPartido = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE partidos
        SET nombre = @varNombre,
            abreviatura = @varAbreviatura,
            pk_region = @varPkRegion,
            url_foto = @varUrlFoto,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_partido = @varPkPartido;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkPartido = @varPkPartido;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkPartido AS pk;
END;
GO

-- # 4
DROP PROCEDURE IF EXISTS VOT_insert_update_movimientos;
GO
CREATE PROCEDURE VOT_insert_update_movimientos
    @varPkMovimiento INT OUTPUT,
    @varNombre NVARCHAR(100),
    @varPkPartido INT,
    @varPartido INT,
    @varUrlFoto NVARCHAR(255),
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- MAYÚSCULAS
    SET @varNombre = UPPER(@varNombre);

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM movimientos
    WHERE nombre = @varNombre
        AND pk_partido = @varPkPartido
        AND estado <> 3
        AND (@varOpc = 1 OR pk_movimiento <> @varPkMovimiento);

    IF @existe > 0
    BEGIN
        SET @result = 'El nombre de ese movimiento ya está en uso en el mismo partido político.';
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1
    BEGIN
        INSERT INTO movimientos
            (nombre, pk_partido, partido, url_foto, creado_por)
        VALUES
            (@varNombre, @varPkPartido, @varPartido, @varUrlFoto, @varUsuario);

        SET @varPkMovimiento = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE movimientos
        SET nombre = @varNombre,
            pk_partido = @varPkPartido,
            partido = @varPartido,
            url_foto = @varUrlFoto,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_movimiento = @varPkMovimiento;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkMovimiento = @varPkMovimiento;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkMovimiento AS pk;
END;
GO

-- # 5
DROP PROCEDURE IF EXISTS VOT_insert_update_candidatos_tipo;
GO
CREATE PROCEDURE VOT_insert_update_candidatos_tipo
    @varPkTipo INT OUTPUT,
    @varNombre NVARCHAR(100),
    @varPkRegionTipo INT,
    @varPkTipoMayor INT,
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- MAYÚSCULAS
    SET @varNombre = UPPER(@varNombre);

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM candidatos_tipo
    WHERE nombre = @varNombre -- Solo con el nombre es motivo suficiente para no permitir considero yo
        AND estado <> 3
        AND (@varOpc = 1 OR pk_tipo <> @varPkTipo);

    IF @existe > 0
    BEGIN
        SET @result = 'El nombre de ese tipo de candidato ya está en uso.';
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO candidatos_tipo
            (nombre, pk_region_tipo, pk_tipo_mayor, creado_por)
        VALUES
            (@varNombre, @varPkRegionTipo, @varPkTipoMayor, @varUsuario);

        SET @varPkTipo = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE candidatos_tipo
        SET nombre = @varNombre,
            pk_region_tipo = @varPkRegionTipo,
            pk_tipo_mayor = @varPkTipoMayor,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_tipo = @varPkTipo;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkTipo = @varPkTipo;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkTipo AS pk;
END;
GO

-- # 6
DROP PROCEDURE IF EXISTS VOT_insert_update_elecciones;
GO
CREATE PROCEDURE VOT_insert_update_elecciones
    @varPkEleccion INT OUTPUT,
    @varPkRegion INT,
    @varPkTipo INT,
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM elecciones
    WHERE pk_region = @varPkRegion
        AND pk_tipo = @varPkTipo
        AND estado <> 3
        AND (@varOpc = 1 OR pk_eleccion <> @varPkEleccion);

    IF @existe > 0
    BEGIN
        -- ADICIONALES
        DECLARE @tipoeleccion_nombre NVARCHAR(200), @region_nombre NVARCHAR(200), @tiporegion_nombre NVARCHAR(200);

        SELECT @tipoeleccion_nombre = nombre
        FROM elecciones_tipo
        WHERE pk_tipo = @varPkTipo;

        SELECT @region_nombre = r.nombre, @tiporegion_nombre = t.nombre
        FROM catalogo_regiones r
            LEFT JOIN catalogo_regiones_tipo t ON t.pk_tipo = r.pk_tipo
        WHERE r.pk_region = @varPkRegion;

        SET @result = CONCAT('Ya existen las elecciones "', @tipoeleccion_nombre,' - ', @region_nombre,' (', @tiporegion_nombre,')".');
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO elecciones
            (pk_region, pk_tipo, creado_por)
        VALUES
            (@varPkRegion, @varPkTipo, @varUsuario);

        SET @varPkEleccion = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE elecciones
        SET pk_region = @varPkRegion,
            pk_tipo = @varPkTipo,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_eleccion = @varPkEleccion;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkEleccion = @varPkEleccion;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkEleccion AS pk;
END;
GO

-- # 7
DROP PROCEDURE IF EXISTS VOT_insert_update_elecciones_tipo;
GO
CREATE PROCEDURE VOT_insert_update_elecciones_tipo
    @varPkTipo INT OUTPUT,
    @varNombre NVARCHAR(100),
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- MAYÚSCULAS
    SET @varNombre = UPPER(@varNombre);

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM elecciones_tipo
    WHERE nombre = @varNombre
        AND estado <> 3
        AND (@varOpc = 1 OR pk_tipo <> @varPkTipo);

    IF @existe > 0
    BEGIN
        SET @result = 'El nombre de este tipo de elecciones ya está en uso.';
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO elecciones_tipo
            (nombre, creado_por)
        VALUES
            (@varNombre, @varUsuario);

        SET @varPkTipo = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE elecciones_tipo
        SET nombre = @varNombre,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_tipo = @varPkTipo;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkTipo = @varPkTipo;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkTipo AS pk;
END;
GO

-- # 8
DROP
PROCEDURE IF EXISTS VOT_insert_update_elecciones_candidatos_x_region;
GO
CREATE PROCEDURE VOT_insert_update_elecciones_candidatos_x_region
    @varPkConfiguracion INT OUTPUT,
    @varPkEleccion INT,
    @varPkRegion INT,
    @varPkCandidatoTipo INT,
    @varCantidad INT,
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM elecciones_candidatos_x_region
    WHERE pk_eleccion = @varPkEleccion
        AND pk_region = @varPkRegion
        AND pk_candidato_tipo = @varPkCandidatoTipo
        AND estado <> 3
        AND (@varOpc = 1 OR pk_configuracion <> @varPkConfiguracion);

    IF @existe > 0
    BEGIN
        -- ADICIONALES
        DECLARE @candidatotipo_nombre NVARCHAR(200), 
        @candidatoregion_nombre NVARCHAR(200), 
        @candidatoregiontipo_nombre NVARCHAR(200), 
        @elecciontipo_nombre NVARCHAR(200), 
        @eleccion_region_nombre NVARCHAR(200);

        SELECT
            @candidatotipo_nombre = ct.nombre,
            @candidatoregion_nombre = cr.nombre,
            @candidatoregiontipo_nombre = crt.nombre,
            @elecciontipo_nombre = et.nombre,
            @eleccion_region_nombre = er.nombre
        FROM elecciones e
            LEFT JOIN candidatos_tipo ct ON ct.pk_tipo = @varPkCandidatoTipo
            LEFT JOIN catalogo_regiones cr ON cr.pk_region = @varPkRegion
            LEFT JOIN catalogo_regiones_tipo crt ON crt.pk_tipo = cr.pk_tipo
            LEFT JOIN elecciones_tipo et ON et.pk_tipo = e.pk_tipo
            LEFT JOIN catalogo_regiones er ON er.pk_region = e.pk_region
        WHERE e.pk_eleccion = @varPkEleccion;

        SET @result = CONCAT(
            'Ya existe la regla de "', @candidatotipo_nombre, '(s)',
            ' para ', @candidatoregion_nombre, ' (', @candidatoregiontipo_nombre, ')',
            ' en las elecciones ', @elecciontipo_nombre, ' ', @eleccion_region_nombre, '".'
        );

        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO elecciones_candidatos_x_region
            (pk_eleccion, pk_region, pk_candidato_tipo, cantidad, creado_por)
        VALUES
            (@varPkEleccion, @varPkRegion, @varPkCandidatoTipo, @varCantidad, @varUsuario);

        SET @varPkConfiguracion = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE elecciones_candidatos_x_region
        SET pk_eleccion = @varPkEleccion,
            pk_region = @varPkRegion,
            pk_candidato_tipo = @varPkCandidatoTipo,
            cantidad = @varCantidad,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_configuracion = @varPkConfiguracion;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkConfiguracion = @varPkConfiguracion;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkConfiguracion AS pk;
END;
GO

-- # 9
DROP PROCEDURE IF EXISTS VOT_insert_update_elecciones_proceso;
GO
CREATE PROCEDURE VOT_insert_update_elecciones_proceso
    @varPkProceso INT OUTPUT,
    @varPkEleccion INT,
    @varAnio INT,
    @varMes NVARCHAR(50),
    @varDia INT,
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM elecciones_proceso
    WHERE pk_eleccion = @varPkEleccion
        AND anio = @varAnio
        AND mes = @varMes
        AND dia = @varDia
        AND estado <> 3
        AND (@varOpc = 1 OR pk_proceso <> @varPkProceso);

    IF @existe > 0
    BEGIN
        -- ADICIONALES
        DECLARE @elecciontipo_nombre NVARCHAR(200), @eleccionregion_nombre NVARCHAR(200), @procesomes_nombre NVARCHAR(200);

        SELECT @elecciontipo_nombre = et.nombre,
            @eleccionregion_nombre = er.nombre,
            @procesomes_nombre = CASE @varMes
            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 'Septiembre'
            WHEN 10 THEN 'Octubre'
            WHEN 11 THEN 'Noviembre'
            WHEN 12 THEN 'Diciembre'
            ELSE 'Desconocido'
        END
        FROM elecciones e
            LEFT JOIN elecciones_tipo et ON et.pk_tipo = e.pk_tipo
            LEFT JOIN catalogo_regiones er ON er.pk_region = e.pk_region
        WHERE e.pk_eleccion = @varPkEleccion;

        SET @result = CONCAT(
            'Ya existe el proceso electoral "', @elecciontipo_nombre, ' ', @eleccionregion_nombre,
            ', ', @varDia, ' de ', @procesomes_nombre, ' ', @varAnio, '".'
        );

        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO elecciones_proceso
            (pk_eleccion, anio, mes, dia, creado_por)
        VALUES
            (@varPkEleccion, @varAnio, @varMes, @varDia, @varUsuario);

        SET @varPkProceso = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE elecciones_proceso
        SET pk_eleccion = @varPkEleccion,
            anio = @varAnio,
            mes = @varMes,
            dia = @varDia,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_proceso = @varPkProceso;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkProceso = @varPkProceso;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkProceso AS pk;
END;
GO

-- # 10
DROP PROCEDURE IF EXISTS VOT_insert_update_elecciones_proceso_acta;
GO
CREATE PROCEDURE VOT_insert_update_elecciones_proceso_acta
    @varPkActa INT OUTPUT,
    @varPkProceso INT,
    @varPkCandidato INT,
    @varVotos INT,
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM elecciones_proceso_acta
    WHERE pk_proceso = @varPkProceso
        AND pk_candidato = @varPkCandidato
        AND estado <> 3
        AND (@varOpc = 1 OR pk_acta <> @varPkActa);

    IF @existe > 0
    BEGIN
        -- ADICIONALES
        DECLARE @candidato_nombrecompleto NVARCHAR(200),
        @candidatotipo_nombre NVARCHAR(200),
        @elecciontipo_nombre NVARCHAR(200),
        @eleccionregion_nombre NVARCHAR(200),
        @procesodia INT,
        @procesoanio INT,
        @procesomes_nombre NVARCHAR(200);

        SELECT @candidato_nombrecompleto = CONCAT(c.nombres, ' ', c.apellidos),
            @candidatotipo_nombre = ct.nombre,
            @elecciontipo_nombre = et.nombre,
            @eleccionregion_nombre = er.nombre,
            @procesodia = ep.dia,
            @procesoanio = ep.anio,
            @procesomes_nombre = CASE ep.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 'Septiembre'
            WHEN 10 THEN 'Octubre'
            WHEN 11 THEN 'Noviembre'
            WHEN 12 THEN 'Diciembre'
            ELSE 'Desconocido'
        END
        FROM elecciones_proceso ep
            LEFT JOIN elecciones e ON e.pk_eleccion = ep.pk_eleccion
            LEFT JOIN candidatos c ON c.pk_candidato = @varPkCandidato
            LEFT JOIN candidatos_tipo ct ON ct.pk_tipo = c.pk_tipo
            LEFT JOIN elecciones_tipo et ON et.pk_tipo = e.pk_tipo
            LEFT JOIN catalogo_regiones er ON er.pk_region = e.pk_region
        WHERE ep.pk_proceso = @varPkProceso;

        SET @result = CONCAT(
            'Ya se ingresó el acta electoral para el candidato "', @candidato_nombrecompleto,
            ' (', @candidatotipo_nombre, ')" en el proceso electoral "', @elecciontipo_nombre,
            ' ', @eleccionregion_nombre, ', ', @procesodia, ' de ', @procesomes_nombre, ' ', @procesoanio, '".'
        );

        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO elecciones_proceso_acta
            (pk_proceso, pk_candidato, votos, creado_por)
        VALUES
            (@varPkProceso, @varPkCandidato, @varVotos, @varUsuario);

        SET @varPkActa = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE elecciones_proceso_acta
        SET pk_proceso = @varPkProceso,
            pk_candidato = @varPkCandidato,
            votos = @varVotos,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_acta = @varPkActa;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkActa = @varPkActa;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkActa AS pk;
END;
GO

-- # 11
DROP PROCEDURE IF EXISTS CAT_insert_update_sexos;
GO
CREATE PROCEDURE CAT_insert_update_sexos
    @varPkSexo INT OUTPUT,
    @varNombre NVARCHAR(50),
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- MAYÚSCULAS
    SET @varNombre = UPPER(@varNombre);

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM catalogo_sexos
    WHERE nombre = @varNombre
        AND estado <> 3
        AND (@varOpc = 1 OR pk_sexo <> @varPkSexo);

    IF @existe > 0
    BEGIN
        SET @result = 'El nombre de ese sexo ya está en uso.';
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO catalogo_sexos
            (nombre, creado_por)
        VALUES
            (@varNombre, @varUsuario);

        SET @varPkSexo = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE catalogo_sexos
        SET nombre = @varNombre,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_sexo = @varPkSexo;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkSexo = @varPkSexo;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkSexo AS pk;
END;
GO

-- # 12
DROP PROCEDURE IF EXISTS CAT_insert_update_nacionalidades;
GO
CREATE PROCEDURE CAT_insert_update_nacionalidades
    @varPkNacionalidad INT OUTPUT,
    @varNombre NVARCHAR(100),
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    -- MAYÚSCULAS
    SET @varNombre = UPPER(@varNombre);

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM catalogo_nacionalidades
    WHERE nombre = @varNombre
        AND estado <> 3
        AND (@varOpc = 1 OR pk_nacionalidad <> @varPkNacionalidad);

    IF @existe > 0
    BEGIN
        SET @result = 'El nombre de esa nacionalidad ya está en uso.';
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    IF @varOpc = 1 
    BEGIN
        INSERT INTO catalogo_nacionalidades
            (nombre, creado_por)
        VALUES
            (@varNombre, @varUsuario);

        SET @varPkNacionalidad = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE catalogo_nacionalidades
        SET nombre = @varNombre,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_nacionalidad = @varPkNacionalidad;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkNacionalidad = @varPkNacionalidad;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkNacionalidad AS pk;
END;
GO

-- # 13
DROP PROCEDURE IF EXISTS VOT_insert_update_candidatos;
GO
CREATE PROCEDURE VOT_insert_update_candidatos
    @varPkCandidato INT OUTPUT,
    @varPkMovimiento INT,
    @varPkEleccionProceso INT,
    @varPkRegion INT,
    @varPkTipo INT,
    @varPkCandidatoMayor INT,
    @varPkSexo INT,
    @varPkNacionalidad INT,
    @varNombres NVARCHAR(100),
    @varApellidos NVARCHAR(100),
    @varDireccion NVARCHAR(255),
    @varTelefono NVARCHAR(20),
    @varCelular NVARCHAR(20),
    @varEmail NVARCHAR(100),
    @varUrlFotoPerfil NVARCHAR(255),
    @varIdentidad NVARCHAR(20),
    @varRtn NVARCHAR(20),
    @varCasilla INT,
    @varUrlFotoPapeleta NVARCHAR(255),
    @varUrlFotoIdentidadAnverso NVARCHAR(255),
    @varUrlFotoIdentidadReverso NVARCHAR(255),
    @varUrlArchivoAdicional NVARCHAR(255),
    @varFechaNacimiento DATE,
    @varUsuario INT,
    @varOpc INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result NVARCHAR(200), @save INT = 0;
    DECLARE @existe INT;

    DECLARE @tipoCandidatoNombre NVARCHAR(100);
    DECLARE @regionNombre NVARCHAR(100);
    DECLARE @regionTipoNombre NVARCHAR(100);
    DECLARE @movimientoNombre NVARCHAR(100);
    DECLARE @movimientoPartidoAbreviatura NVARCHAR(100);
    DECLARE @movimientoPartidoPk INT;
    DECLARE @movimientoPartidoNombre NVARCHAR(100);
    DECLARE @eleccionTipoNombre NVARCHAR(100);
    DECLARE @eleccionRegionNombre NVARCHAR(100);
    DECLARE @procesoAnio NVARCHAR(100);

    DECLARE @maxCantidad INT;
    DECLARE @cantidadActual INT;

    DECLARE @existeCasilla INT;

    -- MAYÚSCULAS
    SET @varNombres = UPPER(@varNombres)
    SET @varApellidos = UPPER(@varApellidos)
    SET @varDireccion = UPPER(@varDireccion)

    -- VALIDAR SI EL REGISTRO YA EXISTE
    SELECT @existe = COUNT(*)
    FROM candidatos
    WHERE identidad = @varIdentidad
        AND estado <> 3
        AND (@varOpc = 1 OR pk_candidato <> @varPkCandidato);

    IF @existe > 0
    BEGIN
        -- ADICIONALES
        DECLARE @tiporegion_nombre NVARCHAR(200);

        SELECT @tiporegion_nombre = nombre
        FROM catalogo_regiones_tipo
        WHERE pk_tipo = @varPkTipo;

        SET @result = CONCAT('Ya existe un candidato con el número de identidad ', @varIdentidad, '.');
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    -- OBTENER REGLA
    SELECT
        @maxCantidad = ecr.cantidad
    FROM elecciones_candidatos_x_region ecr
        LEFT JOIN catalogo_regiones cr ON ecr.pk_region = cr.pk_region AND cr.estado <> 3
        LEFT JOIN catalogo_regiones_tipo crt ON cr.pk_tipo = crt.pk_tipo AND crt.estado <> 3
    WHERE ecr.pk_eleccion = @varPkEleccionProceso
        AND ecr.pk_region = @varPkRegion
        AND ecr.pk_candidato_tipo = @varPkTipo
        AND ecr.estado <> 3;

    SELECT @tipoCandidatoNombre = nombre
    FROM candidatos_tipo
    WHERE pk_tipo = @varPkTipo AND estado <> 3;

    SELECT @regionNombre = r.nombre, @regionTipoNombre = rt.nombre
    FROM catalogo_regiones r
        LEFT JOIN catalogo_regiones_tipo rt ON rt.pk_tipo = r.pk_tipo AND rt.estado <> 3
    WHERE r.pk_region = @varPkRegion AND r.estado <> 3;

    SELECT
        @movimientoNombre = m.nombre,
        @movimientoPartidoAbreviatura = p.abreviatura,
        @movimientoPartidoPk = p.pk_partido,
        @movimientoPartidoNombre = p.nombre
    FROM movimientos m
        LEFT JOIN partidos p ON p.pk_partido = m.pk_partido AND p.estado <> 3
    WHERE m.pk_movimiento = @varPkMovimiento AND m.estado <> 3;

    SELECT
        @eleccionTipoNombre = et.nombre,
        @eleccionRegionNombre = cr.nombre,
        @procesoAnio = ep.anio
    FROM elecciones_proceso ep
        LEFT JOIN elecciones e ON ep.pk_eleccion = e.pk_eleccion AND e.estado <> 3
        LEFT JOIN elecciones_tipo et ON e.pk_tipo = et.pk_tipo AND et.estado <> 3
        LEFT JOIN catalogo_regiones cr ON e.pk_region = cr.pk_region AND cr.estado <> 3
    WHERE ep.pk_proceso = @varPkEleccionProceso AND ep.estado <> 3;

    -- REGLA NO EXISTE
    IF @maxCantidad IS NULL
    BEGIN
        SET @result = CONCAT('No existe una regla de cantidad máxima de "', @tipoCandidatoNombre,'" para la región "', @regionNombre, ' (', @regionTipoNombre, ')"');
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    -- CANDIDATOS ACTUALES
    SELECT @cantidadActual = COUNT(*)
    FROM candidatos
    WHERE pk_eleccion_proceso = @varPkEleccionProceso
        AND pk_region = @varPkRegion
        AND pk_tipo = @varPkTipo
        AND pk_movimiento = @varPkMovimiento -- ✅ Se añadió la validación por movimiento
        AND estado <> 3
        AND (@varOpc = 1 OR pk_candidato <> @varPkCandidato);

    -- MÁXIMO CANDIDATOS ALCANZADO
    IF @cantidadActual >= @maxCantidad
    BEGIN
        SET @result = CONCAT('Se alcanzó el máximo de "', @tipoCandidatoNombre, '" para la región "', @regionNombre, ' (', @regionTipoNombre, ')" en el movimiento "', @movimientoNombre, ' (',@movimientoPartidoAbreviatura,')".');
        SELECT @save AS 'save', @result AS result;
        RETURN;
    END

    -- CASILLA YA EXISTE
    SELECT @existeCasilla = COUNT(*)
    FROM candidatos c
        LEFT JOIN movimientos m ON c.pk_movimiento = m.pk_movimiento AND m.estado <> 3
    WHERE @varCasilla <> 0 -- si es casilla 0 no valida
        AND c.pk_eleccion_proceso = @varPkEleccionProceso
        AND c.pk_region = @varPkRegion
        AND m.pk_partido = @movimientoPartidoPk -- Validación a nivel de partido político
        AND c.casilla = @varCasilla
        AND c.estado <> 3
        AND (@varOpc = 1 OR c.pk_candidato <> @varPkCandidato);

    IF @existeCasilla > 0
    BEGIN
        SET @result = CONCAT(
            'Ya existe un candidato en la casilla #', @varCasilla,
            ' para la región "', @regionNombre, ' (', @regionTipoNombre, ')"',
            -- ' para el proceso electoral "', @eleccionTipoNombre, ' ', @eleccionRegionNombre, ' ', @procesoAnio, '" ',
            ' dentro del partido "', @movimientoPartidoNombre, '".'
        );

        SELECT @save AS 'save', @result AS result;
        RETURN;
    END


    IF @varOpc = 1
    BEGIN
        INSERT INTO candidatos
            (
            pk_movimiento, pk_eleccion_proceso, pk_region, pk_tipo, pk_candidato_mayor, pk_sexo, pk_nacionalidad,
            nombres, apellidos, direccion, telefono, celular, email, url_foto_perfil, identidad,
            rtn, casilla, url_foto_papeleta, url_foto_identidad_anverso, url_foto_identidad_reverso, url_archivo_adicional,
            fecha_nacimiento, creado_por
            )
        VALUES
            (
                @varPkMovimiento, @varPkEleccionProceso, @varPkRegion, @varPkTipo, @varPkCandidatoMayor, @varPkSexo, @varPkNacionalidad,
                @varNombres, @varApellidos, @varDireccion, @varTelefono, @varCelular, @varEmail, @varUrlFotoPerfil, @varIdentidad,
                @varRtn, @varCasilla, @varUrlFotoPapeleta, @varUrlFotoIdentidadAnverso, @varUrlFotoIdentidadReverso, @varUrlArchivoAdicional,
                @varFechaNacimiento, @varUsuario
        );

        SET @varPkCandidato = SCOPE_IDENTITY();
        SET @save = 1;
        SET @result = 'Registro insertado correctamente';
    END
    ELSE IF @varOpc = 2
    BEGIN
        UPDATE candidatos
        SET pk_movimiento = @varPkMovimiento,
            pk_eleccion_proceso = @varPkEleccionProceso,
            pk_region = @varPkRegion,
            pk_tipo = @varPkTipo,
            pk_candidato_mayor = @varPkCandidatoMayor,
            pk_sexo = @varPkSexo,
            pk_nacionalidad = @varPkNacionalidad,
            nombres = @varNombres,
            apellidos = @varApellidos,
            direccion = @varDireccion,
            telefono = @varTelefono,
            celular = @varCelular,
            email = @varEmail,
            url_foto_perfil = @varUrlFotoPerfil,
            identidad = @varIdentidad,
            rtn = @varRtn,
            casilla = @varCasilla,
            url_foto_papeleta = @varUrlFotoPapeleta,
            url_foto_identidad_anverso = @varUrlFotoIdentidadAnverso,
            url_foto_identidad_reverso = @varUrlFotoIdentidadReverso,
            url_archivo_adicional = @varUrlArchivoAdicional,
            fecha_nacimiento = @varFechaNacimiento,
            modificado_por = @varUsuario,
            fecha_hora_modificado = GETDATE()
        WHERE pk_candidato = @varPkCandidato;

        SET @save = 1;
        SET @result = 'Registro actualizado correctamente';
        SET @varPkCandidato = @varPkCandidato;
    END
    ELSE
    BEGIN
        SET @result = 'Opción no válida';
    END

    SELECT @save AS 'save', @result AS result, @varPkCandidato AS pk;
END;
GO