#! /usr/bin/perl

use strict;
use warnings;

use DBI;
use Data::Dumper;
use Text::vCard::Addressbook;
use DateTime;

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

my $contacts = $dbh->prepare("SELECT oid,* from ABPerson");
my $multivals = $dbh->prepare("SELECT * from ABMultivalue where record_id = ?");
my $special = $dbh->prepare("SELECT a.value as value, b.value as type from ABMultivalueEntry a, ABMultiValueEntryKey b WHERE a.key = b.oid AND parent_id = ?");

$contacts->execute();
my $ab = Text::vCard::Addressbook->new;

my $d = 0;

while (my $contact = $contacts->fetchrow_hashref) {
	delete $contact->{JobTitle} if $d;
	delete $contact->{Note} if $d;
	delete $contact->{Department} if $d;
	delete $contact->{DisplayName} if $d;
	delete $contact->{CompositeNameFallback} if $d;
	delete $contact->{Prefix} if $d;
	delete $contact->{Suffix} if $d;
	delete $contact->{FirstPhonetic} if $d;
	delete $contact->{LastPhonetic} if $d;
	delete $contact->{ExternalIdentifier} if $d;
	delete $contact->{StoreID} if $d;
	delete $contact->{LastSortSection} if $d;
	delete $contact->{FirstSortSection} if $d;
	delete $contact->{FirstSortLanguageIndex} if $d;
	delete $contact->{LastSortLanguageIndex} if $d;
	delete $contact->{LastSort} if $d;
	delete $contact->{FirstSort} if $d;
	delete $contact->{MiddlePhonetic} if $d;
	my $vc = $ab->add_vcard;
	$vc->version("2.1");
	if (defined $contact->{Organization}) {
		my $o = $vc->add_node({'node_type' => "ORG"});
		$o->name($contact->{Organization});
	}
	delete $contact->{Organization} if $d;

	{
		my $n = $vc->add_node({'node_type' => "N"});
		$n->family($contact->{Last});
		$n->middle($contact->{Middle});
		$n->given($contact->{First});
		my $full = join " ", ($n->given || ""), ($n->middle || ""), 
		  ($n->family || "");
		$full =~ s/  / /g;
		$vc->fullname($full);
		delete $contact->{Last} if $d;
		delete $contact->{Middle} if $d;
		delete $contact->{First} if $d;
	}

	if (defined $contact->{Nickname}) {
		$vc->nickname($contact->{Nickname});
	}
	delete $contact->{Nickname} if $d;

	my $dt = DateTime->new(year => 2000,
			       month => 1,
			       day => 1)
		+
		DateTime::Duration->new(seconds =>
					$contact->{ModificationDate});
	$vc->REV($dt->ymd('') . "T" . $dt->hms('') . "Z");
	delete $contact->{ModificationDate} if $d;
	delete $contact->{CreationDate} if $d;

	$multivals->execute($contact->{ROWID});
	while (my $mv = $multivals->fetchrow_hashref) {
		my $n;
		if ($mv->{property} == 3) {
			# Phone number
			$n = $vc->add_node({'node_type' => "TEL"});
			$n->value($mv->{value});
		} elsif ($mv->{property} == 4) {
			# Email
			$n = $vc->add_node({'node_type' => "EMAIL"});
			$n->value($mv->{value});
		} elsif ($mv->{property} == 5) {
			# Custom, but some kind of address
			$n = $vc->add_node({'node_type' => "ADR"});

			$special->execute($mv->{UID});
			while (my $sp = $special->fetchrow_hashref) {
				if ($sp->{type} eq "ZIP") {
					$n->post_code($sp->{value});
				} elsif ($sp->{type} eq "City") {
					$n->city($sp->{value});
				} elsif ($sp->{type} eq "Street") {
					$n->street($sp->{value});
				} elsif ($sp->{type} eq "Country") {
					$n->country($sp->{value});
				} elsif ($sp->{type} eq "CountryCode") {
					if ($sp->{value} eq "no") {
						$n->country("Norway");
					} elsif ($sp->{value} eq "uk")  {
						$n->country("United Kingdom");
					} else {
						die "Unknown country: " . $sp->{value};
					}
				}
			}
		} elsif ($mv->{property} == 12) {
			# Flag day
		} else {
			die "Unknown property". $mv->{property};
		}

		if ($mv->{label} == 1) {
			# Home
			$n->add_types('iphone');
		} elsif ($mv->{label} == 2) {
			# Home
			$n->add_types('home');
		} elsif ($mv->{label} == 3) {
			# Mobile
			$n->add_types('cell');
		} elsif ($mv->{label} == 4) {
			# Work
			$n->add_types('work');
		} elsif ($mv->{label} == 5) {
			# Main
			$n->add_types('pref');
		} elsif ($mv->{label} == 6) {
			# Other
			$n->add_types('other');
		} elsif ($mv->{label} == 7) {
			# Flag day – ignore
		} elsif ($mv->{label} == 8) {
			# Fax work
			$n->add_types('work');
			$n->add_types('fax');
		} else {
			die "Unknown label: " . $mv->{label};
		}
#		print Data::Dumper->Dump([\$mv]);
	}
#	print Data::Dumper->Dump([\$contact]);
}

print $ab->export;

__END__

CREATE TABLE ABMultiValue (UID INTEGER PRIMARY KEY, record_id INTEGER, property INTEGER, identifier INTEGER, label INTEGER, value TEXT);

property: 12 identifier: 0 label: 7 - merkedag
property: 4 (email)
property: 3: phone
  label 2: hjem
  label 3: mobil
  label 4: arbeid
  label 5: hoved
  label 6: annet
  label 8: faks arbeid

CREATE TABLE ABMultiValueEntry (parent_id INTEGER, key INTEGER, value TEXT, UNIQUE(parent_id, key));
CREATE TABLE ABMultiValueEntryKey (value TEXT, UNIQUE(value));
CREATE TABLE ABMultiValueLabel (value TEXT, UNIQUE(value));

CREATE TABLE ABPerson (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, First TEXT, Last TEXT, Middle TEXT, FirstPhonetic TEXT, MiddlePhonetic TEXT, LastPhonetic TEXT, Organization TEXT, Department TEXT, Note TEXT, Kind INTEGER, Birthday TEXT, JobTitle TEXT, Nickname TEXT, Prefix TEXT, Suffix TEXT, FirstSort TEXT, LastSort TEXT, CreationDate INTEGER, ModificationDate INTEGER, CompositeNameFallback TEXT, ExternalIdentifier TEXT, StoreID INTEGER, DisplayName TEXT, FirstSortSection TEXT, LastSortSection TEXT, FirstSortLanguageIndex INTEGER DEFAULT 2147483647, LastSortLanguageIndex INTEGER DEFAULT 2147483647);