<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class AdsSystemStatus extends Command
{
    protected $signature = 'ads:status';
    protected $description = 'Show ads system status and statistics';

    public function handle()
    {
        $this->info('📊 Ads System Status Report');
        $this->newLine();

        // Raw data statistics
        $this->info('📥 Raw Data:');
        $rawStats = [
            ['Total Raw Ads', DB::table('ads_raw')->count()],
            ['Last 24h', DB::table('ads_raw')->where('created_at', '>', now()->subDay())->count()],
            ['Last Hour', DB::table('ads_raw')->where('created_at', '>', now()->subHour())->count()],
            ['Bama Ads', DB::table('ads_raw')->where('source', 'bama')->count()],
            ['Divar Ads', DB::table('ads_raw')->where('source', 'divar')->count()],
        ];
        $this->table(['Metric', 'Count'], $rawStats);

        // Opportunities statistics
        $this->newLine();
        $this->info('🎯 Opportunities:');
        $oppStats = [
            ['Total Opportunities', DB::table('opportunities')->count()],
            ['Draft', DB::table('opportunities')->where('status', 'draft')->count()],
            ['Reviewed', DB::table('opportunities')->where('status', 'reviewed')->count()],
            ['Scheduled', DB::table('opportunities')->where('status', 'scheduled')->count()],
            ['Sent', DB::table('opportunities')->where('status', 'sent')->count()],
            ['Archived', DB::table('opportunities')->where('status', 'archived')->count()],
        ];
        $this->table(['Status', 'Count'], $oppStats);

        // Score distribution
        $this->newLine();
        $this->info('📈 Score Distribution:');
        $scoreStats = [
            ['High (≥0.8)', DB::table('opportunities')->where('score', '>=', 0.8)->count()],
            ['Medium (0.6-0.8)', DB::table('opportunities')->whereBetween('score', [0.6, 0.8])->count()],
            ['Low (0.4-0.6)', DB::table('opportunities')->whereBetween('score', [0.4, 0.6])->count()],
            ['Very Low (<0.4)', DB::table('opportunities')->where('score', '<', 0.4)->count()],
        ];
        $this->table(['Score Range', 'Count'], $scoreStats);

        // Featured ads
        $this->newLine();
        $this->info('⭐ Featured Ads:');
        $featuredStats = [
            ['Total', DB::table('featured_ads')->count()],
            ['Draft', DB::table('featured_ads')->where('status', 'draft')->count()],
            ['Scheduled', DB::table('featured_ads')->where('status', 'scheduled')->count()],
            ['Sent', DB::table('featured_ads')->where('status', 'sent')->count()],
        ];
        $this->table(['Status', 'Count'], $featuredStats);

        // Scrape profiles
        $this->newLine();
        $this->info('🔍 Scrape Profiles:');
        $profileStats = [
            ['Total', DB::table('scrape_profiles')->count()],
            ['Enabled', DB::table('scrape_profiles')->where('enabled', true)->count()],
            ['Disabled', DB::table('scrape_profiles')->where('enabled', false)->count()],
        ];
        $this->table(['Status', 'Count'], $profileStats);

        // Recent activity
        $this->newLine();
        $this->info('⏰ Recent Activity:');
        $recentActivity = [
            ['Last Raw Ad', DB::table('ads_raw')->latest('created_at')->value('created_at') ?? 'Never'],
            ['Last Opportunity', DB::table('opportunities')->latest('created_at')->value('created_at') ?? 'Never'],
            ['Last Push', DB::table('opportunities')->whereNotNull('pushed_at')->latest('pushed_at')->value('pushed_at') ?? 'Never'],
        ];
        $this->table(['Activity', 'Last Time'], $recentActivity);

        // System health
        $this->newLine();
        $this->info('🏥 System Health:');

        $healthIssues = [];

        // Check for stale data
        $staleRaw = DB::table('ads_raw')->where('created_at', '<', now()->subDays(7))->count();
        if ($staleRaw > 0) {
            $healthIssues[] = "⚠️  {$staleRaw} stale raw ads (>7 days)";
        }

        // Check for failed jobs
        $failedJobs = DB::table('failed_jobs')->count();
        if ($failedJobs > 0) {
            $healthIssues[] = "❌ {$failedJobs} failed jobs";
        }

        // Check for opportunities without city
        $noCityOpps = DB::table('opportunities')->whereNull('city_id')->count();
        if ($noCityOpps > 0) {
            $healthIssues[] = "⚠️  {$noCityOpps} opportunities without city";
        }

        if (empty($healthIssues)) {
            $this->info('✅ System is healthy');
        } else {
            foreach ($healthIssues as $issue) {
                $this->warn($issue);
            }
        }

        return 0;
    }
}
