-- Speeds up battery safety statistics across every physical charging-order shard.
DROP PROCEDURE IF EXISTS add_battery_statistics_indexes;
DELIMITER //
CREATE PROCEDURE add_battery_statistics_indexes()
BEGIN
  DECLARE finished BOOLEAN DEFAULT FALSE;
  DECLARE shard VARCHAR(64);
  DECLARE existing_index INT;
  DECLARE shards CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = DATABASE() AND table_name REGEXP '^t_charging_order(_[0-9]+)?$';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

  OPEN shards;
  add_indexes: LOOP
    FETCH shards INTO shard;
    IF finished THEN LEAVE add_indexes; END IF;
    SELECT COUNT(*) INTO existing_index FROM information_schema.statistics
    WHERE table_schema = DATABASE() AND table_name = shard AND index_name = 'idx_battery_statistics';
    IF existing_index = 0 THEN
      SET @sql = CONCAT('ALTER TABLE `', shard, '` ADD INDEX `idx_battery_statistics` '
          '(`charging_gun_id`, `status`, `del_flag`, `create_time`)');
      PREPARE statement FROM @sql;
      EXECUTE statement;
      DEALLOCATE PREPARE statement;
    END IF;
  END LOOP;
  CLOSE shards;
END //
DELIMITER ;
CALL add_battery_statistics_indexes();
DROP PROCEDURE add_battery_statistics_indexes;