kknongchang_2506/app/Module/Ulogic/Databases/GenerateSql/app_messages.sql

-- 表 kku_app_messages 的创建SQL

DROP TABLE IF EXISTS `kku_app_messages`;
CREATE TABLE `kku_app_messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息类型：system=系统消息，user=用户消息',
  `template_id` bigint unsigned DEFAULT NULL COMMENT '模板ID，为空表示非模板消息',
  `template_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '模板代码，冗余字段，方便查询',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息标题',
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息内容',
  `content_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'text' COMMENT '内容类型：text=纯文本，html=富文本，markdown=MD格式，json=JSON格式',
  `data` json DEFAULT NULL COMMENT '消息数据',
  `variables_data` json DEFAULT NULL COMMENT '模板变量数据，用于存储模板变量的实际值',
  `sender_id` bigint unsigned NOT NULL COMMENT '发送者ID',
  `sender_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '发送者类型：system=系统，user=用户',
  `receiver_id` bigint unsigned DEFAULT NULL COMMENT '接收者ID，为空表示群发',
  `parent_id` bigint unsigned DEFAULT NULL COMMENT '父消息ID，用于消息回复',
  `allow_reply` tinyint NOT NULL DEFAULT '1' COMMENT '是否允许回复：0不允许 1允许',
  `is_read` tinyint NOT NULL DEFAULT '0' COMMENT '是否已读：0未读 1已读',
  `read_at` timestamp NULL DEFAULT NULL COMMENT '阅读时间',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：0删除 1正常',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `kku_app_messages_type_index` (`type`),
  KEY `kku_app_messages_template_id_index` (`template_id`),
  KEY `kku_app_messages_template_code_index` (`template_code`),
  KEY `kku_app_messages_content_type_index` (`content_type`),
  KEY `kku_app_messages_sender_id_index` (`sender_id`),
  KEY `kku_app_messages_receiver_id_index` (`receiver_id`),
  KEY `kku_app_messages_parent_id_index` (`parent_id`),
  KEY `kku_app_messages_is_read_index` (`is_read`),
  KEY `kku_app_messages_status_index` (`status`),
  CONSTRAINT `kku_app_messages_template_id_foreign` FOREIGN KEY (`template_id`) REFERENCES `kku_app_message_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='站内信消息表';