-- 销售订单模块新增字段（2026-05-11）
-- 说明：
-- 1) 脚本按“存在则跳过，不存在则新增”执行，可重复执行。
-- 2) 物料号统一字段为 material_no；若历史存在 material，会回填到 material_no。

SET @db_name = DATABASE();

-- ----------------------------
-- sales_ledger 新增字段
-- ----------------------------
SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger'
  AND COLUMN_NAME = 'order_type';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger ADD COLUMN order_type VARCHAR(64) NULL COMMENT ''订单类型''',
              'SELECT ''skip: sales_ledger.order_type exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger'
  AND COLUMN_NAME = 'order_line';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger ADD COLUMN order_line VARCHAR(64) NULL COMMENT ''订单行''',
              'SELECT ''skip: sales_ledger.order_line exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger'
  AND COLUMN_NAME = 'demand_date';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger ADD COLUMN demand_date DATE NULL COMMENT ''需求日期''',
              'SELECT ''skip: sales_ledger.demand_date exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ----------------------------
-- sales_ledger_product 新增字段
-- ----------------------------
SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger_product'
  AND COLUMN_NAME = 'delivery_quantity';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger_product ADD COLUMN delivery_quantity DECIMAL(18,6) NULL COMMENT ''交货数量''',
              'SELECT ''skip: sales_ledger_product.delivery_quantity exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger_product'
  AND COLUMN_NAME = 'remaining_quantity';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger_product ADD COLUMN remaining_quantity DECIMAL(18,6) NULL COMMENT ''剩余数量''',
              'SELECT ''skip: sales_ledger_product.remaining_quantity exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger_product'
  AND COLUMN_NAME = 'sub_inventory';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger_product ADD COLUMN sub_inventory VARCHAR(128) NULL COMMENT ''子库存''',
              'SELECT ''skip: sales_ledger_product.sub_inventory exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger_product'
  AND COLUMN_NAME = 'location';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger_product ADD COLUMN location VARCHAR(128) NULL COMMENT ''货位''',
              'SELECT ''skip: sales_ledger_product.location exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger_product'
  AND COLUMN_NAME = 'is_spray';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger_product ADD COLUMN is_spray TINYINT(1) NULL COMMENT ''是否喷砂''',
              'SELECT ''skip: sales_ledger_product.is_spray exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT COUNT(*) INTO @cnt
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger_product'
  AND COLUMN_NAME = 'material_no';
SET @sql = IF(@cnt = 0,
              'ALTER TABLE sales_ledger_product ADD COLUMN material_no VARCHAR(128) NULL COMMENT ''物料号''',
              'SELECT ''skip: sales_ledger_product.material_no exists''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ----------------------------
-- 历史数据兼容：material -> material_no
-- ----------------------------
SELECT COUNT(*) INTO @has_old_material
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger_product'
  AND COLUMN_NAME = 'material';

SELECT COUNT(*) INTO @has_new_material_no
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'sales_ledger_product'
  AND COLUMN_NAME = 'material_no';

SET @sql = IF(@has_old_material = 1 AND @has_new_material_no = 1,
              'UPDATE sales_ledger_product
               SET material_no = material
               WHERE (material_no IS NULL OR material_no = '''')
                 AND material IS NOT NULL',
              'SELECT ''skip: material -> material_no backfill''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 可选：确认前端/后端都已切换后，再评估是否删除旧列 material
-- ALTER TABLE sales_ledger_product DROP COLUMN material;