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

    /**
     * List all active timezones
     * GET /api/v1/timezones
     */
    public function index()
    {
        $data = DB::table('timezones')
            // 1. Select only the columns the frontend needs
            ->select('id', 'iana_name', 'display_name_en', 'display_name_native', 'utc_offset', 'utc_offset_minutes', 'observes_dst', 'is_default', 'country_codes')
            
            // 2. Manually handle Soft Deletes
            ->whereNull('deleted_at_utc')
            
            // 3. Filter only Active timezones
            ->where('is_active', 1)
            ->where('status', 'active')
            
            // 4. Order by UTC offset then name
            ->orderBy('utc_offset_minutes', 'asc')
            ->orderBy('iana_name', 'asc')
            
            ->get();

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

    /**
     * Create a new timezone
     * POST /api/v1/timezones
     */
    public function store(Request $request)
    {
        try {
            // Prepare country_codes as JSON
            $countryCodes = $request->input('country_codes');
            if (is_array($countryCodes)) {
                $countryCodes = json_encode($countryCodes);
            }

            $id = DB::table('timezones')->insertGetId([
                'iana_name' => $request->input('iana_name'),
                'display_name_en' => $request->input('display_name_en'),
                'display_name_native' => $request->input('display_name_native'),
                'utc_offset' => $request->input('utc_offset'),
                'utc_offset_minutes' => $request->input('utc_offset_minutes'),
                'observes_dst' => $request->boolean('observes_dst', false) ? 1 : 0,
                'country_codes' => $countryCodes,
                'is_active' => $request->boolean('is_active', true) ? 1 : 0,
                'is_default' => $request->boolean('is_default', false) ? 1 : 0,
                'status' => $request->input('status', 'active'),
                'created_at_utc' => Carbon::now('UTC')->toDateTimeString(),
                'updated_at_utc' => Carbon::now('UTC')->toDateTimeString(),
            ]);

            $timezone = DB::table('timezones')->where('id', $id)->first();
            
            return response()->json([
                'success' => true,
                'message' => 'Timezone created successfully',
                'data' => $timezone
            ], 201);
            
        } catch (QueryException $e) {
            // Handle duplicate iana_name (MySQL error 1062)
            if (isset($e->errorInfo[1]) && $e->errorInfo[1] === 1062) {
                return response()->json([
                    'success' => false,
                    'message' => 'Timezone IANA name already exists'
                ], 409);
            }
            return response()->json([
                'success' => false,
                'message' => config('app.debug') ? $e->getMessage() : 'Database error'
            ], 500);
        }
    }

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

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

            // 2. Prepare country_codes as JSON
            $countryCodes = $request->input('country_codes');
            if (is_array($countryCodes)) {
                $countryCodes = json_encode($countryCodes);
            } elseif ($countryCodes === null) {
                $countryCodes = $timezone->country_codes;
            }

            // 3. Prepare update data
            $updateData = [
                'iana_name' => $request->input('iana_name', $timezone->iana_name),
                'display_name_en' => $request->input('display_name_en', $timezone->display_name_en),
                'display_name_native' => $request->input('display_name_native', $timezone->display_name_native),
                'utc_offset' => $request->input('utc_offset', $timezone->utc_offset),
                'utc_offset_minutes' => $request->input('utc_offset_minutes', $timezone->utc_offset_minutes),
                'observes_dst' => $request->has('observes_dst') ? ($request->boolean('observes_dst') ? 1 : 0) : $timezone->observes_dst,
                'country_codes' => $countryCodes,
                'is_active' => $request->has('is_active') ? ($request->boolean('is_active') ? 1 : 0) : $timezone->is_active,
                'is_default' => $request->has('is_default') ? ($request->boolean('is_default') ? 1 : 0) : $timezone->is_default,
                'status' => $request->input('status', $timezone->status),
                'updated_at_utc' => Carbon::now('UTC')->toDateTimeString(),
                // 'updated_by' => auth()->id(), // Uncomment if you have authentication
            ];

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

            // 5. Fetch updated record
            $updatedTimezone = DB::table('timezones')
                ->where('id', $id)
                ->whereNull('deleted_at_utc')
                ->first();

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

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

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

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

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

        $timezone = DB::table('timezones')->where('id', $id)->first();
        
        return response()->json([
            'success' => true,
            'message' => 'Timezone activated',
            'data' => $timezone
        ]);
    }

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

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

        $timezone = DB::table('timezones')->where('id', $id)->first();
        
        return response()->json([
            'success' => true,
            'message' => 'Timezone deactivated',
            'data' => $timezone
        ]);
    }

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

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

        // Perform soft delete
        DB::table('timezones')
            ->where('id', $id)
            ->update([
                'deleted_at_utc' => Carbon::now('UTC')->toDateTimeString(),
                // 'deleted_by' => auth()->id(), // Uncomment if you have authentication
            ]);

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