delete_conflicting_users_schema.edgeql

START TRANSACTION;
# create user before function because:
# See https://github.com/edgedb/edgedb/issues/1625
START MIGRATION TO {
    module default {
        type User {
            required property created_at -> datetime {
                default := datetime_current();
            };
            required property nickname -> name_type {
                constraint exclusive on (str_lower(__subject__));
            };
            required property email -> email_type {
                constraint exclusive;
            };
            required property email_verified -> bool {
                default := false;
            };
            index on (__subject__.email);
        }
        scalar type name_type extending str {
            constraint min_len_value(4);
            constraint max_len_value(12);
            constraint regexp(r'[a-zA-Z\d]([a-zA-Z\d]|-(?=[a-zA-Z\d])){3,11}');
        }
        scalar type email_type extending str {
            constraint max_len_value(500);
            constraint regexp(r'.+@.+\..+');
        }
    }
};
POPULATE MIGRATION;
COMMIT MIGRATION;

START MIGRATION TO {
    module default {
        type User {
            required property created_at -> datetime {
                default := datetime_current();
            };
            required property nickname -> name_type {
                constraint exclusive on (str_lower(__subject__));
            };
            required property email -> email_type {
                constraint exclusive;
            };
            required property email_verified -> bool {
                default := false;
            };
            index on (__subject__.email);
        }
        scalar type name_type extending str {
            constraint min_len_value(4);
            constraint max_len_value(12);
            constraint regexp(r'[a-zA-Z\d]([a-zA-Z\d]|-(?=[a-zA-Z\d])){3,11}');
        }
        scalar type email_type extending str {
            constraint max_len_value(500);
            constraint regexp(r'.+@.+\..+');
        }
        function delete_conflicting_users(
            nickname: optional name_type,
            email: optional email_type
        ) -> tuple<int64, int64> {
            volatility := 'VOLATILE';
            using EdgeQL $$
                WITH
                    MODULE default,
                    conflicting := (
                        SELECT User
                        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 (count(conflicting), count(deleted))
            $$
        }
    }
};
POPULATE MIGRATION;
COMMIT MIGRATION;
COMMIT;