# -*- coding: UTF-8 -*-
""" 
@author: hhyo
@license: Apache Licence
@file: instance_database.py
@time: 2019年09月19日
"""
import MySQLdb

import simplejson as json
from django.contrib.auth.decorators import permission_required
from django.http import JsonResponse, HttpResponse
from django_redis import get_redis_connection

from common.utils.extend_json_encoder import ExtendJSONEncoder
from sql.engines import get_engine
from sql.models import Instance, InstanceDatabase, Users
from sql.utils.resource_group import user_instances

__author__ = 'hhyo'


@permission_required('sql.menu_database', raise_exception=True)
def databases(request):
 """获取实例数据库列表"""
 instance_id = request.POST.get('instance_id')
 saved = True if request.POST.get('saved') == 'true' else False # 平台是否保存

 if not instance_id:
 return JsonResponse({'status': 0, 'msg': '', 'data': []})

 try:
 instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
 except Instance.DoesNotExist:
 return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})

 # 获取已录入数据库
 cnf_dbs = dict()
 for db in InstanceDatabase.objects.filter(
 instance=instance).values('id', 'db_name', 'owner', 'owner_display', 'remark'):
 db['saved'] = True
 cnf_dbs[f"{db['db_name']}"] = db

 # 获取所有数据库
 sql_get_db = """SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'test', 'sys');"""
 query_engine = get_engine(instance=instance)
 query_result = query_engine.query('information_schema', sql_get_db, close_conn=False)
 if not query_result.error:
 dbs = query_result.rows
 # 获取数据库关联用户信息
 rows = []
 for db in dbs:
 db_name = db[0]
 sql_get_bind_users = f"""select group_concat(distinct(GRANTEE)),TABLE_SCHEMA
from information_schema.SCHEMA_PRIVILEGES
where TABLE_SCHEMA='{db_name}'
group by TABLE_SCHEMA;"""
 bind_users = query_engine.query('information_schema', sql_get_bind_users, close_conn=False).rows
 row = {
 'db_name': db_name,
 'charset': db[1],
 'collation': db[2],
 'grantees': bind_users[0][0].split(',') if bind_users else [],
 'saved': False
 }
 # 合并数据
 if db_name in cnf_dbs.keys():
 row = dict(row, **cnf_dbs[db_name])
 rows.append(row)
 # 过滤参数
 if saved:
 rows = [row for row in rows if row['saved']]

 result = {'status': 0, 'msg': 'ok', 'rows': rows}
 else:
 result = {'status': 1, 'msg': query_result.error}

 # 关闭连接
 query_engine.close()
 return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
 content_type='application/json')


@permission_required('sql.menu_database', raise_exception=True)
def create(request):
 """创建数据库"""
 instance_id = request.POST.get('instance_id', 0)
 db_name = request.POST.get('db_name')
 owner = request.POST.get('owner', '')
 remark = request.POST.get('remark', '')

 if not all([db_name]):
 return JsonResponse({'status': 1, 'msg': '参数不完整,请确认后提交', 'data': []})

 try:
 instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
 except Instance.DoesNotExist:
 return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})

 try:
 owner_display = Users.objects.get(username=owner).display
 except Users.DoesNotExist:
 return JsonResponse({'status': 1, 'msg': '负责人不存在', 'data': []})

 # escape
 db_name = MySQLdb.escape_string(db_name).decode('utf-8')

 engine = get_engine(instance=instance)
 exec_result = engine.execute(db_name='information_schema', sql=f"create database {db_name};")
 if exec_result.error:
 return JsonResponse({'status': 1, 'msg': exec_result.error})
 # 保存到数据库
 else:
 InstanceDatabase.objects.create(
 instance=instance, db_name=db_name, owner=owner, owner_display=owner_display, remark=remark)
 # 清空实例资源缓存
 r = get_redis_connection("default")
 for key in r.scan_iter(match='*insRes*', count=2000):
 r.delete(key)
 return JsonResponse({'status': 0, 'msg': '', 'data': []})


@permission_required('sql.menu_database', raise_exception=True)
def edit(request):
 """编辑/录入数据库"""
 instance_id = request.POST.get('instance_id', 0)
 db_name = request.POST.get('db_name')
 owner = request.POST.get('owner', '')
 remark = request.POST.get('remark', '')

 if not all([db_name]):
 return JsonResponse({'status': 1, 'msg': '参数不完整,请确认后提交', 'data': []})

 try:
 instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
 except Instance.DoesNotExist:
 return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})

 try:
 owner_display = Users.objects.get(username=owner).display
 except Users.DoesNotExist:
 return JsonResponse({'status': 1, 'msg': '负责人不存在', 'data': []})

 # 更新或者录入信息
 InstanceDatabase.objects.update_or_create(
 instance=instance,
 db_name=db_name,
 defaults={"owner": owner, "owner_display": owner_display, "remark": remark})
 return JsonResponse({'status': 0, 'msg': '', 'data': []})