SET @equipment_area_column_exists := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'em_equipment'
    AND COLUMN_NAME = 'occupied_area_sqm'
);

SET @add_equipment_area_column_sql := IF(
  @equipment_area_column_exists = 0,
  'ALTER TABLE em_equipment ADD COLUMN occupied_area_sqm DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT ''占用场地面积(平方米)'' AFTER rated_power_kw',
  'SELECT ''em_equipment.occupied_area_sqm already exists'''
);

PREPARE add_equipment_area_column_stmt FROM @add_equipment_area_column_sql;
EXECUTE add_equipment_area_column_stmt;
DEALLOCATE PREPARE add_equipment_area_column_stmt;

ALTER TABLE fi_overhead_entry
  MODIFY COLUMN allocation_basis VARCHAR(32) NOT NULL DEFAULT 'WORK_HOURS' COMMENT '分摊依据：WORK_HOURS/MACHINE_HOURS/QTY/OCCUPIED_AREA';

UPDATE em_equipment
SET occupied_area_sqm = CASE equipment_code
  WHEN 'EQ-KP-01' THEN 12.50
  WHEN 'EQ-CY-01' THEN 16.00
  WHEN 'EQ-MC-01' THEN 8.00
  WHEN 'EQ-ZW-01' THEN 14.00
  WHEN 'EQ-HJ-01' THEN 6.00
  WHEN 'EQ-QA-01' THEN 4.00
  ELSE occupied_area_sqm
END
WHERE equipment_code IN ('EQ-KP-01', 'EQ-CY-01', 'EQ-MC-01', 'EQ-ZW-01', 'EQ-HJ-01', 'EQ-QA-01');
