# -*- encoding: utf-8 -*-
import sqlite3
from Crypto.Cipher import Blowfish # pkg install "python-crypto"
from binascii import hexlify, unhexlify

from optparse import OptionParser

help = "EXAMPLE:\n  python usernotes-mail-decrypt.py usernotes-mail.sqlite -k 'Eine lange Passphrase'"

_o = OptionParser(usage="%prog [options] [sqlite-Dateiname]..", description = help)
_o.add_option("-k","--key", metavar="PHRASE", help="Die Passphrase zur Entschluesselung")
_o.add_option("-u","--user", metavar="USERNAME", help="Nur fuer diesen Nutzer alles anzeigen.")
_o.add_option("-v","--verbose", action="store_true")
opts, args = _o.parse_args()

def bsdsum(str): 
    checksum = 0;
    for i in xrange(len(str)):
        checksum = (checksum >> 1) | ((checksum & 1) << 15);
        checksum = (checksum + ord(str[i])) & 65535; # // 0xFFFF
    return checksum;

for filename in args:
    if opts.verbose:
        print "########", filename
        if opts.key: print "########", opts.key, "(%s)" % bsdsum(opts.key)
        print ""
    conn = sqlite3.connect(filename)
    conn.text_factory = str
    db = conn.cursor()

    sql = "SELECT mail_user, mail_date, mail_text, encrypted FROM mail"
    if opts.user: sql += " WHERE mail_user = '%s'" % opts.user
    sql += " ORDER BY mail_date DESC"
    db.execute(sql)

    for mail_user, mail_date, mail_text, encrypted in db:
        print "=======", mail_user, mail_date, "(%s)" % str(encrypted)
        
        if encrypted and opts.key:
            if True:
                try:
                    crypt = Blowfish.new(opts.key, Blowfish.MODE_ECB) # default = CBC
                    ciphertext = unhexlify(mail_text)
                    plaintext = crypt.decrypt(ciphertext)
                    print unicode(plaintext, "utf-8")
                except Exception, e:
                    print "Blowfish OOPS", str(e)
        else:
            print mail_text
        print ""