--
-- 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]

-- Ported to Postgres by Ben Adida (ben@adida.net)

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

--- create a sub-group in Events for managing activities
select administration_subgroup_add ('Activities Administration', 'activities', 'events', 'activities', 'f', '/admin/events/', 'events');

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

create table event_info (
       group_id integer primary key references user_groups
);

-- 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,
	user_id		integer references users,
        creator_id      integer not null references users,
	short_name	varchar(100) not null,
	default_price   numeric default 0 not null,
	currency	char(3) default 'USD',
	description	varchar(4000),
        -- 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
);

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),
       needs_reserve_p	  char(1) default 'f' check (needs_reserve_p in ('t', 'f')),
       max_people	  integer,	
       description	  varchar(4000)
);

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            datetime not null,
        end_time              datetime not null,
	reg_deadline	      datetime 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	      numeric,
	-- 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		      varchar(4000),
	-- notes for catering
	refreshments_note     varchar(4000),
	-- extra info about this event
	additional_note	      varchar(4000),
	-- besides the web, is there another way to register?
	alternative_reg	      varchar(4000),
        check (start_time < end_time),
	check (reg_deadline <= start_time)
);

-- Each activity can have default custom fields registrants should enter.  
create table events_activity_fields (
	activity_id	integer 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 not null references users" column

-- This table describes the columns that go into event_{$event_id}_info
create table events_event_fields (
	event_id	integer 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
);


-- the organizers for events
create table events_organizers_map (
       event_id		      integer not null references events_events,  
       user_id		      integer not null references users,
       role		      varchar(200) default 'organizer' not null,
       responsibilities	      varchar(4000)
);

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 doesnt want
    -- to use the ecommerce module but still wants to have prices
    price		numeric 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	      datetime not null,
    available_date    datetime not null
);

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	datetime,
	price_charged	numeric,
	-- the date this registration was refunded, if it was refunded
	refunded_date	datetime,
	price_refunded	numeric,	
       	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, refunded
	--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	datetime,
	-- when the registration was shipped
	shipped_date	datetime,
  	org		varchar(4000),
  	title_at_org	varchar(4000),
	attending_reason  varchar(4000),
	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	varchar(4000)
);

-- trigger for recording when a registration ships
create function trig_event_ship_date_trigger()
returns opaque
as '
DECLARE
BEGIN
	IF TG_OP=''INSERT''
	then if NEW.reg_state = ''shipped''
	     then NEW.shipped_date:=sysdate();
	     end if;
	else
		IF OLD.reg_state != ''shipped'' and NEW.reg_state=''shipped''
		THEN
			NEW.shipped_date:= sysdate();
		END IF;
	end if;
	return NEW;
END;
' language 'plpgsql';

create trigger event_ship_date_trigger
before insert or update on events_registrations
for each row
execute procedure trig_event_ship_date_trigger();

create view events_pending_orders
as select * from events_registrations where reg_state='pending';

create view events_waiting_orders
as select * from events_registrations where reg_state='waiting';

-- 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 view events_orders_states_helper
as
select order_id, 
case when (floor (avg (case when reg_state = 'canceled' then 0
				when reg_state = 'waiting' then 1
				when reg_state = 'pending' then 2
				when reg_state = 'shipped' then 3
				else 0 end)))=0 then 'canceled'
     when (floor (avg (case when reg_state = 'canceled' then 0
				when reg_state = 'waiting' then 1
				when reg_state = 'pending' then 2
				when reg_state = 'shipped' then 3
				else 0 end)))=1 then 'incomplete'
     when (floor (avg (case when reg_state = 'canceled' then 0
				when reg_state = 'waiting' then 1
				when reg_state = 'pending' then 2
				when reg_state = 'shipped' then 3
				else 0 end)))=2 then 'incomplete'
     when (floor (avg (case when reg_state = 'canceled' then 0
				when reg_state = 'waiting' then 1
				when reg_state = 'pending' then 2
				when reg_state = 'shipped' then 3
				else 0 end)))=3 then 'fulfilled'
     else 'void' end as order_state
from events_registrations group by order_id;

create view events_orders_states 
as
select  o.*,
o_states.order_state
from events_orders o, events_orders_states_helper o_states
where o_states.order_id = o.order_id;

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

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

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

create sequence events_fs_file_id_seq start 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		integer references users,
	creation_ip_address	varchar(100),
	creation_date		datetime default current_timestamp 
);
	
create index events_file_storage_id_idx on events_file_storage(on_which_table, on_what_id);


--
-- Outer join stuff
-- 
-- BMA

create function events_count_reg_shipped_by_price_id(integer)
returns integer
as '
DECLARE
	v_price_id alias for $1;
BEGIN
	return count(*) from events_reg_shipped where price_id= v_price_id;
END;
' language 'plpgsql';


create function events_count_pending_orders_by_price_id(integer)
returns integer
as '
DECLARE
	v_price_id alias for $1;
BEGIN
	return count(*) from events_pending_orders where price_id= v_price_id;
END;
' language 'plpgsql';


create function events_count_waiting_orders_by_price_id(integer)
returns integer
as '
DECLARE
	v_price_id alias for $1;
BEGIN
	return count(*) from events_waiting_orders where price_id= v_price_id;
END;
' language 'plpgsql';