-- Schema for the SMS Trigger System

-- Stores the configuration for each trigger
CREATE TABLE `sms_triggers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `typeNum` VARCHAR(20) NOT NULL COMMENT 'Store identifier (stores.typeNum)',
  `name` VARCHAR(100) NOT NULL COMMENT 'Human-readable name for the trigger',
  `type` VARCHAR(50) NOT NULL COMMENT 'e.g., single_send, recurring_stale, recurring_rating',
  `message_template` TEXT NOT NULL COMMENT 'SMS message body template (may include placeholders like {firstName})',
  `criteria_config` JSON NOT NULL COMMENT 'JSON storing criteria types and their parameters, e.g., [{"type": "last_visit", "days": 90}, {"type": "min_rating", "rating": 4}]',
  `trigger_config` JSON NOT NULL COMMENT 'JSON storing type-specific config, e.g., {"send_date": "2025-06-01"} for single_send, {"frequency": "daily", "time": "08:00:00"} for recurring',
  `is_active` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '0 = Inactive, 1 = Active',
  `last_run_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Timestamp when the trigger was last processed',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_typeNum_active_last_run` (`typeNum`, `is_active`, `last_run_at`), -- Index for scanner
  INDEX `idx_type` (`type`),
  INDEX `idx_is_active_last_run` (`is_active`, `last_run_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stores messages generated by triggers, ready for sending
CREATE TABLE `sms_outbound_queue` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `typeNum` VARCHAR(20) NOT NULL COMMENT 'Store identifier (stores.typeNum)',
  `trigger_id` INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED NOT NULL,
  `phone_number` VARCHAR(20) NOT NULL COMMENT 'Denormalized phone number at the time of queuing',
  `message_body` TEXT NOT NULL,
  `status` ENUM('queued', 'sending', 'sent', 'failed') NOT NULL DEFAULT 'queued',
  `scheduled_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the message was added to the queue',
  `sent_at` TIMESTAMP NULL DEFAULT NULL,
  `error_message` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_status_scheduled` (`status`, `scheduled_at`),
  INDEX `idx_typeNum_trigger_customer` (`typeNum`, `trigger_id`, `customer_id`), -- To help prevent duplicates per store
  CONSTRAINT `fk_sms_outbound_trigger` FOREIGN KEY (`trigger_id`) REFERENCES `sms_triggers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;