import pandas as pd
from datetime import datetime
from matplotlib.ticker import PercentFormatter
import matplotlib.pyplot as plt
import openpyxl

from openpyxl.styles.alignment import Alignment
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.utils.cell import coordinate_from_string, column_index_from_string
from openpyxl.styles import Border, Side
from openpyxl.drawing.image import Image
from typing import List, Tuple
from pandas import DataFrame 
from openpyxl.worksheet.worksheet import Worksheet
from copy import copy

# Redefine all functions

def create_yield_table(data: DataFrame, date: datetime) -> DataFrame:
    """
    Function to build a yield table object

    Parameters
    ----------
    data : DataFrame
        Yield curve data to be used
    date : datetime
        The date to use for the current date

    Yields
    ------
    DataFrame
        A yield table

    """
    # Find the other two dates
    date2 = date.replace(year=date.year-1)
    date3 = date.replace(year=date.year-5)

    # Index into the three dates and copy
    table = yield_curve.loc[[date, date2, date3]].copy()

    # Transpose
    table = table.T

    # Rename the columns
    table.columns = ["Current", "1 Year Ago Curve", "5 Years Ago Curve"]

    # Get the changes in the yield curve
    table["1 Year Change"] = table["Current"] - table["1 Year Ago Curve"]
    table["5 Year Change"] = table["Current"] - table["5 Years Ago Curve"]

    return table

def create_title(ws: Worksheet, area: str, text: str) -> None:
    """
    Writes a title on to the passed worksheet

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    area : str
        The area to put the title in (of the form A1:A5 or similar)
    text : str
        The text for the title

    """
    # Merge the cells in the area
    ws.merge_cells(area)
    
    # Given an area such as A1:A5, we want to first isolate the first cell
    # Split will break the area into the two components, we take the first one
    first_cell = area.split(":")[0]
    
    # Find the coordinates
    first_cell = coordinate_from_string(first_cell)
    
    # Convert the components to the numerical values for column and row
    col = column_index_from_string(first_cell[0])
    row = first_cell[1]
    
    # Change the text of the merged cell to whatever was passed as input
    ws.cell(row=row, column=col).value = text
    
    # Change the font of the title
    font = font = Font(size=16,
                       bold=True,
                      color="283747")
    ws.cell(row=row, column=col).font = font
    
    # Change alignment to be centered
    ws.cell(row=row, column=col).alignment = Alignment(horizontal="center")
    
def convert_to_year_frac(label: str) -> float:
    """
    Function to convert treasury curve indices to floating
    point numbers

    Parameters
    ----------
    label : str
        The string index label.

    Returns
    -------
    float
        A floating point number representing a fraction of a year.

    """
    
    # Handle months
    if "Mo" in label:
        # Get rid of string part
        frac = label.replace(' Mo','')
        
        # Convert to a fraction over 12
        frac = float(frac)
        frac = frac/12
    # Handle years
    else:
        # Get rid of string part
        frac = label.replace(' Yr','')
        # Convert to floating point number
        frac = float(frac)
    
    return frac

def write_to_excel(ws: Worksheet, start_row: int,
                   start_column: int, df: DataFrame,
                   index: bool = True, columns: bool = True) -> None:
    """
    Function which takes a worksheet and writes a table of data to it.

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start_row : int
        The row to begin on
    start_column : int
        The column to start on
    df : DataFrame
        The data to write
    index : bool, optional
        Whether to write the index labels. The default is True.
    columns : bool, optional
        Whether to write the column labels. The default is True.

    """
    
    # If we want to include the index, reset the index on the dataframe first
    if index:
        write_data = df.reset_index().values.tolist()
    else:
        write_data = df.values.tolist()
    
    # If we want to include columns we need to add the columns as the first in the nested lists
    if columns:
        # If we already added an index, then we are going to want also include a blank space since
        # we don't want a label above the index
        if index:
            write_data = [[""]+list(df.columns)]+write_data
        else:
            write_data = [list(df.columns)]+write_data
    
    # For every value iterate through finding the location to place it in
    for row in range(len(write_data)):
        for column in range(len(write_data[0])):
            ws.cell(row=start_row+row,column=start_column+column).value = write_data[row][column]
            
def rgb_to_hex(rgb: tuple) -> str:
    """
    This function takes a RGB color tuple and returns back
    a hexadecimal version of it.

    Parameters
    ----------
    rgb : tuple
        A tuple of length three with values between 0-255 for each. It
        represents red, green and blue values.

    Returns
    -------
    str
        The hexadecimal representation

    """
    return '%02x%02x%02x' % rgb

def adjust_column_widths(ws: Worksheet, start: int, values: List[int]):
    """
    Function to adjust the width of the columns starting at the start value
    to the values listed in values

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start : int
        The column to begin adjustments at
    values : List[int]
        The widths to set each column to in order


    """
    # Iterate through the values
    for i, width in enumerate(values):
        # Set the width of the column
        ws.column_dimensions[get_column_letter(start+i)].width = width
        
def value_to_red_green(value: float) -> tuple:
    """
    Convert a value ranging from [-1, 1] to red or green with
    intensity based on the value

    Parameters
    ----------
    value : float
        The value ranging from -1 to 1

    Returns
    -------
    tuple
        The RGB tuple

    """
    # Positive values are green
    if value > 0:
        return (int(255-value*255), 255, int(255-value*255))
    # Negative values are red
    else:
        value = abs(value)
        return (255, int(255-value*255), int(255-value*255))

def create_color_scale(returns: DataFrame) -> DataFrame:
    """
    Function to build a color scale where each value is transformed
    into hexadecimal colors based on a scaling where more red is more
    negative and more green is more positive.

    Parameters
    ----------
    returns : DataFrame
        Returns data to start with

    Returns
    -------
    DataFrame
        The color data
    """
    # Find the maximum returns
    max_abs_return = abs(returns).max().max()
    
    # Divide each number by this
    scale = returns / max_abs_return
    
    # Map the conversion to rgb
    scale = scale.applymap(value_to_red_green)
    
    # Map rgb to hex
    scale = scale.applymap(rgb_to_hex)
    
    return scale

def red_green_map(ws: Worksheet, returns: DataFrame, start_row: int, start_column: int) -> None:
    """
    Function which, given a DataFrame, maps the colors to red and
    green based on how positive/negative values are.

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    returns : DataFrame
        The returns data
    start_row : int
        The starting row
    start_column : int
        The starting column

    """
    # Create the color scale
    color_scale = create_color_scale(returns.fillna(0))
    
    # Iterate through rows and columns adding the colors
    for row in range(returns.shape[0]):
        for column in range(returns.shape[1]):
            # Define the color
            color = PatternFill(start_color=color_scale.iloc[row, column],
                   end_color=color_scale.iloc[row, column],
                   fill_type='solid')
            
            # Set the colors
            ws.cell(row=start_row+row,column=start_column+column).fill = color
            
def bold_cells_line(ws: Worksheet, start_row: int, start_column: int,
                    length: int, vertical: bool = True) -> None:
    """
    Function which bolds a column or row of a worksheet

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start_row : int
        The row to start at for bolding
    start_column : int
        The column to start at for bolding
    length : int
        The length of the column or row to bold through
    vertical : bool, optional
        Decides if it is a row or column of the parameter length
        that is bolded. The default is True.

    """
    
    # Iterate through the length of the row/column
    for i in range(length):
        # Increment the row or column depending on if vertical is true
        if vertical:
            cell = ws.cell(row=start_row+i,column=start_column)
        else:
            cell = ws.cell(row=start_row,column=start_column+i)
            
        # Copy the font and set the bold property to true
        font = copy(cell.font)
        font.bold = True
        cell.font = font
        
def create_axis_labels(ws: Worksheet, returns: DataFrame):
    """
    Function which adds axis labels to the worksheet

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    returns: DataFrame
        The returns data

    """
    # Merge the cells and label it year
    ws.merge_cells(start_row=4, start_column=1, end_row=4+returns.shape[0], end_column=1)
    ws.cell(row=4,column=1).value = "Year"
    
    # Add alignment and font
    ws.cell(row=4, column=1).alignment = Alignment(horizontal="center", vertical="center", textRotation=90)
    ws.cell(row=4, column=1).font = Font(size=16, bold=True, color="283747")
    
    # Merge the cells and label it quarter
    ws.merge_cells("B3:F3")
    ws.cell(row=3,column=2).value = "Quarter"
    
    # Add alignment and font
    ws.cell(row=3, column=2).font = Font(size=16, bold=True, color="283747")
    ws.cell(row=3, column=2).alignment = Alignment(horizontal="center", vertical="center")

def create_number_formatting(ws: Worksheet, start_row: int,
                             start_column: int, vertical_length: int,
                             horizontal_length: int, num_format: str) -> None:
    """
    Function to change the number formatting of a certain area of an excel sheet.

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start_row : int
        The row to start changing the number formatting at
    start_column : int
        The column to start changing the number formatting at
    vertical_length : int
        How large the area is vertically
    horizontal_length : int
        How large the area is horizontally
    num_format : str
        The number format to apply

    """
    
    # Iterate through rows and columns and set the number format
    for row in range(vertical_length):
        for column in range(horizontal_length):
            ws.cell(row=start_row+row,column=start_column+column).number_format = num_format
            
def create_border(ws: Worksheet, start_row: int, start_column: int,
                  vertical_length: int, horizontal_length: int) -> None:
    """
    Function which takes a starting position and the length vertically
    and horizontally, then creates thin borders around all of them

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start_row : int
        The row to begin at
    start_column : int
        The column to begin at
    vertical_length : int
        How far down borders should be applied
    horizontal_length : int
        How far right borders should be applied
    """
    # Create the border
    border = Border(left=Side(border_style='thin'),
         right=Side(border_style='thin'),
         top=Side(border_style='thin'),
         bottom=Side(border_style='thin'))
    
    # Iterate and set the border
    for row in range(vertical_length):
        for column in range(horizontal_length):
            ws.cell(row=start_row+row,column=start_column+column).border = border
            
def write_treasury_sheet(ws: Worksheet, yield_curve_table: DataFrame) -> None:
    """
    Function to fill out the treasury curve data

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    yield_curve_table : DataFrame
        The yield curve data

    """
    # Write the yield curve table
    write_to_excel(ws, 3, 1, yield_curve_table)
    
    # Color the yield curve changes red/green
    red_green_map(ws, yield_curve_table[["1 Year Change"]], 4, 4)
    red_green_map(ws, yield_curve_table[["5 Year Change"]], 4, 6)
    
    # Adjust the column widths for the table
    adjust_column_widths(ws,2,[15]*len(yield_curve_table.columns))
    
    # Bold the index and columns
    bold_cells_line(ws, 4, 1, len(yield_curve_table), vertical=True)
    bold_cells_line(ws, 3, 2, len(yield_curve_table.columns), vertical=False)
    
    # Create title
    create_title(ws, 'A1:F1', "Treasury Yield Curve")
    
    # Add borders
    create_border(ws, 4, 2, yield_curve_table.shape[0], yield_curve_table.shape[1])
    
    # Get rid of gridlines
    ws.sheet_view.showGridLines = False
    
    # Add yield curve graphs
    img = Image('Images/Treasury Yield Curves.png')
    ws.add_image(img, 'H1')
    
def create_real_estate_report(ws: Worksheet, real_estate_returns: DataFrame, image_name: str):
    """
    Function to write the real estate report
    
    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    real_estate_returns : DataFrame
        The returns data for real estate
    image_name : str
        The name of the image to import in and place
        in the report

    """
    
    # Write the table
    write_to_excel(ws, 4, 2, real_estate_returns)
    
    # Create the axis labels
    create_axis_labels(ws, real_estate_returns)
    
    # Create percentage number formatting
    create_number_formatting(ws, 5, 3, real_estate_returns.shape[0], real_estate_returns.shape[1], "0.0%")
    
    # Map the red-green color map
    red_green_map(ws, real_estate_returns, 5, 3)
    
    # Add image
    img = Image(image_name)
    ws.add_image(img, 'H5')