haha/docs/database/add_order_discount_and_paid_amount.sql

-- =====================================================
-- 订单表添加优惠金额和实付总金额字段
-- 创建时间: 2026-04-13
-- 说明: 
--   - discount_amount: 订单优惠金额（营销活动优惠 + 优惠券优惠）
--   - paid_amount: 实付总金额 = total_amount - discount_amount
-- =====================================================

-- 添加优惠金额字段
ALTER TABLE `t_order` 
ADD COLUMN `discount_amount` DECIMAL(10, 2) DEFAULT 0.00 COMMENT '订单优惠金额（营销活动优惠 + 优惠券优惠）' AFTER `total_amount`;

-- 添加实付总金额字段
ALTER TABLE `t_order` 
ADD COLUMN `paid_amount` DECIMAL(10, 2) DEFAULT 0.00 COMMENT '实付总金额 = total_amount - discount_amount' AFTER `discount_amount`;

-- 更新已有数据：如果没有优惠金额，则paid_amount = total_amount
UPDATE `t_order` 
SET `discount_amount` = 0.00, 
    `paid_amount` = `total_amount` 
WHERE `discount_amount` IS NULL OR `paid_amount` IS NULL;

-- 添加索引（可选，用于按优惠金额查询）
-- ALTER TABLE `t_order` ADD INDEX `idx_discount_amount` (`discount_amount`);

-- 验证
SELECT 
    id, 
    order_no, 
    total_amount, 
    discount_amount, 
    paid_amount,
    (total_amount - discount_amount) as calculated_paid_amount
FROM `t_order` 
ORDER BY id DESC 
LIMIT 10;