# Path: chat/mytools.py
# def read_excel(filepath):
#     """Get excel source.
# 
#     Args:
#         filepath: The full path of excel file. Excel 文件完整路径。
# 
#     Returns:
#         data: Data of excel. Excel 数据。
#     """
#     is_valid = False
#     try:
#         if os.path.isfile(filepath):
#             filename = os.path.basename(filepath)
#             if filename.split('.')[1] in ['xls', 'xlsx']:
#                 is_valid = True
#         data = None
#         if is_valid:
#             data = xlrd.open_workbook(filepath)
#     except Exception as xls_error:
#         raise TypeError("Can't get data from excel!") from xls_error
#     return data
# 
# def write_excel_sql(filename="demo.xlsx", sheets=None):
#     """Write excel from data.
#     """
#     file = xlwt.Workbook() # 创建工作簿
#     for sheet in sheets:
#         new_sheet = file.add_sheet(sheet["name"], cell_overwrite_ok=True) # 创建sheet
#         info = sheet["info"]
#         for col, key in enumerate(info):
#             new_sheet.write(0, col, key[1], set_excel_style('Arial Black', 220, True))
#             new_sheet.write(1, col, key[0], set_excel_style('Arial Black', 220, True))
#         for index, item in enumerate(sheet["items"]):
#             for col, key in enumerate(info):
#                 new_sheet.write(index+2, col, item[col+1])
#     file.save(filename) # 保存文件
# 
# Path: chat/semantic.py
# def get_tag(sentence, config):
#     """Get semantic tag of sentence. 获取句子语义标签。
#     """
#     iquestion = sentence.format(**config)
#     try:
#         keywords = analyse.extract_tags(iquestion, topK=1)
#         keyword = keywords[0]
#     except IndexError:
#         keyword = iquestion
#     tags = synonym_cut(keyword, 'wf') # tuple list
#     if tags:
#         tag = tags[0][1]
#         if not tag:
#             tag = keyword
#     else:
#         tag = keyword
#     return tag


import os
import sqlite3
import string
from tkinter.filedialog import askopenfilename
from .mytools import read_excel, write_excel_sql
from .semantic import get_tag
if __name__ == '__main__':
    db = Database(path='C:/nlu/data/db/nlu_sqlite.db', sql=sqlite3, userid="A0001")
    
    print('删除数据库表测试...')
    for table in ['User', 'Config', 'NluCell']:
        db.drop_table(table)
        
    print('创建数据库表测试...')
    db.create_table_user()
    db.create_table_config()
    db.create_table_nlucell()
    
    print('添加 User 测试...')
    db.add_user()
    
    print('添加 Config 测试...')
    add_config = '''INSERT INTO Config values (?, ?, ?, ?, ?)'''
    config = [(1, 'A0001', '银行业务', '', '1'),
            (2, 'A0001', '闲聊', '', '1'),
            (3, 'A0002', '银行业务', '', '1')]
    db.update(add_config, config)
    
    print('添加 NluCell 测试...')
    db.handle_excel('C:/nlu/data/kb/chat.xls')
    print('可用知识库：', db.get_available_kb())
    print('已挂接知识库：', db.get_selected_kb())
    
    print('重置 NluCell 测试...')
    db.reset(tabel='NluCell', filename='C:/nlu/data/kb/chat.xls')
    

# Path: chat/mytools.py
# def read_excel(filepath):
#     """Get excel source.
# 
#     Args:
#         filepath: The full path of excel file. Excel 文件完整路径。
# 
#     Returns:
#         data: Data of excel. Excel 数据。
#     """
#     is_valid = False
#     try:
#         if os.path.isfile(filepath):
#             filename = os.path.basename(filepath)
#             if filename.split('.')[1] in ['xls', 'xlsx']:
#                 is_valid = True
#         data = None
#         if is_valid:
#             data = xlrd.open_workbook(filepath)
#     except Exception as xls_error:
#         raise TypeError("Can't get data from excel!") from xls_error
#     return data
# 
# def write_excel_sql(filename="demo.xlsx", sheets=None):
#     """Write excel from data.
#     """
#     file = xlwt.Workbook() # 创建工作簿
#     for sheet in sheets:
#         new_sheet = file.add_sheet(sheet["name"], cell_overwrite_ok=True) # 创建sheet
#         info = sheet["info"]
#         for col, key in enumerate(info):
#             new_sheet.write(0, col, key[1], set_excel_style('Arial Black', 220, True))
#             new_sheet.write(1, col, key[0], set_excel_style('Arial Black', 220, True))
#         for index, item in enumerate(sheet["items"]):
#             for col, key in enumerate(info):
#                 new_sheet.write(index+2, col, item[col+1])
#     file.save(filename) # 保存文件
# 
# Path: chat/semantic.py
# def get_tag(sentence, config):
#     """Get semantic tag of sentence. 获取句子语义标签。
#     """
#     iquestion = sentence.format(**config)
#     try:
#         keywords = analyse.extract_tags(iquestion, topK=1)
#         keyword = keywords[0]
#     except IndexError:
#         keyword = iquestion
#     tags = synonym_cut(keyword, 'wf') # tuple list
#     if tags:
#         tag = tags[0][1]
#         if not tag:
#             tag = keyword
#     else:
#         tag = keyword
#     return tag


# Path: chat/sql.py
import os
import sqlite3
import string
from tkinter.filedialog import askopenfilename
from .mytools import read_excel, write_excel_sql
from .semantic import get_tag

if __name__ == '__main__':
    db = Database(path='C:/nlu/data/db/nlu_sqlite.db', sql=sqlite3, userid="A0001")
    
    print('删除数据库表测试...')
    for table in ['User', 'Config', 'NluCell']:
        db.drop_table(table)
        
    print('创建数据库表测试...')
    db.create_table_user()
    db.create_table_config()
    db.create_table_nlucell()
    
    print('添加 User 测试...')
    db.add_user()
    
    print('添加 Config 测试...')
    add_config = '''INSERT INTO Config values (?, ?, ?, ?, ?)'''
    config = [(1, 'A0001', '银行业务', '', '1'),
            (2, 'A0001', '闲聊', '', '1'),
            (3, 'A0002', '银行业务', '', '1')]
    db.update(add_config, config)
    
    print('添加 NluCell 测试...')
    db.handle_excel('C:/nlu/data/kb/chat.xls')
    print('可用知识库：', db.get_available_kb())
    print('已挂接知识库：', db.get_selected_kb())
    
    print('重置 NluCell 测试...')
    db.reset(tabel='NluCell', filename='C:/nlu/data/kb/chat.xls')
    

    print('下载 NluCell 测试...')