# Jay Summet
# CS 1803 Example Program
# Copyright 2010
#
# This example program demonstrates retrieving data from a
# MySQL database and allowing the user to examine it visually
# using a simple tkinter GUI. 
#

#We are using a version of pymysql that works with python 3.
#This is not installed by default with python, so you must find and
#install it on your computer before you can import it.
import pymysql

#Import needed for the GUI
from tkinter import *

# This method retrieves the data from the database and
# returns it as a list of tuples (ordered by date!)
def retrieveData(dbhost, username, password):

    #connect to the database:
    db = pymysql.connect( host=dbhost, passwd= password,
                          user=username, db='cs1803_ie')

    #Create a cursor to use for our query
    cursor = db.cursor()

    #Execute the query. (Note, 'demand' is the name of the table,
    # and is ALSO the name of a data column IN the table...perhaps
    # this is an example of bad data design/naming conventions...)

    sql = "SELECT name, date, demand FROM demand ORDER BY date"
    cursor.execute(sql)

    #Copy  the data from the cursor object into a list of tuples...

    #The first item (name - index 0) is a string.
    #
    #The 2nd item (date - index 1) is returned as a datetime.date
    #object. 
    
    #
    #The third item (demand - index 2) is an integer.
    # (Depending upon who has access to your database, you may
    #  need to watch out for malformed data...)
    data = []
    for item in cursor:
        data.append(item)

    return(data)


class DemandViewer():
    CANVAS_HEIGHT = 60

    def __init__(self, tkMainWin):
        self.data = retrieveData( 'academic-mysql.cc.gatech.edu',
                        'cs1803_ie', 'SECRET_DB_PASSWORD')

        frame = Frame(tkMainWin)

        self.label = Label(frame )  #Yes, no text yet!
                                    #The scaleChange function is called
                                    #once even before a user moves it!
        self.label.pack()

        #Note that basing the width on the number of data items fails
        #horribly if the number of data items is terribly small, or
        #terribly large!
        
        #Make a canvas (do this part last)
        self.canvas = Canvas(frame,width= len(self.data),
                             height = self.CANVAS_HEIGHT,
                             relief=GROOVE, borderwidth=1 )
        self.canvas.pack()
        
        self.scale = Scale(frame, from_ = 0,
                           to = len(self.data) -1, # -1 very important!
                           orient=HORIZONTAL,
                           length=300,
                            command = self.scaleChanged )
        self.scale.pack()

        frame.pack()

    def scaleChanged(self,event):
        position = self.scale.get()
        print(position)

        #This fancy formatting is nicer than just setting the text
        # to str( self.data[position] ), but that works too....
        dataItem = self.data[position]
        displayText = str(dataItem[1]) + " : " +dataItem[0] + " : "  + str( dataItem[2])
        self.label.config(text = displayText)

        self.drawCanvas( position)


    #Draws one line on the canvas for each data item.
    
    def drawCanvas(self, hightlightPosition):

        #Delete anything currently on the canvas:
        self.canvas.delete("line")

        #Draw a line on the item based upon the number in position
        # index 2 for each data item...Tag each line with the
        #"line" tag so we can delete them all together at once if needed...
        position = 0
        for item in self.data:
            height = item[2]
            top = self.CANVAS_HEIGHT -1 - height

            lineX = position
            bottom = self.CANVAS_HEIGHT - 1

            fillColor = "black"
            if position == hightlightPosition:
                fillColor="red"
            
            line = self.canvas.create_line(lineX, bottom,
                                           lineX,top,
                                           fill=fillColor,
                                            tag="line")
           

            #Move on to the next position in the gui!
            position = position + 1
            
        
        
    




mainWin = Tk()
dvdemo = DemandViewer(mainWin)
mainWin.mainloop()