<?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 RegionController extends Controller
{
    /**
     * Test endpoint
     * GET /api/v1/regions/demo
     */
    public function demo()
    {
        return response()->json([
            'success' => true,
            'message' => 'Region API is working'
        ]);
    }

    /**
     * List all active regions (with country name)
     * GET /api/v1/regions
     */
    public function index(Request $request)
    {
        $query = DB::table('regions')
            // Join with countries table to get country name
            ->leftJoin('countries', 'regions.country_id', '=', 'countries.id')
            
            // 1. Select region columns + country name
            ->select(
                'regions.id', 
                'regions.country_id', 
                'regions.name_en', 
                'regions.name_native', 
                'regions.code', 
                'regions.status',
                'countries.name_en as country_name_en',
                'countries.name_native as country_name_native',
                'countries.code as country_code'
            )
            
            // 2. Manually handle Soft Deletes (regions)
            ->whereNull('regions.deleted_at_utc')
            
            // 3. Also ensure country is not deleted
            ->whereNull('countries.deleted_at_utc')
            
            // 4. Filter only Active regions
            ->where('regions.status', 'active')
            
            // 5. Order by name
            ->orderBy('regions.name_en', 'asc');

        // Optional: Filter by country_id
        if ($request->has('country_id')) {
            $query->where('regions.country_id', $request->input('country_id'));
        }

        $data = $query->get();

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

    /**
     * Get region by ID (with country name)
     * GET /api/v1/regions/{id}
     */
    public function show(int $id)
    {
        // 1. Check if region exists and is not soft-deleted
        $region = DB::table('regions')
            // Join with countries table to get country name
            ->leftJoin('countries', 'regions.country_id', '=', 'countries.id')
            
            ->select(
                'regions.id', 
                'regions.country_id', 
                'regions.name_en', 
                'regions.name_native', 
                'regions.code', 
                'regions.status',
                'countries.name_en as country_name_en',
                'countries.name_native as country_name_native',
                'countries.code as country_code'
            )
            ->where('regions.id', $id)
            ->whereNull('regions.deleted_at_utc')
            ->whereNull('countries.deleted_at_utc')
            ->first();

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

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

    /**
     * Create a new region
     * POST /api/v1/regions
     */
    public function store(Request $request)
    {
        try {
            // Validate country exists
            $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);
            }

            $id = DB::table('regions')->insertGetId([
                'country_id' => $request->input('country_id'),
                'name_en' => $request->input('name_en'),
                'name_native' => $request->input('name_native'),
                'code' => $request->input('code'),
                'status' => $request->input('status', 'active'),
                'created_at_utc' => Carbon::now('UTC')->toDateTimeString(),
                'updated_at_utc' => Carbon::now('UTC')->toDateTimeString(),
            ]);

            // Fetch with country name
            $region = DB::table('regions')
                ->leftJoin('countries', 'regions.country_id', '=', 'countries.id')
                ->select(
                    'regions.id', 
                    'regions.country_id', 
                    'regions.name_en', 
                    'regions.name_native', 
                    'regions.code', 
                    'regions.status',
                    'countries.name_en as country_name_en',
                    'countries.name_native as country_name_native',
                    'countries.code as country_code'
                )
                ->where('regions.id', $id)
                ->first();
            
            return response()->json([
                'success' => true,
                'message' => 'Region created successfully',
                'data' => $region
            ], 201);
            
        } catch (QueryException $e) {
            // Handle duplicate code (MySQL error 1062)
            if (isset($e->errorInfo[1]) && $e->errorInfo[1] === 1062) {
                return response()->json([
                    'success' => false,
                    'message' => 'Region code already exists'
                ], 409);
            }
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Database error'
            ], 500);
        }
    }

    /**
     * Update an existing region
     * PUT /api/v1/regions/{id}
     */
    public function update(Request $request, int $id)
    {
        try {
            // 1. Check if region exists and is not soft-deleted
            $region = DB::table('regions')
                ->where('id', $id)
                ->whereNull('deleted_at_utc')
                ->first();

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

            // 2. Validate country exists if being updated
            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);
                }
            }

            // 3. Prepare update data
            $updateData = [
                'country_id' => $request->input('country_id', $region->country_id),
                'name_en' => $request->input('name_en', $region->name_en),
                'name_native' => $request->input('name_native', $region->name_native),
                'code' => $request->input('code', $region->code),
                'status' => $request->input('status', $region->status),
                'updated_at_utc' => Carbon::now('UTC')->toDateTimeString(),
            ];

            // 4. Perform update
            $updated = DB::table('regions')
                ->where('id', $id)
                ->update($updateData);

            // 5. Fetch updated record with country name
            $updatedRegion = DB::table('regions')
                ->leftJoin('countries', 'regions.country_id', '=', 'countries.id')
                ->select(
                    'regions.id', 
                    'regions.country_id', 
                    'regions.name_en', 
                    'regions.name_native', 
                    'regions.code', 
                    'regions.status',
                    'countries.name_en as country_name_en',
                    'countries.name_native as country_name_native',
                    'countries.code as country_code'
                )
                ->where('regions.id', $id)
                ->whereNull('regions.deleted_at_utc')
                ->first();

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

        } catch (QueryException $e) {
            // Handle duplicate code (MySQL error 1062)
            if (isset($e->errorInfo[1]) && $e->errorInfo[1] === 1062) {
                return response()->json([
                    'success' => false,
                    'message' => 'Region code already exists'
                ], 409);
            }

            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Database error'
            ], 500);
        }
    }

    /**
     * Activate region (set status=active)
     * PATCH /api/v1/regions/{id}/activate
     */
    public function activate(int $id)
    {
        $updated = DB::table('regions')
            ->where('id', $id)
            ->whereNull('deleted_at_utc')
            ->update(['status' => 'active']);

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

        $region = DB::table('regions')
            ->leftJoin('countries', 'regions.country_id', '=', 'countries.id')
            ->select(
                'regions.id', 
                'regions.country_id', 
                'regions.name_en', 
                'regions.name_native', 
                'regions.code', 
                'regions.status',
                'countries.name_en as country_name_en',
                'countries.name_native as country_name_native',
                'countries.code as country_code'
            )
            ->where('regions.id', $id)
            ->first();
        
        return response()->json([
            'success' => true,
            'message' => 'Region activated',
            'data' => $region
        ]);
    }

    /**
     * Deactivate region (set status=inactive)
     * PATCH /api/v1/regions/{id}/deactivate
     */
    public function deactivate(int $id)
    {
        $updated = DB::table('regions')
            ->where('id', $id)
            ->whereNull('deleted_at_utc')
            ->update(['status' => 'inactive']);

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

        $region = DB::table('regions')
            ->leftJoin('countries', 'regions.country_id', '=', 'countries.id')
            ->select(
                'regions.id', 
                'regions.country_id', 
                'regions.name_en', 
                'regions.name_native', 
                'regions.code', 
                'regions.status',
                'countries.name_en as country_name_en',
                'countries.name_native as country_name_native',
                'countries.code as country_code'
            )
            ->where('regions.id', $id)
            ->first();
        
        return response()->json([
            'success' => true,
            'message' => 'Region deactivated',
            'data' => $region
        ]);
    }

    /**
     * Soft delete a region
     * DELETE /api/v1/regions/{id}
     */
    public function destroy(int $id)
    {
        // Check if exists and not already deleted
        $region = DB::table('regions')
            ->where('id', $id)
            ->whereNull('deleted_at_utc')
            ->first();

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

        // Perform soft delete
        DB::table('regions')
            ->where('id', $id)
            ->update([
                'deleted_at_utc' => Carbon::now('UTC')->toDateTimeString(),
            ]);

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

    /**
     * Get regions by country (with country name)
     * GET /api/v1/countries/{countryId}/regions
     */
    public function byCountry(int $countryId)
    {
        // Check if country exists
        $country = DB::table('countries')
            ->where('id', $countryId)
            ->whereNull('deleted_at_utc')
            ->first();

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

        $data = DB::table('regions')
            ->leftJoin('countries', 'regions.country_id', '=', 'countries.id')
            ->select(
                'regions.id', 
                'regions.country_id', 
                'regions.name_en', 
                'regions.name_native', 
                'regions.code', 
                'regions.status',
                'countries.name_en as country_name_en',
                'countries.name_native as country_name_native',
                'countries.code as country_code'
            )
            ->where('regions.country_id', $countryId)
            ->whereNull('regions.deleted_at_utc')
            ->whereNull('countries.deleted_at_utc')
            ->where('regions.status', 'active')
            ->orderBy('regions.name_en', 'asc')
            ->get();

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