#!/usr/bin/perl

$doDB = 1;

my ($_oid) = "20110514";

#$theRootDir = "g:\\hfcc\\";
$theRootDir = "/home/hfradio/database/swl/ITU_DATA/hfcc_schedules/";

$theSched = $theRootDir . "allx.txt";
$theAdmin = $theRootDir . "admin.txt";
$theBcst  = $theRootDir . "broadcas.txt";
$theFmorg = $theRootDir . "fmorg.txt";
$theSite  = $theRootDir . "site.txt";

$schedlog = $theRootDir . "schedfill.log";
$adminlog = $theRootDir . "adminfill.log";
$bcstlog  = $theRootDir . "bcstfill.log";
$fmgrlog  = $theRootDir . "fmgrfill.log";
$sitelog  = $theRootDir . "sitefill.log";

$| = 1;

#use Date::Calc qw(:all);

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 {

#first, drop, then create the tables
	#my ($_oid) = "";
	#($year,$month,$day) = Today([$gmt]);
	#$_oid = $year . $month . $day;
	#print "date = [$_oid]\n";
#exit;
	eval {
		$dbh->do("
			DROP TABLE hfcc_sched
		");
	};
	if ($@) {
		print "Did not drop table [hfcc_sched] $@\n";
	} else {
		print "Dropped [hfcc_sched]\n";
	}

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

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

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

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


	eval {
		$dbh->do("
		CREATE TABLE hfcc_sched (
			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)
			)
		");
	};
	if ($@) {
		print "Did not create table [hfcc_sched] $@\n";
	} else {
		print "Created table [hfcc_sched]\n";
	}

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

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

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

	eval {
		$dbh->do("
			CREATE TABLE hfcc_admin
				(
				koid varchar (20) UNIQUE NOT NULL,
				adminkey varchar (3) UNIQUE NOT NULL,
				country varchar (50) NOT NULL,
				PRIMARY KEY (koid)
				)
		");
	};
	if ($@) {
		print "Did not create table [hfcc_admin] $@\n";
	} else {
		print "Created table [hfcc_admin]\n";
	}

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

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

	eval {
		$dbh->do("
			CREATE TABLE hfcc_bcst
				(
				koid varchar (20) UNIQUE NOT NULL,
				bcstkey varchar (3) UNIQUE NOT NULL,
				station varchar (50) NOT NULL,
				PRIMARY KEY (koid)
				)
		");
	};
	if ($@) {
		print "Did not create table [hfcc_bcst] $@\n";
	} else {
		print "Created table [hfcc_bcst]\n";
	}

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

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

	eval {
		$dbh->do("
			CREATE TABLE hfcc_fmgr
				(
				koid varchar (20) UNIQUE NOT NULL,
				fmgrkey varchar (3) UNIQUE NOT NULL,
				fmoname varchar (50) NOT NULL,
				PRIMARY KEY (koid)
				)
		");
	};
	if ($@) {
		print "Did not create table [hfcc_fmgr] $@\n";
	} else {
		print "Created table [hfcc_fmgr]\n";
	}

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

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

	eval {
		$dbh->do("
	CREATE TABLE hfcc_site
	(
	koid varchar (20) UNIQUE NOT NULL,
	sitekey varchar (3) UNIQUE NOT NULL,
	sitename varchar (50) NOT NULL,
	admin varchar (3),
	latitude char (5),
	longitude char (6),
	PRIMARY KEY (koid)
	)
		");
	};
	if ($@) {
		print "Did not create table [hfcc_site] $@\n";
	} else {
		print "Created table [hfcc_site]\n";
	}

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

	eval {
		$dbh->do("
	CREATE INDEX sitenamei
	ON hfcc_site (sitename)
		");
	};
	if ($@) {
		print "Did not create index [sitenamei] $@\n";
	} else {
		print "Created index [sitenamei]\n";
	}
	# now that we have tables, let's populate them...

	print "Schedules...\n";
	&fillSched();
	print "Admin...\n";
	&fillAdmin();
	print "Broadcast...\n";
	&fillBroadcast();
	print "FMO...\n";
	&fillFregManagementOrg();
	print "Sites...\n";
	&fillSite();

	print "Done... \n";

	$dbh->disconnect();

} # end of if connect to MySQL


sub fillSched {

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

	my $modArray = ();

	open (LOG, "> $schedlog") || die "Could not create $schedlog : $!\n";

	while (<DB>) {

		my $line = $_;
		$line =~ tr/\r\n//d;

		if (($line !~ m|^;|) && (length($line) > 4)) {

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

			my $freq    = substr($line,0,5);   # freq
			my $stime   = substr($line,6,4);   # utc_time_start
			my $etime   = substr($line,11,4);  # utc_time_end
			my $ciraf   = substr($line,16,30); # target
			my $location= substr($line,47,3);  # location
			my $power   = substr($line,51,4);  # power
			my $azimuth = substr($line,56,15); # azimuth
			my $days    = substr($line,72,7);  # days
			my $modultn = substr($line,94,1);  # modulation
			my $language= substr($line,102,10);# lang
			my $admin   = substr($line,113,3); # country
			my $station = substr($line,117,3); # station
			my $freqmgr = substr($line,121,3); # freqmgr

			$freq =~ s/^\s*(.*?)\s*$/$1/;
			$freq .= ".00";
			$stime =~ s/^\s*(.*?)\s*$/$1/;
			$etime =~ s/^\s*(.*?)\s*$/$1/;
			$ciraf =~ s/^\s*(.*?)\s*$/$1/;
			$location =~ s/^\s*(.*?)\s*$/$1/;
			$power =~ s/^\s*(.*?)\s*$/$1/;
			$azimuth =~ s/^\s*(.*?)\s*$/$1/;
			$days =~ s/^\s*(.*?)\s*$/$1/;
			$modultn =~ s/^\s*(.*?)\s*$/$1/;
			$language =~ s/^\s*(.*?)\s*$/$1/;
			$admin =~ s/^\s*(.*?)\s*$/$1/;
			$station =~ s/^\s*(.*?)\s*$/$1/;
			$freqmgr =~ s/^\s*(.*?)\s*$/$1/;

			my $utcTimeStart = $stime;
			my $utcTimeEnd   = $etime;

			$utcTimeStart =~ m|(\d\d)(\d\d)|;
			my $hour = $1;
			my $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";

			if (
				($modultn ne "N") &&
				($modultn ne "T")
				) { #

				my $modmod = $modultn;
				if ($modultn eq "S") {
					$modultn = "SSB";
				} elsif ($modultn eq "D") {
					$modultn = "AM";
				} 
				print "[$freq][$stime][$utcTimeStart]-[$etime][$utcTimeEnd]";
				print "[$ciraf][$location][$power][$azimuth][$days]m[$modultn]m[$modmod]";
				print "[$language][$admin][$station][$freqmgr]\n";

				$modArray{$modultn}++;
				
				$oid = $dbh->quote($oid);
				$freq = $dbh->quote($freq);
				$utcTimeStart = $dbh->quote($utcTimeStart);
				$utcTimeEnd = $dbh->quote($utcTimeEnd);
				$ciraf = $dbh->quote($ciraf);
				$location = $dbh->quote($location);
				$power = $dbh->quote($power);
				$azimuth = $dbh->quote($azimuth);
				$days = $dbh->quote($days);
				$language = $dbh->quote($language);
				$admin = $dbh->quote($admin);
				$modultn = $dbh->quote($modultn);
				$station = $dbh->quote($station);
				$freqmgr = $dbh->quote($freqmgr);

				if ($doDB) {
					eval {
						$dbh->do("
							INSERT INTO hfcc_sched VALUES
							(
							$oid,
							$freq,
							$utcTimeStart,
							$utcTimeEnd,
							$ciraf,
							$location,
							$power,
							$azimuth,
							$days,
							$language,
							$admin,
							$modultn,
							$station,
							$freqmgr
							)
						");
					};

					if ($@) {
						print "Did not add record [hfcc_sched] $@\n";
						exit;
					} else {
						print LOG "Inserted $freq - $station s:$utcTimeStart e:$utcTimeEnd ($oid)\n";
					}
				}
			}
		}
	}
	close (DB);
	close (LOG);

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

sub fillAdmin {

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

	open (LOG, "> $adminlog") || die "Could not create $adminlog : $!\n";

	while (<DB>) {

		my $line = $_;
		$line =~ tr/\r\n//d;

		if (($line !~ m|^;|) && (length($line) > 4)) {

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

			my $adminkey = substr($line,0,3);   # adminkey
			my $country  = substr($line,4,50);  # administration - country name

			$adminkey =~ s/^\s*(.*?)\s*$/$1/;
			$country  =~ s/^\s*(.*?)\s*$/$1/;
			
			print "[$adminkey][$country]\n";

			$oid = $dbh->quote($oid);
			$adminkey = $dbh->quote($adminkey);
			$country = $dbh->quote($country);

			if ($doDB) {
				eval {
					$dbh->do("
						INSERT INTO hfcc_admin VALUES
						(
							$oid,
							$adminkey,
							$country
						)
					");
				};
				if ($@) {
					print "Did not add record [hfcc_admin] $@\n";
					exit;
				} else {
					print LOG "Inserted $adminkey - $country ($oid)\n";
				}
			}
		}
	}
	close (DB);
	close (LOG);

}

sub fillBroadcast {

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

	open (LOG, "> $bcstlog") || die "Could not create $bcstlog : $!\n";

	while (<DB>) {

		my $line = $_;
		$line =~ tr/\r\n//d;

		if (($line !~ m|^;|) && (length($line) > 4)) {

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

			my $bcstkey = substr($line,0,3);   # bcstkey
			my $station = substr($line,4,50);  # Broadcast - the station

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

			print "[$bcstkey][$station]\n";

			$oid = $dbh->quote($oid);
			$bcstkey = $dbh->quote($bcstkey);
			$station = $dbh->quote($station);

			if ($doDB) {
				eval {
					$dbh->do("
						INSERT INTO hfcc_bcst VALUES

						(
							$oid,
							$bcstkey,
							$station
						)
					");
				};
				if ($@) {
					print "Did not add record [hfcc_bcst] $@\n";
					exit;
				} else {
					print LOG "Inserted $bcstkey - $station ($oid)\n";
				}
			}
		}
	}
	close (DB);
	close (LOG);

}

sub fillFregManagementOrg {

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

	open (LOG, "> $fmgrlog") || die "Could not create $fmgrlog : $!\n";

	while (<DB>) {

		my $line = $_;
		$line =~ tr/\r\n//d;

		if (($line !~ m|^;|) && (length($line) > 4)) {

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

			my $fmgrkey = substr($line,0,3);   # fmgrkey
			my $fmoname = substr($line,4,50);  # Frequency Management Organization

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

			print "[$fmgrkey][$fmoname]\n";
			$oid = $dbh->quote($oid);
			$fmgrkey = $dbh->quote($fmgrkey);
			$fmoname = $dbh->quote($fmoname);

			if ($doDB) {
				eval {
					$dbh->do("
						INSERT INTO hfcc_fmgr VALUES
						(
							$oid,
							$fmgrkey,
							$fmoname
						)
					");
				};
				if ($@) {
					print "Did not add record [hfcc_fmgr] $@\n";
					exit;
				} else {
					print LOG "Inserted $fmgrkey - $fmoname ($oid)\n";
				}
			}
		}
	}
	close (DB);
	close (LOG);

}

sub fillSite {

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

	open (LOG, "> $sitelog") || die "Could not create $sitelog : $!\n";

	while (<DB>) {

		my $line = $_;
		$line =~ tr/\r\n//d;

		if (($line !~ m|^;|) && (length($line) > 4)) {

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

			my $sitekey  = substr($line,0,3);   # sitekey
			my $sitename = substr($line,4,30);  # Site Name
			my $admin    = substr($line,36,3);  # admin foriegn key
			my $latitude = substr($line,40,5);  # latitude
			my $longitude= substr($line,46,6);  # longitude

			$sitekey   =~ s/^\s*(.*?)\s*$/$1/;
			$sitename  =~ s/^\s*(.*?)\s*$/$1/;
			$admin     =~ s/^\s*(.*?)\s*$/$1/;
			$latitude  =~ s/^\s*(.*?)\s*$/$1/;
			$longitude =~ s/^\s*(.*?)\s*$/$1/;

			print "[$sitekey][$sitename][$admin][$latitude][$longitude]\n";
			$oid = $dbh->quote($oid);
			$sitekey = $dbh->quote($sitekey);
			$sitename = $dbh->quote($sitename);
			$admin = $dbh->quote($admin);
			$latitude = $dbh->quote($latitude);
			$longitude = $dbh->quote($longitude);

			if ($doDB) {
				eval {
					$dbh->do("
						INSERT INTO hfcc_site VALUES
						(
							$oid,
							$sitekey,
							$sitename,
							$admin,
							$latitude,
							$longitude
						)
					");
				};
				if ($@) {
					print "Did not add record [hfcc_site] $@\n";
					exit;
				} else {
					print LOG "Inserted $sitekey - $sitename ($oid)\n";
				}
			}
		}
	}
	close (DB);
	close (LOG);

}