DROP TABLE IF EXISTS beep_logs;
DROP TABLE IF EXISTS trials;
DROP TABLE IF EXISTS nasa_tlx;
DROP TABLE IF EXISTS state;
DROP TABLE IF EXISTS participants;

CREATE TABLE participants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  participant_no INT NOT NULL UNIQUE,
  participant_code VARCHAR(32) NOT NULL UNIQUE,
  group_id TINYINT NOT NULL,
  index_in_group INT NOT NULL,
  seq_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE state (
  participant_id INT PRIMARY KEY,
  phase VARCHAR(16) NOT NULL DEFAULT 'training',
  current_round TINYINT NOT NULL DEFAULT 1,
  current_trial TINYINT NOT NULL DEFAULT 1,
  finished TINYINT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE
);

CREATE TABLE trials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  participant_id INT NOT NULL,
  phase_type VARCHAR(16) NOT NULL DEFAULT 'main',
  round_no TINYINT NOT NULL,
  trial_no TINYINT NOT NULL,
  stimulus_id VARCHAR(64) NOT NULL,
  q_order_id TINYINT NOT NULL DEFAULT 0,
  bk_value INT DEFAULT NULL,
  p_value INT DEFAULT NULL,
  a_value INT DEFAULT NULL,
  d_value INT DEFAULT NULL,
  participant_submitted TINYINT NOT NULL DEFAULT 0,
  participant_time TIMESTAMP NULL DEFAULT NULL,
  operator_choice INT DEFAULT NULL,
  operator_submitted TINYINT NOT NULL DEFAULT 0,
  operator_time TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE,
  UNIQUE KEY uq_trial (participant_id, phase_type, round_no, trial_no)
);

CREATE TABLE nasa_tlx (
  participant_id INT PRIMARY KEY,
  name_field VARCHAR(120) NULL,
  task_field VARCHAR(120) NULL,
  date_field VARCHAR(40) NULL,
  mental_demand TINYINT NULL,
  physical_demand TINYINT NULL,
  temporal_demand TINYINT NULL,
  performance_score TINYINT NULL,
  effort_score TINYINT NULL,
  frustration_score TINYINT NULL,
  submitted TINYINT NOT NULL DEFAULT 0,
  submitted_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE
);

CREATE TABLE beep_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  participant_id INT NOT NULL,
  beep_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE
);
