阶段 2 · 数据库实施

-- 可重复执行：先删后建（生产环境请勿直接跑）
DROP DATABASE IF EXISTS `hiwen`;
CREATE DATABASE `hiwen`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

USE `hiwen`;

-- 验证：这两列都应该是 utf8mb4 开头
SELECT @@character_set_database AS 字符集,
       @@collation_database     AS 排序规则;

-- ① 用户表：两个唯一键，保证登录名和邮箱都不重复
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID，主键自增',
  `username`   VARCHAR(50)  NOT NULL                COMMENT '登录名（唯一）',
  `password`   VARCHAR(255) NOT NULL                COMMENT '密码（哈希后存储，切勿明文）',
  `nickname`   VARCHAR(50)      DEFAULT NULL        COMMENT '昵称',
  `email`      VARCHAR(100)     DEFAULT NULL        COMMENT '邮箱（唯一，可空）',
  `avatar`     VARCHAR(255)     DEFAULT ''          COMMENT '头像图片路径',
  `intro`      VARCHAR(255)     DEFAULT NULL        COMMENT '个人简介',
  `gender`     TINYINT          DEFAULT 0           COMMENT '0未知 1男 2女',
  `status`     TINYINT      NOT NULL DEFAULT 1      COMMENT '1正常 0禁用',
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `updated_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                            ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_username` (`username`),
  UNIQUE KEY `uk_user_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ② 分类表：name 唯一，sort 控制前端展示顺序
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类ID',
  `name`        VARCHAR(30)  NOT NULL                COMMENT '分类名（如 唐诗、宋词）',
  `description` VARCHAR(255)     DEFAULT NULL        COMMENT '分类简介',
  `sort`        INT          NOT NULL DEFAULT 0      COMMENT '排序值，越小越前',
  `status`      TINYINT      NOT NULL DEFAULT 1      COMMENT '1显示 0隐藏',
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_category_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文学分类表';

DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '作品ID',
  `title`        VARCHAR(150) NOT NULL                COMMENT '标题',
  `author`       VARCHAR(50)      DEFAULT NULL        COMMENT '作者（如 李白）',
  `dynasty`      VARCHAR(20)      DEFAULT NULL        COMMENT '朝代（如 唐）',
  `category_id`  INT UNSIGNED     DEFAULT NULL        COMMENT '所属分类',
  `cover`        VARCHAR(255)     DEFAULT ''          COMMENT '封面图路径',
  `summary`      VARCHAR(500)     DEFAULT NULL        COMMENT '内容摘要',
  `content`      TEXT                                 COMMENT '正文',
  `views`        INT UNSIGNED NOT NULL DEFAULT 0      COMMENT '浏览量',
  `likes`        INT UNSIGNED NOT NULL DEFAULT 0      COMMENT '点赞数',
  `is_recommend` TINYINT      NOT NULL DEFAULT 0      COMMENT '1首页推荐 0普通',
  `status`       TINYINT      NOT NULL DEFAULT 1      COMMENT '1已发布 0草稿',
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  -- 外键列一定要单独建索引，否则 JOIN 和级联检查都要全表扫
  KEY `idx_article_category` (`category_id`),
  -- 复合索引：首页「推荐位 + 已发布」这条最常走的查询
  KEY `idx_article_recommend` (`is_recommend`, `status`),
  -- 全文索引：支持标题/摘要的关键词搜索
  FULLTEXT KEY `ft_article_title_summary` (`title`, `summary`),
  CONSTRAINT `fk_article_category` FOREIGN KEY (`category_id`)
    REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文学作品表';

-- ③ 书评表：parent_id 自关联，实现「楼中楼」回复
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '书评ID',
  `user_id`    INT UNSIGNED NOT NULL                COMMENT '评论人',
  `article_id` INT UNSIGNED NOT NULL                COMMENT '被评作品',
  `parent_id`  INT UNSIGNED     DEFAULT NULL        COMMENT '父评论ID（回复时填，否则 NULL）',
  `content`    VARCHAR(1000) NOT NULL               COMMENT '书评内容',
  `likes`      INT UNSIGNED NOT NULL DEFAULT 0      COMMENT '该条书评点赞数',
  `status`     TINYINT      NOT NULL DEFAULT 1      COMMENT '1正常 0已删除',
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  -- 列表接口固定走「某篇作品 + 正常状态」，所以建成复合索引
  KEY `idx_comment_article` (`article_id`, `status`),
  KEY `idx_comment_user` (`user_id`),
  KEY `idx_comment_parent` (`parent_id`),
  CONSTRAINT `fk_comment_user`    FOREIGN KEY (`user_id`)    REFERENCES `user` (`id`)    ON DELETE CASCADE,
  CONSTRAINT `fk_comment_article` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_comment_parent`  FOREIGN KEY (`parent_id`)  REFERENCES `comment` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='书评表';

-- ④ 收藏表：联合唯一键保证「一个人对一篇作品只能收藏一次」
DROP TABLE IF EXISTS `favorite`;
CREATE TABLE `favorite` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED NOT NULL COMMENT '用户',
  `article_id` INT UNSIGNED NOT NULL COMMENT '作品',
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_fav_user_article` (`user_id`, `article_id`),
  KEY `idx_fav_article` (`article_id`),
  CONSTRAINT `fk_fav_user`    FOREIGN KEY (`user_id`)    REFERENCES `user` (`id`)    ON DELETE CASCADE,
  CONSTRAINT `fk_fav_article` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收藏表';

-- ⑤ 点赞记录表：一张表管两种对象，靠 target_type 区分
DROP TABLE IF EXISTS `user_like`;
CREATE TABLE `user_like` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`     INT UNSIGNED NOT NULL,
  `target_type` ENUM('article','comment') NOT NULL COMMENT '点赞对象类型',
  `target_id`   INT UNSIGNED NOT NULL                COMMENT '对象ID',
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_like` (`user_id`, `target_type`, `target_id`),
  KEY `idx_like_target` (`target_type`, `target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='点赞记录表';

-- ⑥ 首页轮播图：独立配置，不依赖任何表
DROP TABLE IF EXISTS `banner`;
CREATE TABLE `banner` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`      VARCHAR(100) NOT NULL COMMENT '标题',
  `image`      VARCHAR(255) NOT NULL COMMENT '图片路径',
  `link`       VARCHAR(255) DEFAULT ''   COMMENT '点击跳转链接',
  `sort`       INT          NOT NULL DEFAULT 0,
  `status`     TINYINT      NOT NULL DEFAULT 1,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='首页轮播图表';

-- 作品列表视图：article 左连接 category，只保留已发布作品
CREATE OR REPLACE VIEW `v_article_list` AS
SELECT a.`id`, a.`title`, a.`author`, a.`dynasty`,
       c.`name` AS `category_name`,
       a.`cover`, a.`summary`, a.`views`, a.`likes`,
       a.`is_recommend`, a.`created_at`
FROM `article` a
LEFT JOIN `category` c ON a.`category_id` = c.`id`
WHERE a.`status` = 1;

-- 用法：像查一张普通表那样查视图
SELECT id, title, category_name, views
FROM v_article_list
WHERE is_recommend = 1
ORDER BY views DESC;

-- 分类：5 条（对应古典文学的体裁划分）
INSERT INTO `category` (`name`, `description`, `sort`) VALUES
('唐诗', '唐代诗歌，格律严谨、气象恢宏', 1),
('宋词', '宋代词作，婉约与豪放并存',   2),
('元曲', '元代散曲与杂剧，通俗生动',   3),
('散文', '先秦至明清的散文名篇',       4),
('小说', '章回体与笔记体小说',         5);

-- 作品：6 篇（category_id 对应上面的分类 id：1 唐诗 / 2 宋词 / 3 元曲 / 4 散文）
INSERT INTO `article`
  (`title`, `author`, `dynasty`, `category_id`, `summary`, `content`, `views`, `likes`, `is_recommend`) VALUES
('静夜思', '李白', '唐', 1, '床前明月光，疑是地上霜。举头望明月，低头思故乡。',
 '床前明月光，疑是地上霜。\n举头望明月，低头思故乡。\n\n【赏析】以月光为线索，写尽羁旅思乡之情。', 328, 42, 1),
('春望', '杜甫', '唐', 1, '国破山河在，城春草木深。',
 '国破山河在，城春草木深。\n感时花溅泪，恨别鸟惊心。\n\n【赏析】以乐景写哀情，字字沉痛。', 256, 31, 1),
('念奴娇·赤壁怀古', '苏轼', '宋', 2, '大江东去，浪淘尽，千古风流人物。',
 '大江东去，浪淘尽，千古风流人物。\n故垒西边，人道是，三国周郎赤壁。\n\n【赏析】豪放词代表，借古抒怀。', 412, 58, 1),
('声声慢', '李清照', '宋', 2, '寻寻觅觅，冷冷清清，凄凄惨惨戚戚。',
 '寻寻觅觅，冷冷清清，凄凄惨惨戚戚。\n乍暖还寒时候，最难将息。\n\n【赏析】叠字起句，婉约极致。', 298, 47, 0),
('天净沙·秋思', '马致远', '元', 3, '枯藤老树昏鸦，小桥流水人家。',
 '枯藤老树昏鸦，小桥流水人家，古道西风瘦马。\n夕阳西下，断肠人在天涯。\n\n【赏析】白描成画，秋思绝唱。', 187, 22, 0),
('陋室铭', '刘禹锡', '唐', 4, '山不在高，有仙则名；水不在深，有龙则灵。',
 '山不在高，有仙则名；水不在深，有龙则灵。\n斯是陋室，惟吾德馨。\n\n【赏析】托物言志，安贫乐道。', 203, 26, 0);

# 生成 123456 的哈希（Werkzeug 3.x 默认用 scrypt 算法）
python -c "from werkzeug.security import generate_password_hash as g; print(g('123456'))"

# 把输出替换掉脚本里的占位符（也可以建完库直接 UPDATE）
# mysql -uroot -p -e "UPDATE hiwen.user SET password='<上一步的输出>' WHERE username='admin';"

-- ① 在系统终端里连上客户端（路径按你的实际位置改）
--    mysql -uroot -p

-- ② 在 mysql> 提示符下导入整个脚本
source /Users/you/pyweb/sql/hiwen.sql

-- ③ 确认库里有哪些表（视图也会一并列出）
SHOW TABLES;

DESC `article`;

SELECT (SELECT COUNT(*) FROM `category`)  AS 分类,
       (SELECT COUNT(*) FROM `article`)   AS 作品,
       (SELECT COUNT(*) FROM `comment`)   AS 书评,
       (SELECT COUNT(*) FROM `favorite`)  AS 收藏,
       (SELECT COUNT(*) FROM `user_like`) AS 点赞,
       (SELECT COUNT(*) FROM `banner`)    AS 轮播图;

-- 列表接口的核心查询：作品 + 分类名
SELECT a.id, a.title, a.author, c.name AS category_name
FROM `article` a
JOIN `category` c ON a.category_id = c.id
ORDER BY a.id;

SELECT c.name AS 分类,
       COUNT(a.id) AS 篇数,
       SUM(a.views) AS 总浏览
FROM `category` c
LEFT JOIN `article` a ON a.category_id = c.id
GROUP BY c.id, c.name
ORDER BY c.sort;