-- Database Design For Blog Modules.
-- blog_posts
CREATE TABLE blog_posts (
    id CHAR(36) PRIMARY KEY,
    titleName VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    authorEmail VARCHAR(150) NOT NULL,
    galleries JSON NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_blog_author_email (authorEmail),
    INDEX idx_blog_created_at (createdAt)
);

-- blog_post_likes
CREATE TABLE blog_post_likes (
    id CHAR(36) PRIMARY KEY,
    postId CHAR(36) NOT NULL,
    authorEmail VARCHAR(150) NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY unique_post_like (postId, authorEmail),
    INDEX idx_post_likes_post (postId)
);
-- blog_comments
CREATE TABLE blog_comments (
    id CHAR(36) PRIMARY KEY,
    postId CHAR(36) NOT NULL,
    authorEmail VARCHAR(150) NOT NULL,
    comment TEXT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_comments_post (postId),
    INDEX idx_comments_author (authorEmail)
);
-- blog_comment_likes
CREATE TABLE blog_comment_likes (
    id CHAR(36) PRIMARY KEY,
    commentId CHAR(36) NOT NULL,
    authorEmail VARCHAR(150) NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY unique_comment_like (commentId, authorEmail),
    INDEX idx_comment_likes_comment (commentId)
);
-- blog_comment_replies
CREATE TABLE blog_comment_replies (
    id CHAR(36) PRIMARY KEY,
    postId CHAR(36) NOT NULL,
    commentId CHAR(36) NOT NULL,
    authorEmail VARCHAR(150) NOT NULL,
    comment TEXT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_replies_comment (commentId),
    INDEX idx_replies_post (postId),
    INDEX idx_replies_author (authorEmail)
);
-- blog_reply_likes
CREATE TABLE blog_reply_likes (
    id CHAR(36) PRIMARY KEY,
    replyId CHAR(36) NOT NULL,
    authorEmail VARCHAR(150) NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY unique_reply_like (replyId, authorEmail),
    INDEX idx_reply_likes_reply (replyId)
);