#!/usr/bin/perl

#
# List of options:
#   txt2sql_upd.pl --help

use strict;
use warnings;
use Getopt::Long;

my $sDirPath = "";
my $sFileout = "";
my $sType = "";
my $sHelp = 0;
my $sTable = "";
my $sSkCom = 1;
my $sOffset = 1;
my $sTarget = "";

my $db;
my $nb_columns;
my @str_col = (); #Use basic escape.
my @str_col2 = (); #Use second escape (currently for scripts).
my $line_format;
my $create_table;
my @defaults = ();

Main();

sub GetArgs {
	GetOptions(
	'i=s' => \$sDirPath, #input directory path
	'o=s' => \$sFileout, #input out filename
	'table=s' => \$sTable, #Table name.
	'm=s' => \$sType, #Database: map,castle
	'c=i' => \$sSkCom, #skip comment ?
	's=i' => \$sOffset, #offset for id
	't=s' => \$sTarget, #Renewal setting: pre-re, re.
	'help!' => \$sHelp,
	) or $sHelp=1; #Display help if invalid options are supplied.
	my $sValidType = "map|castle|abra|impro|mpouch|mboss|mbranch|mporing";
	my $sValidTarget = "re|pre";

	if( $sHelp ) {
		print "Incorrect option specified. Available options:\n"
			."\t --o=filename => Output file name. \n"
			."\t --i=filename => input directory path \n"
			."\t --c=[0/1] => skip comment ?\n"
			."\t --s=[int] => offset for id \n"
			."\t --table=tablename => Table name to create. \n";
		exit;
	}
	unless($sDirPath or $sFileout){
		print "ERROR: DirPath and Filename_out are required to continue.\n";
		exit;
	}
	unless($sType =~ /$sValidType/i){
		print "ERROR: Incorrect type specified. Available types:\n"
			."\t --m => Type (specify which data entry to use [$sValidType]).\n";
		exit;
	}
	if($sTarget && $sTarget !~ /$sValidTarget/i){
		print "ERROR: Incorrect target specified. Available targets:\n"
			."\t --t => Target (specify which kind of table_struct to build [$sValidTarget]).\n";
		exit;
	}
}

sub Main {
	GetArgs();
	BuildDataForType($sType);
	ConvertFile($sDirPath,$sFileout,$sType);
	print "Conversion ended.\n";
}

sub ConvertFile { my($sDirPath,$sFileout,$sType)=@_;
	my $sFHout;
	my $sFileIn;
	my $sOldChamp=0;
	if($sType =~ /map/i){ $sFileIn = "map_index.txt" }
	elsif($sType =~ /castle/i) { $sFileIn = "castle_db.txt"; }
	elsif($sType =~ /abra/i) { $sFileIn = "abra_db.txt"; }
	elsif($sType =~ /impro/i) { $sFileIn = "skill_improvise_db.txt"; }
	elsif($sType =~ /mpouch/i) { $sFileIn = "mob_pouch.txt"; }
	elsif($sType =~ /mboss/i) {	$sFileIn = "mob_boss.txt"; }
	elsif($sType =~ /mbranch/i) { $sFileIn = "mob_branch.txt"; }
	elsif($sType =~ /mporing/i) { $sFileIn = "mob_poring.txt"; }
	
	print "Starting ConvertFile with: \n\t DirPath=$sDirPath targetFiles=[ $sFileIn ] \n\t fileout=$sFileout \n";
	
	
	if($sType =~ /mboss|mbranch|mporing/i){
		if($sTarget =~ /pre/i){ $sDirPath .= "/pre-re/"; }
		elsif($sTarget =~ /Re/i){ $sDirPath .= "/re/"; }
	}
	chdir $sDirPath;
	
	
	open FHIN,"$sFileIn" or die "ERROR: Can't read or locate $sDirPath$sFileIn.\n";
	open $sFHout,">$sFileout" or die "ERROR: Can't write $sDirPath$sFileout.\n";
	
	printf $sFHout ("%s\n",$create_table);
	while(my $ligne=<FHIN>) {
		my $sIsCom=0;
		if ($ligne =~ /^\s*$/ ) {
				print $sFHout "\n";
				next;
		}
		if ($ligne =~ /[^\r\n]+/) {
			$ligne = $&;
			if ($ligne =~ /^\/\//) {
				next if($sSkCom);
				printf $sFHout ("#");
				$ligne = substr($ligne, 2);
				$sIsCom=1;
			}
			my @champ = ();
			
			if ($sType =~ /map/i ) {
				
				@champ = split('\s',$ligne);
				if($#champ < 2 && $champ[0] =~ /\w/ && $champ[0] !~ /\:/ ) {
					if($#champ == 1) { $sOldChamp=$champ[1] }
					else { $sOldChamp+=1; }
					$champ[1] = $sOldChamp;
				}
			}
			elsif ($sType =~ /castle|abra/i) {
				my @desc = ();
				@champ = split(",",$ligne);
				if ($#champ >= 4) { 
					@desc = split('\t',$champ[4]);
					$champ[4] = $desc[0];
				}
			}
			elsif ($sType =~ /impro/i) {
				my @desc = ();
				@champ = split(",",$ligne);
				if ($#champ >= 1) { 
					@desc = split('\t',$champ[1]);
					$champ[1] = $desc[0];
				}
			}
			elsif($sType =~ /mpouch|mboss|mbranch|mporing/i) {
				my @desc = ();
				@champ = split(",",$ligne);
				if ($#champ >= $nb_columns - 1) { 
					@desc = split('\t',$champ[$nb_columns - 1]);
					$champ[$nb_columns - 1] = $desc[0];
				}
			}
			
			
			if ($#champ != $nb_columns - 1) { #Can't parse, it's a real comment.
				printf $sFHout ("%s\n", $ligne);
			} else {
				if ($sType =~ /castle/i ) {
					foreach my $col (@str_col) {
						my $string = $champ[$col];
						$string = escape($string,"'","\\'");
						$string =~ s/\s+$//; #Remove trailing spaces.
						$string =~ s/^\s+//; #Remove leading spaces.
						$champ[$col] = $string;
					}
					print $sFHout "REPLACE INTO `$db`( `id`, `map_id`, `name`, `guildbreakevent` ) VALUES ( '".($champ[0]+$sOffset)."\', ( SELECT DISTINCT(`id`) FROM `maps` WHERE `name` = \'".$champ[1]."\' ), \'".$champ[2]."\', \'".$champ[3]."\');\n"; 
				}
				elsif($sType =~ /map/i) { printf $sFHout ("REPLACE INTO `%s`( `name`, `id` ) VALUES (", $db);
					for (my $i=0; $i<$#champ; $i++) {
						printField($sFHout,$champ[$i],",",$i);
					}
					printField($sFHout,$champ[$#champ],");\n",$#champ);
				}
				elsif($sType =~ /abra|impro/i) { 
					my $sSkID = 294; #if impro
					$sSkID = 290 if($sType =~ /abra/i);
					print $sFHout "REPLACE INTO `$db`( `skill_id`, `target_skill_id`, `rate`) VALUES ( ".$sSkID.",".$champ[0].",".$champ[2].");\n"; 
				}
				elsif($sType =~ /mpouch|mboss|mbranch|mporing/i) { 
					my $sItID = 0; #if impro
					if($sType =~ /mpouch/i) { $sItID = 12024; } 
					elsif($sType =~ /mboss/i) { $sItID = 12103; }
					elsif($sType =~ /mbranch/i) { $sItID = 604; }
					elsif($sType =~ /mporing/i) { $sItID = 12109; }
					print $sFHout "REPLACE INTO `$db`( `item_id`, `mob_id`, `rate`) VALUES ( ".$sItID.",".$champ[0].",".$champ[2].");\n"; 
				}
			}
		}
	}
	print $sFHout "\n";
}

sub printField { my ($sFHout,$str, $suffix, $idCol) = @_;
	# Remove first { and last } .
	if ($str =~ /{.*}/) {
		$str = substr($&,1,-1);
	}
	# If nothing, put NULL.
	if ($str eq "") {
		my $sDef;
		if(scalar(@defaults)) { $sDef = $defaults[$idCol]; } #Use default in array.
		else { $sDef = "NULL" unless($sDef); } #Let SQL handle the default.
		print $sFHout "$sDef$suffix";
	} else {
		my $flag = 0;
		# Search if it's a string column?
		foreach my $col (@str_col) {
			if ($col == $idCol) {
				$flag |= 1;
				last;
			}
		}
		foreach my $col (@str_col2) {
			if ($col == $idCol) {
				$flag |= 2;
				last;
			}
		}

		if ($flag & 3) {
			# String column, so escape , remove trailing and add '' .
			my $string;
			$string = escape($str,"'","\\'") if($flag & 1) ;
			$string =~ s/\s+$//; #Remove trailing spaces.
			$string =~ s/^\s+//; #Remove leading spaces.
			$string = escape($string,'\\\"','\\\\\"') if($flag & 2) ;
			printf $sFHout ("'%s'%s", $string, $suffix);
		} else {
			# Not a string column.
			printf $sFHout ("%s%s", $str,$suffix);
		}
	}
}

sub escape { my ($str,$sregex,$sreplace) = @_;
	my @str_splitted = split($sregex, $str);
	my $result = "";
	for (my $i=0; $i<=$#str_splitted; $i++) {
		if ($i == 0) {
			$result = $str_splitted[0];
		} else {
			$result = $result.$sreplace.$str_splitted[$i];
		}
	}
	return $result
}

sub BuildDataForType{ my($sType) = @_;
	print "Starting BuildDataForType with: \n\t Type=$sType \n";
	
	if($sType =~ /castle/i) {
		$db = $sTable;
		$db = "castles" unless($db);
		$nb_columns = 5;
		@str_col = (3,4);
		@str_col2 = ();
		$create_table =
"#
# Table structure for table `$db`
#

DROP TABLE IF EXISTS `$db`;
CREATE TABLE `castles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `map_id` int(11) unsigned NOT NULL,
  `name` varchar(255) DEFAULT '',
  `guildbreakevent` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;
";
	}
	elsif($sType =~ /map/i){
		$db = $sTable;
		$db = "maps" unless($db);
		$nb_columns = 2;
		@str_col = (0);
		@str_col2 = ();
		$line_format = "";
		$create_table =
"#
# Table structure for table `$db`
#

DROP TABLE IF EXISTS `$db`;
CREATE TABLE `$db` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;
";
	}
	elsif($sType =~ /abra|impro/i){
		$db = $sTable;
		$db = "skills_random" unless($db);
		$nb_columns = 3;
		$nb_columns = 2 if($sType =~ /impro/i);
		@str_col = ();
		@str_col2 = ();
		$line_format = "";
		$create_table =
"#
# Table structure for table `$db`
#

DROP TABLE IF EXISTS `$db`;
CREATE TABLE `$db` (
  `skill_id` int(11) unsigned NOT NULL,
  `target_skill_id` int(11) unsigned NOT NULL,
  `rate` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`skill_id`,`target_skill_id`)
) ENGINE=MyISAM;
";
	}
	elsif($sType =~ /mpouch|mboss|mbranch|mporing/i){
		$db = $sTable;
		$db = "item_mobs_random" unless($db);
		$nb_columns = 3;
		@str_col = ();
		@str_col2 = ();
		$line_format = "";
		$create_table =
"#
# Table structure for table `$db`
#

DROP TABLE IF EXISTS `$db`;
CREATE TABLE `$db` (
  `item_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `mob_id` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `rate` int(11) unsigned NOT NULL,
  PRIMARY KEY (`item_id`,`mob_id`)
) ENGINE=MyISAM;
";		
	}
}