-- # 书籍信息表
--
-- ## 基本信息
--
-- 模块：book
-- 表名：book
-- 模型：model/book/book.go
-- 迁移接入：initialize/gorm_biz.go
-- 删除策略：硬删表
-- 书籍类型字典：book_type
-- 时代标签字典：book_era_tag
-- 完结状态字典：book_completion_status
-- 上下架状态字典：book_publish_status
-- 职责：承载书籍主资料、展示聚合值和章节来源配置，是书籍列表、详情、搜索和推荐的核心主体。

CREATE TABLE book (
  id bigserial PRIMARY KEY,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  title varchar(255) NOT NULL,
  subtitle varchar(255),
  book_type varchar(64) NOT NULL,
  era_tag varchar(32) NOT NULL DEFAULT 'unknown',
  cover_url varchar(500),
  publisher varchar(128),
  published_at date,
  intro text,
  hot_score bigint NOT NULL DEFAULT 0 CHECK (hot_score >= 0),
  rating numeric(3,1) NOT NULL DEFAULT 0.0 CHECK (rating >= 0 AND rating <= 10),
  comment_count bigint NOT NULL DEFAULT 0 CHECK (comment_count >= 0),
  word_count bigint NOT NULL DEFAULT 0 CHECK (word_count >= 0),
  completion_status varchar(32) NOT NULL DEFAULT 'serializing',
  publish_status varchar(32) NOT NULL DEFAULT 'draft',
  series_id bigint,
  series_sort integer NOT NULL DEFAULT 0 CHECK (series_sort >= 0),
  raw_txt_url varchar(500)
);

COMMENT ON TABLE book IS '书籍信息表';
COMMENT ON COLUMN book.id IS '主键';
COMMENT ON COLUMN book.created_at IS '创建时间';
COMMENT ON COLUMN book.updated_at IS '更新时间';
COMMENT ON COLUMN book.title IS '书名主标题';
COMMENT ON COLUMN book.subtitle IS '书籍副标题';
COMMENT ON COLUMN book.book_type IS '书籍类型字典值，对应 book_type';
COMMENT ON COLUMN book.era_tag IS '时代标签字典值，对应 book_era_tag';
COMMENT ON COLUMN book.cover_url IS '封面图片 URL';
COMMENT ON COLUMN book.publisher IS '出版社名称';
COMMENT ON COLUMN book.published_at IS '出版日期';
COMMENT ON COLUMN book.intro IS '书籍简介';
COMMENT ON COLUMN book.hot_score IS '热度聚合值';
COMMENT ON COLUMN book.rating IS '书籍评分，范围 0-10';
COMMENT ON COLUMN book.comment_count IS '点评数聚合值';
COMMENT ON COLUMN book.word_count IS '书籍总字数';
COMMENT ON COLUMN book.completion_status IS '书籍完结状态字典值，对应 book_completion_status';
COMMENT ON COLUMN book.publish_status IS '书籍上下架状态字典值，对应 book_publish_status';
COMMENT ON COLUMN book.series_id IS '所属系列 ID，可为空';
COMMENT ON COLUMN book.series_sort IS '同系列内展示排序';
COMMENT ON COLUMN book.raw_txt_url IS '原始 txt 文件 URL';

CREATE INDEX idx_book_book_type ON book (book_type);
CREATE INDEX idx_book_era_tag ON book (era_tag);
CREATE INDEX idx_book_publish_status ON book (publish_status);
CREATE INDEX idx_book_completion_status ON book (completion_status);
CREATE INDEX idx_book_series_id_series_sort ON book (series_id, series_sort);
CREATE INDEX idx_book_created_at ON book (created_at);