<?php

declare(strict_types=1);

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

/**
 * Refactor subscriptions table to use proper plan relationships and limits.
 *
 * CHANGES:
 * - Rename 'plan' enum to 'billing_cycle'
 * - Add plan_id FK to plans table
 * - Add status enum (active, trial, grace, expired, canceled)
 * - Add custom limit overrides
 * - Add trial tracking
 */
return new class extends Migration
{
    public function up(): void
    {
        Schema::table('subscriptions', function (Blueprint $table) {
            // ═══════════════════════════════════════════════════════════════════════
            // PLAN RELATIONSHIP
            // ═══════════════════════════════════════════════════════════════════════
            $table->foreignId('plan_id')
                ->nullable()
                ->after('company_id')
                ->constrained('plans')
                ->nullOnDelete();

            // ═══════════════════════════════════════════════════════════════════════
            // RENAME 'plan' to 'billing_cycle' (more accurate name)
            // ═══════════════════════════════════════════════════════════════════════
            // First, we need to modify the enum to add 'monthly' option if not exists
            // Then rename the column
        });

        // Rename plan -> billing_cycle using raw SQL (MariaDB compatible)
        DB::statement("ALTER TABLE subscriptions CHANGE COLUMN plan billing_cycle ENUM('monthly', 'quarterly', 'semiannual', 'annual') DEFAULT 'monthly'");

        Schema::table('subscriptions', function (Blueprint $table) {
            // ═══════════════════════════════════════════════════════════════════════
            // STATUS (replaces is_active with more granular states)
            // ═══════════════════════════════════════════════════════════════════════
            $table->enum('status', [
                'active',      // Paid and current
                'trial',       // In trial period
                'grace',       // Payment overdue, in grace period
                'expired',     // Grace period ended, limited functionality
                'canceled',    // Manually canceled
                'suspended',   // Suspended by admin
            ])->default('active')->after('ends_at');

            // ═══════════════════════════════════════════════════════════════════════
            // TRIAL TRACKING
            // ═══════════════════════════════════════════════════════════════════════
            $table->timestamp('trial_ends_at')->nullable()->after('status')
                ->comment('When trial period ends');

            $table->timestamp('grace_period_ends_at')->nullable()->after('trial_ends_at')
                ->comment('When grace period ends after payment failure');

            // ═══════════════════════════════════════════════════════════════════════
            // PRICING (actual amount paid, may differ from plan price)
            // ═══════════════════════════════════════════════════════════════════════
            $table->decimal('amount_paid', 12, 2)->nullable()->after('grace_period_ends_at')
                ->comment('Actual amount paid for this subscription period');

            $table->string('payment_reference', 100)->nullable()->after('amount_paid')
                ->comment('Payment transaction reference');

            // ═══════════════════════════════════════════════════════════════════════
            // CUSTOM LIMIT OVERRIDES
            // These override the plan limits for special arrangements
            // ═══════════════════════════════════════════════════════════════════════
            $table->json('custom_limits')->nullable()->after('payment_reference')
                ->comment('Override plan limits: {"max_users": 15, "max_credits": 300}');

            // ═══════════════════════════════════════════════════════════════════════
            // CANCELLATION TRACKING
            // ═══════════════════════════════════════════════════════════════════════
            $table->timestamp('canceled_at')->nullable()
                ->comment('When subscription was canceled');

            $table->string('cancellation_reason')->nullable()
                ->comment('Reason for cancellation');

            $table->foreignId('canceled_by_user_id')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete();

            // ═══════════════════════════════════════════════════════════════════════
            // AUDIT
            // ═══════════════════════════════════════════════════════════════════════
            $table->text('notes')->nullable()
                ->comment('Internal notes about this subscription');

            // ═══════════════════════════════════════════════════════════════════════
            // INDEXES
            // ═══════════════════════════════════════════════════════════════════════
            $table->index('status');
            $table->index('ends_at');
            $table->index(['company_id', 'status']);
        });

        // Migrate existing data: assign default plan to existing subscriptions
        $this->migrateExistingSubscriptions();
    }

    public function down(): void
    {
        Schema::table('subscriptions', function (Blueprint $table) {
            $table->dropIndex(['company_id', 'status']);
            $table->dropIndex(['status']);
            $table->dropIndex(['ends_at']);

            $table->dropForeign(['plan_id']);
            $table->dropForeign(['canceled_by_user_id']);

            $table->dropColumn([
                'plan_id',
                'status',
                'trial_ends_at',
                'grace_period_ends_at',
                'amount_paid',
                'payment_reference',
                'custom_limits',
                'canceled_at',
                'cancellation_reason',
                'canceled_by_user_id',
                'notes',
            ]);
        });

        // Rename billing_cycle back to plan
        DB::statement("ALTER TABLE subscriptions CHANGE COLUMN billing_cycle plan ENUM('monthly', 'quarterly', 'semiannual', 'annual') DEFAULT 'monthly'");
    }

    /**
     * Assign default plan to existing subscriptions.
     */
    private function migrateExistingSubscriptions(): void
    {
        // Get the basic plan ID
        $basicPlan = DB::table('plans')->where('slug', 'basic')->first();

        if ($basicPlan) {
            // Update all existing subscriptions to use the basic plan
            DB::table('subscriptions')
                ->whereNull('plan_id')
                ->update([
                    'plan_id' => $basicPlan->id,
                    'status' => DB::raw("CASE WHEN is_active = 1 THEN 'active' ELSE 'expired' END"),
                ]);
        }
    }
};