#!/usr/bin/perl
require "/home/httpd/vhosts/trconnection.com/private/lyrics.lib";
&createTables;
dbmopen(%LYRICS,"/home/httpd/vhosts/trconnection.com/private/db/lyrics",0644);
%groups=("Nazz",1,"Todd Rundgren",2,"TR-i",3,"Todd Rundgren\\'s Utopia",4,"Utopia",5,"The New Cars",6,"Other Projects",7);
print "DELETE from title;\n";
@titles=grep(/^Title/,keys %LYRICS);
foreach $title(@titles){
	@titleinfo=();
	($key,$id)=split(/\./,$title);
	gettitleinfo($LYRICS{$title});
	for($t=1;$t<=$#titleinfotype;$t++){
		($type=$titleinfotype[$t])=~s/ //g;
		($value=$titleinfo[$titleinfotype{$type}])=~s/'/\\'/g;
		$value=~s/\r//g;
		if($type eq "Title"){
			($grouping,$title)=split(/: /,$value);
			print qq{INSERT title set id=$id,\n\tTitle='$title',\n};
		}
		elsif($type eq "Duration"){
			if($value){
				($minute,$second)=split(/:/,$value);
				$hour=int($minute/60);
				$minute=$minute%60;
				$value="$hour:$minute:$second";
			}
			print "\t$type='$value',\n";
		}
		else{print "\t$type='$value',\n";}
	}
	if($bg=$LYRICS{"BGTitle.$id"}){print qq{\tBackground='$bg'\n,};}
	print qq{\tTitleSortOrder=$id,\n\tGrouping=$groups{$grouping};\n};
}
@songs=grep(/^Song/,keys %LYRICS);
foreach $song(@songs){
	@songinfo=();
	($key,$titleid,$id)=split(/\./,$song);
	if(($redirect=$LYRICS{$song})=~/^@/){print qq{INSERT song set TitleID=$titleid,Redirect='$redirect';\n};}
	else{
		getsonginfo($LYRICS{$song});
		$songinfo[10]=$id;
		for($t=1;$t<=$#songinfotype;$t++){
			($type=$songinfotype[$t])=~s/ //g;
			($value=$songinfo[$songinfotype{$type}])=~s/'/\\'/g;
			$value=~s/\r//g;
			if($type eq "SongTitle"){print qq{INSERT song set\n\tSongTitle='$value',\n};}
			elsif($type eq "Duration"){$value=$value?"00:$value":""; print "\tDuration='$value',\n";}
			elsif($type eq "CDTrackNumber"){print "\tCDTrackNumber=$id,\n";}
			else{print "\t$type='$value',\n";}
		}
		if(($redirect=$LYRICS{"Chunk.$titleid.$id.1"})=~/^@\d+\.\d+$/){print qq{\tLyricsRedirect='$redirect',\n};}
		else{
			print qq{\tLyrics='};
			$start=0;
			while(1){
				$start++;
				last unless $chunk=$LYRICS{"Chunk.$titleid.$id.$start"};
				$chunk.="\n" unless substr($chunk,-1,1) eq "\n";
				$chunk=~s/'/\\'/g;
				$chunk=~s/\r//g;
				print "$chunk\n";
			}
			print "',\n";
		}
		print qq{\tTitleID=$titleid;\n};
	}
}
sub createTables{
	print <<EOF;
DROP TABLE title;
DROP TABLE song;
DROP TABLE grouping;
CREATE TABLE title (
	id smallint unsigned AUTO_INCREMENT PRIMARY KEY NOT NULL,
	Grouping tinyint NOT NULL,
	Title tinytext NOT NULL,
	Artist tinytext NOT NULL,
	Players text NOT NULL,
	Producer tinytext NOT NULL,
	ReleaseDate date NOT NULL,
	Duration time NOT NULL,
	Label tinytext NOT NULL,
	CatalogNumber tinytext NOT NULL,
	Comments text NOT NULL,
	CoverArt tinytext NOT NULL,
	Background tinytext NOT NULL,
	OtherReferences text NOT NULL,
	AllMusicGuide tinytext NOT NULL,
	AllMovieGuide tinytext NOT NULL,
	OtherReviews text NOT NULL,
	MusicCentral tinytext NOT NULL,
	TitleSortOrder smallint NOT NULL);
CREATE TABLE song (
	id smallint unsigned AUTO_INCREMENT PRIMARY KEY NOT NULL,
	TitleID smallint NOT NULL,
	Redirect tinytext NOT NULL,
	SongTitle tinytext NOT NULL,
	Duration time NOT NULL,
	Composer tinytext NOT NULL,
	SoundByte tinytext NOT NULL,
	DigitizedSong tinytext NOT NULL,
	BMI tinytext NOT NULL,
	ASCAP tinytext NOT NULL,
	Performedby tinytext NOT NULL,
	Performedon tinytext NOT NULL,
	CDTrackNumber tinyint unsigned NOT NULL,
	CDLink text NOT NULL,
	Commentary text NOT NULL,
	GuitarTab tinytext NOT NULL,
	MIDI tinytext NOT NULL,
	LyricsRedirect tinytext NOT NULL,
	Lyrics text NOT NULL);
CREATE TABLE grouping (
	GroupID tinyint unsigned auto_increment primary key NOT NULL,
	GroupName tinytext NOT NULL,
	SortOrder tinyint NOT NULL);
INSERT grouping set GroupID=1,GroupName="Nazz",SortOrder=1;
INSERT grouping set GroupID=2,GroupName="Todd Rundgren",SortOrder=2;
INSERT grouping set GroupID=3,GroupName="TR-i",SortOrder=3;
INSERT grouping set GroupID=4,GroupName="Todd Rundgren\'s Utopia",SortOrder=4;
INSERT grouping set GroupID=5,GroupName="Utopia",SortOrder=5;
INSERT grouping set GroupID=6,GroupName="The New Cars",SortOrder=6;
INSERT grouping set GroupID=7,GroupName="Other Projects",SortOrder=7;
EOF
}