CREATE TABLE IF NOT EXISTS problems (
  id VARCHAR(32) NOT NULL PRIMARY KEY,
  title_en VARCHAR(255) NOT NULL,
  title_ru VARCHAR(255) NOT NULL,
  topic VARCHAR(120) NOT NULL,
  subtopic VARCHAR(160) NOT NULL,
  difficulty ENUM('intro', 'core', 'challenge') NOT NULL DEFAULT 'core',
  tags JSON NULL,
  statement_html_en MEDIUMTEXT NOT NULL,
  statement_html_ru MEDIUMTEXT NOT NULL,
  hint_html_en MEDIUMTEXT NOT NULL,
  hint_html_ru MEDIUMTEXT NOT NULL,
  solution_html_en MEDIUMTEXT NOT NULL,
  solution_html_ru MEDIUMTEXT NOT NULL,
  teacher_note_html_en MEDIUMTEXT NOT NULL,
  teacher_note_html_ru MEDIUMTEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_difficulty (difficulty),
  INDEX idx_subtopic (subtopic),
  FULLTEXT INDEX ft_problem_search (title_en, title_ru, subtopic)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('student', 'teacher', 'admin') NOT NULL DEFAULT 'student',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_email (email),
  INDEX idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;