# -*- coding: utf-8 -*-
#
# (c) 2008, 2009, 2010 by
#   Sascha L. Teichmann <sascha.teichmann@intevation.de>
#   Ingo Weinzierl <ingo.weinzierl@intevation.de>
#
# This is Free Software licensed unter the terms of GPLv3 or later.
# For details see LICENSE coming with the source of 'getan'.
#

import logging
import os

try:
    import sqlite3 as db
except ImportError:
    from pysqlite2 import dbapi2 as db

from getan.project import Project, Entry

DEFAULT_DATABASE = "time.db"

CREATE_TABLES = [
"""
CREATE TABLE projects (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    key         VARCHAR(16),
    description VARCHAR(256),
    active      BOOLEAN DEFAULT 1
)
""",
"""
CREATE TABLE entries (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id  INTEGER REFERENCES projects(id),
    start_time  TIMESTAMP NOT NULL,
    stop_time   TIMESTAMP NOT NULL,
    description VARCHAR(256),

    CHECK (strftime('%s', start_time) <= strftime('%s', stop_time))
)
"""
]

LOAD_ACTIVE_PROJECTS = '''
SELECT id, key, description, total
FROM projects LEFT JOIN
(SELECT 
    project_id, 
    sum(strftime('%s', stop_time) - strftime('%s', start_time)) AS total
    FROM entries 
    GROUP BY project_id) ON project_id = id
    WHERE active
'''

LOAD_PROJECT_ENTRIES = '''
SELECT
    id,
    project_id,
    start_time as "[timestamp]",
    stop_time  as "[timestamp]",
    description
FROM
    entries
WHERE
    project_id = %i
ORDER BY
    id
DESC
'''

INSERT_PROJECT_ENTRY = '''
INSERT INTO entries (project_id, start_time, stop_time, description)
VALUES(?,?,?,?)
'''

INSERT_PROJECT = '''
INSERT INTO projects (id, key, description, active) VALUES (?,?,?,1)
'''

DELETE_PROJECT_ENTRY = 'DELETE FROM entries WHERE id = %i'

MOVE_ENTRY = 'UPDATE entries SET project_id = ? WHERE id = ?'

logger = logging.getLogger()

class Backend:

    def __init__(self, database = DEFAULT_DATABASE):
        self.database = database
        self.ensure_exists()
        self.con      = db.connect(database,
                                   detect_types=db.PARSE_DECLTYPES |
                                   db.PARSE_COLNAMES)
        self.con.text_factory = lambda x: unicode(x, "utf-8", "ignore")


    def ensure_exists(self):
        """ Creates the database file if it does not exist. """
        if os.path.isfile(self.database): return

        con, cur = None, None
        try:
            con = db.connect(self.database);
            cur = con.cursor()
            try:
                for sql in CREATE_TABLES:
                    cur.execute(sql)
                con.commit()
            except:
                con.rollback()
                raise
        finally:
            if cur: cur.close()
            if con: con.close()


    def load_projects(self):
        """ Loads active projects from database and returns them as array """
        logger.debug("load active projects from database.")
        cur = None
        try :
            cur = self.con.cursor()
            cur.execute(LOAD_ACTIVE_PROJECTS)

            projects = []
            while True:
                row = cur.fetchone()

                if not row: break
                proj          = Project(*row)
                proj.entries = self.load_entries(proj.id)
                projects.append(proj)

            logger.info("found %i active projects." % len(projects))
            return projects

        finally:
            close(cur)


    def load_entries(self, project_id):
        """ Loads all entries that belong to a specific project """
        logger.debug("load entries that belong to project %s" % project_id)
        cur = None
        try:
            cur = self.con.cursor()
            cur.execute(LOAD_PROJECT_ENTRIES % project_id)

            entries = []
            while True:
                try:
                    row = cur.fetchone()

                    if not row: break
                    entries.append(Entry(*row))
                except:
                    logger.warn("found invalid entry.")

            logger.debug("Found %i entries that belong to project '%i'"
                         % (len(entries), project_id))
            return entries
        finally:
            close(cur)


    def insert_project_entry(self, project, stop_time, desc):
        if project is None: return
        cur = None
        try:
            cur = self.con.cursor()
            cur.execute(INSERT_PROJECT_ENTRY, (
                project.id, project.start, stop_time, desc))
            self.con.commit()
            logger.debug("Added new entry '%s' of project '%s' into db"
                         % (desc, project.desc))

            project.entries = self.load_entries(project.id)
        finally:
            close(cur)

    def insert_project(self, project, key, description):
        if project is None:
            return
        cur = None
        try
            cur = self.con.cursor()
            cur.execute(INSERT_PROJECT, (project, key, description))
            self.con.commit()
            logger.debug("Added a new project '%s' into db" % project)

    def delete_entries(self, entries):
        if entries is None: return

        cur = None
        try:
            cur = self.con.cursor()
            for entry in entries:
                cur.execute(DELETE_PROJECT_ENTRY % entry.id)
                logger.debug("Deleted entry: %s (%d)" % (entry.desc, entry.id))
            self.con.commit()
        finally:
            close(cur)


    def move_entries(self, entries, new_project_id):
        if entries is None or new_project_id is None: return

        cur = None
        try:
            cur = self.con.cursor()
            for entry in entries:
                cur.execute(MOVE_ENTRY, (new_project_id, entry.id))
                logger.debug("Moved entry '%s' (id=%d) to project with id %d."
                             % (entry.desc, entry.id, new_project_id))
            self.con.commit()
        finally:
            close(cur)

def close(cur):
    """ This function closes a database cursor if it is existing """
    if cur:
        try: cur.close()
        except:
            logger.warn("could not close database cursor.")

# vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8: