-- script to migrate users from acs3.x to acs40
-- mikeb - 12/6/2000
-- run as oracle (dba)

-- must run this grant before running script
-- connect acs3x/acs3x_password;
-- grant select on users to acs4x;

--connect acs4x/acs4x_password;

-- run this DDL once before running script
create table user_bios (
	user_id
	constraint user_bios_user_id_fk references users,
	bio varchar2(4000) );

declare
	v_user_id integer;
	v_item_id integer;
	v_rel_id integer;
	v_revision_id integer;
	counter integer := 0;
begin
for row in (select * from acs3x.users where lower(email) not in (select lower(email) from parties where email is not null)) loop
	-- create a new user
	dbms_output.put_line('adding ' || row.email);
	v_user_id := acs.add_user(email => row.email,
	      	           first_names => row.first_names,
		           last_name => row.last_name,
	    	           password => 'E6758A3F6B4CBCD202285BC96C0E40DFDE3CDFF8',
		           salt => '0.5',
		           screen_name => row.screen_name);

	-- copy the portrait over
	if dbms_lob.getlength(row.portrait) is not null then
		v_item_id := content_item.new(name => 'portrait-of-user-' || v_user_id);
		v_rel_id := acs_rel.new (rel_type => 'user_portrait_rel',
       	  			 object_id_one => v_user_id,
         			 object_id_two => v_item_id);
		v_revision_id := content_revision.new(title => NULL,
     					      description => row.portrait_comment,
     					      text => 'not_important',
     					      mime_type => row.portrait_file_type,
     					      item_id => v_item_id,
     					      creation_user => v_user_id);
		update cr_items
  		   set live_revision = v_revision_id
       	         where item_id = v_item_id;

	        update cr_revisions
	           set content = empty_blob()
	         where revision_id = v_revision_id;
		update cr_revisions
		   set content = row.portrait
	         where revision_id = v_revision_id;
		insert into images
	          (image_id, width, height)
	         values
	          (v_revision_id, row.portrait_original_width, row.portrait_original_height);

	end if;

	-- save user bios
	insert into user_bios
	  (user_id, bio)
	 values
	  (v_user_id, row.bio);

	counter := counter + 1;

end loop;
dbms_output.put_line(counter || ' rows processed.');
end;
/

-- run this once, after the new users have been created successfully
 create table user_party_map as 
   (select users.user_id, parties.party_id, lower(parties.email) as email from acs3x.users, parties where lower(users.email) = lower(parties.email));

-- used this to bring over ACS3.4 passwords
update users u set password = (select password from acs3x.users where user_id = (select user_id from user_party_map where party_id = u.user_id))
  where user_id in (select party_id from user_party_map);

-- replaces the full-size portraits with thumbnail portraits
declare
  v_rev_id integer;
  counter integer := 0;
begin

for row in (select user_id, party_id from user_party_map where exists (select 1 from acs_rels where object_id_one = party_id and rel_type = 'user_portrait_rel')) loop
    select live_revision into v_rev_id
      from acs_rels a, cr_items c
     where a.object_id_one = row.party_id
       and a.object_id_two = c.item_id
       and a.rel_type = 'user_portrait_rel';

    update cr_revisions
       set content = (select portrait_thumbnail from acs3x.users where user_id = row.user_id)
     where revision_id = v_rev_id;

    update images 
       set width = (select portrait_thumbnail_width from acs3x.users where user_id = row.user_id),
           height = (select portrait_thumbnail_height from acs3x.users where user_id = row.user_id)
     where image_id = v_rev_id;

    counter := counter + 1;
end loop;
  dbms_output.put_line('Processed ' || counter || ' rows.');
end;
/