"""
Excel 2007 & JSON conversion library
==================
This library class provides functionality for converting Excel 2007 files
to JSON and vice versa in an efficient streaming fashion, using AWS S3 and
temporary local files to keep memory usage to a minimum.

"""
import json
from os import path, makedirs
from boto.s3.key import Key, boto
import openpyxl
from openpyxl.worksheet import ColumnDimension
from openpyxl.writer.dump_worksheet import WriteOnlyCell
import re


def make_dir(path, exists_ok):
    """
    See: os.makedirs
    """
    return makedirs(path, exists_ok=exists_ok)


def get_s3():
    """Simple wrapper to provide a convenient mocking target

    Args:


    Returns:
        S3Connection: A connection to Amazon's S3

    """
    return boto.connect_s3()


def open_fp(temp_dir, file_type, owner_type, owner_id, filename, mode):
    """Simple wrapper to provide a convenient mocking target

    Args:
        temp_dir (str): The directory on the local filesystem where the library
         can store intermediate files
        file_type (str): 'xlsx' or 'json'
        owner_type (str): 'vendor' or 'subaccount'
        owner_id (str): Concatenated with the owner_type to build a user-
         specific temporary directory
        filename (str): Filename to be opened
        mode (str): One of the modes that can be passed to open()

    Returns:
        An open file-like object

    """
    full_path = path.join(build_fs_path(temp_dir,
                                        file_type,
                                        owner_type,
                                        owner_id), filename)
    return open(full_path, mode)


def reopen_fp(temp_dir,
              file_type,
              owner_type,
              owner_id,
              filename,
              new_mode,
              original_fp):
    """Simple wrapper to provide a convenient mocking target

    Args:
        temp_dir (str): The directory on the local filesystem where the library
         can store intermediate files
        file_type (str): 'xlsx' or 'json'
        owner_type (str): 'vendor' or 'subaccount'
        owner_id (str): Concatenated with the owner_type to build a user-
         specific temporary directory
        filename (str): Filename to be opened
        new_mode (str): One of the modes that can be passed to open()
        original_fp (str): previously opened file-like object

    Returns:
        A new file-like object opened in the new mode

    """
    full_path = path.join(build_fs_path(temp_dir,
                                        file_type,
                                        owner_type,
                                        owner_id), filename)
    original_fp.close()
    return open(full_path, new_mode)


def build_fs_path(temp_dir, file_type, owner_type=None, owner_id=None):
    """Builds a file system path from the provided parameters

    Args:
        temp_dir (str): The directory on the local filesystem where the library
         can store intermediate files
        file_type (str): 'xlsx' or 'json'
        owner_type (str): 'vendor' or 'subaccount'
        owner_id (str): Concatenated with the owner_type to build a user-
         specific temporary directory

    Returns:
        str: Absolute path to the requested directory

    """
    root_folder = temp_dir + '/' + file_type + '/'
    if owner_type is not None and owner_id is not None:
        return root_folder + owner_type + '_' + owner_id + '/'
    else:
        return root_folder


def build_s3_path(prefix, file_type, owner_type=None, owner_id=None):
    """Builds an AWS S3 path from the provided parameters

    Args:
        prefix (str): The top-level folder in the bucket
        file_type (str): 'xlsx' or 'json'
        owner_type (str): 'vendor' or 'subaccount'
        owner_id (str): Concatenated with the owner_type to build a user-
         specific temporary directory

    Returns:
        str: AWS S3 path

    """
    root_folder = prefix + '/' + file_type
    if owner_type is not None and owner_id is not None:
        return root_folder + '/' + owner_type + '_' + owner_id
    else:
        return root_folder


def get_file_from_s3(s3, bucket_name, folder, filename, fp):
    """Gets the contents of an S3 Object and writes them to a file-like object

    Args:
        s3 (boto.s3.connection.S3Connection): AWS S3 object
        bucket_name (str): bucket name
        folder (str): folder inside of the bucket
        filename (str): file name to get
        fp: file-like object to write the S3 Object's contents to

    Returns:
        file-like object: The file-like object to which the S3 Object's
        contents have been written.

        NOTE:  The file-like object's pointer is NOT reset after the contents
        are written.

    """
    bucket = s3.get_bucket(bucket_name)
    key = bucket.get_key(folder + '/' + filename)
    # write the contents to the file pointer
    key.get_contents_to_file(fp)
    return fp


def put_file_to_s3(s3, bucket_name, folder, filename, fp):
    """Sets the contents of an S3 Object from a file-like object

    Args:
        s3 (boto.s3.connection.S3Connection): AWS S3 object
        bucket_name (str): bucket name
        folder (str): folder inside of the bucket
        filename (str): file name to get
        fp: file-like object to read the S3 Object's contents from

    Returns:
        boto.s3.key: The S3 Object Key to which the file-like object's
        contents have been written.

    """
    bucket = s3.get_bucket(bucket_name)
    k = Key(bucket)
    k.key = folder + '/' + filename
    k.set_contents_from_file(fp)
    return k


def convert_xlsx_to_json(xlsx_fp, json_fp, filename, row_offset):
    """Reads data from an Excel 2007 file-like object, and writes the data of
    the file as line-delimited json to another file-like object

    Args:
        xlsx_fp: file-like object containing the Excel 2007 binary content
        json_fp: file-like object to which the json data is written
        filename: name of the file being processed
        row_offset (int): initial rows to skip

    Returns:
        file-like object: The file-like object to which the json data has been
        written.

    """

    # now that we have the Excel 2007 file in the local filesystem,
    # we'll use openpyxl to read the data back out as json
    wb = openpyxl.load_workbook(xlsx_fp, read_only=True)

    ws = wb[wb.sheetnames[0]]

    structure = []

    # the output will in line-delimited json (ie. individual json
    # objects on each line - for more information see http://jsonlines.org/).
    # This is to support large datasets that would
    # be resource-intensive to parse by a downstream component

    # loop through each row in the Excel spreadsheet and
    # write the data out as json to the output file
    for i, row in enumerate(ws.iter_rows(row_offset=row_offset)):
        # set the data structure
        if i == 0:
            structure = [cell.value for cell in row]
            structure.insert(0, 'row')
            structure.insert(0, 'filename')
            continue

        # enhance the data with row-level processing data
        rowdata = [cell.value for cell in row]

        if any(val is not None for val in rowdata):
            rowdata.insert(0, i)
            rowdata.insert(0, filename)

            json_fp.write(bytes(json.dumps(
                dict(zip(structure, rowdata))) + '\n', 'UTF-8'))

    return json_fp


def convert_json_to_xlsx(json_fp, xlsx_fp, filename, template):
    """Reads data from a line-delimited file-like object, and writes the data of
    the file as binary Excel 2007 data to another file-like object

    Args:
        json_fp: file-like object to which the json data is written
        xlsx_fp: file-like object containing the Excel 2007 binary content
        filename: name of the file being processed
        template: Excel 2007 file to be used as a template for the output file

    Returns:
        file-like object: The file-like object to which the binary Excel 2007
        data has been written.

    """

    # now that we have the line-delimited json file in the local filesystem,
    # we'll use openpyxl to read the data back out as Excel 2007

    # rather than try to control the formatting of the output file
    # programmatically, we'll just use a pre-existing Excel 2007 file with the
    # desired formatting and copy the header and 1st row to a new write-only
    # sheet in a different workbook.

    # open up the template file
    template_wb = openpyxl.load_workbook(template)
    template_ws = template_wb[template_wb.sheetnames[0]]
    lookups_ws = template_wb[template_wb.sheetnames[1]]

    output_wb = openpyxl.Workbook(write_only=True)
    output_ws = output_wb.create_sheet(title='Data')
    output_lookups_ws = output_wb.create_sheet(title='Lookups')

    # copy the Lookups sheet
    for i, row in enumerate(lookups_ws.rows):
        output_lookups_ws.append(row)

    # copy the named ranges from the template workbook to the output workbook
    output_wb._named_ranges = template_wb._named_ranges

    # the input will in line-delimited json (ie. individual json
    # objects on each line - for more information see http://jsonlines.org/).
    # This is to support large datasets that would
    # be resource-intensive to parse

    # copy the column dimensions over
    # NOTE: this MUST be done before any rows are written out!
    copyColumnDimensions(template_ws, output_ws)

    # copy the data validation definitions from the template
    output_ws._data_validations = template_ws._data_validations

    # copy the autofilter ref value from the template

    # NOTE: the autofilter functionality is also dependent on a named range
    # which defines the ranges to be used when calculating the values list
    # NOQA See https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname(v=office.14).aspx
    # NOQA and https://bitbucket.org/openpyxl/openpyxl/issue/481/safe-reserved-ranges-are-not-read-from
    # for more details

    output_ws.auto_filter.ref = template_ws.auto_filter.ref
    # freeze pane before populating
    template_freeze_panes = template_ws.freeze_panes
    if template_freeze_panes:
        output_ws.freeze_panes = template_freeze_panes

    # copy the header row over to the new sheet
    header_row = []
    for column_index, cell in enumerate(template_ws.rows[0]):
        new_cell = WriteOnlyCell(output_ws, value=cell.value)
        new_cell.coordinate = cell.coordinate
        copyStyle(cell, new_cell)
        cell_comment = cell.comment
        if cell_comment:
            # delete the comment in the original sheet to avoid AttributeError
            # NOQA See http://openpyxl.readthedocs.org/en/latest/comments.html#adding-a-comment-to-a-cell
            cell.comment = None
            new_cell.comment = cell_comment
            new_cell.comment._height = '150pt'
            new_cell.comment._width = '250pt'
        header_row.append(new_cell)
    output_ws.append(header_row)

    # grab a copy of the first data row to use as a template
    # this will hopefully persist as much of the formatting and functionality
    # (data validation, etc.) as is supported by openpyxl
    template_row = template_ws.rows[1]

    row_index = 2
    for row in json_fp:
        new_row = []
        # keep the ordering from the file
        data = json.loads(row)
        for key, value in data.items():
            # loop over the header row to figure out what column this data
            # should go in
            column_index = -1
            template_cell = None
            for column_index, cell in enumerate(header_row):
                # once the column has been identified, grab the matching cell
                # from the row template
                if cell.value == key:
                    template_cell = template_row[column_index]
                    break
            # if this data key is not in the template row, skip it
            if template_cell is None:
                continue

            if (isinstance(value, str)):
                # copied from openpyxl's list of illegal characters
                ILLEGAL_CHARACTERS_RE = re.compile(
                    r'[\000-\010]|[\013-\014]|[\016-\037]')
                # replace illegal characters with a blank space
                value = re.sub(ILLEGAL_CHARACTERS_RE, ' ', value)
            new_cell = WriteOnlyCell(output_ws, value=value)
            new_cell.coordinate = template_cell.column + str(row_index)

            # copy the "style" - this includes cell protection and hiding!
            copyStyle(template_cell, new_cell)

            # if the template cell is in the data validation cells list
            # add the new cell to the output_ws validations cells
            updateOutputValidations(template_ws,
                                    output_ws,
                                    template_cell,
                                    new_cell)

            new_row.append(new_cell)
        row_index += 1

        # before appending to the worksheet, rearrange the cells to their
        # correct positions
        new_row.sort(key=lambda cell: cell.coordinate)

        # TODO(rick): fix data validations
        output_ws.append(new_row)

    output_ws.protection = template_ws.protection

    output_wb.save(xlsx_fp)

    return xlsx_fp


def updateOutputValidations(template_ws, output_ws, template_cell, new_cell):
    validation_index = 0
    for validations in template_ws._data_validations:
        if template_cell.coordinate in validations.cells:
            output_validation = output_ws._data_validations[validation_index]
            output_validation.cells.append(new_cell.coordinate)
        validation_index += 1


def copyStyle(fromCell, toCell):
    toCell.alignment = fromCell.alignment.copy()
    toCell.border = fromCell.border.copy()
    toCell.fill = fromCell.fill.copy()
    toCell.font = fromCell.font.copy()
    toCell.protection = fromCell.protection.copy()
    toCell.number_format = fromCell.number_format


def copyColumnDimensions(fromSheet, toSheet):
    for column_letter in fromSheet.column_dimensions.keys():
        templateColumnDimension = fromSheet.column_dimensions[column_letter]
        newColumnDimension = ColumnDimension(
            worksheet=toSheet,
            width=templateColumnDimension.width,
            visible=templateColumnDimension.visible
        )
        toSheet.column_dimensions[column_letter] = newColumnDimension