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

-- openacs port: Ola Hansson rockola@mail.com

create function inline_0 ()
returns integer as '
begin
    PERFORM acs_object_type__create_type (
	''mp3'',		-- object_type
	''MP3'',		-- pretty_name
	''MP3s'',		-- pretty_plural
	''acs_object'',		-- supertype
	''MP3_MP3S'',		-- table_name
	''MP3_ID'',		-- id_column
	null,			-- package_name
	''f'',			-- abstract_p
	null,			-- type_extension_table
	null			-- name_method


	);

    PERFORM acs_object_type__create_type (
	''mp3_playlist'',	-- object_type
	''Playlist'',		-- pretty_name
	''Playlists'',		-- pretty_plural
	''acs_object'',		-- supertype
	''MP3_PLAYLISTS'',	-- table_name
	''PLAYLIST_ID'',	-- id_column
	null,			-- package_name
	''f'',			-- abstract_p
	null,			-- type_extension_table
	null			-- name_method
	);

    return 0;
end;' language 'plpgsql';

select inline_0 ();
drop function inline_0 ();

create table mp3_mp3s (
	mp3_id		integer
				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	boolean default 'f' 
				constraint mp3_mp3s_copyright_p_ck
				check(copyright_p in ('t','f')),
	version		integer,
	mode_num	integer,
	stereo_p	boolean default 'f' 
				constraint mp3_mp3s_stereo_p_ck
				check(stereo_p in ('t','f')),
	frequency	numeric,
	minutes		integer,
	seconds		integer,
	comments        varchar(200),
	vbr_p		boolean default 'f' 
				constraint mp3_mp3s_vbr_p_ck
				check(vbr_p in ('t','f')),
	state		varchar(50),
	deleted_p	boolean default 'f' 
				constraint mp3_mp3s_deleted_p_ck
				check(deleted_p in ('t','f'))
);

create table mp3_playlists (
	playlist_id	integer
				constraint mp3_playlist_id_fk
				references acs_objects (object_id)
				constraint mp3_playlists_pk
				primary key,
	name 		varchar(100),
	shuffle_p	boolean 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		integer
				constraint mp3_map_mp3_id_fk
				references mp3_mp3s (mp3_id),
	playlist_id	integer
				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		integer
				constraint mp3_mp3_stats_mp3_id_fk
				references mp3_mp3s (mp3_id),
	user_id		integer
				constraint mp3_mp3_stats_user_id_fk
				references users(user_id),
	access_date	timestamptz
				constraint mp3_mp3_stats_access_date_nn
				not null
);

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

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

create view mp3_mp3_playlist_map_view as
	select m.mp3_id,		
	       m.playlist_id,
	       m.sort_key,
	       coalesce(v.total,0) as total
	from   mp3_mp3_playlist_map m
	       left join (select mp3_id,
	               playlist_id,
	               sum(vote) as total
		       from   mp3_votes
		       group by mp3_id, playlist_id) v
	       using (mp3_id, playlist_id);

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

create 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 function mp3__new (integer,varchar,varchar)
returns integer as '
declare
		p_mp3_id		alias for $1;       -- default null
		p_file_path		alias for $2;       -- default null
		p_title			alias for $3;       -- default null
		p_deleted_p		boolean default ''f'';
		p_object_type		varchar default ''mp3'';
		p_creation_date		timestamptz default current_timestamp;
		p_creation_user		integer default null;
		p_creation_ip		varchar default null;
		p_context_id		integer default null;
		v_mp3_id	 mp3_mp3s.mp3_id%TYPE;
begin
		v_mp3_id := acs_object__new (
			p_mp3_id,	    -- object_id
			p_object_type,	    -- object_type
			p_creation_date,    -- creation_date
			p_creation_user,    -- creation_user
			p_creation_ip,	    -- creation_ip
			p_context_id	    -- context_id
	    	);		
		insert into mp3_mp3s
			(mp3_id, file_path, title, deleted_p)
		values
			(v_mp3_id, p_file_path, p_title, p_deleted_p);

		return v_mp3_id;	
end;' language 'plpgsql';


create function mp3__delete (integer)
returns integer as '
declare
		p_mp3_id		alias for $1;
begin
		delete from mp3_mp3s
			   where mp3_id = p_mp3_id;

		raise NOTICE ''Deleting mp3...'';
		PERFORM acs_object__delete(p_mp3_id);

		return 0;
end;' language 'plpgsql';


create function mp3__mark_as_deleted (integer)
returns integer as '
declare
	p_mp3_id				alias for $1;
begin
	update mp3_mp3s 
		set deleted_p =''t''
		where mp3_id = p_mp3_id;

	return 0;
end;' language 'plpgsql';


create function mp3_playlist__new (integer,varchar,boolean,integer,varchar,timestamptz,integer,varchar,integer)
returns integer as '
declare
		p_playlist_id		alias for $1;       -- default null
		p_name			alias for $2;       -- default null
		p_shuffle_p		alias for $3;       -- default ''f''
		p_remove_threshold	alias for $4;       -- default null
		p_object_type		alias for $5;       -- default ''mp3_playlist''
		p_creation_date		alias for $6;       -- default now()
		p_creation_user		alias for $7;       -- default null
		p_creation_ip		alias for $8;       -- default null
		p_context_id		alias for $9;
		v_playlist_id	acs_objects.object_id%TYPE;
begin
		v_playlist_id := acs_object__new (
			p_playlist_id,		 -- object_id
			p_object_type,		 -- object_type
			p_creation_date,	 -- creation_date
			p_creation_user,	 -- creation_user
			p_creation_ip,		 -- creation_ip
			p_context_id		 -- context_id
		);
		insert into mp3_playlists
			(playlist_id, name, shuffle_p, remove_threshold)
		values
			(v_playlist_id, p_name, p_shuffle_p, p_remove_threshold);		

		return v_playlist_id;	
end;' language 'plpgsql';


create function mp3_playlist__delete (integer)
returns integer as '
declare
	p_playlist_id				alias for $1;
begin
	delete from mp3_votes
		where playlist_id = p_playlist_id;
	delete from mp3_mp3_playlist_map
		where playlist_id = p_playlist_id;
	delete from mp3_playlists
		where playlist_id = p_playlist_id;

	raise NOTICE ''Deleting playlist...'';
	PERFORM acs_object__delete(p_playlist_id);

	return 0;
end;' language 'plpgsql';


	-- Note: item_add uses "insert after" ordering, so "order" points to
	-- the last record _not_ to be updated
create function mp3_playlist__item_add (integer,integer,integer)
returns integer as '
declare
	p_mp3_id				alias for $1;	
	p_playlist_id				alias for $2;
	p_sort_key				alias for $3;	-- default null
	v_map_cur RECORD;	-- Generic record used in for loop
	v_l_sort_key		mp3_mp3_playlist_map.sort_key%TYPE;
	v_l_max_sort_key	mp3_mp3_playlist_map.sort_key%TYPE;
	v_l_new_sort_key	mp3_mp3_playlist_map.sort_key%TYPE;

begin
		select coalesce(max(sort_key),0) into v_l_max_sort_key
			from mp3_mp3_playlist_map
			where playlist_id = p_playlist_id;
		if p_sort_key > v_l_max_sort_key
		then
			v_l_sort_key := v_l_max_sort_key;
		else
			v_l_sort_key := p_sort_key;
		end if;
		v_l_new_sort_key := v_l_sort_key + 2;
	FOR v_map_cur IN 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 = p_playlist_id
			and    mmpm.sort_key > p_sort_key
			order by mmpm.sort_key
			LOOP
			update mp3_mp3_playlist_map
				set    sort_key = v_l_new_sort_key
				where  mp3_id = v_map_cur.mp3_id
				and    playlist_id = v_map_cur.playlist_id;
			v_l_new_sort_key := v_l_new_sort_key + 1;
			END LOOP;
			insert into mp3_mp3_playlist_map
			(mp3_id, playlist_id, sort_key)
			values
			(p_mp3_id, p_playlist_id, v_l_sort_key + 1);

		return 0;
end;' language 'plpgsql';


	-- We'll just delete the mapping and it's corresponding votes
	-- We won't worry about gaps in the order
create function mp3_playlist__item_remove (integer,integer)
returns integer as '
declare
		p_mp3_id		alias for $1;
		p_playlist_id		alias for $2;
begin
		delete from mp3_votes
			where  mp3_id = p_mp3_id
			and    playlist_id = p_playlist_id;
		delete from mp3_mp3_playlist_map
			where  mp3_id = p_mp3_id
			and    playlist_id = p_playlist_id;

	return 0;
end;' language 'plpgsql';


create function mp3_playlist__vote_cast (integer,integer,integer,integer,timestamptz)
returns integer as '
declare
		p_user_id		alias for $1;
		p_playlist_id		alias for $2;
		p_mp3_id		alias for $3;
		p_vote			alias for $4;
		p_vote_date		alias for $5;		-- default now()
		v_l_vote_sum	mp3_votes.vote%TYPE;

begin
		insert into mp3_votes
			(mp3_id, playlist_id, user_id, vote, vote_date)
			values
			(p_mp3_id, p_playlist_id, p_user_id, p_vote, p_vote_date);
		select total into v_l_vote_sum
			from   mp3_mp3_playlist_map_view
			where  mp3_id = p_mp3_id
			and    playlist_id = p_playlist_id;
		return v_l_vote_sum;
end;' language 'plpgsql';


	-- "swap w/ next"
create function mp3_playlist__item_order_swap (integer,integer)
returns integer as '
declare
		p_playlist_id		alias for $1;
		p_sort_key		alias for $2;
		v_l_next_order	mp3_mp3_playlist_map.sort_key%TYPE;
begin
		select min(sort_key) into v_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 > p_sort_key;

		update mp3_mp3_playlist_map
			set    sort_key = (case when sort_key = p_sort_key then p_sort_key + 1 else p_sort_key end)
			where  sort_key in (p_sort_key,v_l_next_order);
		return 0;
end;' language 'plpgsql';