<?php

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

/**
 * A member's claim that they sent money, pending a human check.
 *
 * The financial invariants forbid the obvious shortcut. `transactions` are
 * immutable, PaymentService::recordPayment() forces status = Confirmed and
 * posts to the ledger immediately — so **an unverified screenshot must never
 * create a Payment**. It creates one of these instead, and only approval calls
 * recordPayment(), so double-entry happens exactly once and nothing in the
 * ledger is ever edited.
 *
 * A screenshot is not evidence. It is a convenience; the control is
 * reconciliation against the academy's own InstaPay or bank record.
 * `sender_reference` is the backbone: one per academy per method, enforced by
 * a partial unique index, which kills replay, cross-invoice reuse and
 * "someone else's transfer against my invoice" in a single constraint.
 *
 * Two unique indexes, from two different concerns, both kept: one live proof
 * per invoice (so a member cannot flood the queue), and one payment per proof
 * (so an approval cannot be posted twice).
 *
 * `timestamps()` on purpose: a proof is a request with a lifecycle, not a
 * ledger record. The ledger row it eventually produces is the immutable one.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (Schema::hasTable('payment_proofs')) {
            return;
        }

        Schema::create('payment_proofs', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('academy_id')->constrained()->cascadeOnDelete();

            // NOT NULL: revenue is branch-attributed only through payments, and
            // a NULL-branch payment lands in the all-branches total and in no
            // branch at all — the columns stop summing with no error anywhere.
            $table->foreignId('branch_id')->constrained('branches');

            $table->foreignId('invoice_id')->constrained();
            $table->foreignId('participant_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('submitted_by')->constrained('users');

            $table->bigInteger('amount_claimed');            // what the payer typed
            $table->bigInteger('amount_approved')->nullable(); // what the reviewer read off the statement

            $table->string('method', 20)->default('instapay');
            $table->string('sender_reference', 64)->nullable();
            $table->string('sender_phone', 20)->nullable();
            $table->timestamp('transferred_at')->nullable();

            // Private disk. A transfer screenshot carries a phone number, a
            // name and a bank balance.
            $table->string('proof_path');
            $table->string('proof_mime', 60)->nullable();
            $table->unsignedBigInteger('proof_size')->nullable();

            $table->string('status', 20)->default('pending');
            $table->foreignId('reviewed_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('reviewed_at')->nullable();
            $table->foreignId('second_reviewed_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('second_reviewed_at')->nullable();
            $table->string('rejection_reason', 40)->nullable();
            $table->text('review_notes')->nullable();
            $table->string('review_ip', 45)->nullable();

            $table->foreignId('payment_id')->nullable()->constrained()->nullOnDelete();
            $table->jsonb('metadata')->default('{}');
            $table->timestamps();

            $table->index(['academy_id', 'status', 'created_at']);
            $table->index(['academy_id', 'branch_id', 'status']);
        });

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

        DB::statement("ALTER TABLE payment_proofs ADD CONSTRAINT payment_proofs_status_check CHECK (status IN ('pending','under_review','approved','rejected','superseded'))");
        DB::statement("ALTER TABLE payment_proofs ADD CONSTRAINT payment_proofs_method_check CHECK (method IN ('instapay','bank_transfer'))");
        DB::statement('ALTER TABLE payment_proofs ADD CONSTRAINT payment_proofs_amount_check CHECK (amount_claimed > 0 AND (amount_approved IS NULL OR amount_approved > 0))');

        // An approved proof without a payment is a proof that took money and
        // told no one. The constraint makes that state unrepresentable.
        DB::statement("ALTER TABLE payment_proofs ADD CONSTRAINT payment_proofs_approved_payment_check CHECK (status <> 'approved' OR payment_id IS NOT NULL)");

        DB::statement('CREATE UNIQUE INDEX payment_proofs_one_payment ON payment_proofs (payment_id) WHERE payment_id IS NOT NULL');
        DB::statement("CREATE UNIQUE INDEX payment_proofs_one_pending ON payment_proofs (academy_id, invoice_id) WHERE status IN ('pending','under_review')");
        DB::statement("CREATE UNIQUE INDEX payment_proofs_sender_ref ON payment_proofs (academy_id, method, sender_reference) WHERE sender_reference IS NOT NULL AND status <> 'rejected'");

        // Append-only after submission: once a proof leaves `pending` the payer
        // may not change what they claimed, and nobody may rewrite the review
        // trail. Enforced in the database because approving a proof is the
        // moral equivalent of taking cash, and Auditable::createAuditLog()
        // silently writes nothing when it cannot resolve an academy.
        DB::unprepared(<<<'SQL'
            CREATE OR REPLACE FUNCTION payment_proofs_freeze() RETURNS trigger AS $$
            BEGIN
                IF OLD.status NOT IN ('pending', 'under_review') THEN
                    IF NEW.amount_claimed  IS DISTINCT FROM OLD.amount_claimed
                    OR NEW.amount_approved IS DISTINCT FROM OLD.amount_approved
                    OR NEW.proof_path      IS DISTINCT FROM OLD.proof_path
                    OR NEW.invoice_id      IS DISTINCT FROM OLD.invoice_id
                    OR NEW.payment_id      IS DISTINCT FROM OLD.payment_id
                    OR NEW.status          IS DISTINCT FROM OLD.status THEN
                        RAISE EXCEPTION 'payment_proofs row % is settled and cannot be altered', OLD.id;
                    END IF;
                END IF;
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;

            CREATE TRIGGER payment_proofs_freeze_trigger
            BEFORE UPDATE ON payment_proofs
            FOR EACH ROW EXECUTE FUNCTION payment_proofs_freeze();
        SQL);
    }

    public function down(): void
    {
        if (DB::getDriverName() === 'pgsql') {
            DB::unprepared('DROP TRIGGER IF EXISTS payment_proofs_freeze_trigger ON payment_proofs; DROP FUNCTION IF EXISTS payment_proofs_freeze();');
        }

        Schema::dropIfExists('payment_proofs');
    }
};
