#!/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-May-5 09:00 pm>'
version_short='0.1.0'

from pysqlite2 import dbapi2 as sqlite3
import sqlite, os, sys, datetime
import gtk, hildon
try:
    import osso
    nokia=True
except:
    nokia=False
tags=['GIVEN_NAME',
            'FAMILY_NAME',
            'NOTES',
            'WORK.TELEPHONE',
            'WORK.TITLE',
            'WORK.FAX',
            'WORK.MOBILE',
            'WORK.EMAIL',
            'WORK.WWW',
            'WORK.ADDRESS',
            'COMPANY',
            'HOME.ADDRESS',
            'HOME.EMAIL',
            'HOME.MOBILE',
            'HOME.TELEPHONE',
            'HOME.FAX',
            'HOME.WWW',
            'NAME',
            'BIRTHDAY',
            'PHOTO',
            'MODIFIED']

t_primekey = 0
t_transcode = 1
t_newprimekey = 2
t_name = 20
t_familyname = 4
t_company = 13

c_urn = 0
c_tag = 1
c_value = 2

u_urn = 0
u_name = 1
u_familyname = 2
u_company = 3

e_primekey = 0
e_name = 1
e_familyname = 2
e_givenname = 3
e_email1 = 4
e_email2 = 5
e_email3 = 6

# 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 contacts table from sql2 to sql3
#################################################################
def stage1():
    print >> sys.stderr,'stage1'
    try:
    	os.remove('/home/user/.gpe/zcontactsdumpnew.db')
    	os.remove('/home/user/.gpe/zcontactsdump.db')
    except:
        pass
    dumpdb = sqlite3.connect('/home/user/.gpe/zcontactsdumpnew.db', isolation_level=None)
    dumpdb.text_factory = str
    dumpdbc = dumpdb.cursor()
    dumpdb.execute("""create table IF NOT EXISTS zcontactsdump (
               urn integer primary key,
               [givenname] TEXT, 
               [familyname] TEXT, 
               [notes] VARCHAR(4096), 
               [workphone] TEXT, 
               [worktitle] TEXT, 
               [workfax] TEXT, 
               [workmobile] TEXT, 
               [workemail] TEXT, 
               [workweb] TEXT, 
               [workaddress] TEXT, 
               [company] TEXT, 
               [homeaddress] TEXT, 
               [homeemail] TEXT, 
               [homemobile] TEXT, 
               [homephone] TEXT, 
               [homefax] TEXT, 
               [homeweb] TEXT, 
               [name] TEXT, 
               [birthday] TEXT, 
               [photo] TEXT, 
               [modified] TEXT)
                """)
    caldb = sqlite.connect('/home/user/.gpe/contacts')
    caldbc = caldb.cursor()
    dumpdbc.execute('begin')
    caldbc.execute("SELECT * FROM contacts")
    urn = -1
    for row in caldbc:
#        print 'o=',row
        if row[c_urn] <> urn:
            if urn >= 0:
#                print 'n=',newrow
                dumpdbc.execute('INSERT INTO zcontactsdump VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',(newrow))
#                print uid
            urn = row[c_urn]
            newrow = (urn,) + ('',)*(len(tags))
        try:
            idx = tags.index(row[c_tag])
#                if idx >= 0:
            newrow = newrow[0:idx+1] + (row[c_value],) + newrow[idx+2:] 
#        else:
        except:
            print 'Tag not valid: ', row[c_tag]
#    print >> sys.stderr,'uid=',uid
    if urn >= 0:
#        print 'n2=',newrow
        dumpdbc.execute('INSERT INTO zcontactsdump VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',(newrow))
    dumpdbc.execute('commit')
    caldb.close
    dumpdb.close
    dumpdbc.close
    caldbc.close
    os.rename('/home/user/.gpe/zcontactsdumpnew.db','/home/user/.gpe/zcontactsdump.db')
    print >> sys.stderr,'Finished Part 1'

#################################################################
# Stage 2
# Update gpe calendar from from transaction db
##################################################################
def stage2():
    print >> sys.stderr,'stage2'
    transdb = sqlite3.connect('/home/user/.gpe/zcontactsync.db', isolation_level=None)
    transdb.text_factory = str
    transdbc = transdb.cursor()
    condb = sqlite.connect('/home/user/.gpe/contacts',autocommit=1)
    condbc = condb.cursor()
    transdbc.execute("SELECT * FROM zcontactstrans")
    print transdbc.rowcount

#    print tags
    for row in transdbc:
        print >> sys.stderr, row
        if row[t_transcode] == 'A': 
            for i in range(len(tags)):
                if row[i+3]:
                    condbc.execute('Insert into contacts (urn, tag, value) VALUES(%d,%s,%s)',(row[t_primekey],tags[i],row[i+3]))
            condbc.execute('INSERT into contacts_urn (urn, name, family_name, company) VALUES(%d, %s, %s, %s)' , 
		        (row[t_primekey],row[t_name],row[t_familyname],row[t_company]))
        elif row[t_transcode] == 'C': 
            condbc.execute('DELETE FROM contacts WHERE urn=%d',(row[t_primekey]))
            for i in range(len(tags)):
                if row[i+3]:
                    condbc.execute('Insert into contacts (urn, tag, value) VALUES(%d,%s,%s)',(row[t_primekey],tags[i],row[i+3]))
            condbc.execute('UPDATE contacts_urn SET urn=%d, name=%s, family_name=%s, company=%s WHERE urn=%d' , 
		        (row[t_newprimekey],row[t_name],row[t_familyname],row[t_company],row[t_primekey]))
        else: # D
            condbc.execute('DELETE FROM contacts WHERE urn=%d',(row[t_primekey]))
            condbc.execute('DELETE FROM contacts_urn WHERE urn=%d',(row[t_primekey]))
    condb.close
    transdb.close
    transdbc.close
    condbc.close
#################################################################
# Stage 2B
# Create claws mail email list from transaction db
##################################################################
def stage2b():
    print >> sys.stderr,'stage2B'
    transdb = sqlite3.connect('/home/user/.gpe/zcontactsync.db', isolation_level=None)
    transdb.text_factory = str
    transdbc = transdb.cursor()
    transdbc.execute("SELECT * FROM zcontactsemail ORDER BY name")
    uid = 236406913
    for row in transdbc:
        if uid == 236406913:
            print >> sys.stderr,'Generating claws email  address book'
            infile = open('/home/user/.claws-mail/addrbook/addrbook-000003.xml','w')
            infile.write('<?xml version="1.0" encoding="UTF-8" ?>\n<address-book name="Outlook Address Book" >\n')
        print row
        infile.write('<person uid="' + str(uid) +
            '" first-name="' + row[e_givenname] +
            '" last-name="' + row[e_familyname] +
            '" nick-name="" cn="' + row[e_name] + '" >\n<address-list>\n')
        uid = uid + 1       
        if row[e_email1]:
            infile.write('<address uid="' + str(uid) + '" alias="" email="' + row[e_email1] +  '" remarks="" />\n')
            uid = uid + 1       
        if row[e_email2]:
            infile.write('<address uid="' + str(uid) + '" alias="" email="' + row[e_email2] +  '" remarks="" />\n')
            uid = uid + 1       
        if row[e_email3]:
            infile.write('<address uid="' + str(uid) + '" alias="" email="' + row[e_email3] +  '" remarks="" />\n')
            uid = uid + 1       
        infile.write('</address-list>\n</person>\n')
    if uid > 236406913:
        infile.write('</address-book>\n')
        infile.close

###################################################################################################################
### start of main code
root, vb=application_setup('zcontactsync', 'zcontactsync','.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()
            stage2b()
        else:
            print >> sys.stderr, 'Invalid parms. Must be -stage1 or -stage2'    
    else:
        print >> sys.stderr, 'Invalid parms. Must be -stage1 or -stage2'    
    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()    