SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

/* ==================== COLUMNAS DE VERIFICACIÓN ==================== */

IF COL_LENGTH(N'dbo.Sis_Usuarios', N'CorreoVerificado') IS NULL
    ALTER TABLE [dbo].[Sis_Usuarios]
        ADD [CorreoVerificado] bit NOT NULL
            CONSTRAINT [DF_Sis_Usuarios_CorreoVerificado] DEFAULT ((1)) WITH VALUES;
GO

IF COL_LENGTH(N'dbo.Sis_Usuarios', N'FechaVerificacionCorreo') IS NULL
    ALTER TABLE [dbo].[Sis_Usuarios] ADD [FechaVerificacionCorreo] datetime2(0) NULL;
GO

/* ==================== RELACIÓN USUARIO - CLIENTE ==================== */

IF OBJECT_ID(N'dbo.Sis_UsuariosClientes', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[Sis_UsuariosClientes]
    (
        [CveUsuarioCliente] int IDENTITY(1,1) NOT NULL,
        [CveUsuario] int NOT NULL,
        [CveCliente] int NOT NULL,
        [Orden] int NOT NULL,
        [EsPrincipal] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosClientes_EsPrincipal] DEFAULT ((0)),
        [Activo] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosClientes_Activo] DEFAULT ((1)),
        [FechaCreacion] datetime2(0) NOT NULL CONSTRAINT [DF_Sis_UsuariosClientes_FechaCreacion] DEFAULT (SYSDATETIME()),
        [FechaActualizacion] datetime2(0) NULL,
        [CveUsuarioRegistro] int NOT NULL,
        [Borrado] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosClientes_Borrado] DEFAULT ((0)),
        [FechaBorrado] datetime2(0) NULL,
        CONSTRAINT [PK_Sis_UsuariosClientes] PRIMARY KEY CLUSTERED ([CveUsuarioCliente]),
        CONSTRAINT [CK_Sis_UsuariosClientes_Orden] CHECK ([Orden] > (0)),
        CONSTRAINT [CK_Sis_UsuariosClientes_Borrado] CHECK
        (([Borrado] = (0) AND [FechaBorrado] IS NULL) OR ([Borrado] = (1) AND [FechaBorrado] IS NOT NULL)),
        CONSTRAINT [FK_Sis_UsuariosClientes_Usuario] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]),
        CONSTRAINT [FK_Sis_UsuariosClientes_Cliente] FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]),
        CONSTRAINT [FK_Sis_UsuariosClientes_UsuarioRegistro] FOREIGN KEY ([CveUsuarioRegistro]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
    );

    CREATE UNIQUE NONCLUSTERED INDEX [UX_Sis_UsuariosClientes_Usuario_Cliente]
        ON [dbo].[Sis_UsuariosClientes] ([CveUsuario], [CveCliente])
        WHERE [Borrado] = (0);

    CREATE UNIQUE NONCLUSTERED INDEX [UX_Sis_UsuariosClientes_Usuario_Orden]
        ON [dbo].[Sis_UsuariosClientes] ([CveUsuario], [Orden])
        WHERE [Borrado] = (0) AND [Activo] = (1);
END;
GO

/* ==================== VERIFICACIÓN DE CORREO ==================== */

IF OBJECT_ID(N'dbo.Sis_UsuariosVerificacionesCorreo', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[Sis_UsuariosVerificacionesCorreo]
    (
        [CveUsuarioVerificacionCorreo] int IDENTITY(1,1) NOT NULL,
        [CveUsuario] int NOT NULL,
        [CveClienteRegistro] int NULL,
        [HashToken] char(64) COLLATE Modern_Spanish_CS_AI NOT NULL,
        [FechaExpiracion] datetime2(0) NOT NULL,
        [FechaVerificacion] datetime2(0) NULL,
        [Utilizado] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosVerificacionesCorreo_Utilizado] DEFAULT ((0)),
        [FechaCreacion] datetime2(0) NOT NULL CONSTRAINT [DF_Sis_UsuariosVerificacionesCorreo_FechaCreacion] DEFAULT (SYSDATETIME()),
        [Borrado] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosVerificacionesCorreo_Borrado] DEFAULT ((0)),
        [FechaBorrado] datetime2(0) NULL,
        CONSTRAINT [PK_Sis_UsuariosVerificacionesCorreo] PRIMARY KEY CLUSTERED ([CveUsuarioVerificacionCorreo]),
        CONSTRAINT [UQ_Sis_UsuariosVerificacionesCorreo_HashToken] UNIQUE NONCLUSTERED ([HashToken]),
        CONSTRAINT [CK_Sis_UsuariosVerificacionesCorreo_Borrado] CHECK
        (([Borrado] = (0) AND [FechaBorrado] IS NULL) OR ([Borrado] = (1) AND [FechaBorrado] IS NOT NULL)),
        CONSTRAINT [FK_Sis_UsuariosVerificacionesCorreo_Usuario] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]),
        CONSTRAINT [FK_Sis_UsuariosVerificacionesCorreo_Cliente] FOREIGN KEY ([CveClienteRegistro]) REFERENCES [dbo].[Clientes] ([CveCliente])
    );

    CREATE NONCLUSTERED INDEX [IX_Sis_UsuariosVerificacionesCorreo_Pendiente]
        ON [dbo].[Sis_UsuariosVerificacionesCorreo] ([CveUsuario], [Utilizado], [FechaExpiracion])
        WHERE [Borrado] = (0);
END;
GO

/* ==================== AUTENTICACIÓN CON CORREO VERIFICADO ==================== */

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 @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

/* ==================== CATÁLOGOS ==================== */

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_RolList]
    @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 [CveRol], [NombreRol], [Descripcion]
        FROM [dbo].[Sis_Roles]
        WHERE [Activo] = (1) AND [Borrado] = (0)
        ORDER BY [NombreRol];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar los roles; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Gral_EstadoList]
    @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 [CveEstado], [NombreEstado], [ClaveSATEstado]
        FROM [dbo].[Gral_SAT_Estado]
        WHERE [Borrado] = (0)
        ORDER BY [NombreEstado];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar los estados; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

/* ==================== AUTORREGISTRO ==================== */

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_RegistroClienteValidate]
    @NombreUsuario varchar(100),
    @CorreoElectronico varchar(255),
    @RFC varchar(15),
    @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
        SET @NombreUsuario = LTRIM(RTRIM(@NombreUsuario));
        SET @CorreoElectronico = LOWER(LTRIM(RTRIM(@CorreoElectronico)));
        SET @RFC = UPPER(REPLACE(LTRIM(RTRIM(@RFC)), ' ', ''));

        IF EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE [NombreUsuario] = @NombreUsuario)
        BEGIN
            SET @ErrorMostrar = 'El nombre de usuario ya se encuentra registrado.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        IF EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE LOWER([CorreoElectronico]) = @CorreoElectronico)
        BEGIN
            SET @ErrorMostrar = 'El correo electrónico ya se encuentra registrado.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        /* Incluye clientes activos, inactivos y borrados para impedir autorreactivación. */
        IF EXISTS (SELECT 1 FROM [dbo].[Clientes] WHERE UPPER(REPLACE(LTRIM(RTRIM([RFC])), ' ', '')) = @RFC)
        BEGIN
            SET @ErrorMostrar = 'El RFC ya se encuentra registrado. Contacta a un administrador para solicitar acceso.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

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

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_RegistroClienteAdd]
    @NombreUsuario varchar(100),
    @HashContrasena varchar(500),
    @CorreoElectronico varchar(255),
    @RFC varchar(15),
    @RazonSocial varchar(200),
    @NombreComercial varchar(200) = NULL,
    @Direccion varchar(90),
    @NoExt varchar(20) = NULL,
    @NoInt varchar(50) = NULL,
    @Colonia varchar(50),
    @Ciudad varchar(50),
    @CveSATEstado int,
    @Pais varchar(50) = 'México',
    @CP varchar(10) = NULL,
    @Telefono varchar(25) = NULL,
    @HashToken char(64),
    @HorasExpiracion int = 24,
    @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
        DECLARE @CveRolEmpresa int;
        DECLARE @CveUsuario int;
        DECLARE @CveCliente int;
        DECLARE @NombreEstado varchar(35);

        SET @NombreUsuario = LTRIM(RTRIM(@NombreUsuario));
        SET @CorreoElectronico = LOWER(LTRIM(RTRIM(@CorreoElectronico)));
        SET @RFC = UPPER(REPLACE(LTRIM(RTRIM(@RFC)), ' ', ''));

        SELECT @CveRolEmpresa = [CveRol]
        FROM [dbo].[Sis_Roles]
        WHERE [NombreRol] = 'Empresa' AND [Activo] = (1) AND [Borrado] = (0);

        SELECT @NombreEstado = LEFT([NombreEstado], 35)
        FROM [dbo].[Gral_SAT_Estado]
        WHERE [CveEstado] = @CveSATEstado AND [Borrado] = (0);

        IF @CveRolEmpresa IS NULL OR @NombreEstado IS NULL
        BEGIN
            SET @ErrorMostrar = 'No fue posible determinar el rol Empresa o el estado seleccionado.';
            SET @ErrorTipo = 1;
            RETURN;
        END;

        IF EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE [NombreUsuario] = @NombreUsuario)
           OR EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE LOWER([CorreoElectronico]) = @CorreoElectronico)
           OR EXISTS (SELECT 1 FROM [dbo].[Clientes] WHERE UPPER(REPLACE(LTRIM(RTRIM([RFC])), ' ', '')) = @RFC)
        BEGIN
            SET @ErrorMostrar = 'Los datos ya fueron registrados por otra solicitud. Actualiza la página e inténtalo nuevamente.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        BEGIN TRANSACTION;

        INSERT INTO [dbo].[Sis_Usuarios]
            ([NombreUsuario], [HashContrasena], [CorreoElectronico], [CveRol], [Activo],
             [DebeCambiarContrasena], [CorreoVerificado])
        VALUES
            (@NombreUsuario, @HashContrasena, @CorreoElectronico, @CveRolEmpresa, (0), (0), (0));

        SET @CveUsuario = CONVERT(int, SCOPE_IDENTITY());

        INSERT INTO [dbo].[Clientes]
            ([Nombre], [NombreComercial], [Direccion], [NoExt], [NoInt], [Colonia],
             [Ciudad], [Estado], [CveSATEstado], [Pais], [RFC], [CP], [Telefono],
             [Mail], [Estatus], [Fecha], [CveUsuario], [FechaIng])
        VALUES
            (@RazonSocial, NULLIF(@NombreComercial, ''), @Direccion, NULLIF(@NoExt, ''),
             NULLIF(@NoInt, ''), @Colonia, @Ciudad, @NombreEstado, @CveSATEstado,
             COALESCE(NULLIF(@Pais, ''), 'México'), @RFC, NULLIF(@CP, ''),
             NULLIF(@Telefono, ''), @CorreoElectronico, (0), CONVERT(date, GETDATE()),
             @CveUsuario, GETDATE());

        SET @CveCliente = CONVERT(int, SCOPE_IDENTITY());

        INSERT INTO [dbo].[Sis_UsuariosClientes]
            ([CveUsuario], [CveCliente], [Orden], [EsPrincipal], [Activo], [CveUsuarioRegistro])
        VALUES
            (@CveUsuario, @CveCliente, (1), (1), (1), @CveUsuario);

        INSERT INTO [dbo].[Sis_UsuariosVerificacionesCorreo]
            ([CveUsuario], [CveClienteRegistro], [HashToken], [FechaExpiracion])
        VALUES
            (@CveUsuario, @CveCliente, @HashToken, DATEADD(HOUR, @HorasExpiracion, SYSDATETIME()));

        COMMIT TRANSACTION;

        SELECT @CveUsuario AS [CveUsuario], @CveCliente AS [CveCliente];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        SET @ErrorMostrar = 'Ocurrió un error al crear el registro; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_CorreoVerify]
    @HashToken char(64),
    @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
        DECLARE @CveVerificacion int;
        DECLARE @CveUsuario int;
        DECLARE @CveCliente int;

        SELECT TOP (1)
            @CveVerificacion = [CveUsuarioVerificacionCorreo],
            @CveUsuario = [CveUsuario],
            @CveCliente = [CveClienteRegistro]
        FROM [dbo].[Sis_UsuariosVerificacionesCorreo]
        WHERE [HashToken] = @HashToken
          AND [Utilizado] = (0)
          AND [Borrado] = (0)
          AND [FechaExpiracion] >= SYSDATETIME();

        IF @CveVerificacion IS NULL
        BEGIN
            SET @ErrorMostrar = 'La liga de verificación no es válida o ya expiró.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        BEGIN TRANSACTION;

        UPDATE [dbo].[Sis_UsuariosVerificacionesCorreo]
        SET [Utilizado] = (1), [FechaVerificacion] = SYSDATETIME()
        WHERE [CveUsuarioVerificacionCorreo] = @CveVerificacion AND [Utilizado] = (0);

        IF @@ROWCOUNT = (0)
        BEGIN
            ROLLBACK TRANSACTION;
            SET @ErrorMostrar = 'La liga de verificación ya fue utilizada.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        UPDATE [dbo].[Sis_Usuarios]
        SET [CorreoVerificado] = (1), [FechaVerificacionCorreo] = SYSDATETIME(),
            [Activo] = (1), [FechaActualizacion] = GETDATE()
        WHERE [CveUsuario] = @CveUsuario AND [Borrado] = (0);

        /* Activación inicial del cliente creado por este mismo token. */
        UPDATE [dbo].[Clientes]
        SET [Estatus] = (1), [CveUsuarioModifico] = @CveUsuario
        WHERE [CveCliente] = @CveCliente AND [CveUsuario] = @CveUsuario AND [Borrado] = (0);

        COMMIT TRANSACTION;
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        SET @ErrorMostrar = 'Ocurrió un error al verificar el correo; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

/* ==================== ADMINISTRACIÓN DE USUARIOS ==================== */

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioList]
    @Texto varchar(150) = NULL,
    @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
        IF NOT EXISTS
        (
            SELECT 1 FROM [dbo].[Sis_Usuarios] U
            INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
            WHERE U.[CveUsuario] = @CveUsuarioEjecuta
              AND R.[NombreRol] IN ('SuperUsuario', 'Administrador')
              AND U.[Activo] = (1) AND U.[Borrado] = (0)
        )
        BEGIN
            SET @ErrorMostrar = 'No tienes permiso para consultar usuarios.';
            SET @ErrorTipo = 1;
            RETURN;
        END;

        SELECT U.[CveUsuario], U.[NombreUsuario], U.[CorreoElectronico], U.[CveRol],
               R.[NombreRol], U.[Activo], U.[CorreoVerificado], U.[FechaCreacion]
        FROM [dbo].[Sis_Usuarios] U
        INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
        WHERE U.[Borrado] = (0)
          AND (@Texto IS NULL OR U.[NombreUsuario] LIKE '%' + @Texto + '%'
               OR U.[CorreoElectronico] LIKE '%' + @Texto + '%')
        ORDER BY U.[NombreUsuario];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar usuarios; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioGet]
    @CveUsuario 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 U.[CveUsuario], U.[NombreUsuario], U.[CorreoElectronico], U.[CveRol],
               R.[NombreRol], U.[Activo], U.[DebeCambiarContrasena], U.[CorreoVerificado]
        FROM [dbo].[Sis_Usuarios] U
        INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
        WHERE U.[CveUsuario] = @CveUsuario AND U.[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;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioSave]
    @CveUsuario int = NULL,
    @NombreUsuario varchar(100),
    @CorreoElectronico varchar(255),
    @CveRol int,
    @HashContrasena varchar(500) = NULL,
    @Activo bit,
    @DebeCambiarContrasena bit,
    @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] U
            INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
            WHERE U.[CveUsuario] = @CveUsuarioEjecuta AND R.[NombreRol] = 'SuperUsuario'
              AND U.[Activo] = (1) AND U.[Borrado] = (0)
        )
        BEGIN
            SET @ErrorMostrar = 'Sólo un SuperUsuario puede crear o modificar usuarios.';
            SET @ErrorTipo = 1;
            RETURN;
        END;

        IF EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE [NombreUsuario] = @NombreUsuario AND [CveUsuario] <> ISNULL(@CveUsuario, (0)))
           OR EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE LOWER([CorreoElectronico]) = LOWER(@CorreoElectronico) AND [CveUsuario] <> ISNULL(@CveUsuario, (0)))
        BEGIN
            SET @ErrorMostrar = 'El usuario o correo electrónico ya está registrado.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        IF @CveUsuario IS NULL OR @CveUsuario <= (0)
        BEGIN
            IF NULLIF(@HashContrasena, '') IS NULL
            BEGIN
                SET @ErrorMostrar = 'La contraseña es obligatoria para un usuario nuevo.';
                SET @ErrorTipo = 2;
                RETURN;
            END;

            INSERT INTO [dbo].[Sis_Usuarios]
                ([NombreUsuario], [HashContrasena], [CorreoElectronico], [CveRol], [Activo],
                 [DebeCambiarContrasena], [CorreoVerificado], [FechaVerificacionCorreo])
            VALUES
                (@NombreUsuario, @HashContrasena, @CorreoElectronico, @CveRol, @Activo,
                 @DebeCambiarContrasena, (1), SYSDATETIME());
            SET @CveUsuario = CONVERT(int, SCOPE_IDENTITY());
        END
        ELSE
        BEGIN
            UPDATE [dbo].[Sis_Usuarios]
            SET [NombreUsuario] = @NombreUsuario, [CorreoElectronico] = @CorreoElectronico,
                [CveRol] = @CveRol, [Activo] = @Activo,
                [DebeCambiarContrasena] = @DebeCambiarContrasena,
                [HashContrasena] = COALESCE(NULLIF(@HashContrasena, ''), [HashContrasena]),
                [FechaActualizacion] = GETDATE()
            WHERE [CveUsuario] = @CveUsuario AND [Borrado] = (0);
        END;

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

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioDelete]
    @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
        IF @CveUsuario = @CveUsuarioEjecuta
        BEGIN
            SET @ErrorMostrar = 'No puedes borrar tu propio usuario.';
            SET @ErrorTipo = 2;
            RETURN;
        END;
        UPDATE [dbo].[Sis_Usuarios]
        SET [Activo] = (0), [Borrado] = (1), [FechaBorrado] = SYSDATETIME(), [FechaActualizacion] = GETDATE()
        WHERE [CveUsuario] = @CveUsuario AND [Borrado] = (0)
          AND EXISTS
          (
              SELECT 1 FROM [dbo].[Sis_Usuarios] E
              INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = E.[CveRol]
              WHERE E.[CveUsuario] = @CveUsuarioEjecuta AND R.[NombreRol] = 'SuperUsuario'
                AND E.[Activo] = (1) AND E.[Borrado] = (0)
          );
        IF @@ROWCOUNT = (0)
        BEGIN
            SET @ErrorMostrar = 'No fue posible borrar el usuario o no tienes autorización.';
            SET @ErrorTipo = 1;
            RETURN;
        END;
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al borrar el usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioClienteList]
    @CveUsuario 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 UC.[CveUsuarioCliente], UC.[CveUsuario], UC.[CveCliente], C.[Nombre], C.[RFC],
               UC.[Orden], UC.[EsPrincipal], UC.[Activo], C.[Estatus] AS [ClienteActivo]
        FROM [dbo].[Sis_UsuariosClientes] UC
        INNER JOIN [dbo].[Clientes] C ON C.[CveCliente] = UC.[CveCliente]
        WHERE UC.[CveUsuario] = @CveUsuario AND UC.[Borrado] = (0)
        ORDER BY UC.[Orden];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar los clientes del usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioClienteSave]
    @CveUsuario int,
    @CveCliente int,
    @Orden int,
    @EsPrincipal bit,
    @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] U INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
            WHERE U.[CveUsuario] = @CveUsuarioEjecuta AND R.[NombreRol] IN ('SuperUsuario', 'Administrador')
              AND U.[Activo] = (1) AND U.[Borrado] = (0)
        )
        BEGIN
            SET @ErrorMostrar = 'No tienes permiso para asignar clientes.';
            SET @ErrorTipo = 1;
            RETURN;
        END;

        IF @Orden <= (0) OR NOT EXISTS (SELECT 1 FROM [dbo].[Clientes] WHERE [CveCliente] = @CveCliente AND [Borrado] = (0))
        BEGIN
            SET @ErrorMostrar = 'El cliente o el orden indicado no es válido.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        IF EXISTS
        (
            SELECT 1 FROM [dbo].[Sis_UsuariosClientes]
            WHERE [CveUsuario] = @CveUsuario AND [Orden] = @Orden AND [Activo] = (1)
              AND [Borrado] = (0) AND [CveCliente] <> @CveCliente
        )
        BEGIN
            SET @ErrorMostrar = 'Ya existe otro cliente con el mismo orden para este usuario.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        IF @EsPrincipal = (1)
            UPDATE [dbo].[Sis_UsuariosClientes]
            SET [EsPrincipal] = (0), [FechaActualizacion] = SYSDATETIME()
            WHERE [CveUsuario] = @CveUsuario AND [Borrado] = (0);

        UPDATE [dbo].[Sis_UsuariosClientes]
        SET [Orden] = @Orden, [EsPrincipal] = @EsPrincipal, [Activo] = (1),
            [FechaActualizacion] = SYSDATETIME(), [Borrado] = (0), [FechaBorrado] = NULL
        WHERE [CveUsuario] = @CveUsuario AND [CveCliente] = @CveCliente;

        IF @@ROWCOUNT = (0)
            INSERT INTO [dbo].[Sis_UsuariosClientes]
                ([CveUsuario], [CveCliente], [Orden], [EsPrincipal], [Activo], [CveUsuarioRegistro])
            VALUES
                (@CveUsuario, @CveCliente, @Orden, @EsPrincipal, (1), @CveUsuarioEjecuta);

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

CREATE OR ALTER PROCEDURE [dbo].[PA_Clientes_EstatusUpdate]
    @CveCliente int,
    @Activo bit,
    @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 C
        SET C.[Estatus] = @Activo, C.[CveUsuarioModifico] = @CveUsuarioEjecuta
        FROM [dbo].[Clientes] C
        WHERE C.[CveCliente] = @CveCliente AND C.[Borrado] = (0)
          AND EXISTS
          (
              SELECT 1 FROM [dbo].[Sis_Usuarios] U INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
              WHERE U.[CveUsuario] = @CveUsuarioEjecuta
                AND R.[NombreRol] IN ('SuperUsuario', 'Administrador')
                AND U.[Activo] = (1) AND U.[Borrado] = (0)
          );
        IF @@ROWCOUNT = (0)
        BEGIN
            SET @ErrorMostrar = 'Sólo un Administrador o SuperUsuario puede cambiar el estado del cliente.';
            SET @ErrorTipo = 1;
            RETURN;
        END;
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al cambiar el estado del cliente; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO