[users] 1 ──── N [articles] N ──── N [tags]
   │                  │
   │                  │ 1
   │                  │
   │                  N
   │             [comments]
   │                  │
   N                  │ (self-referencing)
   │                  │
[users] 1 ──── N [comments]

[categories] 1 ──── N [articles]
     │
     └── 1 (self-referencing, parent_id)

-- =============================================
-- Blog System Database Schema
-- Character set: utf8mb4 (supports emoji and multilingual)
-- Storage engine: InnoDB (supports transactions and foreign keys)
-- =============================================

-- Create database
CREATE DATABASE IF NOT EXISTS blog_system
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE blog_system;

-- ----------------------------
-- 1. Users table
-- ----------------------------
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'User ID',
    username VARCHAR(50) NOT NULL COMMENT 'Username',
    email VARCHAR(100) NOT NULL COMMENT 'Email',
    password_hash VARCHAR(255) NOT NULL COMMENT 'Password hash',
    nickname VARCHAR(50) COMMENT 'Nickname',
    avatar_url VARCHAR(500) COMMENT 'Avatar URL',
    bio VARCHAR(500) COMMENT 'Bio',
    website VARCHAR(200) COMMENT 'Personal website',
    status TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 1-active 0-disabled 2-pending verification',
    role VARCHAR(20) NOT NULL DEFAULT 'user' COMMENT 'Role: user/author/admin',
    article_count INT NOT NULL DEFAULT 0 COMMENT 'Article count (denormalized)',
    follower_count INT NOT NULL DEFAULT 0 COMMENT 'Follower count (denormalized)',
    last_login_at DATETIME COMMENT 'Last login time',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Registration time',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated time',
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 'Soft delete: 0-normal 1-deleted',
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Users table';

-- ----------------------------
-- 2. User follow relationships table (many-to-many)
-- ----------------------------
CREATE TABLE user_follows (
    follower_id BIGINT NOT NULL COMMENT 'Follower ID',
    following_id BIGINT NOT NULL COMMENT 'Following ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Follow time',
    PRIMARY KEY (follower_id, following_id),
    INDEX idx_following_id (following_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User follow relationships table';

-- ----------------------------
-- 3. Categories table (supports hierarchical categories)
-- ----------------------------
CREATE TABLE categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Category ID',
    name VARCHAR(50) NOT NULL COMMENT 'Category name',
    slug VARCHAR(100) NOT NULL COMMENT 'URL slug',
    description VARCHAR(200) COMMENT 'Category description',
    parent_id BIGINT DEFAULT NULL COMMENT 'Parent category ID, NULL for top-level',
    sort_order INT NOT NULL DEFAULT 0 COMMENT 'Sort order',
    article_count INT NOT NULL DEFAULT 0 COMMENT 'Article count (denormalized)',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_slug (slug),
    INDEX idx_parent_id (parent_id),
    INDEX idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Categories table';

-- ----------------------------
-- 4. Tags table
-- ----------------------------
CREATE TABLE tags (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Tag ID',
    name VARCHAR(50) NOT NULL COMMENT 'Tag name',
    slug VARCHAR(100) NOT NULL COMMENT 'URL slug',
    article_count INT NOT NULL DEFAULT 0 COMMENT 'Article count (denormalized)',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_name (name),
    UNIQUE KEY uk_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tags table';

-- ----------------------------
-- 5. Articles table
-- ----------------------------
CREATE TABLE articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Article ID',
    user_id BIGINT NOT NULL COMMENT 'Author ID',
    category_id BIGINT COMMENT 'Category ID',
    title VARCHAR(200) NOT NULL COMMENT 'Title',
    slug VARCHAR(200) NOT NULL COMMENT 'URL slug',
    summary VARCHAR(500) COMMENT 'Summary',
    content LONGTEXT NOT NULL COMMENT 'Body content',
    cover_image_url VARCHAR(500) COMMENT 'Cover image',
    status TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-draft 1-published 2-unlisted',
    is_pinned TINYINT NOT NULL DEFAULT 0 COMMENT 'Pinned: 0-no 1-yes',
    view_count INT NOT NULL DEFAULT 0 COMMENT 'View count',
    like_count INT NOT NULL DEFAULT 0 COMMENT 'Like count',
    comment_count INT NOT NULL DEFAULT 0 COMMENT 'Comment count (denormalized)',
    published_at DATETIME COMMENT 'Published time',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created time',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated time',
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 'Soft delete: 0-normal 1-deleted',
    UNIQUE KEY uk_slug (slug),
    INDEX idx_user_id (user_id),
    INDEX idx_category_id (category_id),
    INDEX idx_status_published (status, published_at DESC),
    INDEX idx_is_pinned (is_pinned),
    INDEX idx_created_at (created_at DESC),
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Articles table';

-- ----------------------------
-- 6. Article-tag association table (many-to-many)
-- ----------------------------
CREATE TABLE article_tag (
    article_id BIGINT NOT NULL COMMENT 'Article ID',
    tag_id BIGINT NOT NULL COMMENT 'Tag ID',
    PRIMARY KEY (article_id, tag_id),
    INDEX idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Article-tag association table';

-- ----------------------------
-- 7. Comments table (supports nested replies)
-- ----------------------------
CREATE TABLE comments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Comment ID',
    article_id BIGINT NOT NULL COMMENT 'Article ID',
    user_id BIGINT NOT NULL COMMENT 'Commenter ID',
    parent_id BIGINT DEFAULT NULL COMMENT 'Parent comment ID, NULL for top-level',
    reply_to_user_id BIGINT DEFAULT NULL COMMENT 'User being replied to',
    content TEXT NOT NULL COMMENT 'Comment content',
    like_count INT NOT NULL DEFAULT 0 COMMENT 'Like count',
    status TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-pending 1-approved 2-rejected',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 'Soft delete',
    INDEX idx_article_id (article_id),
    INDEX idx_user_id (user_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_created_at (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Comments table';

-- ----------------------------
-- 8. Article likes table
-- ----------------------------
CREATE TABLE article_likes (
    user_id BIGINT NOT NULL COMMENT 'User ID',
    article_id BIGINT NOT NULL COMMENT 'Article ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, article_id),
    INDEX idx_article_id (article_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Article likes table';

-- Register a new user
INSERT INTO users (username, email, password_hash, nickname)
VALUES ('zhangsan', 'zhangsan@example.com', SHA2('mypassword123', 256), 'Zhang San');

-- User login (query by username)
SELECT id, username, nickname, avatar_url, role
FROM users
WHERE username = 'zhangsan' AND password_hash = SHA2('mypassword123', 256)
    AND status = 1 AND is_deleted = 0;

-- Update profile
UPDATE users
SET nickname = 'Zhang Sanfeng',
    bio = 'Love coding, love life',
    website = 'https://zhangsan.dev'
WHERE id = 1 AND is_deleted = 0;

-- Soft delete user
UPDATE users SET is_deleted = 1 WHERE id = 1;

-- Query user details (exclude deleted)
SELECT id, username, email, nickname, avatar_url, bio, website,
       article_count, follower_count, created_at
FROM users
WHERE id = 1 AND is_deleted = 0;

-- Create top-level categories
INSERT INTO categories (name, slug, description, sort_order)
VALUES ('Tech', 'tech', 'Technology-related articles', 1),
       ('Life', 'life', 'Life essays', 2),
       ('Reading', 'reading', 'Book notes', 3);

-- Create subcategories
INSERT INTO categories (name, slug, description, parent_id, sort_order)
VALUES ('Frontend', 'frontend', 'Frontend development', 1, 1),
       ('Backend', 'backend', 'Backend development', 1, 2),
       ('Database', 'database', 'Database technology', 1, 3),
       ('JavaScript', 'javascript', 'JavaScript language', 4, 1),
       ('Python', 'python', 'Python language', 5, 1);

-- Query category tree
SELECT
    c1.id, c1.name AS level1,
    c2.name AS level2,
    c3.name AS level3
FROM categories c1
LEFT JOIN categories c2 ON c2.parent_id = c1.id
LEFT JOIN categories c3 ON c3.parent_id = c2.id
WHERE c1.parent_id IS NULL
ORDER BY c1.sort_order, c2.sort_order, c3.sort_order;

-- Create tags
INSERT INTO tags (name, slug) VALUES
    ('MySQL', 'mysql'),
    ('JavaScript', 'javascript'),
    ('Python', 'python'),
    ('Docker', 'docker'),
    ('Redis', 'redis'),
    ('Vue.js', 'vuejs'),
    ('React', 'react'),
    ('Algorithms', 'algorithms');

-- Query tags (sorted by article count)
SELECT t.id, t.name, t.slug, t.article_count
FROM tags t
ORDER BY t.article_count DESC
LIMIT 20;

-- Create article (draft)
INSERT INTO articles (user_id, category_id, title, slug, summary, content, status)
VALUES (
    1,
    7,  -- JavaScript category
    'MySQL Index Optimization Practical Guide',
    'mysql-index-optimization-guide',
    'This article details the core strategies and practical techniques of MySQL index optimization',
    '# MySQL Index Optimization\n\n## What is an Index\n\nAn index is...',
    0  -- Draft
);

-- Publish article
UPDATE articles
SET status = 1,
    published_at = NOW()
WHERE id = 1 AND user_id = 1;

-- Tag the article
INSERT INTO article_tag (article_id, tag_id) VALUES
    (1, 1),  -- MySQL
    (1, 8);  -- Algorithms

-- Update article tags (delete then insert)
DELETE FROM article_tag WHERE article_id = 1;
INSERT INTO article_tag (article_id, tag_id) VALUES
    (1, 1),
    (1, 5);  -- MySQL + Redis

-- Soft delete article
UPDATE articles SET is_deleted = 1 WHERE id = 1 AND user_id = 1;

-- Update denormalized field: user article count
UPDATE users SET article_count = (
    SELECT COUNT(*) FROM articles WHERE user_id = 1 AND is_deleted = 0
) WHERE id = 1;

-- Update denormalized field: category article count
UPDATE categories SET article_count = (
    SELECT COUNT(*) FROM articles WHERE category_id = 7 AND status = 1 AND is_deleted = 0
) WHERE id = 7;

-- Update denormalized field: tag article count
UPDATE tags SET article_count = (
    SELECT COUNT(*) FROM article_tag WHERE tag_id = 1
) WHERE id = 1;

-- Post a comment
INSERT INTO comments (article_id, user_id, content)
VALUES (1, 2, 'Great article, learned a lot!');

-- Reply to a comment
INSERT INTO comments (article_id, user_id, parent_id, reply_to_user_id, content)
VALUES (1, 3, 1, 2, 'I agree, especially the index section was very clear');

-- Update article comment count
UPDATE articles SET comment_count = (
    SELECT COUNT(*) FROM comments WHERE article_id = 1 AND status = 1 AND is_deleted = 0
) WHERE id = 1;

-- Soft delete comment
UPDATE comments SET is_deleted = 1 WHERE id = 1 AND user_id = 2;

-- Follow a user
INSERT INTO user_follows (follower_id, following_id) VALUES (2, 1);
-- Update denormalized follower count
UPDATE users SET follower_count = follower_count + 1 WHERE id = 1;

-- Unfollow
DELETE FROM user_follows WHERE follower_id = 2 AND following_id = 1;
UPDATE users SET follower_count = follower_count - 1 WHERE id = 1;

-- Query my following list
SELECT u.id, u.username, u.nickname, u.avatar_url, u.bio
FROM user_follows f
JOIN users u ON f.following_id = u.id
WHERE f.follower_id = 2 AND u.is_deleted = 0
ORDER BY f.created_at DESC;

-- Query my followers list
SELECT u.id, u.username, u.nickname, u.avatar_url, u.bio
FROM user_follows f
JOIN users u ON f.follower_id = u.id
WHERE f.following_id = 1 AND u.is_deleted = 0
ORDER BY f.created_at DESC;

SELECT
    a.id,
    a.title,
    a.slug,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    u.username AS author,
    u.avatar_url AS author_avatar,
    c.name AS category_name,
    -- Composite hotness score: views×1 + likes×5 + comments×3
    (a.view_count * 1 + a.like_count * 5 + a.comment_count * 3) AS hot_score,
    GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tags
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tag at2 ON a.id = at2.article_id
LEFT JOIN tags t ON at2.tag_id = t.id
WHERE a.status = 1
    AND a.is_deleted = 0
    AND a.published_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY a.id
ORDER BY hot_score DESC
LIMIT 20;

SELECT
    u.id,
    u.username,
    u.nickname,
    u.article_count,
    u.follower_count,
    -- Total views
    IFNULL(SUM(a.view_count), 0) AS total_views,
    -- Total likes
    IFNULL(SUM(a.like_count), 0) AS total_likes,
    -- Total comments
    IFNULL(SUM(a.comment_count), 0) AS total_comments,
    -- Latest published article time
    MAX(a.published_at) AS last_published_at,
    -- Average views per article
    IFNULL(ROUND(AVG(a.view_count), 0), 0) AS avg_views_per_article
FROM users u
LEFT JOIN articles a ON u.id = a.user_id AND a.status = 1 AND a.is_deleted = 0
WHERE u.id = 1 AND u.is_deleted = 0
GROUP BY u.id;

SELECT
    a.id,
    a.title,
    a.content,
    a.cover_image_url,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    a.updated_at,
    u.id AS author_id,
    u.username AS author_username,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar,
    u.bio AS author_bio,
    c.name AS category_name,
    c.slug AS category_slug
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.slug = 'mysql-index-optimization-guide'
    AND a.status = 1
    AND a.is_deleted = 0;

-- Query article tags
SELECT t.id, t.name, t.slug
FROM article_tag at2
JOIN tags t ON at2.tag_id = t.id
WHERE at2.article_id = 1;

-- Increment view count
UPDATE articles SET view_count = view_count + 1 WHERE id = 1;

-- Query top-level comments
SELECT
    c.id,
    c.content,
    c.like_count,
    c.created_at,
    u.id AS user_id,
    u.username,
    u.nickname,
    u.avatar_url
FROM comments c
JOIN users u ON c.user_id = u.id
WHERE c.article_id = 1
    AND c.parent_id IS NULL
    AND c.status = 1
    AND c.is_deleted = 0
ORDER BY c.created_at DESC
LIMIT 20;

-- Query replies to a specific comment
SELECT
    c.id,
    c.content,
    c.like_count,
    c.created_at,
    u.username,
    u.nickname,
    u.avatar_url,
    ru.username AS reply_to_username,
    ru.nickname AS reply_to_nickname
FROM comments c
JOIN users u ON c.user_id = u.id
LEFT JOIN users ru ON c.reply_to_user_id = ru.id
WHERE c.parent_id = 1
    AND c.status = 1
    AND c.is_deleted = 0
ORDER BY c.created_at ASC;

-- Query all articles under a specific tag
SELECT
    a.id,
    a.title,
    a.slug,
    a.summary,
    a.view_count,
    a.like_count,
    a.published_at,
    u.username AS author,
    u.avatar_url AS author_avatar
FROM article_tag at2
JOIN articles a ON at2.article_id = a.id
JOIN users u ON a.user_id = u.id
WHERE at2.tag_id = 1
    AND a.status = 1
    AND a.is_deleted = 0
ORDER BY a.published_at DESC
LIMIT 20;

-- Article count and average views per category
SELECT
    c.id,
    c.name,
    c.slug,
    c.article_count,
    IFNULL(COUNT(a.id), 0) AS actual_count,
    IFNULL(ROUND(AVG(a.view_count)), 0) AS avg_views,
    IFNULL(MAX(a.published_at), NULL) AS latest_article_at
FROM categories c
LEFT JOIN articles a ON c.id = a.category_id
    AND a.status = 1 AND a.is_deleted = 0
GROUP BY c.id
ORDER BY c.sort_order;

-- Query a category and all its subcategories' article counts
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1  -- Tech category
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    ct.id,
    ct.name,
    COUNT(a.id) AS article_count
FROM category_tree ct
LEFT JOIN articles a ON a.category_id = ct.id AND a.status = 1 AND a.is_deleted = 0
GROUP BY ct.id, ct.name;

-- Query latest articles from users I follow
SELECT
    a.id,
    a.title,
    a.slug,
    a.summary,
    a.view_count,
    a.like_count,
    a.published_at,
    u.username AS author,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar
FROM user_follows f
JOIN articles a ON f.following_id = a.user_id
JOIN users u ON a.user_id = u.id
WHERE f.follower_id = 2
    AND a.status = 1
    AND a.is_deleted = 0
ORDER BY a.published_at DESC
LIMIT 20;

-- Published articles view (commonly used, simplifies queries)
CREATE VIEW v_published_articles AS
SELECT
    a.id,
    a.user_id,
    a.category_id,
    a.title,
    a.slug,
    a.summary,
    a.cover_image_url,
    a.status,
    a.is_pinned,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    a.created_at,
    u.username AS author_username,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar,
    c.name AS category_name,
    c.slug AS category_slug
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.status = 1 AND a.is_deleted = 0;

-- Query using the view
SELECT * FROM v_published_articles ORDER BY published_at DESC LIMIT 20;
SELECT * FROM v_published_articles WHERE category_id = 1 ORDER BY view_count DESC;

-- Article tags summary view
CREATE VIEW v_article_tags AS
SELECT
    at2.article_id,
    GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tag_names,
    GROUP_CONCAT(t.slug ORDER BY t.name SEPARATOR ', ') AS tag_slugs,
    COUNT(t.id) AS tag_count
FROM article_tag at2
JOIN tags t ON at2.tag_id = t.id
GROUP BY at2.article_id;

-- Articles with tags query
SELECT
    vpa.*,
    vat.tag_names,
    vat.tag_count
FROM v_published_articles vpa
LEFT JOIN v_article_tags vat ON vpa.id = vat.article_id
ORDER BY vpa.published_at DESC
LIMIT 20;

-- 1. Article list query optimization (covering index)
-- For WHERE status=1 AND is_deleted=0 ORDER BY published_at DESC
CREATE INDEX idx_articles_list ON articles(status, is_deleted, published_at DESC, id, title, slug, summary);

-- 2. Popular article ranking optimization
CREATE INDEX idx_articles_hot ON articles(status, is_deleted, published_at, view_count, like_count, comment_count);

-- 3. Category filtering optimization
CREATE INDEX idx_articles_category ON articles(category_id, status, is_deleted, published_at DESC);

-- 4. User article list optimization
CREATE INDEX idx_articles_user ON articles(user_id, status, is_deleted, published_at DESC);

-- 5. Comment list optimization
CREATE INDEX idx_comments_article ON comments(article_id, status, is_deleted, parent_id, created_at);

-- 6. User follow relationship optimization
CREATE INDEX idx_follows_following ON user_follows(following_id, follower_id);

-- 7. View index usage
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'blog_system'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 8. Analyze slow queries
EXPLAIN SELECT * FROM v_published_articles WHERE category_id = 1 ORDER BY view_count DESC LIMIT 20;