-- =========================================================
-- 118_notification_jobs.sql
-- Notification jobs foundation for async outbound delivery
-- =========================================================

CREATE TABLE IF NOT EXISTS notification_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  spk_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NULL,
  phone VARCHAR(40) NULL,
  channel VARCHAR(40) NOT NULL DEFAULT 'whatsapp',
  provider VARCHAR(40) NOT NULL DEFAULT 'wa_web',
  type VARCHAR(60) NOT NULL DEFAULT 'spk_completed',
  message TEXT NOT NULL,
  status ENUM('queued','processing','sent','failed','skipped','canceled') NOT NULL DEFAULT 'queued',
  retry_count INT UNSIGNED NOT NULL DEFAULT 0,
  next_retry_at DATETIME NULL,
  locked_at DATETIME NULL,
  processing_token VARCHAR(80) NULL,
  last_attempt_at DATETIME NULL,
  sent_at DATETIME NULL,
  error_message TEXT NULL,
  payload_json JSON NULL,
  provider_message_id VARCHAR(190) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_notification_jobs_spk_channel_type (spk_id, channel, type),
  UNIQUE KEY uq_notification_jobs_processing_token (processing_token),
  KEY idx_notification_jobs_status_retry (status, next_retry_at, id),
  KEY idx_notification_jobs_tenant_location (tenant_id, location_id, status, id),
  KEY idx_notification_jobs_customer_created (customer_id, created_at),
  KEY idx_notification_jobs_order (order_id),
  KEY idx_notification_jobs_spk (spk_id),
  CONSTRAINT fk_notification_jobs_order FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_notification_jobs_spk FOREIGN KEY (spk_id) REFERENCES spk(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_notification_jobs_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;