SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS content_learning_patterns (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  pattern_type ENUM('headline','body','cta') NOT NULL,
  pattern_value LONGTEXT NOT NULL,
  fingerprint CHAR(64) NOT NULL,
  evidence_status VARCHAR(50) NOT NULL,
  score DECIMAL(8,2) NOT NULL DEFAULT 0,
  sample_count INT UNSIGNED NOT NULL DEFAULT 1,
  source_experiment_id BIGINT UNSIGNED NOT NULL,
  source_variant_id BIGINT UNSIGNED NOT NULL,
  source_decision_id BIGINT UNSIGNED NOT NULL,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  last_learned_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_clp_ws_fp(workspace_id,fingerprint),
  INDEX idx_clp_campaign_type(campaign_id,pattern_type),
  INDEX idx_clp_score(score),
  CONSTRAINT fk_clp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_clp_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_clp_experiment FOREIGN KEY(source_experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
  CONSTRAINT fk_clp_variant FOREIGN KEY(source_variant_id) REFERENCES ab_experiment_variants(id) ON DELETE CASCADE,
  CONSTRAINT fk_clp_decision FOREIGN KEY(source_decision_id) REFERENCES experiment_decisions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS content_learning_suggestions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  headline TEXT NULL,
  body LONGTEXT NULL,
  cta VARCHAR(255) NULL,
  source_headline_pattern_id BIGINT UNSIGNED NULL,
  source_body_pattern_id BIGINT UNSIGNED NULL,
  source_cta_pattern_id BIGINT UNSIGNED NULL,
  fingerprint CHAR(64) NOT NULL,
  status ENUM('saved','archived') NOT NULL DEFAULT 'saved',
  last_exported_to_editor_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cls_ws_fp(workspace_id,fingerprint),
  INDEX idx_cls_campaign_status(campaign_id,status),
  CONSTRAINT fk_cls_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_cls_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_cls_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_cls_h FOREIGN KEY(source_headline_pattern_id) REFERENCES content_learning_patterns(id) ON DELETE SET NULL,
  CONSTRAINT fk_cls_b FOREIGN KEY(source_body_pattern_id) REFERENCES content_learning_patterns(id) ON DELETE SET NULL,
  CONSTRAINT fk_cls_c FOREIGN KEY(source_cta_pattern_id) REFERENCES content_learning_patterns(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
