<?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::table('customer_invoices', function (Blueprint $table): void {
            $table->foreignId('transport_job_id')->nullable()->after('party_id')->constrained()->nullOnDelete();
            $table->foreignId('contract_id')->nullable()->after('transport_job_id')->constrained()->nullOnDelete();
            $table->string('customer_po')->nullable()->after('invoice_type');
            $table->string('pricing_mode', 20)->default('exclusive')->after('customer_po');
            $table->text('terms')->nullable();
            $table->string('einvoice_status', 40)->default('not_applicable')->index();
            $table->string('pdf_hash', 64)->nullable();
            $table->string('pdf_path')->nullable();
        });

        Schema::table('vendor_bills', function (Blueprint $table): void {
            $table->foreignId('transport_job_id')->nullable()->after('party_id')->constrained()->nullOnDelete();
            $table->foreignId('trip_id')->nullable()->after('transport_job_id')->constrained()->nullOnDelete();
            $table->foreignId('vehicle_id')->nullable()->after('trip_id')->constrained()->nullOnDelete();
            $table->foreignId('driver_id')->nullable()->after('vehicle_id')->constrained()->nullOnDelete();
            $table->string('pricing_mode', 20)->default('exclusive');
            $table->string('attachment_hash', 64)->nullable()->index();
        });

        Schema::create('expenses', function (Blueprint $table): void {
            $table->id();
            $table->ulid('public_id')->unique();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->foreignId('party_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('transport_job_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('trip_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('vehicle_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('driver_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('department_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('cost_center_id')->nullable()->constrained()->nullOnDelete();
            $table->string('expense_number')->unique();
            $table->date('expense_date');
            $table->string('category', 60);
            $table->string('description');
            $table->foreignId('expense_account_id')->constrained('accounts')->restrictOnDelete();
            $table->foreignId('input_vat_account_id')->nullable()->constrained('accounts')->restrictOnDelete();
            $table->foreignId('payment_account_id')->constrained('accounts')->restrictOnDelete();
            $table->foreignId('tax_code_id')->nullable()->constrained()->restrictOnDelete();
            $table->decimal('amount_before_vat', 20, 4);
            $table->decimal('recoverable_vat', 20, 4)->default(0);
            $table->decimal('nonrecoverable_vat', 20, 4)->default(0);
            $table->decimal('total_amount', 20, 4);
            $table->string('payment_method', 30)->default('cash');
            $table->boolean('is_billable')->default(false);
            $table->string('reimbursement_status', 30)->default('not_applicable');
            $table->string('status', 30)->default('draft')->index();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('approved_at')->nullable();
            $table->timestamp('posted_at')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        Schema::create('adjustment_notes', function (Blueprint $table): void {
            $table->id();
            $table->ulid('public_id')->unique();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->foreignId('party_id')->constrained()->restrictOnDelete();
            $table->string('note_number')->unique();
            $table->string('side', 20); // customer or vendor
            $table->string('note_type', 20); // credit or debit
            $table->date('note_date');
            $table->foreignId('customer_invoice_id')->nullable()->constrained()->restrictOnDelete();
            $table->foreignId('vendor_bill_id')->nullable()->constrained()->restrictOnDelete();
            $table->string('reason_code', 50);
            $table->text('reason');
            $table->decimal('taxable_total', 20, 4);
            $table->decimal('vat_total', 20, 4)->default(0);
            $table->decimal('grand_total', 20, 4);
            $table->foreignId('receivable_account_id')->nullable()->constrained('accounts')->restrictOnDelete();
            $table->foreignId('payable_account_id')->nullable()->constrained('accounts')->restrictOnDelete();
            $table->foreignId('income_account_id')->nullable()->constrained('accounts')->restrictOnDelete();
            $table->foreignId('expense_account_id')->nullable()->constrained('accounts')->restrictOnDelete();
            $table->foreignId('vat_account_id')->nullable()->constrained('accounts')->restrictOnDelete();
            $table->string('status', 30)->default('draft')->index();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('approved_at')->nullable();
            $table->timestamp('posted_at')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->index(['party_id', 'side', 'note_type', 'note_date']);
        });

        Schema::create('adjustment_note_items', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('adjustment_note_id')->constrained()->cascadeOnDelete();
            $table->unsignedInteger('line_number');
            $table->string('description');
            $table->decimal('quantity', 20, 4)->default(1);
            $table->decimal('unit_rate', 20, 4);
            $table->decimal('taxable_amount', 20, 4);
            $table->decimal('vat_rate', 9, 4)->default(0);
            $table->decimal('vat_amount', 20, 4)->default(0);
            $table->decimal('line_total', 20, 4);
            $table->foreignId('account_id')->constrained('accounts')->restrictOnDelete();
            $table->foreignId('tax_code_id')->nullable()->constrained()->restrictOnDelete();
            $table->timestamps();
            $table->unique(['adjustment_note_id', 'line_number']);
        });

        Schema::create('bank_accounts', function (Blueprint $table): void {
            $table->id();
            $table->ulid('public_id')->unique();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('branch_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('account_id')->constrained('accounts')->restrictOnDelete();
            $table->string('bank_name');
            $table->string('account_name');
            $table->string('account_number');
            $table->string('iban')->nullable();
            $table->string('swift')->nullable();
            $table->char('currency_code', 3)->default('AED');
            $table->decimal('opening_balance', 20, 4)->default(0);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->unique(['company_id', 'account_number']);
        });

        Schema::create('bank_transactions', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('bank_account_id')->constrained()->cascadeOnDelete();
            $table->date('transaction_date');
            $table->string('reference')->nullable()->index();
            $table->text('description')->nullable();
            $table->decimal('debit', 20, 4)->default(0);
            $table->decimal('credit', 20, 4)->default(0);
            $table->decimal('running_balance', 20, 4)->nullable();
            $table->string('source', 30)->default('import');
            $table->string('match_status', 20)->default('unmatched')->index();
            $table->nullableMorphs('matched_document', 'bank_transactions_matched_document_index');
            $table->timestamps();
        });

        Schema::create('bank_reconciliations', function (Blueprint $table): void {
            $table->id();
            $table->ulid('public_id')->unique();
            $table->foreignId('bank_account_id')->constrained()->cascadeOnDelete();
            $table->date('statement_date');
            $table->decimal('statement_ending_balance', 20, 4);
            $table->decimal('book_ending_balance', 20, 4);
            $table->decimal('difference', 20, 4);
            $table->string('status', 20)->default('draft');
            $table->foreignId('closed_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('closed_at')->nullable();
            $table->timestamps();
        });

        Schema::create('bank_reconciliation_items', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('bank_reconciliation_id')->constrained()->cascadeOnDelete();
            $table->foreignId('bank_transaction_id')->constrained()->cascadeOnDelete();
            $table->boolean('is_cleared')->default(false);
            $table->timestamps();
            $table->unique(['bank_reconciliation_id', 'bank_transaction_id'], 'bank_reconciliation_item_unique');
        });

        Schema::create('cheques', function (Blueprint $table): void {
            $table->id();
            $table->ulid('public_id')->unique();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->foreignId('party_id')->nullable()->constrained()->nullOnDelete();
            $table->string('direction', 20); // received or issued
            $table->string('cheque_number');
            $table->date('cheque_date');
            $table->decimal('amount', 20, 4);
            $table->string('bank_name')->nullable();
            $table->string('status', 30)->default('in_hand')->index();
            $table->nullableMorphs('source_document');
            $table->date('deposit_date')->nullable();
            $table->date('clearance_date')->nullable();
            $table->text('notes')->nullable();
            $table->timestamps();
            $table->unique(['company_id', 'direction', 'cheque_number']);
        });

        Schema::create('driver_advances', function (Blueprint $table): void {
            $table->id();
            $table->ulid('public_id')->unique();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->foreignId('driver_id')->constrained()->restrictOnDelete();
            $table->foreignId('trip_id')->nullable()->constrained()->nullOnDelete();
            $table->string('advance_number')->unique();
            $table->date('advance_date');
            $table->string('advance_type', 30)->default('trip');
            $table->decimal('amount', 20, 4);
            $table->decimal('settled_amount', 20, 4)->default(0);
            $table->foreignId('advance_account_id')->constrained('accounts')->restrictOnDelete();
            $table->foreignId('cash_bank_account_id')->constrained('accounts')->restrictOnDelete();
            $table->string('status', 30)->default('draft');
            $table->text('description')->nullable();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('approved_at')->nullable();
            $table->timestamp('posted_at')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        Schema::create('attachments', function (Blueprint $table): void {
            $table->id();
            $table->ulid('public_id')->unique();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->morphs('attachable');
            $table->string('original_name');
            $table->string('stored_name');
            $table->string('disk')->default('local');
            $table->string('path');
            $table->string('mime_type')->nullable();
            $table->unsignedBigInteger('size_bytes')->default(0);
            $table->string('sha256', 64)->nullable()->index();
            $table->foreignId('uploaded_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        Schema::create('approval_histories', function (Blueprint $table): void {
            $table->id();
            $table->morphs('approvable');
            $table->string('action', 30);
            $table->string('from_status', 30)->nullable();
            $table->string('to_status', 30);
            $table->text('reason')->nullable();
            $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
            $table->timestamp('acted_at');
            $table->index(['approvable_type', 'approvable_id', 'acted_at'], 'approval_history_lookup');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('approval_histories');
        Schema::dropIfExists('attachments');
        Schema::dropIfExists('driver_advances');
        Schema::dropIfExists('cheques');
        Schema::dropIfExists('bank_reconciliation_items');
        Schema::dropIfExists('bank_reconciliations');
        Schema::dropIfExists('bank_transactions');
        Schema::dropIfExists('bank_accounts');
        Schema::dropIfExists('adjustment_note_items');
        Schema::dropIfExists('adjustment_notes');
        Schema::dropIfExists('expenses');
        Schema::table('vendor_bills', function (Blueprint $table): void {
            $table->dropConstrainedForeignId('transport_job_id');
            $table->dropConstrainedForeignId('trip_id');
            $table->dropConstrainedForeignId('vehicle_id');
            $table->dropConstrainedForeignId('driver_id');
            $table->dropColumn(['pricing_mode', 'attachment_hash']);
        });
        Schema::table('customer_invoices', function (Blueprint $table): void {
            $table->dropConstrainedForeignId('transport_job_id');
            $table->dropConstrainedForeignId('contract_id');
            $table->dropColumn(['customer_po', 'pricing_mode', 'terms', 'einvoice_status', 'pdf_hash', 'pdf_path']);
        });
    }
};
