-- ============================================================
-- Migration 006: Assign data existing ke user #1 + update constraints
-- JALANKAN SETELAH seed_admin.py!
-- ============================================================

-- Assign semua data existing ke user #1
UPDATE portfolio SET user_id = 1 WHERE user_id IS NULL;
UPDATE trade_history SET user_id = 1 WHERE user_id IS NULL;
UPDATE watchlist SET user_id = 1 WHERE user_id IS NULL;
UPDATE watchlist_groups SET user_id = 1 WHERE user_id IS NULL;
UPDATE capital_allocations SET user_id = 1 WHERE user_id IS NULL;

-- Buat NOT NULL setelah data terisi
ALTER TABLE portfolio MODIFY user_id INT NOT NULL;
ALTER TABLE trade_history MODIFY user_id INT NOT NULL;
ALTER TABLE watchlist MODIFY user_id INT NOT NULL;
ALTER TABLE watchlist_groups MODIFY user_id INT NOT NULL;
ALTER TABLE capital_allocations MODIFY user_id INT NOT NULL;

-- Update unique constraints: portfolio
ALTER TABLE portfolio DROP INDEX coin_id;
ALTER TABLE portfolio ADD UNIQUE INDEX uq_portfolio_user_coin (user_id, coin_id);

-- Update unique constraints: watchlist
ALTER TABLE watchlist DROP INDEX uq_watchlist_coin_group;
ALTER TABLE watchlist ADD UNIQUE INDEX uq_watchlist_user_coin_group (user_id, coin_id, group_id);

-- Update unique constraints: capital_allocations
ALTER TABLE capital_allocations DROP INDEX coin_id;
ALTER TABLE capital_allocations ADD UNIQUE INDEX uq_capital_user_coin (user_id, coin_id);

-- Index baru untuk performa
ALTER TABLE trade_history ADD INDEX idx_trade_user_coin (user_id, coin_id, executed_at);
ALTER TABLE watchlist_groups ADD INDEX idx_wg_share_token (share_token);