<?php

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

/**
 * The deleted mobile API's OrderController built invoice lines by hand with
 * keys that are not columns — `academy_id`, `description_ar` and `line_total`.
 * Laravel does not call preventSilentlyDiscardingAttributes() anywhere in this
 * codebase, so those keys were dropped and `total_amount` took its default of
 * zero. Every line written by that controller says the sale was worth nothing.
 *
 * The damage is downstream, not on the row:
 *  - ParticipantBillingService::allocate() `continue`s on a zero part, so
 *    productPaid() returns no key at all and the per-product ownership screen
 *    shows 0 paid for a product the member demonstrably bought.
 *  - FinancialOverview::getRevenueBreakdown() counts the payment in the total
 *    and in neither the subscription nor the product bucket, so the two stop
 *    reconciling to the total.
 *  - Any later recalculateTotals() on such an invoice sets total_amount to 0,
 *    due_amount to -paid_amount and the status to overpaid, after which
 *    getCollectionRate() starts summing negative numbers.
 *
 * The controller is gone. This repairs what it left behind, from the line's
 * own quantity and unit price — the two values it did write correctly. Scoped
 * to the INV-MOB series so it can never touch a line that is legitimately
 * zero (a fully discounted item, a free kit).
 *
 * The verified oc_sport tenant has no INV-MOB invoices; other installations may.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('invoice_items') || ! Schema::hasTable('invoices')) {
            return;
        }

        if (DB::getDriverName() !== 'pgsql') {
            return;
        }

        $repaired = DB::update("
            UPDATE invoice_items ii
            SET total_amount = (ii.quantity * ii.unit_price)
                             - COALESCE(ii.discount_amount, 0)
                             + COALESCE(ii.tax_amount, 0)
            FROM invoices i
            WHERE i.id = ii.invoice_id
              AND ii.total_amount = 0
              AND ii.unit_price > 0
              AND i.number LIKE 'INV-MOB-%'
        ");

        if ($repaired > 0) {
            // Bring the headers back in line with the repaired lines. Only the
            // invoices just touched, and only their subtotal/total/due — the
            // paid amount is what actually changed hands and is not recomputed.
            DB::update("
                UPDATE invoices i
                SET subtotal_amount = s.line_sum,
                    total_amount    = s.line_sum - COALESCE(i.discount_amount,0)
                                      + COALESCE(i.tax_amount,0) + COALESCE(i.service_fee_amount,0),
                    due_amount      = (s.line_sum - COALESCE(i.discount_amount,0)
                                      + COALESCE(i.tax_amount,0) + COALESCE(i.service_fee_amount,0))
                                      - COALESCE(i.paid_amount,0)
                FROM (
                    SELECT invoice_id, SUM(total_amount) AS line_sum
                    FROM invoice_items GROUP BY invoice_id
                ) s
                WHERE s.invoice_id = i.id AND i.number LIKE 'INV-MOB-%'
            ");
        }
    }

    public function down(): void
    {
        // Deliberately empty: the previous state was a zero written by a bug.
    }
};
