use strict;
use warnings;

use String::Escape qw( unprintable );
use Text::ParseWords;
use Term::ReadLine;
use File::Basename;
use Text::Table;
use Text::CSV;
use DBI;

my $db;
run(shift @ARGV || ':memory:');
exit(0);

sub run {
	my $dbfile = shift;
	$db = DBI->connect("dbi:SQLite:dbname=$dbfile", undef, undef, {
		sqlite_see_if_its_a_number => 1,
	});
	my $term = Term::ReadLine->new($0);
	$term->ornaments(0); # no fancy underline
	while(defined ($_ = $term->readline("sdb> "))) {
		next unless /\S/;
		s/^\s*//;
		
		if(/^quit/i) {
			last;
		}
		elsif(/^(load|dump)/i) {
			no strict 'refs';
			my ($method, @args) = shellwords($_);
			my $output = eval { &$method(@args) };
			warn $@ if $@;
			print "$output\n" if $output;
		}
		else {
			my $output = eval { sql_command($_) };
			warn $@ if $@;
			print "$output\n" if $output;
		}
	}
}

sub load {
	my $file = shift || die "usage: load <file> [<table> [<field_separator>]]\n";
	my ($table_name, undef, undef) = shift || fileparse($file, qr/\.[^.]*/);
	my $FS = unprintable(shift) || "\t";

	my $asv = Text::CSV->new({ binary => 1, sep_char => $FS});

	open my $fh, '<', $file or die "open [$file]: $!";
	my $table;
	push @$table, $asv->getline($fh) while(!eof $fh);
	my $n_col = scalar @{$table->[0]};

	$db->prepare("CREATE TABLE $table_name (".join(', ',map { "col".$_ }0..$n_col - 1).");")
		->execute() or die $!;
	my $i = $db->prepare("INSERT INTO $table_name VALUES(".join(", ",('?')x$n_col).");");
	
	for(@$table) {
		$i->execute(@$_) or die $!;
	}
	return @$table." row(s) loaded on table $table_name.";
}

sub dump {
	my $table = shift || die "usage: dump <table> [<file> [<field_separator>]]\n";
	my $file = shift || $table;
	my $FS = unprintable(shift) || "\t";

	my $asv = Text::CSV->new({ binary => 1, sep_char => $FS, eol => $/});
	
	my $s = $db->prepare("SELECT * FROM $table") or die $!;

	open my $fh, '>', $file or die "open [$file]: $!";
	
	$s->execute() or die $!;
	while(my $row = $s->fetch) {
		$asv->print($fh, $row);
	}
	return $s->rows." row(s) written to file $file.";
}

sub sql_command {
	my ($str) = @_;
	my $s = $db->prepare($str) or die $!;
	$s->execute() or die $!;
	my $table;
	push @$table, [ map { defined $_ ? $_ : "undef" } @{$s->{'NAME'}} ];
	while(my $row = $s->fetch) {
		push @$table, [ map{ defined $_ ? $_ : "undef" }@$row ];
	}
	box_format($table);
	return $s->rows.' row(s) affected.';
}

sub box_format {
	my $table = shift;
	my $n_cols = scalar @{$table->[0]};

	my $tb = Text::Table->new(\'| ', '', (\' | ','')x($n_cols-1), \' |');
	$tb->load(@$table);
	my $rule = $tb->rule(qw/- +/);
	my @rows = $tb->body();
	print $rule, shift @rows, $rule, @rows, $rule
		if @rows;
}