#!/usr/bin/python
""" 
Copyright (C) Dwayne Zon 2009 <dwayne.zon@gmail.com> 

You may redistribute it and/or modify it under the terms of the GNU General Public License, as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. 

zCalSync.py is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of 
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. 

You should have received a copy of the GNU General Public License along with zCalSync.py.  If not, write to: 
The Free Software Foundation, Inc., 
51 Franklin Street, Fifth Floor 
Boston, MA  02110-1301, USA. 
""" 
version_long='Last Update: <2009-Sep-4 11:04 pm>'
version_short='0.1.0'

import datetime
from pysqlite2 import dbapi2 as sqlite3
import sqlite, time, os, sys
import gtk, hildon, calendar
try:
    import osso
    nokia=True
except:
    nokia=False
t_primekey = 0
t_transcode = 1
t_newprimekey = 2
t_summary = 3
t_description = 4
t_location = 5
t_start = 6
t_duration = 7
t_recur = 8
t_rend = 9
t_alarm = 10
t_calendar = 11
t_eventid = 12
t_rcount = 13
t_rincrement = 14
t_modified = 15
t_byday = 16
t_rexceptions = 17

d_primekey = 0
d_summary = 1
d_description = 2
d_location = 3
d_start = 4
d_duration = 5
d_recur = 6
d_rend = 7
d_alarm = 8
d_calendar = 9
d_eventid = 10
d_rcount = 11
d_rincrement = 12
d_modified = 13
d_byday = 14
d_rexceptions = 15

c_uid = 0
c_tag = 1
c_value = 2

e_uid = 0
e_start = 1
e_duration = 2
e_recur = 3
e_rend = 4
e_alarm = 5
e_calendar = 6
e_eventid = 7
e_rcount = 8
e_rincrement = 9
e_modified = 10
e_byday = 11
e_rexceptions = 12

# create Hildonized dialog window
def application_setup(text_name, sys_name, homedir='.python'):
    global program_name, logfile, root, osso_context, osso_note, context_name, app, vb

    program_name=text_name
    # "context" must start with "org.maemo."
    context_name='org.maemo.'+sys_name
    osso_context=None
    app=None
    # change into data dir
    f=os.getenv('HOME') + '/' + homedir
    if not os.path.exists(f):
        os.mkdir(f)
    os.chdir(f)
    if nokia:
        # log errors to a file for post-mortem debugging w/o a console
        logfile=sys_name+'.log'
        sys.stderr=open(logfile, 'w')
        # Nokia tablet setup
        osso_context=osso.Context(context_name, version_short, False)
        osso_note = osso.SystemNote(osso_context)
        app=hildon.Program()
        root=hildon.Window()
        app.add_window(root)
        root.window_in_fullscreen=False
#        root.connect('key-press-event', cb_key_press)
#        root.connect('window-state-event', cb_window_state_change)
        # note that this also sets the main window title in Hildon
        gtk.set_application_name(program_name)
    else:
        root=gtk.Window()
        root.resize(800, 480)
        root.set_title(program_name)

    # exit gracefully when window is closed
    root.connect('destroy', lambda wid: gtk.main_quit())
    root.connect('delete_event', lambda a1, a2: gtk.main_quit())

    # exit gracefully on shutdown
#    signal.signal(15, lambda a1: gtk.main_quit())

    vb=gtk.VBox()
    root.add(vb)
    return root, vb

#################################################################
# Stage 1
# Dump gpe calendar table from sql2 to sql3
#################################################################
def stage1():
    print >> sys.stderr,'stage1'
    try:
    	os.remove('/home/user/.gpe/zcaldumpnew.db')
    	os.remove('/home/user/.gpe/zcaldump.db')
    except:
        pass
    dumpdb = sqlite3.connect('/home/user/.gpe/zcaldumpnew.db', isolation_level=None)
    dumpdb.text_factory = str
    dumpdbc = dumpdb.cursor()
    dumpdb.execute("""create table IF NOT EXISTS zcaldump (
                uid integer primary key,
                summary VARCHAR(255),
                description VARCHAR(4096),
                location VARCHAR(255),
                start DATETIME,
                duration INTEGER,
                recur INTEGER,
                rend DATETIME,
                alarm INTEGER,
                calendar INTEGER,
                eventid STRING,
                rcount INTEGER,
                rincrement INTEGER,
                modified STRING,
                byday STRING,
                rexceptions STRING)
                """)
    caldb = sqlite.connect('/home/user/.gpe/calendar',autocommit=1)
    caldbc = caldb.cursor()
    newtrans = 0
    dumpdbc.execute('begin')
    caldbc.execute("SELECT * FROM calendar")
#    tuid=0
    uid = 0
    for row in caldbc:
#        print >> sys.stderr, row
        if row[c_uid] <> uid:
            if uid <> 0:
#                if uid <> 221 or tuid==0:
                dumpdbc.execute('INSERT INTO zcaldump (uid, summary, description, location) VALUES (?, ?, ?, ?)',(uid, summary, description, location))
#                    tuid=221
#                print 'uid=',uid
            location = ''
            desc = ''
            summary = ''
            uid = row[c_uid]
        if row[c_tag] == 'location':
            location = row[c_value]
        elif row[c_tag] == 'summary':
            summary = row[c_value]
        else:
            description = row[c_value]
    if uid <> 0:
        dumpdbc.execute('INSERT INTO zcaldump (uid, summary, description, location) VALUES (?, ?, ?, ?)',(uid, summary, description, location))
#    print >> sys.stderr,'uid=',uid
    print >> sys.stderr,'finished part1'

#    dumpdbc.execute('begin')
    caldbc.execute("SELECT * FROM events")
    for row in caldbc:
#      if row[e_rexceptions]:   # debug

#        print >> sys.stderr, row
        if row[e_eventid][0:1] <> 'M' and row[e_eventid][0:1] <> 'S' and row[e_uid] > 0:
		    uid = row[e_uid] *  -1
        else:
		    uid = row[e_uid]
		    if uid > newtrans:
		        newtrans = uid
#        print uid,row[e_eventid]
        if len(row[e_start]) > 10:
            start = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(calendar.timegm(time.strptime(row[e_start],'%Y-%m-%d %H:%M:%S'))))
        else:
            start = row[e_start]
        if row[e_rend]:
#            print 'starrt',row[e_uid],row[e_rend]
            rend = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(calendar.timegm(time.strptime(row[e_rend],'%Y-%m-%d %H:%M:%S'))))
        else:
            rend = ''
#            print row[e_uid],rend
        rexceptions = ''
        if row[e_rexceptions] and len(row[e_rexceptions]) > 9: # and False:
            res = row[e_rexceptions].split(',')
#            print 'ers=',uid,row[e_rexceptions],res
            for re in res:
#                print 're=',re
#                z = time.localtime(int(re))
#                print 'z=',z
#                y = calendar.timegm(z)
#                print 'y=',y
#                re2 = str(calendar.timegm(time.localtime(int(re))))
#                print 're=',re2
                rexceptions = rexceptions + str(calendar.timegm(time.localtime(int(re)))) + ','
#                print rexceptions

            rexceptions = rexceptions[0:-1]
#            print rexceptions
#            x=1/0
        dumpdbc.execute('UPDATE zcaldump SET uid=?, start=?, duration=?, recur=?, rend=?, alarm=?, calendar=?, eventid=?, rcount=?, rincrement=?, modified=?, byday=?, rexceptions=? WHERE uid=?',
         (uid,start,row[e_duration],row[e_recur],rend,row[e_alarm],row[e_calendar],row[e_eventid],row[e_rcount],row[e_rincrement],row[e_modified],row[e_byday],rexceptions,row[e_uid]))
    dumpdbc.execute('commit')
#    print >> sys.stderr,'newtrans=',newtrans
    caldbc.execute('UPDATE calendar SET uid = -uid WHERE uid > %d' ,  (newtrans))
    caldbc.execute('UPDATE events SET uid = -uid WHERE uid > %d' ,  (newtrans))
    print >> sys.stderr,'All done!'
    caldb.close
    dumpdb.close
    dumpdbc.close
    caldbc.close
    os.rename('/home/user/.gpe/zcaldumpnew.db','/home/user/.gpe/zcaldump.db')

#################################################################
# Stage 2
# Update gpe calendar from from transaction db
##################################################################
def stage2():
    print >> sys.stderr,'stage2'
    transdb = sqlite3.connect('/home/user/.gpe/zcalsync.db', isolation_level=None)
    transdb.text_factory = str
    transdbc = transdb.cursor()
    caldb = sqlite.connect('/home/user/.gpe/calendar',autocommit=1)
    caldbc = caldb.cursor()
    if datetime.date.today().day == 1:
      caldbc.execute('vacuum')
      print >> sys.stderr,'Doing a vacuum'

#    caldbc.execute('BEGIN TRANSACTION')
    transdbc.execute("SELECT * FROM zcaltrans")
    for row in transdbc:
#      if row[t_rexceptions]: # and False:
        if row[t_transcode] == 'D':
            print 'del',row[t_primekey]
            caldbc.execute('DELETE FROM calendar WHERE uid=%d',(row[t_primekey]))
            caldbc.execute('DELETE FROM events WHERE uid=%d', (row[t_primekey]))
        else:
            if row[t_rend] == '':
                rend = None
            else:
                rend = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(time.mktime(time.strptime(row[t_rend],'%Y-%m-%d %H:%M:%S'))))
            if len(row[t_start]) > 10:
                start = time.strftime('%Y-%m-%d %H:%M:%S',time.gmtime(time.mktime(time.strptime(row[t_start],'%Y-%m-%d %H:%M:%S'))))
#            print time.strptime(row[t_start],'%Y-%m-%d %H:%M:%S')
#            print time.gmtime(time.mktime(time.strptime(row[t_start],'%Y-%m-%d %H:%M:%S')))
#            print time.mktime(time.strptime(row[t_start],'%Y-%m-%d %H:%M:%S'))
            else:
                start = row[t_start]
#        print row[t_start],start
            rexceptions = ''                   
            if row[t_rexceptions]: # and False:
                res = str(row[t_rexceptions]).split(',')
#            print 'ers=',row[t_primekey],row[t_rexceptions],res
                for re in res:
#                print 're=',re
#                print time.localtime(int(re))

#                z = time.gmtime(int(re))
#                print 'z=',z
#                z=z[0:-1]+(-1,)
#                print 'z=',z
#                y = time.mktime(z)
#                print 'y=',y
                    rexceptions = rexceptions + str(int(time.mktime(time.gmtime(int(re))[0:-1]+(-1,)))) + ','
#                print 'rexcept=',rexceptions
                rexceptions = rexceptions[0:-1]
#            print rexceptions
#  
#        X=1/0         
        if row[t_transcode] == 'A': 
#            print >> sys.stderr, row
            caldbc.execute('Insert into calendar (uid, tag, value) VALUES(%d,"sequence","0")',(row[t_primekey]))
            caldbc.execute('Insert into calendar (uid, tag, value) VALUES(%d,"summary",%s)',(row[t_primekey],row[t_summary]))
            caldbc.execute('Insert into calendar (uid, tag, value) VALUES(%d,"description",%s)',(row[t_primekey],row[t_description]))
            caldbc.execute('Insert into calendar (uid, tag, value) VALUES(%d,"location",%s)',(row[t_primekey],row[t_location]))
#            print 'INSERT into events (uid,start, duration, recur, rend, alarm, calendar, eventid, rcount, rincrement, modified, byday, rexceptions) VALUES(%d, %s, %d, %d, %s, %d, %d, %s, %d, %d, %s, %s, %s)' %                                                                                                                                                (row[t_primekey],start,row[t_duration],row[t_recur],rend,row[t_alarm],row[t_calendar],row[t_eventid],row[t_rcount],row[t_rincrement],row[t_modified],row[t_byday],row[t_rexceptions])
            caldbc.execute('INSERT into events (uid,start, duration, recur, rend, alarm, calendar, eventid, rcount, rincrement, modified, byday, rexceptions) VALUES(%d, %s, %d, %d, %s, %d, %d, %s, %d, %d, %s, %s, %s)' , 
		        (row[t_primekey],start,row[t_duration],row[t_recur],rend,row[t_alarm],row[t_calendar],row[t_eventid],row[t_rcount],row[t_rincrement],row[t_modified],row[t_byday],rexceptions))
        elif row[t_transcode] == 'C':
#            if row[t_primekey] < 0:
#                primekey = row[t_primekey] * -1
#            else:
            primekey = row[t_primekey]
#            print >> sys.stderr,primekey, row[t_primekey],row[t_newprimekey], row[t_eventid]
            caldbc.execute('UPDATE calendar SET uid=%d WHERE uid=%d AND tag="sequence"',(row[t_newprimekey],primekey))
            caldbc.execute('UPDATE calendar SET uid=%d, value=%s WHERE uid=%d AND tag="summary"',(row[t_newprimekey],row[t_summary],primekey))
            caldbc.execute('UPDATE calendar SET uid=%d, value=%s WHERE uid=%d AND tag="location"',(row[t_newprimekey],row[t_location],primekey))
            caldbc.execute('UPDATE calendar SET uid=%d, value=%s WHERE uid=%d AND tag="description"',(row[t_newprimekey],row[t_description],primekey))
            caldbc.execute('UPDATE events SET uid=%d, start=%s, duration=%d, recur=%d, rend=%s, alarm=%d, calendar=%d, eventid=%s, rcount=%d, rincrement=%d, modified=%s, byday=%s, rexceptions=%s WHERE uid=%d', 
		        (row[t_newprimekey],start,row[t_duration],row[t_recur],rend,row[t_alarm],row[t_calendar],row[t_eventid],row[t_rcount],row[t_rincrement],row[t_modified],row[t_byday],rexceptions,primekey))
#    print >> sys.stderr, 'about to commit'
#    caldbc.execute('BEGIN TRANSACTION')
#    caldbc.execute('commit')
    caldb.close
    transdb.close
    transdbc.close
    caldbc.close

###################################################################################################################
### start of main code
root, vb=application_setup('zcalsync', 'zcalsync','.gpe')
print >> sys.stderr, 'Started:', datetime.datetime.now(), ' ',version_long
print >> sys.stderr,'Parms=',sys.argv
#stage2()
try:
#if 1==1:
    if len(sys.argv) > 1:
        if sys.argv[1] == '-stage1':
            stage1()
        elif sys.argv[1] == '-stage2':
            stage2()
        else:
            print >> sys.stderr, 'Invalid parms. Must be -stage1 or -stage2'    
    else:
        print >> sys.stderr, 'Invalid parms. Must be -stage1 or -stage2'    
        raise
    print >> sys.stderr, 'All Done!'
except Exception, e : 
    print >> sys.stderr,e
#if 1==1:
# display logfile
    if nokia:
        sw=gtk.ScrolledWindow()
        sw.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
        vb.pack_start(sw,True,True)
        tv=gtk.TextView()
        tv.set_wrap_mode(gtk.WRAP_WORD)
        tv.set_editable(False)
        buf=tv.get_buffer()
        end_mark=buf.create_mark('end', buf.get_end_iter(), False)
        sw.add(tv)

        # read log file and load widget
        sys.stderr.flush()
        buf.insert_at_cursor(file(logfile, 'rb').read())

        root.show_all()
        tv.scroll_mark_onscreen(end_mark)
        gtk.main()
    