-- # 书籍评论表
--
-- ## 基本信息
--
-- 模块：book
-- 表名：book_comment
-- 模型：model/book/book_comment.go
-- 迁移接入：initialize/gorm_biz.go
-- 删除策略：硬删表
-- 评论状态字典：book_comment_status
-- 职责：承载书籍、章节和文本行三级评论定位信息，并冗余维护评论点赞聚合值。

CREATE TABLE book_comment (
  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,
  member_user_id bigint NOT NULL,
  book_id bigint NOT NULL,
  chapter_id bigint NOT NULL DEFAULT 0 CHECK (chapter_id >= 0),
  line_index integer NOT NULL DEFAULT 0 CHECK (line_index >= 0),
  content text NOT NULL,
  like_count bigint NOT NULL DEFAULT 0 CHECK (like_count >= 0),
  comment_status varchar(32) NOT NULL DEFAULT 'normal',
  CHECK ((chapter_id = 0 AND line_index = 0) OR (chapter_id > 0 AND line_index >= 0))
);

COMMENT ON TABLE book_comment IS '书籍评论表';
COMMENT ON COLUMN book_comment.id IS '主键';
COMMENT ON COLUMN book_comment.created_at IS '创建时间';
COMMENT ON COLUMN book_comment.updated_at IS '更新时间';
COMMENT ON COLUMN book_comment.member_user_id IS '评论用户的会员 ID';
COMMENT ON COLUMN book_comment.book_id IS '所属书籍 ID';
COMMENT ON COLUMN book_comment.chapter_id IS '评论目标章节 ID，0 表示整本书';
COMMENT ON COLUMN book_comment.line_index IS '评论目标文本行下标，0 表示整章或整本书';
COMMENT ON COLUMN book_comment.content IS '评论正文内容';
COMMENT ON COLUMN book_comment.like_count IS '评论点赞聚合值';
COMMENT ON COLUMN book_comment.comment_status IS '评论状态字典值，对应 book_comment_status';

CREATE INDEX idx_book_comment_book_id ON book_comment (book_id);
CREATE INDEX idx_book_comment_book_id_chapter_id_line_index ON book_comment (book_id, chapter_id, line_index);
CREATE INDEX idx_book_comment_member_user_id ON book_comment (member_user_id);
CREATE INDEX idx_book_comment_comment_status ON book_comment (comment_status);
CREATE INDEX idx_book_comment_created_at ON book_comment (created_at);