-- Badge Generation System Tables
-- Created for XChange 2025 Event Badge System

-- Badge Settings Table - Stores event configuration and badge design settings
CREATE TABLE IF NOT EXISTS badge_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique identifier for the setting',
    setting_value TEXT NULL COMMENT 'Value of the setting (JSON for complex data)',
    setting_type ENUM('text', 'image', 'json', 'boolean') DEFAULT 'text' COMMENT 'Type of setting value',
    description TEXT NULL COMMENT 'Description of what this setting controls',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 
COMMENT='Stores badge generation settings and event configuration';

-- Badge Generation Log - Track badge generation history
CREATE TABLE IF NOT EXISTS badge_generation_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    delegate_id INT NULL COMMENT 'Delegate ID if single badge generated',
    registration_id INT NULL COMMENT 'Registration ID',
    generation_type ENUM('single', 'batch', 'preview') DEFAULT 'single',
    generated_by INT NULL COMMENT 'Admin user who generated the badge',
    file_path VARCHAR(500) NULL COMMENT 'Path to generated badge file',
    download_count INT DEFAULT 0 COMMENT 'Number of times badge was downloaded',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_delegate_id (delegate_id),
    INDEX idx_registration_id (registration_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (delegate_id) REFERENCES delegates(id) ON DELETE SET NULL,
    FOREIGN KEY (registration_id) REFERENCES registrations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 
COMMENT='Tracks badge generation history and downloads';

-- Insert default badge settings
INSERT INTO badge_settings (setting_key, setting_value, setting_type, description) VALUES
('event_name', 'Applied HE>X CHANGE', 'text', 'Event name displayed on badge'),
('event_dates', '27-28 FEB 2025', 'text', 'Event dates displayed on badge'),
('event_location', 'MANILA, PHILIPPINES', 'text', 'Event location displayed on badge'),
('badge_width', '340', 'text', 'Badge width in pixels'),
('badge_height', '500', 'text', 'Badge height in pixels'),
('enable_qr_code', 'true', 'boolean', 'Enable QR code on badges'),
('qr_code_base_url', '', 'text', 'Base URL for QR code (e.g., https://yoursite.com/verify/)'),
('event_logo_path', '', 'image', 'Path to main event logo'),
('university_logo_path', '', 'image', 'Path to hosting university logo'),
('partner_logo_path', '', 'image', 'Path to partner organization logo'),
('badge_background_color', '#ffffff', 'text', 'Badge background color'),
('badge_text_color', '#000000', 'text', 'Badge primary text color'),
('badge_accent_color', '#6B9F3E', 'text', 'Badge accent color for highlights'),
('show_hosting_logo', 'true', 'boolean', 'Show hosting university logo on badge'),
('show_partner_logo', 'true', 'boolean', 'Show partner organization logo on badge'),
('footer_decoration', 'true', 'boolean', 'Show decorative footer illustration')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Create uploads directory structure for badge assets
-- Note: This needs to be created on filesystem: uploads/badges/logos/
-- Note: This needs to be created on filesystem: uploads/badges/generated/


