-- https://www.phpmyadmin.net/
--

START TRANSACTION;



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

--
-- Table structure for table `admin_permissions`
--

CREATE TABLE `admin_permissions` (
  `id` int(11) UNSIGNED NOT NULL,
  `admin_id` int(11) UNSIGNED NOT NULL,
  `permission_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `name`, `slug`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Programming', 'programming', 'active', '2026-05-14 11:17:00', '2026-05-14 11:17:00', NULL);

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

--
-- Table structure for table `certificates`
--

CREATE TABLE `certificates` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `course_id` bigint(20) UNSIGNED NOT NULL,
  `quiz_attempt_id` bigint(20) UNSIGNED DEFAULT NULL,
  `certificate_number` varchar(100) DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `course_name` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `score` int(11) NOT NULL DEFAULT 0,
  `total` int(11) NOT NULL DEFAULT 10,
  `certificate_path` varchar(255) DEFAULT NULL,
  `qr_code` varchar(255) DEFAULT NULL,
  `download_count` int(11) NOT NULL DEFAULT 0,
  `issued_at` datetime DEFAULT NULL,
  `status` enum('active','revoked') DEFAULT 'active',
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `certificates`
--

INSERT INTO `certificates` (`id`, `user_id`, `course_id`, `quiz_attempt_id`, `certificate_number`, `user_name`, `course_name`, `name`, `course`, `score`, `total`, `certificate_path`, `qr_code`, `download_count`, `issued_at`, `status`, `revoked_at`, `created_at`, `updated_at`) VALUES
(4, 9, 1, NULL, 'CORSO-6A0D5EEC713AE', 'maze', 'Java Basics', 'maze', 'Java Basics', 6, 10, 'certificates/certificate-4-corso-6a0d5eec713ae.pdf', 'http://localhost/ci4/public/api/certificates/download/CORSO-6A0D5EEC713AE', 1, '2026-05-20 07:12:44', 'active', NULL, NULL, NULL),
(8, 14, 1, NULL, 'CORSO-6A14735E1C1F7', 'bambi', 'Java Basics', 'bambi', 'Java Basics', 6, 10, 'certificates/certificate-8-corso-6a14735e1c1f7.pdf', 'http://localhost/ci4/public/api/certificates/download/CORSO-6A14735E1C1F7', 1, '2026-05-25 16:05:50', 'active', NULL, NULL, NULL),
(9, 15, 1, NULL, 'CORSO-6A156DC1CC622', 'Elena', 'Java Basics', 'Elena', 'Java Basics', 6, 10, 'certificates/certificate-9-corso-6a156dc1cc622.pdf', 'http://localhost/ci4/public/api/certificates/download/CORSO-6A156DC1CC622', 1, '2026-05-26 09:54:09', 'active', NULL, NULL, NULL),
(13, 19, 1, NULL, 'CORSO-6A1D26C4108F7', 'nightfury', 'Java Basics', 'nightfury', 'Java Basics', 1, 1, 'certificates/certificate-13-corso-6a1d26c4108f7.pdf', 'http://localhost/ci4/public/api/certificates/download/CORSO-6A1D26C4108F7', 1, '2026-06-01 06:29:24', 'active', NULL, NULL, NULL);

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

--
-- Table structure for table `certificate_logs`
--

CREATE TABLE `certificate_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `certificate_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `action` varchar(50) NOT NULL,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `certificate_logs`
--

INSERT INTO `certificate_logs` (`id`, `certificate_id`, `user_id`, `action`, `created_at`) VALUES
(2, 4, 9, 'issued', '2026-05-25 15:31:57'),
(5, 8, 14, 'issued', '2026-05-25 16:05:50'),
(6, 9, 15, 'issued', '2026-05-26 09:54:09'),
(10, 13, 19, 'issued', '2026-06-01 06:29:24');

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

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

CREATE TABLE `courses` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `thumbnail` varchar(255) DEFAULT NULL,
  `level` enum('beginner','intermediate','advanced') NOT NULL DEFAULT 'beginner',
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `quiz_duration_minutes` int(11) DEFAULT NULL,
  `status` enum('draft','published','archived') NOT NULL DEFAULT 'draft',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `courses` (`id`, `category_id`, `created_by`, `title`, `slug`, `description`, `thumbnail`, `level`, `price`, `quiz_duration_minutes`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, NULL, 'Java Basics', 'java-basics', NULL, NULL, 'beginner', 100.00, NULL, 'published', '2026-05-14 11:17:00', '2026-05-14 11:17:00', NULL),
(2, 1, NULL, 'Python Basics', 'python-basics', NULL, NULL, 'beginner', 100.00, NULL, 'published', '2026-05-14 11:17:00', '2026-05-14 11:17:00', NULL),
(3, 1, NULL, 'Data Science Fundamentals', 'data-science-fundamentals', NULL, NULL, 'beginner', 100.00, NULL, 'published', '2026-05-14 11:17:00', '2026-05-14 11:17:00', NULL),
(4, 1, NULL, 'Digital Marketing', 'digital-marketing', NULL, NULL, 'beginner', 100.00, NULL, 'published', '2026-05-14 11:17:00', '2026-05-14 11:17:00', NULL),
(5, 1, NULL, 'Excel for Analysis', 'excel-for-analysis', NULL, NULL, 'beginner', 100.00, NULL, 'published', '2026-05-14 11:17:00', '2026-05-14 11:17:00', NULL),
(6, 1, NULL, 'SQL Essentials', 'sql-essentials', NULL, NULL, 'beginner', 100.00, NULL, 'published', '2026-05-14 11:17:00', '2026-05-14 11:17:00', NULL);

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

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

CREATE TABLE `enrollments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `course_id` bigint(20) UNSIGNED NOT NULL,
  `progress` int(11) NOT NULL DEFAULT 0,
  `status` enum('pending','active','completed','cancelled') NOT NULL DEFAULT 'active',
  `enrolled_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `enrollments` (`id`, `user_id`, `course_id`, `progress`, `status`, `enrolled_at`, `created_at`, `updated_at`) VALUES
(3, 9, 1, 100, 'completed', '2026-05-25 15:31:57', NULL, NULL),
(5, 14, 1, 100, 'completed', '2026-05-25 16:05:50', NULL, NULL),
(6, 15, 1, 100, 'completed', '2026-05-26 09:54:09', NULL, NULL),
(10, 19, 1, 100, 'completed', '2026-06-01 06:29:24', NULL, NULL);

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

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `version` varchar(255) NOT NULL,
  `class` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `namespace` varchar(255) NOT NULL,
  `time` int(11) NOT NULL,
  `batch` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `version`, `class`, `group`, `namespace`, `time`, `batch`) VALUES
(1, '2026-03-02-170233', 'App\\Database\\Migrations\\CreateUsersTable', 'default', 'App', 1778482591, 1),
(2, '2026-03-02-170343', 'App\\Database\\Migrations\\CreateCategoriesTable', 'default', 'App', 1778482591, 1),
(3, '2026-03-02-170412', 'App\\Database\\Migrations\\CreateCoursesTable', 'default', 'App', 1778482591, 1),
(4, '2026-03-02-170449', 'App\\Database\\Migrations\\CreateEnrollmentsTable', 'default', 'App', 1778482592, 1),
(5, '2026-03-02-170554', 'App\\Database\\Migrations\\CreateQuizzesTable', 'default', 'App', 1778482592, 1),
(6, '2026-03-02-170623', 'App\\Database\\Migrations\\CreateQuizQuestionsTable', 'default', 'App', 1778482592, 1),
(7, '2026-03-02-170650', 'App\\Database\\Migrations\\CreateQuizAttemptsTable', 'default', 'App', 1778482592, 1),
(8, '2026-03-02-170717', 'App\\Database\\Migrations\\CreatePaymentsTable', 'default', 'App', 1778482592, 1),
(9, '2026-03-02-170849', 'App\\Database\\Migrations\\CreateCertificatesTable', 'default', 'App', 1778482592, 1),
(10, '2026-03-02-171043', 'App\\Database\\Migrations\\CreateQuizAttemptAnswersTable', 'default', 'App', 1778482592, 1),
(11, '2026-03-02-171558', 'App\\Database\\Migrations\\CreateRefreshTokensTable', 'default', 'App', 1778482592, 1),
(12, '2026-03-02-171716', 'App\\Database\\Migrations\\CreateCertificateLogsTable', 'default', 'App', 1778482592, 1),
(13, '2026-03-04-061249', 'App\\Database\\Migrations\\AddColumnsToCourses', 'default', 'App', 1778482592, 1),
(14, '2026-03-05-150423', 'App\\Database\\Migrations\\AddStatusToCertificates', 'default', 'App', 1778482592, 1),
(15, '2026-03-05-151449', 'App\\Database\\Migrations\\AddRazorpayToPayments', 'default', 'App', 1778482592, 1),
(16, '2026-03-06-064629', 'App\\Database\\Migrations\\UpdateEnrollmentsStatus', 'default', 'App', 1778482592, 1),
(17, '2026-03-10-101201', 'App\\Database\\Migrations\\AddDeletedAtToQuizzes', 'default', 'App', 1778482592, 1),
(18, '2026-03-11-054932', 'App\\Database\\Migrations\\AddSuperAdminRole', 'default', 'App', 1778482592, 1),
(19, '2026-03-11-065131', 'App\\Database\\Migrations\\CreatePermissionsTables', 'default', 'App', 1778482592, 1),
(20, '2026-03-11-144824', 'App\\Database\\Migrations\\CreateUserPermissionsTable', 'default', 'App', 1778482592, 1),
(21, '2026-03-12-063214', 'App\\Database\\Migrations\\AddTypeToPermissions', 'default', 'App', 1778482592, 1),
(22, '2026-03-13-071611', 'App\\Database\\Migrations\\AddPhoneToUsers', 'default', 'App', 1778482592, 1),
(23, '2026-03-26-062816', 'App\\Database\\Migrations\\CreateVisitorsTable', 'default', 'App', 1778482592, 1),
(24, '2026-04-01-061948', 'App\\Database\\Migrations\\AddPasswordResetRequiredToUsers', 'default', 'App', 1778482592, 1),
(25, '2026-04-06-080909', 'App\\Database\\Migrations\\AddVisitorIdToQuizAttempts', 'default', 'App', 1778482592, 1),
(26, '2026-04-06-145501', 'App\\Database\\Migrations\\EnableGuestFlow', 'default', 'App', 1778482592, 1),
(27, '2026-04-06-173256', 'App\\Database\\Migrations\\AddTestPasswordFieldsToUsers', 'default', 'App', 1778482592, 1),
(28, '2026-04-15-063052', 'App\\Database\\Migrations\\UpdateQuizzesStructure', 'default', 'App', 1778482592, 1),
(29, '2026-04-18-193142', 'App\\Database\\Migrations\\CreateAdminsTable', 'default', 'App', 1778482592, 1),
(30, '2026-04-18-195827', 'App\\Database\\Migrations\\AddPasswordRawToAdmins', 'default', 'App', 1778482592, 1),
(31, '2026-04-19-143647', 'App\\Database\\Migrations\\AddResetFlagToAdmins', 'default', 'App', 1778482592, 1),
(32, '2026-04-19-164825', 'App\\Database\\Migrations\\FixAdminRoles', 'default', 'App', 1778482592, 1),
(33, '2026-04-19-165122', 'App\\Database\\Migrations\\SyncRolePermissionsEnum', 'default', 'App', 1778482592, 1),
(34, '2026-04-19-175857', 'App\\Database\\Migrations\\AddResetColumnsToAdmins', 'default', 'App', 1778482592, 1),
(35, '2026-05-05-190448', 'App\\Database\\Migrations\\FixRefreshTokensForAdmins', 'default', 'App', 1778482592, 1),
(36, '2026-05-05-190520', 'App\\Database\\Migrations\\FixUsersTable', 'default', 'App', 1778482592, 1),
(37, '2026-05-05-190534', 'App\\Database\\Migrations\\FixRolePermissionsEnum', 'default', 'App', 1778482592, 1),
(38, '2026-05-05-190545', 'App\\Database\\Migrations\\FixQuizzesDuplicateFk', 'default', 'App', 1778482592, 1),
(39, '2026-05-06-063749', 'App\\Database\\Migrations\\AddAdminIdToUserPermissions', 'default', 'App', 1778482592, 1),
(40, '2026-05-06-075537', 'App\\Database\\Migrations\\AddAdminToRolePermissionsEnum', 'default', 'App', 1778482593, 1),
(41, '2026-05-06-152840', 'App\\Database\\Migrations\\RenameRawPasswordInUsers', 'default', 'App', 1778482593, 1),
(42, '2026-05-06-191514', 'App\\Database\\Migrations\\CreateAdminPermissions', 'default', 'App', 1778482593, 1);

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

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `visitor_id` int(11) UNSIGNED DEFAULT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `course_id` bigint(20) UNSIGNED NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `payment_method` varchar(50) DEFAULT NULL,
  `transaction_id` varchar(100) DEFAULT NULL,
  `razorpay_order_id` varchar(100) DEFAULT NULL,
  `razorpay_payment_id` varchar(100) DEFAULT NULL,
  `razorpay_signature` varchar(255) DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `payments`
--

INSERT INTO `payments` (`id`, `visitor_id`, `user_id`, `course_id`, `amount`, `status`, `payment_method`, `transaction_id`, `razorpay_order_id`, `razorpay_payment_id`, `razorpay_signature`, `paid_at`, `created_at`, `updated_at`) VALUES
(4, NULL, 9, 1, 300.00, 'completed', 'razorpay', 'backfill-cert-4', NULL, NULL, NULL, '2026-05-20 07:12:44', '2026-05-20 07:12:44', '2026-05-20 07:12:44'),
(7, NULL, 14, 1, 300.00, 'completed', 'razorpay', 'pay_StelyMpqUvGXGO', 'order_StelZRGEVKeFM6', 'pay_StelyMpqUvGXGO', '40f9a41fc0c50640bee8b8641fb903fa3f9b15248e6d05e5d06a048236af7dcc', '2026-05-25 16:05:50', '2026-05-25 16:05:50', '2026-05-25 16:05:50'),
(8, NULL, 15, 1, 300.00, 'completed', 'razorpay', 'pay_StwyRM8KDIMZpg', 'order_StwwYcCRki8UTa', 'pay_StwyRM8KDIMZpg', '90abb04f830394a46489d5be61acfbf2de748170db4b813d918e573422a8006f', '2026-05-26 09:54:17', '2026-05-26 09:54:17', '2026-05-26 09:54:17'),
(12, NULL, 19, 1, 300.00, 'completed', 'razorpay', 'pay_SwGgzWknw3a3uz', 'order_SwGgCDEAeyrp5O', 'pay_SwGgzWknw3a3uz', '7754d5417d1a2513a738a8d650cd61cdd7a851be849cb4f9f7c63c7e07aa9970', '2026-06-01 06:29:27', '2026-06-01 06:29:27', '2026-06-01 06:29:27');

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

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `type` enum('admin','student') NOT NULL DEFAULT 'admin',
  `description` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` (`id`, `name`, `slug`, `type`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Manage system settings', 'manage_settings', '', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(2, 'Manage admins', 'manage_admins', '', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(3, 'Manage role permissions', 'manage_role_permissions', '', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(4, 'Full Database Access', 'database_backup', '', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(5, 'Access admin panel', 'access_admin_panel', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(6, 'View students', 'view_students', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(7, 'Manage student status', 'manage_students', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(8, 'Manage categories', 'manage_categories', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(9, 'Manage courses', 'manage_courses', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(10, 'Manage quizzes', 'manage_quizzes', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(11, 'Manage quiz questions', 'manage_quiz_questions', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(12, 'Manage certificates', 'manage_certificates', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(13, 'View analytics', 'view_analytics', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(14, 'View payments', 'view_payments', 'admin', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(15, 'View courses', 'student_view_courses', 'student', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(16, 'Enroll', 'student_enroll', 'student', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(17, 'Take quiz', 'student_take_quiz', 'student', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(18, 'Download certificate', 'student_download_certificate', 'student', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29'),
(19, 'Update profile', 'student_update_profile', 'student', NULL, '2026-05-11 06:57:29', '2026-05-11 06:57:29');

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

--
-- Table structure for table `quizzes`
--

CREATE TABLE `quizzes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `course_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `total_marks` int(11) NOT NULL DEFAULT 0,
  `passing_marks` int(11) NOT NULL DEFAULT 0,
  `duration` int(11) DEFAULT NULL,
  `status` enum('active','inactive','draft') DEFAULT 'draft',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `quizzes`
--

INSERT INTO `quizzes` (`id`, `course_id`, `title`, `slug`, `total_marks`, `passing_marks`, `duration`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, 'Java Basics — Quiz', 'java-basics-quiz', 10, 6, 600, 'draft', '2026-05-25 15:31:57', '2026-05-25 15:31:57', NULL),
(2, 2, 'Python Basics — Quiz', 'python-basics-quiz', 10, 6, 600, 'draft', '2026-05-25 15:31:57', '2026-05-25 15:31:57', NULL),
(3, 3, 'Data Science Fundamentals — Quiz', 'data-science-fundamentals-quiz', 10, 6, 600, 'draft', '2026-05-25 15:31:57', '2026-05-25 15:31:57', NULL),
(4, 4, 'Digital Marketing — Quiz', 'digital-marketing-quiz', 10, 6, 600, 'draft', '2026-05-25 15:31:57', '2026-05-25 15:31:57', NULL),
(5, 5, 'Excel for Analysis — Quiz', 'excel-for-analysis-quiz', 10, 6, 600, 'draft', '2026-05-25 15:31:57', '2026-05-25 15:31:57', NULL),
(6, 6, 'SQL Essentials — Quiz', 'sql-essentials-quiz', 10, 6, 600, 'draft', '2026-05-25 15:31:57', '2026-05-25 15:31:57', NULL);

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

--
-- Table structure for table `quiz_attempts`
--

CREATE TABLE `quiz_attempts` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `quiz_id` bigint(20) UNSIGNED NOT NULL,
  `visitor_id` int(11) DEFAULT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `score` int(11) NOT NULL DEFAULT 0,
  `passed` tinyint(1) NOT NULL DEFAULT 0,
  `obtained_marks` int(11) NOT NULL DEFAULT 0,
  `total_marks` int(11) NOT NULL DEFAULT 0,
  `best_attempt` tinyint(1) NOT NULL DEFAULT 0,
  `total_questions` int(11) NOT NULL DEFAULT 0,
  `time_taken` int(11) NOT NULL DEFAULT 0,
  `ip_address` varchar(45) DEFAULT NULL,
  `result` enum('pass','fail') DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `attempted_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `quiz_attempts`
--

INSERT INTO `quiz_attempts` (`id`, `quiz_id`, `visitor_id`, `user_id`, `score`, `passed`, `obtained_marks`, `total_marks`, `best_attempt`, `total_questions`, `time_taken`, `ip_address`, `result`, `started_at`, `completed_at`, `attempted_at`, `created_at`) VALUES
(2, 1, NULL, 9, 6, 1, 6, 10, 0, 10, 0, '0.0.0.0', 'pass', '2026-05-25 15:31:57', '2026-05-25 15:31:57', '2026-05-25 15:31:57', '2026-05-25 15:31:57'),
(5, 1, NULL, 14, 6, 1, 6, 10, 0, 10, 0, '::1', 'pass', '2026-05-25 16:05:50', '2026-05-25 16:05:50', '2026-05-25 16:05:50', '2026-05-25 16:05:50'),
(6, 6, NULL, 14, 9, 1, 9, 10, 0, 10, 0, '::1', 'pass', '2026-05-26 06:04:27', '2026-05-26 06:04:27', '2026-05-26 06:04:27', '2026-05-26 06:04:27'),
(7, 1, NULL, 15, 6, 1, 6, 10, 0, 10, 0, '::1', 'pass', '2026-05-26 09:54:09', '2026-05-26 09:54:09', '2026-05-26 09:54:09', '2026-05-26 09:54:09'),
(16, 1, NULL, 19, 1, 0, 1, 10, 0, 1, 0, '::1', 'fail', '2026-06-01 06:29:24', '2026-06-01 06:29:24', '2026-06-01 06:29:24', '2026-06-01 06:29:24');

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

--
-- Table structure for table `quiz_attempt_answers`
--

CREATE TABLE `quiz_attempt_answers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `attempt_id` bigint(20) UNSIGNED NOT NULL,
  `question_id` bigint(20) UNSIGNED NOT NULL,
  `selected_option` enum('A','B','C','D') NOT NULL,
  `is_correct` tinyint(1) NOT NULL DEFAULT 0,
  `marks_awarded` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quiz_questions`
--

CREATE TABLE `quiz_questions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `quiz_id` bigint(20) UNSIGNED NOT NULL,
  `question` text NOT NULL,
  `option_a` varchar(255) NOT NULL,
  `option_b` varchar(255) NOT NULL,
  `option_c` varchar(255) NOT NULL,
  `option_d` varchar(255) NOT NULL,
  `correct_option` enum('A','B','C','D') NOT NULL,
  `marks` int(11) NOT NULL DEFAULT 1,
  `position` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `quiz_questions`
--

INSERT INTO `quiz_questions` (`id`, `quiz_id`, `question`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_option`, `marks`, `position`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, 'Which of the following statements is true about a static method in Java?', 'It can directly access instance variables of the same class.', 'It requires an object of the class to be instantiated before it can be called.', 'It cannot be overloaded in the same class.', 'It can be executed without creating an instance of the class.', 'D', 1, 1, '2026-05-27 05:32:24', '2026-05-27 05:32:34', NULL);

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

--
-- Table structure for table `role_permissions`
--

CREATE TABLE `role_permissions` (
  `id` int(11) UNSIGNED NOT NULL,
  `role_slug` varchar(32) NOT NULL,
  `permission_slug` varchar(64) NOT NULL,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `role_permissions`
--

INSERT INTO `role_permissions` (`id`, `role_slug`, `permission_slug`, `created_at`) VALUES
(1, 'super_admin', 'manage_settings', '2026-05-26 06:16:15'),
(2, 'super_admin', 'manage_admins', '2026-05-26 06:16:15'),
(3, 'super_admin', 'manage_role_permissions', '2026-05-26 06:16:15'),
(4, 'super_admin', 'database_backup', '2026-05-26 06:16:15'),
(5, 'super_admin', 'access_admin_panel', '2026-05-26 06:16:15'),
(7, 'super_admin', 'view_students', '2026-05-26 06:16:15'),
(9, 'super_admin', 'manage_students', '2026-05-26 06:16:15'),
(11, 'super_admin', 'manage_categories', '2026-05-26 06:16:15'),
(13, 'super_admin', 'manage_courses', '2026-05-26 06:16:15'),
(15, 'super_admin', 'manage_quizzes', '2026-05-26 06:16:15'),
(17, 'super_admin', 'manage_quiz_questions', '2026-05-26 06:16:15'),
(19, 'super_admin', 'manage_certificates', '2026-05-26 06:16:15'),
(21, 'super_admin', 'view_analytics', '2026-05-26 06:16:15'),
(23, 'super_admin', 'view_payments', '2026-05-26 06:16:15'),
(25, 'super_admin', 'student_view_courses', '2026-05-26 06:16:15'),
(27, 'student', 'student_view_courses', '2026-05-26 06:16:15'),
(28, 'super_admin', 'student_enroll', '2026-05-26 06:16:15'),
(30, 'student', 'student_enroll', '2026-05-26 06:16:15'),
(31, 'super_admin', 'student_take_quiz', '2026-05-26 06:16:15'),
(33, 'student', 'student_take_quiz', '2026-05-26 06:16:15'),
(34, 'super_admin', 'student_download_certificate', '2026-05-26 06:16:15'),
(36, 'student', 'student_download_certificate', '2026-05-26 06:16:15'),
(37, 'super_admin', 'student_update_profile', '2026-05-26 06:16:15'),
(39, 'student', 'student_update_profile', '2026-05-26 06:16:15'),
(52, 'admin', 'access_admin_panel', '2026-05-27 06:03:17'),
(53, 'admin', 'reports_view', '2026-05-27 06:03:17'),
(54, 'admin', 'users_view', '2026-05-27 06:03:17'),
(55, 'admin', 'users_manage', '2026-05-27 06:03:17'),
(56, 'admin', 'certificates_view', '2026-05-27 06:03:17'),
(57, 'admin', 'certificates_manage', '2026-05-27 06:03:17'),
(58, 'admin', 'courses_manage', '2026-05-27 06:03:17'),
(59, 'admin', 'quizzes_manage', '2026-05-27 06:03:17'),
(60, 'admin', 'questions_manage', '2026-05-27 06:03:17'),
(61, 'admin', 'enrollments_view', '2026-05-27 06:03:17'),
(62, 'admin', 'payments_view', '2026-05-27 06:03:17');

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

--
-- Table structure for table `user_permissions`
--

CREATE TABLE `user_permissions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `admin_id` int(11) UNSIGNED DEFAULT NULL,
  `permission_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for table `admin_permissions`
--
ALTER TABLE `admin_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `admin_id_permission_id` (`admin_id`,`permission_id`),
  ADD KEY `admin_permissions_permission_id_foreign` (`permission_id`);

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

--
-- Indexes for table `certificates`
--
ALTER TABLE `certificates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `certificate_number` (`certificate_number`),
  ADD KEY `certificates_user_id_foreign` (`user_id`),
  ADD KEY `certificates_course_id_foreign` (`course_id`),
  ADD KEY `certificates_quiz_attempt_id_foreign` (`quiz_attempt_id`);

--
-- Indexes for table `certificate_logs`
--
ALTER TABLE `certificate_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `certificate_logs_certificate_id_foreign` (`certificate_id`),
  ADD KEY `certificate_logs_user_id_foreign` (`user_id`);

--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `courses_category_id_foreign` (`category_id`);

--
-- Indexes for table `enrollments`
--
ALTER TABLE `enrollments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `enrollments_user_id_foreign` (`user_id`),
  ADD KEY `enrollments_course_id_foreign` (`course_id`);

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

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `payments_user_id_foreign` (`user_id`),
  ADD KEY `payments_course_id_foreign` (`course_id`);

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

--
-- Indexes for table `quizzes`
--
ALTER TABLE `quizzes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD UNIQUE KEY `idx_quiz_slug` (`slug`),
  ADD KEY `fk_quiz_course` (`course_id`);

--
-- Indexes for table `quiz_attempts`
--
ALTER TABLE `quiz_attempts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `quiz_attempts_quiz_id_foreign` (`quiz_id`),
  ADD KEY `quiz_attempts_user_id_foreign` (`user_id`),
  ADD KEY `best_attempt` (`best_attempt`);

--
-- Indexes for table `quiz_attempt_answers`
--
ALTER TABLE `quiz_attempt_answers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `quiz_attempt_answers_attempt_id_foreign` (`attempt_id`),
  ADD KEY `quiz_attempt_answers_question_id_foreign` (`question_id`);

--
-- Indexes for table `quiz_questions`
--
ALTER TABLE `quiz_questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `quiz_questions_quiz_id_foreign` (`quiz_id`);

--
-- Indexes for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `role_perm` (`role_slug`,`permission_slug`);

--
-- Indexes for table `user_permissions`
--
ALTER TABLE `user_permissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_permissions_user_id_foreign` (`user_id`),
  ADD KEY `user_permissions_permission_id_foreign` (`permission_id`),
  ADD KEY `user_permissions_admin_id_foreign` (`admin_id`);

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

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `certificates`
--
ALTER TABLE `certificates`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT for table `certificate_logs`
--
ALTER TABLE `certificate_logs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `courses`
--
ALTER TABLE `courses`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `enrollments`
--
ALTER TABLE `enrollments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;

--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `permissions`
--
ALTER TABLE `permissions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

--
-- AUTO_INCREMENT for table `quizzes`
--
ALTER TABLE `quizzes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `quiz_attempts`
--
ALTER TABLE `quiz_attempts`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `quiz_attempt_answers`
--
ALTER TABLE `quiz_attempt_answers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `quiz_questions`
--
ALTER TABLE `quiz_questions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

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

--
-- AUTO_INCREMENT for table `user_permissions`
--
ALTER TABLE `user_permissions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `admin_permissions`
--
ALTER TABLE `admin_permissions`
  ADD CONSTRAINT `admin_permissions_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `admin_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `certificates`
--
ALTER TABLE `certificates`
  ADD CONSTRAINT `certificates_course_id_foreign` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `certificates_quiz_attempt_id_foreign` FOREIGN KEY (`quiz_attempt_id`) REFERENCES `quiz_attempts` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  ADD CONSTRAINT `certificates_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `certificate_logs`
--
ALTER TABLE `certificate_logs`
  ADD CONSTRAINT `certificate_logs_certificate_id_foreign` FOREIGN KEY (`certificate_id`) REFERENCES `certificates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `certificate_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `courses`
--
ALTER TABLE `courses`
  ADD CONSTRAINT `courses_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `enrollments`
--
ALTER TABLE `enrollments`
  ADD CONSTRAINT `enrollments_course_id_foreign` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `enrollments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_course_id_foreign` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `payments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

--
-- Constraints for table `quiz_attempts`
--
ALTER TABLE `quiz_attempts`
  ADD CONSTRAINT `quiz_attempts_quiz_id_foreign` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `quiz_attempts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `quiz_attempt_answers`
--
ALTER TABLE `quiz_attempt_answers`
  ADD CONSTRAINT `quiz_attempt_answers_attempt_id_foreign` FOREIGN KEY (`attempt_id`) REFERENCES `quiz_attempts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `quiz_attempt_answers_question_id_foreign` FOREIGN KEY (`question_id`) REFERENCES `quiz_questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `quiz_questions`
--
ALTER TABLE `quiz_questions`
  ADD CONSTRAINT `quiz_questions_quiz_id_foreign` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `user_permissions`
--
ALTER TABLE `user_permissions`
  ADD CONSTRAINT `user_permissions_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `user_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `user_permissions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

