#!/usr/bin/perl -w

# create SBo.sqlite3 database for SBoTools limnoria plugin.
# run with one argument: the path to the SBo repo. This is
# the directory named after the version number, which contains
# SLACKBUILDS.TXT. A git clone will *not* do, needs to be a
# rsync or wget -r copy.

# Actually only SLACKBUILDS.TXT, TAGS.txt, and the */*/*.info files
# are used.

# database isn't fully normalized. *shrug*.

print <<EOF;
pragma journal_mode = memory;
begin transaction;

create table categories (
	id integer primary key not null,
	name varchar not null);

create table builds (
	id integer primary key not null,
	name varchar not null,
	descrip varchar not null,
	category integer not null,
	version varchar not null,
	maintainer integer not null,
	email integer not null,
	foreign key (category) references categories,
	foreign key (maintainer) references maintainers,
	foreign key (email) references emails);

create table deps (
	build_id integer not null,
	depends_on integer not null,
	foreign key (build_id) references categories,
	foreign key (depends_on) references categories);

create table tags (
	build_id integer not null,
	tag varchar not null,
	foreign key (build_id) references builds);

create table maintainers (
	id integer not null,
	name varchar not null);

create table emails (
	id integer not null,
	addr varchar not null);

create unique index t_idx on tags(build_id, tag);

EOF

$sbopath = shift || ".";

$lastcat = 0;
sub get_cat_id {
	my $catname = shift;
	if(!$catids{$catname}) {
		$lastcat++;
		print "insert into categories values($lastcat, '$catname');\n";
		$catids{$catname} = $lastcat;
	}
	return $catids{$catname};
}

$lastmaint = $lastemail = 0;
sub get_maint {
	my ($cat, $build) = @_;
	open my $info, "<$sbopath/$cat/$build/$build.info" or die $!;
	while(<$info>) {
		/^MAINTAINER="(.*?)"/m and $mname = $1;
		/^EMAIL="(.*?)"/m and $ename = $1;
	}
	close $info;

	if(!$maintids{$mname}) {
		$mname =~ s/'/''/g;
		$lastmaint++;
		print "insert into maintainers values($lastmaint, '$mname');\n";
		$maintids{$mname} = $lastmaint;
	}

	if(!$emailids{$ename}) {
		$ename =~ s/'/''/g;
		$lastemail++;
		print "insert into emails values($lastemail, '$ename');\n";
		$emailids{$ename} = $lastemail;
	}

	return ($maintids{$mname}, $emailids{$ename});
}

open $sbtxt, "<" . $sbopath . "/SLACKBUILDS.TXT" or die $!;
{
	local $/ = '';
	while(<$sbtxt>) {
		my ($name, $cat, $ver, $deps, $desc, $maintid, $emailid);
		$deps = "";
		chomp;
		/^SLACKBUILD NAME:\s+(\S+)$/m and $name = $1;
		/^SLACKBUILD LOCATION:\s+\.\/([^\/]*)\//m and $cat = $1;
		/^SLACKBUILD VERSION:\s+(\S+)$/m and $ver = $1;
		/^SLACKBUILD REQUIRES:\s+(\S.+)\n/m and $deps = $1;
		/^SLACKBUILD SHORT DESCRIPTION:\s+(.+)$/m and $desc = $1;

		if($desc =~ /^$name \((.+)\)$/) {
			$desc = $1;
		}

		$desc =~ s/'/''/g;
		$catid = get_cat_id($cat);
		$buildcat{$name} = $catid;
		$buildver{$name} = $ver;
		$builddeps{$name} = $deps;
		$builddesc{$name} = $desc;

		($maintid, $emailid) = get_maint($cat, $name);
		$buildmaint{$name} = $maintid;
		$buildemail{$name} = $emailid;

		push @builds, $name;
	}
}
close $sbtxt;

print "\n";

$buildid = 0;
for(@builds) {
	$buildid++;
	$buildids{$_} = $buildid;
	print <<EOF;
insert into builds values(
	$buildid,
	'$_',
	'$builddesc{$_}',
	$buildcat{$_},
	'$buildver{$_}',
	$buildmaint{$_},
	$buildemail{$_});

EOF
}

for(keys %builddeps) {
	my $bid = $buildids{$_};
	my @d = split /\s+/, $builddeps{$_};
	#warn "build $_ id $b, deps " . join(",", @d) . "\n";
	for(@d) {
		next if /%README%/;
		print <<EOF;
insert into deps values($bid, $buildids{$_});
EOF
	}
}

open $tagstxt, "<" . $sbopath . "/TAGS.txt" or die $!;
while(<$tagstxt>) {
	my ($build, $t, @tags);
	chomp;
	next if /: No tags found for/;
	($build, $t) = /^([^:]*):\s+(.*)$/;
	@tags = split /,/, $t;
	for(@tags) {
		next if /^\s*$/;
		s/'/''/g;
		print <<EOF;
insert into tags values($buildids{$build}, '$_');
EOF
	}
}

print "commit;\n";