--
-- upgrade a 2.2 ACS to 2.3 
--
-- started by philg@mit.edu ojn 9/24/99
--

-- for the user profiling system

alter table categories add (
	category_type	varchar(50),
	parent_category_id  integer references categories,
	profiling_weight	number default 1 check(profiling_weight >= 0));

create index categories_parent_cat_id_idx on categories(parent_category_id);

alter table users_interests add
  (interest_level	integer default 5 check(interest_level between -10 and 10));

create table crm_states (
	state_name	varchar(50) not null primary key,
	description	varchar(1000) not null -- for UI
);

create table crm_state_transitions (
	state_name	not null references crm_states,
	next_state	not null references crm_states,
	triggering_order	integer not null,
	-- a SQL fragment which will get called as:
	-- update users set crm_state = <next_state>, crm_state_entered_date = sysdate where user_state = <state_name> and (<transition_condition>)
	transition_condition	varchar(500) not null,
	primary key (state_name, next_state)
);

-- add the CRM and also portraits fields to the users table

alter table users add (
	crm_state		references crm_states,
	crm_state_entered_date	date, -- when the current state was entered
	portrait		blob,
	portrait_upload_date	date,
	portrait_comment	varchar(4000),
	-- file name including extension but not path
	portrait_client_file_name	varchar(500),
	portrait_file_type			varchar(100),	-- this is a MIME type (e.g., image/jpeg)
	portrait_file_extension		varchar(50), 	-- e.g., "jpg"
	portrait_original_width		integer,
	portrait_original_height	integer,
	-- if our server is smart enough (e.g., has ImageMagick loaded)
	-- we'll try to stuff the thumbnail column with something smaller
	portrait_thumbnail		blob,
	portrait_thumbnail_width	integer,
	portrait_thumbnail_height	integer
);

create index users_by_crm_state on users (crm_state);

alter table general_comments_audit add (one_line varchar(200));

--- dynamic user groupings
create sequence user_class_id_seq;

create table user_classes (
	user_class_id    integer primary key,
	name		 varchar(200) unique,
	description      varchar(4000),
	-- this query was written by our tcl procs, we'll
	-- have an autogenerated description describing what it means.
	sql_description   varchar(1000),
	-- The sql that will follow the select clause.
	-- for example, sql_post_select_list for 'select count(user_id) from
	-- users' would be 'from users'.
	-- We record this fragment instead of the complete sql
	-- query so we can select a count of desired columns as desired.
	sql_post_select    varchar(4000)	
);