-- ------------------------------------------- --
--                                             --
--              Database: `cucoj`              --
--                                             --
-- ------------------------------------------- --
-- 
-- Author: XuanYu
-- CreateTime: 2021/02/01
-- Source: https://github.com/hzxie/voj.git master /voj/voj.sql
--
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 22, 2017 at 04:34 PM
-- Server version: 10.1.10-MariaDB
-- PHP Version: 7.0.3

--
-- Init
--

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Table structure for table `voj_students`
--
CREATE TABLE `voj_students` (
  `stu_id` int(4) NOT NULL,
  `student_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `student_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `class_id` int(4)NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `voj_students`
  ADD PRIMARY KEY (`stu_id`);

ALTER TABLE `voj_students`
  MODIFY `stu_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1000;

--
-- Table structure for table `cucoj_classes`
--
CREATE TABLE `voj_classes` (
  `cid` int(4) NOT NULL,
  `classname` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lessonname` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lessonid` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lessonnum` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `teacher` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `studentcount` int(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `language_id` int(4) NOT NULL,
  `term` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tea_user_id` bigint(20)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `voj_classes`
  ADD PRIMARY KEY (`cid`);

ALTER TABLE `voj_classes`
  MODIFY `cid` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1000;

--
-- Table structure for table `voj_options`
--

UPDATE voj_options SET option_value = 'CUC Online Judge' WHERE option_name = 'websiteName';
UPDATE voj_options SET option_value = 'CUC Online Judge is an online judge system based on Spring MVC Framework. Rewrite the code based on Verwandlung Online Judge' WHERE option_name = 'description';
UPDATE voj_options SET option_value = '<a href="https://gitee.com/xuanyusan/cuc-online-judge-public" target="_blank">xuanyusan</a>' WHERE option_name = 'copyright';
UPDATE voj_options SET option_value = '' WHERE option_name = 'icpNumber';
UPDATE voj_options SET option_value = '' WHERE option_name = 'policeIcpNumber';

--
-- Table structure for table `voj_problems`
--
-- ALTER TABLE voj_problems ADD author INT(4) DEFAULT 1000;

--
-- Table structure for table `voj_user_groups`
--
INSERT INTO `voj_user_groups` (`user_group_id`, `user_group_slug`, `user_group_name`) VALUES
(3, 'students', 'Students'),
(6, 'teachers', 'Teachers');

--
-- Table structure for table `voj_users`
--

ALTER TABLE `voj_users` MODIFY `username` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL;


--
-- Table structure for table `voj_contests`
--

ALTER TABLE `voj_contests` ADD `contest_is_public` tinyint(1) NULL DEFAULT 1;
ALTER TABLE `voj_contests` ADD `contest_edit_is_public` tinyint(1) NULL DEFAULT 0;
ALTER TABLE `voj_contests` ADD `contest_author` bigint(20) NULL DEFAULT 1000;
ALTER TABLE `voj_contests` ADD `contest_classes` text COLLATE utf8mb4_unicode_ci NULL;
UPDATE `voj_contests` SET `contest_classes` = '[]';

--
-- Table structure for table `voj_class_model;`
--
CREATE TABLE `voj_class_model` (
  `class_model_id` bigint(20) NOT NULL,
  `class_model_name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, -- 登分表
  `class_model_term` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, -- 学期
  `class_model_lessonId` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, -- 课程号
  `class_model_lessonNum` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, -- 课序号
  `class_model_lessonName` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `class_model_teacherName` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `class_model_studentsCode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `class_model_studentsName` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `class_model_studentsclass` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `voj_class_model`
  ADD PRIMARY KEY (`class_model_id`);

ALTER TABLE `voj_class_model`
  MODIFY `class_model_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1000;

INSERT INTO `voj_class_model` (`class_model_id`, `class_model_name`, `class_model_term`, `class_model_lessonId`, `class_model_lessonNum`, `class_model_lessonName`, `class_model_teacherName`, `class_model_studentsCode`, `class_model_studentsName`, `class_model_studentsclass`) VALUES
(1, '登分表', 'A3', 'B4', 'D4', 'F4', 'D5', '[A8-A$]', '[B8-B$]', '[C8-C$]');

CREATE TABLE `voj_user_students` (
  `student_uid` bigint(20) NOT NULL,
  `student_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `voj_user_students`
  ADD CONSTRAINT `voj_user_students_ibfk_1` FOREIGN KEY (`student_uid`) REFERENCES `voj_users` (`uid`);

-- 实例：
delimiter $$
create procedure insertUser(a int)
begin
  declare i bigint default 201810413002;
  declare userid bigint default 1000;
  while i<a+201810413002 DO -- 循环开始
    INSERT `voj_users` (`username`, `password`, `email`, `user_group_id`, `prefer_language_id`) VALUES (CONCAT('cuc', i), '785ee107c11dfe36de668b1ae7baacbb', CONCAT(i, '@cuc.edu.cn'), 3, 1);
    set userid=(SELECT `uid` from voj_users where `username`=CONCAT('cuc', i));
    INSERT `voj_user_students` (`student_uid`, `student_code`) VALUES (userid, CONCAT('', i));
    set i=i+1;
  end while; -- 循环结束
end $$
delimiter ;
-- 执行存储过程
call insertUser(3);
-- 删除存储过程
drop procedure if exists insertUser;

--
-- Table structure for table `voj_class_contests;`
--

CREATE TABLE `voj_class_contests` (
  `class_id` int(4) NOT NULL,
  `contest_id` int(4)NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;