-- Global Health CIO Club — Phase 1 schema (MySQL 8 / MariaDB 10.6+)
SET NAMES utf8mb4;

CREATE TABLE settings (
  k VARCHAR(80) PRIMARY KEY,
  v TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE languages (
  code VARCHAR(8) PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  native_name VARCHAR(60) NOT NULL,
  dir ENUM('ltr','rtl') NOT NULL DEFAULT 'ltr',
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE translations (
  lang VARCHAR(8) NOT NULL,
  tkey VARCHAR(120) NOT NULL,
  value TEXT NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (lang, tkey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE currencies (
  code CHAR(3) PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  symbol VARCHAR(8) NOT NULL,
  decimals TINYINT NOT NULL DEFAULT 2,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE regions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE countries (
  iso2 CHAR(2) PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  region_id INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  default_lang VARCHAR(8) NOT NULL DEFAULT 'en',
  timezone VARCHAR(64) NOT NULL DEFAULT 'UTC',
  active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (region_id) REFERENCES regions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE chapters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  country_iso2 CHAR(2) NOT NULL,
  city VARCHAR(80) NULL,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  description TEXT NULL,
  status ENUM('forming','active','paused') NOT NULL DEFAULT 'forming',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (country_iso2) REFERENCES countries(iso2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('platform','chapter','member') NOT NULL DEFAULT 'member',
  platform_subrole ENUM('super','moderator','editor','ads','support') NULL,
  status ENUM('unverified','pending','active','suspended','rejected') NOT NULL DEFAULT 'unverified',
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  job_title VARCHAR(120) NOT NULL DEFAULT '',
  organisation VARCHAR(160) NOT NULL DEFAULT '',
  org_type ENUM('provider','public_health','payer','diagnostics','pharma','health_tech_gov','other') NOT NULL DEFAULT 'provider',
  headline VARCHAR(200) NOT NULL DEFAULT '',
  bio TEXT NULL,
  country_iso2 CHAR(2) NULL,
  city VARCHAR(80) NOT NULL DEFAULT '',
  chapter_id INT NULL,
  linkedin_url VARCHAR(255) NOT NULL DEFAULT '',
  phone_e164 VARCHAR(20) NOT NULL DEFAULT '',
  whatsapp_optin TINYINT(1) NOT NULL DEFAULT 0,
  email_digest TINYINT(1) NOT NULL DEFAULT 1,
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  timezone VARCHAR(64) NOT NULL DEFAULT 'UTC',
  avatar_path VARCHAR(255) NOT NULL DEFAULT '',
  application_note TEXT NULL,
  vouch_emails VARCHAR(400) NOT NULL DEFAULT '',
  is_founding TINYINT(1) NOT NULL DEFAULT 0,
  profile_public TINYINT(1) NOT NULL DEFAULT 0,
  email_verified_at DATETIME NULL,
  approved_by INT NULL,
  approved_at DATETIME NULL,
  review_note VARCHAR(500) NOT NULL DEFAULT '',
  failed_logins INT NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  must_change_password TINYINT(1) NOT NULL DEFAULT 0,
  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,
  FOREIGN KEY (country_iso2) REFERENCES countries(iso2),
  FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE SET NULL,
  KEY idx_users_status (status, country_iso2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Country / chapter admin scope. chapter_id NULL = whole country.
CREATE TABLE admin_scopes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  country_iso2 CHAR(2) NOT NULL,
  chapter_id INT NULL,
  UNIQUE KEY uq_scope (user_id, country_iso2, chapter_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (country_iso2) REFERENCES countries(iso2),
  FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  purpose ENUM('verify_email','reset_password') 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,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE circles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  kind ENUM('segment','role','chapter','topic') NOT NULL DEFAULT 'topic',
  description VARCHAR(400) NOT NULL DEFAULT '',
  chapter_id INT NULL,
  is_private TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE circle_members (
  circle_id INT NOT NULL,
  user_id INT NOT NULL,
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (circle_id, user_id),
  FOREIGN KEY (circle_id) REFERENCES circles(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  kind ENUM('article','post','poll','question') NOT NULL DEFAULT 'post',
  title VARCHAR(220) NOT NULL DEFAULT '',
  slug VARCHAR(240) NULL UNIQUE,
  body_html MEDIUMTEXT NOT NULL,
  excerpt VARCHAR(400) NOT NULL DEFAULT '',
  cover_path VARCHAR(255) NOT NULL DEFAULT '',
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  visibility ENUM('public','members','circle') NOT NULL DEFAULT 'members',
  circle_id INT NULL,
  is_anonymous TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('draft','scheduled','published','removed') NOT NULL DEFAULT 'draft',
  publish_at DATETIME NULL,
  published_at DATETIME NULL,
  best_comment_id INT NULL,
  view_count INT NOT NULL DEFAULT 0,
  share_count INT NOT NULL DEFAULT 0,
  removed_reason VARCHAR(300) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (circle_id) REFERENCES circles(id) ON DELETE SET NULL,
  KEY idx_posts_feed (status, published_at),
  KEY idx_posts_kind (kind, status, published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  slug VARCHAR(70) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE post_tags (
  post_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE poll_options (
  id INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  label VARCHAR(160) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE poll_votes (
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  option_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (post_id, user_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (option_id) REFERENCES poll_options(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  body TEXT NOT NULL,
  status ENUM('visible','removed') NOT NULL DEFAULT 'visible',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_comments_post (post_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reactions (
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (post_id, user_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE saves (
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (post_id, user_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE follows (
  follower_id INT NOT NULL,
  followee_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, followee_id),
  FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (followee_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE connections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requester_id INT NOT NULL,
  addressee_id INT NOT NULL,
  user_low INT NOT NULL,
  user_high INT NOT NULL,
  status ENUM('pending','accepted','declined') NOT NULL DEFAULT 'pending',
  note VARCHAR(300) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  responded_at DATETIME NULL,
  UNIQUE KEY uq_pair (user_low, user_high),
  FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (addressee_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE conversations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_low INT NOT NULL,
  user_high INT NOT NULL,
  last_message_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_conv (user_low, user_high),
  FOREIGN KEY (user_low) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (user_high) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  sender_id INT NOT NULL,
  body TEXT NOT NULL,
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_msg_conv (conversation_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  chapter_id INT NULL,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  description TEXT NOT NULL,
  format ENUM('virtual','in_person','roundtable','hybrid') NOT NULL DEFAULT 'virtual',
  starts_at DATETIME NOT NULL COMMENT 'UTC',
  ends_at DATETIME NOT NULL COMMENT 'UTC',
  host_timezone VARCHAR(64) NOT NULL DEFAULT 'UTC',
  venue VARCHAR(255) NOT NULL DEFAULT '',
  online_url VARCHAR(255) NOT NULL DEFAULT '',
  capacity INT NOT NULL DEFAULT 0 COMMENT '0 = unlimited',
  visibility ENUM('public','members') NOT NULL DEFAULT 'public',
  status ENUM('draft','published','cancelled') NOT NULL DEFAULT 'draft',
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id),
  KEY idx_events_time (status, starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE event_rsvps (
  event_id INT NOT NULL,
  user_id INT NOT NULL,
  status ENUM('going','waitlist','cancelled') NOT NULL DEFAULT 'going',
  checkin_code CHAR(10) NOT NULL,
  checked_in_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (event_id, user_id),
  UNIQUE KEY uq_checkin (checkin_code),
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  kind VARCHAR(40) NOT NULL,
  actor_id INT NULL,
  message VARCHAR(300) NOT NULL,
  link VARCHAR(255) NOT NULL DEFAULT '',
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_notif_user (user_id, read_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  target_type ENUM('post','comment') NOT NULL,
  target_id INT NOT NULL,
  reporter_id INT NOT NULL,
  reason VARCHAR(400) NOT NULL,
  status ENUM('open','actioned','dismissed') NOT NULL DEFAULT 'open',
  handled_by INT NULL,
  handled_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_report (target_type, target_id, reporter_id),
  FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Query submission desk
CREATE TABLE queries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ref VARCHAR(20) NULL UNIQUE,
  kind ENUM('membership','advertise','partner','chapter_lead','support','press','other') NOT NULL DEFAULT 'other',
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(30) NOT NULL DEFAULT '',
  organisation VARCHAR(160) NOT NULL DEFAULT '',
  country_iso2 CHAR(2) NULL,
  message TEXT NOT NULL,
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  source_page VARCHAR(120) NOT NULL DEFAULT '',
  status ENUM('new','open','waiting','closed') NOT NULL DEFAULT 'new',
  assigned_to INT NULL,
  ip VARCHAR(45) NOT NULL DEFAULT '',
  first_response_at DATETIME NULL,
  closed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (country_iso2) REFERENCES countries(iso2),
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  KEY idx_q_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE query_notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  query_id INT NOT NULL,
  user_id INT NOT NULL,
  note TEXT NOT NULL,
  is_reply TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (query_id) REFERENCES queries(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Email (Amazon SES) + WhatsApp Business
CREATE TABLE comm_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(60) NOT NULL,
  channel ENUM('email','whatsapp') NOT NULL,
  lang VARCHAR(8) NOT NULL,
  subject VARCHAR(200) NOT NULL DEFAULT '',
  body TEXT NOT NULL,
  wa_template_name VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Meta-approved template name',
  UNIQUE KEY uq_tpl (code, channel, lang)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE comm_queue (
  id INT AUTO_INCREMENT PRIMARY KEY,
  channel ENUM('email','whatsapp') NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  user_id INT NULL,
  template_code VARCHAR(60) NOT NULL,
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  subject VARCHAR(200) NOT NULL DEFAULT '',
  body MEDIUMTEXT NOT NULL,
  wa_template_name VARCHAR(100) NOT NULL DEFAULT '',
  wa_params TEXT NULL,
  status ENUM('pending','sending','sent','failed','suppressed') NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  next_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  claimed_at DATETIME NULL,
  claim_token CHAR(16) NULL,
  last_error VARCHAR(500) NOT NULL DEFAULT '',
  provider_message_id VARCHAR(190) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  KEY idx_cq (status, next_attempt_at),
  KEY idx_cq_claim (claim_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_suppressions (
  email VARCHAR(190) PRIMARY KEY,
  reason ENUM('bounce','complaint','unsubscribe','manual') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ads (Phase 1: admin-managed; self-serve advertiser portal + Stripe is Phase 2)
CREATE TABLE ad_slots (
  code VARCHAR(40) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  width INT NOT NULL,
  height INT NOT NULL,
  placement VARCHAR(200) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ad_campaigns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  advertiser VARCHAR(160) NOT NULL,
  name VARCHAR(160) NOT NULL,
  slot_code VARCHAR(40) NOT NULL,
  image_path VARCHAR(255) NOT NULL DEFAULT '',
  headline VARCHAR(120) NOT NULL DEFAULT '',
  body VARCHAR(240) NOT NULL DEFAULT '',
  target_url VARCHAR(500) NOT NULL,
  langs VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'comma list, blank = all',
  countries VARCHAR(400) NOT NULL DEFAULT '' COMMENT 'comma list ISO2, blank = all',
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  weight INT NOT NULL DEFAULT 1,
  is_house TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('draft','active','paused','ended') NOT NULL DEFAULT 'draft',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (slot_code) REFERENCES ad_slots(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ad_stats (
  campaign_id INT NOT NULL,
  day DATE NOT NULL,
  country_iso2 CHAR(2) NOT NULL DEFAULT '--',
  lang VARCHAR(8) NOT NULL DEFAULT '--',
  impressions INT NOT NULL DEFAULT 0,
  clicks INT NOT NULL DEFAULT 0,
  PRIMARY KEY (campaign_id, day, country_iso2, lang),
  FOREIGN KEY (campaign_id) REFERENCES ad_campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rate_limits (
  k VARCHAR(190) PRIMARY KEY,
  hits INT NOT NULL,
  window_start DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(80) NOT NULL,
  entity VARCHAR(40) NOT NULL DEFAULT '',
  entity_id INT NULL,
  detail VARCHAR(1000) NOT NULL DEFAULT '',
  ip VARCHAR(45) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit (created_at),
  KEY idx_audit_entity (entity, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE schema_migrations (
  filename VARCHAR(120) PRIMARY KEY,
  checksum CHAR(64) NOT NULL,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;