<?php

declare(strict_types=1);

// Non-production DM single-table, single synthetic key probe. No application API.
const MW_TABLE = 'YDY_MEMBER_WEIGHT';
const MW_STAFF_UUID = '224D6A86-EAE5-4A71-BDA7-C43396862EB4';
const MW_YMD = '2099-12-31';
const MW_CREATE_TIME = '2099-12-31 00:00:00';
const MW_MARKER = 'CDXREPLW'; // pbf CHAR(8), reserved for this synthetic row.

$mode = (string) getenv('CDX_WEIGHT_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 $name) {
    if ((string) getenv($name) === '') {
        fwrite(STDERR, "Approved non-production DM environment unavailable\n");
        exit(2);
    }
}

function mwConnect(): PDO
{
    $dsn = 'dm:host=' . getenv('POC_HOST') . ';port=' . getenv('POC_PORT')
        . ';schema=' . getenv('POC_SCHEMA');
    return new PDO($dsn, (string) getenv('POC_USER'),
        (string) getenv('POC_PASSWORD'), [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
}

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

function mwPairRows(PDO $pdo): array
{
    return mwRows($pdo, 'SELECT ID,STAFF_UUID,WEIGHT,PBF,CREATE_TIME,YMD FROM '
        . MW_TABLE . ' WHERE STAFF_UUID=? AND YMD=? ORDER BY ID',
        [MW_STAFF_UUID, MW_YMD]);
}

function mwCount(PDO $pdo, string $sql, array $params = []): int
{
    $rows = mwRows($pdo, $sql, $params);
    return (int) ($rows[0]['C'] ?? $rows[0]['c'] ?? -1);
}

function mwPreflight(PDO $pdo): array
{
    $columns = mwRows($pdo,
        "SELECT COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_DEFAULT FROM USER_TAB_COLUMNS"
        . " WHERE TABLE_NAME='" . MW_TABLE . "' ORDER BY COLUMN_ID");
    $constraints = mwRows($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='" . MW_TABLE . "'"
        . " AND c.CONSTRAINT_TYPE IN ('P','U','R')"
        . ' ORDER BY c.CONSTRAINT_NAME,cc.POSITION');
    $indexes = mwRows($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='" . MW_TABLE . "'"
        . ' ORDER BY i.INDEX_NAME,ic.COLUMN_POSITION');
    $triggers = mwRows($pdo,
        "SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TABLE_NAME='"
        . MW_TABLE . "' ORDER BY TRIGGER_NAME");
    return [
        'columns' => $columns,
        'constraints' => $constraints,
        'indexes' => $indexes,
        'triggers' => $triggers,
        'table_count' => mwCount($pdo, 'SELECT COUNT(*) AS C FROM ' . MW_TABLE),
        'pair_count' => count(mwPairRows($pdo)),
        'marker_count' => mwCount($pdo,
            "SELECT COUNT(*) AS C FROM " . MW_TABLE . " WHERE PBF LIKE 'CDXREPLW%'"),
    ];
}

function mwReady(array $meta): bool
{
    $columns = [];
    foreach ($meta['columns'] as $row) {
        $columns[] = strtoupper((string) ($row['COLUMN_NAME'] ?? $row['column_name'] ?? ''));
    }
    sort($columns);
    $expected = ['CREATE_TIME', 'ID', 'PBF', 'STAFF_UUID', 'WEIGHT', 'YMD'];
    sort($expected);
    if ($columns !== $expected || $meta['table_count'] !== 0
        || $meta['pair_count'] !== 0 || $meta['marker_count'] !== 0
        || count($meta['triggers']) !== 0) {
        return false;
    }
    foreach ($meta['constraints'] as $row) {
        if (strtoupper((string) ($row['CONSTRAINT_TYPE'] ?? $row['constraint_type'] ?? '')) === 'R') {
            return false;
        }
    }
    $groups = [];
    foreach ($meta['indexes'] as $row) {
        $name = (string) ($row['INDEX_NAME'] ?? $row['index_name'] ?? '');
        $groups[$name]['unique'] = strtoupper((string) ($row['UNIQUENESS'] ?? $row['uniqueness'] ?? ''));
        $groups[$name]['columns'][] = strtoupper((string) ($row['COLUMN_NAME'] ?? $row['column_name'] ?? ''));
    }
    foreach ($groups as $group) {
        if (in_array($group['unique'], ['UNIQUE', 'YES'], true)
            && $group['columns'] === ['STAFF_UUID', 'YMD']) {
            return true;
        }
    }
    return false;
}

function mwFormatRow(array $row): array
{
    $weight = $row['WEIGHT'] ?? $row['weight'] ?? null;
    return [
        'id' => (int) ($row['ID'] ?? $row['id']),
        'staff_uuid' => (string) ($row['STAFF_UUID'] ?? $row['staff_uuid']),
        'ymd' => (string) ($row['YMD'] ?? $row['ymd']),
        'create_time' => (string) ($row['CREATE_TIME'] ?? $row['create_time']),
        'pbf' => (string) ($row['PBF'] ?? $row['pbf']),
        'weight_php_type' => gettype($weight),
        'weight_string' => $weight === null ? null : (string) $weight,
        'weight_float_17' => is_numeric($weight) ? sprintf('%.17g', (float) $weight) : null,
    ];
}

$result = ['mode' => $mode, 'staff_uuid' => MW_STAFF_UUID,
    'ymd' => MW_YMD, 'marker' => MW_MARKER, 'events' => []];
$connection = null;
$mayHaveOurRow = false;
$allowedIds = [];
try {
    $connection = mwConnect();
    $preflight = mwPreflight($connection);
    $result['preflight'] = $preflight;
    $result['preflight_ready'] = mwReady($preflight);
    if ($mode === 'preflight') {
        echo json_encode($result, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT) . PHP_EOL;
        exit(0);
    }
    if ((string) getenv('CDX_WEIGHT_APPROVED') !== '1' || !$result['preflight_ready']) {
        throw new RuntimeException('Approval/metadata/pair/marker gate failed');
    }
    $mayHaveOurRow = true;
    $statement = $connection->prepare('INSERT INTO ' . MW_TABLE
        . ' (STAFF_UUID,WEIGHT,PBF,CREATE_TIME,YMD) VALUES (?,?,?,?,?)');
    $statement->execute([MW_STAFF_UUID, '70.55', MW_MARKER,
        MW_CREATE_TIME, MW_YMD]);
    $inserted = mwPairRows(mwConnect());
    $result['events'][] = ['step' => 'insert', 'affected' => $statement->rowCount(),
        'rows' => array_map('mwFormatRow', $inserted)];
    if (count($inserted) !== 1) {
        throw new RuntimeException('INSERT independent readback count mismatch');
    }
    $original = mwFormatRow($inserted[0]);
    $allowedIds[] = $original['id'];
    if ($original['staff_uuid'] !== MW_STAFF_UUID || $original['ymd'] !== MW_YMD
        || $original['pbf'] !== MW_MARKER
        || abs((float) ($inserted[0]['WEIGHT'] ?? $inserted[0]['weight']) - 70.55) > 0.005) {
        throw new RuntimeException('INSERT independent readback value mismatch');
    }
    try {
        $statement = $connection->prepare('REPLACE INTO ' . MW_TABLE
            . ' (STAFF_UUID,WEIGHT,PBF,CREATE_TIME,YMD) VALUES (?,?,?,?,?)');
        $statement->execute([MW_STAFF_UUID, '71.25', MW_MARKER,
            MW_CREATE_TIME, MW_YMD]);
        $after = mwPairRows(mwConnect());
        foreach ($after as $row) {
            $allowedIds[] = (int) ($row['ID'] ?? $row['id']);
        }
        $result['events'][] = ['step' => 'replace', 'status' => 'accepted',
            'affected' => $statement->rowCount(),
            'rows' => array_map('mwFormatRow', $after)];
    } catch (Throwable $error) {
        $after = mwPairRows(mwConnect());
        $preserved = count($after) === 1 && mwFormatRow($after[0]) === $original;
        $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' => array_map('mwFormatRow', $after),
            'original_row_preserved' => $preserved];
        if (!$preserved) {
            throw new RuntimeException('REPLACE failure altered original row');
        }
    }
} catch (Throwable $error) {
    $result['stop'] = ['error_class' => get_class($error),
        'error_code' => $error->getCode(),
        'vendor_code' => $error instanceof PDOException
            ? ($error->errorInfo[1] ?? null) : null];
} finally {
    if ($mode === 'run' && $mayHaveOurRow) {
        try {
            $cleanup = mwConnect();
            $rows = mwPairRows($cleanup);
            $safe = count($rows) <= 1;
            foreach ($rows as $row) {
                $formatted = mwFormatRow($row);
                if (!in_array($formatted['id'], $allowedIds, true)
                    || $formatted['staff_uuid'] !== MW_STAFF_UUID
                    || $formatted['ymd'] !== MW_YMD
                    || $formatted['pbf'] !== MW_MARKER) {
                    $safe = false;
                }
            }
            if (!$safe) {
                throw new RuntimeException('Exact ID/pair/pbf cleanup gate failed');
            }
            if ($rows) {
                $delete = $cleanup->prepare('DELETE FROM ' . MW_TABLE
                    . ' WHERE ID=? AND STAFF_UUID=? AND YMD=? AND PBF=?');
                $delete->execute([(int) ($rows[0]['ID'] ?? $rows[0]['id']),
                    MW_STAFF_UUID, MW_YMD, MW_MARKER]);
                $result['cleanup_deleted'] = $delete->rowCount();
            } else {
                $result['cleanup_deleted'] = 0;
            }
            $result['cleanup_pair_count'] = count(mwPairRows($cleanup));
            $result['cleanup_marker_count'] = mwCount($cleanup,
                "SELECT COUNT(*) AS C FROM " . MW_TABLE . " WHERE PBF LIKE 'CDXREPLW%'");
            $result['cleanup_table_count'] = mwCount($cleanup,
                'SELECT COUNT(*) AS C FROM ' . MW_TABLE);
        } 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
        || ($result['cleanup_marker_count'] ?? -1) !== 0))) {
    exit(1);
}