-- 银城大喇叭 M1：定向提醒与必读阅读闭环。
-- 保留 M0 的全租户公开消息语义；定向规则只决定优先提醒/必读对象，不隐藏公开频道内容。
-- 可重复执行，需在 aihr_20260722_broadcast_mysql8.sql 之后执行。

SET @has_broadcast_required_read := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE table_schema = DATABASE() AND table_name = 'aihr_broadcast_message'
    AND column_name = 'required_read'
);
SET @broadcast_m1_ddl := IF(
  @has_broadcast_required_read = 0,
  'ALTER TABLE aihr_broadcast_message ADD COLUMN required_read tinyint(1) NOT NULL DEFAULT 0 COMMENT ''是否必读'' AFTER content',
  'SELECT 1'
);
PREPARE aihr_broadcast_m1_stmt FROM @broadcast_m1_ddl; EXECUTE aihr_broadcast_m1_stmt; DEALLOCATE PREPARE aihr_broadcast_m1_stmt;

-- 用于发布幂等重试时校验必读/定向载荷。历史 M0 消息保留 NULL，等价于非必读且未定向。
SET @has_broadcast_target_payload_hash := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE table_schema = DATABASE() AND table_name = 'aihr_broadcast_message'
    AND column_name = 'target_payload_hash'
);
SET @broadcast_m1_ddl := IF(
  @has_broadcast_target_payload_hash = 0,
  'ALTER TABLE aihr_broadcast_message ADD COLUMN target_payload_hash char(64) DEFAULT NULL COMMENT ''必读及定向载荷SHA-256'' AFTER publish_request_hash',
  'SELECT 1'
);
PREPARE aihr_broadcast_m1_stmt FROM @broadcast_m1_ddl; EXECUTE aihr_broadcast_m1_stmt; DEALLOCATE PREPARE aihr_broadcast_m1_stmt;

CREATE TABLE IF NOT EXISTS `aihr_broadcast_target_rule` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '定向规则ID',
  `tenant_id` varchar(20) NOT NULL COMMENT '租户编号',
  `message_id` bigint NOT NULL COMMENT '消息ID',
  `target_type` varchar(20) NOT NULL COMMENT 'PROJECT/POSITION/LEVEL',
  `target_value` varchar(100) NOT NULL COMMENT '目标值',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_aihr_broadcast_target_rule` (`tenant_id`, `message_id`, `target_type`, `target_value`),
  KEY `idx_aihr_broadcast_target_rule_message` (`tenant_id`, `message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='银城大喇叭定向规则快照';

CREATE TABLE IF NOT EXISTS `aihr_broadcast_target_recipient` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '定向对象快照ID',
  `tenant_id` varchar(20) NOT NULL COMMENT '租户编号',
  `message_id` bigint NOT NULL COMMENT '消息ID',
  `recipient_key` varchar(180) NOT NULL COMMENT '外部主体及映射账号唯一键',
  `subject_ref` varchar(100) NOT NULL COMMENT '组织快照外部主体ID',
  `user_id` bigint DEFAULT NULL COMMENT '匹配到的员工账号ID',
  `target_status` varchar(20) NOT NULL COMMENT 'MATCHED/UNMATCHED',
  `target_reason` varchar(100) NOT NULL COMMENT '项目/岗位/职级定向原因',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_aihr_broadcast_target_recipient` (`tenant_id`, `message_id`, `recipient_key`),
  KEY `idx_aihr_broadcast_target_recipient_user` (`tenant_id`, `user_id`, `message_id`, `target_status`),
  KEY `idx_aihr_broadcast_target_recipient_message` (`tenant_id`, `message_id`, `target_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='银城大喇叭定向对象快照';

-- 与 M0 消息表保持同一租户事实排序规则，避免历史库的 utf8mb4_general_ci 与 MySQL 8 0900_ai_ci 混用。
SET @broadcast_m1_target_collation := (
  SELECT collation_name FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'aihr_knowledge_info' AND column_name = 'tenant_id'
  LIMIT 1
);
SET @broadcast_m1_target_collation := COALESCE(@broadcast_m1_target_collation, @@collation_database);

SET @broadcast_target_rule_collation := (
  SELECT table_collation FROM information_schema.tables
  WHERE table_schema = DATABASE() AND table_name = 'aihr_broadcast_target_rule'
  LIMIT 1
);
SET @broadcast_m1_ddl := IF(
  @broadcast_target_rule_collation = @broadcast_m1_target_collation,
  'SELECT 1',
  CONCAT('ALTER TABLE aihr_broadcast_target_rule CONVERT TO CHARACTER SET utf8mb4 COLLATE ', @broadcast_m1_target_collation)
);
PREPARE aihr_broadcast_m1_stmt FROM @broadcast_m1_ddl; EXECUTE aihr_broadcast_m1_stmt; DEALLOCATE PREPARE aihr_broadcast_m1_stmt;

SET @broadcast_target_recipient_collation := (
  SELECT table_collation FROM information_schema.tables
  WHERE table_schema = DATABASE() AND table_name = 'aihr_broadcast_target_recipient'
  LIMIT 1
);
SET @broadcast_m1_ddl := IF(
  @broadcast_target_recipient_collation = @broadcast_m1_target_collation,
  'SELECT 1',
  CONCAT('ALTER TABLE aihr_broadcast_target_recipient CONVERT TO CHARACTER SET utf8mb4 COLLATE ', @broadcast_m1_target_collation)
);
PREPARE aihr_broadcast_m1_stmt FROM @broadcast_m1_ddl; EXECUTE aihr_broadcast_m1_stmt; DEALLOCATE PREPARE aihr_broadcast_m1_stmt;

SET @has_broadcast_required_read := NULL;
SET @has_broadcast_target_payload_hash := NULL;
SET @broadcast_m1_target_collation := NULL;
SET @broadcast_target_rule_collation := NULL;
SET @broadcast_target_recipient_collation := NULL;
SET @broadcast_m1_ddl := NULL;