(oauth2 + email added): a mess that may or may not really work and needs to be refactored...

1 files changed, 30 insertions, 14 deletions

diff --git a/crates/secd/store/sqlite/sql/write_email_validation.sql b/crates/secd/store/sqlite/sql/write_email_validation.sql
index 37b13e1..d839310 100644
--- a/crates/secd/store/sqlite/sql/write_email_validation.sql
+++ b/crates/secd/store/sqlite/sql/write_email_validation.sql
@@ -1,27 +1,43 @@
 insert into email_validation
        (
            email_validation_public_id
-           , identity_email_id
-           , attempts
+           , email_id
            , code
-           , is_validated
+           , is_oauth_derived
            , created_at
-           , expires_at
+           , validated_at
+           , expired_at
        )
 values (
            ?1
-           , (
-               select identity_email_id
-               from identity_email
-               where identity_id = ?2
-               and email_id = ?3
-           )
+           , ?2
+           , ?3
            , ?4
            , ?5
            , ?6
            , ?7
-           , ?8
 ) on conflict (email_validation_public_id) do update
-  set attempts       = excluded.attempts
-      , is_validated = excluded.is_validated
-      , expires_at   = excluded.expires_at;
+  set validated_at = excluded.validated_at
+      , expired_at   = excluded.expired_at;
+--
+insert into identity_email_validation (
+       identity_id
+       , email_validation_id
+       , revoked_at
+       , deleted_at
+) values (
+       (
+       select identity_id
+       from identity
+       where identity_public_id = ?1
+       )
+       , (
+         select email_validation_id
+         from email_validation
+         where email_validation_public_id = ?2
+         )
+        , ?3
+        , ?4
+) on conflict (identity_id, email_validation_id) do update
+  set revoked_at = excluded.revoked_at
+      , deleted_at = excluded.deleted_at;