-- Transfer模块手续费功能数据库修改SQL
-- 添加手续费相关字段和功能

-- =====================================================
-- 1. 应用表添加手续费配置字段
-- =====================================================

-- 添加手续费相关字段到 transfer_apps 表
ALTER TABLE kku_transfer_apps 
ADD COLUMN fee_in_rate DECIMAL(5,4) DEFAULT 0.0000 COMMENT '转入手续费率(0.0000-1.0000)',
ADD COLUMN fee_out_rate DECIMAL(5,4) DEFAULT 0.0000 COMMENT '转出手续费率(0.0000-1.0000)',
ADD COLUMN fee_in_min DECIMAL(15,4) DEFAULT 0.0000 COMMENT '转入最低手续费',
ADD COLUMN fee_in_max DECIMAL(15,4) DEFAULT 0.0000 COMMENT '转入最高手续费(0为不限制)',
ADD COLUMN fee_out_min DECIMAL(15,4) DEFAULT 0.0000 COMMENT '转出最低手续费',
ADD COLUMN fee_out_max DECIMAL(15,4) DEFAULT 0.0000 COMMENT '转出最高手续费(0为不限制)',
ADD COLUMN fee_account_uid INT DEFAULT 0 COMMENT '手续费收取账户UID';

-- =====================================================
-- 2. 订单表添加手续费记录字段
-- =====================================================

-- 添加手续费相关字段到 transfer_orders 表
ALTER TABLE kku_transfer_orders
ADD COLUMN fee_rate DECIMAL(5,4) DEFAULT 0.0000 COMMENT '使用的手续费率',
ADD COLUMN fee_amount DECIMAL(15,4) DEFAULT 0.0000 COMMENT '手续费金额',
ADD COLUMN actual_amount DECIMAL(15,4) DEFAULT 0.0000 COMMENT '实际到账金额(扣除手续费后)';

-- =====================================================
-- 3. 手续费相关索引
-- =====================================================

-- 手续费账户查询索引
CREATE INDEX idx_transfer_apps_fee_account ON kku_transfer_apps(fee_account_uid);

-- 手续费金额统计索引
CREATE INDEX idx_transfer_orders_fee_amount ON kku_transfer_orders(fee_amount, created_at);

-- 应用手续费统计索引
CREATE INDEX idx_transfer_orders_app_fee ON kku_transfer_orders(transfer_app_id, fee_amount, created_at);

-- =====================================================
-- 4. 手续费统计视图
-- =====================================================

-- 创建手续费统计视图
CREATE VIEW v_transfer_fee_stats AS
SELECT 
    ta.id as app_id,
    ta.keyname as app_keyname,
    ta.title as app_title,
    COUNT(to1.id) as total_orders,
    SUM(to1.fee_amount) as total_fee_amount,
    AVG(to1.fee_rate) as avg_fee_rate,
    SUM(CASE WHEN to1.type = 1 THEN to1.fee_amount ELSE 0 END) as in_fee_amount,
    SUM(CASE WHEN to1.type = 2 THEN to1.fee_amount ELSE 0 END) as out_fee_amount,
    COUNT(CASE WHEN to1.type = 1 THEN 1 END) as in_orders,
    COUNT(CASE WHEN to1.type = 2 THEN 1 END) as out_orders,
    DATE(to1.created_at) as stat_date
FROM kku_transfer_apps ta
LEFT JOIN kku_transfer_orders to1 ON ta.id = to1.transfer_app_id
WHERE to1.status = 100 -- 只统计已完成的订单
GROUP BY ta.id, DATE(to1.created_at)
ORDER BY stat_date DESC, total_fee_amount DESC;

-- =====================================================
-- 5. 手续费计算函数
-- =====================================================

-- 创建手续费计算函数
DELIMITER //

CREATE FUNCTION calculate_transfer_fee(
    amount DECIMAL(15,4),
    fee_rate DECIMAL(5,4),
    min_fee DECIMAL(15,4),
    max_fee DECIMAL(15,4)
) RETURNS DECIMAL(15,4)
READS SQL DATA
DETERMINISTIC
COMMENT '计算转账手续费'
BEGIN
    DECLARE calculated_fee DECIMAL(15,4);
    
    -- 按比例计算手续费
    SET calculated_fee = amount * fee_rate;
    
    -- 应用最低手续费限制
    IF calculated_fee < min_fee THEN
        SET calculated_fee = min_fee;
    END IF;
    
    -- 应用最高手续费限制（如果设置了）
    IF max_fee > 0 AND calculated_fee > max_fee THEN
        SET calculated_fee = max_fee;
    END IF;
    
    RETURN calculated_fee;
END //

DELIMITER ;

-- =====================================================
-- 6. 手续费统计存储过程
-- =====================================================

-- 创建手续费统计存储过程
DELIMITER //

CREATE PROCEDURE get_fee_statistics(
    IN app_id INT,
    IN start_date DATE,
    IN end_date DATE
)
READS SQL DATA
COMMENT '获取指定应用和时间范围的手续费统计'
BEGIN
    SELECT 
        ta.keyname as app_name,
        ta.title as app_title,
        COUNT(to1.id) as total_orders,
        SUM(to1.amount) as total_amount,
        SUM(to1.fee_amount) as total_fee,
        AVG(to1.fee_rate) as avg_fee_rate,
        SUM(to1.actual_amount) as total_actual_amount,
        
        -- 转入统计
        COUNT(CASE WHEN to1.type = 1 THEN 1 END) as in_orders,
        SUM(CASE WHEN to1.type = 1 THEN to1.amount ELSE 0 END) as in_amount,
        SUM(CASE WHEN to1.type = 1 THEN to1.fee_amount ELSE 0 END) as in_fee,
        
        -- 转出统计
        COUNT(CASE WHEN to1.type = 2 THEN 1 END) as out_orders,
        SUM(CASE WHEN to1.type = 2 THEN to1.amount ELSE 0 END) as out_amount,
        SUM(CASE WHEN to1.type = 2 THEN to1.fee_amount ELSE 0 END) as out_fee,
        
        -- 时间范围
        start_date,
        end_date
    FROM kku_transfer_apps ta
    LEFT JOIN kku_transfer_orders to1 ON ta.id = to1.transfer_app_id
    WHERE (app_id = 0 OR ta.id = app_id)
    AND to1.status = 100
    AND DATE(to1.created_at) BETWEEN start_date AND end_date
    GROUP BY ta.id
    ORDER BY total_fee DESC;
END //

DELIMITER ;

-- =====================================================
-- 7. 示例数据和测试
-- =====================================================

-- 更新现有应用的手续费配置示例
UPDATE kku_transfer_apps 
SET 
    fee_in_rate = 0.0050,    -- 转入手续费 0.5%
    fee_out_rate = 0.0100,   -- 转出手续费 1.0%
    fee_in_min = 0.1000,     -- 转入最低手续费 0.1
    fee_in_max = 10.0000,    -- 转入最高手续费 10
    fee_out_min = 0.2000,    -- 转出最低手续费 0.2
    fee_out_max = 20.0000,   -- 转出最高手续费 20
    fee_account_uid = 1      -- 手续费收取账户
WHERE keyname = 'test_app';

-- 测试手续费计算函数
SELECT 
    100.0000 as amount,
    calculate_transfer_fee(100.0000, 0.0050, 0.1000, 10.0000) as calculated_fee,
    '应该返回 0.5000' as expected;

SELECT 
    10.0000 as amount,
    calculate_transfer_fee(10.0000, 0.0050, 0.1000, 10.0000) as calculated_fee,
    '应该返回 0.1000 (最低手续费)' as expected;

SELECT 
    5000.0000 as amount,
    calculate_transfer_fee(5000.0000, 0.0050, 0.1000, 10.0000) as calculated_fee,
    '应该返回 10.0000 (最高手续费)' as expected;

-- =====================================================
-- 8. 数据迁移和兼容性
-- =====================================================

-- 为现有订单设置默认的手续费字段值
UPDATE kku_transfer_orders 
SET 
    fee_rate = 0.0000,
    fee_amount = 0.0000,
    actual_amount = amount
WHERE fee_rate IS NULL OR actual_amount IS NULL;

-- =====================================================
-- 9. 权限和安全
-- =====================================================

-- 创建手续费管理角色（可选）
-- CREATE ROLE 'transfer_fee_manager';
-- GRANT SELECT, UPDATE ON kku_transfer_apps TO 'transfer_fee_manager';
-- GRANT SELECT ON kku_transfer_orders TO 'transfer_fee_manager';
-- GRANT EXECUTE ON PROCEDURE get_fee_statistics TO 'transfer_fee_manager';

-- =====================================================
-- 10. 监控和报警
-- =====================================================

-- 手续费异常监控查询
-- 查找手续费异常高的订单
SELECT 
    id,
    transfer_app_id,
    amount,
    fee_rate,
    fee_amount,
    actual_amount,
    created_at
FROM kku_transfer_orders 
WHERE fee_rate > 0.1000  -- 手续费率超过10%
OR fee_amount > amount * 0.1  -- 手续费超过金额的10%
ORDER BY created_at DESC
LIMIT 100;

-- 手续费收入日报
SELECT 
    DATE(created_at) as date,
    COUNT(*) as orders,
    SUM(fee_amount) as total_fee,
    AVG(fee_rate) as avg_rate
FROM kku_transfer_orders 
WHERE status = 100
AND fee_amount > 0
AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date DESC;