SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioGetByName]
    @NombreUsuario varchar(100),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        SELECT TOP (1)
            U.[CveUsuario], U.[NombreUsuario], U.[HashContrasena], U.[CorreoElectronico],
            U.[CveRol], R.[NombreRol], U.[Activo], U.[DebeCambiarContrasena],
            U.[IntentosFallidosInicioSesion], U.[BloqueadoHasta],
            U.[FechaUltimoInicioSesion], U.[FechaUltimaConexion], U.[CorreoVerificado]
        FROM [dbo].[Sis_Usuarios] AS U        INNER JOIN [dbo].[Sis_Roles] AS R ON R.[CveRol] = U.[CveRol]
        WHERE U.[NombreUsuario] = @NombreUsuario
          AND U.[Borrado] = (0)
          AND R.[Borrado] = (0);

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar el usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioGetByName'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioLoginSuccessUpdate]
    @CveUsuario int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        UPDATE [dbo].[Sis_Usuarios]
        SET [FechaUltimoInicioSesion] = GETDATE(),
            [FechaUltimaConexion] = GETDATE(),
            [IntentosFallidosInicioSesion] = (0),
            [BloqueadoHasta] = NULL,
            [FechaActualizacion] = GETDATE()
        WHERE [CveUsuario] = @CveUsuario
          AND [Borrado] = (0);

        IF @@ROWCOUNT = (0)
        BEGIN
            SET @ErrorMostrar = 'El usuario ya no está disponible.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al actualizar el inicio de sesión; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioLoginSuccessUpdate'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioLoginFailedUpdate]
    @CveUsuario int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        UPDATE [dbo].[Sis_Usuarios]
        SET [IntentosFallidosInicioSesion] = [IntentosFallidosInicioSesion] + (1),
            [BloqueadoHasta] = CASE
                WHEN [IntentosFallidosInicioSesion] + (1) >= (5)
                    THEN DATEADD(MINUTE, (15), GETDATE())
                ELSE [BloqueadoHasta]
            END,
            [FechaActualizacion] = GETDATE()
        WHERE [CveUsuario] = @CveUsuario
          AND [Borrado] = (0);

        IF @@ROWCOUNT = (0)
        BEGIN
            SET @ErrorMostrar = 'El usuario ya no está disponible.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al registrar el intento de acceso; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioLoginFailedUpdate'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_IntentoInicioSesionAdd]
    @CveUsuario int = NULL,
    @NombreUsuario varchar(100),
    @Exitoso bit,
    @MotivoFallo varchar(200) = NULL,
    @DireccionIP varchar(50) = NULL,
    @AgenteUsuario varchar(1000) = NULL,
    @NombreDominio varchar(255) = NULL,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        INSERT INTO [dbo].[Sis_IntentosInicioSesion]
            ([CveUsuario], [NombreUsuario], [Exitoso], [MotivoFallo],
             [DireccionIP], [AgenteUsuario], [NombreDominio])
        VALUES
            (@CveUsuario, @NombreUsuario, @Exitoso, @MotivoFallo,
             @DireccionIP, @AgenteUsuario, @NombreDominio);

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al registrar el intento de acceso; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_IntentoInicioSesionAdd'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioSesionAdd]
    @CveUsuario int,
    @TokenSesion varchar(200),
    @DireccionIP varchar(50) = NULL,
    @AgenteUsuario varchar(1000) = NULL,
    @NombreDominio varchar(255) = NULL,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        IF NOT EXISTS
        (
            SELECT 1 FROM [dbo].[Sis_Usuarios]
            WHERE [CveUsuario] = @CveUsuario AND [Activo] = (1) AND [Borrado] = (0)
        )
        BEGIN
            SET @ErrorMostrar = 'El usuario no está disponible para iniciar sesión.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        INSERT INTO [dbo].[Sis_UsuariosSesiones]
            ([CveUsuario], [TokenSesion], [DireccionIP], [AgenteUsuario],
             [NombreDominio], [FechaUltimaActividad])
        VALUES
            (@CveUsuario, @TokenSesion, @DireccionIP, @AgenteUsuario,
             @NombreDominio, GETDATE());

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al crear la sesión; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioSesionAdd'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioSesionEnd]
    @TokenSesion varchar(200),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        UPDATE [dbo].[Sis_UsuariosSesiones]
        SET [FechaFin] = GETDATE(), [Activo] = (0)
        WHERE [TokenSesion] = @TokenSesion
          AND [Borrado] = (0);

        /* Cerrar una sesión inexistente o ya cerrada es una operación idempotente. */
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al cerrar la sesión; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioSesionEnd'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_PermisoCheck]
    @CodigoPermiso varchar(100),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        DECLARE @CveRol int;
        DECLARE @PermisoUsuario bit;

        SELECT @CveRol = [CveRol]
        FROM [dbo].[Sis_Usuarios]
        WHERE [CveUsuario] = @CveUsuarioEjecuta
          AND [Activo] = (1)
          AND [Borrado] = (0);

        SELECT TOP (1) @PermisoUsuario = UP.[Permitido]
        FROM [dbo].[Sis_UsuariosPermisos] AS UP
        INNER JOIN [dbo].[Sis_Permisos] AS P ON P.[CvePermiso] = UP.[CvePermiso]
        WHERE UP.[CveUsuario] = @CveUsuarioEjecuta
          AND P.[CodigoPermiso] = @CodigoPermiso
          AND P.[Activo] = (1)
          AND UP.[Borrado] = (0)
          AND P.[Borrado] = (0);

        IF @PermisoUsuario IS NOT NULL
            SELECT CAST(@PermisoUsuario AS bit) AS [Permitido];
        ELSE IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_RolesPermisos] AS RP
            INNER JOIN [dbo].[Sis_Permisos] AS P ON P.[CvePermiso] = RP.[CvePermiso]
            WHERE RP.[CveRol] = @CveRol
              AND P.[CodigoPermiso] = @CodigoPermiso
              AND RP.[Permitido] = (1)
              AND P.[Activo] = (1)
              AND RP.[Borrado] = (0)
              AND P.[Borrado] = (0)
        )
            SELECT CAST((1) AS bit) AS [Permitido];
        ELSE
            SELECT CAST((0) AS bit) AS [Permitido];

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar los permisos; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_PermisoCheck'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_PaginaAccessCheck]
    @PaginaUrl varchar(500),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        DECLARE @CveRol int;
        DECLARE @PuedeAcceder bit = (0);

        SELECT @CveRol = [CveRol]
        FROM [dbo].[Sis_Usuarios]
        WHERE [CveUsuario] = @CveUsuarioEjecuta
          AND [Activo] = (1)
          AND [Borrado] = (0);

        IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_Modulos] AS M
            INNER JOIN [dbo].[Sis_RolesModulos] AS RM ON RM.[CveModulo] = M.[CveModulo]
            WHERE RM.[CveRol] = @CveRol
              AND RM.[PuedeVer] = (1)
              AND M.[Activo] = (1)
              AND M.[Visible] = (1)
              AND RM.[Borrado] = (0)
              AND M.[Borrado] = (0)
              AND
              (
                  LOWER(REPLACE(M.[Url], '~/', '')) = LOWER(@PaginaUrl)
                  OR LOWER(REPLACE(M.[Url], '/', '')) = LOWER(REPLACE(@PaginaUrl, '/', ''))
              )
        )
            SET @PuedeAcceder = (1);
        ELSE IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_Modulos] AS M
            WHERE M.[Activo] = (1)
              AND M.[Borrado] = (0)
              AND LOWER(REPLACE(M.[Url], '~/', '')) = LOWER(@PaginaUrl)
        )
            SET @PuedeAcceder = (0);
        ELSE IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_UsuariosPermisos] AS UP
            INNER JOIN [dbo].[Sis_Permisos] AS P ON P.[CvePermiso] = UP.[CvePermiso]
            WHERE UP.[CveUsuario] = @CveUsuarioEjecuta
              AND P.[CodigoPermiso] = 'System.Access'
              AND UP.[Permitido] = (1)
              AND P.[Activo] = (1)
              AND UP.[Borrado] = (0)
              AND P.[Borrado] = (0)
        )
            SET @PuedeAcceder = (1);
        ELSE IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_RolesPermisos] AS RP
            INNER JOIN [dbo].[Sis_Permisos] AS P ON P.[CvePermiso] = RP.[CvePermiso]
            WHERE RP.[CveRol] = @CveRol
              AND P.[CodigoPermiso] = 'System.Access'
              AND RP.[Permitido] = (1)
              AND P.[Activo] = (1)
              AND RP.[Borrado] = (0)
              AND P.[Borrado] = (0)
        )
            SET @PuedeAcceder = (1);

        SELECT @PuedeAcceder AS [PuedeAcceder];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al validar el acceso; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_PaginaAccessCheck'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_MenuList]
    @CveRol int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        SELECT DISTINCT
            M.[CveModulo], M.[CveModuloPadre], M.[NombreModulo], M.[Url],
            M.[ClaseCssIcono], M.[ClaseCssEstilo], M.[OrdenVisualizacion], M.[Visible]
        FROM [dbo].[Sis_Modulos] AS M
        INNER JOIN [dbo].[Sis_RolesModulos] AS RM ON RM.[CveModulo] = M.[CveModulo]
        WHERE RM.[CveRol] = @CveRol
          AND RM.[PuedeVer] = (1)
          AND M.[Activo] = (1)
          AND M.[Visible] = (1)
          AND RM.[Borrado] = (0)
          AND M.[Borrado] = (0)
        ORDER BY M.[OrdenVisualizacion], M.[NombreModulo];

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar el menú; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_MenuList'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO