import pandas as pd
import numpy as np
import openpyxl
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows


#####################################################################################################################
#Add the Requirements from here. Make sure that it is a .xlsx file and not a binary work book
input_file = 'PVH_Cisco_SDWAN_SASE_Rqmt_and_Pricing_Form_Ver19_ih2424_111723.xlsx'
############
#Add the Input Sheet here
output_file = 'ICB Cisco SDWAN Input Sheet and List Rates Oct 2023 - Core and Ext Countries v5.xlsx'
############
#This is the mapping file that maps the Licenses on the requirement form to the output file
mapping_file = 'License Mapping.xlsx'
#####################################################################################################################

#####################################################################################################################
#READ the files in to a data frame
input_df = pd.read_excel(input_file, sheet_name = 'Sites', skiprows=range(0, 3), nrows = 1000)
input_df = input_df[ ['qty', 'country', 'site_id', 'ha_site_id', 'license', 'license_secondary']]
mapping_df = pd.read_excel(mapping_file, sheet_name = 'Sheet1', usecols="A:B")



#Set up the lists where we will store all of the duo and umbrella licenses. 
duo_license = []
duo_license_qty = []
umbrella_license = []
umbrella_license_qty=[]

#Read in the ICB file
output_df = pd.read_excel(output_file, sheet_name = 'INPUT SHEET COMBINED', skiprows=range(0, 3), nrows = 1000, usecols="A:DC")
core = pd.read_excel(output_file, sheet_name = 'INPUT SHEET COMBINED', nrows = 1, usecols="A:DB")
######################################################################################################################

#==============FUNCTIONS=================#

#Function 1: 
    #This is where we look at each Site ID tab and return the devices & thier volumes that we should count
    #We determine the ones to keep if they have a list price of '$$
def handleLicenses(theList, currentParts, licDf):
    for x in range(0,len(theList)):
        license = theList.iloc[x][0]
        quantity = theList.iloc[x][1]
        country = theList.iloc[x][2]
    
        if license in currentParts:
            print(license)
            selected_row_index = licDf.loc[licDf['Unnamed: 2'] == license].index[0] # search for the part # in the output df
            licDf.loc[selected_row_index, country] = licDf.loc[selected_row_index, country] + quantity
            licDf.loc[selected_row_index, 'Total'] = licDf.loc[selected_row_index, 'Total'] + quantity

        else:
            currentParts.append(license)
            newlicenseDf = addRow(licenseDf.copy(), license , quantity, country)
            licDf = pd.concat([licDf, newlicenseDf], ignore_index = True)
    
    return licDf.copy()

#Function 2:
    # This is where we add the primary and secondary licenses to  a master list of licenses
def addLicense(lic1, lic2, country, qty):
    if(str(lic2) != 'nan' ): #Check to make sure it is not null
        data = { 'License': [lic1, lic2] , 'Quantity': [qty,qty], 'country': [country, country]}
    else:
        data = { 'License': lic1,  'Quantity': qty, 'country': country}
        data = [data]
    returndf = pd.DataFrame(data)
    return returndf.copy()

#Function 3
    #add a row to a data frame
def addRow(partsDf, value ,qty, country):
        specified_values = { 'Unnamed: 2' : value, 'Unnamed: 0' : 'SOC', 'Unnamed: 1' : None , 'Unnamed: 4' : None, 'Total': qty, country : qty }
        new_row = {col: specified_values.get(col, 0) for col in partsDf.columns}
        new_row1 = pd.DataFrame([new_row])
        return new_row1
    

################################################################################################################3
# MAIN CODE#:     

part_index = 0;
license_index = 0;
duo_index = 0;
umbrella_index = 0;
stop_index = 0;
second_mrc = False

#Loop through Column B on the ICB file and separate out what is a part,license etc...
for x in range(0, len(output_df['Unnamed: 1'])):
    #if(output_df['Un'])
    if(output_df['Unnamed: 1'][x] == 'SD-WAN Equipment (MRC)'):
        part_index = x
    elif(output_df['Unnamed: 1'][x] == 'SD-WAN Subscription (MRC)'): #This shows up twice, we want the second one??
        if second_mrc == False: 
            license_index = x
            second_mrc = True
        #else:
            #What do we do about the Logacy and Soc
    elif(output_df['Unnamed: 1'][x] == 'Secure Remote Access (MRC)'): #
        duo_index = x
    elif(output_df['Unnamed: 1'][x] == 'Secure Web Gateway (MRC)' ):
        umbrella_index = x
        stop_index = x

#These data frames hold the parts, licenses, duo licenses, umbrella licenses and what ever else is in the ICB file
partDf = output_df.loc[part_index:license_index-1].copy()
licenseDf = output_df.loc[license_index:duo_index-1].copy()
duoDf = output_df.loc[duo_index:umbrella_index-1].copy()
umbrellaDf = output_df.loc[umbrella_index:stop_index-1].copy()
restDF = output_df[stop_index:].copy()

#add
x=3
str_new_df = input_df['country'].astype(str) 
country = str_new_df[x]
siteList = set()
while (country != 'nan' ): #Iterate over all of the countries until you hit a blank cell
    if(len(str(input_df['site_id'][x])) >3):
        siteList.add(input_df['site_id'][x])
    if(len(str(input_df['ha_site_id'][x]))> 3):
        siteList.add(input_df['ha_site_id'][x])
    x = x+1
    country = str_new_df[x]

#Iterate over all of the SITE_ID tabs, gather the parts for each site ID, and map the site_id to its parts list in a dictionary
    # We are storing the parts associated with each Site ID so we can access them
    #SitesDict is a dictionary with:
        # Key : "Site ID"
        # Payload : a data frame that holds the Parts for the site Id

sitesDict = {}
workbook1 = openpyxl.load_workbook(input_file)
for sheet in workbook1:
    if sheet.title in siteList:
        min_row = 8
        max_row = 50
        min_col = 1
        max_col = 11
    
        selected_data = [[cell.value for cell in sheet[row][min_col - 1:max_col]]for row in range(min_row - 1, max_row)]
        df1 = pd.DataFrame(selected_data)
        new_header = df1.iloc[0] #grab the first row for the header
        df1 = df1[1:] #take the data less the header row
        df1.columns = new_header
        
        df1 = df1[ [ 'Part Number', 'Equipment Order List', 'Quantity', 'ATT List Price / Monthly']]
        new_df1 = df1[df1['ATT List Price / Monthly'] == '$$']
        sitesDict[sheet.title] = new_df1
        #print(sheet)
    # Do any processing you need with the sheet here


#We want to make a list for all of the parts, licenses, duo licenses and umbrella licenses
masterList = pd.DataFrame(columns = ['Part Number', 'Equipment Order List', 'country'])
licenseList = pd.DataFrame(columns = ['License', 'Quantity', 'country'])
duoList = pd.DataFrame(columns = ['License', 'Quantity', 'country'])
umbrellaList = pd.DataFrame(columns = ['License', 'Quantity', 'country'])



#In the code below, we will be looping through the country column of the SDS form again. 
#We are trying to gather a maser list of all the parts (masertList), all of the licenses (licenseList), duo licenses (duoList)...
#We want to store the Part Number, description, quanity and country. We can store a part multiple times as a part can be used in multiple sites in different countries
    #note a part can appear twice here under the same country. In the next step we will get a tally of each part for each country
    
x = 3 
str_new_df = input_df['country'].astype(str) 
country = str_new_df[x]
while (country != 'nan' ): 
    qty = input_df['qty'][x]
    
    ########################################
    #Update the countries here that do not align perfectly with each other
        #The if statement contains the country on the SDS form
        #The line below it should be the country in the ICB sheet
    if(country =='UK (United Kingdom)'):
        country = 'UK'
    if(country == 'Korea (South)'):
        country = 'Korea'
    #########################################
    
    
    if( qty > 0 and country != 'China'): #we only want to count the sites where qty >0
        site_num = input_df['site_id'][x] 
        site_num2 = input_df['ha_site_id'][x]
        
        if(len(str(site_num)) > 3  ):
            site1  = sitesDict[site_num].copy()
            site1['Quantity'] = site1['Quantity']*qty
            site1['country'] = country
        
            if(len(str(site_num2)) > 3  ):
                site2  = sitesDict[site_num2].copy()
                site2['Quantity'] = site2['Quantity']*qty
                site2['country'] = country
                masterList = pd.concat([site1, site2, masterList], ignore_index = True)
            else:
                masterList = pd.concat([site1, masterList], ignore_index = True)
            #=========================================================
            lic1 = input_df['license'][x]
            lic2 = input_df['license_secondary'][x]

            result1 = addLicense(lic1, lic2, country, qty)
            licenseList = pd.concat([licenseList,result1.copy()], ignore_index = True)

            
            #Add this for the ones that have duo licenses?
            
            if 'duo_license' in input_df.columns.values:
                duo = input_df['duo_license'][x]
                duo_qty = input_df['duo_license_qty'][x]

                if(str(duo) != 'nan'):
                    result2 = addLicense(duo, 'nan', country, duo_qty)
                    duoList = pd.concat([duoList,result2.copy()], ignore_index = True)
            
            if 'umbrella_license' in input_df.columns.values:
                umbrella = input_df['umbrella_license'][x]
                umbrella_qty = input_df['umbrella _license_qty'][x]
                if(str(umbrella) != 'nan'):
                    result3 = addLicense(umbrella, 'nan', country, umbrella_qty)
                    umbrellaList = pd.concat([umbrellaList,result3.copy()], ignore_index = True)
    x = x+1
    country = str_new_df[x]

masterList = masterList.dropna(how='any',axis=0)

#Now that we have all of the parts and their quantity and the country they are being added to we now need to get a total of 
#each part for each country. For all of the countries supported in the output file we need to make sure that there is an
# associated sum for EACH part (i.e 0, 1, ... 100)

currentCountries = list(output_df.columns.values) #Load all the countries
currentParts = list(output_df['Unnamed: 2'].dropna())
#currentCountries = ['UK' if value == 'United Kingdom' else 'Korea' if value == 'South Korea' 
 #                   else value for value in currentCountries]


#Master list holds all of the parts and their quantity and the country it is being used in, we will loop through this and get a sum for each country
#partDF is where we are storing the tallied information of each part by country
for x in range(0,len(masterList)):
        
    ##This is where we need to update the quantity##
    part = masterList.iloc[x][0]
    
    if(masterList.iloc[x][4] == 'UK'):
        country = 'UK'
    else:
        country = masterList.iloc[x][4].title()

    quantity = masterList.iloc[x][2]
    #print(x)
    #First, check to see if the country is a column
    if country not in currentCountries:
        #We need to add the country to each of the data frames and set the values to 0
        partDf[country] = 0
        licenseDf[country] = 0
        duoDf[country] = 0 
        umbrellaDf[country] = 0 
        restDF[country] = 0   
        currentCountries.append(country)
        print(country)

     
    if part in currentParts:
        #print(part)
        selected_row_index = partDf[partDf['Unnamed: 2'] == part].index[0] # search for the part # in the output df

        partDf.loc[selected_row_index, country] = partDf.loc[selected_row_index, country] + quantity
        partDf.loc[selected_row_index, 'Total'] = partDf.loc[selected_row_index, 'Total'] + quantity
    
    else:#We need to add the new license???
        currentParts.append(part)
        partDftemp = addRow(partDf.copy(), part , quantity, country)
        partDf= pd.concat([partDf, partDftemp], ignore_index = True)
        partDf.loc[len(partDf)-1, 'Unnamed: 3' ] = masterList.iloc[x][1] #add the description (only for parts)

#Get a total of each license for each country

#Master list holds all of the licenses and their quantity and the country it is being used in, we will loop through this and get a sum for each country
#Lincesne is where we are storing the tallied information of each license by country
for x in range(0,len(licenseList)):
        
    ##This is where we need to update the quantity##
    part = licenseList.iloc[x][0]
    if(licenseList.iloc[x][2] == 'UK'):
        country = 'UK'
    else:
        country = licenseList.iloc[x][2].title()

    quantity = licenseList.iloc[x][1]
    #print(x)
    #First, check to see if the country is a column
    substr = ' - upto'
    if substr in part:
        
        #We now neet to split at the 4th'-''
        #part = part.split(substr)[0]
        part = part.split('-')
        part = (part[0] + '-' + part[1] + '-' + part[2] + '-' + part[3])
        
        #We need to check the mapping dataframe (where all the license mapping info is stored) so we can get the right license name for the icb file
        mappedPart = mapping_df[ mapping_df['RequirementForm '] == part]
        part = mappedPart['InputSheet'].iloc[0]
        
        #Look this part up on the mapping file
        #replace with the other license
        
    #This is where we look for the license on the output file
    if part in currentParts:
        #print(part)
        selected_row_index = licenseDf[licenseDf['Unnamed: 2'] == part].index[0] # search for the part # in the output df
        licenseDf.loc[selected_row_index, country] = licenseDf.loc[selected_row_index, country] + quantity
        licenseDf.loc[selected_row_index, 'Total'] = licenseDf.loc[selected_row_index, 'Total'] + quantity
    
    #Add the license to current parts
    else:
        #If we add a new license it is not going to have a description
        currentParts.append(part)
        partDftemp = addRow(licenseDf.copy(), part , quantity, country)
        licenseDf= pd.concat([licenseDf, partDftemp], ignore_index = True)
        licenseDf.loc[len(licenseDf)-1, 'Unnamed: 3' ] = None

#Get a total of each duo license for each country
for x in range(0,len(duoList)):
        
    ##This is where we need to update the quantity##
    part = duoList.iloc[x][0]
    if(licenseList.iloc[x][2] == 'UK'):
        country = 'UK'
    else:
        country = licenseList.iloc[x][2].title()
    quantity = duoList.iloc[x][1]
    #print(x)
    #First, check to see if the country is a column

    if part in currentParts:
        #print(part)
        selected_row_index = duoDf[duoDf['Unnamed: 2'] == part].index[0] # search for the part # in the output df
        duoDf.loc[selected_row_index, country] = duoDf.loc[selected_row_index, country] + quantity
        duoDf.loc[selected_row_index, 'Total'] = duoDf.loc[selected_row_index, 'Total'] + quantity
    
    else:
        #Duo licenses do not have a description
        currentParts.append(part)
        partDftemp = addRow(duoDf.copy(), part , qty, country)
        duoDf= pd.concat([duoDf, partDftemp], ignore_index = True)
        duoDf.loc[len(partDf)-1, 'Unnamed: 3' ] = None
#Get a total of each umbrella license for each country
for x in range(0,len(umbrellaList)):
        
    ##This is where we need to update the quantity##
    part = umbrellaList.iloc[x][0]
    if(licenseList.iloc[x][2] == 'UK'):
        country = 'UK'
    else:
        country = licenseList.iloc[x][2].title()

    quantity = umbrellaList.iloc[x][1]
    #print(x)
    #First, check to see if the country is a column
    if quantity > 0:
        if part in currentParts:

            selected_row_index = umbrellaDf[umbrellaDf['Unnamed: 2'] == part].index[0] # search for the part # in the output df
            umbrellaDf.loc[selected_row_index, country] = umbrellaDf.loc[selected_row_index, country] + quantity
            umbrellaDf.loc[selected_row_index, 'Total'] = umbrellaDf.loc[selected_row_index, 'Total'] + quantity

        else:
            #umbrella licenses do not have a description
            currentParts.append(part)
            partDftemp = addRow(umbrellaDf.copy(), part , quantity, country)
            umbrellaDf= pd.concat([umbrellaDf, partDftemp], ignore_index = True)
            umbrellaDf.loc[len(umbrellaDf)-1, 'Unnamed: 3' ] = None


#We put together the dataFrames: partDf, duoDf, umbrellaDf, restDf so we can have a single dataframe hold all of this info

#partDF will hold the sum for each part and each licnese (licnese, duo, umbrella) for each country
partDF = pd.concat([partDf,licenseDf, duoDf, umbrellaDf, restDF], ignore_index = True)
partDF.rename(columns = {'Unnamed: x': '', 'Unnamed: 0' : '', 'Unnamed: 1' : '',
                         'Unnamed: 2' : '','Unnamed: 3' : '', 'Unnamed: 4' : ''}, inplace = True)

#Make a excel work book and all of the rows from the data fram above, this is what our output will be
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(partDF, index=True, header=True):
    ws.append(r)


ws.delete_cols(1,1)
ws.insert_rows(idx = 0, amount =3)

#add the row that tells if the country is core or ext
x = 0
for cell in ws[2]:
    if( x < len(core.iloc[0])):
        cell.value = core.iloc[0][x]
    x = x+1

#Insert some blank rows for styling
ws.insert_rows(idx = 5, amount =1)

#The file will be saved with the same name as the input SDS form but with ICB infront
wb.save('ICB-' + input_file)
wb.close()