<?php

declare(strict_types=1);

$dsn = getenv('POC_DSN');
$user = getenv('POC_USER');
$password = getenv('POC_PASSWORD');
if (!$dsn || !$user || !$password) exit(2);
$pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$table = 'CODEX_MERGE_POC_' . strtoupper(bin2hex(random_bytes(4)));
$check = $pdo->prepare('SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=?');
$check->execute([$table]);
if ((int)$check->fetchColumn() !== 0) throw new RuntimeException('Probe table already exists');
$created = false;
try {
    $pdo->exec('CREATE TABLE "' . $table . '" (ID INT AUTO_INCREMENT PRIMARY KEY, BIZ_TYPE VARCHAR(20), BIZ_KEY VARCHAR(20), SOURCE_ID VARCHAR(20), UNIQUE(BIZ_TYPE,BIZ_KEY))');
    $created = true;
    $sql = 'MERGE INTO ' . $table . ' t USING (SELECT ? AS BIZ_TYPE, ? AS BIZ_KEY, ? AS SOURCE_ID FROM DUAL) s '
        . 'ON (t.BIZ_TYPE=s.BIZ_TYPE AND t.BIZ_KEY=s.BIZ_KEY) '
        . 'WHEN MATCHED THEN UPDATE SET t.SOURCE_ID=s.SOURCE_ID '
        . 'WHEN NOT MATCHED THEN INSERT (BIZ_TYPE,BIZ_KEY,SOURCE_ID) VALUES (s.BIZ_TYPE,s.BIZ_KEY,s.SOURCE_ID)';
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['order', 'o1', 'first']);
    $stmt->execute(['order', 'o1', 'second']);
    $value = $pdo->query("SELECT SOURCE_ID FROM $table WHERE BIZ_TYPE='order' AND BIZ_KEY='o1'")->fetchColumn();
    $count = (int)$pdo->query("SELECT COUNT(*) FROM $table")->fetchColumn();
    if ($value !== 'second' || $count !== 1) throw new RuntimeException('MERGE did not preserve upsert semantics');

    $insertOnly = $pdo->prepare('MERGE INTO ' . $table . ' t USING (SELECT ? AS BIZ_TYPE, ? AS BIZ_KEY, ? AS SOURCE_ID FROM DUAL) s '
        . 'ON (t.BIZ_TYPE=s.BIZ_TYPE AND t.BIZ_KEY=s.BIZ_KEY) '
        . 'WHEN NOT MATCHED THEN INSERT (BIZ_TYPE,BIZ_KEY,SOURCE_ID) VALUES (s.BIZ_TYPE,s.BIZ_KEY,s.SOURCE_ID)');
    $insertOnly->execute(['order', 'o1', 'ignored']);
    $matchedAffected = $insertOnly->rowCount();
    $insertOnly->execute(['order', 'o2', 'new']);
    $insertedAffected = $insertOnly->rowCount();
    $afterInsertOnly = $pdo->query("SELECT SOURCE_ID FROM $table WHERE BIZ_TYPE='order' AND BIZ_KEY='o1'")->fetchColumn();
    $total = (int)$pdo->query("SELECT COUNT(*) FROM $table")->fetchColumn();
    echo json_encode(['merge_value' => $value, 'row_count' => $count, 'matched_affected' => $matchedAffected, 'inserted_affected' => $insertedAffected, 'matched_value' => $afterInsertOnly, 'total' => $total]) . PHP_EOL;
    if ($matchedAffected !== 0 || $insertedAffected !== 1 || $afterInsertOnly !== 'second' || $total !== 2) {
        throw new RuntimeException('MERGE insert-only did not preserve INSERT IGNORE semantics');
    }
} finally {
    if ($created) $pdo->exec('DROP TABLE "' . $table . '"');
}