-- @author tigre@ybos.net
-- @author jennie@ybos.net

begin
     	acs_object_type.create_type (
			  supertype => 'acs_object',
			  object_type => 'mp3',
			  pretty_name => 'MP3',
			  pretty_plural => 'MP3s',
			  table_name => 'MP3_MP3S',
			  id_column => 'MP3_ID'
	);

     	acs_object_type.create_type (
			  supertype => 'acs_object',
			  object_type => 'mp3_playlist',
			  pretty_name => 'Playlist',
			  pretty_plural => 'Playlists',
			  table_name => 'MP3_PLAYLISTS',
			  id_column => 'PLAYLIST_ID'
	);
end;
/

create table mp3_mp3s (
	mp3_id		constraint mp3_mp3_id_fk
			references acs_objects (object_id)
			constraint mp3_mp3s_pk
			primary key,
	file_path	varchar(200)
			constraint mp3_mp3s_nn
			not null
			constraint mp3_mp3s_file_unique
			unique,
	title		varchar(200)
			constraint mp3_mp3s_title_nn
			not null,
	artist		varchar(100),
	bitrate		varchar(50),
	album		varchar(200),
	tracknum	integer,
	genre		varchar(100),
	year		varchar(10),
	layer		integer,
	copyright_p	char(1) default 'f' 
			constraint mp3_mp3s_copyright_p_ck
			check(copyright_p in ('t','f')),
	version		integer,
	mode_num	integer,
	stereo_p	char(1) default 'f' 
			constraint mp3_mp3s_stereo_p_ck
			check(stereo_p in ('t','f')),
	frequency	number,
	minutes		integer,
	seconds		integer,
	comments        varchar(200),
	vbr_p		char(1) default 'f' 
			constraint mp3_mp3s_vbr_p_ck
			check(vbr_p in ('t','f')),
	state		varchar(50),
	deleted_p	char(1) default 'f' 
			constraint mp3_mp3s_deleted_p_ck
			check(deleted_p in ('t','f'))
);

create table mp3_playlists (
	playlist_id	constraint mp3_playlist_id_fk
			references acs_objects (object_id)
			constraint mp3_playlists_pk
			primary key,
	name 		varchar(100),
	shuffle_p	char(1) default 'f'
			constraint mp3_playlists_shuffle_p_ck
			check(shuffle_p in ('f','t')),
	remove_threshold	integer
);

create table mp3_mp3_playlist_map (
	mp3_id		constraint mp3_map_mp3_id_fk
			references mp3_mp3s (mp3_id),
	playlist_id	constraint mp3_map_playlist_id_fk
			references mp3_playlists (playlist_id),
	sort_key	integer,
	-- This pk constraint will have ot be changed, maybe add a column
	-- for the pk, thus allowing multiple instances of a given song
	-- on a playlist
	constraint mp3_map_pk
	primary key (mp3_id,playlist_id)
);

create table mp3_mp3_stats (
	mp3_id		constraint mp3_mp3_stats_mp3_id_fk
			references mp3_mp3s (mp3_id),
	user_id		constraint mp3_mp3_stats_user_id_fk
			references users(user_id),
	access_date	date
			constraint mp3_mp3_stats_access_date_nn
			not null
);

create table mp3_playlist_stats (
	playlist_id	constraint mp3_playlist_stats_playlist_fk
			references mp3_playlists (playlist_id),
	user_id		constraint mp3_playlists_stats_user_id_fk
			references users(user_id),
	access_date	date
			constraint mp3_playlist_stats_date_nn
			not null
);

create table mp3_votes (
	mp3_id		constraint mp3_votes_mp3_id_fk
			references mp3_mp3s (mp3_id),
	playlist_id	constraint mp3_votes_playlist_id_fk
			references mp3_playlists (playlist_id),
	user_id		constraint mp3_votes_user_id_fk
			references users(user_id),
	vote		integer
			constraint mp3_votes_vote_nn
			not null,
	vote_date	date
);

create or replace view mp3_mp3_playlist_map_view as
	select m.mp3_id,		
	       m.playlist_id,
	       m.sort_key,
	       nvl(v.total,0) as total
	from   mp3_mp3_playlist_map m,
	       (select mp3_id,
	               playlist_id,
	               sum(vote) as total
	        from   mp3_votes
                group by mp3_id, playlist_id) v
	where  m.mp3_id = v.mp3_id (+)
	and    m.playlist_id = v.playlist_id (+);

create or replace view mp3_mp3s_not_deleted as
	select *
	from   mp3_mp3s
	where  deleted_p = 'f';

create or replace view mp3_playlist_song_count as
	select mpv.playlist_id, count(*) as songs
	from   mp3_mp3_playlist_map_view mpv,
               mp3_mp3s_not_deleted mnd,
	       mp3_playlists mp
        where  mpv.mp3_id = mnd.mp3_id
        and    mpv.playlist_id = mp.playlist_id
	and    mpv.total > mp.remove_threshold
        group by mpv.playlist_id;

create or replace package mp3
as
	function new (
		mp3_id		in mp3_mp3s.mp3_id%TYPE		default null,
		file_path 	in mp3_mp3s.file_path%TYPE,
		title 		in mp3_mp3s.title%TYPE,
		artist		in mp3_mp3s.artist%TYPE		default null,
		bitrate		in mp3_mp3s.bitrate%TYPE	default null,
		album		in mp3_mp3s.album%TYPE		default null,
		tracknum	in mp3_mp3s.tracknum%TYPE	default null,
		genre		in mp3_mp3s.genre%TYPE		default null,
		year		in mp3_mp3s.year%TYPE		default null,
		layer		in mp3_mp3s.layer%TYPE		default null,
		copyright_p	in mp3_mp3s.copyright_p%TYPE	default null,
		version		in mp3_mp3s.version%TYPE	default null,
		mode_num	in mp3_mp3s.mode_num%TYPE	default null,
		stereo_p	in mp3_mp3s.stereo_p%TYPE		default null,
		frequency	in mp3_mp3s.frequency%TYPE	default null,
		minutes		in mp3_mp3s.minutes%TYPE	default null,
		seconds		in mp3_mp3s.seconds%TYPE	default null,
		comments	in mp3_mp3s.comments%TYPE	default null,
		vbr_p		in mp3_mp3s.vbr_p%TYPE		default null,
		state		in mp3_mp3s.state%TYPE		default null,
		deleted_p	in mp3_mp3s.deleted_p%TYPE	default 'f',
		object_type   	in acs_objects.object_type%TYPE     default 'mp3',
		creation_date 	in acs_objects.creation_date%TYPE   default sysdate,
		creation_user 	in acs_objects.creation_user%TYPE   default null,
		creation_ip   	in acs_objects.creation_ip%TYPE     default null,
		context_id    	in acs_objects.context_id%TYPE      default null
        ) return acs_objects.object_id%TYPE;

	procedure del (
		mp3_id in mp3_mp3s.mp3_id%TYPE
	);

	procedure mark_as_deleted (
		mp3_id in mp3_mp3s.mp3_id%TYPE
	);

end mp3;
/
show errors

create or replace package body mp3
as
	function new (
		mp3_id		in mp3_mp3s.mp3_id%TYPE		default null,
		file_path 	in mp3_mp3s.file_path%TYPE,
		title 		in mp3_mp3s.title%TYPE,
		artist		in mp3_mp3s.artist%TYPE		default null,
		bitrate		in mp3_mp3s.bitrate%TYPE	default null,
		album		in mp3_mp3s.album%TYPE		default null,
		tracknum	in mp3_mp3s.tracknum%TYPE	default null,
		genre		in mp3_mp3s.genre%TYPE		default null,
		year		in mp3_mp3s.year%TYPE		default null,
		layer		in mp3_mp3s.layer%TYPE		default null,
		copyright_p	in mp3_mp3s.copyright_p%TYPE	default null,
		version		in mp3_mp3s.version%TYPE	default null,
		mode_num	in mp3_mp3s.mode_num%TYPE	default null,
		stereo_p	in mp3_mp3s.stereo_p%TYPE	default null,
		frequency	in mp3_mp3s.frequency%TYPE	default null,
		minutes		in mp3_mp3s.minutes%TYPE	default null,
		seconds		in mp3_mp3s.seconds%TYPE	default null,
		comments	in mp3_mp3s.comments%TYPE	default null,
		vbr_p		in mp3_mp3s.vbr_p%TYPE		default null,
		state		in mp3_mp3s.state%TYPE		default null,
		deleted_p	in mp3_mp3s.deleted_p%TYPE	default 'f',
		object_type   	in acs_objects.object_type%TYPE     default 'mp3',
		creation_date 	in acs_objects.creation_date%TYPE   default sysdate,
		creation_user 	in acs_objects.creation_user%TYPE   default null,
		creation_ip   	in acs_objects.creation_ip%TYPE     default null,
		context_id    	in acs_objects.context_id%TYPE      default null
        ) return acs_objects.object_id%TYPE
	is
		v_mp3_id	mp3_mp3s.mp3_id%TYPE;
	begin
		v_mp3_id := acs_object.new (
			object_id => mp3_id,
			object_type => object_type,
			creation_date => creation_date,
			creation_user => creation_user,
			creation_ip => creation_ip,
			context_id => context_id
	    	);		
		insert into mp3_mp3s
			(mp3_id, file_path, title, artist, bitrate, album, tracknum, genre, year, layer,
			copyright_p, version, mode_num, stereo_p, frequency, minutes, seconds, comments,
			vbr_p, state, deleted_p)
		values
			(v_mp3_id, file_path, title, artist, bitrate, album, tracknum, genre,
			year, layer, copyright_p, version, mode_num, stereo_p, frequency,
			minutes, seconds, comments, vbr_p, state, deleted_p);

		return v_mp3_id;	
	end new;



	procedure del (
		mp3_id in mp3_mp3s.mp3_id%TYPE
	)	
	is
	begin
		delete from mp3_mp3s where mp3_id = mp3.del.mp3_id;
		acs_object.del(mp3_id);
	end del;



	procedure mark_as_deleted (
		mp3_id in mp3_mp3s.mp3_id%TYPE
	)
	is
	begin

	update mp3_mp3s 
		set deleted_p ='t'
		where mp3_id = mp3.mark_as_deleted.mp3_id;

	end mark_as_deleted;

end mp3;
/
show errors


create or replace package mp3_playlist
as

	function new (
		playlist_id	in mp3_playlists.playlist_id%TYPE	default null,
		name		in mp3_playlists.name%TYPE		default null,
		shuffle_p	in mp3_playlists.shuffle_p%TYPE		default 'f',
		remove_threshold in mp3_playlists.remove_threshold%TYPE	default null,
		object_type	in acs_objects.object_type%TYPE		default 'mp3_playlist',
		creation_date	in acs_objects.creation_date%TYPE	default sysdate,
		creation_user	in acs_objects.creation_user%TYPE	default null,
		creation_ip	in acs_objects.creation_ip%TYPE		default null,
		context_id	in acs_objects.context_id%TYPE		
        ) return acs_objects.object_id%TYPE;

	procedure del (
		playlist_id	in mp3_playlists.playlist_id%TYPE
	);

	procedure item_add (
		mp3_id		in mp3_mp3s.mp3_id%TYPE,
		playlist_id	in mp3_playlists.playlist_id%TYPE,
		sort_key	in mp3_mp3_playlist_map.sort_key%TYPE	default null
	);

	procedure item_remove (
		mp3_id		in mp3_mp3s.mp3_id%TYPE,
		playlist_id	in mp3_playlists.playlist_id%TYPE
	);

	function vote_cast (
		user_id		in users.user_id%TYPE,
		playlist_id	in mp3_playlists.playlist_id%TYPE,
		mp3_id		in mp3_mp3s.mp3_id%TYPE,
		vote		in mp3_votes.vote%TYPE,
		vote_date	in mp3_votes.vote_date%TYPE		default sysdate
	) return mp3_mp3_playlist_map_view.total%TYPE;

	procedure item_order_swap (
		playlist_id	in mp3_playlists.playlist_id%TYPE,
		sort_key	in mp3_mp3_playlist_map.sort_key%TYPE
	);

end mp3_playlist;
/
show errors



create or replace package body mp3_playlist
as
	function new (
		playlist_id	in mp3_playlists.playlist_id%TYPE	default null,
		name		in mp3_playlists.name%TYPE		default null,
		shuffle_p	in mp3_playlists.shuffle_p%TYPE		default 'f',
		remove_threshold in mp3_playlists.remove_threshold%TYPE	default null,
		object_type	in acs_objects.object_type%TYPE		default 'mp3_playlist',
		creation_date	in acs_objects.creation_date%TYPE	default sysdate,
		creation_user	in acs_objects.creation_user%TYPE	default null,
		creation_ip	in acs_objects.creation_ip%TYPE		default null,
		context_id	in acs_objects.context_id%TYPE		
        ) return acs_objects.object_id%TYPE
	is
		v_playlist_id	mp3_playlists.playlist_id%TYPE;
	begin
		v_playlist_id := acs_object.new (
			object_id => playlist_id,
			object_type => object_type,
			creation_date => creation_date,
			creation_user => creation_user,
			creation_ip => creation_ip,
			context_id => context_id
		);
		insert into mp3_playlists
			(playlist_id, name, shuffle_p, remove_threshold)
		values
			(v_playlist_id, name, shuffle_p, remove_threshold);
		return v_playlist_id;
	end new;

	procedure del (
		playlist_id	in mp3_playlists.playlist_id%TYPE
	)
	is
	begin
		delete from mp3_votes
			where playlist_id = mp3_playlist.del.playlist_id;
		delete from mp3_mp3_playlist_map
			where playlist_id = mp3_playlist.del.playlist_id;
		delete from mp3_playlists
			where playlist_id = mp3_playlist.del.playlist_id;
		acs_object.del(playlist_id);
	end del;

	-- Note: item_add uses "insert after" ordering, so "order" points to
	-- the last record _not_ to be updated
	procedure item_add (
		mp3_id		in mp3_mp3s.mp3_id%TYPE,
		playlist_id	in mp3_playlists.playlist_id%TYPE,
		sort_key	in mp3_mp3_playlist_map.sort_key%TYPE	default null
	)
	is
		cursor map_cur is
			select mmpm.mp3_id, mmpm.playlist_id, mmpm.sort_key
			from   mp3_mp3_playlist_map mmpm, mp3_mp3s_not_deleted mmnd
			where  mmpm.mp3_id = mmnd.mp3_id
			and    mmpm.playlist_id = mp3_playlist.item_add.playlist_id
			and    mmpm.sort_key > mp3_playlist.item_add.sort_key
			order by mmpm.sort_key;
		l_sort_key	mp3_mp3_playlist_map.sort_key%TYPE;
		l_max_sort_key	mp3_mp3_playlist_map.sort_key%TYPE;
		l_new_sort_key	mp3_mp3_playlist_map.sort_key%TYPE;
	begin
		select nvl(max(sort_key),0) into l_max_sort_key
			from mp3_mp3_playlist_map
			where playlist_id = mp3_playlist.item_add.playlist_id;
		if sort_key > l_max_sort_key
		then
			l_sort_key := l_max_sort_key;
		else
			l_sort_key := sort_key;
		end if;
		l_new_sort_key := l_sort_key + 2;
		for map_rec in map_cur
		loop
			update mp3_mp3_playlist_map
				set    sort_key = l_new_sort_key
				where  mp3_id = map_rec.mp3_id
				and    playlist_id = map_rec.playlist_id;
			l_new_sort_key := l_new_sort_key + 1;
		end loop;
		insert into mp3_mp3_playlist_map
			(mp3_id, playlist_id, sort_key)
			values
			(mp3_id, playlist_id, l_sort_key + 1);
	end item_add;

	-- We'll just delete the mapping and it's corresponding votes
	-- We won't worry about gaps in the order
	procedure item_remove (
		mp3_id		in mp3_mp3s.mp3_id%TYPE,
		playlist_id	in mp3_playlists.playlist_id%TYPE
	)
	is
	begin
		delete from mp3_votes
			where  mp3_id = mp3_playlist.item_remove.mp3_id
			and    playlist_id = mp3_playlist.item_remove.playlist_id;
		delete from mp3_mp3_playlist_map
			where  mp3_id = mp3_playlist.item_remove.mp3_id
			and    playlist_id = mp3_playlist.item_remove.playlist_id;
	end item_remove;

	function vote_cast (
		user_id		in users.user_id%TYPE,
		playlist_id	in mp3_playlists.playlist_id%TYPE,
		mp3_id		in mp3_mp3s.mp3_id%TYPE,
		vote		in mp3_votes.vote%TYPE,
		vote_date	in mp3_votes.vote_date%TYPE		default sysdate
	) return mp3_mp3_playlist_map_view.total%TYPE
	is
		l_vote_sum	mp3_votes.vote%TYPE;
	begin
		insert into mp3_votes
			(mp3_id, playlist_id, user_id, vote, vote_date)
			values
			(mp3_id, playlist_id, user_id, vote, vote_date);
		select total into l_vote_sum
			from   mp3_mp3_playlist_map_view
			where  mp3_id = mp3_playlist.vote_cast.mp3_id
			and    playlist_id = mp3_playlist.vote_cast.playlist_id;
		return l_vote_sum;
	end vote_cast;

	-- "swap w/ next"
	procedure item_order_swap (
		playlist_id	in mp3_playlists.playlist_id%TYPE,
		sort_key	in mp3_mp3_playlist_map.sort_key%TYPE
	)
	is
		l_next_order	mp3_mp3_playlist_map.sort_key%TYPE;
	begin
		select min(sort_key) into l_next_order
			from   mp3_mp3_playlist_map m, mp3_mp3s_not_deleted d
			where  m.mp3_id = d.mp3_id
			and    m.sort_key > mp3_playlist.item_order_swap.sort_key;

		update mp3_mp3_playlist_map mmpm
			set    sort_key = decode(mmpm.sort_key,
			                      mp3_playlist.item_order_swap.sort_key,
			                      mp3_playlist.item_order_swap.sort_key + 1,
			                      mp3_playlist.item_order_swap.sort_key)
			where  sort_key in (mp3_playlist.item_order_swap.sort_key,
			                    l_next_order);
	end item_order_swap;

end mp3_playlist;
/
show errors


-- permissions don't quite work with the file system we've set up; most
-- would be useful under /admin, where the admin permission is required. 

--declare
--      default_context acs_objects.object_id%TYPE;
--      registered_users acs_objects.object_id%TYPE;
--      the_public acs_objects.object_id%TYPE;

--begin 
--      acs_privilege.create_privilege('mp3_view_mp3');
--      acs_privilege.create_privilege('mp3_create_mp3');
--      acs_privilege.create_privilege('mp3_delete_mp3');
--      acs_privilege.create_privilege('mp3_modify_mp3');
--      acs_privilege.create_privilege('mp3_view_playlist');
--      acs_privilege.create_privilege('mp3_create_playlist');
--      acs_privilege.create_privilege('mp3_delete_playlist');
--      acs_privilege.create_privilege('mp3_modify_playlist');
--      acs_privilege.create_privilege('mp3_admin_mp3');
--      acs_privilege.create_privilege('mp3_admin_playlist');
--      acs_privilege.create_privilege('mp3_admin');
--      acs_privilege.add_child('mp3_admin_mp3', 'mp3_view_mp3');
--      acs_privilege.add_child('mp3_admin_mp3', 'mp3_create_mp3');
--      acs_privilege.add_child('mp3_admin_mp3', 'mp3_delete_mp3');
--      acs_privilege.add_child('mp3_admin_mp3', 'mp3_modify_mp3');
--      acs_privilege.add_child('mp3_admin_playlist', 'mp3_view_playlist');
--      acs_privilege.add_child('mp3_admin_playlist', 'mp3_create_playlist');
--      acs_privilege.add_child('mp3_admin_playlist', 'mp3_delete_playlist');
--      acs_privilege.add_child('mp3_admin_playlist', 'mp3_modify_playlist'); 
--      acs_privilege.add_child('mp3_admin', 'mp3_admin_playlist');
--      acs_privilege.add_child('mp3_admin', 'mp3_admin_mp3');

--      -- bind privileges to global names

--      acs_privilege.add_child('create','mp3_create_mp3');
--      acs_privilege.add_child('create','mp3_create_playlist');
--      acs_privilege.add_child('write','mp3_modify_mp3');
--      acs_privilege.add_child('write','mp3_modify_playlist');
--      acs_privilege.add_child('read','mp3_view_mp3');
--      acs_privilege.add_child('read','mp3_view_playlist');
--      acs_privilege.add_child('delete','mp3_delete_mp3');
--      acs_privilege.add_child('delete','mp3_delete_playlist');
--      acs_privilege.add_child('admin','mp3_admin');  

--      default_context := acs.magic_object_id('default_context');
--      registered_users := acs.magic_object_id('registered_users');
--      the_public := acs.magic_object_id('the_public'); 

--      -- give the public the power to view playlists by default

--      acs_permission.grant_permission (
--         object_id => acs.magic_object_id('default_context'),
--   	 grantee_id => acs.magic_object_id('the_public'),
--         privilege => 'mp3_view_playlist'
--       );


--      -- give the public the power to view mp3s by default

--      acs_permission.grant_permission (
--         object_id => acs.magic_object_id('default_context'),
--	 grantee_id => acs.magic_object_id('the_public'),
--         privilege => 'mp3_view_mp3'
--      );

-- end;
-- /