'''
Created on Nov 25, 2015

@author: Brett Paufler
Copyright Brett Paufler

Use the for exploring as of now
'''

#year, month, day, settle, volume, issue

import sqlite3
from itertools import chain


def cz2015_low_ave_high():
    '''Returns a tuple of (Low, Ave, High) settle prices for 
        corn future CZ2015 traded in the year 2015'''
    #Open database, get cursor
    grainDB = './sql/grain.db'
    conn = sqlite3.connect(grainDB)
    c = conn.cursor()
    
    #Base string for sql statements
    sfw_base = "SELECT %s FROM %s WHERE %s;"
    
    #Creates a cz2015 view, if none exists 
    sql = sfw_base % ('*', 'sqlite_master', "type='view'")
    if 'cz2015' not in chain.from_iterable(c.execute(sql).fetchall()):
        c.execute('''CREATE VIEW cz2015 AS SELECT * FROM
                        futures WHERE issue=="CZ2015" and year=2015;''')
    
    #MIN: 358.25
    sql = sfw_base % ('settle', 'cz2015',
                      'settle == (SELECT min(settle) FROM cz2015)')
    (cz_min,) = c.execute(sql).fetchone()
    print 'MIN: %.2f' % cz_min
    
    #MAX: 451.75
    sql = sfw_base % ('settle', 'cz2015',
                      'settle == (SELECT max(settle) FROM cz2015)')
    (cz_max,) = c.execute(sql).fetchone()
    print 'MAX: %.2f' % cz_max
    
    #AVE: 393.24
    sql = sfw_base % ('sum(settle)/count(settle)', 'cz2015', 'settle')
    (cz_ave,) = c.execute(sql).fetchone()
    print 'AVE: %.2f' % cz_ave
    
    conn.close()
    
    return cz_min, cz_ave, cz_max



if __name__ == '__main__':
    cz2015_low_ave_high()


    print 358/451.0