import os
import sys
import csv
import re
import numpy as np
import pandas as pd
import openpyxl


from tklib.tkprogvars import tkprog_X_path
from tklib.tkutils import terminate, get_ext
from tklib.tkdatafile import tkDataFile
from tklib.tkcsv import tkCSV
from tklib.tkexcel import tkExcel
import tklib.tkre as tkre


pattern_int   = re.compile(r'^[-+]?[0-9]+$')
pattern_float = re.compile(r'^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$')


def find_template(template):
    if os.path.isfile(template): return template

    script_dir = os.path.dirname(os.path.abspath(sys.argv[0]))
    candidate = os.path.join(script_dir, template)
    if os.path.isfile(candidate): return candidate

    _template = os.path.join(tkprog_X_path, "excel", "template", template)
    if os.path.isfile(_template): return _template

    return None

class tkVariousData(tkDataFile):
    def __init__(self, path = None, mode = 'r', OpenFile = 1, data_only = True, **args):
#        super(tkFile, self).__init__(path, mode, **args)
        self.fp    = None
        self.path  = None
        self.mode  = None
        self.path  = self.IfYes(path is not None, path, self.path)
        self.mode  = self.IfYes(mode is not None, mode, self.mode)
        self.update(**args)
        
        self.labelarray    = []
        self.datalistarray = []

    def __del__(self):
        self.Close()

    def __str__(self):
        return self.ClassPath()


    def Read_minimum_matrix(self, close_fp = False, force_numeric = True, usage = None):
        if(tkre.Search(r'\.xlsx$', self.path, flag ='i')):
            excel = tkExcel(self.path)
            self.labelarray, self.datalistarray = excel.Read_minimum_matrix(close_fp = close_fp, force_numeric = force_numeric)
        elif(tkre.Search(r'\.csv$', self.path, flag ='i')):
            csv = tkCSV(self.path)
            self.labelarray, self.datalistarray = csv.Read_minimum_matrix(close_fp = close_fp, force_numeric = force_numeric)
        else:
            terminate("Error in tkVariousData.Read_minimum_matrix(): File type [{}] is not supported."
                    .format(self.path), usage = usage)

        return self.labelarray, self.datalistarray

    def read_data(self, infile, xlabel = 0, ylabel = 1, xmin = None, xmax = None, usage = None):
        print("")
        print(f"Read [{infile}]")
        labels, datalist = self.Read_minimum_matrix(close_fp = True, force_numeric = False, usage = usage)
        xlabel, xin = self.FindDataArray(xlabel, flag = 'i')
        ylabel, yin = self.FindDataArray(ylabel, flag = 'i')

        self.labels   = labels
        self.datalist = datalist
        self.xlabel   = xlabel
        self.ylabel   = ylabel

        self.ndata_all = len(xin)
        print("ndata_all=", self.ndata_all)
        self.x = []
        self.y = []
        self.included_index = []
        for i in range(self.ndata_all):
            if xmin is not None and xmin > xin[i]:
                continue
            if xmax is not None and xmax < xin[i]:
                continue
            
            self.x.append(xin[i])
            self.y.append(yin[i])
            self.included_index.append(i)

        self.ndata = len(self.x)
        self.index = range(self.ndata)

        return labels, datalist

    def to_csv(self, outfile, labels, data_list, print_level = 1):
        try: 
            if print_level:
                print(f"Save to [{outfile}]")
            f = open(outfile, 'w')
        except Exception as e:
            if print_level:
                print(f"Error: Can not write to [{outfile}] due to error [{e}]")
            return False

        fout = csv.writer(f, lineterminator='\n')
        fout.writerow(labels)
        for i in range(0, len(data_list[0])):
            a = []
            for j in range(len(data_list)):
                v = data_list[j][i]
                if type(v) is float:
                    if abs(v) > 1.0e6:
                        v = f"{v:e}"
                    else:
                        v = f"{v:g}"
                a.append(v)
            fout.writerow(a)

        f.close()
        return True

    def reformat_workbook(self, wb):
        ws_main = wb.worksheets[0]
        for col in ws_main.iter_cols(min_col=1, max_col=5, min_row=1, max_row=ws_main.max_row):
            for cell in col:
                cell.value = None

        #wbの全シートからChartを探して削除
        for sheet in wb.sheetnames:
            ws = wb[sheet]
            for chart in ws._charts:
                ws._charts.remove(chart)

        return ws_main

    def to_excel_from_dataframe(self, outfile, df, template = None, isheet_create = 0, print_level = 1):
        labels = list(df.columns)
        data_list = df.values.tolist()
        data_list = list(map(list, zip(*data_list)))
        self.to_excel(outfile, labels, data_list, 
                template = template, isheet_create = isheet_create, print_level = print_level)

    def to_excel(self, outfile, labels, data_list, template = None, isheet_create = 0, print_level = 1):
        t = type(labels[0])
        if t is list or t is tuple or isinstance(t, np.ndarray):
            data_all = []
            labels_all = []
            for d, l in zip(data_list, labels):
                data_all.extend(d)
                labels_all.extend(l)

            data_list = data_all
            labels = labels_all
        else:
            data_list = data_list.copy()

        nx = len(data_list)
        max_ndata = 0
        for d in data_list:
            ndata = len(d)
            if max_ndata < ndata:
                max_ndata = ndata
        for i in range(nx):
            ndata = len(data_list[i])
            if ndata < max_ndata:
                if type(data_list[i]) is not list:
                    data_list[i] = data_list[i].tolist()
                else:
                    data_list[i] = data_list[i].copy()

                data_list = data_list.copy()
                for j in range(max_ndata - ndata):
                    data_list[i].append(None)

        for i in range(nx):
            dl = data_list[i]
            for j in range(len(dl)):
                t = type(dl[j])
                if t is int or t is float:
                    continue

                if t is str:
                    if pattern_int.match(dl[j]):
                        dl[j] = int(dl[j])
                    else:
                        if pattern_float.match(dl[j]):
                            dl[j] = float(dl[j])

        _template = None
        if template:
            _template = find_template(template)
            if _template != template:
                print(f"tkVariousData.to_excel(): Use Excel template [{_template}] to save to [{outfile}]")
            elif _template is None:
                print(f"Warning in tkVariousData.to_excel(): Can not find Excel template [{template}]")

        if _template is not None :
            if not outfile.lower().endswith(".xlsm"):
                outfile = os.path.splitext(outfile)[0] + ".xlsm"
                print(f"Warning in tkVariousData.to_excel(): .xlsm template is given but the output file is not .xlsm.")
                print(f"    Change output file path to [{outfile}]\n")

            worksheet_name = "data"
            wb = openpyxl.load_workbook(_template, keep_vba=True)
            self.reformat_workbook(wb)
            nworksheets = len(wb.sheetnames)
            if isheet_create <= 0:
                ws = wb.create_sheet(title=worksheet_name, index = 0)
            elif nworksheets + 1 < isheet_create:
                ws = wb.create_sheet(title=worksheet_name, index = nworksheets + 1)
            else:
                ws = wb.create_sheet(title=worksheet_name, index = isheet_create)
            wb.active = wb.sheetnames.index(worksheet_name)
        else:
            wb = openpyxl.Workbook()

        ws = wb.active

        for col, label in enumerate(labels, 1):
            ws.cell(row = 1, column = col, value = label)
        for col, data_col in enumerate(data_list, 1):
            for row, data in enumerate(data_col, 2):
                ws.cell(row = row, column = col, value = data)

        if _template:
            wb.active = wb.worksheets[0]

        try: 
            if print_level:
                print(f"Save to [{os.path.abspath(outfile)}]")
            wb.save(outfile)
        except Exception as e:
            if print_level:
                print(f"Error: Can not write to [{outfile}] due to error [{e}]")

        if not os.path.exists(outfile):
            if print_level:
                print("")
                print(f"Error in tkVariousData.to_excel(): Could not write to [{outfile}].")
            return False

        return True

    def save(self, outfile, labels, data_list, print_level = 1):
        ext = get_ext(outfile).lower()
        if ext == '.xlsx':
            return self.to_excel(outfile, labels, data_list, print_level = print_level)
        elif ext == '.csv':
            return self.to_csv(outfile, labels, data_list, print_level = print_level)
        
        if print_level:
            print(f"Error in tkVariousData().save(): Invalid extension [{ext}] for [{outfile}]")
        return False