--
-- Host: localhost    Database: lms_database
-- ------------------------------------------------------


--
-- Table structure for table `assignments`
--

CREATE TABLE `assignments` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `attachmentName` varchar(255) DEFAULT NULL,
  `attachmentSize` bigint DEFAULT NULL,
  `attachmentUrl` varchar(255) DEFAULT NULL,
  `createdAt` datetime(6) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `dueDate` datetime(6) NOT NULL,
  `maxPoints` int DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `updatedAt` datetime(6) DEFAULT NULL,
  `course_id` bigint NOT NULL,
  `teacher_id` bigint NOT NULL,
  `attachmentType` varchar(255) DEFAULT NULL,
  `created_by` bigint DEFAULT NULL,
  `attachment_name` varchar(255) DEFAULT NULL,
  `attachment_size` bigint DEFAULT NULL,
  `attachment_type` varchar(255) DEFAULT NULL,
  `attachment_url` varchar(255) DEFAULT NULL,
  `created_at` datetime(6) DEFAULT NULL,
  `due_date` datetime(6) DEFAULT NULL,
  `max_points` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6p1m72jobsvmrrn4bpj4168mg` (`course_id`),
  KEY `FK67msc7a52b0l2pttoq5bhm6bk` (`teacher_id`),
  KEY `FKotqcl7qkgnihgxa6x71is49i3` (`created_by`),
  CONSTRAINT `FK67msc7a52b0l2pttoq5bhm6bk` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`),
  CONSTRAINT `FK6p1m72jobsvmrrn4bpj4168mg` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`),
  CONSTRAINT `FKotqcl7qkgnihgxa6x71is49i3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `assignments`
--

INSERT INTO `assignments` VALUES (1,NULL,NULL,NULL,'2025-11-08 07:18:47.090152','','2025-11-29 18:30:00.000000',100,'assignment01','2025-11-08 07:18:47.090152',10,28,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

--
-- Table structure for table `courses`
--

CREATE TABLE `courses` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `category` varchar(255) NOT NULL,
  `level` enum('BEGINNER','INTERMEDIATE','ADVANCED') NOT NULL DEFAULT 'BEGINNER',
  `instructor_id` bigint NOT NULL,
  `duration` varchar(255) NOT NULL,
  `students` int DEFAULT '0',
  `rating` double DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `price` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_instructor` (`instructor_id`),
  KEY `idx_category` (`category`),
  KEY `idx_level` (`level`),
  CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`instructor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `courses`
--

INSERT INTO `courses` VALUES (1,'Introduction to Programming','Learn the basics of programming with Python. Perfect for beginners with no prior experience.','Programming','BEGINNER',2,'8 weeks',151,4.5,NULL,'Free','2025-10-29 09:12:23','2025-11-11 06:44:33'),(2,'Web Development Fundamentals','Master HTML, CSS, and JavaScript to build modern, responsive websites.','Web Development','BEGINNER',2,'10 weeks',201,4.7,NULL,'Free','2025-10-29 09:12:23','2025-11-11 09:54:06'),(3,'Advanced Java Programming','Deep dive into Java programming concepts, design patterns, and enterprise development.','Programming','ADVANCED',2,'12 weeks',77,4.8,NULL,'$99','2025-10-29 09:12:23','2025-11-14 01:18:44'),(4,'Data Science Essentials','Learn data analysis, visualization, and machine learning with Python and R.','Data Science','INTERMEDIATE',2,'14 weeks',120,4.9,NULL,'$149','2025-10-29 09:12:23','2025-10-29 09:12:23'),(5,'Mobile App Development with React Native','Build cross-platform mobile applications using React Native and JavaScript.','Mobile Development','INTERMEDIATE',2,'10 weeks',90,4.6,NULL,'$79','2025-10-29 09:12:23','2025-10-29 09:12:23'),(6,'Database Management Systems','Comprehensive course on SQL, database design, and management with MySQL and PostgreSQL.','Database','BEGINNER',11,'8 weeks',181,4.4,NULL,'Free','2025-10-29 09:12:23','2025-11-11 15:31:34'),(7,'thi','mmm','Arts','INTERMEDIATE',1,'4 to 8',0,0,NULL,'1000','2025-10-30 09:59:57','2025-11-01 03:27:29'),(8,'Software Engineering','Software Engineering computer science','Programming','ADVANCED',23,'242 weeks',0,0,NULL,'Free','2025-10-30 10:26:14','2025-11-08 03:05:45'),(9,'Data Structure and Algorithms','Data Structure and Algorithms','Programming','BEGINNER',1,'242 weeks',0,0,NULL,'1000','2025-11-01 03:43:28','2025-11-01 03:43:28'),(10,'software engineer and cs','software engineer and cyber security','Programming','BEGINNER',28,'242 weeks',0,0,NULL,'Free','2025-11-07 18:12:00','2025-11-07 18:12:00'),(11,'mobile application development','developing mobile application','Programming','BEGINNER',28,'242 weeks',0,0,NULL,'Free','2025-11-08 03:04:48','2025-11-08 03:04:48'),(12,'object oriented programming','object oriented programming OOP','Programming','BEGINNER',33,'242 weeks',2,0,NULL,'Free','2025-11-08 04:14:20','2025-11-14 05:18:04'),(13,'Information technology','Information technology','Programming','BEGINNER',36,'242 weeks',0,0,NULL,'Free','2025-11-18 17:07:59','2025-11-18 17:07:59');

--
-- Table structure for table `enrollments`
--

CREATE TABLE `enrollments` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `completed` bit(1) DEFAULT NULL,
  `enrolled_at` datetime(6) DEFAULT NULL,
  `progress` int DEFAULT NULL,
  `course_id` bigint NOT NULL,
  `student_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UKi0g6mfijtuh199nj653nva6j5` (`student_id`,`course_id`),
  KEY `FKho8mcicp4196ebpltdn9wl6co` (`course_id`),
  CONSTRAINT `FK2lha5vwilci2yi3vu5akusx4a` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`),
  CONSTRAINT `FKho8mcicp4196ebpltdn9wl6co` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `enrollments`
--

INSERT INTO `enrollments` VALUES (1,_binary '\0',NULL,0,1,37),(2,_binary '\0',NULL,0,3,37),(3,_binary '\0',NULL,0,12,29),(4,_binary '\0',NULL,0,2,29),(5,_binary '\0',NULL,0,6,37),(6,_binary '\0',NULL,0,3,38),(7,_binary '\0',NULL,0,12,38);

--
-- Table structure for table `notes`
--

CREATE TABLE `notes` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `attachment_name` varchar(255) DEFAULT NULL,
  `attachment_size` bigint DEFAULT NULL,
  `attachment_type` varchar(255) DEFAULT NULL,
  `attachment_url` varchar(255) DEFAULT NULL,
  `content` text NOT NULL,
  `created_at` datetime(6) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `updated_at` datetime(6) DEFAULT NULL,
  `visibility` varchar(255) NOT NULL,
  `author_id` bigint NOT NULL,
  `course_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKeequ6tj8iu98mxv7jr0nrb98n` (`author_id`),
  KEY `FKdn1o768ox9voi78asnp5mp4mw` (`course_id`),
  CONSTRAINT `FKdn1o768ox9voi78asnp5mp4mw` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`),
  CONSTRAINT `FKeequ6tj8iu98mxv7jr0nrb98n` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `notes`
--


--
-- Table structure for table `study_materials`
--

CREATE TABLE `study_materials` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `course_id` bigint DEFAULT NULL,
  `teacher_id` bigint DEFAULT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `file_size` bigint DEFAULT NULL,
  `file_type` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `course_id` (`course_id`),
  KEY `teacher_id` (`teacher_id`),
  CONSTRAINT `study_materials_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`),
  CONSTRAINT `study_materials_ibfk_2` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `study_materials`
--


--
-- Table structure for table `submissions`
--

CREATE TABLE `submissions` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `attachment_name` varchar(255) DEFAULT NULL,
  `attachment_size` bigint DEFAULT NULL,
  `attachment_type` varchar(255) DEFAULT NULL,
  `attachment_url` varchar(255) DEFAULT NULL,
  `content` text,
  `feedback` varchar(255) DEFAULT NULL,
  `grade` int DEFAULT NULL,
  `graded` bit(1) DEFAULT NULL,
  `graded_at` datetime(6) DEFAULT NULL,
  `submitted_at` datetime(6) DEFAULT NULL,
  `assignment_id` bigint NOT NULL,
  `student_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FKrirbb44savy2g7nws0hoxs949` (`assignment_id`),
  KEY `FK3p6y8mnhpwusdgqrdl4hcl72m` (`student_id`),
  CONSTRAINT `FK3p6y8mnhpwusdgqrdl4hcl72m` FOREIGN KEY (`student_id`) REFERENCES `users` (`id`),
  CONSTRAINT `FKrirbb44savy2g7nws0hoxs949` FOREIGN KEY (`assignment_id`) REFERENCES `assignments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `submissions`
--



