<?php

namespace App\Http\Controllers\api\v1;

use App\Http\Controllers\Controller;
use App\Models\Badge;
use App\Models\HomeLogos;
use App\Models\User;
use App\Models\UserPreference;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;

class HomeController extends Controller
{
    private function validateAccesToken($access_token)
    {
        $user = User::where(['user_access_token' => $access_token])->first();
        if (!$user) {
            http_response_code(401);
            echo json_encode([
                'status' => (string) 0,
                'message' => "Invalid access token",
                'oData' => (object) array(),
                'errors' => (object) [],
            ]);
            exit;
        }

        if ($user->status != 1) {
            http_response_code(401);
            echo json_encode([
                'status' => (string) 0,
                'message' => "User is not active",
                'oData' => (object) array(),
                'errors' => (object) [],
            ]);
            exit;
        }

        return $user;
    }

  public function getUserMatchListing(Request $request)
    {
        $status = "1";
        $message = "Match list fetched successfully";
        $o_data = [];
        $errors = [];

        $validator = Validator::make($request->all(), [
            'access_token' => 'required',
            'page' => 'nullable|integer|min:1',
            'limit' => 'nullable|integer|min:1|max:100',
        ]);

        if ($validator->fails()) {
            $status = (string) 0;
            $message = "Validation error occurred";
            $errors = $validator->messages();
            return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);
        }

        $user = $this->validateAccesToken($request->access_token);
        $userId=$user->id;
        $currentUserVariantIds = \App\Models\UserDiseaseVariant::where('user_id', $userId)
    ->pluck('genetic_disease_variant_id')
    ->toArray();
        $limit = $request->limit ?? 10;
        $offset = isset($request->page) ? ($request->page - 1) * $limit : 0;

        $users_id=$user->matches()->pluck('matched_user_id');
      $userData = User::with(['children', 'preference','nationality','emirate', 'country', 'images', 'details.sect', 'details.education', 'details.profession', 'details.maritalStatus', 'details.dressCode',  'plan', 'sports.sport', 'activities.activity', 'known_languages.language', 'notification_settings','geneticDiseases.geneticDisease','geneticDiseases.geneticDiseaseVariant.geneticDiseaseVariant'])->whereIn('users.id',$users_id)           
           ->leftJoin('user_requests as ur_sent', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_sent.receiver_id')
             ->where('ur_sent.sender_id', '=', $userId);
    })
    ->leftJoin('user_requests as ur_received', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_received.sender_id')
             ->where('ur_received.receiver_id', '=', $userId);
    })
    ->leftJoin('user_blocks as ub_me', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ub_me.blocked_user_id')
             ->where('ub_me.user_id', '=', $userId);
    })
    ->leftJoin('user_blocks as ub_other', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ub_other.user_id')
             ->where('ub_other.blocked_user_id', '=', $userId);
    })
    ->leftJoin('user_reports as ur_me', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_me.reported_user_id')
             ->where('ur_me.reporter_id', '=', $userId);
    })
    ->leftJoin('user_reports as ur_other', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_other.reporter_id')
             ->where('ur_other.reported_user_id', '=', $userId);
    })
    ->leftJoin('user_connections as uc1', function ($join) use ($userId) {
        $join->on('users.id', '=', 'uc1.connected_user_id')
             ->where('uc1.user_id', '=', $userId);
    })
    ->leftJoin('user_connections as uc2', function ($join) use ($userId) {
        $join->on('users.id', '=', 'uc2.user_id')
             ->where('uc2.connected_user_id', '=', $userId);
    })
    ->leftJoin('profile_picture_requests as ppr', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ppr.receiver_id')
             ->where('ppr.sender_id', '=', $userId);
    })
    ->whereNull('users.deleted_at')
    ->where('users.deleted',0)

    // Exclude blocked or reported users entirely
    ->whereNull('ub_me.id')
    ->whereNull('ub_other.id')
    ->whereNull('ur_me.id')
    ->whereNull('ur_other.id')

    ->select('users.*')
    ->selectRaw('
        CASE WHEN uc1.id IS NOT NULL OR uc2.id IS NOT NULL THEN 1 ELSE 0 END as is_connected,

        CASE 
            WHEN (ub_me.id IS NULL AND ub_other.id IS NULL AND ur_me.id IS NULL AND ur_other.id IS NULL) 
                 AND ur_sent.id IS NOT NULL 
            THEN 1 ELSE 0 
        END as is_sent,

        CASE 
            WHEN (ub_me.id IS NULL AND ub_other.id IS NULL AND ur_me.id IS NULL AND ur_other.id IS NULL) 
                 AND ur_received.id IS NOT NULL 
            THEN 1 ELSE 0 
        END as is_received,

        0 as is_blocked, -- always zero since blocked users excluded
        0 as am_i_blocked, -- same here

        0 as is_reported, -- reported users excluded
        0 as am_i_reported,

        CASE 
            WHEN (uc1.id IS NOT NULL OR uc2.id IS NOT NULL) 
            THEN COALESCE(uc1.hide_image, 0) ELSE 0 
        END as hide_image_by_current_user,

        CASE 
            WHEN ppr.status = "accepted" 
            THEN "0" ELSE "1"
        END as hide_image_by_target_user
    ')
             ->where('gender', '!=', $user->gender)
            ->when($user->gender == GENDER_MALE,function($q){
                $q->where('users.parent_approval', 1);
            })->when($user->not_show_over_aged,function($q) use ($user){
                    $dob=$user->dob;
                 $q->whereDate('users.dob','>=',$dob);
                })
              ->where('users.status', 1)
            ->where('users.deleted', 0)
            ->whereHas('matches',function($q){
                $q->where('match_percentage','>=',40);
            })
             ->whereDoesntHave('geneticDiseases.geneticDiseaseVariant', function ($query) use ($currentUserVariantIds) {
    $query->whereIn('genetic_disease_variant_id', $currentUserVariantIds);
})
->when(in_array(11, $currentUserVariantIds), function ($query) {
    $query->whereDoesntHave('geneticDiseases.geneticDiseaseVariant', function ($q) {
        $q->where('genetic_disease_variant_id', 2);
    });
})
->when(in_array(2, $currentUserVariantIds), function ($query) {
    $query->whereDoesntHave('geneticDiseases.geneticDiseaseVariant', function ($q) {
        $q->where('genetic_disease_variant_id', 11);
    });
})
             ->when($limit != 0, function ($query) use ($limit, $offset) {
                $query->limit($limit)->offset($offset);
            })
          ->get()
          ->map(function ($u) use ($user) {
                $u = $u->toArray();
                convertNullRelationsToObjects($u);
            return convert_all_elements_to_string($u, true);

          })->toArray();                 

        $o_data['users'] = $userData;
        
        return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);

    }

    public function getPremiumUserListing(Request $request)
    {
        $status = "1";
        $message = "Match list fetched successfully";
        $o_data = [];
        $errors = [];

        $validator = Validator::make($request->all(), [
            'access_token' => 'required',
            'page' => 'nullable|integer|min:1',
            'limit' => 'nullable|integer|min:1|max:100',
        ]);

        if ($validator->fails()) {
            $status = (string) 0;
            $message = "Validation error occurred";
            $errors = $validator->messages();
            return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);
        }

        $user = $this->validateAccesToken($request->access_token);
        $limit = $request->limit ?? 10;
        $offset = isset($request->page) ? ($request->page - 1) * $limit : 0;
        $userId=$user->id;
$currentUserVariantIds = \App\Models\UserDiseaseVariant::where('user_id', $userId)
    ->pluck('genetic_disease_variant_id')
    ->toArray();
        $users_id=$user->matches()->pluck('matched_user_id');
      $userList = User::with(['children', 'preference','nationality','emirate', 'country', 'images', 'details.sect', 'details.education', 'details.profession', 'details.maritalStatus', 'details.dressCode',  'plan', 'sports.sport', 'activities.activity', 'known_languages.language', 'notification_settings','geneticDiseases.geneticDisease','geneticDiseases.geneticDiseaseVariant.geneticDiseaseVariant'])->whereIn('users.id',$users_id)
           ->whereHas('plan', function ($query) {
                $query->where('type', 'premium');
            })         
           ->leftJoin('user_requests as ur_sent', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_sent.receiver_id')
             ->where('ur_sent.sender_id', '=', $userId);
    })
    ->leftJoin('user_requests as ur_received', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_received.sender_id')
             ->where('ur_received.receiver_id', '=', $userId);
    })
    ->leftJoin('user_blocks as ub_me', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ub_me.blocked_user_id')
             ->where('ub_me.user_id', '=', $userId);
    })
    ->leftJoin('user_blocks as ub_other', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ub_other.user_id')
             ->where('ub_other.blocked_user_id', '=', $userId);
    })
    ->leftJoin('user_reports as ur_me', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_me.reported_user_id')
             ->where('ur_me.reporter_id', '=', $userId);
    })
    ->leftJoin('user_reports as ur_other', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_other.reporter_id')
             ->where('ur_other.reported_user_id', '=', $userId);
    })
    ->leftJoin('user_connections as uc1', function ($join) use ($userId) {
        $join->on('users.id', '=', 'uc1.connected_user_id')
             ->where('uc1.user_id', '=', $userId);
    })
    ->leftJoin('user_connections as uc2', function ($join) use ($userId) {
        $join->on('users.id', '=', 'uc2.user_id')
             ->where('uc2.connected_user_id', '=', $userId);
    })
    ->leftJoin('profile_picture_requests as ppr', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ppr.receiver_id')
             ->where('ppr.sender_id', '=', $userId);
    })
    ->whereNull('users.deleted_at')
    ->where('users.deleted',0)

    // Exclude blocked or reported users entirely
    ->whereNull('ub_me.id')
    ->whereNull('ub_other.id')
    ->whereNull('ur_me.id')
    ->whereNull('ur_other.id')

    ->select('users.*')
    ->selectRaw('
        CASE WHEN uc1.id IS NOT NULL OR uc2.id IS NOT NULL THEN 1 ELSE 0 END as is_connected,

        CASE 
            WHEN (ub_me.id IS NULL AND ub_other.id IS NULL AND ur_me.id IS NULL AND ur_other.id IS NULL) 
                 AND ur_sent.id IS NOT NULL 
            THEN 1 ELSE 0 
        END as is_sent,

        CASE 
            WHEN (ub_me.id IS NULL AND ub_other.id IS NULL AND ur_me.id IS NULL AND ur_other.id IS NULL) 
                 AND ur_received.id IS NOT NULL 
            THEN 1 ELSE 0 
        END as is_received,

        0 as is_blocked, -- always zero since blocked users excluded
        0 as am_i_blocked, -- same here

        0 as is_reported, -- reported users excluded
        0 as am_i_reported,

        CASE 
            WHEN (uc1.id IS NOT NULL OR uc2.id IS NOT NULL) 
            THEN COALESCE(uc1.hide_image, 0) ELSE 0 
        END as hide_image_by_current_user,

        CASE 
            WHEN ppr.status = "accepted" 
            THEN "0" ELSE "1"
        END as hide_image_by_target_user
    ')->when($user->gender == GENDER_MALE,function($q){
                $q->where('users.parent_approval', 1);
            })->when($user->not_show_over_aged,function($q) use ($user){
                    $dob=$user->dob;
                 $q->whereDate('users.dob','>=',$dob);
                })
                ->where('gender', '!=', $user->gender)
              ->where('users.status', 1)
            ->where('users.deleted', 0)
             ->whereDoesntHave('geneticDiseases.geneticDiseaseVariant', function ($query) use ($currentUserVariantIds) {
    $query->whereIn('genetic_disease_variant_id', $currentUserVariantIds);
})
->when(in_array(11, $currentUserVariantIds), function ($query) {
    $query->whereDoesntHave('geneticDiseases.geneticDiseaseVariant', function ($q) {
        $q->where('genetic_disease_variant_id', 2);
    });
})
->when(in_array(2, $currentUserVariantIds), function ($query) {
    $query->whereDoesntHave('geneticDiseases.geneticDiseaseVariant', function ($q) {
        $q->where('genetic_disease_variant_id', 11);
    });
})

             ->when($limit != 0, function ($query) use ($limit, $offset) {
                $query->limit($limit)->offset($offset);
            })
          ->get()
          ->map(function ($u) use ($user) {
                $u = $u->toArray();
                convertNullRelationsToObjects($u);
            return convert_all_elements_to_string($u, true);

          })->toArray();  

   

        $o_data['premium_users'] = $userList;
        
        return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);
        
    }

    public function getUserHomeStats(Request $request)
    {
        $status = "1";
        $message = "Home stats fetched successfully";
        $o_data = [];
        $errors = [];

        $validator = Validator::make($request->all(), [
            'access_token' => 'required',
        ]);

        if ($validator->fails()) {
            $status = (string) 0;
            $message = "Validation error occurred";
            $errors = $validator->messages();
            return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);
        }

        $user = $this->validateAccesToken($request->access_token);
        exec("php " . base_path() . "/artisan app:send-first-push " . $user->id . " > /dev/null 2>&1 & "); 

        $userPreference = UserPreference::where('user_id',$user->id)->first();

        if($userPreference){

            $query = $user->matches()->with(['details'])
                ->join('user_details', 'user_details.user_id', '=', 'users.id')
                ->where('users.status', 1)
                ->where('users.deleted', 0)
                ->where('user_matches.match_percentage','>=',40);

            if($user->gender == GENDER_MALE){
                $query->where('users.parent_approval', 1);
            }

            $totalCount = (clone $query)->count();

            $educationCount = clone $query;
            if($userPreference->user->educations()->count() > 0){
                $educationsIds = $userPreference->user->educations()->pluck('education_id')->toArray();
                $educationCount = $educationCount->whereIn('user_details.education_id', $educationsIds)
                    ->count();
            } else {
                $educationCount = $totalCount;
            }

            $sectCount = clone $query;
            if($userPreference->user->sects()->count() > 0){
                $sectIds = $userPreference->user->sects()->pluck('sect_id')->toArray();
                $sectCount = $sectCount->whereIn('user_details.sect_id', $sectIds)
                    ->count();
            } else {
                $sectCount = $totalCount;
            }

            $professionCount = clone $query;
            if($userPreference->user->professions()->count() > 0){
                $professionIds = $userPreference->user->professions()->pluck('profession_id')->toArray();
                $professionCount = $professionCount->whereIn('user_details.profession_id', $professionIds)
                    ->count();
            } else {
                $professionCount = $totalCount;
            }

            $maritalStatusCount = clone $query;
            if($userPreference->user->maritalStatuses()->count() > 0){
                $martialStatusId = $userPreference->user->maritalStatuses()->pluck('marital_status_id')->toArray();
                $maritalStatusCount = $maritalStatusCount->whereIn('user_details.marital_status_id', $martialStatusId)
                    ->count();
            } else {
                $maritalStatusCount = $totalCount;
            }

            $heightCount = clone $query;
            if(!empty($userPreference->from_height_cm) || !empty($userPreference->to_height_cm)){
                $fromHeight = (float) $userPreference->from_height_cm;
                $toHeight = (float) $userPreference->to_height_cm;
                $heightCount = $heightCount->whereBetween(DB::raw('CAST(user_details.height_cm AS FLOAT)'), [$fromHeight, $toHeight])->count();
            } else {
                $heightCount = $totalCount;
            }

            $weightCount = clone $query;
            if(!empty($userPreference->from_weight_kg) || !empty($userPreference->to_weight_kg)){
                $fromWeight = (float) $userPreference->from_weight_kg;
                $toWeight = (float) $userPreference->to_weight_kg;
                $weightCount = $weightCount->whereBetween(DB::raw('CAST(user_details.weight_kg AS FLOAT)'), [$fromWeight, $toWeight])->count();
            } else {
                $weightCount = $totalCount;
            }

            $religionCount = clone $query;
            if(!empty($userPreference->religion_practice )){
                $range = null;
                $ranges = [
                    [
                    'from' => 0,
                    'to' => 33,
                    'label_en' => 'Not Practicing',
                    'label_ar' => 'غير ممارس'
                    ],
                    [
                    'from' => 34,
                    'to' => 66,
                    'label_en' => 'Moderate Practicing',
                    'label_ar' => 'ممارس معتدل'
                    ],
                    [
                    'from' => 67,
                    'to' => 100,
                    'label_en' => 'Very Practicing',
                    'label_ar' => 'ممارس جدا'
                    ],
                    ];
                foreach ($ranges as $r) {
                    if ($userPreference->religion_practice >= $r['from'] && $userPreference->religion_practice <= $r['to']) {
                        $range = $r;
                        break;
                    }
                }
                $religionCount = $religionCount->whereBetween('user_details.religious_practice', [$range['from'],$range['to']])->count();
            } else {
                $religionCount = $totalCount;
            }

            $dressCodeCount = clone $query;
            if($userPreference->user->dressCodes()->count() > 0){
                $dressCodeIds = $userPreference->user->dressCodes()->pluck('dress_code_id')->toArray();
                $dressCodeCount = $dressCodeCount->whereIn('user_details.dress_code_id', $dressCodeIds)
                    ->count();
            } else {
                $dressCodeCount = $totalCount;
            }

            $smokeCount = clone $query;
            if($userPreference->smoking !== NULL){
                $smokeCount = $smokeCount->where('user_details.smoking', $userPreference->smoking)
                    ->count();
            } else {
                $smokeCount = $totalCount;
            }

            $o_data['stats'] = [
                'education_count' => [
                    'count' => $educationCount,
                    'icon' => HomeLogos::where('slug','education_count')->first()??(object)[]
                ],
                'religion_count' => [
                    'count' => $religionCount,
                    'icon' => HomeLogos::where('slug','religion_count')->first()??(object)[]
                ],
                'profession_count' => [
                    'count' => $professionCount,
                    'icon' => HomeLogos::where('slug','profession_count')->first()??(object)[]
                ],
                'height_count' => [
                    'count' => $heightCount,
                    'icon' => HomeLogos::where('slug','height_count')->first()??(object)[]
                ],
                'weight_count' => [
                    'count' => $weightCount,
                    'icon' => HomeLogos::where('slug','weight_count')->first()??(object)[]
                ],
                'marital_status_count' => [
                    'count' => $maritalStatusCount,
                    'icon' => HomeLogos::where('slug','marital_status_count')->first()??(object)[]
                ],
                'sect_count' => [
                    'count' => $sectCount,
                    'icon' => HomeLogos::where('slug','sect_count')->first()??(object)[]
                ],
                'dress_code_count' => [
                    'count' => $dressCodeCount,
                    'icon' => HomeLogos::where('slug','dress_code_count')->first()??(object)[]
                ],
                'smoke_count' => [
                    'count' => $smokeCount,
                    'icon' => HomeLogos::where('slug','smoke_count')->first()??(object)[]
                ],
            ];
        } else {
            $o_data['stats'] = [
                'education_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','education_count')->first()??(object)[]
                ],
                'religion_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','religion_count')->first()??(object)[]
                ],
                'profession_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','profession_count')->first()??(object)[]
                ],
                'height_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','height_count')->first()??(object)[]
                ],
                'weight_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','weight_count')->first()??(object)[]
                ],
                'marital_status_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','marital_status_count')->first()??(object)[]
                ],
                'sect_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','sect_count')->first()??(object)[]
                ],
                'dress_code_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','dress_code_count')->first()??(object)[]
                ],
                'smoke_count' => [
                    'count' => 0,
                    'icon' => HomeLogos::where('slug','smoke_count')->first()??(object)[]
                ],
            ];
        }

        $isUserPremium = $user->plan && $user->plan->status == 1 && $user->plan->type === 'premium';

        $badges = Badge::where('status', 1);
        if($isUserPremium) {
            $badges->where('type', '!=' , 'upgrade');
        }
        $o_data['badges'] = $badges->get()->toArray();

        $o_data = convert_all_elements_to_string($o_data, true);

        return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);
    }

    public function getStatUserList(Request $request)
    {
        $status = "1";
        $message = "User list fetched successfully";
        $o_data = [];
        $errors = [];

        $validator = Validator::make($request->all(), [
            'access_token' => 'required',
            'stat_type' => 'required|in:education_count,religion_count,profession_count,height_count,marital_status_count,weight_count,sect_count,dress_code_count,smoke_count',
            'page' => 'nullable|integer|min:1',
            'limit' => 'nullable|integer|min:1|max:100',
        ]);

        if ($validator->fails()) {
            $status = (string) 0;
            $message = "Validation error occurred";
            $errors = $validator->messages();
            return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);
        }

        $user = $this->validateAccesToken($request->access_token);

        $userPreference = UserPreference::where('user_id',$user->id)->first();

        if(!$userPreference){
            $status = (string) 0;
            $message = "User preference not found";
            return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);
        }

        $users_id=$user->matches()->pluck('matched_user_id');
        $userId=$user->id;
          $limit = $request->limit ?? 10;
        $offset = isset($request->page) ? ($request->page - 1) * $limit : 0;
      $query = User::join('user_details', 'user_details.user_id', '=', 'users.id')->with(['children', 'preference','nationality','emirate', 'country', 'images', 'details.sect', 'details.education', 'details.profession', 'details.maritalStatus', 'details.dressCode',  'plan', 'sports.sport', 'activities.activity', 'known_languages.language', 'notification_settings','geneticDiseases.geneticDisease','geneticDiseases.geneticDiseaseVariant.geneticDiseaseVariant'])->whereIn('users.id',$users_id)           
           ->leftJoin('user_requests as ur_sent', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_sent.receiver_id')
             ->where('ur_sent.sender_id', '=', $userId);
    })
    ->leftJoin('user_requests as ur_received', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_received.sender_id')
             ->where('ur_received.receiver_id', '=', $userId);
    })
    ->leftJoin('user_blocks as ub_me', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ub_me.blocked_user_id')
             ->where('ub_me.user_id', '=', $userId);
    })
    ->leftJoin('user_blocks as ub_other', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ub_other.user_id')
             ->where('ub_other.blocked_user_id', '=', $userId);
    })
    ->leftJoin('user_reports as ur_me', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_me.reported_user_id')
             ->where('ur_me.reporter_id', '=', $userId);
    })
    ->leftJoin('user_reports as ur_other', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ur_other.reporter_id')
             ->where('ur_other.reported_user_id', '=', $userId);
    })
    ->leftJoin('user_connections as uc1', function ($join) use ($userId) {
        $join->on('users.id', '=', 'uc1.connected_user_id')
             ->where('uc1.user_id', '=', $userId);
    })
    ->leftJoin('user_connections as uc2', function ($join) use ($userId) {
        $join->on('users.id', '=', 'uc2.user_id')
             ->where('uc2.connected_user_id', '=', $userId);
    })
    ->leftJoin('profile_picture_requests as ppr', function ($join) use ($userId) {
        $join->on('users.id', '=', 'ppr.receiver_id')
             ->where('ppr.sender_id', '=', $userId);
    })
    ->whereNull('users.deleted_at')
    ->where('users.deleted',0)

    // Exclude blocked or reported users entirely
    ->whereNull('ub_me.id')
    ->whereNull('ub_other.id')
    ->whereNull('ur_me.id')
    ->whereNull('ur_other.id')

    ->select('users.*')
    ->selectRaw('
        CASE WHEN uc1.id IS NOT NULL OR uc2.id IS NOT NULL THEN 1 ELSE 0 END as is_connected,

        CASE 
            WHEN (ub_me.id IS NULL AND ub_other.id IS NULL AND ur_me.id IS NULL AND ur_other.id IS NULL) 
                 AND ur_sent.id IS NOT NULL 
            THEN 1 ELSE 0 
        END as is_sent,

        CASE 
            WHEN (ub_me.id IS NULL AND ub_other.id IS NULL AND ur_me.id IS NULL AND ur_other.id IS NULL) 
                 AND ur_received.id IS NOT NULL 
            THEN 1 ELSE 0 
        END as is_received,

        0 as is_blocked, -- always zero since blocked users excluded
        0 as am_i_blocked, -- same here

        0 as is_reported, -- reported users excluded
        0 as am_i_reported,

        CASE 
            WHEN (uc1.id IS NOT NULL OR uc2.id IS NOT NULL) 
            THEN COALESCE(uc1.hide_image, 0) ELSE 0 
        END as hide_image_by_current_user,

        CASE 
            WHEN ppr.status = "accepted" 
            THEN "0" ELSE "1"
        END as hide_image_by_target_user
    ')
             ->where('gender', '!=', $user->gender)
            ->when($user->gender == GENDER_MALE,function($q){
                $q->where('users.parent_approval', 1);
            })->when($user->not_show_over_aged,function($q) use ($user){
                    $dob=$user->dob;
                 $q->whereDate('users.dob','>=',$dob);
                })
              ->where('users.status', 1)
            ->where('users.deleted', 0)
            ->whereHas('matches',function($q){
                $q->where('match_percentage','>=',40);
            });

        if($request->stat_type == 'education_count') {
            if($userPreference->user->educations()->count() > 0){
                $educationsIds = $userPreference->user->educations()->pluck('education_id')->toArray();
                $query->whereIn('user_details.education_id', $educationsIds);
            }
        } 

        if($request->stat_type == 'profession_count') {
            if($userPreference->user->professions()->count() > 0){
                $professionIds = $userPreference->user->professions()->pluck('profession_id')->toArray();
                $query->whereIn('user_details.profession_id', $professionIds);
            }
        }

        if($request->stat_type == 'sect_count') {
            if($userPreference->user->sects()->count() > 0){
                $sectIds = $userPreference->user->sects()->pluck('sect_id')->toArray();
                $query->whereIn('user_details.sect_id', $sectIds);
            }
        }

        if($request->stat_type == 'marital_status_count') {
            if($userPreference->user->maritalStatuses()->count() > 0){
                $martialStatusId = $userPreference->user->maritalStatuses()->pluck('marital_status_id')->toArray();
                $query->whereIn('user_details.marital_status_id', $martialStatusId);
            }
        }

        if($request->stat_type == 'height_count') {
            if(!empty($userPreference->from_height_cm) || !empty($userPreference->to_height_cm)){
                $fromHeight = (float) $userPreference->from_height_cm;
                $toHeight = (float) $userPreference->to_height_cm;
                $query->whereBetween(DB::raw('CAST(user_details.height_cm AS FLOAT)'), [$fromHeight, $toHeight]);
            }
        }

        if($request->stat_type == 'weight_count') {
            if(!empty($userPreference->from_weight_kg) || !empty($userPreference->to_weight_kg)){
                $fromWeight = (float) $userPreference->from_weight_kg;
                $toWeight = (float) $userPreference->to_weight_kg;
                $query->whereBetween(DB::raw('CAST(user_details.weight_kg AS FLOAT)'), [$fromWeight, $toWeight]);
            }
        }

        if($request->stat_type == 'religion_count') {
            if(!empty($userPreference->religion_practice)){
                $range = null;
                $ranges = [
                    [
                    'from' => 0,
                    'to' => 33,
                    'label_en' => 'Not Practicing',
                    'label_ar' => 'غير ممارس'
                    ],
                    [
                    'from' => 34,
                    'to' => 66,
                    'label_en' => 'Moderate Practicing',
                    'label_ar' => 'ممارس معتدل'
                    ],
                    [
                    'from' => 67,
                    'to' => 100,
                    'label_en' => 'Very Practicing',
                    'label_ar' => 'ممارس جدا'
                    ],
                ];
                foreach ($ranges as $r) {
                    if ($userPreference->religion_practice >= $r['from'] && $userPreference->religion_practice <= $r['to']) {
                        $range = $r;
                        break;
                    }
                }
                $query->whereBetween('user_details.religious_practice', [$range['from'],$range['to']]);
            }
        }

        if($request->stat_type == 'marital_status_count') {
            if($userPreference->user->maritalStatuses()->count() > 0){
                $martialStatusId = $userPreference->user->maritalStatuses()->pluck('marital_status_id')->toArray();
                $query->whereIn('user_details.marital_status_id', $martialStatusId);
            }
        }

        if($request->stat_type == 'dress_code_count') {
            if($userPreference->user->dressCodes()->count() > 0){
                $martialStatusId = $userPreference->user->dressCodes()->pluck('dress_code_id')->toArray();
                $query->whereIn('user_details.dress_code_id', $martialStatusId);
            }
        }


        if($request->stat_type == 'smoke_count') {
            if($userPreference->smoking !== NULL){
            $query->where('user_details.smoking', $userPreference->smoking)
                ->count();
            }
        }

        $userList = $query->offset($offset)->limit($limit)->orderBy('users.updated_at','desc')->get()->toArray();
        foreach ($userList as $key => $value) {
            convertNullRelationsToObjects($userList[$key]);
        }

        $o_data['stat_filter_users'] = convert_all_elements_to_string($userList, true);

        return response()->json(['status' => $status, 'error' => $errors, 'message' => $message, 'oData' => $o_data], 200);

    }


}