refactor everything with more abstraction and a nicer interface

1 files changed, 15 insertions, 11 deletions

diff --git a/crates/secd/store/sqlite/sql/find_identity.sql b/crates/secd/store/sqlite/sql/find_identity.sql
index f94e7b1..1528407 100644
--- a/crates/secd/store/sqlite/sql/find_identity.sql
+++ b/crates/secd/store/sqlite/sql/find_identity.sql
@@ -1,11 +1,15 @@
-select
-        identity_public_id
-        , data
-        , i.created_at
-        , i.deleted_at
-from identity       i
-join identity_email_validation iev using (identity_id)
-join email_validation          ev  using (email_validation_id)
-join email                     e   using (email_id)
-where ((?1 is null) or (i.identity_public_id = ?1))
-and   ((?2 is null) or (e.address = ?2));
+select distinct
+       identity_public_id
+       , data
+       , i.created_at
+       , i.updated_at
+       , i.deleted_at
+from identity i
+left join address_validation av using (identity_id)
+left join address            a  using (address_id)
+left join session            s  using (identity_id)
+where (($1 is null) or (i.identity_public_id = $1))
+and (($2 is null) or (a.value = $2))
+and (($3 is null) or (($3 is true) and (av.validated_at is not null)))
+and (($4 is null) or (s.token_hash = $4))
+and i.deleted_at is null;