

/*---------------------------------------------------------------------------------------------------------------------------------------------*/


-------------------
-- SCHEMA
-------------------

CREATE SCHEMA SUL;

-------------------
-- TABLES
-------------------
CREATE TABLE [SUL].[SulfurPrintRatings](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ProductId] [int] NOT NULL,
    [AddDate] [date] NOT NULL,
    [SurfaceCracksId] [INT] NOT NULL,
    [SubsurfaceCracksId] [INT] NOT NULL,
    [PinholesId] [INT] NOT NULL,
    [InternalCracksId] [INT] NOT NULL,
    [CenterCracksId] [INT] NOT NULL,
    [CenterSegregationId] [INT] NOT NULL,
    [RatedById] [int] NULL,
    [H1] [int] NULL,
    [H2] [int] NULL,
    [H3] [int] NULL,
    [H4] [int] NULL,
    [H5] [int] NULL,
    [W1] [int] NULL,
    [W2] [int] NULL,
    [W3] [int] NULL,
    [W4] [int] NULL,
    [W5] [int] NULL,
    [D1] [int] NULL,
    [D2] [int] NULL,
    [CurrentUser] [varchar](100) NULL,
    FOREIGN KEY (ProductId) REFERENCES CAS.Products(Id),
    FOREIGN KEY (RatedById) REFERENCES SUL.RatedBy(Id),
    FOREIGN KEY (SurfaceCracksId) REFERENCES SUL.Ratings(Id),
    FOREIGN KEY (SubsurfaceCracksId) REFERENCES SUL.Ratings(Id),
    FOREIGN KEY (PinholesId) REFERENCES SUL.Ratings(Id),
    FOREIGN KEY (InternalCracksId) REFERENCES SUL.Ratings(Id),
    FOREIGN KEY (CenterCracksId) REFERENCES SUL.Ratings(Id),
    FOREIGN KEY (CenterSegregationId) REFERENCES SUL.Ratings(Id)
    )




USE [MES]
GO

/****** Object:  Table [SUL].[RatedBy]    Script Date: 6/28/2023 4:17:40 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SUL].[RatedBy](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]
GO


USE [MES]
GO

/****** Object:  Table [SUL].[Rating]    Script Date: 6/28/2023 4:18:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SUL].[Ratings](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Value] [varchar](50) NULL
) ON [PRIMARY]
GO




USE [MES]
GO
/****** Object:  StoredProcedure [SUL].[GetSulpherPrintCatalog]    Script Date: 7/5/2023 3:25:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE  [SUL].[GetSulpherPrintCatalog]
	
AS
BEGIN
	-----------------------
	-- list of catalogs
	-----------------------
	select [Index]=1, Name='RATINGS'  
	union select 2,'RATEDBY'
		
	-----------------------
	-- Ratings
	-----------------------
	 SELECT * FROM SUL.Ratings

		
	-----------------------
	-- Rated By
	-----------------------
	SELECT * FROM SUL.RatedBy;
END




---------------------------------------------------------------------------------------------



SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [SUL].[UpsertSulfurRating]
    @ProductId INT,
    @SurfaceCracksId INT,
    @SubsurfaceCracksId INT,
    @PinholesId INT,
    @InternalCracksId INT,
    @CenterCracksId INT,
    @CenterSegregationId INT,
    @RatedBy VARCHAR(50) = NULL,
    @H1 INT = null,
    @H2 INT = null,
    @H3 INT = null,
    @H4 INT = null,
    @H5 INT = null,
    @W1 INT = null,
    @W2 INT = null,
    @W3 INT = null,
    @W4 INT = null,
    @W5 INT = null,
    @D1 INT = null,
    @D2 INT = null,
    @CurrentUser VARCHAR(100)
AS
BEGIN

    -------------------------
    --- Common Variables ----
    -------------------------
    
    declare @procName VARCHAR(300)
    declare @logMsg VARCHAR(MAX)
    SELECT @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
    DECLARE @buf varchar(200) = ''
    SET NOCOUNT ON    

    declare @RatedById INT = 0
    DECLARE @AddDate DATETIME = GETUTCDATE()   -- Insert/Update sulfur entry



    BEGIN TRY
        
        select @RatedById = Id from SUL.RatedBy where [Name] = @RatedBy

        IF @RatedById is NULL
        BEGIN
            INSERT INTO [SUL].[RatedBy]([Name]) VALUES (@RatedBy)
            DECLARE @ratedByrc int = @@ROWCOUNT
            IF @ratedByrc > 0
                BEGIN
                set @RatedById = @@IDENTITY
                    set @logMsg = 'Inserted row on SUL.RatedBy with Id: ' + cast(@RatedById as varchar) + ' and Name: ' + @RatedBy 
                    EXEC SYSTEM.LogMsg @message = @logMsg
                    ,@procedure = @procName
                    ,@typeCode = 'DEBUG'
                    ,@logError = 0
                END
            
                IF  @ratedByrc = 0
                BEGIN
                SET @logMsg = 'Error Inserting row on SUL.RatedBy'
                RAISERROR( @logMsg ,0,1) WITH NOWAIT
                END
        END
    MERGE SUL.SulfurPrintRatings AS target
    USING (SELECT @ProductId AS ProductId) AS source
    ON target.ProductId = source.ProductId
    WHEN MATCHED THEN
        UPDATE SET
            AddDate = @AddDate,
            SurfaceCracksId = @SurfaceCracksId,
            SubsurfaceCracksId = @SubsurfaceCracksId,
            PinholesId = @PinholesId,
            InternalCracksId = @InternalCracksId,
            CenterCracksId = @CenterCracksId,
            CenterSegregationId = @CenterSegregationId,
            @RatedById = @RatedById,
            H1 = @H1,
            H2 = @H2,
            H3 = @H3,
            H4 = @H4,
            H5 = @H5,
            W1 = @W1,
            W2 = @W2,
            W3 = @W3,
            W4 = @W4,
            W5 = @W5,
            D1 = @D1,
            D2 = @D2,
            CurrentUser = @CurrentUser

    WHEN NOT MATCHED THEN
        INSERT (ProductId, AddDate, SurfaceCracksId, SubsurfaceCracksId, PinholesId, InternalCracksId, CenterCracksId, CenterSegregationId, RatedById, H1, H2, H3, H4, H5, W1, W2, W3, W4, W5, D1, D2, CurrentUser)
        VALUES (@ProductId, @AddDate, @SurfaceCracksId, @SubsurfaceCracksId, @PinholesId, @InternalCracksId, @CenterCracksId, @CenterSegregationId, @RatedById, @H1, @H2, @H3, @H4, @H5, @W1, @W2, @W3, @W4, @W5, @D1, @D2, @CurrentUser);
        declare @rc int =@@ROWCOUNT
    if @rc>0
            begin
                set @logMsg = 'Updated Sulfur Print Rating for ProductId: ' + cast(@ProductId as varchar) +  @buf
                EXEC SYSTEM.LogMsg @message = @logMsg
                ,@procedure = @procName
                ,@typeCode = 'DEBUG'
                ,@logError = 0
            end

    END TRY
        BEGIN CATCH
            SELECT     @logMsg = ERROR_MESSAGE()

            SET @logMsg = 'Failed ' + @logMsg
                RAISERROR( @logMsg ,0,1) WITH NOWAIT

            EXEC SYSTEM.LogMsg @message = @logMsg
                ,@procedure = @procName
                ,@typeCode = 'ERROR'
                ,@logError = 1
        END CATCH
END;



EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [casterproducts_readWrite] -- The role
,@name = N'securedsp-SUL.UpsertSulfurRating' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [casterproducts_read] -- The role
,@name = N'securedsp-SUL.GetSulpherPrintCatalog' -- the procedure
,@value = N'' 
GO


EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [casterproducts_readWrite] -- The role
,@name = N'securedsp-SUL.GetSulpherPrintCatalog' -- the procedure
,@value = N'' 
GO

