-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Jun 14, 2025 at 03:38 PM
-- Server version: 10.11.10-MariaDB
-- PHP Version: 7.2.34

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!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 */;

--
-- Database: `u320202348_ob`
--

-- --------------------------------------------------------

--
-- Table structure for table `cohorts`
--

CREATE TABLE `cohorts` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` enum('active','completed','upcoming') DEFAULT 'upcoming',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

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

CREATE TABLE `courses` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `trainer_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `thumbnail` varchar(255) DEFAULT NULL,
  `level` enum('beginner','intermediate','advanced') DEFAULT 'beginner',
  `category` varchar(50) DEFAULT NULL,
  `language` varchar(20) NOT NULL DEFAULT 'english',
  `objectives` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`objectives`)),
  `prerequisites` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`prerequisites`)),
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tags`)),
  `duration` int(11) DEFAULT 18
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `courses` (`id`, `title`, `description`, `trainer_id`, `created_at`, `updated_at`, `thumbnail`, `level`, `category`, `language`, `objectives`, `prerequisites`, `tags`, `duration`) VALUES
(26, 'Ultimate AWS Certified Solutions Architect Associate 2026', 'Full Practice Exam | Learn Cloud Computing | Pass the AWS Certified Solutions Architect Associate Certification SAA-C03!', 6, '2025-04-18 16:33:34', '2025-04-18 16:56:06', '68027ede4b7f81.26403050.png', 'intermediate', 'technology', 'english', '[\"Business\"]', '[\"Internet and computer\"]', '[\"["Technology"]\"]', 18),
(27, 'Financial Accounting: A Comprehensive Guide with E-book', 'Unlock Financial Accounting Mastery: Comprehensive Course for Students, Professionals, and Business Owners\r\nRunning a manufacturing, wholesale, or retail business requires clear oversight, inventory precision, financial control, and streamlined operations across multiple departments. Prodesk24 is an all-in-one business management software built to simplify operations for businesses like yours. Our platform offers smart tools to handle sales, inventory, human resources, financials, and multi-branch operations, all in one place.', 6, '2025-04-18 17:26:38', '2025-04-18 17:26:38', '68028b4e6d6640.32989242.png', 'beginner', 'finance', 'english', '[\"Grasp the basics of financial accounting and understand its objectives and significance in business.\",\"Record business transactions using debits and credits, including period-end adjusting entries.\",\"Handle transactions related to the purchase and sale of inventory, including applying inventory cost flow assumptions such as FIFO, LIFO, and weighted average m\",\"Conduct bank reconciliations and apply best practices for cash internal controls.\"]', '[\"Familiarity with basic mathematical operations: As the course involves performing calculations for different financial processes, basic mathematical skills are necessary.\",\"Willingness to learn and engage with complex concepts: Financial accounting can be detailed and requires careful study. Being prepared to engage with the content actively will help ensure successful learning outcomes.\"]', '[\"["Business"\",\""Finance"\",\""Accounting"]\"]', 18),
(28, '3D Designs and Mockup Creation Course', 'This course teaches how to use Adobe Dimension to create stunning 3D designs and Mockups', 6, '2025-04-20 00:56:05', '2025-04-20 00:56:05', '68044625438092.08231213.png', 'beginner', 'design', 'english', '[\"Grasp the basics of financial accounting and understand its objectives and significance in business.\",\"Record business transactions using debits and credits, including period-end adjusting entries.\"]', '[\"Familiarity with basic mathematical operations: As the course involves performing calculations for different financial processes, basic mathematical skills are necessary.\"]', '[\"["Technology"]\"]', 18),
(29, 'Inclusive Business Growth', 'The Inclusive Business Growth course highlights the fundamentals of entrepreneurship. The course covers designing a Blueprint for an inclusive business, Inclusivity and Diversity, Business Structures, Product/Service Marketing, Leadership Skills, Financial Management and Scalability.', 12, '2025-06-10 14:40:19', '2025-06-10 14:47:34', '684845863b42c0.89548694.jpg', 'beginner', 'business', 'english', '[\"In this course, learners will be guided through launching, managing and scaling an inclusive and sustainable business\"]', '[]', '[]', 18);

-- --------------------------------------------------------

--
-- Table structure for table `course_materials`
--

CREATE TABLE `course_materials` (
  `id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` enum('text','video','audio','pdf') NOT NULL,
  `content_url` varchar(255) NOT NULL,
  `order_index` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `course_materials`
--

INSERT INTO `course_materials` (`id`, `course_id`, `title`, `description`, `type`, `content_url`, `order_index`, `created_at`, `updated_at`) VALUES
(8, 27, 'What is Accounting', 'Grasp the basics of financial accounting and understand its objectives and significance in business.', 'video', '68028d76cb1868.77666824.mp4', 0, '2025-04-18 17:35:50', '2025-04-18 17:35:50'),
(9, 27, 'Importance of Accounting to your business', 'Record business transactions using debits and credits, including period-end adjusting entries.', 'pdf', '68028dc0c9f764.61782389.pdf', 1, '2025-04-18 17:37:04', '2025-04-18 17:37:04'),
(10, 26, 'Website Development', 'hello', 'audio', '6803b6483cd7b8.07678807.mp4', 0, '2025-04-19 14:42:16', '2025-04-19 14:42:16'),
(11, 26, 'Website Development', 'Heloo Ben', 'video', '6803dd88785b20.43800781.mp4', 1, '2025-04-19 17:29:44', '2025-04-19 17:29:44'),
(12, 27, 'Website Development', 'gh', 'pdf', '6803e1cf897f53.91583310.pdf', 3, '2025-04-19 17:47:59', '2025-04-19 17:47:59'),
(13, 27, 'Website Development5', 'yuy', 'video', '6803e1ee7cb105.26158024.mp4', 4, '2025-04-19 17:48:30', '2025-04-19 17:48:30'),
(14, 28, '3D Website Develop', 'Aben', 'video', '6804468e827039.90035747.mp4', 0, '2025-04-20 00:57:50', '2025-04-20 00:57:50'),
(15, 28, 'Carrots for Sale', 'For the members', 'video', '68044b822268b0.61860880.mp4', 1, '2025-04-20 01:18:58', '2025-04-20 01:18:58'),
(16, 28, 'Website Development', 'gesa', 'pdf', '68044b9e49cf95.09802935.pdf', 2, '2025-04-20 01:19:26', '2025-04-20 01:19:26');

-- --------------------------------------------------------

--
-- Table structure for table `course_reviews`
--

CREATE TABLE `course_reviews` (
  `id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `rating` int(11) NOT NULL CHECK (`rating` >= 1 and `rating` <= 5),
  `review` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `course_reviews`
--

INSERT INTO `course_reviews` (`id`, `course_id`, `user_id`, `rating`, `review`, `created_at`, `updated_at`) VALUES
(1, 28, 8, 1, 'Loved it', '2025-06-03 02:26:15', '2025-06-03 02:26:15'),
(2, 27, 8, 1, '', '2025-06-10 13:15:29', '2025-06-10 13:15:29');

-- --------------------------------------------------------

--
-- Table structure for table `forum_categories`
--

CREATE TABLE `forum_categories` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `forum_categories`
--

INSERT INTO `forum_categories` (`id`, `name`, `description`, `created_at`) VALUES
(1, 'General Discussion', 'General topics and discussions', '2025-03-28 23:12:10'),
(2, 'Course Questions', 'Questions about course materials and content', '2025-03-28 23:12:10'),
(3, 'Technical Support', 'Technical issues and support requests', '2025-03-28 23:12:10'),
(4, 'Announcements', 'Important announcements and updates', '2025-03-28 23:12:10');

-- --------------------------------------------------------

--
-- Table structure for table `forum_replies`
--

CREATE TABLE `forum_replies` (
  `id` int(11) NOT NULL,
  `topic_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `forum_topics`
--

CREATE TABLE `forum_topics` (
  `id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `login_history`
--

CREATE TABLE `login_history` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `login_time` timestamp NULL DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `material_discussions`
--

CREATE TABLE `material_discussions` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `material_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `material_discussions`
--

INSERT INTO `material_discussions` (`id`, `user_id`, `material_id`, `content`, `parent_id`, `created_at`, `updated_at`) VALUES
(4, 8, 14, 'Hello', NULL, '2025-04-22 03:49:07', NULL),
(5, 8, 14, 'I am', NULL, '2025-04-22 03:49:24', NULL),
(6, 8, 14, '@message-3 banku', NULL, '2025-04-22 03:49:39', NULL),
(8, 8, 16, 'hello', NULL, '2025-04-22 04:02:24', NULL),
(9, 8, 14, 'Can you talk?', NULL, '2025-04-26 03:52:03', NULL),
(10, 11, 14, '@message-9 yes, what do you want to discuss ?', NULL, '2025-04-26 03:54:52', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `message_reads`
--

CREATE TABLE `message_reads` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `message_id` int(11) NOT NULL,
  `read_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `message_reads`
--

INSERT INTO `message_reads` (`id`, `user_id`, `message_id`, `read_at`) VALUES
(1, 11, 4, '2025-04-26 03:53:50'),
(2, 11, 5, '2025-04-26 03:53:50'),
(3, 11, 6, '2025-04-26 03:53:50'),
(4, 11, 9, '2025-04-26 03:53:50'),
(8, 11, 8, '2025-04-26 03:55:43'),
(11, 8, 10, '2025-04-26 11:13:15'),
(18, 13, 4, '2025-06-10 15:29:54'),
(19, 13, 5, '2025-06-10 15:29:54'),
(20, 13, 6, '2025-06-10 15:29:54'),
(21, 13, 9, '2025-06-10 15:29:54'),
(22, 13, 10, '2025-06-10 15:29:54');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('learner','trainer','admin') NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `email_verified` tinyint(1) DEFAULT 0,
  `verification_token` varchar(100) DEFAULT NULL,
  `verification_expires` timestamp NULL DEFAULT NULL,
  `two_factor_enabled` tinyint(1) DEFAULT 0,
  `two_factor_secret` varchar(32) DEFAULT NULL,
  `login_attempts` int(11) DEFAULT 0,
  `last_login` timestamp NULL DEFAULT NULL,
  `last_login_ip` varchar(45) DEFAULT NULL,
  `profile_picture` varchar(255) DEFAULT NULL,
  `verification_status` enum('pending','verified','rejected') DEFAULT 'pending',
  `verification_documents` varchar(255) DEFAULT NULL,
  `terms_accepted` tinyint(1) DEFAULT 0,
  `terms_version` varchar(10) DEFAULT NULL,
  `terms_accepted_at` timestamp NULL DEFAULT NULL,
  `reset_token` varchar(100) DEFAULT NULL,
  `reset_token_expires` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `email`, `password`, `role`, `full_name`, `created_at`, `updated_at`, `email_verified`, `verification_token`, `verification_expires`, `two_factor_enabled`, `two_factor_secret`, `login_attempts`, `last_login`, `last_login_ip`, `profile_picture`, `verification_status`, `verification_documents`, `terms_accepted`, `terms_version`, `terms_accepted_at`, `reset_token`, `reset_token_expires`) VALUES
(1, 'admin@obaasima.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'System Administrator', '2025-03-28 23:12:10', '2025-03-28 23:12:10', 0, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 'pending', NULL, 0, NULL, NULL, NULL, NULL),
(3, 'winnieworla@gmail.com', '$2y$10$gE49h9HKqW/J1gYixY7rOeBnVsSRXuArEPEk5PeP0u4YnyJ0vhdua', 'trainer', 'RICHARD Mensah', '2025-03-28 23:45:03', '2025-03-28 23:45:03', 0, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 'pending', NULL, 0, NULL, NULL, NULL, NULL),
(6, 'ricmen874@gmail.com', '$2y$10$BznCiYZdlLMQHA62Udg.EuuBA/IS7OHFbRcTmwloyiR2YLbur4lbC', 'trainer', 'Rchard Mensah', '2025-03-31 14:08:43', '2025-06-10 12:56:32', 1, NULL, NULL, 0, NULL, 0, '2025-06-10 12:56:32', '::1', NULL, 'pending', NULL, 1, '1.0', '2025-03-31 16:08:43', NULL, NULL),
(7, 'rich6hill@gmail.com', '$2y$10$LyWCUfOGDzh4jUHyet7jcef/dbnNPtGV9VGTVHs17KOo2b2WeaDpm', 'learner', 'Abena Mensah', '2025-03-31 14:30:00', '2025-03-31 14:30:26', 1, NULL, NULL, 0, NULL, 0, '2025-03-31 14:30:26', '::1', NULL, 'pending', NULL, 1, '1.0', '2025-03-31 16:30:00', NULL, NULL),
(8, 'ricmen875@gmail.com', '$2y$10$tG3qg2EWO/7ibYTii71HTu6j22Z/fpLFVzUPZBqJrSvoGr4ik9LDO', 'learner', 'Rchard Mensah2', '2025-03-31 14:38:07', '2025-06-10 12:56:58', 1, NULL, NULL, 0, NULL, 0, '2025-06-10 12:56:58', '::1', NULL, 'pending', NULL, 1, '1.0', '2025-03-31 16:38:07', NULL, NULL),
(9, 'ricmen87625@gmail.com', '$2y$10$GxTY5uRLd9VjKQM3odh0puhbfT8T6TszSDOqB55UbTyi5QksfiIKK', 'trainer', 'Rchard Mensah', '2025-03-31 14:40:00', '2025-03-31 15:05:40', 1, NULL, NULL, 0, NULL, 0, '2025-03-31 14:40:10', '::1', NULL, 'pending', NULL, 1, '1.0', '2025-03-31 16:40:00', '40e43eb8ab3e0b317a15d9c4ad700a03de19b440795cda0ea97e21d1ab41e83c', '2025-03-31 18:05:40'),
(10, 'rich6hill2@gmail.com', '$2y$10$ZmYPxHPu4yc2ZhXAMqgKuuaTSKZxxwNao51Snlz/Kkiknjh.C3plu', 'trainer', 'Abeku Mensah', '2025-04-25 04:12:23', '2025-04-25 04:12:37', 1, NULL, NULL, 0, NULL, 0, '2025-04-25 04:12:37', NULL, NULL, 'pending', NULL, 1, '1.0', '2025-04-25 04:12:23', NULL, NULL),
(11, 'ricmen87@gmail.com', '$2y$10$ZjqSaRg0zsJWhVqzJjgNj.ReqetJsdDTtYHMRXFVKlRWvwwpYQbca', 'learner', 'Vivian Jill', '2025-04-26 03:53:05', '2025-04-26 03:53:12', 1, NULL, NULL, 0, NULL, 0, '2025-04-26 03:53:12', NULL, NULL, 'pending', NULL, 1, '1.0', '2025-04-26 03:53:05', NULL, NULL),
(12, 'pearl@runbalconsulting.com', '$2y$10$qPcAk/54KKMCo.wNaLeap./TXSsPa3U7QIN080dyOEG8KBueKLqpC', 'trainer', 'Pearl', '2025-06-10 14:26:26', '2025-06-10 14:26:44', 1, NULL, NULL, 0, NULL, 0, '2025-06-10 14:26:44', NULL, NULL, 'pending', NULL, 1, '1.0', '2025-06-10 14:26:26', NULL, NULL),
(13, 'ricmen879@gmail.com', '$2y$10$1/9.62clc5vuvAUVfzuuneUrm5QbVZSqmyUsvAWaGqwdBEB4csWZK', 'learner', 'Ashia Massamba', '2025-06-10 15:26:38', '2025-06-11 01:28:12', 1, NULL, NULL, 0, NULL, 0, '2025-06-11 01:28:12', NULL, NULL, 'pending', NULL, 1, '1.0', '2025-06-10 15:26:38', NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `user_cohorts`
--

CREATE TABLE `user_cohorts` (
  `user_id` int(11) NOT NULL,
  `cohort_id` int(11) NOT NULL,
  `joined_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_course_progress`
--

CREATE TABLE `user_course_progress` (
  `user_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `progress` int(11) DEFAULT 0,
  `last_updated` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user_course_progress`
--

INSERT INTO `user_course_progress` (`user_id`, `course_id`, `progress`, `last_updated`) VALUES
(8, 19, 100, '2025-04-18 03:28:31'),
(8, 22, 100, '2025-04-18 03:39:39'),
(8, 26, 100, '2025-04-20 01:43:24'),
(8, 27, 75, '2025-04-22 04:08:45'),
(8, 28, 100, '2025-04-20 01:42:45'),
(11, 28, 33, '2025-04-26 03:57:29'),
(13, 28, 67, '2025-06-10 15:30:21');

-- --------------------------------------------------------

--
-- Table structure for table `user_enrollments`
--

CREATE TABLE `user_enrollments` (
  `user_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `status` enum('active','completed','dropped') NOT NULL DEFAULT 'active',
  `enrolled_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `completed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user_enrollments`
--

INSERT INTO `user_enrollments` (`user_id`, `course_id`, `status`, `enrolled_at`, `completed_at`) VALUES
(8, 26, 'active', '2025-04-18 17:18:38', NULL),
(8, 27, 'completed', '2025-04-18 17:40:47', '2025-06-10 15:24:44'),
(8, 28, 'completed', '2025-04-20 01:17:15', '2025-06-10 13:15:52'),
(11, 28, 'active', '2025-04-26 03:53:29', NULL),
(13, 28, 'completed', '2025-06-10 15:29:52', '2025-06-10 15:30:26'),
(13, 29, 'active', '2025-06-10 16:59:04', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `user_notes`
--

CREATE TABLE `user_notes` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `material_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user_notes`
--

INSERT INTO `user_notes` (`id`, `user_id`, `material_id`, `content`, `created_at`, `updated_at`) VALUES
(10, 8, 14, 'This is to keep in mind the best', '2025-04-22 03:44:26', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `user_progress`
--

CREATE TABLE `user_progress` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `material_id` int(11) NOT NULL,
  `status` enum('not_started','in_progress','completed') DEFAULT 'not_started',
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user_progress`
--

INSERT INTO `user_progress` (`id`, `user_id`, `material_id`, `status`, `completed_at`, `created_at`, `updated_at`) VALUES
(8, 8, 8, 'completed', '2025-04-18 18:27:42', '2025-04-18 18:27:42', '2025-04-18 18:27:42'),
(9, 8, 9, 'completed', '2025-04-19 13:45:37', '2025-04-19 13:45:37', '2025-04-19 13:45:37'),
(10, 8, 10, 'completed', '2025-04-20 00:56:35', '2025-04-20 00:56:35', '2025-04-20 00:56:35'),
(11, 8, 14, 'completed', '2025-04-20 01:20:49', '2025-04-20 01:20:49', '2025-04-20 01:20:49'),
(12, 8, 15, 'completed', '2025-04-20 01:42:40', '2025-04-20 01:42:40', '2025-04-20 01:42:40'),
(13, 8, 16, 'completed', '2025-04-20 01:42:45', '2025-04-20 01:42:45', '2025-04-20 01:42:45'),
(14, 8, 11, 'completed', '2025-04-20 01:43:24', '2025-04-20 01:43:24', '2025-04-20 01:43:24'),
(15, 8, 12, 'completed', '2025-04-22 04:08:45', '2025-04-22 04:08:45', '2025-04-22 04:08:45'),
(16, 8, 12, 'not_started', NULL, '2025-04-22 04:16:14', '2025-04-22 04:16:14'),
(17, 11, 14, 'completed', '2025-04-26 03:57:29', '2025-04-26 03:57:29', '2025-04-26 03:57:29'),
(18, 13, 14, 'completed', '2025-06-10 15:30:02', '2025-06-10 15:30:02', '2025-06-10 15:30:02'),
(19, 13, 15, 'completed', '2025-06-10 15:30:21', '2025-06-10 15:30:21', '2025-06-10 15:30:21');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cohorts`
--
ALTER TABLE `cohorts`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `trainer_id` (`trainer_id`);

--
-- Indexes for table `course_materials`
--
ALTER TABLE `course_materials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `course_id` (`course_id`);

--
-- Indexes for table `course_reviews`
--
ALTER TABLE `course_reviews`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user_course_review` (`user_id`,`course_id`),
  ADD KEY `course_id` (`course_id`);

--
-- Indexes for table `forum_categories`
--
ALTER TABLE `forum_categories`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `forum_replies`
--
ALTER TABLE `forum_replies`
  ADD PRIMARY KEY (`id`),
  ADD KEY `topic_id` (`topic_id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `forum_topics`
--
ALTER TABLE `forum_topics`
  ADD PRIMARY KEY (`id`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `login_history`
--
ALTER TABLE `login_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `material_discussions`
--
ALTER TABLE `material_discussions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `material_id` (`material_id`),
  ADD KEY `parent_id` (`parent_id`);

--
-- Indexes for table `message_reads`
--
ALTER TABLE `message_reads`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_message_unique` (`user_id`,`message_id`),
  ADD KEY `message_id` (`message_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `user_cohorts`
--
ALTER TABLE `user_cohorts`
  ADD PRIMARY KEY (`user_id`,`cohort_id`),
  ADD KEY `cohort_id` (`cohort_id`);

--
-- Indexes for table `user_course_progress`
--
ALTER TABLE `user_course_progress`
  ADD PRIMARY KEY (`user_id`,`course_id`);

--
-- Indexes for table `user_enrollments`
--
ALTER TABLE `user_enrollments`
  ADD PRIMARY KEY (`user_id`,`course_id`),
  ADD KEY `course_id` (`course_id`);

--
-- Indexes for table `user_notes`
--
ALTER TABLE `user_notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `material_id` (`material_id`);

--
-- Indexes for table `user_progress`
--
ALTER TABLE `user_progress`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `material_id` (`material_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `cohorts`
--
ALTER TABLE `cohorts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `courses`
--
ALTER TABLE `courses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;

--
-- AUTO_INCREMENT for table `course_materials`
--
ALTER TABLE `course_materials`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `course_reviews`
--
ALTER TABLE `course_reviews`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `forum_categories`
--
ALTER TABLE `forum_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `forum_replies`
--
ALTER TABLE `forum_replies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `forum_topics`
--
ALTER TABLE `forum_topics`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `login_history`
--
ALTER TABLE `login_history`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `material_discussions`
--
ALTER TABLE `material_discussions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `message_reads`
--
ALTER TABLE `message_reads`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT for table `user_notes`
--
ALTER TABLE `user_notes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `user_progress`
--
ALTER TABLE `user_progress`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `courses`
--
ALTER TABLE `courses`
  ADD CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`trainer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `course_materials`
--
ALTER TABLE `course_materials`
  ADD CONSTRAINT `course_materials_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `course_reviews`
--
ALTER TABLE `course_reviews`
  ADD CONSTRAINT `course_reviews_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `course_reviews_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `forum_replies`
--
ALTER TABLE `forum_replies`
  ADD CONSTRAINT `forum_replies_ibfk_1` FOREIGN KEY (`topic_id`) REFERENCES `forum_topics` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `forum_replies_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `forum_topics`
--
ALTER TABLE `forum_topics`
  ADD CONSTRAINT `forum_topics_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `forum_categories` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `forum_topics_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `login_history`
--
ALTER TABLE `login_history`
  ADD CONSTRAINT `login_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `material_discussions`
--
ALTER TABLE `material_discussions`
  ADD CONSTRAINT `material_discussions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `material_discussions_ibfk_2` FOREIGN KEY (`material_id`) REFERENCES `course_materials` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `material_discussions_ibfk_3` FOREIGN KEY (`parent_id`) REFERENCES `material_discussions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `message_reads`
--
ALTER TABLE `message_reads`
  ADD CONSTRAINT `message_reads_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `message_reads_ibfk_2` FOREIGN KEY (`message_id`) REFERENCES `material_discussions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_cohorts`
--
ALTER TABLE `user_cohorts`
  ADD CONSTRAINT `user_cohorts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `user_cohorts_ibfk_2` FOREIGN KEY (`cohort_id`) REFERENCES `cohorts` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_enrollments`
--
ALTER TABLE `user_enrollments`
  ADD CONSTRAINT `user_enrollments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `user_enrollments_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_notes`
--
ALTER TABLE `user_notes`
  ADD CONSTRAINT `user_notes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `user_notes_ibfk_2` FOREIGN KEY (`material_id`) REFERENCES `course_materials` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_progress`
--
ALTER TABLE `user_progress`
  ADD CONSTRAINT `user_progress_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `user_progress_ibfk_2` FOREIGN KEY (`material_id`) REFERENCES `course_materials` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;