<?php

function call_rtp_ensure_ledger_table(mysqli $db): bool
{
    static $ensured = null;

    if ($ensured === true) {
        return true;
    }

    $sql = "CREATE TABLE IF NOT EXISTS tb_call_rtp_ledger (
        id int(11) NOT NULL AUTO_INCREMENT,
        provider_call_id varchar(96) DEFAULT NULL,
        local_ref varchar(96) NOT NULL,
        intUser varchar(255) NOT NULL,
        extUser varchar(255) NOT NULL DEFAULT '',
        agent_name varchar(255) NOT NULL,
        agent_code varchar(255) NOT NULL DEFAULT '',
        provider_code varchar(100) NOT NULL,
        game_code varchar(255) NOT NULL,
        call_rtp int(11) NOT NULL DEFAULT 0,
        call_type int(11) NOT NULL DEFAULT 0,
        bet_amount double NOT NULL DEFAULT 0,
        called_money double NOT NULL DEFAULT 0,
        user_credit_amount double NOT NULL DEFAULT 0,
        source varchar(20) NOT NULL DEFAULT 'web',
        provider_status tinyint(4) DEFAULT NULL,
        refund_status enum('applied','refunded') NOT NULL DEFAULT 'applied',
        refund_message text DEFAULT NULL,
        created_at datetime NOT NULL,
        refunded_at datetime DEFAULT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY uniq_call_rtp_local_ref (local_ref),
        UNIQUE KEY uniq_call_rtp_provider_call_id (provider_call_id),
        KEY idx_call_rtp_match (intUser, provider_code, game_code, refund_status),
        KEY idx_call_rtp_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    $ensured = (bool)mysqli_query($db, $sql);
    return $ensured;
}

function call_rtp_sql_string(mysqli $db, $value): string
{
    return "'" . mysqli_real_escape_string($db, (string)$value) . "'";
}

function call_rtp_sql_nullable_string(mysqli $db, $value): string
{
    $value = trim((string)($value ?? ''));
    return $value === '' ? 'NULL' : call_rtp_sql_string($db, $value);
}

function call_rtp_sql_number($value): string
{
    $number = (float)$value;
    $formatted = rtrim(rtrim(sprintf('%.8F', $number), '0'), '.');
    return $formatted === '' || $formatted === '-0' ? '0' : $formatted;
}

function call_rtp_pick_numeric(array $row, array $keys, float $default = 0.0): float
{
    foreach ($keys as $key) {
        if (isset($row[$key]) && is_numeric($row[$key])) {
            return (float)$row[$key];
        }
    }

    return $default;
}

function call_rtp_pick_string(array $row, array $keys, string $default = ''): string
{
    foreach ($keys as $key) {
        if (isset($row[$key]) && trim((string)$row[$key]) !== '') {
            return trim((string)$row[$key]);
        }
    }

    return $default;
}

function call_rtp_provider_call_id(array $row): string
{
    $rawId = call_rtp_pick_string($row, ['call_id', 'callId', 'id', 'transaction_id', 'trxid']);

    if ($rawId !== '') {
        $cleanId = preg_replace('/[^A-Za-z0-9:_\-.]/', '', $rawId);
        return 'NEXUSGGR:' . substr($cleanId ?: md5($rawId), 0, 80);
    }

    $hashSource = implode('|', [
        $row['user_code'] ?? '',
        $row['provider_code'] ?? '',
        $row['game_code'] ?? '',
        $row['expect'] ?? '',
        $row['created_at'] ?? '',
        $row['status'] ?? '',
    ]);

    return 'NEXUSGGR:HASH:' . md5($hashSource);
}

function call_rtp_response_call_id(array $response): ?string
{
    foreach (['call_id', 'callId', 'id'] as $key) {
        if (isset($response[$key]) && trim((string)$response[$key]) !== '') {
            return call_rtp_provider_call_id([$key => $response[$key]]);
        }
    }

    foreach (['data', 'call'] as $containerKey) {
        if (isset($response[$containerKey]) && is_array($response[$containerKey])) {
            $callId = call_rtp_response_call_id($response[$containerKey]);
            if ($callId !== null) {
                return $callId;
            }
        }
    }

    return null;
}

function call_rtp_make_local_ref(string $prefix = 'call'): string
{
    try {
        $random = bin2hex(random_bytes(8));
    } catch (Throwable $exception) {
        $random = str_replace('.', '', uniqid('', true));
    }

    return $prefix . '_' . date('YmdHis') . '_' . $random;
}

function call_rtp_insert_ledger(mysqli $db, array $data): int
{
    if (!call_rtp_ensure_ledger_table($db)) {
        return 0;
    }

    $providerCallId = $data['provider_call_id'] ?? null;
    if ($providerCallId === null && isset($data['provider_response']) && is_array($data['provider_response'])) {
        $providerCallId = call_rtp_response_call_id($data['provider_response']);
    }

    $localRef = trim((string)($data['local_ref'] ?? ''));
    if ($localRef === '') {
        $localRef = call_rtp_make_local_ref((string)($data['source'] ?? 'call'));
    }

    $createdAt = trim((string)($data['created_at'] ?? ''));
    if ($createdAt === '') {
        $createdAt = date('Y-m-d H:i:s');
    }

    $sql = "INSERT INTO tb_call_rtp_ledger (
        provider_call_id, local_ref, intUser, extUser, agent_name, agent_code,
        provider_code, game_code, call_rtp, call_type, bet_amount, called_money,
        user_credit_amount, source, provider_status, refund_status, created_at
    ) VALUES (
        " . call_rtp_sql_nullable_string($db, $providerCallId) . ",
        " . call_rtp_sql_string($db, $localRef) . ",
        " . call_rtp_sql_string($db, $data['intUser'] ?? '') . ",
        " . call_rtp_sql_string($db, $data['extUser'] ?? '') . ",
        " . call_rtp_sql_string($db, $data['agent_name'] ?? '') . ",
        " . call_rtp_sql_string($db, $data['agent_code'] ?? '') . ",
        " . call_rtp_sql_string($db, $data['provider_code'] ?? '') . ",
        " . call_rtp_sql_string($db, $data['game_code'] ?? '') . ",
        " . (int)($data['call_rtp'] ?? 0) . ",
        " . (int)($data['call_type'] ?? 0) . ",
        " . call_rtp_sql_number($data['bet_amount'] ?? 0) . ",
        " . call_rtp_sql_number($data['called_money'] ?? 0) . ",
        " . call_rtp_sql_number($data['user_credit_amount'] ?? 0) . ",
        " . call_rtp_sql_string($db, $data['source'] ?? 'web') . ",
        NULL,
        'applied',
        " . call_rtp_sql_string($db, $createdAt) . "
    )";

    if (!mysqli_query($db, $sql)) {
        return 0;
    }

    return (int)mysqli_insert_id($db);
}

function call_rtp_record_apply(mysqli $db, array $data): bool
{
    return call_rtp_insert_ledger($db, $data) > 0;
}

function call_rtp_find_rejected_ledger(mysqli $db, string $providerCallId): ?array
{
    $providerCallIdSql = call_rtp_sql_string($db, $providerCallId);
    $query = mysqli_query(
        $db,
        "SELECT * FROM tb_call_rtp_ledger
         WHERE provider_call_id = $providerCallIdSql
         LIMIT 1
         FOR UPDATE"
    );

    if (!$query || mysqli_num_rows($query) === 0) {
        return null;
    }

    return mysqli_fetch_assoc($query) ?: null;
}

function call_rtp_find_matching_ledger(mysqli $db, string $intUser, string $provider, string $game, float $amount): ?array
{
    $intUserSql = call_rtp_sql_string($db, $intUser);
    $providerSql = call_rtp_sql_string($db, $provider);
    $gameSql = call_rtp_sql_string($db, $game);
    $amountSql = call_rtp_sql_number($amount);

    $query = mysqli_query(
        $db,
        "SELECT * FROM tb_call_rtp_ledger
         WHERE refund_status = 'applied'
           AND provider_call_id IS NULL
           AND intUser = $intUserSql
           AND provider_code = $providerSql
           AND game_code = $gameSql
           AND ABS(called_money - $amountSql) <= 0.01
         ORDER BY id DESC
         LIMIT 1
         FOR UPDATE"
    );

    if ($query && mysqli_num_rows($query) > 0) {
        return mysqli_fetch_assoc($query) ?: null;
    }

    $query = mysqli_query(
        $db,
        "SELECT * FROM tb_call_rtp_ledger
         WHERE refund_status = 'applied'
           AND provider_call_id IS NULL
           AND intUser = $intUserSql
           AND provider_code = $providerSql
           AND game_code = $gameSql
         ORDER BY id DESC
         LIMIT 1
         FOR UPDATE"
    );

    if (!$query || mysqli_num_rows($query) === 0) {
        return null;
    }

    return mysqli_fetch_assoc($query) ?: null;
}

function call_rtp_load_user_agent_for_update(mysqli $db, string $intUser): ?array
{
    $intUserSql = call_rtp_sql_string($db, $intUser);
    $userQuery = mysqli_query(
        $db,
        "SELECT id, intUser, extUser, balance, agent_code
         FROM tb_user
         WHERE intUser = $intUserSql
         LIMIT 1
         FOR UPDATE"
    );

    if (!$userQuery || mysqli_num_rows($userQuery) === 0) {
        return null;
    }

    $user = mysqli_fetch_assoc($userQuery);
    $agentRefSql = call_rtp_sql_string($db, $user['agent_code'] ?? '');
    $agentQuery = mysqli_query(
        $db,
        "SELECT id, agent_name, agent_code, agent_balance
         FROM tb_agent
         WHERE agent_name = $agentRefSql OR agent_code = $agentRefSql
         LIMIT 1
         FOR UPDATE"
    );

    if (!$agentQuery || mysqli_num_rows($agentQuery) === 0) {
        return null;
    }

    $agent = mysqli_fetch_assoc($agentQuery);
    return ['user' => $user, 'agent' => $agent];
}

function call_rtp_refund_rejected_history(mysqli $db, array $historyRows, array $allowedUsers = []): array
{
    $summary = [
        'checked' => 0,
        'refunded' => 0,
        'skipped' => 0,
        'errors' => [],
        'agent_balances' => [],
    ];

    if (!call_rtp_ensure_ledger_table($db)) {
        $summary['errors'][] = 'CALL_RTP_LEDGER_TABLE_FAILED';
        return $summary;
    }

    foreach ($historyRows as $history) {
        if (!is_array($history) || (int)($history['status'] ?? -1) !== 3) {
            continue;
        }

        $summary['checked']++;

        $intUser = trim((string)($history['user_code'] ?? ''));
        if ($intUser === '' || (!empty($allowedUsers) && !isset($allowedUsers[$intUser]))) {
            $summary['skipped']++;
            continue;
        }

        $provider = trim((string)($history['provider_code'] ?? ''));
        $game = trim((string)($history['game_code'] ?? ''));
        $amount = call_rtp_pick_numeric($history, ['expect', 'called_money', 'amount', 'payout'], 0.0);

        if ($provider === '' || $game === '' || $amount <= 0) {
            $summary['skipped']++;
            continue;
        }

        $providerCallId = call_rtp_provider_call_id($history);

        mysqli_begin_transaction($db);

        try {
            $ledger = call_rtp_find_rejected_ledger($db, $providerCallId);
            if ($ledger !== null && ($ledger['refund_status'] ?? '') === 'refunded') {
                mysqli_commit($db);
                $summary['skipped']++;
                continue;
            }

            if ($ledger === null) {
                $ledger = call_rtp_find_matching_ledger($db, $intUser, $provider, $game, $amount);
            }

            $pair = call_rtp_load_user_agent_for_update($db, $intUser);
            if ($pair === null) {
                throw new RuntimeException('USER_OR_AGENT_NOT_FOUND');
            }

            $user = $pair['user'];
            $agent = $pair['agent'];
            $refundAmount = $ledger !== null && (float)($ledger['called_money'] ?? 0) > 0
                ? (float)$ledger['called_money']
                : $amount;
            $userCreditAmount = $ledger !== null ? (float)($ledger['user_credit_amount'] ?? 0) : 0.0;

            if ($refundAmount <= 0) {
                throw new RuntimeException('INVALID_REFUND_AMOUNT');
            }

            if ($ledger === null) {
                $ledgerId = call_rtp_insert_ledger($db, [
                    'provider_call_id' => $providerCallId,
                    'local_ref' => 'history_' . md5($providerCallId),
                    'intUser' => $intUser,
                    'extUser' => $user['extUser'] ?? ($allowedUsers[$intUser] ?? ''),
                    'agent_name' => $agent['agent_name'] ?? '',
                    'agent_code' => $agent['agent_code'] ?? '',
                    'provider_code' => $provider,
                    'game_code' => $game,
                    'call_rtp' => 0,
                    'call_type' => 0,
                    'bet_amount' => $history['bet'] ?? 0,
                    'called_money' => $refundAmount,
                    'user_credit_amount' => 0,
                    'source' => 'history',
                    'created_at' => $history['created_at'] ?? date('Y-m-d H:i:s'),
                ]);

                if ($ledgerId <= 0) {
                    throw new RuntimeException('LEDGER_INSERT_FAILED');
                }
            } else {
                $ledgerId = (int)$ledger['id'];
            }

            $agentId = (int)($agent['id'] ?? 0);
            $agentBalanceAfter = (float)($agent['agent_balance'] ?? 0) + $refundAmount;
            $refundSql = call_rtp_sql_number($refundAmount);
            $agentUpdate = mysqli_query(
                $db,
                "UPDATE tb_agent
                 SET agent_balance = agent_balance + $refundSql
                 WHERE id = $agentId"
            );

            if (!$agentUpdate || mysqli_affected_rows($db) !== 1) {
                throw new RuntimeException('AGENT_REFUND_FAILED');
            }

            $userDeducted = 0.0;
            if ($userCreditAmount > 0) {
                $userDeducted = min((float)($user['balance'] ?? 0), $userCreditAmount);
                if ($userDeducted > 0) {
                    $deductSql = call_rtp_sql_number($userDeducted);
                    $userId = (int)($user['id'] ?? 0);
                    $userUpdate = mysqli_query(
                        $db,
                        "UPDATE tb_user
                         SET balance = balance - $deductSql
                         WHERE id = $userId"
                    );

                    if (!$userUpdate || mysqli_affected_rows($db) !== 1) {
                        throw new RuntimeException('USER_CREDIT_REVERSAL_FAILED');
                    }
                }
            }

            $message = 'Rejected by game server';
            if ($userCreditAmount > 0 && $userDeducted < $userCreditAmount) {
                $message .= '; partial user balance reversal';
            }

            $ledgerIdSql = (int)$ledgerId;
            $ledgerUpdate = mysqli_query(
                $db,
                "UPDATE tb_call_rtp_ledger
                 SET provider_call_id = " . call_rtp_sql_string($db, $providerCallId) . ",
                     provider_status = 3,
                     refund_status = 'refunded',
                     refund_message = " . call_rtp_sql_string($db, $message) . ",
                     refunded_at = NOW()
                 WHERE id = $ledgerIdSql
                   AND refund_status = 'applied'"
            );

            if (!$ledgerUpdate || mysqli_affected_rows($db) !== 1) {
                throw new RuntimeException('LEDGER_REFUND_MARK_FAILED');
            }

            mysqli_commit($db);

            $summary['refunded']++;
            $summary['agent_balances'][(string)($agent['agent_name'] ?? '')] = $agentBalanceAfter;
        } catch (Throwable $exception) {
            mysqli_rollback($db);
            $summary['errors'][] = $providerCallId . ': ' . $exception->getMessage();
        }
    }

    return $summary;
}

