SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS im_message_media (
  message_id BIGINT UNSIGNED NOT NULL,
  media_asset_id BIGINT UNSIGNED NULL,
  media_type VARCHAR(20) NOT NULL,
  public_url VARCHAR(500) NOT NULL DEFAULT '',
  duration_ms INT UNSIGNED NULL,
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0 deleted, 1 active, 2 deleting, 3 cleanup failed',
  delete_reason VARCHAR(255) NOT NULL DEFAULT '',
  cleanup_error VARCHAR(500) NOT NULL DEFAULT '',
  deleted_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (message_id),
  KEY idx_im_message_media_cleanup (status, created_at),
  KEY idx_im_message_media_asset (media_asset_id),
  KEY idx_im_message_media_type_created (media_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Associate media messages that predate this table with their immutable upload
-- record. A nullable asset id keeps the audit row visible even if historical
-- data was imported without a matching media_assets entry.
INSERT IGNORE INTO im_message_media (
  message_id,
  media_asset_id,
  media_type,
  public_url,
  duration_ms,
  status,
  created_at
)
SELECT
  m.id,
  (
    SELECT ma.id
    FROM media_assets ma
    WHERE ma.owner_user_id=m.sender_id
      AND ma.public_url=JSON_UNQUOTE(JSON_EXTRACT(IF(JSON_VALID(CONVERT(m.body USING utf8mb4)),CONVERT(m.body USING utf8mb4),'{}'), '$.url'))
      AND ma.media_type=IF(m.message_type=2, 'image', 'audio')
    ORDER BY ma.id DESC
    LIMIT 1
  ),
  IF(m.message_type=2, 'image', 'voice'),
  JSON_UNQUOTE(JSON_EXTRACT(IF(JSON_VALID(CONVERT(m.body USING utf8mb4)),CONVERT(m.body USING utf8mb4),'{}'), '$.url')),
  IF(
    m.message_type=3,
    CAST(JSON_UNQUOTE(JSON_EXTRACT(IF(JSON_VALID(CONVERT(m.body USING utf8mb4)),CONVERT(m.body USING utf8mb4),'{}'), '$.duration')) AS UNSIGNED) * 1000,
    NULL
  ),
  1,
  m.created_at
FROM im_messages m
WHERE m.message_type IN (2,3)
  AND JSON_VALID(CONVERT(m.body USING utf8mb4))
  AND JSON_UNQUOTE(JSON_EXTRACT(IF(JSON_VALID(CONVERT(m.body USING utf8mb4)),CONVERT(m.body USING utf8mb4),'{}'), '$.url')) IS NOT NULL;

INSERT INTO system_configs (config_key, config_value, value_type, description) VALUES
  ('im.chat_media_retention_enabled', 'false', 'boolean', '是否按保留天数自动清理聊天图片和语音文件'),
  ('im.chat_media_retention_days', '90', 'number', '聊天图片和语音文件保留天数（1-3650）'),
  ('im.chat_media_cleanup_last_at', '', 'string', '聊天媒体最近一次定期清理时间'),
  ('im.chat_media_cleanup_last_result', '', 'string', '聊天媒体最近一次定期清理结果')
ON DUPLICATE KEY UPDATE
  value_type=VALUES(value_type),
  description=VALUES(description);