-- ============================================================================
-- LASER CUT SAAS - SUPPORT TICKETS SCHEMA
-- Customer support system
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- SUPPORT TICKETS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED NULL COMMENT 'Related order if applicable',
  `ticket_number` VARCHAR(20) NOT NULL COMMENT 'Unique ticket reference',
  `subject` VARCHAR(255) NOT NULL,
  `category` ENUM('order', 'payment', 'production', 'shipping', 'technical', 'general') DEFAULT 'general',
  `priority` ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
  `status` ENUM('open', 'awaiting_response', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
  `assigned_user_id` INT UNSIGNED NULL COMMENT 'Admin user assigned to ticket',
  `last_reply_at` DATETIME NULL,
  `last_reply_by` ENUM('customer', 'admin') NULL,
  `resolved_at` DATETIME NULL,
  `closed_at` DATETIME NULL,
  `satisfaction_rating` TINYINT NULL COMMENT '1-5 stars',
  `satisfaction_comment` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_ticket_number` (`tenant_id`, `ticket_number`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_customer_id` (`customer_id`),
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_priority` (`priority`),
  INDEX `idx_category` (`category`),
  INDEX `idx_assigned_user_id` (`assigned_user_id`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_support_tickets_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_support_tickets_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_support_tickets_order` FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_support_tickets_user` FOREIGN KEY (`assigned_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SUPPORT MESSAGES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `support_messages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `ticket_id` INT UNSIGNED NOT NULL,
  `sender_type` ENUM('customer', 'admin', 'system') NOT NULL,
  `sender_id` INT UNSIGNED NULL COMMENT 'customer_id or user_id based on sender_type',
  `sender_name` VARCHAR(255) NULL,
  `message` TEXT NOT NULL,
  `is_internal_note` TINYINT(1) DEFAULT 0 COMMENT 'Internal notes not visible to customer',
  `attachments` JSON NULL COMMENT 'Array of attachment URLs',
  `read_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_ticket_id` (`ticket_id`),
  INDEX `idx_sender_type` (`sender_type`),
  INDEX `idx_is_internal_note` (`is_internal_note`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_support_messages_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_support_messages_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CANNED RESPONSES (templates for admin)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `canned_responses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL COMMENT 'Short title for admin reference',
  `category` VARCHAR(50) NULL COMMENT 'Grouping category',
  `message` TEXT NOT NULL,
  `shortcut` VARCHAR(50) NULL COMMENT 'Keyboard shortcut e.g. /agradecimento',
  `use_count` INT UNSIGNED DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_category` (`category`),
  INDEX `idx_shortcut` (`shortcut`),
  INDEX `idx_active` (`active`),
  CONSTRAINT `fk_canned_responses_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;