<?php

namespace App\Http\Controllers;

use App\Models\Tier;
use App\Models\EmpCompanyDetails;
use DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;
use Illuminate\Http\Request;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Auth;

class TierController extends Controller
{
public function index(Request $request)
{
    $userTable = $this->getUserTable();
    
    // Start with base Tier query
    $tierQuery = Tier::query();
    
    // Apply user-specific filters to tiers
    if ($userTable === "customer") {
        $tierQuery->where('customer_id', Auth::user()->id)
                  ->where('workspace_id', Auth::user()->current_workspace_id);
    }
    
    if ($userTable === "emp") {
        $tierQuery->where('customer_id', auth()->user()->customer_id)
                  ->where('workspace_id', auth()->user()->workspace_id);
    }
    
    // Get count before applying limit/offset
    $count = $tierQuery->count();
    if($request->filled('pagination') || !empty($request->filters)){
        return $this->searchFilter($tierQuery, $request);
    }
    $tiers = $tierQuery->get();
    
    // Get tier IDs for company details query
    $tierIds = $tiers->pluck('id')->toArray();
    
    $companyDetailsGrouped = collect();
    
    if (!empty($tierIds)) {
        // Modified query to ensure we get all company details
        // Using RIGHT JOIN or separate queries might be better
        $companyDetails = EmpCompanyDetails::withoutGlobalScope(\App\Scopes\NotDeletedScope::class)
            ->leftJoin('emp_personal_details', 'emp_company_details.id', '=', 'emp_personal_details.emp_id')
            ->whereIn('emp_company_details.tier_id', $tierIds)
            ->select(
                'emp_company_details.id',
                'emp_company_details.tier_id',
                'emp_company_details.user_type',
                'emp_company_details.employee_email',
                
                // Personal details with fallbacks
                DB::raw('COALESCE(emp_personal_details.first_name, emp_company_details.employee_email) as first_name'),
                'emp_personal_details.middle_name',
                'emp_personal_details.last_name',
                DB::raw('COALESCE(emp_personal_details.image, "upload/images/default.png") as image'),
                'emp_personal_details.emp_id'
            )
            ->get();
            
        // Debug: Check if company details are being fetched
        \Log::info('Tier IDs: ' . json_encode($tierIds));
        \Log::info('Company Details Count: ' . $companyDetails->count());
        
        // Apply filters to company details if needed
        if ($request->filled('filter')) {
            $filters = json_decode($request->filter, true);
            $companyDetails = $this->filterCompanyDetails($companyDetails, $filters);
        }
        
        // Group by tier_id
        $companyDetailsGrouped = $companyDetails->groupBy('tier_id');
    }
    
    
    
    // Build the response structure
    $responseData = $tiers->map(function($tier) use ($companyDetailsGrouped) {
        $tierCompanyDetails = $companyDetailsGrouped->get($tier->id, collect());
        
        return [
            'id' => $tier->id,
            'customer_id' => $tier->customer_id,
            'workspace_id' => $tier->workspace_id,
            'title' => $tier->title,
            'description' => $tier->description,
            'created_at' => $tier->created_at,
            'updated_at' => $tier->updated_at,
            'tier_key' => $tier->tier_key,
            'company_details' => $tierCompanyDetails->map(function($detail) {
                return [
                    'id' => $detail->id,
                    'tier_id' => (string)$detail->tier_id,
                    'user_type' => $detail->user_type,
                    'tier_emp_personal_detail' => [
                        'emp_id' => (string)$detail->id,
                        'first_name' => $detail->first_name,
                        'middle_name' => $detail->middle_name,
                        'last_name' => $detail->last_name,
                        'image' => $detail->image
                    ]
                ];
            })->values()->toArray()
        ];
    })->toArray();
    
    $array_filter = json_decode($request->filter ?? '{}', true);
    $filters = [
        'first_name' => $array_filter['first_name'] ?? '',
        'middle_name' => $array_filter['middle_name'] ?? '',
        'last_name' => $array_filter['last_name'] ?? '',
        'employee_email' => $array_filter['employee_email'] ?? '',
        'completed' => $array_filter['completed'] ?? '',
        'status' => $array_filter['status'] ?? '',
    ];
    
    return response()->json([
        'data' => $responseData,
        'filter' => $filters,
        'message' => 'success',
        'totalCount' => $count,
        'statusCode' => 200
    ]);
}

// Helper method to filter company details
private function filterCompanyDetails($companyDetails, $filters)
{
    return $companyDetails->filter(function($detail) use ($filters) {
        $matches = true;
        
        if (!empty($filters['first_name'])) {
            $matches = $matches && (
                stripos($detail->first_name, $filters['first_name']) !== false
            );
        }
        
        if (!empty($filters['middle_name'])) {
            $matches = $matches && (
                stripos($detail->middle_name ?? '', $filters['middle_name']) !== false
            );
        }
        
        if (!empty($filters['last_name'])) {
            $matches = $matches && (
                stripos($detail->last_name ?? '', $filters['last_name']) !== false
            );
        }
        
        if (!empty($filters['employee_email'])) {
            $matches = $matches && (
                stripos($detail->employee_email, $filters['employee_email']) !== false
            );
        }
        
        // Add other filter conditions as needed
        if (!empty($filters['completed'])) {
            // Add your completed filter logic here
        }
        
        if (!empty($filters['status'])) {
            // Add your status filter logic here
        }
        
        return $matches;
    });
}

    public function store(Request $request)
    {
        $userTable = $this->getUserTable();
        $auth_id = 0;
        $workspace_id = 0;
        if ($userTable === "customer") {
            $auth_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
        }
        if ($userTable === "emp") {
            $auth_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
        }
        
        $validator = Validator::make($request->all(), [
            'title' => [
                'required',
                'string',
                Rule::unique('tiers')->where(function ($query) use ($auth_id, $workspace_id) {
                    $query->where('customer_id', $auth_id)
                        ->where('workspace_id', $workspace_id);
                }),

            ],
            'description' => 'nullable',
            'tier_key' => [
                'nullable',
                Rule::unique('tiers')->where(function ($query) use ($auth_id, $workspace_id) {
                    $query->where('customer_id', $auth_id)
                        ->where('workspace_id', $workspace_id);
                }),

            ],
        ], [
            'title.unique' => 'The title must be unique within your workspace and customer account.',
            'tier_key.unique' => 'The Tier Key must be unique within your workspace and customer account.',
        ]);

        // Check if validation fails
        if ($validator->fails()) {
            return $this->handleValidationFailure($validator);
        } else {
            $validatedData = $validator->validated();
            $validatedData['workspace_id'] = $workspace_id;
            $validatedData['customer_id'] = $auth_id;
            $DATA = Tier::insert($validatedData);
            $data = Tier::where('workspace_id', $workspace_id)
                ->where('customer_id', $auth_id)
                ->get();
            return $this->success($data, 'Tier Saved Successfully');
        }
    }



    public function edit($id)
    {

        $tier = Tier::find($id);
        if (!$tier) {
            return $this->message('The tier with the specified ID does not exist.', 404);
        }
        $userTable = $this->getUserTable();
        if (
            $userTable == "customer" && ($tier->workspace_id != auth()->user()->current_workspace_id || $tier->customer_id != auth()->user()->id)
        ) {
            return $this->message('You do not have access to this Tier', 403);
        }

        if ($userTable == "emp" && ($tier->customer_id != auth()->user()->customer_id || $tier->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Tier', 403);
        }
        $tier = Tier::where('id', $id)->get();
        return $this->success($tier, 'tier  Successfully');
    }

    public function tier_update(Request $request)
    {
        $userTable = $this->getUserTable();
        $auth_id = 0;
        $workspace_id = 0;
        if ($userTable === "customer") {
            $auth_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
        }
        if ($userTable === "emp") {
            $auth_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
        }
        $id = $request->id;
        $validator = Validator::make($request->all(), [
            'title' => [
                'required',
                'string',
                Rule::unique('tiers', 'title')
                    ->where('customer_id', $auth_id)
                    ->where('workspace_id', $workspace_id)
                    ->ignore($id), // Ignores the current ID for updates
            ],
            'description' => 'nullable|string',
            'tier_key' => [
                'nullable',
                'string',
                Rule::unique('tiers', 'tier_key')
                    ->where('customer_id', $auth_id)
                    ->where('workspace_id', $workspace_id)
                    ->ignore($id), // Ignores the current ID for updates
            ]
        ], [
            'title.unique' => 'The title must be unique within your workspace and customer account.',
            'tier_key.unique' => 'The Tier Key must be unique within your workspace and customer account.',
        ]);
        if ($validator->fails()) {

            return $this->handleValidationFailure($validator);
        } else {


            $tier = Tier::find($id);
            if (!$tier) {
                return $this->message('The tier with the specified ID does not exist.', 404);
            }
            $userTable = $this->getUserTable();
            if (
                $userTable == "customer" && ($tier->workspace_id != auth()->user()->current_workspace_id || $tier->customer_id != auth()->user()->id)
            ) {
                return $this->message('You do not have access to this Tier', 403);
            }

            if ($userTable == "emp" && ($tier->customer_id != auth()->user()->customer_id || $tier->workspace_id != auth()->user()->workspace_id)) {
                return $this->message('You do not have access to this Tier', 403);
            }
            $tier = Tier::findOrFail($id);
            $tier->update($request->all());

            $data = Tier::where('workspace_id', $workspace_id)
                ->where('customer_id', $auth_id)
                ->get();
            return $this->success($data, 'Tier Updated Successfully');
        }
    }

    public function destroy($id)
    {

        $tier = Tier::find($id);
        if (!$tier) {
            return $this->message('The tier with the specified ID does not exist.', 404);
        }
        $userTable = $this->getUserTable();
        if (
            $userTable == "customer" && ($tier->workspace_id != auth()->user()->current_workspace_id || $tier->customer_id != auth()->user()->id)
        ) {
            return $this->message('You do not have access to this Tier', 403);
        }

        if ($userTable == "emp" && ($tier->customer_id != auth()->user()->customer_id || $tier->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Tier', 403);
        }
        if (DB::table('emp_company_details')->where('emp_company_details.del' , 0)->where('emp_company_details.tier_id', $id)->count() || DB::table('tier_packages')->where('tier_id', $id)->count()) {

            return $this->message('Please delete the related employees and packages first', 403);
        }

        Tier::where('id', $id)->delete();
        return $this->message('Tier Deleted Successfully', 200);
    }

    public function tierdesc($id)
    {
        $tier = Tier::find($id);
        if (!$tier) {
            return $this->message('The tier with the specified ID does not exist.', 404);
        }
        $userTable = $this->getUserTable();
        if (
            $userTable == "customer" && ($tier->workspace_id != auth()->user()->current_workspace_id || $tier->customer_id != auth()->user()->id)
        ) {
            return $this->message('You do not have access to this Tier', 403);
        }

        if ($userTable == "emp" && ($tier->customer_id != auth()->user()->customer_id || $tier->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Tier', 403);
        }
        return $this->success($data, 'Tier  Successfully');
    }

    public function tier_emp($id)
    {
        $tier = Tier::find($id);
        if (!$tier) {
            return $this->message('The tier with the specified ID does not exist.', 404);
        }

        $userTable = $this->getUserTable();
        if (
            $userTable == "customer" && ($tier->workspace_id != auth()->user()->current_workspace_id || $tier->customer_id != auth()->user()->id)
        ) {
            return $this->message('You do not have access to this Tier', 403);
        }

        if ($userTable == "emp" && ($tier->customer_id != auth()->user()->customer_id || $tier->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Tier', 403);
        }

         $emp = EmpCompanyDetails::withoutGlobalScope(\App\Scopes\NotDeletedScope::class)
        ->leftJoin('emp_personal_details', 'emp_company_details.id', '=', 'emp_personal_details.emp_id')
        ->where('emp_company_details.tier_id', $id)
        ->select(
            'emp_company_details.id as emp_id',
            'emp_personal_details.first_name',
            'emp_personal_details.middle_name',
            'emp_personal_details.last_name',
            'emp_personal_details.image',
            'emp_company_details.employee_email',
            'emp_company_details.status'
        )
        ->get();

        // Process the results to handle null first_name and image
        $emp = $emp->map(function ($employee) {
            if (empty($employee->first_name)) {
                $employee->first_name = $employee->employee_email;
            }
            if (empty($employee->image)) {
                $employee->image = 'upload/images/default.png';
            }
            // Remove employee_email from final result if not needed
            // unset($employee->employee_email);
            return $employee;
        });

        return $this->success($emp, 'Tier Employee Successfully');
    }
}
