<?php
/**
 * Direct SQL Execution for Badge Tables
 */

require_once 'db_config.php';

try {
    $pdo = getDBConnection();
    
    echo "🔧 Creating Badge System Tables...\n\n";
    
    // Create badge_settings table
    echo "📊 Creating badge_settings table...\n";
    $pdo->exec("
        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'
    ");
    echo "   ✅ badge_settings table created\n";
    
    // Create badge_generation_log table
    echo "\n📝 Creating badge_generation_log table...\n";
    $pdo->exec("
        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'
    ");
    echo "   ✅ badge_generation_log table created\n";
    
    // Insert default settings
    echo "\n⚙️ Inserting default badge settings...\n";
    
    $settings = [
        ['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'],
        ['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']
    ];
    
    $stmt = $pdo->prepare("
        INSERT INTO badge_settings (setting_key, setting_value, setting_type, description) 
        VALUES (?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE 
            setting_value = VALUES(setting_value),
            setting_type = VALUES(setting_type),
            description = VALUES(description),
            updated_at = CURRENT_TIMESTAMP
    ");
    
    foreach ($settings as $setting) {
        $stmt->execute($setting);
    }
    
    $count = $pdo->query("SELECT COUNT(*) FROM badge_settings")->fetchColumn();
    echo "   ✅ {$count} settings inserted/updated\n";
    
    // Verify tables
    echo "\n🔍 Verification:\n";
    $tables = $pdo->query("SHOW TABLES LIKE 'badge%'")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $table) {
        echo "   ✅ {$table}\n";
    }
    
    echo "\n✅ Badge System Database Setup Complete!\n\n";
    echo "🚀 Ready to use!\n";
    echo "   Go to: http://localhost/Xchange_Admin/admin/badges\n\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
    exit(1);
}
?>


