-- =============================================
-- 数据库迁移脚本：为金融计算增加关键字段
-- 执行前请备份数据
-- =============================================

-- 1. positions 表添加 total_cost 字段
ALTER TABLE positions ADD COLUMN total_cost DECIMAL(18,4) DEFAULT 0;

-- 初始化 total_cost（shares * avg_price）
UPDATE positions SET total_cost = shares * avg_price WHERE total_cost = 0 OR total_cost IS NULL;

-- 验证 positions 更新
SELECT 'positions' as table_name, COUNT(*) as total, 
       SUM(CASE WHEN total_cost > 0 THEN 1 ELSE 0 END) as with_cost
FROM positions;

-- =============================================

-- 2. transactions 表添加汇率相关字段
ALTER TABLE transactions ADD COLUMN exchange_rate DECIMAL(18,6) DEFAULT NULL;
ALTER TABLE transactions ADD COLUMN total_amount_base DECIMAL(18,4) DEFAULT NULL;

-- 为已有交易记录填充当前汇率（历史数据只能用当前汇率近似）
-- 注意：这会导致历史净值计算有汇率偏差，建议手动修正重要历史交易
-- UPDATE transactions t
-- JOIN portfolios p ON t.portfolio_id = p.id
-- SET t.exchange_rate = 1.0, t.total_amount_base = t.total_amount
-- WHERE t.currency = p.currency OR t.currency IS NULL;

-- 验证 transactions 更新
SELECT 'transactions' as table_name, COUNT(*) as total,
       SUM(CASE WHEN total_amount_base IS NOT NULL THEN 1 ELSE 0 END) as with_base_amount
FROM transactions;

-- =============================================

-- SQLite 版本（如果使用 SQLite）
-- ALTER TABLE positions ADD COLUMN total_cost REAL DEFAULT 0;
-- UPDATE positions SET total_cost = shares * avg_price;

-- SQLite 不支持多个 ADD COLUMN，需要分开执行
-- ALTER TABLE transactions ADD COLUMN exchange_rate REAL DEFAULT NULL;
-- ALTER TABLE transactions ADD COLUMN total_amount_base REAL DEFAULT NULL;