BEGIN;

INSERT INTO sql_version (version) VALUES (4);

CREATE VIEW view_search_table AS
 SELECT U.*, N.*,
   ID.internal_id AS id_internal_id,
   ID.external_id AS id_external_id,
   ID.id AS id_id,
   ID.meta AS id_meta,

   E.value AS email_value,
   E.type AS email_type,
   E.is_primary AS email_is_primary,
   E.multi_value_id AS email_multi_value_id,

   C.value AS certificate_value,
   C.type AS certificate_type,
   C.is_primary AS certificate_is_primary,
   C.multi_value_id AS certificate_multi_value_id,

   EN.value AS entitlements_value,
   EN.type AS entitlements_type,
   EN.is_primary AS entitlements_is_primary,
   EN.multi_value_id AS entitlements_multi_value_id,

   I.value AS im_value,
   I.type AS im_type,
   I.is_primary AS im_is_primary,
   I.multi_value_id AS im_multi_value_id,

   PN.value AS phonenumber_value,
   PN.type AS phonenumber_type,
   PN.is_primary AS phonenumber_is_primary,
   PN.multi_value_id AS phonenumber_multi_value_id,

   P.value AS photo_value,
   P.type AS photo_type,
   P.is_primary AS photo_is_primary,
   P.multi_value_id AS photo_multi_value_id,

   R.value AS roles_value,
   R.type AS roles_type,
   R.is_primary AS roles_is_primary,
   R.multi_value_id AS roles_multi_value_id,

   AD.multi_value_id AS address_multi_value_id,
   AD.is_primary AS address_is_primary,
   AD.country AS address_country,
   AD.formatted AS address_formatted,
   AD.locality AS address_locality,
   AD.postal_code AS address_postal_code,
   AD.region AS address_region,
   AD.street_address AS address_street_address,
   AD.type AS address_type,
   G.display_name AS group_display_name,
   G.internal_id AS group_internal_id

 FROM scim_id AS ID
 LEFT JOIN scim_user AS U ON U.internal_id = ID.internal_id
 LEFT JOIN scim_name AS N ON U.name = N.id
 LEFT JOIN scim_group_members AS GM ON GM.members = ID.internal_id
 LEFT JOIN scim_group AS G ON G.internal_id = GM.groups
 LEFT JOIN scim_email AS E ON E.user_internal_id = ID.internal_id
 LEFT JOIN scim_certificate AS C ON C.user_internal_id = ID.internal_id
 LEFT JOIN scim_entitlements AS EN ON EN.user_internal_id = ID.internal_id
 LEFT JOIN scim_im AS I ON I.user_internal_id = ID.internal_id
 LEFT JOIN scim_phonenumber AS PN ON PN.user_internal_id = ID.internal_id
 LEFT JOIN scim_photo AS P ON P.user_internal_id = ID.internal_id
 LEFT JOIN scim_roles AS R ON R.user_internal_id = ID.internal_id
 LEFT JOIN scim_address AS AD ON AD.user_internal_id = ID.internal_id;


-- active
-- display_name
-- locale
-- nick_name
-- password
-- preferred_language
-- profile_url
-- timezone
-- title
-- user_name
-- user_type
-- internal_id
-- name
-- preferred_color
-- id
-- family_name
-- formatted
-- given_name
-- honorific_prefix
-- honorific_suffix
-- middle_name

COMMIT;