SET @daily_chat_limit_exists=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='membership_plans' AND COLUMN_NAME='daily_active_chat_limit');
SET @daily_chat_limit_sql=IF(@daily_chat_limit_exists=0,'ALTER TABLE membership_plans ADD COLUMN daily_active_chat_limit INT UNSIGNED NOT NULL DEFAULT 20 AFTER duration_days','SELECT 1');
PREPARE daily_chat_limit_stmt FROM @daily_chat_limit_sql;
EXECUTE daily_chat_limit_stmt;
DEALLOCATE PREPARE daily_chat_limit_stmt;

UPDATE membership_plans
SET daily_active_chat_limit=CASE WHEN level>=2 THEN 100 ELSE 20 END
WHERE daily_active_chat_limit=20;

INSERT INTO system_configs(config_key,config_value,value_type,description) VALUES
  ('membership.free_daily_active_chat_limit','5','integer','普通用户每日可主动聊天的不同用户数，0 表示不限制')
ON DUPLICATE KEY UPDATE description=VALUES(description);

CREATE TABLE IF NOT EXISTS im_daily_active_chat_usage (
  user_id BIGINT UNSIGNED NOT NULL,
  usage_date DATE NOT NULL,
  used_count INT UNSIGNED NOT NULL DEFAULT 0,
  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 (user_id,usage_date),
  KEY idx_daily_chat_usage_date (usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS im_daily_active_chat_targets (
  user_id BIGINT UNSIGNED NOT NULL,
  target_user_id BIGINT UNSIGNED NOT NULL,
  usage_date DATE NOT NULL,
  conversation_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (user_id,target_user_id,usage_date),
  KEY idx_daily_chat_target_date (target_user_id,usage_date),
  KEY idx_daily_chat_conversation (conversation_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;