<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('sim_cards', function (Blueprint $table): void {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('device_id')->constrained()->restrictOnDelete();
            $table->foreignId('user_id')->constrained()->restrictOnDelete();
            $table->unsignedTinyInteger('slot_number')->default(1);
            $table->text('phone_number')->nullable();
            $table->string('phone_number_hash', 64)->nullable()->index();
            $table->string('country_code', 8)->nullable();
            $table->string('carrier_name')->nullable()->index();
            $table->string('operator_name')->nullable()->index();
            $table->string('network_type', 30)->nullable()->index();
            $table->text('iccid')->nullable();
            $table->string('iccid_hash', 64)->nullable()->unique();
            $table->text('imsi')->nullable();
            $table->string('imsi_hash', 64)->nullable()->index();
            $table->text('sim_serial')->nullable();
            $table->string('sim_serial_hash', 64)->nullable()->index();
            $table->text('subscriber_id')->nullable();
            $table->string('subscriber_id_hash', 64)->nullable()->index();
            $table->boolean('is_primary')->default(false)->index();
            $table->date('activation_date')->nullable();
            $table->string('status', 30)->default('unknown')->index();
            $table->string('data_access_level', 30)->default('consumer_limited');
            $table->string('data_source', 30)->default('registration');
            $table->timestamp('last_detected')->nullable()->index();
            $table->timestamps();
            $table->softDeletes();

            $table->unique(['device_id', 'slot_number', 'deleted_at']);
            $table->index(['user_id', 'status']);
            $table->index(['carrier_name', 'network_type']);
        });

        Schema::create('sim_history', function (Blueprint $table): void {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('device_id')->constrained()->restrictOnDelete();
            $table->foreignId('user_id')->constrained()->restrictOnDelete();
            $table->text('old_sim')->nullable();
            $table->text('new_sim')->nullable();
            $table->string('old_sim_hash', 64)->nullable()->index();
            $table->string('new_sim_hash', 64)->nullable()->index();
            $table->string('change_type', 40)->index();
            $table->string('change_reason')->nullable();
            $table->timestamp('detected_at')->index();
            $table->decimal('latitude', 10, 7)->nullable();
            $table->decimal('longitude', 10, 7)->nullable();
            $table->string('address')->nullable();
            $table->string('operator_name')->nullable();
            $table->string('country', 100)->nullable();
            $table->boolean('authorized')->default(false)->index();
            $table->foreignId('theft_event_id')->nullable()->constrained('theft_events')->nullOnDelete();
            $table->json('metadata')->nullable();
            $table->timestamp('created_at')->useCurrent();

            $table->index(['device_id', 'detected_at']);
            $table->index(['user_id', 'detected_at']);
            $table->index(['latitude', 'longitude']);
        });

        Schema::create('imei_records', function (Blueprint $table): void {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('device_id')->unique()->constrained()->restrictOnDelete();
            $table->text('imei_1')->nullable();
            $table->string('imei_1_hash', 64)->nullable()->unique();
            $table->text('imei_2')->nullable();
            $table->string('imei_2_hash', 64)->nullable()->unique();
            $table->text('meid')->nullable();
            $table->string('meid_hash', 64)->nullable()->unique();
            $table->text('serial_number')->nullable();
            $table->string('serial_number_hash', 64)->nullable()->index();
            $table->text('android_id')->nullable();
            $table->string('android_id_hash', 64)->nullable()->index();
            $table->date('registered_date');
            $table->string('verification_status', 30)->default('pending')->index();
            $table->timestamp('last_verified')->nullable()->index();
            $table->string('data_access_level', 30)->default('consumer_limited');
            $table->string('data_source', 30)->default('registration');
            $table->json('device_identity_snapshot')->nullable();
            $table->timestamps();
        });

        Schema::create('network_logs', function (Blueprint $table): void {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('device_id')->constrained()->cascadeOnDelete();
            $table->string('carrier')->nullable()->index();
            $table->string('network_type', 30)->nullable()->index();
            $table->smallInteger('signal_strength')->nullable();
            $table->boolean('roaming')->default(false)->index();
            $table->text('wifi_name')->nullable();
            $table->string('wifi_name_hash', 64)->nullable()->index();
            $table->ipAddress('ip_address')->nullable();
            $table->ipAddress('public_ip_address')->nullable();
            $table->boolean('vpn_detected')->default(false)->index();
            $table->string('country', 100)->nullable()->index();
            $table->string('city', 100)->nullable();
            $table->decimal('download_speed', 12, 3)->nullable();
            $table->decimal('upload_speed', 12, 3)->nullable();
            $table->timestamp('recorded_at')->index();

            $table->index(['device_id', 'recorded_at']);
            $table->index(['carrier', 'network_type', 'roaming']);
        });

        Schema::create('imei_verification_history', function (Blueprint $table): void {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('imei_record_id')->constrained('imei_records')->cascadeOnDelete();
            $table->foreignId('device_id')->constrained()->restrictOnDelete();
            $table->string('result', 30)->index();
            $table->unsignedTinyInteger('match_score')->default(0);
            $table->json('matched_fields')->nullable();
            $table->json('mismatched_fields')->nullable();
            $table->string('verification_source', 30);
            $table->foreignId('theft_event_id')->nullable()->constrained('theft_events')->nullOnDelete();
            $table->timestamp('verified_at')->index();

            $table->index(['device_id', 'verified_at']);
        });

        Schema::create('device_recovery_reports', function (Blueprint $table): void {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->string('report_number', 40)->unique();
            $table->foreignId('device_id')->constrained()->restrictOnDelete();
            $table->foreignId('user_id')->constrained()->restrictOnDelete();
            $table->string('report_type', 30)->index();
            $table->string('status', 30)->default('generated')->index();
            $table->string('disk', 30);
            $table->string('path');
            $table->string('checksum', 64);
            $table->string('verification_token', 64)->unique();
            $table->json('snapshot');
            $table->timestamp('generated_at')->index();
            $table->timestamp('expires_at')->nullable();
            $table->timestamps();

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

    public function down(): void
    {
        Schema::dropIfExists('device_recovery_reports');
        Schema::dropIfExists('imei_verification_history');
        Schema::dropIfExists('network_logs');
        Schema::dropIfExists('imei_records');
        Schema::dropIfExists('sim_history');
        Schema::dropIfExists('sim_cards');
    }
};
