<?php

declare(strict_types=1);

// Local-config development MySQL only; exact synthetic keys; no business row reads.
const TABLE_NAME = 'ydy_nutrition_cupboard';
const PAIRS = [
    'A' => [
        'station' => '94A17980-774D-4BBA-8DE8-C250D016944F',
        'nutrition' => 'CDA38A1A-826B-4AB4-9AAE-3F6C3EE60EC9',
        'marker' => 'CDXREPLN-A-94A17980',
    ],
    'B' => [
        'station' => 'D0BA9896-BE7C-468F-8556-72AFA3C5EC4D',
        'nutrition' => '5F43953F-F21F-434C-B0EB-0F73A7EB4833',
        'marker' => 'CDXREPLN-B-D0BA9896',
    ],
];

$group = (string) getenv('CDX_REPLACE_GROUP');
$mode = (string) getenv('CDX_REPLACE_MODE');
if (!isset(PAIRS[$group]) || !in_array($mode, ['preflight', 'run'], true)) {
    fwrite(STDERR, "Explicit A/B and preflight/run required\n");
    exit(2);
}
$key = PAIRS[$group];
$configPath = realpath(__DIR__
    . '/../../../../../zhctproject/store/application/config/local/database.php');
if (!$configPath || strpos($configPath, '/zhctproject/store/application/config/local/') === false) {
    fwrite(STDERR, "Local development config unavailable\n");
    exit(2);
}
$config = require $configPath;
foreach (['hostname', 'database', 'username', 'password'] as $name) {
    if (!isset($config[$name]) || (string) $config[$name] === '') {
        fwrite(STDERR, "Local development config incomplete\n");
        exit(2);
    }
}

function db(array $config): PDO
{
    $dsn = 'mysql:host=' . $config['hostname'] . ';port='
        . (int) ($config['hostport'] ?? 3306) . ';dbname=' . $config['database']
        . ';charset=utf8mb4';
    return new PDO($dsn, (string) $config['username'], (string) $config['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_TIMEOUT => 8,
    ]);
}

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

function pairRows(PDO $pdo, array $key): array
{
    return rows($pdo, 'SELECT id,station_uuid,nutrition_uuid,current_num,all_num FROM '
        . TABLE_NAME . ' WHERE station_uuid=? AND nutrition_uuid=? ORDER BY id',
        [$key['station'], $key['nutrition']]);
}

function prefixCount(PDO $pdo): int
{
    $result = rows($pdo, "SELECT COUNT(*) AS c FROM " . TABLE_NAME
        . " WHERE all_num LIKE 'CDXREPLN%'");
    return (int) ($result[0]['c'] ?? -1);
}

function inspect(PDO $pdo, array $config, array $key): array
{
    $version = (string) $pdo->query('SELECT VERSION()')->fetchColumn();
    $columns = rows($pdo,
        'SELECT COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,EXTRA'
        . ' FROM information_schema.COLUMNS'
        . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION',
        [$config['database'], TABLE_NAME]);
    $indexes = rows($pdo,
        'SELECT INDEX_NAME,NON_UNIQUE,SEQ_IN_INDEX,COLUMN_NAME'
        . ' FROM information_schema.STATISTICS'
        . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY INDEX_NAME,SEQ_IN_INDEX',
        [$config['database'], TABLE_NAME]);
    $triggerRows = rows($pdo,
        'SELECT COUNT(*) AS c FROM information_schema.TRIGGERS'
        . ' WHERE TRIGGER_SCHEMA=? AND EVENT_OBJECT_TABLE=?',
        [$config['database'], TABLE_NAME]);
    return [
        'version' => $version,
        'columns' => $columns,
        'indexes' => $indexes,
        'trigger_count' => (int) ($triggerRows[0]['c'] ?? -1),
        'pair_count' => count(pairRows($pdo, $key)),
        'prefix_count' => prefixCount($pdo),
    ];
}

function ready(array $meta): bool
{
    if (strpos($meta['version'], '5.6.') === false || $meta['pair_count'] !== 0
        || $meta['prefix_count'] !== 0 || $meta['trigger_count'] !== 0) {
        return false;
    }
    $map = [];
    foreach ($meta['columns'] as $row) {
        $map[strtolower((string) $row['COLUMN_NAME'])] = $row;
    }
    if (count($map) !== 5
        || !isset($map['id'], $map['station_uuid'], $map['nutrition_uuid'],
            $map['current_num'], $map['all_num'])
        || strpos(strtolower((string) $map['id']['EXTRA']), 'auto_increment') === false
        || strtoupper((string) $map['current_num']['IS_NULLABLE']) !== 'YES'
        || strtoupper((string) $map['all_num']['IS_NULLABLE']) !== 'YES'
        || $map['current_num']['COLUMN_DEFAULT'] !== null
        || $map['all_num']['COLUMN_DEFAULT'] !== null) {
        return false;
    }
    $indexGroups = [];
    foreach ($meta['indexes'] as $row) {
        $name = (string) $row['INDEX_NAME'];
        $indexGroups[$name]['non_unique'] = (int) $row['NON_UNIQUE'];
        $indexGroups[$name]['columns'][] = strtolower((string) $row['COLUMN_NAME']);
    }
    foreach ($indexGroups as $index) {
        if ($index['non_unique'] === 0
            && $index['columns'] === ['station_uuid', 'nutrition_uuid']) {
            return true;
        }
    }
    return false;
}

function normalized(array $row): array
{
    return [
        'id' => (int) $row['id'],
        'station_uuid' => (string) $row['station_uuid'],
        'nutrition_uuid' => (string) $row['nutrition_uuid'],
        'current_num' => $row['current_num'] === null ? null : (int) $row['current_num'],
        'all_num' => $row['all_num'] === null ? null : (string) $row['all_num'],
    ];
}

$result = ['group' => $group, 'mode' => $mode, 'pair' => $key, 'events' => []];
$connection = null;
$allowedStates = [];
$mayHaveOurRow = false;
try {
    $connection = db($config);
    $meta = inspect($connection, $config, $key);
    $result['preflight'] = $meta;
    $result['preflight_ready'] = ready($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('Approval/metadata/pair/prefix gate failed');
    }
    $mayHaveOurRow = true;
    $statement = $connection->prepare('INSERT INTO ' . TABLE_NAME
        . ' (station_uuid,nutrition_uuid,current_num,all_num) VALUES (?,?,?,?)');
    $statement->execute([$key['station'], $key['nutrition'], 7, $key['marker'] . ':1,2']);
    $insertId = (int) $connection->lastInsertId();
    if ($insertId > 0) {
        $allowedStates[] = ['id' => $insertId, 'station_uuid' => $key['station'],
            'nutrition_uuid' => $key['nutrition'], 'current_num' => 7,
            'all_num' => $key['marker'] . ':1,2'];
    }
    $after = pairRows(db($config), $key);
    if (count($after) !== 1) {
        throw new RuntimeException('INSERT readback count mismatch');
    }
    $inserted = normalized($after[0]);
    $allowedStates[] = $inserted;
    $result['events'][] = ['step' => 'insert', 'affected' => $statement->rowCount(),
        'row' => $inserted];
    if ($inserted['current_num'] !== 7 || $inserted['all_num'] !== $key['marker'] . ':1,2') {
        throw new RuntimeException('INSERT value mismatch');
    }

    $statement = $connection->prepare('REPLACE INTO ' . TABLE_NAME
        . ' (station_uuid,nutrition_uuid,current_num,all_num) VALUES (?,?,?,?)');
    $statement->execute([$key['station'], $key['nutrition'], 9, $key['marker'] . ':3,4']);
    $replaceId = (int) $connection->lastInsertId();
    if ($replaceId > 0) {
        $allowedStates[] = ['id' => $replaceId, 'station_uuid' => $key['station'],
            'nutrition_uuid' => $key['nutrition'], 'current_num' => 9,
            'all_num' => $key['marker'] . ':3,4'];
    }
    $after = pairRows(db($config), $key);
    if (count($after) !== 1) {
        throw new RuntimeException('REPLACE full readback count mismatch');
    }
    $replaced = normalized($after[0]);
    $allowedStates[] = $replaced;
    $result['events'][] = ['step' => 'replace_full',
        'affected' => $statement->rowCount(), 'row' => $replaced,
        'id_changed' => $replaced['id'] !== $inserted['id']];
    if ($replaced['current_num'] !== 9 || $replaced['all_num'] !== $key['marker'] . ':3,4') {
        throw new RuntimeException('REPLACE full value mismatch');
    }

    $statement = $connection->prepare('REPLACE INTO ' . TABLE_NAME
        . ' (station_uuid,nutrition_uuid) VALUES (?,?)');
    $statement->execute([$key['station'], $key['nutrition']]);
    $defaultId = (int) $connection->lastInsertId();
    if ($defaultId > 0) {
        $allowedStates[] = ['id' => $defaultId, 'station_uuid' => $key['station'],
            'nutrition_uuid' => $key['nutrition'], 'current_num' => null,
            'all_num' => null];
    }
    $after = pairRows(db($config), $key);
    if (count($after) !== 1) {
        throw new RuntimeException('REPLACE defaults readback count mismatch');
    }
    $defaults = normalized($after[0]);
    $allowedStates[] = $defaults;
    $result['events'][] = ['step' => 'replace_defaults',
        'affected' => $statement->rowCount(), 'row' => $defaults,
        'id_changed' => $defaults['id'] !== $replaced['id']];
    if ($defaults['current_num'] !== null || $defaults['all_num'] !== null) {
        throw new RuntimeException('REPLACE defaults mismatch');
    }

    $connection->beginTransaction();
    try {
        $statement = $connection->prepare('REPLACE INTO ' . TABLE_NAME
            . ' (station_uuid,nutrition_uuid,current_num,all_num) VALUES (?,?,?,?)');
        $statement->execute([$key['station'], $key['nutrition'], 11,
            $key['marker'] . ':rollback']);
        $rollbackId = (int) $connection->lastInsertId();
        if ($rollbackId > 0) {
            $allowedStates[] = ['id' => $rollbackId,
                'station_uuid' => $key['station'],
                'nutrition_uuid' => $key['nutrition'], 'current_num' => 11,
                'all_num' => $key['marker'] . ':rollback'];
        }
        $inside = pairRows($connection, $key);
        $result['events'][] = ['step' => 'replace_inside_transaction',
            'affected' => $statement->rowCount(), 'rows' => array_map('normalized', $inside)];
    } finally {
        if ($connection->inTransaction()) {
            $connection->rollBack();
        }
    }
    $after = pairRows(db($config), $key);
    $rollbackRestored = count($after) === 1 && normalized($after[0]) === $defaults;
    $result['events'][] = ['step' => 'rollback', 'restored' => $rollbackRestored,
        'rows' => array_map('normalized', $after)];
    if (!$rollbackRestored) {
        throw new RuntimeException('ROLLBACK did not restore exact 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 {
            // A dropped original connection (e.g. MySQL 2006) must not skip cleanup.
            $cleanupDb = db($config);
            $current = pairRows($cleanupDb, $key);
            $safe = count($current) <= 1;
            foreach ($current as $row) {
                $candidate = normalized($row);
                $marked = is_string($candidate['all_num'])
                    && strpos($candidate['all_num'], $key['marker'] . ':') === 0;
                if (!in_array($candidate, $allowedStates, true) && !$marked) {
                    $safe = false;
                }
            }
            if (!$safe) {
                throw new RuntimeException('Exact pair/state cleanup gate failed');
            }
            if ($current) {
                $delete = $cleanupDb->prepare('DELETE FROM ' . TABLE_NAME
                    . ' WHERE id=? AND station_uuid=? AND nutrition_uuid=?');
                $delete->execute([(int) $current[0]['id'],
                    $key['station'], $key['nutrition']]);
                $result['cleanup_deleted'] = $delete->rowCount();
            } else {
                $result['cleanup_deleted'] = 0;
            }
            $result['cleanup_pair_count'] = count(pairRows(db($config), $key));
            $result['cleanup_prefix_count'] = prefixCount(db($config));
        } catch (Throwable $error) {
            $result['cleanup_error'] = ['error_class' => get_class($error),
                'error_code' => $error->getCode(),
                'vendor_code' => $error instanceof PDOException
                    ? ($error->errorInfo[1] ?? null) : null];
        }
    }
}
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_prefix_count'] ?? -1) !== 0))) {
    exit(1);
}