Artist Queries.sql

--DROP PROCEDURE IF EXISTS AddNewArtist;

--CREATE PROCEDURE AddNewArtist
--    @Username VARCHAR(50),
--    @Password VARCHAR(50),
--    @BirthYear INT,
--    @Name VARCHAR(100),
--    @Email VARCHAR(100),
--    @Location VARCHAR(100),
--    @SubscriptionDate DATE,
--    @ArtName VARCHAR(100)
--AS
--BEGIN
--    DECLARE @UserID INT;
    
--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;

--		-- Check if the Username is unique
--        IF EXISTS (SELECT 1 FROM Users WHERE Username = @Username)
--        BEGIN
--            RAISERROR('Username already exists.', 16, 1);
--            ROLLBACK TRANSACTION;
--            RETURN;
--        END

--        -- Check if the Email is unique
--        IF EXISTS (SELECT 1 FROM Users WHERE Email = @Email)
--        BEGIN
--            RAISERROR('Email already exists.', 16, 1);
--            ROLLBACK TRANSACTION;
--            RETURN;
--        END
        
--        -- Insert into Users table
--        INSERT INTO Users (Username, Password, BirthYear, Name, Email, Location, UserType)
--        VALUES (@Username, @Password, @BirthYear, @Name, @Email, @Location, 'Artist');
        
--        -- Get the UserID of the newly inserted user
--        SET @UserID = SCOPE_IDENTITY();
        
--        -- Insert into Premium table
--        INSERT INTO Premium (UserID, SubscriptionDate)
--        VALUES (@UserID, @SubscriptionDate);
        
--        -- Insert into Artist table
--        INSERT INTO Artist (artist_id, [Art Name])
--        VALUES (@UserID, @ArtName);

--        -- Insert into Wallets table
--        INSERT INTO Wallets (user_id, balance)
--        VALUES (@UserID, 1000.00);  -- Initial balance
        
--        -- Commit transaction
--        COMMIT TRANSACTION;
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        ROLLBACK TRANSACTION;

--        -- Raise the error again to notify the caller
--        THROW;
--    END CATCH
--END;


--EXEC AddNewArtist
--    @Username = 'gha',
--    @Password = 'password123',
--    @BirthYear = 1890,
--    @Name = 'Taylor Swift',
--    @Email = 'john.de@example.com',
--    @Location = 'California',
--    @SubscriptionDate = '2024-07-10',
--    @ArtName = 'Taylor';


---------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS AddNewMusic;

--CREATE PROCEDURE AddNewMusic
--    @title VARCHAR(255),
--    @duration TIME,
--    @genre VARCHAR(255),
--    @zone VARCHAR(50) = NULL,
--    @age_category VARCHAR(50) = NULL,
--    @can_add_to_playlist BIT = 1,
--    @album_id INT = NULL,  -- Existing parameter for album_id
--    @artist_id INT         -- New parameter for artist_id
--AS
--BEGIN
--    -- Check if album_id exists in Album table
--    IF @album_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM Album WHERE album_id = @album_id)
--    BEGIN
--        -- If album_id is invalid, set it to NULL
--        SET @album_id = NULL;
--    END

--    -- Insert the new music record
--    INSERT INTO Music (title, duration, genre, zone, age_category, can_add_to_playlist, album_id)
--    VALUES (@title, @duration, @genre, @zone, @age_category, @can_add_to_playlist, @album_id);

--    -- Get the ID of the newly inserted music record
--    DECLARE @new_music_id INT;
--    SET @new_music_id = SCOPE_IDENTITY();

--    -- Insert the record into the Music_Artist table
--    INSERT INTO Music_Artist (music_id, artist_id)
--    VALUES (@new_music_id, @artist_id);
--END;


--EXEC AddNewMusic
--    @title = 'New Song with Album',
--    @duration = '00:04:20',
--    @genre = 'Jazz',
--    @zone = 'EU',
--    @age_category = 'Adult',
--    @can_add_to_playlist = 1,
--    @album_id = 5,  -- Assuming 5 is a valid album_id
--    @artist_id = 1;  -- Assuming 7 is a valid artist_id



----------------------------------------------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS AddNewAlbum;

--CREATE PROCEDURE AddNewAlbum
--    @AlbumTitle VARCHAR(255),
--    @ReleaseDate DATE,
--    @AlbumGenre VARCHAR(255),
--    @AlbumZone VARCHAR(255),
--    @AlbumAgeCategory VARCHAR(255),
--    @ArtistID INT,
--    @MusicTracks dbo.MusicTrackType READONLY
--AS
--BEGIN
--    DECLARE @AlbumID INT;

--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;
        
--        -- Insert into Album table
--        INSERT INTO Album (title, release_date, genre, zone, age_category)
--        VALUES (@AlbumTitle, @ReleaseDate, @AlbumGenre, @AlbumZone, @AlbumAgeCategory);
        
--        -- Get the AlbumID of the newly inserted album
--        SET @AlbumID = SCOPE_IDENTITY();
        
--        -- Insert into Album_Artist table
--        INSERT INTO Album_Artist (album_id, artist_id)
--        VALUES (@AlbumID, @ArtistID);
        
--        -- Insert into Music table and capture inserted IDs
--        CREATE TABLE #InsertedMusic (music_id INT, title VARCHAR(255));

--        INSERT INTO Music (title, duration, genre, zone, age_category, can_add_to_playlist, album_id)
--        OUTPUT inserted.music_id, inserted.title INTO #InsertedMusic
--        SELECT title, duration, genre, zone, age_category, can_add_to_playlist, @AlbumID
--        FROM @MusicTracks;
        
--        -- Insert into Music_Artist table using captured IDs
--        INSERT INTO Music_Artist (music_id, artist_id)
--        SELECT music_id, @ArtistID
--        FROM #InsertedMusic;

--        -- Drop temporary table
--        DROP TABLE #InsertedMusic;

--        -- Commit transaction
--        COMMIT TRANSACTION;
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        ROLLBACK TRANSACTION;
        
--        -- Raise the error again to notify the caller
--        THROW;
--    END CATCH
--END;



---- Define the table type parameter and populate with example tracks
--DECLARE @Tracks dbo.MusicTrackType;

--INSERT INTO @Tracks (title, duration, genre, zone, age_category, can_add_to_playlist)
--VALUES
--    ('Track dddaaa', '00:03:30', 'JAZZ', 'Zone 1', '18+', 1),
--    ('Track bjj22', '00:04:00', 'Rock', 'Zone 1', '18+', 1),
--    ('Track 33c', '00:02:45', 'Rock', 'Zone 1', '18+', 1);

---- Execute the stored procedure AddNewAlbum
--EXEC AddNewAlbum
--    @AlbumTitle = 'SSS',
--    @ReleaseDate = '2022-09-11',
--    @AlbumGenre = 'Classic',
--    @AlbumZone = 'Zone 0',
--    @AlbumAgeCategory = '2+',
--    @ArtistID = 2,  -- Assuming 5 is a valid artist_id
--    @MusicTracks = @Tracks;

----------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE RemoveSong
--    @MusicID INT
--AS
--BEGIN
--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;

--        -- Check if the music exists
--        IF NOT EXISTS (SELECT 1 FROM Music WHERE music_id = @MusicID)
--        BEGIN
--            -- Rollback transaction
--            ROLLBACK TRANSACTION;

--            -- Raise an error
--            THROW 50001, 'Music record not found', 1;
--            RETURN;
--        END

--        -- Delete from Music table
--        DELETE FROM Music WHERE music_id = @MusicID;

--        -- Commit transaction
--        COMMIT TRANSACTION;
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        IF @@TRANCOUNT > 0
--            ROLLBACK TRANSACTION;

--        -- Get error information
--        DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
--        SELECT 
--            @ErrorMessage = ERROR_MESSAGE(), 
--            @ErrorSeverity = ERROR_SEVERITY(), 
--            @ErrorState = ERROR_STATE();

--        -- Raise the error again to notify the caller
--        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
--    END CATCH
--END;

--EXEC RemoveSong @MusicID = 1; -- Replace 1 with the actual MusicID of the song to be removed
------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE RemoveAlbum
--    @AlbumID INT
--AS
--BEGIN
--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;

--        -- Check if the album exists
--        IF NOT EXISTS (SELECT 1 FROM Album WHERE album_id = @AlbumID)
--        BEGIN
--            -- Raise an error and rollback transaction
--            THROW 50001, 'Album record not found', 1;
--        END

--        -- Delete the album (this will cascade delete related records)
--        DELETE FROM Album WHERE album_id = @AlbumID;

--        -- Commit transaction
--        COMMIT TRANSACTION;
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        ROLLBACK TRANSACTION;

--        -- Return error message
--        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
--        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
--        DECLARE @ErrorState INT = ERROR_STATE();
--        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
--    END CATCH
--END;


--EXEC RemoveAlbum @AlbumID = 8;

------------------------------------------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS AddNewConcertWithTickets
--CREATE PROCEDURE AddNewConcertWithTickets
--    @ArtistID INT,
--    @ConcertDate DATE,
--    @ConcertLocation NVARCHAR(255),
--    @RegularTicketPrice DECIMAL(10, 2),
--    @VIPTicketPrice DECIMAL(10, 2),
--    @PremiumTicketPrice DECIMAL(10, 2),
--    @NumRegularTickets INT,
--    @NumVIPTickets INT,
--    @NumPremiumTickets INT
--AS
--BEGIN
--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;

--        -- Verify artist exists
--        IF NOT EXISTS (SELECT 1 FROM Artist WHERE artist_id = @ArtistID)
--        BEGIN
--            THROW 50002, 'Artist ID is invalid.', 1;
--        END

--        -- Check if the artist already has a concert on the given date
--        IF EXISTS (
--            SELECT 1
--            FROM Concert_Artists ca
--            INNER JOIN Concerts c ON ca.concert_id = c.concert_id
--            WHERE ca.artist_id = @ArtistID AND c.date = @ConcertDate
--        )
--        BEGIN
--            THROW 50003, 'Artist already has a concert scheduled on this date.', 1;
--        END

--        -- Insert new concert
--        INSERT INTO Concerts (
--            date, location, is_cancelled,
--            NumRegularTickets, NumVIPTickets, NumPremiumTickets,
--            RegularTicketPrice, VIPTicketPrice, PremiumTicketPrice
--        )
--        VALUES (
--            @ConcertDate, @ConcertLocation, 0,
--            @NumRegularTickets, @NumVIPTickets, @NumPremiumTickets,
--            @RegularTicketPrice, @VIPTicketPrice, @PremiumTicketPrice
--        );

--        -- Get the ID of the newly inserted concert
--        DECLARE @NewConcertID INT = SCOPE_IDENTITY();

--        -- Insert into Concert_Artists
--        INSERT INTO Concert_Artists (concert_id, artist_id)
--        VALUES (@NewConcertID, @ArtistID);

--        -- Insert Regular Tickets
--        DECLARE @i INT = 1;
--        WHILE @i <= @NumRegularTickets
--        BEGIN
--            INSERT INTO Tickets (user_id, concert_id, is_valid, is_purchased, ticket_type)
--            VALUES (NULL, @NewConcertID, 1, 0, 'Regular');
--            SET @i = @i + 1;
--        END

--        -- Insert VIP Tickets
--        SET @i = 1;
--        WHILE @i <= @NumVIPTickets
--        BEGIN
--            INSERT INTO Tickets (user_id, concert_id, is_valid, is_purchased, ticket_type)
--            VALUES (NULL, @NewConcertID, 1, 0, 'VIP');
--            SET @i = @i + 1;
--        END

--        -- Insert Premium Tickets
--        SET @i = 1;
--        WHILE @i <= @NumPremiumTickets
--        BEGIN
--            INSERT INTO Tickets (user_id, concert_id, is_valid, is_purchased, ticket_type)
--            VALUES (NULL, @NewConcertID, 1, 0, 'Premium');
--            SET @i = @i + 1;
--        END

--        -- Commit transaction
--        COMMIT TRANSACTION;
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        ROLLBACK TRANSACTION;

--        -- Raise the error again to notify the caller
--        THROW;
--    END CATCH
--END;



-- Example of executing the procedure
--EXEC AddNewConcertWithTickets
--    @ArtistID = 2,
--    @ConcertDate = '2020-10-05',
--    @ConcertLocation = 'Concert Hall XX',
--    @RegularTicketPrice = 60.00,
--    @VIPTicketPrice = 12.00,
--    @PremiumTicketPrice = 200.00,
--    @NumRegularTickets = 1,
--    @NumVIPTickets = 1,
--    @NumPremiumTickets = 2;

------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE GetSongsByArtist
--    @artist_id INT
--AS
--BEGIN
--    -- Check if the artist_id is valid
--    IF NOT EXISTS (SELECT 1 FROM Artist WHERE artist_id = @artist_id)
--    BEGIN
--        RAISERROR ('Invalid artist ID.', 16, 1);
--        RETURN;
--    END

--    -- Check if the artist has any songs published
--    IF NOT EXISTS (
--        SELECT 1 
--        FROM Music_Artist ma 
--        WHERE ma.artist_id = @artist_id
--    )
--    BEGIN
--        RAISERROR ('The artist has no music published.', 16, 1);
--        RETURN;
--    END

--    -- Get all songs published by the valid artist
--    SELECT 
--        m.music_id,
--        m.title,
--        m.duration,
--        m.genre,
--        m.zone,
--        m.age_category,
--        m.album_id
--    FROM 
--        Music m
--    INNER JOIN 
--        Music_Artist ma ON m.music_id = ma.music_id
--    WHERE 
--        ma.artist_id = @artist_id;
--END;

---- Declare variables for testing
--DECLARE @artist_id INT = 1;

---- Execute the stored procedure
--EXEC GetSongsByArtist @artist_id;
------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE GetAlbumsByArtist
--    @artist_id INT
--AS
--BEGIN
--    -- Check if the artist_id is valid
--    IF NOT EXISTS (SELECT 1 FROM Artist WHERE artist_id = @artist_id)
--    BEGIN
--        RAISERROR ('Invalid artist ID.', 16, 1);
--        RETURN;
--    END

--    -- Check if the artist has any albums published
--    IF NOT EXISTS (
--        SELECT 1 
--        FROM Album_Artist aa 
--        WHERE aa.artist_id = @artist_id
--    )
--    BEGIN
--        RAISERROR ('The artist has no albums published.', 16, 1);
--        RETURN;
--    END

--    -- Get albums by the artist
--    SELECT 
--        a.album_id,
--        a.title AS album_title,
--        a.release_date,
--        a.genre AS album_genre
--    FROM 
--        Album a
--    INNER JOIN 
--        Album_Artist aa ON a.album_id = aa.album_id
--    WHERE 
--        aa.artist_id = @artist_id;
--END;

------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE GetMusicByAlbum
--    @album_id INT
--AS
--BEGIN
--    -- Check if the album_id is valid
--    IF NOT EXISTS (SELECT 1 FROM Album WHERE album_id = @album_id)
--    BEGIN
--        RAISERROR ('Invalid album ID.', 16, 1);
--        RETURN;
--    END

--    -- Retrieve music tracks of the album
--    SELECT 
--        m.music_id,
--        m.title AS music_title,
--        m.duration,
--        m.genre AS music_genre,
--        m.zone,
--        m.age_category
--    FROM 
--        Music m
--    WHERE 
--        m.album_id = @album_id;
--END;

------------------------------------------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS SendMessage;
--CREATE PROCEDURE SendMessage
--    @SenderID INT,
--    @ReceiverID INT,
--    @MessageContent NVARCHAR(MAX)
--AS
--BEGIN
--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;

--        -- Check if SenderID exists in Users table
--        IF NOT EXISTS (SELECT 1 FROM Users WHERE ID = @SenderID)
--        BEGIN
--            THROW 50000, 'Sender ID is invalid.', 1;
--        END;

--        -- Check if ReceiverID exists in Users table
--        IF NOT EXISTS (SELECT 1 FROM Users WHERE ID = @ReceiverID)
--        BEGIN
--            THROW 50001, 'Receiver ID is invalid.', 1;
--        END;

--        -- Check if the users are friends and the friendship request is accepted
--        IF NOT EXISTS (
--            SELECT 1
--            FROM Friends
--            WHERE (UserID = @SenderID AND FriendID = @ReceiverID AND Status = 'Accepted')
--               OR (UserID = @ReceiverID AND FriendID = @SenderID AND Status = 'Accepted')
--        )
--        BEGIN
--            THROW 50002, 'Users are not friends or friendship request is not accepted.', 1;
--        END;

--        -- Insert the message into the Chat table
--        INSERT INTO Chat (sender_id, receiver_id, message_content, sent_at)
--        VALUES (@SenderID, @ReceiverID, @MessageContent, GETDATE());

--        -- Commit transaction
--        COMMIT TRANSACTION;
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        ROLLBACK TRANSACTION;

--        -- Raise the error again to notify the caller
--        THROW;
--    END CATCH
--END;

---- Example test case
--DECLARE @SenderID INT = 2;
--DECLARE @ReceiverID INT = 1;
--DECLARE @MessageContent NVARCHAR(MAX) = 'Hello, friend!';

--EXEC SendMessage @SenderID, @ReceiverID, @MessageContent;

------------------------------------------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS AddToFavoriteSongs
--CREATE PROCEDURE AddToFavoriteSongs
--    @user_id INT,
--    @song_id INT
--AS
--BEGIN
--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;

--        -- Check if the song exists
--        IF EXISTS (SELECT 1 FROM Music WHERE music_id = @song_id)
--        BEGIN
--            -- Check if the user exists and is a Premium user
--            IF EXISTS (SELECT 1 FROM Premium WHERE UserID = @user_id)
--            BEGIN
--                -- Check if the song is already in the user's favorite songs
--                IF NOT EXISTS (SELECT 1 FROM Favorite_Songs WHERE user_id = @user_id AND song_id = @song_id)
--                BEGIN
--                    -- Insert the song into the Favorite_Songs table
--                    INSERT INTO Favorite_Songs (user_id, song_id)
--                    VALUES (@user_id, @song_id);

--                    -- Commit transaction
--                    COMMIT TRANSACTION;
--                END
--                ELSE
--                BEGIN
--                    -- Song is already in the favorite list
--                    ROLLBACK TRANSACTION;
--                    RAISERROR ('Song is already in the favorite list.', 16, 1);
--                END
--            END
--            ELSE
--            BEGIN
--                -- User does not exist or is not a Premium user
--                ROLLBACK TRANSACTION;
--                RAISERROR ('User does not exist or is not a Premium user.', 16, 1);
--            END
--        END
--        ELSE
--        BEGIN
--            -- Song does not exist
--            ROLLBACK TRANSACTION;
--            RAISERROR ('Song does not exist.', 16, 1);
--        END
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        IF @@TRANCOUNT > 0
--            ROLLBACK TRANSACTION;

--        -- Retrieve error information
--        DECLARE @ErrorMessage NVARCHAR(4000);
--        DECLARE @ErrorSeverity INT;
--        DECLARE @ErrorState INT;

--        SELECT
--            @ErrorMessage = ERROR_MESSAGE(),
--            @ErrorSeverity = ERROR_SEVERITY(),
--            @ErrorState = ERROR_STATE();

--        -- Raise the error again to notify the caller
--        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
--    END CATCH
--END;



---- Testing the AddToFavoriteSongs procedure
--DECLARE @user_id INT = 2;
--DECLARE @song_id INT = 34;
---- Execute the procedure
--EXEC AddToFavoriteSongs @user_id, @song_id;

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

--CREATE PROCEDURE AddToFavoritePlaylists
--    @user_id INT,
--    @playlist_id INT
--AS
--BEGIN
--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;

--        -- Check if the playlist exists
--        IF EXISTS (SELECT 1 FROM Playlist WHERE playlist_id = @playlist_id)
--        BEGIN
--            -- Check if the user exists
--            IF EXISTS (SELECT 1 FROM Premium WHERE UserID = @user_id)
--            BEGIN
--                -- Check if the playlist is already in the user's favorite playlists
--                IF NOT EXISTS (SELECT 1 FROM Favorite_Playlists WHERE user_id = @user_id AND playlist_id = @playlist_id)
--                BEGIN
--                    -- Insert the playlist into the Favorite_Playlists table
--                    INSERT INTO Favorite_Playlists (user_id, playlist_id)
--                    VALUES (@user_id, @playlist_id);

--                    -- Commit transaction
--                    COMMIT TRANSACTION;
--                END
--                ELSE
--                BEGIN
--                    -- Playlist is already in the favorites
--                    ROLLBACK TRANSACTION;
--                    RAISERROR ('Playlist is already in the favorites.', 16, 1);
--                END
--            END
--            ELSE
--            BEGIN
--                -- User does not exist
--                ROLLBACK TRANSACTION;
--                RAISERROR ('User does not exist.', 16, 1);
--            END
--        END
--        ELSE
--        BEGIN
--            -- Playlist does not exist
--            ROLLBACK TRANSACTION;
--            RAISERROR ('Playlist does not exist.', 16, 1);
--        END
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        IF @@TRANCOUNT > 0
--            ROLLBACK TRANSACTION;

--        -- Retrieve error information
--        DECLARE @ErrorMessage NVARCHAR(4000);
--        DECLARE @ErrorSeverity INT;
--        DECLARE @ErrorState INT;

--        SELECT
--            @ErrorMessage = ERROR_MESSAGE(),
--            @ErrorSeverity = ERROR_SEVERITY(),
--            @ErrorState = ERROR_STATE();

--        -- Raise the error again to notify the caller
--        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
--    END CATCH
--END;



---- Testing the AddToFavoritePlaylists procedure
--DECLARE @user_id INT = 2;
--DECLARE @playlist_id INT = 1;

---- Execute the procedure
--EXEC AddToFavoritePlaylists @user_id, @playlist_id;

---------------------------------------------------------------------------------------------------------
--!!!
--CREATE PROCEDURE AddSongToPlaylist
--    @user_id INT,
--    @playlist_id INT,
--    @music_id INT
--AS
--BEGIN
--    BEGIN TRY
--        -- Start transaction
--        BEGIN TRANSACTION;

--        -- Check if the song exists
--        IF EXISTS (SELECT 1 FROM Music WHERE music_id = @music_id)
--        BEGIN
--            -- Check if the song can be added to the playlist
--            IF (SELECT can_add_to_playlist FROM Music WHERE music_id = @music_id) = 1
--            BEGIN
--                -- Check if the user exists and is a Premium user
--                IF EXISTS (SELECT 1 FROM Premium WHERE UserID = @user_id)
--                BEGIN
--                    -- Check if the song is already in the playlist
--                    IF NOT EXISTS (SELECT 1 FROM Playlist_Music WHERE playlist_id = @playlist_id AND music_id = @music_id)
--                    BEGIN
--                        -- Insert the song into the Playlist_Music table
--                        INSERT INTO Playlist_Music (playlist_id, music_id)
--                        VALUES (@playlist_id, @music_id);

--                        -- Commit transaction
--                        COMMIT TRANSACTION;
--                    END
--                    ELSE
--                    BEGIN
--                        -- Song is already in the playlist
--                        ROLLBACK TRANSACTION;
--                        RAISERROR ('Song is already in the playlist.', 16, 1);
--                    END
--                END
--                ELSE
--                BEGIN
--                    -- User does not exist or is not a Premium user
--                    ROLLBACK TRANSACTION;
--                    RAISERROR ('User does not exist or is not a Premium user.', 16, 1);
--                END
--            END
--            ELSE
--            BEGIN
--                -- Song cannot be added to playlist
--                ROLLBACK TRANSACTION;
--                RAISERROR ('Song cannot be added to the playlist.', 16, 1);
--            END
--        END
--        ELSE
--        BEGIN
--            -- Song does not exist
--            ROLLBACK TRANSACTION;
--            RAISERROR ('Song does not exist.', 16, 1);
--        END
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        IF @@TRANCOUNT > 0
--            ROLLBACK TRANSACTION;

--        -- Retrieve error information
--        DECLARE @ErrorMessage NVARCHAR(4000);
--        DECLARE @ErrorSeverity INT;
--        DECLARE @ErrorState INT;

--        SELECT
--            @ErrorMessage = ERROR_MESSAGE(),
--            @ErrorSeverity = ERROR_SEVERITY(),
--            @ErrorState = ERROR_STATE();

--        -- Raise the error again to notify the caller
--        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
--    END CATCH
--END;



---- Testing the AddSongToPlaylist procedure
--DECLARE @user_id INT = 2;
--DECLARE @playlist_id INT = 1;
--DECLARE @music_id INT = 28;

---- Execute the procedure
--EXEC AddSongToPlaylist @user_id, @playlist_id, @music_id;

---------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE SearchSongs
--    @search_keyword VARCHAR(255) = NULL,
--    @artist_name VARCHAR(100) = NULL,
--    @desired_genre VARCHAR(255) = NULL,
--    @desired_zone VARCHAR(50) = NULL,
--    @desired_age_category VARCHAR(50) = NULL
--AS
--BEGIN
--    -- Temporary table to store results
--    CREATE TABLE #TempSongs (
--        music_id INT,
--        title VARCHAR(255),
--        duration TIME,
--        genre VARCHAR(255),
--        zone VARCHAR(50),
--        age_category VARCHAR(50),
--        artist_name VARCHAR(100)
--    );

--    INSERT INTO #TempSongs
--    SELECT M.music_id, M.title, M.duration, M.genre, M.zone, M.age_category, U.Name AS artist_name
--    FROM Music M
--    INNER JOIN Music_Artist MA ON M.music_id = MA.music_id
--    INNER JOIN Artist A ON MA.artist_id = A.artist_id
--    INNER JOIN Premium P ON A.artist_id = P.UserID
--    INNER JOIN Users U ON P.UserID = U.ID
--    WHERE (@search_keyword IS NULL OR M.title LIKE '%' + @search_keyword + '%')
--    AND (@artist_name IS NULL OR U.Name LIKE '%' + @artist_name + '%')
--    AND (@desired_genre IS NULL OR M.genre = @desired_genre)
--    AND (@desired_zone IS NULL OR M.zone = @desired_zone)
--    AND (@desired_age_category IS NULL OR M.age_category = @desired_age_category);

--    -- Check if the temporary table is empty
--    IF NOT EXISTS (SELECT 1 FROM #TempSongs)
--    BEGIN
--        -- Raise an error or return a message
--        RAISERROR ('No songs found matching the search criteria.', 16, 1);
--        RETURN;
--    END

--    -- Return the results
--    SELECT * FROM #TempSongs;

--    -- Clean up
--    DROP TABLE #TempSongs;
--END
--GO


--EXEC SearchSongs  @desired_genre = 'Rock';

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

--CREATE PROCEDURE SearchAlbums
--    @search_keyword VARCHAR(255) = NULL,
--    @artist_name VARCHAR(100) = NULL,
--    @desired_genre VARCHAR(255) = NULL,
--    @desired_zone VARCHAR(255) = NULL,
--    @desired_age_category VARCHAR(255) = NULL
--AS
--BEGIN
--    -- Temporary table to store results
--    CREATE TABLE #TempAlbums (
--        album_id INT,
--        title VARCHAR(255),
--        release_date DATE,
--        genre VARCHAR(255),
--        zone VARCHAR(255),
--        age_category VARCHAR(255),
--        artist_name VARCHAR(100)
--    );

--    INSERT INTO #TempAlbums
--    SELECT A.album_id, A.title, A.release_date, A.genre, A.zone, A.age_category, U.Name AS artist_name
--    FROM Album A
--    INNER JOIN Album_Artist AA ON A.album_id = AA.album_id
--    INNER JOIN Artist AR ON AA.artist_id = AR.artist_id
--    INNER JOIN Premium P ON AR.artist_id = P.UserID
--    INNER JOIN Users U ON P.UserID = U.ID
--    WHERE (@search_keyword IS NULL OR A.title LIKE '%' + @search_keyword + '%')
--    AND (@artist_name IS NULL OR U.Name LIKE '%' + @artist_name + '%')
--    AND (@desired_genre IS NULL OR A.genre = @desired_genre)
--    AND (@desired_zone IS NULL OR A.zone = @desired_zone)
--    AND (@desired_age_category IS NULL OR A.age_category = @desired_age_category);

--    -- Check if the temporary table is empty
--    IF NOT EXISTS (SELECT 1 FROM #TempAlbums)
--    BEGIN
--        -- Raise an error or return a message
--        RAISERROR ('No albums found matching the search criteria.', 16, 1);
--        RETURN;
--    END

--    -- Return the results
--    SELECT * FROM #TempAlbums;

--    -- Clean up
--    DROP TABLE #TempAlbums;
--END
--GO


--EXEC SearchAlbums  @desired_age_category = '2+';

---------------------------------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS GetUpcomingConcertDetails
--CREATE PROCEDURE GetUpcomingConcertDetails
--AS
--BEGIN
--    SELECT 
--        u.Name AS artist,
--        c.location,
--        c.date,
--        c.NumRegularTickets,
--        c.RegularTicketPrice,
--        c.NumVIPTickets,
--        c.VIPTicketPrice,
--        c.NumPremiumTickets,
--        c.PremiumTicketPrice, 
--		c.concert_id
--    FROM 
--        Concerts c
--    JOIN 
--        Concert_Artists ca ON c.concert_id = ca.concert_id
--    JOIN 
--        Artist a ON ca.artist_id = a.artist_id
--    JOIN 
--        Premium p ON a.artist_id = p.UserID
--    JOIN 
--        Users u ON p.UserID = u.ID
--    WHERE 
--        c.is_cancelled = 0
--        AND c.date >= GETDATE(); -- Ensures the date has not expired
--END;

--EXEC GetUpcomingConcertDetails;

---------------------------------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS GetArtistConcertDetails
--CREATE PROCEDURE GetArtistConcertDetails
--    @ArtistID INT
--AS
--BEGIN
--    SELECT 
--        c.location,
--        CONVERT(NVARCHAR, c.date, 101) AS concert_date, -- Always show the date formatted as mm/dd/yyyy
--        CASE 
--            WHEN c.is_cancelled = 1 THEN 'Cancelled'
--            WHEN c.date < GETDATE() THEN 'Expired'
--            ELSE 'Pending'
--        END AS concert_status
--    FROM 
--        Concerts c
--    JOIN 
--        Concert_Artists ca ON c.concert_id = ca.concert_id
--    JOIN 
--        Artist a ON ca.artist_id = a.artist_id
--    WHERE 
--        a.artist_id = @ArtistID
--    ORDER BY 
--        c.date DESC; -- Order by concert date, soonest first
--END;




--EXEC GetArtistConcertDetails @ArtistID=1;

---------------------------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS BuyTicket
--CREATE PROCEDURE BuyTicket
--    @user_id INT,
--    @concert_id INT,
--    @ticket_type VARCHAR(50)
--AS
--BEGIN
--    -- Start a transaction
--    BEGIN TRANSACTION;

--    -- Declare variables to hold ticket prices, wallet balance, and available tickets
--    DECLARE @ticket_price DECIMAL(10, 2);
--    DECLARE @balance DECIMAL(10, 2);
--    DECLARE @available_tickets INT;
--    DECLARE @num_tickets_column NVARCHAR(50);
--    DECLARE @ticket_id INT;

--    -- Determine the ticket price and available tickets based on the ticket type
--    IF @ticket_type = 'Regular'
--    BEGIN
--        SELECT @ticket_price = RegularTicketPrice,
--               @available_tickets = NumRegularTickets
--        FROM Concerts
--        WHERE concert_id = @concert_id;
--        SET @num_tickets_column = 'NumRegularTickets';
--    END
--    ELSE IF @ticket_type = 'VIP'
--    BEGIN
--        SELECT @ticket_price = VIPTicketPrice,
--               @available_tickets = NumVIPTickets
--        FROM Concerts
--        WHERE concert_id = @concert_id;
--        SET @num_tickets_column = 'NumVIPTickets';
--    END
--    ELSE IF @ticket_type = 'Premium'
--    BEGIN
--        SELECT @ticket_price = PremiumTicketPrice,
--               @available_tickets = NumPremiumTickets
--        FROM Concerts
--        WHERE concert_id = @concert_id;
--        SET @num_tickets_column = 'NumPremiumTickets';
--    END

--    -- Check if there are available tickets of the requested type
--    IF @available_tickets <= 0
--    BEGIN
--        ROLLBACK TRANSACTION;
--        RAISERROR('No available tickets of the requested type.', 16, 1);
--        RETURN;
--    END

--    -- Get the user's wallet balance
--    SELECT @balance = balance
--    FROM Wallets
--    WHERE user_id = @user_id;

--    -- Check if the user has sufficient balance
--    IF @balance < @ticket_price
--    BEGIN
--        ROLLBACK TRANSACTION;
--        RAISERROR('Insufficient balance in the wallet.', 16, 1);
--        RETURN;
--    END

--    -- Deduct the ticket price from the user's wallet
--    UPDATE Wallets
--    SET balance = balance - @ticket_price
--    WHERE user_id = @user_id;

--    -- Update the concert's ticket count
--    DECLARE @sql NVARCHAR(MAX);
--    SET @sql = N'UPDATE Concerts SET ' + @num_tickets_column + ' = ' + @num_tickets_column + ' - 1 WHERE concert_id = @concert_id';
--    EXEC sp_executesql @sql, N'@concert_id INT', @concert_id;

--    -- Find an available ticket of the specified type for the given concert
--    SELECT TOP 1 @ticket_id = ticket_id
--    FROM Tickets
--    WHERE concert_id = @concert_id
--      AND ticket_type = @ticket_type
--      AND is_purchased = 0
--      AND is_valid = 1;

--    -- Update the ticket to mark it as purchased and assign it to the user
--    UPDATE Tickets
--    SET user_id = @user_id,
--        is_purchased = 1
--    WHERE ticket_id = @ticket_id;

--    -- Commit the transaction
--    COMMIT TRANSACTION;
--END;


--INSERT INTO Wallets (user_id, balance) VALUES (1, 5.00), (2, 1000.00), (3, 1500.00), (4, 2000.00);

--EXEC BuyTicket @user_id = 4, @concert_id = 2, @ticket_type = 'Premium';

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

--CREATE PROCEDURE UpgradeToPremium
--    @user_id INT
--AS
--BEGIN
--    DECLARE @balance DECIMAL(10, 2);
--    DECLARE @UserType NVARCHAR(50);
--    DECLARE @SubscriptionDate DATE = GETDATE();

--    BEGIN TRY
--        -- Start a transaction
--        BEGIN TRANSACTION;

--        -- Check if the user exists and is a normal user
--        SELECT @UserType = UserType, @balance = balance
--        FROM Users U
--        JOIN Wallets W ON U.ID = W.user_id
--        WHERE U.ID = @user_id;


--        IF @UserType != 'Normal'
--        BEGIN
--            RAISERROR('User is not a normal user.', 16, 1);
--            ROLLBACK TRANSACTION;
--            RETURN;
--        END

--        -- Check if the user has sufficient balance
--        IF @balance < 200
--        BEGIN
--            RAISERROR('Insufficient balance in the wallet.', 16, 1);
--            ROLLBACK TRANSACTION;
--            RETURN;
--        END

--        -- Deduct the cost from the user's wallet
--        UPDATE Wallets
--        SET balance = balance - 200
--        WHERE user_id = @user_id;

--        -- Update the user type to 'Premium'
--        UPDATE Users
--        SET UserType = 'Premium'
--        WHERE ID = @user_id;

--        -- Insert into Premium table
--        INSERT INTO Premium (UserID, SubscriptionDate)
--        VALUES (@user_id, @SubscriptionDate);

--        -- Remove the user from the NormalUser table
--        DELETE FROM NormalUser
--        WHERE UserID = @user_id;

--        -- Commit the transaction
--        COMMIT TRANSACTION;

--        -- Print success message
--        PRINT 'User has been successfully upgraded to Premium.';
--    END TRY
--    BEGIN CATCH
--        -- Rollback transaction in case of error
--        ROLLBACK TRANSACTION;

--        -- Raise the error again to notify the caller
--        THROW;
--    END CATCH
--END;



--EXEC UpgradeToPremium @NewUserID;

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

---- Create the procedure
--CREATE PROCEDURE GetAllArtists
--AS
--BEGIN
--    SELECT Users.Name
--    FROM Users
--    JOIN Artist ON Users.ID = Artist.artist_id;
--END;

---- Execute the procedure
--EXEC GetAllArtists;
------------------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS GetAlbumsNamesByArtist
--CREATE PROCEDURE GetAlbumsNamesByArtist
--    @artist_id INT
--AS
--BEGIN
--    -- Check if the artist_id is valid
--    IF NOT EXISTS (SELECT 1 FROM Artist WHERE artist_id = @artist_id)
--    BEGIN
--        RAISERROR ('Invalid artist ID.', 16, 1);
--        RETURN;
--    END

--    -- Check if the artist has any albums published
--    IF NOT EXISTS (
--        SELECT 1 
--        FROM Album_Artist aa 
--        WHERE aa.artist_id = @artist_id
--    )
--    BEGIN
--        RAISERROR ('The artist has no albums published.', 16, 1);
--        RETURN;
--    END

--    -- Get album titles by the artist
--    SELECT 
--		a.album_id,
--        a.title AS album_title
--    FROM 
--        Album a
--    INNER JOIN 
--        Album_Artist aa ON a.album_id = aa.album_id
--    WHERE 
--        aa.artist_id = @artist_id;
--END;


--EXEC GetAlbumsNamesByArtist @artist_id = 1;

-------------------------------------------------------------------------------
---- Create the procedure
--CREATE PROCEDURE GetSongsNamesByArtist
--    @artist_id INT
--AS
--BEGIN
--    -- Check if the artist_id is valid
--    IF NOT EXISTS (SELECT 1 FROM Artist WHERE artist_id = @artist_id)
--    BEGIN
--        RAISERROR ('Invalid artist ID.', 16, 1);
--        RETURN;
--    END

--    -- Check if the artist has any songs published
--    IF NOT EXISTS (
--        SELECT 1 
--        FROM Music_Artist ma 
--        WHERE ma.artist_id = @artist_id
--    )
--    BEGIN
--        RAISERROR ('The artist has no music published.', 16, 1);
--        RETURN;
--    END

--    -- Get song IDs and titles by the artist
--    SELECT 
--        m.music_id,
--        m.title AS song_title
--    FROM 
--        Music m
--    INNER JOIN 
--        Music_Artist ma ON m.music_id = ma.music_id
--    WHERE 
--        ma.artist_id = @artist_id;
--END;

---- Execute the stored procedure
--EXEC GetSongsByArtist 1;

------------------------------------------------------------------------
--CREATE PROCEDURE GetMusicNamesByAlbum
--    @album_id INT
--AS
--BEGIN
--    -- Check if the album_id is valid
--    IF NOT EXISTS (SELECT 1 FROM Album WHERE album_id = @album_id)
--    BEGIN
--        RAISERROR ('Invalid album ID.', 16, 1);
--        RETURN;
--    END

--    -- Retrieve music tracks of the album
--    SELECT 
--        m.music_id,
--        m.title AS music_title
--    FROM 
--        Music m
--    WHERE 
--        m.album_id = @album_id;
--END;


--EXEC GetMusicNamesByAlbum 12;

------------------------------------------------------------------------
--DROP PROCEDURE IF EXISTS GetSongDetails

--CREATE PROCEDURE GetSongDetails
--    @music_id INT
--AS
--BEGIN
--    SELECT 
--        u.Name AS artist_name,
--        m.title,
--        CONVERT(VARCHAR(8), m.duration, 108) AS duration, -- Format duration to HH:MM:SS
--        m.genre,
--        m.zone,
--        m.age_category,
--        m.lyrics,
--        CASE 
--            WHEN m.can_add_to_playlist = 1 THEN 'Can be added to playlist' 
--            ELSE 'Cannot be added to playlist' 
--        END AS playlist_status
--    FROM 
--        Music m
--        JOIN Music_Artist ma ON m.music_id = ma.music_id
--        JOIN Artist ar ON ma.artist_id = ar.artist_id
--        JOIN Users u ON ar.artist_id = u.ID
--    WHERE 
--        m.music_id = @music_id;
--END;
--GO

--EXEC GetSongDetails @music_id = 33;


-----------------------------------------------------------------------
--CREATE PROCEDURE GetAlbumDetails
--    @album_id INT
--AS
--BEGIN
--    SELECT 
--		a.album_id,
--        a.title,
--        a.release_date,
--        a.genre,
--        a.zone,
--        a.age_category
--    FROM 
--        Album a
--    WHERE 
--        a.album_id = @album_id;
--END;
--GO


--EXEC GetAlbumDetails @album_id = 10;

-----------------------------------------------------------------------
---- Create the stored procedure
--CREATE PROCEDURE GetPlaylistDetails
--    @playlist_id INT
--AS
--BEGIN
--    SELECT 
--        p.name AS title,
--        p.create_date AS creation_date,
--        CASE 
--            WHEN p.is_private = 1 THEN 'Private' 
--            ELSE 'Public' 
--        END AS privacy_status
--    FROM 
--        Playlist p
--    WHERE 
--        p.playlist_id = @playlist_id;
--END;
--GO

--EXEC GetPlaylistDetails @playlist_id = 1;