-- GVAS Academy LMS
-- Recommended schema and starter seed data
-- Target audience: Nigerian primary, secondary, and pre-varsity learners

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


CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE campuses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  campus_code VARCHAR(40) NOT NULL UNIQUE,
  state_name VARCHAR(80) NOT NULL,
  city VARCHAR(80) NOT NULL,
  address_line VARCHAR(255) NULL,
  is_virtual TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE learner_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  campus_id BIGINT UNSIGNED NULL,
  learner_code VARCHAR(40) NOT NULL UNIQUE,
  learner_stage ENUM('primary', 'secondary', 'prevarsity') NOT NULL,
  current_level VARCHAR(40) NOT NULL,
  date_of_birth DATE NULL,
  gender ENUM('male', 'female', 'other') NULL,
  admission_date DATE NULL,
  career_goal VARCHAR(150) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_learner_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_learner_campus FOREIGN KEY (campus_id) REFERENCES campuses(id)
);

CREATE TABLE staff_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  campus_id BIGINT UNSIGNED NULL,
  staff_code VARCHAR(40) NOT NULL UNIQUE,
  staff_type ENUM('teacher', 'admin', 'finance', 'support') NOT NULL,
  specialization VARCHAR(120) NULL,
  hire_date DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_staff_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_staff_campus FOREIGN KEY (campus_id) REFERENCES campuses(id)
);

CREATE TABLE guardians (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  relationship_label VARCHAR(40) NOT NULL,
  occupation VARCHAR(120) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_guardian_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE learner_guardians (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  learner_id BIGINT UNSIGNED NOT NULL,
  guardian_id BIGINT UNSIGNED NOT NULL,
  is_primary_contact TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_learner_guardian (learner_id, guardian_id),
  CONSTRAINT fk_lg_learner FOREIGN KEY (learner_id) REFERENCES learner_profiles(id),
  CONSTRAINT fk_lg_guardian FOREIGN KEY (guardian_id) REFERENCES guardians(id)
);

CREATE TABLE academic_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_name VARCHAR(30) NOT NULL UNIQUE,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE subjects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subject_name VARCHAR(100) NOT NULL,
  subject_code VARCHAR(30) NOT NULL UNIQUE,
  stage ENUM('primary', 'secondary', 'prevarsity') NOT NULL,
  exam_track ENUM('general', 'waec', 'neco', 'jamb', 'jupeb', 'ijmb') NOT NULL DEFAULT 'general',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE courses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subject_id BIGINT UNSIGNED NOT NULL,
  class_group_id BIGINT UNSIGNED NULL,
  owner_staff_id BIGINT UNSIGNED NULL,
  title VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL UNIQUE,
  summary TEXT NULL,
  course_type ENUM('full_course', 'revision', 'cbt_track', 'remedial') NOT NULL DEFAULT 'full_course',
  price_ngn DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  is_published TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_course_subject FOREIGN KEY (subject_id) REFERENCES subjects(id),
  CONSTRAINT fk_course_class_group FOREIGN KEY (class_group_id) REFERENCES class_groups(id),
  CONSTRAINT fk_course_owner FOREIGN KEY (owner_staff_id) REFERENCES staff_profiles(id)
);

CREATE TABLE course_modules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(150) NOT NULL,
  module_order INT UNSIGNED NOT NULL DEFAULT 1,
  overview TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_module_course FOREIGN KEY (course_id) REFERENCES courses(id)
);

CREATE TABLE lessons (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  lesson_order INT UNSIGNED NOT NULL DEFAULT 1,
  content_type ENUM('video', 'article', 'live_class', 'download', 'quiz_prep') NOT NULL DEFAULT 'video',
  duration_minutes INT UNSIGNED NULL,
  content_url VARCHAR(255) NULL,
  lesson_body LONGTEXT NULL,
  is_preview TINYINT(1) NOT NULL DEFAULT 0,
  published_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_lesson_module FOREIGN KEY (module_id) REFERENCES course_modules(id)
);

CREATE TABLE lesson_progress (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  learner_id BIGINT UNSIGNED NOT NULL,
  lesson_id BIGINT UNSIGNED NOT NULL,
  watched_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  completed_at DATETIME NULL,
  last_accessed_at DATETIME NULL,
  UNIQUE KEY uq_learner_lesson (learner_id, lesson_id),
  CONSTRAINT fk_progress_learner FOREIGN KEY (learner_id) REFERENCES learner_profiles(id),
  CONSTRAINT fk_progress_lesson FOREIGN KEY (lesson_id) REFERENCES lessons(id)
);

CREATE TABLE attendance_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  learner_id BIGINT UNSIGNED NOT NULL,
  class_group_id BIGINT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('present', 'late', 'absent', 'excused') NOT NULL DEFAULT 'present',
  recorded_by_staff_id BIGINT UNSIGNED NULL,
  remarks VARCHAR(255) NULL,
  UNIQUE KEY uq_attendance (learner_id, class_group_id, attendance_date),
  CONSTRAINT fk_attendance_learner FOREIGN KEY (learner_id) REFERENCES learner_profiles(id),
  CONSTRAINT fk_attendance_class FOREIGN KEY (class_group_id) REFERENCES class_groups(id),
  CONSTRAINT fk_attendance_staff FOREIGN KEY (recorded_by_staff_id) REFERENCES staff_profiles(id)
);

CREATE TABLE assignments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  instructions TEXT NULL,
  due_at DATETIME NULL,
  max_score DECIMAL(5,2) NOT NULL DEFAULT 100.00,
  created_by_staff_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_assignment_course FOREIGN KEY (course_id) REFERENCES courses(id),
  CONSTRAINT fk_assignment_staff FOREIGN KEY (created_by_staff_id) REFERENCES staff_profiles(id)
);

CREATE TABLE assignment_submissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  assignment_id BIGINT UNSIGNED NOT NULL,
  learner_id BIGINT UNSIGNED NOT NULL,
  submitted_at DATETIME NULL,
  submission_text LONGTEXT NULL,
  file_url VARCHAR(255) NULL,
  score DECIMAL(5,2) NULL,
  feedback TEXT NULL,
  status ENUM('pending', 'submitted', 'graded', 'late') NOT NULL DEFAULT 'pending',
  UNIQUE KEY uq_assignment_learner (assignment_id, learner_id),
  CONSTRAINT fk_submission_assignment FOREIGN KEY (assignment_id) REFERENCES assignments(id),
  CONSTRAINT fk_submission_learner FOREIGN KEY (learner_id) REFERENCES learner_profiles(id)
);

CREATE TABLE assessments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  assessment_type ENUM('quiz', 'cbt', 'exam', 'mock', 'practice') NOT NULL DEFAULT 'quiz',
  exam_track ENUM('general', 'waec', 'neco', 'jamb', 'jupeb', 'ijmb') NOT NULL DEFAULT 'general',
  duration_minutes INT UNSIGNED NULL,
  total_score DECIMAL(6,2) NOT NULL DEFAULT 100.00,
  attempts_allowed INT UNSIGNED NOT NULL DEFAULT 1,
  opens_at DATETIME NULL,
  closes_at DATETIME NULL,
  created_by_staff_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_assessment_course FOREIGN KEY (course_id) REFERENCES courses(id),
  CONSTRAINT fk_assessment_staff FOREIGN KEY (created_by_staff_id) REFERENCES staff_profiles(id)
);

CREATE TABLE assessment_questions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  assessment_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_option ENUM('A', 'B', 'C', 'D') NOT NULL,
  explanation TEXT NULL,
  difficulty_level ENUM('easy', 'medium', 'hard') NOT NULL DEFAULT 'medium',
  marks DECIMAL(5,2) NOT NULL DEFAULT 1.00,
  question_order INT UNSIGNED NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_question_assessment FOREIGN KEY (assessment_id) REFERENCES assessments(id)
);

CREATE TABLE assessment_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  assessment_id BIGINT UNSIGNED NOT NULL,
  learner_id BIGINT UNSIGNED NOT NULL,
  attempt_number INT UNSIGNED NOT NULL DEFAULT 1,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  submitted_at DATETIME NULL,
  score DECIMAL(6,2) NULL,
  percentage_score DECIMAL(5,2) NULL,
  status ENUM('in_progress', 'submitted', 'graded', 'expired') NOT NULL DEFAULT 'in_progress',
  CONSTRAINT fk_attempt_assessment FOREIGN KEY (assessment_id) REFERENCES assessments(id),
  CONSTRAINT fk_attempt_learner FOREIGN KEY (learner_id) REFERENCES learner_profiles(id)
);

CREATE TABLE question_attempt_answers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  attempt_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  selected_option ENUM('A', 'B', 'C', 'D') NULL,
  is_correct TINYINT(1) NULL,
  marks_awarded DECIMAL(5,2) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_attempt_question (attempt_id, question_id),
  CONSTRAINT fk_answer_attempt FOREIGN KEY (attempt_id) REFERENCES assessment_attempts(id),
  CONSTRAINT fk_answer_question FOREIGN KEY (question_id) REFERENCES assessment_questions(id)
);

CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  course_id BIGINT UNSIGNED NULL,
  reference_code VARCHAR(80) NOT NULL UNIQUE,
  amount_ngn DECIMAL(12,2) NOT NULL,
  payment_channel ENUM('card', 'bank_transfer', 'ussd', 'wallet') NOT NULL DEFAULT 'card',
  payment_status ENUM('pending', 'successful', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
  paid_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_payment_course FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO roles (name, description) VALUES
('super_admin', 'Full platform owner'),
('school_admin', 'Campus or school manager'),
('teacher', 'Academic instructor'),
('student', 'Learner account'),
('guardian', 'Parent or guardian account');

INSERT INTO campuses (name, campus_code, state_name, city, address_line, is_virtual) VALUES
('GVAS Virtual Campus', 'GVAS-VC', 'Lagos', 'Ikeja', 'Remote-first digital campus', 1),
('GVAS Mainland Learning Hub', 'GVAS-MLH', 'Lagos', 'Yaba', 'Yaba, Lagos', 0);

INSERT INTO staff_profiles (user_id, campus_id, staff_code, staff_type, specialization, hire_date) VALUES
(2, 1, 'TCH-001', 'teacher', 'Primary Mathematics', '2025-01-10'),
(3, 1, 'TCH-002', 'teacher', 'SSS Science and CBT Coaching', '2025-01-12');

INSERT INTO learner_profiles (user_id, campus_id, learner_code, learner_stage, current_level, date_of_birth, gender, admission_date, career_goal) VALUES
(4, 1, 'LRN-001', 'primary', 'Primary 5', '2015-05-14', 'male', '2026-01-08', 'Build strong mathematics confidence'),
(5, 1, 'LRN-002', 'prevarsity', 'JAMB Science', '2008-11-09', 'female', '2026-01-08', 'Study medicine');

INSERT INTO guardians (user_id, relationship_label, occupation) VALUES
(6, 'Mother', 'Civil Servant');

INSERT INTO learner_guardians (learner_id, guardian_id, is_primary_contact) VALUES
(1, 1, 1);

INSERT INTO academic_sessions (session_name, starts_on, ends_on, is_current) VALUES
('2025/2026', '2025-09-08', '2026-07-24', 0),
('2026/2027', '2026-09-07', '2027-07-23', 1);

INSERT INTO subjects (subject_name, subject_code, stage, exam_track) VALUES
('Mathematics', 'MTH-PRI', 'primary', 'general'),
('English Language', 'ENG-SEC', 'secondary', 'waec'),
('Biology', 'BIO-JAMB', 'prevarsity', 'jamb'),
('Chemistry', 'CHM-JAMB', 'prevarsity', 'jamb');

INSERT INTO courses (subject_id, class_group_id, owner_staff_id, title, slug, summary, course_type, price_ngn, is_published) VALUES
(1, 1, 1, 'Primary 5 Mathematics Mastery', 'primary-5-mathematics-mastery', 'Weekly mathematics learning pathway for upper primary learners.', 'full_course', 0.00, 1),
(3, 2, 2, 'JAMB Biology Intensive CBT Track', 'jamb-biology-intensive-cbt-track', 'Timed CBT drills, revision lessons, and correction guides for JAMB Biology.', 'cbt_track', 3500.00, 1),
(2, 3, 2, 'SSS 3 English WAEC Revision Lab', 'sss-3-english-waec-revision-lab', 'Revision content and mock preparation for senior secondary learners.', 'revision', 2500.00, 1);

INSERT INTO course_modules (course_id, title, module_order, overview) VALUES
(1, 'Number Operations and Problem Solving', 1, 'Core foundation for upper primary numeracy growth.'),
(1, 'Fractions, Decimals, and Percentages', 2, 'Bridge into stronger quantitative reasoning.'),
(2, 'Cell Biology and Living Systems', 1, 'High-yield JAMB Biology concepts with drill practice.'),
(2, 'Timed CBT Mock Sessions', 2, 'Full practice cycles and score review.'),
(3, 'Comprehension and Lexis Focus', 1, 'WAEC-style English improvement track.');

INSERT INTO lessons (module_id, title, lesson_order, content_type, duration_minutes, content_url, lesson_body, is_preview, published_at) VALUES
(1, 'Addition and subtraction challenge sets', 1, 'video', 18, 'https://example.com/lessons/p5-math-1', 'Interactive lesson with worked examples and checkpoint tasks.', 1, '2026-03-10 09:00:00'),
(1, 'Word problems with money and time', 2, 'article', 14, NULL, 'Guided practice focused on Nigerian real-life examples.', 0, '2026-03-12 09:00:00'),
(3, 'Cell structure crash revision', 1, 'video', 22, 'https://example.com/lessons/jamb-bio-1', 'High-yield Biology revision content.', 1, '2026-03-08 12:00:00'),
(4, 'Full JAMB mock exam 01', 1, 'quiz_prep', 40, NULL, 'Timed exam drill with analytics.', 0, '2026-03-15 08:00:00');

INSERT INTO lesson_progress (learner_id, lesson_id, watched_percent, completed_at, last_accessed_at) VALUES
(1, 1, 100.00, '2026-03-11 18:00:00', '2026-03-11 18:00:00'),
(1, 2, 45.00, NULL, '2026-03-14 16:25:00'),
(2, 3, 100.00, '2026-03-12 21:10:00', '2026-03-12 21:10:00');

INSERT INTO attendance_records (learner_id, class_group_id, attendance_date, status, recorded_by_staff_id, remarks) VALUES
(1, 1, '2026-03-18', 'present', 1, 'Participated actively'),
(2, 2, '2026-03-18', 'late', 2, 'Joined after first 10 minutes');

INSERT INTO assignments (course_id, title, instructions, due_at, max_score, created_by_staff_id) VALUES
(1, 'Primary 5 weekly numeracy worksheet', 'Solve all section A and B questions.', '2026-03-20 23:59:00', 20.00, 1),
(2, 'Biology correction notebook', 'Summarize all errors from mock exam 01.', '2026-03-22 23:59:00', 15.00, 2);

INSERT INTO assignment_submissions (assignment_id, learner_id, submitted_at, submission_text, score, feedback, status) VALUES
(1, 1, '2026-03-19 17:50:00', 'Completed worksheet with full workings.', 17.00, 'Good effort. Review question 5.', 'graded'),
(2, 2, '2026-03-22 19:05:00', 'Uploaded corrections from notebook.', NULL, NULL, 'submitted');

INSERT INTO assessments (course_id, title, assessment_type, exam_track, duration_minutes, total_score, attempts_allowed, opens_at, closes_at, created_by_staff_id) VALUES
(1, 'Primary 5 Mathematics Quiz 1', 'quiz', 'general', 15, 20.00, 2, '2026-03-16 08:00:00', '2026-03-30 23:59:00', 1),
(2, 'JAMB Biology Mock CBT 01', 'mock', 'jamb', 40, 50.00, 3, '2026-03-16 08:00:00', '2026-04-06 23:59:00', 2);

INSERT INTO assessment_questions (assessment_id, question_text, option_a, option_b, option_c, option_d, correct_option, explanation, difficulty_level, marks, question_order) VALUES
(1, 'What is 245 + 136?', '371', '381', '391', '401', 'B', 'Add hundreds, tens, and units carefully.', 'easy', 2.00, 1),
(1, 'Which fraction is equal to one-half?', '2/3', '3/4', '5/10', '7/10', 'C', '5 divided by 10 simplifies to 1/2.', 'easy', 2.00, 2),
(2, 'The basic unit of life is the?', 'Tissue', 'Cell', 'Organ', 'System', 'B', 'Cells are the smallest structural and functional units of life.', 'easy', 2.00, 1),
(2, 'Photosynthesis mainly occurs in the?', 'Nucleus', 'Chloroplast', 'Vacuole', 'Ribosome', 'B', 'Chloroplast contains chlorophyll for photosynthesis.', 'medium', 2.00, 2);

INSERT INTO assessment_attempts (assessment_id, learner_id, attempt_number, started_at, submitted_at, score, percentage_score, status) VALUES
(1, 1, 1, '2026-03-17 14:00:00', '2026-03-17 14:11:00', 18.00, 90.00, 'graded'),
(2, 2, 1, '2026-03-19 16:00:00', '2026-03-19 16:37:00', 36.00, 72.00, 'graded');

INSERT INTO question_attempt_answers (attempt_id, question_id, selected_option, is_correct, marks_awarded) VALUES
(1, 1, 'B', 1, 2.00),
(1, 2, 'C', 1, 2.00),
(2, 3, 'B', 1, 2.00),
(2, 4, 'A', 0, 0.00);

INSERT INTO payments (user_id, course_id, reference_code, amount_ngn, payment_channel, payment_status, paid_at) VALUES
(5, 2, 'GVASPAY-0001', 3500.00, 'card', 'successful', '2026-03-01 10:02:00'),
(5, 3, 'GVASPAY-0002', 2500.00, 'bank_transfer', 'pending', NULL);
