#!/usr/bin/perl -w

# create SBo.sqlite3 database for SBo limnoria plugin.
# requires SLACKBUILDS.txt and TAGS.txt from the SBo repo.
# https://slackware.uk/slackbuilds.org/14.2/SLACKBUILDS.TXT
# https://slackware.uk/slackbuilds.org/14.2/TAGS.txt

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,
	foreign key (category) references categories);

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 unique index t_idx on tags(build_id, tag);

EOF

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

open $sbtxt, "<" . ($ARGV[0] || "SLACKBUILDS.TXT") or die $!;
{
	local $/ = '';
	while(<$sbtxt>) {
		my ($name, $cat, $ver, $deps, $desc,);
		$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;
		push @builds, $name;
	}
}
close $sbtxt;

print "\n";

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

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, "<" . ($ARGV[1] || "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";