<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\User;
use App\Models\DnsZone;
use App\Models\DnsQueryUsage;
use App\Models\DnsZoneStatistic;
use Illuminate\Support\Facades\DB;

class QuickSyncQueryUsage extends Command
{
    protected $signature = 'dns:quick-sync
        {--user_id= : Sync doar pentru un utilizator specific}
        {--today : Include și statisticile de azi dacă există}';

    protected $description = 'Sincronizare rapidă query-uri DNS din statisticile deja colectate (fără request-uri HTTP)';

    public function handle()
    {
        $startTime = microtime(true);
        $this->info('⚡ Sincronizare RAPIDĂ Query-uri DNS...');

        $userId = $this->option('user_id');
        $includeToday = $this->option('today');

        $year = (int) date('Y');
        $month = (int) date('n');
        $today = now()->format('Y-m-d');

        // Query optimizat - calculează totale direct în SQL
        $query = DB::table('dns_zones')
            ->select([
                'dns_zones.user_id',
                DB::raw('SUM(CASE WHEN YEAR(dns_zone_statistics.statistic_date) = ' . $year . '
                         AND MONTH(dns_zone_statistics.statistic_date) = ' . $month . '
                         THEN dns_zone_statistics.total_queries ELSE 0 END) as monthly_queries'),
                DB::raw('SUM(CASE WHEN DATE(dns_zone_statistics.statistic_date) = "' . $today . '"
                         THEN dns_zone_statistics.total_queries ELSE 0 END) as daily_queries'),
            ])
            ->join('dns_zone_statistics', 'dns_zones.domain', '=', 'dns_zone_statistics.domain')
            ->whereNotNull('dns_zones.user_id')
            ->groupBy('dns_zones.user_id');

        if ($userId) {
            $query->where('dns_zones.user_id', $userId);
        }

        $results = $query->get();

        $this->info("📊 Găsiți {$results->count()} utilizatori cu statistici");

        $updated = 0;

        foreach ($results as $result) {
            $user = User::find($result->user_id);
            if (!$user) continue;

            $monthlyQueries = (int) $result->monthly_queries;
            $dailyQueries = $includeToday ? (int) $result->daily_queries : 0;

            DnsQueryUsage::updateOrCreate(
                [
                    'user_id' => $result->user_id,
                    'year' => $year,
                    'month' => $month,
                ],
                [
                    'query_count' => $monthlyQueries,
                    'daily_query_count' => $dailyQueries,
                    'last_daily_reset' => now()->startOfDay(),
                ]
            );

            $updated++;
            $this->line("  ✓ {$user->name}: " . number_format($monthlyQueries) . " lunar / " . number_format($dailyQueries) . " zilnic");
        }

        $duration = round(microtime(true) - $startTime, 3);
        $this->newLine();
        $this->info("✅ Sincronizare completă în {$duration}s ({$updated} utilizatori)");

        return 0;
    }
}
