create_user_v2.edgeql

START TRANSACTION;
START MIGRATION TO {
    module default {
        type User {
            required property nickname -> str {
                constraint exclusive on (str_lower(__subject__));
            };
            required property email -> str {
                constraint exclusive;
            };
            required property email_verified -> bool {
                default := false;
            };
            required property password -> bytes;
            required property created_at -> datetime {
                default := datetime_current();
                readonly := true;
            };
    
            index on (__subject__.email);
        }

        function create_user(nickname: str, email: str, password: bytes) -> optional User
        {
            volatility := 'VOLATILE';
            using EdgeQL $$
                WITH
                    MODULE default,
                    conflicting := (
                        SELECT User {
                            created_at,
                        }
                        FILTER .nickname = nickname OR .email = email
                        LIMIT 2
                    ),
                    deleted := (
                        DELETE
                        User
                        FILTER
                            User IN conflicting
                            AND NOT .email_verified
                            AND (datetime_of_transaction() - .created_at) > to_duration(hours := 24 * 5)
                    )
                SELECT (
                    FOR _ IN {(
                        SELECT true
                        FILTER count(conflicting) = count(deleted)
                    )}
                    UNION (
                        INSERT User {
                            nickname := nickname,
                            email := email,
                            password := password,
                        }
                    )
                )
            $$
        }
    };
};

POPULATE MIGRATION;
COMMIT MIGRATION;
COMMIT;