-- Migration: Add specials table and link to stages
-- Date: 2026-03-31

CREATE TABLE IF NOT EXISTS `specials` (
  `id` char(36) NOT NULL,
  `event_id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_event_id` (`event_id`),
  CONSTRAINT `specials_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Add special_id to stages
ALTER TABLE `stages` ADD COLUMN `special_id` char(36) DEFAULT NULL AFTER `event_id`;
ALTER TABLE `stages` ADD CONSTRAINT `stages_special_id_fk` FOREIGN KEY (`special_id`) REFERENCES `specials` (`id`) ON DELETE SET NULL;

-- View for cumulative rankings
-- Logic: Sum of rank_time_seconds for all stages in a special for the same user.
-- It uses the 'rankings' table which should have the final time for each stage.
CREATE OR REPLACE VIEW v_special_cumulative_rankings AS
SELECT 
    sp.id AS special_id,
    reg.user_id,
    u.username,
    SUM(r.rank_time_seconds) AS total_time_seconds,
    COUNT(r.id) AS stages_completed,
    (SELECT COUNT(*) FROM stages WHERE special_id = sp.id) AS total_stages_in_special
FROM specials sp
JOIN stages s ON s.special_id = sp.id
JOIN rankings r ON r.stage_id = s.id
JOIN registrations reg ON r.registration_id = reg.id
JOIN users u ON reg.user_id = u.id
GROUP BY sp.id, reg.user_id, u.username;
