<?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('users', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->string('email')->unique();
            $table->string('password_hash');
            $table->string('first_name')->nullable();
            $table->string('last_name')->nullable();
            $table->string('phone')->nullable();
            $table->boolean('phone_verified')->default(false);
            $table->boolean('email_verified')->default(false);
            $table->string('avatar')->nullable();
            $table->text('bio')->nullable();
            $table->enum('role', ['USER', 'PARTNER', 'ADMIN', 'SUPER_ADMIN'])->default('USER');
            $table->enum('status', ['ACTIVE', 'SUSPENDED', 'PENDING_VERIFICATION', 'DELETED'])->default('PENDING_VERIFICATION');
            $table->boolean('two_factor_enabled')->default(false);
            $table->text('two_factor_secret')->nullable();
            $table->timestampTz('last_login_at')->nullable();
            $table->string('last_login_ip', 45)->nullable();
            $table->unsignedInteger('failed_login_count')->default(0);
            $table->timestampTz('locked_until')->nullable();
            $table->enum('preferred_currency', ['USD', 'EUR', 'GBP', 'NGN'])->default('USD');
            $table->string('language', 10)->default('en');
            $table->string('timezone')->default('UTC');
            $table->timestampsTz();
            $table->softDeletesTz();
            $table->index(['status', 'role']);
        });

        Schema::create('refresh_tokens', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->text('token')->unique();
            $table->foreignUuid('user_id')->constrained('users')->cascadeOnDelete();
            $table->timestampTz('expires_at');
            $table->timestampTz('created_at')->useCurrent();
        });

        Schema::create('password_resets', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('user_id')->constrained('users')->cascadeOnDelete();
            $table->string('token')->unique();
            $table->timestampTz('expires_at');
            $table->timestampTz('used_at')->nullable();
            $table->timestampTz('created_at')->useCurrent();
        });

        Schema::create('partners', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('user_id')->unique()->constrained('users')->cascadeOnDelete();
            $table->string('business_name');
            $table->text('description')->nullable();
            $table->string('website')->nullable();
            $table->string('logo')->nullable();
            $table->enum('status', ['PENDING', 'VERIFIED', 'SUSPENDED', 'REJECTED'])->default('PENDING');
            $table->boolean('is_super_partner')->default(false);
            $table->unsignedSmallInteger('joined_year');
            $table->timestampTz('verified_at')->nullable();
            $table->jsonb('verification_docs')->nullable();
            $table->decimal('commission_rate', 5, 2)->default(15);
            $table->string('bank_account_name')->nullable();
            $table->string('bank_account_number')->nullable();
            $table->string('bank_name')->nullable();
            $table->string('bank_code')->nullable();
            $table->decimal('total_earnings', 12, 2)->default(0);
            $table->decimal('pending_payouts', 12, 2)->default(0);
            $table->unsignedInteger('total_bookings')->default(0);
            $table->decimal('average_rating', 3, 2)->nullable();
            $table->timestampsTz();
            $table->softDeletesTz();
        });

        Schema::create('listings', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('partner_id')->constrained('partners');
            $table->string('title');
            $table->string('slug')->unique();
            $table->text('description');
            $table->enum('category', ['VILLA', 'CABIN', 'APARTMENT', 'BOUTIQUE', 'UNIQUE', 'BEACHFRONT', 'COUNTRYSIDE', 'CITY']);
            $table->string('address');
            $table->string('city');
            $table->string('country');
            $table->string('postal_code')->nullable();
            $table->decimal('latitude', 10, 8);
            $table->decimal('longitude', 11, 8);
            $table->decimal('base_price_usd', 10, 2);
            $table->decimal('base_price_eur', 10, 2);
            $table->decimal('base_price_gbp', 10, 2);
            $table->decimal('base_price_ngn', 10, 2);
            $table->decimal('cleaning_fee', 10, 2)->default(0);
            $table->decimal('service_fee', 10, 2)->default(0);
            $table->unsignedSmallInteger('max_guests');
            $table->unsignedSmallInteger('bedrooms');
            $table->unsignedSmallInteger('beds');
            $table->unsignedSmallInteger('bathrooms');
            $table->unsignedSmallInteger('min_nights')->default(1);
            $table->unsignedSmallInteger('max_nights')->nullable();
            $table->string('check_in_time', 5)->default('15:00');
            $table->string('check_out_time', 5)->default('11:00');
            $table->string('cancellation_policy')->default('flexible');
            $table->enum('status', ['DRAFT', 'PUBLISHED', 'ARCHIVED', 'SUSPENDED'])->default('DRAFT');
            $table->boolean('is_new')->default(true);
            $table->boolean('is_featured')->default(false);
            $table->unsignedBigInteger('views_count')->default(0);
            $table->unsignedInteger('bookings_count')->default(0);
            $table->decimal('average_rating', 3, 2)->nullable();
            $table->unsignedInteger('reviews_count')->default(0);
            $table->timestampTz('published_at')->nullable();
            $table->timestampsTz();
            $table->softDeletesTz();
            $table->index(['status', 'is_featured', 'created_at']);
            $table->index(['city', 'country']);
            $table->index(['category', 'max_guests']);
            $table->index('base_price_usd');
        });

        if (DB::connection()->getDriverName() === 'pgsql') {
            DB::statement('CREATE EXTENSION IF NOT EXISTS postgis');
            DB::statement('ALTER TABLE listings ADD COLUMN location geography(Point, 4326)');
            DB::statement("UPDATE listings SET location = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography");
            DB::unprepared(<<<'SQL'
                CREATE FUNCTION encostay_set_listing_location() RETURNS trigger AS $$
                BEGIN
                    NEW.location := ST_SetSRID(ST_MakePoint(NEW.longitude, NEW.latitude), 4326)::geography;
                    RETURN NEW;
                END;
                $$ LANGUAGE plpgsql;
                CREATE TRIGGER listings_location_trigger
                BEFORE INSERT OR UPDATE OF latitude, longitude ON listings
                FOR EACH ROW EXECUTE FUNCTION encostay_set_listing_location();
                SQL);
            DB::statement('CREATE INDEX listings_location_gist ON listings USING GIST (location)');
        }

        Schema::create('listing_images', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('listing_id')->constrained('listings')->cascadeOnDelete();
            $table->text('url');
            $table->string('alt')->nullable();
            $table->unsignedSmallInteger('order')->default(0);
            $table->timestampTz('created_at')->useCurrent();
            $table->index(['listing_id', 'order']);
        });

        Schema::create('amenities', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->string('name')->unique();
            $table->string('icon')->nullable();
            $table->string('category');
            $table->timestampTz('created_at')->useCurrent();
        });

        Schema::create('listing_amenities', function (Blueprint $table): void {
            $table->foreignUuid('listing_id')->constrained('listings')->cascadeOnDelete();
            $table->foreignUuid('amenity_id')->constrained('amenities')->cascadeOnDelete();
            $table->primary(['listing_id', 'amenity_id']);
        });

        Schema::create('availability', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('listing_id')->constrained('listings')->cascadeOnDelete();
            $table->date('date');
            $table->boolean('available')->default(true);
            $table->decimal('price', 10, 2)->nullable();
            $table->unsignedSmallInteger('min_nights')->nullable();
            $table->unique(['listing_id', 'date']);
        });

        Schema::create('bookings', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('listing_id')->constrained('listings');
            $table->foreignUuid('user_id')->constrained('users');
            $table->date('check_in');
            $table->date('check_out');
            $table->unsignedSmallInteger('nights');
            $table->unsignedSmallInteger('adults');
            $table->unsignedSmallInteger('children')->default(0);
            $table->unsignedSmallInteger('infants')->default(0);
            $table->decimal('base_price', 10, 2);
            $table->decimal('cleaning_fee', 10, 2)->default(0);
            $table->decimal('service_fee', 10, 2)->default(0);
            $table->decimal('total_price', 10, 2);
            $table->enum('currency', ['USD', 'EUR', 'GBP', 'NGN'])->default('USD');
            $table->enum('status', ['PENDING', 'CONFIRMED', 'CANCELLED', 'COMPLETED', 'REFUNDED'])->default('PENDING');
            $table->timestampTz('confirmed_at')->nullable();
            $table->timestampTz('cancelled_at')->nullable();
            $table->text('cancellation_reason')->nullable();
            $table->text('special_requests')->nullable();
            $table->timestampsTz();
            $table->index(['listing_id', 'check_in', 'check_out']);
            $table->index(['user_id', 'status']);
        });

        Schema::create('payments', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('booking_id')->unique()->constrained('bookings');
            $table->decimal('amount', 10, 2);
            $table->enum('currency', ['USD', 'EUR', 'GBP', 'NGN']);
            $table->enum('status', ['PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED'])->default('PENDING');
            $table->enum('processor', ['STRIPE', 'PAYSTACK', 'FLUTTERWAVE', 'PAYPAL']);
            $table->string('processor_payment_id')->nullable();
            $table->decimal('processor_fee', 10, 2)->default(0);
            $table->string('card_last4', 4)->nullable();
            $table->string('card_brand')->nullable();
            $table->decimal('refund_amount', 10, 2)->nullable();
            $table->timestampTz('refunded_at')->nullable();
            $table->text('refund_reason')->nullable();
            $table->timestampTz('paid_at')->nullable();
            $table->timestampsTz();
            $table->index(['processor', 'processor_payment_id']);
        });

        Schema::create('wallets', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('user_id')->unique()->constrained('users')->cascadeOnDelete();
            $table->decimal('balance_usd', 12, 2)->default(0);
            $table->decimal('balance_eur', 12, 2)->default(0);
            $table->decimal('balance_gbp', 12, 2)->default(0);
            $table->decimal('balance_ngn', 12, 2)->default(0);
            $table->timestampsTz();
        });

        Schema::create('transactions', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('wallet_id')->constrained('wallets');
            $table->foreignUuid('payment_id')->nullable()->constrained('payments');
            $table->enum('type', ['PAYMENT', 'REFUND', 'PAYOUT', 'DEPOSIT', 'WITHDRAWAL', 'FEE']);
            $table->decimal('amount', 12, 2);
            $table->enum('currency', ['USD', 'EUR', 'GBP', 'NGN']);
            $table->decimal('balance_before', 12, 2);
            $table->decimal('balance_after', 12, 2);
            $table->text('description');
            $table->jsonb('metadata')->nullable();
            $table->timestampTz('created_at')->useCurrent();
            $table->index(['wallet_id', 'created_at']);
        });

        Schema::create('payouts', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('partner_id')->constrained('partners');
            $table->decimal('amount', 12, 2);
            $table->enum('currency', ['USD', 'EUR', 'GBP', 'NGN']);
            $table->enum('status', ['PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED'])->default('PENDING');
            $table->string('bank_account_name');
            $table->string('bank_account_number');
            $table->string('bank_name');
            $table->enum('processor', ['STRIPE', 'PAYSTACK', 'FLUTTERWAVE', 'PAYPAL']);
            $table->string('processor_payout_id')->nullable();
            $table->decimal('processor_fee', 10, 2)->default(0);
            $table->timestampTz('requested_at')->useCurrent();
            $table->timestampTz('processed_at')->nullable();
            $table->timestampTz('completed_at')->nullable();
            $table->timestampTz('failed_at')->nullable();
            $table->text('failure_reason')->nullable();
            $table->index(['partner_id', 'status']);
        });

        Schema::create('reviews', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('listing_id')->constrained('listings');
            $table->foreignUuid('booking_id')->unique()->constrained('bookings');
            $table->foreignUuid('user_id')->constrained('users');
            $table->unsignedTinyInteger('rating');
            $table->unsignedTinyInteger('cleanliness')->nullable();
            $table->unsignedTinyInteger('accuracy')->nullable();
            $table->unsignedTinyInteger('check_in')->nullable();
            $table->unsignedTinyInteger('communication')->nullable();
            $table->unsignedTinyInteger('location')->nullable();
            $table->unsignedTinyInteger('value')->nullable();
            $table->text('comment')->nullable();
            $table->text('response')->nullable();
            $table->timestampTz('responded_at')->nullable();
            $table->boolean('is_hidden')->default(false);
            $table->timestampsTz();
            $table->index(['listing_id', 'is_hidden', 'created_at']);
        });

        Schema::create('favorites', function (Blueprint $table): void {
            $table->foreignUuid('user_id')->constrained('users')->cascadeOnDelete();
            $table->foreignUuid('listing_id')->constrained('listings')->cascadeOnDelete();
            $table->timestampTz('created_at')->useCurrent();
            $table->primary(['user_id', 'listing_id']);
        });

        Schema::create('notifications', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('user_id')->constrained('users')->cascadeOnDelete();
            $table->enum('channel', ['EMAIL', 'SMS', 'PUSH', 'IN_APP']);
            $table->enum('status', ['PENDING', 'SENT', 'FAILED', 'READ'])->default('PENDING');
            $table->string('title');
            $table->text('message');
            $table->jsonb('data')->nullable();
            $table->timestampTz('sent_at')->nullable();
            $table->timestampTz('read_at')->nullable();
            $table->timestampTz('created_at')->useCurrent();
            $table->index(['user_id', 'status', 'created_at']);
        });

        Schema::create('messages', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->foreignUuid('sender_id')->constrained('users');
            $table->foreignUuid('receiver_id')->constrained('users');
            $table->text('content');
            $table->timestampTz('read_at')->nullable();
            $table->timestampTz('created_at')->useCurrent();
            $table->index(['receiver_id', 'created_at']);
        });

        Schema::create('personal_access_tokens', function (Blueprint $table): void {
            $table->uuid('id')->primary();
            $table->uuidMorphs('tokenable');
            $table->string('name');
            $table->string('token', 64)->unique();
            $table->text('abilities')->nullable();
            $table->timestamp('last_used_at')->nullable();
            $table->timestamp('expires_at')->nullable()->index();
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('personal_access_tokens');
        Schema::dropIfExists('messages');
        Schema::dropIfExists('notifications');
        Schema::dropIfExists('favorites');
        Schema::dropIfExists('reviews');
        Schema::dropIfExists('payouts');
        Schema::dropIfExists('transactions');
        Schema::dropIfExists('wallets');
        Schema::dropIfExists('payments');
        Schema::dropIfExists('bookings');
        Schema::dropIfExists('availability');
        Schema::dropIfExists('listing_amenities');
        Schema::dropIfExists('amenities');
        Schema::dropIfExists('listing_images');
        Schema::dropIfExists('listings');
        if (DB::connection()->getDriverName() === 'pgsql') {
            DB::statement('DROP FUNCTION IF EXISTS encostay_set_listing_location()');
        }
        Schema::dropIfExists('partners');
        Schema::dropIfExists('password_resets');
        Schema::dropIfExists('refresh_tokens');
        Schema::dropIfExists('users');
    }
};