reddit_db_script.sql

create database RedditDB

use RedditDB

-- Drop tables if they exist
DROP TABLE IF EXISTS EntryCategory;
DROP TABLE IF EXISTS Entry;
DROP TABLE IF EXISTS Author;
DROP TABLE IF EXISTS Feed;

-- Feed table (top-level subreddit info)
CREATE TABLE Feed (
    id INT PRIMARY KEY IDENTITY(1,1),
    title NVARCHAR(255) NOT NULL,
    subtitle NVARCHAR(MAX),
    link NVARCHAR(500),
    updated DATETIME2 NOT NULL,
    icon NVARCHAR(500)
);

-- Author table (Reddit user who posts content)
CREATE TABLE Author (
    id INT PRIMARY KEY IDENTITY(1,1),
    username NVARCHAR(255) NOT NULL,
    uri NVARCHAR(500)
);

-- Entry table (posts inside the feed)
CREATE TABLE Entry (
    id INT PRIMARY KEY IDENTITY(1,1),
    title NVARCHAR(255) NOT NULL,
    link NVARCHAR(500) NOT NULL,
    published DATETIME2 NOT NULL,
    updated DATETIME2 NOT NULL,
    content NVARCHAR(MAX),
    thumbnail NVARCHAR(500),
    
    feedId INT NOT NULL,
    authorId INT NOT NULL,

    FOREIGN KEY (feedId) REFERENCES Feed(id) ON DELETE CASCADE,
    FOREIGN KEY (authorId) REFERENCES Author(id) ON DELETE CASCADE
);

-- EntryCategory table (tags like 'CroIT', 'TooAfraidToAsk')
CREATE TABLE EntryCategory (
    id INT PRIMARY KEY IDENTITY(1,1),
    name NVARCHAR(100) NOT NULL,
    entryId INT NOT NULL,

    FOREIGN KEY (entryId) REFERENCES Entry(id) ON DELETE CASCADE
);

-- ========================================
-- Author Procedures
-- ========================================

CREATE PROCEDURE CreateAuthor
    @username NVARCHAR(255),
    @uri NVARCHAR(500)
AS
BEGIN
    INSERT INTO Author (username, uri)
    VALUES (@username, @uri);
END;
GO

CREATE PROCEDURE GetAuthorById
    @id INT
AS
BEGIN
    SELECT * FROM Author WHERE id = @id;
END;
GO

-- ========================================
-- Feed Procedures
-- ========================================

CREATE PROCEDURE CreateFeed
    @title NVARCHAR(255),
    @subtitle NVARCHAR(MAX),
    @link NVARCHAR(500),
    @updated DATETIME2,
    @icon NVARCHAR(500)
AS
BEGIN
    INSERT INTO Feed (title, subtitle, link, updated, icon)
    VALUES (@title, @subtitle, @link, @updated, @icon);
END;
GO

CREATE PROCEDURE GetFeedById
    @id INT
AS
BEGIN
    SELECT * FROM Feed WHERE id = @id;
END;
GO

-- ========================================
-- Entry Procedures
-- ========================================

CREATE PROCEDURE CreateEntry
    @title NVARCHAR(255),
    @link NVARCHAR(500),
    @published DATETIME2,
    @updated DATETIME2,
    @content NVARCHAR(MAX),
    @thumbnail NVARCHAR(500),
    @feedId INT,
    @authorId INT
AS
BEGIN
    INSERT INTO Entry (title, link, published, updated, content, thumbnail, feedId, authorId)
    VALUES (@title, @link, @published, @updated, @content, @thumbnail, @feedId, @authorId);
END;
GO


ALTER PROCEDURE CreateEntry
    @title NVARCHAR(255),
    @link NVARCHAR(500),
    @published DATETIME2,
    @updated DATETIME2,
    @content NVARCHAR(MAX),
    @thumbnail NVARCHAR(500),
    @feedId INT,
    @authorId INT,
    @id INT OUTPUT -- ← Add output parameter
AS
BEGIN
    INSERT INTO Entry (title, link, published, updated, content, thumbnail, feedId, authorId)
    VALUES (@title, @link, @published, @updated, @content, @thumbnail, @feedId, @authorId);

    SET @id = SCOPE_IDENTITY(); -- ← Return the new Entry ID
END;
GO


CREATE PROCEDURE GetEntryById
    @id INT
AS
BEGIN
    SELECT * FROM Entry WHERE id = @id;
END;
GO

-- ========================================
-- EntryCategory Procedures
-- ========================================

CREATE PROCEDURE CreateEntryCategory
    @name NVARCHAR(100),
    @entryId INT
AS
BEGIN
    INSERT INTO EntryCategory (name, entryId)
    VALUES (@name, @entryId);
END;
GO

CREATE PROCEDURE GetEntryCategories
    @entryId INT
AS
BEGIN
    SELECT * FROM EntryCategory WHERE entryId = @entryId;
END;
GO


-- Update EntryCategory
CREATE PROCEDURE UpdateEntryCategory
    @id INT,
    @name NVARCHAR(100),
    @entryId INT
AS
BEGIN
    UPDATE EntryCategory
    SET name = @name,
        entryId = @entryId
    WHERE id = @id;
END;
GO

-- Delete EntryCategory
CREATE PROCEDURE DeleteEntryCategory
    @id INT
AS
BEGIN
    DELETE FROM EntryCategory WHERE id = @id;
END;
GO

-- Get EntryCategory by ID
CREATE PROCEDURE GetEntryCategoryById
    @id INT
AS
BEGIN
    SELECT * FROM EntryCategory WHERE id = @id;
END;
GO

-- Get All EntryCategories
CREATE PROCEDURE GetAllEntryCategories
AS
BEGIN
    SELECT * FROM EntryCategory;
END;
GO

select * from Author
select * from Feed

select * from [Entry]
-- Update Entry
alter PROCEDURE UpdateEntry
    @id INT,
    @title NVARCHAR(255),
    @link NVARCHAR(500),
    @published DATETIME2,
    @updated DATETIME2,
    @content NVARCHAR(MAX),
    @thumbnailblob VARBINARY(MAX),
    @feedId INT,
    @authorId INT
AS
BEGIN
    UPDATE Entry
    SET title = @title,
        link = @link,
        published = @published,
        updated = @updated,
        content = @content,
        thumbnailblob = @thumbnailblob,
        feedId = @feedId,
        authorId = @authorId
    WHERE id = @id;
END;
GO

-- Delete Entry
CREATE PROCEDURE DeleteEntry
    @id INT
AS
BEGIN
    DELETE FROM Entry WHERE id = @id;
END;
GO

-- Get All Entries
CREATE PROCEDURE GetAllEntries
AS
BEGIN
    SELECT * FROM Entry;
END;
GO

select * from Feed
select * from Author
-- Update Author
alter PROCEDURE UpdateAuthor
    @id INT,
    @username NVARCHAR(255),
    @uri NVARCHAR(500),
	@userAvatar VARBINARY(MAX)
AS
BEGIN
    UPDATE Author
    SET username = @username,
        uri = @uri,
		userAvatar = @userAvatar
    WHERE id = @id;
END;
GO

-- Delete Author
CREATE PROCEDURE DeleteAuthor
    @id INT
AS
BEGIN
    DELETE FROM Author WHERE id = @id;
END;
GO

-- Get All Authors
CREATE PROCEDURE GetAllAuthors
AS
BEGIN
    SELECT * FROM Author;
END;
GO

EXEC GetAllEntries;




-- Update Feed
alter PROCEDURE UpdateFeed
    @id INT,
    @title NVARCHAR(255),
    @subtitle NVARCHAR(MAX),
    @link NVARCHAR(500),
    @updated DATETIME2,
    @icon VARBINARY(MAX)
AS
BEGIN
    UPDATE Feed
    SET title = @title,
        subtitle = @subtitle,
        link = @link,
        updated = @updated,
        iconBlob = @icon
    WHERE id = @id;
END;
GO

-- Delete Feed
CREATE PROCEDURE DeleteFeed
    @id INT
AS
BEGIN
    DELETE FROM Feed WHERE id = @id;
END;
GO

-- Get All Feeds
CREATE PROCEDURE GetAllFeeds
AS
BEGIN
    SELECT * FROM Feed;
END;
GO

select * from [Entry]

select * from Feed

select * from 

ALTER PROCEDURE CreateFeed
    @title NVARCHAR(255),
    @subtitle NVARCHAR(MAX),
    @link NVARCHAR(500),
    @updated DATETIME2,
    @icon NVARCHAR(500),
    @id INT OUTPUT
AS
BEGIN
    INSERT INTO Feed (title, subtitle, link, updated, icon)
    VALUES (@title, @subtitle, @link, @updated, @icon);

    SET @id = SCOPE_IDENTITY();
END;
GO

alter PROCEDURE GetEntriesByFeedId
    @feedId INT
AS
BEGIN
    SELECT 
        e.id,
        e.title,
        e.link,
        e.published,
        e.updated,
        e.content,
        e.thumbnailblob,
        e.feedId,
        e.authorId,
        a.username,
        a.uri
    FROM Entry e
    INNER JOIN Author a ON e.authorId = a.id
    WHERE e.feedId = @feedId;
END;
GO

alter PROCEDURE GetEntriesByAuthorId
    @authorId INT
AS
BEGIN
    SELECT 
        e.id,
        e.title,
        e.link,
        e.published,
        e.updated,
        e.content,
        e.thumbnailblob,
        e.feedId,
        e.authorId,
        a.username,
        a.uri
    FROM Entry e
    INNER JOIN Author a ON e.authorId = a.id
    WHERE e.authorId = @authorId;
END;
GO

alter PROCEDURE CreateAuthor
    @username NVARCHAR(255),
    @uri NVARCHAR(500),
	@id INT OUTPUT 
AS
BEGIN
    INSERT INTO Author (username, uri)
    VALUES (@username, @uri);
	SET @id = SCOPE_IDENTITY();
END;
GO

CREATE PROCEDURE GetAuthorById
    @id INT
AS
BEGIN
    SELECT * FROM Author WHERE id = @id;
END;
GO


select * from [Entry]

CREATE TABLE Comment (
    id INT PRIMARY KEY IDENTITY(1,1),
    content NVARCHAR(MAX) NOT NULL,
    published DATETIME2 NOT NULL,
    updated DATETIME2,
    
    entryId INT NOT NULL,
    authorId INT NOT NULL,
    parentCommentId INT NULL, -- for replies (if ever implemented)

    FOREIGN KEY (entryId) REFERENCES Entry(id) ON DELETE CASCADE,
    FOREIGN KEY (authorId) REFERENCES Author(id),
    FOREIGN KEY (parentCommentId) REFERENCES Comment(id)
);

ALTER TABLE Comment
ADD link NVARCHAR(500);



alter PROCEDURE CreateComment
    @content NVARCHAR(MAX),
    @published DATETIME2,
    @updated DATETIME2,
    @entryId INT,
    @authorId INT,
	@link NVARCHAR(500),
    @parentCommentId INT = NULL
AS
BEGIN
    INSERT INTO Comment (content, published, updated, entryId, authorId, parentCommentId, link)
    VALUES (@content, @published, @updated, @entryId, @authorId, @parentCommentId, @link);
END;
GO

CREATE PROCEDURE DeleteComment
    @commentId INT
AS
BEGIN
    DELETE FROM Comment WHERE id = @commentId;
END;
GO

CREATE PROCEDURE EditComment
    @commentId INT,
    @content NVARCHAR(MAX),
    @published DATETIME2,
    @updated DATETIME2,
    @entryId INT,
    @authorId INT,
    @link NVARCHAR(500),
    @parentCommentId INT = NULL
AS
BEGIN
    UPDATE Comment
    SET 
        content = @content,
        published = @published,
        updated = @updated,
        entryId = @entryId,
        authorId = @authorId,
        parentCommentId = @parentCommentId,
        link = @link
    WHERE id = @commentId;
END;
GO



alter PROCEDURE GetCommentById
    @commentId INT
AS
BEGIN
    SELECT 
        c.id,
        c.content,
        c.published,
        c.updated,
        c.entryId,
        c.authorId,
		c.link,
        c.parentCommentId
    FROM Comment c
    WHERE c.id = @commentId;
END;
GO

alter PROCEDURE GetCommentsByEntryId
    @entryId INT
AS
BEGIN
    SELECT 
        c.id,
        c.content,
        c.published,
        c.updated,
        c.entryId,
        c.authorId,
		c.link,
        a.username,
        c.parentCommentId
    FROM Comment c
    INNER JOIN Author a ON c.authorId = a.id
    WHERE c.entryId = @entryId;
END;
GO

alter PROCEDURE GetCommentsByParentId
    @parentCommentId INT
AS
BEGIN
    SELECT 
        c.id,
        c.content,
        c.published,
        c.updated,
        c.entryId,
		c.link,
        c.authorId,
        a.username
    FROM Comment c
    INNER JOIN Author a ON c.authorId = a.id
    WHERE c.parentCommentId = @parentCommentId;
END;
GO

alter PROCEDURE GetCommentsByAuthorId
    @authorId INT
AS
BEGIN
    SELECT 
        c.id,
        c.content,
        c.published,
		c.link,
        c.updated,
        c.entryId,
        c.parentCommentId,
        e.title AS entryTitle,
        e.link AS entryLink
    FROM Comment c
    INNER JOIN Entry e ON c.entryId = e.id
    WHERE c.authorId = @authorId;
END;
GO

CREATE PROCEDURE MasterDelete
AS
BEGIN
    DECLARE @deletedEntryCategories INT = 0;
    DECLARE @deletedComments INT = 0;
    DECLARE @deletedEntries INT = 0;
    DECLARE @deletedAuthors INT = 0;
    DECLARE @deletedFeeds INT = 0;

    -- Delete in correct order and count
    DELETE FROM EntryCategory;
    SET @deletedEntryCategories = @@ROWCOUNT;

    DELETE FROM Comment;
    SET @deletedComments = @@ROWCOUNT;

    DELETE FROM Entry;
    SET @deletedEntries = @@ROWCOUNT;

    DELETE FROM Author;
    SET @deletedAuthors = @@ROWCOUNT;

    DELETE FROM Feed;
    SET @deletedFeeds = @@ROWCOUNT;

    -- Return as a result set
    SELECT
        @deletedFeeds AS DeletedFeeds,
        @deletedAuthors AS DeletedAuthors,
        @deletedEntries AS DeletedEntries,
        @deletedComments AS DeletedComments,
        @deletedEntryCategories AS DeletedEntryCategories;
END;
GO

select * from Feed

select * from [Entry]
select * from [Comment]


ALTER PROCEDURE CreateFeed
    @title NVARCHAR(255),
    @subtitle NVARCHAR(MAX),
    @link NVARCHAR(500),
    @updated DATETIME2,
    @icon VARBINARY(MAX), -- Changed from NVARCHAR to VARBINARY
    @id INT OUTPUT
AS
BEGIN
    -- Try to find existing feed by link
    SELECT @id = id FROM Feed WHERE title = @title;

    IF @id IS NULL
    BEGIN
        -- Insert new feed
        INSERT INTO Feed (title, subtitle, link, updated, iconBlob)
        VALUES (@title, @subtitle, @link, @updated, @icon);

        -- Return newly created ID
        SET @id = SCOPE_IDENTITY();
    END
END;
GO

select * from Feed

alter PROCEDURE CreateEntry
    @title NVARCHAR(255),
    @link NVARCHAR(500),
    @published DATETIME2,
    @updated DATETIME2,
    @content NVARCHAR(MAX),
    @thumbnail VARBINARY(MAX),
    @feedId INT,
    @authorId INT,
	@id INT OUTPUT -- ← Output feed ID
AS
BEGIN
	select @id = id from Entry where title = @title
	if @id is null
	begin
    INSERT INTO Entry (title, link, published, updated, content, thumbnailblob, feedId, authorId)
    VALUES (@title, @link, @published, @updated, @content, @thumbnail, @feedId, @authorId);
	set @id = SCOPE_IDENTITY()
	end;
END;
GO

alter PROCEDURE CreateAuthor
    @username NVARCHAR(255),
    @uri NVARCHAR(500),
	@userAvatar VARBINARY(MAX),
	@id int output
AS
BEGIN

	select @id = id from Author where @username = username
	if @id is null
	begin
    INSERT INTO Author (username, uri, userAvatar)
    VALUES (@username, @uri, @userAvatar);
	set @id = SCOPE_IDENTITY();
	end;
END;
GO

select * from Author
alter PROCEDURE CreateComment
    @content NVARCHAR(MAX),
    @published DATETIME2,
    @updated DATETIME2,
    @entryId INT,
    @authorId INT,
	@link NVARCHAR(500),
    @parentCommentId INT = NULL,
	@id int output
AS
BEGIN
	select @id = id from Comment where @content = content
	if @id is null
	begin
    INSERT INTO Comment (content, published, updated, entryId, authorId, parentCommentId, link)
    VALUES (@content, @published, @updated, @entryId, @authorId, @parentCommentId, @link);
	set @id = SCOPE_IDENTITY();
	end;
END;
GO

create PROCEDURE GetFeedByEntryId
    @entryId INT
AS
BEGIN
    SELECT 
        f.id,
        f.title,
        f.subtitle,
        f.link,
        f.updated,
        c.authorId,
		c.link,
        a.username,
        c.parentCommentId
    FROM Feed f
    INNER JOIN Author a ON c.authorId = a.id
    WHERE c.entryId = @entryId;
END;
GO

select * from Feed

create table UserRole (
    IDUserRole INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(50) NOT NULL UNIQUE
)

create table AppUser (
	IDUser INT PRIMARY KEY IDENTITY(1,1),
    Email VARCHAR(255) NOT NULL UNIQUE,
    Password TEXT NOT NULL,
    Username VARCHAR(100) NOT NULL UNIQUE,
    RoleId INTEGER NOT NULL,
    FOREIGN KEY (RoleId) REFERENCES UserRole(IDUserRole)
)

INSERT INTO UserRole (name) VALUES ('user'), ('admin');

INSERT INTO AppUser (email, password, username, RoleId)
VALUES ('miki@example.com', 'miki1234', 'miki123', 1);


INSERT INTO AppUser (email, password, username, RoleId)
VALUES ('admin@example.com', 'admin1234', 'admin', 2);

select * from UserRole
select * from AppUser

CREATE PROCEDURE CreateAppUser
    @Email NVARCHAR(255),
    @Password NVARCHAR(255),
    @Username NVARCHAR(100),
    @RoleId INT,
    @NewId INT OUTPUT
AS
BEGIN
    INSERT INTO AppUser (Email, Password, Username, RoleId)
    VALUES (@Email, @Password, @Username, @RoleId);

    SET @NewId = SCOPE_IDENTITY();
END;


CREATE PROCEDURE GetAppUserById
    @Id INT
AS
BEGIN
    SELECT * FROM AppUser WHERE IDUser = @Id;
END;

CREATE PROCEDURE GetRoleById
    @Id INT
AS
BEGIN
    SELECT * FROM UserRole WHERE IDUserRole = @Id;
END;

select * from UserRole
CREATE PROCEDURE UpdateAppUser
    @Id INT,
    @Email NVARCHAR(255),
    @Password NVARCHAR(255),
    @Username NVARCHAR(100),
    @RoleId INT,
    @UpdatedId INT OUTPUT
AS
BEGIN
    UPDATE AppUser
    SET Email = @Email,
        Password = @Password,
        Username = @Username,
        RoleId = @RoleId
    WHERE IDUser = @Id;

    SET @UpdatedId = @Id;
END;


CREATE PROCEDURE DeleteAppUser
    @Id INT,
    @DeletedId INT OUTPUT
AS
BEGIN
    DELETE FROM AppUser WHERE IDUser = @Id;
    SET @DeletedId = @Id;
END;

CREATE PROCEDURE GetUserWithUsername
    @Username NVARCHAR(100),
    @Password NVARCHAR(255),
    @IsValid BIT OUTPUT
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM AppUser
        WHERE Username = @Username AND Password = @Password
    )
        SET @IsValid = 1;
    ELSE
        SET @IsValid = 0;
END;

ALTER TABLE AppUser
ALTER COLUMN Password NVARCHAR(255);

CREATE OR ALTER PROCEDURE GetUserWithUsername
    @Username NVARCHAR(100),
    @Password NVARCHAR(255)
AS
BEGIN
    SELECT *
    FROM AppUser
    WHERE Username = @Username
      AND CAST(Password AS NVARCHAR(MAX)) = @Password;
END;

select * from AppUser

alter PROCEDURE GetAllAppUsers
AS
BEGIN
    SELECT * FROM AppUser;
END;

CREATE PROCEDURE UpdateUsernameById
    @UserID INT,
    @NewUsername NVARCHAR(100)
AS
BEGIN

    UPDATE AppUser
    SET Username = @NewUsername
    WHERE IDUser = @UserID;
END;

CREATE PROCEDURE UpdateEmailById
    @UserID INT,
    @NewEmail NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE AppUser
    SET Email = @NewEmail
    WHERE IDUser = @UserID;
END;


select * from AppUser

ALTER PROCEDURE DeleteAuthor
    @id INT
AS
BEGIN
    DELETE FROM Comment WHERE authorId = @id;
	Delete from Entry where authorId = @id;

    DELETE FROM Author WHERE id = @id;
END;
GO

select * from Feed

ALTER TABLE Feed
add iconBlob VARBINARY(MAX);

ALTER TABLE Entry
add thumbnailBlob VARBINARY(MAX);

ALTER TABLE Author
ADD userAvatar VARBINARY(MAX);