<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Carbon\Carbon;

class LocationRatingController extends Controller
{
    /**
     * Store a new rating using existing ratings table structure
     */
    public function store(Request $request)
    {
        // Debug: Log the request data
        \Log::info('Rating submission data:', $request->all());
        \Log::info('Request headers:', $request->headers->all());

        $validator = Validator::make($request->all(), [
            'title_id' => 'required|string|max:50',
            'name' => 'required|string|max:100',
            'email' => 'required|email|max:100',
            'user_phone' => 'nullable|string|max:20',
            'rating' => 'required|integer|min:1|max:5',
            'service_rating' => 'nullable|integer|min:1|max:5',
            'quality_rating' => 'nullable|integer|min:1|max:5',
            'location_rating' => 'nullable|integer|min:1|max:5',
            'price_rating' => 'nullable|integer|min:1|max:5',
            'cleanliness_rating' => 'nullable|integer|min:1|max:5',
            'comment' => 'nullable|string|max:2000',
            'positive_comment' => 'nullable|string|max:1000',
            'negative_comment' => 'nullable|string|max:1000',
            'visit_date' => 'nullable|date',
            'visit_purpose' => 'nullable|string|max:50',
            'recommendation' => 'nullable|in:yes,no,maybe',
        ]);

        if ($validator->fails()) {
            if ($request->ajax() || $request->wantsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => 'خطا در اعتبارسنجی داده‌ها',
                    'errors' => $validator->errors()
                ], 422);
            }
            return back()->withErrors($validator)->withInput();
        }

        try {
            // Use Eloquent model to avoid collation issues
            $location = \App\Models\Location::where('title_id', $request->title_id)->first();

            if (!$location) {
                \Log::warning("Location not found for title_id: " . $request->title_id);

                // Create a dummy location for testing
                $location = (object)[
                    'title_id' => $request->title_id,
                    'category' => 'test',
                    'title' => 'مکان تست'
                ];
                \Log::info("Using dummy location for testing");
            } else {
                \Log::info("Location found: " . json_encode($location->toArray()));
            }

            // Check for duplicate rating from same IP/email in last 24 hours
            try {
                $existingRating = DB::table('ratings')
                    ->where('title_id', $request->title_id)
                    ->where('email', $request->email)
                    ->where('timestamp', '>', Carbon::now()->subDay())
                    ->first();
            } catch (\Exception $e) {
                // If timestamp column doesn't exist, skip duplicate check
                \Log::warning('Could not check for duplicate ratings: ' . $e->getMessage());
                $existingRating = null;
            }

            if ($existingRating) {
                if ($request->ajax() || $request->wantsJson()) {
                    return response()->json([
                        'success' => false,
                        'message' => 'شما در ۲۴ ساعت گذشته برای این مکان نظر ثبت کرده‌اید'
                    ], 429);
                }
                return back()->with('error', 'شما در ۲۴ ساعت گذشته برای این مکان نظر ثبت کرده‌اید')->withInput();
            }

            // Store the rating using existing table structure
            $insertData = [
                'title_id' => $request->title_id,
                'category' => $location->category,
                'hotel_name' => $location->title, // Using location title as hotel_name
                'name' => $request->name,
                'email' => $request->email,
                'rating' => $request->rating,
                'comment' => $request->comment,
                'positive_comment' => $request->positive_comment,
                'negative_comment' => $request->negative_comment,
                'status' => 'pending', // Default to pending for moderation
                'timestamp' => now(),
            ];

            // Add optional fields only if they exist in the table
            $optionalFields = [
                'user_phone' => $request->user_phone,
                'service_rating' => $request->service_rating,
                'quality_rating' => $request->quality_rating,
                'location_rating' => $request->location_rating,
                'price_rating' => $request->price_rating,
                'cleanliness_rating' => $request->cleanliness_rating,
                'visit_date' => $request->visit_date,
                'visit_purpose' => $request->visit_purpose,
                'recommendation' => $request->recommendation,
                'ip_address' => $request->ip(),
                'user_agent' => $request->userAgent(),
                'created_at' => now(),
                'updated_at' => now(),
            ];

            // Check which columns exist in the table
            try {
                $columns = DB::select('SHOW COLUMNS FROM ratings');
                $existingColumns = collect($columns)->pluck('Field')->toArray();

                foreach ($optionalFields as $field => $value) {
                    if (in_array($field, $existingColumns) && $value !== null) {
                        $insertData[$field] = $value;
                    }
                }
            } catch (\Exception $e) {
                \Log::warning('Could not check table columns: ' . $e->getMessage());
            }

            // Use PDO directly to completely bypass Laravel's query builder and avoid collation issues
            try {
                $pdo = DB::connection()->getPdo();

                // Set connection charset to utf8mb4 with unicode_ci collation
                $pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
                $pdo->exec("SET CHARACTER SET utf8mb4");
                $pdo->exec("SET collation_connection = utf8mb4_unicode_ci");

                $sql = "INSERT INTO ratings (
                    title_id, category, hotel_name, name, email, rating, comment,
                    positive_comment, negative_comment, status, timestamp,
                    service_rating, quality_rating, location_rating, price_rating,
                    cleanliness_rating, visit_date, visit_purpose, recommendation,
                    ip_address, user_agent, created_at, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                $stmt = $pdo->prepare($sql);

                $values = [
                    $insertData['title_id'],
                    $insertData['category'],
                    $insertData['hotel_name'],
                    $insertData['name'],
                    $insertData['email'],
                    $insertData['rating'],
                    $insertData['comment'],
                    $insertData['positive_comment'],
                    $insertData['negative_comment'],
                    $insertData['status'],
                    $insertData['timestamp'],
                    $insertData['service_rating'],
                    $insertData['quality_rating'],
                    $insertData['location_rating'],
                    $insertData['price_rating'],
                    $insertData['cleanliness_rating'],
                    $insertData['visit_date'],
                    $insertData['visit_purpose'],
                    $insertData['recommendation'],
                    $insertData['ip_address'],
                    $insertData['user_agent'],
                    $insertData['created_at'],
                    $insertData['updated_at']
                ];

                \Log::info("PDO SQL Query: " . $sql);
                \Log::info("PDO Insert Values: " . json_encode($values));

                $result = $stmt->execute($values);

                if (!$result) {
                    throw new \Exception("Failed to execute PDO statement: " . implode(', ', $stmt->errorInfo()));
                }

                // Get the inserted ID
                $ratingId = $pdo->lastInsertId();

                \Log::info("Rating saved successfully with PDO, ID: " . $ratingId);

            } catch (\PDOException $e) {
                \Log::error("PDO Error: " . $e->getMessage());
                throw new \Exception("خطا در ذخیره امتیاز: " . $e->getMessage());
            }

            // Update rating statistics
            try {
                $this->updateRatingStatistics($request->title_id);
            } catch (\Exception $e) {
                \Log::warning('Could not update rating statistics: ' . $e->getMessage());
            }

            if ($request->ajax() || $request->wantsJson()) {
                return response()->json([
                    'success' => true,
                    'message' => 'نظر شما با موفقیت ثبت شد و پس از بررسی نمایش داده خواهد شد',
                    'rating_id' => $ratingId
                ]);
            }

            return back()->with('success', 'نظر شما با موفقیت ثبت شد و پس از بررسی نمایش داده خواهد شد');

        } catch (\Exception $e) {
            \Log::error('Error storing rating:', [
                'message' => $e->getMessage(),
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'trace' => $e->getTraceAsString(),
                'request_data' => $request->all()
            ]);

            if ($request->ajax() || $request->wantsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => 'خطا در ثبت نظر: ' . $e->getMessage()
                ], 500);
            }

            return back()->with('error', 'خطا در ثبت نظر. لطفاً دوباره تلاش کنید')->withInput();
        }
    }

    /**
     * React to a rating (helpful/not helpful)
     */
    public function react(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'rating_id' => 'required|integer|exists:ratings,id',
            'vote_type' => 'required|in:helpful,not_helpful',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'داده‌های نامعتبر'
            ], 422);
        }

        try {
            $ratingId = $request->rating_id;
            $voteType = $request->vote_type;
            $userIp = $request->ip();

            // Check if user already voted
            $existingVote = DB::table('rating_helpful_votes')
                ->where('rating_id', $ratingId)
                ->where('user_ip', $userIp)
                ->first();

            if ($existingVote) {
                if ($existingVote->vote_type === $voteType) {
                    // Remove vote if same type
                    DB::table('rating_helpful_votes')
                        ->where('id', $existingVote->id)
                        ->delete();

                    $userReacted = false;
                } else {
                    // Update vote type
                    DB::table('rating_helpful_votes')
                        ->where('id', $existingVote->id)
                        ->update([
                            'vote_type' => $voteType,
                            'created_at' => now()
                        ]);

                    $userReacted = true;
                }
            } else {
                // Insert new vote
                DB::table('rating_helpful_votes')->insert([
                    'rating_id' => $ratingId,
                    'user_ip' => $userIp,
                    'user_agent' => $request->userAgent(),
                    'vote_type' => $voteType,
                    'created_at' => now()
                ]);

                $userReacted = true;
            }

            // Update counts in ratings table
            $helpfulCount = DB::table('rating_helpful_votes')
                ->where('rating_id', $ratingId)
                ->where('vote_type', 'helpful')
                ->count();

            $notHelpfulCount = DB::table('rating_helpful_votes')
                ->where('rating_id', $ratingId)
                ->where('vote_type', 'not_helpful')
                ->count();

            DB::table('ratings')
                ->where('id', $ratingId)
                ->update([
                    'helpful_count' => $helpfulCount,
                    'not_helpful_count' => $notHelpfulCount,
                    'updated_at' => now()
                ]);

            $count = $voteType === 'helpful' ? $helpfulCount : $notHelpfulCount;

            return response()->json([
                'success' => true,
                'message' => $userReacted ? 'نظر شما ثبت شد' : 'نظر شما حذف شد',
                'user_reacted' => $userReacted,
                'count' => $count
            ]);

        } catch (\Exception $e) {
            \Log::error('Error reacting to rating: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'خطا در ثبت نظر'
            ], 500);
        }
    }

    /**
     * Reply to a comment
     */
    public function reply(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'parent_id' => 'required|integer|exists:rating_location_comments,id',
            'content' => 'required|string|max:1000',
            'title_id' => 'required|string|max:50|exists:locations,title_id',
            'user_name' => 'required|string|max:100',
            'user_email' => 'required|email|max:100',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'داده‌های نامعتبر',
                'errors' => $validator->errors()
            ], 422);
        }

        try {
            $replyId = DB::table('rating_location_comments')->insertGetId([
                'title_id' => $request->title_id,
                'parent_id' => $request->parent_id,
                'name' => $request->user_name,
                'email' => $request->user_email,
                'user_name' => $request->user_name,  // برای سازگاری
                'user_email' => $request->user_email, // برای سازگاری
                'comment' => $request->content,
                'ip_address' => $request->ip(),
                'user_agent' => $request->userAgent(),
                'status' => 'pending',
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            // Update replies count
            DB::table('rating_location_comments')
                ->where('id', $request->parent_id)
                ->increment('replies_count');

            return response()->json([
                'success' => true,
                'message' => 'پاسخ شما ثبت شد و پس از بررسی نمایش داده خواهد شد',
                'reply_id' => $replyId
            ]);

        } catch (\Exception $e) {
            \Log::error('Error storing reply: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'خطا در ثبت پاسخ'
            ], 500);
        }
    }

    /**
     * Update rating statistics for a location
     */
    private function updateRatingStatistics($titleId)
    {
        try {
            // Check if rating_statistics table exists
            $tableExists = DB::select("SHOW TABLES LIKE 'rating_statistics'");
            if (empty($tableExists)) {
                \Log::info('rating_statistics table does not exist, skipping statistics update');
                return;
            }

            $ratings = DB::table('ratings')
                ->where('title_id', $titleId)
                ->where('status', 'approved')
                ->get();

            if ($ratings->isEmpty()) {
                return;
            }

            $totalRatings = $ratings->count();
            $averageRating = $ratings->avg('rating');

            $ratingCounts = [
                'rating_1_count' => $ratings->where('rating', 1)->count(),
                'rating_2_count' => $ratings->where('rating', 2)->count(),
                'rating_3_count' => $ratings->where('rating', 3)->count(),
                'rating_4_count' => $ratings->where('rating', 4)->count(),
                'rating_5_count' => $ratings->where('rating', 5)->count(),
            ];

            $averages = [
                'service_avg' => $ratings->whereNotNull('service_rating')->avg('service_rating'),
                'quality_avg' => $ratings->whereNotNull('quality_rating')->avg('quality_rating'),
                'location_avg' => $ratings->whereNotNull('location_rating')->avg('location_rating'),
                'price_avg' => $ratings->whereNotNull('price_rating')->avg('price_rating'),
                'cleanliness_avg' => $ratings->whereNotNull('cleanliness_rating')->avg('cleanliness_rating'),
            ];

            $lastRatingDate = $ratings->max('created_at') ?? $ratings->max('timestamp');

            DB::table('rating_statistics')->updateOrInsert(
                ['title_id' => $titleId],
                array_merge([
                    'total_ratings' => $totalRatings,
                    'average_rating' => round($averageRating, 2),
                    'last_rating_date' => $lastRatingDate,
                    'updated_at' => now()
                ], $ratingCounts, $averages)
            );
        } catch (\Exception $e) {
            \Log::error('Error updating rating statistics: ' . $e->getMessage());
        }
    }

    /**
     * Get ratings for a location
     */
    public function getLocationRatings($titleId)
    {
        try {
            $ratings = DB::table('ratings')
                ->where('title_id', $titleId)
                ->where('status', 'approved')
                ->orderBy('created_at', 'desc')
                ->get();

            $statistics = DB::table('rating_statistics')
                ->where('title_id', $titleId)
                ->first();

            return response()->json([
                'success' => true,
                'ratings' => $ratings,
                'statistics' => $statistics
            ]);
        } catch (\Exception $e) {
            \Log::error('Error getting location ratings: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'خطا در دریافت نظرات'
            ], 500);
        }
    }
}
