-- View: sonex.v_accounts

-- DROP VIEW sonex.v_accounts;

CREATE OR REPLACE VIEW sonex.v_accounts
 AS
 WITH base_accounts AS (
         SELECT r.oid AS account_role_oid,
            r.rolname AS account_name,
            r.rolcanlogin AS is_enabled,
            (EXISTS ( SELECT 1
                   FROM pg_auth_members sm
                     JOIN pg_roles sr ON sr.oid = sm.roleid
                  WHERE sm.member = r.oid AND sr.rolname = 'super_admin'::name)) AS superadmin
           FROM pg_roles r
             JOIN pg_auth_members m ON m.member = r.oid
             JOIN pg_roles parent_role ON parent_role.oid = m.roleid
          WHERE parent_role.rolname = 'account'::name
        ), account_groups AS (
         SELECT m.member AS account_role_oid,
            array_agg(grp.rolname ORDER BY grp.rolname) AS groups
           FROM pg_auth_members m
             JOIN pg_roles grp ON grp.oid = m.roleid
          WHERE grp.rolname ~~ like_escape('group\_%'::text, '\'::text)
          GROUP BY m.member
        ), direct_permissions AS (
         SELECT m.member AS account_role_oid,
            array_agg(p.rolname ORDER BY p.rolname) AS permissions_direct
           FROM pg_auth_members m
             JOIN pg_roles p ON p.oid = m.roleid
          WHERE p.rolname ~~ like_escape('permission\_%'::text, '\'::text)
          GROUP BY m.member
        ), group_permissions_raw AS (
         SELECT account_members.member AS account_role_oid,
            permission_role.rolname AS permission_name
           FROM pg_auth_members account_members
             JOIN pg_roles group_role ON group_role.oid = account_members.roleid
             JOIN pg_auth_members group_members ON group_members.member = group_role.oid
             JOIN pg_roles permission_role ON permission_role.oid = group_members.roleid
          WHERE group_role.rolname ~~ like_escape('group\_%'::text, '\'::text) AND permission_role.rolname ~~ like_escape('permission\_%'::text, '\'::text)
        ), all_permissions AS (
         SELECT b_1.account_role_oid,
            array_agg(p.permission_name ORDER BY p.permission_name) AS permissions_all
           FROM base_accounts b_1
             LEFT JOIN ( SELECT d.account_role_oid,
                    unnest(d.permissions_direct) AS permission_name
                   FROM direct_permissions d
                UNION
                 SELECT DISTINCT g_1.account_role_oid,
                    g_1.permission_name
                   FROM group_permissions_raw g_1) p ON p.account_role_oid = b_1.account_role_oid
          GROUP BY b_1.account_role_oid
        )
 SELECT a.id,
    b.account_name,
    a.first_name,
    a.last_name,
    a.initials,
    a.email,
    a.phone_number,
    a.department,
    a.job_title,
    a.native_language,
    a.other_languages,
    a.account_locus_wms,
    a.account_locus_terminal,
    a.account_orp,
    a.account_bk,
    a.account_dpd,
    a.account_xenos,
    a.tags,
    a.notes,
    a.created_at,
    a.updated_at,
    b.is_enabled,
    b.superadmin,
    COALESCE(g.groups, ARRAY[]::name[]) AS groups,
    COALESCE(dp.permissions_direct, ARRAY[]::name[]) AS permissions_direct,
    COALESCE(ap.permissions_all, ARRAY[]::name[]) AS permissions_all
   FROM base_accounts b
     LEFT JOIN sonex.accounts a ON a.account_name::text = b.account_name
     LEFT JOIN account_groups g ON g.account_role_oid = b.account_role_oid
     LEFT JOIN direct_permissions dp ON dp.account_role_oid = b.account_role_oid
     LEFT JOIN all_permissions ap ON ap.account_role_oid = b.account_role_oid;

ALTER TABLE sonex.v_accounts
    OWNER TO postgres;

GRANT SELECT ON TABLE sonex.v_accounts TO permission_accounts_create;
GRANT SELECT ON TABLE sonex.v_accounts TO permission_accounts_delete;
GRANT SELECT ON TABLE sonex.v_accounts TO permission_accounts_edit;
GRANT SELECT ON TABLE sonex.v_accounts TO permission_accounts_view;
GRANT ALL ON TABLE sonex.v_accounts TO postgres WITH GRANT OPTION;
GRANT INSERT, DELETE, SELECT, UPDATE ON TABLE sonex.v_accounts TO sonex;

