<?php

declare(strict_types=1);

// Development MySQL 5.6 only; synthetic staff UUIDs, no staff-table lookups.
const WEIGHT_TABLE = 'ydy_member_weight';
const WEIGHT_YMD = '2099-12-31';
const WEIGHT_CREATED_AT = '2099-12-31 00:00:00'; // Convention only: DATETIME has no timezone.
const WEIGHT_MARKER = 'CDXREPLW';
const WEIGHT_KEYS = [
    'A' => '38AD550D-E14B-4303-8CB4-9526C8884011',
    'B' => '304BD2DC-6D05-4E40-8B75-0CA9A8398A73',
];

$group = (string) getenv('CDX_WEIGHT_GROUP');
$mode = (string) getenv('CDX_WEIGHT_MODE');
if (!isset(WEIGHT_KEYS[$group]) || !in_array($mode, ['preflight', 'run'], true)) {
    fwrite(STDERR, "Explicit A/B and preflight/run required\n");
    exit(2);
}
$staffUuid = WEIGHT_KEYS[$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 weightDb(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 weightRows(PDO $pdo, string $sql, array $bind = []): array
{
    $statement = $pdo->prepare($sql);
    $statement->execute($bind);
    return $statement->fetchAll();
}

function weightPair(PDO $pdo, string $staffUuid): array
{
    return weightRows($pdo, 'SELECT id,staff_uuid,weight,pbf,create_time,ymd FROM '
        . WEIGHT_TABLE . ' WHERE staff_uuid=? AND ymd=? ORDER BY id',
        [$staffUuid, WEIGHT_YMD]);
}

function weightMarkerCount(PDO $pdo): int
{
    $rows = weightRows($pdo, "SELECT COUNT(*) AS c FROM " . WEIGHT_TABLE
        . " WHERE pbf LIKE 'CDXREPLW%'");
    return (int) ($rows[0]['c'] ?? -1);
}

function weightInspect(PDO $pdo, array $config, string $staffUuid): array
{
    $version = (string) $pdo->query('SELECT VERSION()')->fetchColumn();
    $columns = weightRows($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'], WEIGHT_TABLE]);
    $indexes = weightRows($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'], WEIGHT_TABLE]);
    $fkRows = weightRows($pdo,
        'SELECT COUNT(*) AS c FROM information_schema.KEY_COLUMN_USAGE'
        . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND REFERENCED_TABLE_NAME IS NOT NULL',
        [$config['database'], WEIGHT_TABLE]);
    $triggerRows = weightRows($pdo,
        'SELECT COUNT(*) AS c FROM information_schema.TRIGGERS'
        . ' WHERE TRIGGER_SCHEMA=? AND EVENT_OBJECT_TABLE=?',
        [$config['database'], WEIGHT_TABLE]);
    return [
        'version' => $version,
        'columns' => $columns,
        'indexes' => $indexes,
        'fk_count' => (int) ($fkRows[0]['c'] ?? -1),
        'trigger_count' => (int) ($triggerRows[0]['c'] ?? -1),
        'pair_count' => count(weightPair($pdo, $staffUuid)),
        'marker_count' => weightMarkerCount($pdo),
    ];
}

function weightReady(array $meta): bool
{
    if (strpos($meta['version'], '5.6.') === false
        || $meta['fk_count'] !== 0 || $meta['trigger_count'] !== 0
        || $meta['pair_count'] !== 0 || $meta['marker_count'] !== 0) {
        return false;
    }
    $map = [];
    foreach ($meta['columns'] as $row) {
        $map[strtolower((string) $row['COLUMN_NAME'])] = $row;
    }
    if (count($map) !== 6
        || !isset($map['id'], $map['staff_uuid'], $map['weight'],
            $map['pbf'], $map['create_time'], $map['ymd'])
        || strpos(strtolower((string) $map['id']['EXTRA']), 'auto_increment') === false
        || strtoupper((string) $map['pbf']['IS_NULLABLE']) !== 'YES'
        || (string) $map['pbf']['COLUMN_DEFAULT'] !== ''
        || (float) $map['weight']['COLUMN_DEFAULT'] !== 0.0) {
        return false;
    }
    $groups = [];
    foreach ($meta['indexes'] as $row) {
        $name = (string) $row['INDEX_NAME'];
        $groups[$name]['non_unique'] = (int) $row['NON_UNIQUE'];
        $groups[$name]['columns'][] = strtolower((string) $row['COLUMN_NAME']);
    }
    foreach ($groups as $group) {
        if ($group['non_unique'] === 0
            && $group['columns'] === ['staff_uuid', 'ymd']) {
            return true;
        }
    }
    return false;
}

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

function weightSame(array $row, array $expected): bool
{
    $actual = weightFormat($row);
    return $actual['id'] === $expected['id']
        && $actual['staff_uuid'] === $expected['staff_uuid']
        && $actual['ymd'] === $expected['ymd']
        && $actual['create_time'] === $expected['create_time']
        && $actual['pbf'] === $expected['pbf']
        && abs((float) $row['weight'] - $expected['weight']) < 0.005;
}

$result = ['group' => $group, 'mode' => $mode, 'staff_uuid' => $staffUuid,
    'ymd' => WEIGHT_YMD, 'marker' => WEIGHT_MARKER, 'events' => []];
$pdo = null;
$mayHaveOurRow = false;
$allowed = [];
try {
    $pdo = weightDb($config);
    $meta = weightInspect($pdo, $config, $staffUuid);
    $result['preflight'] = $meta;
    $result['preflight_ready'] = weightReady($meta);
    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;
    $insert = $pdo->prepare('INSERT INTO ' . WEIGHT_TABLE
        . ' (staff_uuid,weight,pbf,create_time,ymd) VALUES (?,?,?,?,?)');
    $insert->execute([$staffUuid, '70.55', WEIGHT_MARKER,
        WEIGHT_CREATED_AT, WEIGHT_YMD]);
    $id = (int) $pdo->lastInsertId();
    if ($id > 0) {
        $allowed[] = ['id' => $id, 'staff_uuid' => $staffUuid,
            'ymd' => WEIGHT_YMD, 'create_time' => WEIGHT_CREATED_AT,
            'pbf' => WEIGHT_MARKER, 'weight' => 70.55];
    }
    $first = weightPair(weightDb($config), $staffUuid);
    if (count($first) !== 1) {
        throw new RuntimeException('INSERT independent readback count mismatch');
    }
    $original = weightFormat($first[0]);
    $allowed[] = ['id' => $original['id'], 'staff_uuid' => $staffUuid,
        'ymd' => WEIGHT_YMD, 'create_time' => WEIGHT_CREATED_AT,
        'pbf' => WEIGHT_MARKER, 'weight' => 70.55];
    $result['events'][] = ['step' => 'insert', 'affected' => $insert->rowCount(),
        'row' => $original];
    if (!weightSame($first[0], end($allowed))) {
        throw new RuntimeException('INSERT value mismatch');
    }

    $full = $pdo->prepare('REPLACE INTO ' . WEIGHT_TABLE
        . ' (staff_uuid,weight,pbf,create_time,ymd) VALUES (?,?,?,?,?)');
    $full->execute([$staffUuid, '71.25', WEIGHT_MARKER,
        WEIGHT_CREATED_AT, WEIGHT_YMD]);
    $id = (int) $pdo->lastInsertId();
    if ($id > 0) {
        $allowed[] = ['id' => $id, 'staff_uuid' => $staffUuid,
            'ymd' => WEIGHT_YMD, 'create_time' => WEIGHT_CREATED_AT,
            'pbf' => WEIGHT_MARKER, 'weight' => 71.25];
    }
    $after = weightPair(weightDb($config), $staffUuid);
    if (count($after) !== 1) {
        throw new RuntimeException('REPLACE independent readback count mismatch');
    }
    $replaced = weightFormat($after[0]);
    $allowed[] = ['id' => $replaced['id'], 'staff_uuid' => $staffUuid,
        'ymd' => WEIGHT_YMD, 'create_time' => WEIGHT_CREATED_AT,
        'pbf' => WEIGHT_MARKER, 'weight' => 71.25];
    $result['events'][] = ['step' => 'replace_full', 'affected' => $full->rowCount(),
        'row' => $replaced, 'id_changed' => $replaced['id'] !== $original['id']];
    if (!weightSame($after[0], end($allowed))) {
        throw new RuntimeException('REPLACE value mismatch');
    }

    $defaultsStmt = $pdo->prepare('REPLACE INTO ' . WEIGHT_TABLE
        . ' (staff_uuid,create_time,ymd) VALUES (?,?,?)');
    $defaultsStmt->execute([$staffUuid, WEIGHT_CREATED_AT, WEIGHT_YMD]);
    $id = (int) $pdo->lastInsertId();
    if ($id > 0) {
        $allowed[] = ['id' => $id, 'staff_uuid' => $staffUuid,
            'ymd' => WEIGHT_YMD, 'create_time' => WEIGHT_CREATED_AT,
            'pbf' => '', 'weight' => 0.0];
    }
    $after = weightPair(weightDb($config), $staffUuid);
    if (count($after) !== 1) {
        throw new RuntimeException('REPLACE defaults readback count mismatch');
    }
    $defaults = weightFormat($after[0]);
    $allowed[] = ['id' => $defaults['id'], 'staff_uuid' => $staffUuid,
        'ymd' => WEIGHT_YMD, 'create_time' => WEIGHT_CREATED_AT,
        'pbf' => '', 'weight' => 0.0];
    $result['events'][] = ['step' => 'replace_defaults',
        'affected' => $defaultsStmt->rowCount(), 'row' => $defaults,
        'id_changed' => $defaults['id'] !== $replaced['id']];
    if (!weightSame($after[0], end($allowed))) {
        throw new RuntimeException('REPLACE default value mismatch');
    }

    $pdo->beginTransaction();
    try {
        $rollbackStmt = $pdo->prepare('REPLACE INTO ' . WEIGHT_TABLE
            . ' (staff_uuid,weight,pbf,create_time,ymd) VALUES (?,?,?,?,?)');
        $rollbackStmt->execute([$staffUuid, '71.25', WEIGHT_MARKER,
            WEIGHT_CREATED_AT, WEIGHT_YMD]);
        $id = (int) $pdo->lastInsertId();
        if ($id > 0) {
            $allowed[] = ['id' => $id, 'staff_uuid' => $staffUuid,
                'ymd' => WEIGHT_YMD, 'create_time' => WEIGHT_CREATED_AT,
                'pbf' => WEIGHT_MARKER, 'weight' => 71.25];
        }
        $inside = weightPair($pdo, $staffUuid);
        $result['events'][] = ['step' => 'replace_inside_transaction',
            'affected' => $rollbackStmt->rowCount(),
            'rows' => array_map('weightFormat', $inside)];
    } finally {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
    }
    $after = weightPair(weightDb($config), $staffUuid);
    $restored = count($after) === 1 && weightSame($after[0],
        ['id' => $defaults['id'], 'staff_uuid' => $staffUuid,
            'ymd' => WEIGHT_YMD, 'create_time' => WEIGHT_CREATED_AT,
            'pbf' => '', 'weight' => 0.0]);
    $result['events'][] = ['step' => 'rollback', 'restored' => $restored,
        'rows' => array_map('weightFormat', $after)];
    if (!$restored) {
        throw new RuntimeException('ROLLBACK did not restore 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 = weightDb($config);
            $current = weightPair($cleanup, $staffUuid);
            $safe = count($current) <= 1;
            foreach ($current as $row) {
                $matched = false;
                foreach ($allowed as $expected) {
                    if (weightSame($row, $expected)) {
                        $matched = true;
                        break;
                    }
                }
                if (!$matched) {
                    $safe = false;
                }
            }
            if (!$safe) {
                throw new RuntimeException('Exact ID/pair/state cleanup gate failed');
            }
            if ($current) {
                $delete = $cleanup->prepare('DELETE FROM ' . WEIGHT_TABLE
                    . ' WHERE id=? AND staff_uuid=? AND ymd=?');
                $delete->execute([(int) $current[0]['id'], $staffUuid, WEIGHT_YMD]);
                $result['cleanup_deleted'] = $delete->rowCount();
            } else {
                $result['cleanup_deleted'] = 0;
            }
            $result['cleanup_pair_count'] = count(weightPair(weightDb($config), $staffUuid));
            $result['cleanup_marker_count'] = weightMarkerCount(weightDb($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_marker_count'] ?? -1) !== 0))) {
    exit(1);
}