<?php

namespace App\Http\Controllers;

use App\Models\CityHRACLA;
use Illuminate\Http\Request;
use Illuminate\Database\Eloquent\ModelNotFoundException;
use Illuminate\Support\Facades\Validator;

class CityHRACLAController extends Controller
{
    public function index(Request $request)
    {
        try {
            $query = CityHRACLA::query()->with([
                'cityGrade' => function($q) {
                    $q->select('CGR_Id', 'City_Name', 'Grade');
                }
            ]);

            if ($request->has('search') && !empty($request->search)) {
                $search = $request->search;
                $query->where(function($q) use ($search) {
                    $q->where('HRA_1', 'LIKE', "%{$search}%")
                      ->orWhere('CLA_1', 'LIKE', "%{$search}%")
                      ->orWhere('Basic_From', 'LIKE', "%{$search}%")
                      ->orWhere('Basic_To', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_Min', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_UT24', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_UT49', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_AB49', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_6th', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_7th', 'LIKE', "%{$search}%")
                      ->orWhereHas('cityGrade', function($cityQuery) use ($search) {
                          $cityQuery->where('City_Name', 'LIKE', "%{$search}%")
                                    ->orWhere('Grade', 'LIKE', "%{$search}%");
                      });
                });
            }

            if ($request->has('cgr_id') && !empty($request->cgr_id)) {
                $query->where('CGR_Id', $request->cgr_id);
            }

            if ($request->has('basic_range') && !empty($request->basic_range)) {
                $basic = $request->basic_range;
                $query->where('Basic_From', '<=', $basic)
                      ->where('Basic_To', '>=', $basic);
            }

            if ($request->has('hra_min_range') && !empty($request->hra_min_range)) {
                $query->where('HRA_Min', '>=', $request->hra_min_range);
            }

            if ($request->has('hra_max_range') && !empty($request->hra_max_range)) {
                $query->where('HRA_Min', '<=', $request->hra_max_range);
            }

            if ($request->has('sort_by') && $request->has('sort_order') && !empty($request->sort_by)) {
                $query->orderBy($request->sort_by, $request->sort_order);
            } else {
                $query->orderBy('CGR_Id', 'asc')->orderBy('Basic_From', 'asc');
            }

            $perPage = $request->get('per_page', 15);
            $data = $query->paginate($perPage);

            return response()->json([
                'success' => true,
                'message' => 'City HRACLA records retrieved successfully',
                'data' => $data,
                'pagination' => [
                    'total' => $data->total(),
                    'per_page' => $data->perPage(),
                    'current_page' => $data->currentPage(),
                    'last_page' => $data->lastPage(),
                    'from' => $data->firstItem(),
                    'to' => $data->lastItem()
                ]
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve records',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function all(Request $request)
    {
        try {
            $query = CityHRACLA::query()->with([
                'cityGrade' => function($q) {
                    $q->select('CGR_Id', 'City_Name', 'Grade');
                }
            ]);

            if ($request->has('search') && !empty($request->search)) {
                $search = $request->search;
                $query->where(function($q) use ($search) {
                    $q->where('HRA_1', 'LIKE', "%{$search}%")
                      ->orWhere('CLA_1', 'LIKE', "%{$search}%")
                      ->orWhere('Basic_From', 'LIKE', "%{$search}%")
                      ->orWhere('Basic_To', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_Min', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_UT24', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_UT49', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_AB49', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_6th', 'LIKE', "%{$search}%")
                      ->orWhere('HRA_7th', 'LIKE', "%{$search}%")
                      ->orWhereHas('cityGrade', function($cityQuery) use ($search) {
                          $cityQuery->where('City_Name', 'LIKE', "%{$search}%")
                                    ->orWhere('Grade', 'LIKE', "%{$search}%");
                      });
                });
            }

            if ($request->has('cgr_id') && !empty($request->cgr_id)) {
                $query->where('CGR_Id', $request->cgr_id);
            }

            if ($request->has('basic_range') && !empty($request->basic_range)) {
                $basic = $request->basic_range;
                $query->where('Basic_From', '<=', $basic)
                      ->where('Basic_To', '>=', $basic);
            }

            if ($request->has('sort_by') && $request->has('sort_order') && !empty($request->sort_by)) {
                $query->orderBy($request->sort_by, $request->sort_order);
            } else {
                $query->orderBy('CGR_Id', 'asc')->orderBy('Basic_From', 'asc');
            }

            $data = $query->get();

            return response()->json([
                'success' => true,
                'message' => 'All City HRACLA records retrieved successfully',
                'data' => $data
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve records',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function show($id)
    {
        try {
            $record = CityHRACLA::with([
                'cityGrade' => function($q) {
                    $q->select('CGR_Id', 'City_Name', 'Grade');
                }
            ])->findOrFail($id);

            return response()->json([
                'success' => true,
                'message' => 'Record retrieved successfully',
                'data' => $record
            ], 200);
        } catch (ModelNotFoundException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Record not found with ID: ' . $id
            ], 404);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve record',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function store(Request $request)
    {
        try {
            $validator = Validator::make($request->all(), [
                'CGR_Id' => 'required|integer|exists:city_grade,CGR_Id',
                'HRA_1' => 'nullable|integer|min:0',
                'CLA_1' => 'nullable|integer|min:0',
                'Basic_From' => 'nullable|integer|min:0',
                'Basic_To' => 'nullable|integer|min:0|gt:Basic_From',
                'HRA_Min' => 'nullable|integer|min:0',
                'HRA_UT24' => 'nullable|integer|min:0',
                'HRA_UT49' => 'nullable|integer|min:0',
                'HRA_AB49' => 'nullable|integer|min:0',
                'HRA_6th' => 'nullable|integer|min:0',
                'HRA_7th' => 'nullable|integer|min:0'
            ]);

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

            $record = CityHRACLA::create($request->all());

            
            $record->load([
                'cityGrade' => function($q) {
                    $q->select('CGR_Id', 'City_Name', 'Grade');
                }
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Record created successfully',
                'data' => $record
            ], 201);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to create record',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function update(Request $request, $id)
    {
        try {
            $record = CityHRACLA::findOrFail($id);

            $validator = Validator::make($request->all(), [
                'CGR_Id' => 'sometimes|required|integer|exists:city_grade,CGR_Id',
                'HRA_1' => 'nullable|integer|min:0',
                'CLA_1' => 'nullable|integer|min:0',
                'Basic_From' => 'nullable|integer|min:0',
                'Basic_To' => 'nullable|integer|min:0|gt:Basic_From',
                'HRA_Min' => 'nullable|integer|min:0',
                'HRA_UT24' => 'nullable|integer|min:0',
                'HRA_UT49' => 'nullable|integer|min:0',
                'HRA_AB49' => 'nullable|integer|min:0',
                'HRA_6th' => 'nullable|integer|min:0',
                'HRA_7th' => 'nullable|integer|min:0'
            ]);

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

            $record->update($request->all());

            
            $record->load([
                'cityGrade' => function($q) {
                    $q->select('CGR_Id', 'City_Name', 'Grade');
                }
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Record updated successfully',
                'data' => $record
            ], 200);
        } catch (ModelNotFoundException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Record not found with ID: ' . $id
            ], 404);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to update record',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function destroy($id)
    {
        try {
            $record = CityHRACLA::findOrFail($id);
            $record->delete();

            return response()->json([
                'success' => true,
                'message' => 'Record deleted successfully'
            ], 200);
        } catch (ModelNotFoundException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Record not found with ID: ' . $id
            ], 404);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to delete record',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function getByCityGrade($cgrId)
    {
        try {
            $records = CityHRACLA::with([
                'cityGrade' => function($q) {
                    $q->select('CGR_Id', 'City_Name', 'Grade');
                }
            ])->where('CGR_Id', $cgrId)
              ->orderBy('Basic_From')
              ->get();

            return response()->json([
                'success' => true,
                'message' => 'Records retrieved successfully for CGR_Id: ' . $cgrId,
                'data' => $records
            ], 200);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve records',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }
}
