-- ============================================================================
-- 优匠优服 · Phase 12 对标奇兵到家业务体系高级演进
-- 1. 多商品组合明细表 fa_order_goods
-- 2. 主订单表 fa_order 扩展超时转单、空跑费与施工增项补差价
-- ============================================================================
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `fa_order_goods` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属主订单ID',
  `service_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务类目ID',
  `goods_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名称',
  `goods_model` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '规格型号',
  `goods_num` INT(11) NOT NULL DEFAULT 1 COMMENT '商品数量',
  `goods_image` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '商品实物照片',
  `addon_services` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '增项服务(拆旧/打孔/切割)',
  `base_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '单项服务基础价格',
  `createtime` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单多商品组合明细表';

-- 检查并添加字段
SET @dbname = DATABASE();
SET @tablename = "fa_order";

-- auto_quote_timeout
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'auto_quote_timeout') > 0,
  'SELECT 1',
  'ALTER TABLE `fa_order` ADD COLUMN `auto_quote_timeout` INT(11) NOT NULL DEFAULT 0 COMMENT "超时转报价单时长(秒)"'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- empty_run_fee
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'empty_run_fee') > 0,
  'SELECT 1',
  'ALTER TABLE `fa_order` ADD COLUMN `empty_run_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT "空跑补偿费"'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- diff_amount
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'diff_amount') > 0,
  'SELECT 1',
  'ALTER TABLE `fa_order` ADD COLUMN `diff_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT "增项补差价金额"'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- diff_status
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'diff_status') > 0,
  'SELECT 1',
  'ALTER TABLE `fa_order` ADD COLUMN `diff_status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT "补差价状态:0无,1待支付,2已支付"'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- diff_remark
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'diff_remark') > 0,
  'SELECT 1',
  'ALTER TABLE `fa_order` ADD COLUMN `diff_remark` VARCHAR(255) NOT NULL DEFAULT "" COMMENT "补差价原因"'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- diff_image
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = 'diff_image') > 0,
  'SELECT 1',
  'ALTER TABLE `fa_order` ADD COLUMN `diff_image` VARCHAR(255) NOT NULL DEFAULT "" COMMENT "补差价凭据图"'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;