From c2268c285648ef02ece04de0d9df0813c6d70ff8 Mon Sep 17 00:00:00 2001
From: benj <benj@rse8.com>
Date: Sat, 24 Dec 2022 00:43:38 -0800
Subject: refactor everything with more abstraction and a nicer interface

---
 crates/secd/store/pg/sql/find_identity.sql | 26 +++++++++++++++-----------
 1 file changed, 15 insertions(+), 11 deletions(-)

(limited to 'crates/secd/store/pg/sql/find_identity.sql')

diff --git a/crates/secd/store/pg/sql/find_identity.sql b/crates/secd/store/pg/sql/find_identity.sql
index 135ff9a..37105cb 100644
--- a/crates/secd/store/pg/sql/find_identity.sql
+++ b/crates/secd/store/pg/sql/find_identity.sql
@@ -1,11 +1,15 @@
-select
-        identity_public_id
-        , data
-        , i.created_at
-        , i.deleted_at
-from secd.identity       i
-join secd.identity_email_validation iev using (identity_id)
-join secd.email_validation          ev  using (email_validation_id)
-join secd.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::text
+       , i.created_at
+       , i.updated_at
+       , i.deleted_at
+from secd.identity i
+left join secd.address_validation av using (identity_id)
+left join secd.address            a  using (address_id)
+left join secd.session            s  using (identity_id)
+where (($1::uuid is null) or (i.identity_public_id = $1))
+and (($2::text is null) or (a.value = $2))
+and (($3::bool is null) or (($3::bool is true) and (av.validated_at is not null)))
+and (($4::bytea is null) or (s.token_hash = $4))
+and i.deleted_at is null;
-- 
cgit v1.2.3

