-- Migration to support multiple reported players per report
-- Run this after the main player_reports_setup.sql

-- Add table for multiple reported players
CREATE TABLE IF NOT EXISTS `player_report_players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `license` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `discord` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `report_id` (`report_id`),
  KEY `license` (`license`),
  CONSTRAINT `fk_report_players_report` FOREIGN KEY (`report_id`) REFERENCES `player_reports` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migrate existing data from old single-player format
INSERT INTO `player_report_players` (report_id, license, name, discord)
SELECT id, reported_license, reported_name, reported_discord
FROM `player_reports`
WHERE reported_license IS NOT NULL;

-- Optional: Remove old columns after migration (uncomment if you want to clean up)
-- ALTER TABLE `player_reports` DROP COLUMN `reported_license`;
-- ALTER TABLE `player_reports` DROP COLUMN `reported_name`;
-- ALTER TABLE `player_reports` DROP COLUMN `reported_discord`;
