CREATE TABLE `contests` (
  `cid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `created_by` varchar(100) DEFAULT NULL,
  `createdon` datetime NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `last_update` datetime,
  `cstatus` tinyint(1) DEFAULT NULL,
  `p_point` int(3) DEFAULT NULL,
  `v_point` int(3) DEFAULT NULL,
  `lang` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`cid`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ContestAdmin` (
  `cid` int(10) unsigned NOT NULL,
  `user_name` varchar(190) NOT NULL,
  PRIMARY KEY (`cid`,`user_name`),
  CONSTRAINT `contest_admins_contest_fk` FOREIGN KEY (`cid`) REFERENCES `contests` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `IndexPages` (
  `idbp` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `index_name` varchar(200) CHARACTER SET utf8mb3 NOT NULL,
  `index_page` varchar(100) CHARACTER SET utf8mb3 NOT NULL,
  `icode` int(2) NOT NULL,
  PRIMARY KEY (`idbp`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `UnlistedUser` (
  `cid` int(10) unsigned NOT NULL,
  `user_name` varchar(190) NOT NULL,
  PRIMARY KEY (`cid`,`user_name`),
  CONSTRAINT `unlisted_user_fk` FOREIGN KEY (`cid`) REFERENCES `contests` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ContestIndices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cid` int(10) unsigned NOT NULL,
  `idbp` int(10) unsigned NOT NULL,
  `validator` varchar(190) DEFAULT NULL,
  `proofreader` varchar(190) DEFAULT NULL,
  `validate_time` datetime DEFAULT NULL,
  `proofread_time` datetime DEFAULT NULL,
  `v_revision_id` int(5) DEFAULT NULL,
  `p_revision_id` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contest_index_index_page_fk` (`idbp`),
  KEY `contest_index_contest_fk` (`cid`),
  CONSTRAINT `contest_index_contest_fk` FOREIGN KEY (`cid`) REFERENCES `contests` (`cid`),
  CONSTRAINT `contest_index_index_page_fk` FOREIGN KEY (`idbp`) REFERENCES `IndexPages` (`idbp`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;