-- Create the necessary data model and ACS relationships for the ACS Core UI.
--
-- @author Hiro Iwashima (iwashima@mit.edu)
--
-- @creation-date 28 August 2000
--
-- @cvs-id $Id: acs-subsite-create.sql,v 1.9.10.2 2019/07/26 11:12:11 antoniop Exp $
--

\i portraits.sql
\i email-image.sql
\i application-groups-create.sql
\i subsite-callbacks-create.sql
\i host-node-map-create.sql
\i user-sc-create.sql
\i site-node-selection.sql
\i themes-create.sql

-- DRB: user profiles are fundamentally broken, which is probably why they
-- weren't created in the original ACS 4.2 Oracle sources.
-- \i user-profiles-create.sql

create view party_names
as
select p.party_id,
       (case
         when groups.group_id is null then
	   (case
	     when persons.person_id is null then
	       p.email
	     else
	       persons.first_names || ' ' || persons.last_name
	    end)
         else
	   groups.group_name
       end) as party_name
from ((parties p left outer join groups on p.party_id = groups.group_id)
      left outer join persons on p.party_id = persons.person_id);