<?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
    {
        if (! Schema::hasTable('customer_quality_flags')) {
            Schema::create('customer_quality_flags', function (Blueprint $table) {
                $table->id();
                $table->foreignId('user_id')->unique()->constrained('users')->cascadeOnDelete();
                $table->boolean('is_blacklisted')->default(false)->index();
                $table->boolean('is_fake')->default(false)->index();
                $table->string('reason')->nullable();
                $table->foreignId('created_by_admin_id')->nullable()->constrained('admins')->nullOnDelete();
                $table->foreignId('updated_by_admin_id')->nullable()->constrained('admins')->nullOnDelete();
                $table->timestamps();
            });
        }

        if (! Schema::hasTable('customer_quality_metrics')) {
            Schema::create('customer_quality_metrics', function (Blueprint $table) {
                $table->id();
                $table->string('metric_key', 100)->unique();
                $table->foreignId('user_id')->nullable()->constrained('users')->nullOnDelete();
                $table->string('customer_name')->nullable();
                $table->string('phone', 40)->nullable();
                $table->string('normalized_phone', 20)->nullable()->index();
                $table->string('phone_hash', 64)->nullable()->index();
                $table->string('email_hash', 64)->nullable()->index();
                $table->string('external_id_hash', 64)->nullable()->index();
                $table->string('country_code', 2)->default('BD')->index();
                $table->unsignedInteger('total_orders')->default(0);
                $table->unsignedInteger('delivered_orders')->default(0);
                $table->unsignedInteger('returned_orders')->default(0);
                $table->unsignedInteger('cancelled_orders')->default(0);
                $table->decimal('delivered_amount', 24, 8)->default(0);
                $table->decimal('average_order_value', 24, 8)->default(0);
                $table->decimal('receive_rate', 8, 2)->default(0)->index();
                $table->decimal('return_rate', 8, 2)->default(0)->index();
                $table->decimal('quality_score', 8, 2)->default(0)->index();
                $table->decimal('lookalike_value', 24, 8)->default(0);
                $table->string('quality_status', 40)->default('standard')->index();
                $table->boolean('is_blacklisted')->default(false)->index();
                $table->boolean('is_fake')->default(false)->index();
                $table->boolean('is_blocked')->default(false)->index();
                $table->unsignedInteger('source_order_count')->default(0);
                $table->string('order_scope_checksum', 64)->nullable();
                $table->timestamp('calculated_at')->nullable()->index();
                $table->timestamps();

                $table->index(['quality_status', 'normalized_phone'], 'customer_quality_metrics_status_phone_index');
                $table->index(['delivered_orders', 'delivered_amount'], 'customer_quality_metrics_delivered_value_index');
            });
        }

        if (! Schema::hasTable('customer_quality_audience_members')) {
            Schema::create('customer_quality_audience_members', function (Blueprint $table) {
                $table->id();
                $table->string('audience_key', 80)->index();
                $table->string('metric_key', 100)->nullable()->index();
                $table->foreignId('user_id')->nullable()->constrained('users')->nullOnDelete();
                $table->string('phone_hash', 64)->index();
                $table->string('email_hash', 64)->nullable()->index();
                $table->string('external_id_hash', 64)->nullable()->index();
                $table->string('country_code', 2)->default('BD')->index();
                $table->decimal('lookalike_value', 24, 8)->default(0);
                $table->timestamp('last_seen_at')->nullable();
                $table->timestamp('last_synced_at')->nullable();
                $table->timestamps();

                $table->unique(['audience_key', 'phone_hash'], 'customer_quality_audience_hash_unique');
            });
        }

        if (! Schema::hasTable('customer_quality_sync_logs')) {
            Schema::create('customer_quality_sync_logs', function (Blueprint $table) {
                $table->id();
                $table->string('audience_key', 80)->index();
                $table->string('audience_name')->nullable();
                $table->string('meta_audience_id')->nullable();
                $table->string('mode', 30)->default('dry_run')->index();
                $table->string('status', 30)->default('completed')->index();
                $table->string('scope_hash', 64)->nullable()->index();
                $table->string('checksum', 64)->nullable()->index();
                $table->timestamp('expires_at')->nullable()->index();
                $table->unsignedInteger('eligible_count')->default(0);
                $table->unsignedInteger('add_count')->default(0);
                $table->unsignedInteger('remove_count')->default(0);
                $table->unsignedInteger('skipped_count')->default(0);
                $table->json('meta_response')->nullable();
                $table->text('error_message')->nullable();
                $table->foreignId('synced_by_admin_id')->nullable()->constrained('admins')->nullOnDelete();
                $table->timestamp('started_at')->nullable();
                $table->timestamp('finished_at')->nullable();
                $table->timestamps();
            });
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('customer_quality_sync_logs');
        Schema::dropIfExists('customer_quality_audience_members');
        Schema::dropIfExists('customer_quality_metrics');
        Schema::dropIfExists('customer_quality_flags');
    }
};
