#!/usr/bin/perl
# $Id: sync-iPod-Calendar.pl,v 1.2 2008/02/17 17:31:06 fukudat Exp $
#
# Copyright (c) 2008 Takeshi Fukuda.
#
# THIS SOFTWARE IS PROVIDED "AS IS" AND WITHOUT WARRANTY OF ANY KIND.
# You may use, modify, and redistribute this software freely.

use Text::CSV_XS;
use IO::Handle;
use Time::Local;

# = = = = = = = = = = = = = = = =
# iPodCalendar class
# = = = = = = = = = = = = = = = =
package iPodCalendar;

$nkf = 'nkf';
$sqlite3 = 'sqlite3';
$scp = '/usr/bin/scp';

$alarm_interval_all_day = -86400;
$alarm_interval = -300;

# constructor
sub new {
    my ($class, $csvfile, $dbfile, $ipaddr) = @_;
    my $self = {
	csvfile => $csvfile,
	ipaddr => $ipaddr,
	dbfile => $dbfile,
	cache => [],
	remotefile => "/private/var/mobile/Library/Calendar/Calendar.sqlitedb"
    };
    return bless $self, $class;
}

# initialize the database
sub init {
    my ($self) = @_;
    system("rm -f " . $self->{dbfile});
    my $ddl =<<EOF;
CREATE TABLE Alarm (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, trigger_date INTEGER, trigger_interval INTEGER, type INTEGER, entity_id INTEGER, entity_type INTEGER);
CREATE TABLE AlarmChanges (record INTEGER, type INTEGER);
CREATE TABLE Calendar (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, read_only INTEGER);
CREATE TABLE CalendarChanges (record INTEGER, type INTEGER);
CREATE TABLE Event (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, summary TEXT, location TEXT, description TEXT, start_date INTEGER, start_tz TEXT, end_date INTEGER, all_day INTEGER, calendar_id INTEGER, orig_event_id INTEGER, orig_start_date INTEGER);
CREATE TABLE EventChanges (record INTEGER, type INTEGER);
CREATE TABLE EventExceptionDate (event_id INTEGER, date INTEGER, UNIQUE(event_id, date));
CREATE TABLE OccurrenceCache (day INTEGER, event_id INTEGER, occurrence_date INTEGER, occurrence_start_date INTEGER);
CREATE TABLE OccurrenceCacheDays (day INTEGER PRIMARY KEY, count INTEGER);
CREATE TABLE Recurrence (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, frequency INTEGER, interval INTEGER, week_start INTEGER, count INTEGER, cached_end_date INTEGER, cached_end_date_tz TEXT, end_date INTEGER, specifier TEXT, by_month_months INTEGER, event_id INTEGER);
CREATE TABLE RecurrenceChanges (record INTEGER, type INTEGER);
CREATE TABLE Task (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, summary TEXT, priority INTEGER, due_date INTEGER, completion_date INTEGER, calendar_id INTEGER);
CREATE TABLE TaskChanges (record INTEGER, type INTEGER);
CREATE TABLE _SqliteDatabaseProperties (key TEXT, value TEXT, UNIQUE(key));
CREATE INDEX EventCalendarId on Event(calendar_id);
CREATE INDEX EventExceptionDateEventId on EventExceptionDate(event_id, date);
CREATE INDEX EventOriginalEventId on Event(orig_event_id);
CREATE INDEX OccurrenceCacheDay on OccurrenceCache(day);
CREATE INDEX OccurrenceCacheEventId on OccurrenceCache(event_id);
CREATE INDEX OccurrenceCacheOccurrenceDate on OccurrenceCache(occurrence_date);
CREATE INDEX RecurrenceEndCountIndex on Recurrence(end_date, count);
CREATE INDEX RecurrenceEventIdIndex on Recurrence(event_id);
CREATE TRIGGER delete_calendar_members AFTER DELETE ON Calendar
BEGIN
DELETE FROM Task WHERE calendar_id = OLD.ROWID;DELETE FROM Event WHERE calendar_id = OLD.ROWID;
END;
CREATE TRIGGER delete_event_alarms_recurs AFTER DELETE ON Event
BEGIN
DELETE FROM Alarm WHERE entity_id = OLD.ROWID AND entity_type = 2;DELETE FROM Recurrence WHERE event_id = OLD.ROWID;DELETE FROM EventExceptionDate WHERE event_id = OLD.ROWID;DELETE FROM OccurrenceCache WHERE event_id = OLD.ROWID;
END;
CREATE TRIGGER delete_task_alarms AFTER DELETE ON Task
BEGIN
DELETE FROM Alarm WHERE entity_id = OLD.ROWID AND entity_type = 3;
END;
CREATE TRIGGER update_cache_days_after_delete AFTER DELETE ON OccurrenceCache
BEGIN
UPDATE OccurrenceCacheDays SET count = count - 1 WHERE day = OLD.day;DELETE FROM OccurrenceCacheDays WHERE day=OLD.day AND count = 0;
END;
CREATE TRIGGER update_cache_days_after_insert AFTER INSERT ON OccurrenceCache
BEGIN
REPLACE INTO OccurrenceCacheDays VALUES (NEW.day, 1 + IFNULL((SELECT count FROM OccurrenceCacheDays WHERE day = NEW.day), 0));
END;
INSERT INTO Calendar(title, read_only) VALUES('default', 0);
INSERT INTO _SqliteDatabaseProperties VALUES('_ClientVersion', '6');
INSERT INTO _SqliteDatabaseProperties VALUES('_UniqueIdentifier', 'C869D301-7B02-4556-AC37-BF6F556FC901');
INSERT INTO _SqliteDatabaseProperties VALUES('OccurrenceCacheLastSavedDay', '0');
INSERT INTO _SqliteDatabaseProperties VALUES('OccurrenceCacheTimeZone', 'Asia/Tokyo');
EOF
    $self->execute($ddl);
}

# execute a sql statement
sub execute {
    my ($self, $sql) = @_;
    push(@{$self->{cache}}, $sql);
}

# actually run the sql statements
sub commit {
    my ($self) = @_;
    open(DB, "| $sqlite3 " . $self->{dbfile}) || die($sqlite3. ": $!");
    my $sql;
    while ($sql = shift(@{$self->{cache}})) {
	print main::LOG $sql, "\n";
	print DB $sql,"\n";
    }
    close(DB);
}

# add event entry
sub add_event {
    my ($self, $event) = @_;
    my $insert = "INSERT INTO Event(";
    my $values = "VALUES(";
    my $first = 1;
    foreach my $key (keys(%$event)) {
	$insert .= "," unless ($first);
	$values .= "," unless ($first);
	$first = 0;
	$insert .= $key;
	$values .= $event->{$key};
    }
    $insert .= ") ";
    $values .= ");";
    # print STDERR $insert . $values . "\n";
    $self->execute($insert . $values);
}

sub quote {
    (my $s = $_[0]) =~ s/\'/\'\'/g;
    return "'" . $s . "'";
}


sub strtotime {
    my $s = $_[0];
    $s =~ m|([0-9]+)/([0-9]+)/([0-9]+)| || return 0;
    my ($year, $mon, $mday, $hour, $min) = ($1, $2, $3, 0, 0);
    if ($s =~ m|([0-9]+):([0-9]+)|) {
	($hour, $min) = ($1, $2);
    }
    return Time::Local::timelocal(0, $min, $hour, $mday, $mon-1, $year) - 978307200;
}

# load the csv file into the database
sub load {
    my ($self) = @_;
    my $csv = Text::CSV_XS->new({ binary => 1, eol => $/ });
    die($self->{csvfile}. ": $!") unless -r $self->{csvfile};
    open(my $io, "$nkf -w " . $self->{csvfile} . "|") || die($nkf. ": $!");
    while (my $row = $csv->getline($io)) {
	my @f = @$row;
	$self->add_event({ 'summary' => quote($f[0]),
			   'location' => quote($f[1]),
			   'description' => quote($f[2]),
			   'start_date' => strtotime($f[3]),
			   'start_tz' => "'Asia/Tokyo'",
			   'end_date' => strtotime($f[5]),
			   'all_day' => $f[6]=="1"?1:0,
			   'calendar_id' => 1 });
    }
    close($io);
    $self->execute("INSERT INTO OccurrenceCache(day, event_id, occurrence_date) SELECT ((start_date+9*3600)/24/3600)*24*3600-9*3600, rowid, start_date from Event;");
    $self->execute("INSERT INTO Alarm(trigger_date, trigger_interval, type, entity_id, entity_type) select -2147483648,".$alarm_interval.",0,ROWID,2 FROM Event WHERE all_day<>1;");
    $self->execute("INSERT INTO Alarm(trigger_date, trigger_interval, type, entity_id, entity_type) select -2147483648,".$alarm_interval_all_day.",0,ROWID,2 FROM Event WHERE all_day=1;");
}

# send the database to iPod
sub send {
    my ($self) = @_;
    my $cmd = "$scp " . $self->{dbfile} . " root@" . $self->{ipaddr} . ":" . $self->{remotefile};
    # print STDERR "$cmd\n";
    system($cmd) == 0 || die "file transfer failed: $cmd";
}

# = = = = = = = = = = = = = = = =
# main
# = = = = = = = = = = = = = = = =
package main;

sub Usage {
    my $name = $0;
    $name =~ s|.*\/||;
    print STDERR "Usage: ", $name, " csv-file db-file ipod-address\n";
}

if ($#ARGV != 2) {
    Usage($0);
    exit(1);
}

open(LOG, "> sync-iPod-Calendar.log");
$cal = new iPodCalendar($ARGV[0], $ARGV[1], $ARGV[2]);
$cal->init();
$cal->load();
$cal->commit();
$cal->send();
close(LOG);
exit(0);