<?php

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

return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('couriers')) {
            Schema::create('couriers', function (Blueprint $table) {
                $table->id();
                $table->string('name');
                $table->string('provider_key', 80)->unique();
                $table->string('api_type', 80)->nullable();
                $table->string('receivable_account_name')->default('Courier Receivable');
                $table->string('charge_expense_account_name')->default('Courier Charge Expense');
                $table->string('return_charge_expense_account_name')->default('Courier Return Charge Expense');
                $table->string('cod_charge_expense_account_name')->default('Courier COD Charge Expense');
                $table->string('default_settlement_account_name')->default('Cash/Bank');
                $table->decimal('opening_balance', 24, 8)->default(0);
                $table->decimal('current_balance', 24, 8)->default(0);
                $table->boolean('is_active')->default(true);
                $table->json('settings_payload')->nullable();
                $table->timestamps();
            });
        }

        if (Schema::hasTable('order_courier_shipments')) {
            Schema::table('order_courier_shipments', function (Blueprint $table) {
                if (! Schema::hasColumn('order_courier_shipments', 'courier_id')) {
                    $table->foreignId('courier_id')->nullable()->after('id')->constrained('couriers')->nullOnDelete();
                }

                if (! Schema::hasColumn('order_courier_shipments', 'customer_delivery_charge')) {
                    $table->decimal('customer_delivery_charge', 24, 8)->nullable()->after('collectable_amount');
                }

                if (! Schema::hasColumn('order_courier_shipments', 'delivered_at')) {
                    $table->timestamp('delivered_at')->nullable()->after('cod_collected_at');
                }

                if (! Schema::hasColumn('order_courier_shipments', 'net_receivable_amount')) {
                    $table->decimal('net_receivable_amount', 24, 8)->nullable()->after('settled_amount');
                }

                if (! Schema::hasColumn('order_courier_shipments', 'net_received_amount')) {
                    $table->decimal('net_received_amount', 24, 8)->nullable()->after('net_receivable_amount');
                }

                if (! Schema::hasColumn('order_courier_shipments', 'balance_amount')) {
                    $table->decimal('balance_amount', 24, 8)->nullable()->after('net_received_amount');
                }
            });

            $providers = DB::table('order_courier_shipments')
                ->whereNotNull('provider')
                ->select('provider')
                ->distinct()
                ->pluck('provider');

            foreach ($providers as $provider) {
                if (! $provider) {
                    continue;
                }

                $providerKey = Str::slug((string) $provider, '_');
                DB::table('couriers')->updateOrInsert(
                    ['provider_key' => $providerKey],
                    [
                        'name' => ucwords(str_replace(['_', '-'], ' ', $providerKey)),
                        'api_type' => $providerKey,
                        'updated_at' => now(),
                        'created_at' => now(),
                    ]
                );

                $courierId = DB::table('couriers')->where('provider_key', $providerKey)->value('id');

                if ($courierId) {
                    DB::table('order_courier_shipments')
                        ->whereRaw('LOWER(provider) = ?', [strtolower(trim((string) $provider))])
                        ->whereNull('courier_id')
                        ->update(['courier_id' => $courierId]);
                }
            }
        }

        if (! Schema::hasTable('courier_settlements')) {
            Schema::create('courier_settlements', function (Blueprint $table) {
                $table->id();
                $table->foreignId('courier_id')->constrained('couriers')->cascadeOnDelete();
                $table->string('voucher_no', 60)->unique();
                $table->date('settlement_date')->index();
                $table->string('reference_no')->nullable()->index();
                $table->string('payment_method', 80)->nullable();
                $table->string('settlement_account')->nullable();
                $table->decimal('total_cod_amount', 24, 8)->default(0);
                $table->decimal('delivery_charge_deduction', 24, 8)->default(0);
                $table->decimal('cod_charge_deduction', 24, 8)->default(0);
                $table->decimal('return_charge_deduction', 24, 8)->default(0);
                $table->decimal('adjustment_amount', 24, 8)->default(0);
                $table->decimal('net_received_amount', 24, 8)->default(0);
                $table->string('status', 40)->default('posted')->index();
                $table->text('note')->nullable();
                $table->string('attachment_path')->nullable();
                $table->json('payload')->nullable();
                $table->foreignId('created_by_admin_id')->nullable()->constrained('admins')->nullOnDelete();
                $table->foreignId('reversed_by_admin_id')->nullable()->constrained('admins')->nullOnDelete();
                $table->timestamp('reversed_at')->nullable();
                $table->string('reverse_reason')->nullable();
                $table->timestamps();
            });
        }

        if (! Schema::hasTable('courier_settlement_items')) {
            Schema::create('courier_settlement_items', function (Blueprint $table) {
                $table->id();
                $table->foreignId('courier_settlement_id')->constrained('courier_settlements')->cascadeOnDelete();
                $table->foreignId('courier_id')->constrained('couriers')->cascadeOnDelete();
                $table->foreignId('order_courier_shipment_id')->nullable()->constrained('order_courier_shipments')->nullOnDelete();
                $table->foreignId('order_id')->nullable()->constrained('orders')->nullOnDelete();
                $table->string('parcel_id', 120)->nullable()->index();
                $table->string('invoice_no', 120)->nullable()->index();
                $table->decimal('cod_amount', 24, 8)->default(0);
                $table->decimal('delivery_charge', 24, 8)->default(0);
                $table->decimal('cod_charge', 24, 8)->default(0);
                $table->decimal('return_charge', 24, 8)->default(0);
                $table->decimal('adjustment_amount', 24, 8)->default(0);
                $table->decimal('net_receivable', 24, 8)->default(0);
                $table->decimal('net_received', 24, 8)->default(0);
                $table->string('previous_settlement_status', 80)->nullable();
                $table->string('settlement_status', 80)->default('settled')->index();
                $table->string('note')->nullable();
                $table->timestamps();
                $table->unique(['courier_settlement_id', 'order_courier_shipment_id'], 'courier_settlement_item_unique');
            });
        }

        if (! Schema::hasTable('courier_ledger_entries')) {
            Schema::create('courier_ledger_entries', function (Blueprint $table) {
                $table->id();
                $table->foreignId('courier_id')->constrained('couriers')->cascadeOnDelete();
                $table->foreignId('order_courier_shipment_id')->nullable()->constrained('order_courier_shipments')->nullOnDelete();
                $table->foreignId('order_id')->nullable()->constrained('orders')->nullOnDelete();
                $table->foreignId('courier_settlement_id')->nullable()->constrained('courier_settlements')->nullOnDelete();
                $table->string('voucher_no', 60)->unique();
                $table->string('event_type', 80)->index();
                $table->date('entry_date')->index();
                $table->string('parcel_id', 120)->nullable()->index();
                $table->string('invoice_no', 120)->nullable()->index();
                $table->string('particulars');
                $table->decimal('debit', 24, 8)->default(0);
                $table->decimal('credit', 24, 8)->default(0);
                $table->decimal('balance', 24, 8)->default(0);
                $table->string('status', 60)->default('posted')->index();
                $table->string('source', 60)->nullable()->index();
                $table->string('source_type')->nullable();
                $table->unsignedBigInteger('source_id')->nullable();
                $table->string('idempotency_key', 191)->unique();
                $table->json('payload')->nullable();
                $table->foreignId('created_by_admin_id')->nullable()->constrained('admins')->nullOnDelete();
                $table->foreignId('reversed_entry_id')->nullable()->constrained('courier_ledger_entries')->nullOnDelete();
                $table->timestamp('reversed_at')->nullable();
                $table->string('reverse_reason')->nullable();
                $table->timestamps();
                $table->index(['courier_id', 'entry_date']);
            });
        }

        if (! Schema::hasTable('courier_reconciliation_imports')) {
            Schema::create('courier_reconciliation_imports', function (Blueprint $table) {
                $table->id();
                $table->foreignId('courier_id')->constrained('couriers')->cascadeOnDelete();
                $table->date('import_date')->index();
                $table->string('source', 80)->default('csv');
                $table->string('reference_no')->nullable();
                $table->string('file_name')->nullable();
                $table->unsignedInteger('total_rows')->default(0);
                $table->unsignedInteger('matched_rows')->default(0);
                $table->unsignedInteger('mismatch_rows')->default(0);
                $table->unsignedInteger('missing_rows')->default(0);
                $table->unsignedInteger('duplicate_rows')->default(0);
                $table->string('status', 40)->default('processed')->index();
                $table->json('summary')->nullable();
                $table->foreignId('created_by_admin_id')->nullable()->constrained('admins')->nullOnDelete();
                $table->timestamps();
            });
        }

        if (! Schema::hasTable('courier_reconciliation_rows')) {
            Schema::create('courier_reconciliation_rows', function (Blueprint $table) {
                $table->id();
                $table->foreignId('courier_reconciliation_import_id');
                $table->foreignId('courier_id');
                $table->foreignId('order_courier_shipment_id')->nullable();
                $table->unsignedInteger('row_no');
                $table->string('parcel_id', 120)->nullable()->index();
                $table->string('invoice_no', 120)->nullable()->index();
                $table->decimal('courier_cod_amount', 24, 8)->default(0);
                $table->decimal('system_cod_amount', 24, 8)->default(0);
                $table->decimal('courier_charge_amount', 24, 8)->default(0);
                $table->decimal('system_charge_amount', 24, 8)->default(0);
                $table->decimal('courier_settlement_amount', 24, 8)->default(0);
                $table->decimal('system_settlement_amount', 24, 8)->default(0);
                $table->string('status', 60)->index();
                $table->string('mismatch_reason')->nullable();
                $table->json('raw_payload')->nullable();
                $table->timestamps();

                $table->foreign('courier_reconciliation_import_id', 'cr_rows_import_fk')
                    ->references('id')->on('courier_reconciliation_imports')->cascadeOnDelete();
                $table->foreign('courier_id', 'cr_rows_courier_fk')
                    ->references('id')->on('couriers')->cascadeOnDelete();
                $table->foreign('order_courier_shipment_id', 'cr_rows_shipment_fk')
                    ->references('id')->on('order_courier_shipments')->nullOnDelete();
            });
        } else {
            $this->addForeignIfMissing('courier_reconciliation_rows', 'courier_reconciliation_import_id', 'cr_rows_import_fk', 'courier_reconciliation_imports', 'cascade');
            $this->addForeignIfMissing('courier_reconciliation_rows', 'courier_id', 'cr_rows_courier_fk', 'couriers', 'cascade');
            $this->addForeignIfMissing('courier_reconciliation_rows', 'order_courier_shipment_id', 'cr_rows_shipment_fk', 'order_courier_shipments', 'set null');
        }

        if (! Schema::hasTable('courier_audit_logs')) {
            Schema::create('courier_audit_logs', function (Blueprint $table) {
                $table->id();
                $table->foreignId('courier_id')->nullable()->constrained('couriers')->nullOnDelete();
                $table->foreignId('order_courier_shipment_id')->nullable()->constrained('order_courier_shipments')->nullOnDelete();
                $table->foreignId('courier_settlement_id')->nullable()->constrained('courier_settlements')->nullOnDelete();
                $table->foreignId('courier_ledger_entry_id')->nullable()->constrained('courier_ledger_entries')->nullOnDelete();
                $table->string('action', 100)->index();
                $table->string('auditable_type')->nullable();
                $table->unsignedBigInteger('auditable_id')->nullable();
                $table->json('old_values')->nullable();
                $table->json('new_values')->nullable();
                $table->string('source', 80)->nullable();
                $table->foreignId('created_by_admin_id')->nullable()->constrained('admins')->nullOnDelete();
                $table->timestamps();
            });
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('courier_audit_logs');
        Schema::dropIfExists('courier_reconciliation_rows');
        Schema::dropIfExists('courier_reconciliation_imports');
        Schema::dropIfExists('courier_ledger_entries');
        Schema::dropIfExists('courier_settlement_items');
        Schema::dropIfExists('courier_settlements');

        if (Schema::hasTable('order_courier_shipments')) {
            Schema::table('order_courier_shipments', function (Blueprint $table) {
                foreach ([
                    'customer_delivery_charge',
                    'delivered_at',
                    'net_receivable_amount',
                    'net_received_amount',
                    'balance_amount',
                ] as $column) {
                    if (Schema::hasColumn('order_courier_shipments', $column)) {
                        $table->dropColumn($column);
                    }
                }

                if (Schema::hasColumn('order_courier_shipments', 'courier_id')) {
                    $table->dropConstrainedForeignId('courier_id');
                }
            });
        }

        Schema::dropIfExists('couriers');
    }

    private function addForeignIfMissing(string $table, string $column, string $constraint, string $references, string $onDelete): void
    {
        if (! Schema::hasTable($table) || ! Schema::hasTable($references) || ! Schema::hasColumn($table, $column)) {
            return;
        }

        if ($this->hasForeignKey($table, $constraint)) {
            return;
        }

        Schema::table($table, function (Blueprint $blueprint) use ($column, $constraint, $references, $onDelete) {
            $foreign = $blueprint->foreign($column, $constraint)->references('id')->on($references);

            if ($onDelete === 'cascade') {
                $foreign->cascadeOnDelete();
            } elseif ($onDelete === 'set null') {
                $foreign->nullOnDelete();
            }
        });
    }

    private function hasForeignKey(string $table, string $constraint): bool
    {
        if (DB::getDriverName() !== 'mysql') {
            return false;
        }

        return DB::table('information_schema.TABLE_CONSTRAINTS')
            ->whereRaw('CONSTRAINT_SCHEMA = DATABASE()')
            ->where('TABLE_NAME', $table)
            ->where('CONSTRAINT_NAME', $constraint)
            ->where('CONSTRAINT_TYPE', 'FOREIGN KEY')
            ->exists();
    }
};
