<?php

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

/**
 * Point historical POS invoice lines at the product they sold.
 *
 * POSService::buildInvoiceItems() used to write description-only lines, leaving
 * itemable_type NULL. Reporting reads a NULL itemable_type as "programme
 * subscription", so every past POS sale was counted as subscription revenue,
 * was missing from product revenue, and never satisfied the "has this
 * participant bought the required product" check.
 *
 * Matches an invoice line back to its POS line by invoice -> pos_transaction ->
 * pos_transaction_items on (name, quantity). Only fills rows that are still
 * NULL, and only where the match is unambiguous, 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', 'pos_transactions', 'pos_transaction_items'] as $t) {
            if (! Schema::hasTable($t)) {
                return;
            }
        }

        $productClass = 'App\\Domain\\Inventory\\Models\\Product';
        $kitClass = 'App\\Domain\\Inventory\\Models\\Kit';

        // One UPDATE per item type. The subquery requires exactly one matching
        // POS line, so ambiguous rows are left untouched rather than guessed.
        foreach ([['product', $productClass], ['kit', $kitClass]] as [$posType, $class]) {
            DB::statement("
                UPDATE invoice_items ii
                SET itemable_type = ?, itemable_id = m.item_id
                FROM (
                    SELECT pti.item_id,
                           pt.invoice_id,
                           pti.item_name_ar,
                           pti.quantity
                    FROM pos_transaction_items pti
                    JOIN pos_transactions pt ON pt.id = pti.pos_transaction_id
                    WHERE pti.item_type = ?
                      AND pti.item_id IS NOT NULL
                      AND pt.invoice_id IS NOT NULL
                ) AS m
                WHERE ii.invoice_id = m.invoice_id
                  AND ii.description = m.item_name_ar
                  AND ii.quantity = m.quantity
                  AND ii.itemable_type IS NULL
            ", [$class, $posType]);
        }
    }

    public function down(): void
    {
        // Intentionally irreversible.
        //
        // up() only *fills* rows that were NULL, but there is no record of
        // which ones it touched. POSService now stamps itemable_type at
        // creation time, so a blanket "NULL out every POS invoice line" would
        // destroy correct data written after this migration ran — losing more
        // than rolling back gains. A repair migration that cannot be undone
        // without collateral damage is better left un-undone.
    }
};
