-- TecnoCric multi-tournament migration
-- BACK UP auction_db before running. Run once against the existing database.
-- Existing legacy data is assigned to the currently active auction (or newest auction).
SET @tc_legacy_auction_id := (SELECT id FROM auctions ORDER BY is_active DESC, auction_date DESC, id DESC LIMIT 1);
SET @tc_legacy_auction_id := COALESCE(@tc_legacy_auction_id, 1);

ALTER TABLE players ADD COLUMN auction_id INT NULL AFTER id;
ALTER TABLE teams ADD COLUMN auction_id INT NULL AFTER team_id;
ALTER TABLE auction_settings ADD COLUMN auction_id INT NULL AFTER id;
ALTER TABLE bid_increment_rules ADD COLUMN auction_id INT NULL AFTER id;
ALTER TABLE bid_history ADD COLUMN auction_id INT NULL AFTER id;

UPDATE players SET auction_id=@tc_legacy_auction_id WHERE auction_id IS NULL;
UPDATE teams SET auction_id=@tc_legacy_auction_id WHERE auction_id IS NULL;
UPDATE auction_settings SET auction_id=@tc_legacy_auction_id WHERE auction_id IS NULL;
UPDATE bid_increment_rules SET auction_id=@tc_legacy_auction_id WHERE auction_id IS NULL;
UPDATE bid_history SET auction_id=@tc_legacy_auction_id WHERE auction_id IS NULL;

ALTER TABLE players MODIFY auction_id INT NOT NULL;
ALTER TABLE teams MODIFY auction_id INT NOT NULL;
ALTER TABLE auction_settings MODIFY auction_id INT NOT NULL;
ALTER TABLE bid_increment_rules MODIFY auction_id INT NOT NULL;
ALTER TABLE bid_history MODIFY auction_id INT NOT NULL;

-- Serial numbers only need to be unique inside a tournament, not globally.
ALTER TABLE players DROP INDEX sr_no;
ALTER TABLE players ADD UNIQUE KEY uq_players_auction_sr_no (auction_id, sr_no);
ALTER TABLE players ADD KEY idx_players_auction_status (auction_id, status);
ALTER TABLE teams ADD KEY idx_teams_auction (auction_id);
ALTER TABLE auction_settings ADD UNIQUE KEY uq_settings_auction (auction_id);
ALTER TABLE bid_increment_rules ADD KEY idx_rules_auction_range (auction_id, from_amount, to_amount);
ALTER TABLE bid_history ADD KEY idx_bid_history_auction_time (auction_id, timestamp);

-- Ensure the legacy tournament has a settings row if the dump was incomplete.
INSERT INTO auction_settings (auction_id, base_price, team_budget, min_players_per_team, max_players_per_team, starting_points)
SELECT @tc_legacy_auction_id, 1000, 100000, 11, 16, 100000
WHERE NOT EXISTS (SELECT 1 FROM auction_settings WHERE auction_id=@tc_legacy_auction_id);