<?php
declare(strict_types=1);

const SQLSAVE_DB_HOST = 'localhost';
const SQLSAVE_DB_PORT = 3306;
const SQLSAVE_DB_NAME = 'pixelmonster_ingame';
const SQLSAVE_DB_USER = 'root';
const SQLSAVE_DB_PASSWORD = '2024!PixelMon$ter';
const SQLSAVE_DEFAULT_TABLE = 'sqlsave_documents';
const SQLSAVE_DEFAULT_CHARSET = 'utf8mb4';
const SQLSAVE_MAX_SELECT_ROWS = 500;

function sqlsave_now_utc(): string
{
    return gmdate('Y-m-d H:i:s');
}

function sqlsave_json_response(int $statusCode, array $payload): void
{
    http_response_code($statusCode);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

function sqlsave_ok(string $action, array $data = [], int $statusCode = 200): void
{
    sqlsave_json_response($statusCode, [
        'ok' => true,
        'action' => $action,
        'server_time_utc' => sqlsave_now_utc(),
        'data' => $data,
    ]);
}

function sqlsave_fail(string $action, string $code, string $message, int $statusCode = 400, array $data = []): void
{
    sqlsave_json_response($statusCode, [
        'ok' => false,
        'action' => $action,
        'server_time_utc' => sqlsave_now_utc(),
        'error_code' => $code,
        'error_message' => $message,
        'data' => $data,
    ]);
}

function sqlsave_read_request(): array
{
    $contentType = strtolower(trim((string)($_SERVER['CONTENT_TYPE'] ?? '')));
    $raw = file_get_contents('php://input');

    if ($raw !== false && $raw !== '' && str_contains($contentType, 'application/json')) {
        $decoded = json_decode($raw, true);
        if (!is_array($decoded)) {
            sqlsave_fail('unknown', 'invalid_json', 'Request body is not valid JSON.', 400);
        }
        return $decoded;
    }

    if (!empty($_POST)) {
        return $_POST;
    }

    if ($raw !== false && $raw !== '') {
        parse_str($raw, $parsed);
        if (is_array($parsed) && !empty($parsed)) {
            return $parsed;
        }
    }

    return [];
}

function sqlsave_require_string(array $request, string $key): string
{
    if (!array_key_exists($key, $request)) {
        throw new InvalidArgumentException("Missing required field: {$key}");
    }

    $value = trim((string)$request[$key]);
    if ($value === '') {
        throw new InvalidArgumentException("Field '{$key}' cannot be empty.");
    }

    return $value;
}

function sqlsave_optional_string(array $request, string $key, string $default = ''): string
{
    if (!array_key_exists($key, $request)) {
        return $default;
    }

    return trim((string)$request[$key]);
}

function sqlsave_optional_int(array $request, string $key, int $default = 0): int
{
    if (!array_key_exists($key, $request) || $request[$key] === '') {
        return $default;
    }

    return (int)$request[$key];
}

function sqlsave_optional_array(array $request, string $key, array $default = []): array
{
    if (!array_key_exists($key, $request)) {
        return $default;
    }

    $value = $request[$key];
    if (is_array($value)) {
        return $value;
    }

    if (is_string($value) && $value !== '') {
        $decoded = json_decode($value, true);
        if (is_array($decoded)) {
            return $decoded;
        }
    }

    return $default;
}

function sqlsave_normalize_identifier(string $identifier): string
{
    if (!preg_match('/^[A-Za-z0-9_]+$/', $identifier)) {
        throw new InvalidArgumentException("Invalid identifier: {$identifier}");
    }

    return $identifier;
}

function sqlsave_assert_single_statement(string $query): void
{
    $trimmed = trim($query);
    if ($trimmed === '') {
        throw new InvalidArgumentException('Query cannot be empty.');
    }

    $semicolonCount = substr_count($trimmed, ';');
    if ($semicolonCount > 1) {
        throw new InvalidArgumentException('Only a single SQL statement is allowed.');
    }

    if ($semicolonCount === 1 && !str_ends_with($trimmed, ';')) {
        throw new InvalidArgumentException('Only a single SQL statement is allowed.');
    }
}

function sqlsave_connect(): mysqli
{
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

    $mysqli = mysqli_init();
    $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
    $mysqli->real_connect(
        SQLSAVE_DB_HOST,
        SQLSAVE_DB_USER,
        SQLSAVE_DB_PASSWORD,
        SQLSAVE_DB_NAME,
        SQLSAVE_DB_PORT
    );
    $mysqli->set_charset(SQLSAVE_DEFAULT_CHARSET);

    return $mysqli;
}

function sqlsave_bind_types_and_values(array $params): array
{
    $types = '';
    $values = [];

    foreach ($params as $value) {
        if ($value === null) {
            $types .= 's';
            $values[] = null;
            continue;
        }

        if (is_bool($value)) {
            $types .= 'i';
            $values[] = $value ? 1 : 0;
            continue;
        }

        if (is_int($value)) {
            $types .= 'i';
            $values[] = $value;
            continue;
        }

        if (is_float($value)) {
            $types .= 'd';
            $values[] = $value;
            continue;
        }

        if (is_array($value) || is_object($value)) {
            $types .= 's';
            $values[] = json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            continue;
        }

        $types .= 's';
        $values[] = (string)$value;
    }

    return [$types, $values];
}

function sqlsave_execute_prepared(mysqli $db, string $query, array $params = []): mysqli_stmt
{
    $stmt = $db->prepare($query);

    if (!empty($params)) {
        [$types, $values] = sqlsave_bind_types_and_values($params);
        $bindArgs = [$types];
        foreach ($values as $index => $value) {
            $bindArgs[] = &$values[$index];
        }
        $stmt->bind_param(...$bindArgs);
    }

    $stmt->execute();
    return $stmt;
}

function sqlsave_fetch_all_assoc(mysqli_stmt $stmt): array
{
    $result = $stmt->get_result();
    if ($result === false) {
        return [];
    }

    $rows = $result->fetch_all(MYSQLI_ASSOC);
    $result->free();
    return $rows;
}

function sqlsave_fetch_first_assoc(mysqli_stmt $stmt): ?array
{
    $rows = sqlsave_fetch_all_assoc($stmt);
    if (count($rows) <= 0) {
        return null;
    }

    return $rows[0];
}

function sqlsave_json_decode_assoc(?string $json): array
{
    if (!is_string($json) || trim($json) === '') {
        return [];
    }

    $decoded = json_decode($json, true);
    if (!is_array($decoded)) {
        return [];
    }

    return $decoded;
}

function sqlsave_merge_extra_json(array $base, ?string $extraJson): array
{
    $extra = sqlsave_json_decode_assoc($extraJson);
    if (count($extra) <= 0) {
        return $base;
    }

    foreach ($extra as $key => $value) {
        if (!array_key_exists($key, $base)) {
            $base[$key] = $value;
        }
    }

    return $base;
}

function sqlsave_table_name(array $request): string
{
    $table = sqlsave_optional_string($request, 'table', SQLSAVE_DEFAULT_TABLE);
    return sqlsave_normalize_identifier($table);
}

function sqlsave_ensure_documents_table(mysqli $db, string $tableName): void
{
    $tableName = sqlsave_normalize_identifier($tableName);
    $sql = "
        CREATE TABLE IF NOT EXISTS `{$tableName}` (
            `namespace` VARCHAR(64) NOT NULL,
            `entity_id` VARCHAR(128) NOT NULL,
            `slot_key` VARCHAR(64) NOT NULL DEFAULT 'main',
            `payload_json` LONGTEXT NOT NULL,
            `payload_hash` CHAR(40) NOT NULL,
            `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`namespace`, `entity_id`, `slot_key`),
            KEY `idx_entity` (`entity_id`),
            KEY `idx_updated_at` (`updated_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ";

    $db->query($sql);
}

function sqlsave_document_key(array $request): array
{
    return [
        sqlsave_require_string($request, 'namespace'),
        sqlsave_require_string($request, 'entity_id'),
        sqlsave_optional_string($request, 'slot_key', 'main'),
    ];
}

function sqlsave_document_payload(array $request): array
{
    if (!array_key_exists('payload', $request)) {
        throw new InvalidArgumentException('Missing required field: payload');
    }

    $payload = $request['payload'];
    if (is_string($payload)) {
        $decoded = json_decode($payload, true);
        if (is_array($decoded)) {
            return $decoded;
        }
    }

    if (!is_array($payload)) {
        throw new InvalidArgumentException("Field 'payload' must be a JSON object or associative array.");
    }

    return $payload;
}

function sqlsave_document_upsert(mysqli $db, string $tableName, array $key, array $payload): array
{
    $payloadJson = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    if ($payloadJson === false) {
        throw new RuntimeException('Failed to encode payload_json.');
    }

    $payloadHash = sha1($payloadJson);
    $sql = "
        INSERT INTO `{$tableName}` (`namespace`, `entity_id`, `slot_key`, `payload_json`, `payload_hash`)
        VALUES (?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            `payload_json` = VALUES(`payload_json`),
            `payload_hash` = VALUES(`payload_hash`),
            `updated_at` = CURRENT_TIMESTAMP
    ";

    $stmt = sqlsave_execute_prepared($db, $sql, [
        $key[0],
        $key[1],
        $key[2],
        $payloadJson,
        $payloadHash,
    ]);

    return [
        'namespace' => $key[0],
        'entity_id' => $key[1],
        'slot_key' => $key[2],
        'payload_hash' => $payloadHash,
        'row_count' => $stmt->affected_rows,
    ];
}

function sqlsave_document_get(mysqli $db, string $tableName, array $key): array
{
    $sql = "
        SELECT `namespace`, `entity_id`, `slot_key`, `payload_json`, `payload_hash`, `created_at`, `updated_at`
        FROM `{$tableName}`
        WHERE `namespace` = ?
          AND `entity_id` = ?
          AND `slot_key` = ?
        LIMIT 1
    ";

    $stmt = sqlsave_execute_prepared($db, $sql, $key);
    $rows = sqlsave_fetch_all_assoc($stmt);
    if (count($rows) <= 0) {
        return ['found' => false];
    }

    $row = $rows[0];
    $payload = json_decode((string)$row['payload_json'], true);
    if (!is_array($payload)) {
        $payload = [];
    }

    return [
        'found' => true,
        'namespace' => (string)$row['namespace'],
        'entity_id' => (string)$row['entity_id'],
        'slot_key' => (string)$row['slot_key'],
        'payload_hash' => (string)$row['payload_hash'],
        'created_at' => (string)$row['created_at'],
        'updated_at' => (string)$row['updated_at'],
        'payload' => $payload,
    ];
}

function sqlsave_document_delete(mysqli $db, string $tableName, array $key): array
{
    $sql = "
        DELETE FROM `{$tableName}`
        WHERE `namespace` = ?
          AND `entity_id` = ?
          AND `slot_key` = ?
    ";

    $stmt = sqlsave_execute_prepared($db, $sql, $key);
    return [
        'namespace' => $key[0],
        'entity_id' => $key[1],
        'slot_key' => $key[2],
        'row_count' => $stmt->affected_rows,
    ];
}

function sqlsave_document_list(mysqli $db, string $tableName, array $request): array
{
    $namespace = sqlsave_require_string($request, 'namespace');
    $entityId = sqlsave_optional_string($request, 'entity_id', '');
    $limit = max(1, min(SQLSAVE_MAX_SELECT_ROWS, sqlsave_optional_int($request, 'limit', 100)));
    $offset = max(0, sqlsave_optional_int($request, 'offset', 0));

    $sql = "
        SELECT `namespace`, `entity_id`, `slot_key`, `payload_hash`, `created_at`, `updated_at`
        FROM `{$tableName}`
        WHERE `namespace` = ?
    ";
    $params = [$namespace];

    if ($entityId !== '') {
        $sql .= " AND `entity_id` = ?";
        $params[] = $entityId;
    }

    $sql .= " ORDER BY `entity_id` ASC, `slot_key` ASC LIMIT ? OFFSET ?";
    $params[] = $limit;
    $params[] = $offset;

    $stmt = sqlsave_execute_prepared($db, $sql, $params);
    return [
        'rows' => sqlsave_fetch_all_assoc($stmt),
        'limit' => $limit,
        'offset' => $offset,
    ];
}

function sqlsave_run_select(mysqli $db, array $request): array
{
    $query = sqlsave_require_string($request, 'query');
    sqlsave_assert_single_statement($query);

    if (!preg_match('/^\s*(SELECT|SHOW|DESCRIBE|EXPLAIN|WITH)\b/i', $query)) {
        throw new InvalidArgumentException('sql_select allows only SELECT/SHOW/DESCRIBE/EXPLAIN/WITH statements.');
    }

    $params = array_values(sqlsave_optional_array($request, 'params', []));
    $limit = max(1, min(SQLSAVE_MAX_SELECT_ROWS, sqlsave_optional_int($request, 'limit', 100)));

    $stmt = sqlsave_execute_prepared($db, $query, $params);
    $rows = sqlsave_fetch_all_assoc($stmt);
    if (count($rows) > $limit) {
        $rows = array_slice($rows, 0, $limit);
    }

    return [
        'rows' => $rows,
        'row_count' => count($rows),
        'limit' => $limit,
    ];
}

function sqlsave_run_execute(mysqli $db, array $request): array
{
    $query = sqlsave_require_string($request, 'query');
    sqlsave_assert_single_statement($query);

    if (!preg_match('/^\s*(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP|TRUNCATE)\b/i', $query)) {
        throw new InvalidArgumentException('sql_execute allows only write/ddl statements.');
    }

    $params = array_values(sqlsave_optional_array($request, 'params', []));
    $stmt = sqlsave_execute_prepared($db, $query, $params);

    return [
        'row_count' => $stmt->affected_rows,
        'insert_id' => $db->insert_id,
    ];
}