From c9b6b9a5ca082fe7c1b6f58d7713f785a9eb6a5c Mon Sep 17 00:00:00 2001
From: Martial Simon <msimon_fr@hotmail.com>
Date: Mon, 15 Sep 2025 01:08:27 +0200
Subject: add: graphs et rushs

---
 graphs/sql/corrupted_friends/req01.sql | 18 +++++++++++++++
 graphs/sql/corrupted_friends/req02.sql | 41 ++++++++++++++++++++++++++++++++++
 graphs/sql/corrupted_friends/req03.sql |  2 ++
 3 files changed, 61 insertions(+)
 create mode 100644 graphs/sql/corrupted_friends/req01.sql
 create mode 100644 graphs/sql/corrupted_friends/req02.sql
 create mode 100644 graphs/sql/corrupted_friends/req03.sql

(limited to 'graphs/sql/corrupted_friends')

diff --git a/graphs/sql/corrupted_friends/req01.sql b/graphs/sql/corrupted_friends/req01.sql
new file mode 100644
index 0000000..bc759f7
--- /dev/null
+++ b/graphs/sql/corrupted_friends/req01.sql
@@ -0,0 +1,18 @@
+CREATE TYPE public.prefix AS ENUM (
+    'MRS',
+    'MS',
+    'MR',
+    'DR'
+);
+
+CREATE TABLE dtf.madelines_contacts
+(
+    id INTEGER PRIMARY KEY,
+    title public.prefix,
+    first_name TEXT,
+    last_name TEXT,
+    phone TEXT,
+    email TEXT,
+    favorite BOOLEAN,
+    created_at TIMESTAMP
+);
diff --git a/graphs/sql/corrupted_friends/req02.sql b/graphs/sql/corrupted_friends/req02.sql
new file mode 100644
index 0000000..11f9947
--- /dev/null
+++ b/graphs/sql/corrupted_friends/req02.sql
@@ -0,0 +1,41 @@
+INSERT INTO dtf.madelines_contacts
+SELECT
+id,
+upper(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 1))::public.prefix AS title,
+initcap(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 2)) AS first_name,
+initcap(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 3)) AS last_name,
+concat(
+    lpad(
+        regexp_replace(
+            regexp_replace(phone, '[^0-9\.\-]', '', 'g'),
+            '(.?.?.?)[\.\-].*',
+            '\1',
+            'g'
+        ),
+        3, '0'
+    ),
+    '.',
+    lpad(
+        regexp_replace(
+            regexp_replace(phone, '[^0-9\.\-]', '', 'g'),
+            '.*[\.\-](.?.?.?)[\.\-].*',
+            '\1',
+            'g'
+        ),
+        3, '0'
+    ),
+    '.',
+    lpad(
+        regexp_replace(
+            regexp_replace(phone, '[^0-9\.\-]', '', 'g'),
+            '.*[\.\-](.?.?.?)',
+            '\1',
+            'g'
+        ),
+        3, '0'
+    )
+) AS phone,
+regexp_replace(trim(email), '[^[[:ascii:]]]', '', 'g') AS email,
+trim(favorite) LIKE '1' AS favorite,
+created_at::timestamp AS created_at
+FROM dtf.madelines_contacts_corrupted AS c
diff --git a/graphs/sql/corrupted_friends/req03.sql b/graphs/sql/corrupted_friends/req03.sql
new file mode 100644
index 0000000..baa5863
--- /dev/null
+++ b/graphs/sql/corrupted_friends/req03.sql
@@ -0,0 +1,2 @@
+UPDATE dtf.madelines_contacts
+SET email = concat(lower(first_name), '.', lower(last_name),'@roger_roger.com');
-- 
cgit v1.2.3