-- ============================================================================
-- LASER CUT SAAS - NOTIFICATIONS SCHEMA
-- User and admin notification system
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- NOTIFICATIONS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `notifiable_type` ENUM('customer', 'user') NOT NULL COMMENT 'Recipient type',
  `notifiable_id` INT UNSIGNED NOT NULL COMMENT 'customer_id or user_id',
  `type` VARCHAR(100) NOT NULL COMMENT 'Notification type identifier',
  `category` ENUM('order', 'payment', 'production', 'shipping', 'support', 'system', 'promotion') DEFAULT 'system',
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `icon` VARCHAR(50) NULL COMMENT 'Icon class or emoji',
  `color` VARCHAR(20) NULL COMMENT 'Badge color',
  `action_url` VARCHAR(500) NULL COMMENT 'Link to related resource',
  `action_label` VARCHAR(100) NULL COMMENT 'Button text',
  `data` JSON NULL COMMENT 'Additional data as JSON',
  `read_at` DATETIME NULL,
  `email_sent` TINYINT(1) DEFAULT 0,
  `email_sent_at` DATETIME NULL,
  `sms_sent` TINYINT(1) DEFAULT 0,
  `sms_sent_at` DATETIME NULL,
  `whatsapp_sent` TINYINT(1) DEFAULT 0,
  `whatsapp_sent_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_notifiable` (`notifiable_type`, `notifiable_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_category` (`category`),
  INDEX `idx_read_at` (`read_at`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_notifications_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- NOTIFICATION TEMPLATES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `notification_templates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `type` VARCHAR(100) NOT NULL COMMENT 'Template type identifier',
  `name` VARCHAR(255) NOT NULL,
  `title_template` VARCHAR(255) NOT NULL COMMENT 'Title with placeholders like {order_number}',
  `message_template` TEXT NOT NULL COMMENT 'Message with placeholders',
  `email_subject` VARCHAR(255) NULL,
  `email_template` TEXT NULL COMMENT 'HTML email template',
  `sms_template` VARCHAR(500) NULL COMMENT 'SMS message template',
  `whatsapp_template` TEXT NULL,
  `channels` JSON NULL COMMENT '["app", "email", "sms", "whatsapp"]',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_tenant_type` (`tenant_id`, `type`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_active` (`active`),
  CONSTRAINT `fk_notification_templates_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- NOTIFICATION PREFERENCES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `notification_preferences` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `notifiable_type` ENUM('customer', 'user') NOT NULL,
  `notifiable_id` INT UNSIGNED NOT NULL,
  `notification_type` VARCHAR(100) NOT NULL,
  `channel_app` TINYINT(1) DEFAULT 1,
  `channel_email` TINYINT(1) DEFAULT 1,
  `channel_sms` TINYINT(1) DEFAULT 0,
  `channel_whatsapp` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_preference` (`tenant_id`, `notifiable_type`, `notifiable_id`, `notification_type`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_notifiable` (`notifiable_type`, `notifiable_id`),
  CONSTRAINT `fk_notification_preferences_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- DEFAULT NOTIFICATION TEMPLATES (INSERT FOR NEW TENANTS)
-- ============================================================================

-- These will be inserted via seed or installer
-- Example types:
-- order_created, order_confirmed, order_processing, order_shipped, order_delivered
-- payment_pending, payment_confirmed, payment_failed
-- production_started, production_completed
-- support_ticket_reply