SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','blocked','pending') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS workspaces (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_user_id BIGINT UNSIGNED NULL,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  timezone VARCHAR(80) NOT NULL DEFAULT 'America/Sao_Paulo',
  status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_workspaces_owner FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS workspace_users (
  workspace_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role ENUM('owner','admin','editor','analyst') NOT NULL DEFAULT 'editor',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(workspace_id,user_id),
  CONSTRAINT fk_wu_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_wu_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL,
  type ENUM('digital','physical','app','site','service','affiliate') NOT NULL DEFAULT 'digital',
  description TEXT NULL,
  target_audience TEXT NULL,
  destination_url TEXT NULL,
  price DECIMAL(12,2) NULL,
  status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_product_slug(workspace_id,slug),
  CONSTRAINT fk_products_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaigns (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  name VARCHAR(180) NOT NULL,
  objective ENUM('traffic','clicks','leads','sales','app_installs','engagement','awareness') NOT NULL DEFAULT 'traffic',
  status ENUM('draft','active','paused','completed','archived') NOT NULL DEFAULT 'draft',
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  daily_post_limit INT UNSIGNED NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_campaign_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_campaign_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS creatives (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  name VARCHAR(180) NOT NULL,
  type ENUM('text','image','video','carousel','template') NOT NULL,
  headline TEXT NULL,
  body LONGTEXT NULL,
  cta VARCHAR(120) NULL,
  media_path VARCHAR(500) NULL,
  media_alt VARCHAR(255) NULL,
  status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_creative_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_creative_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS channels (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  publishing_mode ENUM('api','manual_queue','hybrid') NOT NULL DEFAULT 'manual_queue',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO channels(code,name,publishing_mode) VALUES
('facebook_page','Facebook Page','api'),
('facebook_group','Facebook Group','manual_queue'),
('instagram','Instagram','api'),
('website','Website','api'),
('other','Outro','manual_queue');

CREATE TABLE IF NOT EXISTS destinations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  channel_id BIGINT UNSIGNED NOT NULL,
  integration_id BIGINT UNSIGNED NULL,
  name VARCHAR(180) NOT NULL,
  external_id VARCHAR(255) NULL,
  external_url TEXT NULL,
  category VARCHAR(160) NULL,
  audience_size BIGINT UNSIGNED NULL,
  rules_notes TEXT NULL,
  requires_approval TINYINT(1) NOT NULL DEFAULT 0,
  min_interval_minutes INT UNSIGNED NULL,
  status ENUM('active','paused','blocked','archived') NOT NULL DEFAULT 'active',
  health_score DECIMAL(8,2) NOT NULL DEFAULT 100,
  last_success_at DATETIME NULL,
  last_failure_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_dest_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_dest_channel FOREIGN KEY(channel_id) REFERENCES channels(id),
  CONSTRAINT fk_dest_integration FOREIGN KEY(integration_id) REFERENCES integrations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaign_destinations (
  campaign_id BIGINT UNSIGNED NOT NULL,
  destination_id BIGINT UNSIGNED NOT NULL,
  status ENUM('active','paused') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(campaign_id,destination_id),
  CONSTRAINT fk_cd_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_cd_destination FOREIGN KEY(destination_id) REFERENCES destinations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS schedules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  timezone VARCHAR(80) NOT NULL DEFAULT 'America/Sao_Paulo',
  days_of_week VARCHAR(30) NULL,
  start_time TIME NULL,
  end_time TIME NULL,
  interval_min_minutes INT UNSIGNED NULL,
  interval_max_minutes INT UNSIGNED NULL,
  max_posts_per_day INT UNSIGNED NULL,
  status ENUM('active','paused') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_schedules_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_schedules_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS content_components (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  product_id BIGINT UNSIGNED NULL,
  component_type ENUM('headline','body','cta') NOT NULL,
  type ENUM('headline','body','cta') NULL,
  name VARCHAR(180) NULL,
  content LONGTEXT NOT NULL,
  tags VARCHAR(500) NULL,
  weight INT UNSIGNED NOT NULL DEFAULT 100,
  status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_cc_lookup(workspace_id,campaign_id,component_type,status),
  CONSTRAINT fk_cc_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_cc_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
  CONSTRAINT fk_cc_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS media_library (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  product_id BIGINT UNSIGNED NULL,
  name VARCHAR(180) NULL,
  original_name VARCHAR(255) NULL,
  file_path VARCHAR(500) NULL,
  storage_path VARCHAR(500) NULL,
  mime_type VARCHAR(120) NULL,
  file_size BIGINT UNSIGNED NULL,
  width INT UNSIGNED NULL,
  height INT UNSIGNED NULL,
  sha256 CHAR(64) NULL,
  alt_text VARCHAR(255) NULL,
  status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_media_sha(workspace_id,sha256),
  CONSTRAINT fk_ml_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ml_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
  CONSTRAINT fk_ml_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS content_variants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NULL,
  headline_component_id BIGINT UNSIGNED NULL,
  body_component_id BIGINT UNSIGNED NULL,
  cta_component_id BIGINT UNSIGNED NULL,
  media_id BIGINT UNSIGNED NULL,
  signature CHAR(64) NULL,
  fingerprint CHAR(64) NULL,
  rendered_headline TEXT NULL,
  rendered_body LONGTEXT NULL,
  rendered_cta VARCHAR(255) NULL,
  times_scheduled INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
  performance_score DECIMAL(12,4) NOT NULL DEFAULT 0,
  last_selected_at DATETIME NULL,
  generated_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_variant_media(media_id),
  INDEX idx_variant_campaign(workspace_id,campaign_id,status),
  CONSTRAINT fk_cv_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_cv_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_cv_headline FOREIGN KEY(headline_component_id) REFERENCES content_components(id) ON DELETE SET NULL,
  CONSTRAINT fk_cv_body FOREIGN KEY(body_component_id) REFERENCES content_components(id) ON DELETE SET NULL,
  CONSTRAINT fk_cv_cta FOREIGN KEY(cta_component_id) REFERENCES content_components(id) ON DELETE SET NULL,
  CONSTRAINT fk_cv_media FOREIGN KEY(media_id) REFERENCES media_library(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tracked_links (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  product_id BIGINT UNSIGNED NULL,
  code VARCHAR(80) NOT NULL UNIQUE,
  destination_url TEXT NOT NULL,
  utm_source VARCHAR(150) NULL,
  utm_medium VARCHAR(150) NULL,
  utm_campaign VARCHAR(150) NULL,
  utm_content VARCHAR(150) NULL,
  status ENUM('active','paused') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tl_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_tl_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
  CONSTRAINT fk_tl_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS scheduled_posts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  destination_id BIGINT UNSIGNED NOT NULL,
  creative_id BIGINT UNSIGNED NULL,
  content_variant_id BIGINT UNSIGNED NULL,
  media_id BIGINT UNSIGNED NULL,
  tracked_link_id BIGINT UNSIGNED NULL,
  ab_experiment_id BIGINT UNSIGNED NULL,
  ab_variant_id BIGINT UNSIGNED NULL,
  scheduled_at DATETIME NOT NULL,
  rendered_headline TEXT NULL,
  rendered_body LONGTEXT NULL,
  rendered_cta VARCHAR(255) NULL,
  rendered_media_path VARCHAR(500) NULL,
  tracking_url TEXT NULL,
  status ENUM('scheduled','queued','published','failed','cancelled','manual_review') NOT NULL DEFAULT 'scheduled',
  published_at DATETIME NULL,
  publication_confirmed_at DATETIME NULL,
  external_post_id VARCHAR(255) NULL,
  external_post_url TEXT NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_scheduled_due(status,scheduled_at),
  INDEX idx_sp_ab_experiment(ab_experiment_id),
  INDEX idx_sp_ab_variant(ab_variant_id),
  CONSTRAINT fk_sp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_sp_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_sp_destination FOREIGN KEY(destination_id) REFERENCES destinations(id) ON DELETE CASCADE,
  CONSTRAINT fk_sp_creative FOREIGN KEY(creative_id) REFERENCES creatives(id) ON DELETE SET NULL,
  CONSTRAINT fk_sp_variant FOREIGN KEY(content_variant_id) REFERENCES content_variants(id) ON DELETE SET NULL,
  CONSTRAINT fk_sp_media FOREIGN KEY(media_id) REFERENCES media_library(id) ON DELETE SET NULL,
  CONSTRAINT fk_sp_tracking FOREIGN KEY(tracked_link_id) REFERENCES tracked_links(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS queue_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  scheduled_post_id BIGINT UNSIGNED NULL,
  job_type VARCHAR(80) NOT NULL,
  status ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
  payload JSON NULL,
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts INT UNSIGNED NOT NULL DEFAULT 5,
  available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  locked_at DATETIME NULL,
  finished_at DATETIME NULL,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_queue_ready(status,available_at),
  UNIQUE KEY uq_queue_post_job(scheduled_post_id,job_type),
  CONSTRAINT fk_q_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_q_scheduled FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS link_clicks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tracked_link_id BIGINT UNSIGNED NOT NULL,
  scheduled_post_id BIGINT UNSIGNED NULL,
  clicked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip_hash CHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  referrer TEXT NULL,
  country VARCHAR(2) NULL,
  device_type VARCHAR(40) NULL,
  session_key VARCHAR(120) NULL,
  INDEX idx_click_link_time(tracked_link_id,clicked_at),
  CONSTRAINT fk_click_link FOREIGN KEY(tracked_link_id) REFERENCES tracked_links(id) ON DELETE CASCADE,
  CONSTRAINT fk_click_post FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conversions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  tracked_link_id BIGINT UNSIGNED NULL,
  ab_experiment_id BIGINT UNSIGNED NULL,
  ab_variant_id BIGINT UNSIGNED NULL,
  scheduled_post_id BIGINT UNSIGNED NULL,
  product_id BIGINT UNSIGNED NULL,
  conversion_type ENUM('sale','lead','signup','install','custom') NOT NULL DEFAULT 'sale',
  external_order_id VARCHAR(190) NULL,
  value DECIMAL(12,2) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'BRL',
  converted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  metadata JSON NULL,
  INDEX idx_conversion_workspace_time(workspace_id,converted_at),
  CONSTRAINT fk_conv_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_conv_link FOREIGN KEY(tracked_link_id) REFERENCES tracked_links(id) ON DELETE SET NULL,
  CONSTRAINT fk_conv_post FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE SET NULL,
  CONSTRAINT fk_conv_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  event VARCHAR(150) NOT NULL,
  entity_type VARCHAR(100) NULL,
  entity_id BIGINT UNSIGNED NULL,
  ip_hash CHAR(64) NULL,
  context JSON NULL,
  correlation_id VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_workspace_time(workspace_id,created_at),
  CONSTRAINT fk_audit_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS destination_policies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  destination_id BIGINT UNSIGNED NOT NULL,
  allowed_days VARCHAR(30) NULL,
  allowed_start_time TIME NULL,
  allowed_end_time TIME NULL,
  max_posts_per_day INT UNSIGNED NOT NULL DEFAULT 0,
  similarity_threshold DECIMAL(5,4) NOT NULL DEFAULT 0.8500,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_destination_policy(destination_id),
  CONSTRAINT fk_dp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_dp_destination FOREIGN KEY(destination_id) REFERENCES destinations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS webhook_keys (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  key_hash CHAR(64) NOT NULL UNIQUE,
  key_prefix VARCHAR(12) NOT NULL,
  status ENUM('active','revoked') NOT NULL DEFAULT 'active',
  last_used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_wk_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS integrations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  channel_id BIGINT UNSIGNED NOT NULL,
  integration_id BIGINT UNSIGNED NULL,
  name VARCHAR(180) NOT NULL,
  adapter VARCHAR(160) NOT NULL,
  auth_type ENUM('oauth','token','api_key','basic','none') NOT NULL DEFAULT 'none',
  status ENUM('active','inactive','warning','error') NOT NULL DEFAULT 'inactive',
  credentials_encrypted LONGTEXT NULL,
  settings JSON NULL,
  last_tested_at DATETIME NULL,
  last_success_at DATETIME NULL,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_integrations_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_integrations_channel FOREIGN KEY(channel_id) REFERENCES channels(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS publication_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  scheduled_post_id BIGINT UNSIGNED NOT NULL,
  integration_id BIGINT UNSIGNED NULL,
  attempt_no INT UNSIGNED NOT NULL DEFAULT 1,
  result_status VARCHAR(80) NOT NULL,
  response_code VARCHAR(80) NULL,
  response_excerpt TEXT NULL,
  remote_post_id VARCHAR(255) NULL,
  correlation_id VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pub_attempt_post(scheduled_post_id,created_at),
  CONSTRAINT fk_pa_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_post FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_integration FOREIGN KEY(integration_id) REFERENCES integrations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaign_automation_settings (
  campaign_id BIGINT UNSIGNED PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  auto_compose TINYINT(1) NOT NULL DEFAULT 0,
  auto_schedule TINYINT(1) NOT NULL DEFAULT 0,
  prefer_winners TINYINT(1) NOT NULL DEFAULT 1,
  exploration_percent TINYINT UNSIGNED NOT NULL DEFAULT 20,
  winner_min_uses INT UNSIGNED NOT NULL DEFAULT 5,
  winner_min_conversions INT UNSIGNED NOT NULL DEFAULT 1,
  require_manual_approval TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_cas_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_cas_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  type ENUM('info','success','warning','error') NOT NULL DEFAULT 'info',
  title VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  action_url TEXT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  read_at DATETIME NULL,
  source_type VARCHAR(80) NULL,
  source_id BIGINT UNSIGNED NULL,
  source_key VARCHAR(180) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_notifications_ws_read(workspace_id,is_read,created_at),
  INDEX idx_notification_source(workspace_id,source_type,source_id),
  CONSTRAINT fk_notifications_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS content_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  name VARCHAR(180) NOT NULL,
  headline_template TEXT NULL,
  body_template LONGTEXT NULL,
  cta_template VARCHAR(255) NULL,
  status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ct_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ct_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS import_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  import_type VARCHAR(80) NOT NULL,
  original_filename VARCHAR(255) NULL,
  inserted_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_count INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('processing','completed','failed') NOT NULL DEFAULT 'processing',
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  CONSTRAINT fk_ib_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_preferences (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  dashboard_layout JSON NULL,
  items_per_page INT UNSIGNED NOT NULL DEFAULT 25,
  timezone VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_up_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_up_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_exports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  report_type VARCHAR(100) NOT NULL,
  format ENUM('csv','json') NOT NULL DEFAULT 'csv',
  file_path VARCHAR(500) NOT NULL,
  status ENUM('ready','failed','expired') NOT NULL DEFAULT 'ready',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NULL,
  CONSTRAINT fk_re_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_re_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cron_heartbeats (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  worker_name VARCHAR(120) NOT NULL,
  host_name VARCHAR(190) NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  processed_jobs INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('running','ok','error') NOT NULL DEFAULT 'running',
  message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_heartbeat_worker(worker_name,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email_hash CHAR(64) NOT NULL,
  ip_hash CHAR(64) NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_login_email_time(email_hash,attempted_at),
  INDEX idx_login_ip_time(ip_hash,attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS operator_actions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  scheduled_post_id BIGINT UNSIGNED NULL,
  action_type ENUM('copy','open_destination','confirm_publish','cancel') NOT NULL,
  context JSON NULL,
  correlation_id VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_oa_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_oa_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_oa_post FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  context JSON NULL,
  correlation_id VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_ws_time(workspace_id,created_at),
  INDEX idx_audit_entity(entity_type,entity_id),
  CONSTRAINT fk_ae_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ae_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS schema_migrations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  migration_name VARCHAR(255) NOT NULL UNIQUE,
  checksum CHAR(64) NULL,
  status ENUM('applied','failed') NOT NULL DEFAULT 'applied',
  applied_at DATETIME NULL,
  error_message TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_prt_user(user_id),
  INDEX idx_prt_exp(expires_at),
  CONSTRAINT fk_prt_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS smtp_settings (
  workspace_id BIGINT UNSIGNED PRIMARY KEY,
  host VARCHAR(255) NOT NULL,
  port INT UNSIGNED NOT NULL DEFAULT 587,
  encryption ENUM('tls','ssl','none') NOT NULL DEFAULT 'tls',
  from_email VARCHAR(255) NOT NULL,
  from_name VARCHAR(180) NULL,
  credentials_encrypted LONGTEXT NULL,
  status ENUM('active','inactive','error') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_smtp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_health_checks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  check_name VARCHAR(150) NOT NULL,
  status ENUM('ok','warning','error') NOT NULL,
  details TEXT NULL,
  checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_health_name_time(check_name,checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rate_limit_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bucket VARCHAR(80) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_rate_bucket_key_time(bucket,key_hash,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS onboarding_progress (
  workspace_id BIGINT UNSIGNED PRIMARY KEY,
  dismissed_at DATETIME NULL,
  completed_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_onboarding_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS structured_log_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  level ENUM('debug','info','warning','error','critical') NOT NULL DEFAULT 'info',
  message VARCHAR(255) NOT NULL,
  context JSON NULL,
  correlation_id VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_log_ws_time(workspace_id,created_at),
  INDEX idx_log_level_time(level,created_at),
  CONSTRAINT fk_sle_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE SET NULL,
  CONSTRAINT fk_sle_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS saved_filters (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  module VARCHAR(80) NOT NULL,
  name VARCHAR(120) NOT NULL,
  filters_json JSON NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_saved_filter_user_module(workspace_id,user_id,module),
  CONSTRAINT fk_sf_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_sf_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS integration_http_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NULL,
  integration_id BIGINT UNSIGNED NULL,
  adapter VARCHAR(120) NOT NULL,
  method VARCHAR(12) NOT NULL,
  url VARCHAR(500) NOT NULL,
  http_status INT NULL,
  request_meta JSON NULL,
  response_meta JSON NULL,
  duration_ms INT NULL,
  correlation_id VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ihttp_workspace_time(workspace_id,created_at),
  INDEX idx_ihttp_integration_time(integration_id,created_at),
  INDEX idx_ihttp_adapter_time(adapter,created_at),
  CONSTRAINT fk_ihttp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE SET NULL,
  CONSTRAINT fk_ihttp_integration FOREIGN KEY(integration_id) REFERENCES integrations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS maintenance_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  result_json JSON NULL,
  duration_ms INT NULL,
  triggered_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_maintenance_ws_time(workspace_id,created_at),
  CONSTRAINT fk_mr_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE SET NULL,
  CONSTRAINT fk_mr_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS backup_settings (
  workspace_id BIGINT UNSIGNED PRIMARY KEY,
  keep_count INT NOT NULL DEFAULT 10,
  max_age_days INT NOT NULL DEFAULT 30,
  allow_restore TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_backup_settings_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS system_test_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NULL,
  test_suite VARCHAR(120) NOT NULL,
  status ENUM('ok','failed') NOT NULL,
  result_json JSON NULL,
  duration_ms INT NULL,
  triggered_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_system_test_time(test_suite,created_at),
  CONSTRAINT fk_str_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS system_incidents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  dedupe_key VARCHAR(190) NULL,
  source VARCHAR(100) NOT NULL,
  severity ENUM('info','warning','error','critical') NOT NULL DEFAULT 'warning',
  title VARCHAR(190) NOT NULL,
  details TEXT NULL,
  status ENUM('open','acknowledged','resolved') NOT NULL DEFAULT 'open',
  occurrences INT UNSIGNED NOT NULL DEFAULT 1,
  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME NULL,
  acknowledged_by_user_id BIGINT UNSIGNED NULL,
  acknowledged_at DATETIME NULL,
  resolved_by_user_id BIGINT UNSIGNED NULL,
  resolved_at DATETIME NULL,
  resolution_note TEXT NULL,
  response_sla_breached_at DATETIME NULL,
  resolution_sla_breached_at DATETIME NULL,
  escalated_at DATETIME NULL,
  INDEX idx_incident_workspace_status(workspace_id,status,opened_at),
  INDEX idx_incident_dedupe(workspace_id,dedupe_key,status),
  CONSTRAINT fk_incident_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_incident_ack_user FOREIGN KEY(acknowledged_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_incident_res_user FOREIGN KEY(resolved_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS incident_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  incident_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  note TEXT NULL,
  context_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_incident_history_incident(incident_id,created_at),
  CONSTRAINT fk_ih_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ih_incident FOREIGN KEY(incident_id) REFERENCES system_incidents(id) ON DELETE CASCADE,
  CONSTRAINT fk_ih_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sla_policies (
  workspace_id BIGINT UNSIGNED PRIMARY KEY,
  critical_response_minutes INT UNSIGNED NOT NULL DEFAULT 15,
  error_response_minutes INT UNSIGNED NOT NULL DEFAULT 60,
  warning_response_minutes INT UNSIGNED NOT NULL DEFAULT 240,
  critical_resolve_minutes INT UNSIGNED NOT NULL DEFAULT 120,
  error_resolve_minutes INT UNSIGNED NOT NULL DEFAULT 480,
  warning_resolve_minutes INT UNSIGNED NOT NULL DEFAULT 1440,
  auto_escalate TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sla_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS maintenance_windows (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  source VARCHAR(100) NOT NULL DEFAULT 'all',
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  notes TEXT NULL,
  status ENUM('active','cancelled','completed') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_mw_workspace_time(workspace_id,starts_at,ends_at),
  CONSTRAINT fk_mw_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS sla_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  period_date DATE NOT NULL,
  uptime_percent DECIMAL(8,4) NOT NULL DEFAULT 100,
  sla_compliance_percent DECIMAL(8,4) NOT NULL DEFAULT 100,
  mtta_minutes DECIMAL(12,2) NOT NULL DEFAULT 0,
  mttr_minutes DECIMAL(12,2) NOT NULL DEFAULT 0,
  incidents_total INT UNSIGNED NOT NULL DEFAULT 0,
  incidents_critical INT UNSIGNED NOT NULL DEFAULT 0,
  sla_breaches INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sla_snapshot(workspace_id,period_date),
  CONSTRAINT fk_ss_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS observability_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  active_incidents INT UNSIGNED NOT NULL DEFAULT 0,
  failed_jobs_24h INT UNSIGNED NOT NULL DEFAULT 0,
  integration_issues INT UNSIGNED NOT NULL DEFAULT 0,
  avg_adapter_latency_ms DECIMAL(12,2) NOT NULL DEFAULT 0,
  unread_notifications INT UNSIGNED NOT NULL DEFAULT 0,
  INDEX idx_obs_snapshot_ws_time(workspace_id,captured_at),
  CONSTRAINT fk_obs_snapshot_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS oauth_states (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(80) NOT NULL,
  state_hash CHAR(64) NOT NULL,
  redirect_uri VARCHAR(500) NOT NULL,
  expires_at DATETIME NOT NULL,
  consumed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_oauth_state_hash(state_hash),
  INDEX idx_oauth_state_lookup(workspace_id,user_id,provider,expires_at),
  CONSTRAINT fk_oauth_state_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_oauth_state_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS publication_control_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  scheduled_post_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action ENUM('retry','cancel') NOT NULL,
  previous_status VARCHAR(40) NULL,
  new_status VARCHAR(40) NULL,
  note VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pce_post_time(scheduled_post_id,created_at),
  CONSTRAINT fk_pce_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pce_post FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_pce_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS post_editor_drafts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  payload JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_post_editor_draft(workspace_id,user_id),
  CONSTRAINT fk_ped_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ped_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS post_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  headline VARCHAR(500) NULL,
  body LONGTEXT NULL,
  cta VARCHAR(255) NULL,
  default_link TEXT NULL,
  default_image_url TEXT NULL,
  usage_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_used_at DATETIME NULL,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_post_templates_ws_status(workspace_id,status),
  CONSTRAINT fk_pt_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS bulk_plan_previews (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  payload LONGTEXT NOT NULL,
  expires_at DATETIME NOT NULL,
  consumed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_bulk_preview_token(token_hash),
  INDEX idx_bulk_preview_ws_exp(workspace_id,expires_at),
  CONSTRAINT fk_bpp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS editorial_calendar_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  scheduled_post_id BIGINT UNSIGNED NOT NULL,
  action ENUM('move') NOT NULL,
  new_scheduled_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ece_post_time(scheduled_post_id,created_at),
  CONSTRAINT fk_ece_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ece_post FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS executive_dashboard_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  payload LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_eds_ws_period(workspace_id,period_from,period_to),
  CONSTRAINT fk_eds_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS campaign_costs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  cost_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'BRL',
  source VARCHAR(120) NULL,
  note VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_campaign_costs_ws_date(workspace_id,cost_date),
  INDEX idx_campaign_costs_campaign_date(campaign_id,cost_date),
  CONSTRAINT fk_ccost_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ccost_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS performance_goals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  metric ENUM('revenue','conversions','cpa','roi','roas') NOT NULL,
  operator ENUM('gte','lte') NOT NULL DEFAULT 'gte',
  target_value DECIMAL(16,4) NOT NULL,
  period_days INT UNSIGNED NOT NULL DEFAULT 30,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_perf_goal(workspace_id,campaign_id,metric),
  INDEX idx_perf_goal_ws_status(workspace_id,status),
  CONSTRAINT fk_pg_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pg_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS performance_goal_evaluations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  goal_id BIGINT UNSIGNED NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  current_value DECIMAL(16,4) NULL,
  status ENUM('met','missed','unavailable') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pge_goal_time(goal_id,created_at),
  CONSTRAINT fk_pge_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pge_goal FOREIGN KEY(goal_id) REFERENCES performance_goals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS performance_alerts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  goal_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  metric ENUM('revenue','conversions','cpa','roi','roas') NOT NULL,
  current_value DECIMAL(16,4) NULL,
  target_value DECIMAL(16,4) NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  status ENUM('open','resolved') NOT NULL DEFAULT 'open',
  first_triggered_at DATETIME NOT NULL,
  last_triggered_at DATETIME NOT NULL,
  resolved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_perf_alert_open(goal_id,status),
  INDEX idx_perf_alert_ws_status(workspace_id,status),
  CONSTRAINT fk_pa_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_goal FOREIGN KEY(goal_id) REFERENCES performance_goals(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaign_optimization_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  priority_score INT UNSIGNED NOT NULL DEFAULT 0,
  needs_attention TINYINT(1) NOT NULL DEFAULT 0,
  payload LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_cos_ws_time(workspace_id,created_at),
  INDEX idx_cos_campaign_time(campaign_id,created_at),
  CONSTRAINT fk_cos_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_cos_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS optimization_actions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  recommendation_key VARCHAR(120) NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  status ENUM('acknowledged') NOT NULL DEFAULT 'acknowledged',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_opt_action(workspace_id,campaign_id,recommendation_key),
  INDEX idx_opt_action_ws_campaign(workspace_id,campaign_id),
  CONSTRAINT fk_oa_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_oa_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_oa_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS content_intelligence_suggestions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  headline TEXT NULL,
  body LONGTEXT NULL,
  cta VARCHAR(255) NULL,
  link TEXT NULL,
  fingerprint CHAR(64) NOT NULL,
  status ENUM('saved','used','archived') NOT NULL DEFAULT 'saved',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cis_fingerprint(workspace_id,fingerprint),
  INDEX idx_cis_campaign_status(campaign_id,status),
  CONSTRAINT fk_cis_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_cis_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_cis_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS editor_suggestion_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  suggestion_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  apply_mode ENUM('headline','body','cta','link','all') NOT NULL DEFAULT 'all',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ese_ws_time(workspace_id,created_at),
  INDEX idx_ese_suggestion(suggestion_id),
  CONSTRAINT fk_ese_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ese_suggestion FOREIGN KEY(suggestion_id) REFERENCES content_intelligence_suggestions(id) ON DELETE CASCADE,
  CONSTRAINT fk_ese_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ab_experiments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  name VARCHAR(180) NOT NULL,
  success_metric ENUM('click_rate','conversion_rate','conversion_value') NOT NULL DEFAULT 'conversion_rate',
  min_published_per_variant INT UNSIGNED NOT NULL DEFAULT 10,
  min_clicks_per_variant INT UNSIGNED NOT NULL DEFAULT 20,
  status ENUM('draft','running','completed','archived') NOT NULL DEFAULT 'draft',
  winner_variant_id BIGINT UNSIGNED NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_abe_ws_status(workspace_id,status),
  CONSTRAINT fk_abe_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_abe_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_abe_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ab_experiment_variants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  experiment_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(80) NOT NULL,
  headline TEXT NULL,
  body LONGTEXT NULL,
  cta VARCHAR(255) NULL,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  is_manual_winner TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ab_variant_label(experiment_id,label),
  INDEX idx_abv_exp_status(experiment_id,status),
  CONSTRAINT fk_abv_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_abv_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ab_experiment_assignments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  experiment_id BIGINT UNSIGNED NOT NULL,
  variant_id BIGINT UNSIGNED NOT NULL,
  scheduled_post_id BIGINT UNSIGNED NOT NULL,
  distribution_batch_id BIGINT UNSIGNED NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ab_assignment_post(workspace_id,scheduled_post_id),
  INDEX idx_aba_variant(variant_id),
  INDEX idx_aba_distribution_batch(distribution_batch_id),
  INDEX idx_aba_experiment(experiment_id),
  CONSTRAINT fk_aba_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_aba_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
  CONSTRAINT fk_aba_variant FOREIGN KEY(variant_id) REFERENCES ab_experiment_variants(id) ON DELETE CASCADE,
  CONSTRAINT fk_aba_post FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ab_distribution_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  experiment_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  distribution_mode ENUM('balanced','randomized') NOT NULL DEFAULT 'balanced',
  requested_quantity INT UNSIGNED NOT NULL,
  weights_json LONGTEXT NOT NULL,
  allocation_json LONGTEXT NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_abdb_ws_time(workspace_id,created_at),
  INDEX idx_abdb_experiment(experiment_id),
  CONSTRAINT fk_abdb_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_abdb_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
  CONSTRAINT fk_abdb_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_abdb_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ab_statistical_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  experiment_id BIGINT UNSIGNED NOT NULL,
  success_metric ENUM('click_rate','conversion_rate','conversion_value') NOT NULL,
  evidence_status VARCHAR(50) NOT NULL,
  leader_variant_id BIGINT UNSIGNED NULL,
  relative_lift DECIMAL(16,6) NULL,
  payload LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_abss_ws_time(workspace_id,created_at),
  INDEX idx_abss_experiment_time(experiment_id,created_at),
  CONSTRAINT fk_abss_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_abss_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
  CONSTRAINT fk_abss_leader FOREIGN KEY(leader_variant_id) REFERENCES ab_experiment_variants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS experiment_decisions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  experiment_id BIGINT UNSIGNED NOT NULL,
  variant_id BIGINT UNSIGNED NOT NULL,
  status ENUM('approved','superseded','revoked') NOT NULL DEFAULT 'approved',
  evidence_status VARCHAR(50) NOT NULL,
  decision_note TEXT NULL,
  decided_by BIGINT UNSIGNED NOT NULL,
  decided_at DATETIME NOT NULL,
  revoked_by BIGINT UNSIGNED NULL,
  revoked_at DATETIME NULL,
  revoke_note TEXT NULL,
  exported_to_editor_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ed_ws_status(workspace_id,status),
  INDEX idx_ed_experiment(experiment_id),
  INDEX idx_ed_variant(variant_id),
  CONSTRAINT fk_ed_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ed_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
  CONSTRAINT fk_ed_variant FOREIGN KEY(variant_id) REFERENCES ab_experiment_variants(id) ON DELETE CASCADE,
  CONSTRAINT fk_ed_decided_by FOREIGN KEY(decided_by) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT fk_ed_revoked_by FOREIGN KEY(revoked_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=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;

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;

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;

CREATE TABLE IF NOT EXISTS experiment_launch_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  experiment_id BIGINT UNSIGNED NOT NULL,
  event_type ENUM('reviewed','started') NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  checklist_snapshot LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ele_ws_time(workspace_id,created_at),
  INDEX idx_ele_exp_time(experiment_id,created_at),
  CONSTRAINT fk_ele_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ele_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
  CONSTRAINT fk_ele_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS experiment_operation_acknowledgements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 experiment_id BIGINT UNSIGNED NOT NULL,
 alert_key VARCHAR(80) NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 acknowledged_at DATETIME NOT NULL,
 UNIQUE KEY uq_eoa_exp_alert(experiment_id,alert_key),
 INDEX idx_eoa_ws(workspace_id),
 CONSTRAINT fk_eoa_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_eoa_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
 CONSTRAINT fk_eoa_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS experiment_closing_assessments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 experiment_id BIGINT UNSIGNED NOT NULL,
 recommendation ENUM('continue_collecting','inconclusive','eligible_for_decision') NOT NULL,
 assessment_snapshot LONGTEXT NOT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_eca_ws_time(workspace_id,created_at),
 INDEX idx_eca_exp_time(experiment_id,created_at),
 CONSTRAINT fk_eca_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_eca_experiment FOREIGN KEY(experiment_id) REFERENCES ab_experiments(id) ON DELETE CASCADE,
 CONSTRAINT fk_eca_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS=1;
