<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * Migrasi data dari offices dan business_trip_locations ke locations
     */
    public function up(): void
    {
        // 1. Migrasi data dari offices
        if (Schema::hasTable('offices')) {
            $offices = DB::table('offices')->get();

            foreach ($offices as $office) {
                // Skip jika company_id null
                if (empty($office->company_id)) {
                    continue;
                }

                // Tentukan category berdasarkan type
                $category = in_array($office->type, ['headquarters', 'branch'])
                    ? 'internal'
                    : 'external';

                // Internal locations: allow_daily_checkin = true
                // External locations dari office table juga allow_daily_checkin = true (karena sebelumnya dipakai untuk daily)
                $allowDailyCheckin = true;

                // Semua lokasi bisa jadi tujuan dinas
                $allowBusinessTrip = true;

                // Radius default berdasarkan category
                $radius = $office->radius ?? ($category === 'internal' ? 100 : 500);

                DB::table('locations')->insert([
                    'company_id' => $office->company_id,
                    'name' => $office->name,
                    'code' => $office->code,
                    'address' => $office->address,
                    'city' => $office->city,
                    'province' => $office->province,
                    'latitude' => $office->latitude,
                    'longitude' => $office->longitude,
                    'radius' => $radius,
                    'category' => $category,
                    'type' => $office->type ?? 'other',
                    'allow_daily_checkin' => $allowDailyCheckin,
                    'allow_business_trip' => $allowBusinessTrip,
                    'is_active' => $office->is_active ?? true,
                    'created_at' => $office->created_at ?? now(),
                    'updated_at' => $office->updated_at ?? now(),
                ]);
            }
        }

        // 2. Migrasi data dari business_trip_locations (skip duplicates)
        if (Schema::hasTable('business_trip_locations')) {
            $businessTripLocations = DB::table('business_trip_locations')->get();

            foreach ($businessTripLocations as $btl) {
                // Skip jika company_id null
                if (empty($btl->company_id)) {
                    continue;
                }

                // Cek apakah sudah ada lokasi dengan nama dan company yang sama
                $exists = DB::table('locations')
                    ->where('company_id', $btl->company_id)
                    ->where('name', $btl->name)
                    ->exists();

                if (!$exists) {
                    // Tentukan category (semua BTL adalah external)
                    $category = 'external';

                    // BTL tidak untuk daily checkin, hanya untuk dinas
                    $allowDailyCheckin = false;
                    $allowBusinessTrip = true;

                    // Radius default untuk external = 500m
                    $radius = $btl->radius ?? 500;

                    DB::table('locations')->insert([
                        'company_id' => $btl->company_id,
                        'name' => $btl->name,
                        'code' => $btl->code ?? null,
                        'address' => $btl->address ?? null,
                        'city' => $btl->city ?? null,
                        'province' => $btl->province ?? null,
                        'latitude' => $btl->latitude ?? null,
                        'longitude' => $btl->longitude ?? null,
                        'radius' => $radius,
                        'category' => $category,
                        'type' => $btl->type ?? 'other',
                        'allow_daily_checkin' => $allowDailyCheckin,
                        'allow_business_trip' => $allowBusinessTrip,
                        'is_active' => $btl->is_active ?? true,
                        'created_at' => $btl->created_at ?? now(),
                        'updated_at' => $btl->updated_at ?? now(),
                    ]);
                }
            }
        }

        // 3. Tambah kolom location_id ke tabel attendances untuk referensi baru
        if (Schema::hasTable('attendances') && !Schema::hasColumn('attendances', 'location_id')) {
            Schema::table('attendances', function ($table) {
                $table->foreignId('location_id')->nullable()->after('office_id');
            });

            // Migrasi office_id ke location_id
            // Mapping old office_id to new location_id berdasarkan name + company_id
            $attendancesWithOffice = DB::table('attendances')
                ->whereNotNull('office_id')
                ->get();

            foreach ($attendancesWithOffice as $attendance) {
                $office = DB::table('offices')->find($attendance->office_id);
                if ($office) {
                    $location = DB::table('locations')
                        ->where('company_id', $office->company_id)
                        ->where('name', $office->name)
                        ->first();

                    if ($location) {
                        DB::table('attendances')
                            ->where('id', $attendance->id)
                            ->update(['location_id' => $location->id]);
                    }
                }
            }

            // Migrasi business_trip_location_id ke location_id
            if (Schema::hasColumn('attendances', 'business_trip_location_id')) {
                $attendancesWithBTL = DB::table('attendances')
                    ->whereNotNull('business_trip_location_id')
                    ->get();

                foreach ($attendancesWithBTL as $attendance) {
                    $btl = DB::table('business_trip_locations')->find($attendance->business_trip_location_id);
                    if ($btl) {
                        $location = DB::table('locations')
                            ->where('company_id', $btl->company_id)
                            ->where('name', $btl->name)
                            ->first();

                        if ($location) {
                            DB::table('attendances')
                                ->where('id', $attendance->id)
                                ->update(['location_id' => $location->id]);
                        }
                    }
                }
            }
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Hapus kolom location_id dari attendances
        if (Schema::hasColumn('attendances', 'location_id')) {
            Schema::table('attendances', function ($table) {
                $table->dropColumn('location_id');
            });
        }

        // Kosongkan tabel locations (data akan dikembalikan dari offices dan business_trip_locations)
        DB::table('locations')->truncate();
    }
};
