-- Digitechy Academy database schema
-- This is the single canonical schema file. Import this file only —
-- database_extended.sql and database_assessments.sql are deprecated
-- historical drafts kept for reference and must NOT be run.

CREATE DATABASE IF NOT EXISTS `digitech_academy` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `digitech_academy`;

CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('learner','tutor','affiliate','superadmin') NOT NULL DEFAULT 'learner',
  `status` ENUM('active','pending','suspended') NOT NULL DEFAULT 'active',
  `referral_code` VARCHAR(20) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_referral_code` (`referral_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL UNIQUE,
  `slug` VARCHAR(150) NOT NULL UNIQUE,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `courses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tutor_id` INT UNSIGNED NOT NULL,
  `category_id` INT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `subtitle` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `thumbnail_url` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('draft','pending_review','published','archived','rejected') NOT NULL DEFAULT 'draft',
  `rejection_reason` TEXT DEFAULT NULL,
  `popularity` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`tutor_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `lessons` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `position` INT UNSIGNED NOT NULL DEFAULT 0,
  `media_url` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `quizzes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `passing_score` INT UNSIGNED NOT NULL DEFAULT 60,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `quiz_questions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `quiz_id` INT UNSIGNED NOT NULL,
  `question_text` TEXT NOT NULL,
  `option_a` VARCHAR(255) NOT NULL,
  `option_b` VARCHAR(255) NOT NULL,
  `option_c` VARCHAR(255) DEFAULT NULL,
  `option_d` VARCHAR(255) DEFAULT NULL,
  `correct_answer` ENUM('A','B','C','D') NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`quiz_id`) REFERENCES `quizzes`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `exams` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `passing_score` INT UNSIGNED NOT NULL DEFAULT 60,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `exam_questions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `exam_id` INT UNSIGNED NOT NULL,
  `question_text` TEXT NOT NULL,
  `option_a` VARCHAR(255) NOT NULL,
  `option_b` VARCHAR(255) NOT NULL,
  `option_c` VARCHAR(255) DEFAULT NULL,
  `option_d` VARCHAR(255) DEFAULT NULL,
  `correct_answer` ENUM('A','B','C','D') NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `course_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `provider` VARCHAR(50) NOT NULL,
  `transaction_reference` VARCHAR(150) NOT NULL,
  `status` ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `enrollments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `course_id` INT UNSIGNED NOT NULL,
  `payment_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_course` (`user_id`, `course_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`payment_id`) REFERENCES `payments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `receipts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `course_id` INT UNSIGNED NOT NULL,
  `provider` VARCHAR(50) NOT NULL,
  `transaction_reference` VARCHAR(150) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `issued_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `certificates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `certificate_id` VARCHAR(80) NOT NULL UNIQUE,
  `student_id` INT UNSIGNED NOT NULL,
  `course_id` INT UNSIGNED NOT NULL,
  `student_name` VARCHAR(255) NOT NULL,
  `course_title` VARCHAR(255) NOT NULL,
  `instructor_name` VARCHAR(255) NOT NULL,
  `completion_date` DATE NOT NULL,
  `score` DECIMAL(5,2) NOT NULL,
  `verification_hash` VARCHAR(128) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_student_course` (`student_id`, `course_id`),
  FOREIGN KEY (`student_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `quiz_submissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `quiz_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `score` DECIMAL(5,2) NOT NULL,
  `passed` TINYINT(1) NOT NULL DEFAULT 0,
  `submitted_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`quiz_id`) REFERENCES `quizzes`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `exam_submissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `exam_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `score` DECIMAL(5,2) NOT NULL,
  `passed` TINYINT(1) NOT NULL DEFAULT 0,
  `submitted_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `lesson_progress` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `lesson_id` INT UNSIGNED NOT NULL,
  `completed_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_lesson` (`user_id`, `lesson_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`lesson_id`) REFERENCES `lessons`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `affiliate_referrals` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `affiliate_id` INT UNSIGNED NOT NULL,
  `referred_user_id` INT UNSIGNED DEFAULT NULL,
  `first_purchase` TINYINT(1) NOT NULL DEFAULT 0,
  `commission_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_referred_user` (`referred_user_id`),
  FOREIGN KEY (`affiliate_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referred_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `affiliate_clicks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `affiliate_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`affiliate_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `withdrawals` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending','paid','rejected') NOT NULL DEFAULT 'pending',
  `requested_at` DATETIME NOT NULL,
  `processed_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `course_revisions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_id` INT UNSIGNED NOT NULL,
  `tutor_id` INT UNSIGNED NOT NULL,
  `change_reason` VARCHAR(255) NOT NULL,
  `change_description` TEXT NOT NULL,
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`tutor_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `audit_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `action` VARCHAR(255) NOT NULL,
  `resource_type` VARCHAR(50) NOT NULL,
  `resource_id` INT UNSIGNED DEFAULT NULL,
  `metadata` JSON DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `password_resets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_token_hash` (`token_hash`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `rate_limit_hits` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `action` VARCHAR(50) NOT NULL,
  `identifier` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_action_identifier` (`action`, `identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed data

INSERT INTO `users` (`name`, `email`, `password_hash`, `role`, `status`, `created_at`, `updated_at`) VALUES
('Eve Mndangwa', 'evemndangwa@gmail.com', '$2y$10$iiNbj0lFafTHe6gFqz0P5OuA2j9Npvr0d5b328G5JGYmUVWm9b0XO', 'superadmin', 'active', NOW(), NOW()),
('Ndakris Investments', 'ndakrisinvestments@gmail.com', '$2y$10$iiNbj0lFafTHe6gFqz0P5OuA2j9Npvr0d5b328G5JGYmUVWm9b0XO', 'superadmin', 'active', NOW(), NOW()),
('Kelyn Nyamai', 'tutor@example.com', '$2y$10$iiNbj0lFafTHe6gFqz0P5OuA2j9Npvr0d5b328G5JGYmUVWm9b0XO', 'tutor', 'active', NOW(), NOW()),
('Sample Learner', 'learner@example.com', '$2y$10$iiNbj0lFafTHe6gFqz0P5OuA2j9Npvr0d5b328G5JGYmUVWm9b0XO', 'learner', 'active', NOW(), NOW()),
('Sample Affiliate', 'affiliate@example.com', '$2y$10$iiNbj0lFafTHe6gFqz0P5OuA2j9Npvr0d5b328G5JGYmUVWm9b0XO', 'affiliate', 'active', NOW(), NOW());

INSERT INTO `categories` (`name`, `slug`, `created_at`, `updated_at`) VALUES
('Artificial Intelligence','artificial-intelligence',NOW(),NOW()),
('Data Science','data-science',NOW(),NOW()),
('Machine Learning','machine-learning',NOW(),NOW()),
('Programming','programming',NOW(),NOW()),
('Software Development','software-development',NOW(),NOW()),
('Mobile Development','mobile-development',NOW(),NOW()),
('Web Development','web-development',NOW(),NOW()),
('Cloud Computing','cloud-computing',NOW(),NOW()),
('Cybersecurity','cybersecurity',NOW(),NOW()),
('Networking','networking',NOW(),NOW()),
('UI/UX Design','ui-ux-design',NOW(),NOW()),
('Graphic Design','graphic-design',NOW(),NOW()),
('Video Editing','video-editing',NOW(),NOW()),
('Digital Marketing','digital-marketing',NOW(),NOW()),
('Content Creation','content-creation',NOW(),NOW()),
('Microsoft Office','microsoft-office',NOW(),NOW()),
('Business','business',NOW(),NOW()),
('Finance','finance',NOW(),NOW()),
('Entrepreneurship','entrepreneurship',NOW(),NOW()),
('Project Management','project-management',NOW(),NOW());

INSERT INTO `courses` (`tutor_id`, `category_id`, `title`, `subtitle`, `description`, `price`, `thumbnail_url`, `status`, `popularity`, `created_at`, `updated_at`) VALUES
(3, 1, 'AI Essentials for Business', 'Master AI fundamentals and practical applications.', 'A modern professional course for learners seeking enterprise-ready AI skills.', 2.00, 'https://via.placeholder.com/640x360', 'published', 185, NOW(), NOW());

INSERT INTO `lessons` (`course_id`, `title`, `content`, `position`, `created_at`) VALUES
(1, 'Introduction to Artificial Intelligence', 'An overview of AI concepts, history, and business relevance.', 1, NOW()),
(1, 'Machine Learning Foundations', 'Core machine learning concepts every professional should know.', 2, NOW());

INSERT INTO `quizzes` (`course_id`, `title`, `description`, `passing_score`, `created_at`) VALUES
(1, 'AI Essentials Chapter 1 Quiz', 'A short quiz to verify your fundamentals.', 70, NOW());

INSERT INTO `quiz_questions` (`quiz_id`, `question_text`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_answer`, `created_at`) VALUES
(1, 'What does AI primarily automate?', 'Manual and repetitive work', 'Creative writing', 'Dream interpretation', 'Financial audits only', 'A', NOW()),
(1, 'Which statement is true about AI?', 'AI always replaces human judgment', 'AI can support better decisions with data', 'AI does not use data', 'AI requires no maintenance', 'B', NOW());

INSERT INTO `exams` (`course_id`, `title`, `description`, `passing_score`, `created_at`) VALUES
(1, 'AI Essentials Final Exam', 'Final assessment for the AI Essentials course.', 75, NOW());

INSERT INTO `exam_questions` (`exam_id`, `question_text`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_answer`, `created_at`) VALUES
(1, 'Which technology helps computers learn from data without being explicitly programmed?', 'Artificial intelligence', 'Machine learning', 'Cloud computing', 'Traditional programming', 'B', NOW()),
(1, 'What is a key benefit of using AI in business workflows?', 'Slower decision-making', 'Higher operational costs', 'Improved efficiency and automation', 'Less insight from data', 'C', NOW());

INSERT INTO `certificates` (`certificate_id`, `student_id`, `course_id`, `student_name`, `course_title`, `instructor_name`, `completion_date`, `score`, `verification_hash`, `created_at`) VALUES
('DGA-2026-0001', 4, 1, 'Sample Learner', 'AI Essentials for Business', 'Kelyn Nyamai', CURDATE(), 92.50, SHA2(CONCAT('DGA-2026-0001','digitech-academy'), 256), NOW());