<?php
// =============================================================================
// PHASE 3-5 INCREMENTAL MIGRATION — SaaS Accounting Software
// Purpose : Adds tables and columns NOT yet created in Phase 1+2 run.
//           Uses Schema::hasTable() and Schema::hasColumn() guards so it is
//           IDEMPOTENT — safe to run multiple times.
//
// Run:      php artisan migrate          (Laravel will apply this as next migration)
//           php artisan db:seed          (adds Phase 3-5 demo data via firstOrCreate)
// =============================================================================

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

return new class extends Migration
{
    public function up(): void
    {
        // ─────────────────────────────────────────────────────────────────────
        // All tables below were already created by the Phase 1+2 mega-migration.
        // We use Schema::hasTable() guards so re-running this is safe.
        // ─────────────────────────────────────────────────────────────────────

        // ── Dimensions (Phase 3) ──────────────────────────────────────────────
        if (!Schema::hasTable('dimensions')) {
            Schema::create('dimensions', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->unsignedTinyInteger('slot');
                $table->string('name', 100);
                $table->boolean('is_active')->default(false);
                $table->boolean('is_required')->default(false);
                $table->timestamps();
                $table->unique(['company_id','slot']);
            });
        }

        if (!Schema::hasTable('dimension_values')) {
            Schema::create('dimension_values', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('dimension_id')->constrained()->cascadeOnDelete();
                $table->foreignId('parent_id')->nullable()->constrained('dimension_values')->nullOnDelete();
                $table->string('code', 30)->nullable();
                $table->string('name', 150);
                $table->boolean('is_active')->default(true);
                $table->unsignedSmallInteger('sort_order')->default(0);
                $table->unsignedTinyInteger('depth')->default(0);
                $table->string('path', 500)->nullable();
                $table->timestamps();
                $table->softDeletes();
                $table->index(['company_id','dimension_id']);
            });
        }

        // ── Voucher Number Settings (Phase 2 — may already exist) ────────────
        if (!Schema::hasTable('voucher_number_settings')) {
            Schema::create('voucher_number_settings', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->string('voucher_type', 50);
                $table->string('prefix', 20)->nullable();
                $table->string('suffix', 20)->nullable();
                $table->unsignedInteger('starting_number')->default(1);
                $table->unsignedInteger('current_number')->default(0);
                $table->unsignedTinyInteger('padding_digits')->default(4);
                $table->boolean('include_year')->default(false);
                $table->boolean('include_month')->default(false);
                $table->string('separator', 5)->default('-');
                $table->enum('reset_period', ['never','yearly','monthly'])->default('never');
                $table->enum('number_on', ['draft','approval'])->default('draft');
                $table->boolean('allow_manual')->default(false);
                $table->timestamp('last_reset_at')->nullable();
                $table->timestamps();
                $table->unique(['company_id','voucher_type']);
            });
        }

        // ── Audit Logs (Phase 2 — may already exist) ─────────────────────────
        if (!Schema::hasTable('audit_logs')) {
            Schema::create('audit_logs', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->nullable()->constrained()->nullOnDelete();
                $table->unsignedBigInteger('user_id')->nullable();
                $table->string('user_type', 20)->default('user');
                $table->string('action', 50);
                $table->string('module', 100);
                $table->string('record_id', 100)->nullable();
                $table->json('old_values')->nullable();
                $table->json('new_values')->nullable();
                $table->string('ip_address', 45)->nullable();
                $table->string('user_agent', 500)->nullable();
                $table->text('description')->nullable();
                $table->timestamp('created_at')->useCurrent();
                $table->index(['company_id','module','action'], 'al_company_module_action_idx');
                $table->index(['company_id','created_at'], 'al_company_created_idx');
            });
        }

        // ── Vouchers (Phase 5) ────────────────────────────────────────────────
        if (!Schema::hasTable('vouchers')) {
            Schema::create('vouchers', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('fiscal_year_id')->constrained()->cascadeOnDelete();
                $table->string('voucher_type', 50);
                $table->string('voucher_number', 50)->nullable();
                $table->date('voucher_date');
                $table->string('reference', 100)->nullable();
                $table->foreignId('currency_id')->nullable()->constrained()->nullOnDelete();
                $table->decimal('exchange_rate', 18, 6)->default(1);
                $table->unsignedBigInteger('party_id')->nullable();
                $table->string('party_type', 50)->nullable();
                $table->text('narration')->nullable();
                $table->decimal('total_amount', 18, 2)->default(0);
                $table->decimal('total_tax', 18, 2)->default(0);
                $table->decimal('total_discount', 18, 2)->default(0);
                $table->decimal('grand_total', 18, 2)->default(0);
                $table->enum('status', ['draft','submitted','approved','posted','cancelled','rejected'])->default('draft');
                $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
                $table->foreignId('submitted_by')->nullable()->constrained('users')->nullOnDelete();
                $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
                $table->foreignId('posted_by')->nullable()->constrained('users')->nullOnDelete();
                $table->foreignId('cancelled_by')->nullable()->constrained('users')->nullOnDelete();
                $table->timestamp('submitted_at')->nullable();
                $table->timestamp('approved_at')->nullable();
                $table->timestamp('posted_at')->nullable();
                $table->timestamp('cancelled_at')->nullable();
                $table->text('rejection_reason')->nullable();
                $table->json('extra_data')->nullable();
                $table->timestamps();
                $table->softDeletes();
                $table->index(['company_id','voucher_type','status'], 'v_company_type_status_idx');
                $table->index(['company_id','voucher_date']);
                $table->index(['company_id','voucher_type','voucher_number'], 'v_company_type_number_idx');
            });
        }

        // ── Voucher Lines (Phase 5) ───────────────────────────────────────────
        if (!Schema::hasTable('voucher_lines')) {
            Schema::create('voucher_lines', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->unsignedSmallInteger('line_number')->default(1);
                $table->foreignId('account_id')->nullable()->constrained('accounts')->nullOnDelete();
                $table->foreignId('item_id')->nullable()->constrained()->nullOnDelete();
                $table->text('description')->nullable();
                $table->decimal('quantity', 18, 4)->default(0);
                $table->foreignId('uom_id')->nullable()->constrained('uoms')->nullOnDelete();
                $table->decimal('unit_price', 18, 6)->default(0);
                $table->decimal('discount_percent', 8, 4)->default(0);
                $table->decimal('discount_amount', 18, 2)->default(0);
                $table->foreignId('tax_code_id')->nullable()->constrained('tax_codes')->nullOnDelete();
                $table->decimal('tax_rate', 8, 4)->default(0);
                $table->decimal('tax_amount', 18, 2)->default(0);
                $table->decimal('line_total', 18, 2)->default(0);
                $table->decimal('foreign_amount', 18, 2)->default(0);
                $table->enum('dr_cr', ['dr','cr'])->default('dr');
                $table->foreignId('dim1_id')->nullable()->constrained('dimension_values')->nullOnDelete();
                $table->foreignId('dim2_id')->nullable()->constrained('dimension_values')->nullOnDelete();
                $table->foreignId('dim3_id')->nullable()->constrained('dimension_values')->nullOnDelete();
                $table->foreignId('dim4_id')->nullable()->constrained('dimension_values')->nullOnDelete();
                $table->foreignId('dim5_id')->nullable()->constrained('dimension_values')->nullOnDelete();
                $table->foreignId('dim6_id')->nullable()->constrained('dimension_values')->nullOnDelete();
                $table->foreignId('dim7_id')->nullable()->constrained('dimension_values')->nullOnDelete();
                $table->timestamps();
                $table->index(['company_id','voucher_id']);
            });
        }

        // ── Voucher Approvals (Phase 5) ───────────────────────────────────────
        if (!Schema::hasTable('voucher_approvals')) {
            Schema::create('voucher_approvals', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->enum('action', ['submitted','approved','rejected','posted','cancelled']);
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->text('comments')->nullable();
                $table->timestamps();
                $table->index(['company_id','voucher_id']);
            });
        }

        // ── Transaction Attachments (Phase 5) ─────────────────────────────────
        if (!Schema::hasTable('transaction_attachments')) {
            Schema::create('transaction_attachments', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->morphs('attachable');
                $table->string('file_name', 255);
                $table->string('file_path', 500);
                $table->string('file_type', 100)->nullable();
                $table->unsignedInteger('file_size')->default(0);
                $table->text('description')->nullable();
                $table->foreignId('uploaded_by')->nullable()->constrained('users')->nullOnDelete();
                $table->timestamps();
                $table->index(['company_id','attachable_type','attachable_id'], 'ta_company_attachable_idx');
            });
        }

        // ── Ledger Entries (Phase 5) ──────────────────────────────────────────
        if (!Schema::hasTable('ledger_entries')) {
            Schema::create('ledger_entries', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('fiscal_year_id')->constrained()->cascadeOnDelete();
                $table->foreignId('account_id')->constrained('accounts')->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_line_id')->nullable()->constrained()->nullOnDelete();
                $table->date('entry_date');
                $table->decimal('debit', 18, 2)->default(0);
                $table->decimal('credit', 18, 2)->default(0);
                $table->string('reference', 100)->nullable();
                $table->text('narration')->nullable();
                $table->unsignedBigInteger('party_id')->nullable();
                $table->string('party_type', 50)->nullable();
                $table->timestamps();
                $table->index(['company_id','account_id','entry_date'], 'le_company_account_date_idx');
                $table->index(['company_id','fiscal_year_id','entry_date'], 'le_company_fy_date_idx');
            });
        }

        // ── Stock Movements (Phase 4) ─────────────────────────────────────────
        if (!Schema::hasTable('stock_movements')) {
            Schema::create('stock_movements', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('item_id')->constrained()->cascadeOnDelete();
                $table->foreignId('warehouse_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->date('movement_date');
                $table->enum('type', ['in','out','transfer_in','transfer_out','adjustment'])->default('in');
                $table->decimal('quantity', 18, 4)->default(0);
                $table->decimal('unit_cost', 18, 6)->default(0);
                $table->decimal('total_cost', 18, 2)->default(0);
                $table->decimal('running_qty', 18, 4)->default(0);
                $table->decimal('running_avg_cost', 18, 6)->default(0);
                $table->timestamps();
                $table->index(['company_id','item_id','warehouse_id','movement_date'], 'sm_item_wh_date_idx');
            });
        }

        // ── Item Serial Numbers (Phase 4) ─────────────────────────────────────
        if (!Schema::hasTable('item_serial_numbers')) {
            Schema::create('item_serial_numbers', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('item_id')->constrained()->cascadeOnDelete();
                $table->foreignId('warehouse_id')->nullable()->constrained()->nullOnDelete();
                $table->string('serial_number', 100);
                $table->enum('status', ['in_stock','sold','returned','damaged','in_transit'])->default('in_stock');
                $table->date('warranty_expiry')->nullable();
                $table->nullableMorphs('source_voucher');
                $table->timestamps();
                $table->unique(['company_id','serial_number']);
                $table->index(['company_id','item_id','status'], 'isn_company_item_status_idx');
            });
        }

        // ── Item Lot Numbers (Phase 4) ────────────────────────────────────────
        if (!Schema::hasTable('item_lot_numbers')) {
            Schema::create('item_lot_numbers', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('item_id')->constrained()->cascadeOnDelete();
                $table->foreignId('warehouse_id')->nullable()->constrained()->nullOnDelete();
                $table->string('lot_number', 100);
                $table->date('manufacturing_date')->nullable();
                $table->date('expiry_date')->nullable();
                $table->decimal('original_qty', 18, 4)->default(0);
                $table->decimal('remaining_qty', 18, 4)->default(0);
                $table->nullableMorphs('source_voucher');
                $table->timestamps();
                $table->index(['company_id','item_id','expiry_date'], 'iln_company_item_expiry_idx');
            });
        }

        // ── Item Price Levels (Phase 4) ───────────────────────────────────────
        if (!Schema::hasTable('item_price_levels')) {
            Schema::create('item_price_levels', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('item_id')->constrained()->cascadeOnDelete();
                $table->foreignId('price_level_id')->constrained()->cascadeOnDelete();
                $table->foreignId('currency_id')->nullable()->constrained()->nullOnDelete();
                $table->decimal('unit_price', 18, 6)->default(0);
                $table->date('effective_date')->nullable();
                $table->timestamps();
                $table->index(['item_id','price_level_id','effective_date'], 'ipl_item_level_date_idx');
            });
        }

        // ── AR: Quotations ────────────────────────────────────────────────────
        if (!Schema::hasTable('quotations')) {
            Schema::create('quotations', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
                $table->date('valid_until')->nullable();
                $table->enum('conversion_status', ['pending','converted','expired','cancelled'])->default('pending');
                $table->unsignedBigInteger('sales_order_id')->nullable();
                $table->timestamps();
                $table->index(['company_id','customer_id']);
            });
        }

        // ── AR: Sales Orders ──────────────────────────────────────────────────
        if (!Schema::hasTable('sales_orders')) {
            Schema::create('sales_orders', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
                $table->foreignId('quotation_id')->nullable()->constrained()->nullOnDelete();
                $table->date('delivery_date')->nullable();
                $table->enum('fulfillment_status', ['pending','partial','fulfilled','cancelled'])->default('pending');
                $table->timestamps();
                $table->index(['company_id','customer_id']);
            });
        }

        // Add deferred FK: quotations.sales_order_id → sales_orders
        if (Schema::hasTable('quotations') && Schema::hasTable('sales_orders')) {
            if (!Schema::hasColumn('quotations', 'sales_order_id')) {
                Schema::table('quotations', function (Blueprint $table) {
                    $table->unsignedBigInteger('sales_order_id')->nullable();
                });
            }
            // Add FK only if not already there (MySQL doesn't have IF NOT EXISTS for constraints)
            try {
                Schema::table('quotations', function (Blueprint $table) {
                    $table->foreign('sales_order_id')
                          ->references('id')->on('sales_orders')
                          ->nullOnDelete();
                });
            } catch (\Exception $e) {
                // FK already exists — safe to ignore
            }
        }

        // ── AR: Delivery Notes ────────────────────────────────────────────────
        if (!Schema::hasTable('delivery_notes')) {
            Schema::create('delivery_notes', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
                $table->foreignId('sales_order_id')->nullable()->constrained()->nullOnDelete();
                $table->timestamps();
                $table->index(['company_id','customer_id']);
            });
        }

        // ── AR: Sales Invoices ────────────────────────────────────────────────
        if (!Schema::hasTable('sales_invoices')) {
            Schema::create('sales_invoices', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
                $table->foreignId('sales_order_id')->nullable()->constrained()->nullOnDelete();
                $table->date('due_date')->nullable();
                $table->enum('payment_status', ['unpaid','partial','paid'])->default('unpaid');
                $table->decimal('amount_paid', 18, 2)->default(0);
                $table->timestamps();
                $table->index(['company_id','customer_id']);
            });
        }

        // ── AR: Sales Returns ─────────────────────────────────────────────────
        if (!Schema::hasTable('sales_returns')) {
            Schema::create('sales_returns', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
                $table->foreignId('sales_invoice_id')->nullable()->constrained()->nullOnDelete();
                $table->timestamps();
                $table->index(['company_id','customer_id']);
            });
        }

        // ── AR: Receive Payments ──────────────────────────────────────────────
        if (!Schema::hasTable('receive_payments')) {
            Schema::create('receive_payments', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
                $table->decimal('amount', 18, 2)->default(0);
                $table->timestamps();
                $table->index(['company_id','customer_id']);
            });
        }

        // ── AP: Purchase Requisitions ─────────────────────────────────────────
        if (!Schema::hasTable('purchase_requisitions')) {
            Schema::create('purchase_requisitions', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->unsignedBigInteger('requested_by')->nullable();
                $table->timestamps();
                $table->index('company_id');
            });
        }

        // ── AP: Purchase Orders ───────────────────────────────────────────────
        if (!Schema::hasTable('purchase_orders')) {
            Schema::create('purchase_orders', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('vendor_id')->constrained()->cascadeOnDelete();
                $table->foreignId('purchase_req_id')->nullable()->constrained('purchase_requisitions')->nullOnDelete();
                $table->date('expected_date')->nullable();
                $table->enum('fulfillment_status', ['pending','partial','fulfilled','cancelled'])->default('pending');
                $table->timestamps();
                $table->index(['company_id','vendor_id']);
            });
        }

        // ── AP: GRN ───────────────────────────────────────────────────────────
        if (!Schema::hasTable('goods_receipts')) {
            Schema::create('goods_receipts', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('vendor_id')->constrained()->cascadeOnDelete();
                $table->foreignId('purchase_order_id')->nullable()->constrained()->nullOnDelete();
                $table->timestamps();
                $table->index(['company_id','vendor_id']);
            });
        }

        // ── AP: Bills ─────────────────────────────────────────────────────────
        if (!Schema::hasTable('bills')) {
            Schema::create('bills', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('vendor_id')->constrained()->cascadeOnDelete();
                $table->foreignId('grn_id')->nullable()->constrained('goods_receipts')->nullOnDelete();
                $table->date('due_date')->nullable();
                $table->enum('payment_status', ['unpaid','partial','paid'])->default('unpaid');
                $table->decimal('amount_paid', 18, 2)->default(0);
                $table->timestamps();
                $table->index(['company_id','vendor_id']);
            });
        }

        // ── AP: Bill Payments ─────────────────────────────────────────────────
        if (!Schema::hasTable('bill_payments')) {
            Schema::create('bill_payments', function (Blueprint $table) {
                $table->id();
                $table->foreignId('company_id')->constrained()->cascadeOnDelete();
                $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
                $table->foreignId('vendor_id')->constrained()->cascadeOnDelete();
                $table->decimal('amount', 18, 2)->default(0);
                $table->timestamps();
                $table->index(['company_id','vendor_id']);
            });
        }
    }

    public function down(): void
    {
        // Drop in reverse FK order
        Schema::dropIfExists('bill_payments');
        Schema::dropIfExists('bills');
        Schema::dropIfExists('goods_receipts');
        Schema::dropIfExists('purchase_orders');
        Schema::dropIfExists('purchase_requisitions');
        Schema::dropIfExists('receive_payments');
        Schema::dropIfExists('sales_returns');
        Schema::dropIfExists('sales_invoices');
        Schema::dropIfExists('delivery_notes');
        Schema::dropIfExists('quotations');
        Schema::dropIfExists('sales_orders');
        Schema::dropIfExists('ledger_entries');
        Schema::dropIfExists('voucher_approvals');
        Schema::dropIfExists('voucher_lines');
        Schema::dropIfExists('vouchers');
        Schema::dropIfExists('stock_movements');
        Schema::dropIfExists('item_lot_numbers');
        Schema::dropIfExists('item_serial_numbers');
        Schema::dropIfExists('item_price_levels');
        Schema::dropIfExists('transaction_attachments');
        Schema::dropIfExists('audit_logs');
        Schema::dropIfExists('voucher_number_settings');
        Schema::dropIfExists('dimension_values');
        Schema::dropIfExists('dimensions');
    }
};
