-- Baltique OS Core 0.6 — Channel Engine: SMS + WhatsApp
-- Operational communication layer only. Does not replace OpenCare/EDM.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS channel_delivery_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  communication_queue_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  channel ENUM('sms','whatsapp','phone','email','portal') NOT NULL,
  provider VARCHAR(80) NULL,
  recipient VARCHAR(190) NULL,
  request_payload LONGTEXT NULL,
  response_status INT NULL,
  response_body LONGTEXT NULL,
  status ENUM('sent','failed','skipped','test') NOT NULL DEFAULT 'sent',
  error_message TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_channel_delivery_queue (communication_queue_id),
  KEY idx_channel_delivery_patient (patient_id, channel, created_at),
  KEY idx_channel_delivery_status (status, created_at),
  KEY idx_channel_delivery_user (created_by_user_id),
  CONSTRAINT fk_channel_delivery_queue FOREIGN KEY (communication_queue_id) REFERENCES communication_queue(id) ON DELETE SET NULL,
  CONSTRAINT fk_channel_delivery_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE SET NULL,
  CONSTRAINT fk_channel_delivery_user FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (setting_key, setting_value, created_at, updated_at) VALUES
('core_version','0.6.0',NOW(),NOW()),
('channel_engine_enabled','1',NOW(),NOW()),
('channel_cron_token','',NOW(),NOW()),
('channel_max_attempts','3',NOW(),NOW()),
('sms_enabled','0',NOW(),NOW()),
('sms_provider','webhook',NOW(),NOW()),
('sms_safe_mode','1',NOW(),NOW()),
('sms_batch_limit','20',NOW(),NOW()),
('sms_test_phone','',NOW(),NOW()),
('sms_webhook_url','',NOW(),NOW()),
('sms_webhook_secret','',NOW(),NOW()),
('sms_from_name','Baltique',NOW(),NOW()),
('whatsapp_enabled','0',NOW(),NOW()),
('whatsapp_provider','webhook',NOW(),NOW()),
('whatsapp_safe_mode','1',NOW(),NOW()),
('whatsapp_batch_limit','10',NOW(),NOW()),
('whatsapp_test_phone','',NOW(),NOW()),
('whatsapp_webhook_url','',NOW(),NOW()),
('whatsapp_webhook_secret','',NOW(),NOW()),
('whatsapp_meta_api_version','v20.0',NOW(),NOW()),
('whatsapp_meta_phone_number_id','',NOW(),NOW()),
('whatsapp_meta_access_token','',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=setting_value, updated_at=updated_at;

SET FOREIGN_KEY_CHECKS = 1;
