<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('expense_categories', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained()->cascadeOnDelete();
            $table->string('name', 150);
            $table->boolean('is_active')->default(true);
            $table->timestamps();

            $table->unique(['company_id', 'name']);
        });

        Schema::table('expenses', function (Blueprint $table) {
            $table->foreignId('vendor_id')->nullable()->after('company_id')->constrained()->nullOnDelete();
            $table->foreignId('customer_id')->nullable()->after('vendor_id')->constrained()->nullOnDelete();
            $table->string('expense_number', 50)->nullable()->after('customer_id');
            $table->string('expense_prefix', 20)->nullable()->after('expense_number');
            $table->unsignedInteger('expense_seq')->nullable()->after('expense_prefix');
            $table->string('payment_method', 30)->default('cash')->after('expense_date');
            $table->string('status', 20)->default('draft')->after('payment_method');
            $table->string('place_of_supply', 100)->nullable()->after('status');
            $table->boolean('is_igst')->default(false)->after('place_of_supply');
            $table->string('tax_on', 20)->default('bill_to')->after('is_igst');
            $table->decimal('subtotal', 15, 2)->default(0)->after('amount');
            $table->decimal('taxable_amount', 15, 2)->default(0)->after('subtotal');
            $table->decimal('discount_amount', 15, 2)->default(0)->after('taxable_amount');
            $table->decimal('shipping_charges', 15, 2)->default(0)->after('discount_amount');
            $table->decimal('discount_on_total', 15, 2)->default(0)->after('shipping_charges');
            $table->decimal('discount_all_percent', 8, 2)->default(0)->after('discount_on_total');
            $table->string('discount_all_type', 10)->default('percent')->after('discount_all_percent');
            $table->decimal('discount_all_value', 15, 2)->default(0)->after('discount_all_type');
            $table->decimal('custom_amount', 15, 2)->default(0)->after('discount_all_value');
            $table->string('custom_amount_label', 100)->nullable()->after('custom_amount');
            $table->decimal('total_tax', 15, 2)->default(0)->after('custom_amount_label');
            $table->decimal('cgst_amount', 15, 2)->default(0)->after('total_tax');
            $table->decimal('sgst_amount', 15, 2)->default(0)->after('cgst_amount');
            $table->decimal('igst_amount', 15, 2)->default(0)->after('sgst_amount');
            $table->decimal('grand_total', 15, 2)->default(0)->after('igst_amount');
            $table->decimal('round_off', 15, 2)->default(0)->after('grand_total');
            $table->boolean('has_shipping')->default(false)->after('round_off');
            $table->boolean('has_discount_total')->default(false)->after('has_shipping');
            $table->boolean('has_discount_all')->default(false)->after('has_discount_total');
            $table->boolean('has_custom_amount')->default(false)->after('has_discount_all');
            $table->boolean('has_total_quantity')->default(false)->after('has_custom_amount');
            $table->string('total_quantity_unit', 20)->nullable()->after('has_total_quantity');
            $table->decimal('total_quantity', 15, 3)->nullable()->after('total_quantity_unit');
            $table->boolean('show_cess')->default(false)->after('total_quantity');
            $table->boolean('reverse_charge')->default(false)->after('show_cess');
            $table->string('shipping_mode', 30)->nullable()->after('reverse_charge');
            $table->text('terms')->nullable()->after('notes');
            $table->text('private_notes')->nullable()->after('terms');
            $table->string('share_token', 64)->nullable()->unique()->after('private_notes');
        });

        DB::statement('UPDATE expenses SET payment_method = payment_mode WHERE payment_method IS NULL OR payment_method = ""');
        DB::table('expenses')->update([
            'grand_total' => DB::raw('COALESCE(grand_total, amount)'),
            'subtotal' => DB::raw('COALESCE(NULLIF(subtotal, 0), amount)'),
        ]);

        foreach (DB::table('expenses')->whereNull('expense_number')->orderBy('id')->get() as $row) {
            DB::table('expenses')->where('id', $row->id)->update([
                'expense_number' => 'EXP-'.str_pad((string) $row->id, 4, '0', STR_PAD_LEFT),
                'expense_prefix' => 'EXP',
                'expense_seq' => $row->id,
                'status' => 'recorded',
            ]);
        }

        Schema::table('expenses', function (Blueprint $table) {
            $table->unique(['company_id', 'expense_number']);
        });

        Schema::create('expense_items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('expense_id')->constrained()->cascadeOnDelete();
            $table->foreignId('expense_category_id')->nullable()->constrained()->nullOnDelete();
            $table->string('item_name');
            $table->text('description')->nullable();
            $table->string('hsn_sac', 20)->nullable();
            $table->string('unit', 20)->default('NOS');
            $table->decimal('quantity', 15, 3)->default(1);
            $table->decimal('rate', 15, 2)->default(0);
            $table->decimal('discount_percent', 8, 2)->default(0);
            $table->decimal('discount', 15, 2)->default(0);
            $table->decimal('tax_rate', 5, 2)->default(0);
            $table->decimal('cgst_rate', 5, 2)->default(0);
            $table->decimal('sgst_rate', 5, 2)->default(0);
            $table->decimal('igst_rate', 5, 2)->default(0);
            $table->decimal('tax_amount', 15, 2)->default(0);
            $table->decimal('cgst_amount', 15, 2)->default(0);
            $table->decimal('sgst_amount', 15, 2)->default(0);
            $table->decimal('igst_amount', 15, 2)->default(0);
            $table->decimal('amount', 15, 2)->default(0);
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('expense_items');
        Schema::table('expenses', function (Blueprint $table) {
            $table->dropForeign(['vendor_id']);
            $table->dropForeign(['customer_id']);
            $table->dropUnique(['company_id', 'expense_number']);
            $table->dropColumn([
                'vendor_id', 'customer_id', 'expense_number', 'expense_prefix', 'expense_seq',
                'payment_method', 'status', 'place_of_supply', 'is_igst', 'tax_on',
                'subtotal', 'taxable_amount', 'discount_amount', 'shipping_charges',
                'discount_on_total', 'discount_all_percent', 'discount_all_type', 'discount_all_value',
                'custom_amount', 'custom_amount_label', 'total_tax', 'cgst_amount', 'sgst_amount',
                'igst_amount', 'grand_total', 'round_off', 'has_shipping', 'has_discount_total',
                'has_discount_all', 'has_custom_amount', 'has_total_quantity', 'total_quantity_unit',
                'total_quantity', 'show_cess', 'reverse_charge', 'shipping_mode', 'terms', 'private_notes', 'share_token',
            ]);
        });
        Schema::dropIfExists('expense_categories');
    }
};
