<?php

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

/**
 * Point hand-typed product invoice lines at the product they sold.
 *
 * 2026_09_01_000002 repaired POS sales by matching each line back to its
 * pos_transaction_item. Lines typed straight into the enrolment and payment
 * wizards have no POS row to match against, so they stayed NULL — and a NULL
 * itemable_type reads everywhere as "programme subscription". A shopping bag
 * sold that way inflates the player's subscription figure, is missing from
 * product revenue, and leaves the group roster claiming they never bought the
 * kit they are wearing.
 *
 * Matching is on the FULL trimmed description being identical to the product's
 * name, never a substring. Substring matching on Arabic is how you decide that
 * "تجهيزي" (preparatory) contains "زي" (kit) and reclassify a subscription as
 * merchandise. Partial descriptions ("الشنطة", "قسط القيد") are deliberately
 * left alone: they are money, and a guess about money is worse than a gap.
 *
 * Only fills rows that are still NULL and only where exactly one product in
 * that academy carries the name, so it is safe to re-run and cannot overwrite
 * a correct value.
 */
return new class extends Migration
{
    public function up(): void
    {
        foreach (['invoice_items', 'invoices', 'products'] as $table) {
            if (! Schema::hasTable($table)) {
                return;
            }
        }

        foreach ([['invoice_items', 'itemable_type'], ['invoices', 'academy_id'], ['products', 'name_ar']] as [$table, $column]) {
            if (! Schema::hasColumn($table, $column)) {
                return;
            }
        }

        DB::statement("
            UPDATE invoice_items ii
            SET itemable_type = ?, itemable_id = m.product_id
            FROM invoices inv,
                 (
                     SELECT p.id AS product_id,
                            p.academy_id,
                            TRIM(p.name_ar) AS nm
                     FROM products p
                     WHERE p.deleted_at IS NULL
                       AND p.name_ar IS NOT NULL
                       AND TRIM(p.name_ar) <> ''
                       -- Two products sharing a name make the match ambiguous;
                       -- skip rather than pick one.
                       AND NOT EXISTS (
                           SELECT 1
                           FROM products q
                           WHERE q.deleted_at IS NULL
                             AND q.academy_id = p.academy_id
                             AND q.id <> p.id
                             AND TRIM(q.name_ar) = TRIM(p.name_ar)
                       )
                 ) AS m
            WHERE ii.invoice_id = inv.id
              AND ii.itemable_type IS NULL
              AND inv.academy_id = m.academy_id
              AND TRIM(ii.description) = m.nm
        ", ['App\\Domain\\Inventory\\Models\\Product']);
    }

    public function down(): void
    {
        // Intentionally irreversible, for the same reason as
        // 2026_09_01_000002: up() only fills rows that were NULL and keeps no
        // record of which, so any rollback would also blank correct links
        // written afterwards. Losing more than the rollback gains is not a
        // rollback.
    }
};
