xgkblog

-- 1. 用户表
CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL COMMENT '必须使用 password_hash() 加密存储',
    `email` VARCHAR(100),
    `role` ENUM('admin', 'editor', 'user') DEFAULT 'user'
);

-- 2. 文章分类表
CREATE TABLE `categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) UNIQUE
);

-- 3. 文章标签表
CREATE TABLE `tags` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE
);

-- 4. 文章表（必须在评论表和标签关联表之前创建）
CREATE TABLE `posts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `content` TEXT NOT NULL,
    `cover_image` VARCHAR(255) DEFAULT NULL COMMENT '文章封面或上传的文件路径',
    `category_id` INT,
    `author_id` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`),
    FOREIGN KEY (`author_id`) REFERENCES `users`(`id`)
);

-- 5. 文章-标签关联表（多对多关系）
CREATE TABLE `post_tags` (
    `post_id` INT,
    `tag_id` INT,
    PRIMARY KEY (`post_id`, `tag_id`),
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`) ON DELETE CASCADE
);

-- 6. 独立页表
CREATE TABLE `pages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(100) UNIQUE,
    `content` TEXT NOT NULL
);

-- 7. 评论表（现在 posts 表已存在，可以安全建立外键）
CREATE TABLE `comments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT,
    `user_id` INT DEFAULT NULL COMMENT '登录用户ID，为空则为游客',
    `guest_name` VARCHAR(50) COMMENT '游客昵称',
    `guest_email` VARCHAR(100) COMMENT '游客邮箱',
    `content` TEXT NOT NULL,
    `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending' COMMENT '审核状态',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE
);

-- 8. 点赞表
CREATE TABLE `likes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT,
    `user_ip` VARCHAR(45) COMMENT '用于防刷和游客点赞记录',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 9. 媒体管理表
CREATE TABLE `media` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `file_name` VARCHAR(255) NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `mime_type` VARCHAR(50),
    `uploaded_by` INT,
    `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 10. 友情链接表
CREATE TABLE `links` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `url` VARCHAR(255) NOT NULL,
    `description` VARCHAR(255)
);

-- 11. 系统设置表 (优化了字段名，避免使用保留字 key)
CREATE TABLE `settings` (
    `setting_key` VARCHAR(50) PRIMARY KEY,
    `setting_value` TEXT
);

-- 12. 插件管理表
CREATE TABLE `plugins` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `plugin_key` VARCHAR(50) UNIQUE NOT NULL,
    `is_active` TINYINT(1) DEFAULT 0
);