'''
Created on Nov 25, 2015
@author: Brett Paufler
Copyright Brett Paufler


If run as main:
    creates a new sqlite database './sql/grain.db'
    based on current csv in corn and soybean folders
    
    grain.db
    futures
        year, month, day, settle, volume, issue


Nice and compact
Took too long to write
But it works
Looks clean
'''



import sqlite3
from os import listdir, remove
from os.path import isfile
import re
import csv


def dir_paths(dir_path):
    '''Returns file_path for all files in passed dir_path.'''
    return [dir_path + f for f in listdir(dir_path)]

def issue(file_path):
    '''Returns issue name {SX1970 thru CZ2025} from passed file_path.'''
    return re.search('SX\d{4}|CZ\d{4}', file_path).group(0)

def csv_data(csv_file_path):
    '''Returns tuple list of data for passed csv file:
        (year, month, day, settle, volume), as
        (int, int, int, float, float).'''
    #Extracts raw csv data from file as text
    with open(csv_file_path, 'rb') as f:
        csv_data = list(csv.reader(f))
    #Finds Header Indexes
    header = [a.lower() for a in csv_data[0]]
    d_i = header.index('date')
    s_i = header.index('settle')
    v_i = header.index('volume')
    #Builds Tuple List (year, month, day, settle, volume)
    data_list = []
    for line in csv_data[1:]:
        year, month, day = line[d_i].split('-')
        data_list.append([int(year), int(month), int(day),
                           float(line[s_i]), float(line[v_i])])
    return data_list

def create_grain_db():
    '''Creates a new sqlite grain database using current csv data files.
        grain.db includes futures table:
            year, month, day, settle, volume, issue'''
    #Removes Old grain.db if it exists
    name = 'sql/grain.db'
    if isfile(name):
        remove(name)
    #New grain.db is created: note schema 
    conn = sqlite3.connect(name)
    c = conn.cursor()
    c.execute('''CREATE TABLE futures
                        (year integer, month integer, day integer,
                        settle real, volume real, issue text)''')    
    #Data is loaded from these two directories
    path_list = dir_paths('./corn/') + dir_paths('./soybean/')
    for file_path in path_list:
        issue_name = issue(file_path)
        for datum in csv_data(file_path):
            data = str(tuple(datum + [issue_name]))
            sql_command = 'INSERT INTO futures VALUES %s' % data
            print sql_command
            c.execute(sql_command)
    conn.commit()
    print '\nCreated: %s' % name



if __name__ == '__main__':
    #Probably the Only reason to use or call this module
    #Create a new grain sqlite database
    create_grain_db()