upskill-event-manager/scripts/import-trainer-csv.php

<?php
/**
 * Import HVAC Trainers from CSV export
 * 
 * Usage: php scripts/import-trainer-csv.php /path/to/csv/file.csv
 */

// WordPress environment setup
if (!defined('WP_CLI') && !defined('ABSPATH')) {
    // Try to load WordPress
    $wp_load_paths = [
        __DIR__ . '/../wordpress/wp-load.php',
        __DIR__ . '/../wp-load.php',
        __DIR__ . '/../../../../wp-load.php',
        '/var/www/html/wp-load.php'
    ];
    
    foreach ($wp_load_paths as $path) {
        if (file_exists($path)) {
            require_once $path;
            break;
        }
    }
    
    if (!defined('ABSPATH')) {
        die("Could not load WordPress. Please run from WordPress root or adjust path.\n");
    }
}

class TrainerCSVImporter {
    
    private $dry_run = false;
    private $stats = [
        'processed' => 0,
        'created' => 0,
        'updated' => 0,
        'skipped' => 0,
        'errors' => 0
    ];
    
    public function __construct($dry_run = false) {
        $this->dry_run = $dry_run;
    }
    
    public function import_csv($csv_file) {
        if (!file_exists($csv_file)) {
            die("CSV file not found: $csv_file\n");
        }
        
        echo "Starting import from: $csv_file\n";
        if ($this->dry_run) {
            echo "DRY RUN MODE - No data will be modified\n";
        }
        echo "----------------------------------------\n";
        
        $handle = fopen($csv_file, 'r');
        if (!$handle) {
            die("Could not open CSV file\n");
        }
        
        // Read header row
        $headers = fgetcsv($handle);
        if (!$headers) {
            die("Could not read CSV headers\n");
        }
        
        // Process each row
        while (($row = fgetcsv($handle)) !== false) {
            $data = array_combine($headers, $row);
            $this->process_trainer_record($data);
            $this->stats['processed']++;
        }
        
        fclose($handle);
        $this->print_stats();
    }
    
    private function process_trainer_record($data) {
        // Skip if no email
        if (empty($data['Work Email'])) {
            echo "Skipping record with no email\n";
            $this->stats['skipped']++;
            return;
        }
        
        $email = sanitize_email($data['Work Email']);
        $existing_user = get_user_by('email', $email);
        
        if ($existing_user) {
            echo "Updating existing user: $email\n";
            $this->update_user($existing_user, $data);
            $this->stats['updated']++;
        } else {
            echo "Creating new user: $email\n";
            $this->create_user($data);
            $this->stats['created']++;
        }
    }
    
    private function create_user($data) {
        if ($this->dry_run) return;
        
        $email = sanitize_email($data['Work Email']);
        $username = $this->generate_username($email);
        
        // Generate random password - user will need to reset
        $password = wp_generate_password(12, true, true);
        
        $user_data = [
            'user_login' => $username,
            'user_email' => $email,
            'user_pass' => $password,
            'first_name' => sanitize_text_field($data['Name'] ?? ''),
            'last_name' => sanitize_text_field($data['Last Name'] ?? ''),
            'display_name' => trim(($data['Name'] ?? '') . ' ' . ($data['Last Name'] ?? '')),
            'role' => 'hvac_trainer'
        ];
        
        $user_id = wp_insert_user($user_data);
        
        if (is_wp_error($user_id)) {
            echo "Error creating user $email: " . $user_id->get_error_message() . "\n";
            $this->stats['errors']++;
            return;
        }
        
        $this->set_user_meta($user_id, $data);
        
        // Send password reset email
        $this->send_welcome_email($user_id);
    }
    
    private function update_user($user, $data) {
        if ($this->dry_run) return;
        
        // Update basic user data
        $user_data = [
            'ID' => $user->ID,
            'first_name' => sanitize_text_field($data['Name'] ?? ''),
            'last_name' => sanitize_text_field($data['Last Name'] ?? ''),
            'display_name' => trim(($data['Name'] ?? '') . ' ' . ($data['Last Name'] ?? ''))
        ];
        
        wp_update_user($user_data);
        
        // Ensure user has correct role
        $user_obj = new WP_User($user->ID);
        $user_obj->set_role('hvac_trainer');
        
        $this->set_user_meta($user->ID, $data);
    }
    
    private function set_user_meta($user_id, $data) {
        // Personal Information
        update_user_meta($user_id, 'personal_accreditation', sanitize_textarea_field($data['Personal Accreditations'] ?? ''));
        
        // Business Information
        update_user_meta($user_id, 'business_name', sanitize_text_field($data['Company Name'] ?? ''));
        update_user_meta($user_id, 'business_phone', sanitize_text_field($data['Phone Number'] ?? ''));
        update_user_meta($user_id, 'business_email', sanitize_email($data['Work Email'] ?? ''));
        update_user_meta($user_id, 'business_website', esc_url_raw($data['Company Website'] ?? ''));
        update_user_meta($user_id, 'business_type', $this->map_organization_type($data['Organization Type'] ?? ''));
        
        // Address Information
        update_user_meta($user_id, 'user_country', sanitize_text_field($data['Country'] ?? ''));
        update_user_meta($user_id, 'user_state', sanitize_text_field($data['State'] ?? ''));
        
        // Training Information
        $training_audience = $this->map_training_audience($data['Training Target'] ?? '');
        update_user_meta($user_id, 'training_audience', $training_audience);
        
        // Application Data
        update_user_meta($user_id, 'application_details', sanitize_textarea_field($data['Trainer Details'] ?? ''));
        
        // Profile Image - Map staging URL to production
        $profile_image_id = $this->map_profile_image($data['Profile Picture'] ?? '');
        if ($profile_image_id) {
            update_user_meta($user_id, 'profile_image_id', $profile_image_id);
        }
        
        // Default settings for imported users
        update_user_meta($user_id, 'account_status', 'approved'); // Pre-approved users
        update_user_meta($user_id, 'create_venue', 'Yes');
        
        // Create organizer and venue profiles
        $this->create_organizer_venue($user_id, $data);
    }
    
    private function map_organization_type($org_type) {
        $mapping = [
            'Training Organization' => 'Manufacturer',
            'Service Company' => 'Contractor',
            '' => 'Other'
        ];
        
        return $mapping[$org_type] ?? 'Other';
    }
    
    private function map_training_audience($target) {
        if (empty($target)) {
            return ['Anyone'];
        }
        
        // Convert single string to array format expected by system
        $mapping = [
            'Industry professionals' => ['Industry professionals'],
            'Internal staff' => ['Internal staff'],
            'Anyone' => ['Anyone']
        ];
        
        return $mapping[$target] ?? ['Anyone'];
    }
    
    private function map_profile_image($staging_url) {
        if (empty($staging_url)) {
            return null;
        }
        
        // Convert staging URL to production URL pattern
        $production_url = str_replace(
            'upskill-staging.measurequick.com',
            'upskill.measurequick.com', // Adjust this to your production domain
            $staging_url
        );
        
        // Try to find existing attachment by URL
        $attachment_id = attachment_url_to_postid($production_url);
        
        if (!$attachment_id) {
            // Try with staging URL in case it's already in the system
            $attachment_id = attachment_url_to_postid($staging_url);
        }
        
        if (!$attachment_id) {
            echo "Warning: Could not find attachment for URL: $staging_url\n";
            return null;
        }
        
        return $attachment_id;
    }
    
    private function create_organizer_venue($user_id, $data) {
        if ($this->dry_run) return;
        
        // Check if organizer already exists
        $existing_organizer = get_user_meta($user_id, 'hvac_organizer_id', true);
        if ($existing_organizer) {
            return; // Already has organizer
        }
        
        // Create Events Calendar organizer
        $organizer_data = [
            'post_title' => trim(($data['Name'] ?? '') . ' ' . ($data['Last Name'] ?? '')),
            'post_type' => 'tribe_organizer',
            'post_status' => 'publish',
            'meta_input' => [
                '_OrganizerEmail' => sanitize_email($data['Work Email'] ?? ''),
                '_OrganizerPhone' => sanitize_text_field($data['Phone Number'] ?? ''),
                '_OrganizerWebsite' => esc_url_raw($data['Company Website'] ?? '')
            ]
        ];
        
        $organizer_id = wp_insert_post($organizer_data);
        if ($organizer_id) {
            update_user_meta($user_id, 'hvac_organizer_id', $organizer_id);
        }
        
        // Create venue if business name exists
        if (!empty($data['Company Name'])) {
            $venue_data = [
                'post_title' => sanitize_text_field($data['Company Name']),
                'post_type' => 'tribe_venue',
                'post_status' => 'publish',
                'meta_input' => [
                    '_VenueCountry' => sanitize_text_field($data['Country'] ?? ''),
                    '_VenueStateProvince' => sanitize_text_field($data['State'] ?? ''),
                    '_VenueURL' => esc_url_raw($data['Company Website'] ?? '')
                ]
            ];
            
            $venue_id = wp_insert_post($venue_data);
            if ($venue_id) {
                update_user_meta($user_id, 'hvac_venue_id', $venue_id);
            }
        }
    }
    
    private function generate_username($email) {
        $username = sanitize_user(substr($email, 0, strpos($email, '@')));
        
        // Ensure username is unique
        $base_username = $username;
        $counter = 1;
        
        while (username_exists($username)) {
            $username = $base_username . $counter;
            $counter++;
        }
        
        return $username;
    }
    
    private function send_welcome_email($user_id) {
        $user = get_user_by('id', $user_id);
        if (!$user) return;
        
        // Send password reset email
        $reset_key = get_password_reset_key($user);
        if (!is_wp_error($reset_key)) {
            $reset_url = network_site_url("wp-login.php?action=rp&key=$reset_key&login=" . rawurlencode($user->user_login), 'login');
            
            $subject = 'Welcome to HVAC Upskill Platform';
            $message = "Hello {$user->first_name},\n\n";
            $message .= "Your trainer account has been created on the HVAC Upskill Platform.\n\n";
            $message .= "Please set your password by clicking this link:\n";
            $message .= $reset_url . "\n\n";
            $message .= "After setting your password, you can login at: " . wp_login_url() . "\n\n";
            $message .= "Welcome to the platform!\n";
            
            wp_mail($user->user_email, $subject, $message);
        }
    }
    
    private function print_stats() {
        echo "\n========================================\n";
        echo "Import Complete!\n";
        echo "========================================\n";
        echo "Processed: {$this->stats['processed']}\n";
        echo "Created: {$this->stats['created']}\n";
        echo "Updated: {$this->stats['updated']}\n";
        echo "Skipped: {$this->stats['skipped']}\n";
        echo "Errors: {$this->stats['errors']}\n";
    }
}

// Command line execution
if (php_sapi_name() === 'cli') {
    $csv_file = $argv[1] ?? '';
    $dry_run = in_array('--dry-run', $argv);
    
    if (empty($csv_file)) {
        echo "Usage: php import-trainer-csv.php <csv_file> [--dry-run]\n";
        echo "Example: php import-trainer-csv.php /path/to/trainers.csv\n";
        echo "Options:\n";
        echo "  --dry-run    Show what would be imported without making changes\n";
        exit(1);
    }
    
    $importer = new TrainerCSVImporter($dry_run);
    $importer->import_csv($csv_file);
}