#!/usr/bin/python

import sqlite3
from sqlite3 import Error
import cv2
import numpy as np


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return None


def select_all_tasks(conn):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT * FROM ImageData WHERE ImageId >= 1 AND ImageId <= 100")

    rows = cur.fetchall()

    for row in rows:
        print(row)


def select_task_by_priority(conn, priority):
    """
    Query tasks by priority
    :param conn: the Connection object
    :param priority:
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))

    rows = cur.fetchall()

    for row in rows:
        print(row)

def update_task(conn, task):
    """
    update priority, begin_date, and end date of a task
    :param conn:
    :param task:
    :return: project id
    """
    sql = '''UPDATE Images SET CellInfo = ? WHERE ImageId = ?'''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()

def read_image_from_sqlite(conn, task):
    cur = conn.cursor()
    cur.execute("SELECT Content from ImageData where ImageId = 1")
    rows = cur.fetchall()
    for item in rows:

        #--- Decode blob
        print("lenght of item")
        print(type(item[0]))

        nparr  = np.fromstring(item[0], np.uint8)
        img_np = cv2.imdecode(nparr, cv2.IMREAD_COLOR)

        #--- Display image
        cv2.imshow('image',img_np)
        k = cv2.waitKey(0)
        if k == 27:         # wait for ESC key to exit
            cv2.destroyAllWindows()
            break

def main():
    sqlite_db_path = "E:/Demo Timelapse/MTL-DEMO-0F9F-25DA/FP00.sqlite"
    database = sqlite_db_path

    # create a database connection
    conn = create_connection(database)
    with conn:
 #       print("1. Query task by priority:")
 #       select_task_by_priority(conn,1)

        print("2. Query all tasks")
        #select_all_tasks(conn)

  #  update_task(conn,(5,2))
        read_image_from_sqlite(conn, "")


if __name__ == '__main__':
    main()