<?php

namespace App\Examples;

/**
 * BEISPIEL-QUERIES FÜR DAS EVENT-PORTAL
 * 
 * Diese Datei zeigt praktische Eloquent-Queries zum Abrufen und Filtern von Events.
 * Kopiere diese Queries in deine Controller, Services oder Artisan-Commands.
 */

use App\Models\Event;
use App\Models\EventOccurrence;
use Carbon\Carbon;

// ============================================================================
// 1. ALLE EVENTS AN EINEM BESTIMMTEN DATUM
// ============================================================================

// Alle geplanten Events am 15. April 2026
$targetDate = Carbon::parse('2026-04-15');
$eventsOnDate = Event::published()
    ->with(['source', 'occurrences' => function ($q) use ($targetDate) {
        $q->onDate($targetDate)->scheduled();
    }])
    ->whereHas('occurrences', function ($q) use ($targetDate) {
        $q->onDate($targetDate)->scheduled();
    })
    ->orderBy('title')
    ->get();

// Iteriere über Events und ihre Termine
foreach ($eventsOnDate as $event) {
    echo "{$event->title}\n";
    foreach ($event->occurrences as $occ) {
        echo "  - {$occ->formatted_duration}\n";
    }
}


// ============================================================================
// 2. NÄCHSTE 10 KOMMENDEN EVENTS IN DRESDEN
// ============================================================================

$upcomingDresdenEvents = Event::published()
    ->byLocation('Dresden')
    ->with(['occurrences' => function ($q) {
        $q->upcoming()->limit(1); // Nur nächster Termin pro Event
    }])
    ->whereHas('occurrences', function ($q) {
        $q->upcoming();
    })
    ->orderBy(EventOccurrence::select('start_datetime')
        ->whereColumn('event_id', 'events.id')
        ->orderBy('start_datetime')
        ->limit(1), 'asc')
    ->limit(10)
    ->get();


// ============================================================================
// 3. EVENTS NACH KATEGORIE FILTERN
// ============================================================================

$culturalEvents = Event::published()
    ->byCategory('Kultur')
    ->with('occurrences')
    ->get();

$sportEvents = Event::published()
    ->byCategory('Sport')
    ->with('occurrences')
    ->get();


// ============================================================================
// 4. EVENTS IN EINEM ZEITRAUM (z.B. Osterferien)
// ============================================================================

$from = Carbon::parse('2026-04-23');
$to = Carbon::parse('2026-05-05');

$easterHolidayEvents = Event::published()
    ->with(['occurrences' => function ($q) use ($from, $to) {
        $q->between($from, $to)->scheduled()->orderBy('start_datetime');
    }])
    ->whereHas('occurrences', function ($q) use ($from, $to) {
        $q->between($from, $to)->scheduled();
    })
    ->orderBy('title')
    ->get();


// ============================================================================
// 5. EVENTS MIT VERFÜGBAREN TICKETS
// ============================================================================

// Events mit verfügbaren Tickets
$ticketableEvents = Event::published()
    ->with(['occurrences' => function ($q) {
        $q->upcoming();
    }])
    ->whereHas('occurrences', function ($q) {
        $q->upcoming()
          ->whereRaw('available_tickets > 0 OR available_tickets IS NULL');
    })
    ->get();


// ============================================================================
// 6. EVENTS DER LETZTEN 7 TAGE
// ============================================================================

// Neue Events, die in der letzten Woche hinzugefügt wurden
$newEvents = Event::published()
    ->where('created_at', '>=', now()->subWeek())
    ->with(['occurrences' => function ($q) {
        $q->upcoming();
    }])
    ->orderByDesc('created_at')
    ->get();


// ============================================================================
// 7. BELIEBTE KATEGORIEN & ORTE
// ============================================================================

// Top Kategorien (mit Event-Anzahl)
$topCategories = Event::published()
    ->whereNotNull('category')
    ->selectRaw('category, COUNT(*) as event_count')
    ->groupBy('category')
    ->orderByDesc('event_count')
    ->limit(10)
    ->get();

// Top Orte
$topLocations = Event::published()
    ->selectRaw('location, COUNT(*) as event_count')
    ->groupBy('location')
    ->orderByDesc('event_count')
    ->get();


// ============================================================================
// 8. TAGESANSICHT (alle Termine eines Tages)
// ============================================================================

$date = Carbon::parse('2026-04-15');

$dayOverview = EventOccurrence::scheduled()
    ->onDate($date)
    ->with(['event' => function ($q) {
        $q->published();
    }])
    ->orderBy('start_datetime')
    ->get();

foreach ($dayOverview as $occurrence) {
    echo "{$occurrence->start_datetime->format('H:i')} - {$occurrence->event->title}\n";
}


// ============================================================================
// 9. EVENTS VON EINER BESTIMMTEN QUELLE
// ============================================================================

// Events nur aus der Stadt-Dresden-Quelle
$dresdenCityEvents = Event::whereHas('source', function ($q) {
    $q->where('name', 'Stadt Dresden');
})
->published()
->with('occurrences')
->get();


// ============================================================================
// 10. ROHE SQL-ABFRAGE FÜR KOMPLEXE FILTERUNG
// ============================================================================

use Illuminate\Support\Facades\DB;

// Alle kommenden Events mit mindestens einem verfügbaren Ticket
$complexQuery = Event::published()
    ->select('events.*')
    ->join('event_occurrences', 'events.id', '=', 'event_occurrences.event_id')
    ->where('event_occurrences.start_datetime', '>=', now())
    ->where('event_occurrences.status', 'scheduled')
    ->where(function ($q) {
        $q->where('event_occurrences.available_tickets', '>', 0)
          ->orWhereNull('event_occurrences.available_tickets');
    })
    ->distinct('events.id')
    ->orderBy('event_occurrences.start_datetime')
    ->get();