# Doc
#  Script to extract Contacts from Sailfish Contact SQLite DB located at 
#
#  
# Links
#  FileFormatdescription: https://docs.fileformat.com/email/vcf/#vcf-30-example
#  Pytho vobject: http://eventable.github.io/vobject/

# ChangeLog
#  2021-08-03 - multiple E-Mails with different types are working correctly

import sqlite3
import vobject
import uuid

SQLconn = sqlite3.connect('Testdata/contacts_with-Phone-Mobile-Fax-Pager-Assistent.db')


try:
    SQLContCur = SQLconn.cursor()
    for row in SQLContCur.execute('SELECT * FROM Contacts'):
        
            # contactID abfragen
            contactID=row[0]
            
            # wir erstellen das Objekt
            vcf = vobject.vCard()
            
            vcf.add('uid').value = str(uuid.uuid4())
            #vcf.add('uid').value = "Testdaten"
            vcf.add('n').value = vobject.vcard.Name( family=row[6], given=row[4] )
            vcf.add('fn').value =row[1]
            
            print("debug: Contact " + row[1])
            
            
            SQLADRCur = SQLconn.cursor()
            for ADRrow in SQLADRCur.execute('SELECT * FROM Addresses JOIN Details on Details.detailId = Addresses.detailId where contactId = ' + str(contactID)):
                adr = vcf.add('ADR').value = vobject.vcard.Address(street=ADRrow[2], city=ADRrow[5], region=ADRrow[4], code=ADRrow[6],country=ADRrow[7])
            
            ## Abfragen Organisation
            SQLORGCur = SQLconn.cursor()
            for ORGrow in SQLORGCur.execute('SELECT * from Organizations where contactId = ' + str(contactID)):
                org = vcf.add('ORG').value =  [str(ORGrow[2]), str(ORGrow[6])]
                
                if ORGrow[4] is not None:
                    title = vcf.add('TITLE').value = str(ORGrow[4])
                
                if ORGrow[3] is not None:
                    role = vcf.add('ROLE').str = value(ORGrow[3])
                
                # Also parameters are possible. Could be read out
                # | columnID | column        |
                # ----------------------------
                # |     0    | detailId	     |
                # |     1    | contactId     |
                # |     2    | name	         |
                # |     3    | role	         |
                # |     4    | title         |
                # |     5    | location	     |
                # |     6    | department    |
                # |     7    | logoUrl       |
                # |     8    | assistantName |

            
            ## Abfragen E-Mail-Adressen
            SQLEmailCur = SQLconn.cursor()
            for Emailrow in SQLEmailCur.execute('SELECT * from EmailAddresses JOIN Details on Details.detailId= EmailAddresses.detailId where EmailAddresses.contactId = ' + str(contactID)):
                
                # debug ausgabe
                print("...debug: " + str(Emailrow[2]) + " at " + str(Emailrow[9]))
                
                email = vcf.add('email')
                email.value = str(Emailrow[2])
    
                # nur den Typ einpflegen, wenn das hier nicht none ist
                if Emailrow[9] != None:
                    email.type_param = str(Emailrow[9])


            SQLPhoneCur = SQLconn.cursor()
            
            ## Abfragen Telefonnummer, Fax, SMS - Nummern kommen aus der gleichen Tabelle
            for Phonerow in SQLPhoneCur.execute('SELECT * from PhoneNumbers JOIN Details on Details.detailId = PhoneNumbers.detailId where PhoneNumbers.contactId = ' + str(contactID)):
                
                # wir müssen die SubTypen unterscheiden
                #Null	voice
                #1	cell
                #2	fax
                #3	pager
                #6	video
                #10	Assistent
                
                
                # debug ausgabe
                print("...debug: " + str(Phonerow[2]) + " at " + str(Phonerow[10]) + " subtype=" + str(Phonerow[3]))
                
                # None is a normal phone Number


                if Phonerow[3] == "1":
                    phcat='cell'
                elif Phonerow[3] == "2":
                    phcat='fax'
                elif Phonerow[3] == "3":
                    phcat='pager'
                elif Phonerow[3] == "6":
                    phcat='video'
                elif Phonerow[3] == "10":
                    phcat='assistent'
                elif Phonerow[3] is None:
                    phcat='voice'
                
                print("...debug: " + phcat)

                phone = vcf.add(phcat).value = str(Phonerow[2])
                
                # nur den Typ einpflegen, wenn das hier nicht none ist
                if Phonerow[10] != None:
                    try:
                        phone.type_param = str(Phonerow[10])
                    except AttributeError:
                        continue

            # Ausgabe
            print(vcf.serialize())

# hier brauchen wir einige eception handles -> wie bekommen wir die einzelnen exceptions heruas ?
#except:
    #print("Error in executing SQL")
    
    
except AttributeError:
    print("Datatype mismatch")
    raise

# das generöse Except am Ende    
except:
    print("unhandled error")
    raise