import numpy as np
import pandas as pd
import re

from pathlib import Path


def read_and_preprocess_spreadsheet(file_name):
    """ Creates a pandas dataframe from the curriculum overview spreadsheet """
    DATA_DIR = Path(__file__).parent.parent / "mathtext_fastapi" / "data" / file_name
    script_df = pd.read_excel(DATA_DIR, engine='openpyxl')
    # Ensures the grade level columns are integers instead of floats
    script_df.columns = script_df.columns[:2].tolist() + script_df.columns[2:11].astype(int).astype(str).tolist() + script_df.columns[11:].tolist()
    script_df.fillna('', inplace=True)
    return script_df


def extract_skill_code(skill):
    """ Looks within a curricular skill description for its descriptive code

    Input
    - skill: str - a brief description of a curricular skill

    >>> extract_skill_code('A3.3.4 - Solve inequalities')
    'A3.3.4'
    >>> extract_skill_code('A3.3.2 - Graph linear equations, and identify the x- and y-intercepts or the slope of a line')
    'A3.3.2'
    """
    pattern = r'[A-Z][0-9]\.\d+\.\d+'
    result = re.search(pattern, skill)
    return result.group()


def build_horizontal_transitions(script_df):
    """ Build a list of transitional relationships within a curricular skill

    Inputs
    - script_df: pandas dataframe - an overview of the curriculum skills by grade level

    Output
    - horizontal_transitions: array of arrays - transition data with label, from state, and to state

    >>> script_df = read_and_preprocess_spreadsheet('curriculum_framework_for_tests.xlsx')
    >>> build_horizontal_transitions(script_df)
    [['right', 'N1.1.1_G1', 'N1.1.1_G2'], ['right', 'N1.1.1_G2', 'N1.1.1_G3'], ['right', 'N1.1.1_G3', 'N1.1.1_G4'], ['right', 'N1.1.1_G4', 'N1.1.1_G5'], ['right', 'N1.1.1_G5', 'N1.1.1_G6'], ['left', 'N1.1.1_G6', 'N1.1.1_G5'], ['left', 'N1.1.1_G5', 'N1.1.1_G4'], ['left', 'N1.1.1_G4', 'N1.1.1_G3'], ['left', 'N1.1.1_G3', 'N1.1.1_G2'], ['left', 'N1.1.1_G2', 'N1.1.1_G1'], ['right', 'N1.1.2_G1', 'N1.1.2_G2'], ['right', 'N1.1.2_G2', 'N1.1.2_G3'], ['right', 'N1.1.2_G3', 'N1.1.2_G4'], ['right', 'N1.1.2_G4', 'N1.1.2_G5'], ['right', 'N1.1.2_G5', 'N1.1.2_G6'], ['left', 'N1.1.2_G6', 'N1.1.2_G5'], ['left', 'N1.1.2_G5', 'N1.1.2_G4'], ['left', 'N1.1.2_G4', 'N1.1.2_G3'], ['left', 'N1.1.2_G3', 'N1.1.2_G2'], ['left', 'N1.1.2_G2', 'N1.1.2_G1']]
    """
    horizontal_transitions = []
    for index, row in script_df.iterrows():     
        skill_code = extract_skill_code(row['Knowledge or Skill'])

        rightward_matches = []
        for i in range(9):
            # Grade column
            current_grade = i+1
            if row[current_grade].lower().strip() == 'x':
                rightward_matches.append(i)
            
        for match in rightward_matches:
            if rightward_matches[-1] != match:
                horizontal_transitions.append([
                    "right",
                    f"{skill_code}_G{match}",
                    f"{skill_code}_G{match+1}"
                ])

        leftward_matches = []
        for i in reversed(range(9)):
            current_grade = i
            if row[current_grade].lower().strip() == 'x':
                leftward_matches.append(i)

        for match in leftward_matches:
            if leftward_matches[0] != match:
                horizontal_transitions.append([
                    "left",
                    f"{skill_code}_G{match}",
                    f"{skill_code}_G{match-1}"
                ])

    return horizontal_transitions


def gather_all_vertical_matches(script_df):
    """ Build a list of transitional relationships within a grade level across skills

    Inputs
    - script_df: pandas dataframe - an overview of the curriculum skills by grade level

    Output
    - all_matches: array of arrays - represents skills at each grade level

    >>> script_df = read_and_preprocess_spreadsheet('curriculum_framework_for_tests.xlsx')
    >>> gather_all_vertical_matches(script_df)
    [['N1.1.1', '1'], ['N1.1.2', '1'], ['N1.1.1', '2'], ['N1.1.2', '2'], ['N1.1.1', '3'], ['N1.1.2', '3'], ['N1.1.1', '4'], ['N1.1.2', '4'], ['N1.1.1', '5'], ['N1.1.2', '5'], ['N1.1.1', '6'], ['N1.1.2', '6']]
    """
    all_matches = []
    columns = ['1', '2', '3', '4', '5', '6', '7', '8', '9']

    for column in columns:
        for index, value in script_df[column].iteritems():
            row_num = index + 1
            if value == 'x':
                # Extract skill code
                skill_code = extract_skill_code(
                    script_df['Knowledge or Skill'][row_num-1]
                )

                all_matches.append([skill_code, column])
    return all_matches


def build_vertical_transitions(script_df):
    """ Build a list of transitional relationships within a grade level across skills

    Inputs
    - script_df: pandas dataframe - an overview of the curriculum skills by grade level

    Output
    - vertical_transitions: array of arrays - transition data with label, from state, and to state

    >>> script_df = read_and_preprocess_spreadsheet('curriculum_framework_for_tests.xlsx')
    >>> build_vertical_transitions(script_df)
    [['down', 'N1.1.1_G1', 'N1.1.2_G1'], ['down', 'N1.1.2_G1', 'N1.1.1_G1'], ['down', 'N1.1.1_G2', 'N1.1.2_G2'], ['down', 'N1.1.2_G2', 'N1.1.1_G2'], ['down', 'N1.1.1_G3', 'N1.1.2_G3'], ['down', 'N1.1.2_G3', 'N1.1.1_G3'], ['down', 'N1.1.1_G4', 'N1.1.2_G4'], ['down', 'N1.1.2_G4', 'N1.1.1_G4'], ['down', 'N1.1.1_G5', 'N1.1.2_G5'], ['down', 'N1.1.2_G5', 'N1.1.1_G5'], ['down', 'N1.1.1_G6', 'N1.1.2_G6'], ['up', 'N1.1.2_G6', 'N1.1.1_G6'], ['up', 'N1.1.1_G6', 'N1.1.2_G6'], ['up', 'N1.1.2_G5', 'N1.1.1_G5'], ['up', 'N1.1.1_G5', 'N1.1.2_G5'], ['up', 'N1.1.2_G4', 'N1.1.1_G4'], ['up', 'N1.1.1_G4', 'N1.1.2_G4'], ['up', 'N1.1.2_G3', 'N1.1.1_G3'], ['up', 'N1.1.1_G3', 'N1.1.2_G3'], ['up', 'N1.1.2_G2', 'N1.1.1_G2'], ['up', 'N1.1.1_G2', 'N1.1.2_G2'], ['up', 'N1.1.2_G1', 'N1.1.1_G1']]
    """
    vertical_transitions = []

    all_matches = gather_all_vertical_matches(script_df)

    # Downward
    for index, match in enumerate(all_matches):
        skill = match[0]
        row_num = match[1]
        if all_matches[-1] != match:
            vertical_transitions.append([
                "down",
                f"{skill}_G{row_num}",
                f"{all_matches[index+1][0]}_G{row_num}"
            ])

    # Upward
    for index, match in reversed(list(enumerate(all_matches))):
        skill = match[0]
        row_num = match[1]
        if all_matches[0] != match:
            vertical_transitions.append([
                "up",
                f"{skill}_G{row_num}",
                f"{all_matches[index-1][0]}_G{row_num}"
            ])
    
    return vertical_transitions


def build_all_states(all_transitions):
    """ Creates an array with all state labels for the curriculum

    Input
    - all_transitions: list of lists - all possible up, down, left, or right transitions in curriculum

    Output
    - all_states: list - a collection of state labels (skill code and grade number)
    
    >>> all_transitions = [['right', 'N1.1.1_G1', 'N1.1.1_G2'], ['right', 'N1.1.1_G2', 'N1.1.1_G3'], ['right', 'N1.1.1_G3', 'N1.1.1_G4'], ['right', 'N1.1.1_G4', 'N1.1.1_G5'], ['right', 'N1.1.1_G5', 'N1.1.1_G6'], ['left', 'N1.1.1_G6', 'N1.1.1_G5'], ['left', 'N1.1.1_G5', 'N1.1.1_G4'], ['left', 'N1.1.1_G4', 'N1.1.1_G3'], ['left', 'N1.1.1_G3', 'N1.1.1_G2'], ['left', 'N1.1.1_G2', 'N1.1.1_G1'], ['right', 'N1.1.2_G1', 'N1.1.2_G2'], ['right', 'N1.1.2_G2', 'N1.1.2_G3'], ['right', 'N1.1.2_G3', 'N1.1.2_G4'], ['right', 'N1.1.2_G4', 'N1.1.2_G5'], ['right', 'N1.1.2_G5', 'N1.1.2_G6'], ['left', 'N1.1.2_G6', 'N1.1.2_G5'], ['left', 'N1.1.2_G5', 'N1.1.2_G4'], ['left', 'N1.1.2_G4', 'N1.1.2_G3'], ['left', 'N1.1.2_G3', 'N1.1.2_G2'], ['left', 'N1.1.2_G2', 'N1.1.2_G1'], ['down', 'N1.1.1_G1', 'N1.1.2_G1'], ['down', 'N1.1.2_G1', 'N1.1.1_G1'], ['down', 'N1.1.1_G2', 'N1.1.2_G2'], ['down', 'N1.1.2_G2', 'N1.1.1_G2'], ['down', 'N1.1.1_G3', 'N1.1.2_G3'], ['down', 'N1.1.2_G3', 'N1.1.1_G3'], ['down', 'N1.1.1_G4', 'N1.1.2_G4'], ['down', 'N1.1.2_G4', 'N1.1.1_G4'], ['down', 'N1.1.1_G5', 'N1.1.2_G5'], ['down', 'N1.1.2_G5', 'N1.1.1_G5'], ['down', 'N1.1.1_G6', 'N1.1.2_G6'], ['up', 'N1.1.2_G6', 'N1.1.1_G6'], ['up', 'N1.1.1_G6', 'N1.1.2_G6'], ['up', 'N1.1.2_G5', 'N1.1.1_G5'], ['up', 'N1.1.1_G5', 'N1.1.2_G5'], ['up', 'N1.1.2_G4', 'N1.1.1_G4'], ['up', 'N1.1.1_G4', 'N1.1.2_G4'], ['up', 'N1.1.2_G3', 'N1.1.1_G3'], ['up', 'N1.1.1_G3', 'N1.1.2_G3'], ['up', 'N1.1.2_G2', 'N1.1.1_G2'], ['up', 'N1.1.1_G2', 'N1.1.2_G2'], ['up', 'N1.1.2_G1', 'N1.1.1_G1']]
    >>> build_all_states(all_transitions)
    ['N1.1.1_G1', 'N1.1.1_G2', 'N1.1.1_G3', 'N1.1.1_G4', 'N1.1.1_G5', 'N1.1.1_G6', 'N1.1.2_G1', 'N1.1.2_G2', 'N1.1.2_G3', 'N1.1.2_G4', 'N1.1.2_G5', 'N1.1.2_G6']
    """
    all_states = []
    for transition in all_transitions:
        for index, state in enumerate(transition):
            if index == 0:
                continue   
            if state not in all_states:
                all_states.append(state)
    return all_states


def build_curriculum_logic():
    script_df = read_and_preprocess_spreadsheet('Rori_Framework_v1.xlsx')
    horizontal_transitions = build_horizontal_transitions(script_df)
    vertical_transitions = build_vertical_transitions(script_df)
    all_transitions = horizontal_transitions + vertical_transitions
    all_states = build_all_states(all_transitions)
    return all_states, all_transitions