-- Create database (run once in your MySQL/MariaDB)
-- CREATE DATABASE adaptive_php CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE adaptive_php;

-- Accounts table for authentication
CREATE TABLE IF NOT EXISTS accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_md5 CHAR(32) NOT NULL,
  role ENUM('student','admin') NOT NULL DEFAULT 'student',
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id VARCHAR(128) NOT NULL UNIQUE,
  account_id INT NULL UNIQUE,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_users_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assessments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  started_at DATETIME NOT NULL,
  ended_at DATETIME NULL,
  total_easy INT NOT NULL DEFAULT 0,
  total_medium INT NOT NULL DEFAULT 0,
  total_hard INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_assess_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  question_id VARCHAR(64) NOT NULL,
  topic VARCHAR(128) NULL,
  difficulty ENUM('easy','medium','hard') NOT NULL,
  type ENUM('multiple_choice','interactive') NOT NULL,
  is_correct TINYINT(1) NOT NULL DEFAULT 0,
  mode ENUM('assessment','practice') NOT NULL,
  assessment_id INT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_attempts_user (user_id),
  INDEX idx_attempts_assessment (assessment_id),
  INDEX idx_attempts_question (question_id),
  CONSTRAINT fk_attempts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_attempts_assessment FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- No questions table is required for now; the client provides bank metadata.
-- You can add a `questions` table later if you want server-driven questions.

-- Questions table to serve questions from server
CREATE TABLE IF NOT EXISTS questions (
  id VARCHAR(64) PRIMARY KEY,
  type ENUM('multiple_choice','interactive') NOT NULL,
  topic VARCHAR(128) NOT NULL,
  title VARCHAR(255) NOT NULL,
  difficulty ENUM('easy','medium','hard') NOT NULL,
  explanation TEXT NULL,
  code TEXT NULL,
  question TEXT NULL,
  options JSON NULL,
  correct_answer TEXT NULL,
  task TEXT NULL,
  initial_code TEXT NULL,
  solution TEXT NULL,
  expected_output TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Feedback from students on questions
CREATE TABLE IF NOT EXISTS feedbacks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NULL,
  user_id INT NULL,
  question_id VARCHAR(64) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('open','resolved') NOT NULL DEFAULT 'open',
  admin_note TEXT NULL,
  created_at DATETIME NOT NULL,
  resolved_at DATETIME NULL,
  INDEX idx_feedbacks_question (question_id),
  INDEX idx_feedbacks_status (status),
  CONSTRAINT fk_feedback_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL,
  CONSTRAINT fk_feedback_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
