<?php

require __DIR__.'/vendor/autoload.php';
$app = require_once __DIR__.'/bootstrap/app.php';
$app->make('Illuminate\Contracts\Console\Kernel')->bootstrap();

use Illuminate\Support\Facades\DB;

echo "Testing Marketing Officer Assignment\n";
echo "=====================================\n\n";

// 1. Check ImmigoPro category
echo "1. Checking ImmigoPro Category:\n";
$immigoproCategory = DB::table('user_categories')
    ->where('name', 'ImmigoPro')
    ->first();

if ($immigoproCategory) {
    echo "   ✓ ImmigoPro Category found (ID: {$immigoproCategory->id})\n\n";
} else {
    echo "   ✗ ImmigoPro Category NOT found\n\n";
}

// 2. Check marketing officers
echo "2. Checking Marketing Officers:\n";
$marketingOfficers = DB::table('users as u')
    ->join('model_has_roles as mhr', 'u.id', '=', 'mhr.model_id')
    ->join('roles as r', 'mhr.role_id', '=', 'r.id')
    ->where('r.name', 'marketing_officer')
    ->where('u.status', 'active')
    ->select('u.id', 'u.name', 'u.email')
    ->get();

if ($marketingOfficers->count() > 0) {
    echo "   Found " . $marketingOfficers->count() . " active marketing officers:\n";
    foreach ($marketingOfficers as $officer) {
        echo "   - ID: {$officer->id}, Name: {$officer->name}, Email: {$officer->email}\n";
    }
} else {
    echo "   ✗ No active marketing officers found\n";
}
echo "\n";

// 3. Check marketing_officer_users table
echo "3. Checking marketing_officer_users assignments:\n";
try {
    $assignments = DB::table('marketing_officer_users')
        ->join('users as u', 'marketing_officer_users.user_id', '=', 'u.id')
        ->join('users as mo', 'marketing_officer_users.marketing_officer_id', '=', 'mo.id')
        ->select(
            'marketing_officer_users.id',
            'mo.name as marketing_officer_name',
            'u.name as user_name',
            'u.email as user_email',
            'marketing_officer_users.assigned_at'
        )
        ->get();
    
    if ($assignments->count() > 0) {
        echo "   Found " . $assignments->count() . " assignment(s):\n";
        foreach ($assignments as $assignment) {
            echo "   - Marketing Officer: {$assignment->marketing_officer_name}\n";
            echo "     Assigned to: {$assignment->user_name} ({$assignment->user_email})\n";
            echo "     Assigned at: {$assignment->assigned_at}\n\n";
        }
    } else {
        echo "   No assignments found yet\n";
    }
} catch (\Exception $e) {
    echo "   Error: " . $e->getMessage() . "\n";
}
echo "\n";

echo "✓ Test complete!\n";
