ALTER TABLE users
ADD COLUMN org_email VARCHAR(255);


ALTER TABLE users
ADD COLUMN source VARCHAR(255),
ADD COLUMN action VARCHAR(255);


UPDATE users u
SET 
    org_email = r.email, 
    legal_full_name = r.name,
    full_name = r.name,
    user_type = 'Org',
    gender = r.gender,
    address = CASE 
                WHEN r.location ILIKE '%chennai%' THEN 'Chennai'
                ELSE r.location
              END,
    source = 'ORG_LIST',
    action = 'UPDATE'
FROM zz_ref_org_users_list r
WHERE u.phone_number = r.mobile;



-- 9841679090
-- update all for this user with r.

UPDATE users u
SET 
    org_email = r.email, 
    email = r.email,
    legal_full_name = r.name,
    full_name = r.name,
    user_type = 'Org',
    gender = r.gender,
    dob = r.dob,
    address = CASE 
                WHEN r.location ILIKE '%chennai%' THEN 'Chennai'
                ELSE r.location
              END,
    source = 'ORG_LIST',
    action = 'UPDATE'
FROM zz_ref_org_users_list r
WHERE u.phone_number = '9841679090' and u.phone_number = r.mobile;

SELECT 
    u.email AS "portal email",
    r.email AS "org email",
	u.org_email as "org eeemail",
    r.hdb_msd_registered_email AS "org hdb msd registered email",
    u.phone_number AS "portal number",
    r.mobile AS "org number",
    u.legal_full_name AS "portal legal name",
    r.name AS "org name",
    u.address AS "portal location",
    r.location AS "org location",
    u.gender AS "portal gender",
    r.gender AS "org gender",
    u.dob AS "portal dob",
    r.dob AS "org dob",
    CASE 
        WHEN (lower(u.email) = lower(r.email) OR lower(u.email) = lower(r.hdb_msd_registered_email)) 
        THEN true 
        ELSE false 
    END AS email_match,
    CASE WHEN u.phone_number = r.mobile THEN true ELSE false END AS mobile_match,
    CASE WHEN u.legal_full_name = r.name THEN true ELSE false END AS name_match,
    CASE WHEN u.address = r.location THEN true ELSE false END AS location_match,
    CASE WHEN u.gender = r.gender THEN true ELSE false END AS gender_match,
    CASE WHEN u.dob = r.dob THEN true ELSE false END AS dob_match
FROM users u
INNER JOIN zz_ref_org_users_list r
    ON u.email = r.email 
    OR u.phone_number = r.mobile 
    OR u.legal_full_name = r.name;