#!/usr/bin/perl

use strict;
use warnings;
use Time::Piece;

my %sms;
my $numsms = 0;

# Dump the SMS from an N900 backup file:
# 1. Open the backup folder and use an unpacker to open comm_and_cal.zip.
# 2. Navigate to /Root/home/user/.rtcom-eventlogger/ in the archive.
# 3. Open the backup.tgz archive.
# 4. Extract el-v1.db and open it using "SQLite database browser".
# 5. Select "File" -> "Export" -> "Table as CSV file".
# 6. Choose table "Events" and store the file.

# The file now contains the SMS along with some other events. Double
# quotes (") in the SMS text are replaced by "" (double double quotes)
# and new lines are still present. This script takes care of the former
# and replaces them by '' (two apostrophes), but it doesn't handle the
# latter. This makes some quasi-manual preprocessing necessary, which
# can be done using the Geany text editor:
# Replace all ([^"])\n by \1 to get rid of wrapped lines. Repeat this step
# until the pattern is not found anymore.
# If you ignore this step, multi-line messages will be silently discarded.

# When this is done, the script can be run by supplying the converted
# CSV file as standard input and redirecting standard output to another
# CSV file:
# ./n900_smsconv.pl < sms_in.csv > sms_out.csv

# An application like "SMS Tools" can then be used to import the SMS
# into your Android phone (choose the nokia format when asked).

# The database contains some definitions for the various IDs, those
# are shown in the following lists:

# service_id field:
# 1:  RTCOM_EL_SERVICE_CALL
# 2:  RTCOM_EL_SERVICE_CHAT
# 3:  RTCOM_EL_SERVICE_SMS

# event_type_id field:
# 1:  RTCOM_EL_EVENTTYPE_CALL
# 2:  RTCOM_EL_EVENTTYPE_CALL_MISSED
# 3:  RTCOM_EL_EVENTTYPE_CALL_VOICEMAIL
# 4:  RTCOM_EL_EVENTTYPE_CHAT_MESSAGE
# 5:  RTCOM_EL_EVENTTYPE_CHAT_NOTICE
# 6:  RTCOM_EL_EVENTTYPE_CHAT_ACTION
# 7:  RTCOM_EL_EVENTTYPE_CHAT_AUTOREPLY
# 8:  RTCOM_EL_EVENTTYPE_CHAT_JOIN
# 9:  RTCOM_EL_EVENTTYPE_CHAT_LEAVE
# 10: RTCOM_EL_EVENTTYPE_CHAT_TOPIC
# 11: RTCOM_EL_EVENTTYPE_SMS_MESSAGE

# flags field:
# 1:  RTCOM_EL_FLAG_CHAT_GROUP
# 2:  RTCOM_EL_FLAG_CHAT_ROOM
# 3:  RTCOM_EL_FLAG_OPAQUE
# 4:  RTCOM_EL_FLAG_OFFLINE
# 5:  RTCOM_EL_FLAG_SMS_PENDING
# 6:  RTCOM_EL_FLAG_SMS_TEMPORARY_ERROR
# 7:  RTCOM_EL_FLAG_SMS_PERMANENT_ERROR

foreach my $csvline (<STDIN>) {
  chomp $csvline;
  if ($csvline =~ m/
      "(\d+)",                # 1  id                 e.g. "3131"
      "(3)",                  # 2  service_id         e.g. "3"
      "(11)",                 # 3  event_type_id      e.g. "11"
      "(\d+)",                # 4  storage_time       e.g. "1396603341"
      "(\d+)",                # 5  start_time         e.g. "1396603339"
      "(\d+)",                # 6  end_time           e.g. "1396603341" or "0"
      "(\d+)",                # 7  is_read            e.g. "1"
      "(\d+)",                # 8  outgoing           e.g. "0" or "1"
      "(\d+)",                # 9  flags              e.g. "0"
      "(\d+)",                # 10 bytes_sent         e.g. "0"
      "(\d+)",                # 11 bytes_received     e.g. "0"
      "([^"]*)",              # 12 local_uid          e.g. "ring tel ring"
      "([^"]*)",              # 13 local_name         e.g. "<SelfHandle>"
      "([^"]*)",              # 14 remote_uid         e.g. "+4179..."
      "([^"]*)",              # 15 channel            e.g. ""
      "(.*?)",                # 16 free_text          e.g. "abcdef"
      "([^"]*)"               # 17 group_uid          e.g. "6288879"
      $/pxo)
  {
    $sms{$numsms}{'sent'} = int($8);
    $sms{$numsms}{'time'} = $5;
    $sms{$numsms}{'number'} = $14;
    $sms{$numsms}{'msg'} = $16 =~ s/""/''/ro;
    ++$numsms;
  }
}

print STDERR "Parsed $numsms messages.\n";

foreach my $key (sort {$a<=>$b} keys %sms) {
  print '"sms",';
  my $number = $sms{$key}{'number'};
  my $time = localtime($sms{$key}{'time'})->strftime('%Y.%m.%d %H:%M');
  my $text = $sms{$key}{'msg'};
  if ($sms{$key}{'sent'}) {
    print '"SENT",';
    print '"",';
    print "\"$number\",";
  } else {
    print '"READ,RECEIVED",';
    print "\"$number\",";
    print '"",';
  }
    print '"",';
    print "\"$time\",";
    print '"",';
    print "\"$text\"\n";
}