<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Models\ZoneMaster;
use App\Models\WardMaster;
use App\Models\AreaMaster;
use App\Models\CccMaster;
use App\Models\Department;
use App\Models\UserMaster;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ListingReportController extends Controller
{
    /**
     * Get Zone List Report with filters
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getZoneList(Request $request)
    {
        try {
            $query = ZoneMaster::with('organisation')
                ->select('Zon_Id', 'Zon_Code', 'Zon_Name', 'Org_Id');

            // Apply filters
            if ($request->has('zone_code')) {
                $query->where('Zon_Code', 'LIKE', '%' . $request->zone_code . '%');
            }

            if ($request->has('zone_name')) {
                $query->where('Zon_Name', 'LIKE', '%' . $request->zone_name . '%');
            }

            if ($request->has('org_id')) {
                $query->where('Org_Id', $request->org_id);
            }

            if ($request->has('organization_name')) {
                $query->whereHas('organisation', function($q) use ($request) {
                    $q->where('Org_Name', 'LIKE', '%' . $request->organization_name . '%');
                });
            }

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

            $formattedData = collect($zones->items())->map(function($zone) {
                return [
                    'zone_code' => $zone->Zon_Code,
                    'zone_name' => $zone->Zon_Name,
                    'organization_name' => $zone->organisation->Org_Name ?? null,
                    'organization_code' => $zone->organisation->Org_Code ?? null
                ];
            });

            return response()->json([
                'success' => true,
                'message' => 'Zone list retrieved successfully',
                'data' => $formattedData,
                'pagination' => [
                    'total' => $zones->total(),
                    'per_page' => $zones->perPage(),
                    'current_page' => $zones->currentPage(),
                    'last_page' => $zones->lastPage(),
                    'from' => $zones->firstItem(),
                    'to' => $zones->lastItem()
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error retrieving zone list',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get Ward List Report with filters
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getWardList(Request $request)
    {
        try {
            $query = WardMaster::with('zone')
                ->select('Wrd_Id', 'Wrd_Code', 'Wrd_Name', 'Zon_Id');

            // Apply filters
            if ($request->has('ward_code')) {
                $query->where('Wrd_Code', 'LIKE', '%' . $request->ward_code . '%');
            }

            if ($request->has('ward_name')) {
                $query->where('Wrd_Name', 'LIKE', '%' . $request->ward_name . '%');
            }

            if ($request->has('zone_id')) {
                $query->where('Zon_Id', $request->zone_id);
            }

            if ($request->has('zone_name')) {
                $query->whereHas('zone', function($q) use ($request) {
                    $q->where('Zon_Name', 'LIKE', '%' . $request->zone_name . '%');
                });
            }

            if ($request->has('zone_code')) {
                $query->whereHas('zone', function($q) use ($request) {
                    $q->where('Zon_Code', 'LIKE', '%' . $request->zone_code . '%');
                });
            }

            if ($request->has('is_active')) {
                $query->where('is_active', $request->is_active);
            }

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

            $formattedData = collect($wards->items())->map(function($ward) {
                return [
                    'ward_code' => $ward->Wrd_Code,
                    'ward_name' => $ward->Wrd_Name,
                    'zone_code' => $ward->zone->Zon_Code ?? null,
                    'zone_name' => $ward->zone->Zon_Name ?? null,
                    'zone_id' => $ward->Zon_Id
                ];
            });

            return response()->json([
                'success' => true,
                'message' => 'Ward list retrieved successfully',
                'data' => $formattedData,
                'pagination' => [
                    'total' => $wards->total(),
                    'per_page' => $wards->perPage(),
                    'current_page' => $wards->currentPage(),
                    'last_page' => $wards->lastPage(),
                    'from' => $wards->firstItem(),
                    'to' => $wards->lastItem()
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error retrieving ward list',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get CCC List Report with filters
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getCCCList(Request $request)
    {
        try {
            $query = CccMaster::with('ward.zone')
                ->select('CCC_Id', 'CCC_Code', 'CCC_Name', 'Wrd_Id');

            // Apply filters
            if ($request->has('ccc_code')) {
                $query->where('CCC_Code', 'LIKE', '%' . $request->ccc_code . '%');
            }

            if ($request->has('ccc_name')) {
                $query->where('CCC_Name', 'LIKE', '%' . $request->ccc_name . '%');
            }

            if ($request->has('ward_id')) {
                $query->where('Wrd_Id', $request->ward_id);
            }

            if ($request->has('ward_name')) {
                $query->whereHas('ward', function($q) use ($request) {
                    $q->where('Wrd_Name', 'LIKE', '%' . $request->ward_name . '%');
                });
            }

            if ($request->has('ward_code')) {
                $query->whereHas('ward', function($q) use ($request) {
                    $q->where('Wrd_Code', 'LIKE', '%' . $request->ward_code . '%');
                });
            }

            if ($request->has('zone_id')) {
                $query->whereHas('ward', function($q) use ($request) {
                    $q->where('Zon_Id', $request->zone_id);
                });
            }

            if ($request->has('zone_name')) {
                $query->whereHas('ward.zone', function($q) use ($request) {
                    $q->where('Zon_Name', 'LIKE', '%' . $request->zone_name . '%');
                });
            }

            if ($request->has('is_active')) {
                $query->where('is_active', $request->is_active);
            }

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

            $formattedData = collect($cccs->items())->map(function($ccc) {
                return [
                    'ccc_code' => $ccc->CCC_Code,
                    'ccc_name' => $ccc->CCC_Name,
                    'ward_code' => $ccc->ward->Wrd_Code ?? null,
                    'ward_name' => $ccc->ward->Wrd_Name ?? null,
                    'zone_code' => $ccc->ward->zone->Zon_Code ?? null,
                    'zone_name' => $ccc->ward->zone->Zon_Name ?? null,
                    'ward_id' => $ccc->Wrd_Id
                ];
            });

            return response()->json([
                'success' => true,
                'message' => 'CCC list retrieved successfully',
                'data' => $formattedData,
                'pagination' => [
                    'total' => $cccs->total(),
                    'per_page' => $cccs->perPage(),
                    'current_page' => $cccs->currentPage(),
                    'last_page' => $cccs->lastPage(),
                    'from' => $cccs->firstItem(),
                    'to' => $cccs->lastItem()
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error retrieving CCC list',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get Area List Report with filters - FIXED VERSION
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getAreaList(Request $request)
    {
        try {
            // Fixed: Use 'Are_Id' instead of 'Area_Id' and remove CCC_Id if it doesn't exist
            $query = AreaMaster::with(['ward.zone'])
                ->select('Are_Id', 'Are_Code', 'Are_Name', 'Wrd_Id');

            // Apply filters
            if ($request->has('area_code')) {
                $query->where('Are_Code', 'LIKE', '%' . $request->area_code . '%');
            }

            if ($request->has('area_name')) {
                $query->where('Are_Name', 'LIKE', '%' . $request->area_name . '%');
            }

            if ($request->has('ward_id')) {
                $query->where('Wrd_Id', $request->ward_id);
            }

            if ($request->has('ward_name')) {
                $query->whereHas('ward', function($q) use ($request) {
                    $q->where('Wrd_Name', 'LIKE', '%' . $request->ward_name . '%');
                });
            }

            // Fix: Use correct column name for zone filter
            if ($request->has('zone_id')) {
                $query->whereHas('ward', function($q) use ($request) {
                    $q->where('Zon_Id', $request->zone_id);
                });
            }

            if ($request->has('zone_name')) {
                $query->whereHas('ward.zone', function($q) use ($request) {
                    $q->where('Zon_Name', 'LIKE', '%' . $request->zone_name . '%');
                });
            }

            if ($request->has('zone_code')) {
                $query->whereHas('ward.zone', function($q) use ($request) {
                    $q->where('Zon_Code', 'LIKE', '%' . $request->zone_code . '%');
                });
            }

            if ($request->has('is_active')) {
                $query->where('is_active', $request->is_active);
            }

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

            $formattedData = collect($areas->items())->map(function($area) {
                return [
                    'area_id' => $area->Are_Id,  // Fixed: Use Are_Id
                    'area_code' => $area->Are_Code,
                    'area_name' => $area->Are_Name,
                    'ward_code' => $area->ward->Wrd_Code ?? null,
                    'ward_name' => $area->ward->Wrd_Name ?? null,
                    'zone_code' => $area->ward->zone->Zon_Code ?? null,
                    'zone_name' => $area->ward->zone->Zon_Name ?? null,
                    'ward_id' => $area->Wrd_Id,
                    // 'ccc_id' and 'ccc_code' removed as they don't exist in your response
                ];
            });

            return response()->json([
                'success' => true,
                'message' => 'Area list retrieved successfully',
                'data' => $formattedData,
                'pagination' => [
                    'total' => $areas->total(),
                    'per_page' => $areas->perPage(),
                    'current_page' => $areas->currentPage(),
                    'last_page' => $areas->lastPage(),
                    'from' => $areas->firstItem(),
                    'to' => $areas->lastItem()
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error retrieving area list',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get Department List Report with filters
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getDepartmentList(Request $request)
    {
        try {
            $query = Department::select(
                    'Dpt_id',
                    'Dpt_Desc as gujarati_name',
                    'Dpt_Name as english_name',
                    'GrCod as group_code',
                    'UpGrp as parent_group',
                    'GrSrn as group_serial',
                    'GrStt as group_status',
                    'isvisible'
                );

            // Apply filters
            if ($request->has('department_name_gujarati')) {
                $query->where('Dpt_Desc', 'LIKE', '%' . $request->department_name_gujarati . '%');
            }

            if ($request->has('department_name_english')) {
                $query->where('Dpt_Name', 'LIKE', '%' . $request->department_name_english . '%');
            }

            if ($request->has('group_code')) {
                $query->where('GrCod', 'LIKE', '%' . $request->group_code . '%');
            }

            if ($request->has('department_type')) {
                if ($request->department_type == 'main') {
                    $query->where('GrStt', '1');
                } elseif ($request->department_type == 'sub') {
                    $query->where('GrStt', '2');
                }
            }

            if ($request->has('is_visible')) {
                $query->where('isvisible', $request->is_visible);
            }

            if ($request->has('parent_group')) {
                $query->where('UpGrp', $request->parent_group);
            }

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

            $formattedData = collect($departments->items())->map(function($dept) {
                return [
                    'department_code' => $dept->group_code,
                    'department_name_gujarati' => $dept->gujarati_name,
                    'department_name_english' => $dept->english_name,
                    'parent_group_code' => $dept->parent_group,
                    'department_status' => $dept->group_status == '1' ? 'Main Department' : 'Sub Department',
                    'is_visible' => $dept->isvisible == '1' ? 'Yes' : 'No',
                    'group_serial' => $dept->group_serial
                ];
            });

            return response()->json([
                'success' => true,
                'message' => 'Department list retrieved successfully',
                'data' => $formattedData,
                'pagination' => [
                    'total' => $departments->total(),
                    'per_page' => $departments->perPage(),
                    'current_page' => $departments->currentPage(),
                    'last_page' => $departments->lastPage(),
                    'from' => $departments->firstItem(),
                    'to' => $departments->lastItem()
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error retrieving department list',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get User List Report with filters
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getUserList(Request $request)
    {
        try {
            $query = UserMaster::with('role')
                ->select(
                    'Usr_Id',
                    'Usr_Name',
                    'Usr_Email',
                    'Usr_Mobile',
                    'Usr_Role_Id',
                    'Usr_Dpt_Id',
                    'is_active',
                    'is_locked',
                    'last_login'
                );

            // Apply filters
            if ($request->has('user_name')) {
                $query->where('Usr_Name', 'LIKE', '%' . $request->user_name . '%');
            }

            if ($request->has('user_email')) {
                $query->where('Usr_Email', 'LIKE', '%' . $request->user_email . '%');
            }

            if ($request->has('user_mobile')) {
                $query->where('Usr_Mobile', 'LIKE', '%' . $request->user_mobile . '%');
            }

            if ($request->has('role_id')) {
                $query->where('Usr_Role_Id', $request->role_id);
            }

            if ($request->has('role_name')) {
                $query->whereHas('role', function($q) use ($request) {
                    $q->where('Role_Name', 'LIKE', '%' . $request->role_name . '%');
                });
            }

            if ($request->has('department_id')) {
                $query->where('Usr_Dpt_Id', $request->department_id);
            }

            if ($request->has('account_status')) {
                if ($request->account_status == 'active') {
                    $query->where('is_active', true);
                } elseif ($request->account_status == 'inactive') {
                    $query->where('is_active', false);
                } elseif ($request->account_status == 'locked') {
                    $query->where('is_locked', true);
                }
            }

            if ($request->has('date_from') && $request->has('date_to')) {
                $query->whereBetween('created_at', [$request->date_from, $request->date_to]);
            }

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

            $formattedData = collect($users->items())->map(function($user) {
                return [
                    'user_name' => $user->Usr_Name,
                    'user_email' => $user->Usr_Email,
                    'user_mobile' => $user->Usr_Mobile,
                    'role_name' => $user->role->Role_Name ?? null,
                    'role_prefix' => $user->role->Role_Prefix ?? null,
                    'department_id' => $user->Usr_Dpt_Id,
                    'account_status' => $this->getAccountStatus($user),
                    'last_login' => $user->last_login ? date('d-m-Y H:i:s', strtotime($user->last_login)) : null
                ];
            });

            return response()->json([
                'success' => true,
                'message' => 'User list retrieved successfully',
                'data' => $formattedData,
                'pagination' => [
                    'total' => $users->total(),
                    'per_page' => $users->perPage(),
                    'current_page' => $users->currentPage(),
                    'last_page' => $users->lastPage(),
                    'from' => $users->firstItem(),
                    'to' => $users->lastItem()
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error retrieving user list',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get Complete Listing Report with filters
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getCompleteListingReport(Request $request)
    {
        try {
            $report = [
                'zones' => $this->getZoneListData($request),
                'wards' => $this->getWardListData($request),
                'cccs' => $this->getCCCListData($request),
                'areas' => $this->getAreaListData($request),  // Added areas
                'departments' => $this->getDepartmentListData($request),
                'users' => $this->getUserListData($request)
            ];

            $summary = [
                'total_zones' => count($report['zones']),
                'total_wards' => count($report['wards']),
                'total_cccs' => count($report['cccs']),
                'total_areas' => count($report['areas']),  // Added areas count
                'total_departments' => count($report['departments']),
                'total_users' => count($report['users']),
                'generated_at' => now()->format('d-m-Y H:i:s')
            ];

            return response()->json([
                'success' => true,
                'message' => 'Complete listing report retrieved successfully',
                'summary' => $summary,
                'filters_applied' => $request->all(),
                'data' => $report
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error retrieving complete listing report',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get Zone List Data with filters (Helper Method)
     */
    private function getZoneListData($request)
    {
        $query = ZoneMaster::with('organisation')
            ->select('Zon_Id', 'Zon_Code', 'Zon_Name', 'Org_Id');

        if ($request->has('zone_code')) {
            $query->where('Zon_Code', 'LIKE', '%' . $request->zone_code . '%');
        }

        if ($request->has('zone_name')) {
            $query->where('Zon_Name', 'LIKE', '%' . $request->zone_name . '%');
        }

        return $query->get()->map(function($zone) {
            return [
                'zone_code' => $zone->Zon_Code,
                'zone_name' => $zone->Zon_Name,
                'organization_name' => $zone->organisation->Org_Name ?? null,
                'organization_code' => $zone->organisation->Org_Code ?? null
            ];
        });
    }

    /**
     * Get Ward List Data with filters (Helper Method)
     */
    private function getWardListData($request)
    {
        $query = WardMaster::with('zone')
            ->select('Wrd_Id', 'Wrd_Code', 'Wrd_Name', 'Zon_Id');

        if ($request->has('ward_code')) {
            $query->where('Wrd_Code', 'LIKE', '%' . $request->ward_code . '%');
        }

        if ($request->has('ward_name')) {
            $query->where('Wrd_Name', 'LIKE', '%' . $request->ward_name . '%');
        }

        return $query->get()->map(function($ward) {
            return [
                'ward_code' => $ward->Wrd_Code,
                'ward_name' => $ward->Wrd_Name,
                'zone_code' => $ward->zone->Zon_Code ?? null,
                'zone_name' => $ward->zone->Zon_Name ?? null
            ];
        });
    }

    /**
     * Get CCC List Data with filters (Helper Method)
     */
    private function getCCCListData($request)
    {
        $query = CccMaster::with('ward.zone')
            ->select('CCC_Id', 'CCC_Code', 'CCC_Name', 'Wrd_Id');

        if ($request->has('ccc_code')) {
            $query->where('CCC_Code', 'LIKE', '%' . $request->ccc_code . '%');
        }

        if ($request->has('ccc_name')) {
            $query->where('CCC_Name', 'LIKE', '%' . $request->ccc_name . '%');
        }

        return $query->get()->map(function($ccc) {
            return [
                'ccc_code' => $ccc->CCC_Code,
                'ccc_name' => $ccc->CCC_Name,
                'ward_code' => $ccc->ward->Wrd_Code ?? null,
                'ward_name' => $ccc->ward->Wrd_Name ?? null,
                'zone_code' => $ccc->ward->zone->Zon_Code ?? null,
                'zone_name' => $ccc->ward->zone->Zon_Name ?? null
            ];
        });
    }

    /**
     * Get Area List Data with filters (Helper Method) - FIXED VERSION
     */
    private function getAreaListData($request)
    {
        $query = AreaMaster::with(['ward.zone'])
            ->select('Are_Id', 'Are_Code', 'Are_Name', 'Wrd_Id');

        if ($request->has('area_code')) {
            $query->where('Are_Code', 'LIKE', '%' . $request->area_code . '%');
        }

        if ($request->has('area_name')) {
            $query->where('Are_Name', 'LIKE', '%' . $request->area_name . '%');
        }

        return $query->get()->map(function($area) {
            return [
                'area_id' => $area->Are_Id,
                'area_code' => $area->Are_Code,
                'area_name' => $area->Are_Name,
                'ward_code' => $area->ward->Wrd_Code ?? null,
                'ward_name' => $area->ward->Wrd_Name ?? null,
                'zone_code' => $area->ward->zone->Zon_Code ?? null,
                'zone_name' => $area->ward->zone->Zon_Name ?? null
            ];
        });
    }

    /**
     * Get Department List Data with filters (Helper Method)
     */
    private function getDepartmentListData($request)
    {
        $query = Department::select(
            'Dpt_id',
            'Dpt_Desc as gujarati_name',
            'Dpt_Name as english_name',
            'GrCod as group_code',
            'GrStt as group_status'
        );

        if ($request->has('department_name_english')) {
            $query->where('Dpt_Name', 'LIKE', '%' . $request->department_name_english . '%');
        }

        return $query->get()->map(function($dept) {
            return [
                'department_code' => $dept->group_code,
                'department_name_gujarati' => $dept->gujarati_name,
                'department_name_english' => $dept->english_name,
                'department_status' => $dept->group_status == '1' ? 'Main Department' : 'Sub Department'
            ];
        });
    }

    /**
     * Get User List Data with filters (Helper Method)
     */
    private function getUserListData($request)
    {
        $query = User::with('role')
            ->select(
                'Usr_Id',
                'Usr_Name',
                'Usr_Email',
                'Usr_Mobile',
                'Usr_Role_Id',
                'is_active',
                'last_login'
            );

        if ($request->has('user_name')) {
            $query->where('Usr_Name', 'LIKE', '%' . $request->user_name . '%');
        }

        return $query->get()->map(function($user) {
            return [
                'user_name' => $user->Usr_Name,
                'user_email' => $user->Usr_Email,
                'user_mobile' => $user->Usr_Mobile,
                'role_name' => $user->role->Role_Name ?? null,
                'role_prefix' => $user->role->Role_Prefix ?? null,
                'account_status' => $this->getAccountStatus($user),
                'last_login' => $user->last_login ? date('d-m-Y H:i:s', strtotime($user->last_login)) : null
            ];
        });
    }

    /**
     * Get Account Status (Helper Method)
     */
    private function getAccountStatus($user)
    {
        if ($user->is_locked) {
            return 'Locked';
        } elseif ($user->is_active) {
            return 'Active';
        } else {
            return 'Inactive';
        }
    }
}
