database-coding-lasted.sql

-- --------------------------------------------------------
-- Host:                         gateway01.ap-southeast-1.prod.aws.tidbcloud.com
-- Server version:               5.7.28-TiDB-v7.1.3-serverless - TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
-- Server OS:                    linux
-- HeidiSQL Version:             12.3.0.6589
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for coding_web
CREATE DATABASE IF NOT EXISTS `coding_web` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `coding_web`;

-- Dumping structure for table coding_web.3rd_party_acc
CREATE TABLE IF NOT EXISTS `3rd_party_acc` (
  `rd_acc_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `provider_token` varchar(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `provider` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`rd_acc_id`) /*T![clustered_index] CLUSTERED */,
  KEY `FK_3rd_party_acc_users` (`user_id`),
  CONSTRAINT `FK_3rd_party_acc_users` FOREIGN KEY (`user_id`) REFERENCES `coding_web`.`users` (`user_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=330001;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.lang_runtime
CREATE TABLE IF NOT EXISTS `lang_runtime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `runtime` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `version` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `status` enum('ready','unready','delete') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'ready',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=60001;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.login
CREATE TABLE IF NOT EXISTS `login` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `login_token` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `ably_client` text COLLATE utf8mb4_general_ci DEFAULT NULL,
  `timestamp` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `FK_login_users` (`user_id`),
  CONSTRAINT `FK_login_users` FOREIGN KEY (`user_id`) REFERENCES `coding_web`.`users` (`user_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=2130025;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.notifications
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `target_user` int(11) DEFAULT NULL,
  `target_group` enum('all','user','admin','super_admin') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `msg` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `icon` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_by` enum('system','admin') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'system',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `FK_notifications_users` (`target_user`),
  CONSTRAINT `FK_notifications_users` FOREIGN KEY (`target_user`) REFERENCES `coding_web`.`users` (`user_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=300001;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.otp
CREATE TABLE IF NOT EXISTS `otp` (
  `otp_id` int(11) NOT NULL AUTO_INCREMENT,
  `otp_code` varchar(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `code` varchar(10) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `receiver` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `user_for` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `create_time` bigint(20) NOT NULL DEFAULT '0',
  `expire_time` bigint(20) NOT NULL DEFAULT '0',
  `verify` enum('yes','no') COLLATE utf8mb4_general_ci DEFAULT 'no',
  PRIMARY KEY (`otp_id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=390002;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.questions
CREATE TABLE IF NOT EXISTS `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `us_c_id` int(11) NOT NULL,
  `level_id` int(11) DEFAULT NULL,
  `topic_id` int(11) DEFAULT NULL,
  `no` smallint(6) NOT NULL DEFAULT '0',
  `vector_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `description` json DEFAULT NULL,
  `point` smallint(6) NOT NULL DEFAULT '0',
  `type` enum('public','pravite') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'public',
  `mem_limit` mediumint(9) NOT NULL DEFAULT '0',
  `time_out` mediumint(9) NOT NULL DEFAULT '0',
  `test_type` enum('random','code') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'random',
  `test_count` int(11) NOT NULL DEFAULT '0',
  `llm_prompt` text COLLATE utf8mb4_general_ci DEFAULT NULL,
  `count_make` int(11) NOT NULL DEFAULT '0',
  `count_success` int(11) NOT NULL DEFAULT '0',
  `update_at` datetime NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `FK_questions_question_topic` (`topic_id`),
  KEY `FK_questions_question_level` (`level_id`),
  KEY `FK_questions_users` (`us_c_id`),
  CONSTRAINT `FK_questions_question_topic` FOREIGN KEY (`topic_id`) REFERENCES `coding_web`.`question_topic` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `FK_questions_question_level` FOREIGN KEY (`level_id`) REFERENCES `coding_web`.`question_level` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `FK_questions_users` FOREIGN KEY (`us_c_id`) REFERENCES `coding_web`.`users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=330001;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.question_level
CREATE TABLE IF NOT EXISTS `question_level` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `l_name` char(50) COLLATE utf8mb4_general_ci NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_time` datetime NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=60001;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.question_like
CREATE TABLE IF NOT EXISTS `question_like` (
  `question_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  KEY `FK_like_questions` (`question_id`),
  KEY `FK_like_users` (`user_id`),
  CONSTRAINT `FK_like_questions` FOREIGN KEY (`question_id`) REFERENCES `coding_web`.`questions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `FK_like_users` FOREIGN KEY (`user_id`) REFERENCES `coding_web`.`users` (`user_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.question_topic
CREATE TABLE IF NOT EXISTS `question_topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `t_logo` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_time` datetime NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=180001;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.summit_history
CREATE TABLE IF NOT EXISTS `summit_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `question_id` int(11) DEFAULT NULL,
  `lang_id` int(11) NOT NULL DEFAULT '0',
  `description` json NOT NULL,
  `status` enum('wrong','success','wait','error','out_memory','timeout') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'wait',
  `receive_point` int(11) NOT NULL DEFAULT '0',
  `ram_usage` int(11) DEFAULT NULL,
  `time_usage` int(11) DEFAULT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `FK_summit_history_users` (`user_id`),
  KEY `FK_summit_history_questions` (`question_id`),
  KEY `FK_summit_history_lang_runtime` (`lang_id`),
  CONSTRAINT `FK_summit_history_users` FOREIGN KEY (`user_id`) REFERENCES `coding_web`.`users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_summit_history_questions` FOREIGN KEY (`question_id`) REFERENCES `coding_web`.`questions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_summit_history_lang_runtime` FOREIGN KEY (`lang_id`) REFERENCES `coding_web`.`lang_runtime` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=660001;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.testcases
CREATE TABLE IF NOT EXISTS `testcases` (
  `test_c_id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) NOT NULL DEFAULT '0',
  `input` mediumtext COLLATE utf8mb4_general_ci NOT NULL,
  `answer` mediumtext COLLATE utf8mb4_general_ci NOT NULL,
  `t_type` enum('random','code') COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`test_c_id`) /*T![clustered_index] CLUSTERED */,
  KEY `FK__questions` (`question_id`),
  CONSTRAINT `FK__questions` FOREIGN KEY (`question_id`) REFERENCES `coding_web`.`questions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=240001;

-- Data exporting was unselected.

-- Dumping structure for table coding_web.users
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `type` tinyint(4) NOT NULL DEFAULT '1',
  `profile_pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `profile_show` enum('true','false') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'true',
  PRIMARY KEY (`user_id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=480010;

-- Data exporting was unselected.

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;