<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('inspection_workspaces', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('default_currency', 3)->default('IDR');
            $table->json('metadata')->nullable();
            $table->timestamps();

            $table->index(['user_id', 'name']);
        });

        Schema::table('inspection_collections', function (Blueprint $table) {
            $table->foreignId('inspection_workspace_id')
                ->nullable()
                ->after('user_id')
                ->constrained('inspection_workspaces')
                ->nullOnDelete();
        });

        Schema::table('inspections', function (Blueprint $table) {
            $table->string('currency_code', 3)->default('IDR')->after('estimated_cost');
            $table->string('estimated_cost_label')->nullable()->after('currency_code');
        });

        $now = now();
        $userIds = DB::table('users')->pluck('id');

        foreach ($userIds as $userId) {
            $workspaceId = DB::table('inspection_workspaces')->insertGetId([
                'user_id' => $userId,
                'name' => 'Default Workspace',
                'default_currency' => 'IDR',
                'metadata' => json_encode(['created_from' => 'migration']),
                'created_at' => $now,
                'updated_at' => $now,
            ]);

            DB::table('inspection_collections')
                ->where('user_id', $userId)
                ->whereNull('inspection_workspace_id')
                ->update([
                    'inspection_workspace_id' => $workspaceId,
                    'updated_at' => $now,
                ]);
        }

        DB::table('inspections')
            ->whereNull('inspection_collection_id')
            ->orderBy('id')
            ->chunkById(100, function ($inspections) use ($now) {
                foreach ($inspections as $inspection) {
                    $workspaceId = DB::table('inspection_workspaces')
                        ->where('user_id', $inspection->user_id)
                        ->orderBy('id')
                        ->value('id');

                    if (! $workspaceId) {
                        continue;
                    }

                    $propertyName = trim((string) ($inspection->property_name ?: 'General Collection'));
                    $collection = DB::table('inspection_collections')
                        ->where('user_id', $inspection->user_id)
                        ->where('inspection_workspace_id', $workspaceId)
                        ->where('property_name', $propertyName)
                        ->whereNull('room_name')
                        ->first();

                    $collectionId = $collection?->id ?? DB::table('inspection_collections')->insertGetId([
                        'user_id' => $inspection->user_id,
                        'inspection_workspace_id' => $workspaceId,
                        'name' => $propertyName,
                        'collection_type' => 'house',
                        'property_name' => $propertyName,
                        'room_name' => null,
                        'property_address' => $inspection->property_address,
                        'metadata' => json_encode(['created_from' => 'migration']),
                        'created_at' => $now,
                        'updated_at' => $now,
                    ]);

                    DB::table('inspections')
                        ->where('id', $inspection->id)
                        ->update([
                            'inspection_collection_id' => $collectionId,
                            'updated_at' => $now,
                        ]);
                }
            });

        DB::table('inspections')
            ->whereNull('estimated_cost_label')
            ->orderBy('id')
            ->chunkById(100, function ($inspections) {
                foreach ($inspections as $inspection) {
                    $currency = $this->currencyFromSettings($inspection->analysis_settings ?? null);
                    DB::table('inspections')
                        ->where('id', $inspection->id)
                        ->update([
                            'currency_code' => $currency,
                            'estimated_cost_label' => $this->formatMoney((float) $inspection->estimated_cost, $currency),
                        ]);
                }
            });
    }

    public function down(): void
    {
        Schema::table('inspections', function (Blueprint $table) {
            $table->dropColumn(['currency_code', 'estimated_cost_label']);
        });

        Schema::table('inspection_collections', function (Blueprint $table) {
            $table->dropConstrainedForeignId('inspection_workspace_id');
        });

        Schema::dropIfExists('inspection_workspaces');
    }

    private function currencyFromSettings(?string $settings): string
    {
        $decoded = json_decode((string) $settings, true);
        $currency = is_array($decoded) ? strtoupper((string) ($decoded['currency'] ?? 'IDR')) : 'IDR';

        return in_array($currency, ['IDR', 'USD'], true) ? $currency : 'IDR';
    }

    private function formatMoney(float $amount, string $currency): string
    {
        if ($currency === 'USD') {
            return '$'.number_format($amount, 2, '.', ',');
        }

        return 'Rp '.number_format($amount, 0, ',', '.');
    }
};
