-- JNM Homeschool Platform - Complete Database Schema
-- PostgreSQL

-- Drop existing tables if they exist (for clean install)
DROP TABLE IF EXISTS notifications CASCADE;
DROP TABLE IF EXISTS comments CASCADE;
DROP TABLE IF EXISTS retakes CASCADE;
DROP TABLE IF EXISTS grades CASCADE;
DROP TABLE IF EXISTS submissions CASCADE;
DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS lessons CASCADE;
DROP TABLE IF EXISTS units CASCADE;
DROP TABLE IF EXISTS enrollments CASCADE;
DROP TABLE IF EXISTS courses CASCADE;
DROP TABLE IF EXISTS attendance CASCADE;
DROP TABLE IF EXISTS daily_logs CASCADE;
DROP TABLE IF EXISTS period_grades CASCADE;
DROP TABLE IF EXISTS assessment_periods CASCADE;
DROP TABLE IF EXISTS calendar_events CASCADE;
DROP TABLE IF EXISTS students CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- =============================================
-- USERS TABLE (Parents/Teachers)
-- =============================================
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  role VARCHAR(50) DEFAULT 'parent',
  phone VARCHAR(50),
  notification_email BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- STUDENTS TABLE
-- =============================================
CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  school_id VARCHAR(50) UNIQUE NOT NULL,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  password_hash VARCHAR(255),
  date_of_birth DATE,
  grade_level INT NOT NULL,
  profile_image VARCHAR(500),
  status VARCHAR(50) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- COURSES TABLE (Main + Electives)
-- =============================================
CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  grade_level INT NOT NULL,
  subject VARCHAR(100) NOT NULL,
  course_type VARCHAR(50) DEFAULT 'core',
  description TEXT,
  credit_hours DECIMAL(3,1) DEFAULT 1.0,
  is_active BOOLEAN DEFAULT TRUE,
  created_by INT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- STUDENT ENROLLMENTS
-- =============================================
CREATE TABLE enrollments (
  id SERIAL PRIMARY KEY,
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  course_id INT REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(50) DEFAULT 'active',
  UNIQUE(student_id, course_id)
);

-- =============================================
-- UNITS WITHIN COURSES
-- =============================================
CREATE TABLE units (
  id SERIAL PRIMARY KEY,
  course_id INT REFERENCES courses(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  unit_number INT NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- LESSONS WITHIN UNITS
-- =============================================
CREATE TABLE lessons (
  id SERIAL PRIMARY KEY,
  unit_id INT REFERENCES units(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  lesson_number INT NOT NULL,
  video_url VARCHAR(500),
  video_title VARCHAR(255),
  video_duration VARCHAR(50),
  content TEXT,
  supplementary_links TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- ASSIGNMENTS TABLE
-- =============================================
CREATE TABLE assignments (
  id SERIAL PRIMARY KEY,
  lesson_id INT REFERENCES lessons(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  instructions TEXT,
  due_date DATE,
  due_time TIME DEFAULT '15:00:00',
  assignment_type VARCHAR(50) DEFAULT 'homework',
  point_value INT DEFAULT 100,
  weight_category VARCHAR(50) DEFAULT 'homework',
  allow_retake BOOLEAN DEFAULT TRUE,
  max_retakes INT DEFAULT 3,
  passing_grade INT DEFAULT 70,
  is_active BOOLEAN DEFAULT TRUE,
  created_by INT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- STUDENT SUBMISSIONS
-- =============================================
CREATE TABLE submissions (
  id SERIAL PRIMARY KEY,
  assignment_id INT REFERENCES assignments(id) ON DELETE CASCADE,
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  submission_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  submission_text TEXT,
  submission_file_url VARCHAR(500),
  submission_file_name VARCHAR(255),
  hours_spent DECIMAL(5,2) DEFAULT 0,
  status VARCHAR(50) DEFAULT 'submitted',
  is_retake BOOLEAN DEFAULT FALSE,
  retake_number INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- GRADES TABLE
-- =============================================
CREATE TABLE grades (
  id SERIAL PRIMARY KEY,
  submission_id INT REFERENCES submissions(id) ON DELETE CASCADE,
  assignment_id INT REFERENCES assignments(id),
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  points_earned DECIMAL(6,2),
  point_value INT DEFAULT 100,
  percentage DECIMAL(5,2),
  letter_grade VARCHAR(2),
  weight_category VARCHAR(50) DEFAULT 'homework',
  feedback TEXT,
  graded_by INT REFERENCES users(id),
  graded_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_final BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- RETAKES TRACKING
-- =============================================
CREATE TABLE retakes (
  id SERIAL PRIMARY KEY,
  assignment_id INT REFERENCES assignments(id) ON DELETE CASCADE,
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  original_grade_id INT REFERENCES grades(id),
  new_grade_id INT REFERENCES grades(id),
  retake_number INT DEFAULT 1,
  reason TEXT,
  approved_by INT REFERENCES users(id),
  submitted_date TIMESTAMP,
  status VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- TEACHER COMMENTS
-- =============================================
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  assignment_id INT REFERENCES assignments(id),
  course_id INT REFERENCES courses(id),
  user_id INT REFERENCES users(id),
  comment_text TEXT NOT NULL,
  comment_type VARCHAR(50) DEFAULT 'general',
  is_private BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- DAILY LOGS AND TIME TRACKING
-- =============================================
CREATE TABLE daily_logs (
  id SERIAL PRIMARY KEY,
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  log_date DATE NOT NULL,
  total_hours DECIMAL(5,2) DEFAULT 0,
  math_hours DECIMAL(4,2) DEFAULT 0,
  ela_hours DECIMAL(4,2) DEFAULT 0,
  science_hours DECIMAL(4,2) DEFAULT 0,
  social_studies_hours DECIMAL(4,2) DEFAULT 0,
  elective_hours DECIMAL(4,2) DEFAULT 0,
  subjects_covered TEXT,
  notes TEXT,
  day_status VARCHAR(50) DEFAULT 'school_day',
  logged_by INT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(student_id, log_date)
);

-- =============================================
-- ATTENDANCE TRACKING
-- =============================================
CREATE TABLE attendance (
  id SERIAL PRIMARY KEY,
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  attendance_date DATE NOT NULL,
  status VARCHAR(50) DEFAULT 'present',
  check_in_time TIME,
  check_out_time TIME,
  total_hours DECIMAL(4,2),
  notes TEXT,
  recorded_by INT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(student_id, attendance_date)
);

-- =============================================
-- ASSESSMENT PERIODS (Quarters, Midterms, Finals)
-- =============================================
CREATE TABLE assessment_periods (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  period_type VARCHAR(50) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  school_year VARCHAR(20),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- PERIOD GRADES (Quarter/Midterm/Final)
-- =============================================
CREATE TABLE period_grades (
  id SERIAL PRIMARY KEY,
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  course_id INT REFERENCES courses(id),
  assessment_period_id INT REFERENCES assessment_periods(id),
  homework_avg DECIMAL(5,2),
  quiz_avg DECIMAL(5,2),
  test_avg DECIMAL(5,2),
  weighted_average DECIMAL(5,2),
  letter_grade VARCHAR(2),
  teacher_comments TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- NEVADA SCHOOL CALENDAR
-- =============================================
CREATE TABLE calendar_events (
  id SERIAL PRIMARY KEY,
  event_date DATE NOT NULL,
  event_end_date DATE,
  event_type VARCHAR(50) NOT NULL,
  event_name VARCHAR(255) NOT NULL,
  is_school_day BOOLEAN DEFAULT TRUE,
  school_year VARCHAR(20),
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- NOTIFICATIONS
-- =============================================
CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  student_id INT REFERENCES students(id),
  notification_type VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  is_read BOOLEAN DEFAULT FALSE,
  is_emailed BOOLEAN DEFAULT FALSE,
  priority VARCHAR(20) DEFAULT 'normal',
  link VARCHAR(500),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- INDEXES FOR PERFORMANCE
-- =============================================
CREATE INDEX idx_students_user_id ON students(user_id);
CREATE INDEX idx_students_school_id ON students(school_id);
CREATE INDEX idx_students_grade_level ON students(grade_level);
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
CREATE INDEX idx_units_course ON units(course_id);
CREATE INDEX idx_lessons_unit ON lessons(unit_id);
CREATE INDEX idx_assignments_lesson ON assignments(lesson_id);
CREATE INDEX idx_submissions_student ON submissions(student_id);
CREATE INDEX idx_submissions_assignment ON submissions(assignment_id);
CREATE INDEX idx_grades_student ON grades(student_id);
CREATE INDEX idx_grades_assignment ON grades(assignment_id);
CREATE INDEX idx_daily_logs_student_date ON daily_logs(student_id, log_date);
CREATE INDEX idx_attendance_student_date ON attendance(student_id, attendance_date);
CREATE INDEX idx_period_grades_student ON period_grades(student_id);
CREATE INDEX idx_calendar_date ON calendar_events(event_date);
CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_comments_student ON comments(student_id);

-- =============================================
-- INSERT DEFAULT ASSESSMENT PERIODS (2026-2027)
-- =============================================
INSERT INTO assessment_periods (name, period_type, start_date, end_date, school_year) VALUES
('Quarter 1', 'quarter', '2026-08-12', '2026-10-16', '2026-2027'),
('Quarter 2', 'quarter', '2026-10-19', '2026-12-18', '2026-2027'),
('Midterm', 'midterm', '2026-08-12', '2026-12-18', '2026-2027'),
('Quarter 3', 'quarter', '2027-01-04', '2027-03-12', '2026-2027'),
('Quarter 4', 'quarter', '2027-03-15', '2027-05-28', '2026-2027'),
('Final', 'final', '2026-08-12', '2027-05-28', '2026-2027');

-- =============================================
-- INSERT NEVADA SCHOOL CALENDAR (2026-2027)
-- =============================================
INSERT INTO calendar_events (event_date, event_end_date, event_type, event_name, is_school_day, school_year) VALUES
('2026-09-07', NULL, 'holiday', 'Labor Day', false, '2026-2027'),
('2026-10-30', NULL, 'holiday', 'Nevada Day (Observed)', false, '2026-2027'),
('2026-11-11', NULL, 'holiday', 'Veterans Day', false, '2026-2027'),
('2026-11-26', '2026-11-27', 'break', 'Thanksgiving Break', false, '2026-2027'),
('2026-12-21', '2027-01-01', 'break', 'Winter Break', false, '2026-2027'),
('2027-01-18', NULL, 'holiday', 'Martin Luther King Jr. Day', false, '2026-2027'),
('2027-02-15', NULL, 'holiday', 'Presidents Day', false, '2026-2027'),
('2027-03-29', '2027-04-02', 'break', 'Spring Break', false, '2026-2027'),
('2027-05-31', NULL, 'holiday', 'Memorial Day', false, '2026-2027'),
('2027-06-01', '2027-08-11', 'break', 'Summer Break', false, '2026-2027');

-- =============================================
-- INSERT DEFAULT COURSES (K-12 Core + Electives)
-- =============================================

-- GRADE 1 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Grade 1 Mathematics', 1, 'Math', 'core', 'Counting, addition, subtraction, place value, basic geometry', 1.0),
('Grade 1 Language Arts', 1, 'Language Arts', 'core', 'Phonics, sight words, reading comprehension, handwriting, basic writing', 1.0),
('Grade 1 Science', 1, 'Science', 'core', 'Life science, weather, plants, animals, basic earth science', 1.0),
('Grade 1 Social Studies', 1, 'Social Studies', 'core', 'Community helpers, maps, families, American symbols', 1.0);

-- GRADE 2 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Grade 2 Mathematics', 2, 'Math', 'core', 'Addition and subtraction fluency, place value, measurement, time, money', 1.0),
('Grade 2 Language Arts', 2, 'Language Arts', 'core', 'Reading fluency, comprehension strategies, writing sentences and paragraphs', 1.0),
('Grade 2 Science', 2, 'Science', 'core', 'Matter, energy, habitats, life cycles, earth materials', 1.0),
('Grade 2 Social Studies', 2, 'Social Studies', 'core', 'Geography basics, history, citizenship, economics', 1.0);

-- GRADE 3 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Grade 3 Mathematics', 3, 'Math', 'core', 'Multiplication, division, fractions, area, perimeter', 1.0),
('Grade 3 Language Arts', 3, 'Language Arts', 'core', 'Reading comprehension, writing paragraphs, grammar, vocabulary', 1.0),
('Grade 3 Science', 3, 'Science', 'core', 'Forces, motion, ecosystems, weather patterns, rocks and minerals', 1.0),
('Grade 3 Social Studies', 3, 'Social Studies', 'core', 'Communities, government, geography, economics, history', 1.0);

-- GRADE 4 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Grade 4 Mathematics', 4, 'Math', 'core', 'Multi-digit multiplication, long division, fractions, decimals', 1.0),
('Grade 4 Language Arts', 4, 'Language Arts', 'core', 'Reading analysis, essay writing, research skills, grammar', 1.0),
('Grade 4 Science', 4, 'Science', 'core', 'Energy, waves, Earth structures, fossils, plant and animal adaptations', 1.0),
('Grade 4 Social Studies', 4, 'Social Studies', 'core', 'State history, regions of US, Native Americans, exploration', 1.0);

-- GRADE 5 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Grade 5 Mathematics', 5, 'Math', 'core', 'Decimals, fractions operations, volume, coordinate planes, order of operations', 1.0),
('Grade 5 Language Arts', 5, 'Language Arts', 'core', 'Literature analysis, persuasive writing, research papers, advanced grammar', 1.0),
('Grade 5 Science', 5, 'Science', 'core', 'Matter and its interactions, ecosystems, Earth systems, space science', 1.0),
('Grade 5 Social Studies', 5, 'Social Studies', 'core', 'American history, colonization, Revolution, Constitution, westward expansion', 1.0);

-- GRADE 6 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Grade 6 Mathematics', 6, 'Math', 'core', 'Ratios, proportions, integers, expressions, equations, statistics', 1.0),
('Grade 6 Language Arts', 6, 'Language Arts', 'core', 'Literary analysis, argumentative writing, research, speaking and listening', 1.0),
('Grade 6 Science', 6, 'Science', 'core', 'Cells, body systems, ecology, weather and climate, engineering design', 1.0),
('Grade 6 Social Studies', 6, 'Social Studies', 'core', 'Ancient civilizations, world geography, cultures', 1.0);

-- GRADE 7 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Grade 7 Mathematics', 7, 'Math', 'core', 'Pre-algebra, proportional relationships, geometry, probability, statistics', 1.0),
('Grade 7 Language Arts', 7, 'Language Arts', 'core', 'Advanced literature, narrative and expository writing, vocabulary development', 1.0),
('Grade 7 Science', 7, 'Science', 'core', 'Life science, genetics, evolution, body systems, ecology', 1.0),
('Grade 7 Social Studies', 7, 'Social Studies', 'core', 'World history, medieval to modern era, geography, civics', 1.0);

-- GRADE 8 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Grade 8 Mathematics', 8, 'Math', 'core', 'Algebra foundations, linear equations, functions, geometry, Pythagorean theorem', 1.0),
('Grade 8 Language Arts', 8, 'Language Arts', 'core', 'Critical reading, research papers, persuasive essays, literary criticism', 1.0),
('Grade 8 Science', 8, 'Science', 'core', 'Physical science, chemistry basics, physics, Earth and space science', 1.0),
('Grade 8 Social Studies', 8, 'Social Studies', 'core', 'US history, Constitution, Civil War, industrialization, civil rights', 1.0);

-- GRADE 9 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Algebra I', 9, 'Math', 'core', 'Linear equations, inequalities, polynomials, quadratics, systems of equations', 1.0),
('English 9', 9, 'Language Arts', 'core', 'Literature survey, analytical writing, grammar, vocabulary, public speaking', 1.0),
('Biology', 9, 'Science', 'core', 'Cell biology, genetics, evolution, ecology, anatomy', 1.0),
('World History', 9, 'Social Studies', 'core', 'Ancient civilizations through modern era, world cultures, geography', 1.0);

-- GRADE 10 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Geometry', 10, 'Math', 'core', 'Proofs, triangles, circles, area, volume, transformations, trigonometry intro', 1.0),
('English 10', 10, 'Language Arts', 'core', 'World literature, research writing, rhetoric, critical analysis', 1.0),
('Chemistry', 10, 'Science', 'core', 'Atomic structure, periodic table, bonding, reactions, stoichiometry', 1.0),
('US History', 10, 'Social Studies', 'core', 'Colonial era through modern America, government, economics', 1.0);

-- GRADE 11 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Algebra II', 11, 'Math', 'core', 'Advanced algebra, polynomials, rational expressions, logarithms, sequences', 1.0),
('English 11 - American Literature', 11, 'Language Arts', 'core', 'American literature, college-prep writing, SAT/ACT prep', 1.0),
('Physics', 11, 'Science', 'core', 'Mechanics, energy, waves, electricity, magnetism', 1.0),
('US Government and Economics', 11, 'Social Studies', 'core', 'Government structure, Constitution, economics, personal finance', 1.0);

-- GRADE 12 CORE
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
('Pre-Calculus', 12, 'Math', 'core', 'Advanced functions, trigonometry, limits, intro to calculus', 1.0),
('English 12 - British Literature', 12, 'Language Arts', 'core', 'British literature, college essays, advanced composition', 1.0),
('Earth and Space Science', 12, 'Science', 'core', 'Geology, oceanography, meteorology, astronomy', 1.0),
('Contemporary World Issues', 12, 'Social Studies', 'core', 'Current events, global issues, international relations, sociology', 1.0);

-- ELECTIVES (Available for grades 1-12 as appropriate)
INSERT INTO courses (name, grade_level, subject, course_type, description, credit_hours) VALUES
-- Music (all grades)
('Music Fundamentals (K-5)', 1, 'Music', 'elective', 'Rhythm, melody, singing, basic instruments, music appreciation', 0.5),
('Music Fundamentals (K-5)', 2, 'Music', 'elective', 'Rhythm, melody, singing, basic instruments, music appreciation', 0.5),
('Music Fundamentals (K-5)', 3, 'Music', 'elective', 'Rhythm, melody, singing, basic instruments, music appreciation', 0.5),
('Music Fundamentals (K-5)', 4, 'Music', 'elective', 'Rhythm, melody, singing, basic instruments, music appreciation', 0.5),
('Music Fundamentals (K-5)', 5, 'Music', 'elective', 'Rhythm, melody, singing, basic instruments, music appreciation', 0.5),
('Music Appreciation (6-8)', 6, 'Music', 'elective', 'Music history, genres, theory basics, instrument exploration', 0.5),
('Music Appreciation (6-8)', 7, 'Music', 'elective', 'Music history, genres, theory basics, instrument exploration', 0.5),
('Music Appreciation (6-8)', 8, 'Music', 'elective', 'Music history, genres, theory basics, instrument exploration', 0.5),
('Music Theory (9-12)', 9, 'Music', 'elective', 'Music theory, composition, history, performance', 0.5),
('Music Theory (9-12)', 10, 'Music', 'elective', 'Music theory, composition, history, performance', 0.5),
('Music Theory (9-12)', 11, 'Music', 'elective', 'Music theory, composition, history, performance', 0.5),
('Music Theory (9-12)', 12, 'Music', 'elective', 'Music theory, composition, history, performance', 0.5),

-- French
('French I', 9, 'French', 'elective', 'Basic French vocabulary, grammar, conversation, culture', 1.0),
('French I', 10, 'French', 'elective', 'Basic French vocabulary, grammar, conversation, culture', 1.0),
('French II', 11, 'French', 'elective', 'Intermediate French, reading, writing, conversation', 1.0),
('French II', 12, 'French', 'elective', 'Intermediate French, reading, writing, conversation', 1.0),

-- Spanish
('Spanish I', 9, 'Spanish', 'elective', 'Basic Spanish vocabulary, grammar, conversation, culture', 1.0),
('Spanish I', 10, 'Spanish', 'elective', 'Basic Spanish vocabulary, grammar, conversation, culture', 1.0),
('Spanish II', 11, 'Spanish', 'elective', 'Intermediate Spanish, reading, writing, conversation', 1.0),
('Spanish II', 12, 'Spanish', 'elective', 'Intermediate Spanish, reading, writing, conversation', 1.0),

-- Art
('Art Fundamentals (K-5)', 1, 'Art', 'elective', 'Drawing, painting, crafts, art appreciation', 0.5),
('Art Fundamentals (K-5)', 2, 'Art', 'elective', 'Drawing, painting, crafts, art appreciation', 0.5),
('Art Fundamentals (K-5)', 3, 'Art', 'elective', 'Drawing, painting, crafts, art appreciation', 0.5),
('Art Fundamentals (K-5)', 4, 'Art', 'elective', 'Drawing, painting, crafts, art appreciation', 0.5),
('Art Fundamentals (K-5)', 5, 'Art', 'elective', 'Drawing, painting, crafts, art appreciation', 0.5),
('Visual Arts (6-8)', 6, 'Art', 'elective', 'Drawing, painting, sculpture, art history', 0.5),
('Visual Arts (6-8)', 7, 'Art', 'elective', 'Drawing, painting, sculpture, art history', 0.5),
('Visual Arts (6-8)', 8, 'Art', 'elective', 'Drawing, painting, sculpture, art history', 0.5),
('Studio Art (9-12)', 9, 'Art', 'elective', 'Advanced drawing, painting, mixed media, portfolio development', 1.0),
('Studio Art (9-12)', 10, 'Art', 'elective', 'Advanced drawing, painting, mixed media, portfolio development', 1.0),
('Studio Art (9-12)', 11, 'Art', 'elective', 'Advanced drawing, painting, mixed media, portfolio development', 1.0),
('Studio Art (9-12)', 12, 'Art', 'elective', 'Advanced drawing, painting, mixed media, portfolio development', 1.0),

-- Physical Education
('Physical Education (K-5)', 1, 'PE', 'elective', 'Basic movement, fitness, coordination, team sports', 0.5),
('Physical Education (K-5)', 2, 'PE', 'elective', 'Basic movement, fitness, coordination, team sports', 0.5),
('Physical Education (K-5)', 3, 'PE', 'elective', 'Basic movement, fitness, coordination, team sports', 0.5),
('Physical Education (K-5)', 4, 'PE', 'elective', 'Basic movement, fitness, coordination, team sports', 0.5),
('Physical Education (K-5)', 5, 'PE', 'elective', 'Basic movement, fitness, coordination, team sports', 0.5),
('Physical Education (6-8)', 6, 'PE', 'elective', 'Fitness, team sports, health education', 0.5),
('Physical Education (6-8)', 7, 'PE', 'elective', 'Fitness, team sports, health education', 0.5),
('Physical Education (6-8)', 8, 'PE', 'elective', 'Fitness, team sports, health education', 0.5),
('Health and Fitness (9-12)', 9, 'PE', 'elective', 'Fitness, nutrition, health, wellness, sports', 0.5),
('Health and Fitness (9-12)', 10, 'PE', 'elective', 'Fitness, nutrition, health, wellness, sports', 0.5),
('Health and Fitness (9-12)', 11, 'PE', 'elective', 'Fitness, nutrition, health, wellness, sports', 0.5),
('Health and Fitness (9-12)', 12, 'PE', 'elective', 'Fitness, nutrition, health, wellness, sports', 0.5);

-- =============================================
-- INSERT DEFAULT ADMIN USER
-- =============================================
-- Password: admin123 (bcrypt hash)
INSERT INTO users (email, password_hash, first_name, last_name, role) VALUES
('admin@jnmhomeschool.com', '$2a$10$defaulthashchangethisaftersetup', 'Admin', 'User', 'parent');