-- 应付账单四段合计。total_amount 为账单合计，等于 A、B、C、D 四段之和。可重复执行。
SET NAMES utf8mb4;

SET @sql = (
  SELECT IF(
    EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_bill' AND COLUMN_NAME = 'amount_a'),
    'SELECT 1',
    'ALTER TABLE pay_bill ADD COLUMN amount_a decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT ''A段合计'' AFTER order_count'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(
    EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_bill' AND COLUMN_NAME = 'amount_b'),
    'SELECT 1',
    'ALTER TABLE pay_bill ADD COLUMN amount_b decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT ''B段合计'' AFTER amount_a'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(
    EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_bill' AND COLUMN_NAME = 'amount_c'),
    'SELECT 1',
    'ALTER TABLE pay_bill ADD COLUMN amount_c decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT ''C段合计'' AFTER amount_b'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
  SELECT IF(
    EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_bill' AND COLUMN_NAME = 'amount_d'),
    'SELECT 1',
    'ALTER TABLE pay_bill ADD COLUMN amount_d decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT ''D段合计'' AFTER amount_c'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE pay_bill
  MODIFY COLUMN total_amount decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '账单合计';