papercheck.sql

-- PaperCheck PostgreSQL schema
-- 用法: psql -U <user> -d <db> -f papercheck.sql
-- 说明: utf8mb4_unicode_ci 等价于 PG 的默认 ICU/C 排序;字符集由数据库级 UTF-8 保证。
--       自增主键使用 GENERATED BY DEFAULT AS IDENTITY,允许显式插入历史 ID。

DROP TABLE IF EXISTS check_histories CASCADE;
DROP TABLE IF EXISTS self_check_histories CASCADE;
DROP TABLE IF EXISTS document_sentences CASCADE;
DROP TABLE IF EXISTS fingerprints CASCADE;
DROP TABLE IF EXISTS semantic_vectors CASCADE;
DROP TABLE IF EXISTS documents CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS system_config CASCADE;

-- ----------------------------
-- Table structure for documents
-- ----------------------------
CREATE TABLE documents (
  id varchar(36) NOT NULL,
  filename varchar(255) NOT NULL,
  file_path varchar(512) NOT NULL,
  char_count integer NOT NULL DEFAULT 0,
  sentence_count integer NOT NULL DEFAULT 0,
  category varchar(64) NULL DEFAULT '未分类',
  created_at timestamp NOT NULL DEFAULT now(),
  PRIMARY KEY (id)
);
CREATE INDEX ix_documents_created_at ON documents (created_at);

-- ----------------------------
-- Table structure for document_sentences
-- ----------------------------
CREATE TABLE document_sentences (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  doc_id varchar(36) NOT NULL,
  sentence_idx integer NOT NULL,
  sentence_text text NOT NULL,
  start_offset integer NOT NULL,
  end_offset integer NOT NULL,
  CONSTRAINT uq_document_sentences_doc_sentence UNIQUE (doc_id, sentence_idx),
  CONSTRAINT document_sentences_fk FOREIGN KEY (doc_id) REFERENCES documents (id) ON DELETE CASCADE
);

-- ----------------------------
-- Table structure for fingerprints
-- ----------------------------
CREATE TABLE fingerprints (
  doc_id varchar(36) NOT NULL,
  sentence_idx integer NOT NULL,
  ngram_text varchar(96) NOT NULL,
  PRIMARY KEY (ngram_text, doc_id, sentence_idx),
  CONSTRAINT fingerprints_fk FOREIGN KEY (doc_id) REFERENCES documents (id) ON DELETE CASCADE
);
CREATE INDEX ix_fingerprints_doc_id ON fingerprints (doc_id);

-- ----------------------------
-- Table structure for semantic_vectors
-- ----------------------------
CREATE TABLE semantic_vectors (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  doc_id varchar(36) NOT NULL,
  sentence_idx integer NOT NULL,
  vector bytea NOT NULL,
  dim integer NOT NULL,
  dtype varchar(16) NOT NULL DEFAULT 'float32',
  model_name varchar(255) NOT NULL,
  CONSTRAINT uq_semantic_vectors_doc_sentence_model UNIQUE (doc_id, sentence_idx, model_name),
  CONSTRAINT semantic_vectors_fk FOREIGN KEY (doc_id) REFERENCES documents (id) ON DELETE CASCADE
);

-- ----------------------------
-- Table structure for users
-- ----------------------------
CREATE TABLE users (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username varchar(64) NOT NULL,
  password_hash varchar(128) NOT NULL,
  role varchar(16) NOT NULL DEFAULT 'user',
  daily_quota integer NOT NULL DEFAULT 5,
  quota_limit integer NOT NULL DEFAULT 5,
  quota_date date NULL DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT now(),
  CONSTRAINT ux_users_username UNIQUE (username)
);

-- 管理员账号密码对应 ADMIN_DEFAULT_PASSWORD(=zhu123456 的 bcrypt 哈希)
INSERT INTO users (id, username, password_hash, role, daily_quota, quota_limit, quota_date, created_at) VALUES
  (1, 'admin', '$2b$12$aSBMB22SUsSDe2kfIM7oNeI86EpP02QA.A.J7ijfp/2YrgW.akUsi', 'admin', -1, -1, NULL, '2026-05-05 21:47:07'),
  (3, 'student1', '$2b$12$yR4d6CWYuazMDpHR69HiI.jpiPQIjUYAoI8y1qi8Qlq1oUSIItGqS', 'user', 3, 3, '2026-05-06', '2026-05-05 22:15:00'),
  (4, 'student2', '$2b$12$yR4d6CWYuazMDpHR69HiI.jpiPQIjUYAoI8y1qi8Qlq1oUSIItGqS', 'user', 3, 3, NULL, '2026-05-05 22:16:50');

-- ----------------------------
-- Table structure for check_histories
-- ----------------------------
CREATE TABLE check_histories (
  task_id varchar(36) NOT NULL,
  source_name varchar(255) NOT NULL,
  target_doc_id varchar(36) NULL DEFAULT NULL,
  rate float NOT NULL,
  total_chars integer NOT NULL,
  plagiarized_chars integer NOT NULL,
  literal_count integer NOT NULL DEFAULT 0,
  semantic_count integer NOT NULL DEFAULT 0,
  synonym_count integer NOT NULL DEFAULT 0,
  restructured_count integer NOT NULL DEFAULT 0,
  paraphrase_count integer NOT NULL DEFAULT 0,
  paragraph_count integer NOT NULL DEFAULT 0,
  report_json json NOT NULL,
  user_id integer NULL DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT now(),
  PRIMARY KEY (task_id),
  CONSTRAINT check_histories_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
);
CREATE INDEX ix_check_histories_created_at ON check_histories (created_at);
CREATE INDEX ix_check_histories_user_id ON check_histories (user_id);

-- ----------------------------
-- Table structure for self_check_histories
-- ----------------------------
CREATE TABLE self_check_histories (
  id varchar(36) NOT NULL,
  total integer NOT NULL DEFAULT 0,
  pairs_json json NOT NULL,
  created_at timestamp NOT NULL DEFAULT now(),
  PRIMARY KEY (id)
);
CREATE INDEX ix_self_check_histories_created_at ON self_check_histories (created_at);

-- ----------------------------
-- Table structure for system_config
-- ----------------------------
CREATE TABLE system_config (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  key varchar(64) NOT NULL,
  value text NULL,
  updated_at timestamp NOT NULL DEFAULT now(),
  CONSTRAINT ux_system_config_key UNIQUE (key)
);

INSERT INTO system_config (key, value) VALUES ('registration_enabled', 'true');

-- 维护 updated_at 的触发器(等价 MySQL 的 ON UPDATE CURRENT_TIMESTAMP)
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_system_config_updated_at ON system_config;
CREATE TRIGGER trg_system_config_updated_at
  BEFORE UPDATE ON system_config
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();