<?php

namespace App\Console\Commands;

use App\Models\Committee;
use App\Models\User;
use App\Models\WorkGroup;
use App\Services\MicrosoftGraphSyncService;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class DiagnoseMicrosoftGraphSyncCommand extends Command
{
    protected $signature = 'msgraph:diagnose
                           {--detailed : Show detailed user information}
                           {--check-contacts : Check existing contacts in Microsoft Graph}';

    protected $description = 'Diagnose Microsoft Graph sync discrepancies between database and synced contacts';

    protected $syncService;

    public function __construct(MicrosoftGraphSyncService $syncService)
    {
        parent::__construct();
        $this->syncService = $syncService;
    }

    public function handle()
    {
        $this->info('🔍 Diagnosing Microsoft Graph Sync Discrepancies...');

        // Initialize the service - this will check if MS Graph is available
        if (!MicrosoftGraphSyncService::isAvailable()) {
            $this->error('❌ Microsoft Graph is not available. Please run migrations first.');
            return Command::FAILURE;
        }
        $this->syncService->initialize();

        // Step 1: Analyze database data
        $this->analyzeDatabaseData();

        // Step 2: Check Microsoft Graph if requested
        if ($this->option('check-contacts')) {
            $this->analyzeGraphContacts();
        }

        // Step 3: Find discrepancies
        $this->findDiscrepancies();

        return Command::SUCCESS;
    }

    protected function analyzeDatabaseData()
    {
        $this->info('📊 Analyzing Database Data:');

        // Committee users analysis
        $totalCommitteeUsers = DB::table('committee_user')->count();
        $uniqueCommitteeUsers = DB::table('committee_user')->distinct('user_id')->count('user_id');

        $this->table(['Metric', 'Count'], [
            ['Total committee_user records', $totalCommitteeUsers],
            ['Unique users in committees', $uniqueCommitteeUsers]
        ]);

        // Check for users without email
        $usersWithoutEmail = DB::table('committee_user')
            ->join('users', 'committee_user.user_id', '=', 'users.id')
            ->whereNull('users.email')
            ->orWhere('users.email', '')
            ->count();

        $this->table(['Issue Check', 'Count'], [
            ['Users without email in committees', $usersWithoutEmail],
        ]);

        // Check for inactive users
        $inactiveUsers = DB::table('committee_user')
            ->join('users', 'committee_user.user_id', '=', 'users.id')
            ->where('users.state', '!=', 'active')
            ->count();

        $this->table(['State Check', 'Count'], [
            ['Inactive users in committees', $inactiveUsers],
        ]);

        // Show committees and their user counts
        $committees = Committee::full()->where('status', 'published')->get();

        $this->info('🏛️ Published Committees with Active Users:');
        $tableData = [];
        foreach ($committees as $committee) {
            $activeUsers = $committee->users()->where('state', 'active')
                ->whereNotNull('email')
                ->where('email', '!=', '')
                ->count();

            $tableData[] = [
                'ID' => $committee->id,
                'Title' => $committee->title,
                'Active Users' => $activeUsers,
                'MS ID' => $committee->ms_id ? 'Yes' : 'No'
            ];
        }
        $this->table(['ID', 'Title', 'Active Users', 'MS ID'], $tableData);

        // Show workgroups and their user counts
        $workgroups = WorkGroup::full()->where('status', 'published')->get();

        $this->info('👨‍💼 Published WorkGroups with Active Users:');
        $tableData = [];
        foreach ($workgroups as $workgroup) {
            $activeUsers = $workgroup->users()->where('state', 'active')
                ->whereNotNull('email')
                ->where('email', '!=', '')
                ->count();

            $tableData[] = [
                'ID' => $workgroup->id,
                'Title' => $workgroup->title,
                'Active Users' => $activeUsers,
                'MS ID' => $workgroup->ms_id ? 'Yes' : 'No'
            ];
        }
        $this->table(['ID', 'Title', 'Active Users', 'MS ID'], $tableData);

        // Calculate expected total unique users to sync using Eloquent models
        $committeeUsers = collect();
        $workgroupUsers = collect();

        $publishedCommittees = Committee::full()->where('status', 'published')->get();
        foreach ($publishedCommittees as $committee) {
            $activeUsers = $committee->users()->where('state', 'active')
                ->whereNotNull('email')
                ->where('email', '!=', '')
                ->get();
            $committeeUsers = $committeeUsers->merge($activeUsers);
        }

        $publishedWorkgroups = WorkGroup::full()->where('status', 'published')->get();
        foreach ($publishedWorkgroups as $workgroup) {
            $activeUsers = $workgroup->users()->where('state', 'active')
                ->whereNotNull('email')
                ->where('email', '!=', '')
                ->get();
            $workgroupUsers = $workgroupUsers->merge($activeUsers);
        }

        $allUsers = $committeeUsers->merge($workgroupUsers)->unique('id');
        $expectedUsersToSync = $allUsers->count();

        $this->info("📈 Expected unique users to sync: {$expectedUsersToSync}");

        if ($this->option('detailed')) {
            $this->showDetailedUserInfo();
            $this->showExcludedCommitteeUsers();
        }
    }

    protected function showDetailedUserInfo()
    {
        $this->info('👥 Detailed User Information:');

        // Use the same logic as in findDiscrepancies to get expected users
        $committeeUsers = collect();
        $workgroupUsers = collect();

        $publishedCommittees = Committee::full()->where('status', 'published')->get();
        foreach ($publishedCommittees as $committee) {
            $activeUsers = $committee->users()->where('state', 'active')
                ->whereNotNull('email')
                ->where('email', '!=', '')
                ->select('id', 'name', 'last_name', 'email')
                ->get();
            $committeeUsers = $committeeUsers->merge($activeUsers);
        }

        $publishedWorkgroups = WorkGroup::full()->where('status', 'published')->get();
        foreach ($publishedWorkgroups as $workgroup) {
            $activeUsers = $workgroup->users()->where('state', 'active')
                ->whereNotNull('email')
                ->where('email', '!=', '')
                ->select('id', 'name', 'last_name', 'email')
                ->get();
            $workgroupUsers = $workgroupUsers->merge($activeUsers);
        }

        $users = $committeeUsers->merge($workgroupUsers)->unique('id');

        $tableData = [];
        foreach ($users as $user) {
            // Get committees and workgroups for this user using Eloquent
            $userId = $user->id;
            $userData = User::where('id', $userId)
                ->with([
                    'committees' => function ($query) {
                        $query->full()->where('status', 'published');
                    },
                    'workGroups' => function ($query) {
                        $query->full()->where('status', 'published');
                    }
                ])
                ->first();

            $committees = $userData->committees->pluck('title')->toArray();
            $workGroups = $userData->workGroups->pluck('title')->toArray();

            $tableData[] = [
                'ID' => $user->id,
                'Name' => $user->name . ' ' . $user->last_name,
                'Email' => $user->email,
                'Committees' => implode(', ', $committees),
                'WorkGroups' => implode(', ', $workGroups),
            ];
        }

        $this->table(['ID', 'Name', 'Email', 'Committees', 'WorkGroups'], $tableData);
    }

    protected function analyzeGraphContacts()
    {
        $this->info('🔍 Analyzing Microsoft Graph Contacts...');

        try {
            $allContacts = $this->syncService->listAllContacts();
            $totalGraphContacts = 0;
            $contactsByFolder = [];

            foreach ($allContacts as $folderName => $contacts) {
                $contactCount = count($contacts);
                $totalGraphContacts += $contactCount;
                $contactsByFolder[$folderName] = $contactCount;

                if ($this->option('detailed')) {
                    $this->info("📁 {$folderName}: {$contactCount} contacts");
                    foreach ($contacts as $contact) {
                        $email = $contact['emailAddresses'][0]['address'] ?? 'No email';
                        $name = $contact['displayName'] ?? 'No name';
                        $this->line("   - {$name} ({$email})");
                    }
                }
            }

            $this->table(['Folder', 'Contact Count'],
                array_map(function($folder, $count) {
                    return [$folder, $count];
                }, array_keys($contactsByFolder), array_values($contactsByFolder))
            );

            $this->info("📊 Total contacts in Microsoft Graph: {$totalGraphContacts}");

        } catch (\Exception $e) {
            $this->error('❌ Error analyzing Graph contacts: ' . $e->getMessage());
        }
    }

    protected function findDiscrepancies()
    {
        $this->info('🔍 Finding Discrepancies...');

        // Use the same logic as in analyzeDatabaseData to get expected users
        $committeeUsers = collect();
        $workgroupUsers = collect();

        $publishedCommittees = Committee::full()->where('status', 'published')->get();
        foreach ($publishedCommittees as $committee) {
            $activeUsers = $committee->users()->where('state', 'active')
                ->whereNotNull('email')
                ->where('email', '!=', '')
                ->get();
            $committeeUsers = $committeeUsers->merge($activeUsers);
        }

        $publishedWorkgroups = WorkGroup::full()->where('status', 'published')->get();
        foreach ($publishedWorkgroups as $workgroup) {
            $activeUsers = $workgroup->users()->where('state', 'active')
                ->whereNotNull('email')
                ->where('email', '!=', '')
                ->get();
            $workgroupUsers = $workgroupUsers->merge($activeUsers);
        }

        $expectedUsers = $committeeUsers->merge($workgroupUsers)->unique('id');

        $this->info("📋 Expected users to sync: {$expectedUsers->count()}");

        // Check for users without proper email
        $usersWithBadEmail = $expectedUsers->filter(function($user) {
            return empty($user->email) || !filter_var($user->email, FILTER_VALIDATE_EMAIL);
        });

        if ($usersWithBadEmail->count() > 0) {
            $this->warn("⚠️ Users with invalid emails: {$usersWithBadEmail->count()}");
            if ($this->option('detailed')) {
                foreach ($usersWithBadEmail as $user) {
                    $this->line("   - ID {$user->id}: {$user->name} {$user->last_name} ({$user->email})");
                }
            }
        }

        // Check for users without name
        $usersWithoutName = $expectedUsers->filter(function($user) {
            return empty(trim(($user->name ?? '') . ' ' . ($user->last_name ?? '')));
        });

        if ($usersWithoutName->count() > 0) {
            $this->warn("⚠️ Users without proper names: {$usersWithoutName->count()}");
            if ($this->option('detailed')) {
                foreach ($usersWithoutName as $user) {
                    $this->line("   - ID {$user->id}: '{$user->name}' '{$user->last_name}' ({$user->email})");
                }
            }
        }

        // Show summary
        $validUsers = $expectedUsers->filter(function($user) {
            return !empty($user->email) &&
                   filter_var($user->email, FILTER_VALIDATE_EMAIL) &&
                   !empty(trim(($user->name ?? '') . ' ' . ($user->last_name ?? '')));
        });

        $this->table(['Category', 'Count'], [
            ['Total Expected Users', $expectedUsers->count()],
            ['Users with Valid Email', $expectedUsers->count() - $usersWithBadEmail->count()],
            ['Users with Valid Name', $expectedUsers->count() - $usersWithoutName->count()],
            ['Users Ready to Sync', $validUsers->count()],
        ]);

        $this->info("🎯 Users that should sync successfully: {$validUsers->count()}");
    }

    protected function showExcludedCommitteeUsers()
    {
        $this->info('❌ Committee_User Rows Being Excluded from Sync:');

        // Get all committee_user relationships
        $allCommitteeUsers = DB::table('committee_user')
            ->join('users', 'committee_user.user_id', '=', 'users.id')
            ->join('committees', 'committee_user.committee_id', '=', 'committees.id')
            ->join('website_objects', function($join) {
                $join->on('committees.id', '=', 'website_objects.object_id')
                     ->where('website_objects.content_type', '=', 'committees');
            })
            ->leftJoin('committee_languages', function($join) {
                $join->on('committees.id', '=', 'committee_languages.committee_id')
                     ->where('committee_languages.language_id', '=', 'en');
            })
            ->select([
                'committee_user.user_id',
                'committee_user.committee_id',
                'users.name',
                'users.last_name',
                'users.email',
                'users.state',
                'website_objects.status as committee_status',
                'committee_languages.title as committee_title'
            ])
            ->get();

        $excludedRows = [];
        $includedCount = 0;

        foreach ($allCommitteeUsers as $row) {
            $reasons = [];

            // Check if user is inactive
            if ($row->state !== 'active') {
                $reasons[] = "User is {$row->state} (not active)";
            }

            // Check if user has no email
            if (empty($row->email)) {
                $reasons[] = "User has no email";
            }

            // Check if committee is not published
            if ($row->committee_status !== 'published') {
                $reasons[] = "Committee is {$row->committee_status} (not published)";
            }

            if (!empty($reasons)) {
                $excludedRows[] = [
                    'User ID' => $row->user_id,
                    'User Name' => trim(($row->name ?? '') . ' ' . ($row->last_name ?? '')),
                    'Email' => $row->email ?: 'N/A',
                    'State' => $row->state,
                    'Committee ID' => $row->committee_id,
                    'Committee' => $row->committee_title ?: 'N/A',
                    'Committee Status' => $row->committee_status,
                    'Exclusion Reason' => implode(', ', $reasons)
                ];
            } else {
                $includedCount++;
            }
        }

        if (!empty($excludedRows)) {
            $this->table([
                'User ID', 'User Name', 'Email', 'State',
                'Committee ID', 'Committee', 'Committee Status', 'Exclusion Reason'
            ], $excludedRows);
        } else {
            $this->info('✅ No committee_user rows are being excluded.');
        }

        $this->info("📊 Summary:");
        $this->info("   - Total committee_user rows: " . $allCommitteeUsers->count());
        $this->info("   - Rows included in sync: {$includedCount}");
        $this->info("   - Rows excluded from sync: " . count($excludedRows));

        if (count($excludedRows) > 0) {
            $this->warn("💡 The difference between database rows and contacts is due to these exclusions.");
        }
    }
}