/*
 Store-Level Database Schema for Seller Marketing Module
 
 Source Database: kiosk_{typeNum} (Store-Level Database)
 Target Server Type: MariaDB
 File Encoding: UTF-8
 
 Date: $(date)
 
 Note: These tables should be created in each store's database
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for seller_marketing_messages
-- ----------------------------
DROP TABLE IF EXISTS `seller_marketing_messages`;
CREATE TABLE `seller_marketing_messages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'Display name for the message template',
  `content` text NOT NULL COMMENT 'Message content with variable placeholders',
  `variables` json DEFAULT NULL COMMENT 'Available variables and their descriptions',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `active` tinyint(1) unsigned NOT NULL DEFAULT 1 COMMENT 'Whether message is active/available',
  `character_count` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Character count for SMS calculation',
  `sms_count` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT 'Estimated SMS count',
  `category` enum('trigger','blast','general') NOT NULL DEFAULT 'general' COMMENT 'Message category',
  PRIMARY KEY (`id`),
  KEY `idx_active` (`active`),
  KEY `idx_category` (`category`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Message templates for seller marketing';

-- ----------------------------
-- Table structure for seller_marketing_triggers
-- ----------------------------
DROP TABLE IF EXISTS `seller_marketing_triggers`;
CREATE TABLE `seller_marketing_triggers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'Display name for the trigger',
  `type` enum('days_since_event','days_since_sold','birthday','expiring_points','custom') NOT NULL COMMENT 'Type of trigger',
  `message_id` int(10) unsigned NOT NULL COMMENT 'References seller_marketing_messages.id',
  `status` enum('active','inactive','expired') NOT NULL DEFAULT 'active' COMMENT 'Trigger status',
  `config` json NOT NULL COMMENT 'Trigger configuration parameters',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `expire_date` date DEFAULT NULL COMMENT 'When trigger should stop running',
  `last_processed` timestamp NULL DEFAULT NULL COMMENT 'Last time trigger was processed',
  `processing_frequency` enum('hourly','daily','weekly') NOT NULL DEFAULT 'daily' COMMENT 'How often to check trigger',
  `time_frame` time DEFAULT NULL COMMENT 'What time of day to send messages',
  `time_random` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Send at random time',
  `active_days` set('monday','tuesday','wednesday','thursday','friday','saturday','sunday') DEFAULT 'monday,tuesday,wednesday,thursday,friday,saturday,sunday' COMMENT 'Days trigger is active',
  PRIMARY KEY (`id`),
  KEY `idx_status_type` (`status`,`type`),
  KEY `idx_message_id` (`message_id`),
  KEY `idx_last_processed` (`last_processed`),
  KEY `idx_expire_date` (`expire_date`),
  CONSTRAINT `fk_trigger_message` FOREIGN KEY (`message_id`) REFERENCES `seller_marketing_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Automated trigger configurations';

-- ----------------------------
-- Table structure for seller_marketing_blasts
-- ----------------------------
DROP TABLE IF EXISTS `seller_marketing_blasts`;
CREATE TABLE `seller_marketing_blasts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'Display name for the blast',
  `message_id` int(10) unsigned NOT NULL COMMENT 'References seller_marketing_messages.id',
  `criteria` json NOT NULL COMMENT 'Customer selection criteria',
  `scheduled_at` timestamp NOT NULL COMMENT 'When blast should be sent',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `started_at` timestamp NULL DEFAULT NULL COMMENT 'When blast processing started',
  `completed_at` timestamp NULL DEFAULT NULL COMMENT 'When blast processing completed',
  `status` enum('pending','processing','completed','failed','cancelled') NOT NULL DEFAULT 'pending' COMMENT 'Blast status',
  `recipient_count` int(10) unsigned DEFAULT 0 COMMENT 'Number of recipients',
  `sent_count` int(10) unsigned DEFAULT 0 COMMENT 'Number successfully sent',
  `failed_count` int(10) unsigned DEFAULT 0 COMMENT 'Number failed to send',
  `error_message` text DEFAULT NULL COMMENT 'Error details if failed',
  `created_by` int(10) unsigned DEFAULT NULL COMMENT 'User ID who created blast',
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_scheduled_at` (`scheduled_at`),
  KEY `idx_message_id` (`message_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_blast_message` FOREIGN KEY (`message_id`) REFERENCES `seller_marketing_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='One-time message blast configurations';

-- ----------------------------
-- Table structure for seller_marketing_analytics
-- ----------------------------
DROP TABLE IF EXISTS `seller_marketing_analytics`;
CREATE TABLE `seller_marketing_analytics` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `trigger_id` int(10) unsigned DEFAULT NULL COMMENT 'References seller_marketing_triggers.id',
  `blast_id` int(10) unsigned DEFAULT NULL COMMENT 'References seller_marketing_blasts.id',
  `message_id` int(10) unsigned NOT NULL COMMENT 'References seller_marketing_messages.id',
  `type` enum('trigger','blast') NOT NULL COMMENT 'Analytics type',
  `date` date NOT NULL COMMENT 'Analytics date',
  `recipient_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Number of recipients',
  `sent_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Number successfully sent',
  `delivered_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Number delivered (if tracking available)',
  `failed_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Number failed',
  `opt_out_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Number who opted out',
  `response_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Number who responded (future feature)',
  `conversion_count` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Number who made purchase (future feature)',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_trigger_date` (`trigger_id`,`date`),
  UNIQUE KEY `unique_blast_date` (`blast_id`,`date`),
  KEY `idx_message_date` (`message_id`,`date`),
  KEY `idx_type_date` (`type`,`date`),
  CONSTRAINT `fk_analytics_trigger` FOREIGN KEY (`trigger_id`) REFERENCES `seller_marketing_triggers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_analytics_blast` FOREIGN KEY (`blast_id`) REFERENCES `seller_marketing_blasts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_analytics_message` FOREIGN KEY (`message_id`) REFERENCES `seller_marketing_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Analytics data for seller marketing campaigns';

-- ----------------------------
-- Table structure for seller_marketing_customer_log
-- ----------------------------
DROP TABLE IF EXISTS `seller_marketing_customer_log`;
CREATE TABLE `seller_marketing_customer_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL COMMENT 'References customers.id',
  `phone` varchar(15) NOT NULL COMMENT 'Customer phone number',
  `trigger_id` int(10) unsigned DEFAULT NULL COMMENT 'References seller_marketing_triggers.id',
  `blast_id` int(10) unsigned DEFAULT NULL COMMENT 'References seller_marketing_blasts.id',
  `message_id` int(10) unsigned NOT NULL COMMENT 'References seller_marketing_messages.id',
  `message_content` text NOT NULL COMMENT 'Actual message sent to customer',
  `sent_at` timestamp NOT NULL COMMENT 'When message was sent',
  `status` enum('sent','delivered','failed','opt_out') NOT NULL COMMENT 'Message status',
  `provider_id` varchar(255) DEFAULT NULL COMMENT 'Provider message ID',
  `response_received` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Whether customer responded',
  `response_content` text DEFAULT NULL COMMENT 'Customer response (if any)',
  `conversion_tracked` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Whether conversion was tracked',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_customer_sent` (`customer_id`,`sent_at`),
  KEY `idx_phone` (`phone`),
  KEY `idx_trigger_id` (`trigger_id`),
  KEY `idx_blast_id` (`blast_id`),
  KEY `idx_message_id` (`message_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_log_trigger` FOREIGN KEY (`trigger_id`) REFERENCES `seller_marketing_triggers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_log_blast` FOREIGN KEY (`blast_id`) REFERENCES `seller_marketing_blasts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_log_message` FOREIGN KEY (`message_id`) REFERENCES `seller_marketing_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Log of all messages sent to customers';

SET FOREIGN_KEY_CHECKS = 1;