from datetime import date
import sqlite3

EPOCH_OFFSET = 978278400

conn = sqlite3.connect('banktivity.sql')
conn.row_factory = sqlite3.Row

first_date = None
accounts = set([])

txn_c = conn.cursor()
for txn in txn_c.execute('''SELECT ZPLINEITEM,
                                   ZLINEITEMSOURCE.ZPDATE AS ZPDATE,
                                   ZPAMOUNT,
                                   ZPDETAILS,
                                   ZPACCOUNT,
                                   ZPTRANSACTION,
                                   ZPNOTE,
                                   ZPTITLE
                              FROM ZLINEITEMSOURCE
                         LEFT JOIN ZLINEITEM ON (ZLINEITEM.Z_PK=ZLINEITEMSOURCE.ZPLINEITEM)
                         LEFT JOIN ZTRANSACTION ON (ZLINEITEM.ZPTRANSACTION=ZTRANSACTION.Z_PK)
                          ORDER BY ZLINEITEMSOURCE.ZPDATE ASC'''):
                             #WHERE ZLINEITEMSOURCE.Z_PK=2213
    # Now we have the main bank transaction
    txn_id = txn['ZPTRANSACTION']
    txn_date = date.fromtimestamp(txn['ZPDATE'] + EPOCH_OFFSET)
    txn_note = txn['ZPNOTE']

    if not first_date:
        first_date = txn_date

    if not txn_note:
        if txn['ZPDETAILS'].startswith(txn['ZPTITLE']) and len(txn['ZPDETAILS']) != len(txn['ZPTITLE']):
            txn_note = txn['ZPDETAILS'][len(txn['ZPTITLE']) + 3:]
        elif len(txn['ZPTITLE']) > len(txn['ZPDETAILS']):
            txn_note = txn['ZPTITLE']
        else:
            txn_note = txn['ZPDETAILS']

    print('''{:%Y-%m-%d} * "{:s}"'''.format(txn_date, txn_note))
    print('''  ;{}'''.format(txn['ZPTITLE']))
    print('''  ;{}'''.format(txn['ZPNOTE']))
    print('''  ;{}'''.format(txn['ZPDETAILS']))


    # Get all associated entries
    entry_c = conn.cursor()
    entries = entry_c.execute('''SELECT *
                                   FROM ZLINEITEM
                              LEFT JOIN ZACCOUNT ON (ZACCOUNT.Z_PK=ZLINEITEM.ZPACCOUNT)
                                  WHERE ZPTRANSACTION=?
                               ORDER BY ZLINEITEM.Z_PK ASC''', (txn_id,)).fetchall()
    for entry in entries:
        account = entry['ZPFULLNAME'] or 'UnknownAccount'
        amount = entry['ZPTRANSACTIONAMOUNT']
        width = 51 - len(str(amount))
        print('''  {:<{width}s}        {:.2f} CAD'''.format(account, amount, width=width))
        accounts.add(account)

    print()

for account in accounts:
    print('''{:%Y-%m-%d} open {:s}'''.format(first_date, account))