<?php

declare(strict_types=1);

// Non-production DM, one synthetic key only. Preflight cannot write. Run requires
// an explicit approval flag and always attempts exact-key/marker cleanup.
const TEST_STATION_UUID = 'B1BADC1F-5980-4438-ADF5-607948C59050';
const TEST_NUTRITION_UUID = '106A030B-3CDC-4A17-B1C2-5307673B4F85';
const TEST_MARKER = 'CDXREPLN-B1BADC1F';
const TEST_TABLE = 'YDY_NUTRITION_CUPBOARD';

$mode = (string) getenv('CDX_REPLACE_MODE');
if (!in_array($mode, ['preflight', 'run'], true)) {
    fwrite(STDERR, "Explicit preflight or run mode required\n");
    exit(2);
}
foreach (['POC_HOST', 'POC_PORT', 'POC_SCHEMA', 'POC_USER', 'POC_PASSWORD'] as $key) {
    if ((string) getenv($key) === '') {
        fwrite(STDERR, "Approved non-production DM environment unavailable\n");
        exit(2);
    }
}

function fetchRows(PDO $pdo, string $sql, array $bind = []): array
{
    $statement = $pdo->prepare($sql);
    $statement->execute($bind);
    return $statement->fetchAll(PDO::FETCH_ASSOC);
}

function pairRows(PDO $pdo): array
{
    return fetchRows($pdo,
        'SELECT ID,STATION_UUID,NUTRITION_UUID,CURRENT_NUM,ALL_NUM FROM '
        . TEST_TABLE . ' WHERE STATION_UUID=? AND NUTRITION_UUID=? ORDER BY ID',
        [TEST_STATION_UUID, TEST_NUTRITION_UUID]);
}

function tableCount(PDO $pdo): int
{
    $rows = fetchRows($pdo, 'SELECT COUNT(*) AS C FROM ' . TEST_TABLE);
    return (int) ($rows[0]['C'] ?? $rows[0]['c'] ?? -1);
}

function inspect(PDO $pdo): array
{
    $columns = fetchRows($pdo,
        "SELECT COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_DEFAULT FROM USER_TAB_COLUMNS"
        . " WHERE TABLE_NAME='" . TEST_TABLE . "' ORDER BY COLUMN_ID");
    $constraints = fetchRows($pdo,
        "SELECT c.CONSTRAINT_NAME,c.CONSTRAINT_TYPE,cc.COLUMN_NAME,cc.POSITION"
        . " FROM USER_CONSTRAINTS c JOIN USER_CONS_COLUMNS cc"
        . " ON c.CONSTRAINT_NAME=cc.CONSTRAINT_NAME"
        . " WHERE c.TABLE_NAME='" . TEST_TABLE . "' AND c.CONSTRAINT_TYPE IN ('P','U')"
        . ' ORDER BY c.CONSTRAINT_NAME,cc.POSITION');
    $indexes = fetchRows($pdo,
        "SELECT i.INDEX_NAME,i.UNIQUENESS,ic.COLUMN_NAME,ic.COLUMN_POSITION"
        . " FROM USER_INDEXES i JOIN USER_IND_COLUMNS ic ON i.INDEX_NAME=ic.INDEX_NAME"
        . " WHERE i.TABLE_NAME='" . TEST_TABLE . "'"
        . ' ORDER BY i.INDEX_NAME,ic.COLUMN_POSITION');
    $triggers = fetchRows($pdo,
        "SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TABLE_NAME='"
        . TEST_TABLE . "' ORDER BY TRIGGER_NAME");
    return [
        'columns' => $columns,
        'constraints' => $constraints,
        'indexes' => $indexes,
        'triggers' => $triggers,
        'table_count' => tableCount($pdo),
        'pair_count' => count(pairRows($pdo)),
    ];
}

function metadataReady(array $meta): bool
{
    $names = [];
    foreach ($meta['columns'] as $row) {
        $names[] = strtoupper((string) ($row['COLUMN_NAME'] ?? $row['column_name'] ?? ''));
    }
    sort($names);
    $expected = ['ALL_NUM', 'CURRENT_NUM', 'ID', 'NUTRITION_UUID', 'STATION_UUID'];
    sort($expected);
    if ($names !== $expected || $meta['table_count'] !== 0 || $meta['pair_count'] !== 0) {
        return false;
    }
    // Unique index must be exactly the two-key pair in this order or reverse.
    $groups = [];
    foreach ($meta['indexes'] as $row) {
        $name = (string) ($row['INDEX_NAME'] ?? $row['index_name'] ?? '');
        $unique = strtoupper((string) ($row['UNIQUENESS'] ?? $row['uniqueness'] ?? ''));
        $column = strtoupper((string) ($row['COLUMN_NAME'] ?? $row['column_name'] ?? ''));
        $groups[$name]['unique'] = $unique;
        $groups[$name]['columns'][] = $column;
    }
    $found = false;
    foreach ($groups as $group) {
        $columns = $group['columns'];
        sort($columns);
        if (in_array($group['unique'], ['UNIQUE', 'YES'], true)
            && $columns === ['NUTRITION_UUID', 'STATION_UUID']) {
            $found = true;
        }
    }
    return $found && count($meta['triggers']) === 0;
}

$result = [
    'mode' => $mode,
    'station_uuid' => TEST_STATION_UUID,
    'nutrition_uuid' => TEST_NUTRITION_UUID,
    'marker' => TEST_MARKER,
    'events' => [],
];
$pdo = null;
$mayHaveOurRow = false;
try {
    $dsn = 'dm:host=' . getenv('POC_HOST') . ';port=' . getenv('POC_PORT')
        . ';schema=' . getenv('POC_SCHEMA');
    $pdo = new PDO($dsn, (string) getenv('POC_USER'),
        (string) getenv('POC_PASSWORD'), [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    $meta = inspect($pdo);
    $result['preflight'] = $meta;
    $result['preflight_ready'] = metadataReady($meta);
    if ($mode === 'preflight') {
        echo json_encode($result, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT) . PHP_EOL;
        exit(0);
    }
    if ((string) getenv('CDX_REPLACE_APPROVED') !== '1' || !$result['preflight_ready']) {
        throw new RuntimeException('Explicit approval and clean metadata gate required');
    }
    $mayHaveOurRow = true; // Set before INSERT so an uncertain result still reaches cleanup.
    $statement = $pdo->prepare('INSERT INTO ' . TEST_TABLE
        . ' (STATION_UUID,NUTRITION_UUID,CURRENT_NUM,ALL_NUM) VALUES (?,?,?,?)');
    $statement->execute([TEST_STATION_UUID, TEST_NUTRITION_UUID, 7, TEST_MARKER . ':1,2']);
    $inserted = pairRows($pdo);
    $result['events'][] = ['step' => 'insert', 'affected' => $statement->rowCount(),
        'rows' => $inserted];
    if (count($inserted) !== 1
        || (string) ($inserted[0]['ALL_NUM'] ?? $inserted[0]['all_num'] ?? '') !== TEST_MARKER . ':1,2'
        || (int) ($inserted[0]['CURRENT_NUM'] ?? $inserted[0]['current_num'] ?? -1) !== 7) {
        throw new RuntimeException('Inserted synthetic row mismatch; do not run REPLACE');
    }
    try {
        $statement = $pdo->prepare('REPLACE INTO ' . TEST_TABLE
            . ' (STATION_UUID,NUTRITION_UUID,CURRENT_NUM,ALL_NUM) VALUES (?,?,?,?)');
        $statement->execute([TEST_STATION_UUID, TEST_NUTRITION_UUID, 9, TEST_MARKER . ':3,4']);
        $result['events'][] = ['step' => 'replace', 'status' => 'accepted',
            'affected' => $statement->rowCount(), 'rows' => pairRows($pdo)];
    } catch (Throwable $error) {
        $after = pairRows($pdo);
        $result['events'][] = ['step' => 'replace', 'status' => 'rejected',
            'error_class' => get_class($error), 'error_code' => $error->getCode(),
            'vendor_code' => $error instanceof PDOException
                ? ($error->errorInfo[1] ?? null) : null,
            'rows_after' => $after,
            'original_row_preserved' => $after === $inserted];
        if ($after !== $inserted) {
            $result['stop'] = ['error_class' => 'UnexpectedMutationAfterReplaceFailure',
                'error_code' => 0];
        }
    }
} catch (Throwable $error) {
    $result['stop'] = ['error_class' => get_class($error),
        'error_code' => $error->getCode()];
} finally {
    if ($mode === 'run' && $mayHaveOurRow && $pdo instanceof PDO) {
        try {
            $rows = pairRows($pdo);
            $safe = count($rows) <= 1;
            foreach ($rows as $row) {
                $value = (string) ($row['ALL_NUM'] ?? $row['all_num'] ?? '');
                if (strpos($value, TEST_MARKER . ':') !== 0) {
                    $safe = false;
                }
            }
            if (!$safe) {
                throw new RuntimeException('Exact key/marker cleanup gate failed');
            }
            if ($rows) {
                $delete = $pdo->prepare('DELETE FROM ' . TEST_TABLE
                    . ' WHERE STATION_UUID=? AND NUTRITION_UUID=? AND ALL_NUM=?');
                $delete->execute([TEST_STATION_UUID, TEST_NUTRITION_UUID,
                    $rows[0]['ALL_NUM'] ?? $rows[0]['all_num']]);
                $result['cleanup_deleted'] = $delete->rowCount();
            } else {
                $result['cleanup_deleted'] = 0;
            }
            $result['cleanup_pair_count'] = count(pairRows($pdo));
            $result['cleanup_table_count'] = tableCount($pdo);
        } catch (Throwable $error) {
            $result['cleanup_error'] = ['error_class' => get_class($error),
                'error_code' => $error->getCode()];
        }
    }
}
echo json_encode($result, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT) . PHP_EOL;
if (isset($result['stop']) || isset($result['cleanup_error'])
    || ($mode === 'run' && ($result['cleanup_pair_count'] ?? -1) !== 0)) {
    exit(1);
}