<?php

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

/**
 * The proof register: one append-only row per commercial event.
 *
 * What a customer was sold, when, on what terms and for how much — kept so it
 * can be answered years later without reconstructing it from tables that have
 * moved on. Purchases, upgrades, downgrades and cancellations all land here.
 *
 * Flat columns for everything that is searched or relied on as evidence, PLUS a
 * versioned JSON copy of the whole snapshot. Not JSON alone: you cannot query
 * it, you cannot index it, and "the amount is in there somewhere" is poor
 * evidence. The JSON is the long tail — the fields nobody thought to ask about
 * yet — and `snapshot_version` says which shape it is in, so an old row stays
 * readable after the shape changes.
 */
return new class extends Migration
{
    public function up(): void
    {
        Schema::create('subscription_records', function (Blueprint $table) {
            $table->id();
            $table->uuid()->unique();

            // purchase | renewal | payment_failed | upgrade | downgrade |
            // addon_booked | addon_cancelled | cancellation
            $table->string('event');

            // Nullable and nullOnDelete throughout: a customer exercising their
            // right to erasure must not take the commercial record with them,
            // and the flat columns below still say what happened.
            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('subscription_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('order_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('plan_version_id')->nullable()->constrained()->nullOnDelete();

            // Copied, not joined. These have to survive the rows they came from.
            $table->string('customer_name')->nullable();
            $table->string('plan_key');
            $table->unsignedInteger('plan_version')->nullable();
            $table->string('term');

            $table->integer('net_cents');
            $table->integer('tax_cents');
            $table->integer('gross_cents');
            $table->char('currency', 3);
            $table->decimal('tax_rate', 5, 2);
            $table->boolean('reverse_charge')->default(false);

            $table->string('stripe_event_id')->nullable();
            $table->string('stripe_invoice_id')->nullable();
            $table->string('stripe_subscription_id')->nullable();

            $table->timestamp('occurred_at');

            $table->unsignedSmallInteger('snapshot_version')->default(1);
            $table->json('snapshot');

            $table->timestamps();

            $table->index(['customer_id', 'occurred_at']);
            $table->index(['subscription_id', 'occurred_at']);
            $table->index(['event', 'occurred_at']);
            $table->index('plan_key');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('subscription_records');
    }
};
