import openpyxl
import datetime as dt
import os
import re
from update_excel_workbooks import convert_to_numbers, copy_data, store_data_to_txt_file
from CONSTANTS import CALL_DESIGNATION_PATTERN, PUT_DESIGNATION_PATTERN


def group_contracts_by_strike(reference_wb):
    '''
    Given a workbook with many option contracs, sheet names that contain options with the same strike price are grouped together.
    Returns a dictionary with keys representing the strike and type of the option (call or put), and the value being a list of
    sheet names corresponding to each option for the given strike.
    '''
    #a dictionary to store the sorted data
    options_contracts = {}

    #exclude the first sheet because that isn't an options contract
    contract_list = reference_wb.get_sheet_names()[1:]

    #loop through each contract sheet:
    for (index, contract) in enumerate(contract_list):
        #split the sheet name by whitespace and take only the last item in the list
        #the last item will either look similar to 'C(some numbers)' or 'P(some numbers)'
        contract_type = contract.split(' ')[-1]
        
        #if the contract is a call set the default value, create a new key for the contract if it
        #doesn't already exist, increase the count by 1, and append the contract to the appropriate list 
        if re.match(CALL_DESIGNATION_PATTERN, contract_type):
            options_contracts.setdefault('call',{})
            options_contracts['call'].setdefault(contract_type, [])
            options_contracts['call'][contract_type].append(contract)

        #if the contract is a put set the default value, create a new key for the contract if it
        #doesn't already exist, increase the count by 1, and append the contract to the appropriate list
        elif re.match(PUT_DESIGNATION_PATTERN, contract_type):
            options_contracts.setdefault('put',{})
            options_contracts['put'].setdefault(contract_type, [])
            options_contracts['put'][contract_type].append(contract)

    #finally return the options_contracts dictionary
    return options_contracts


def group_contracts_by_expiration(reference_wb):
	'''
	Given a workbook with many option contracs, those that have the same expiration date are grouped together.
	Returns a dictionary with keys representing the expiration date and type of the option (call or put), and the value being a list of
	sheet names corresponding to each option with the given expiration date.
	'''
	#a dictionary to store the sorted data
	options_contracts = {}

	#exclude the first sheet because that isn't an options contract
	contract_list = reference_wb.get_sheet_names()[1:]

	#loop through each contract sheet:
	for (index, contract) in enumerate(contract_list):
		#split the sheet name by whitespace and take only the last item in the list
		#the last item will either look similar to 'C(some numbers)' or 'P(some numbers)'
		contract_type = contract.split(' ')[-1]
		expiration = contract.split(' ')[2]

		#if the contract is a call set the default value, create a new key for the contract if it
		#doesn't already exist, increase the count by 1, and append the contract to the appropriate list 
		if re.match(CALL_DESIGNATION_PATTERN, contract_type):
		    options_contracts.setdefault('call',{})
		    options_contracts['call'].setdefault(expiration, [])
		    options_contracts['call'][expiration].append(contract)

		#if the contract is a put set the default value, create a new key for the contract if it
		#doesn't already exist, increase the count by 1, and append the contract to the appropriate list
		elif re.match(PUT_DESIGNATION_PATTERN, contract_type):
		    options_contracts.setdefault('put',{})
		    options_contracts['put'].setdefault(expiration, [])
		    options_contracts['put'][expiration].append(contract)

    #finally return the options_contracts dictionary
	return options_contracts


def create_sorted_sheet(new_workbook, reference_wb, new_sheet_title, reference_sheet_list, data_start_row, data_column, index_column):
    '''
    Given the following arguments, a new worksheet is created in the new workbook, with sorted data taken from worksheets of the reference_wb

    new_workbook          should be a new openpyxl Workbook where the data will be stored
    
    reference_wb          should be an openpyxl.workbook.Workbook containig the sheets contined in reference_sheet_list
    
    
    new_sheet_title       should be a string with the name that you would like to give the newly created sheet
            
    
    reference_sheet_list  should be a list of sheets that you would like to pull data from
    
    data_start_row        should be an integer indicating which row or the reference_sheet the data starts on
    
    data_column           should be a list of numbers, where 1=column A, 2=column B, 3=column C, and so on. 
                          specifies which columns of data are of interest from the reference_sheet contained in
                          in reference_sheet_list
    
    index_column          should be a list of numbers, where 1=column A, 2=column B, 3=column C, and so on.
                          specifies which columns from the reference_sheet_list[0], you want to use as your index's
                          NOTE: For accurate indexing, each sheet of data referenced should have the same index
    '''
    #creates a new sheet where the combined data will be stored and names it based on the passed in new_sheet_title argument
    #If the only sheet is the default 'Sheet', then rename it to the desired new_sheet_title,
    if new_workbook.get_sheet_names()[0] == 'Sheet':
        new_sheet = new_workbook.get_active_sheet()
        new_sheet.title = new_sheet_title 
    #else create an entirely new sheet and give it the apporpiate new_sheet_title
    else:
         new_sheet = new_workbook.create_sheet(title = new_sheet_title)
        
    #iterate over all the sheets in the reference_wb that were passed in reference_sheet_list
    for (i,sheet_name) in enumerate(reference_sheet_list):  
        #if its the first iteration, update the sheet with the index_colum and the data_column
        if i ==0:
            #load in the new worksheet.
            data_sheet = reference_wb.get_sheet_by_name(sheet_name)
            
            #get the max row of the index column
            max_row = find_end_index_row(reference_sheet=data_sheet, index_column=index_column[0])
            
            #iterate over the columns and the rows that we're interested in
            for (j,column_num) in enumerate(index_column+data_column):
                #find the max_column of the new_worksheet
                max_col = new_sheet.max_column
                
                #if we reach the fist item in the data column set a header for the contract name
                if column_num == data_column[0]:
                    #the header is set at the current max_col+1, which data will go under
                    new_sheet.cell(row= data_start_row-1, column= max_col+1).value = sheet_name
                    
                if j == 0:
                    copy_data(reference_sheet=data_sheet, main_sheet=new_sheet,index_start_row=data_start_row, index_end_row=max_row, reference_data_column=column_num, main_data_column=max_col)
                else:
                    copy_data(reference_sheet=data_sheet, main_sheet=new_sheet,index_start_row=data_start_row, index_end_row=max_row, reference_data_column=column_num, main_data_column=max_col+1)

        #else: just grab the data_column    
        else:
            #load in the new worksheet.
            data_sheet = reference_wb.get_sheet_by_name(sheet_name)
            
            #get the max row of the index column
            max_row = find_end_index_row(reference_sheet=data_sheet, index_column=index_column[0])
            
            #iterate over the columns in the data_sheet that we're interested in
            for (j,column_num) in enumerate(data_column):
                #find the max_column of the new_worksheet
                max_col = new_sheet.max_column
                
                #if we reach the fist item in the data column set a header for the contract name
                if column_num == data_column[0]:
                    #the header is set at the current max_col+1, which data will go under
                    new_sheet.cell(row= data_start_row-1, column=max_col+1).value = sheet_name

                copy_data(reference_sheet=data_sheet, main_sheet=new_sheet,index_start_row=data_start_row, index_end_row=max_row, reference_data_column=column_num, main_data_column=max_col+1)
 


def create_sorted_workbooks(reference_wb_path, header_start_row, data_column, index_column, sort_by_strike=True, sort_by_expiration=False):
    '''
    Creates a new workbook, containing sorted data in each of its sheets

    reference_wb_path   should be the path to the workbook that data will be taken from

    header_start_row    is a variable that will be passed to the call of create_sorted_sheet()

    data_column         is a variable that will be passed to the call of create_sorted_sheet()

    index_column        is a variable that will be passed to the call of create_sorted_sheet()

    sort_by_strike		A boolean that determins weather the new options workbook will be sorted by strike, defaults to True

    sort_by_expiration	A boolean that determins weather the new options workbook will be sorted by expiration, defaults to False
    '''
    #initial check to ensure that the user has designated one sort method, but not both. By defualt sort_by_stike is True.
    if(sort_by_strike and sort_by_expiration):
        return print('Error: Choose either to sort by strike or by expiration')

    elif((not sort_by_strike) and (not sort_by_expiration)):
        return print('Error: Choose to sort by strike or by expiration')

    #loads the reference workbook
    reference_wb = openpyxl.load_workbook(reference_wb_path)

    #formats the data_column, and index_column in case they were imput as letters.
    data_column = convert_to_numbers(data_column)
    index_column = convert_to_numbers(index_column)

    #creats the new workbooks.
    new_call_wb = openpyxl.Workbook()
    new_put_wb = openpyxl.Workbook()

    #adds the stock sheet to the new_call_wb.
    create_sorted_sheet(new_workbook =new_call_wb, reference_wb= reference_wb,
                        new_sheet_title= 'Stock Price', reference_sheet_list=[reference_wb.get_sheet_names()[1]],
                        data_start_row= header_start_row, data_column= [data_column[0]],
                        index_column= index_column)

    #adds the stock sheet to the new_put_wb.
    create_sorted_sheet(new_workbook =new_put_wb, reference_wb= reference_wb,
                        new_sheet_title= 'Stock Price', reference_sheet_list=[reference_wb.get_sheet_names()[1]],
                        data_start_row= header_start_row, data_column= [data_column[0]],
                        index_column= index_column)

    #sets the value of the variable contracts based on the type of sort designated
    if sort_by_strike:
        #stores the outpuf of group_contracts_by_strike(), which is a nested dictionary that looks similar to:
        #{ {'call':{'C55':['List of contract sheets'], ....}, {'put':{'P55':['List of contract sheets']} } } }
        contracts=group_contracts_by_strike(reference_wb= reference_wb)
        #creates a list of sorted keys for both the call and the put dictionary
        sorted_call_list = sort_strike_prices(list(contracts['call'].keys()))
        sorted_put_list = sort_strike_prices(list(contracts['put'].keys()))

    elif sort_by_expiration:
        #stores the outpuf of group_contracts_by_expiration(), which is a nested dictionary that looks similar to:
        #{ {'call':{'03-20-15':['List of contract sheets'], ....}, {'put':{'03-20-15':['List of contract sheets']} } } }
        contracts = group_contracts_by_expiration(reference_wb= reference_wb)
        #creates a list of sorted keys for both the call and the put dictionary
        sorted_call_list = sort_expiration_dates(list(contracts['call'].keys()))
        sorted_put_list = sort_expiration_dates(list(contracts['put'].keys()))


    #iterate over all the keys in the 'call' dictionary stored in contracts 
    for (index, key) in enumerate(sorted_call_list):
        #create a sorted sheet in the new workbook for every strike price
        create_sorted_sheet(new_workbook =new_call_wb, reference_wb= reference_wb,
                            new_sheet_title= key, reference_sheet_list=contracts['call'][key],
                            data_start_row= header_start_row, data_column= data_column,
                            index_column= index_column)

    #iterate over all the keys in the 'put' dictionary stored in contracts 
    for (index, key) in enumerate(sorted_put_list):
        #create a sorted sheet in the new workbook for every strike price
        create_sorted_sheet(new_workbook =new_put_wb, reference_wb= reference_wb,
                            new_sheet_title= key, reference_sheet_list=contracts['put'][key],
                            data_start_row= header_start_row, data_column= data_column,
                            index_column= index_column)

    if sort_by_strike:
        #save new_call_wb
        save_new_workbook(new_workbook=new_call_wb, workbook_path= reference_wb_path,
                            new_folder='call_by_strike', append_file_name='call_by_strike')
        #save new_put_wb
        save_new_workbook(new_workbook= new_put_wb, workbook_path= reference_wb_path,
                            new_folder= 'put_by_strike', append_file_name='put_by_strike')

    elif sort_by_expiration:
        #save new_call_wb
        save_new_workbook(new_workbook=new_call_wb, workbook_path= reference_wb_path,
                            new_folder='call_by_expiration', append_file_name='call_by_expiration')
        #save new_put_wb
        save_new_workbook(new_workbook= new_put_wb, workbook_path= reference_wb_path,
                            new_folder= 'put_by_expiration', append_file_name='put_by_expiration')


def save_new_workbook(new_workbook,workbook_path,new_folder,append_file_name):
    '''
    Saves the new file into a newly created folder at the end of the current path file path, 
    and appends the file name.
    '''
    #breakes the path into a list split by '/'
    path_list = workbook_path.split('/')

    #the last item in the split_path is removed and returned from pop().
    #By defualt pop removes and returns the last item, but it is explicilty removed below.
    #the file name is the last item, and we split that by the '.' yeilding a new list in the form ['file_name','file_extension']
    split_file_name = path_list.pop(-1).split('.')
    
    #creates a new file name from the existing name, and the new_folder's value
    new_file_name = '{}_{}'.format(split_file_name[0], append_file_name)

    #appends the name of the new folder to the end of the path
    path_list.append(new_folder)

    #takes the list and converts it to a string representing the new path.
    new_path ='/'.join(path_list)
    
    #checks to see if the given new_path exists
    if os.path.exists(new_path):
        #formats the new file_path with the variables created above
        final_path = '{}/{}.{}'.format(new_path,new_file_name,split_file_name[-1])
        #save the workbook
        new_workbook.save(final_path)
        # import pdb; pdb.set_trace()
        data = 'Saving {}.{}\n'.format(new_file_name,split_file_name[-1])
        if workbook_path.split('/')[-2] == 'target':
            store_data_to_txt_file(file_name='sorted_target_sheet', data= data)

        elif workbook_path.split('/')[-2] == 'acquirer':
            store_data_to_txt_file(file_name='sorted_acquirer_sheet', data= data)

    else:
        #if the path doesn't exist, create it
        os.makedirs(new_path, exist_ok=False)
        print('Generating file path: {}'.format(new_path))
        final_path = '{}/{}.{}'.format(new_path,new_file_name,split_file_name[-1])
        #save the workbook
        new_workbook.save(final_path)
        data ='Saving {}.{}\n'.format(new_file_name,split_file_name[-1])
        if workbook_path.split('/')[-2] == 'target':
            store_data_to_txt_file(file_name='sorted_target_sheet', data= data)

        elif workbook_path.split('/')[-2] == 'acquirer':
            store_data_to_txt_file(file_name='sorted_acquirer_sheet', data= data)


def find_end_index_row(reference_sheet, index_column):
	'''
	Given a reference sheet and an index_column, the last row in that column is returned.
	'''
	#Gets the last row of the reference sheet
	sheet_max_row = reference_sheet.max_row
	#while the value in the reference column is None, subtract 1 from sheet_max_row until a value is found in the index_column
	while reference_sheet.cell(row=sheet_max_row, column=index_column).value == None:
		sheet_max_row -=1

	return sheet_max_row


def sort_expiration_dates(exp_date_list):
    '''
    Give a list of expiration date strings formated in m-d-y, they are returned in order from earliest to latest
    This method is based on an insertion sort method
    '''
    for i in range(1,len(exp_date_list)):
        current_string = exp_date_list[i]
        current_date = mdy_string_to_date(exp_date_list[i])
        position = i

        while (position> 0) and (mdy_string_to_date(exp_date_list[position-1]) > current_date):
            exp_date_list[position] = exp_date_list[position-1]
            position = position-1

        exp_date_list[position] = current_string

    return exp_date_list

def mdy_string_to_date(mdy_str):
    '''
    Given a date string in the form m-d-y, the corresponding datetime object is returned
    '''
    return dt.datetime.strptime(mdy_str,'%m-%d-%y' ).date()


def sort_strike_prices(strike_list):
    '''
    Given a list of string values in the form ['C#', 'C#', 'C#'...] or ['P#', 'P#', 'P#'...]
    a sorted list is returned
    '''
    for i in range(1,len(strike_list)):
        current_string = strike_list[i]
        current_strike = string_to_strike(strike_list[i])
        position = i

        while position>0 and string_to_strike(strike_list[position-1]) > current_strike:
            strike_list[position] = strike_list[position-1]
            position = position-1

        strike_list[position] = current_string
    
    return strike_list

def string_to_strike(strike_string):
    '''
    Given a strike in the form 'C#' or 'P#', the number is returned as an integer or a float
    '''
    try:
        strike = int(strike_string[1:])
    except Exception as e:
        strike = float(strike_string[1:])
    
    return strike










