-- Messaging through the official WhatsApp Business Platform (Cloud API).
-- Kept separate from billing tables. Access tokens and app secrets are encrypted at rest (libsodium secretbox).

CREATE TABLE wa_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  waba_id VARCHAR(64) NOT NULL,
  phone_number_id VARCHAR(64) NOT NULL,
  display_phone VARCHAR(40) NULL,
  access_token_enc TEXT NOT NULL,
  app_secret_enc TEXT NOT NULL,
  verify_token VARCHAR(64) NOT NULL,
  webhook_key CHAR(32) NOT NULL,
  messages_per_second INT NOT NULL DEFAULT 20,
  daily_recipient_limit INT NOT NULL DEFAULT 250,
  status ENUM('connected','error','disconnected') NOT NULL DEFAULT 'connected',
  last_error VARCHAR(255) NULL,
  last_webhook_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_wa_user (user_id),
  UNIQUE KEY uq_wa_webhook_key (webhook_key),
  UNIQUE KEY uq_wa_phone (phone_number_id),
  CONSTRAINT fk_wa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wa_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id BIGINT UNSIGNED NOT NULL,
  provider_template_id VARCHAR(64) NULL,
  name VARCHAR(512) NOT NULL,
  language VARCHAR(16) NOT NULL,
  category VARCHAR(32) NULL,
  status VARCHAR(32) NOT NULL,
  body_text TEXT NULL,
  body_variable_count INT NOT NULL DEFAULT 0,
  components JSON NULL,
  synced_at DATETIME NOT NULL,
  UNIQUE KEY uq_tpl (account_id, name(190), language),
  CONSTRAINT fk_tpl_account FOREIGN KEY (account_id) REFERENCES wa_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE recipients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  phone_e164 VARCHAR(20) NOT NULL,
  name VARCHAR(160) NULL,
  fields JSON NULL,
  consent_status ENUM('opted_in','opted_out','unknown') NOT NULL DEFAULT 'unknown',
  consent_source VARCHAR(80) NULL,
  consent_at DATETIME NULL,
  consent_evidence TEXT NULL,
  opted_out_at DATETIME NULL,
  opt_out_source VARCHAR(80) NULL,
  last_inbound_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_recipient (user_id, phone_e164),
  CONSTRAINT fk_recipient_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE consent_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recipient_id BIGINT UNSIGNED NOT NULL,
  action ENUM('opt_in','opt_out','opt_in_reaffirmed') NOT NULL,
  source VARCHAR(80) NOT NULL,
  evidence TEXT NULL,
  actor VARCHAR(40) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY ix_consent_recipient (recipient_id, created_at),
  CONSTRAINT fk_consent_recipient FOREIGN KEY (recipient_id) REFERENCES recipients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE recipient_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_group_name (user_id, name),
  CONSTRAINT fk_group_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE recipient_group_members (
  group_id BIGINT UNSIGNED NOT NULL,
  recipient_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (group_id, recipient_id),
  CONSTRAINT fk_rgm_group FOREIGN KEY (group_id) REFERENCES recipient_groups(id) ON DELETE CASCADE,
  CONSTRAINT fk_rgm_recipient FOREIGN KEY (recipient_id) REFERENCES recipients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE campaigns (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  template_id BIGINT UNSIGNED NOT NULL,
  variable_map JSON NOT NULL,
  audience JSON NOT NULL,
  timezone VARCHAR(64) NOT NULL,
  schedule_local DATETIME NOT NULL,
  recurrence ENUM('none','daily','weekly','monthly') NOT NULL DEFAULT 'none',
  recurrence_until DATE NULL,
  next_run_at DATETIME NULL,
  status ENUM('scheduled','running','completed','cancelled','failed') NOT NULL DEFAULT 'scheduled',
  idempotency_key VARCHAR(64) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  cancelled_at DATETIME NULL,
  UNIQUE KEY uq_campaign_idem (user_id, idempotency_key),
  KEY ix_campaign_due (status, next_run_at),
  CONSTRAINT fk_campaign_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_campaign_tpl FOREIGN KEY (template_id) REFERENCES wa_templates(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE campaign_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  campaign_id BIGINT UNSIGNED NOT NULL,
  scheduled_for DATETIME NOT NULL,
  status ENUM('queued','sending','completed','cancelled') NOT NULL DEFAULT 'queued',
  created_at DATETIME NOT NULL,
  completed_at DATETIME NULL,
  UNIQUE KEY uq_run (campaign_id, scheduled_for),
  CONSTRAINT fk_run_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE campaign_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  run_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  recipient_id BIGINT UNSIGNED NOT NULL,
  phone_e164 VARCHAR(20) NOT NULL,
  status ENUM('queued','sending','accepted','sent','delivered','read','failed','skipped','unknown','cancelled') NOT NULL DEFAULT 'queued',
  skip_reason VARCHAR(80) NULL,
  wamid VARCHAR(190) NULL,
  error_code VARCHAR(32) NULL,
  error_title VARCHAR(255) NULL,
  attempts INT NOT NULL DEFAULT 0,
  next_attempt_at DATETIME NULL,
  claim_token CHAR(32) NULL,
  claimed_at DATETIME NULL,
  accepted_at DATETIME NULL,
  sent_at DATETIME NULL,
  delivered_at DATETIME NULL,
  read_at DATETIME NULL,
  failed_at DATETIME NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_msg_run_recipient (run_id, recipient_id),
  UNIQUE KEY uq_msg_wamid (wamid),
  KEY ix_msg_dispatch (status, next_attempt_at),
  KEY ix_msg_user_day (user_id, accepted_at),
  CONSTRAINT fk_msg_run FOREIGN KEY (run_id) REFERENCES campaign_runs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
