<?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::create('subscription_plans', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('slug')->unique();
            $table->text('description')->nullable();
            $table->decimal('price_monthly', 12, 2)->default(0);
            $table->decimal('price_yearly', 12, 2)->default(0);
            $table->unsignedInteger('staff_limit')->default(3);
            $table->unsignedInteger('customer_limit')->default(200);
            $table->unsignedInteger('branch_limit')->default(1);
            $table->unsignedInteger('storage_limit_mb')->default(500);
            $table->json('features')->nullable();
            $table->boolean('is_active')->default(true)->index();
            $table->timestamps();
        });

        Schema::create('tenants', function (Blueprint $table) {
            $table->id();
            $table->foreignId('subscription_plan_id')->nullable()->constrained('subscription_plans')->nullOnDelete();
            $table->string('name');
            $table->string('slug')->unique();
            $table->string('subdomain')->nullable()->unique();
            $table->string('owner_name');
            $table->string('email')->unique();
            $table->string('phone')->nullable();
            $table->text('address')->nullable();
            $table->string('logo_path')->nullable();
            $table->string('primary_color', 20)->default('#155e75');
            $table->string('status')->default('pending')->index();
            $table->json('settings')->nullable();
            $table->timestamp('trial_ends_at')->nullable()->index();
            $table->timestamp('subscription_ends_at')->nullable()->index();
            $table->timestamp('approved_at')->nullable();
            $table->timestamp('rejected_at')->nullable();
            $table->text('rejected_reason')->nullable();
            $table->timestamp('suspended_at')->nullable();
            $table->timestamps();
        });

        Schema::create('system_settings', function (Blueprint $table) {
            $table->id();
            $table->string('key')->unique();
            $table->text('value')->nullable();
        });

        Schema::create('branches', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('manager_id')->nullable()->constrained('users')->nullOnDelete();
            $table->string('name');
            $table->string('code');
            $table->text('address')->nullable();
            $table->string('phone')->nullable();
            $table->boolean('is_active')->default(true)->index();
            $table->timestamps();
            $table->unique(['tenant_id', 'code']);
            $table->index(['tenant_id', 'is_active']);
        });

        Schema::table('users', function (Blueprint $table) {
            $table->foreign('tenant_id')->references('id')->on('tenants')->nullOnDelete();
            $table->foreign('branch_id')->references('id')->on('branches')->nullOnDelete();
        });

        Schema::create('subscriptions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('subscription_plan_id')->constrained('subscription_plans')->cascadeOnDelete();
            $table->string('status')->default('trial')->index();
            $table->string('billing_cycle')->default('monthly');
            $table->decimal('amount', 12, 2)->default(0);
            $table->timestamp('starts_at')->nullable();
            $table->timestamp('ends_at')->nullable()->index();
            $table->timestamp('trial_ends_at')->nullable();
            $table->string('reference')->nullable()->index();
            $table->timestamps();
            $table->index(['tenant_id', 'status']);
        });

        Schema::create('tenant_payments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('subscription_plan_id')->constrained('subscription_plans')->cascadeOnDelete();
            $table->decimal('amount', 12, 2);
            $table->string('billing_cycle')->default('monthly');
            $table->string('status')->default('pending')->index();
            $table->string('reference')->unique();
            $table->string('proof_path')->nullable();
            $table->text('notes')->nullable();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('approved_at')->nullable();
            $table->text('rejected_reason')->nullable();
            $table->timestamps();
            $table->index(['tenant_id', 'status']);
        });

        Schema::create('invoices', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('tenant_payment_id')->nullable()->constrained('tenant_payments')->nullOnDelete();
            $table->string('invoice_number')->unique();
            $table->string('status')->default('draft')->index();
            $table->decimal('amount', 12, 2);
            $table->timestamp('issued_at')->nullable();
            $table->timestamp('due_at')->nullable();
            $table->timestamp('paid_at')->nullable();
            $table->json('line_items')->nullable();
            $table->text('notes')->nullable();
            $table->timestamps();
            $table->index(['tenant_id', 'status']);
        });

        Schema::create('customers', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('branch_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
            $table->string('account_number');
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email')->nullable();
            $table->string('phone');
            $table->string('gender')->nullable();
            $table->date('date_of_birth')->nullable();
            $table->string('occupation')->nullable();
            $table->text('address')->nullable();
            $table->string('city')->nullable();
            $table->string('state')->nullable();
            $table->string('photo_path')->nullable();
            $table->string('id_type')->nullable();
            $table->string('id_number')->nullable();
            $table->string('id_upload_path')->nullable();
            $table->string('guarantor_name')->nullable();
            $table->string('guarantor_phone')->nullable();
            $table->text('guarantor_address')->nullable();
            $table->string('guarantor_relationship')->nullable();
            $table->string('status')->default('active')->index();
            $table->string('kyc_status')->default('pending')->index();
            $table->timestamps();
            $table->unique(['tenant_id', 'account_number']);
            $table->index(['tenant_id', 'phone']);
            $table->index(['tenant_id', 'status']);
        });

        Schema::create('savings_accounts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
            $table->string('account_number');
            $table->string('product_name')->default('Regular Savings');
            $table->decimal('balance', 14, 2)->default(0);
            $table->string('status')->default('active')->index();
            $table->timestamp('opened_at')->nullable();
            $table->timestamps();
            $table->unique(['tenant_id', 'account_number']);
            $table->index(['tenant_id', 'status']);
        });

        Schema::create('savings_transactions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('savings_account_id')->constrained()->cascadeOnDelete();
            $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->string('type')->index();
            $table->decimal('amount', 14, 2);
            $table->decimal('balance_before', 14, 2)->default(0);
            $table->decimal('balance_after', 14, 2)->default(0);
            $table->string('reference')->unique();
            $table->string('method')->default('cash');
            $table->text('description')->nullable();
            $table->timestamp('transacted_at')->useCurrent()->index();
            $table->timestamps();
            $table->index(['tenant_id', 'type', 'transacted_at']);
        });

        Schema::create('loan_products', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->text('description')->nullable();
            $table->decimal('interest_rate', 5, 2)->default(0);
            $table->string('interest_method')->default('flat');
            $table->string('repayment_frequency')->default('monthly');
            $table->unsignedInteger('term_days')->default(30);
            $table->decimal('penalty_rate', 5, 2)->default(0);
            $table->decimal('minimum_amount', 14, 2)->default(0);
            $table->decimal('maximum_amount', 14, 2)->default(0);
            $table->boolean('is_active')->default(true)->index();
            $table->timestamps();
            $table->unique(['tenant_id', 'name']);
            $table->index(['tenant_id', 'is_active']);
        });

        Schema::create('loans', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('branch_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
            $table->foreignId('loan_product_id')->nullable()->constrained()->nullOnDelete();
            $table->string('loan_number');
            $table->decimal('principal', 14, 2);
            $table->decimal('interest_rate', 5, 2)->default(0);
            $table->decimal('interest_amount', 14, 2)->default(0);
            $table->decimal('total_payable', 14, 2)->default(0);
            $table->decimal('amount_disbursed', 14, 2)->default(0);
            $table->decimal('balance', 14, 2)->default(0);
            $table->string('repayment_frequency')->default('monthly');
            $table->unsignedInteger('term_days')->default(30);
            $table->string('status')->default('applied')->index();
            $table->text('purpose')->nullable();
            $table->text('collateral')->nullable();
            $table->date('application_date')->index();
            $table->timestamp('approved_at')->nullable();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('disbursed_at')->nullable();
            $table->foreignId('disbursed_by')->nullable()->constrained('users')->nullOnDelete();
            $table->date('due_date')->nullable()->index();
            $table->text('rejected_reason')->nullable();
            $table->timestamps();
            $table->unique(['tenant_id', 'loan_number']);
            $table->index(['tenant_id', 'status']);
            $table->index(['tenant_id', 'due_date']);
        });

        Schema::create('loan_guarantors', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('loan_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('phone');
            $table->text('address')->nullable();
            $table->string('relationship')->nullable();
            $table->string('id_number')->nullable();
            $table->timestamps();
            $table->index(['tenant_id', 'loan_id']);
        });

        Schema::create('loan_repayment_schedules', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('loan_id')->constrained()->cascadeOnDelete();
            $table->unsignedInteger('installment_no');
            $table->date('due_date')->index();
            $table->decimal('principal_due', 14, 2)->default(0);
            $table->decimal('interest_due', 14, 2)->default(0);
            $table->decimal('penalty_due', 14, 2)->default(0);
            $table->decimal('total_due', 14, 2)->default(0);
            $table->decimal('paid_amount', 14, 2)->default(0);
            $table->string('status')->default('pending')->index();
            $table->timestamps();
            $table->unique(['loan_id', 'installment_no']);
            $table->index(['tenant_id', 'status', 'due_date']);
        });

        Schema::create('loan_payments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('loan_id')->constrained()->cascadeOnDelete();
            $table->foreignId('loan_repayment_schedule_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
            $table->foreignId('received_by')->nullable()->constrained('users')->nullOnDelete();
            $table->decimal('amount', 14, 2);
            $table->decimal('principal_component', 14, 2)->default(0);
            $table->decimal('interest_component', 14, 2)->default(0);
            $table->decimal('penalty_component', 14, 2)->default(0);
            $table->decimal('balance_after', 14, 2)->default(0);
            $table->string('reference')->unique();
            $table->timestamp('paid_at')->useCurrent()->index();
            $table->string('method')->default('cash');
            $table->text('notes')->nullable();
            $table->timestamps();
            $table->index(['tenant_id', 'paid_at']);
        });

        Schema::create('accounting_accounts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->string('code');
            $table->string('name');
            $table->string('type')->index();
            $table->decimal('balance', 14, 2)->default(0);
            $table->boolean('is_system')->default(false);
            $table->timestamps();
            $table->unique(['tenant_id', 'code']);
            $table->index(['tenant_id', 'type']);
        });

        Schema::create('ledger_entries', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('accounting_account_id')->constrained()->cascadeOnDelete();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->date('entry_date')->index();
            $table->string('type')->index();
            $table->decimal('amount', 14, 2);
            $table->string('reference')->index();
            $table->text('description')->nullable();
            $table->string('source_type')->nullable()->index();
            $table->unsignedBigInteger('source_id')->nullable()->index();
            $table->timestamps();
            $table->index(['tenant_id', 'entry_date']);
        });

        Schema::create('incomes', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('recorded_by')->nullable()->constrained('users')->nullOnDelete();
            $table->string('category')->index();
            $table->decimal('amount', 14, 2);
            $table->date('received_at')->index();
            $table->string('payer')->nullable();
            $table->string('method')->default('cash');
            $table->text('notes')->nullable();
            $table->string('reference')->unique();
            $table->timestamps();
            $table->index(['tenant_id', 'received_at']);
        });

        Schema::create('expenses', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('recorded_by')->nullable()->constrained('users')->nullOnDelete();
            $table->string('category')->index();
            $table->decimal('amount', 14, 2);
            $table->date('spent_at')->index();
            $table->string('payee')->nullable();
            $table->string('method')->default('cash');
            $table->text('notes')->nullable();
            $table->string('reference')->unique();
            $table->timestamps();
            $table->index(['tenant_id', 'spent_at']);
        });

        Schema::create('activity_logs', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->nullable()->constrained()->cascadeOnDelete();
            $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
            $table->string('action')->index();
            $table->text('description')->nullable();
            $table->string('subject_type')->nullable()->index();
            $table->unsignedBigInteger('subject_id')->nullable()->index();
            $table->json('properties')->nullable();
            $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable();
            $table->timestamps();
            $table->index(['tenant_id', 'created_at']);
        });

        Schema::create('app_notifications', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignId('user_id')->nullable()->constrained()->cascadeOnDelete();
            $table->string('title');
            $table->text('body');
            $table->string('type')->default('info')->index();
            $table->string('channel')->default('database');
            $table->timestamp('scheduled_for')->nullable()->index();
            $table->timestamp('read_at')->nullable()->index();
            $table->timestamps();
            $table->index(['tenant_id', 'read_at']);
        });

        Schema::create('support_tickets', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->nullable()->constrained()->cascadeOnDelete();
            $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
            $table->string('ticket_number')->unique();
            $table->string('subject');
            $table->string('category')->default('general')->index();
            $table->string('priority')->default('normal')->index();
            $table->string('status')->default('open')->index();
            $table->longText('message');
            $table->longText('admin_response')->nullable();
            $table->timestamp('resolved_at')->nullable();
            $table->timestamps();
            $table->index(['tenant_id', 'status']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('support_tickets');
        Schema::dropIfExists('app_notifications');
        Schema::dropIfExists('activity_logs');
        Schema::dropIfExists('expenses');
        Schema::dropIfExists('incomes');
        Schema::dropIfExists('ledger_entries');
        Schema::dropIfExists('accounting_accounts');
        Schema::dropIfExists('loan_payments');
        Schema::dropIfExists('loan_repayment_schedules');
        Schema::dropIfExists('loan_guarantors');
        Schema::dropIfExists('loans');
        Schema::dropIfExists('loan_products');
        Schema::dropIfExists('savings_transactions');
        Schema::dropIfExists('savings_accounts');
        Schema::dropIfExists('customers');
        Schema::dropIfExists('invoices');
        Schema::dropIfExists('tenant_payments');
        Schema::dropIfExists('subscriptions');

        Schema::table('users', function (Blueprint $table) {
            $table->dropForeign(['tenant_id']);
            $table->dropForeign(['branch_id']);
        });

        Schema::dropIfExists('branches');
        Schema::dropIfExists('system_settings');
        Schema::dropIfExists('tenants');
        Schema::dropIfExists('subscription_plans');
    }
};
