#! /usr/bin/perl

use strict;
use warnings;

use DBI;
use Data::Dumper;
use DateTime;

my $dbh = DBI->connect("dbi:SQLite:dbname=sms.db", "", "", {
	RaiseError => 1
}) or die "Can't connect";

# Output format should be: (on one line)
# sms
# \t
# deliver (if inbound), submit (if outbound)
# \t
# "$remote_phone_number" (with quotes, empty if outbound)
# \t
# "$remote_phone_number" (with quotes, empty if inbound)
# \t
# "$year.$month.$day $hour:$min" (with quotes, full year, full month)
# \t
# $text

my $smses = $dbh->prepare("SELECT * from message");
$smses->execute();

my $d = 1;

while (my $sms = $smses->fetchrow_hashref) {
	delete $sms->{svc_center} if $d;
	delete $sms->{group_id} if $d;
	delete $sms->{association_id} if $d;
	delete $sms->{height} if $d;
	delete $sms->{uiflags} if $d;
	delete $sms->{version} if $d;

	my $dt = DateTime->new(year => 1970,
			       month => 1,
			       day => 1)
		+
		DateTime::Duration->new(seconds =>
					$sms->{date});
	my $date = $dt->ymd('.') . " " . $dt->hms(":");
	$date =~ s/:\d\d$//;

	delete $sms->{date} if $d;

	my $address = $sms->{address};
	my $text = $sms->{text};

	if ($sms->{flags} == 2) {
		# inbound
		print qq[sms\tdeliver\t"$address"\t""\t""\t"$date"\t""\t"$text"\n];
	} elsif ($sms->{flags} == 3) {
		# outbound
		print qq[sms\tsubmit\t""\t"$address"\t""\t"$date\t""\t"$text"\n];
	} elsif ($sms->{flags} == 129) {
		next;
	}

#	print Data::Dumper->Dump([\$sms]);
}

__END__