# -*- coding: utf-8 -*-

from storageBase import storageBase
from itemList import itemList
from item import item
from pysqlite2 import dbapi2 as sqliteBackend

class sqlite(storageBase):
    dbVersion = '0.2'

    def __init__(self):
        self.con = sqliteBackend.connect(self.getConfigDir() + '/data.sqlite')
        self.cur = self.con.cursor()

        # wenn todo tabelle noch nicht exsistiert, dann sollten wir sie anlegen
        cur = self.cur.execute("select name from sqlite_master where type='table';")
        tables = cur.fetchall()
        if ('todo',) not in tables or ('control',) not in tables:
            self.init_db()

        # überprüfung ob die db-version identisch ist
        version = self.cur.execute("select value from control where setting='db-version'").fetchone()
        if (sqlite.dbVersion,) != version:
            # TODO: update not init
            self.update_db(version)
            


    def init_db(self):
        print '(re)create todo table...'
        self.cur.execute('drop table if exists todo')
        self.cur.execute('''create table todo (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT(255),
                createdAt INTEGER,
                priority INTEGER,
                desc BLOB
        )''')

        self.cur.execute('drop table if exists control')
        self.cur.execute('''create table control (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                setting TEXT,
                value TEXT
        )''')

        self.cur.execute("insert into control (setting, value) VALUES ('db-version', ?)", (sqlite.dbVersion,))
        self.con.commit()

    def update_db(self, updateFrom):
        print 'updating todo table...'
        if updateFrom == '0.1':
            self.cur.execute('''alter table todo
                add desc BLOB''')
            self.con.commit()
            updateFrom = '0.2'

        self.cur.execute("update  control set value = ? where setting = 'db-version'", (sqlite.dbVersion,))
        self.con.commit()

    def __del__(self):
        self.con.close()
        
    def load(self):
        items = itemList(self)
        todos = self.cur.execute('select * from todo').fetchall()
        for todo in todos:
            items += item(row=todo)
            
        return items

    def notifyChange(self, sender):
        if sender.getId() >= 0:
            self.cur.execute('update todo set title=?, createdAt=?, priority=?, desc=? where id=?',
                             (sender.getTitle(), sender.getCreatedAt(), sender.getPriority(), sender.getId(), sender.getDescription()))
            self.con.commit()
        else:      
            self.cur.execute('insert into todo (title, createdAt, priority, desc) VALUES (?, ?, ?, ?)',
                             (sender.getTitle(), sender.getCreatedAt(), sender.getPriority(), sender.getDescription()))
            self.con.commit()
            sender.setId(self.cur.execute('select last_insert_rowid()').fetchone()[0])