-- STEM Tutor multilingual course, order, and payment-ready database schema
-- Import this in phpMyAdmin after creating your cPanel database and database user.
-- Recommended charset: utf8mb4 for Chinese, Korean, Japanese, Arabic, Russian, Vietnamese, and Spanish text.

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  parent_name VARCHAR(120) NOT NULL,
  student_name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL,
  phone VARCHAR(60),
  grade_level VARCHAR(80),
  course_interest VARCHAR(120),
  language_preference VARCHAR(80),
  message TEXT,
  lead_status VARCHAR(40) DEFAULT 'new',
  created_at DATETIME NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_students_email (email),
  INDEX idx_students_course (course_interest),
  INDEX idx_students_status (lead_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS teachers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL,
  phone VARCHAR(60),
  subject_area VARCHAR(120),
  languages VARCHAR(180),
  hourly_rate DECIMAL(10,2) DEFAULT NULL,
  bio TEXT,
  experience TEXT,
  status VARCHAR(40) DEFAULT 'applicant',
  created_at DATETIME NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_teachers_email (email),
  INDEX idx_teachers_subject (subject_area),
  INDEX idx_teachers_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  course_code VARCHAR(50) UNIQUE,
  course_title VARCHAR(180) NOT NULL,
  category VARCHAR(80) NOT NULL,
  level_name VARCHAR(80),
  language VARCHAR(120),
  delivery_mode VARCHAR(120),
  price_monthly DECIMAL(10,2) DEFAULT NULL,
  price_package DECIMAL(10,2) DEFAULT NULL,
  description TEXT,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_courses_category (category),
  INDEX idx_courses_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_id INT NULL,
  teacher_id INT NULL,
  enrollment_status VARCHAR(50) DEFAULT 'interested',
  start_date DATE NULL,
  end_date DATE NULL,
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL,
  FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
  INDEX idx_enroll_status (enrollment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS material_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL,
  phone VARCHAR(60),
  material_title VARCHAR(180) NOT NULL,
  amount DECIMAL(10,2) DEFAULT 0.00,
  payment_method VARCHAR(80),
  delivery_mode VARCHAR(80),
  payment_reference VARCHAR(180),
  customer_notes TEXT,
  order_status VARCHAR(50) DEFAULT 'pending_payment',
  public_token VARCHAR(64),
  download_link VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_material_email (email),
  INDEX idx_material_status (order_status),
  INDEX idx_material_token (public_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NULL,
  order_id INT NULL,
  payer_name VARCHAR(120),
  email VARCHAR(180),
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  payment_method VARCHAR(80),
  transaction_id VARCHAR(180),
  payment_status VARCHAR(50) DEFAULT 'pending',
  notes TEXT,
  created_at DATETIME NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL,
  FOREIGN KEY (order_id) REFERENCES material_orders(id) ON DELETE SET NULL,
  INDEX idx_payments_status (payment_status),
  INDEX idx_payments_email (email),
  INDEX idx_payments_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL,
  phone VARCHAR(60),
  topic VARCHAR(120),
  message TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_contact_email (email),
  INDEX idx_contact_topic (topic)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS digital_materials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  category VARCHAR(80),
  level_name VARCHAR(80),
  price DECIMAL(10,2) DEFAULT 0.00,
  file_path VARCHAR(255),
  description TEXT,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_materials_category (category),
  INDEX idx_materials_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  entity_type VARCHAR(80),
  entity_id INT,
  action_name VARCHAR(120),
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_logs_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO courses (course_code, course_title, category, level_name, language, delivery_mode, price_monthly, price_package, description)
VALUES
('MATH-FOUND-01','Math Foundation Package','Math','Elementary / Middle','English / Chinese / Spanish','Online Live / Offline / Hybrid',199.00,199.00,'Four-week math foundation package with small-group teaching, worksheets, and homework review.'),
('MATH-SAT-01','SAT/AP Math Prep','Math','High School','English / Chinese','Online Live / Hybrid',260.00,799.00,'SAT math, AP calculus foundation, practice exams, and strategy.'),
('SCI-STEM-01','Science and STEM Lab','Science / STEM','Middle / High School','English / Chinese','Online Live / Offline Workshop',240.00,699.00,'Science experiments, robotics ideas, engineering thinking, and project reports.'),
('AI-STEM-01','STEM + AI Project Package','AI & Coding','Elementary / Middle / High School','English / Chinese','Online Live + Project',299.00,299.00,'AI-guided STEM project classes, science report template, and portfolio-ready student work.'),
('ENG-ESL-01','English / ESL Package','English','All Levels','English / Chinese / Spanish / Vietnamese','Online Live',229.00,229.00,'Conversation, listening, pronunciation, writing, and confidence-building.'),
('PY-DB-WEB-01','Python, Database, and Web Design','AI & Coding','Middle / High School','English','Online Live + Project',280.00,850.00,'Python basics, MySQL ideas, website forms, and mini-projects.'),
('COLLEGE-ESSAY-01','College Essay Guide and Coaching','College Prep','High School','English / Chinese','1-on-1 / Small Group',300.00,950.00,'Essay brainstorming, application story, editing, and interview coaching.'),
('MUSIC-ART-01','Music / Drawing / Painting Package','Music & Arts','Beginner / Intermediate','English / Chinese','Private Online / Offline',260.00,260.00,'Violin, piano, music theory, drawing, painting, creative portfolio, and AI art ideas.');

INSERT IGNORE INTO digital_materials (title, category, level_name, price, file_path, description)
VALUES
('AI for Kids Starter Pack','AI & Coding','Beginner',29.00,'materials/ai_for_kids_starter_pack.pdf','Introductory AI lessons, safe prompt practice, and project worksheets.'),
('Algebra Foundation Worksheets','Math','Middle School',19.00,'materials/algebra_foundation_worksheets.pdf','Practice worksheets for algebra basics and problem solving.'),
('ESL Speaking and Listening Pack','English','All Levels',25.00,'materials/esl_speaking_listening_pack.pdf','Speaking prompts, listening practice plan, and vocabulary exercises.'),
('College Essay Guide','College Prep','High School',39.00,'materials/college_essay_guide.pdf','Essay structure, story development, and editing checklist.');