"""
This interfaces the reader for a data-transport
"""

import cms
import transport
from io import BytesIO, StringIO
import pandas as pd
import numpy as np
import json
import base64
from flask import make_response, send_file

@cms.Plugin(mimetype="application/json",method="POST")
def read(**_args) :
    _request = _args['request']
    _body = _request.json
    _data = {}
    try:
        reader = transport.get.reader(label = _body['label']) 
        _query = _body['query']
        #
        # @TODO : 
        #   - support for mongodb and NoSQL ...
        #   - limit to 10K
        _data = reader.apply(_query)
        _data.to_dict(orient='split')
        del _data['index']
    except Exception as e:
        print (e)
    return _data
def format (file,_mimetype) :
    if 'csv' in _mimetype :
        _df = pd.read_csv(file).fillna('n/a')
        
    rows = _df.apply(lambda row:row.values ,axis=1).apply(lambda row: {'cells':[{'value':x}for x in row]} ).tolist()
    rows = [{"cells":[ {'value':_name} for _name in _df.columns]}] + rows
    sheets = [{'usedRange':{'colIndex':1,'rowIndex':_df.shape[0]},'name':'sheet1','rows':rows,'standardHeight':20}]
    return {'Workbook':{'sheets':sheets}}
    


@cms.Plugin(mimetype="application/json",method="POST")
def open(**_args):
    _request = _args['request']
    file = _request.files['file']
    spreadsheet = format(file,file.mimetype)
    return spreadsheet #{"Workbook":{"sheets":[{"usedRange":{"colIndex":1,"rowIndex":3},"name":"Sheet1","rows":[{"cells":[{"value":"name"},{"value":"age"}]},{"cells":[{"value":"steve"},{"value":"44"}]},{"cells":[{"value":"elon"},{"value":"9"}]},{"cells":[{"value":"nico"},{"value":"33"}]}],"standardHeight":20}]}})
@cms.Plugin(mimetype="application/octet-stream",method="POST")
def write(**_args) :
    _request = _args['request']
    _object = _request.json
    _sheets = None
    if _object :
        _rows = _object['rows']
        _label = _object['label']
    #
    # convert this to a spreadsheet
    _excelFile = BytesIO()
    with pd.ExcelWriter(_excelFile, engine='openpyxl') as writer:
        _index = 1
        for _name in _rows :
            _sheet = _rows[_name]
            _df  = pd.DataFrame(_sheet['values'],columns=_sheet['columns'])

            _df.to_excel(writer,sheet_name=_name,index=False)
            _index += 1
        
    # Write each DataFrame to a different sheet
    _excelFile.seek(0)
    _stream= _excelFile.read() #send_file(_excelFile,as_attachment=True,download_name=f'{_label}-export.xlsx', mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    return base64.b64encode(_stream).decode('utf-8')