-- School App Database - MySQL/MariaDB Compatible
-- Generated: 2026-03-21


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

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

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL DEFAULT 'user',
  `credits` int NOT NULL DEFAULT 0,
  `parent_name` varchar(255) DEFAULT NULL,
  `parent_email` varchar(255) DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `role`, `credits`, `parent_name`, `parent_email`, `remember_token`, `created_at`, `updated_at`) VALUES
(1, 'Super Admin', 'admin@school.com', NULL, '$2y$12$o6WhDy/IoZcmm37xbjuuoeoHI/GlRTy966C.UNPkppjgS3kMmZzeC', 'super_admin', 0, 'System', 'system@school.com', NULL, NOW(), NOW()),
(2, 'Student One', 'student@example.com', NULL, '$2y$12$U9luzBvrEKUH/ekxTUg9UumUYYTKxu9DwB5oPfQ0aNDZ15J8OzL.C', 'user', 0, 'Parent One', 'parent@example.com', NULL, NOW(), NOW());

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

--
-- Table structure for table `password_reset_tokens`
--

CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `payload` longtext NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `cache`
--

CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `cache_locks`
--

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `job_batches`
--

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int unsigned NOT NULL,
  `pending_jobs` int unsigned NOT NULL,
  `failed_jobs` int unsigned NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` longtext,
  `cancelled_at` int unsigned DEFAULT NULL,
  `created_at` int unsigned NOT NULL,
  `finished_at` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `cbt_classes`
--

CREATE TABLE `cbt_classes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cbt_classes`
--

INSERT INTO `cbt_classes` (`id`, `name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Mathematics 101', 'Introductory mathematics course covering basic algebra and geometry.', NOW(), NOW());

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

--
-- Table structure for table `question_sets`
--

CREATE TABLE `question_sets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cbt_class_id` bigint unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `question_sets_cbt_class_id_foreign` (`cbt_class_id`),
  CONSTRAINT `question_sets_cbt_class_id_foreign` FOREIGN KEY (`cbt_class_id`) REFERENCES `cbt_classes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `question_sets`
--

INSERT INTO `question_sets` (`id`, `cbt_class_id`, `title`, `description`, `created_at`, `updated_at`) VALUES
(1, 1, 'Mid-term Quiz', 'A quiz covering the first 5 chapters of the course.', NOW(), NOW());

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

--
-- Table structure for table `questions`
--

CREATE TABLE `questions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `question_set_id` bigint unsigned NOT NULL,
  `question_text` 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_answer` varchar(1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `questions_question_set_id_foreign` (`question_set_id`),
  CONSTRAINT `questions_question_set_id_foreign` FOREIGN KEY (`question_set_id`) REFERENCES `question_sets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `questions`
--

INSERT INTO `questions` (`id`, `question_set_id`, `question_text`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_answer`, `created_at`, `updated_at`) VALUES
(1, 1, 'What is 2 + 2?', '3', '4', '5', '6', 'B', NOW(), NOW()),
(2, 1, 'Solve for x: 2x = 10', '2', '5', '10', '20', 'B', NOW(), NOW()),
(3, 1, 'Which shape has 3 sides?', 'Square', 'Circle', 'Triangle', 'Rectangle', 'C', NOW(), NOW()),
(4, 1, 'What is the square root of 81?', '7', '8', '9', '10', 'C', NOW(), NOW()),
(5, 1, 'What is 15 x 3?', '30', '40', '45', '50', 'C', NOW(), NOW());

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

--
-- Table structure for table `results`
--

CREATE TABLE `results` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `score` int NOT NULL,
  `total_questions` int NOT NULL,
  `correct_answers` int NOT NULL DEFAULT 0,
  `wrong_answers` int NOT NULL DEFAULT 0,
  `percentage` decimal(5,2) NOT NULL DEFAULT 0,
  `grade` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `results_user_id_foreign` (`user_id`),
  CONSTRAINT `results_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `test_sessions`
--

CREATE TABLE `test_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `question_set_id` bigint unsigned NOT NULL,
  `current_question_index` int NOT NULL DEFAULT 0,
  `score` int NOT NULL DEFAULT 0,
  `completed` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_sessions_user_id_foreign` (`user_id`),
  KEY `test_sessions_question_set_id_foreign` (`question_set_id`),
  CONSTRAINT `test_sessions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `test_sessions_question_set_id_foreign` FOREIGN KEY (`question_set_id`) REFERENCES `question_sets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `student_answers`
--

CREATE TABLE `student_answers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `test_session_id` bigint unsigned NOT NULL,
  `question_id` bigint unsigned NOT NULL,
  `selected_answer` varchar(1) NOT NULL,
  `is_correct` tinyint(1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_answers_test_session_id_foreign` (`test_session_id`),
  KEY `student_answers_question_id_foreign` (`question_id`),
  CONSTRAINT `student_answers_test_session_id_foreign` FOREIGN KEY (`test_session_id`) REFERENCES `test_sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `student_answers_question_id_foreign` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `value` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `key`, `value`, `created_at`, `updated_at`) VALUES
(1, 'stripe_enabled', '1', NOW(), NOW()),
(2, 'paypal_enabled', '1', NOW(), NOW()),
(3, 'stripe_webhook_secret', '', NOW(), NOW()),
(4, 'paypal_client_id', '', NOW(), NOW()),
(5, 'paypal_client_secret', '', NOW(), NOW()),
(6, 'paypal_sandbox_mode', '1', NOW(), NOW()),
(7, 'payment_confirmation_email', '1', NOW(), NOW());

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

--
-- Table structure for table `plans`
--

CREATE TABLE `plans` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `duration_months` int NOT NULL,
  `credits` int NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `plans`
--

INSERT INTO `plans` (`id`, `name`, `display_name`, `price`, `duration_months`, `credits`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'monthly', 'Monthly Plan', 3.00, 1, 30, 1, NOW(), NOW()),
(2, '6_months', '6 Months Plan', 10.00, 6, 200, 1, NOW(), NOW()),
(3, 'yearly', 'Yearly Plan', 20.00, 12, 500, 1, NOW(), NOW());

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

--
-- Table structure for table `subscriptions`
--

CREATE TABLE `subscriptions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `plan_id` bigint unsigned NOT NULL,
  `plan_name` varchar(255) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `start_date` timestamp NOT NULL,
  `end_date` timestamp NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'active',
  `gateway_subscription_id` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `subscriptions_user_id_foreign` (`user_id`),
  KEY `subscriptions_plan_id_foreign` (`plan_id`),
  CONSTRAINT `subscriptions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `subscriptions_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `payments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `subscription_id` bigint unsigned DEFAULT NULL,
  `plan_id` bigint unsigned DEFAULT NULL,
  `amount` decimal(8,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `payment_method` varchar(255) NOT NULL,
  `transaction_id` varchar(255) NOT NULL,
  `gateway_payment_id` varchar(255) DEFAULT NULL,
  `gateway_customer_id` varchar(255) DEFAULT NULL,
  `status` varchar(255) NOT NULL,
  `metadata` json DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_transaction_id_unique` (`transaction_id`),
  KEY `payments_user_id_foreign` (`user_id`),
  KEY `payments_subscription_id_foreign` (`subscription_id`),
  KEY `payments_plan_id_foreign` (`plan_id`),
  KEY `payments_gateway_payment_id_index` (`gateway_payment_id`),
  KEY `payments_status_index` (`status`),
  CONSTRAINT `payments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payments_subscription_id_foreign` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payments_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `file_buckets`
--

CREATE TABLE `file_buckets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `description` text,
  `visibility` enum('public','private','mixed') NOT NULL DEFAULT 'private',
  `max_file_size` bigint unsigned NOT NULL DEFAULT 104857600,
  `allowed_mime_types` json DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `file_buckets_name_unique` (`name`),
  KEY `file_buckets_created_by_foreign` (`created_by`),
  CONSTRAINT `file_buckets_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `file_buckets`
--

INSERT INTO `file_buckets` (`id`, `name`, `display_name`, `description`, `visibility`, `max_file_size`, `allowed_mime_types`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'question-media', 'Question Media', 'Images and media for exam questions', 'private', 104857600, NULL, 1, NULL, NOW(), NOW()),
(2, 'user-avatars', 'User Avatars', 'User profile pictures', 'public', 104857600, NULL, 1, NULL, NOW(), NOW()),
(3, 'course-materials', 'Course Materials', 'PDFs, documents, and learning resources', 'private', 104857600, NULL, 1, NULL, NOW(), NOW()),
(4, 'submissions', 'Student Submissions', 'Student uploaded assignments and files', 'private', 104857600, NULL, 1, NULL, NOW(), NOW());

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

--
-- Table structure for table `files`
--

CREATE TABLE `files` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `bucket_id` bigint unsigned NOT NULL,
  `uploaded_by` bigint unsigned NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `stored_name` varchar(255) NOT NULL,
  `path` varchar(255) NOT NULL,
  `disk` varchar(255) NOT NULL DEFAULT 's3',
  `mime_type` varchar(255) NOT NULL,
  `extension` varchar(20) NOT NULL,
  `size` bigint unsigned NOT NULL,
  `checksum` char(64) NOT NULL,
  `visibility` enum('public','private','signed') NOT NULL DEFAULT 'private',
  `metadata` json DEFAULT NULL,
  `thumbnail_path` varchar(255) DEFAULT NULL,
  `virus_scan_status` enum('pending','clean','infected','skipped') NOT NULL DEFAULT 'pending',
  `virus_scanned_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `files_uuid_unique` (`uuid`),
  KEY `files_bucket_id_foreign` (`bucket_id`),
  KEY `files_uploaded_by_foreign` (`uploaded_by`),
  KEY `files_mime_type_index` (`mime_type`),
  KEY `files_virus_scan_status_index` (`virus_scan_status`),
  KEY `files_expires_at_index` (`expires_at`),
  CONSTRAINT `files_bucket_id_foreign` FOREIGN KEY (`bucket_id`) REFERENCES `file_buckets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `files_uploaded_by_foreign` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `upload_sessions`
--

CREATE TABLE `upload_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `bucket_id` bigint unsigned NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `total_size` bigint unsigned NOT NULL,
  `chunk_size` int unsigned NOT NULL,
  `total_chunks` int unsigned NOT NULL,
  `uploaded_chunks` json DEFAULT NULL,
  `checksum` char(64) DEFAULT NULL,
  `status` enum('pending','uploading','assembling','completed','failed','expired') NOT NULL DEFAULT 'pending',
  `mime_type` varchar(255) DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `upload_sessions_uuid_unique` (`uuid`),
  KEY `upload_sessions_user_id_foreign` (`user_id`),
  KEY `upload_sessions_bucket_id_foreign` (`bucket_id`),
  KEY `upload_sessions_status_index` (`status`),
  KEY `upload_sessions_expires_at_index` (`expires_at`),
  CONSTRAINT `upload_sessions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `upload_sessions_bucket_id_foreign` FOREIGN KEY (`bucket_id`) REFERENCES `file_buckets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `file_chunks`
--

CREATE TABLE `file_chunks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `upload_session_id` bigint unsigned NOT NULL,
  `chunk_index` int unsigned NOT NULL,
  `chunk_path` varchar(255) NOT NULL,
  `chunk_size` int unsigned NOT NULL,
  `checksum` char(64) NOT NULL,
  `uploaded_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `file_chunks_session_index_unique` (`upload_session_id`, `chunk_index`),
  KEY `file_chunks_upload_session_id_foreign` (`upload_session_id`),
  CONSTRAINT `file_chunks_upload_session_id_foreign` FOREIGN KEY (`upload_session_id`) REFERENCES `upload_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `file_permissions`
--

CREATE TABLE `file_permissions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `file_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  `permission` enum('view','download','manage') NOT NULL DEFAULT 'view',
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `file_permissions_file_id_foreign` (`file_id`),
  KEY `file_permissions_user_id_foreign` (`user_id`),
  KEY `file_permissions_role_index` (`role`),
  CONSTRAINT `file_permissions_file_id_foreign` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`) ON DELETE CASCADE,
  CONSTRAINT `file_permissions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `storage_usage_stats`
--

CREATE TABLE `storage_usage_stats` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned DEFAULT NULL,
  `bucket_id` bigint unsigned DEFAULT NULL,
  `date` date NOT NULL,
  `storage_bytes` bigint unsigned NOT NULL DEFAULT 0,
  `upload_count` int unsigned NOT NULL DEFAULT 0,
  `upload_bytes` bigint unsigned NOT NULL DEFAULT 0,
  `download_count` int unsigned NOT NULL DEFAULT 0,
  `download_bytes` bigint unsigned NOT NULL DEFAULT 0,
  `bandwidth_bytes` bigint unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `storage_usage_stats_user_bucket_date_unique` (`user_id`, `bucket_id`, `date`),
  KEY `storage_usage_stats_user_id_foreign` (`user_id`),
  KEY `storage_usage_stats_bucket_id_foreign` (`bucket_id`),
  KEY `storage_usage_stats_date_index` (`date`),
  CONSTRAINT `storage_usage_stats_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `storage_usage_stats_bucket_id_foreign` FOREIGN KEY (`bucket_id`) REFERENCES `file_buckets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `signed_urls`
--

CREATE TABLE `signed_urls` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `file_id` bigint unsigned NOT NULL,
  `token` char(64) NOT NULL,
  `created_by` bigint unsigned NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `expires_at` timestamp NOT NULL,
  `access_count` int unsigned NOT NULL DEFAULT 0,
  `max_access_count` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `signed_urls_token_unique` (`token`),
  KEY `signed_urls_file_id_foreign` (`file_id`),
  KEY `signed_urls_created_by_foreign` (`created_by`),
  KEY `signed_urls_expires_at_index` (`expires_at`),
  CONSTRAINT `signed_urls_file_id_foreign` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`) ON DELETE CASCADE,
  CONSTRAINT `signed_urls_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `personal_access_tokens`
--

CREATE TABLE `personal_access_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`, `tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('0001_01_01_000000_create_users_table', 1),
('0001_01_01_000001_create_cache_table', 1),
('0001_01_01_000002_create_jobs_table', 1),
('2026_03_21_142937_create_cbt_classes_table', 1),
('2026_03_21_142939_create_question_sets_table', 1),
('2026_03_21_142940_create_questions_table', 1),
('2026_03_21_142942_create_results_table', 1),
('2026_03_21_144817_create_test_sessions_table', 1),
('2026_03_21_144819_create_student_answers_table', 1),
('2026_03_21_145304_add_grading_to_results_table', 1),
('2026_03_21_145424_create_settings_table', 1),
('2026_03_21_150142_create_plans_table', 1),
('2026_03_21_150144_create_subscriptions_table', 1),
('2026_03_21_150146_create_payments_table', 1),
('2026_03_21_150147_add_credits_to_users_table', 1),
('2026_03_21_160000_create_file_buckets_table', 2),
('2026_03_21_160001_create_files_table', 2),
('2026_03_21_160002_create_upload_sessions_table', 2),
('2026_03_21_160003_create_file_chunks_table', 2),
('2026_03_21_160004_create_file_permissions_table', 2),
('2026_03_21_160005_create_storage_usage_stats_table', 2),
('2026_03_21_160006_create_signed_urls_table', 2),
('2026_03_21_171358_create_personal_access_tokens_table', 2),
('2026_03_21_170000_add_payment_fields_to_payments_table', 3);

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

-- Update user passwords (they need to be re-hashed for your installation)
-- Run this after importing, or create new users via registration
-- UPDATE `users` SET `password` = '$2y$12$YOUR_NEW_HASH_HERE' WHERE `id` = 1;

-- End of database dump
