#!/usr/bin/perl
#
# Export Maemo N900 SMS to CSV
# Allows importing to Android phones using "Anything With SMS"
#
# Use this format when importing with Anything With SMS:
# "$(folder)","$(address)","$(dateyyyy-MM-dd HH:mm:ss)","$(body)"\s*
#
# Written by Alan
#
use strict;
use warnings;
use Text::CSV;
use DBI;
use POSIX;

# SQLite format from n900 (file: el-v1.db)
# From an exported backup, in comm_and_cal.zip -> Root/home/user/.rtcom-eventlogger/backup.tgz -> el-v1.db
#
# CREATE TABLE Events (
# id INTEGER PRIMARY KEY,
# service_id INTEGER NOT NULL,
# event_type_id INTEGER NOT NULL,
# storage_time INTEGER NOT NULL,
# start_time INTEGER NOT NULL,
# end_time INTEGER,
# is_read INTEGER DEFAULT 0,
# outgoing BOOL DEFAULT 0,
# flags INTEGER DEFAULT 0,
# bytes_sent INTEGER DEFAULT 0,
# bytes_received INTEGER DEFAULT 0,
# local_uid TEXT,
# local_name TEXT,
# remote_uid TEXT,
# channel TEXT,
# free_text TEXT,
# group_uid TEXT
# );

# Output columns in the order expected by import program (Anything with SMS)
my @out_cols = ('folders', 'number', 'time', 'msg');

die "Usage: $0 <sqlite input> <csv output>" unless $#ARGV == 1;

my ($dbfile, $csvfile) = @ARGV;

my $dbh = DBI->connect('dbi:SQLite:dbname='.$dbfile, '', '', {RaiseError => 1, PrintError => 0});
my $csv = Text::CSV->new( { always_quote => 1, auto_diag => 1, sep => ',', binary => 1}) or die "Error creating CSV object: ", Text::CSV->error_diag;
open(my $csvh, '>', $csvfile) or die "Error opening $csvfile: $!";

my $sth = $dbh->prepare('select * from Events');
my $res = $sth->execute;

# assign column mapping
$csv->column_names(\@out_cols);

# Print CSV header
# Commented because Anything With SMS doesn't like it
# print header to csv
# $csv->print($csvh, \@out_cols);
# $csvh->print("\n");

my $count = 0;
while (my $in = $sth->fetchrow_hashref) {
	next unless $in->{'service_id'} eq '3';
	$count++;
	my $out;
	
	# convert some fields for csv output
	$out->{'type'} = 'sms';
	
	$out->{'x'} = '';
	# flags
	my @folders;
	if ($in->{'outgoing'}) {
		if ($in->{'flags'} eq '0') {
			push(@folders, 'sent');
		} elsif ($in->{'flags'} eq '5') {
			push(@folders, 'submit');
		} else {
			push(@folders, 'outgoing');
		}
	} else {
		if ($in->{'is_read'} eq '1') {
			push(@folders, 'read');
		} else {
			push(@folders, 'read');
		}
	}
	$out->{'folders'} = join(',', @folders);
	$out->{'number'} = $in->{'remote_uid'};
	$out->{'msg'} = $in->{'free_text'};
	
	# Anything with SMS doesn't support escaped quotes or newlines in CSV files
	# Replace double quotes with singles, and newlines with spaces
	if (defined $out->{'msg'}) {
		$out->{'msg'} =~ s{"}{'};
		$out->{'msg'} =~ s{\n}{ };
	}
	$out->{'time'} = strftime('%Y-%m-%d %H:%M:%S', localtime($in->{'start_time'}));
	
	$csv->print_hr($csvh, $out);
	$csvh->print("\n");
}
$dbh->disconnect;
close($csvh);
print "Exported $count messages\n";
exit(0);