<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\QueryException;
use Carbon\Carbon;

class DepartmentController extends Controller
{
    public function demo()
    {
        return response()->json([
            'success' => true,
            'message' => 'Department API is working'
        ]);
    }

    public function index(Request $request)
    {
        $query = DB::table('departments as d')
            ->select(
                // ALL department fields from your table
                'd.id',
                'd.company_id',
                'd.name_en',
                'd.name_native',
                'd.code',
                'd.description_en',
                'd.description_native',
                'd.parent_id',
                'd.current_head_id',
                'd.current_deputy_head_id',
                'd.location_id',
                'd.budget',
                'd.employee_count',
                'd.level',
                'd.path',
                'd.is_active',
                'd.status',
                'd.created_at_utc',
                'd.updated_at_utc',
                'd.created_by',
                'd.updated_by',
                'd.deleted_at_utc',
                'd.deleted_by',
                
                // Parent department details
                'parent.name_en as parent_name_en',
                'parent.name_native as parent_name_native',
                'parent.code as parent_code',
                
                // Company details
                'c.legal_name_en as company_name_en',
                'c.legal_name_native as company_name_native',
                'c.trade_name_en as company_trade_name_en',
                
                // Location details (if location_id exists)
                'cl.name_en as location_name_en',
                'cl.name_native as location_name_native',
                'cl.address_line1_en as location_address_en',
                'cl.address_line1_native as location_address_native'
            )
            ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
            ->leftJoin('companies as c', 'd.company_id', '=', 'c.id')
            ->leftJoin('company_locations as cl', 'd.location_id', '=', 'cl.id')
            ->whereNull('d.deleted_at_utc')
            ->where('d.status', '!=', 'dissolved')
            ->orderBy('d.company_id', 'asc')
            ->orderBy('d.path', 'asc');

        if ($request->has('company_id')) {
            $query->where('d.company_id', $request->input('company_id'));
        }

        if ($request->has('parent_id')) {
            $query->where('d.parent_id', $request->input('parent_id'));
        }

        if ($request->has('location_id')) {
            $query->where('d.location_id', $request->input('location_id'));
        }

        $data = $query->get();

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

    public function show(int $id)
    {
        $department = DB::table('departments as d')
            ->select(
                // ALL department fields from your table
                'd.id',
                'd.company_id',
                'd.name_en',
                'd.name_native',
                'd.code',
                'd.description_en',
                'd.description_native',
                'd.parent_id',
                'd.current_head_id',
                'd.current_deputy_head_id',
                'd.location_id',
                'd.budget',
                'd.employee_count',
                'd.level',
                'd.path',
                'd.is_active',
                'd.status',
                'd.created_at_utc',
                'd.updated_at_utc',
                'd.created_by',
                'd.updated_by',
                'd.deleted_at_utc',
                'd.deleted_by',
                
                // Parent department details
                'parent.name_en as parent_name_en',
                'parent.name_native as parent_name_native',
                'parent.code as parent_code',
                
                // Company details
                'c.legal_name_en as company_name_en',
                'c.legal_name_native as company_name_native',
                'c.trade_name_en as company_trade_name_en',
                
                // Location details
                'cl.name_en as location_name_en',
                'cl.name_native as location_name_native',
                'cl.address_line1_en as location_address_en',
                'cl.address_line1_native as location_address_native'
            )
            ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
            ->leftJoin('companies as c', 'd.company_id', '=', 'c.id')
            ->leftJoin('company_locations as cl', 'd.location_id', '=', 'cl.id')
            ->where('d.id', $id)
            ->whereNull('d.deleted_at_utc')
            ->first();

        if (!$department) {
            return response()->json([
                'success' => false,
                'message' => 'Department not found'
            ], 404);
        }

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

    // public function store(Request $request)
    // {
    //     DB::beginTransaction();
        
    //     try {
    //         $request->validate([
    //             'company_id' => 'required|integer|exists:companies,id',
    //             'name_en' => 'required|string|max:100',
    //             'code' => 'required|string|max:20',
    //             'is_active' => 'boolean',
    //             'status' => 'in:active,inactive,merged,dissolved'
    //         ]);

    //         // Validate foreign keys
    //         $company = DB::table('companies')->where('id', $request->input('company_id'))->whereNull('deleted_at_utc')->first();
    //         if (!$company) {
    //             return response()->json(['success' => false, 'message' => 'Company not found'], 404);
    //         }

    //         // Validate parent department (if provided)
    //         $parentId = $request->input('parent_id');
    //         $parentPath = '';
    //         $level = 1;
            
    //         if ($parentId) {
    //             $parent = DB::table('departments')
    //                 ->where('id', $parentId)
    //                 ->where('company_id', $request->input('company_id')) // Must be same company
    //                 ->whereNull('deleted_at_utc')
    //                 ->first();
                    
    //             if (!$parent) {
    //                 return response()->json(['success' => false, 'message' => 'Parent department not found or belongs to different company'], 404);
    //             }
    //             $parentPath = $parent->path;
    //             $level = $parent->level + 1;
    //         }

    //         // Build path for hierarchical structure
    //         $path = $parentPath ? $parentPath . '.' . $request->input('code') : $request->input('code');

    //         // Check for duplicate code within company
    //         $existing = DB::table('departments')
    //             ->where('company_id', $request->input('company_id'))
    //             ->where('code', $request->input('code'))
    //             ->whereNull('deleted_at_utc')
    //             ->first();
                
    //         if ($existing) {
    //             return response()->json(['success' => false, 'message' => 'Department code already exists for this company'], 409);
    //         }

    //         // Validate optional foreign keys
    //         $this->validateOptionalForeignKeys($request);

    //         $id = DB::table('departments')->insertGetId([
    //             'company_id' => $request->input('company_id'),
    //             'name_en' => $request->input('name_en'),
    //             'name_native' => $request->input('name_native'),
    //             'code' => strtoupper($request->input('code')),
    //             'description_en' => $request->input('description_en'),
    //             'description_native' => $request->input('description_native'),
    //             'parent_id' => $parentId,
    //             'current_head_id' => $request->input('current_head_id'),
    //             'current_deputy_head_id' => $request->input('current_deputy_head_id'),
    //             'location_id' => $request->input('location_id'),
    //             'budget' => $request->input('budget'),
    //             'employee_count' => $request->input('employee_count', 0),
    //             'level' => $level,
    //             'path' => $path,
    //             'is_active' => $request->boolean('is_active', true) ? 1 : 0,
    //             'status' => $request->input('status', 'active'),
    //             'created_at_utc' => Carbon::now('UTC'),
    //             'updated_at_utc' => Carbon::now('UTC'),
    //         ]);

    //         $department = DB::table('departments as d')
    //             ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
    //             ->leftJoin('companies as c', 'd.company_id', '=', 'c.id')
    //             ->leftJoin('company_locations as cl', 'd.location_id', '=', 'cl.id')
    //             ->select(
    //                 'd.*',
    //                 'parent.name_en as parent_name_en', 'parent.name_native as parent_name_native', 'parent.code as parent_code',
    //                 'c.legal_name_en as company_name_en', 'c.legal_name_native as company_name_native', 'c.trade_name_en as company_trade_name_en',
    //                 'cl.name_en as location_name_en', 'cl.name_native as location_name_native',
    //                 'cl.address_line1_en as location_address_en', 'cl.address_line1_native as location_address_native'
    //             )
    //             ->where('d.id', $id)
    //             ->whereNull('d.deleted_at_utc')
    //             ->first();

    //         DB::commit();
            
    //         return response()->json([
    //             'success' => true,
    //             'message' => 'Department created successfully',
    //             'data' => $department
    //         ], 201);
            
    //     } catch (\Exception $e) {
    //         DB::rollBack();
    //         return response()->json([
    //             'success' => false,
    //             'message' => config('app.debug') ? $e->getMessage() : 'Department creation failed'
    //         ], 500);
    //     } catch (QueryException $e) {
    //         DB::rollBack();
    //         if (isset($e->errorInfo[1]) && $e->errorInfo[1] === 1062) {
    //             return response()->json([
    //                 'success' => false,
    //                 'message' => 'Department code already exists for this company'
    //             ], 409);
    //         }
    //         return response()->json([
    //             'success' => false,
    //             'message' => config('app.debug') ? $e->getMessage() : 'Database error'
    //         ], 500);
    //     }
    // }
public function store(Request $request)
{
    DB::beginTransaction();
    
    try {
        $request->validate([
            'company_id' => 'required|integer|exists:companies,id',
            'name_en' => 'required|string|max:100',
            'code' => 'required|string|max:20',
            'is_active' => 'boolean',
            'status' => 'in:active,inactive,merged,dissolved'
        ]);

        // Validate foreign keys (only company and location for now)
        $company = DB::table('companies')->where('id', $request->input('company_id'))->whereNull('deleted_at_utc')->first();
        if (!$company) {
            return response()->json(['success' => false, 'message' => 'Company not found'], 404);
        }

        // Validate parent department (if provided)
        $parentId = $request->input('parent_id');
        $parentPath = '';
        $level = 1;
        
        if ($parentId) {
            $parent = DB::table('departments')
                ->where('id', $parentId)
                ->where('company_id', $request->input('company_id'))
                ->whereNull('deleted_at_utc')
                ->first();
                
            if (!$parent) {
                return response()->json(['success' => false, 'message' => 'Parent department not found or belongs to different company'], 404);
            }
            $parentPath = $parent->path;
            $level = $parent->level + 1;
        }

        // Validate location (if provided)
        if ($request->has('location_id') && $request->input('location_id') !== null) {
            $location = DB::table('company_locations')->where('id', $request->input('location_id'))->whereNull('deleted_at_utc')->first();
            if (!$location) {
                return response()->json(['success' => false, 'message' => 'Location not found'], 404);
            }
        }

        // Build path for hierarchical structure
        $path = $parentPath ? $parentPath . '.' . $request->input('code') : $request->input('code');

        // Check for duplicate code within company
        $existing = DB::table('departments')
            ->where('company_id', $request->input('company_id'))
            ->where('code', $request->input('code'))
            ->whereNull('deleted_at_utc')
            ->first();
            
        if ($existing) {
            return response()->json(['success' => false, 'message' => 'Department code already exists for this company'], 409);
        }

        $id = DB::table('departments')->insertGetId([
            'company_id' => $request->input('company_id'),
            'name_en' => $request->input('name_en'),
            'name_native' => $request->input('name_native'),
            'code' => strtoupper($request->input('code')),
            'description_en' => $request->input('description_en'),
            'description_native' => $request->input('description_native'),
            'parent_id' => $parentId,
            'current_head_id' => $request->input('current_head_id'), // Will be NULL if employees table doesn't exist
            'current_deputy_head_id' => $request->input('current_deputy_head_id'), // Will be NULL
            'location_id' => $request->input('location_id'),
            'budget' => $request->input('budget'),
            'employee_count' => $request->input('employee_count', 0),
            'level' => $level,
            'path' => $path,
            'is_active' => $request->boolean('is_active', true) ? 1 : 0,
            'status' => $request->input('status', 'active'),
            'created_at_utc' => Carbon::now('UTC'),
            'updated_at_utc' => Carbon::now('UTC'),
            // created_by/updated_by will be NULL (no employees table yet)
        ]);

        $department = DB::table('departments as d')
            ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
            ->leftJoin('companies as c', 'd.company_id', '=', 'c.id')
            ->leftJoin('company_locations as cl', 'd.location_id', '=', 'cl.id')
            ->select(
                'd.*',
                'parent.name_en as parent_name_en', 'parent.name_native as parent_name_native', 'parent.code as parent_code',
                'c.legal_name_en as company_name_en', 'c.legal_name_native as company_name_native', 'c.trade_name_en as company_trade_name_en',
                'cl.name_en as location_name_en', 'cl.name_native as location_name_native',
                'cl.address_line1_en as location_address_en', 'cl.address_line1_native as location_address_native'
            )
            ->where('d.id', $id)
            ->whereNull('d.deleted_at_utc')
            ->first();

        DB::commit();
        
        return response()->json([
            'success' => true,
            'message' => 'Department created successfully',
            'data' => $department
        ], 201);
        
    } catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'success' => false,
            'message' => config('app.debug') ? $e->getMessage() : 'Department creation failed'
        ], 500);
    } catch (QueryException $e) {
        DB::rollBack();
        if (isset($e->errorInfo[1]) && $e->errorInfo[1] === 1062) {
            return response()->json([
                'success' => false,
                'message' => 'Department code already exists for this company'
            ], 409);
        }
        return response()->json([
            'success' => false,
            'message' => config('app.debug') ? $e->getMessage() : 'Database error'
        ], 500);
    }
}
    // public function update(Request $request, int $id)
    // {
    //     DB::beginTransaction();
        
    //     try {
    //         $department = DB::table('departments')
    //             ->where('id', $id)
    //             ->whereNull('deleted_at_utc')
    //             ->first();

    //         if (!$department) {
    //             return response()->json(['success' => false, 'message' => 'Department not found'], 404);
    //         }

    //         // Validate required fields if being updated
    //         if ($request->has('name_en') && empty($request->input('name_en'))) {
    //             return response()->json(['success' => false, 'message' => 'Department name (English) is required'], 400);
    //         }

    //         // Validate company_id cannot be changed
    //         if ($request->has('company_id') && $request->input('company_id') != $department->company_id) {
    //             return response()->json(['success' => false, 'message' => 'Cannot change company for existing department'], 400);
    //         }

    //         // Validate parent department changes
    //         if ($request->has('parent_id')) {
    //             $newParentId = $request->input('parent_id');
    //             if ($newParentId && $newParentId != $department->parent_id) {
    //                 $parent = DB::table('departments')
    //                     ->where('id', $newParentId)
    //                     ->where('company_id', $department->company_id)
    //                     ->whereNull('deleted_at_utc')
    //                     ->first();
                        
    //                 if (!$parent) {
    //                     return response()->json(['success' => false, 'message' => 'Parent department not found or belongs to different company'], 404);
    //                 }
                    
    //                 // Recalculate path and level
    //                 $newPath = $parent->path . '.' . $department->code;
    //                 $newLevel = $parent->level + 1;
    //             } elseif (!$newParentId && $department->parent_id) {
    //                 // Removing parent - becomes root level
    //                 $newPath = $department->code;
    //                 $newLevel = 1;
    //             }
    //         }

    //         // Validate code uniqueness within company (if changing)
    //         if ($request->has('code') && strtoupper($request->input('code')) !== $department->code) {
    //             $existing = DB::table('departments')
    //                 ->where('company_id', $department->company_id)
    //                 ->where('code', strtoupper($request->input('code')))
    //                 ->where('id', '!=', $id)
    //                 ->whereNull('deleted_at_utc')
    //                 ->first();
                    
    //             if ($existing) {
    //                 return response()->json(['success' => false, 'message' => 'Department code already exists for this company'], 409);
    //             }
    //         }

    //         // Validate optional foreign keys
    //        // $this->validateOptionalForeignKeys($request);

    //         // Build update data
    //         $updateData = [
    //             'name_en' => $request->input('name_en', $department->name_en),
    //             'name_native' => $request->input('name_native', $department->name_native),
    //             'code' => strtoupper($request->input('code', $department->code)),
    //             'description_en' => $request->input('description_en', $department->description_en),
    //             'description_native' => $request->input('description_native', $department->description_native),
    //             'current_head_id' => $request->input('current_head_id', $department->current_head_id),
    //             'current_deputy_head_id' => $request->input('current_deputy_head_id', $department->current_deputy_head_id),
    //             'location_id' => $request->input('location_id', $department->location_id),
    //             'budget' => $request->input('budget', $department->budget),
    //             'employee_count' => $request->input('employee_count', $department->employee_count),
    //             'is_active' => $request->has('is_active') ? ($request->boolean('is_active') ? 1 : 0) : $department->is_active,
    //             'status' => $request->input('status', $department->status),
    //             'updated_at_utc' => Carbon::now('UTC'),
    //         ];

    //         // Handle parent changes
    //         if (isset($newPath)) {
    //             $updateData['parent_id'] = $request->input('parent_id');
    //             $updateData['path'] = $newPath;
    //             $updateData['level'] = $newLevel;
    //         }

    //         DB::table('departments')->where('id', $id)->update($updateData);

    //         $updatedDepartment = DB::table('departments as d')
    //             ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
    //             ->leftJoin('companies as c', 'd.company_id', '=', 'c.id')
    //             ->leftJoin('company_locations as cl', 'd.location_id', '=', 'cl.id')
    //             ->select(
    //                 'd.*',
    //                 'parent.name_en as parent_name_en', 'parent.name_native as parent_name_native', 'parent.code as parent_code',
    //                 'c.legal_name_en as company_name_en', 'c.legal_name_native as company_name_native', 'c.trade_name_en as company_trade_name_en',
    //                 'cl.name_en as location_name_en', 'cl.name_native as location_name_native',
    //                 'cl.address_line1_en as location_address_en', 'cl.address_line1_native as location_address_native'
    //             )
    //             ->where('d.id', $id)
    //             ->whereNull('d.deleted_at_utc')
    //             ->first();

    //         DB::commit();

    //         return response()->json([
    //             'success' => true,
    //             'message' => 'Department updated successfully',
    //             'data' => $updatedDepartment
    //         ]);

    //     } catch (\Exception $e) {
    //         DB::rollBack();
    //         return response()->json([
    //             'success' => false,
    //             'message' => config('app.debug') ? $e->getMessage() : 'Department update failed'
    //         ], 500);
    //     } catch (QueryException $e) {
    //         DB::rollBack();
    //         if (isset($e->errorInfo[1]) && $e->errorInfo[1] === 1062) {
    //             return response()->json([
    //                 'success' => false,
    //                 'message' => 'Department code already exists for this company'
    //             ], 409);
    //         }
    //         return response()->json([
    //             'success' => false,
    //             'message' => config('app.debug') ? $e->getMessage() : 'Database error'
    //         ], 500);
    //     }
    // }

    public function update(Request $request, int $id)
    {
        DB::beginTransaction();
        
        try {
            $department = DB::table('departments')
                ->where('id', $id)
                ->whereNull('deleted_at_utc')
                ->first();

            if (!$department) {
                return response()->json(['success' => false, 'message' => 'Department not found'], 404);
            }

            // Validate required fields if being updated
            if ($request->has('name_en') && empty($request->input('name_en'))) {
                return response()->json(['success' => false, 'message' => 'Department name (English) is required'], 400);
            }

            // Validate company_id cannot be changed
            if ($request->has('company_id') && $request->input('company_id') != $department->company_id) {
                return response()->json(['success' => false, 'message' => 'Cannot change company for existing department'], 400);
            }

            // Validate parent department changes
            if ($request->has('parent_id')) {
                $newParentId = $request->input('parent_id');
                if ($newParentId && $newParentId != $department->parent_id) {
                    $parent = DB::table('departments')
                        ->where('id', $newParentId)
                        ->where('company_id', $department->company_id)
                        ->whereNull('deleted_at_utc')
                        ->first();
                        
                    if (!$parent) {
                        return response()->json(['success' => false, 'message' => 'Parent department not found or belongs to different company'], 404);
                    }
                    
                    // Recalculate path and level
                    $newPath = $parent->path . '.' . $department->code;
                    $newLevel = $parent->level + 1;
                } elseif (!$newParentId && $department->parent_id) {
                    // Removing parent - becomes root level
                    $newPath = $department->code;
                    $newLevel = 1;
                }
            }

            // Validate location (if provided)
            if ($request->has('location_id') && $request->input('location_id') !== null) {
                $location = DB::table('company_locations')->where('id', $request->input('location_id'))->whereNull('deleted_at_utc')->first();
                if (!$location) {
                    return response()->json(['success' => false, 'message' => 'Location not found'], 404);
                }
            }

            // Validate code uniqueness within company (if changing)
            if ($request->has('code') && strtoupper($request->input('code')) !== $department->code) {
                $existing = DB::table('departments')
                    ->where('company_id', $department->company_id)
                    ->where('code', strtoupper($request->input('code')))
                    ->where('id', '!=', $id)
                    ->whereNull('deleted_at_utc')
                    ->first();
                    
                if ($existing) {
                    return response()->json(['success' => false, 'message' => 'Department code already exists for this company'], 409);
                }
            }

            // Build update data
            $updateData = [
                'name_en' => $request->input('name_en', $department->name_en),
                'name_native' => $request->input('name_native', $department->name_native),
                'code' => strtoupper($request->input('code', $department->code)),
                'description_en' => $request->input('description_en', $department->description_en),
                'description_native' => $request->input('description_native', $department->description_native),
                'current_head_id' => $request->input('current_head_id', $department->current_head_id), // Can be NULL
                'current_deputy_head_id' => $request->input('current_deputy_head_id', $department->current_deputy_head_id), // Can be NULL
                'location_id' => $request->input('location_id', $department->location_id),
                'budget' => $request->input('budget', $department->budget),
                'employee_count' => $request->input('employee_count', $department->employee_count),
                'is_active' => $request->has('is_active') ? ($request->boolean('is_active') ? 1 : 0) : $department->is_active,
                'status' => $request->input('status', $department->status),
                'updated_at_utc' => Carbon::now('UTC'),
            ];

            // Handle parent changes
            if (isset($newPath)) {
                $updateData['parent_id'] = $request->input('parent_id');
                $updateData['path'] = $newPath;
                $updateData['level'] = $newLevel;
            }

            DB::table('departments')->where('id', $id)->update($updateData);

            $updatedDepartment = DB::table('departments as d')
                ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
                ->leftJoin('companies as c', 'd.company_id', '=', 'c.id')
                ->leftJoin('company_locations as cl', 'd.location_id', '=', 'cl.id')
                ->select(
                    'd.*',
                    'parent.name_en as parent_name_en', 'parent.name_native as parent_name_native', 'parent.code as parent_code',
                    'c.legal_name_en as company_name_en', 'c.legal_name_native as company_name_native', 'c.trade_name_en as company_trade_name_en',
                    'cl.name_en as location_name_en', 'cl.name_native as location_name_native',
                    'cl.address_line1_en as location_address_en', 'cl.address_line1_native as location_address_native'
                )
                ->where('d.id', $id)
                ->whereNull('d.deleted_at_utc')
                ->first();

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Department updated successfully',
                'data' => $updatedDepartment
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Department update failed'
            ], 500);
        } catch (QueryException $e) {
            DB::rollBack();
            if (isset($e->errorInfo[1]) && $e->errorInfo[1] === 1062) {
                return response()->json([
                    'success' => false,
                    'message' => 'Department code already exists for this company'
                ], 409);
            }
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Database error'
            ], 500);
        }
    }
    private function validateOptionalForeignKeys(Request $request)
    {
        // Validate current_head_id (optional)
        if ($request->has('current_head_id') && $request->input('current_head_id') !== null) {
            $head = DB::table('employees')->where('id', $request->input('current_head_id'))->whereNull('deleted_at_utc')->first();
            if (!$head) {
                throw new \Exception('Department head not found');
            }
        }

        // Validate current_deputy_head_id (optional)
        if ($request->has('current_deputy_head_id') && $request->input('current_deputy_head_id') !== null) {
            $deputy = DB::table('employees')->where('id', $request->input('current_deputy_head_id'))->whereNull('deleted_at_utc')->first();
            if (!$deputy) {
                throw new \Exception('Department deputy head not found');
            }
        }

        // Validate location_id (optional)
        if ($request->has('location_id') && $request->input('location_id') !== null) {
            $location = DB::table('company_locations')->where('id', $request->input('location_id'))->whereNull('deleted_at_utc')->first();
            if (!$location) {
                throw new \Exception('Location not found');
            }
        }
    }

    public function activate(int $id)
    {
        $updated = DB::table('departments')
            ->where('id', $id)
            ->whereNull('deleted_at_utc')
            ->update(['is_active' => 1, 'status' => 'active', 'updated_at_utc' => Carbon::now('UTC')]);

        if (!$updated) {
            return response()->json(['success' => false, 'message' => 'Department not found'], 404);
        }

        $department = DB::table('departments as d')
            ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
            ->leftJoin('companies as c', 'd.company_id', '=', 'c.id')
            ->leftJoin('company_locations as cl', 'd.location_id', '=', 'cl.id')
            ->select(
                'd.*',
                'parent.name_en as parent_name_en', 'parent.name_native as parent_name_native', 'parent.code as parent_code',
                'c.legal_name_en as company_name_en', 'c.legal_name_native as company_name_native', 'c.trade_name_en as company_trade_name_en',
                'cl.name_en as location_name_en', 'cl.name_native as location_name_native',
                'cl.address_line1_en as location_address_en', 'cl.address_line1_native as location_address_native'
            )
            ->where('d.id', $id)
            ->whereNull('d.deleted_at_utc')
            ->first();
        
        return response()->json([
            'success' => true,
            'message' => 'Department activated',
            'data' => $department
        ]);
    }

    public function deactivate(int $id)
    {
        $updated = DB::table('departments')
            ->where('id', $id)
            ->whereNull('deleted_at_utc')
            ->update(['is_active' => 0, 'status' => 'inactive', 'updated_at_utc' => Carbon::now('UTC')]);

        if (!$updated) {
            return response()->json(['success' => false, 'message' => 'Department not found'], 404);
        }

        $department = DB::table('departments as d')
            ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
            ->leftJoin('companies as c', 'd.company_id', '=', 'c.id')
            ->leftJoin('company_locations as cl', 'd.location_id', '=', 'cl.id')
            ->select(
                'd.*',
                'parent.name_en as parent_name_en', 'parent.name_native as parent_name_native', 'parent.code as parent_code',
                'c.legal_name_en as company_name_en', 'c.legal_name_native as company_name_native', 'c.trade_name_en as company_trade_name_en',
                'cl.name_en as location_name_en', 'cl.name_native as location_name_native',
                'cl.address_line1_en as location_address_en', 'cl.address_line1_native as location_address_native'
            )
            ->where('d.id', $id)
            ->whereNull('d.deleted_at_utc')
            ->first();
        
        return response()->json([
            'success' => true,
            'message' => 'Department deactivated',
            'data' => $department
        ]);
    }

    public function destroy(int $id)
    {
        $department = DB::table('departments')
            ->where('id', $id)
            ->whereNull('deleted_at_utc')
            ->first();

        if (!$department) {
            return response()->json(['success' => false, 'message' => 'Department not found'], 404);
        }

        // Check if department has child departments
        $hasChildren = DB::table('departments')
            ->where('parent_id', $id)
            ->whereNull('deleted_at_utc')
            ->exists();

        if ($hasChildren) {
            return response()->json([
                'success' => false,
                'message' => 'Cannot delete department with child departments'
            ], 400);
        }

        DB::table('departments')
            ->where('id', $id)
            ->update(['deleted_at_utc' => Carbon::now('UTC')]);

        return response()->json([
            'success' => true,
            'message' => 'Department deleted successfully'
        ]);
    }

    /**
     * Get departments by company
     * GET /api/v1/companies/{companyId}/departments
     */
    public function byCompany(int $companyId)
    {
        $company = DB::table('companies')
            ->where('id', $companyId)
            ->whereNull('deleted_at_utc')
            ->first();

        if (!$company) {
            return response()->json(['success' => false, 'message' => 'Company not found'], 404);
        }

        $data = DB::table('departments as d')
            ->select(
                'd.id', 'd.name_en', 'd.name_native', 'd.code', 'd.parent_id',
                'd.current_head_id', 'd.location_id', 'd.employee_count',
                'd.level', 'd.is_active', 'd.status',
                'parent.name_en as parent_name_en', 'parent.name_native as parent_name_native'
            )
            ->leftJoin('departments as parent', 'd.parent_id', '=', 'parent.id')
            ->where('d.company_id', $companyId)
            ->whereNull('d.deleted_at_utc')
            ->where('d.status', '!=', 'dissolved')
            ->orderBy('d.path', 'asc')
            ->get();

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

    /**
     * Get child departments of a parent
     * GET /api/v1/departments/{parentId}/children
     */
    public function children(int $parentId)
    {
        $parent = DB::table('departments')
            ->where('id', $parentId)
            ->whereNull('deleted_at_utc')
            ->first();

        if (!$parent) {
            return response()->json(['success' => false, 'message' => 'Parent department not found'], 404);
        }

        $data = DB::table('departments as d')
            ->select(
                'd.id', 'd.name_en', 'd.name_native', 'd.code', 'd.description_en',
                'd.current_head_id', 'd.location_id', 'd.employee_count',
                'd.level', 'd.is_active', 'd.status'
            )
            ->where('d.parent_id', $parentId)
            ->whereNull('d.deleted_at_utc')
            ->where('d.status', '!=', 'dissolved')
            ->orderBy('d.name_en', 'asc')
            ->get();

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

    /**
     * Get full department hierarchy for a company
     * GET /api/v1/companies/{companyId}/departments/hierarchy
     */
    public function hierarchy(int $companyId)
    {
        $company = DB::table('companies')
            ->where('id', $companyId)
            ->whereNull('deleted_at_utc')
            ->first();

        if (!$company) {
            return response()->json(['success' => false, 'message' => 'Company not found'], 404);
        }

        // Get all departments ordered by path (hierarchical order)
        $departments = DB::table('departments')
            ->where('company_id', $companyId)
            ->whereNull('deleted_at_utc')
            ->where('status', '!=', 'dissolved')
            ->orderBy('path', 'asc')
            ->get();

        // Build hierarchical tree
        $tree = [];
        $lookup = [];

        foreach ($departments as $dept) {
            $dept->children = [];
            $lookup[$dept->id] = &$dept;
            $tree[] = &$dept;
        }

        foreach ($departments as $dept) {
            if ($dept->parent_id && isset($lookup[$dept->parent_id])) {
                $lookup[$dept->parent_id]->children[] = &$lookup[$dept->id];
            }
        }

        // Remove references and return only root departments
        $result = [];
        foreach ($tree as $dept) {
            if (!$dept->parent_id) {
                $result[] = $dept;
            }
        }

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