--
-- Data model for WimpyPoint II.
-- Even Wimpier than the Original(tm).
--
-- Jon Salz <jsalz@mit.edu>
-- 13 Nov 1999
--
-- (c)1999 Jon Salz
--

-- Add WimpyPoint user group type.
insert into user_group_types(group_type, pretty_name, pretty_plural, approval_policy, default_new_member_policy, group_module_administration)
values('wp', 'WimpyPoint presentation', 'WimpyPoint presentations', 'closed', 'closed', 'none');

create sequence wp_ids;

-- Styles for presentations. We'll think more about this later if there's time -
-- maybe allow ADPs for more flexibility.
create table wp_styles (
	style_id		integer primary key,
	name			varchar(400) not null,
	-- CSS source
	css			lztext,
	-- HTML style properties. Colors are in the form '192,192,255'.
	text_color		varchar(20) check(text_color like '%,%,%'),
	background_color	varchar(20) check(background_color like '%,%,%'),
	background_image	varchar(200),
	link_color		varchar(20) check(link_color like '%,%,%'),
	alink_color		varchar(20) check(alink_color like '%,%,%'),
	vlink_color		varchar(20) check(vlink_color like '%,%,%'),
	-- public? Set directly by administrators - not accessible through web interface
        public_p                boolean not null default 'f',
	-- if provided by a user, his/her ID
	owner			integer references users on delete cascade
);

create index wp_styles_by_owner on wp_styles(owner);

-- Insert the magic, "default" style.
insert into wp_styles(style_id, name, public_p, css)
values(-1, 'Default (Plain)', 't',
       'BODY { background-color: white; color: black } P { line-height: 120% } UL { line-height: 140% }');

-- Images used for styles.
create table wp_style_images (
	style_id	integer not null references wp_styles on delete cascade,
	lob			integer not null references lobs,
--	image		blob not null,
	file_size	integer not null,
	file_name	varchar(200) not null,
	mime_type	varchar(100) not null,
	primary key (style_id, file_name)
);

create function wp_style_image_file_size(integer) returns integer
as '
begin
  return count(file_size) from wp_style_images where style_id = $1;
end;' language 'plpgsql';

create index wp_style_images_idx on wp_style_images(style_id);

-- DRB: PG doesn't allow this foreign key because it's non-standard.
-- The names of the columns in both tables must be the same when
-- you define a table-level foreign key.  I've fixed it using a
-- trigger.

--alter table wp_styles add 
--	foreign key (style_id, background_image) references wp_style_images(style_id, file_name) on delete set null
--;

create trigger wp_style_images_trig before delete or update or insert
on wp_style_images for each row execute procedure on_lob_ref();

create function on_wp_style_image_delete() returns opaque as 
'
begin
  update wp_styles set background_image = null
  where style_id = old.style_id;
  return old;
end;' language 'plpgsql';

create trigger wp_style_images_trig2 before delete on wp_style_images
for each row execute procedure on_wp_style_image_delete();

create table wp_presentations (
	presentation_id		integer primary key,
	-- The title of the presentation, as displayed to the user.
	title			varchar(400) not null,
	-- A signature on the bottom.
	page_signature		varchar(200),
	-- The copyright notice displayed on all pages.
	copyright_notice	varchar(400),
	-- Creation date and user. The creation user always has admin access to
	-- a presentation.
	creation_date		datetime not null,
	creation_user		integer not null references users,
	-- Style information.
	style			integer references wp_styles on delete set null,
	-- Show last-modified date for slides?
	show_modified_p         boolean not null default 'f',
	-- Can the public view the presentation?
	public_p		boolean not null default 't',
	-- Metainformation.
	audience		varchar(4000),
	background		varchar(4000),
	-- The group used for access control on this presentation.
	-- This group should have type 'wp' and group_name = our presentation_id.
	group_id		integer references user_groups
);

create index wp_presentations_by_date on wp_presentations(creation_date);

-- A list of checkpoints (frozen versions of a presentation).
create table wp_checkpoints (
	presentation_id		integer references wp_presentations on delete cascade not null,
	checkpoint		integer not null,
	description		varchar(200),
	checkpoint_date		datetime,
	primary key(presentation_id, checkpoint)
);

-- Slides belonging to presentations. When a slide is created, set checkpoint
-- to the value of wp_presentations.checkpoint.
create table wp_slides (
	slide_id		integer primary key,
	presentation_id 	integer references wp_presentations on delete cascade not null,
	-- The slide_id which this was branched from. Used to preserve comments across
	-- versions.
	original_slide_id	integer references wp_slides on delete set null,
	-- The minimum and maximum checkpoint for which a slide apply.
        -- max_checkpoint = null is the "current" version. To search for
	-- the slide used for checkpoint n, use condition
	--   min_checkpoint <= n and (max_checkpoint is null or max_checkpoint >= n)
	min_checkpoint		integer not null,
	max_checkpoint		integer,
	sort_key		numeric not null,
	title			varchar(400),
	preamble		lztext,
	-- Store bullet items in a Tcl list.
	bullet_items		lztext,
	postamble		lztext,
	include_in_outline_p	boolean not null default 't',
	context_break_after_p	boolean not null default 'f',
	modification_date	datetime not null,
	-- Can override the style setting for the presentation.
	style			integer references wp_styles
);

-- we use alter table because 6.5 doesn't know about foreign keys
alter table wp_slides add
        foreign key (presentation_id, min_checkpoint) references wp_checkpoints;

alter table wp_slides add
	foreign key (presentation_id, max_checkpoint) references wp_checkpoints;

create function wp_slide_id_count(integer) returns integer 
as '
begin
  return count(*) from wp_slides where presentation_id = $1
  and max_checkpoint is null;
end;' language 'plpgsql';

create index wp_sorted_slides on wp_slides(presentation_id, max_checkpoint, sort_key);

-- Keeps track of the sorting order for frozen sets of slides.
create table wp_historical_sort (
	slide_id	integer references wp_slides on delete cascade not null,
        presentation_id integer not null,
        checkpoint      integer not null,        
	sort_key	numeric not null,
	primary key (slide_id, checkpoint),
	foreign key (presentation_id, checkpoint) references wp_checkpoints on delete cascade
);

create index wp_sorted_historical_slides on wp_historical_sort(presentation_id, checkpoint, sort_key);

-- File attachments (including images).
create table wp_attachments (
	attach_id	integer primary key,
	slide_id	integer references wp_slides on delete cascade not null,
	lob			integer not null references lobs,
--	attachment	blob not null,
	file_size	integer not null,
	file_name	varchar(200) not null,
	mime_type	varchar(100) not null,
	-- Display how? null for a link
        display         varchar(20) check(display in ('preamble', 'bullets', 'postamble', 'top', 'after-preamble', 'after-bullets', 'bottom'))
);

create trigger wp_attachments_trig before delete or update or insert
on wp_attachments for each row execute procedure on_lob_ref();

create index wp_attachments_by_slide on wp_attachments(slide_id);

-- A "ticket" which can be redeemed for an ACL entry. Useful for inviting
-- someone to work on a presentation: we generate a ticket, send it to the
-- invitee (along with the secret code), and when the user access WimpyPoint we
-- grant him access based on issued tickets.
create table wp_user_access_ticket (
	invitation_id   integer primary key,
	presentation_id	integer references wp_presentations on delete cascade not null,
	role		varchar(10) not null check (role in('read','write','admin')),
	name            varchar(200) not null,
	email		varchar(200) not null,
	-- secret is null if already redeemed
	secret		varchar(50),
        invite_date     datetime not null,
	invite_user     integer references users on delete cascade not null
);

-- Functions.

create function wp_real_user_p(numeric) returns boolean
AS '
DECLARE
  n_slides alias for $1;
BEGIN
  IF n_slides < 5 THEN
     return ''f'';
  ELSE
     return ''t'';
  END IF;
END;' language 'plpgsql';

create function wp_previous_slide(numeric, integer, integer) returns integer
as '
declare
  v_sort_key alias for $1;
  v_presentation_id alias for $2;
  v_checkpoint alias for $3;
  ret integer;
begin
  if v_checkpoint < 0 then
    select slide_id into ret
    from   wp_slides
    where  presentation_id = v_presentation_id
    and    max_checkpoint is null
    and    sort_key = (select max(sort_key) from wp_slides
                       where  presentation_id = v_presentation_id
                       and    max_checkpoint is null
                       and    sort_key < v_sort_key);
  else
    select slide_id into ret
    from   wp_historical_sort
    where  presentation_id = v_presentation_id
    and    checkpoint = v_checkpoint
    and    sort_key = (select max(sort_key) from wp_historical_sort
                       where  presentation_id = v_presentation_id
                       and    checkpoint = v_checkpoint
                       and    sort_key < v_sort_key);
  end if;
  return ret;
end;' language 'plpgsql';

create function wp_next_slide(numeric, integer, integer) returns integer
as '
declare
  v_sort_key alias for $1;
  v_presentation_id alias for $2;
  v_checkpoint alias for $3;
  ret integer;
begin
  if v_checkpoint < 0 then
    select slide_id into ret
    from   wp_slides
    where  presentation_id = v_presentation_id
    and    max_checkpoint is null
    and    sort_key = (select min(sort_key) from wp_slides
                       where  presentation_id = v_presentation_id
                       and    max_checkpoint is null
                       and    sort_key > v_sort_key);
  else
    select slide_id into ret
    from   wp_historical_sort
    where  presentation_id = v_presentation_id
    and    checkpoint = v_checkpoint
    and    sort_key = (select min(sort_key) from wp_historical_sort
                       where  presentation_id = v_presentation_id
                       and    checkpoint = v_checkpoint
                       and    sort_key > v_sort_key);
  end if;
  return ret;
end;' language 'plpgsql';

-- Turns the read/write/admin role predicate into a numeric (used for ordering).
-- Higher means more access.

-- DRB: Function handler in PG can't handle return of NULL, so we'll
-- return 0 instead

create function wp_role_order(varchar) returns integer
as '
declare
  v_role alias $1;
begin
  if v_role = ''read'' then
    return 1;
  elsif v_role = ''write'' then
    return 2;
  elsif v_role = ''admin'' then
    return 3;
  end if;

  return 0;
end;' language 'plpgsql';

-- Given a min_checkpoint/max_checkpoint pair, determines whether the slide
-- refers to a particular checkpoint. A max_checkpoint of null is considered
-- infinitely high (i.e., the very latest).
create function wp_between_checkpoints_p(integer, integer, integer)
returns char as '
declare
  v_checkpoint alias for $1;
  v_min_checkpoint alias for $2;
  v_max_checkpoint alias for $3;
begin
  if v_checkpoint >= v_min_checkpoint AND (v_max_checkpoint < 0 OR v_checkpoint < v_max_checkpoint) then
    return ''t'';
  end if;

  return ''f'';
end;' language 'plpgsql';

-- Returns the access rights for a presentation. Never returns an access
-- level lower than v_role (e.g., if v_role = 'write' but we only have
-- read access, returns null).

-- DRB: We'll return '' for PG

create function wp_access(integer, integer, varchar, boolean, integer, integer)
returns varchar as '
declare
  v_presentation_id alias for $1;
  v_user_id alias for $2;
  v_role alias for $3;
  v_public_p alias for $4;
  v_creation_user alias for $5;
  v_group_id alias for $6;
  a_role user_group_map.role%TYPE;
begin
  if v_creation_user = v_user_id then
    return ''admin'';
  end if;
  select role into a_role
    from user_group_map
    where group_id = v_group_id
    and user_id = v_user_id;
  if not found then
    a_role := '''';
  end if;
  if v_role = ''write'' and a_role = ''read'' then
    a_role := '''';
  end if;
  if v_role = ''admin'' and a_role <> ''admin'' then
    a_role := '''';
  end if;
  if v_role = ''read'' and v_public_p = ''t'' and a_role = '''' then
    a_role := ''read'';
  end if;
  return a_role;
end;' language 'plpgsql';

-- Reverts to a checkpoint in a presentation.
create function wp_revert_to_checkpoint(integer, integer) returns integer
as '
declare
   v_presentation_id alias for $1;
   v_checkpoint alias for $2;
  duplicate_sort_keys integer;
begin
  -- Fix old versions of slides. If min_checkpoint <= v_checkpoint < max_checkpoint,
  -- the slide is now the most recent.
  update wp_slides
    set max_checkpoint = null
    where presentation_id = v_presentation_id
    and wp_between_checkpoints_p(v_checkpoint, min_checkpoint, max_checkpoint) = ''t'';
  -- Restore sort_keys from wp_historical sort.
  update wp_slides
    set sort_key = (select sort_key
                    from wp_historical_sort h
                    where h.slide_id = wp_slides.slide_id
                    and h.checkpoint = v_checkpoint)
    where presentation_id = v_presentation_id
    and max_checkpoint is null
    and min_checkpoint <= v_checkpoint;
  -- Delete wp_historical_sort info for the current checkpoint.
  delete from wp_historical_sort
    where presentation_id = v_presentation_id
    and checkpoint = v_checkpoint;
  -- Delete hosed slides.
  delete from wp_slides
    where presentation_id = v_presentation_id
    and min_checkpoint > v_checkpoint;
  -- Delete recent checkpoints. "on delete cascade" causes appropriate rows
  -- in wp_historical_sort to be hosed. Gotta love cascading deletes!
  delete from wp_checkpoints
    where presentation_id = v_presentation_id
    and checkpoint > v_checkpoint;
  -- A little sanity checking: make sure sort_keys are unique in the most recent
  -- version now. Use a self-join.

  return 0::integer;

end;' language 'plpgsql';

-- DRB: not sure I can raise an exception in PG, need to check it out
--  select  count(*) into duplicate_sort_keys
--   from  wp_slides s1, wp_slides s2
--    where s1.presentation_id = v_presentation_id
--    and   s2.presentation_id = v_presentation_id
--    and   s1.max_checkpoint is null
--    and   s2.max_checkpoint is null
--    and   s1.sort_key = s2.sort_key
--    and   s1.slide_id <> s2.slide_id;
--  if duplicate_sort_keys <> 0 then
--    raise_application_error(-20000, ''Duplicate sort_keys'');
--  end if;

-- Sets a checkpoint in a presentation.
create function wp_set_checkpoint(integer, varchar) returns integer
as '
declare
   v_presentation_id alias for $1;
   v_description alias for $2;
  latest_checkpoint wp_checkpoints.checkpoint%TYPE;
begin
  select max(checkpoint) into latest_checkpoint
    from  wp_checkpoints
    where presentation_id = v_presentation_id;
  update wp_checkpoints
    set   description = v_description, checkpoint_date = sysdate()
    where presentation_id = v_presentation_id
    and   checkpoint = latest_checkpoint;
  insert into wp_checkpoints(presentation_id, checkpoint)
    values(v_presentation_id, latest_checkpoint + 1);
  -- Save sort order.
  insert into wp_historical_sort(slide_id, presentation_id, checkpoint, sort_key)
    select slide_id, v_presentation_id, latest_checkpoint, sort_key
    from   wp_slides
    where  presentation_id = v_presentation_id
    and    max_checkpoint is null;
  return 0::integer;
end;' language 'plpgsql';

create function wp_migrate_slide(integer, integer) returns integer
as '
declare
  v_presentation_id alias for $1;
  v_slide_id alias for $2;
  latest_checkpoint wp_checkpoints.checkpoint%TYPE;
  should_migrate integer;
  new_slide_id integer;
begin
  select max(checkpoint) into latest_checkpoint
    from  wp_checkpoints
    where presentation_id = v_presentation_id;
  select count(*) into should_migrate
    from wp_slides
    where slide_id = v_slide_id
    and min_checkpoint < (select max(checkpoint) from wp_checkpoints where presentation_id = v_presentation_id)
    and max_checkpoint is null;
  if should_migrate > 0 then
    select nextval(''wp_ids'') into new_slide_id;
    update wp_slides
      set max_checkpoint = latest_checkpoint
      where slide_id = v_slide_id;
    insert into wp_slides(slide_id, presentation_id, modification_date, sort_key, min_checkpoint, include_in_outline_p, context_break_after_p,
                          title, preamble, bullet_items, postamble, original_slide_id)
    select new_slide_id, presentation_id, modification_date, sort_key, latest_checkpoint, include_in_outline_p, context_break_after_p,
           title, preamble, bullet_items, postamble, coalesce(original_slide_id, slide_id)
      from wp_slides
      where slide_id = v_slide_id;
    insert into wp_attachments(attach_id, slide_id, attachment, file_size, file_name, mime_type, display)
      select nextval(''nextval''), new_slide_id, attachment, file_size, file_name, mime_type, display
        from   wp_attachments
        where  slide_id = v_slide_id;
    return new_slide_id;
  else
    return v_slide_id;
  end if;
end;' language 'plpgsql';