-- Remove the handshake VIN column from every physical charging-order shard.
-- Execute directly on the backend MySQL instance, not through the database proxy.
-- The procedure is idempotent: only tables that still own the column are altered.
-- After this change all charging orders (CD/SD/VD) store the device-reported VIN in actual_vin only.

USE `charging_pile_order`;

DROP PROCEDURE IF EXISTS `drop_charging_order_vin`;
DELIMITER $$
CREATE PROCEDURE `drop_charging_order_vin`()
BEGIN
    DECLARE shard_no INT DEFAULT 0;
    DECLARE target_table VARCHAR(64);
    DECLARE table_exists INT DEFAULT 0;
    DECLARE column_exists INT DEFAULT 0;

    WHILE shard_no < 50 DO
        SET target_table = CONCAT('t_charging_order_', shard_no);

        SELECT COUNT(*) INTO table_exists
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_name = target_table;

        SELECT COUNT(*) INTO column_exists
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = target_table
          AND column_name = 'vin';

        IF table_exists = 1 AND column_exists = 1 THEN
            SET @ddl = CONCAT('ALTER TABLE `', target_table, '` DROP COLUMN `vin`');
            PREPARE stmt FROM @ddl;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        SET shard_no = shard_no + 1;
    END WHILE;
END$$
DELIMITER ;

CALL `drop_charging_order_vin`();
DROP PROCEDURE `drop_charging_order_vin`;

SELECT COUNT(*) AS shard_count,
       SUM(CASE WHEN c.column_name = 'vin' THEN 1 ELSE 0 END) AS vin_column_count
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
       ON c.table_schema = t.table_schema
      AND c.table_name = t.table_name
      AND c.column_name = 'vin'
WHERE t.table_schema = DATABASE()
  AND t.table_name REGEXP '^t_charging_order_[0-9]+$';