-- Bazani tanlang: c1065_fizik (yoki sizniki)
-- 1) users (foydalanuvchilar)
CREATE TABLE IF NOT EXISTS users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL UNIQUE,
  username VARCHAR(100),
  full_name VARCHAR(200),
  skill_score INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) questions (savollar)
CREATE TABLE IF NOT EXISTS questions (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  text TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) options (har bir savolga variantlar)
CREATE TABLE IF NOT EXISTS options (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  question_id INT NOT NULL,
  label CHAR(1) NOT NULL,       -- 'A','B','C','D'
  text VARCHAR(500) NOT NULL,
  is_correct TINYINT(1) DEFAULT 0,
  CONSTRAINT fk_opt_q FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) admin_states (admin qo'shish jarayonining vaqtinchalik holati)
CREATE TABLE IF NOT EXISTS admin_states (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  admin_id BIGINT NOT NULL,
  step VARCHAR(50) NOT NULL,    -- e.g. 'await_question','await_A','await_B',...
  data JSON DEFAULT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;