index.php

<?php
use Google\Cloud\Sql\Admin\V1\Client\SqlDatabasesServiceClient;
use Google\Cloud\Sql\Admin\V1\Database;
use Google\Cloud\Sql\Admin\V1\InsertDatabaseRequest;
use Google\CloudFunctions\FunctionsFramework;
use Psr\Http\Message\ServerRequestInterface;

// Load Composer autoloader when available (ensures google client classes are present)
if (file_exists(__DIR__ . '/vendor/autoload.php')) {
    require_once __DIR__ . '/vendor/autoload.php';
}

// Register an HTTP function with the Functions Framework
FunctionsFramework::http('myHttpFunction', 'myHttpHandler');

// Define your HTTP handler
function myHttpHandler(ServerRequestInterface $request): string
{
    // Orchestrator: accepts tenant name via JSON body or ?name= query
    $tenant = 'tenant-' . bin2hex(random_bytes(4));
    $body = $request->getBody()->getContents();
    if (!empty($body)) {
        $json = json_decode($body, true);
        if (json_last_error() != JSON_ERROR_NONE) {
            throw new RuntimeException(sprintf(
                'Could not parse body: %s',
                json_last_error_msg()
            ));
        }
        if (!empty($json['name'])) {
            $tenant = $json['name'];
        }
    }
    $queryString = $request->getQueryParams();
    $tenant = $queryString['name'] ?? $tenant;

    // Configuration (from environment)
    $projectId = getenv('PROJECT_ID') ?: getenv('GCP_PROJECT') ?: getenv('GOOGLE_CLOUD_PROJECT');
    if (empty($projectId)) {
        throw new RuntimeException('GCP project not set in environment (PROJECT_ID or GCP_PROJECT)');
    }
    $instanceId = getenv('INSTANCE_ID') ?: 'cloud-function-test';
    // Step 1: create database
    $dbName = $tenant;
    $dbResult = createTenantDatabase($projectId, $instanceId, $dbName);

    // Step 2: create service account
    // Build a valid service account id: must start with a letter, contain
    // only letters, digits and hyphens, and end with an alphanumeric character.
    $saId = strtolower($tenant);
    // replace invalid chars with hyphen
    $saId = preg_replace('/[^a-z0-9-]+/', '-', $saId);
    // collapse multiple hyphens
    $saId = preg_replace('/-+/', '-', $saId);
    // trim leading/trailing hyphens
    $saId = trim($saId, '-');
    if ($saId === '') {
        $saId = 'tenant';
    }
    // ensure starts with a letter
    if (!preg_match('/^[a-z]/', $saId)) {
        $saId = 'a' . $saId;
    }
    // ensure ends with alphanumeric
    if (!preg_match('/[a-z0-9]$/', $saId)) {
        $saId = rtrim($saId, '-') . '1';
    }
    // limit length to 30 chars (safe for SA ids)
    $saId = substr($saId, 0, 30);
    $saResult = createServiceAccount($projectId, $saId, "SA for {$tenant}");
    $saEmail = $saResult['email'] ?? null;

    // Step 2a: ensure the tenant service account is added as a Cloud SQL IAM user
    // and grant it privileges on the tenant database.
    $dbAdminUser = getenv('DB_ADMIN_USER') ?: null;
    $dbAdminPass = getenv('DB_ADMIN_PASSWORD') ?: null;
    $dbUserResult = null;
    if ($saEmail) {
        $dbUserResult = createIamDbUserAndGrant($projectId, $instanceId, $dbName, $saEmail, $dbAdminUser, $dbAdminPass);
    }
    // Step 3: deploy a simple Cloud Run service with SA and TENANT env var
    $region = 'europe-west1';
    $serviceName = 'hello-' . $saId;
    $image = 'gcr.io/cloudrun/hello';
    $deployResult = deployCloudRunService($projectId, $region, $serviceName, $image, $saEmail, ['TENANT' => $tenant]);

    // Step 4: database authentication method
    // We use Cloud SQL IAM authentication: do NOT create a passworded DB user here.
    // Deploy tenant Cloud Run services with the tenant service account and attach
    // the Cloud SQL instance (`--add-cloudsql-instances`) so they authenticate via IAM.
    $instanceConnectionName = getenv('CLOUD_SQL_CONNECTION_NAME') ?: null; // PROJECT:REGION:INSTANCE
    if ($instanceConnectionName) {
        $response['dbAuth'] = [
            'method' => 'iam',
            'note' => 'tenant services should be deployed with the tenant service account and the Cloud SQL instance attached',
            'connection_name' => $instanceConnectionName,
        ];
    }

    $response = [
        'database' => $dbResult,
        'serviceAccount' => $saResult,
        'cloudRun' => $deployResult,
    ];

    header('Content-Type: application/json');
    return json_encode($response);
}

function createTenantDatabase($projectId, $instanceId, $dbName)
{
    // Prefer library if autoloaded and class exists; otherwise use REST fallback.
    if (class_exists(SqlDatabasesServiceClient::class)) {
        try {
            $client = new SqlDatabasesServiceClient();
            $database = new Database([
                'name' => $dbName,
            ]);
            $request = new InsertDatabaseRequest([
                'project' => $projectId,
                'instance' => $instanceId,
                'database' => $database,
            ]);
            $operation = $client->insert($request);
            $operation->pollUntilComplete();
            return ['status' => 'created', 'database' => $dbName];
        } catch (\Throwable $e) {
            $libError = $e->getMessage();
        }
    } else {
        $libError = 'Cloud SQL Admin client library not available';
    }

    // REST fallback
    try {
        $token = getAccessToken();
    } catch (\Throwable $e) {
        return ['status' => 'error', 'message' => 'failed to get access token for REST fallback', 'exception' => $e->getMessage(), 'library_error' => $libError ?? null];
    }
    $url = sprintf('https://sqladmin.googleapis.com/sql/v1beta4/projects/%s/instances/%s/databases', $projectId, $instanceId);
    $payload = json_encode(['name' => $dbName]);
    $opts = [
        'http' => [
            'method' => 'POST',
            'header' => [
                'Authorization: Bearer ' . $token,
                'Content-Type: application/json',
            ],
            'content' => $payload,
            'ignore_errors' => true,
        ],
    ];
    $context = stream_context_create($opts);
    $result = @file_get_contents($url, false, $context);
    $httpInfo = isset($http_response_header) ? $http_response_header : null;
    if ($result === false) {
        return ['status' => 'error', 'message' => 'failed to create database via REST', 'library_error' => $libError ?? null, 'http_info' => $httpInfo];
    }
    $decoded = json_decode($result, true);
    return ['status' => 'created_rest', 'database' => $dbName, 'response' => $decoded, 'http_info' => $httpInfo];
}

function getAccessToken()
{
    // Get an access token from the metadata server
    $url = 'http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/default/token';
    $opts = [
        'http' => [
            'method' => 'GET',
            'header' => [
                'Metadata-Flavor: Google',
            ],
            'timeout' => 5,
        ],
    ];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($url, false, $context);
    if ($resp === false) {
        throw new RuntimeException('Unable to fetch access token from metadata server');
    }
    $data = json_decode($resp, true);
    if (empty($data['access_token'])) {
        throw new RuntimeException('No access token in metadata response');
    }
    return $data['access_token'];
}

function createServiceAccount($projectId, $saId, $displayName)
{
    $token = getAccessToken();
    // Use POST body `accountId` when creating a service account (no query param)
    $createUrl = sprintf('https://iam.googleapis.com/v1/projects/%s/serviceAccounts', $projectId);
    $body = ['accountId' => $saId, 'serviceAccount' => ['displayName' => $displayName]];
    $opts = [
        'http' => [
            'method' => 'POST',
            'header' => [
                'Authorization: Bearer ' . $token,
                'Content-Type: application/json',
            ],
            'content' => json_encode($body),
            'timeout' => 10,
            'ignore_errors' => true,
        ],
    ];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($createUrl, false, $context);
    $httpInfo = isset($http_response_header) ? $http_response_header : null;

    // Determine service account email even if creation failed (may already exist)
    $saEmail = $saId . '@' . $projectId . '.iam.gserviceaccount.com';

    if ($resp === false) {
        $created = false;
        $raw = null;
    } else {
        $raw = json_decode($resp, true);
        if (isset($raw['error'])) {
            // If the SA already exists the API may return a 400 with a message.
            $msg = $raw['error']['message'] ?? '';
            if (stripos($msg, 'already exists') !== false) {
                $created = false;
            } else {
                // creation failed for other reasons
                return ['email' => $saEmail, 'raw' => $raw, 'note' => 'create_failed', 'http_info' => $httpInfo];
            }
        } else {
            $created = isset($raw['email']);
            if ($created) {
                $saEmail = $raw['email'];
            }
        }
    }

    // Attempt to add impersonation binding so the runtime SA can 'actAs' this tenant SA.
    // Get runtime service account email from metadata
    try {
        $runtimeSa = getRuntimeServiceAccountEmail();
    } catch (\Throwable $e) {
        return ['email' => $saEmail, 'raw' => $raw, 'note' => 'created' . ($created ? '' : '_not'), 'impersonation' => 'failed_to_read_runtime_sa', 'error' => $e->getMessage(), 'http_info' => $httpInfo];
    }

    // Build resource name for the tenant SA
    $resource = sprintf('projects/%s/serviceAccounts/%s', $projectId, $saEmail);

    // Get current policy
    $policy = iamGetPolicy($resource);
    if ($policy === null) {
        return ['email' => $saEmail, 'raw' => $raw, 'note' => 'created' . ($created ? '' : '_not'), 'impersonation' => 'get_policy_failed', 'http_info' => $httpInfo];
    }

    // Ensure binding exists
    $member = 'serviceAccount:' . $runtimeSa;
    $role = 'roles/iam.serviceAccountUser';
    $found = false;
    if (!isset($policy['bindings']) || !is_array($policy['bindings'])) {
        $policy['bindings'] = [];
    }
    foreach ($policy['bindings'] as &$b) {
        if (($b['role'] ?? '') === $role) {
            if (!in_array($member, $b['members'] ?? [])) {
                $b['members'][] = $member;
            }
            $found = true;
            break;
        }
    }
    if (!$found) {
        $policy['bindings'][] = ['role' => $role, 'members' => [$member]];
    }

    $setOk = iamSetPolicy($resource, $policy);
    $impersonation = $setOk ? 'binding_added' : 'binding_failed';

    // Also grant the tenant service account the Cloud SQL client role at project level
    // so Cloud Run services running as this SA can connect to Cloud SQL.
    try {
        $member = 'serviceAccount:' . $saEmail;
        error_log("DEBUG: Attempting to grant roles/cloudsql.client to $member at project level");
        $added = addProjectIamBinding($projectId, 'roles/cloudsql.client', $member);
        if ($added === true) {
            error_log("DEBUG: Successfully granted roles/cloudsql.client");
            $impersonation .= '|cloudsql_client_granted';
        } else {
            $errorMsg = is_array($added) ? ($added['error'] ?? 'unknown_error') : 'unknown_error';
            error_log("ERROR: Failed to grant roles/cloudsql.client: $errorMsg");
            $impersonation .= '|cloudsql_client_failed:' . $errorMsg;
        }
        
        // Also grant Cloud SQL instance user role so the SA can be used as a DB user
        error_log("DEBUG: Attempting to grant roles/cloudsql.instanceUser to $member at project level");
        $added2 = addProjectIamBinding($projectId, 'roles/cloudsql.instanceUser', $member);
        if ($added2 === true) {
            error_log("DEBUG: Successfully granted roles/cloudsql.instanceUser");
            $impersonation .= '|cloudsql_instanceuser_granted';
        } else {
            $errorMsg = is_array($added2) ? ($added2['error'] ?? 'unknown_error') : 'unknown_error';
            error_log("ERROR: Failed to grant roles/cloudsql.instanceUser: $errorMsg");
            $impersonation .= '|cloudsql_instanceuser_failed:' . $errorMsg;
        }
    } catch (\Throwable $e) {
        error_log("ERROR: Exception while granting Cloud SQL roles: " . $e->getMessage());
        $impersonation .= '|cloudsql_roles_exception:' . $e->getMessage();
    }

    return ['email' => $saEmail, 'raw' => $raw, 'note' => 'created' . ($created ? '' : '_not'), 'impersonation' => $impersonation, 'http_info' => $httpInfo];
}

function getRuntimeServiceAccountEmail()
{
    $url = 'http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/default/email';
    $opts = ['http' => ['method' => 'GET', 'header' => ['Metadata-Flavor: Google'], 'timeout' => 3]];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($url, false, $context);
    if ($resp === false) {
        throw new RuntimeException('Unable to fetch runtime service account email from metadata');
    }
    return trim($resp);
}

function iamGetPolicy($resource)
{
    try {
        $token = getAccessToken();
    } catch (\Throwable $e) {
        return null;
    }
    $url = sprintf('https://iam.googleapis.com/v1/%s:getIamPolicy', $resource);
    $opts = [
        'http' => [
            'method' => 'POST',
            'header' => [
                'Authorization: Bearer ' . $token,
                'Content-Type: application/json',
            ],
            'content' => '{}',
            'timeout' => 10,
            'ignore_errors' => true,
        ],
    ];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($url, false, $context);
    if ($resp === false) {
        return null;
    }
    return json_decode($resp, true);
}

function iamSetPolicy($resource, $policy)
{
    try {
        $token = getAccessToken();
    } catch (\Throwable $e) {
        return false;
    }
    $url = sprintf('https://iam.googleapis.com/v1/%s:setIamPolicy', $resource);
    $body = ['policy' => $policy];
    $opts = [
        'http' => [
            'method' => 'POST',
            'header' => [
                'Authorization: Bearer ' . $token,
                'Content-Type: application/json',
            ],
            'content' => json_encode($body),
            'timeout' => 10,
            'ignore_errors' => true,
        ],
    ];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($url, false, $context);
    if ($resp === false) {
        return false;
    }
    $decoded = json_decode($resp, true);
    return isset($decoded['bindings']);
}

function deployCloudRunService($projectId, $region, $serviceName, $image, $serviceAccountEmail, $envVars = [])
{
    $token = getAccessToken();
    $parent = sprintf('projects/%s/locations/%s', $projectId, $region);
    $url = sprintf('https://run.googleapis.com/v1/%s/services', $parent);

    $serviceFullName = sprintf('projects/%s/locations/%s/services/%s', $projectId, $region, $serviceName);
    $envList = [];
    foreach ($envVars as $k => $v) {
        $envList[] = ['name' => $k, 'value' => $v];
    }

    // If a Cloud SQL connection name is provided in env, add the Cloud Run annotation
    // so the tenant service can use the platform-managed Cloud SQL socket.
    $connName = getenv('CLOUD_SQL_CONNECTION_NAME') ?: null;
    // Template metadata must NOT include `name` (revision name is generated by Cloud Run
    // and must be prefixed by the service name). Only include annotations here.
    $templateMetadata = [];
    if ($connName) {
        $templateMetadata['annotations'] = [
            'run.googleapis.com/cloudsql-instances' => $connName,
        ];
    }

    $body = [
        'apiVersion' => 'serving.knative.dev/v1',
        'kind' => 'Service',
        'metadata' => ['name' => $serviceName],
        'spec' => [
            'template' => [
                'metadata' => $templateMetadata,
                'spec' => [
                    'serviceAccountName' => $serviceAccountEmail,
                    'containers' => [[
                        'image' => $image,
                        'env' => $envList,
                    ]],
                ],
            ],
        ],
    ];

    $opts = [
        'http' => [
            'method' => 'POST',
            'header' => [
                'Authorization: Bearer ' . $token,
                'Content-Type: application/json',
            ],
            'content' => json_encode($body),
            'timeout' => 20,
            'ignore_errors' => true,
        ],
    ];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($url, false, $context);
    $httpInfo = isset($http_response_header) ? $http_response_header : null;
    if ($resp === false) {
        return ['status' => 'error', 'message' => 'failed to create cloud run service', 'http_info' => $httpInfo];
    }
    $decoded = json_decode($resp, true);
    // If the API returned an error payload, include it for debugging
    if (is_array($decoded) && (isset($decoded['error']) || isset($decoded['status']) && $decoded['status'] !== 'OK')) {
        return ['status' => 'error', 'message' => 'cloud run API returned error', 'body' => $decoded, 'http_info' => $httpInfo];
    }
    return ['status' => 'created', 'service' => $decoded, 'http_info' => $httpInfo];
}

/**
 * Create a Cloud SQL IAM user for the given service account email and grant
 * privileges on the specified database using admin credentials.
 */
function createIamDbUserAndGrant($projectId, $instanceId, $dbName, $saEmail, $adminUser = null, $adminPass = null)
{
    $result = ['created_via_api' => false, 'api_response' => null, 'http_info' => null, 'grants' => null, 'error' => null];

    // 1) Create the DB user via SQL Admin API (type=CLOUD_IAM_SERVICE_ACCOUNT)
    try {
        $token = getAccessToken();
    } catch (\Throwable $e) {
        $result['error'] = 'no_access_token: ' . $e->getMessage();
        return $result;
    }

    // Wait for the database to become available (the DB creation may be async).
    $dbReady = false;
    $waitTimeout = 60; // seconds
    $waitInterval = 2; // seconds
    $elapsed = 0;
    while ($elapsed < $waitTimeout) {
        $checkUrl = sprintf('https://sqladmin.googleapis.com/sql/v1beta4/projects/%s/instances/%s/databases/%s', $projectId, $instanceId, $dbName);
        $checkOpts = [
            'http' => [
                'method' => 'GET',
                'header' => [
                    'Authorization: Bearer ' . $token,
                    'Content-Type: application/json'
                ],
                'timeout' => 5,
                'ignore_errors' => true,
            ],
        ];
        $checkCtx = stream_context_create($checkOpts);
        $checkResp = @file_get_contents($checkUrl, false, $checkCtx);
        if ($checkResp !== false) {
            $checkDecoded = json_decode($checkResp, true);
            if (!isset($checkDecoded['error'])) {
                $dbReady = true;
                break;
            }
        }
        sleep($waitInterval);
        $elapsed += $waitInterval;
    }
    $result['db_ready'] = $dbReady ? 'yes' : 'no';

    // Derive a short, MySQL-compatible username (max 32 chars) from the service account email.
    // Use the local-part (before @), replace unsafe chars with underscore and truncate.
    $local = strpos($saEmail, '@') !== false ? strstr($saEmail, '@', true) : $saEmail;
    $short = preg_replace('/[^A-Za-z0-9_]/', '_', $local);
    $short = substr($short, 0, 32);
    if ($short === '') {
        // fallback to a generated short name
        $short = 'sa_' . substr(md5($saEmail), 0, 28);
    }

    $dbUserName = $short;

    $url = sprintf('https://sqladmin.googleapis.com/sql/v1beta4/projects/%s/instances/%s/users', $projectId, $instanceId);
    // Include the service account email so Cloud SQL associates the IAM identity
    // Use the full service account email as the user name (matches console/curl)
    // and omit host — the API accepts the email as the name for IAM users.
    $body = [
        'name' => $saEmail,
        'type' => 'CLOUD_IAM_SERVICE_ACCOUNT',
        'serviceAccountEmailAddress' => $saEmail,
    ];
    $opts = [
        'http' => [
            'method' => 'POST',
            'header' => [
                'Authorization: Bearer ' . $token,
                'Content-Type: application/json'
            ],
            'content' => json_encode($body),
            'timeout' => 15,
            'ignore_errors' => true,
        ],
    ];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($url, false, $context);
    $httpInfo = isset($http_response_header) ? $http_response_header : null;
    $result['http_info'] = $httpInfo;
    if ($resp !== false) {
        $decoded = json_decode($resp, true);
        $result['api_response'] = $decoded;
        if (!isset($decoded['error'])) {
            $result['created_via_api'] = true;
        }
    }

    // include the DB username chosen so callers can use it for connection/GRANTs
    $result['db_user'] = $dbUserName;

    // 2) If admin credentials provided, connect and GRANT privileges on DB
    if (empty($adminUser) || empty($adminPass)) {
        $result['grants'] = 'no_admin_credentials';
        return $result;
    }

    $connName = getenv('CLOUD_SQL_CONNECTION_NAME') ?: null;
    if ($connName) {
        $socket = '/cloudsql/' . $connName;
        $mysqli = @new mysqli(null, $adminUser, $adminPass, null, null, $socket);
    } else {
        $ip = getInstanceIp($projectId, $instanceId);
        if (empty($ip)) {
            $result['grants'] = 'no_instance_ip';
            return $result;
        }
        $mysqli = @new mysqli($ip, $adminUser, $adminPass);
    }

    if ($mysqli === null || $mysqli->connect_errno) {
        $result['grants'] = 'connect_failed: ' . ($mysqli ? $mysqli->connect_error : 'unknown');
        return $result;
    }

    // Use the short DB username for GRANTs (MySQL username length limit is 32)
    $safeUser = $mysqli->real_escape_string($dbUserName);
    $dbEsc = $mysqli->real_escape_string($dbName);

    // Verify the Cloud SQL user exists via the SQL Admin API (the Console creates
    // the IAM-linked user for you; we must check the API list to confirm).
    // Poll for the SQL user to appear (user creation can be eventually consistent).
    try {
        $listUrl = sprintf('https://sqladmin.googleapis.com/sql/v1beta4/projects/%s/instances/%s/users', $projectId, $instanceId);
        $userWaitTimeout = 30;
        $userWaitInterval = 2;
        $userElapsed = 0;
        $listDecoded = null;
        $userExists = false;
        $matchedSqlUser = null;
        
        while ($userElapsed < $userWaitTimeout) {
            $listOpts = [
                'http' => [
                    'method' => 'GET',
                    'header' => [
                        'Authorization: Bearer ' . $token,
                        'Content-Type: application/json'
                    ],
                    'timeout' => 10,
                    'ignore_errors' => true,
                ],
            ];
            $listContext = stream_context_create($listOpts);
            $listResp = @file_get_contents($listUrl, false, $listContext);
            $result['list_api_http_info'] = isset($http_response_header) ? $http_response_header : null;
            $listDecoded = $listResp ? json_decode($listResp, true) : null;
            
            if (is_array($listDecoded) && isset($listDecoded['items']) && is_array($listDecoded['items'])) {
                foreach ($listDecoded['items'] as $u) {
                    if ((isset($u['name']) && $u['name'] === $dbUserName) || (isset($u['serviceAccountEmailAddress']) && $u['serviceAccountEmailAddress'] === $saEmail)) {
                        $userExists = true;
                        // prefer the API's reported name for the SQL user (this is the username to target for GRANT)
                        if (isset($u['name']) && $u['name'] !== '') {
                            $matchedSqlUser = $u['name'];
                        }
                        break 2; // break out of while loop
                    }
                }
            }
            
            if (!$userExists) {
                sleep($userWaitInterval);
                $userElapsed += $userWaitInterval;
            } else {
                break;
            }
        }
        $result['user_waited_seconds'] = $userElapsed;
        $result['list_api_response'] = $listDecoded;
    } catch (\Throwable $e) {
        $result['list_api_error'] = $e->getMessage();
        $userExists = false;
    }

    // Record whether API reports the user exists; do NOT return early —
    // even if the API did not list the IAM user, attempt GRANTs when admin creds provided
    $result['user_exists_via_api'] = $userExists;
    if (!$userExists) {
        $result['suggest_api_payload'] = [
            'url' => $url,
            'body' => [
                'name' => $dbUserName,
                'host' => '%',
                'type' => 'CLOUD_IAM_SERVICE_ACCOUNT',
                'serviceAccountEmailAddress' => $saEmail,
            ],
        ];
        // do not return; continue and attempt to run GRANTs (may succeed if short DB user exists)
    }

    // Decide which SQL username to grant: prefer API-matched username, else the short local name
    $targetUser = $matchedSqlUser ?? $dbUserName;
    
    error_log("DEBUG: Target user for GRANT: '$targetUser'");
    error_log("DEBUG: Database name: '$dbName'");
    error_log("DEBUG: Service account email: '$saEmail'");
    error_log("DEBUG: Matched SQL user from API: " . ($matchedSqlUser ?? 'null'));
    error_log("DEBUG: Short DB user name: '$dbUserName'");
    
    // For Cloud SQL IAM users, the actual MySQL user is just the prefix before @
    // e.g., 'wednesday-8@peoplecloud-test.iam.gserviceaccount.com' becomes 'wednesday-8'
    $userToCheck = explode('@', $saEmail)[0];
    error_log("DEBUG: Will check for Cloud SQL IAM user with prefix: '$userToCheck'");
    
    // Poll MySQL directly to confirm the user exists before attempting GRANT.
    // The API may report success before the user is actually in the database.
    $mysqlUserReady = false;
    $mysqlWaitTimeout = 30;
    $mysqlWaitInterval = 2;
    $mysqlElapsed = 0;
    $mysqlCheckAttempts = 0;
    $mysqlCheckErrors = [];
    
    $result['debug_target_user'] = $targetUser;
    $result['debug_iamuser_lookup'] = $userToCheck;
    
    if ($mysqli && !$mysqli->connect_errno) {
        error_log("DEBUG: Connected to MySQL, starting user existence check...");
        while ($mysqlElapsed < $mysqlWaitTimeout) {
            $checkUserSql = sprintf("SELECT user, host FROM mysql.user WHERE user = '%s' AND host = '%%'", $mysqli->real_escape_string($userToCheck));
            error_log("DEBUG: Attempt " . ($mysqlCheckAttempts + 1) . " - Executing: $checkUserSql");
            $result['debug_check_sql'] = $checkUserSql;
            $mysqlCheckAttempts++;
            
            $res = @$mysqli->query($checkUserSql);
            if ($res === false) {
                $errMsg = 'Query error: ' . $mysqli->error;
                error_log("DEBUG: Query failed - $errMsg");
                $mysqlCheckErrors[] = $errMsg;
            } else {
                error_log("DEBUG: Query successful, rows found: " . $res->num_rows);
                if ($res->num_rows > 0) {
                    $mysqlUserReady = true;
                    $row = $res->fetch_assoc();
                    error_log("DEBUG: User found in mysql.user: " . json_encode($row));
                    $result['debug_user_found'] = $row;
                    break;
                } else {
                    error_log("DEBUG: User NOT found yet, waiting...");
                }
            }
            sleep($mysqlWaitInterval);
            $mysqlElapsed += $mysqlWaitInterval;
        }
    } else {
        error_log("DEBUG: MySQL connection not available");
    }
    
    error_log("DEBUG: MySQL user ready: " . ($mysqlUserReady ? 'YES' : 'NO') . " (waited $mysqlElapsed seconds)");
    $result['mysql_user_waited_seconds'] = $mysqlElapsed;
    $result['mysql_user_ready'] = $mysqlUserReady ? 'yes' : 'no';
    $result['mysql_check_attempts'] = $mysqlCheckAttempts;
    if (!empty($mysqlCheckErrors)) {
        $result['mysql_check_errors'] = $mysqlCheckErrors;
    }
    
    // STEP 2: Grant privileges on the tenant database to the chosen DB user
    // Use the short username for the GRANT (MySQL has a 32-char limit on usernames)
    $grantSql = sprintf("GRANT ALL PRIVILEGES ON `%s`.* TO '%s'@'%%'", $dbEsc, $mysqli->real_escape_string($userToCheck));
    error_log("DEBUG: About to execute GRANT: $grantSql");
    $result['attempted_grant'] = false;
    $result['grant_sql'] = $grantSql;
    if ($mysqli) {
        $result['db_connect_info'] = ['host' => $connName ? 'unix_socket' : ($ip ?? null), 'user' => $adminUser];
        error_log("DEBUG: Executing GRANT with mysqli->query()");
        try {
            $ok = $mysqli->query($grantSql);
            $result['attempted_grant'] = true;
            if ($ok) {
                error_log("DEBUG: GRANT succeeded!");
                $flushOk = $mysqli->query('FLUSH PRIVILEGES');
                if (!$flushOk) {
                    error_log("ERROR: FLUSH PRIVILEGES failed: " . $mysqli->error);
                    $result['grant_error'] = 'flush_failed: ' . $mysqli->error;
                    $result['grants'] = 'flush_failed';
                } else {
                    error_log("DEBUG: FLUSH PRIVILEGES succeeded");
                    $result['grants'] = 'ok';
                }
            } else {
                error_log("ERROR: GRANT failed: " . $mysqli->error);
                $result['grant_error'] = 'grant_failed: ' . $mysqli->error;
                $result['grants'] = 'grant_failed';
                $result['suggest_sql'] = [sprintf("GRANT ALL PRIVILEGES ON `%s`.* TO '%s'@'%%';", $dbEsc, $userToCheck), 'FLUSH PRIVILEGES;'];
            }
        } catch (\Throwable $e) {
            error_log("ERROR: Exception during GRANT: " . $e->getMessage());
            error_log("ERROR: Stack: " . $e->getTraceAsString());
            $result['attempted_grant'] = true;
            $result['grant_error'] = 'exception: ' . $e->getMessage();
            $result['grants'] = 'grant_exception';
            throw $e;  // Re-throw so it gets logged by Cloud Run
        }
        $mysqli->close();
    } else {
        $result['grants'] = 'no_db_connection_for_grant';
        $result['suggest_sql'] = [sprintf("GRANT ALL PRIVILEGES ON `%s`.* TO '%s'@'%%';", $dbEsc, $safeUser)];
    }

    return $result;
}

function getInstanceIp($projectId, $instanceId)
{
    try {
        $token = getAccessToken();
    } catch (\Throwable $e) {
        return null;
    }
    $url = sprintf('https://sqladmin.googleapis.com/sql/v1beta4/projects/%s/instances/%s', $projectId, $instanceId);
    $opts = ['http' => ['method' => 'GET', 'header' => ['Authorization: Bearer ' . $token], 'ignore_errors' => true]];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($url, false, $context);
    if ($resp === false) {
        return null;
    }
    $data = json_decode($resp, true);
    if (empty($data['ipAddresses']) || !is_array($data['ipAddresses'])) {
        return null;
    }
    // prefer PRIMARY or first
    foreach ($data['ipAddresses'] as $addr) {
        if (($addr['type'] ?? '') === 'PRIMARY' || ($addr['type'] ?? '') === 'PRIMARY_ADDRESS') {
            return $addr['ipAddress'];
        }
    }
    return $data['ipAddresses'][0]['ipAddress'] ?? null;
}

// Add an IAM binding to the project policy for a given role/member
// Returns: true on success, or associative array with error details
function addProjectIamBinding($projectId, $role, $member)
{
    try {
        $token = getAccessToken();
    } catch (\Throwable $e) {
        return ['success' => false, 'error' => 'Failed to get access token: ' . $e->getMessage()];
    }
    
    $getUrl = sprintf('https://cloudresourcemanager.googleapis.com/v1/projects/%s:getIamPolicy', $projectId);
    $opts = [
        'http' => [
            'method' => 'POST',
            'header' => [
                'Authorization: Bearer ' . $token,
                'Content-Type: application/json'
            ],
            'content' => json_encode(new stdClass()),
            'timeout' => 15,
            'ignore_errors' => true
        ]
    ];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($getUrl, false, $context);
    $httpHeaders = isset($http_response_header) ? $http_response_header : null;
    if ($resp === false) {
        return ['success' => false, 'error' => 'Failed to get IAM policy', 'http_info' => $httpHeaders];
    }
    $policy = json_decode($resp, true);
    if (isset($policy['error'])) {
        $errorDetail = isset($policy['error']['message']) ? $policy['error']['message'] : json_encode($policy['error']);
        error_log("ERROR: getIamPolicy failed: $errorDetail");
        return ['success' => false, 'error' => 'API error in getIamPolicy: ' . $errorDetail, 'api_error' => $policy['error'], 'http_info' => $httpHeaders];
    }
    if (!isset($policy['bindings']) || !is_array($policy['bindings'])) {
        $policy['bindings'] = [];
    }
    
    $found = false;
    foreach ($policy['bindings'] as $b) {
        if (isset($b['role']) && $b['role'] === $role && in_array($member, $b['members'] ?? [])) {
            $found = true;
            break;
        }
    }
    if ($found) {
        return true;  // Already bound
    }
    
    // add or append
    $appended = false;
    for ($i = 0; $i < count($policy['bindings']); $i++) {
        if ($policy['bindings'][$i]['role'] === $role) {
            $policy['bindings'][$i]['members'][] = $member;
            $appended = true;
            break;
        }
    }
    if (!$appended) {
        $policy['bindings'][] = ['role' => $role, 'members' => [$member]];
    }

    // set policy
    $setUrl = sprintf('https://cloudresourcemanager.googleapis.com/v1/projects/%s:setIamPolicy', $projectId);
    $body = ['policy' => $policy];
    $opts = [
        'http' => [
            'method' => 'POST',
            'header' => [
                'Authorization: Bearer ' . $token,
                'Content-Type: application/json'
            ],
            'content' => json_encode($body),
            'timeout' => 15,
            'ignore_errors' => true
        ]
    ];
    $context = stream_context_create($opts);
    $resp = @file_get_contents($setUrl, false, $context);
    $httpHeaders = isset($http_response_header) ? $http_response_header : null;
    if ($resp === false) {
        return ['success' => false, 'error' => 'Failed to set IAM policy (network error)', 'http_info' => $httpHeaders];
    }
    $respDecoded = json_decode($resp, true);
    if (isset($respDecoded['error'])) {
        $errorDetail = isset($respDecoded['error']['message']) ? $respDecoded['error']['message'] : json_encode($respDecoded['error']);
        error_log("ERROR: setIamPolicy failed: $errorDetail");
        return ['success' => false, 'error' => 'API error in setIamPolicy: ' . $errorDetail, 'api_error' => $respDecoded['error'], 'http_info' => $httpHeaders];
    }
    return true;
}