PROCEDURES.sql

-------------------------------------------------------------------------------------------------
-- PROCEDURES_STRUCTURE
-------------------------------------------------------------------------------------------------
USE VideoHosting;

PRINT N'➕ PROCEDURE JOB IS STARTED';

GO
CREATE or ALTER PROCEDURE GET_VIDEO_FOR_USER @USER_NAME NVARCHAR(30) AS 
BEGIN
    DECLARE @USER_AGE DATETIME = (SELECT [USERS].[BIRTHDAY_DT] FROM [USERS] WHERE [USERNAME] = @USER_NAME)
    SELECT
        [VIDEO].[TITLE],
        [VIDEO].[DESCRIPTION],
        [USERS].[USERNAME] [USER],
        [VIDEO].[CREATED_DT] [CREATED_DT]
	FROM [VIDEOS] [VIDEO]
    LEFT JOIN [USERS] [USERS] ON [VIDEO].[USER_UID]=[USERS].[UID]
	LEFT JOIN [AGE_RESTRICTIONS] [AGE_REST] ON [VIDEO].[AGE_RESTRICTIONS_UID]=[AGE_REST].[UID]
    WHERE [AGE_REST].[AGE] <= DATEDIFF(YEAR, @USER_AGE, GETDATE()) AND [USERS].[USERNAME] != @USER_NAME;
END;
GO
PRINT N'➕ GET_VIDEO_FOR_USER WAS CREATED';


GO
CREATE or ALTER PROCEDURE GET_VIDEO_BY_TAG @TAG_TITLE NVARCHAR(30) AS 
BEGIN
	SELECT
        [VIDEO].[TITLE],
        [VIDEO].[DESCRIPTION],
        [USERS].[USERNAME] [USER],
        [VIDEO].[CREATED_DT] [CREATED_DT]
	FROM [FK_VIDEO_TAGS] [VIDEO_TAG]
    LEFT JOIN [TAGS] [TAG] ON [VIDEO_TAG].[TAG_UID]=[TAG].[UID]
    LEFT JOIN [VIDEOS] [VIDEO] ON [VIDEO_TAG].[VIDEO_UID]=[VIDEO].[UID]
    LEFT JOIN [USERS] [USERS] ON [VIDEO].[USER_UID]=[USERS].[UID]
    WHERE [TAG].[TITLE] = @TAG_TITLE
END;
GO
PRINT N'➕ GET_VIDEO_BY_TAG WAS CREATED';


GO
CREATE or ALTER PROCEDURE SET_TAG_TO_VIDEO @VIDEO_TITLE NVARCHAR(100), @TAG_TITLE NVARCHAR(10) AS 
BEGIN
    DECLARE @VIDEO_UID UNIQUEIDENTIFIER = (SELECT [VIDEOS].[UID] FROM [VIDEOS] WHERE [TITLE] = @VIDEO_TITLE);
    IF NOT EXISTS (SELECT 1 FROM [VIDEOS] WHERE [UID] = @VIDEO_UID) 
    BEGIN
        RAISERROR('ERROR: Video with this title does not exist.', 16, 1);
        RETURN;
    END;

    DECLARE @TAG_UID UNIQUEIDENTIFIER = NEWID();
    IF EXISTS (SELECT 1 FROM [TAGS] WHERE [TITLE] = @TAG_TITLE) BEGIN
        SET @TAG_UID = (SELECT TOP 1 [UID] FROM [TAGS] WHERE [TITLE] = @TAG_TITLE)
    END ELSE BEGIN
        INSERT INTO [TAGS] ([UID], [TITLE]) VALUES (@TAG_UID, @TAG_TITLE);
    END;
    
    IF NOT EXISTS (SELECT 1 FROM [FK_VIDEO_TAGS] WHERE [VIDEO_UID] = @VIDEO_UID and [TAG_UID] = @TAG_UID) BEGIN
        INSERT INTO [FK_VIDEO_TAGS] ([VIDEO_UID], [TAG_UID]) VALUES (@VIDEO_UID, @TAG_UID);
    END
END;
GO
PRINT N'➕ SET_TAG_TO_VIDEO WAS CREATED';