<?php

declare(strict_types=1);

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

/**
 * Adds per-partition advisory lock to audit_chain_hash() trigger function.
 *
 * Root cause: concurrent INSERT workers (e.g. supplier-webhook handlers) all
 * read the same prev_hash before any of them commits → multiple rows derive
 * their hash from the same predecessor → hash chain branches → validator finds
 * mismatches (Finding 1 from Stage-5 Day-1 monitoring).
 *
 * Fix: derive a bigint lock key from the physical partition OID (TG_RELID).
 * pg_advisory_xact_lock() serialises concurrent INSERTs into the SAME partition
 * without blocking INSERTs to other partitions (distinct OIDs → distinct keys).
 * The lock is automatically released at transaction end.
 *
 * Hash formula: unchanged (verbatim from db/schema.sql:3107-3127):
 *   digest(COALESCE(prev_hash, ''::bytea) || NEW::text::bytea, 'sha256')
 *
 * Ref: docs/superpowers/plans/2026-05-29-audit-chain-race-fix.md Task 2
 */
return new class extends Migration
{
    public function up(): void
    {
        DB::statement(<<<'SQL'
            CREATE OR REPLACE FUNCTION public.audit_chain_hash() RETURNS trigger AS $$
            DECLARE
                prev_hash BYTEA;
                lock_key  BIGINT;
            BEGIN
                -- Derive a partition-specific advisory lock key from the physical
                -- table OID (TG_RELID). Each child partition has a distinct OID,
                -- so concurrent INSERTs to DIFFERENT partitions do not block each
                -- other, while concurrent INSERTs to the SAME partition are
                -- serialised — preventing the race that branches the hash chain.
                lock_key := ('x' || lpad(to_hex(TG_RELID::int), 16, '0'))::bit(64)::bigint;
                PERFORM pg_advisory_xact_lock(lock_key);

                -- Берём log_hash последней строки этой таблицы. NULL для первой записи.
                -- TG_TABLE_NAME — имя таблицы, через которое триггер сработал; используем
                -- format/EXECUTE для полиморфности.
                EXECUTE format(
                    'SELECT log_hash FROM %I ORDER BY id DESC LIMIT 1',
                    TG_TABLE_NAME
                ) INTO prev_hash;

                -- log_hash = sha256(prev_hash || NEW::text). Если prev_hash NULL — берём
                -- пустую байтовую строку (первая запись цепочки).
                NEW.log_hash := digest(
                    COALESCE(prev_hash, ''::bytea) || NEW::text::bytea,
                    'sha256'
                );
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        SQL);
    }

    public function down(): void
    {
        // Restore verbatim original from db/schema.sql:3107-3127 (without advisory lock).
        DB::statement(<<<'SQL'
            CREATE OR REPLACE FUNCTION public.audit_chain_hash() RETURNS trigger AS $$
            DECLARE
                prev_hash BYTEA;
            BEGIN
                -- Берём log_hash последней строки этой таблицы. NULL для первой записи.
                -- TG_TABLE_NAME — имя таблицы, через которое триггер сработал; используем
                -- format/EXECUTE для полиморфности.
                EXECUTE format(
                    'SELECT log_hash FROM %I ORDER BY id DESC LIMIT 1',
                    TG_TABLE_NAME
                ) INTO prev_hash;

                -- log_hash = sha256(prev_hash || NEW::text). Если prev_hash NULL — берём
                -- пустую байтовую строку (первая запись цепочки).
                NEW.log_hash := digest(
                    COALESCE(prev_hash, ''::bytea) || NEW::text::bytea,
                    'sha256'
                );
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        SQL);
    }
};
