serve_template/sql/postgersql.sql

-- CREATE DATABASE  serve;
-- USE serve;

-- 创建 schema（如果不存在）
CREATE SCHEMA IF NOT EXISTS dev;


DROP TABLE IF EXISTS dev.user;
-- 在 dev schema 下创建用户表
CREATE TABLE dev.user (
    id SERIAL PRIMARY KEY,
    account VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user' CHECK ( role IN ('user','expert','admin') ),
    avatar TEXT,
    phone_number char(11),
    enable INT NOT NULL DEFAULT 1,
    delete_flag INT NOT NULL DEFAULT 0
);

-- =============================================
-- 钱包表
-- =============================================
DROP TABLE IF EXISTS dev.wallet;
CREATE TABLE dev.wallet (
    id          BIGSERIAL       PRIMARY KEY,
    user_id     BIGINT          NOT NULL UNIQUE,
    balance     DECIMAL(10,2)   NOT NULL DEFAULT 0,
    created_at  TIMESTAMP       NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP       NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.wallet IS '钱包表';
COMMENT ON COLUMN dev.wallet.user_id IS '用户ID';
COMMENT ON COLUMN dev.wallet.balance IS '钱包余额';

CREATE UNIQUE INDEX idx_wallet_user_id ON dev.wallet(user_id);

-- =============================================
-- 用户扩展信息表
-- =============================================
DROP TABLE IF EXISTS dev.user_profile;
CREATE TABLE dev.user_profile (
    id          BIGSERIAL   PRIMARY KEY,
    user_id     BIGINT      NOT NULL UNIQUE,
    level       VARCHAR(20) NOT NULL DEFAULT 'gold' CHECK ( level IN ('gold','diamond','master') ),
    is_realname BOOLEAN     DEFAULT FALSE,
    created_at  TIMESTAMP   NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP   NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.user_profile IS '用户扩展信息表';
COMMENT ON COLUMN dev.user_profile.level IS '等级：gold黄金 diamond钻石 master大师';
COMMENT ON COLUMN dev.user_profile.is_realname IS '是否已实名认证';

CREATE UNIQUE INDEX idx_up_user_id ON dev.user_profile(user_id);

-- 添加注释
COMMENT ON TABLE dev.user IS '用户表';
COMMENT ON COLUMN dev.user.id IS '用户ID，自增主键';
COMMENT ON COLUMN dev.user.account IS '账号，唯一';
COMMENT ON COLUMN dev.user.password IS '密码';
COMMENT ON COLUMN dev.user.username IS '用户名';
COMMENT ON COLUMN dev.user.role IS '角色';
COMMENT ON COLUMN dev.user.avatar IS '头像URL';
COMMENT ON COLUMN dev.user.phone_number IS '手机号码';
COMMENT ON COLUMN dev.user.enable IS '是否启用';
COMMENT ON COLUMN dev.user.delete_flag IS '删除标记';

-- 创建索引
CREATE INDEX idx_user_account ON dev.user(account);
CREATE INDEX idx_user_role ON dev.user(role);
CREATE INDEX idx_user_enable ON dev.user(enable);
CREATE INDEX idx_user_delete_flag ON dev.user(delete_flag);

-- 元数据
CREATE TABLE dev.meta (
                      id    SERIAL PRIMARY KEY,
                      key   VARCHAR(32) NOT NULL,
                      value JSONB
);

-- 2. 添加注释
COMMENT ON TABLE dev.meta IS '元数据表，用于存储键值对形式的配置或扩展属性';
COMMENT ON COLUMN dev.meta.id IS '自增主键，唯一标识一条元数据记录';
COMMENT ON COLUMN dev.meta.key IS '元数据的键，长度不超过32个字符，不可为空';
COMMENT ON COLUMN dev.meta.value IS '元数据的值，使用 JSONB 类型存储，支持结构化数据';

-- 3. 索引建议
-- 业务上 key 通常需要唯一约束，且频繁用于等值查询
CREATE UNIQUE INDEX idx_meta_key ON dev.meta (key);

-- 若经常对 value 内部字段进行查询或过滤，可添加 GIN 索引以加速 JSONB 操作
CREATE INDEX idx_meta_value_gin ON dev.meta USING GIN (value);

INSERT INTO dev.meta(key, value) VALUES
                                     ('site','{
                                       "title": "咕咕嘎嘎论坛",
                                       "logo": "",
                                       "announcement": "欢迎访问咕咕嘎嘎论坛！",
                                       "statement": "本站所有内容仅代表发布者个人观点，平台对内容的真实性、完整性、及时性不做任何保证，请用户理性参考，谨慎打赏。"
                                     }');

-- =============================================
-- 帖子表
-- =============================================
DROP TABLE IF EXISTS dev.post;
CREATE TABLE dev.post (
    id              BIGSERIAL       PRIMARY KEY,
    expert_id       BIGINT          NOT NULL,
    title           VARCHAR(200)    NOT NULL,
    content_intro   TEXT,
    content_paid    TEXT,
    price           DECIMAL(10,2)   NOT NULL,
    hit_status      VARCHAR(20)     NOT NULL DEFAULT 'pending' CHECK ( hit_status IN ('pending','hit','miss') ),
    view_count      INT             NOT NULL DEFAULT 0,
    publish_time    TIMESTAMP       NOT NULL DEFAULT now(),
    expire_time     TIMESTAMP       NOT NULL,
    delete_flag     INT             DEFAULT 0,
    created_at      TIMESTAMP       NOT NULL DEFAULT now(),
    updated_at      TIMESTAMP       NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.post IS '帖子表';
COMMENT ON COLUMN dev.post.id IS '帖子ID';
COMMENT ON COLUMN dev.post.expert_id IS '发布专家ID';
COMMENT ON COLUMN dev.post.title IS '帖子标题（含期号）';
COMMENT ON COLUMN dev.post.content_intro IS '内容简介（公开）';
COMMENT ON COLUMN dev.post.content_paid IS '付费内容';
COMMENT ON COLUMN dev.post.price IS '打赏金额';
COMMENT ON COLUMN dev.post.hit_status IS '命中状态：pending待确认 hit命中 miss未命中';
COMMENT ON COLUMN dev.post.view_count IS '查看人数（管理员可修改）';
COMMENT ON COLUMN dev.post.publish_time IS '发布时间';
COMMENT ON COLUMN dev.post.expire_time IS '过期时间，超出此时间自动转为公开';

CREATE INDEX idx_post_expert_id ON dev.post(expert_id);
CREATE INDEX idx_post_hit_status ON dev.post(hit_status);
CREATE INDEX idx_post_expire_time ON dev.post(expire_time);
CREATE INDEX idx_post_delete_flag ON dev.post(delete_flag);

-- =============================================
-- 帖子访问记录表
-- =============================================
DROP TABLE IF EXISTS dev.post_view_record;
CREATE TABLE dev.post_view_record (
    id          BIGSERIAL   PRIMARY KEY,
    post_id     BIGINT      NOT NULL,
    user_id     BIGINT      NOT NULL,
    view_time   TIMESTAMP   NOT NULL DEFAULT now(),
    created_at  TIMESTAMP   NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP   NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.post_view_record IS '帖子访问记录表';
COMMENT ON COLUMN dev.post_view_record.post_id IS '帖子ID';
COMMENT ON COLUMN dev.post_view_record.user_id IS '访问用户ID';

CREATE UNIQUE INDEX idx_pvr_post_user ON dev.post_view_record(post_id, user_id);

-- =============================================
-- 打赏订单表
-- =============================================
DROP TABLE IF EXISTS dev.order_tip;
CREATE TABLE dev.order_tip (
    id          BIGSERIAL       PRIMARY KEY,
    user_id     BIGINT          NOT NULL,
    post_id     BIGINT          NOT NULL,
    expert_id   BIGINT          NOT NULL,
    amount      DECIMAL(10,2)   NOT NULL,
    status      VARCHAR(20)     NOT NULL DEFAULT 'completed',
    create_time TIMESTAMP       NOT NULL DEFAULT now(),
    created_at  TIMESTAMP       NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP       NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.order_tip IS '打赏订单表';
COMMENT ON COLUMN dev.order_tip.user_id IS '打赏人ID';
COMMENT ON COLUMN dev.order_tip.post_id IS '帖子ID';
COMMENT ON COLUMN dev.order_tip.expert_id IS '被打赏专家ID';
COMMENT ON COLUMN dev.order_tip.amount IS '打赏金额';

CREATE INDEX idx_ot_user_id ON dev.order_tip(user_id);
CREATE INDEX idx_ot_post_id ON dev.order_tip(post_id);
CREATE INDEX idx_ot_expert_id ON dev.order_tip(expert_id);

-- =============================================
-- 钱包流水表
-- =============================================
DROP TABLE IF EXISTS dev.wallet_transaction;
CREATE TABLE dev.wallet_transaction (
    id              BIGSERIAL       PRIMARY KEY,
    user_id         BIGINT          NOT NULL,
    type            VARCHAR(20)     NOT NULL,
    amount          DECIMAL(10,2)   NOT NULL,
    balance_before  DECIMAL(10,2),
    balance_after   DECIMAL(10,2),
    status          VARCHAR(20)     NOT NULL DEFAULT 'success',
    remark          VARCHAR(255),
    review_time     TIMESTAMP,
    created_at      TIMESTAMP       NOT NULL DEFAULT now(),
    updated_at      TIMESTAMP       NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.wallet_transaction IS '钱包流水表';
COMMENT ON COLUMN dev.wallet_transaction.user_id IS '用户ID';
COMMENT ON COLUMN dev.wallet_transaction.type IS '流水类型：recharge充值 withdraw提现 tip_out打赏支出 admin_adjust管理员调整';
COMMENT ON COLUMN dev.wallet_transaction.amount IS '金额，正数为收入，负数为支出';

CREATE INDEX idx_wt_user_id ON dev.wallet_transaction(user_id);
CREATE INDEX idx_wt_type ON dev.wallet_transaction(type);

-- =============================================
-- 实名认证表
-- =============================================
DROP TABLE IF EXISTS dev.realname_auth;
CREATE TABLE dev.realname_auth (
    id              BIGSERIAL       PRIMARY KEY,
    user_id         BIGINT          NOT NULL UNIQUE,
    real_name       VARCHAR(50)     NOT NULL,
    id_card         VARCHAR(18)     NOT NULL,
    id_card_front   TEXT,
    id_card_back    TEXT,
    status          VARCHAR(20)     NOT NULL DEFAULT 'pending' CHECK ( status IN ('pending','approved','rejected') ),
    reject_reason   VARCHAR(255),
    review_time     TIMESTAMP,
    created_at      TIMESTAMP       NOT NULL DEFAULT now(),
    updated_at      TIMESTAMP       NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.realname_auth IS '实名认证表';
COMMENT ON COLUMN dev.realname_auth.user_id IS '用户ID';
COMMENT ON COLUMN dev.realname_auth.real_name IS '真实姓名';
COMMENT ON COLUMN dev.realname_auth.id_card IS '身份证号';
COMMENT ON COLUMN dev.realname_auth.status IS '认证状态：pending待审核 approved通过 rejected驳回';

CREATE UNIQUE INDEX idx_ra_user_id ON dev.realname_auth(user_id);

-- =============================================
-- 系统通知表
-- =============================================
DROP TABLE IF EXISTS dev.notification;
CREATE TABLE dev.notification (
    id          BIGSERIAL       PRIMARY KEY,
    user_id     BIGINT          NOT NULL,
    type        VARCHAR(30)     NOT NULL,
    title       VARCHAR(100),
    content     TEXT,
    is_read     BOOLEAN         DEFAULT FALSE,
    created_at  TIMESTAMP       NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP       NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.notification IS '系统通知表';
COMMENT ON COLUMN dev.notification.user_id IS '接收人ID';
COMMENT ON COLUMN dev.notification.type IS '通知类型：tip_success recharge_success withdraw_request withdraw_success withdraw_failed system';

CREATE INDEX idx_notif_user_id ON dev.notification(user_id);
CREATE INDEX idx_notif_is_read ON dev.notification(is_read);

-- =============================================
-- OSS附件表
-- =============================================
DROP TABLE IF EXISTS dev.attachment;
CREATE TABLE dev.attachment (
    id          BIGSERIAL   PRIMARY KEY,
    user_id     BIGINT      NOT NULL,
    url         TEXT        NOT NULL,
    type        VARCHAR(20),
    created_at  TIMESTAMP   NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP   NOT NULL DEFAULT now()
);

COMMENT ON TABLE dev.attachment IS 'OSS附件表';
COMMENT ON COLUMN dev.attachment.user_id IS '上传人ID';
COMMENT ON COLUMN dev.attachment.url IS 'OSS文件URL';
COMMENT ON COLUMN dev.attachment.type IS '附件类型：avatar头像 id_card身份证 post_image帖子图片';

CREATE INDEX idx_attachment_user_id ON dev.attachment(user_id);