--
-- data model for events module
-- 
-- re-written by bryanche@arsdigita.com on Feb 02, 2000
-- to support group-based registrations
-- created by bryanche@arsdigita.com on Jan 13, 2000
-- adapted from register.photo.net's chautauqua code

-- we store the ISO code in lower case, e.g,. 'us'

-- if detail_url does not start with "HTTP://" then we assume
-- it is a stub for information on our server and we grab it
-- from the file system, starting at [ns_info pageroot]

create or replace procedure administration_subgroup_add (pretty_name IN
varchar, v_short_name IN varchar, v_module IN varchar, v_submodule IN
varchar, v_multi_role_p IN varchar, v_url IN varchar, 
v_parent_module IN varchar) 
IS
  v_group_id	integer;
  n_administration_groups integer;
  v_system_user_id integer; 
  v_parent_id integer;
BEGIN
  if v_submodule is null then
      select count(group_id) into n_administration_groups
        from administration_info 
        where module = v_module 
        and submodule is null;
      else
	select count(group_id) into n_administration_groups
         from administration_info
         where module = v_module 
         and submodule = v_submodule;
  end if;
  if n_administration_groups = 0 then 
     -- call procedure defined in community-core.sql to get system user
     v_system_user_id := system_user_id;
     select user_group_sequence.nextval into v_group_id from dual;
     insert into user_groups 
      (group_id, group_type, short_name, group_name, creation_user, creation_ip_address, approved_p, existence_public_p, new_member_policy, multi_role_p)
      values
      (v_group_id, 'administration', v_short_name, pretty_name, v_system_user_id, '0.0.0.0', 't', 'f', 'closed', v_multi_role_p);
     insert into administration_info (group_id, module, submodule, url) values (v_group_id, v_module, v_submodule, v_url);
   end if;

   Begin
      select ai.group_id into v_parent_id
      from administration_info ai, user_groups ug
      where ai.module = v_parent_module
      and ai.group_id != v_group_id
      and ug.group_id = ai.group_id
      and ug.parent_group_id is null;
   Exception when others then null;
   End;
   
   update user_groups
   set parent_group_id = v_parent_id
   where group_id = v_group_id;
end;
/
show errors


--- create the administration group for the Events module
begin
   administration_group_add ('Events Administration', 'events', 'events', '', 'f', '/events/admin/'); 
end;
/

-- create a group type of "events"
insert into user_group_types 
(group_type, pretty_name, pretty_plural, approval_policy, group_module_administration, user_group_types_id)
values
('event', 'Event', 'Events', 'closed', 'full', user_group_types_seq.nextval);

create table event_info (
       group_id primary key references user_groups,
	-- the contact person for this event
	contact_user_id	      integer references users
);

insert into user_group_type_fields 
(group_type, column_name, pretty_name, column_type, 
column_actual_type, sort_key)
values
('event', 'contact_user_id', 'Event Contact Person', 'integer', 'integer', 1);

-- can't ever delete an event/activity because it might have been
-- ordered and therefore the row in events_registrations would be hosed
-- so we flag it

create sequence events_activity_id_sequence;

-- the activities
create table events_activities (
	activity_id	integer primary key,
	-- activities are owned by user groups
	group_id	integer references user_groups,
        creator_id      integer not null references users,
	short_name	varchar(100) not null,
	default_price   number default 0 not null,
	currency	char(3) default 'USD',
	description	clob,
        -- Is this activity occurring? If not, we can't assign
        -- any new events to it.
        available_p	char(1) default 't' check (available_p in ('t', 'f')),
        deleted_p	char(1) default 'f' check (deleted_p in ('t', 'f')),
        detail_url 	varchar(256), -- URL for more details
	default_contact_user_id integer references users
);

create sequence events_venues_id_sequence;

-- where the events occur
create table events_venues (
       venue_id		  integer primary key,
       venue_name	  varchar(200) not null,
       address1		  varchar(100),
       address2		  varchar(100),
       city		  varchar(100) not null,
       usps_abbrev	  char(2),
       postal_code	  varchar(20),
       iso		  char(2) default 'us' references country_codes,
       time_zone	  varchar(50),
       -- some contact info for this venue
       fax_number	  varchar(30),
       phone_number	  varchar(30),
       email		  varchar(100),
       needs_reserve_p	  char(1) default 'f' check (needs_reserve_p in ('t', 'f')),
       max_people	  integer,	
       description	  clob
);

create sequence events_event_id_sequence;

-- the events (instances of activities)
create table events_events (
        event_id              integer not null primary key,
        activity_id	      integer not null references events_activities,
	venue_id	      integer not null references events_venues,
	-- the user group that is created for this event's registrants
	group_id	      integer not null references user_groups,
	creator_id	      integer not null references users,
        -- HTML to be displayed after a successful order.
        display_after         varchar(4000),
        -- Date and time.
        start_time            date not null,
        end_time              date not null,
	reg_deadline	      date not null,
        -- An event may have been cancelled.
        available_p	      char(1) default 't' check (available_p in ('t', 'f')),	
        deleted_p	      char(1) default 'f' check (deleted_p in ('t', 'f')),
        max_people	      integer,
	-- can someone cancel his registration?		
	reg_cancellable_p     char(1) default 't' check (reg_cancellable_p in ('t', 'f')),
	-- does a registration need approval to become finalized?
	reg_needs_approval_p  char(1) default 'f' check (reg_needs_approval_p in ('t', 'f')),
	-- notes for doing av setup
	av_note		      clob,
	-- notes for catering
	refreshments_note     clob,
	-- extra info about this event
	additional_note	      clob,
	-- besides the web, is there another way to register?
	alternative_reg	      clob,
        check (start_time < end_time),
	check (reg_deadline <= start_time)
);

create index evnt_evnt_idx on events_events(event_id, activity_id, start_time, end_time);

-- Each activity can have default custom fields registrants should enter.  
create table events_activity_fields (
	activity_id	not null references events_activities,
	column_name	varchar(30) not null,
	pretty_name	varchar(50) not null,
	-- something generic and suitable for handing to AOLserver, 
	-- e.g., boolean or text
	column_type	varchar(50) not null,
	-- something nitty gritty and Oracle-specific, e.g.,
	-- char(1) instead of boolean
	-- things like "not null"
	column_actual_type	varchar(100) not null,
	column_extra	varchar(100),
	-- Sort key for display of columns.
	sort_key	integer not null
);


-- Each event can have custom fields registrants should enter.  The
-- event's custom fields are actually stored in the table,
-- event_{$event_id}_info.  For example, the event with event_id == 5
-- would have a corresponding table of event_5_info.  Furthermore, this
-- table will contain a "user_id integer primary key references users" 
-- column

-- This table describes the columns that go into event_{$event_id}_info
create table events_event_fields (
	event_id	not null references events_events,
	column_name	varchar(30) not null,
	pretty_name	varchar(50) not null,
	-- something generic and suitable for handing to AOLserver, 
	-- e.g., boolean or text
	column_type	varchar(50) not null,
	-- something nitty gritty and Oracle-specific, e.g.,
	-- char(1) instead of boolean
	-- things like "not null"
	column_actual_type	varchar(100) not null,
	column_extra	varchar(100),
	-- Sort key for display of columns.
	sort_key	integer not null
);

-- create default organizer roles for an activity
create sequence events_activity_org_roles_seq start with 1;
create table events_activity_org_roles (
       role_id			integer 
				constraint evnt_act_org_roles_role_id_pk 
				primary key ,
       activity_id		integer 
				constraint evnt_act_role_activity_id_fk 
				references events_activities
				constraint evnt_act_role_activity_id_nn
				not null,  
       role			varchar(200) 
				constraint evnt_act_org_roles_role_nn
				not null,
       responsibilities		clob,
       -- is this a role that we want event registrants to see?
       public_role_p		char(1) default 'f' 
				constraint evnt_act_role_public_role_p
				check (public_role_p in ('t', 'f'))
);

-- create actual organizer roles for each event
create sequence events_event_org_roles_seq start with 1;
create table events_event_organizer_roles (
       role_id			integer 
				constraint evnt_ev_org_roles_role_id_pk 
				primary key,
       event_id			integer 
				constraint evnt_ev_org_roles_event_id_fk 
				references events_events
				constraint evnt_ev_org_roles_event_id_nn
				not null,  
       role			varchar(200) 
				constraint evnt_ev_org_roles_role_nn
				not null,
       responsibilities		clob,
       -- is this a role that we want event registrants to see?
       public_role_p		char(1) default 'f' 
				constraint evnt_ev_roles_public_role_p
				check (public_role_p in ('t', 'f'))
);

create table events_organizers_map (
       user_id			   constraint evnt_org_map_user_id_nn
				   not null
				   constraint evnt_org_map_user_id_fk
				   references users,
       role_id			   integer 
				   constraint evnt_org_map_role_id_nn
				   not null 
				   constraint evnt_org_map_role_id_fk
				   references events_event_organizer_roles,
       constraint events_org_map_pk primary key (user_id, role_id)
);

-- create a view to see event organizer roles and the people in those roles
create or replace view events_organizers 
as
select eor.*, eom.user_id
from events_event_organizer_roles eor, events_organizers_map eom
where eor.role_id=eom.role_id(+);


create sequence events_price_id_sequence;

create table events_prices (
    price_id            integer primary key,
    event_id            integer not null references events_events,
    -- e.g., "Developer", "Student"
    description         varchar(100) not null,
    -- we also store the price here too in case someone doesn't want
    -- to use the ecommerce module but still wants to have prices
    price		number not null,
    -- This is for hooking up to ecommerce.	
    -- Each product is a different price for this event.  For example,
    -- student price and normal price products for an event.
--  product_id          integer references ec_products,
    -- prices may be different for early, normal, late, on-site
    -- admission,
    -- depending on the date
    expire_date	      date not null,
    available_date    date not null
);

create index evnt_price_idx on events_prices(price_id, event_id);

create sequence events_orders_id_sequence;

create table events_orders (
       order_id		integer not null primary key,
--       ec_order_id	integer references ec_orders,
       -- the person who made the order
       user_id		integer not null references users,
       paid_p		char(1) default null check (paid_p in ('t', 'f', null)),
	payment_method	varchar(50),
	confirmed_date	date,
	price_charged	number,
	-- the date this registration was refunded, if it was refunded
	refunded_date	date,
	price_refunded	number,	
       	ip_address	varchar(50) not null
);

create sequence events_reg_id_sequence;

create table events_registrations(
        -- Goes into table at confirmation time:
	reg_id		integer not null primary key,
	order_id	integer not null references events_orders,
	price_id	integer not null references events_prices,
	-- the person registered for this reg_id (may not be the person
	-- who made the order)
	user_id		integer not null references users,
	-- reg_states: pending, shipped, canceled, waiting
	--pending: waiting for approval
	--shipped: registration all set 
	--canceled: registration canceled
	--waiting: registration is wait-listed
	reg_state	varchar(50) not null check (reg_state in ('pending', 'shipped', 'canceled',  'waiting')),
	-- when the registration was made
	reg_date	date,
	-- when the registration was shipped
	shipped_date	date,
	org		varchar(500),
	title_at_org	varchar(500),
	attending_reason  clob,
	where_heard	varchar(4000),
	-- does this person need a hotel?
        need_hotel_p	char(1) default 'f' check (need_hotel_p in ('t', 'f')),
	-- does this person need a rental car?
        need_car_p	char(1) default 'f' check (need_car_p in ('t', 'f')),
	-- does this person need airfare?
	need_plane_p	char(1) default 'f' check (need_plane_p in ('t', 'f')),
	comments	clob
);

create index evnt_reg_idx on events_registrations(reg_id, user_id, price_id, reg_state, org, title_at_org);

-- need this index for speeding up /events/admin/order-history-one.tcl
create index users_last_name_idx on users(lower(last_name), last_name, first_names, email, user_id);

-- trigger for recording when a registration ships
create or replace trigger event_ship_date_trigger
before insert or update on events_registrations
for each row
when (old.reg_state <> 'shipped' and new.reg_state = 'shipped')
begin
	:new.shipped_date := sysdate;
end;
/
show errors

-- create a view that shows order states based upon each order's 
-- registrations.  The order states are:
-- void: All registrations canceled
-- incomplete: This order is not completely fulfilled--some registrations
-- are either canceled, waiting, or pending
-- fulfilled: This order is completely fulfilled
create or replace view events_orders_states 
as
select  o.*,
o_states.order_state
from events_orders o,
 (select
 order_id,
 decode (floor(avg (decode (reg_state, 
 		   'canceled', 0,
		   'waiting', 1,
		   'pending', 2,
		   'shipped', 3,
		   0))),
	     0, 'canceled',
	     1, 'incomplete',
	     2, 'incomplete',
	     3, 'fulfilled',
	     'void') as order_state
 from events_registrations
 group by order_id) o_states
where o_states.order_id = o.order_id;

create or replace view events_reg_not_canceled
as 
select * 
from events_registrations
where reg_state <> 'canceled';

create or replace view events_reg_canceled
as 
select * 
from events_registrations
where reg_state = 'canceled';

create or replace view events_reg_shipped
as
select *
from events_registrations
where reg_state = 'shipped';

create sequence events_fs_file_id_seq start with 1;

create table events_file_storage (
	file_id			integer primary key,
	file_title		varchar(300),
	file_content		blob not null,
	client_file_name		varchar(500),
	file_type		varchar(100),
	file_extension		varchar(50),
	on_which_table		varchar(100) not null,
	on_what_id		integer not null,
	-- the size (kB) of the fileument
	file_size		integer, 
	created_by		references users,
	creation_ip_address	varchar(100),
	creation_date		date default sysdate 
);
	
create index events_file_storage_id_idx on events_file_storage(on_which_table, on_what_id);


-- write functions for completely deleting an event (useful for dev/debug)

-- completely deletes a user group.  Follows /admin/ug/group-delete-2.tcl
create or replace procedure delete_user_group(v_group_id integer)
IS
	sql_stmt			varchar(500);
	v_group_type_table		varchar(20);
BEGIN
	delete from user_group_map_queue 
	where group_id = v_group_id;

	delete from user_group_map
	where group_id = v_group_id;

	-- delete from the user group's info table
	select trim(group_type) || '_info' into v_group_type_table
	from user_groups
	where group_id = v_group_id;

	sql_stmt := 'delete from ' || v_group_type_table || 
	' where group_id = :id';
	EXECUTE IMMEDIATE sql_stmt using v_group_id;
	
	delete from user_group_member_fields where group_id = v_group_id;

	delete from user_group_roles where group_id = v_group_id;

	delete from user_group_action_role_map where group_id = v_group_id;

	delete from user_group_actions where group_id = v_group_id;

	delete from content_section_links
	where from_section_id in (select section_id
			      from content_sections
			      where scope='group'
			      and group_id=v_group_id)
	or to_section_id in (select section_id
			      from content_sections
			      where scope='group'
			      and group_id=v_group_id);

        delete from content_files
	       where section_id in (select section_id
                          from content_sections
                          where scope='group'
                          and group_id=v_group_id);

	delete from content_sections
	where scope='group'
	and group_id=v_group_id;

	delete from faqs
	where scope='group'
	and group_id=v_group_id;

	delete from page_logos
	where scope='group'
	and group_id=v_group_id;

	delete from css_simple
	where scope='group'
	and group_id=v_group_id;

	delete from downloads
	where scope='group'
	and group_id=v_group_id;

	delete from user_groups 
	where group_id = v_group_id;
END delete_user_group;
/
show errors;

-- create a function for deleting an event
-- NOTE: this will delete all the event's registrants too!
create or replace procedure events_delete_event (v_event_id IN integer)
     IS 
	sql_stmt		varchar(500);
	i_group_id		integer;

	-- get all the orders for this event
	cursor c1 is
	select distinct r.order_id
	from events_prices p, events_registrations r
	where p.event_id = v_event_id
	and r.price_id = p.price_id;

	-- get all the organizer roles for this event
	cursor c2 is
	select role_id 
	from events_event_organizer_roles
	where event_id = v_event_id;

     BEGIN
	-- delete all the registrations/orders for this event
	FOR e in c1 LOOP
		delete from events_registrations
		where order_id = e.order_id;

		delete from events_orders
		where order_id = e.order_id;
	END LOOP;

	-- delete the event prices for this event
	delete from events_prices
	where event_id = v_event_id;

	-- get the event's group_id
	select group_id into i_group_id
	from events_events
	where event_id = v_event_id;

	-- delete the event's event fields
	delete from events_event_fields
	where event_id = v_event_id;

	-- delete the event's organizers and roles
	FOR f in c2 LOOP
	    -- delete the organizers with this role
	    delete from events_organizers_map
	    where role_id = f.role_id;

	    -- delete this role
	    delete from events_event_organizer_roles
	    where role_id = f.role_id;
	END LOOP;

	-- drop the event_n_info table
	sql_stmt := 'drop table event_' || v_event_id || '_info';
	EXECUTE IMMEDIATE sql_stmt;

	-- delete the event
	delete from events_events
	where event_id = v_event_id;

	-- delete the event's user group
	delete_user_group(i_group_id);

END events_delete_event;
/
show errors;