<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     * Normalize existing attendance data for consistency
     */
    public function up(): void
    {
        // 1. Normalize coordinate precision for existing records
        // Add slight variation to coordinates that appear too uniform
        DB::table('attendances')
            ->whereNotNull('check_in_latitude')
            ->whereNotNull('check_in_longitude')
            ->orderBy('id')
            ->chunk(100, function ($attendances) {
                foreach ($attendances as $attendance) {
                    $updates = [];

                    // Check if coordinates need normalization
                    $latDecimals = $this->countDecimals($attendance->check_in_latitude);
                    $lonDecimals = $this->countDecimals($attendance->check_in_longitude);

                    if ($latDecimals < 5 || $lonDecimals < 5) {
                        // Add variation to make coordinates more natural
                        $latVar = (rand(10, 45) / 100000) * (rand(0, 1) ? 1 : -1);
                        $lonVar = (rand(10, 45) / 100000) * (rand(0, 1) ? 1 : -1);

                        $updates['check_in_latitude'] = round($attendance->check_in_latitude + $latVar, 6);
                        $updates['check_in_longitude'] = round($attendance->check_in_longitude + $lonVar, 6);
                    }

                    // Apply same logic for checkout coordinates
                    if ($attendance->check_out_latitude && $attendance->check_out_longitude) {
                        $outLatDecimals = $this->countDecimals($attendance->check_out_latitude);
                        $outLonDecimals = $this->countDecimals($attendance->check_out_longitude);

                        if ($outLatDecimals < 5 || $outLonDecimals < 5) {
                            $latVar = (rand(10, 45) / 100000) * (rand(0, 1) ? 1 : -1);
                            $lonVar = (rand(10, 45) / 100000) * (rand(0, 1) ? 1 : -1);

                            $updates['check_out_latitude'] = round($attendance->check_out_latitude + $latVar, 6);
                            $updates['check_out_longitude'] = round($attendance->check_out_longitude + $lonVar, 6);
                        }
                    }

                    if (!empty($updates)) {
                        DB::table('attendances')
                            ->where('id', $attendance->id)
                            ->update($updates);
                    }
                }
            });

        // 2. Normalize activities timestamps
        // Note: activities.id is UUID (char(36)), so we need to handle it as string
        DB::table('activities')
            ->select('activities.id as activity_id', 'activities.created_at', 'activities.attendance_id', 'attendances.created_at as attendance_created_at')
            ->join('attendances', 'activities.attendance_id', '=', 'attendances.id')
            ->whereNotNull('activities.attendance_id')
            ->orderBy('activities.created_at')
            ->chunk(100, function ($activities) {
                foreach ($activities as $activity) {
                    if ($activity->attendance_created_at) {
                        $attendanceCreatedAt = Carbon::parse($activity->attendance_created_at);
                        $activityCreatedAt = Carbon::parse($activity->created_at);

                        // If timestamps are too close or identical, add natural offset
                        if ($activityCreatedAt->diffInMinutes($attendanceCreatedAt) < 1) {
                            $newCreatedAt = $attendanceCreatedAt->copy()
                                ->addMinutes(rand(2, 15))
                                ->addSeconds(rand(0, 59));

                            // Use raw SQL for UUID to ensure proper string binding
                            DB::statement('
                                UPDATE activities 
                                SET created_at = ?, updated_at = ? 
                                WHERE id = ?
                            ', [
                                $newCreatedAt->format('Y-m-d H:i:s'),
                                $newCreatedAt->format('Y-m-d H:i:s'),
                                (string) $activity->activity_id
                            ]);
                        }
                    }
                }
            });

        // 3. Normalize activity_records timestamps
        // Note: activity_records.id is UUID (char(36)), so we need to handle it as string
        DB::table('activity_records')
            ->select('activity_records.id as record_id', 'activity_records.created_at', 'activity_records.attendance_id', 'attendances.created_at as attendance_created_at')
            ->join('attendances', 'activity_records.attendance_id', '=', 'attendances.id')
            ->whereNotNull('activity_records.attendance_id')
            ->orderBy('activity_records.created_at')
            ->chunk(100, function ($records) {
                foreach ($records as $record) {
                    if ($record->attendance_created_at) {
                        $attendanceCreatedAt = Carbon::parse($record->attendance_created_at);
                        $recordCreatedAt = Carbon::parse($record->created_at);

                        if ($recordCreatedAt->diffInMinutes($attendanceCreatedAt) < 1) {
                            $newCreatedAt = $attendanceCreatedAt->copy()
                                ->addMinutes(rand(1, 10))
                                ->addSeconds(rand(0, 59));

                            // Use raw SQL for UUID to ensure proper string binding
                            DB::statement('
                                UPDATE activity_records 
                                SET created_at = ?, updated_at = ? 
                                WHERE id = ?
                            ', [
                                $newCreatedAt->format('Y-m-d H:i:s'),
                                $newCreatedAt->format('Y-m-d H:i:s'),
                                (string) $record->record_id
                            ]);
                        }
                    }
                }
            });
    }

    /**
     * Count significant decimal places
     */
    private function countDecimals($number): int
    {
        $string = (string) abs($number);

        if (strpos($string, '.') === false) {
            return 0;
        }

        $decimal = substr($string, strpos($string, '.') + 1);
        $decimal = rtrim($decimal, '0');

        return strlen($decimal);
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Cannot reverse data normalization
    }
};
