<?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 CompanyLocationController extends Controller
{
    public function demo()
    {
        return response()->json([
            'success' => true,
            'message' => 'Company Location API is working'
        ]);
    }

    public function index(Request $request)
    {
        $query = DB::table('company_locations as cl')
            ->select(
                // ALL company location fields from your table
                'cl.id',
                'cl.company_id',
                'cl.name_en',
                'cl.name_native',
                'cl.location_type',
                'cl.address_line1_en',
                'cl.address_line1_native',
                'cl.address_line2_en',
                'cl.address_line2_native',
                'cl.city_id',
                'cl.postal_code',
                'cl.country_id',
                'cl.phone',
                'cl.email',
                'cl.manager_id',
                'cl.capacity',
                'cl.facilities_en',
                'cl.facilities_native',
                'cl.is_primary',
                'cl.status',
                'cl.created_at_utc',
                'cl.updated_at_utc',
                'cl.created_by',
                'cl.updated_by',
                'cl.deleted_at_utc',
                'cl.deleted_by',
                
                // City details (both languages)
                'ci.name_en as city_name_en',
                'ci.name_native as city_name_native',
                
                // Country details (both languages)
                'co.name_en as country_name_en',
                'co.name_native as country_name_native',
                'co.code as country_code',
                
                // Company details (both languages)
                'c.legal_name_en as company_legal_name_en',
                'c.legal_name_native as company_legal_name_native',
                'c.trade_name_en as company_trade_name_en',
                'c.trade_name_native as company_trade_name_native'
            )
            ->leftJoin('cities as ci', 'cl.city_id', '=', 'ci.id')
            ->leftJoin('countries as co', 'cl.country_id', '=', 'co.id')
            ->leftJoin('companies as c', 'cl.company_id', '=', 'c.id')
            ->whereNull('cl.deleted_at_utc')
            ->where('cl.status', '!=', 'closed')
            ->orderBy('cl.name_en', 'asc');

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

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

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

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

        $data = $query->get();

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

    public function show(int $id)
    {
        $location = DB::table('company_locations as cl')
            ->select(
                // ALL company location fields from your table
                'cl.id',
                'cl.company_id',
                'cl.name_en',
                'cl.name_native',
                'cl.location_type',
                'cl.address_line1_en',
                'cl.address_line1_native',
                'cl.address_line2_en',
                'cl.address_line2_native',
                'cl.city_id',
                'cl.postal_code',
                'cl.country_id',
                'cl.phone',
                'cl.email',
                'cl.manager_id',
                'cl.capacity',
                'cl.facilities_en',
                'cl.facilities_native',
                'cl.is_primary',
                'cl.status',
                'cl.created_at_utc',
                'cl.updated_at_utc',
                'cl.created_by',
                'cl.updated_by',
                'cl.deleted_at_utc',
                'cl.deleted_by',
                
                // City details (both languages)
                'ci.name_en as city_name_en',
                'ci.name_native as city_name_native',
                
                // Country details (both languages)
                'co.name_en as country_name_en',
                'co.name_native as country_name_native',
                'co.code as country_code',
                
                // Company details (both languages)
                'c.legal_name_en as company_legal_name_en',
                'c.legal_name_native as company_legal_name_native',
                'c.trade_name_en as company_trade_name_en',
                'c.trade_name_native as company_trade_name_native'
            )
            ->leftJoin('cities as ci', 'cl.city_id', '=', 'ci.id')
            ->leftJoin('countries as co', 'cl.country_id', '=', 'co.id')
            ->leftJoin('companies as c', 'cl.company_id', '=', 'c.id')
            ->where('cl.id', $id)
            ->whereNull('cl.deleted_at_utc')
            ->first();

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

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

    public function store(Request $request)
    {
        DB::beginTransaction();
        
        try {
            $request->validate([
                'company_id' => 'required|integer|exists:companies,id',
                'name_en' => 'required|string|max:200',
                'location_type' => 'required|in:headquarters,branch,office,warehouse,factory,remote',
                'address_line1_en' => 'required|string|max:255',
                'city_id' => 'required|integer|exists:cities,id',
                'country_id' => 'required|integer|exists:countries,id',
                'is_primary' => 'boolean',
                'status' => 'in:active,inactive,closed'
            ]);

            // Validate foreign keys exist and are not soft-deleted
            $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);
            }

            $city = DB::table('cities')->where('id', $request->input('city_id'))->whereNull('deleted_at_utc')->first();
            if (!$city) {
                return response()->json(['success' => false, 'message' => 'City not found'], 404);
            }

            $country = DB::table('countries')->where('id', $request->input('country_id'))->whereNull('deleted_at_utc')->first();
            if (!$country) {
                return response()->json(['success' => false, 'message' => 'Country not found'], 404);
            }

            // Handle manager validation (optional - uncomment if employees table exists)
            // if ($request->has('manager_id') && $request->input('manager_id') !== null) {
            //     $manager = DB::table('employees')->where('id', $request->input('manager_id'))->whereNull('deleted_at_utc')->first();
            //     if (!$manager) {
            //         return response()->json(['success' => false, 'message' => 'Manager not found'], 404);
            //     }
            // }

            // Auto-set is_primary logic
            $isPrimary = $request->boolean('is_primary', false);
            if (!$isPrimary) {
                // Check if this company has any existing primary location
                $existingPrimary = DB::table('company_locations')
                    ->where('company_id', $request->input('company_id'))
                    ->where('is_primary', 1)
                    ->whereNull('deleted_at_utc')
                    ->exists();
                if (!$existingPrimary) {
                    $isPrimary = true; // First location becomes primary automatically
                }
            } else {
                // If setting as primary, unset other primary locations for this company
                DB::table('company_locations')
                    ->where('company_id', $request->input('company_id'))
                    ->where('is_primary', 1)
                    ->whereNull('deleted_at_utc')
                    ->update(['is_primary' => 0, 'updated_at_utc' => Carbon::now('UTC')]);
            }

            $id = DB::table('company_locations')->insertGetId([
                'company_id' => $request->input('company_id'),
                'name_en' => $request->input('name_en'),
                'name_native' => $request->input('name_native'),
                'location_type' => $request->input('location_type'),
                'address_line1_en' => $request->input('address_line1_en'),
                'address_line1_native' => $request->input('address_line1_native'),
                'address_line2_en' => $request->input('address_line2_en'),
                'address_line2_native' => $request->input('address_line2_native'),
                'city_id' => $request->input('city_id'),
                'postal_code' => $request->input('postal_code'),
                'country_id' => $request->input('country_id'),
                'phone' => $request->input('phone'),
                'email' => $request->input('email'),
                'manager_id' => $request->input('manager_id'),
                'capacity' => $request->input('capacity'),
                'facilities_en' => $request->input('facilities_en'),
                'facilities_native' => $request->input('facilities_native'),
                'is_primary' => $isPrimary ? 1 : 0,
                'status' => $request->input('status', 'active'),
                'created_at_utc' => Carbon::now('UTC'),
                'updated_at_utc' => Carbon::now('UTC'),
            ]);

            // Fetch complete location data with master records
            $location = DB::table('company_locations as cl')
                ->leftJoin('cities as ci', 'cl.city_id', '=', 'ci.id')
                ->leftJoin('countries as co', 'cl.country_id', '=', 'co.id')
                ->leftJoin('companies as c', 'cl.company_id', '=', 'c.id')
                ->select(
                    'cl.*',
                    'ci.name_en as city_name_en', 'ci.name_native as city_name_native',
                    'co.name_en as country_name_en', 'co.name_native as country_name_native', 'co.code as country_code',
                    'c.legal_name_en as company_legal_name_en', 'c.legal_name_native as company_legal_name_native',
                    'c.trade_name_en as company_trade_name_en', 'c.trade_name_native as company_trade_name_native'
                )
                ->where('cl.id', $id)
                ->whereNull('cl.deleted_at_utc')
                ->first();

            DB::commit();
            
            return response()->json([
                'success' => true,
                'message' => 'Company location created successfully',
                'data' => $location
            ], 201);
            
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Company location creation failed'
            ], 500);
        } catch (QueryException $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Database error'
            ], 500);
        }
    }

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

            if (!$location) {
                return response()->json(['success' => false, 'message' => 'Company location 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' => 'Location name (English) is required'], 400);
            }
            if ($request->has('address_line1_en') && empty($request->input('address_line1_en'))) {
                return response()->json(['success' => false, 'message' => 'Address line 1 (English) is required'], 400);
            }

            // Validate foreign keys if being updated
            if ($request->has('company_id')) {
                $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);
                }
            }

            if ($request->has('city_id')) {
                $city = DB::table('cities')->where('id', $request->input('city_id'))->whereNull('deleted_at_utc')->first();
                if (!$city) {
                    return response()->json(['success' => false, 'message' => 'City not found'], 404);
                }
            }

            if ($request->has('country_id')) {
                $country = DB::table('countries')->where('id', $request->input('country_id'))->whereNull('deleted_at_utc')->first();
                if (!$country) {
                    return response()->json(['success' => false, 'message' => 'Country not found'], 404);
                }
            }

            // Handle manager validation (optional)
            // if ($request->has('manager_id') && $request->input('manager_id') !== null) {
            //     $manager = DB::table('employees')->where('id', $request->input('manager_id'))->whereNull('deleted_at_utc')->first();
            //     if (!$manager) {
            //         return response()->json(['success' => false, 'message' => 'Manager not found'], 404);
            //     }
            // }

            // Handle primary location logic
            if ($request->has('is_primary')) {
                $isPrimary = $request->boolean('is_primary');
                if ($isPrimary && !$location->is_primary) {
                    // Set this as primary and unset others for this company
                    DB::table('company_locations')
                        ->where('company_id', $location->company_id)
                        ->where('is_primary', 1)
                        ->whereNull('deleted_at_utc')
                        ->update(['is_primary' => 0, 'updated_at_utc' => Carbon::now('UTC')]);
                } elseif (!$isPrimary && $location->is_primary) {
                    // Unset primary - auto-assign to another location if exists
                    $otherLocation = DB::table('company_locations')
                        ->where('company_id', $location->company_id)
                        ->where('id', '!=', $id)
                        ->whereNull('deleted_at_utc')
                        ->orderBy('is_primary', 'desc') // Prefer existing primary
                        ->orderBy('created_at_utc', 'asc') // Otherwise oldest
                        ->first();
                    if ($otherLocation) {
                        DB::table('company_locations')
                            ->where('id', $otherLocation->id)
                            ->update(['is_primary' => 1, 'updated_at_utc' => Carbon::now('UTC')]);
                    }
                }
            }

            // Build update data array
            $updateData = [
                'updated_at_utc' => Carbon::now('UTC')
            ];

            $fields = [
                'company_id', 'name_en', 'name_native', 'location_type',
                'address_line1_en', 'address_line1_native', 'address_line2_en', 'address_line2_native',
                'city_id', 'postal_code', 'country_id', 'phone', 'email', 'manager_id',
                'capacity', 'facilities_en', 'facilities_native', 'status'
            ];

            foreach ($fields as $field) {
                if ($request->has($field)) {
                    $updateData[$field] = $request->input($field);
                }
            }

            if ($request->has('is_primary')) {
                $updateData['is_primary'] = $request->boolean('is_primary') ? 1 : 0;
            }

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

            // Fetch updated location with master data
            $updatedLocation = DB::table('company_locations as cl')
                ->leftJoin('cities as ci', 'cl.city_id', '=', 'ci.id')
                ->leftJoin('countries as co', 'cl.country_id', '=', 'co.id')
                ->leftJoin('companies as c', 'cl.company_id', '=', 'c.id')
                ->select(
                    'cl.*',
                    'ci.name_en as city_name_en', 'ci.name_native as city_name_native',
                    'co.name_en as country_name_en', 'co.name_native as country_name_native', 'co.code as country_code',
                    'c.legal_name_en as company_legal_name_en', 'c.legal_name_native as company_legal_name_native',
                    'c.trade_name_en as company_trade_name_en', 'c.trade_name_native as company_trade_name_native'
                )
                ->where('cl.id', $id)
                ->whereNull('cl.deleted_at_utc')
                ->first();

            DB::commit();

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

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Company location update failed'
            ], 500);
        } catch (QueryException $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Database error'
            ], 500);
        }
    }

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

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

        $location = DB::table('company_locations as cl')
            ->leftJoin('cities as ci', 'cl.city_id', '=', 'ci.id')
            ->leftJoin('countries as co', 'cl.country_id', '=', 'co.id')
            ->leftJoin('companies as c', 'cl.company_id', '=', 'c.id')
            ->select(
                'cl.*',
                'ci.name_en as city_name_en', 'ci.name_native as city_name_native',
                'co.name_en as country_name_en', 'co.name_native as country_name_native', 'co.code as country_code',
                'c.legal_name_en as company_legal_name_en', 'c.legal_name_native as company_legal_name_native',
                'c.trade_name_en as company_trade_name_en', 'c.trade_name_native as company_trade_name_native'
            )
            ->where('cl.id', $id)
            ->whereNull('cl.deleted_at_utc')
            ->first();
        
        return response()->json([
            'success' => true,
            'message' => 'Company location activated',
            'data' => $location
        ]);
    }

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

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

        $location = DB::table('company_locations as cl')
            ->leftJoin('cities as ci', 'cl.city_id', '=', 'ci.id')
            ->leftJoin('countries as co', 'cl.country_id', '=', 'co.id')
            ->leftJoin('companies as c', 'cl.company_id', '=', 'c.id')
            ->select(
                'cl.*',
                'ci.name_en as city_name_en', 'ci.name_native as city_name_native',
                'co.name_en as country_name_en', 'co.name_native as country_name_native', 'co.code as country_code',
                'c.legal_name_en as company_legal_name_en', 'c.legal_name_native as company_legal_name_native',
                'c.trade_name_en as company_trade_name_en', 'c.trade_name_native as company_trade_name_native'
            )
            ->where('cl.id', $id)
            ->whereNull('cl.deleted_at_utc')
            ->first();
        
        return response()->json([
            'success' => true,
            'message' => 'Company location deactivated',
            'data' => $location
        ]);
    }

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

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

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

        return response()->json([
            'success' => true,
            'message' => 'Company location deleted successfully (soft delete)'
        ]);
    }

    /**
     * Get all locations for a specific company
     * GET /api/v1/companies/{companyId}/locations
     */
    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('company_locations as cl')
            ->select(
                'cl.id', 'cl.name_en', 'cl.name_native', 'cl.location_type',
                'cl.address_line1_en', 'cl.address_line1_native', 'cl.city_id', 'cl.country_id',
                'cl.phone', 'cl.email', 'cl.is_primary', 'cl.status',
                'ci.name_en as city_name_en', 'ci.name_native as city_name_native',
                'co.name_en as country_name_en', 'co.name_native as country_name_native',
                'co.code as country_code'
            )
            ->leftJoin('cities as ci', 'cl.city_id', '=', 'ci.id')
            ->leftJoin('countries as co', 'cl.country_id', '=', 'co.id')
            ->where('cl.company_id', $companyId)
            ->whereNull('cl.deleted_at_utc')
            ->where('cl.status', '!=', 'closed')
            ->orderBy('cl.is_primary', 'desc') // Primary first
            ->orderBy('cl.name_en', 'asc')
            ->get();

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

    /**
     * Set a location as primary for its company
     * PATCH /api/v1/company-locations/{id}/set-primary
     */
    public function setPrimary(int $id)
    {
        DB::beginTransaction();
        
        try {
            $location = DB::table('company_locations')
                ->where('id', $id)
                ->whereNull('deleted_at_utc')
                ->first();

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

            // Unset current primary location for this company
            DB::table('company_locations')
                ->where('company_id', $location->company_id)
                ->where('is_primary', 1)
                ->whereNull('deleted_at_utc')
                ->update(['is_primary' => 0, 'updated_at_utc' => Carbon::now('UTC')]);

            // Set this location as primary
            DB::table('company_locations')
                ->where('id', $id)
                ->update(['is_primary' => 1, 'updated_at_utc' => Carbon::now('UTC')]);

            // Fetch updated location with master data
            $updatedLocation = DB::table('company_locations as cl')
                ->leftJoin('cities as ci', 'cl.city_id', '=', 'ci.id')
                ->leftJoin('countries as co', 'cl.country_id', '=', 'co.id')
                ->leftJoin('companies as c', 'cl.company_id', '=', 'c.id')
                ->select(
                    'cl.*',
                    'ci.name_en as city_name_en', 'ci.name_native as city_name_native',
                    'co.name_en as country_name_en', 'co.name_native as country_name_native', 'co.code as country_code',
                    'c.legal_name_en as company_legal_name_en', 'c.legal_name_native as company_legal_name_native',
                    'c.trade_name_en as company_trade_name_en', 'c.trade_name_native as company_trade_name_native'
                )
                ->where('cl.id', $id)
                ->whereNull('cl.deleted_at_utc')
                ->first();

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Primary location set successfully',
                'data' => $updatedLocation
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Failed to set primary location'
            ], 500);
        }
    }
}