<?php

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

/**
 * Revenue is branch-attributed only through `payments.branch_id`. `invoices`
 * carries no branch at all — POSService passes 'branch_id' into
 * InvoiceService::create() and mass assignment drops it, because it was never
 * in Invoice::$fillable.
 *
 * Two consequences, both visible in the branch columns of FinancialOverview:
 *
 *  1. getCollectionRate() scopes invoices through whereHas('payments', branch),
 *     so an invoice with no payments yet belongs to no branch. Portal invoices
 *     waiting on an InstaPay proof would vanish from every branch's overdue
 *     figure — inflating the collection rate exactly when collections are worst.
 *  2. `transactions` has no branch either, so the ledger cannot be read per
 *     branch even once PaymentService credits real revenue accounts.
 *
 * Both columns are additive and nullable. NOT NULL is enforced at the service
 * boundary for portal-originated money, not by a constraint on a populated
 * table.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (Schema::hasTable('invoices') && ! Schema::hasColumn('invoices', 'branch_id')) {
            Schema::table('invoices', function (Blueprint $table) {
                $table->foreignId('branch_id')->nullable()->after('academy_id')
                    ->constrained('branches')->nullOnDelete();
            });

            // Backfill from the branch that actually took the money, then from
            // the participant the invoice bills.
            DB::statement("
                UPDATE invoices i
                SET branch_id = p.branch_id
                FROM (
                    SELECT DISTINCT ON (invoice_id) invoice_id, branch_id
                    FROM payments
                    WHERE invoice_id IS NOT NULL AND branch_id IS NOT NULL
                    ORDER BY invoice_id, created_at ASC
                ) p
                WHERE p.invoice_id = i.id AND i.branch_id IS NULL
            ");

            if (Schema::hasTable('participants') && Schema::hasColumn('participants', 'branch_id')) {
                DB::statement("
                    UPDATE invoices i
                    SET branch_id = pt.branch_id
                    FROM participants pt
                    WHERE i.branch_id IS NULL
                      AND i.billable_type = 'App\\\\Domain\\\\Participant\\\\Models\\\\Participant'
                      AND i.billable_id = pt.id
                      AND pt.branch_id IS NOT NULL
                ");
            }

            Schema::table('invoices', function (Blueprint $table) {
                $table->index(['academy_id', 'branch_id'], 'invoices_academy_branch_index');
            });
        }

        if (Schema::hasTable('transactions') && ! Schema::hasColumn('transactions', 'branch_id')) {
            Schema::table('transactions', function (Blueprint $table) {
                $table->foreignId('branch_id')->nullable()->after('academy_id')
                    ->constrained('branches')->nullOnDelete();
            });

            DB::statement("
                UPDATE transactions t
                SET branch_id = p.branch_id
                FROM payments p
                WHERE t.payment_id = p.id AND t.branch_id IS NULL AND p.branch_id IS NOT NULL
            ");

            Schema::table('transactions', function (Blueprint $table) {
                $table->index(['academy_id', 'branch_id', 'transaction_date'], 'transactions_academy_branch_date_index');
            });
        }
    }

    public function down(): void
    {
        if (Schema::hasTable('transactions') && Schema::hasColumn('transactions', 'branch_id')) {
            Schema::table('transactions', function (Blueprint $table) {
                $table->dropIndex('transactions_academy_branch_date_index');
                $table->dropConstrainedForeignId('branch_id');
            });
        }

        if (Schema::hasTable('invoices') && Schema::hasColumn('invoices', 'branch_id')) {
            Schema::table('invoices', function (Blueprint $table) {
                $table->dropIndex('invoices_academy_branch_index');
                $table->dropConstrainedForeignId('branch_id');
            });
        }
    }
};
