-- # 用户收藏记录表
--
-- ## 基本信息
--
-- 模块：book
-- 表名：book_favorite_record
-- 模型：model/book/book_favorite_record.go
-- 迁移接入：initialize/gorm_biz.go
-- 删除策略：硬删表
-- 职责：承载用户对书籍的收藏关系，保证同一用户对同一本书只保留一条有效收藏记录。

CREATE TABLE book_favorite_record (
  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,
  favorited_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE book_favorite_record IS '用户收藏记录表';
COMMENT ON COLUMN book_favorite_record.id IS '主键';
COMMENT ON COLUMN book_favorite_record.created_at IS '创建时间';
COMMENT ON COLUMN book_favorite_record.updated_at IS '更新时间';
COMMENT ON COLUMN book_favorite_record.member_user_id IS '收藏用户的会员 ID';
COMMENT ON COLUMN book_favorite_record.book_id IS '收藏书籍 ID';
COMMENT ON COLUMN book_favorite_record.favorited_at IS '收藏发生时间';

CREATE UNIQUE INDEX uk_book_favorite_record_member_user_id_book_id ON book_favorite_record (member_user_id, book_id);
CREATE INDEX idx_book_favorite_record_member_user_id_favorited_at ON book_favorite_record (member_user_id, favorited_at);
CREATE INDEX idx_book_favorite_record_book_id ON book_favorite_record (book_id);
