<?php

namespace App\Http\Controllers;

namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use App\Models\Clearance;
use App\Models\ClearanceStatusLog;
use App\Models\ClearanceTemplate;
use App\Models\SignatoriesComment;
use Exception;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Validation\Rule;
use PHPUnit\Event\Code\Throwable;
use Illuminate\Support\Str;

class ClearanceController extends Controller
{

    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
        $user = Auth::user();
        $employeeId = $user->employees->employee_id ?? 'admin';

        $keyword = $request->input('keyword', '');
        $perPage = $request->input('per_page', PHP_INT_MAX);
        $sortBy = $request->input('sortBy', '');
        $sortType = $request->input('sortType', '');

        DB::enableQueryLog();
        $data = Clearance::with('template', 'employee:employee_id,firstname,lastname')
            ->where(function ($query) use ($keyword) {
                $keyword = str_replace(" ", "%", $keyword);
                $query->where('date_accepted', 'like', '%' . $keyword . '%');
            })
            ->where(function ($query) use ($employeeId, $request) {
                // if($request->status != 2) {
                // Show clearances created by the user
                $query->where('created_by', $employeeId)
                    // OR where the user is a primary officer in signatories
                    ->orWhereRaw("JSON_SEARCH(signatories, 'one', ?, NULL, '$[*].areas[*].items[*].primary_officer') IS NOT NULL", [$employeeId])
                    // OR where the user is a secondary officer in signatories
                    ->orWhereRaw("JSON_SEARCH(signatories, 'one', ?, NULL, '$[*].areas[*].items[*].secondary_officer') IS NOT NULL", [$employeeId]);
                }
            );


        if (!empty($sortBy) && !empty($sortType)) {
            $data = $data->orderBy($sortBy, $sortType);
        }

        $data = $data->paginate($perPage);

        // dd(DB::getQueryLog());
        return response()->json($data);
    }

    public function show($id): JsonResponse
    {
        try {
            // Fetch the clearance record with relationships
            $clearance = Clearance::with([
                'employee' => function($query) {
                    $query->select('employee_id', 'firstname', 'lastname', 'middlename', 'email');
                },
                'template' => function($query) {
                    $query->select('id', 'name', 'signatories');
                }
            ])->findOrFail($id);

            // Decode signatories if stored as JSON
            if ($clearance->signatories) {
                $clearance->signatories = is_string($clearance->signatories) 
                    ? json_decode($clearance->signatories, true) 
                    : $clearance->signatories;
            }

            // If signatories don't exist on clearance, get from template
            if (!$clearance->signatories && $clearance->template && $clearance->template->signatories) {
                $clearance->signatories = is_string($clearance->template->signatories)
                    ? json_decode($clearance->template->signatories, true)
                    : $clearance->template->signatories;
            }

            // Decode template signatories if needed
            if ($clearance->template && $clearance->template->signatories) {
                $clearance->template->signatories = is_string($clearance->template->signatories)
                    ? json_decode($clearance->template->signatories, true)
                    : $clearance->template->signatories;
            }

            return response()->json([
                'status' => 'success',
                'message' => 'Clearance record retrieved successfully',
                'data' => $clearance
            ], 200);

        } catch (\Illuminate\Database\Eloquent\ModelNotFoundException $e) {
            return response()->json([
                'status' => 'error',
                'message' => 'Clearance record not found',
                'data' => null
            ], 404);

        } catch (\Exception $e) {
            return response()->json([
                'status' => 'error',
                'message' => 'Failed to retrieve clearance record',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function store(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required',
            'clearance_template_id' => 'required',
            'reason_id' => 'required',
            'date_accepted' => 'required|date',
            'effectivity_date' => 'required|date',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
            'attachment' => 'required',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'status' => false,
                'message' => $validator->errors()->first(),
                'errors' => $validator->errors()
            ], Response::HTTP_UNPROCESSABLE_ENTITY);
        }

        DB::beginTransaction();

        try {
            $signatories = ClearanceTemplate::where('id', $request->clearance_template_id)
                ->pluck('signatories')
                ->first();

            // Decode the JSON signatories
            $signatoriesData = json_decode($signatories, true);

            // Collect all unique officer IDs for batch fetching
            $officerIds = [];
            foreach ($signatoriesData as $category) {
                foreach ($category['areas'] as $area) {
                    foreach ($area['items'] as $item) {
                        if (!is_null($item['primary_officer'])) {
                            $officerIds[] = $item['primary_officer'];
                        }
                        if (!is_null($item['secondary_officer'])) {
                            $officerIds[] = $item['secondary_officer'];
                        }
                    }
                }
            }

            // Fetch all employees in a single query and create a lookup map
            $employees = DB::table('employees')
                ->whereIn('employee_id', array_unique($officerIds))
                ->select('employee_id', DB::raw("CONCAT(firstname, ' ', lastname) as full_name"))
                ->pluck('full_name', 'employee_id')
                ->toArray();

            // Filter signatories to only include items with officers
            $filteredSignatories = [];
            
            foreach ($signatoriesData as $category) {
                $filteredAreas = [];
                
                foreach ($category['areas'] as $area) {
                    $filteredItems = [];
                    
                    foreach ($area['items'] as $item) {
                        // Keep item only if it has a primary_officer OR secondary_officer
                        if (!is_null($item['primary_officer']) || !is_null($item['secondary_officer'])) {
                            // Add officer names from the lookup map
                            $item['primary_officer_name'] = !is_null($item['primary_officer']) 
                                ? ($employees[$item['primary_officer']] ?? null)
                                : null;
                            
                            $item['secondary_officer_name'] = !is_null($item['secondary_officer']) 
                                ? ($employees[$item['secondary_officer']] ?? null)
                                : null;
                            
                            // Add new status fields
                            $item['status'] = 'pending';
                            $item['date_signed'] = null;
                            $item['time_signed'] = null;
                            $item['action_slip'] = null;
                            
                            $filteredItems[] = $item;
                        }
                    }
                    
                    // Only add area if it has items after filtering
                    if (!empty($filteredItems)) {
                        $filteredAreas[] = [
                            'name' => $area['name'],
                            'items' => $filteredItems
                        ];
                    }
                }
                
                // Only add category if it has areas after filtering
                if (!empty($filteredAreas)) {
                    $filteredSignatories[] = [
                        'name' => $category['name'],
                        'areas' => $filteredAreas
                    ];
                }
            }

            // Encode back to JSON
            $filteredSignatoriesJson = json_encode($filteredSignatories);

            $attachmentName = $this->handleAttachment($request);

            $status = Clearance::create([
                'employee_id' => $request->employee_id,
                'clearance_template_id' => $request->clearance_template_id,
                'reason_id' => $request->reason_id,
                'shared_services' => $request->shared_services,
                'date_accepted' => $request->date_accepted,
                'to_company' => $request->to_company,
                'effectivity_date' => $request->effectivity_date,
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'signatories' => $filteredSignatoriesJson,
                'note' => $request->note,
                'attachment' => $attachmentName,
                'created_by' => $request->created_by,
            ]);
            
            DB::commit();
            
            return response()->json([
                'status' => true,
                'message' => 'Saved successfully!',
                'data' => $status
            ], Response::HTTP_CREATED);

        } catch(Throwable $e) {
            DB::rollBack();
            return response()->json([
                'status' => false,
                'message' => 'Unable to process request. Please try again.',
                'data' => $e->getMessage()
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }

    public function handleAttachment(Request $request)
    {
        if ($request->hasFile('attachment')) {
            $image = $request->file('attachment');
            $attachmentName = time() . '_' . $image->getClientOriginalName();
            $path = "employee-clearance/" . $attachmentName;
            if (Storage::disk('local')->put($path, file_get_contents($image))) {
                return $attachmentName;
            }
            throw new Exception('Unable to process attachment.');
        }
        return '';
    }

    public function update(Request $request)
    {
        $id = $request->id;

        $validator = Validator::make($request->all(), [
            'employee_id' => 'required',
            'clearance_template_id' => 'required',
            'reason_id' => 'required',
            'date_accepted' => 'required|date',
            'effectivity_date' => 'required|date',
            'from_date' => 'required|date',
            'to_date' => 'required|date',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'status' => false,
                'message' => $validator->errors()->first(),
                'errors' => $validator->errors()
            ], Response::HTTP_UNPROCESSABLE_ENTITY);
        }

        DB::beginTransaction();

        try {
            $clearance = Clearance::findOrFail($id);

            if($clearance->clearance_template_id !== $request->clearance_template_id) {
                $signatories = ClearanceTemplate::where('id', $request->clearance_template_id)
                    ->pluck('signatories')
                    ->first();

                // Decode the JSON signatories
                $signatoriesData = json_decode($signatories, true);

                // Collect all unique officer IDs for batch fetching
                $officerIds = [];
                foreach ($signatoriesData as $category) {
                    foreach ($category['areas'] as $area) {
                        foreach ($area['items'] as $item) {
                            if (!is_null($item['primary_officer'])) {
                                $officerIds[] = $item['primary_officer'];
                            }
                            if (!is_null($item['secondary_officer'])) {
                                $officerIds[] = $item['secondary_officer'];
                            }
                        }
                    }
                }

                // Fetch all employees in a single query and create a lookup map
                $employees = DB::table('employees')
                    ->whereIn('employee_id', array_unique($officerIds))
                    ->select('employee_id', DB::raw("CONCAT(firstname, ' ', lastname) as full_name"))
                    ->pluck('full_name', 'employee_id')
                    ->toArray();

                // Filter signatories to only include items with officers
                $filteredSignatories = [];
                
                foreach ($signatoriesData as $category) {
                    $filteredAreas = [];
                    
                    foreach ($category['areas'] as $area) {
                        $filteredItems = [];
                        
                        foreach ($area['items'] as $item) {
                            // Keep item only if it has a primary_officer OR secondary_officer
                            if (!is_null($item['primary_officer']) || !is_null($item['secondary_officer'])) {
                                // Add officer names from the lookup map
                                $item['primary_officer_name'] = !is_null($item['primary_officer']) 
                                    ? ($employees[$item['primary_officer']] ?? null)
                                    : null;
                                
                                $item['secondary_officer_name'] = !is_null($item['secondary_officer']) 
                                    ? ($employees[$item['secondary_officer']] ?? null)
                                    : null;
                                
                                // Add new status fields
                                $item['status'] = 'pending';
                                $item['date_signed'] = null;
                                $item['time_signed'] = null;
                                $item['action_slip'] = null;
                                
                                $filteredItems[] = $item;
                            }
                        }
                        
                        // Only add area if it has items after filtering
                        if (!empty($filteredItems)) {
                            $filteredAreas[] = [
                                'name' => $area['name'],
                                'items' => $filteredItems
                            ];
                        }
                    }
                    
                    // Only add category if it has areas after filtering
                    if (!empty($filteredAreas)) {
                        $filteredSignatories[] = [
                            'name' => $category['name'],
                            'areas' => $filteredAreas
                        ];
                    }
                }
            } else {
                $filteredSignatories = $clearance->signatories;
            }

            // Encode back to JSON
            $filteredSignatoriesJson = json_encode($filteredSignatories);

            // Handle attachment if provided
            $attachmentName = $clearance->attachment;
            if ($request->hasFile('attachment')) {
                // Delete old attachment if exists
                if ($clearance->attachment && Storage::exists('public/clearances/' . $clearance->attachment)) {
                    Storage::delete('public/clearances/' . $clearance->attachment);
                }
                $attachmentName = $this->handleAttachment($request);
            }

            $clearance->update([
                'employee_id' => $request->employee_id,
                'clearance_template_id' => $request->clearance_template_id,
                'reason_id' => $request->reason_id,
                'shared_services' => $request->shared_services,
                'date_accepted' => $request->date_accepted,
                'to_company' => $request->to_company,
                'effectivity_date' => $request->effectivity_date,
                'from_date' => $request->from_date,
                'to_date' => $request->to_date,
                'signatories' => $filteredSignatoriesJson,
                'note' => $request->note,
                'attachment' => $attachmentName,
            ]);
            
            DB::commit();
            
            return response()->json([
                'status' => true,
                'message' => 'Updated successfully!',
                'data' => $clearance
            ], Response::HTTP_OK);

        } catch(Throwable $e) {
            DB::rollBack();
            return response()->json([
                'status' => false,
                'message' => 'Unable to process request. Please try again.',
                'data' => $e->getMessage()
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }

    public function destroy(int $id)
    {
        $data = Clearance::find($id);

        if(!$data)
        {
            return response()->json(['message' => "Record not found!"],204);
        }

        DB::connection()->beginTransaction();
        $data->delete();
        DB::connection()->commit();
        return response()->json(['message' => "Record successfully deleted!"],201);
    }

    public function removeAttachment(Request $request)
    {
        $id = $request->id;
        $attachment = $request->attachment;

        $data = Clearance::where('id', $id)->first();

        $data->attachment = NULL;

        $data->save();

        DB::connection()->commit();

        $attachment_file_path = 'employee-clearance/' . $request->attachment;
        if(Storage::exists($attachment_file_path))
        {
            Storage::delete($attachment_file_path);
        }

        return response()->json([
            'status'  => true,
            'message' => 'Saved successfully!',
            'data'    => $data
        ],201);
    }

    public function updateSignatories(Request $request, $id)
    {
        // Validate the incoming request data
        $validator = Validator::make($request->all(), [
            'signatories' => 'required|array',
        ]);
        
        /** If validation fails return with error message */
        if ($validator->fails()) {
            $errors = $validator->errors();
            $first = $errors->getMessages();
            return response()->json([
                'status'  => false,
                'message' => (reset($first))[0],
                'errors'  => $errors
            ], Response::HTTP_UNPROCESSABLE_ENTITY);
        }

        DB::connection()->beginTransaction();
        try {
            $template = Clearance::where('id', $id)->first();
            
            $sections = $request->signatories;

            // Collect all unique officer IDs for batch fetching
            $officerIds = [];
            foreach ($sections as $section) {
                foreach ($section['areas'] as $area) {
                    foreach ($area['items'] as $item) {
                        if (!empty($item['primary_officer'])) {
                            $officerIds[] = $item['primary_officer'];
                        }
                        if (!empty($item['secondary_officer'])) {
                            $officerIds[] = $item['secondary_officer'];
                        }
                    }
                }
            }

            // Fetch all employees in a single query and create a lookup map
            $employees = [];
            if (!empty($officerIds)) {
                $employees = DB::table('employees')
                    ->whereIn('employee_id', array_unique($officerIds))
                    ->select('employee_id', DB::raw("CONCAT(firstname, ' ', lastname) as full_name"))
                    ->pluck('full_name', 'employee_id')
                    ->toArray();
            }

            // Process ALL signatories (including empty ones)
            $processedSignatories = [];
            
            foreach ($sections as $section) {
                $processedAreas = [];
                
                foreach ($section['areas'] as $area) {
                    $processedItems = [];
                    
                    foreach ($area['items'] as $item) {
                        // Add officer names from the lookup map (if officers are assigned)
                        $item['primary_officer_name'] = !empty($item['primary_officer']) 
                            ? ($employees[$item['primary_officer']] ?? null)
                            : null;
                        
                        $item['secondary_officer_name'] = !empty($item['secondary_officer']) 
                            ? ($employees[$item['secondary_officer']] ?? null)
                            : null;
                        
                        // Add tracking fields (only set status to pending if there's an officer)
                        $item['status'] = !empty($item['primary_officer']) ? 'pending' : null;
                        $item['date_signed'] = null;
                        $item['time_signed'] = null;
                        $item['action_slip'] = null;
                        
                        // Remove the 'expanded' field if it exists (frontend-only field)
                        unset($item['expanded']);
                        
                        // KEEP ALL ITEMS (even without officers)
                        $processedItems[] = $item;
                    }
                    
                    // KEEP ALL AREAS (even if they have no assigned officers)
                    $processedAreas[] = [
                        'name' => $area['name'],
                        'expanded' => false,
                        'items' => $processedItems
                    ];
                }
                
                // KEEP ALL SECTIONS
                $processedSignatories[] = [
                    'name' => $section['name'],
                    'areas' => $processedAreas
                ];
            }

            // Encode to JSON
            $processedSignatoriesJson = json_encode($processedSignatories);

            // Update only the signatories column
            $template->update([
                'signatories' => $processedSignatoriesJson,
            ]);
            
            DB::connection()->commit();
            
            return response()->json([
                'message' => 'Signatories updated successfully!',
                'status' => 'success',
            ], 200);

        } catch (Throwable $e) {
            DB::connection()->rollback();
            return response()->json([
                'status'  => false,
                'message' => 'Unable to process request. Please try again.',
                'data'    => $e->getMessage()
            ]);
        }
    }

    public function unapproveSignatories(Request $request, $id)
    {
        try {
            $user = Auth::user();
            $currentEmployeeId = $user->employees->employee_id;
            
            $validator = Validator::make($request->all(), [
                'section_index' => 'required|integer',
                'area_index' => 'required|integer',
                'item_index' => 'required|integer',
                'item_code' => 'required|string',
            ]);
            
            if ($validator->fails()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Validation failed',
                    'errors' => $validator->errors()
                ], 422);
            }
            
            $clearance = Clearance::findOrFail($id);
            $signatories = json_decode($clearance->signatories, true);
            
            if (!$signatories) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid signatories data'
                ], 400);
            }
            
            $sectionIndex = $request->section_index;
            $areaIndex = $request->area_index;
            $itemIndex = $request->item_index;
            
            if (!isset($signatories[$sectionIndex]['areas'][$areaIndex]['items'][$itemIndex])) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid signatory item'
                ], 404);
            }
            
            $item = &$signatories[$sectionIndex]['areas'][$areaIndex]['items'][$itemIndex];
            
            if ($item['primary_officer'] != $currentEmployeeId && $item['secondary_officer'] != $currentEmployeeId) {
                return response()->json([
                    'success' => false,
                    'message' => 'You are not authorized to unapprove this item'
                ], 403);
            }
            
            if ($item['status'] !== 'cleared') {
                return response()->json([
                    'success' => false,
                    'message' => 'This item has not been approved yet'
                ], 400);
            }
            
            // Reset to pending and clear signatures
            $item['status'] = 'pending';
            $item['date_signed'] = null;
            $item['time_signed'] = null;
            
            $clearance->signatories = json_encode($signatories);
            $clearance->save();
            
            return response()->json([
                'success' => true,
                'message' => 'Signatory unapproved successfully',
                'data' => [
                    'item' => $item,
                    'clearance_id' => $clearance->id
                ]
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred while unapproving',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function returnToStatus(Request $request, $id)
    {
        try {
            $request->validate([
                'status' => 'required|integer',
                'reason' => 'required|string|min:10'
            ]);

            DB::beginTransaction();

            $clearance = Clearance::findOrFail($id);
            $oldStatus = $clearance->status;
            $newStatus = $request->input('status');
            $reason = $request->input('reason');

            // Update clearance status
            $clearance->status = $newStatus;
            $clearance->save();

            // Log the status change
            ClearanceStatusLog::create([
                'clearance_id' => $clearance->id,
                'old_status' => $oldStatus,
                'new_status' => $newStatus,
                'action' => 'returned',
                'reason' => $reason,
                'changed_by' => Auth::id(),
                'changed_at' => now()
            ]);

            // Optional: Send notification to employee
            // $this->sendStatusChangeNotification($clearance, $oldStatus, $newStatus, $reason);

            DB::commit();

            return response()->json([
                'message' => 'Clearance status updated successfully',
                'clearance' => $clearance
            ], 200);

        } catch (Exception $e) {
            DB::rollBack();
            
            return response()->json([
                'message' => 'Failed to update clearance status',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function approveSignatories(Request $request, $id)
    {
        try {
            $user = Auth::user();
            $currentEmployeeId = $user->employees->employee_id;

            $validator = Validator::make($request->all(), [
                'section_index' => 'required|integer',
                'area_index' => 'required|integer',
                'item_index' => 'required|integer',
                'item_code' => 'required|string',
            ]);

            if ($validator->fails()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Validation failed',
                    'errors' => $validator->errors()
                ], 422);
            }

            $clearance = Clearance::findOrFail($id);

            $signatories = json_decode($clearance->signatories, true);

            if (!$signatories) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid signatories data'
                ], 400);
            }

            $sectionIndex = $request->section_index;
            $areaIndex = $request->area_index;
            $itemIndex = $request->item_index;

            if (!isset($signatories[$sectionIndex]['areas'][$areaIndex]['items'][$itemIndex])) {
                return response()->json([
                    'success' => false,
                    'message' => 'Invalid signatory item'
                ], 404);
            }

            $item = &$signatories[$sectionIndex]['areas'][$areaIndex]['items'][$itemIndex];

            if ($item['primary_officer'] != $currentEmployeeId && $item['secondary_officer'] != $currentEmployeeId) {
                return response()->json([
                    'success' => false,
                    'message' => 'You are not authorized to approve this item'
                ], 403);
            }

            if ($item['status'] === 'cleared') {
                return response()->json([
                    'success' => false,
                    'message' => 'This item has already been approved'
                ], 400);
            }

            $currentDateTime = now();
            $item['status'] = 'cleared';
            $item['date_signed'] = $currentDateTime->format('Y-m-d');
            $item['time_signed'] = $currentDateTime->format('H:i:s');

            $clearance->signatories = json_encode($signatories);
            $clearance->save();

            return response()->json([
                'success' => true,
                'message' => 'Signatory approved successfully',
                'data' => [
                    'item' => $item,
                    'clearance_id' => $clearance->id
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred while approving',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function saveComment(Request $request, $id)
    {
        // dd($request->all());
        $request->validate([
            'comment' => 'required|string',
        ]);

        try {
            $clearance = Clearance::findOrFail($id);
            $clearance->signatories_comments()->create([
                'employee_id' => $request->input('employee_id'),
                'comment' => $request->input('comment'),
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Comment saved successfully',
            ], 201);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred while saving comment',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function getComment($id)
    {
        try {
            $comments = SignatoriesComment::with('employee:employee_id,firstname,lastname')
                ->where('clearance_id', $id)
                ->orderBy('created_at', 'desc')
                ->get()
                ->map(function($comment) {
                    return [
                        'id' => $comment->id,
                        'employee_id' => $comment->employee_id,
                        'employee_name' => $comment->employee 
                            ? $comment->employee->firstname . ' ' . $comment->employee->lastname 
                            : null,
                        'comment' => $comment->comment,
                        'created_at' => $comment->created_at,
                    ];
                });

            return response()->json([
                'success' => true,
                'data' => $comments
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred while fetching comments',
                'error' => $e->getMessage()
            ], 500);
        }
    }
    
    public function getClearanceForApproval(Request $request)
    {
        $user = Auth::user();
        $employeeId = $user->employees->employee_id;

        $keyword = $request->input('keyword', '');
        $perPage = $request->input('per_page', PHP_INT_MAX);
        $sortBy = $request->input('sortBy', '');
        $sortType = $request->input('sortType', '');

        $query = Clearance::with('template', 'employee:employee_id,firstname,lastname', 'final_computation_history', 'final_computation_history.employee:employee_id,firstname,lastname');

        if (!empty($sortBy) && !empty($sortType)) {
            $query = $query->orderBy($sortBy, $sortType);
        }

        if($request->process_name == 'endorsement'){
            $query->where('status', '=', 3)
                ->where(function($q) use ($request) {
                    // Officer 1: sees records where recommending_officer_1_data IS NULL
                    $q->whereRelation('template', 'recommending_officer_1', $request->approver_id)
                        ->whereNull('recommending_officer_1_date');
                    
                    // Officer 2: sees records where recommending_officer_1_data IS NOT NULL
                    $q->orWhere(function($subQ) use ($request) {
                        $subQ->whereRelation('template', 'recommending_officer_2', $request->approver_id)
                            ->whereNotNull('recommending_officer_1_date');
                    });
                });
        } else if($request->process_name == 'final-approval'){
            $query->where('status', '=', 4)
                ->where(function($q) use ($request) {
                    // Officer 1: sees records where approving_officer_1_data IS NULL
                    $q->whereRelation('template', 'approving_officer_1', $request->approver_id)
                        ->whereNull('approving_officer_1_date');
                    
                    // Officer 2: sees records where approving_officer_1_data IS NOT NULL
                    $q->orWhere(function($subQ) use ($request) {
                        $subQ->whereRelation('template', 'approving_officer_2', $request->approver_id)
                            ->whereNotNull('approving_officer_1_date');
                    });
                });
        } else if($request->process_name == 'for-routing-approval'){
            $query->whereRelation('template', 'authorizing_hr', $request->approver_id);
            $query->where('status', '=', 0);
        } else if($request->process_name == 'clearance-lists'){
            $query->whereRelation('template', 'authorizing_hr', $request->approver_id);
        }

        $allRecords = $query->get();

        // Manual pagination
        $currentPage = request()->get('page', 1);
        $total = $allRecords->count();
        $perPage = $perPage === PHP_INT_MAX ? $total : $perPage;
        $paginatedData = $allRecords->slice(($currentPage - 1) * $perPage, $perPage)->values();

        $data = new \Illuminate\Pagination\LengthAwarePaginator(
            $paginatedData,
            $total,
            $perPage,
            $currentPage,
            ['path' => request()->url(), 'query' => request()->query()]
        );

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

    public function getApprovalProcessCounts(Request $request)
    {
        $user = Auth::user();
        $employeeId = $user->employees->employee_id;
        
        $counts = [];
        
        // Count for pending clearance (created by user OR in signatories)
        $counts['pending-clearance'] = Clearance::where(function ($q) use ($employeeId) {
            $q->where('created_by', $employeeId)
            ->orWhereRaw("JSON_SEARCH(signatories, 'one', ?, NULL, '$[*].areas[*].items[*].primary_officer') IS NOT NULL", [$employeeId])
            ->orWhereRaw("JSON_SEARCH(signatories, 'one', ?, NULL, '$[*].areas[*].items[*].secondary_officer') IS NOT NULL", [$employeeId]);
        })->count();
        
        // Count for template-based roles with status
        $statusMap = [
            'authorizing_hr' => ['field' => 'authorizing_hr', 'statuses' => [0], 'useSignatories' => true],
            'employee-approval' => ['field' => 'authorizing_hr', 'statuses' => [2], 'useSignatories' => true],
            'payroll_officer' => ['field' => 'payroll_officer', 'statuses' => [5], 'useSigantories' => false],
            'treasury_officer' => ['field' => 'treasury_officer', 'statuses' => [6], 'useSignatories' => false],
            'legal_officer_schedule' => ['field' => 'legal_officer', 'statuses' => [8], 'useSignatories' => false],
            'legal_officer_ready' => ['field' => 'legal_officer', 'statuses' => [9], 'useSignatories' => false]
        ];
        
        foreach ($statusMap as $key => $config) {
            DB::enableQueryLog();
            $query = Clearance::with('template')
                ->whereIn('status', $config['statuses'])
                ->whereRelation('template', $config['field'], $employeeId);
            
            $counts[$key] = $query->count();

            // dd(DB::getQueryLog());
        }
        
        // Count for last computation (payroll_officer in template, status 1, and all items in signatories are cleared)
        $counts['last-computation'] = Clearance::where('status', 1)
            ->whereRelation('template', 'payroll_officer', $employeeId)
            ->whereRaw("JSON_SEARCH(signatories, 'all', 'pending', NULL, '$.areas[*].items[*].status') IS NULL")
            ->count();
        
        // Count for signatories (endorsement, final-approval, routing-approval)
        $signatoryCount = Clearance::where(function ($q) use ($employeeId) {
            $q->whereRaw("JSON_SEARCH(signatories, 'one', ?, NULL, '$[*].areas[*].items[*].primary_officer') IS NOT NULL", [$employeeId])
            ->orWhereRaw("JSON_SEARCH(signatories, 'one', ?, NULL, '$[*].areas[*].items[*].secondary_officer') IS NOT NULL", [$employeeId]);
        })->count();

        $endorsementCount = Clearance::where(function ($q) use ($employeeId) {
            $q->whereRelation('template', 'recommending_officer_1', $employeeId)
            ->whereRaw("JSON_SEARCH(signatories, 'all', 'pending', NULL, '$.areas[*].items[*].status') IS NULL");
        })->where('status', 3)->count();

        $finapApprovalCount = Clearance::where(function ($q) use ($employeeId) {
            $q->whereRelation('template', 'approving_officer_1', $employeeId)
            ->whereRaw("JSON_SEARCH(signatories, 'all', 'pending', NULL, '$.areas[*].items[*].status') IS NULL");
        })->where('status', 4)->count();
        
        $counts['obligation'] = Clearance::where('status', 99)->count();
        $counts['completed'] = Clearance::where('status', 10)->count();

        $counts['endorsement'] = $endorsementCount;
        $counts['final-approval'] = $finapApprovalCount;
        $counts['routing-approval'] = $signatoryCount;
        
        return response()->json(['data' => $counts]);
    }

    public function getCompleted(Request $request)
    {
        DB::enableQueryLog();

        $user = Auth::user();
        $employeeId = $user->employees->employee_id;

        $keyword = $request->input('keyword', '');
        $perPage = $request->input('per_page', PHP_INT_MAX);
        $sortBy = $request->input('sortBy', '');
        $sortType = $request->input('sortType', '');

        $query = Clearance::with('template', 'employee:employee_id,firstname,lastname', 'final_computation_history', 'final_computation_history.employee:employee_id,firstname,lastname')
            ->where(function ($query) use ($keyword) {
                $keyword = str_replace(" ", "%", $keyword);
                $query->where('date_accepted', 'like', '%' . $keyword . '%');
            })
            ->where(function ($query) use ($employeeId, $request) {
                // Show clearances created by the user
                $query->where('created_by', $employeeId);

                if(in_array($request->process_name, ['routing-approval', 'endorsement', 'final-approval'])){
                    $query->orWhereRaw("JSON_SEARCH(signatories, 'one', ?, NULL, '$[*].areas[*].items[*].primary_officer') IS NOT NULL", [$employeeId])
                    ->orWhereRaw("JSON_SEARCH(signatories, 'one', ?, NULL, '$[*].areas[*].items[*].secondary_officer') IS NOT NULL", [$employeeId]);
                }
            });
        
        // dd($request->all());
        if($request->process_name == 'last-computation'){
            $query->whereRelation('template', 'payroll_officer', $request->approver_id);
            $query->where('status', '=', 1);
        } else if($request->process_name == 'employee-approval') {
            $query->whereRelation('template', 'authorizing_hr', $request->approver_id);
            $query->where('status', '=', 2);
        } else if($request->process_name == 'check-request') {
            $query->whereRelation('template', 'payroll_officer', $request->approver_id);
            $query->where('status', '=', 5);
        } else if($request->process_name == 'check-releasing') {
            $query->whereRelation('template', 'treasury_officer', $request->approver_id);
            $query->where('status', '=', 6);
        } else if($request->process_name == 'documentation') {
            $query->whereRelation('template', 'authorizing_hr', $request->approver_id);
            $query->where('status', '=', 7);
        } else if($request->process_name == 'schedule-release') {
            $query->whereRelation('template', 'legal_officer', $request->approver_id);
            $query->where('status', '=',8);
        } else if($request->process_name == 'ready-release') {
            $query->whereRelation('template', 'legal_officer', $request->approver_id);
            $query->where('status', '=',9);
        } else if($request->process_name == 'completed') {
            $query->where('status', '=',10);
        } else if($request->process_name == 'obligation') {
            $query->where('status', '=',99);
        }

        if (!empty($sortBy) && !empty($sortType)) {
            $query = $query->orderBy($sortBy, $sortType);
        }

        // Get all records and filter completed ones
        // if($request->input('status') > 0) {
        //     $query->where('status', $request->input('status'));
        // } else {
        //     $query->where('status', '=', 0);
        // }

        $allRecords = $query->get();

        // dd($allRecords);

        // dd(DB::getQueryLog());
        $completedRecords = $allRecords->filter(function ($clearance) {
            $signatories = json_decode($clearance->signatories, true);
            
            if (empty($signatories)) {
                return false;
            }
            
            $hasAssignedItems = false;
            
            foreach ($signatories as $section) {
                if (empty($section['areas'])) continue;
                
                foreach ($section['areas'] as $area) {
                    if (empty($area['items'])) continue;
                    
                    foreach ($area['items'] as $item) {
                        // Only check items that have an assigned officer
                        $hasPrimaryOfficer = isset($item['primary_officer']) && !empty($item['primary_officer']);
                        $hasSecondaryOfficer = isset($item['secondary_officer']) && !empty($item['secondary_officer']);
                        
                        if ($hasPrimaryOfficer || $hasSecondaryOfficer) {
                            $hasAssignedItems = true;
                            
                            // If any assigned item is not cleared, this clearance is not completed
                            if (!isset($item['status']) || strtolower($item['status']) !== 'cleared') {
                                return false;
                            }
                        }
                    }
                }
            }
            
            // Only return true if there are assigned items AND all are cleared
            return $hasAssignedItems;
        });

        // Manual pagination
        $currentPage = request()->get('page', 1);
        $total = $completedRecords->count();
        $perPage = $perPage === PHP_INT_MAX ? $total : $perPage;
        $paginatedData = $completedRecords->slice(($currentPage - 1) * $perPage, $perPage)->values();

        $data = new \Illuminate\Pagination\LengthAwarePaginator(
            $paginatedData,
            $total,
            $perPage,
            $currentPage,
            ['path' => request()->url(), 'query' => request()->query()]
        );

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

    public function submitComputation(Request $request, $id)
    {
        try {
            $validator = Validator::make($request->all(), [
                'employee_id' => 'required|string',
                'attachments' => 'required|array|min:1',
                'attachments.*' => 'required|file|max:10240|mimes:jpg,jpeg,png,pdf,doc,docx,xls,xlsx',
            ], [
                'attachments.required' => 'Please upload at least one file',
                'attachments.min' => 'Please upload at least one file',
                'attachments.*.required' => 'Each attachment must be a valid file',
                'attachments.*.max' => 'Each file must not exceed 10MB',
                'attachments.*.mimes' => 'Only images, PDF, Word, and Excel files are allowed',
            ]);

            if ($validator->fails()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Validation failed',
                    'errors' => $validator->errors()
                ], 422);
            }

            DB::beginTransaction();

            $clearance = Clearance::findOrFail($id);

            // Get existing attachments from clearance table
            $existingAttachments = [];
            if ($clearance->last_pay_computation) {
                try {
                    $decoded = json_decode($clearance->last_pay_computation, true);
                    if (is_array($decoded)) {
                        $existingAttachments = $decoded;
                    }
                } catch (\Exception $e) {
                    \Log::error('Error decoding existing attachments: ' . $e->getMessage());
                }
            }

            $newAttachments = [];
            $uploadedFiles = [];

            // Handle file uploads
            if ($request->hasFile('attachments')) {
                foreach ($request->file('attachments') as $index => $file) {
                    try {
                        $originalName = $file->getClientOriginalName();
                        $extension = $file->getClientOriginalExtension();
                        $filename = time() . '_' . uniqid() . '_' . str_replace(' ', '_', $originalName);
                        
                        // Store file
                        $path = $file->storeAs('clearance_attachments', $filename, 'public');
                        
                        if (!$path) {
                            throw new \Exception("Failed to store file: {$originalName}");
                        }

                        $attachmentData = [
                            'original_name' => $originalName,
                            'filename' => $filename,
                            'path' => $path,
                            'url' => Storage::url($path),
                            'size' => $file->getSize(),
                            'mime_type' => $file->getMimeType(),
                            'extension' => $extension,
                            'uploaded_by' => $request->employee_id,
                            'uploaded_at' => now()->toDateTimeString(),
                        ];
                        
                        $newAttachments[] = $attachmentData;
                        $existingAttachments[] = $attachmentData;
                        $uploadedFiles[] = $path; // Track for rollback if needed
                        
                    } catch (\Exception $e) {
                        \Log::error('Error uploading file: ' . $e->getMessage());
                        throw new \Exception("Failed to upload {$originalName}: " . $e->getMessage());
                    }
                }
            }

            if (empty($newAttachments)) {
                throw new \Exception('No files were successfully uploaded');
            }

            // dd($existingAttachments);
            $clearance->last_pay_computation = json_encode($existingAttachments);
            $clearance->save();

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => count($newAttachments) . ' file(s) uploaded successfully',
                'data' => [
                    'uploaded_count' => count($newAttachments),
                    'total_attachments' => count($existingAttachments),
                    'new_attachments' => $newAttachments,
                ]
            ], 200);

        } catch (\Illuminate\Database\Eloquent\ModelNotFoundException $e) {
            DB::rollBack();
            
            return response()->json([
                'success' => false,
                'message' => 'Clearance record not found',
            ], 404);
            
        } catch (\Exception $e) {
            DB::rollBack();
            
            // Clean up uploaded files if transaction fails
            if (isset($uploadedFiles)) {
                foreach ($uploadedFiles as $filePath) {
                    if (Storage::disk('public')->exists($filePath)) {
                        Storage::disk('public')->delete($filePath);
                    }
                }
            }
            
            \Log::error('Error submitting computation: ' . $e->getMessage());
            \Log::error('Stack trace: ' . $e->getTraceAsString());
            
            return response()->json([
                'success' => false,
                'message' => 'An error occurred while uploading files',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function deleteAttachment(Request $request, $id)
    {
        try {
            // Validate request
            $validator = Validator::make($request->all(), [
                'filename' => 'required|string',
            ]);

            if ($validator->fails()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Validation failed',
                    'errors' => $validator->errors()
                ], 422);
            }

            DB::beginTransaction();

            // Find clearance record
            $clearance = Clearance::findOrFail($id);
            
            // Get existing attachments
            $attachments = [];
            if ($clearance->last_pay_computation) {
                try {
                    $decoded = json_decode($clearance->last_pay_computation, true);
                    if (is_array($decoded)) {
                        $attachments = $decoded;
                    }
                } catch (\Exception $e) {
                    throw new \Exception('Error reading attachments data: ' . $e->getMessage());
                }
            }

            // Check if attachments array is empty
            if (empty($attachments)) {
                DB::rollBack();
                return response()->json([
                    'success' => false,
                    'message' => 'No attachments found',
                ], 404);
            }

            $fileDeleted = false;
            $deletedFilePath = null;
            $deletedFileName = null;

            // Find and remove the attachment
            $filteredAttachments = array_filter($attachments, function($attachment) use ($request, &$fileDeleted, &$deletedFilePath, &$deletedFileName) {
                if (isset($attachment['filename']) && $attachment['filename'] === $request->filename) {
                    // Store file info for logging
                    $deletedFilePath = $attachment['path'] ?? null;
                    $deletedFileName = $attachment['original_name'] ?? $attachment['filename'];
                    
                    // Delete the file from storage
                    if (isset($attachment['path']) && Storage::disk('public')->exists($attachment['path'])) {
                        try {
                            Storage::disk('public')->delete($attachment['path']);
                            \Log::info("File deleted from storage: {$attachment['path']}");
                        } catch (\Exception $e) {
                            \Log::error("Failed to delete file from storage: {$attachment['path']}, Error: " . $e->getMessage());
                            // Continue anyway to remove from database
                        }
                    } else {
                        \Log::warning("File not found in storage: " . ($attachment['path'] ?? 'unknown'));
                    }
                    
                    $fileDeleted = true;
                    return false; // Remove from array
                }
                return true; // Keep in array
            });

            // Check if file was found and deleted
            if (!$fileDeleted) {
                DB::rollBack();
                return response()->json([
                    'success' => false,
                    'message' => 'Attachment not found',
                ], 404);
            }

            // Re-index array to maintain proper array structure
            $filteredAttachments = array_values($filteredAttachments);

            // Update clearance with filtered attachments
            $clearance->last_pay_computation = !empty($filteredAttachments) 
                ? json_encode($filteredAttachments) 
                : null;
            
            $clearance->save();

            DB::commit();

            // Log the deletion
            \Log::info("Attachment deleted successfully", [
                'clearance_id' => $id,
                'filename' => $request->filename,
                'original_name' => $deletedFileName,
                'path' => $deletedFilePath,
                'remaining_attachments' => count($filteredAttachments),
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Attachment deleted successfully',
                'data' => $filteredAttachments,
                'remaining_count' => count($filteredAttachments),
            ], 200);

        } catch (\Illuminate\Database\Eloquent\ModelNotFoundException $e) {
            DB::rollBack();
            
            \Log::error("Clearance not found for deletion", [
                'clearance_id' => $id,
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Clearance record not found',
            ], 404);
            
        } catch (\Exception $e) {
            DB::rollBack();
            
            \Log::error("Error deleting attachment", [
                'clearance_id' => $id,
                'filename' => $request->filename ?? 'unknown',
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'An error occurred while deleting attachment',
                'error' => config('app.debug') ? $e->getMessage() : 'Internal server error',
            ], 500);
        }
    } 

    public function processClearance(Request $request, $id)
    {
        $user = Auth::user();

        try {
            $clearance = Clearance::with('template')->findOrFail($id);
            $clearance->final_computation_history()->create([
                'employee_id' => $request->input('employee_id'),
                'submitted_by' => $user->employee_id,
                'status_to' => $request->status,
                'process_name' => $request->process_name,
            ]);

            // dd($request->status == 4 && $clearance->template->recommending_officer_2 != null && $clearance->recommending_officer_1_date == null);
            $status = $request->status;

            if (
                $request->status == 5 &&
                $clearance->template->recommending_officer_2 != null
            ) {
                if ($clearance->recommending_officer_1_date == null) {
                    $status = 3;
                } elseif ($clearance->approving_officer_1_date == null) {
                    $status = 4;
                }
            }

            if($request->status == 4 && $clearance->template->recommending_officer_2 != null && $clearance->recommending_officer_1_date == null){
                $status = 3;
            }

            if($request->status == 5 && $clearance->template->approving_officer_2 != null && $clearance->approving_officer_1_date == null){
                $status = 4;
            }

            $clearance->update([
                'status' => $status,
                'recommending_officer_1_date' => $request->status == 4 && $clearance->template->recommending_officer_2 != null && $clearance->recommending_officer_1_date == null ? now() : $clearance->recommending_officer_1_date,
                'approving_officer_1_date' => $request->status == 5 && $clearance->template->approving_officer_2 != null && $clearance->approving_officer_1_date == null ? now() : $clearance->approving_officer_1_date,
                'check_prepared' => $request->status == 6 ? now() : $clearance->check_prepared,
                'release_date' => $request->release_date ? date('Y-m-d H:i:s', strtotime($request->release_date)) : $clearance->release_date,
            ]);

            $clearance->save();

            return response()->json([
                'success' => true,
                'message' => 'Saved successfully',
            ], 201);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred while saving',
                'error' => $e->getMessage()
            ], 500);
        }
    }
}
