-- 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.12 2026/11/18 08:09:46 gustafn 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);

create table subsite_email_changes (
    user_id integer constraint subsite_email_changes_pk primary key
                    constraint subsite_email_changes_fk references users(user_id) on delete cascade,
    old_email varchar(100),
    new_email varchar(100) not null,
    token_digest varchar(64) not null,
    expires_at numeric(12) not null
);