<?php

namespace App\Http\Controllers\Api\V1;

use App\Models\RecruitmentVacancyPlantilla;
use App\Models\RecruitmentActualSchedules;
use App\Models\RecruitmentApplicantAttachment;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;
class RecruitmentVacancyPlantillaController extends BaseController
{
    protected $model = RecruitmentVacancyPlantilla::class;

    protected $validationRules = [
        'job_title_code' => 'required|string|max:45',
        'job_title_item_number_code' => 'required|string|max:45',
        'section_code' => 'required|string|max:45',
        'actual_assignment' => 'required|string|max:250',
        'vacancy_status' => 'required|string|max:250',
        'application_status_code' => 'required|string|max:45',
        'open_for_vacancy' => 'required|string|in:YES,NO'
    ];

    public function __construct()
    {
        $this->model = new RecruitmentVacancyPlantilla();
    }

    /**
     * Display a listing of plantilla vacancies.
     */
public function index(Request $request)
{
    try {
        Log::info('Fetching plantilla vacancies', ['request' => $request->all()]);

        $query = RecruitmentVacancyPlantilla::query();

        // PURE ADVANCED SEARCH IMPLEMENTATION
        $advancedFilters = [];

        // Handle search parameter as advanced filter
        if ($request->has('search') && !empty($request->search)) {
            $advancedFilters[] = [
                'column' => 'job_title_code',
                'operator' => 'contains',
                'value' => $request->search,
                'boolean' => 'or'
            ];
            $advancedFilters[] = [
                'column' => 'job_title_item_number_code',
                'operator' => 'contains',
                'value' => $request->search,
                'boolean' => 'or'
            ];
            $advancedFilters[] = [
                'column' => 'section_code',
                'operator' => 'contains',
                'value' => $request->search,
                'boolean' => 'or'
            ];
            $advancedFilters[] = [
                'column' => 'actual_assignment',
                'operator' => 'contains',
                'value' => $request->search,
                'boolean' => 'or'
            ];
        }

        // Handle vacancy status filter as advanced filter
        if ($request->has('vacancy_status') && !empty($request->vacancy_status)) {
            $advancedFilters[] = [
                'column' => 'vacancy_status',
                'operator' => 'equals',
                'value' => $request->vacancy_status
            ];
        }

        // Handle application status filter as advanced filter
        if ($request->has('application_status_code') && !empty($request->application_status_code)) {
            $advancedFilters[] = [
                'column' => 'application_status_code',
                'operator' => 'equals',
                'value' => $request->application_status_code
            ];
        }

        // Handle section filter as advanced filter
        if ($request->has('section_code') && !empty($request->section_code)) {
            $advancedFilters[] = [
                'column' => 'section_code',
                'operator' => 'equals',
                'value' => $request->section_code
            ];
        }

        // Handle open for vacancy filter as advanced filter
        if ($request->has('open_for_vacancy') && !empty($request->open_for_vacancy)) {
            $advancedFilters[] = [
                'column' => 'open_for_vacancy',
                'operator' => 'equals',
                'value' => $request->open_for_vacancy
            ];
        }

        // Handle publication status filter
        if ($request->has('is_published')) {
            if ($request->boolean('is_published')) {
                $advancedFilters[] = [
                    'column' => 'vacant_position_publication_id',
                    'operator' => 'not_null',
                    'value' => true
                ];
            } else {
                $advancedFilters[] = [
                    'column' => 'vacant_position_publication_id',
                    'operator' => 'null',
                    'value' => true
                ];
            }
        }

        // Handle date range filters if needed
        if ($request->has('date_vacancy_from') && !empty($request->date_vacancy_from)) {
            $advancedFilters[] = [
                'column' => 'date_vacancy',
                'operator' => 'greater_than_equal',
                'value' => $request->date_vacancy_from
            ];
        }

        if ($request->has('date_vacancy_to') && !empty($request->date_vacancy_to)) {
            $advancedFilters[] = [
                'column' => 'date_vacancy',
                'operator' => 'less_than_equal',
                'value' => $request->date_vacancy_to
            ];
        }

        // Apply external advanced filters if provided
        if ($request->has('advanced_filters')) {
            $externalFilters = json_decode($request->advanced_filters, true);
            if (is_array($externalFilters) && !empty($externalFilters)) {
                $advancedFilters = array_merge($advancedFilters, $externalFilters);
            }
        }

        // Apply all advanced filters using the trait
        if (!empty($advancedFilters)) {
            $this->applyAdvancedFilters($query, $advancedFilters);
        }

        // Ordering
        $sortBy = $request->get('sort_by', 'created');
        $sortOrder = $request->get('sort_order', 'desc');
        $query->orderBy($sortBy, $sortOrder)
              ->orderBy('date_vacancy', 'desc');

        // Pagination
        $perPage = $request->get('per_page', 15);
        $data = $query
            ->select('id', 'job_title_code', 'job_title_item_number_code', 'role_type_code', 'actual_assignment', 'years_of_experience', 'hours_of_relevant_training')
            ->with([
                'publication' => function ($query) {
                    $query->select('id', 'date_of_publication', 'deadline_of_submission'); 
                }
            ])
            ->paginate($perPage);
        
        $dataArray = $data->toArray();
        
        $vacancies = $dataArray['data'];
        unset($dataArray['data']); 
        $dataArray['vacancies'] = $vacancies;

        $this->logAudit('VIEW_LIST', null, null, [
            'advanced_filters' => $advancedFilters,
            'per_page' => $perPage,
            'sort_by' => $sortBy,
            'sort_order' => $sortOrder,
            'result_count' => $data->total()
        ]);

        return $this->sendResponse($dataArray, 'Plantilla vacancies retrieved successfully.');

    } catch (\Exception $e) {
        Log::error('Error fetching plantilla vacancies', [
            'error' => $e->getMessage(),
            'trace' => $e->getTraceAsString(),
            'request' => $request->all()
        ]);

        return $this->sendError('Internal Server Error', [], 500);
    }
}

    /**
     * Store a newly created plantilla vacancy.
     */
    public function store(Request $request)
    {
        try {
            Log::info('Creating plantilla vacancy', ['request' => $request->all()]);

            // Validate request
            $validationError = $this->validateRequest($request);
            if ($validationError) {
                return $validationError;
            }

            // Create record
            $plantillaVacancy = RecruitmentVacancyPlantilla::create([
                'job_title_code' => $request->job_title_code,
                'job_title_item_number_code' => $request->job_title_item_number_code,
                'section_code' => $request->section_code,
                'valid_from_date' => $request->valid_from_date,
                'valid_to_date' => $request->valid_to_date,
                'years_of_experience' => $request->years_of_experience,
                'hours_of_relevant_training' => $request->hours_of_relevant_training,
                'eligibility' => $request->eligibility,
                'education' => $request->education,
                'number_of_applicants' => $request->number_of_applicants ?? 0,
                'actual_assignment' => $request->actual_assignment,
                'vacancy_status' => $request->vacancy_status,
                'application_status_code' => $request->application_status_code,
                'open_for_vacancy' => $request->open_for_vacancy,
                'vice' => $request->vice,
                'assignment_based_on_psiop' => $request->assignment_based_on_psiop,
                'reason_for_vacancy' => $request->reason_for_vacancy,
                'date_vacancy' => $request->date_vacancy,
                'separation_status_code' => $request->separation_status_code,
                'show_in_kiosk' => $request->boolean('show_in_kiosk', true),
                'competencies' => $request->competencies,
                'job_description_form' => $request->job_description_form,
                'actual_assignment_code' => $request->actual_assignment_code,
                'turn_around_time' => $request->turn_around_time,
                'actual_processing_time' => $request->actual_processing_time,
                'campus_code' => $request->campus_code,
                'role_type_code' => $request->role_type_code,
                'deliberation_date' => $request->deliberation_date,
                'status' => $request->status ?? 'active',
                'created_by' => auth()->id()
            ]);

            $this->logAudit('CREATE', $plantillaVacancy, null, $plantillaVacancy->toArray());

            Log::info('Plantilla vacancy created successfully', ['id' => $plantillaVacancy->id]);

            return $this->sendResponse($plantillaVacancy, 'Plantilla vacancy created successfully.', 201);

        } catch (\Exception $e) {
            Log::error('Error creating plantilla vacancy', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
                'request' => $request->all()
            ]);

            return $this->sendError('Internal Server Error', [], 500);
        }
    }
public function storeUnderPublication(Request $request, $publication_id)
{
    DB::beginTransaction();
    
    try {
        Log::info('Creating plantilla vacancy under publication', [
            'publication_id' => $publication_id,
            'request' => $request->all()
        ]);

        // Validate request
        $validationError = $this->validateRequest($request);
        if ($validationError) {
            return $validationError;
        }

        // Create plantilla vacancy record
        $plantillaVacancy = RecruitmentVacancyPlantilla::create([
            'vacant_position_publication_id' => $publication_id,
            'job_title_code' => $request->job_title_code,
            'job_title_item_number_code' => $request->job_title_item_number_code,
            'section_code' => $request->section_code,
            'valid_from_date' => $request->valid_from_date,
            'valid_to_date' => $request->valid_to_date,
            'years_of_experience' => $request->years_of_experience,
            'hours_of_relevant_training' => $request->hours_of_relevant_training,
            'eligibility' => $request->eligibility,
            'education' => $request->education,
            'number_of_applicants' => $request->number_of_applicants ?? 0,
            'actual_assignment' => $request->actual_assignment,
            'vacancy_status' => $request->vacancy_status,
            'application_status_code' => $request->application_status_code,
            'open_for_vacancy' => $request->open_for_vacancy,
            'show_in_kiosk' => $request->boolean('show_in_kiosk', true),
            'status' => $request->status ?? 'active',
            'vice' => $request->vice,
            'assignment_based_on_psiop' => $request->assignment_based_on_psiop,
            'reason_for_vacancy' => $request->reason_for_vacancy,
            'date_vacancy' => $request->date_vacancy,
            'created_by' => auth()->id()
        ]);

        // Generate plantilla vacancy code
        $plantillaCode = 'PLANTILLA_' . $plantillaVacancy->id . '_' . time();
        $plantillaVacancy->update(['recruitment_plantilla_vacancy_code' => $plantillaCode]);

        // Handle Actual Recruitment Schedules
        if ($request->has('schedules')) {
            $this->createRecruitmentSchedules($plantillaVacancy->id, $plantillaCode, $request->schedules, $request);
        }

        // Handle Attachments/Documentary Requirements
        if ($request->has('attachments')) {
            $this->createPlantillaAttachments($plantillaCode, $request->attachments, $request);
        }

        $this->logAudit('CREATE', $plantillaVacancy, null, $plantillaVacancy->toArray());

        DB::commit();

        Log::info('Plantilla vacancy created successfully with attachments and schedules', [
            'publication_id' => $publication_id,
            'plantilla_id' => $plantillaVacancy->id,
            'plantilla_code' => $plantillaCode
        ]);

        return $this->sendResponse([
            'plantilla' => $plantillaVacancy,
            'schedules_created' => $request->has('schedules'),
            'attachments_created' => $request->has('attachments')
        ], 'Plantilla vacancy created successfully with attachments and schedules.', 201);

    } catch (\Exception $e) {
        DB::rollBack();
        
        Log::error('Error creating plantilla vacancy under publication', [
            'error' => $e->getMessage(),
            'trace' => $e->getTraceAsString(),
            'request' => $request->all()
        ]);

        return $this->sendError('Internal Server Error', [], 500);
    }
}

/**
 * Create recruitment schedules for plantilla vacancy
 */
private function createRecruitmentSchedules($plantillaId, $plantillaCode, $schedulesData, Request $request)
{
    $schedule = RecruitmentActualSchedules::create([
        'vacancy_plantilla_id' => $plantillaId,
        'job_title_code' => $request->job_title_code,
        'job_title_item_number_code' => $request->job_title_item_number_code,
        'number_of_applicants' => $request->number_of_applicants ?? 0,
        'date_vacancy' => $request->date_vacancy,
        'assignment_based_on_psiop' => $request->assignment_based_on_psiop,
        'vice' => $request->vice,
        'reason_for_vacancy' => $request->reason_for_vacancy,
        'open_for_vacancy' => $request->open_for_vacancy,
        
        // Recruitment timeline dates
        'date_posted_from' => $schedulesData['date_posted_from'] ?? null,
        'date_posted_to' => $schedulesData['date_posted_to'] ?? null,
        'deadline_of_acceptance_of_application' => $schedulesData['deadline_of_acceptance_of_application'] ?? null,
        'examination_date' => $schedulesData['examination_date'] ?? null,
        'checking_from' => $schedulesData['checking_from'] ?? null,
        'checking_to' => $schedulesData['checking_to'] ?? null,
        'interview_from' => $schedulesData['interview_from'] ?? null,
        'interview_to' => $schedulesData['interview_to'] ?? null,
        'notification_from' => $schedulesData['notification_from'] ?? null,
        'notification_to' => $schedulesData['notification_to'] ?? null,
        'preparation_of_reports_from' => $schedulesData['preparation_of_reports_from'] ?? null,
        'preparation_of_reports_to' => $schedulesData['preparation_of_reports_to'] ?? null,
        'deliberation' => $schedulesData['deliberation'] ?? null,
        'background_investigation_from' => $schedulesData['background_investigation_from'] ?? null,
        'background_investigation_to' => $schedulesData['background_investigation_to'] ?? null,
        'review_by_psb_from' => $schedulesData['review_by_psb_from'] ?? null,
        'review_by_psb_to' => $schedulesData['review_by_psb_to'] ?? null,
        'approval_of_appointment_from' => $schedulesData['approval_of_appointment_from'] ?? null,
        'approval_of_appointment_to' => $schedulesData['approval_of_appointment_to'] ?? null,
        
        'status' => 'active',
        'created_by' => auth()->id(),
        'created' => now()
    ]);

    Log::info('Recruitment schedules created for plantilla', [
        'plantilla_id' => $plantillaId,
        'schedule_id' => $schedule->id
    ]);

    return $schedule;
}

/**
 * Create required attachments/documentary requirements for plantilla
 */
private function createPlantillaAttachments($plantillaCode, $attachmentsData, Request $request)
{
    $createdAttachments = [];
    
    foreach ($attachmentsData as $attachment) {
        $attachmentRecord = RecruitmentApplicantAttachment::create([
            'recruitment_plantilla_vacancy_code' => $plantillaCode,
            'name' => $attachment['name'] ?? null,
            'attachment' => $attachment['filename'] ?? null, // Assuming file upload is handled separately
            'is_checked' => $attachment['is_required'] ?? '0',
            'status' => 'Active',
            'created_by' => auth()->id(),
            'created' => now()
        ]);
        
        $createdAttachments[] = $attachmentRecord;
    }

    Log::info('Plantilla attachments created', [
        'plantilla_code' => $plantillaCode,
        'attachments_count' => count($createdAttachments)
    ]);

    return $createdAttachments;
}


    /**
     * Display the specified plantilla vacancy.
     */
public function show($id)
{
    try {
        Log::info('Fetching plantilla vacancy', ['id' => $id]);

<<<<<<< HEAD
            $plantillaVacancy = RecruitmentVacancyPlantilla::query()
                ->select('id', 'job_title_code', 'job_title_item_number_code', 'role_type_code', 'actual_assignment', 'years_of_experience', 'hours_of_relevant_training', 'eligibility', 'education')
                ->with([
                    'publication' => function ($query) {
                        $query->select('id', 'date_of_publication', 'deadline_of_submission'); 
                    }
                ])
                ->where('id', $id)
                ->first();
=======
        $plantillaVacancy = RecruitmentVacancyPlantilla::with([
            'publication', 
            'creator', 
            'modifier',
            'schedules',
            'attachments'
        ])->find($id);
>>>>>>> origin/Development

        if (!$plantillaVacancy) {
            return $this->sendError('Plantilla vacancy not found.', [], 404);
        }

        // Load additional relationships if they exist
        $plantillaCode = $plantillaVacancy->recruitment_plantilla_vacancy_code;
        
        // If schedules relationship doesn't exist, fetch them manually
        if (!$plantillaVacancy->relationLoaded('schedules')) {
            $schedules = RecruitmentActualSchedules::where('vacancy_plantilla_id', $id)->first();
            $plantillaVacancy->setRelation('schedules', $schedules);
        }

        // If attachments relationship doesn't exist, fetch them manually
        if (!$plantillaVacancy->relationLoaded('attachments')) {
            $attachments = RecruitmentApplicantAttachment::where('recruitment_plantilla_vacancy_code', $plantillaCode)->get();
            $plantillaVacancy->setRelation('attachments', $attachments);
        }

        // Format the response data
        $responseData = [
            'plantilla' => $plantillaVacancy,
            'schedules' => $plantillaVacancy->schedules,
            'attachments' => $plantillaVacancy->attachments,
            'attachments_count' => $plantillaVacancy->attachments->count()
        ];

        $this->logAudit('VIEW', $plantillaVacancy);

        return $this->sendResponse($responseData, 'Plantilla vacancy retrieved successfully.');

    } catch (\Exception $e) {
        Log::error('Error fetching plantilla vacancy', [
            'id' => $id,
            'error' => $e->getMessage(),
            'trace' => $e->getTraceAsString()
        ]);

        return $this->sendError('Internal Server Error', [], 500);
    }
}

    /**
     * Update the specified plantilla vacancy.
     */
    public function update(Request $request, $id)
    {
        try {
            Log::info('Updating plantilla vacancy', ['id' => $id, 'request' => $request->all()]);

            $plantillaVacancy = RecruitmentVacancyPlantilla::find($id);

            if (!$plantillaVacancy) {
                return $this->sendError('Plantilla vacancy not found.', [], 404);
            }

            // Update validation rules (make fields optional for update)
            $updateRules = [];
            foreach ($this->validationRules as $field => $rule) {
                if (strpos($rule, 'required') !== false) {
                    $updateRules[$field] = str_replace('required', 'sometimes', $rule);
                } else {
                    $updateRules[$field] = 'sometimes|' . $rule;
                }
            }

            // Validate request
            $validator = Validator::make($request->all(), $updateRules, $this->validationMessages);
            if ($validator->fails()) {
                return $this->sendError('Validation Error', $validator->errors(), 422);
            }

            $oldValues = $plantillaVacancy->toArray();

            // Update record
            $updateData = $request->all();
            $updateData['modified_by'] = auth()->id();
            
            $plantillaVacancy->update($updateData);

            $plantillaVacancy->refresh();
            $newValues = $plantillaVacancy->toArray();

            $this->logAudit('UPDATE', $plantillaVacancy, $oldValues, $newValues);

            Log::info('Plantilla vacancy updated successfully', ['id' => $id]);

            return $this->sendResponse($plantillaVacancy, 'Plantilla vacancy updated successfully.');

        } catch (\Exception $e) {
            Log::error('Error updating plantilla vacancy', [
                'id' => $id,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
                'request' => $request->all()
            ]);

            return $this->sendError('Internal Server Error', [], 500);
        }
    }
/**
 * Update plantilla vacancy and sync to online portal simultaneously
 */
public function updateAndSync(Request $request, $id)
{
    try {
        DB::beginTransaction();

        Log::info('🔄 UPDATING PLANTILLA VACANCY AND SYNCING TO ONLINE PORTAL', [
            'id' => $id,
            'update_fields' => array_keys($request->all())
        ]);

        $plantillaVacancy = RecruitmentVacancyPlantilla::with('publication')->find($id);

        if (!$plantillaVacancy) {
            return $this->sendError('Plantilla vacancy not found.', [], 404);
        }

        // Update validation rules (make fields optional for update)
        $updateRules = [];
        foreach ($this->validationRules as $field => $rule) {
            if (strpos($rule, 'required') !== false) {
                $updateRules[$field] = str_replace('required', 'sometimes', $rule);
            } else {
                $updateRules[$field] = 'sometimes|' . $rule;
            }
        }

        // Validate request
        $validator = Validator::make($request->all(), $updateRules, $this->validationMessages);
        if ($validator->fails()) {
            return $this->sendError('Validation Error', $validator->errors(), 422);
        }

        $oldValues = $plantillaVacancy->toArray();

        // Update local record
        $updateData = $request->all();
        $updateData['modified_by'] = auth()->id();
        
        $plantillaVacancy->update($updateData);
        $plantillaVacancy->refresh();

        $newValues = $plantillaVacancy->toArray();

        Log::info('✅ LOCAL PLANTILLA VACANCY UPDATE SUCCESSFUL', [
            'vacancy_id' => $id,
            'updated_fields' => array_keys($request->all())
        ]);

        // ✅ SYNC TO ONLINE PORTAL
        $syncData = [
            'hris_vacancy_id' => $plantillaVacancy->id,
            'job_title_code' => $plantillaVacancy->job_title_code,
            'job_title_item_number_code' => $plantillaVacancy->job_title_item_number_code,
            'section_code' => $plantillaVacancy->section_code,
            'valid_from_date' => $plantillaVacancy->valid_from_date,
            'valid_to_date' => $plantillaVacancy->valid_to_date,
            'years_of_experience' => $plantillaVacancy->years_of_experience,
            'hours_of_relevant_training' => $plantillaVacancy->hours_of_relevant_training,
            'eligibility' => $plantillaVacancy->eligibility,
            'education' => $plantillaVacancy->education,
            'number_of_applicants' => $plantillaVacancy->number_of_applicants,
            'status' => $plantillaVacancy->status,
            'open_for_vacancy' => $plantillaVacancy->open_for_vacancy,
            'vice' => $plantillaVacancy->vice,
            'assignment_based_on_psiop' => $plantillaVacancy->assignment_based_on_psiop,
            'actual_assignment' => $plantillaVacancy->actual_assignment,
            'reason_for_vacancy' => $plantillaVacancy->reason_for_vacancy,
            'vacancy_status' => $plantillaVacancy->vacancy_status,
            'date_vacancy' => $plantillaVacancy->date_vacancy,
            'separation_status_code' => $plantillaVacancy->separation_status_code,
            'application_status_code' => $plantillaVacancy->application_status_code,
            'show_in_kiosk' => $plantillaVacancy->show_in_kiosk,
            'vacant_position_publication_id' => $plantillaVacancy->vacant_position_publication_id,
            'competencies' => $plantillaVacancy->competencies,
            'job_description_form' => $plantillaVacancy->job_description_form,
            'actual_assignment_code' => $plantillaVacancy->actual_assignment_code,
            'turn_around_time' => $plantillaVacancy->turn_around_time,
            'actual_processing_time' => $plantillaVacancy->actual_processing_time,
            'campus_code' => $plantillaVacancy->campus_code,
            'role_type_code' => $plantillaVacancy->role_type_code,
            'deliberation_date' => $plantillaVacancy->deliberation_date,
            'action' => 'update',
            'updated_fields' => array_keys($request->all())
        ];

        $onlineResponse = null;
        $syncSuccess = false;

        // ✅ TRY MULTIPLE URL VARIATIONS
        $baseUrl = config('services.online_portal.url');
        $urlVariations = [
            $baseUrl . '/api/v1/online/vacancies/plantillas/update-from-hris',
            $baseUrl . '/api/online/vacancies/plantillas/update-from-hris',
            $baseUrl . '/online/vacancies/plantillas/update-from-hris',
            'http://127.0.0.1:8001/api/v1/online/vacancies/plantillas/update-from-hris',
        ];

        foreach ($urlVariations as $url) {
            try {
                Log::info('🔄 ATTEMPTING SYNC PLANTILLA VACANCY TO ONLINE PORTAL', ['url' => $url]);
                
                $onlineResponse = Http::timeout(10)
                    ->withHeaders([
                        'Accept' => 'application/json',
                        'Content-Type' => 'application/json',
                    ])
                    ->put($url, $syncData);

                Log::info('📡 ONLINE PORTAL RESPONSE FOR PLANTILLA VACANCY', [
                    'url' => $url,
                    'status_code' => $onlineResponse->status(),
                    'success' => $onlineResponse->successful()
                ]);

                if ($onlineResponse->successful()) {
                    $syncSuccess = true;
                    Log::info('✅ PLANTILLA VACANCY ONLINE SYNC SUCCESSFUL', [
                        'url' => $url,
                        'online_vacancy_id' => $onlineResponse->json('data.id')
                    ]);
                    break;
                } else {
                    Log::warning('⚠️ PLANTILLA VACANCY ONLINE SYNC FAILED', [
                        'url' => $url,
                        'status' => $onlineResponse->status(),
                        'response' => $onlineResponse->body()
                    ]);
                }
            } catch (\Exception $e) {
                Log::error('❌ PLANTILLA VACANCY ONLINE SYNC EXCEPTION', [
                    'url' => $url,
                    'error' => $e->getMessage()
                ]);
            }
        }

        DB::commit();

        $this->logAudit('UPDATE_AND_SYNC_PLANTILLA_VACANCY', $plantillaVacancy, $oldValues, $newValues);

        Log::info('🏁 PLANTILLA VACANCY UPDATE AND SYNC COMPLETE', [
            'id' => $id,
            'local_update' => 'SUCCESS',
            'online_sync' => $syncSuccess ? 'SUCCESS' : 'FAILED'
        ]);

        return $this->sendResponse([
            'plantilla_vacancy' => $plantillaVacancy,
            'online_sync' => [
                'success' => $syncSuccess,
                'response' => $onlineResponse ? $onlineResponse->json() : null,
                'message' => $syncSuccess ? 'Plantilla vacancy update synced to online portal' : 'Online sync failed'
            ]
        ], 'Plantilla vacancy updated successfully.');

    } catch (\Exception $e) {
        DB::rollBack();

        Log::error('💥 ERROR IN PLANTILLA VACANCY UPDATE AND SYNC', [
            'id' => $id,
            'error' => $e->getMessage(),
            'trace' => $e->getTraceAsString()
        ]);

        return $this->sendError('Internal Server Error', [], 500);
    }
}
    /**
     * Remove the specified plantilla vacancy.
     */
    public function destroy($id)
    {
        try {
            Log::info('Deleting plantilla vacancy', ['id' => $id]);

            $plantillaVacancy = RecruitmentVacancyPlantilla::find($id);

            if (!$plantillaVacancy) {
                return $this->sendError('Plantilla vacancy not found.', [], 404);
            }

            // Check if vacancy is published to a publication
            if ($plantillaVacancy->vacant_position_publication_id) {
                return $this->sendError('Cannot delete plantilla vacancy that is published to a publication. Please remove it from the publication first.', [], 422);
            }

            $oldValues = $plantillaVacancy->toArray();
            $plantillaVacancy->delete();

            $this->logAudit('DELETE', $plantillaVacancy, $oldValues, null);

            Log::info('Plantilla vacancy deleted successfully', ['id' => $id]);

            return $this->sendResponse([], 'Plantilla vacancy deleted successfully.');

        } catch (\Exception $e) {
            Log::error('Error deleting plantilla vacancy', [
                'id' => $id,
                'error' => $e->getMessage()
            ]);

            return $this->sendError('Internal Server Error', [], 500);
        }
    }
    /**
     * Get plantilla vacancy statistics.
     */
    public function statistics()
    {
        try {
            Log::info('Fetching plantilla vacancy statistics');

            $totalVacancies = RecruitmentVacancyPlantilla::count();
            $activeVacancies = RecruitmentVacancyPlantilla::active()->count();
            $openVacancies = RecruitmentVacancyPlantilla::open()->count();
            $publishedVacancies = RecruitmentVacancyPlantilla::published()->count();

            $statusBreakdown = RecruitmentVacancyPlantilla::select('vacancy_status')
                ->selectRaw('COUNT(*) as count')
                ->groupBy('vacancy_status')
                ->get();

            $statistics = [
                'total_vacancies' => $totalVacancies,
                'active_vacancies' => $activeVacancies,
                'open_vacancies' => $openVacancies,
                'published_vacancies' => $publishedVacancies,
                'status_breakdown' => $statusBreakdown
            ];

            $this->logAudit('VIEW_STATISTICS', null, null, $statistics);

            return $this->sendResponse($statistics, 'Plantilla vacancy statistics retrieved successfully.');

        } catch (\Exception $e) {
            Log::error('Error fetching plantilla vacancy statistics', [
                'error' => $e->getMessage()
            ]);

            return $this->sendError('Internal Server Error', [], 500);
        }
    }
}