import os
import sys
import copy
import re
import openpyxl
#import msoffcrypto
import pandas as pd


from tklib.tkutils import pint, pfloat, del_quote, split_two, split_quoted_args, split_command_line, quote_command_if_space
from tklib.tkexcel import tkExcel, tkExcel_sheet


class tkExcelDB(tkExcel):
    def __init__(self, path = None, mode = 'r', table_name = None, password = None, allow_no_password = False, tmp_file = None,
                    OpenFile = True, CloseFile = False, try_text_file = False, data_only = True, description = '', split_from = 1, **args):
        self.description = description
        
        self.fp       = None
        self.path     = None
        self.mode     = None
        self.password = None
        self.tmp_file = None
        self.try_text_file = try_text_file
        self.wb       = None
        self.ws       = None
        self.table_name = table_name
        self.isheet     = None
        self.sheetname  = None
        self.dataframe  = None
        self.split_from = split_from

        super().__init__(path = path, mode = mode, password = password, allow_no_password = allow_no_password, tmp_file = tmp_file, 
                            OpenFile = OpenFile, CloseFile = CloseFile, try_text_file = try_text_file, data_only = data_only, split_from = split_from, **args)
        
        if mode == 'w': #self.path is None:
            self.wb = workbook = openpyxl.Workbook()
            self.ws = self.wb.active

        if OpenFile and self.wb and self.table_name:
            if self.ws is not None:
                self.isheet, self.sheetname, self.ws = self.find_isheet(self.table_name, reg_exp = True)

        if CloseFile:
            self.close()


#        self.update(**args)

#        self.ws = self.wb.worksheets[0]


    def __del__(self):
#        self.close()
        pass

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


    def print_inf(self):
        print("")
        print(f"description: {self.description}")
        print(f"  path: {self.path}")
        print(f"    mode: {self.mode}")
        print(f"  # of worksheet: {len(self.wb.worksheets)}")
        for i in range(len(self.wb.sheetnames)):
            print(f"    {i}: {self.wb.sheetnames[i]}")

        isheet, sheetname, ws = self.find_isheet(self.wb.worksheets)
        if isheet is None:
            print(f"  Error: current worksheet cannot be identified")
        else:
            print(f"  current worksheet #{isheet}: {self.wb.sheetnames[isheet]}")


    def use_dataframe(self, labels, data_list):
        df = pd.DataFrame(data_list, columns = labels)
        self.dataframe = df

        return df

    def add_to_list(self, irow, itargets, labels, data_list, data_dict):
        for i in range(len(itargets)):
            icol  = itargets[i]
            label = labels[i]
            val = self.get(irow = irow, icol = icol, def_val = '')

            data_list.append(val)
            data_dict[label] = val

    def evaluate(self, var1_pos, operator, str1, str2, args, irow = '', def_val = '', is_print = False):
        fval1 = pfloat(str1, 'nan')
        fval2 = pfloat(str2, 'nan')
        str1  = f"{str1}"

        is_ok = False
        if operator == '==' or operator == '!=' or operator == '<' or operator == '<=' or operator == '>' or operator == '>=':
            if fval1 == 'nan':
                if is_print:
                    print(f"Error in tkexcel_db.evaluate(): irow={irow}: Invalid argment #1 [{str1}] for operator [{operator}]")
                return None
            if fval2 == 'nan':
                if is_print:
                    print(f"Error in tkexcel_db.evaluate(): irow={irow}: Invalid argment #2 [{str2}] for operator [{operator}]")
                return None

        if operator == '==':
            if fval1 == fval2:
                is_ok = True
        if operator == '!=':
            if fval1 != fval2:
                is_ok = True
        elif operator == '<':
            if fval1 < fval2:
                is_ok = True
        elif operator == '<=':
            if fval1 <= fval2:
                is_ok = True
        elif operator == '>':
            if fval1 > fval2:
                is_ok = True
        elif operator == '>=':
            if fval1 >= fval2:
                is_ok = True
        elif operator == 'eq':
            if str1 == str2:
                is_ok = True
        elif operator == 'neq':
            if str1 != str2:
                is_ok = True
        elif operator == 'like':
            if re.search(str2, str1):
                is_ok = True
        elif operator == 'not like':
            if not re.search(str2, str1):
                is_ok = True
        else:
            print(f"Error in tkexcel_db.evaluate(): irow={irow}: Invalid operator [{operator}]")
            exit()

        return is_ok

    def select_irows(self, condition, key_column_org = 1, key_row_org = 1, target_row_org = 2, is_print = False, first_hit_only = False):
        if is_print:
            print("")
            print(f"Search [{condition}] to find irow")

# 検索スタート
        hit = []
        _args = split_quoted_args(condition, quotation = '"')
        for irow in range(target_row_org, self.max_row() + 1):
            args = _args.copy()
            while len(args) > 0:
                var1_pos  = 'h'
                if args[0] == 'and' or args[0] == 'or':
                    operator_h = args[0]
                    args       = args[1:]
                else:
                    operator_h = 'first'

                var_name1 = del_quote(args[0])
                args      = args[1:]

                if args[0] == 'not' and args[1] == 'like':
                    operator = "not like"
                    str2 = del_quote(args[2])
                    args = args[3:]
                else:
                    operator = args[0]
                    str2     = del_quote(args[1])
                    args     = args[2:]

#                print(f"op: [{var_name1}] [{operator}] [{str2}] (args={args})")

                ivar1 = self.get_icolumn_from_label_regex(var_name1, sheet = self.ws, 
                                column_org = key_column_org, row_org = key_row_org)
                str1 = self.get(irow = irow, icol = ivar1, def_val = '')

#                print(f"h={operator_h}: {var_name1}={str1} {operator} {str2} (args={args})")
                ret = self.evaluate(var1_pos, operator, str1, str2, args, irow = irow, def_val = '', is_print = False)
#                print("ret=", ret)
                if ret is None:
                    break

                if operator_h == 'first':
                    is_ok = ret
                elif operator_h == 'and':
#                    print("  and:", is_ok, ret, end = '')
                    is_ok = is_ok and ret
#                        print(" => ", is_ok)
                elif operator_h == 'or':
#                    print("  and:", is_ok, ret, end = '')
                    is_ok = is_ok or ret
#                    print(" => ", is_ok)

#            print("  is_ok=", is_ok)
            if is_ok is None or not is_ok:
                 continue

            if first_hit_only:
                return irow

            hit.append(irow)

        if first_hit_only:
            return None

        return hit

    def select(self, condition, target_labels = None, 
                    key_column_org = 1, target_row_org = 2, key_row_org = 1, 
                    ret_type = 'list', is_print = False):
        if is_print:
            print("")
            print(f"Search [{condition}] for ", target_labels)

        if target_labels is None:
            target_labels  = self.get_labels(irow_origin = key_row_org, icol_origin = key_column_org)

# 戻り値に入れるフィールド番号・フィールド名のリスト
        itargets = []
        labels   = []
        for i in range(len(target_labels)):
            idx = self.get_icolumn_from_label_regex(target_labels[i], sheet = self.ws, 
                        column_org = key_column_org, row_org = key_row_org)
            itargets.append(idx)
#            print("i=", i, target_labels[i], idx, key_row_org)
            label = self.get(irow = key_row_org, icol = idx, def_val = '')
            labels.append(label)

# 検索スタート
        _args = split_quoted_args(condition, quotation = '"')
        irow_list = self.select_irows(condition = condition, key_column_org = key_column_org, key_row_org = key_row_org, 
                                     target_row_org = target_row_org, is_print = is_print)

        res       = []
        res_dict  = []
        for irow in irow_list:
            data_list = []
            data_dict = {}
            self.add_to_list(irow, itargets, labels, data_list, data_dict)
#            print("data_list=", data_list)

# 戻り値のリストに追加
            res_dict.append(data_dict)
            if ret_type == 'list' or ret_type == 'all':
                res.append(data_list)
            elif ret_type == 'dict':
                res.append(data_dict)
            else:
                print("")
                print(f"Error in tkexcel_db.select(): Invalid ret_type=[{ret_type}]")
                exit()

        if ret_type == 'all':
            return {"labels": labels, "list": res, "dict": res_dict, "irows": irow_list}
        if ret_type == 'irows':
            return labels, irows

        return labels, res

