# -*- coding: UTF-8 -*-
""" 
@author: hhyo
@license: Apache Licence
@file: sql_utils.py
@time: 2019年03月13日
"""
import re
import xml
import mybatis_mapper2sql
import sqlparse

from sql.engines.models import SqlItem
from sql.utils.extract_tables import extract_tables as extract_tables_by_sql_parse

__author__ = 'hhyo'


def get_syntax_type(sql, parser=True, db_type='mysql'):
 """
 返回SQL语句类型,仅判断DDL和DML
 :param sql:
 :param parser: 是否使用sqlparse解析
 :param db_type: 不使用sqlparse解析时需要提供该参数
 :return:
 """
 sql = remove_comments(sql=sql, db_type=db_type)
 if parser:
 try:
 statement = sqlparse.parse(sql)[0]
 syntax_type = statement.token_first(skip_cm=True).ttype.__str__()
 if syntax_type == 'Token.Keyword.DDL':
 syntax_type = 'DDL'
 elif syntax_type == 'Token.Keyword.DML':
 syntax_type = 'DML'
 except Exception:
 syntax_type = None
 else:
 if db_type == 'mysql':
 ddl_re = r"^alter|^create|^drop|^rename|^truncate"
 dml_re = r"^call|^delete|^do|^handler|^insert|^load\s+data|^load\s+xml|^replace|^select|^update"
 elif db_type == 'oracle':
 ddl_re = r"^alter|^create|^drop|^rename|^truncate"
 dml_re = r"^delete|^exec|^insert|^select|^update|^with|^merge"
 else:
 # TODO 其他数据库的解析正则
 return None
 if re.match(ddl_re, sql, re.I):
 syntax_type = 'DDL'
 elif re.match(dml_re, sql, re.I):
 syntax_type = 'DML'
 else:
 syntax_type = None
 return syntax_type


def remove_comments(sql, db_type='mysql'):
 """
 去除SQL语句中的注释信息
 来源:https://stackoverflow.com/questions/35647841/parse-sql-file-with-comments-into-sqlite-with-python
 :param sql:
 :param db_type:
 :return:
 """
 sql_comments_re = {
 'oracle':
 [r'(?:--)[^\n]*\n', r'(?:\W|^)(?:remark|rem)\s+[^\n]*\n'],
 'mysql':
 [r'(?:#|--\s)[^\n]*\n']
 }
 specific_comment_re = sql_comments_re[db_type]
 additional_patterns = "|"
 if isinstance(specific_comment_re, str):
 additional_patterns += specific_comment_re
 elif isinstance(specific_comment_re, list):
 additional_patterns += "|".join(specific_comment_re)
 pattern = r"(\".*?\"|\'.*?\')|(/\*.*?\*/{})".format(additional_patterns)
 regex = re.compile(pattern, re.MULTILINE | re.DOTALL)

 def _replacer(match):
 if match.group(2):
 return ""
 else:
 return match.group(1)

 return regex.sub(_replacer, sql).strip()


def extract_tables(sql):
 """
 获取sql语句中的库、表名
 :param sql:
 :return:
 """
 tables = list()
 for i in extract_tables_by_sql_parse(sql):
 tables.append({
 "schema": i.schema,
 "name": i.name,
 })
 return tables


def generate_sql(text):
 """
 从SQL文本、MyBatis3 Mapper XML file文件中解析出sql 列表
 :param text:
 :return: [{"sql_id": key, "sql": soar.compress(value)}]
 """
 # 尝试XML解析
 try:
 mapper, xml_raw_text = mybatis_mapper2sql.create_mapper(xml_raw_text=text)
 statements = mybatis_mapper2sql.get_statement(mapper, result_type='list')
 rows = []
 # 压缩SQL语句,方便展示
 for statement in statements:
 for key, value in statement.items():
 row = {"sql_id": key, "sql": value}
 rows.append(row)
 except xml.etree.ElementTree.ParseError:
 # 删除注释语句
 text = sqlparse.format(text, strip_comments=True)
 statements = sqlparse.split(text)
 rows = []
 num = 0
 for statement in statements:
 num = num + 1
 row = {"sql_id": num, "sql": statement}
 rows.append(row)
 return rows


def get_base_sqlitem_list(full_sql):
 ''' 把参数 full_sql 转变为 SqlItem列表
 :param full_sql: 完整sql字符串, 每个SQL以分号;间隔, 不包含plsql执行块和plsql对象定义块
 :return: SqlItem对象列表
 '''
 list = []
 for statement in sqlparse.split(full_sql):
 statement = sqlparse.format(statement, strip_comments=True, reindent=True, keyword_case='lower')
 if len(statement) <= 0:
 continue
 item = SqlItem(statement=statement)
 list.append(item)
 return list


def get_full_sqlitem_list(full_sql, db_name):
 ''' 获取Sql对应的SqlItem列表, 包括PLSQL部分
 PLSQL语句块由delimiter $$作为开始间隔符,以$$作为结束间隔符
 :param full_sql: 全部sql内容
 :return: SqlItem 列表
 '''
 list = []

 # 定义开始分隔符,两端用括号,是为了re.split()返回列表包含分隔符
 regex_delimiter = r'(delimiter\s*\$\$)'
 # 注意:必须把package body置于package之前,否则将永远匹配不上package body
 regex_objdefine = r'create\s+or\s+replace\s+(function|procedure|trigger|package\s+body|package|view)\s+("?\w+"?\.)?"?\w+"?[\s+|\(]'
 # 对象命名,两端有双引号
 regex_objname = r'^".+"$'

 sql_list = re.split(pattern=regex_delimiter, string=full_sql, flags=re.I)

 # delimiter_flag => 分隔符标记, 0:不是, 1:是
 # 遇到分隔符标记为1, 则本块SQL要去判断是否有PLSQL内容
 # PLSQL内容存在判定依据, 本块SQL包含'$$'

 delimiter_flag = 0
 for sql in sql_list:
 # 截去首尾空格和多余空字符
 sql = sql.strip()

 # 如果字符串长度为0, 跳过该字符串
 if len(sql) <= 0:
 continue

 # 表示这一行是分隔符, 跳过该字符串
 if re.match(regex_delimiter, sql):
 delimiter_flag = 1
 continue

 if delimiter_flag == 1:
 # 表示SQL块为delimiter $$标记之后的内容

 # 查找是否存在'$$'结束符
 pos = sql.find("$$")
 length = len(sql)
 if pos > -1:
 # 该sqlitem包含结束符$$
 # 处理PLSQL语句块, 这里需要先去判定语句块的类型
 plsql_block = sql[0:pos].strip()
 # 如果plsql_area字符串最后一个字符为/,则把/给去掉
 while True:
 if plsql_block[-1:] == '/':
 plsql_block = plsql_block[:-1].strip()
 else:
 break

 search_result = re.search(regex_objdefine, plsql_block, flags=re.I)

 # 检索关键字, 分为两个情况
 # 情况1:plsql block 为对象定义执行块
 # 情况2:plsql block 为匿名执行块

 if search_result:

 # 检索到关键字, 属于情况1

 str_plsql_match = search_result.group()
 str_plsql_type = search_result.groups()[0]

 idx = str_plsql_match.index(str_plsql_type)
 nm_str = str_plsql_match[idx + len(str_plsql_type):].strip()

 if nm_str[-1:] == '(':
 nm_str = nm_str[:-1]
 nm_list = nm_str.split('.')

 if len(nm_list) > 1:
 # 带有属主的对象名, 形如object_owner.object_name

 # 获取object_owner
 if re.match(regex_objname, nm_list[0]):
 # object_owner两端带有双引号
 object_owner = nm_list[0].strip().strip('"')
 else:
 # object_owner两端不带有双引号
 object_owner = nm_list[0].upper().strip().strip("'")

 # 获取object_name
 if re.match(regex_objname, nm_list[1]):
 # object_name两端带有双引号
 object_name = nm_list[1].strip().strip('"')
 else:
 # object_name两端不带有双引号
 object_name = nm_list[1].upper().strip()
 else:
 # 不带属主
 object_owner = db_name
 if re.match(regex_objname, nm_list[0]):
 # object_name两端带有双引号
 object_name = nm_list[0].strip().strip('"')
 else:
 # object_name两端不带有双引号
 object_name = nm_list[0].upper().strip()

 tmp_object_type = str_plsql_type.upper()
 tmp_stmt_type = 'PLSQL'
 if tmp_object_type == 'VIEW':
 tmp_stmt_type = 'SQL'

 item = SqlItem(statement=plsql_block,
 stmt_type=tmp_stmt_type,
 object_owner=object_owner,
 object_type=tmp_object_type,
 object_name=object_name)
 list.append(item)
 else:
 # 未检索到关键字, 属于情况2, 匿名可执行块 it's ANONYMOUS
 item = SqlItem(statement=plsql_block.strip(),
 stmt_type='PLSQL',
 object_owner=db_name,
 object_type='ANONYMOUS',
 object_name='ANONYMOUS')
 list.append(item)

 if length > pos + 2:
 # 处理$$之后的那些语句, 默认为单条可执行SQL的集合
 sql_area = sql[pos + 2:].strip()
 if len(sql_area) > 0:
 tmp_list = get_base_sqlitem_list(sql_area)
 list.extend(tmp_list)

 else:
 # 没有匹配到$$标记, 默认为单条可执行SQL集合
 tmp_list = get_base_sqlitem_list(sql)
 list.extend(tmp_list)

 # 处理完本次delimiter标记的内容,把delimiter_flag重置
 delimiter_flag = 0
 else:
 # 表示当前为以;结尾的正常sql
 tmp_list = get_base_sqlitem_list(sql)
 list.extend(tmp_list)
 return list


def get_exec_sqlitem_list(reviewResult, db_name):
 """ 根据审核结果生成新的SQL列表
 :param reviewResult: SQL审核结果列表
 :param db_name:
 :return:
 """
 list = []
 list.append(SqlItem(statement=f"ALTER SESSION SET CURRENT_SCHEMA = {db_name}"))

 for item in reviewResult:
 list.append(SqlItem(statement=item['sql'],
 stmt_type=item['stmt_type'],
 object_owner=item['object_owner'],
 object_type=item['object_type'],
 object_name=item['object_name']))
 return list