<?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
    {
        // 1. Settings Table
        Schema::create('settings', function (Blueprint $table) {
            $table->id();
            $table->string('key')->unique();
            $table->text('value')->nullable();
            $table->timestamps();
        });

        // 2. Bank Accounts Table
        Schema::create('bank_accounts', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('bank_name');
            $table->string('account_number');
            $table->string('sort_code')->nullable();
            $table->string('type')->default('business');
            $table->timestamps();
        });

        // 3. Booking Channels Table
        Schema::create('booking_channels', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->timestamps();
        });

        // 4. Payment Methods Table
        Schema::create('payment_methods', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->timestamps();
        });

        // 5. Payment Types Table
        Schema::create('payment_types', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->string('direction'); // in/out
            $table->timestamps();
        });

        // 6. Guests Table
        Schema::create('guests', function (Blueprint $table) {
            $table->id();
            $table->foreignId('booking_channel_id')->nullable()->constrained('booking_channels')->nullOnDelete();
            $table->string('title')->nullable();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email')->nullable()->index();
            $table->string('phone')->nullable()->index();
            $table->string('mobile')->nullable()->index();
            $table->string('company_name')->nullable();
            $table->string('company_vat')->nullable();
            $table->string('address_line1')->nullable();
            $table->string('address_line2')->nullable();
            $table->string('city')->nullable();
            $table->string('state')->nullable();
            $table->string('postcode')->nullable();
            $table->string('country')->nullable();
            $table->text('id_type')->nullable(); // Encrypted values stored as text
            $table->text('id_number')->nullable(); // Encrypted values stored as text
            $table->date('id_expiry')->nullable();
            $table->string('nationality')->nullable();
            $table->text('internal_notes')->nullable();
            $table->text('special_requests')->nullable();
            $table->boolean('marketing_opt_in')->default(false);
            $table->boolean('is_blacklisted')->default(false);
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('updated_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->softDeletes();

            $table->fullText(['first_name', 'last_name']);
            $table->fullText(['email', 'phone', 'mobile']);
        });

        // 7. Guest Cards Table
        Schema::create('guest_cards', function (Blueprint $table) {
            $table->id();
            $table->foreignId('guest_id')->constrained('guests')->cascadeOnDelete();
            $table->string('card_type')->nullable();
            $table->text('card_number_encrypted');
            $table->string('last_four_digits', 4);
            $table->text('cardholder_name_encrypted');
            $table->text('expiry_month_encrypted');
            $table->text('expiry_year_encrypted');
            $table->text('cvv_encrypted');
            $table->boolean('is_primary')->default(false);
            $table->timestamps();
        });

        // 8. Guest Documents Table
        Schema::create('guest_documents', function (Blueprint $table) {
            $table->id();
            $table->foreignId('guest_id')->constrained('guests')->cascadeOnDelete();
            $table->string('doc_type')->nullable();
            $table->string('doc_title');
            $table->text('notes')->nullable();
            $table->string('file_path');
            $table->integer('file_size')->nullable();
            $table->string('file_type')->nullable();
            $table->foreignId('uploaded_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        // 9. Expense Categories Table
        Schema::create('expense_categories', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->foreignId('parent_id')->nullable()->constrained('expense_categories')->cascadeOnDelete();
            $table->timestamps();
        });

        // 10. User Properties Table
        Schema::create('user_properties', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->text('address')->nullable();
            $table->string('contact_person')->nullable();
            $table->string('contact_phone')->nullable();
            $table->text('notes')->nullable();
            $table->string('status')->default('active')->index();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        // 11. Custom Accounts Table
        Schema::create('custom_accounts', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->nullable();
            $table->string('account_type');
            $table->text('notes')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('updated_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->softDeletes();
        });

        // 12. Cash In Table
        Schema::create('cash_in', function (Blueprint $table) {
            $table->id();
            $table->foreignId('guest_id')->nullable()->constrained('guests')->nullOnDelete();
            $table->foreignId('custom_account_id')->nullable()->constrained('custom_accounts')->nullOnDelete();
            $table->foreignId('property_id')->nullable()->constrained('user_properties')->nullOnDelete();
            $table->date('date')->index();
            $table->decimal('amount', 12, 2);
            $table->foreignId('payment_method_id')->nullable()->constrained('payment_methods')->nullOnDelete();
            $table->foreignId('payment_type_id')->nullable()->constrained('payment_types')->nullOnDelete();
            $table->foreignId('bank_account_id')->nullable()->constrained('bank_accounts')->nullOnDelete();
            $table->string('sender_bank_name')->nullable();
            $table->string('sender_account_no')->nullable();
            $table->string('transaction_id')->nullable()->index();
            $table->foreignId('booking_channel_id')->nullable()->constrained('booking_channels')->nullOnDelete();
            $table->string('booking_no')->nullable()->index();
            $table->date('checkin_date')->nullable();
            $table->date('checkout_date')->nullable();
            $table->integer('adult_count')->default(1);
            $table->integer('child_count')->default(0);
            $table->boolean('vat_inclusive')->nullable()->default(true);
            $table->decimal('vat_rate', 5, 2)->nullable();
            $table->decimal('net_amount', 12, 2)->nullable();
            $table->decimal('vat_amount', 12, 2)->nullable();
            $table->string('receipt_path')->nullable();
            $table->text('notes')->nullable();
            $table->boolean('is_hidden')->default(false);
            $table->string('payment_status', 20)->default('pending');
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('updated_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->softDeletes();

            $table->index('custom_account_id');
        });

        // 13. Cash Out Table
        Schema::create('cash_out', function (Blueprint $table) {
            $table->id();
            $table->foreignId('guest_id')->nullable()->constrained('guests')->nullOnDelete();
            $table->foreignId('custom_account_id')->nullable()->constrained('custom_accounts')->nullOnDelete();
            $table->foreignId('property_id')->nullable()->constrained('user_properties')->nullOnDelete();
            $table->date('date')->index();
            $table->decimal('amount', 12, 2);
            $table->foreignId('payment_method_id')->nullable()->constrained('payment_methods')->nullOnDelete();
            $table->foreignId('payment_type_id')->nullable()->constrained('payment_types')->nullOnDelete();
            $table->foreignId('bank_account_id')->nullable()->constrained('bank_accounts')->nullOnDelete();
            $table->string('transaction_id')->nullable()->index();
            $table->string('guest_bank_name')->nullable();
            $table->string('guest_account_no')->nullable();
            $table->foreignId('booking_channel_id')->nullable()->constrained('booking_channels')->nullOnDelete();
            $table->string('booking_no')->nullable()->index();
            $table->date('checkin_date')->nullable();
            $table->date('checkout_date')->nullable();
            $table->boolean('vat_inclusive')->nullable()->default(true);
            $table->decimal('vat_rate', 5, 2)->nullable();
            $table->decimal('net_amount', 12, 2)->nullable();
            $table->decimal('vat_amount', 12, 2)->nullable();
            $table->string('receipt_path')->nullable();
            $table->text('notes')->nullable();
            $table->boolean('is_hidden')->default(false);
            $table->string('payment_status', 20)->default('pending');
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('updated_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->softDeletes();

            $table->index('custom_account_id');
        });

        // 14. Expenses Table
        Schema::create('expenses', function (Blueprint $table) {
            $table->id();
            $table->foreignId('category_id')->nullable()->constrained('expense_categories')->nullOnDelete();
            $table->foreignId('property_id')->nullable()->constrained('user_properties')->nullOnDelete();
            $table->date('date')->index();
            $table->decimal('amount', 12, 2);
            $table->foreignId('bank_account_id')->nullable()->constrained('bank_accounts')->nullOnDelete();
            $table->string('payee_bank_name')->nullable();
            $table->string('payee_account_no')->nullable();
            $table->string('transaction_id')->nullable()->index();
            $table->boolean('vat_inclusive')->nullable()->default(true);
            $table->decimal('vat_rate', 5, 2)->nullable();
            $table->decimal('net_amount', 12, 2)->nullable();
            $table->decimal('vat_amount', 12, 2)->nullable();
            $table->string('receipt_path')->nullable();
            $table->text('notes')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('updated_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->softDeletes();
        });

        // 15. Custom Invoices Table
        Schema::create('custom_invoices', function (Blueprint $table) {
            $table->id();
            $table->string('invoice_number')->unique();
            $table->date('invoice_date');
            $table->date('due_date')->nullable();
            $table->string('client_name');
            $table->string('client_email')->nullable()->index();
            $table->string('client_phone')->nullable();
            $table->text('client_address')->nullable();
            $table->json('items');
            $table->decimal('subtotal', 12, 2);
            $table->decimal('tax_rate', 5, 2)->nullable();
            $table->decimal('tax_amount', 12, 2)->nullable();
            $table->string('discount_type')->nullable();
            $table->decimal('discount_value', 12, 2)->nullable();
            $table->decimal('discount_amount', 12, 2)->nullable();
            $table->decimal('total_amount', 12, 2);
            $table->text('notes')->nullable();
            $table->string('status')->default('draft')->index();
            $table->string('currency_symbol', 10)->default('£');
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->softDeletes();
        });

        // 16. Cleaning Schedules Table
        Schema::create('cleaning_schedules', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->date('scheduled_date');
            $table->text('notes')->nullable();
            $table->foreignId('cleaner_id')->nullable()->constrained('users')->nullOnDelete();
            $table->decimal('total_amount', 12, 2)->default(0.00);
            $table->decimal('paid_amount', 12, 2)->default(0.00);
            $table->string('status')->default('unpaid');
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        // 17. Cleaning Schedule Properties Table
        Schema::create('cleaning_schedule_properties', function (Blueprint $table) {
            $table->id();
            $table->foreignId('schedule_id')->constrained('cleaning_schedules')->cascadeOnDelete();
            $table->foreignId('property_id')->constrained('user_properties')->cascadeOnDelete();
            $table->date('check_out_date')->nullable();
            $table->date('check_in_date')->nullable();
            $table->text('notes')->nullable();
            $table->decimal('cleaning_price', 10, 2)->default(0.00);
            $table->timestamps();
        });

        // 18. Laundry Items Table
        Schema::create('laundry_items', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        // 19. Laundry Reports Table
        Schema::create('laundry_reports', function (Blueprint $table) {
            $table->id();
            $table->date('report_date');
            $table->text('notes')->nullable();
            $table->integer('total_quantity')->default(0);
            $table->decimal('total_amount', 12, 2)->default(0.00);
            $table->decimal('paid_amount', 12, 2)->default(0.00);
            $table->string('status')->default('unpaid');
            $table->foreignId('cleaner_id')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        // 20. Laundry Report Items Table
        Schema::create('laundry_report_items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('report_id')->constrained('laundry_reports')->cascadeOnDelete();
            $table->foreignId('item_id')->constrained('laundry_items')->cascadeOnDelete();
            $table->integer('quantity')->default(1);
            $table->decimal('unit_price', 10, 2)->default(0.00);
            $table->decimal('total_price', 12, 2)->default(0.00);
            $table->timestamps();
        });

        // 21. Cleaning Payments Table
        Schema::create('cleaning_payments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('schedule_id')->constrained('cleaning_schedules')->cascadeOnDelete();
            $table->decimal('amount', 12, 2);
            $table->date('payment_date');
            $table->text('notes')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });

        // 22. Laundry Payments Table
        Schema::create('laundry_payments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('report_id')->constrained('laundry_reports')->cascadeOnDelete();
            $table->decimal('amount', 12, 2);
            $table->date('payment_date');
            $table->text('notes')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('laundry_payments');
        Schema::dropIfExists('cleaning_payments');
        Schema::dropIfExists('laundry_report_items');
        Schema::dropIfExists('laundry_reports');
        Schema::dropIfExists('laundry_items');
        Schema::dropIfExists('cleaning_schedule_properties');
        Schema::dropIfExists('cleaning_schedules');
        Schema::dropIfExists('custom_invoices');
        Schema::dropIfExists('expenses');
        Schema::dropIfExists('cash_out');
        Schema::dropIfExists('cash_in');
        Schema::dropIfExists('custom_accounts');
        Schema::dropIfExists('user_properties');
        Schema::dropIfExists('expense_categories');
        Schema::dropIfExists('guest_documents');
        Schema::dropIfExists('guest_cards');
        Schema::dropIfExists('guests');
        Schema::dropIfExists('payment_types');
        Schema::dropIfExists('payment_methods');
        Schema::dropIfExists('booking_channels');
        Schema::dropIfExists('bank_accounts');
        Schema::dropIfExists('settings');
    }
};
