<?php

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

/**
 * The plan catalogue, moved out of config/provisioning.php and into three
 * tables — because one table cannot hold three different lifetimes.
 *
 *  - `plan_families` is the thing that has a NAME. "Team" is still Team after
 *    five price changes, and everything that points at a plan by name points
 *    here.
 *  - `plan_versions` is what a plan WAS at a point in time. Frozen from the
 *    moment it is published, because a customer bought a version, not a name.
 *  - `plan_prices` is per version AND term, each with its own Stripe Price id:
 *    a Stripe Price carries its own recurring interval, so monthly and yearly
 *    can never share one.
 *
 * Availability is computed when read — `available_from <= now < available_until`,
 * half-open, UTC — never flipped by a scheduled job. What does not run cannot
 * fail to run, and a plan that goes on sale because a worker happened to be up
 * is not a plan you can promise anyone.
 */
return new class extends Migration
{
    /**
     * Today's catalogue, inlined rather than read from config: this migration
     * has to seed the same values in five years, whatever config says by then.
     */
    private const SEED = [
        'start' => ['tier' => 1, 'traffic_gb' => 1000, 'quota_gb' => 100, 'disk_gb' => 120, 'ram_mb' => 4096, 'cores' => 2, 'seats' => 5, 'performance' => 'standard', 'price_cents' => 4900, 'template_vmid' => 9000,
            'features' => ['managed_updates', 'daily_backups', 'monitoring', 'subdomain']],
        'team' => ['tier' => 2, 'traffic_gb' => 3000, 'quota_gb' => 500, 'disk_gb' => 540, 'ram_mb' => 8192, 'cores' => 4, 'seats' => 25, 'performance' => 'enhanced', 'price_cents' => 17900, 'template_vmid' => 9000,
            'features' => ['managed_updates', 'daily_backups', 'monitoring', 'subdomain', 'office', 'branding', 'priority_support']],
        'business' => ['tier' => 3, 'traffic_gb' => 8000, 'quota_gb' => 1000, 'disk_gb' => 1050, 'ram_mb' => 16384, 'cores' => 8, 'seats' => 100, 'performance' => 'high', 'price_cents' => 39900, 'template_vmid' => 9000,
            'features' => ['managed_updates', 'daily_backups', 'monitoring', 'custom_domain', 'office', 'branding', 'priority_support', 'extended_retention', 'audit_log']],
        'enterprise' => ['tier' => 4, 'traffic_gb' => 20000, 'quota_gb' => 2000, 'disk_gb' => 2100, 'ram_mb' => 32768, 'cores' => 16, 'seats' => 500, 'performance' => 'dedicated', 'price_cents' => 79900, 'template_vmid' => 9000,
            'features' => ['managed_updates', 'daily_backups', 'monitoring', 'custom_domain', 'office', 'branding', 'premium_sla', 'extended_retention', 'audit_log', 'onboarding']],
    ];

    public function up(): void
    {
        Schema::create('plan_families', function (Blueprint $table) {
            $table->id();
            $table->uuid()->unique();

            // The stable handle. Orders, instances and contracts have referred
            // to plans by this string since day one; it must not change.
            $table->string('key')->unique();
            $table->string('name');

            // Rank, not price, decides whether a plan change is an upgrade — a
            // grandfathered plan can cost less than a smaller one does today.
            $table->unsignedTinyInteger('tier');

            // The kill switch: stop selling this plan NOW, without editing a
            // window and without waiting for a job. Existing customers keep
            // their contract; the plan simply disappears from the shop.
            $table->boolean('sales_enabled')->default(true);

            $table->string('stripe_product_id')->nullable();
            $table->timestamps();

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

        Schema::create('plan_versions', function (Blueprint $table) {
            $table->id();
            $table->uuid()->unique();
            // Restrict, not cascade: a cascade would delete published versions
            // without any model ever seeing it, and null the plan_version_id off
            // every contract that pointed at them. Removing a family means
            // clearing its drafts first, and a family with customers on it
            // cannot be removed at all.
            $table->foreignId('plan_family_id')->constrained()->restrictOnDelete();
            $table->unsignedInteger('version');

            // What the plan can do. Frozen from publication — see PlanVersion.
            $table->unsignedBigInteger('quota_gb');
            $table->unsignedBigInteger('traffic_gb');
            $table->unsignedInteger('seats');
            $table->unsignedInteger('ram_mb');
            $table->unsignedInteger('cores');
            $table->unsignedBigInteger('disk_gb');
            $table->string('performance')->nullable();
            $table->unsignedInteger('template_vmid')->nullable();

            // Which feature bullets this version puts in front of a customer.
            $table->json('features');

            // Half-open [from, until): until is the first moment it is NO
            // longer sold, so a successor starting exactly then leaves no gap
            // and no overlap. Null means open-ended.
            $table->timestamp('available_from');
            $table->timestamp('available_until')->nullable();

            // Publication, not the first sale, is what locks the capabilities:
            // a plan that was announced has been promised to someone, whether
            // or not anyone has bought it yet.
            $table->timestamp('published_at')->nullable();

            $table->timestamps();

            $table->unique(['plan_family_id', 'version']);
            $table->index(['plan_family_id', 'available_from']);
        });

        Schema::create('plan_prices', function (Blueprint $table) {
            $table->id();
            $table->uuid()->unique();
            $table->foreignId('plan_version_id')->constrained()->cascadeOnDelete();

            $table->string('term');                      // monthly|yearly
            $table->unsignedInteger('amount_cents');     // NET, per term
            $table->char('currency', 3);

            // A Stripe Price is immutable and carries its own interval, so this
            // belongs to the row, not to the family.
            $table->string('stripe_price_id')->nullable();

            $table->timestamps();

            $table->unique(['plan_version_id', 'term', 'currency']);
        });

        $this->seedFromTodaysCatalogue();
    }

    public function down(): void
    {
        Schema::dropIfExists('plan_prices');
        Schema::dropIfExists('plan_versions');
        Schema::dropIfExists('plan_families');
    }

    /**
     * Version 1 of every plan we sell today, published and on sale from now,
     * open-ended. Idempotent, so a re-run after a rollback adopts what is
     * already there instead of duplicating it.
     */
    private function seedFromTodaysCatalogue(): void
    {
        $currency = strtoupper((string) config('provisioning.currency', 'EUR'));
        $now = now();

        foreach (self::SEED as $key => $plan) {
            $familyId = DB::table('plan_families')->where('key', $key)->value('id');

            if ($familyId === null) {
                $familyId = DB::table('plan_families')->insertGetId([
                    'uuid' => (string) Str::uuid(),
                    'key' => $key,
                    'name' => ucfirst($key),
                    'tier' => $plan['tier'],
                    'sales_enabled' => true,
                    'created_at' => $now,
                    'updated_at' => $now,
                ]);
            }

            $versionId = DB::table('plan_versions')
                ->where('plan_family_id', $familyId)->where('version', 1)->value('id');

            if ($versionId === null) {
                $versionId = DB::table('plan_versions')->insertGetId([
                    'uuid' => (string) Str::uuid(),
                    'plan_family_id' => $familyId,
                    'version' => 1,
                    'quota_gb' => $plan['quota_gb'],
                    'traffic_gb' => $plan['traffic_gb'],
                    'seats' => $plan['seats'],
                    'ram_mb' => $plan['ram_mb'],
                    'cores' => $plan['cores'],
                    'disk_gb' => $plan['disk_gb'],
                    'performance' => $plan['performance'],
                    'template_vmid' => $plan['template_vmid'],
                    'features' => json_encode($plan['features']),
                    // Backdated so a contract opened a moment ago still falls
                    // inside the window it was sold under.
                    'available_from' => $now->copy()->subYear(),
                    'available_until' => null,
                    'published_at' => $now->copy()->subYear(),
                    'created_at' => $now,
                    'updated_at' => $now,
                ]);
            }

            // Yearly is twelve months of the same price. Any discount is a
            // decision the owner makes in the catalogue, never arithmetic
            // hidden in a conversion.
            foreach (['monthly' => $plan['price_cents'], 'yearly' => $plan['price_cents'] * 12] as $term => $amount) {
                $exists = DB::table('plan_prices')
                    ->where('plan_version_id', $versionId)
                    ->where('term', $term)
                    ->where('currency', $currency)
                    ->exists();

                if ($exists) {
                    continue;
                }

                DB::table('plan_prices')->insert([
                    'uuid' => (string) Str::uuid(),
                    'plan_version_id' => $versionId,
                    'term' => $term,
                    'amount_cents' => $amount,
                    'currency' => $currency,
                    'created_at' => $now,
                    'updated_at' => $now,
                ]);
            }
        }
    }
};
