-- Add new media and facts fields to participants table
-- Run this script to add photo, motion video, and facts functionality

-- Add photo field
ALTER TABLE participants 
ADD COLUMN photo VARCHAR(255) NULL COMMENT 'Path to participant photo file';

-- Add motion video field  
ALTER TABLE participants 
ADD COLUMN motion_video VARCHAR(255) NULL COMMENT 'Path to participant motion/video file';

-- Create participant_facts table for repeater field functionality
CREATE TABLE participant_facts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    participant_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    fact TEXT NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE,
    INDEX idx_participant_sort (participant_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Add index on photo and motion_video fields for better performance
ALTER TABLE participants 
ADD INDEX idx_photo (photo),
ADD INDEX idx_motion_video (motion_video);
