import csv

def copy_csv(original, destination, mode = "wb"):
    # read CSV into a dictionary
    with open(original) as f:
        csv_f = csv.reader(f)
        rows_to_write = []
        for row in csv_f:
            rowCopy = list(row)
            rows_to_write.append(rowCopy)
    f.close()

    write_csv(destination, rows_to_write, mode)

def add_column(csv_file, indexToAdd, columnToAdd, contentsToAdd):
    # read CSV into a dictionary
    if(isinstance(contentsToAdd, int)):
        counter = contentsToAdd
    elif(isinstance(contentsToAdd, list)):
        counter = 0
    with open(csv_file) as f:
        csv_f = csv.reader(f)
        first = True
        rows_to_write = []
        for row in csv_f:
            rowCopy = list(row)
            if first:
                rowCopy.insert(indexToAdd, columnToAdd)
                first = False
            else:
                if(isinstance(contentsToAdd, int)):
                    rowCopy.insert(indexToAdd, counter)
                    counter = counter + 1
                elif(isinstance(contentsToAdd, list)):
                    rowCopy.insert(indexToAdd, contentsToAdd[counter])
                    counter = counter + 1
                else:
                    rowCopy.insert(indexToAdd, contentsToAdd)
            rows_to_write.append(rowCopy)
    f.close()

    write_csv(csv_file, rows_to_write)

def remove_column(csv_file, columnToRemove):
    print columnToRemove
    # read CSV into a dictionary
    with open(csv_file) as f:
        csv_f = csv.reader(f)
        first = True
        rows_to_write = []
        for row in csv_f:
            if first:
                for column in row:
                    if column == columnToRemove:
                        columnToRemoveIndex = row.index(column)
                first = False
                rowCopy = list(row)
                del rowCopy[columnToRemoveIndex]
                rows_to_write.append(rowCopy)
            else:
                rowCopy = list(row)
                del rowCopy[columnToRemoveIndex]
                rows_to_write.append(rowCopy)
    f.close()

    write_csv(csv_file, rows_to_write)

def move_column(csv_file, column_name, index_to_move_to):
    # Grab all the values
    with open(csv_file) as f:
        csv_f = csv.reader(f)
        first = True
        column_values = []
        rowIndex = 0
        for row in csv_f:
            if first:
                for column in row:
                    if column == column_name:
                        rowIndex = row.index(column)
                first = False
            else:
                column_values.append(row[rowIndex])
        
    # remove the column
    remove_column(csv_file, column_name)
    # insert the column
    add_column(csv_file, index_to_move_to, column_name, column_values)

# opens the CSV, looks up the value for "column_name" at row "index" and returns it.
def get_value_at_row(csv_file, index, column_name):
    with open(csv_file) as f:
        csv_f = csv.reader(f)
        first = True
        rowIndex = None
        for i, row in enumerate(csv_f):
            if first:
                for column in row:
                    if column == column_name:
                        rowIndex = row.index(column)
                if rowIndex == None:
                    return None
                first = False
            else:
                if i == index:
                    return row[rowIndex]
        return None # if we're here, something failed.

def get_value_or_index(csv_file, index, column_name):
    # check if our "Column name" exists. Return the value of our "index/column name"
    value = get_value_at_row(csv_file, index, column_name)
    if value:
        return value
    else:
        return index
    # if not, return index

def get_value(csv_file, index, column_name):
    # check if our "Column name" exists. Return the value of our "index/column name"
    # if not, return false
    value = get_value_at_row(csv_file, index, column_name)
    if value:
        return value
    else:
        return None


# looks through the csv_file, searching for "Value" in the "value_column_name". When it finds the first, returns the value for that row in the "return_column_name" column.
# this is NOT case sensitive. In other words SPANISH will match Spanish.
def search_csv_for_row(csv_file, value_column_name, value, return_column_name):
    with open(csv_file) as f:
        csv_f = csv.reader(f)
        first = True
        valueIndex = None
        returnIndex = None
        for i, row in enumerate(csv_f):
            if first:
                for column in row:
                    if column == value_column_name:
                        valueIndex = row.index(column)
                    elif column == return_column_name:
                        returnIndex = row.index(column)
                if valueIndex == None or returnIndex == None:
                    return None
                first = False
            else:
                if row[valueIndex].lower() == value.lower():
                    return row[returnIndex]
        return None # if we're here, it didn't show up in the entire csv.

# blindly adds a row to the end of a csv
def add_row_to_csv(csv_file, rowList):
    with open(csv_file) as f:
        csv_f = csv.reader(f)
        rows_to_write = []
        for row in csv_f:
            rowCopy = list(row)
            rows_to_write.append(rowCopy)
    f.close()
    rows_to_write.append(rowList)
    write_csv(csv_file, rows_to_write)

# Adds a row to the "csv_File", with the "value" in the "value_column_name" column and creating a smart index_column_name 
def add_row_to_csv_single_value(csv_file, value_column_name, value, index_column_name):
    # read CSV into a dictionary
    with open(csv_file) as f:
        csv_f = csv.reader(f)
        first = True
        rows_to_write = []
        indexColumnRowIndex = None
        valueColumnRowIndex = None
        for row in csv_f:
            if first:
                for column in row:
                    if column == index_column_name:
                        indexColumnRowIndex = row.index(column)
                    elif column == value_column_name:
                        valueColumnRowIndex = row.index(column)
                first = False
                rowCopy = list(row)
                rows_to_write.append(rowCopy)
            else:
                rowCopy = list(row)
                rows_to_write.append(rowCopy)
    f.close()

    # for the new row:
    rowCopy = list(rowCopy)
    try:
        newIndex = int(rowCopy[indexColumnRowIndex]) + 1
    except:
        newIndex = 1
    
    rowCopy[indexColumnRowIndex] = newIndex
    rowCopy[valueColumnRowIndex] = value
    rows_to_write.append(rowCopy)

    write_csv(csv_file, rows_to_write)
    return newIndex

def add_row_to_csv_multiple_values(csv_file, value_column_names, values):
    # read CSV into a dictionary
    with open(csv_file) as f:
        csv_f = csv.reader(f)
        first = True
        rows_to_write = []
        indexes = []
        for row in csv_f:
            if first:
                for value_column_name in value_column_names:
                    for column in row:
                        if column == value_column_name:
                            indexes.append(row.index(column))
                first = False
                rowCopy = list(row)
                rows_to_write.append(rowCopy)
            else:
                rowCopy = list(row)
                rows_to_write.append(rowCopy)
    f.close()

    # for the new row:
    rowCopy = list(rowCopy)
    for i, index in enumerate(indexes):
        rowCopy[index] = values[i]
    rows_to_write.append(rowCopy)

    write_csv(csv_file, rows_to_write)
    return

def write_csv(csv_file, rows_to_write, mode = "wb"):
    with open(csv_file, mode) as writecsvfile:
        writer = csv.writer(writecsvfile)
        writer.writerows(rows_to_write)
    writecsvfile.close()

def search_csv_for_row_and_add_if_missing(csv_file, value_column_name, value, return_column_name):
    value_to_return = search_csv_for_row(csv_file, value_column_name, value, return_column_name)
    if value_to_return == None:
        # just assuming return_column_name is the index for now.
        value_to_return = add_row_to_csv_single_value(csv_file, value_column_name, value, return_column_name)
    return value_to_return

def create_csv_file(csv_file, columns_for_first_row):
    write_csv(csv_file, columns_for_first_row)

def create_many_to_many_relationship(
    csv_file = None, 
    input_csv_file_id_column_name = None,
    input_csv_file_column_name = None,
    
    input_resource_table = None,
    input_resource_table_id_column_name = None, # id column. Usually "id"
    input_resource_table_column_name = None, # The row in the input_resource_table that we're looking up our values in. Usually "name"
    
    output_resource_table = None, # Place to save a copy of input_resource_table to (with additional rows if necessary)

    output_join_table = None, # Place to save the 2-column table that joins the two tables together.
    output_csv_file_column_name = None, # The name of the column that represents the IDs from the input_csv_file.
    output_resource_table_column_name = None, # Name of the column that should represent the ID from the 
    output_join_table_static_values = None # List of lists of values to add to the the output_join_table blindly
):

    # Read through each row in csv_file
    # 1. Get the ID of the row (if None, use counter)
    # 2. Get the value in our "input_csv_file_column_name"
    # 3. If "input_resource_table" exists, Look up if it exists
    # 3. If "input_resource_Table" does not exist, create it (single CSV with "id|name" columns)
    # 4. Write a value to "output_join_table" with ID from step 1 (output_csv_file_column_name column) and ID from step 3 (output_resource_table_column_name)
    with open(csv_file) as f:
        csv_f = csv.reader(f)

        # get columns to add to the output_join_table
        columns = [output_csv_file_column_name, output_resource_table_column_name]
        columnsToAdd = []
        valuesToAdd = []
        if output_join_table_static_values:
            for array in output_join_table_static_values:
                columnsToAdd.append(array[0])
                valuesToAdd.append(array[1])
            columns = columns + columnsToAdd
        # write the headers of output_join_table:
        create_csv_file(output_join_table, [columns])

        # copy input_resource_table to output_resource_table
        if input_resource_table == None:
            create_csv_file(output_resource_table, [[input_resource_table_id_column_name, input_resource_table_column_name]])
        else:
            copy_csv(input_resource_table, output_resource_table)

        output_join_table_rows = []
        output_join_table_rows.append([output_csv_file_column_name, output_resource_table_column_name])
        for index, row in enumerate(csv_f):
            if index >= 0:
                # get the IDs
                input_csv_file_row_id = get_value_or_index(csv_file, index+1, input_csv_file_id_column_name)
                input_csv_file_value = get_value(csv_file, index+1, input_csv_file_column_name)

                # do not go on if the value is empty:
                if input_csv_file_value != "" and input_csv_file_value != None:
                    for input_csv_file_v in [x.strip() for x in input_csv_file_value.split(',')]:
                        resource_table_id = search_csv_for_row_and_add_if_missing(output_resource_table, input_resource_table_column_name, input_csv_file_v, input_resource_table_id_column_name)
                        add_row_to_csv(output_join_table, [input_csv_file_row_id,resource_table_id] + valuesToAdd)

def belongs_to_rows(
    csv_file = None, 
    input_csv_file_id_column_name = None,
    input_csv_file_value_column_names = None, # List of columns that we should be filling into

    foreign_table_start_csv = None, # initial starting place of the foreign table
    foreign_table_key_column = None, # the column name where the IDs of our main table (csv_file) should go
    foreign_table_value_columns = None, # the list of columns in the foreign table we should be filling input_csv_file_value_column_names into.
    skip_copy = False, # if you run this more than once per input, you want to set this "False" the first time and true other.

    output_foreign_table = None
):
    with open(csv_file) as f:
        csv_f = csv.reader(f)

        # copy input_resource_table to foreign_table_start_csv
        if not skip_copy:
            if foreign_table_start_csv == None:
                create_csv_file(foreign_table_start_csv, [foreign_table_value_columns])
            else:
                copy_csv(foreign_table_start_csv, output_foreign_table)
        
        for index, row in enumerate(csv_f):
            if index > 0:
                # get the IDs
                input_csv_file_row_id = get_value_or_index(csv_file, index, input_csv_file_id_column_name)
                values = [get_value(csv_file, index, col) for col in input_csv_file_value_column_names]

                # check if at least one of the values is not empty
                if all(v is None for v in values) == False:
                    add_row_to_csv_multiple_values(
                        output_foreign_table, 
                        [foreign_table_key_column] + foreign_table_value_columns,
                        [input_csv_file_row_id]  + values
                    )