<?php
// =============================================================================
// CONSOLIDATED MIGRATION — SaaS Accounting Software  v1.0
// Run: php artisan migrate
// Run: php artisan db:seed
// =============================================================================

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

return new class extends Migration {

    public function up(): void
    {
        // ── Super Admins ──────────────────────────────────────────────────────
        Schema::create('super_admins', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->string('avatar')->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamp('last_login_at')->nullable();
            $table->string('last_login_ip', 45)->nullable();
            $table->rememberToken();
            $table->timestamps();
        });

        // ── Companies ─────────────────────────────────────────────────────────
        Schema::create('companies', function (Blueprint $table) {
            $table->id();
            $table->string('company_code', 20)->unique();
            $table->string('name', 150);
            $table->string('legal_name', 150)->nullable();
            $table->string('email', 150)->nullable();
            $table->string('phone', 30)->nullable();
            $table->text('address')->nullable();
            $table->string('city', 80)->nullable();
            $table->string('country', 80)->nullable();
            $table->string('tax_id', 50)->nullable();
            $table->string('logo', 500)->nullable();
            $table->string('website', 200)->nullable();
            $table->enum('status', ['active','suspended','expired','trial'])->default('trial');
            $table->timestamps();
            $table->softDeletes();
        });

        // ── Company Subscriptions ─────────────────────────────────────────────
        Schema::create('company_subscriptions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->unsignedSmallInteger('max_concurrent_users')->default(5);
            $table->unsignedSmallInteger('session_timeout_minutes')->default(120);
            $table->json('modules_enabled')->nullable();
            $table->date('trial_ends_at')->nullable();
            $table->date('subscription_starts_at')->nullable();
            $table->date('subscription_ends_at')->nullable();
            $table->decimal('monthly_fee', 10, 2)->default(0);
            $table->string('billing_cycle', 20)->default('monthly');
            $table->text('notes')->nullable();
            $table->timestamps();
        });

        // ── Users ─────────────────────────────────────────────────────────────
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('name', 100);
            $table->string('username', 50);
            $table->string('email', 150)->nullable();
            $table->string('password');
            $table->string('phone', 30)->nullable();
            $table->string('avatar', 500)->nullable();
            $table->boolean('is_admin')->default(false);
            $table->boolean('is_active')->default(true);
            $table->timestamp('last_login_at')->nullable();
            $table->string('last_login_ip', 45)->nullable();
            $table->timestamp('password_changed_at')->nullable();
            $table->boolean('force_password_change')->default(false);
            $table->rememberToken();
            $table->timestamps();
            $table->softDeletes();
            $table->unique(['company_id','username']);
            $table->index(['company_id','is_active']);
        });

        // ── Company Sessions (Laravel session table — custom) ─────────────────
        Schema::create('company_sessions', function (Blueprint $table) {
            $table->string('id')->primary();
            $table->foreignId('company_id')->nullable()->index()->constrained()->nullOnDelete();
            $table->foreignId('user_id')->nullable()->index()->constrained()->nullOnDelete();
            $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable();
            $table->longText('payload');
            $table->integer('last_activity')->index();
            $table->timestamp('logged_in_at')->nullable();
        });

        // ── Roles ─────────────────────────────────────────────────────────────
        Schema::create('roles', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('name', 100);
            $table->string('display_name', 100)->nullable();
            $table->text('description')->nullable();
            $table->boolean('is_system')->default(false);
            $table->timestamps();
            $table->unique(['company_id','name']);
        });

        // ── Permissions ───────────────────────────────────────────────────────
        Schema::create('permissions', function (Blueprint $table) {
            $table->id();
            $table->string('module', 100);
            $table->string('action', 100);
            $table->string('display_name', 150)->nullable();
            $table->timestamps();
            $table->unique(['module','action']);
        });

        // ── Role Permissions ──────────────────────────────────────────────────
        Schema::create('role_permissions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('role_id')->constrained()->cascadeOnDelete();
            $table->foreignId('permission_id')->constrained()->cascadeOnDelete();
            $table->unique(['role_id','permission_id']);
        });

        // ── User Roles ────────────────────────────────────────────────────────
        Schema::create('user_roles', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            $table->foreignId('role_id')->constrained()->cascadeOnDelete();
            $table->unique(['user_id','role_id']);
        });

        // ── Settings ──────────────────────────────────────────────────────────
        Schema::create('settings', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('key', 100);
            $table->text('value')->nullable();
            $table->timestamps();
            $table->unique(['company_id','key']);
        });

        // ── Fiscal Years ──────────────────────────────────────────────────────
        Schema::create('fiscal_years', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('name', 50);
            $table->date('start_date');
            $table->date('end_date');
            $table->enum('status', ['open','locked'])->default('open');
            $table->boolean('is_current')->default(false);
            $table->timestamp('closed_at')->nullable();
            $table->foreignId('closed_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->unique(['company_id','name']);
            $table->index(['company_id','is_current']);
        });

        // ── Accounting Periods ────────────────────────────────────────────────
        Schema::create('accounting_periods', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('fiscal_year_id')->constrained()->cascadeOnDelete();
            $table->string('name', 30);
            $table->date('start_date');
            $table->date('end_date');
            $table->enum('status', ['open','locked'])->default('open');
            $table->timestamp('locked_at')->nullable();
            $table->foreignId('locked_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->index(['company_id','fiscal_year_id']);
        });

        // ── Currencies ────────────────────────────────────────────────────────
        Schema::create('currencies', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('code', 3);
            $table->string('name', 50);
            $table->string('symbol', 10);
            $table->boolean('is_base')->default(false);
            $table->boolean('is_active')->default(true);
            $table->unsignedTinyInteger('decimal_places')->default(2);
            $table->timestamps();
            $table->unique(['company_id','code']);
        });

        // ── Exchange Rates ────────────────────────────────────────────────────
        Schema::create('exchange_rates', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('currency_id')->constrained()->cascadeOnDelete();
            $table->date('rate_date');
            $table->decimal('rate', 18, 6);
            $table->timestamps();
            $table->unique(['company_id','currency_id','rate_date'], 'er_company_currency_date_uniq');
        });

        // ── Accounts (Chart of Accounts) ──────────────────────────────────────
        Schema::create('accounts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('parent_id')->nullable()->constrained('accounts')->nullOnDelete();
            // code nullable but unique per company — NULL values are excluded from unique check in MySQL
            $table->string('code', 30)->nullable();
            $table->string('name', 150);
            $table->enum('type', ['asset','liability','equity','income','expense']);
            $table->enum('sub_type', [
                'current_asset','fixed_asset','other_asset',
                'current_liability','long_term_liability','other_liability',
                'equity','retained_earnings',
                'income','other_income',
                'cost_of_goods','expense','other_expense'
            ])->nullable();
            $table->string('detail_type', 100)->nullable();
            $table->boolean('is_group')->default(false);
            $table->boolean('is_system')->default(false);
            $table->boolean('is_active')->default(true);
            $table->boolean('is_bank')->default(false);
            $table->boolean('is_cash')->default(false);
            $table->text('description')->nullable();
            $table->unsignedSmallInteger('sort_order')->default(0);
            $table->unsignedTinyInteger('depth')->default(0);
            $table->string('path', 500)->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->index(['company_id','type','is_active'], 'acc_company_type_active_idx');
            // Partial unique: only enforce unique code when code is not null (MySQL allows multi-NULL)
            $table->unique(['company_id','code']);
        });

        // ── 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']);
        });

        // ── 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','is_active']);
        });

        // ── Customers ────────────────────────────────────────────────────────
        Schema::create('customers', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('parent_id')->nullable()->constrained('customers')->nullOnDelete();
            $table->boolean('is_job')->default(false);
            $table->string('code', 30)->nullable();
            $table->string('name', 150);
            $table->string('contact_person', 100)->nullable();
            $table->string('email', 150)->nullable();
            $table->string('phone', 30)->nullable();
            $table->text('billing_address')->nullable();
            $table->text('shipping_address')->nullable();
            $table->string('tax_id', 50)->nullable();
            $table->foreignId('currency_id')->nullable()->constrained()->nullOnDelete();
            $table->unsignedBigInteger('price_level_id')->nullable(); // FK added after price_levels table
            $table->decimal('credit_limit', 18, 2)->default(0);
            $table->unsignedSmallInteger('payment_terms_days')->default(30);
            $table->foreignId('account_receivable_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->boolean('is_active')->default(true);
            $table->text('notes')->nullable();
            $table->unsignedTinyInteger('depth')->default(0);
            $table->string('path', 500)->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->index(['company_id','is_active']);
        });

        // ── Vendors ───────────────────────────────────────────────────────────
        Schema::create('vendors', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('parent_id')->nullable()->constrained('vendors')->nullOnDelete();
            $table->string('code', 30)->nullable();
            $table->string('name', 150);
            $table->string('contact_person', 100)->nullable();
            $table->string('email', 150)->nullable();
            $table->string('phone', 30)->nullable();
            $table->text('address')->nullable();
            $table->string('tax_id', 50)->nullable();
            $table->foreignId('currency_id')->nullable()->constrained()->nullOnDelete();
            $table->unsignedSmallInteger('payment_terms_days')->default(30);
            $table->string('bank_name', 100)->nullable();
            $table->string('bank_account', 50)->nullable();
            $table->string('bank_routing', 50)->nullable();
            $table->foreignId('account_payable_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->boolean('is_active')->default(true);
            $table->text('notes')->nullable();
            $table->unsignedTinyInteger('depth')->default(0);
            $table->string('path', 500)->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->index(['company_id','is_active']);
        });

        // ── Employees ─────────────────────────────────────────────────────────
        Schema::create('employees', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('parent_id')->nullable()->constrained('employees')->nullOnDelete();
            $table->string('code', 30)->nullable();
            $table->string('name', 150);
            $table->string('designation', 100)->nullable();
            $table->string('department', 100)->nullable();
            $table->string('email', 150)->nullable();
            $table->string('phone', 30)->nullable();
            $table->date('joining_date')->nullable();
            $table->string('bank_account', 50)->nullable();
            $table->boolean('is_active')->default(true);
            $table->unsignedTinyInteger('depth')->default(0);
            $table->timestamps();
            $table->softDeletes();
            $table->index(['company_id','is_active']);
        });

        // ── Other Names ───────────────────────────────────────────────────────
        Schema::create('other_names', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('code', 30)->nullable();
            $table->string('name', 150);
            $table->string('type', 50)->nullable();
            $table->string('phone', 30)->nullable();
            $table->string('email', 150)->nullable();
            $table->text('address')->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->softDeletes();
            $table->index(['company_id','is_active']);
        });

        // ── Tax Codes ─────────────────────────────────────────────────────────
        Schema::create('tax_codes', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('code', 30);
            $table->string('name', 100);
            $table->decimal('rate', 8, 4);
            $table->enum('type', ['inclusive','exclusive'])->default('exclusive');
            $table->enum('applicable_on', ['sales','purchase','both'])->default('both');
            $table->foreignId('sales_account_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->foreignId('purchase_account_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->unique(['company_id','code']);
        });

        // ── Warehouses ────────────────────────────────────────────────────────
        Schema::create('warehouses', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('parent_id')->nullable()->constrained('warehouses')->nullOnDelete();
            $table->string('code', 30)->nullable();
            $table->string('name', 150);
            $table->enum('type', ['warehouse','zone','rack','bin'])->default('warehouse');
            $table->text('address')->nullable();
            $table->boolean('is_active')->default(true);
            $table->unsignedTinyInteger('depth')->default(0);
            $table->string('path', 500)->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->index(['company_id','is_active']);
        });

        // ── Units of Measure ──────────────────────────────────────────────────
        Schema::create('uoms', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('code', 20);
            $table->string('name', 80);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->unique(['company_id','code']);
        });

        // ── Item Categories ───────────────────────────────────────────────────
        Schema::create('item_categories', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('parent_id')->nullable()->constrained('item_categories')->nullOnDelete();
            $table->string('code', 30)->nullable();
            $table->string('name', 150);
            $table->boolean('is_active')->default(true);
            $table->unsignedTinyInteger('depth')->default(0);
            $table->string('path', 500)->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->index(['company_id','is_active']);
        });

        // ── Price Levels ──────────────────────────────────────────────────────
        // Defined before items because customers references price_levels
        Schema::create('price_levels', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('name', 100);
            $table->text('description')->nullable();
            $table->boolean('is_default')->default(false);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->index(['company_id','is_active']);
        });

        // ── Add price_level_id FK to customers now that price_levels exists ────
        Schema::table('customers', function (Blueprint $table) {
            $table->foreign('price_level_id')->references('id')->on('price_levels')->nullOnDelete();
        });

        // ── Items ─────────────────────────────────────────────────────────────
        Schema::create('items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('category_id')->nullable()->constrained('item_categories')->nullOnDelete();
            $table->string('code', 50);
            $table->string('name', 200);
            $table->text('description')->nullable();
            $table->string('barcode', 100)->nullable();
            $table->enum('type', ['inventory','service','non_inventory'])->default('inventory');
            $table->foreignId('uom_id')->nullable()->constrained('uoms')->nullOnDelete();
            $table->foreignId('default_warehouse_id')->nullable()->constrained('warehouses')->nullOnDelete();
            $table->foreignId('inventory_account_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->foreignId('cogs_account_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->foreignId('sales_account_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->foreignId('purchase_account_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->decimal('reorder_level', 18, 4)->default(0);
            $table->decimal('opening_stock', 18, 4)->default(0);
            $table->enum('valuation_method', ['weighted_average','fifo'])->default('weighted_average');
            $table->enum('tracking_type', ['none','serial','lot','both'])->default('none');
            $table->boolean('is_active')->default(true);
            $table->boolean('is_sold')->default(true);
            $table->boolean('is_purchased')->default(true);
            $table->timestamps();
            $table->softDeletes();
            $table->unique(['company_id','code']);
            $table->index(['company_id','barcode']);
            $table->index(['company_id','is_active']);
        });

        // ── 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');
        });

        // ── 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');
        });

        // ── 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');
        });

        // ── 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']);
        });

        // ── Vouchers (Master) ─────────────────────────────────────────────────
        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);
            // Polymorphic party (customer / vendor / employee / other)
            $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']);
            // voucher_number unique enforced in app layer (nullable column)
            $table->index(['company_id','voucher_type','voucher_number'], 'v_company_type_number_idx');
        });

        // ── 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');
            // 7 user-defined dimensions
            $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 ─────────────────────────────────────────────────
        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 ───────────────────────────────────────────
        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_index');
        });

        // ── 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 ───────────────────────────────────────────────────
        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_company_item_wh_date_idx');
        });

        // ── AR: 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(); // FK added after sales_orders
            $table->timestamps();
            $table->index(['company_id','customer_id']);
        });

        // ── AR: 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 sales_order_id FK to quotations now that sales_orders exists
        Schema::table('quotations', function (Blueprint $table) {
            $table->foreign('sales_order_id')->references('id')->on('sales_orders')->nullOnDelete();
        });

        // ── AR: 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->foreignId('warehouse_id')->nullable()->constrained()->nullOnDelete();
            $table->enum('invoice_status', ['pending','invoiced','partial'])->default('pending');
            $table->timestamps();
            $table->index(['company_id','customer_id']);
        });

        // ── AR: 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->foreignId('delivery_note_id')->nullable()->constrained()->nullOnDelete();
            $table->date('due_date')->nullable();
            $table->decimal('amount_paid', 18, 2)->default(0);
            $table->decimal('amount_due', 18, 2)->default(0);
            $table->enum('payment_status', ['unpaid','partial','paid','overdue'])->default('unpaid');
            $table->timestamps();
            $table->index(['company_id','customer_id','payment_status'], 'si_company_cust_status_idx');
        });

        // ── AR: 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->foreignId('bank_account_id')->nullable()->constrained('accounts')->nullOnDelete();
            $table->string('payment_method', 50)->nullable();
            $table->string('cheque_number', 50)->nullable();
            $table->date('cheque_date')->nullable();
            $table->timestamps();
            $table->index(['company_id','customer_id']);
        });

        // ── AR: Payment Allocations ───────────────────────────────────────────
        Schema::create('payment_allocations', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('payment_id')->constrained('receive_payments')->cascadeOnDelete();
            $table->foreignId('invoice_id')->constrained('sales_invoices')->cascadeOnDelete();
            $table->decimal('allocated_amount', 18, 2)->default(0);
            $table->timestamps();
            $table->index(['company_id','payment_id']);
        });

        // ── AP: Purchase Requisitions ─────────────────────────────────────────
        Schema::create('purchase_requisitions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
            $table->foreignId('requested_by')->nullable()->constrained('users')->nullOnDelete();
            $table->date('required_date')->nullable();
            $table->enum('po_status', ['pending','partial','converted','cancelled'])->default('pending');
            $table->timestamps();
            $table->index(['company_id','po_status']);
        });

        // ── AP: 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('requisition_id')->nullable()->constrained('purchase_requisitions')->nullOnDelete();
            $table->date('expected_date')->nullable();
            $table->enum('receipt_status', ['pending','partial','received','cancelled'])->default('pending');
            $table->timestamps();
            $table->index(['company_id','vendor_id']);
        });

        // ── AP: GRN ───────────────────────────────────────────────────────────
        Schema::create('grns', 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->foreignId('warehouse_id')->nullable()->constrained()->nullOnDelete();
            $table->enum('bill_status', ['pending','partial','billed'])->default('pending');
            $table->timestamps();
            $table->index(['company_id','vendor_id']);
        });

        // ── AP: 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()->nullOnDelete();
            $table->date('due_date')->nullable();
            $table->decimal('amount_paid', 18, 2)->default(0);
            $table->decimal('amount_due', 18, 2)->default(0);
            $table->enum('payment_status', ['unpaid','partial','paid','overdue'])->default('unpaid');
            $table->timestamps();
            $table->index(['company_id','vendor_id','payment_status'], 'bills_company_vendor_status_idx');
        });

        // ── Manufacturing: BOM ────────────────────────────────────────────────
        Schema::create('boms', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('finished_item_id')->constrained('items')->cascadeOnDelete();
            $table->string('name', 150);
            $table->string('version', 20)->default('1.0');
            $table->decimal('output_quantity', 18, 4)->default(1);
            $table->foreignId('uom_id')->nullable()->constrained('uoms')->nullOnDelete();
            $table->boolean('is_active')->default(true);
            $table->text('notes')->nullable();
            $table->timestamps();
            $table->index(['company_id','finished_item_id']);
        });

        // ── Manufacturing: BOM Lines ──────────────────────────────────────────
        Schema::create('bom_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('bom_id')->constrained()->cascadeOnDelete();
            $table->foreignId('item_id')->constrained()->cascadeOnDelete();
            $table->decimal('quantity', 18, 4)->default(0);
            $table->foreignId('uom_id')->nullable()->constrained('uoms')->nullOnDelete();
            $table->decimal('wastage_percent', 8, 4)->default(0);
            $table->timestamps();
            $table->index(['company_id','bom_id']);
        });

        // ── Manufacturing: Production Orders ──────────────────────────────────
        Schema::create('production_orders', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
            $table->foreignId('bom_id')->constrained()->cascadeOnDelete();
            $table->foreignId('finished_item_id')->constrained('items')->cascadeOnDelete();
            $table->foreignId('warehouse_id')->nullable()->constrained()->nullOnDelete();
            $table->decimal('planned_quantity', 18, 4)->default(0);
            $table->decimal('produced_quantity', 18, 4)->default(0);
            $table->date('planned_start')->nullable();
            $table->date('planned_end')->nullable();
            $table->enum('production_status', ['planned','in_progress','completed','cancelled'])->default('planned');
            $table->timestamps();
            $table->index(['company_id','production_status']);
        });

        // ── Bank Accounts ─────────────────────────────────────────────────────
        Schema::create('bank_accounts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('account_id')->constrained('accounts')->cascadeOnDelete();
            $table->string('bank_name', 100);
            $table->string('account_number', 50)->nullable();
            $table->string('account_name', 100)->nullable();
            $table->string('branch', 100)->nullable();
            $table->string('routing_number', 50)->nullable();
            $table->foreignId('currency_id')->nullable()->constrained()->nullOnDelete();
            $table->decimal('opening_balance', 18, 2)->default(0);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->index(['company_id','is_active']);
        });

        // ── Bank Reconciliations ──────────────────────────────────────────────
        Schema::create('bank_reconciliations', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('bank_account_id')->constrained()->cascadeOnDelete();
            $table->date('statement_date');
            $table->decimal('statement_balance', 18, 2)->default(0);
            $table->decimal('book_balance', 18, 2)->default(0);
            $table->decimal('reconciled_balance', 18, 2)->default(0);
            $table->enum('status', ['in_progress','reconciled'])->default('in_progress');
            $table->foreignId('reconciled_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('reconciled_at')->nullable();
            $table->timestamps();
            $table->index(['company_id','bank_account_id','statement_date'], 'br_company_bank_date_idx');
        });

        // ── Budgets ───────────────────────────────────────────────────────────
        Schema::create('budgets', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('fiscal_year_id')->constrained()->cascadeOnDelete();
            $table->string('name', 150);
            $table->enum('type', ['account','dimension','customer','job'])->default('account');
            $table->string('version', 20)->default('Original');
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->index(['company_id','fiscal_year_id']);
        });

        // ── Budget Lines ──────────────────────────────────────────────────────
        Schema::create('budget_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->foreignId('budget_id')->constrained()->cascadeOnDelete();
            $table->foreignId('account_id')->constrained('accounts')->cascadeOnDelete();
            $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('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->decimal('jan', 18, 2)->default(0);
            $table->decimal('feb', 18, 2)->default(0);
            $table->decimal('mar', 18, 2)->default(0);
            $table->decimal('apr', 18, 2)->default(0);
            $table->decimal('may', 18, 2)->default(0);
            $table->decimal('jun', 18, 2)->default(0);
            $table->decimal('jul', 18, 2)->default(0);
            $table->decimal('aug', 18, 2)->default(0);
            $table->decimal('sep', 18, 2)->default(0);
            $table->decimal('oct', 18, 2)->default(0);
            $table->decimal('nov', 18, 2)->default(0);
            $table->decimal('dec', 18, 2)->default(0);
            $table->decimal('total', 18, 2)->default(0);
            $table->timestamps();
            $table->index(['company_id','budget_id']);
        });

        // ── Audit Logs (permanent — no updated_at) ────────────────────────────
        Schema::create('audit_logs', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('company_id')->nullable();
            $table->unsignedBigInteger('user_id')->nullable();
            $table->string('user_type', 20)->default('user');
            $table->string('action', 50);
            $table->string('module', 100);
            $table->string('record_id', 50)->nullable();
            $table->json('old_values')->nullable();
            $table->json('new_values')->nullable();
            $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable();
            $table->text('description')->nullable();
            $table->timestamp('created_at')->useCurrent();
            $table->index(['company_id','action','created_at'], 'al_company_action_date_idx');
            $table->index(['company_id','module','created_at'], 'al_company_module_date_idx');
            $table->index('created_at');
        });
    }

    public function down(): void
    {
        // Drop in reverse dependency order
        $tables = [
            'audit_logs',
            'budget_lines','budgets',
            'bank_reconciliations','bank_accounts',
            'production_orders','bom_lines','boms',
            'bills','grns','purchase_orders','purchase_requisitions',
            'payment_allocations','receive_payments','sales_invoices',
            'delivery_notes','sales_orders','quotations',
            'stock_movements','ledger_entries',
            'transaction_attachments','voucher_approvals',
            'voucher_lines','vouchers',
            'voucher_number_settings',
            'item_lot_numbers','item_serial_numbers',
            'item_price_levels','items',
            'price_levels',
            'item_categories',
            'uoms','warehouses',
            'tax_codes','other_names','employees',
            'vendors','customers',
            'dimension_values','dimensions',
            'accounts',
            'exchange_rates','currencies',
            'accounting_periods','fiscal_years',
            'settings',
            'user_roles','role_permissions','permissions','roles',
            'company_sessions',
            'users','company_subscriptions','companies',
            'super_admins',
        ];
        foreach ($tables as $table) {
            Schema::dropIfExists($table);
        }
    }
};
