<?php

// app/Http/Controllers/GameController.php

namespace App\Http\Controllers;

use Carbon\Carbon;
use Illuminate\Http\Request;
use App\Models\Game;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

class GameController extends Controller
{
    // Cache duration in minutes - optimized for Redis
    private const CACHE_TTL = 60; // 1 hour
    private const CACHE_TTL_SHORT = 30; // 30 minutes for date-specific queries

    public function index()
    {
        // Cache the query
        $games = Cache::remember('games.index', self::CACHE_TTL, function () {
            return Game::select('id', 'date_time', 'home_team', 'away_team', 'league', 'option', 'odds', 'status',
                'sporty_booking', 'onex_booking', 'msport_booking', 'betway_booking', 'created_at')
                ->orderBy('created_at', 'desc')
                ->limit(100) // Limit to prevent loading too many records
                ->get();
        });

        return response()->json($games, 200);
    }

    public function store(Request $request)
    {
        $request->validate([
            'sporty_booking' => 'nullable|string',
            'onex_booking' => 'nullable|string',
            'msport_booking' => 'nullable|string',
            'betway_booking' => 'nullable|string',
            'created_at' => 'nullable|date',
            'games' => 'required|array',
            'games.*.date_time' => 'required|string',
            'games.*.home_team' => 'required|string|max:255',
            'games.*.away_team' => 'required|string|max:255',
            'games.*.league' => 'required|string|max:255',
            'games.*.option' => 'required|string|max:255',
            'games.*.odds' => 'required|numeric',
        ]);

        DB::beginTransaction();
        try {
            $gamesData = $request->input('games');

            // Sanitize booking codes - remove # symbols
            $sanitizedGamesData = array_map(function($game) {
                $bookingFields = ['sporty_booking', 'onex_booking', 'msport_booking', 'betway_booking'];
                foreach ($bookingFields as $field) {
                    if (isset($game[$field])) {
                        $game[$field] = str_replace('#', '', $game[$field]);
                    }
                }
                return $game;
            }, $gamesData);

            // Bulk insert for better performance
            $timestamp = now();
            $bulkData = array_map(function($game) use ($timestamp) {
                $game['created_at'] = $game['created_at'] ?? $timestamp;
                $game['updated_at'] = $timestamp;
                return $game;
            }, $sanitizedGamesData);

            // Insert all games at once
            Game::insert($bulkData);

            // Fetch the created games if needed
            $createdGames = Game::where('created_at', $timestamp)
                ->whereIn('home_team', array_column($sanitizedGamesData, 'home_team'))
                ->get();

            DB::commit();

            // Clear relevant caches
            $this->clearGameCaches();

            return response()->json($createdGames, 201);

        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Error creating games: ' . $e->getMessage());
            return response()->json(['message' => 'Error creating games'], 500);
        }
    }

    public function show($id)
    {
        // Cache individual game lookups
        $game = Cache::remember("game.{$id}", self::CACHE_TTL, function () use ($id) {
            return Game::find($id);
        });

        if (is_null($game)) {
            return response()->json(['message' => 'Game Not Found'], 404);
        }
        return response()->json($game, 200);
    }

    public function update(Request $request, $id)
    {
        $request->validate([
            'sporty_booking' => 'nullable|string',
            'onex_booking' => 'nullable|string',
            'msport_booking' => 'nullable|string',
            'betway_booking' => 'nullable|string',
            'created_at' => 'nullable|date',
            'games' => 'required|array',
            'games.*.date_time' => 'required|string',
            'games.*.home_team' => 'required|string|max:255',
            'games.*.away_team' => 'required|string|max:255',
            'games.*.league' => 'required|string|max:255',
            'games.*.option' => 'required|string|max:255',
            'games.*.odds' => 'required|numeric',
        ]);

        DB::beginTransaction();
        try {
            // Extract and sanitize booking codes from the first game in the array
            $gamesData = $request->input('games');
            $bookingFields = ['sporty_booking', 'onex_booking', 'msport_booking', 'betway_booking'];

            // Sanitize # symbols from booking codes
            foreach ($bookingFields as $field) {
                if (isset($gamesData[0][$field])) {
                    $gamesData[0][$field] = str_replace('#', '', $gamesData[0][$field]);
                }
            }

            $newsportyBooking = $gamesData[0]['sporty_booking'] ?? null;
            $newonexBooking = $gamesData[0]['onex_booking'] ?? null;
            $newmsportBooking = $gamesData[0]['msport_booking'] ?? null;
            $newbetwayBooking = $gamesData[0]['betway_booking'] ?? null;

            $sportyBooking = $request->input('old_sporty_booking');
            $onexBooking = $request->input('old_booking');
            $msportBooking = $request->input('old_msport_booking');
            $betwayBooking = $request->input('old_betway_booking');

            // Optimized deletion using whereIn for multiple conditions
            $bookingCodes = array_filter([
                $sportyBooking,
                $onexBooking,
                $msportBooking,
                $betwayBooking
            ]);

            if (!empty($bookingCodes)) {
                $deletedRows = Game::where(function($query) use ($bookingCodes, $sportyBooking, $onexBooking, $msportBooking, $betwayBooking) {
                    if ($sportyBooking) $query->orWhere('sporty_booking', $sportyBooking);
                    if ($onexBooking) $query->orWhere('onex_booking', $onexBooking);
                    if ($msportBooking) $query->orWhere('msport_booking', $msportBooking);
                    if ($betwayBooking) $query->orWhere('betway_booking', $betwayBooking);
                })->delete();

                Log::info('Number of deleted rows:', ['deleted_rows' => $deletedRows]);
            }

            // Bulk insert updated games
            $timestamp = now();
            $bulkData = array_map(function($game) use ($newsportyBooking, $newonexBooking, $newmsportBooking, $newbetwayBooking, $timestamp) {
                $game['sporty_booking'] = $newsportyBooking;
                $game['onex_booking'] = $newonexBooking;
                $game['msport_booking'] = $newmsportBooking;
                $game['betway_booking'] = $newbetwayBooking;
                $game['created_at'] = $game['created_at'] ?? $timestamp;
                $game['updated_at'] = $timestamp;
                return $game;
            }, $gamesData);

            Game::insert($bulkData);

            $updatedGames = Game::where('created_at', '>=', Carbon::now()->subSeconds(5))
                ->where(function($query) use ($newsportyBooking, $newonexBooking, $newmsportBooking, $newbetwayBooking) {
                    if ($newsportyBooking) $query->orWhere('sporty_booking', $newsportyBooking);
                    if ($newonexBooking) $query->orWhere('onex_booking', $newonexBooking);
                    if ($newmsportBooking) $query->orWhere('msport_booking', $newmsportBooking);
                    if ($newbetwayBooking) $query->orWhere('betway_booking', $newbetwayBooking);
                })->get();

            DB::commit();

            // Clear caches
            $this->clearGameCaches();

            Log::info('Updated games:', $updatedGames->toArray());

            return response()->json($updatedGames, 200);

        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Error updating games: ' . $e->getMessage());
            return response()->json(['message' => 'Error updating games'], 500);
        }
    }

    public function destroy($id)
    {
        $game = Game::find($id);
        if (is_null($game)) {
            return response()->json(['message' => 'Game Not Found'], 404);
        }

        DB::beginTransaction();
        try {
            $game->delete();
            DB::commit();

            // Clear caches
            Cache::forget("game.{$id}");
            $this->clearGameCaches();

            return response()->json(['message' => 'Game Deleted'], 204);

        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Error deleting game: ' . $e->getMessage());
            return response()->json(['message' => 'Error deleting game'], 500);
        }
    }

    public function getAllGames(Request $request)
    {
        $limit = $request->query('limit', 20); // Default limit to 20

        // Cache this query with limit in key
        $cacheKey = "all_games.limit_{$limit}";
        $games = Cache::remember($cacheKey, self::CACHE_TTL, function () use ($limit) {
            return Game::select('id', 'date_time', 'home_team', 'away_team', 'league', 'option', 'odds', 'status',
                'sporty_booking', 'onex_booking', 'msport_booking', 'betway_booking', 'created_at')
                ->orderBy('created_at', 'desc')
                ->limit($limit)
                ->get();
        });

        return response()->json($games, 200);
    }

    public function getGamesByDateRange(Request $request)
    {
        $date = $request->query('date');
        if (!$date) {
            return response()->json(['message' => 'Date is required'], 400);
        }

        $parsedDate = Carbon::parse($date);

        // Cache with date-specific key
        $cacheKey = "games_by_date." . $parsedDate->format('Y-m-d');
        $games = Cache::remember($cacheKey, self::CACHE_TTL_SHORT, function () use ($parsedDate) {
            return Game::select('id', 'date_time', 'home_team', 'away_team', 'league', 'option', 'odds', 'status',
                'sporty_booking', 'onex_booking', 'msport_booking', 'betway_booking', 'created_at')
                ->whereDate('date_time', $parsedDate)
                ->orderBy('created_at', 'desc')
                ->get();
        });

        return response()->json($games, 200);
    }

    public function updateStatus(Request $request, $id)
    {
        $request->validate([
            'status' => 'nullable|in:Won,Lost',
        ]);

        DB::beginTransaction();
        try {
            $game = Game::findOrFail($id);
            $game->status = $request->status;
            $game->save();

            DB::commit();

            // Clear specific game cache
            Cache::forget("game.{$id}");
            $this->clearGameCaches();

            return response()->json([
                'message' => 'Game status updated successfully',
                'game' => $game
            ]);
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Error updating game status: ' . $e->getMessage());
            return response()->json(['message' => 'Error updating game status'], 500);
        }
    }

    public function getGamesByBookingCode($bookingCode)
    {
        Log::info('Searching for booking code: ' . $bookingCode);

        // Sanitize booking code - remove # if present
        $sanitizedCode = str_replace('#', '', $bookingCode);

        // Cache booking code queries
        $cacheKey = "games_by_booking." . md5($sanitizedCode);
        $games = Cache::remember($cacheKey, self::CACHE_TTL, function () use ($sanitizedCode) {
            return Game::select('id', 'date_time', 'home_team', 'away_team', 'league', 'option', 'odds', 'status',
                'sporty_booking', 'onex_booking', 'msport_booking', 'betway_booking', 'created_at')
                ->where(function($query) use ($sanitizedCode) {
                    $query->where('sporty_booking', $sanitizedCode)
                        ->orWhere('onex_booking', $sanitizedCode)
                        ->orWhere('msport_booking', $sanitizedCode)
                        ->orWhere('betway_booking', $sanitizedCode);
                })
                ->get();
        });

        return response()->json($games);
    }

    /**
     * Clear all game-related caches
     */
    private function clearGameCaches()
    {
        Cache::forget('games.index');

        // Clear all_games with different limits
        $limits = [20, 50, 100];
        foreach ($limits as $limit) {
            Cache::forget("all_games.limit_{$limit}");
        }

        // Clear date-specific caches for the last 7 days
        for ($i = 0; $i < 7; $i++) {
            $date = Carbon::now()->subDays($i)->format('Y-m-d');
            Cache::forget("games_by_date.{$date}");
        }

        // If using cache tags (Redis) - only use if Redis supports it
        if (Cache::getStore() instanceof \Illuminate\Cache\RedisStore) {
            try {
                Cache::tags(['games'])->flush();
            } catch (\Exception $e) {
                // Tags might not be supported, continue without error
            }
        }
    }
}
