SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS assisted_experiment_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  experiment_id BIGINT UNSIGNED NOT NULL,
  hypothesis_id BIGINT UNSIGNED NOT NULL,
  draft_payload_id BIGINT UNSIGNED NOT NULL,
  planned_days INT UNSIGNED NOT NULL DEFAULT 14,
  distribution_mode ENUM('balanced','manual') NOT NULL DEFAULT 'balanced',
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  status ENUM('approved_draft','started','completed','archived') NOT NULL DEFAULT 'approved_draft',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_aep_experiment(experiment_id),
  INDEX idx_aep_ws_status(workspace_id,status),
  INDEX idx_aep_hypothesis(hypothesis_id),
  CONSTRAINT fk_aep_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_aep_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
  CONSTRAINT fk_aep_hypothesis FOREIGN KEY(hypothesis_id) REFERENCES content_hypotheses(id) ON DELETE CASCADE,
  CONSTRAINT fk_aep_draft FOREIGN KEY(draft_payload_id) REFERENCES experiment_draft_payloads(id) ON DELETE RESTRICT,
  CONSTRAINT fk_aep_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
