<?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_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::table('customer_quality_audience_members', function (Blueprint $table) {
                if (! Schema::hasColumn('customer_quality_audience_members', 'metric_key')) {
                    $table->string('metric_key', 100)->nullable()->index();
                }
                if (! Schema::hasColumn('customer_quality_audience_members', 'email_hash')) {
                    $table->string('email_hash', 64)->nullable()->index();
                }
                if (! Schema::hasColumn('customer_quality_audience_members', 'external_id_hash')) {
                    $table->string('external_id_hash', 64)->nullable()->index();
                }
                if (! Schema::hasColumn('customer_quality_audience_members', 'country_code')) {
                    $table->string('country_code', 2)->default('BD')->index();
                }
                if (! Schema::hasColumn('customer_quality_audience_members', 'lookalike_value')) {
                    $table->decimal('lookalike_value', 24, 8)->default(0);
                }
            });
        }

        if (Schema::hasTable('customer_quality_sync_logs')) {
            Schema::table('customer_quality_sync_logs', function (Blueprint $table) {
                if (! Schema::hasColumn('customer_quality_sync_logs', 'scope_hash')) {
                    $table->string('scope_hash', 64)->nullable()->index();
                }
                if (! Schema::hasColumn('customer_quality_sync_logs', 'checksum')) {
                    $table->string('checksum', 64)->nullable()->index();
                }
                if (! Schema::hasColumn('customer_quality_sync_logs', 'expires_at')) {
                    $table->timestamp('expires_at')->nullable()->index();
                }
            });
        }
    }

    public function down(): void
    {
        if (Schema::hasTable('customer_quality_audience_members')) {
            Schema::table('customer_quality_audience_members', function (Blueprint $table) {
                foreach (['metric_key', 'email_hash', 'external_id_hash', 'country_code', 'lookalike_value'] as $column) {
                    if (Schema::hasColumn('customer_quality_audience_members', $column)) {
                        $table->dropColumn($column);
                    }
                }
            });
        }

        if (Schema::hasTable('customer_quality_sync_logs')) {
            Schema::table('customer_quality_sync_logs', function (Blueprint $table) {
                foreach (['scope_hash', 'checksum', 'expires_at'] as $column) {
                    if (Schema::hasColumn('customer_quality_sync_logs', $column)) {
                        $table->dropColumn($column);
                    }
                }
            });
        }
    }
};
