/*
 Global Database Schema for Seller Marketing Module
 
 Source Database: kiosk_buykiosk (Global Database)
 Target Server Type: MariaDB
 File Encoding: UTF-8
 
 Date: $(date)
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for seller_marketing_queue
-- ----------------------------
DROP TABLE IF EXISTS `seller_marketing_queue`;
CREATE TABLE `seller_marketing_queue` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `store_id` int(10) unsigned NOT NULL COMMENT 'References stores.id',
  `phone` varchar(15) NOT NULL COMMENT 'Recipient phone number (10 digits)',
  `message` text NOT NULL COMMENT 'SMS message content',
  `message_type` enum('trigger','blast') NOT NULL COMMENT 'Type of message',
  `trigger_id` int(10) unsigned DEFAULT NULL COMMENT 'ID of trigger that generated this message (if applicable)',
  `blast_id` int(10) unsigned DEFAULT NULL COMMENT 'ID of blast that generated this message (if applicable)',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'When message was queued',
  `send_at` timestamp NOT NULL COMMENT 'When message should be sent',
  `sent_at` timestamp NULL DEFAULT NULL COMMENT 'When message was actually sent',
  `status` enum('pending','sent','failed','cancelled') NOT NULL DEFAULT 'pending' COMMENT 'Message status',
  `provider` enum('vonage','twilio') NOT NULL COMMENT 'SMS provider to use',
  `provider_id` varchar(255) DEFAULT NULL COMMENT 'Provider message ID',
  `error_message` text DEFAULT NULL COMMENT 'Error details if failed',
  `attempts` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'Number of send attempts',
  `max_attempts` tinyint(3) unsigned NOT NULL DEFAULT 3 COMMENT 'Maximum retry attempts',
  `priority` tinyint(3) unsigned NOT NULL DEFAULT 5 COMMENT 'Message priority (1=highest, 10=lowest)',
  PRIMARY KEY (`id`),
  KEY `idx_store_status` (`store_id`,`status`),
  KEY `idx_send_at` (`send_at`),
  KEY `idx_phone` (`phone`),
  KEY `idx_status_attempts` (`status`,`attempts`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_provider` (`provider`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Global outgoing message queue for seller marketing';

SET FOREIGN_KEY_CHECKS = 1;