SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS content_hypotheses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  hypothesis_type VARCHAR(80) NOT NULL,
  title VARCHAR(220) NOT NULL,
  hypothesis_text TEXT NOT NULL,
  dimension VARCHAR(80) NOT NULL,
  variant_a_pattern_id BIGINT UNSIGNED NOT NULL,
  variant_b_pattern_id BIGINT UNSIGNED NOT NULL,
  variant_a_label VARCHAR(40) NOT NULL DEFAULT 'A',
  variant_b_label VARCHAR(40) NOT NULL DEFAULT 'B',
  fingerprint CHAR(64) NOT NULL,
  status ENUM('draft','approved','archived') NOT NULL DEFAULT 'draft',
  evidence_summary LONGTEXT NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  archived_by BIGINT UNSIGNED NULL,
  archived_at DATETIME NULL,
  last_exported_to_experiment_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ch_ws_fp(workspace_id,fingerprint),
  INDEX idx_ch_campaign_status(campaign_id,status),
  CONSTRAINT fk_ch_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ch_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_ch_created_by FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_ch_pattern_a FOREIGN KEY(variant_a_pattern_id) REFERENCES content_learning_patterns(id) ON DELETE RESTRICT,
  CONSTRAINT fk_ch_pattern_b FOREIGN KEY(variant_b_pattern_id) REFERENCES content_learning_patterns(id) ON DELETE RESTRICT,
  CONSTRAINT fk_ch_approved_by FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_ch_archived_by FOREIGN KEY(archived_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS experiment_draft_payloads (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  hypothesis_id BIGINT UNSIGNED NOT NULL,
  payload LONGTEXT NOT NULL,
  status ENUM('draft','used','archived') NOT NULL DEFAULT 'draft',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_edp_ws_status(workspace_id,status),
  INDEX idx_edp_hypothesis(hypothesis_id),
  CONSTRAINT fk_edp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_edp_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_edp_hypothesis FOREIGN KEY(hypothesis_id) REFERENCES content_hypotheses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
