alter table as_item_rels drop constraint as_item_rels_item_fk;
alter table as_item_rels drop constraint as_item_rels_target_fk;
alter table as_item_rels add constraint as_item_rels_item_fk foreign key (item_rev_id) references acs_objects(object_id) on delete cascade;
alter table as_item_rels add constraint as_item_rels_target_fk foreign key (target_rev_id) references acs_objects(object_id) on delete cascade;

alter table as_item_section_map drop constraint as_item_smap_i_id_fk;
alter table as_item_section_map drop constraint as_item_smap_s_id_fk;
alter table as_item_section_map add constraint as_item_smap_i_id_fk foreign key (as_item_id) references as_items(as_item_id) on delete cascade;
alter table as_item_section_map add constraint as_item_smap_s_id_fk foreign key (section_id) references as_sections(section_id) on delete cascade;

alter table as_item_choices drop constraint as_item_choices_content_fk;
alter table as_item_choices drop constraint as_item_choices_id_fk;
alter table as_item_choices drop constraint as_item_choices_parent_id_fk;
alter table as_item_choices add constraint as_item_choices_content_fk foreign key (content_value) references cr_revisions(revision_id) on delete cascade;
alter table as_item_choices add constraint as_item_choices_id_fk foreign key (choice_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_choices add constraint as_item_choices_parent_id_fk foreign key (mc_id) references as_item_type_mc(as_item_type_id) on delete cascade;

alter table as_item_sa_answers drop constraint as_item_sa_answ_id_fk;
alter table as_item_sa_answers drop constraint as_item_sa_answ_parent_id_fk;
alter table as_item_sa_answers add constraint as_item_sa_answ_id_fk foreign key (choice_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_sa_answers add constraint as_item_sa_answ_parent_id_fk foreign key (answer_id) references as_item_type_sa(as_item_type_id) on delete cascade;

alter table as_item_help_map drop constraint as_item_help_map_as_item_id_fk;
alter table as_item_help_map drop constraint as_item_help_map_message_id_fk;
alter table as_item_help_map add constraint as_item_help_map_as_item_id_fk foreign key (as_item_id) references as_items(as_item_id) on delete cascade;
alter table as_item_help_map add constraint as_item_help_map_message_id_fk foreign key (message_id) references as_messages(message_id) on delete cascade;

alter table as_sections drop constraint as_sections_display_type_id_fk;
alter table as_sections drop constraint as_sections_section_id_fk;
alter table as_sections add constraint  as_sections_display_type_id_fk foreign key (display_type_id) references as_section_display_types(display_type_id) on delete cascade;
alter table as_sections add constraint as_sections_section_id_fk foreign key (section_id) references cr_revisions(revision_id) on delete cascade;

alter table as_sessions drop constraint as_sessions_assessment_id_fk;
alter table as_sessions drop constraint as_sessions_session_id_fk;
alter table as_sessions drop constraint as_sessions_staff_id_fk;
alter table as_sessions drop constraint as_sessions_subject_id_fk;
alter table as_sessions add constraint as_sessions_assessment_id_fk foreign key (assessment_id) references as_assessments(assessment_id) on delete cascade;
alter table as_sessions add constraint as_sessions_session_id_fk foreign key (session_id) references cr_revisions(revision_id) on delete cascade;
alter table as_sessions add constraint as_sessions_staff_id_fk foreign key (staff_id) references users(user_id) on delete cascade;
alter table as_sessions add constraint as_sessions_subject_id_fk foreign key (subject_id) references persons(person_id) on delete cascade;

alter table as_section_data drop constraint as_section_data_id_fk;
alter table as_section_data add constraint as_section_data_id_fk foreign key (section_data_id) references cr_revisions(revision_id) on delete cascade;
alter table as_section_data drop constraint as_section_data_sect_id_fk;
alter table as_section_data add constraint as_section_data_sect_id_fk foreign key (section_id) references as_sections(section_id) on delete cascade;
alter table as_section_data drop constraint as_section_data_sess_id_fk;
alter table as_section_data add constraint as_section_data_sess_id_fk foreign key (session_id) references as_sessions(session_id) on delete cascade;
alter table as_section_data drop constraint as_section_data_staff_id_fk;
alter table as_section_data add constraint as_section_data_staff_id_fk foreign key (staff_id) references users(user_id) on delete cascade;
alter table as_section_data drop constraint as_section_data_subj_id_fk;
alter table as_section_data add constraint as_section_data_subj_id_fk foreign key (subject_id) references persons(person_id) on delete cascade;

alter table as_item_type_mc drop constraint as_item_type_mc_type_id_fk;
alter table as_item_type_mc add constraint as_item_type_mc_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_type_oq drop constraint as_item_type_oq_type_id_fk;
alter table as_item_type_oq add constraint as_item_type_oq_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_display_rb drop constraint as_item_display_rb_displ_id_fk;
alter table as_item_display_rb add constraint as_item_display_rb_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_display_cb drop constraint as_item_display_cb_displ_id_fk;
alter table as_item_display_cb add constraint as_item_display_cb_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_display_sb drop constraint as_item_display_sb_displ_id_fk;
alter table as_item_display_sb add constraint as_item_display_sb_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_display_tb drop constraint as_item_display_tb_displ_id_fk;
alter table as_item_display_tb add constraint as_item_display_tb_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_display_sa drop constraint as_item_display_sa_displ_id_fk;
alter table as_item_display_sa add constraint as_item_display_sa_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_type_sa drop constraint as_item_type_sa_type_id_fk;
alter table as_item_type_sa add constraint as_item_type_sa_type_id_fk foreign key (as_item_type_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_display_ta drop constraint as_item_display_ta_displ_id_fk;
alter table as_item_display_ta add constraint as_item_display_ta_displ_id_fk foreign key (as_item_display_id) references cr_revisions(revision_id) on delete cascade;
alter table as_items drop constraint as_items_item_id_fk;
alter table as_items add constraint as_items_item_id_fk foreign key (as_item_id) references cr_revisions(revision_id) on delete cascade;
alter table as_section_display_types drop constraint as_section_display_types_id_fk;
alter table as_section_display_types add constraint as_section_display_types_id_fk foreign key (display_type_id) references cr_revisions(revision_id) on delete cascade;
alter table as_assessments drop constraint as_assessments_id_fk;
alter table as_assessments add constraint as_assessments_id_fk foreign key (assessment_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_data drop constraint as_item_data_content_fk;
alter table as_item_data add constraint as_item_data_content_fk foreign key (content_answer) references cr_revisions(revision_id) on delete cascade;
alter table as_item_data drop constraint as_item_data_file_id_fk;
alter table as_item_data add constraint as_item_data_file_id_fk foreign key (file_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_data drop constraint as_item_data_id_fk;
alter table as_item_data add constraint as_item_data_id_fk foreign key (item_data_id) references cr_revisions(revision_id) on delete cascade;
alter table as_item_data drop constraint as_item_data_item_id;
alter table as_item_data add constraint as_item_data_item_id foreign key (as_item_id) references as_items(as_item_id) on delete cascade;
alter table as_item_data drop constraint as_item_data_section_id;
alter table as_item_data add constraint as_item_data_section_id foreign key (section_id) references as_sections(section_id) on delete cascade;
alter table as_item_data drop constraint as_item_data_sess_id_fk;
alter table as_item_data add constraint as_item_data_sess_id_fk foreign key (session_id) references as_sessions(session_id) on delete cascade;
alter table as_item_data drop constraint as_item_data_staff_id_fk;
alter table as_item_data add constraint as_item_data_staff_id_fk foreign key (staff_id) references users(user_id) on delete cascade;
alter table as_item_data drop constraint as_item_data_subj_id_fk;
alter table as_item_data add constraint as_item_data_subj_id_fk foreign key (subject_id) references persons(person_id) on delete cascade;

-- -- File Upload Item
-- create table as_item_type_fu (
-- 	as_item_type_id		integer
-- 				constraint as_item_type_fu_type_id_pk
-- 				primary key
-- 				constraint as_item_type_fu_type_id_fk
-- 				references cr_revisions(revision_id)
--                 on delete cascade
-- );

-- -- File Upload Display Type
-- create table as_item_display_f (
-- 	as_item_display_id	integer
-- 				constraint as_item_display_f_displ_id_pk
-- 				primary key
-- 				constraint as_item_display_f_displ_id_fk
-- 				references cr_revisions(revision_id)
--                 on delete cascade,
-- 	-- field to specify other stuff like textarea dimensions
-- 	html_display_options	varchar(50),
-- 	-- an abstraction of the real size value in "small","medium","large" 
-- 	abs_size		varchar(10),
-- 	-- the orientation between the "question part" of the Item (the title/subtext) and the "answer part" (beside-left, beside-right, bellow, above)
-- 	item_answer_alignment	varchar(20)
-- );

-- alter table as_item_data add file_id integer
-- 		constraint as_item_data_file_id_fk
-- 		references cr_revisions(revision_id)
--                 on delete cascade;

-- insert into as_item_types_map (item_type, display_type)
-- values ('fu', 'f');


create or replace package as_action
as
	function new (
	action_id     	in acs_objects.object_id%TYPE default null,
        name  		in as_actions.name%TYPE,
        description     in as_actions.description%TYPE,
        tcl_code        in as_actions.tcl_code%TYPE,
	context_id	in acs_objects.context_id%TYPE,
	creation_user	in acs_objects.creation_user%TYPE
		     ) return as_actions.action_id%TYPE;
	procedure del (
	 	action_id 	in as_actions.action_id%TYPE
	);
	procedure default_actions (
		context_id	in acs_objects.context_id%TYPE,
		creation_user	in acs_objects.creation_user%TYPE
	);
end as_action;
/
show errors;

create or replace package body  as_action
as
	function new (
	action_id     	in acs_objects.object_id%TYPE default null,
        name  		in as_actions.name%TYPE,
        description     in as_actions.description%TYPE,
        tcl_code        in as_actions.tcl_code%TYPE,
	context_id	in acs_objects.context_id%TYPE,
	creation_user	in acs_objects.creation_user%TYPE
     ) return as_actions.action_id%TYPE
	is
		v_action_id as_actions.action_id%TYPE;
 	
	begin 

	v_action_id := acs_object.new (
			object_id	=> 	action_id,
			object_type	=>	'as_action',
			creation_user	=>	creation_user,
			creation_ip	=>	null,
			context_id	=>	context_id
			);

	insert into as_actions 	(action_id,name,description,tcl_code)
        values (v_action_id,name,description,tcl_code);

        return v_action_id;
	end new;



        procedure del (
		action_id  as_actions.action_id%TYPE 
		) is
	begin

		delete from as_action_params where action_id=as_action.del.action_id;
		delete from as_actions where action_id = as_action.del.action_id;
        	acs_object.del(as_action.del.action_id);	

	end del;	
	

	procedure default_actions (
		context_id	in acs_objects.context_id%TYPE,
		creation_user	in acs_objects.creation_user%TYPE

	) is
		v_action_id		as_actions.action_id%TYPE;

	begin
		
	v_action_id := new (
		action_id	=>	null,
		name		=>	'Register User',
		description	=>	'Register new users',
		tcl_code	=>	'set password [ad_generate_random_string] 
db_transaction {
array set user_new_info [auth::create_user -username $user_name -email $email -first_names $first_names -last_name $last_name -password $password]
}

set admin_user_id [as::actions::get_admin_user_id]
set administration_name [db_string admin_name "select first_names || '' '' || last_name from persons where 
person_id = :admin_user_id"]

set system_name [ad_system_name]
set system_url [ad_parameter -package_id [ad_acs_kernel_id] SystemURL ""].
set admin_email [db_string unused "select email from parties where party_id = :admin_user_id"]
set message "$first_names $last_name,
You have been added as a user to $system_name
at $system_url
Login information:
Email: $email
Password: $password
(you may change your password after you log in)
Thank you,
$administration_name"
ns_sendmail "$email" "$admin_email" "You have been added as a user to [ad_system_name] at [ad_url]" "$message"',
	context_id	=>	context_id,
	creation_user	=>	creation_user
	);


insert into as_action_params (parameter_id, action_id,type, varname, description) 
values (parameter_id_seq.nextval,v_action_id,'n','first_names','First Names of the User');

insert into as_action_params (parameter_id, action_id,type, varname, description) 
values (parameter_id_seq.nextval,v_action_id,'n','last_name','Last Name of the User');

insert into as_action_params (parameter_id, action_id,type, varname, description) 
values (parameter_id_seq.nextval,v_action_id,'n','email','Email of the User');

insert into as_action_params (parameter_id, action_id,type, varname, description) 
values (parameter_id_seq.nextval,v_action_id,'n','user_name','User name of the User');

v_action_id:=  new (
	action_id	=>	null,
	name		=>	'Event Registration',
	description	=>	'Register user to event',
	tcl_code	=>	'set user_id [ad_conn user_id]
events::registration::new -event_id $event_id -user_id $user_id',
	context_id	=>	context_id,
	creation_user	=>	creation_user
	);


insert into as_action_params (parameter_id, action_id,type, varname, description,query) 
values (parameter_id_seq.nextval,v_action_id,'q','event_id','Event to add the user', 'select event_id,event_id from acs_events');

v_action_id:= new (
	action_id	=>	null,
	name		=>	'Add to Community',
	description	=>	'Add user to a community',
	tcl_code	=>	'set user_id [ad_conn user_id]
if { [exists_and_not_null subject_id] } {
	set user_id $subject_id
}
dotlrn_privacy::set_user_guest_p -user_id $user_id -value "t"
dotlrn::user_add -can_browse  -user_id $user_id
dotlrn_community::add_user_to_community -community_id $community_id -user_id $user_id
set community_name [db_string get_community_name { select pretty_name from dotlrn_communities where community_id = :community_id}]

set subject "Your $community_name membership has been approved"
set message "Your $community_name membership has been approved. Please return to [ad_url] to log into [ad_system_name]."

set email_from [ad_parameter -package_id [ad_acs_kernel_id] SystemOwner]

db_1row select_user_info { select email, first_names, last_name from registered_users where user_id = :user_id}

if [catch {ns_sendmail $email $email_from $subject $message} errmsg] {
         ad_return_error \
        "Error sending mail" \
        "There was an error sending email to $email."
}',
	context_id	=>	context_id,
	creation_user	=>	creation_user
	);


insert into as_action_params (parameter_id, action_id,type, varname, description,query) 
values (parameter_id_seq.nextval,v_action_id,'q','community_id','Community to add the user', 
'select pretty_name,community_id from dotlrn_communities');


	end default_actions;
	
end as_action;
/
show errors;
	
alter table as_session_items drop constraint as_sess_items_item_fk;
alter table as_session_items drop constraint as_sess_items_section_fk;
alter table as_session_items drop constraint as_sess_items_session_fk;
alter table as_session_items add constraint "as_sess_items_item_fk" FOREIGN KEY (as_item_id) REFERENCES as_items(as_item_id) on delete cascade;
alter table as_session_items add constraint "as_sess_items_section_fk" FOREIGN KEY (section_id) REFERENCES as_sections(section_id) on delete cascade;
alter table as_session_items add constraint "as_sess_items_session_fk" FOREIGN KEY (session_id) REFERENCES as_sessions(session_id) on delete cascade;
