<?php

namespace App\Http\Controllers;

use App\Models\Community;
use App\Models\JobExperience;
use App\Models\RateMyPay;
use Illuminate\Http\Request;

class DashboardController extends Controller
{
    public function index(Request $request)
    {
        // return DB::getDefaultConnection();

        $user = auth()->user();
        // return config('database.default');
        // Fetch Unique Filter Options for Dropdowns
        // We get distinct values from JobExperiences to populate the filter dropdowns
        $filterOptions = [
            'countries' => JobExperience::where('is_published', true)->whereNotNull('country')->distinct()->pluck('country')->sort(),
            'titles' => JobExperience::where('is_published', true)->whereNotNull('job_title')->distinct()->pluck('job_title')->sort(),
            'experience_levels' => JobExperience::where('is_published', true)->distinct()->orderBy('years_of_experience')->pluck('years_of_experience'),
        ];

        // 2. Build the Feed Query
        $query = RateMyPay::with(['user', 'jobExperience', 'communities', 'likes'])
            ->withCount(['likes', 'comments', 'ratings']);

        // --- FILTER: Search ---
        if ($request->filled('search')) {
            $search = $request->search;
            $query->where(function($q) use ($search) {
                $q->where('content', 'like', "%{$search}%")
                  ->orWhereHas('jobExperience', function($jq) use ($search) {
                      $jq->where('job_title', 'like', "%{$search}%")
                         ->orWhere('company_name', 'like', "%{$search}%");
                  });
            });
        }

        // --- FILTER: Country ---
        if ($request->filled('country')) {
            $query->whereHas('jobExperience', fn($q) => $q->where('country', $request->country));
        }

        // --- FILTER: Job Title ---
        if ($request->filled('job_title')) {
            $query->whereHas('jobExperience', fn($q) => $q->where('job_title', $request->job_title));
        }

        // --- FILTER: Experience Years ---
        if ($request->filled('experience')) {
            $query->whereHas('jobExperience', fn($q) => $q->where('years_of_experience', $request->experience));
        }

        // --- TABS / SORTING ---
        $tab = $request->input('tab', 'all'); // Default to 'all'

        switch ($tab) {
            case 'community':
                // Only show posts from communities the user has joined
                $joinedCommunityIds = $user->communities()->pluck('communities.id');
                $query->whereHas('communities', fn($q) => $q->whereIn('communities.id', $joinedCommunityIds));
                $query->latest();
                break;

            case 'verified':
                // Highest Verified (Has payslip, sorted by Salary)
                $query->whereNotNull('payslip_path');
                // To sort by salary, we join job_experiences. 
                // Note: Standard Eloquent sorting on related columns is tricky, this is a simplified approach
                $query->join('job_experiences', 'rate_my_pays.job_experience_id', '=', 'job_experiences.id')
                      ->orderBy('job_experiences.annual_base_salary', 'desc')
                      ->select('rate_my_pays.*'); // Ensure we select post fields
                break;

            case 'recent':
            default:
                $query->latest();
                break;
        }

        // Execute Query
        $rateMyPayPosts = $query->paginate(15)->withQueryString();

        // 3. User Data (Sidebar/Widgets)
        $communities = $user->communities()->orderBy('name')->get();
        $experiences = JobExperience::where('user_id', $user->id)->where('is_published', true)->get();

        // 4. Calculate Totals (Keep existing logic)
        $grouped = $experiences->groupBy('currency');
        $totals = [];
        foreach ($grouped as $currency => $jobs) {
            $totals[$currency] = [
                'base_salary' => $jobs->sum('annual_base_salary'),
                'bonus' => $jobs->sum('bonus_commission'),
                'equity' => $jobs->sum('equity'),
                'benefits' => $jobs->pluck('benefits')->filter()->all()
            ];
        }

        return view('dashboard.index', compact(
            'totals',
            'user',
            'communities',
            'experiences',
            'rateMyPayPosts',
            'filterOptions'
        ));
    }
}
