SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS analytics_cache;
DROP TABLE IF EXISTS exam_answers;
DROP TABLE IF EXISTS exam_attempts;
DROP TABLE IF EXISTS mock_exam_questions;
DROP TABLE IF EXISTS knowledge_area_test_questions;
DROP TABLE IF EXISTS mock_exams;
DROP TABLE IF EXISTS knowledge_area_tests;
DROP TABLE IF EXISTS leaderboards;
DROP TABLE IF EXISTS player_progress;
DROP TABLE IF EXISTS scenarios;
DROP TABLE IF EXISTS exam_questions;
DROP TABLE IF EXISTS game_board;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS=1;

-- BABOK Board Game + Mock Exam Portal Schema
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','player') DEFAULT 'player',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE game_board (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tile_number INT NOT NULL UNIQUE,
  tile_type ENUM('scenario','bonus','penalty','exam_challenge','stakeholder_event') NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE scenarios (
  id INT AUTO_INCREMENT PRIMARY KEY,
  knowledge_area VARCHAR(100) NOT NULL,
  technique VARCHAR(100) DEFAULT NULL,
  difficulty_level ENUM('ECBA','CCBA','CBAP') NOT NULL,
  scenario_text TEXT NOT NULL,
  option_a VARCHAR(500) NOT NULL,
  option_b VARCHAR(500) NOT NULL,
  option_c VARCHAR(500) NOT NULL,
  option_d VARCHAR(500) NOT NULL,
  correct_option CHAR(1) NOT NULL,
  explanation TEXT NOT NULL,
  points INT DEFAULT 10,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE player_progress (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  current_tile INT DEFAULT 1,
  total_points INT DEFAULT 0,
  badges JSON DEFAULT NULL,
  streak_days INT DEFAULT 0,
  last_played DATE DEFAULT NULL,
  level ENUM('ECBA','CCBA','CBAP') DEFAULT 'ECBA',
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leaderboards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL UNIQUE,
  score INT NOT NULL,
  rank_position INT DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE exam_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  knowledge_area VARCHAR(100) NOT NULL,
  technique VARCHAR(100) DEFAULT NULL,
  difficulty_level ENUM('ECBA','CCBA','CBAP') NOT NULL,
  question_text TEXT NOT NULL,
  option_a VARCHAR(500) NOT NULL,
  option_b VARCHAR(500) NOT NULL,
  option_c VARCHAR(500) NOT NULL,
  option_d VARCHAR(500) NOT NULL,
  correct_option CHAR(1) NOT NULL,
  explanation TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE mock_exams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  duration_minutes INT NOT NULL,
  total_questions INT NOT NULL,
  difficulty_level ENUM('ECBA','CCBA','CBAP','MIXED') NOT NULL,
  knowledge_area_filter VARCHAR(100) DEFAULT NULL,
  technique_filter VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE mock_exam_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mock_exam_id INT NOT NULL,
  question_id INT NOT NULL,
  FOREIGN KEY (mock_exam_id) REFERENCES mock_exams(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON DELETE CASCADE,
  UNIQUE(mock_exam_id, question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE exam_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  mock_exam_id INT DEFAULT NULL,
  test_type ENUM('full','knowledge_area','technique','difficulty') DEFAULT 'full',
  score INT DEFAULT 0,
  total_questions INT NOT NULL,
  correct_count INT DEFAULT 0,
  time_taken INT DEFAULT 0,
  readiness_score DECIMAL(5,2) DEFAULT 0,
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (mock_exam_id) REFERENCES mock_exams(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE exam_answers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  attempt_id INT NOT NULL,
  question_id INT NOT NULL,
  selected_option CHAR(1) DEFAULT NULL,
  is_correct TINYINT(1) DEFAULT 0,
  time_spent INT DEFAULT 0,
  FOREIGN KEY (attempt_id) REFERENCES exam_attempts(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE knowledge_area_tests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  knowledge_area VARCHAR(100) NOT NULL,
  title VARCHAR(200) NOT NULL,
  duration_minutes INT NOT NULL,
  total_questions INT NOT NULL,
  difficulty_level ENUM('ECBA','CCBA','CBAP','MIXED') DEFAULT 'MIXED',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE knowledge_area_test_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  test_id INT NOT NULL,
  question_id INT NOT NULL,
  FOREIGN KEY (test_id) REFERENCES knowledge_area_tests(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE analytics_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  knowledge_area VARCHAR(100) NOT NULL,
  accuracy DECIMAL(5,2) DEFAULT 0,
  avg_time INT DEFAULT 0,
  weak_area TINYINT(1) DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;