use strict;

return sub
{
	my $dbh = shift;
	
	
	my $packScalar = sub
	{
		my $s = shift;
		$s =~ s/([%:\r\n])/sprintf('%%%02X', ord($1))/ge;
		$s;
	};
	
	
	my $sth = $dbh->prepare("select id, commission_aff, commission_charity, commission_charity_uids, commission_charity_pbase from jin_product");
	$sth->execute;
	
	while (my $row = $sth->fetchrow_hashref)
	{
		next
			if ! $row->{commission_aff} && ! $row->{commission_charity} && ! $row->{commission_charity_uids} 
			|| split(':', $row->{commission_aff}, 3) == 3 
			|| split(':', $row->{commission_charity}, 4) == 4;
		
		
		my ($ts, $irr) = split ':', $row->{commission_aff};
		my @commission_aff;
		for my $t (split '/', $ts)
		{
			my ($value, $mode) = $t =~ /([\d\.]+)(%)?/;
			$mode = '$'
				if $value && ! $mode;
			push @commission_aff, join(':', $value, &$packScalar($mode), $irr);
		}
		my $commission_aff = join "\n", @commission_aff;
		
		
		my @commission_charity;
		if ($row->{commission_charity} || $row->{commission_charity_uids})
		{
			my ($comm, $irr) = split ':', $row->{commission_charity};
			my ($value, $mode) = $comm =~ /([\d\.]+)(%)?/;
			
			$mode = '$'
				if $value && ! $mode;
			$mode = '%%'
				if $mode eq '%' && $row->{commission_charity_pbase};
			$mode = &$packScalar($mode);
			
			my @uids = $row->{commission_charity_uids}
				? split(',', $row->{commission_charity_uids}) 
				: ('');
			for my $u (@uids)
			{
				push @commission_charity, join(':', $value, $mode, $u, $irr);
			}
		}
		my $commission_charity = join "\n", @commission_charity;
		
		$dbh->do("UPDATE jin_product SET commission_aff=".$dbh->quote($commission_aff).", commission_charity=".$dbh->quote($commission_charity)." WHERE id=$row->{id}");
	}
	
	
	my $sth = $dbh->prepare("select id, commission_special from jin_affprog");
	$sth->execute;
	
	while (my $row = $sth->fetchrow_hashref)
	{
		next
			if ! $row->{commission_special} || split(':', $row->{commission_special}, 3) == 3;
		
		my ($ts, $irr) = split ':', $row->{commission_special};
		my @commission_special;
		for my $t (split '/', $ts)
		{
			my ($value, $mode) = $t =~ /(\d+)(%)?/;
			$mode = '$'
				if $value && ! $mode;
			push @commission_special, join(':', $value, &$packScalar($mode), $irr);
		}
		my $commission_special = join "\n", @commission_special;
		
		$dbh->do("UPDATE jin_affprog SET commission_special=".$dbh->quote($commission_special)." WHERE id=$row->{id}");
	}
	

	my $remap_tbl = 'jin_upgrade_v2_mems_remap';
	my $urls_tbl = 'jin_upgrade_v2_mems_urls';

	my $sth = $dbh->prepare("show tables like '$remap_tbl'");
	$sth->execute;
	
	unless ($sth->fetchrow)
	{	

		$sth = $dbh->do("delete from zen_id_dbtable");

		
		$sth = $dbh->do(<<END);
create table $remap_tbl
(
  id     int(11) not null primary key,
  old_id int(11) not null default 0,
  type   varchar(50) not null
);
END
	
		$sth = $dbh->do(<<END);
create table $urls_tbl
(
  id  int(11) not null primary key,
  url varchar(255) not null
);
END

		my $maxId = 0;
		my (%remap, %urls);

		my $sth_new_remap = $dbh->prepare("insert into $remap_tbl (id,old_id,type) values (?,?,?)");
		my $sth_new_url = $dbh->prepare("insert into $urls_tbl (id,url) values (?,?)");


		$sth = $dbh->prepare("select * from jin_rmem ORDER BY id");
		$sth->execute;

		my $sthi = $dbh->prepare("insert into jin_membership (type,id,owner_id,resprog_base_obj_id,custom_v1,custom_v2,custom_v3,custom_v4,url,comment,insecure,use_altauth,email_access,email_delete) values ('rmem',?,?,?,?,?,?,?,?,?,?,?,?,?)");

		while (my $row = $sth->fetchrow_hashref)
		{
			$maxId = $row->{id};

			$remap{rmem}{$row->{id}} = $maxId;
			$sth_new_remap->execute($maxId, $row->{id}, 'rmem');
	
			$urls{rmem}{$row->{id}} = $row->{url};
			$sth_new_url->execute($maxId, $row->{url});

			$sthi->execute(map { $row->{$_} } qw/id owner_id resprog_base_obj_id ftp_serv ftp_user ftp_pass ftp_dir url comment insecure use_altauth email_access email_delete/);
		}


		my $jin_url = ($ENV{HTTPS} eq 'on' ? 'https://' : 'http://') . $ENV{HTTP_HOST};

		$sth = $dbh->prepare("select * from jin_lmem");
		$sth->execute;

		my $sthi = $dbh->prepare("insert into jin_membership (type,url,id,owner_id,resprog_base_obj_id,custom_v1,comment,insecure,use_altauth,email_access,email_delete) values ('lmem',?,?,?,?,?,?,?,?,?,?)");

		while (my $row = $sth->fetchrow_hashref)
		{
			$maxId++;

			$remap{lmem}{$row->{id}} = $maxId;
			$sth_new_remap->execute($maxId, $row->{id}, 'lmem');

			$urls{lmem}{$row->{id}} = "$jin_url$row->{path}";
			$sth_new_url->execute($maxId, $urls{lmem}{$row->{id}});

			$sthi->execute($urls{lmem}{$row->{id}}, $maxId, map { $row->{$_} } qw/owner_id resprog_base_obj_id path comment insecure use_altauth email_access email_delete/);		
		}


		$sth = $dbh->prepare("select * from jin_cmsmem");
		$sth->execute;

		$sthi = $dbh->prepare("insert into jin_membership (type,url,id,owner_id,resprog_base_obj_id,custom_int,custom_v1,insecure,email_access,email_delete) values ('extmem',?,?,?,?,?,?,?,?,?)");
		
		while (my $row = $sth->fetchrow_hashref)
		{
			$maxId++;

			$remap{extmem}{$row->{id}} = $maxId;
			$sth_new_remap->execute($maxId, $row->{id}, 'extmem');

			my $sthi1 = $dbh->prepare("select url from jin_ext_script where id=?");
			$sthi1->execute($row->{ext_script});

			my $memURL = $sthi1->fetchrow;
			$memURL =~ s!^((?:https?\://)?[^/]+).*!$1!i;
			$memURL .= $row->{path};

			$urls{extmem}{$row->{id}} = $memURL;
			$sth_new_url->execute($maxId, $memURL);

			$sthi->execute($memURL, $maxId, map { $row->{$_} } qw/owner_id resprog_base_obj_id ext_script path insecure email_access email_delete/);
		}

		
		my $updateTbl = sub
		{
			my ($tbl, $f, %types) = @_;

			my $fields = join(',', 'id', keys %types);
			
			my $sthi = $dbh->prepare("select $fields from $tbl");
			$sthi->execute;

			while (my $row = $sthi->fetchrow_hashref)
			{
				my @ids;

				for my $k (keys %types)
				{
					my $mem_ids = join(',', map { $remap{$types{$k}}{$_} } split(',', $row->{$k}));
					
					push @ids, $mem_ids
						if $mem_ids;
				}

				$dbh->do("update $tbl set $f=? where id=?", undef, join(',', @ids), $row->{id});
			}

			return;
		};

		&$updateTbl('jin_product', 'master_memberships', ('master_rmems' => 'rmem', 'master_lmems' => 'lmem', 'master_cmsmems' => 'extmem'));
		&$updateTbl('jin_product', 'dlv_memberships', ('dlv_rmems' => 'rmem', 'dlv_lmems' => 'lmem', 'dlv_cmsmems' => 'extmem'));
		&$updateTbl('jin_coupon', 'otc_memberships', ('otc_rmems' => 'rmem', 'otc_lmems' => 'lmem', 'otc_cmsmems' => 'extmem'));
		&$updateTbl('jin_specusers', 'u_memberships', ('u_rmems' => 'rmem', 'u_lmems' => 'lmem', 'u_cmsmems' => 'extmem'));
		&$updateTbl('jin_api_user', 'u_memberships', ('u_rmems' => 'rmem', 'u_lmems' => 'lmem', 'u_cmsmems' => 'extmem'));
		&$updateTbl('jin_supro', 'u_memberships', ('u_rmems' => 'rmem', 'u_lmems' => 'lmem', 'u_cmsmems' => 'extmem'));
		&$updateTbl('jin_jvevent', 'membership_id', ('rmem_id' => 'rmem'));
		&$updateTbl('jin_linkdir', 'sponsor_membership', ('sponsor_rmem' => 'rmem'));


		$dbh->do("alter table jin_user_dlv modify id int(11) not null AUTO_INCREMENT");

		$dbh->do(<<END);
insert into jin_user_dlv (type,obj_id,obj_title,expires,owner_id,ctime,user_id,sale_id,expired)
select 'membership',$remap_tbl.id,$urls_tbl.url,expires,owner_id,now(),user_id,sale_id,expired
from jin_user_lmem
left join $remap_tbl on $remap_tbl.old_id=jin_user_lmem.lmem_id and $remap_tbl.type='lmem'
left join $urls_tbl on $urls_tbl.id=$remap_tbl.id
END

		$dbh->do(<<END);
insert into jin_user_dlv (type,obj_id,obj_title,expires,owner_id,ctime,user_id,sale_id,expired)
select 'membership',$remap_tbl.id,$urls_tbl.url,expires,owner_id,ctime,user_id,sale_id,expired
from jin_user_rmem
left join $remap_tbl on $remap_tbl.old_id=jin_user_rmem.rmem_id and $remap_tbl.type='rmem'
left join $urls_tbl on $urls_tbl.id=$remap_tbl.id
END

		$dbh->do(<<END);
insert into jin_user_dlv (type,obj_id,obj_title,expires,owner_id,ctime,user_id,sale_id,expired)
select 'membership',$remap_tbl.id,$urls_tbl.url,expires,owner_id,now(),user_id,sale_id,expired
from jin_user_cmsmem
left join $remap_tbl on $remap_tbl.old_id=jin_user_cmsmem.cmsmem_id and $remap_tbl.type='extmem'
left join $urls_tbl on $urls_tbl.id=$remap_tbl.id
END

		$dbh->do("alter table jin_user_dlv modify id int(11) not null");


		$sth = $dbh->prepare("select * from jin_role");
		$sth->execute;

		while (my $row = $sth->fetchrow_hashref)
		{
			my @mems;

			push @mems, 'rmem'
				if $row->{priv_master_store_rmems};

			push @mems, 'lmem'
				if $row->{priv_master_store_lmems};

			push @mems, 'extmem'
				if $row->{priv_master_store_cmsmems};

			$dbh->do(
				"update jin_role set priv_master_store_memberships=?,priv_master_customers_memberships=?,limit_memberships=? where id=?",
				undef,
				($row->{priv_master_store_rmems} || $row->{priv_master_store_lmems} || $row->{priv_master_store_cmsmems} ? 1 : 0),
				($row->{priv_master_customers_rmems} || $row->{priv_master_customers_lmems} || $row->{priv_master_customers_cmsmems} ? 1 : 0),
				join(',', @mems),
				$row->{id}
			);
		}
	}

	
	my $sth = $dbh->prepare("show tables like 'jin_template_old'");
	$sth->execute;
	if ($sth->fetchrow)
	{
		my %group_id_for_set_id;
		
		
		for my $group_type ('headfoot', 'packord', 'minisite')
		{
			my $sth1 = $dbh->prepare(<<END_SQL);
select s.id as id, s.owner_id as owner_id, s.title as title, s.skin as skin, p.tmpl_set_id as default_set_id
from jin_template_set s
left join jin_template_place p on p.owner_id=s.owner_id and p.tmpl_set_type=s.tmpl_set_type and p.scope=s.scope
inner join jin_user u on u.id=s.owner_id
where s.tmpl_set_type='$group_type' and s.scope='global' order by s.owner_id, s.id
END_SQL
			$sth1->execute;
			
			while (my $set = $sth1->fetchrow_hashref)
			{
				my ($max_id, $group_id, $group_active);
				$sth = $dbh->prepare("select max(id) from jin_template");
				$sth->execute;
				$max_id = $group_id = $sth->fetchrow + 1;
				
				$group_active = $set->{default_set_id} == $set->{id}  ? 1 : 0;
				
				$group_id_for_set_id{$set->{id}} = $max_id
					if $group_type ne 'headfoot';
				
				my $sth2 = $dbh->prepare("select * from jin_template_old where owner_id='$set->{owner_id}' and tmpl_set_id='$set->{id}'");
				$sth2->execute;
				
				while (my $t = $sth2->fetchrow_hashref)
				{
					$dbh->do("insert into jin_template set id='$max_id', owner_id='$set->{owner_id}', group_id='$group_id', group_type='$group_type', group_title=" . $dbh->quote($set->{title}) . ", group_active='$group_active', group_skin='$set->{skin}', tmpl_type='$t->{name}', scope='global', template=" . $dbh->quote($t->{template}));
					$max_id++;
				}
			}
		}
		
		
		my $sth1 = $dbh->prepare("select p.id as id, p.owner_id as owner_id from jin_package p inner join jin_user u on u.id=p.owner_id order by p.owner_id, p.id");
		$sth1->execute;
		while (my $pack = $sth1->fetchrow_hashref)
		{
			my $owner_id = $pack->{owner_id};
			my $scope = "package$pack->{id}";
			
			for my $group_type ('packord', 'minisite')
			{
				$sth = $dbh->prepare("select tmpl_set_id from jin_template_place where owner_id=$owner_id and tmpl_set_type='$group_type' and scope='$scope'");
				$sth->execute;
				my $default_set_id = $sth->fetchrow;
				
				next
					unless $default_set_id;
				
				my ($default_set) = $dbh->selectrow_hashref("select * from jin_template_set where id=$default_set_id and owner_id=$owner_id");
				
				next
					unless $default_set;
				
				my $tmpl_group_id = $default_set->{scope} eq $scope
					? -1 
					: $group_id_for_set_id{$default_set_id};
				$dbh->do("update jin_package set " . ($group_type eq 'minisite' ? 'minisite_' : '') . "tmpl_group_id=$tmpl_group_id where id=$pack->{id}");
				
				next
					unless $default_set->{scope} eq $scope;
				
				my ($max_id, $group_id, $group_active);
				$sth = $dbh->prepare("select max(id) from jin_template");
				$sth->execute;
				$max_id = $group_id = $sth->fetchrow + 1;
				
				my $sth2 = $dbh->prepare("select * from jin_template_old where owner_id=$owner_id and tmpl_set_id='$default_set_id'");
				$sth2->execute;
				
				while (my $t = $sth2->fetchrow_hashref)
				{
					$dbh->do("insert into jin_template set id='$max_id', owner_id='$owner_id', group_id='$group_id', group_type='$group_type', group_title=" . $dbh->quote($default_set->{title}) . ", group_active='0', group_skin='$default_set->{skin}', tmpl_type='$t->{name}', scope='$scope', template=" . $dbh->quote($t->{template}));
					$max_id++;
				}
			}
		}
		
		$sth = $dbh->prepare("select max(id) from jin_template");
		$sth->execute;
		my $max_id = $sth->fetchrow + 0;
		
		$sth = $dbh->prepare("select * from zen_id_dbtable where sqltable='jin_template'");
		$sth->execute;
		$sth->fetchrow
			? $dbh->do("update zen_id_dbtable set curvalue=$max_id where sqltable='jin_template'")
			: $dbh->do("insert into zen_id_dbtable set sqltable='jin_template', sqlfield='id', curvalue=$max_id");
	}

	
	my $sth = $dbh->prepare("select id, template from jin_template where tmpl_type='css_supl'");
	$sth->execute;
	
	while (my $row = $sth->fetchrow_hashref)
	{
		next
			if $row->{template} =~ m/a\.atkListerControlRows/g;
		
		my @old_t = split /(td\.atkListerControlRows[\s\t\n]+\{[^\}]+\})/s, $row->{template};
		
		my ($color) = $old_t[1] =~ /[\n\s\t]+(color:[#\s\w]+;)/g;
		my ($font_weight) = $old_t[1] =~ /(font-weight:[\s\w]+;)/g;
		
		my @new_t;
		push @new_t, $old_t[0] . $old_t[1];
		push @new_t, "\na.atkListerControlRows";
		push @new_t, "{";
		push @new_t, "\t" . $color;
		push @new_t, "\t" . $font_weight;
		push @new_t, "}" . $old_t[2];
		
		$dbh->do("UPDATE jin_template SET template=".$dbh->quote(join "\n", @new_t)." WHERE id=$row->{id}");
	}



	my $sth = $dbh->prepare("select * from jin_membership where type='extmem'");
	$sth->execute;

	my $sthi = $dbh->prepare("update jin_membership set url=? where id=?");
		
	while (my $row = $sth->fetchrow_hashref)
	{
		my $sthi1 = $dbh->prepare("select url from jin_ext_script where id=?");
		$sthi1->execute($row->{custom_int});

		my $memURL = $sthi1->fetchrow;
		$memURL =~ s!^((?:https?\://)?[^/]+).*!$1!i;
		$memURL .= $row->{custom_v1};

		$sthi->execute($memURL, $row->{id});
	}



	my $sth = $dbh->prepare("select * from jin_role where ext_scripts!=''");
	$sth->execute;
	
	my $sthi1 = $dbh->prepare("select * from jin_product where owner_id=? and find_in_set(?,dlv_roles)");
	my $sthi2 = $dbh->prepare("update jin_product set dlv_roles=?, dlv_extscripts=? where id=?");
	my $sthi3 = $dbh->prepare("select * from jin_user_role where owner_id=? and role_id=?");
	my $sthi4 = $dbh->prepare("select url from jin_ext_script where id=?");
	my $sthi5 = $dbh->prepare(<<END);
insert into jin_user_dlv (type,obj_id,obj_title,expires,owner_id,ctime,user_id,sale_id,expired)
select 'extscript',?,?,expires,owner_id,now(),user_id,sale_id,expired
from jin_user_role
where id=?
END
	
	$dbh->do("alter table jin_user_dlv modify id int(11) not null AUTO_INCREMENT");

	
	while (my $role = $sth->fetchrow_hashref)
	{
		$sthi1->execute($role->{owner_id}, $role->{id});
		
		while (my $product = $sthi1->fetchrow_hashref)
		{
			my $roles = join ',', grep { $_ != $role->{id} } split ',', $product->{dlv_roles};
			
			my $extscripts = $product->{dlv_extscripts};
			$extscripts .= $extscripts ? ",$role->{ext_scripts}" : $role->{ext_scripts};
			
			$sthi2->execute($roles, $extscripts, $product->{id});
		}
		
		$sthi3->execute($role->{owner_id}, $role->{id});
		
		while (my $user_role = $sthi3->fetchrow_hashref)
		{
			for my $extscript (split ',', $user_role->{ext_scripts})
			{
				$sthi4->execute($extscript);
			
				my $url = $sthi4->fetchrow;
				
				$sthi5->execute($extscript, $url, $user_role->{id});
			}
		}
	}
	
	$dbh->do("alter table jin_user_dlv modify id int(11) not null");
	$dbh->do("delete from zen_id_dbtable");
	


	$sth = $dbh->prepare("select * from jin_role");
	$sth->execute;
	
	while (my $row = $sth->fetchrow_hashref)
	{
		$dbh->do(
			"update jin_role set priv_master_tools_pieces=?,priv_master_tools_export=? where id=?",
			undef,
			($row->{priv_master_tools_exportpieces} || $row->{priv_master_tools_importpieces} ? 1 : 0),
			($row->{priv_master_tools_export} || $row->{priv_master_tools_exportcomms} ? 1 : 0),
			$row->{id}
		);
	}


	
	my $extscriptDir = $main::In{ScriptDir} . '/kit.jin.plugins/';
	
	unlink("$extscriptDir/ExtScriptCMS.pm", "$extscriptDir/config/ExtScriptCMS.conf");
	
	for my $p (qw/InvisionPB PhpBB SMF VBulletin/)
	{
		my $f = "$extscriptDir/ExtScript$p.pm";
		my $fh;
		open $fh, $f or next;
		my $data;
		read $fh, $data, -s $fh;
		unless ($data =~ /sub suspendUser/)
		{
			my $str = '
sub suspendUser
{
	my $this = shift;
	my $user = shift;
	
	$this->deleteUser($user);
	
	return;
}
';
			$data =~ s!(1;\s*)$!$str$1!;
			open $fh, ">$f" or next;
			print $fh $data;
			close $fh;
		}
	}
	
	return;
};
