-- Migration: Add bulk_registration_imports table
-- Date: 2025-09-10
-- Description: Track bulk CSV registration imports with payment status

CREATE TABLE IF NOT EXISTS bulk_registration_imports (
    id CHAR(36) PRIMARY KEY,
    stage_id CHAR(36) NOT NULL,
    organizer_id CHAR(36) NOT NULL,
    stripe_payment_intent_id VARCHAR(255) NOT NULL,
    registration_count INT NOT NULL,
    total_fee DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'completed', 'failed') DEFAULT 'pending',
    csv_data TEXT NOT NULL, -- JSON encoded array of registrations
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (stage_id) REFERENCES stages(id) ON DELETE CASCADE,
    FOREIGN KEY (organizer_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_stripe_payment_intent (stripe_payment_intent_id),
    INDEX idx_organizer (organizer_id),
    INDEX idx_stage (stage_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
