#!/usr/bin/perl
# vim:ts=3

use POSIX qw(strftime);

$filename = $ARGV[0];
$today_serial = strftime "%Y%m%d01", localtime;
%domains = ();
%ACL = ();

if ( -f "SOA.sql" ) { unlink("SOA.sql") };
if ( -f "A.sql" ) { unlink("A.sql") };
if ( -f "CNAME.sql" ) { unlink("CNAME.sql") };
if ( -f "PTR.sql" ) { unlink("PTR.sql") };
if ( -f "MX.sql" ) { unlink("MX.sql") };
if ( -f "NS.sql" ) { unlink("NS.sql") };
if ( -f "DOMAINS.sql" ) { unlink("DOMAINS.sql") };
if ( -f "ACL.sql" ) { unlink("ACL.sql") };
if ( -f "SUBNET.sql" ) { unlink("SUBNET.sql") };

open(INFILE, "<$filename") or die "Cannot open $filename - $!";

while ($line = <INFILE>) {
	chomp $line;
	$ch1 = substr($line, 0, 1);
	SWITCH: {
#################################################################
#	 ____   ___    _
#	/ ___| / _ \  / \
#	\___ \| | | |/ _ \
#	 ___) | |_| / ___ \
#	|____/ \___/_/   \_\
#	
#################################################################
		if ($ch1 eq 'Z') {
			# SOA	-	Zfqdn:mname:rname:ser:ref:ret:exp:min:ttl:timestamp:lo
			# CREATE TABLE soa (
			#         soa_id          NUMBER(9) NOT NULL,
			#         domain_name_id  NUMBER(9) NOT NULL,
			#         dns_id          NUMBER(9) NOT NULL,
			#         soa_serial      NUMBER(10) NOT NULL,
			#         soa_refresh     NUMBER(6) NOT NULL,
			#         soa_retry       NUMBER(6) NOT NULL,
			#         soa_expire      NUMBER(6) NOT NULL,
			#         soa_min_ttl     NUMBER(6) NOT NULL,
			#         PRIMARY KEY     (soa_id),
			#         FOREIGN KEY     (domain_name_id) REFERENCES domain_name,
			#         FOREIGN KEY     (dns_id) REFERENCES domain_name
			# );
			open(SOA, ">>SOA.sql") or die "Can’t open SOA.sql: $!";
			@field = split(/:/, $line);
			$field[0] =~ s/\.$//;
			$field[1] =~ s/\.$//;
			print SOA "INSERT INTO\n";
			print SOA "	soa(\n";
			print SOA "		domain_name_id,\n";
			print SOA "		dns_id,\n";
			print SOA "		administrator,\n";
			print SOA "		soa_serial,\n";
			print SOA "		soa_refresh,\n";
			print SOA "		soa_retry,\n";
			print SOA "		soa_expire,\n";
			print SOA "		soa_min_ttl)\n";
			print SOA "	SELECT \n";
			print SOA "		d1.domain_name_id,\n";
			print SOA "		d2.domain_name_id,\n";
			print SOA "		'", $field[2], "',\n";		# administrator
			if ( $field[3] eq '' ) {
				print SOA "		", $today_serial, ", \n"		# soa_serial
			} else {
				print SOA "		", $field[3], ", \n"		# soa_serial
			}
			if ( $field[4] eq '' ) {
				print SOA "		16384, \n"		# soa_refresh
			} else {
				print SOA "		", $field[4], ", \n"		# soa_refresh
			}
			if ( $field[5] eq '' ) {
				print SOA "		2048, \n"		# soa_retry
			} else {
				print SOA "		", $field[5], ", \n"		# soa_retry
			}
			if ( $field[6] eq '' ) {
				print SOA "		1048576, \n"		# soa_expire
			} else {
				print SOA "		", $field[6], ", \n"		# soa_expire
			}
			if ( $field[7] eq '' ) {
				print SOA "		2560 \n"		# soa_min_ttl
			} else {
				print SOA "		", $field[7], " \n"		# soa_min_ttl
			}
			print SOA "	FROM domain_name d1,\n";
			print SOA "		domain_name d2\n";
			print SOA "	WHERE d1.name = '", lc(substr($field[0], 1)), "'\n";
			print SOA "		AND d2.name = '", lc($field[1]), "'\n";
			print SOA "		LIMIT 1;\n\n";
			close(SOA);
			$domains{lc(substr($field[0], 1))} = 1;
			$domains{lc($field[1])} = 1;
			last SWITCH;
		}
#################################################################
#	 _   _ ____
#	| \ | / ___|
#	|  \| \___ \
#	| |\  |___) |
#	|_| \_|____/
#
#################################################################
		if ($ch1 eq '&') {
			# NS	-	&fqdn:ip:x:ttl:timestamp:lo
			# CREATE TABLE ns (
			#         ns_id           NUMBER(9) NOT NULL,
			#         domain_name_id  NUMBER(9) NOT NULL,
			#         access_group_id CHAR(2) NOT NULL,
			#         ns_ttl          NUMBER(6),
			#         target_name_id  NUMBER(9) NOT NULL,
			#         PRIMARY KEY     (ns_id),
			#         FOREIGN KEY     (domain_name_id) REFERENCES domain_name,
			#         FOREIGN KEY     (access_group_id) REFERENCES access_group,
			#         FOREIGN KEY     (target_name_id) REFERENCES domain_name
			# );
			open(NS, ">>NS.sql") or die "Can’t open NS.sql: $!";
			@field = split(/:/, $line);
			$field[0] =~ s/\.$//;
			$field[2] =~ s/\.$//;
			print NS "INSERT INTO\n";
			print NS "	ns(\n";
			print NS "		domain_name_id,\n";
			print NS "		access_group_id,\n";
			print NS "		ns_ttl,\n";
			print NS "		target_name_id)\n";
			print NS "	SELECT \n";
			print NS "		d1.domain_name_id,\n";
			if ( $field[5] eq '' ) {
				print NS "		'AA', \n"		# access_group_id
			} else {
				print NS "		'", uc($field[5]), "', \n"		# access_group_id
			}
			if ( $field[3] eq '' ) {
				print NS "		2560, \n"		# ns_ttl
			} else {
				print NS "		", $field[3], ", \n"		# ns_ttl
			}
			print NS "		d2.domain_name_id\n";
			print NS "	FROM domain_name d1,\n";
			print NS "		domain_name d2\n";
			print NS "	WHERE d1.name = '", lc(substr($field[0], 1)), "'\n";
			print NS "		AND d2.name = '", lc($field[2]), "'\n";
			print NS "		LIMIT 1;\n\n";
			close(NS);
			$domains{lc(substr($field[0], 1))} = 1;
			$domains{lc($field[2])} = 1;
			last SWITCH;
		}
#################################################################
#	 __  ____  __
#	|  \/  \ \/ /
#	| |\/| |\  /
#	| |  | |/  \
#	|_|  |_/_/\_\
#	
#################################################################
		if ($ch1 eq '@') {
			# MX	-	@fqdn:ip:x:dist:ttl:timestamp:lo
			# CREATE TABLE mx (
			#         mx_id           NUMBER(9) NOT NULL,
			#         domain_name_id  NUMBER(9) NOT NULL,
			#         access_group_id CHAR(2) NOT NULL,
			#         mx_ttl          NUMBER(6),
			#         mx_priority     NUMBER(2) NOT NULL,
			#         target_name_id  NUMBER(9) NOT NULL,
			#         PRIMARY KEY     (mx_id),
			#         FOREIGN KEY     (domain_name_id) REFERENCES domain_name,
			#         FOREIGN KEY     (access_group_id) REFERENCES access_group,
			#         FOREIGN KEY     (target_name_id) REFERENCES domain_name
			# );
			open(MX, ">>MX.sql") or die "Can’t open MX.sql: $!";
			@field = split(/:/, $line);
			$field[0] =~ s/\.$//;
			$field[2] =~ s/\.$//;
			print MX "INSERT INTO\n";
			print MX "	mx(\n";
			print MX "		domain_name_id,\n";
			print MX "		access_group_id,\n";
			print MX "		mx_ttl,\n";
			print MX "		mx_priority,\n";
			print MX "		target_name_id)\n";
			print MX "	SELECT \n";
			print MX "		d1.domain_name_id,\n";
			if ( $field[6] eq '' ) {
				print MX "		'AA', \n"		# access_group_id
			} else {
				print MX "		'", uc($field[6]), "', \n"		# access_group_id
			}
			if ( $field[4] eq '' ) {
				print MX "		2560, \n"		# mx_ttl
			} else {
				print MX "		", $field[4], ", \n"		# mx_ttl
			}
			if ( $field[3] eq '' ) {
				print MX "		2560, \n"		# mx_ttl
			} else {
				print MX "		", $field[3], ", \n"		# mx_ttl
			}
			print MX "		d2.domain_name_id\n";
			print MX "	FROM domain_name d1,\n";
			print MX "		domain_name d2\n";
			print MX "	WHERE d1.name = '", lc(substr($field[0], 1)), "'\n";
			print MX "		AND d2.name = '", lc($field[2]), "'\n";
			print MX "		LIMIT 1;\n\n";
			close(MX);
			$domains{lc(substr($field[0], 1))} = 1;
			$domains{lc($field[2])} = 1;
			last SWITCH;
		}
#################################################################
#	    _
#	   / \
#	  / _ \
#	 / ___ \
#	/_/   \_\
#	
#################################################################
		if ($ch1 eq '+') {
			# A	-	+fqdn:ip:ttl:timestamp:lo
			# CREATE TABLE address (
			#         address_id      NUMBER(9) NOT NULL,
			#         domain_name_id  NUMBER(9) NOT NULL,
			#         access_group_id CHAR(2) NOT NULL,
			#         address_ttl     NUMBER(6),
			#         ip_octet1       char(1),
			#         ip_octet2       char(1),
			#         ip_octet3       char(1),
			#         ip_octet4       char(1),
			#         PRIMARY KEY     (address_id),
			#         FOREIGN KEY     (domain_name_id) REFERENCES domain_name,
			#         FOREIGN KEY     (access_group_id) REFERENCES access_group
			# );
			open(A, ">>A.sql") or die "Can’t open A.sql: $!";
			@field = split(/:/, $line);
			$field[0] =~ s/\.$//;
			print A "INSERT INTO\n";
			print A "	address(\n";
			print A "		domain_name_id,\n";
			print A "		access_group_id,\n";
			print A "		address_ttl,\n";
			print A "		ip_octet1,\n";
			print A "		ip_octet2,\n";
			print A "		ip_octet3,\n";
			print A "		ip_octet4)\n";
			print A "	SELECT \n";
			print A "		d1.domain_name_id,\n";
			if ( $field[4] eq '' ) {
				print A "		'AA', \n"		# access_group_id
			} else {
				print A "		'", uc($field[4]), "', \n"		# access_group_id
			}
			if ( $field[2] eq '' ) {
				print A "		2560, \n"		# address_ttl
			} else {
				print A "		", $field[2], ", \n"		# address_ttl
			}
			@octet = split(/\./, $field[1]);
			print A "		", $octet[0], ",\n";	# ip_octet1
			print A "		", $octet[1], ",\n";	# ip_octet2
			print A "		", $octet[2], ",\n";	# ip_octet3
			print A "		", $octet[3], "\n";	# ip_octet4
			print A "	FROM domain_name d1\n";
			print A "	WHERE d1.name = '", lc(substr($field[0], 1)), "'\n";
			print A "	LIMIT 1;\n\n";
			close(A);
			$domains{lc(substr($field[0], 1))} = 1;
			last SWITCH;
		}
#################################################################
#	  ____ _   _    _    __  __ _____
#	 / ___| \ | |  / \  |  \/  | ____|
#	| |   |  \| | / _ \ | |\/| |  _|
#	| |___| |\  |/ ___ \| |  | | |___
#	 \____|_| \_/_/   \_\_|  |_|_____|
#	
#################################################################
		if ($ch1 eq 'C') {
			# CNAME	-	Cfqdn:p:ttl:timestamp:lo
			# CREATE TABLE cname (
			#         cname_id        NUMBER(9) NOT NULL,
			#         domain_name_id  NUMBER(9) NOT NULL,
			#         access_group_id CHAR(2) NOT NULL,
			#         cname_ttl       NUMBER(6),
			#         target_name_id  NUMBER(9) NOT NULL,
			#         PRIMARY KEY     (cname_id),
			#         FOREIGN KEY     (domain_name_id) REFERENCES domain_name,
			#         FOREIGN KEY     (access_group_id) REFERENCES access_group,
			#         FOREIGN KEY     (target_name_id) REFERENCES domain_name
			# );
			open(CNAME, ">>CNAME.sql") or die "Can’t open CNAME.sql: $!";
			@field = split(/:/, $line);
			$field[0] =~ s/\.$//;
			$field[1] =~ s/\.$//;
			print CNAME "INSERT INTO\n";
			print CNAME "	cname(\n";
			print CNAME "		domain_name_id,\n";
			print CNAME "		access_group_id,\n";
			print CNAME "		cname_ttl,\n";
			print CNAME "		target_name_id)\n";
			print CNAME "	SELECT \n";
			print CNAME "		d1.domain_name_id,\n";
			if ( $field[4] eq '' ) {
				print CNAME "		'AA', \n"		# access_group_id
			} else {
				print CNAME "		'", uc($field[4]), "', \n"		# access_group_id
			}
			if ( $field[2] eq '' ) {
				print CNAME "		2560, \n"		# cname_ttl
			} else {
				print CNAME "		", $field[2], ", \n"		# cname_ttl
			}
			print CNAME "		d2.domain_name_id\n";
			print CNAME "	FROM domain_name d1,\n";
			print CNAME "		domain_name d2\n";
			print CNAME "	WHERE d1.name = '", lc(substr($field[0], 1)), "'\n";
			print CNAME "		AND d2.name = '", lc($field[1]), "'\n";
			print CNAME "		LIMIT 1;\n\n";
			close(CNAME);
			$domains{lc(substr($field[0], 1))} = 1;
			$domains{lc($field[1])} = 1;
			last SWITCH;
		}
#################################################################
#	 ____ _____ ____
#	|  _ \_   _|  _ \
#	| |_) || | | |_) |
#	|  __/ | | |  _ <
#	|_|    |_| |_| \_\
#	
#################################################################
		if ($ch1 eq '^') {
			# PTR	-	^fqdn:p:ttl:timestamp:lo
			# CREATE TABLE ptr (
			#         ptr_id          NUMBER(9) NOT NULL,
			#         address_id      NUMBER(9) NOT NULL,
			#         access_group_id CHAR(2) NOT NULL,
			#         ptr_ttl         NUMBER(6),
			#         target_name_id  NUMBER(9) NOT NULL,
			#         PRIMARY KEY     (ptr_id),
			#         FOREIGN KEY     (address_id) REFERENCES address,
			#         FOREIGN KEY     (access_group_id) REFERENCES access_group,
			#         FOREIGN KEY     (target_name_id) REFERENCES domain_name
			# );
			open(PTR, ">>PTR.sql") or die "Can’t open PTR.sql: $!";
			@field = split(/:/, $line);
			$field[1] =~ s/\.$//;
			$addr = substr($field[0], 1);
			@octet = split(/\./, $addr);
			print PTR "INSERT INTO\n";
			print PTR "	ptr(\n";
			print PTR "		address_id,\n";
			print PTR "		access_group_id,\n";
			print PTR "		ptr_ttl,\n";
			print PTR "		target_name_id)\n";
			print PTR "	SELECT \n";
			print PTR "		a1.address_id,\n";
			if ( $field[4] eq '' ) {
				print PTR "		'AA', \n"		# access_group_id
			} else {
				print PTR "		'", uc($field[4]), "', \n"		# access_group_id
			}
			if ( $field[2] eq '' ) {
				print PTR "		2560, \n"		# ptr_ttl
			} else {
				print PTR "		", $field[2], ", \n"		# ptr_ttl
			}
			print PTR "		d1.domain_name_id\n";
			print PTR "	FROM address a1, domain_name d1\n";
			print PTR "	WHERE d1.name = '", lc($field[1]), "'\n";
			print PTR "		AND a1.ip_octet1 = ", $octet[3], "\n";
			print PTR "		AND a1.ip_octet2 = ", $octet[2], "\n";
			print PTR "		AND a1.ip_octet3 = ", $octet[1], "\n";
			print PTR "		AND a1.ip_octet4 = ", $octet[0], "\n";
			print PTR "		LIMIT 1;\n\n";
			close(PTR);
			$domains{lc($field[1])} = 1;
			last SWITCH;
		}
#################################################################
#	    _    ____ ____ _____ ____ ____     ____ ____   ___  _   _ ____
#	   / \  / ___/ ___| ____/ ___/ ___|   / ___|  _ \ / _ \| | | |  _ \
#	  / _ \| |  | |   |  _| \___ \___ \  | |  _| |_) | | | | | | | |_) |
#	 / ___ \ |__| |___| |___ ___) |__) | | |_| |  _ <| |_| | |_| |  __/
#	/_/   \_\____\____|_____|____/____/___\____|_| \_\\___/ \___/|_|
#	                                 |_____|
#################################################################
		if ($ch1 eq '%') {
			# ACCESS_GROUP	-	%lo:ipprefix
			# CREATE TABLE access_group (
			#         access_group_id         CHAR(2) NOT NULL,
			#         access_group_desc       VARCHAR2(255) NOT NULL,
			#         PRIMARY KEY     (access_group_id)
			# );
			# 
			# CREATE TABLE subnet (
			#         subnet_id       NUMBER(9) NOT NULL,
			#         access_group_id CHAR(2) NOT NULL,
			#         net_octet1 char(1),
			#         net_octet2 char(1),
			#         net_octet3 char(1),
			#         net_octet4 char(1),
			#         mask_octet1 char(1),
			#         mask_octet2 char(1),
			#         mask_octet3 char(1),
			#         mask_octet4 char(1),
			#         PRIMARY KEY     (subnet_id),
			#         FOREIGN KEY     (access_group_id) REFERENCES access_group
			# );
			open(SUBNET, ">>SUBNET.sql") or die "Can’t open SUBNET.sql: $!";
			@field = split(/:/, $line);
			$ACL{substr($field[0], 1)} = 1;
			@octet = split(/\./, $field[1]);
			for($i=0; $i<4; $i++) {
				if ($octet[$i] ne '') { 
					$subnet[$i] = 255;
				} else {
					$subnet[$i] = 0;
					$octet[$i] = 0;
				}
			}
			print SUBNET "INSERT INTO\n";
			print SUBNET "	subnet(\n";
			print SUBNET "		access_group_id,\n";
			print SUBNET "		net_octet1,\n";
			print SUBNET "		net_octet2,\n";
			print SUBNET "		net_octet3,\n";
			print SUBNET "		net_octet4,\n";
			print SUBNET "		mask_octet1,\n";
			print SUBNET "		mask_octet2,\n";
			print SUBNET "		mask_octet3,\n";
			print SUBNET "		mask_octet4)\n";
			print SUBNET "	VALUES(\n";
			print SUBNET "		'", substr($field[0], 1), "',\n";
			print SUBNET "		", $octet[0], ",\n";
			print SUBNET "		", $octet[1], ",\n";
			print SUBNET "		", $octet[2], ",\n";
			print SUBNET "		", $octet[3], ",\n";
			print SUBNET "		", $subnet[0], ",\n";
			print SUBNET "		", $subnet[1], ",\n";
			print SUBNET "		", $subnet[2], ",\n";
			print SUBNET "		", $subnet[3], ");\n\n";
			close(SUBNET);
			last SWITCH;
		}
	}
}
close(INFILE);

&record_domains;
&record_ACLs;

exit 0;

sub record_domains {

	open(DOMAINS, ">>DOMAINS.sql") or die "Cannot open DOMAINS.sql - $!";
	foreach $key (keys %domains) {
		if ($key !~ m/in-addr.arpa/) {
			print DOMAINS "INSERT INTO domain_name(name) VALUES('", $key, "');\n";
		}
	}
	close(DOMAINS);
}

sub record_ACLs {
	open(ACL, ">>ACL.sql") or die "Can’t open ACL.sql: $!";
	foreach $key (keys %ACL) {
		print ACL "INSERT INTO\n";
		print ACL "	access_group(\n";
		print ACL "		access_group_id,\n";
		print ACL "		access_group_desc)\n";
		print ACL "	VALUES(\n";
		print ACL "		'", $key, "',\n";	# ns_id
		print ACL "		'Edit this description.');\n\n";
	}
	close(ACL);
}
