(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/pg/sql/write_email_validation.sql b/crates/secd/store/pg/sql/write_email_validation.sql
index d99a04c..ff25b87 100644
--- a/crates/secd/store/pg/sql/write_email_validation.sql
+++ b/crates/secd/store/pg/sql/write_email_validation.sql
@@ -1,27 +1,43 @@
 insert into secd.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 secd.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 secd.identity_email_validation (
+       identity_id
+       , email_validation_id
+       , revoked_at
+       , deleted_at
+) values (
+       (
+       select identity_id
+       from secd.identity
+       where identity_public_id = $1
+       )
+       , (
+         select email_validation_id
+         from secd.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;