<?php

namespace App\Services;

use App\Models\UserBusiness;
use App\Models\UserLevel;
use App\User;
use Illuminate\Support\Collection;

class LevelReportService
{
    public function getLevelPromotions(array $filters = []): Collection
    {
        $month = $filters['month'] ?? null;
        $year = $filters['year'] ?? null;
        $userId = $filters['user_id'] ?? null;
        $onlyNotUpdated = $filters['only_not_updated'] ?? false;

        // Lade UserLevels für Referenz
        $userLevels = UserLevel::where('active', 1)->orderBy('pos')->get()->keyBy('id');

        // Query UserBusiness Einträge mit Level-Aufstiegen
        $query = UserBusiness::whereNotNull('next_qual_user_level')
            ->whereRaw("JSON_LENGTH(next_qual_user_level) > 0")
            ->orderBy('year', 'desc')
            ->orderBy('month', 'desc')
            ->orderBy('user_id');

        // Filter anwenden
        if ($month) {
            $query->where('month', $month);
        }
        if ($year) {
            $query->where('year', $year);
        }
        if ($userId) {
            $query->where('user_id', $userId);
        }

        $userBusinesses = $query->get();

        return $this->processLevelPromotions($userBusinesses, $userLevels, $onlyNotUpdated);
    }

    public function processLevelPromotions($userBusinesses, $userLevels, $onlyNotUpdated = false): Collection
    {
        $promotions = [];

        // Lade User-Daten für alle Level-Vergleiche
        $userIds = $userBusinesses->pluck('user_id')->unique();
        $users = User::whereIn('id', $userIds)->get(['id', 'm_level']);
        $currentUserLevels = $users->keyBy('id');

        foreach ($userBusinesses as $userBusiness) {
            $nextQualUserLevel = $userBusiness->next_qual_user_level;

            if (is_array($nextQualUserLevel) && !empty($nextQualUserLevel)) {
                // next_qual_user_level kann sowohl ein einzelnes Level-Objekt als auch ein Array von Level-Objekten sein
                $levelArray = isset($nextQualUserLevel['id']) ? [$nextQualUserLevel] : $nextQualUserLevel;

                foreach ($levelArray as $newLevelData) {
                    // Überprüfe ob es ein vollständiges Level-Objekt ist
                    if (is_array($newLevelData) && isset($newLevelData['id'])) {
                        $currentLevel = $userLevels->get($userBusiness->m_level_id);
                        $newLevelId = $newLevelData['id'];

                        // Lade aktuellen User Level
                        $currentUser = $currentUserLevels->get($userBusiness->user_id);
                        $currentUserLevelName = 'Unbekannt';

                        if ($currentUser && $currentUser->m_level) {
                            $currentUserLevel = $userLevels->get($currentUser->m_level);
                            $currentUserLevelName = $currentUserLevel ? $currentUserLevel->name : 'Level ID: ' . $currentUser->m_level;
                        }

                        // Filter: Nur User die noch nicht auf das neue Level umgestellt wurden
                        if ($onlyNotUpdated) {
                            if (!$currentUser || $currentUser->m_level == $newLevelId) {
                                continue; // Skip - User ist bereits auf das neue Level umgestellt
                            }
                        }

                        $promotions[] = [
                            'user_id' => $userBusiness->user_id,
                            'email' => $userBusiness->email,
                            'first_name' => $userBusiness->first_name,
                            'last_name' => $userBusiness->last_name,
                            'month' => $userBusiness->month,
                            'year' => $userBusiness->year,
                            'date' => sprintf('%04d-%02d', $userBusiness->year, $userBusiness->month),
                            'from_level_id' => $userBusiness->m_level_id,
                            'from_level_name' => $currentLevel ? $currentLevel->name : 'Unbekannt',
                            'to_level_id' => $newLevelId,
                            'to_level_name' => $newLevelData['name'] ?? 'Unbekannt',
                            'to_level_margin' => $newLevelData['margin'] ?? 0,
                            'to_level_margin_shop' => $newLevelData['margin_shop'] ?? 0,
                            'to_level_qual_kp' => $newLevelData['qual_kp'] ?? 0,
                            'to_level_qual_pp' => $newLevelData['qual_pp'] ?? 0,
                            'to_level_growth_bonus' => $newLevelData['growth_bonus'] ?? 0,
                            'to_level_pos' => $newLevelData['pos'] ?? 0,
                            'current_user_level_id' => $currentUser ? $currentUser->m_level : null,
                            'current_user_level_name' => $currentUserLevelName,
                            'level_updated' => $onlyNotUpdated ? 'Nein' : ($currentUser && $currentUser->m_level == $newLevelId ? 'Ja' : 'Nein'),
                            'total_pp' => $userBusiness->total_pp ?? 0,
                            'total_qual_pp' => $userBusiness->total_qual_pp ?? 0,
                            'payline_points_qual_kp' => $userBusiness->payline_points_qual_kp ?? 0,
                            'sales_volume_points_sum' => $userBusiness->sales_volume_points_KP_sum ?? 0,
                            'active_account' => $userBusiness->active_account ? 'Ja' : 'Nein',
                        ];
                    }
                }
            }
        }

        // Sortiere nach Datum (neueste zuerst) und dann nach User ID
        usort($promotions, function ($a, $b) {
            if ($a['year'] !== $b['year']) {
                return $b['year'] - $a['year'];
            }
            if ($a['month'] !== $b['month']) {
                return $b['month'] - $a['month'];
            }
            return $a['user_id'] - $b['user_id'];
        });

        return collect($promotions);
    }

    public function getStatistics(Collection $promotions): array
    {
        $stats = [
            'total_count' => $promotions->count(),
            'level_stats' => [],
            'period_stats' => []
        ];

        // Statistik nach Level
        foreach ($promotions as $promotion) {
            $levelName = $promotion['to_level_name'];
            if (!isset($stats['level_stats'][$levelName])) {
                $stats['level_stats'][$levelName] = 0;
            }
            $stats['level_stats'][$levelName]++;
        }

        // Statistik nach Monat/Jahr
        foreach ($promotions as $promotion) {
            $period = $promotion['date'];
            if (!isset($stats['period_stats'][$period])) {
                $stats['period_stats'][$period] = 0;
            }
            $stats['period_stats'][$period]++;
        }

        return $stats;
    }

    public function exportToCsv(Collection $promotions, string $filename = null): string
    {
        if (!$filename) {
            $filename = 'level_promotions_' . date('Y-m-d_H-i-s') . '.csv';
        }

        $filepath = storage_path('app/reports/' . $filename);

        // Erstelle Verzeichnis falls nicht vorhanden
        if (!file_exists(dirname($filepath))) {
            mkdir(dirname($filepath), 0755, true);
        }

        $file = fopen($filepath, 'w');

        // UTF-8 BOM für korrekte Darstellung in Excel
        fwrite($file, "\xEF\xBB\xBF");

        // CSV Headers
        $headers = [
            'Datum',
            'User ID',
            'Vorname',
            'Nachname',
            'E-Mail',
            'Von Level ID',
            'Von Level Name',
            'Zu Level ID',
            'Zu Level Name',
            'Zu Level KP Anforderung',
            'Zu Level PP Anforderung',
            'User PP Total',
            'User PP Qualifiziert',
            'Aktueller User Level ID',
            'Aktueller User Level Name',
            'Level bereits geupdatet',
            'Aktiver Account',
            'Monat',
            'Jahr'
        ];

        fputcsv($file, $headers, ';');

        // Daten schreiben
        foreach ($promotions as $promotion) {
            $row = [
                $promotion['date'],
                $promotion['user_id'],
                $promotion['first_name'],
                $promotion['last_name'],
                $promotion['email'],
                $promotion['from_level_id'],
                $promotion['from_level_name'],
                $promotion['to_level_id'],
                $promotion['to_level_name'],
                $promotion['to_level_qual_kp'],
                $promotion['to_level_qual_pp'],
                $promotion['sales_volume_points_sum'],
                $promotion['payline_points_qual_kp'],
                $promotion['current_user_level_id'] ?? 'N/A',
                $promotion['current_user_level_name'],
                $promotion['level_updated'],
                $promotion['active_account'],
                $promotion['month'],
                $promotion['year']
            ];

            fputcsv($file, $row, ';');
        }

        fclose($file);

        return $filepath;
    }
}