# -*- coding: UTF-8 -*-
import datetime
import logging
import traceback

import simplejson as json
from django.contrib.auth.decorators import permission_required
from django.core.exceptions import PermissionDenied
from django.db import transaction
from django.db.models import Q
from django.http import HttpResponse, HttpResponseRedirect, JsonResponse
from django.shortcuts import render, get_object_or_404
from django.urls import reverse
from django.utils import timezone

from common.config import SysConfig
from common.utils.const import Const, WorkflowDict
from common.utils.extend_json_encoder import ExtendJSONEncoder
from sql.engines.models import ReviewResult, ReviewSet
from sql.notify import notify_for_audit
from sql.models import ResourceGroup
from sql.utils.resource_group import user_groups, user_instances
from sql.utils.tasks import add_sql_schedule, del_schedule
from sql.utils.sql_review import can_timingtask, can_cancel, can_execute, on_correct_time_period, can_view, can_rollback
from sql.utils.workflow_audit import Audit
from .models import SqlWorkflow, SqlWorkflowContent, Instance
from django_q.tasks import async_task

from sql.engines import get_engine

logger = logging.getLogger('default')


@permission_required('sql.menu_sqlworkflow', raise_exception=True)
def sql_workflow_list(request):
 """
 获取审核列表
 :param request:
 :return:
 """
 nav_status = request.POST.get('navStatus')
 instance_id = request.POST.get('instance_id')
 resource_group_id = request.POST.get('group_id')
 start_date = request.POST.get('start_date')
 end_date = request.POST.get('end_date')
 limit = int(request.POST.get('limit'))
 offset = int(request.POST.get('offset'))
 limit = offset + limit
 search = request.POST.get('search')
 user = request.user

 # 组合筛选项
 filter_dict = dict()
 # 工单状态
 if nav_status:
 filter_dict['status'] = nav_status
 # 实例
 if instance_id:
 filter_dict['instance_id'] = instance_id
 # 资源组
 if resource_group_id:
 filter_dict['group_id'] = resource_group_id
 # 时间
 if start_date and end_date:
 end_date = datetime.datetime.strptime(end_date, '%Y-%m-%d') + datetime.timedelta(days=1)
 filter_dict['create_time__range'] = (start_date, end_date)
 # 管理员,可查看所有工单
 if user.is_superuser:
 pass
 # 非管理员,拥有审核权限、资源组粒度执行权限的,可以查看组内所有工单
 elif user.has_perm('sql.sql_review') or user.has_perm('sql.sql_execute_for_resource_group'):
 # 先获取用户所在资源组列表
 group_list = user_groups(user)
 group_ids = [group.group_id for group in group_list]
 filter_dict['group_id__in'] = group_ids
 # 其他人只能查看自己提交的工单
 else:
 filter_dict['engineer'] = user.username

 # 过滤组合筛选项
 workflow = SqlWorkflow.objects.filter(**filter_dict)

 # 过滤搜索项,模糊检索项包括提交人名称、工单名
 if search:
 workflow = workflow.filter(Q(engineer_display__icontains=search) | Q(workflow_name__icontains=search))

 count = workflow.count()
 workflow_list = workflow.order_by('-create_time')[offset:limit].values(
 "id", "workflow_name", "engineer_display",
 "status", "is_backup", "create_time",
 "instance__instance_name", "db_name",
 "group_name", "syntax_type")

 # QuerySet 序列化
 rows = [row for row in workflow_list]
 result = {"total": count, "rows": rows}
 # 返回查询结果
 return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
 content_type='application/json')


@permission_required('sql.sql_submit', raise_exception=True)
def check(request):
 """SQL检测按钮, 此处没有产生工单"""
 sql_content = request.POST.get('sql_content')
 instance_name = request.POST.get('instance_name')
 instance = Instance.objects.get(instance_name=instance_name)
 db_name = request.POST.get('db_name')

 result = {'status': 0, 'msg': 'ok', 'data': {}}
 # 服务器端参数验证
 if sql_content is None or instance_name is None or db_name is None:
 result['status'] = 1
 result['msg'] = '页面提交参数可能为空'
 return HttpResponse(json.dumps(result), content_type='application/json')

 # 交给engine进行检测
 try:
 check_engine = get_engine(instance=instance)
 check_result = check_engine.execute_check(db_name=db_name, sql=sql_content.strip())
 except Exception as e:
 result['status'] = 1
 result['msg'] = str(e)
 return HttpResponse(json.dumps(result), content_type='application/json')

 # 处理检测结果
 result['data']['rows'] = check_result.to_dict()
 result['data']['CheckWarningCount'] = check_result.warning_count
 result['data']['CheckErrorCount'] = check_result.error_count
 return HttpResponse(json.dumps(result), content_type='application/json')


@permission_required('sql.sql_submit', raise_exception=True)
def submit(request):
 """正式提交SQL, 此处生成工单"""
 sql_content = request.POST.get('sql_content').strip()
 workflow_title = request.POST.get('workflow_name')
 demand_url = request.POST.get('demand_url', '')
 # 检查用户是否有权限涉及到资源组等, 比较复杂, 可以把检查权限改成一个独立的方法
 group_name = request.POST.get('group_name')
 group_id = ResourceGroup.objects.get(group_name=group_name).group_id
 instance_name = request.POST.get('instance_name')
 instance = Instance.objects.get(instance_name=instance_name)
 db_name = request.POST.get('db_name')
 is_backup = True if request.POST.get('is_backup') == 'True' else False
 cc_users = request.POST.getlist('cc_users')
 run_date_start = request.POST.get('run_date_start')
 run_date_end = request.POST.get('run_date_end')

 # 服务器端参数验证
 if None in [sql_content, db_name, instance_name, db_name, is_backup, demand_url]:
 context = {'errMsg': '页面提交参数可能为空'}
 return render(request, 'error.html', context)

 # 验证组权限(用户是否在该组、该组是否有指定实例)
 try:
 user_instances(request.user, tag_codes=['can_write']).get(instance_name=instance_name)
 except instance.DoesNotExist:
 context = {'errMsg': '你所在组未关联该实例!'}
 return render(request, 'error.html', context)

 # 再次交给engine进行检测,防止绕过
 try:
 check_engine = get_engine(instance=instance)
 check_result = check_engine.execute_check(db_name=db_name, sql=sql_content.strip())
 except Exception as e:
 context = {'errMsg': str(e)}
 return render(request, 'error.html', context)

 # 未开启备份选项,并且engine支持备份,强制设置备份
 sys_config = SysConfig()
 if not sys_config.get('enable_backup_switch') and check_engine.auto_backup:
 is_backup = True

 # 按照系统配置确定是自动驳回还是放行
 auto_review_wrong = sys_config.get('auto_review_wrong', '') # 1表示出现警告就驳回,2和空表示出现错误才驳回
 workflow_status = 'workflow_manreviewing'
 if check_result.warning_count > 0 and auto_review_wrong == '1':
 workflow_status = 'workflow_autoreviewwrong'
 elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'):
 workflow_status = 'workflow_autoreviewwrong'

 # 调用工作流生成工单
 # 使用事务保持数据一致性
 try:
 with transaction.atomic():
 # 存进数据库里
 sql_workflow = SqlWorkflow.objects.create(
 workflow_name=workflow_title,
 demand_url=demand_url,
 group_id=group_id,
 group_name=group_name,
 engineer=request.user.username,
 engineer_display=request.user.display,
 audit_auth_groups=Audit.settings(group_id, WorkflowDict.workflow_type['sqlreview']),
 status=workflow_status,
 is_backup=is_backup,
 instance=instance,
 db_name=db_name,
 is_manual=0,
 syntax_type=check_result.syntax_type,
 create_time=timezone.now(),
 run_date_start=run_date_start or None,
 run_date_end=run_date_end or None
 )
 SqlWorkflowContent.objects.create(workflow=sql_workflow,
 sql_content=sql_content,
 review_content=check_result.json(),
 execute_result=''
 )
 workflow_id = sql_workflow.id
 # 自动审核通过了,才调用工作流
 if workflow_status == 'workflow_manreviewing':
 # 调用工作流插入审核信息, 查询权限申请workflow_type=2
 Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id)
 except Exception as msg:
 logger.error(f"提交工单报错,错误信息:{traceback.format_exc()}")
 context = {'errMsg': msg}
 logger.error(traceback.format_exc())
 return render(request, 'error.html', context)
 else:
 # 自动审核通过才进行消息通知
 if workflow_status == 'workflow_manreviewing':
 # 获取审核信息
 audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
 workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
 async_task(notify_for_audit, audit_id=audit_id, cc_users=cc_users, timeout=60,
 task_name=f'sqlreview-submit-{workflow_id}')

 return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))


def detail_content(request):
 """获取工单内容"""
 workflow_id = request.GET.get('workflow_id')
 workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
 if not can_view(request.user, workflow_id):
 raise PermissionDenied
 if workflow_detail.status in ['workflow_finish', 'workflow_exception']:
 rows = workflow_detail.sqlworkflowcontent.execute_result
 else:
 rows = workflow_detail.sqlworkflowcontent.review_content

 review_result = ReviewSet()
 if rows:
 try:
 # 检验rows能不能正常解析
 loaded_rows = json.loads(rows)
 # 兼容旧数据'[[]]'格式,转换为新格式[{}]
 if isinstance(loaded_rows[-1], list):
 for r in loaded_rows:
 review_result.rows += [ReviewResult(inception_result=r)]
 rows = review_result.json()
 except IndexError:
 review_result.rows += [ReviewResult(
 id=1,
 sql=workflow_detail.sqlworkflowcontent.sql_content,
 errormessage="Json decode failed."
 "执行结果Json解析失败, 请联系管理员"
 )]
 rows = review_result.json()
 except json.decoder.JSONDecodeError:
 review_result.rows += [ReviewResult(
 id=1,
 sql=workflow_detail.sqlworkflowcontent.sql_content,
 # 迫于无法单元测试这里加上英文报错信息
 errormessage="Json decode failed."
 "执行结果Json解析失败, 请联系管理员"
 )]
 rows = review_result.json()
 else:
 rows = workflow_detail.sqlworkflowcontent.review_content

 result = {"rows": json.loads(rows)}
 return HttpResponse(json.dumps(result), content_type='application/json')


def backup_sql(request):
 """获取回滚语句"""
 workflow_id = request.GET.get('workflow_id')
 if not can_rollback(request.user, workflow_id):
 raise PermissionDenied
 workflow = get_object_or_404(SqlWorkflow, pk=workflow_id)

 try:
 query_engine = get_engine(instance=workflow.instance)
 list_backup_sql = query_engine.get_rollback(workflow=workflow)
 except Exception as msg:
 return JsonResponse({'status': 1, 'msg': f'{msg}', 'rows': []})

 result = {'status': 0, 'msg': '', 'rows': list_backup_sql}
 return HttpResponse(json.dumps(result), content_type='application/json')


@permission_required('sql.sql_review', raise_exception=True)
def alter_run_date(request):
 """
 审核人修改可执行时间
 :param request:
 :return:
 """
 workflow_id = int(request.POST.get('workflow_id', 0))
 run_date_start = request.POST.get('run_date_start')
 run_date_end = request.POST.get('run_date_end')
 if workflow_id == 0:
 context = {'errMsg': 'workflow_id参数为空.'}
 return render(request, 'error.html', context)

 user = request.user
 if Audit.can_review(user, workflow_id, 2) is False:
 context = {'errMsg': '你无权操作当前工单!'}
 return render(request, 'error.html', context)

 try:
 # 存进数据库里
 SqlWorkflow(id=workflow_id,
 run_date_start=run_date_start or None,
 run_date_end=run_date_end or None
 ).save(update_fields=['run_date_start', 'run_date_end'])
 except Exception as msg:
 context = {'errMsg': msg}
 return render(request, 'error.html', context)

 return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))


@permission_required('sql.sql_review', raise_exception=True)
def passed(request):
 """
 审核通过,不执行
 :param request:
 :return:
 """
 workflow_id = int(request.POST.get('workflow_id', 0))
 audit_remark = request.POST.get('audit_remark', '')
 if workflow_id == 0:
 context = {'errMsg': 'workflow_id参数为空.'}
 return render(request, 'error.html', context)

 user = request.user
 if Audit.can_review(user, workflow_id, 2) is False:
 context = {'errMsg': '你无权操作当前工单!'}
 return render(request, 'error.html', context)

 # 使用事务保持数据一致性
 try:
 with transaction.atomic():
 # 调用工作流接口审核
 audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
 workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
 audit_result = Audit.audit(audit_id, WorkflowDict.workflow_status['audit_success'],
 user.username, audit_remark)

 # 按照审核结果更新业务表审核状态
 if audit_result['data']['workflow_status'] == WorkflowDict.workflow_status['audit_success']:
 # 将流程状态修改为审核通过
 SqlWorkflow(id=workflow_id, status='workflow_review_pass').save(update_fields=['status'])
 except Exception as msg:
 logger.error(f"审核工单报错,错误信息:{traceback.format_exc()}")
 context = {'errMsg': msg}
 return render(request, 'error.html', context)
 else:
 # 消息通知
 async_task(notify_for_audit, audit_id=audit_id, audit_remark=audit_remark, timeout=60,
 task_name=f'sqlreview-pass-{workflow_id}')

 return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))


def execute(request):
 """
 执行SQL
 :param request:
 :return:
 """
 # 校验多个权限
 if not (request.user.has_perm('sql.sql_execute') or request.user.has_perm('sql.sql_execute_for_resource_group')):
 raise PermissionDenied
 workflow_id = int(request.POST.get('workflow_id', 0))
 if workflow_id == 0:
 context = {'errMsg': 'workflow_id参数为空.'}
 return render(request, 'error.html', context)

 if can_execute(request.user, workflow_id) is False:
 context = {'errMsg': '你无权操作当前工单!'}
 return render(request, 'error.html', context)

 if on_correct_time_period(workflow_id) is False:
 context = {'errMsg': '不在可执行时间范围内,如果需要修改执行时间请重新提交工单!'}
 return render(request, 'error.html', context)
 # 获取审核信息
 audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
 workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
 # 根据执行模式进行对应修改
 mode = request.POST.get('mode')
 # 交由系统执行
 if mode == "auto":
 # 修改工单状态为排队中
 SqlWorkflow(id=workflow_id, status="workflow_queuing").save(update_fields=['status'])
 # 删除定时执行任务
 schedule_name = f"sqlreview-timing-{workflow_id}"
 del_schedule(schedule_name)
 # 加入执行队列
 async_task('sql.utils.execute_sql.execute', workflow_id, request.user,
 hook='sql.utils.execute_sql.execute_callback',
 timeout=-1, task_name=f'sqlreview-execute-{workflow_id}')
 # 增加工单日志
 Audit.add_log(audit_id=audit_id,
 operation_type=5,
 operation_type_desc='执行工单',
 operation_info='工单执行排队中',
 operator=request.user.username,
 operator_display=request.user.display)

 # 线下手工执行
 elif mode == "manual":
 # 将流程状态修改为执行结束
 SqlWorkflow(id=workflow_id, status="workflow_finish", finish_time=datetime.datetime.now()
 ).save(update_fields=['status', 'finish_time'])
 # 增加工单日志
 Audit.add_log(audit_id=audit_id,
 operation_type=6,
 operation_type_desc='手工工单',
 operation_info='确认手工执行结束',
 operator=request.user.username,
 operator_display=request.user.display)
 return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))


def timing_task(request):
 """
 定时执行SQL
 :param request:
 :return:
 """
 # 校验多个权限
 if not (request.user.has_perm('sql.sql_execute') or request.user.has_perm('sql.sql_execute_for_resource_group')):
 raise PermissionDenied
 workflow_id = request.POST.get('workflow_id')
 run_date = request.POST.get('run_date')
 if run_date is None or workflow_id is None:
 context = {'errMsg': '时间不能为空'}
 return render(request, 'error.html', context)
 elif run_date < datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
 context = {'errMsg': '时间不能小于当前时间'}
 return render(request, 'error.html', context)
 workflow_detail = SqlWorkflow.objects.get(id=workflow_id)

 if can_timingtask(request.user, workflow_id) is False:
 context = {'errMsg': '你无权操作当前工单!'}
 return render(request, 'error.html', context)

 run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M")
 schedule_name = f"sqlreview-timing-{workflow_id}"

 if on_correct_time_period(workflow_id, run_date) is False:
 context = {'errMsg': '不在可执行时间范围内,如果需要修改执 行时间请重新提交工单!'}
 return render(request, 'error.html', context)

 # 使用事务保持数据一致性
 try:
 with transaction.atomic():
 # 将流程状态修改为定时执行
 workflow_detail.status = 'workflow_timingtask'
 workflow_detail.save()
 # 调用添加定时任务
 add_sql_schedule(schedule_name, run_date, workflow_id)
 # 增加工单日志
 audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
 workflow_type=WorkflowDict.workflow_type[
 'sqlreview']).audit_id
 Audit.add_log(audit_id=audit_id,
 operation_type=4,
 operation_type_desc='定时执行',
 operation_info="定时执行时间:{}".format(run_date),
 operator=request.user.username,
 operator_display=request.user.display
 )
 except Exception as msg:
 logger.error(f"定时执行工单报错,错误信息:{traceback.format_exc()}")
 context = {'errMsg': msg}
 return render(request, 'error.html', context)
 return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))


def cancel(request):
 """
 终止流程
 :param request:
 :return:
 """
 workflow_id = int(request.POST.get('workflow_id', 0))
 if workflow_id == 0:
 context = {'errMsg': 'workflow_id参数为空.'}
 return render(request, 'error.html', context)
 workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
 audit_remark = request.POST.get('cancel_remark')
 if audit_remark is None:
 context = {'errMsg': '终止原因不能为空'}
 return render(request, 'error.html', context)

 user = request.user
 if can_cancel(request.user, workflow_id) is False:
 context = {'errMsg': '你无权操作当前工单!'}
 return render(request, 'error.html', context)

 # 使用事务保持数据一致性
 try:
 with transaction.atomic():
 # 调用工作流接口取消或者驳回
 audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
 workflow_type=WorkflowDict.workflow_type[
 'sqlreview']).audit_id
 # 仅待审核的需要调用工作流,审核通过的不需要
 if workflow_detail.status != 'workflow_manreviewing':
 # 增加工单日志
 if user.username == workflow_detail.engineer:
 Audit.add_log(audit_id=audit_id,
 operation_type=3,
 operation_type_desc='取消执行',
 operation_info="取消原因:{}".format(audit_remark),
 operator=request.user.username,
 operator_display=request.user.display
 )
 else:
 Audit.add_log(audit_id=audit_id,
 operation_type=2,
 operation_type_desc='审批不通过',
 operation_info="审批备注:{}".format(audit_remark),
 operator=request.user.username,
 operator_display=request.user.display
 )
 else:
 if user.username == workflow_detail.engineer:
 Audit.audit(audit_id,
 WorkflowDict.workflow_status['audit_abort'],
 user.username, audit_remark)
 # 非提交人需要校验审核权限
 elif user.has_perm('sql.sql_review'):
 Audit.audit(audit_id,
 WorkflowDict.workflow_status['audit_reject'],
 user.username, audit_remark)
 else:
 raise PermissionDenied

 # 删除定时执行task
 if workflow_detail.status == 'workflow_timingtask':
 schedule_name = f"sqlreview-timing-{workflow_id}"
 del_schedule(schedule_name)
 # 将流程状态修改为人工终止流程
 workflow_detail.status = 'workflow_abort'
 workflow_detail.save()
 except Exception as msg:
 logger.error(f"取消工单报错,错误信息:{traceback.format_exc()}")
 context = {'errMsg': msg}
 return render(request, 'error.html', context)
 else:
 # 发送取消、驳回通知
 audit_detail = Audit.detail_by_workflow_id(workflow_id=workflow_id,
 workflow_type=WorkflowDict.workflow_type['sqlreview'])
 if audit_detail.current_status in (
 WorkflowDict.workflow_status['audit_abort'], WorkflowDict.workflow_status['audit_reject']):
 async_task(notify_for_audit, audit_id=audit_detail.audit_id, audit_remark=audit_remark, timeout=60,
 task_name=f'sqlreview-cancel-{workflow_id}')
 return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))


def get_workflow_status(request):
 """
 获取某个工单的当前状态
 """
 workflow_id = request.POST['workflow_id']
 if workflow_id == '' or workflow_id is None:
 context = {"status": -1, 'msg': 'workflow_id参数为空.', "data": ""}
 return HttpResponse(json.dumps(context), content_type='application/json')

 workflow_id = int(workflow_id)
 workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
 result = {"status": workflow_detail.status, "msg": "", "data": ""}
 return JsonResponse(result)


def osc_control(request):
 """用于mysql控制osc执行"""
 workflow_id = request.POST.get('workflow_id')
 sqlsha1 = request.POST.get('sqlsha1')
 command = request.POST.get('command')
 workflow = SqlWorkflow.objects.get(id=workflow_id)
 execute_engine = get_engine(workflow.instance)
 try:
 execute_result = execute_engine.osc_control(command=command, sqlsha1=sqlsha1)
 rows = execute_result.to_dict()
 error = execute_result.error
 except Exception as e:
 rows = []
 error = str(e)
 result = {"total": len(rows), "rows": rows, "msg": error}
 return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
 content_type='application/json')