OJ_server/sql/postgersql.sql

CREATE DATABASE  oj;
-- USE oj;

-- 创建 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 BIGSERIAL 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', 'coach', 'admin')),
                          avatar TEXT,
                          enable INT NOT NULL DEFAULT 1 CHECK (enable IN (0, 1)),
                          delete_flag INT NOT NULL DEFAULT 0 CHECK (delete_flag IN (0, 1)),
                          created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                          updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- 添加注释
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 '角色：user-普通用户，coach-教练，admin-管理员';
COMMENT ON COLUMN dev.user.avatar IS '头像URL';
COMMENT ON COLUMN dev.user.enable IS '是否启用';
COMMENT ON COLUMN dev.user.delete_flag IS '删除标记';
COMMENT ON COLUMN dev.user.created_at IS '创建时间';
COMMENT ON COLUMN dev.user.updated_at IS '更新时间';

-- 创建索引
CREATE INDEX idx_user_role ON dev.user(role);

-- 自动刷新 updated_at 的触发器函数
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 用户表更新时间自动刷新触发器
CREATE TRIGGER trg_user_updated_at
BEFORE UPDATE ON dev.user
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE INDEX idx_user_enable ON dev.user(enable);
CREATE INDEX idx_user_delete_flag ON dev.user(delete_flag);

-- 题目表
DROP TABLE IF EXISTS dev.problem;
CREATE TABLE dev.problem (
                         id BIGSERIAL PRIMARY KEY,
                         title VARCHAR(200) NOT NULL,
                         description TEXT NOT NULL,
                         time_limit_ms INT NOT NULL CHECK (time_limit_ms > 0),
                         memory_limit_mb INT NOT NULL CHECK (memory_limit_mb > 0),
                         try_count BIGINT NOT NULL DEFAULT 0,
                         accepted_count BIGINT NOT NULL DEFAULT 0,
                         is_public SMALLINT NOT NULL DEFAULT 0 CHECK (is_public IN (0, 1)),
                         difficulty VARCHAR(10) CHECK (difficulty IN ('easy', 'medium', 'hard')),
                         created_by BIGINT,
                         created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                         updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE dev.problem IS '题目表';
COMMENT ON COLUMN dev.problem.id IS '题目唯一标识';
COMMENT ON COLUMN dev.problem.title IS '题目标题';
COMMENT ON COLUMN dev.problem.description IS '题目描述';
COMMENT ON COLUMN dev.problem.time_limit_ms IS '时间限制（毫秒）';
COMMENT ON COLUMN dev.problem.memory_limit_mb IS '内存限制（MB）';
COMMENT ON COLUMN dev.problem.try_count IS '总提交次数';
COMMENT ON COLUMN dev.problem.accepted_count IS '通过次数（AC次数）';
COMMENT ON COLUMN dev.problem.is_public IS '是否公开题目,0-未公开,1-公开';
COMMENT ON COLUMN dev.problem.difficulty IS '难度：easy-简单，medium-中等，hard-困难';
COMMENT ON COLUMN dev.problem.created_by IS '创建者ID，逻辑外键，关联用户表';
COMMENT ON COLUMN dev.problem.created_at IS '创建时间';
COMMENT ON COLUMN dev.problem.updated_at IS '更新时间';

-- 题目样例表
DROP TABLE IF EXISTS dev.problem_case;
CREATE TABLE dev.problem_case (
                              id BIGSERIAL PRIMARY KEY,
                              problem_id BIGINT NOT NULL,
                              input TEXT,
                              output TEXT,
                              description TEXT,
                              sort_order INT NOT NULL DEFAULT 0,
                              created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                              updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE dev.problem_case IS '题目样例表（展示给用户的示例）';
COMMENT ON COLUMN dev.problem_case.id IS '样例唯一标识';
COMMENT ON COLUMN dev.problem_case.problem_id IS '所属题目ID，逻辑外键，关联题目表';
COMMENT ON COLUMN dev.problem_case.input IS '样例输入';
COMMENT ON COLUMN dev.problem_case.output IS '样例输出';
COMMENT ON COLUMN dev.problem_case.description IS '样例说明';
COMMENT ON COLUMN dev.problem_case.sort_order IS '显示顺序';
COMMENT ON COLUMN dev.problem_case.created_at IS '创建时间';
COMMENT ON COLUMN dev.problem_case.updated_at IS '更新时间';

-- 题目评测用例表
DROP TABLE IF EXISTS dev.problem_test;
CREATE TABLE dev.problem_test (
                              id BIGSERIAL PRIMARY KEY,
                              problem_id BIGINT NOT NULL,
                              input TEXT,
                              output TEXT,
                              sort_order INT NOT NULL DEFAULT 0,
                              is_hidden INT NOT NULL DEFAULT 0,
                              created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                              updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE dev.problem_test IS '题目评测用例表（后台判题用）';
COMMENT ON COLUMN dev.problem_test.id IS '用例唯一标识';
COMMENT ON COLUMN dev.problem_test.problem_id IS '所属题目ID，逻辑外键，关联题目表';
COMMENT ON COLUMN dev.problem_test.input IS '测试输入';
COMMENT ON COLUMN dev.problem_test.output IS '测试输出';
COMMENT ON COLUMN dev.problem_test.sort_order IS '判题顺序';
COMMENT ON COLUMN dev.problem_test.is_hidden IS '是否隐藏用例，0-不隐藏，1-隐藏（不展示给用户）';
COMMENT ON COLUMN dev.problem_test.created_at IS '创建时间';
COMMENT ON COLUMN dev.problem_test.updated_at IS '更新时间';

-- 提交记录表
DROP TABLE IF EXISTS dev.submission;
CREATE TABLE dev.submission (
                            id BIGSERIAL PRIMARY KEY,
                            problem_id BIGINT NOT NULL,
                            user_id BIGINT NOT NULL,
                            contest_id BIGINT,
                            code TEXT NOT NULL,
                            language VARCHAR(20) NOT NULL,
                            status VARCHAR(20) NOT NULL DEFAULT 'SUBMITTED' CHECK (status IN ('SUBMITTED', 'JUDGING', 'AC', 'WA', 'TLE', 'MLE', 'RE', 'CE')),
                            judge_detail TEXT,
                            time_used_ms INT,
                            memory_used_kb INT,
                            score INT,
                            total_test_cases INT NOT NULL DEFAULT 0,
                            passed_test_cases INT NOT NULL DEFAULT 0,
                            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE dev.submission IS '提交记录表';
COMMENT ON COLUMN dev.submission.id IS '提交唯一标识';
COMMENT ON COLUMN dev.submission.problem_id IS '题目ID，逻辑外键，关联题目表';
COMMENT ON COLUMN dev.submission.user_id IS '提交用户ID，逻辑外键，关联用户表';
COMMENT ON COLUMN dev.submission.contest_id IS '所属比赛ID，逻辑外键，关联比赛表，为空表示非比赛提交';
COMMENT ON COLUMN dev.submission.code IS '提交代码';
COMMENT ON COLUMN dev.submission.language IS '编程语言（c/cpp/java/python等）';
COMMENT ON COLUMN dev.submission.status IS '判题状态：SUBMITTED-提交成功，JUDGING-判题中，AC-全部测试用例通过，WA-输出与预期不符，TLE-运行时间超限，MLE-内存使用超限，RE-运行时错误，CE-编译失败';
COMMENT ON COLUMN dev.submission.judge_detail IS '判题详情（如编译错误输出、失败用例说明等）';
COMMENT ON COLUMN dev.submission.time_used_ms IS '运行耗时（毫秒）';
COMMENT ON COLUMN dev.submission.memory_used_kb IS '内存占用（KB）';
COMMENT ON COLUMN dev.submission.score IS '该题得分（用于比赛）';
COMMENT ON COLUMN dev.submission.total_test_cases IS '总测试用例数';
COMMENT ON COLUMN dev.submission.passed_test_cases IS '通过的测试用例数';
COMMENT ON COLUMN dev.submission.created_at IS '创建时间';
COMMENT ON COLUMN dev.submission.updated_at IS '更新时间';

-- 题目类别表
DROP TABLE IF EXISTS dev.category;
CREATE TABLE dev.category (
                          id BIGSERIAL PRIMARY KEY,
                          category_name VARCHAR(50) NOT NULL UNIQUE,
                          description TEXT,
                          created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                          updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE dev.category IS '题目类别表（如动态规划、贪心等）';
COMMENT ON COLUMN dev.category.id IS '类别唯一标识';
COMMENT ON COLUMN dev.category.category_name IS '类别名称';
COMMENT ON COLUMN dev.category.description IS '类别描述';
COMMENT ON COLUMN dev.category.created_at IS '创建时间';
COMMENT ON COLUMN dev.category.updated_at IS '更新时间';

-- 题目-类别关联表
DROP TABLE IF EXISTS dev.problem_category_rel;
CREATE TABLE dev.problem_category_rel (
                                      problem_id BIGINT NOT NULL,
                                      category_id BIGINT NOT NULL,
                                      created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                      updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                      PRIMARY KEY (problem_id, category_id)
);

COMMENT ON TABLE dev.problem_category_rel IS '题目与类别关联表';
COMMENT ON COLUMN dev.problem_category_rel.problem_id IS '题目ID，逻辑外键，关联题目表';
COMMENT ON COLUMN dev.problem_category_rel.category_id IS '类别ID，逻辑外键，关联类别表';
COMMENT ON COLUMN dev.problem_category_rel.created_at IS '创建时间';
COMMENT ON COLUMN dev.problem_category_rel.updated_at IS '更新时间';

-- 比赛表
DROP TABLE IF EXISTS dev.contest;
CREATE TABLE dev.contest (
                         id BIGSERIAL PRIMARY KEY,
                         title VARCHAR(200) NOT NULL,
                         description TEXT,
                         type VARCHAR(20) NOT NULL CHECK (type IN ('public', 'private', 'password')),
                         password VARCHAR(255),
                         start_datetime TIMESTAMP NOT NULL,
                         end_datetime TIMESTAMP NOT NULL CHECK (end_datetime > start_datetime),
                         status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'ended')),
                         created_by BIGINT,
                         created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                         updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE dev.contest IS '比赛表';
COMMENT ON COLUMN dev.contest.id IS '比赛唯一标识';
COMMENT ON COLUMN dev.contest.title IS '比赛标题';
COMMENT ON COLUMN dev.contest.description IS '比赛描述';
COMMENT ON COLUMN dev.contest.type IS '比赛类型：public-公开，private-私有，password-密码访问';
COMMENT ON COLUMN dev.contest.password IS '访问密码（type为password时使用）';
COMMENT ON COLUMN dev.contest.start_datetime IS '开始时间';
COMMENT ON COLUMN dev.contest.end_datetime IS '结束时间';
COMMENT ON COLUMN dev.contest.status IS '状态：pending-未开始，running-进行中，ended-已结束';
COMMENT ON COLUMN dev.contest.created_by IS '创建者ID，逻辑外键，关联用户表（管理员或教练）';
COMMENT ON COLUMN dev.contest.created_at IS '创建时间';
COMMENT ON COLUMN dev.contest.updated_at IS '更新时间';

-- 比赛题目表
DROP TABLE IF EXISTS dev.contest_problem;
CREATE TABLE dev.contest_problem (
                                 contest_id BIGINT NOT NULL,
                                 problem_id BIGINT NOT NULL,
                                 display_order INT NOT NULL,
                                 score INT NOT NULL,
                                 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                 updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                 PRIMARY KEY (contest_id, problem_id)
);

COMMENT ON TABLE dev.contest_problem IS '比赛与题目关联表';
COMMENT ON COLUMN dev.contest_problem.contest_id IS '比赛ID，逻辑外键，关联比赛表';
COMMENT ON COLUMN dev.contest_problem.problem_id IS '题目ID，逻辑外键，关联题目表';
COMMENT ON COLUMN dev.contest_problem.display_order IS '题目在比赛中的显示序号';
COMMENT ON COLUMN dev.contest_problem.score IS '本题分值';
COMMENT ON COLUMN dev.contest_problem.created_at IS '创建时间';
COMMENT ON COLUMN dev.contest_problem.updated_at IS '更新时间';

-- 比赛报名表
DROP TABLE IF EXISTS dev.contest_registration;
CREATE TABLE dev.contest_registration (
                                      id BIGSERIAL PRIMARY KEY,
                                      contest_id BIGINT NOT NULL,
                                      user_id BIGINT NOT NULL,
                                      created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                      updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                      UNIQUE (contest_id, user_id)
);

COMMENT ON TABLE dev.contest_registration IS '比赛报名表';
COMMENT ON COLUMN dev.contest_registration.id IS '报名记录ID';
COMMENT ON COLUMN dev.contest_registration.contest_id IS '比赛ID，逻辑外键，关联比赛表';
COMMENT ON COLUMN dev.contest_registration.user_id IS '报名用户ID，逻辑外键，关联用户表';
COMMENT ON COLUMN dev.contest_registration.created_at IS '创建时间';
COMMENT ON COLUMN dev.contest_registration.updated_at IS '更新时间';

-- 索引
CREATE INDEX idx_problem_created_by ON dev.problem(created_by);
CREATE INDEX idx_problem_is_public ON dev.problem(is_public);
CREATE INDEX idx_problem_case_problem_id ON dev.problem_case(problem_id);
CREATE INDEX idx_problem_test_problem_id ON dev.problem_test(problem_id);
CREATE INDEX idx_submission_problem_id ON dev.submission(problem_id);
CREATE INDEX idx_submission_user_id ON dev.submission(user_id);
CREATE INDEX idx_submission_contest_id ON dev.submission(contest_id);
CREATE INDEX idx_submission_created_at ON dev.submission(created_at);
CREATE INDEX idx_submission_user_problem ON dev.submission(user_id, problem_id);
CREATE INDEX idx_submission_contest_status ON dev.submission(contest_id, status);
CREATE INDEX idx_problem_category_rel_problem_id ON dev.problem_category_rel(problem_id);
CREATE INDEX idx_problem_category_rel_category_id ON dev.problem_category_rel(category_id);
CREATE INDEX idx_contest_status ON dev.contest(status);
CREATE INDEX idx_contest_start_datetime ON dev.contest(start_datetime);
CREATE INDEX idx_contest_problem_contest_id ON dev.contest_problem(contest_id);
CREATE INDEX idx_contest_problem_problem_id ON dev.contest_problem(problem_id);
CREATE INDEX idx_contest_registration_contest_id ON dev.contest_registration(contest_id);
CREATE INDEX idx_contest_registration_user_id ON dev.contest_registration(user_id);

COMMENT ON INDEX idx_problem_created_by IS '题目创建者索引';
COMMENT ON INDEX idx_problem_is_public IS '题目公开状态索引';
COMMENT ON INDEX idx_problem_case_problem_id IS '题目样例题目ID索引';
COMMENT ON INDEX idx_problem_test_problem_id IS '评测用例题目ID索引';
COMMENT ON INDEX idx_submission_problem_id IS '提交记录题目ID索引';
COMMENT ON INDEX idx_submission_user_id IS '提交记录用户ID索引';
COMMENT ON INDEX idx_submission_contest_id IS '提交记录比赛ID索引';
COMMENT ON INDEX idx_submission_created_at IS '提交记录时间索引';
COMMENT ON INDEX idx_submission_user_problem IS '提交记录用户+题目索引';
COMMENT ON INDEX idx_submission_contest_status IS '提交记录比赛+状态索引';
COMMENT ON INDEX idx_problem_category_rel_problem_id IS '题目类别关联题目ID索引';
COMMENT ON INDEX idx_problem_category_rel_category_id IS '题目类别关联类别ID索引';
COMMENT ON INDEX idx_contest_status IS '比赛状态索引';
COMMENT ON INDEX idx_contest_start_datetime IS '比赛开始时间索引';
COMMENT ON INDEX idx_contest_problem_contest_id IS '比赛题目比赛ID索引';
COMMENT ON INDEX idx_contest_problem_problem_id IS '比赛题目题目ID索引';
COMMENT ON INDEX idx_contest_registration_contest_id IS '比赛报名比赛ID索引';
COMMENT ON INDEX idx_contest_registration_user_id IS '比赛报名用户ID索引';