-- PHP 论坛数据库结构
-- 创建数据库（如果需要）
-- CREATE DATABASE IF NOT EXISTS forum DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE forum;

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    avatar VARCHAR(255) DEFAULT NULL COMMENT '头像',
    signature VARCHAR(500) DEFAULT NULL COMMENT '个性签名',
    role TINYINT UNSIGNED DEFAULT 0 COMMENT '角色: 0普通用户, 1管理员, 2超级管理员',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0禁用, 1正常',
    post_count INT UNSIGNED DEFAULT 0 COMMENT '发帖数',
    reply_count INT UNSIGNED DEFAULT 0 COMMENT '回复数',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    last_login_at TIMESTAMP NULL DEFAULT NULL COMMENT '最后登录时间',
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- 板块分类表
CREATE TABLE IF NOT EXISTS categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '板块名称',
    description VARCHAR(500) DEFAULT NULL COMMENT '板块描述',
    icon VARCHAR(100) DEFAULT NULL COMMENT '图标',
    sort_order INT UNSIGNED DEFAULT 0 COMMENT '排序',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0隐藏, 1显示',
    post_count INT UNSIGNED DEFAULT 0 COMMENT '帖子数量',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sort (sort_order),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='板块分类表';

-- 帖子表
CREATE TABLE IF NOT EXISTS posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL COMMENT '作者ID',
    category_id INT UNSIGNED NOT NULL COMMENT '板块ID',
    title VARCHAR(200) NOT NULL COMMENT '标题',
    content TEXT NOT NULL COMMENT '内容',
    is_top TINYINT UNSIGNED DEFAULT 0 COMMENT '是否置顶: 0否, 1是',
    is_essence TINYINT UNSIGNED DEFAULT 0 COMMENT '是否精华: 0否, 1是',
    is_locked TINYINT UNSIGNED DEFAULT 0 COMMENT '是否锁定: 0否, 1是',
    view_count INT UNSIGNED DEFAULT 0 COMMENT '浏览次数',
    reply_count INT UNSIGNED DEFAULT 0 COMMENT '回复数量',
    last_reply_at TIMESTAMP NULL DEFAULT NULL COMMENT '最后回复时间',
    last_reply_user_id INT UNSIGNED DEFAULT NULL COMMENT '最后回复用户ID',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0删除, 1正常',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_category_id (category_id),
    INDEX idx_status (status),
    INDEX idx_is_top (is_top),
    INDEX idx_is_locked (is_locked),
    INDEX idx_created_at (created_at),
    INDEX idx_last_reply_at (last_reply_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='帖子表';

-- 回复表
CREATE TABLE IF NOT EXISTS replies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL COMMENT '帖子ID',
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    content TEXT NOT NULL COMMENT '回复内容',
    parent_id INT UNSIGNED DEFAULT NULL COMMENT '父回复ID（用于楼中楼）',
    floor_number INT UNSIGNED DEFAULT 0 COMMENT '楼层号',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0删除, 1正常',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_post_id (post_id),
    INDEX idx_user_id (user_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES replies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='回复表';

-- 系统设置表
CREATE TABLE IF NOT EXISTS settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE COMMENT '设置键',
    setting_value TEXT COMMENT '设置值',
    description VARCHAR(255) DEFAULT NULL COMMENT '描述',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统设置表';

-- 点赞表
CREATE TABLE IF NOT EXISTS likes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL COMMENT '点赞用户ID',
    target_type ENUM('post', 'reply') NOT NULL COMMENT '目标类型: post帖子, reply回复',
    target_id INT UNSIGNED NOT NULL COMMENT '目标ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_target (user_id, target_type, target_id),
    INDEX idx_target (target_type, target_id),
    INDEX idx_user_id (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='点赞表';

-- 收藏表
CREATE TABLE IF NOT EXISTS favorites (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL COMMENT '收藏用户ID',
    target_type ENUM('post', 'reply') NOT NULL COMMENT '目标类型: post帖子, reply回复',
    target_id INT UNSIGNED NOT NULL COMMENT '目标ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_target (user_id, target_type, target_id),
    INDEX idx_target (target_type, target_id),
    INDEX idx_user_id (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='收藏表';

-- 消息通知表
CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL COMMENT '接收通知的用户ID',
    sender_id INT UNSIGNED DEFAULT NULL COMMENT '发送者用户ID',
    type ENUM('like', 'favorite', 'reply', 'follow') NOT NULL COMMENT '通知类型: like点赞, favorite收藏, reply回复, follow关注',
    target_type ENUM('post', 'reply') DEFAULT NULL COMMENT '目标类型',
    target_id INT UNSIGNED DEFAULT NULL COMMENT '目标ID',
    content VARCHAR(255) DEFAULT NULL COMMENT '通知内容摘要',
    is_read TINYINT UNSIGNED DEFAULT 0 COMMENT '是否已读: 0未读, 1已读',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_sender_id (sender_id),
    INDEX idx_type (type),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息通知表';

-- 插入默认板块数据
INSERT INTO categories (name, description, icon, sort_order) VALUES
('综合讨论', '论坛综合话题讨论区', 'fa-comments', 1),
('技术交流', '编程技术、开发经验分享', 'fa-code', 2),
('生活闲聊', '日常生活、兴趣爱好交流', 'fa-coffee', 3),
('问题求助', '遇到问题可以在这里求助', 'fa-question-circle', 4);

-- 勋章表
CREATE TABLE IF NOT EXISTS medals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '勋章名称',
    description VARCHAR(500) DEFAULT NULL COMMENT '勋章描述',
    icon VARCHAR(255) NOT NULL COMMENT '勋章图标路径',
    type VARCHAR(50) NOT NULL COMMENT '勋章类型: bookmark收藏, comment评论, like点赞',
    condition_type VARCHAR(50) NOT NULL COMMENT '条件类型: post_favorited帖子被收藏数, reply_count回复数, post_liked帖子被点赞数',
    condition_value INT UNSIGNED NOT NULL COMMENT '条件数值',
    sort_order INT UNSIGNED DEFAULT 0 COMMENT '排序',
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态: 0禁用, 1启用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='勋章表';

-- 用户勋章表
CREATE TABLE IF NOT EXISTS user_medals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    medal_id INT UNSIGNED NOT NULL COMMENT '勋章ID',
    is_wearing TINYINT UNSIGNED DEFAULT 0 COMMENT '是否佩戴: 0否, 1是',
    awarded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '获得时间',
    UNIQUE KEY uk_user_medal (user_id, medal_id),
    INDEX idx_user_id (user_id),
    INDEX idx_is_wearing (is_wearing),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (medal_id) REFERENCES medals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户勋章表';

-- 插入默认勋章数据
INSERT INTO medals (name, description, icon, type, condition_type, condition_value, sort_order) VALUES
('收藏达人', '发布的帖子被收藏12次', 'assets/images/Medal/Bookmark.png', 'bookmark', 'post_favorited', 12, 1),
('评论先锋', '参与50次评论', 'assets/images/Medal/Comment.png', 'comment', 'reply_count', 50, 2),
('人气作者', '发布的帖子被点赞20次', 'assets/images/Medal/Like.png', 'like', 'post_liked', 20, 3);

-- 插入默认设置（使用 INSERT IGNORE 避免重复安装时报错）
INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
('site_name', 'PHP论坛', '网站名称'),
('site_description', '一个简洁优雅的PHP论坛系统', '网站描述'),
('site_keywords', 'PHP,论坛,社区', '网站关键词'),
('posts_per_page', '20', '每页帖子数'),
('replies_per_page', '20', '每页回复数'),
('allow_register', '1', '是否允许注册: 1允许, 0禁止'),
('need_approve', '0', '注册是否需要审核: 1需要, 0不需要');
