<?php

function guestInboxTableExists(PDO $pdo, string $table): bool
{
    try {
        $stmt = $pdo->prepare('SHOW TABLES LIKE :table');
        $stmt->execute(['table' => $table]);
        return (bool) $stmt->fetchColumn();
    } catch (Throwable $e) {
        return false;
    }
}

function guestInboxColumnExists(PDO $pdo, string $table, string $column): bool
{
    try {
        $stmt = $pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE :column");
        $stmt->execute(['column' => $column]);
        return (bool) $stmt->fetchColumn();
    } catch (Throwable $e) {
        return false;
    }
}

function findGuestInboxByAccessToken(PDO $pdo, string $accessToken): ?array
{
    $stmt = $pdo->prepare('
        SELECT *
        FROM inboxes
        WHERE access_token = :access_token
        ORDER BY id DESC
        LIMIT 1
    ');
    $stmt->execute(['access_token' => $accessToken]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function findUserInbox(PDO $pdo, int $userId, string $email): ?array
{
    $stmt = $pdo->prepare(""
        . "SELECT *\n"
        . "FROM inboxes\n"
        . "WHERE (user_id = :user_id AND email_address = :email_address)\n"
        . "   OR (email_address = :email_address_exact)\n"
        . "ORDER BY CASE WHEN user_id = :user_id_exact THEN 0 ELSE 1 END, id ASC\n"
        . "LIMIT 1"
    );
    $stmt->execute([
        'user_id' => $userId,
        'user_id_exact' => $userId,
        'email_address' => strtolower($email),
        'email_address_exact' => strtolower($email),
    ]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function createUserInbox(PDO $pdo, int $userId, string $email): array
{
    $email = strtolower(trim($email));

    $stmt = $pdo->prepare('
        INSERT INTO inboxes (
            user_id,
            email_address,
            type,
            access_token,
            expires_at,
            is_active,
            created_at,
            updated_at
        ) VALUES (
            :user_id,
            :email_address,
            :type,
            NULL,
            NULL,
            1,
            NOW(),
            NOW()
        )
    ');

    $stmt->execute([
        'user_id' => $userId,
        'email_address' => $email,
        'type' => 'user',
    ]);

    $id = (int) $pdo->lastInsertId();
    $created = findUserInbox($pdo, $userId, $email);

    if ($created) {
        return $created;
    }

    return [
        'id' => $id,
        'user_id' => $userId,
        'email_address' => $email,
        'type' => 'user',
        'access_token' => null,
        'expires_at' => null,
        'is_active' => 1,
    ];
}

function ensureUserInbox(PDO $pdo, int $userId, string $email): array
{
    $email = strtolower(trim($email));
    $existing = findUserInbox($pdo, $userId, $email);

    if ($existing) {
        $update = $pdo->prepare('
            UPDATE inboxes
            SET user_id = :user_id,
                email_address = :email_address,
                type = :type,
                access_token = NULL,
                expires_at = NULL,
                is_active = 1,
                updated_at = NOW()
            WHERE id = :id
        ');
        $update->execute([
            'user_id' => $userId,
            'email_address' => $email,
            'type' => 'user',
            'id' => $existing['id'],
        ]);

        $existing['user_id'] = $userId;
        $existing['email_address'] = $email;
        $existing['type'] = 'user';
        $existing['access_token'] = null;
        $existing['expires_at'] = null;
        $existing['is_active'] = 1;
        return $existing;
    }

    return createUserInbox($pdo, $userId, $email);
}

function attachGuestInboxAsAlias(PDO $pdo, int $userId, int $guestInboxId): array
{
    $stmt = $pdo->prepare('
        UPDATE inboxes
        SET user_id = :user_id,
            type = :type,
            access_token = NULL,
            expires_at = NULL,
            is_active = 1,
            updated_at = NOW()
        WHERE id = :id
    ');
    $stmt->execute([
        'user_id' => $userId,
        'type' => 'alias',
        'id' => $guestInboxId,
    ]);

    $fetch = $pdo->prepare('SELECT * FROM inboxes WHERE id = :id LIMIT 1');
    $fetch->execute(['id' => $guestInboxId]);
    $row = $fetch->fetch(PDO::FETCH_ASSOC);
    return $row ?: [];
}

function performGuestInboxTransfer(PDO $pdo, int $userId, string $userEmail, ?string $guestAction, ?string $guestAccessToken): ?array
{
    $action = strtolower(trim((string) $guestAction));
    $guestAccessToken = trim((string) $guestAccessToken);

    if ($guestAccessToken === '' || $action !== 'transfer') {
        return null;
    }

    $guestInbox = findGuestInboxByAccessToken($pdo, $guestAccessToken);
    if (!$guestInbox) {
        throw new RuntimeException('guest_inbox_not_found');
    }

    $guestInboxId = (int) ($guestInbox['id'] ?? 0);
    if ($guestInboxId <= 0) {
        throw new RuntimeException('guest_inbox_not_found');
    }

    $userInbox = ensureUserInbox($pdo, $userId, $userEmail);
    $userInboxId = (int) ($userInbox['id'] ?? 0);
    if ($userInboxId <= 0) {
        throw new RuntimeException('user_inbox_not_found');
    }

    if ($guestInboxId === $userInboxId) {
        return $userInbox;
    }

    if (!empty($guestInbox['user_id']) && (int) $guestInbox['user_id'] !== $userId) {
        throw new RuntimeException('guest_inbox_already_owned');
    }

    $pdo->beginTransaction();
    try {
        $linkedGuestInbox = attachGuestInboxAsAlias($pdo, $userId, $guestInboxId);
        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }

    return $linkedGuestInbox;
}
