SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS accounts (
  id CHAR(26) PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  status ENUM('active','suspended','terminated') NOT NULL DEFAULT 'active',
  external_customer_id VARCHAR(190) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_accounts_external_customer (external_customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  name VARCHAR(190) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('owner','admin','member') NOT NULL DEFAULT 'member',
  status ENUM('active','disabled') 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,
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_account (account_id),
  CONSTRAINT fk_users_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_sessions (
  id CHAR(26) PRIMARY KEY,
  user_id CHAR(26) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  expires_at DATETIME NOT NULL,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_login_token (token_hash),
  KEY idx_login_user (user_id),
  KEY idx_login_expires (expires_at),
  CONSTRAINT fk_login_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS plans (
  id CHAR(26) PRIMARY KEY,
  code VARCHAR(80) NOT NULL,
  name VARCHAR(190) NOT NULL,
  channel_limit INT UNSIGNED NOT NULL DEFAULT 1,
  monthly_message_limit BIGINT UNSIGNED NOT NULL DEFAULT 0,
  campaign_recipient_limit INT UNSIGNED NOT NULL DEFAULT 1000,
  api_rate_limit_per_minute INT UNSIGNED NOT NULL DEFAULT 120,
  features_json LONGTEXT NULL,
  is_active 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,
  UNIQUE KEY uq_plans_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  plan_id CHAR(26) NOT NULL,
  external_service_id VARCHAR(190) NULL,
  external_order_id VARCHAR(190) NULL,
  status ENUM('active','suspended','cancelled','expired','trial') NOT NULL DEFAULT 'active',
  starts_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_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_subscription_external_service (external_service_id),
  KEY idx_subscription_account_status (account_id, status),
  CONSTRAINT fk_subscription_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_subscription_plan FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS channels (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  name VARCHAR(190) NOT NULL,
  provider ENUM('qr','meta_cloud') NOT NULL,
  status ENUM('new','connecting','qr_ready','connected','disconnected','error','disabled') NOT NULL DEFAULT 'new',
  phone_number VARCHAR(32) NULL,
  provider_account_id VARCHAR(190) NULL,
  provider_phone_id VARCHAR(190) NULL,
  public_config_json LONGTEXT NULL,
  secret_config LONGTEXT NULL,
  last_error TEXT NULL,
  last_seen_at DATETIME NULL,
  connected_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_channels_account (account_id),
  KEY idx_channels_provider_status (provider, status),
  CONSTRAINT fk_channels_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_keys (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  created_by CHAR(26) NULL,
  name VARCHAR(190) NOT NULL,
  key_prefix VARCHAR(24) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  scopes_json LONGTEXT NULL,
  last_used_at DATETIME NULL,
  expires_at DATETIME NULL,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_api_key_hash (key_hash),
  KEY idx_api_key_account (account_id),
  CONSTRAINT fk_api_key_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_api_key_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  channel_id CHAR(26) NOT NULL,
  direction ENUM('outbound','inbound') NOT NULL DEFAULT 'outbound',
  provider_message_id VARCHAR(255) NULL,
  idempotency_key VARCHAR(190) NULL,
  recipient VARCHAR(32) NULL,
  sender VARCHAR(32) NULL,
  type VARCHAR(40) NOT NULL,
  payload_json LONGTEXT NOT NULL,
  status ENUM('queued','sending','sent','delivered','read','failed','received','cancelled') NOT NULL DEFAULT 'queued',
  source ENUM('api','dashboard','campaign','autoreply','whmcs','provider') NOT NULL DEFAULT 'api',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts INT UNSIGNED NOT NULL DEFAULT 4,
  next_attempt_at DATETIME NULL,
  last_error TEXT NULL,
  sent_at DATETIME NULL,
  delivered_at DATETIME NULL,
  read_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_message_idempotency (account_id, idempotency_key),
  KEY idx_message_queue (status, next_attempt_at, created_at),
  KEY idx_message_channel_created (channel_id, created_at),
  KEY idx_message_provider_id (provider_message_id),
  CONSTRAINT fk_message_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_message_channel FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_events (
  id CHAR(26) PRIMARY KEY,
  message_id CHAR(26) NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  payload_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_message_events_message (message_id, created_at),
  CONSTRAINT fk_message_event_message FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhook_endpoints (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  name VARCHAR(190) NOT NULL,
  url VARCHAR(1000) NOT NULL,
  secret VARCHAR(255) NOT NULL,
  events_json LONGTEXT NULL,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_webhook_account (account_id),
  CONSTRAINT fk_webhook_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id CHAR(26) PRIMARY KEY,
  webhook_endpoint_id CHAR(26) NOT NULL,
  event_id CHAR(26) NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  payload_json LONGTEXT NOT NULL,
  status ENUM('pending','delivered','failed') NOT NULL DEFAULT 'pending',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  next_attempt_at DATETIME NULL,
  response_status INT 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,
  KEY idx_webhook_delivery_queue (status, next_attempt_at),
  CONSTRAINT fk_delivery_endpoint FOREIGN KEY (webhook_endpoint_id) REFERENCES webhook_endpoints(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contacts (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  name VARCHAR(190) NULL,
  phone VARCHAR(32) NOT NULL,
  attributes_json LONGTEXT NULL,
  opted_in TINYINT(1) NOT NULL DEFAULT 0,
  opt_in_source VARCHAR(190) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_contact_phone (account_id, phone),
  CONSTRAINT fk_contact_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaigns (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  channel_id CHAR(26) NOT NULL,
  name VARCHAR(190) NOT NULL,
  message_type VARCHAR(40) NOT NULL,
  payload_json LONGTEXT NOT NULL,
  status ENUM('draft','scheduled','running','paused','completed','cancelled','failed') NOT NULL DEFAULT 'draft',
  scheduled_at DATETIME 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,
  KEY idx_campaign_due (status, scheduled_at),
  CONSTRAINT fk_campaign_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_campaign_channel FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaign_recipients (
  id CHAR(26) PRIMARY KEY,
  campaign_id CHAR(26) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  variables_json LONGTEXT NULL,
  status ENUM('pending','queued','sent','failed','skipped') NOT NULL DEFAULT 'pending',
  message_id CHAR(26) 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,
  UNIQUE KEY uq_campaign_recipient (campaign_id, phone),
  KEY idx_campaign_recipient_queue (campaign_id, status),
  CONSTRAINT fk_campaign_recipient_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
  CONSTRAINT fk_campaign_recipient_message FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS provider_templates (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  channel_id CHAR(26) NOT NULL,
  provider_template_id VARCHAR(190) NULL,
  name VARCHAR(190) NOT NULL,
  language VARCHAR(20) NOT NULL,
  category VARCHAR(40) NULL,
  status VARCHAR(40) NULL,
  components_json LONGTEXT NULL,
  synced_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_provider_template (channel_id, name, language),
  CONSTRAINT fk_template_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_template_channel FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NULL,
  user_id CHAR(26) NULL,
  action VARCHAR(120) NOT NULL,
  subject_type VARCHAR(80) NULL,
  subject_id VARCHAR(190) NULL,
  ip_address VARCHAR(64) NULL,
  metadata_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_account_created (account_id, created_at),
  CONSTRAINT fk_audit_account FOREIGN KEY (account_id) REFERENCES accounts(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 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activation_tokens (
  id CHAR(26) PRIMARY KEY,
  user_id CHAR(26) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_activation_token (token_hash),
  CONSTRAINT fk_activation_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;