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,
  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),
  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,
  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;

SET FOREIGN_KEY_CHECKS=1;
