#!/usr/bin/perl

$doDB = 1;

$| = 1;

$theRootDir = "/home/hfradio/database/swl";
$theDatabase = $theRootDir . "/ILGSTEXT.TXT";


use DBI;
my ($dsn, $result, $conn);

$dsn = "DBI:mysql:database=swl;host=localhost";

eval {
	$dbh = DBI->connect($dsn, "proptomas", "pr0pag8sion", {'RaiseError' => 1});
};
if ($@) {
	print "Did not connect with MySQL: \nerror [" . $@ . "]\n";
} else {

	eval {
		$dbh->do("
			DROP TABLE schedule_flat
		");
	};
	if ($@) {
		print "Did not drop table [schedule_flat] $@\n";
	} else {
		print "Dropped [schedule_flat]\n";
	}

#			koid varchar (20) UNIQUE NOT NULL,
#			freq varchar (8) NOT NULL,
#			utc_time_start time NOT NULL,
#			utc_time_end time NOT NULL,
#			target varchar (30),
#			location varchar (3),
#			power varchar (4),
#			azimuth varchar (15),
#			days char (7),
#			lang varchar (80),
#			country varchar (3),
#			modulation varchar (8),
#			station varchar (3),
#			freqmgr varchar (3),
#			PRIMARY KEY (koid)
	eval {
		$dbh->do("
			CREATE TABLE schedule_flat
			(
				koid varchar (20) UNIQUE NOT NULL,
				freq varchar (8) NOT NULL,
				station varchar (60) NOT NULL,
				utc_time_start time NOT NULL,
				utc_time_end time NOT NULL,
				days char (7),
				lang varchar (50),
				country varchar (30),
				target varchar (30),
				location varchar (40),
				power varchar (20),
				azimuth varchar (3),
				remarks varchar (30),
				modulation varchar (6),
				longitude varchar (6),
				latitude varchar (6),
				region varchar (20),
				program varchar (40),
				PRIMARY KEY (koid)
			)
		");
	};
	if ($@) {
		print "Did not create table [schedule_flat] $@\n";
	} else {
		print "Created table [schedule_flat]\n";
	}

	eval {
		$dbh->do("
			CREATE INDEX freqi
			ON schedule_flat (freq)
		");
	};
	if ($@) {
		print "Did not create index [freqi] $@\n";
	} else {
		print "Created index [freqi]\n";
	}

	eval {
		$dbh->do("
			CREATE INDEX times
			ON schedule_flat (utc_time_start)
		");
	};
	if ($@) {
		print "Did not create index [times] $@\n";
	} else {
		print "Created index [times]\n";
	}

	eval {
		$dbh->do("
			CREATE INDEX timee
			ON schedule_flat (utc_time_end)
		");
	};
	if ($@) {
		print "Did not create index [timee] $@\n";
	} else {
		print "Created index [timee]\n";
	}

	eval {
		$dbh->do("
			CREATE INDEX calli
			ON schedule_flat (station)
		");
	};
	if ($@) {
		print "Did not create index [calli] $@\n";
	} else {
		print "Created index [calli]\n";
	}

	
	&fillDatabase;
	
	print "Done... \n";

	$dbh->disconnect();
}



sub fillDatabase {

	open (DB, "$theDatabase") || die "Could not open $theDatabase : $!\n";

	my ($_oid) = `date +'%Y%m%d'`;
	chop ($_oid);

	my %statusArray = ();
	my $modArray = ();
	my $targetArray = ();

	open (LOG, "> ./filldb.log") || die "Could not create ./filldb.log : $!\n";

	my ($oLines) = 0;
	
	while (<DB>) {
		my $line = $_;
		$line =~ tr/\r\n//d;

		if (($line !~ m|^.{8}\=\=|) && (length($line) > 4)) {

			$count++;
			$oid = $_oid . $count;

			my $freq    = substr($line,0,7);
			my $station = substr($line,7,22);
			my $utcTime = substr($line,29,9);
			my $days    = substr($line,38,7);
			my $lang    = substr($line,45,17);
			my $graphic = substr($line,62,48);
			my $target  = substr($line,110,8);
			my $location= substr($line,118,25);
			my $power   = substr($line,143,8);
			my $azimuth = substr($line,151,3);
			my $remarks = substr($line,154,15);
			my $status  = substr($line,169,1);		# I need to look at this!
			my $modultn = substr($line,170,1);

			my $northam = substr($line,171,2);
			my $centam  = substr($line,173,2);
			my $southam = substr($line,175,2);
			my $europe  = substr($line,177,2);
			my $africa  = substr($line,179,2);
			my $mideast = substr($line,181,2);
			my $asia    = substr($line,183,2);
			my $austnz  = substr($line,185,2);
			my $pacific = substr($line,187,2);

			my $ciraf   = substr($line,189,30);
			my $admin   = substr($line,219,3);

			my $country = substr($line,222,18);

			my $longi   = substr($line,240,6);
			my $lati    = substr($line,246,5);
			my $locode  = substr($line,251,2);
			my $region  = substr($line,253,20);
			my $stn     = substr($line,273,5);
			my $pstruct = substr($line,278,1);
			my $program = substr($line,279,20);

#			$freq =~ s/^\s*(.*?)\s*$/$1/;
#			$freq =~ tr/ //d;
			$freq =~ s/^\s*(.*?)\s*$/$1/;
			$freq .= "0";

			$station =~ s/^\s*(.*?)\s*$/$1/;
			$utcTime =~ s/^\s*(.*?)\s*$/$1/;

			if ($utcTime =~ m|\d|) {
				$utcTime =~ m|(\d+)[^\d]+?(\d+)|;
				$utcTimeStart = "$1";
				$utcTimeEnd = "$2";
			} else {
				$utcTimeStart = "?";
				$utcTimeEnd   = "?";
			}

			$days =~ s/^\s*(.*?)\s*$/$1/;

			$lang =~ s/^\s*(.*?)\s*$/$1/;
			$lang =~ s|/| / |ig;
			$lang =~ s|Engl\.|English|ig;

			$target =~ s/^\s*(.*?)\s*$/$1/;
			$location =~ s/^\s*(.*?)\s*$/$1/;
			$remarks =~ s/^\s*(.*?)\s*$/$1/;
			$region =~ s/^\s*(.*?)\s*$/$1/;
			$program =~ s/^\s*(.*?)\s*$/$1/;
			$power =~ s/^\s*(.*?)\s*$/$1/;
			$station =~ s/^\s*(.*?)\s*$/$1/;

			$country =~ s/^\s*(.*?)\s*$/$1/;
			if ($country =~ m|\#+|) {
				$country = "Censorship / Jamming";	
			}

			$country =~ s|UNIT\.|UNITED |ig;
			$country =~ s|NETH\.|Netherland |ig;
			$country =~ s|NORTH\.|North |ig;
			$country =~ s|SOUTH\.|South |ig;
			$country =~ s|IS\.|Island |ig;
			$country =~ s|N\.|North |ig;
			$country =~ s|S\.|South |ig;
			$country =~ s#([a-zA-Z0-9])(\.|\-)([a-zA-Z0-9])#$1 $2 $3#ig;
			$station =~ s|Univers\.|Universal |ig;
			$station =~ s|Ministr\.|Ministry |ig;
			$station =~ s|Intern\.|International |ig;
			$station =~ s|Int\.|International |ig;
			$station =~ s|MOCAMBIQUE|MOZAMBIQUE|ig;
			$station =~ s|R\.|Radio |ig;

			$country =~ s|\'|\'\'|g;
			$station =~ s|\'|\'\'|g;

			$utcTimeStart =~ m|(\d\d)(\d\d)|;
			$hour = $1;
			$minute = $2;
			if ($hour == 24) {
				if ($minute == 0) {
					$hour = "23";
					$minute = "59";
				} else {
					$hour = "00";
				}
			}
			$utcTimeStart = "$hour:$minute:00";

			$utcTimeEnd =~ m|(\d\d)(\d\d)|;
			$hour = $1;
			$minute = $2;
			if ($hour == 24) {
				if ($minute == 0) {
					$hour = "23";
					$minute = "59";
				} else {
					$hour = "00";
				}
			}
			$utcTimeEnd = "$hour:$minute:00";
			$program =~ s|R\.|Radio |ig;
			$program =~ s|Intern.|Internal |ig;
			$program =~ s|Polit.|Political |ig;

			#print "[$freq] [$station][$location] r[$remarks] p[$program] t[$target] [$power] [$azimuth]\n";
			print "modulation: [$modultn] and status = [$status]\n";

			if (
				($modultn ne "N") &&
				($modultn ne "T")
				# &&
				#(  ($status eq "C") || ($status eq "N")  )
				) { # current or new
				my $modmod = $modultn;
				if ($modultn eq "S") {
					if ($remarks =~ m!(USB|LSB)!) {
						$modultn = $1;
					} else {
						$modultn = "SSB";
					}
				} elsif ($modultn eq "D") {
					$modultn = "AM";
				} elsif ($modultn eq "U") {
					$modultn = "USB";
				} 
				#print "[$freq] [$station][$location] r[$remarks] p[$program] t[$target] [$power] [$azimuth]\n";
				$modArray{$modultn}++;
				$targetArray{$target}++;

				# INSERT INTO DATABASE HERE

				$oid = $dbh->quote($oid);
				$freq = $dbh->quote($freq);
				$station = $dbh->quote($station);
				$utcTimeStart = $dbh->quote($utcTimeStart);
				$utcTimeEnd = $dbh->quote($utcTimeEnd);
				$days = $dbh->quote($days);
				$lang = $dbh->quote($lang);
				$country = $dbh->quote($country);
				$target = $dbh->quote($target);
				$location = $dbh->quote($location);
				$power = $dbh->quote($power);
				$azimuth = $dbh->quote($azimuth);
				$remarks = $dbh->quote($remarks);
				$modultn = $dbh->quote($modultn);
				$longi = $dbh->quote($longi);
				$lati = $dbh->quote($lati);
				$region = $dbh->quote($region);
				$program = $dbh->quote($program);
				$oLine++;
				if ($doDB) {
					eval {
						$dbh->do("
							INSERT INTO schedule_flat VALUES
							(
								$oid,
								$freq,
								$station,
								$utcTimeStart,
								$utcTimeEnd,
								$days,
								$lang,
								$country,
								$target,
								$location,
								$power,
								$azimuth,
								$remarks,
								$modultn,
								$longi,
								$lati,
								$region,
								$program
							)
						");
					};
					if ($@) {
						print "Did not insert record = $@\n";
					}
				}
			}
		}
		print "$oLine and $count\n";
		$statusArray{$status}++;
	}
			
}