-- DentalCare 0.5.9.8.30: compact dashboard guidance and Help & Support center.
CREATE TABLE IF NOT EXISTS `{{prefix}}support_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `category` VARCHAR(40) NOT NULL DEFAULT 'workflow',
  `priority` ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `subject` VARCHAR(180) NOT NULL,
  `message` TEXT NOT NULL,
  `page_route` VARCHAR(80) NULL,
  `page_url` VARCHAR(500) NULL,
  `error_reference` VARCHAR(100) NULL,
  `context_json` JSON NULL,
  `status` ENUM('open','in_review','waiting_user','resolved','closed') NOT NULL DEFAULT 'open',
  `assigned_to_user_id` BIGINT UNSIGNED NULL,
  `resolution_note` TEXT NULL,
  `resolved_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_support_request_user` (`user_id`,`status`,`updated_at`),
  KEY `idx_support_request_branch` (`branch_id`,`status`,`priority`,`updated_at`),
  KEY `idx_support_request_assignment` (`assigned_to_user_id`,`status`,`updated_at`),
  KEY `idx_support_request_error_reference` (`error_reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
