<?php

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

/**
 * One row per Stripe Price on the module side too.
 *
 * `stripe_plan_prices.stripe_price_id` has been unique since
 * 2026_07_30_110000; the module register was never given the same guard, so two
 * rows could claim one Price — and archiving the one would have withdrawn the
 * Price the other was still selling. It is also the net under the adoption step
 * (App\Services\Billing\AdoptStripePrice): at a VAT rate of nought both
 * treatments charge the same amount, and the check that refuses an
 * already-claimed Price is then the only thing keeping them apart.
 *
 * Duplicates are DELETED rather than archived. An archived row goes on claiming
 * the id, and a unique index knows nothing about `archived_at`. Deleting is safe
 * because the row rebuilds itself — but not through adoption: the survivor keeps
 * the shared Price, so AdoptStripePrice's `claimed` callback reports it taken no
 * matter which row asks, and the deleted row's own tuple — necessarily a
 * DIFFERENT one, since the composite unique on (addon_key, reverse_charge,
 * amount_cents, currency, interval) from 2026_07_31_200000 forbids two rows
 * sharing a tuple — mints a fresh Price of its own instead, under its own
 * tuple-specific idempotency key (AddonPrices::ensure() falling through to
 * createPrice()). `subscription_addons` holds the Stripe id as text, not as a
 * foreign key, so no booking loses its price either way.
 *
 * MariaDB does not run DDL in a transaction, so the deletes below are already
 * committed by the time `Schema::table()` adds the index — a row inserted between
 * the two on a Price id that is still shared aborts the migration with the deletes
 * standing. Re-running is safe (the dedupe is idempotent and the index is simply
 * not there yet), but the sweep and anything else calling
 * AddonPrices::ensure() — a customer's module booking included — should be
 * quiesced for the one real run this gets.
 *
 * The `Log::warning` below is the only record this ever gets: `down()` restores
 * the index, not the rows it deleted. It names the shared Price, the row kept,
 * and every row thrown away, so an operator reading the log after the one real
 * run this migration gets can check the survivor's `amount_cents` against what
 * Stripe actually charges for that Price — the lowest id is written first, not
 * necessarily right, and nothing else will say what was discarded.
 */
return new class extends Migration
{
    public function up(): void
    {
        // Written with groupBy/having and a delete by id rather than a joined
        // delete: production is MariaDB and the suite runs on SQLite.
        $shared = DB::table('stripe_addon_prices')
            ->select('stripe_price_id')
            ->groupBy('stripe_price_id')
            ->havingRaw('count(*) > 1')
            ->pluck('stripe_price_id');

        foreach ($shared as $priceId) {
            $rows = DB::table('stripe_addon_prices')->where('stripe_price_id', $priceId)->get();

            // The lowest id stays — the row that was written first, which is the
            // one anything already billing is likeliest to have been reading.
            $keep = (int) $rows->min('id');
            $kept = $rows->first(fn ($row) => (int) $row->id === $keep);
            $discarded = $rows->reject(fn ($row) => (int) $row->id === $keep);

            // The only forensic trace this delete ever leaves — see the class
            // docblock for why that has to be enough.
            Log::warning('stripe: kept one row of several claiming one price, deleted the rest', [
                'stripe_price_id' => $priceId,
                'kept' => [
                    'id' => $kept->id,
                    'addon_key' => $kept->addon_key,
                    'reverse_charge' => (bool) $kept->reverse_charge,
                    'amount_cents' => $kept->amount_cents,
                    'currency' => $kept->currency,
                    'interval' => $kept->interval,
                ],
                'deleted' => $discarded->map(fn ($row) => [
                    'id' => $row->id,
                    'addon_key' => $row->addon_key,
                    'reverse_charge' => (bool) $row->reverse_charge,
                    'amount_cents' => $row->amount_cents,
                    'currency' => $row->currency,
                    'interval' => $row->interval,
                ])->values()->all(),
            ]);

            DB::table('stripe_addon_prices')
                ->where('stripe_price_id', $priceId)
                ->where('id', '!=', $keep)
                ->delete();
        }

        Schema::table('stripe_addon_prices', function (Blueprint $table) {
            $table->unique('stripe_price_id', 'stripe_addon_prices_price_unique');
        });
    }

    public function down(): void
    {
        Schema::table('stripe_addon_prices', function (Blueprint $table) {
            $table->dropUnique('stripe_addon_prices_price_unique');
        });
    }
};
