<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('ad_package_purchases', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
            $table->foreignId('package_id')->constrained('ad_packages')->onDelete('restrict');
            $table->foreignId('pricing_tier_id')->constrained('package_pricing_tiers')->onDelete('restrict');
            
            // Source of purchase
            $table->enum('purchase_source', ['sell_faster', 'buy_packages'])->index();
            $table->foreignId('ad_id')->nullable()->constrained('ads')->onDelete('set null');
            
            // REQUIRED: Location Selection
            $table->string('location_country'); // REQUIRED
            $table->string('location_state')->nullable();
            $table->string('location_city')->nullable();
            $table->enum('location_scope', ['city', 'state', 'country', 'all_india'])->index();
            
            // REQUIRED: Category Selection
            $table->foreignId('category_id')->constrained('categories')->onDelete('restrict');
            $table->foreignId('subcategory_id')->nullable()->constrained('subcategories')->onDelete('set null');
            
            // Payment information
            $table->decimal('total_paid', 10, 2);
            $table->string('currency', 3)->default('INR');
            
            // Features & usage tracking
            $table->json('features_granted'); // Copy of package features at purchase time
            $table->integer('usage_count')->default(0);
            $table->integer('usage_limit');
            
            // Timestamps
            $table->timestamp('purchased_at');
            $table->timestamp('expires_at')->index();
            $table->timestamp('last_used_at')->nullable();
            
            $table->enum('status', ['active', 'expired', 'exhausted', 'cancelled'])->default('active')->index();
            
            $table->timestamps();
            
            // Critical indexes for performance
            $table->index(['user_id', 'category_id', 'location_country', 'status'], 'purchases_user_cat_loc_status_idx');
            $table->index(['status', 'expires_at']);
            $table->index(['user_id', 'status']);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('ad_package_purchases');
    }
};
