table.sql

CREATE DATABASE IF NOT EXISTS blogManagement;
USE blogManagement;

CREATE TABLE user(
    id int PRIMARY KEY AUTO_INCREMENT,
    avatar_url varchar(255),
    username varchar(50) NOT NULL UNIQUE,
    email varchar(100) NOT NULL UNIQUE,
    password varchar(255) NOT NULL,
    bio TEXT,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE blog(
    id int PRIMARY KEY AUTO_INCREMENT,
    user_id int,
    title varchar(200) NOT NULL,
    slug varchar(255) UNIQUE,
    excerpt varchar(300),
    content TEXT NOT NULL,
    category varchar(50),
    thumbnail_url varchar(255),
    view_count int DEFAULT 0,

    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);


CREATE TABLE comment(
    id int PRIMARY KEY AUTO_INCREMENT,
    blog_id int,
    user_id int,
    content TEXT NOT NULL,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_id) REFERENCES blog(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

CREATE TABLE likes(
    id int PRIMARY KEY AUTO_INCREMENT,
    user_id int,
    blog_id int,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (blog_id) REFERENCES blog(id) ON DELETE CASCADE,
    UNIQUE KEY unique_like (user_id, blog_id)
);

CREATE TABLE follows(
    id int PRIMARY KEY AUTO_INCREMENT,
    follower_id int,
    followed_id int,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (follower_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (followed_id) REFERENCES user(id) ON DELETE CASCADE,
    UNIQUE KEY unique_follow (follower_id, followed_id)
);

CREATE TABLE saved_blogs(
    id int PRIMARY KEY AUTO_INCREMENT,
    user_id int,
    blog_id int,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (blog_id) REFERENCES blog(id) ON DELETE CASCADE,
    UNIQUE KEY unique_save (user_id, blog_id)
);