#!/usr/local/bin/perl

$doDB = 1;

$| = 1;
use Pg;
my ($result, $conn);
$conn = Pg::connectdb("dbname=swl user=tomas");
if (! PGRES_CONNECTION_OK eq $conn->status) {
	print "Error initializing - could not connect to swl\n";
	print $conn->errorMessage . "\n";
	exit;
}

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

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";

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,18);
		my $graphic = substr($line,63,48);
		my $target  = substr($line,111,8);
		my $location= substr($line,119,25);
		my $power   = substr($line,144,7);
		my $azimuth = substr($line,151,3);
		my $remarks = substr($line,154,15);
		my $modultn = substr($line,169,1);

		my $northam = substr($line,170,2);
		my $centam  = substr($line,172,2);
		my $southam = substr($line,174,2);
		my $europe  = substr($line,176,2);
		my $africa  = substr($line,178,2);
		my $mideast = substr($line,180,2);
		my $asia    = substr($line,182,2);
		my $austnz  = substr($line,184,2);
		my $pacific = substr($line,186,2);

		my $ciraf   = substr($line,188,30);
		my $admin   = substr($line,218,3);

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

		my $longi   = substr($line,244,6);
		my $lati    = substr($line,250,5);
		my $region  = substr($line,255,20);

		my $status  = substr($line,296,1);		# I need to look at this!
		
		my ($program) = "";

		$freq =~ s/^\s*(.*?)\s*$/$1/;
		$freq =~ tr/ //d;
		$freq = $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   = "?";
		}

#		if ($line =~ m|DOMINICAN REP|i) {
#			print STDERR "[$line]\n[$utcTimeStart][$utcTimeEnd] from [$utcTime]\n";
#		}


		$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;

		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] s[$station] l[$location] [$utcTimeStart - $utcTimeEnd] r[$remarks] m[$modultn] status[$status] t[$target] pwr[$power] azm[$azimuth]\n";
			$modArray{$modultn}++;
			$targetArray{$target}++;

#	koid char (20) UNIQUE NOT NULL,
#	freq char (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),
if ($doDB) {
			$result = $conn->exec("
				INSERT INTO schedule_flat VALUES
				(
				'$oid',
				'$freq',
				'$station',
				'$utcTimeStart',
				'$utcTimeEnd',
				'$days',
				'$lang',
				'$country',
				'$target',
				'$location',
				'$power',
				'$azimuth',
				'$remarks',
				'$modultn',
				'$longi',
				'$lati',
				'$region',
				'$program'
				)
			");

			if (!PGRES_TUPLES_OK eq $result->resultStatus) {
				print "Error: " . $conn->errorMessage . "\n";
				exit;
			} else {
				print LOG "Inserted $freq - $station s:$utcTimeStart e:$utcTimeEnd ($oid)\n";
			}
}
		}

		$statusArray{$status}++;
	}
}
close (DB);
close (LOG);

print "\nstatus:\n======\n";

foreach my $recordKey (sort keys(%statusArray)) {
	print "[$recordKey] ($statusArray{$recordKey})\n";
}

print "\nModulations:\n===========\n";
foreach my $recordKey (sort keys(%modArray)) {
	print "[$recordKey] ($modArray{$recordKey})\n";
}

print "\nTarget:\n======\n";
foreach my $recordKey (sort keys(%targetArray)) {
	print "[$recordKey] ($targetArray{$recordKey})\n";
}

print "\n";