serve_template/sql/postgersql.sql

-- CREATE DATABASE  serve;
-- USE serve;

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


-- 删除序列（如果存在）
-- DROP SEQUENCE IF EXISTS dev.user_id_seq;

-- 创建新序列
-- CREATE SEQUENCE dev.user_id_seq
--     START WITH 1
--     INCREMENT BY 1
--     NO MINVALUE
--     NO MAXVALUE
--     CACHE 1;

-- 添加注释
-- COMMENT ON SEQUENCE dev.user_seq IS '用户表ID序列';

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
);

-- 添加注释
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
                                     ('website_config','{
                                       "title": "示例网站",
                                       "logo": "",
                                       "announcement": "欢迎访问我们的网站！",
                                       "statement": "版权所有 © 2025 示例网站"
                                     }');