#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2019-10-17 18:15
# @Author  : Lemon
# @File    : db.py
# @Software: PyCharm
import itertools
import math
from functools import lru_cache
from io import BytesIO

import xlwt
from flask import Flask, request
from xlwt import XFStyle, Pattern, Style

from __global import tomd5
from __sdk import Yes


def create_table(prefix):
    tablename = 'cj_%s_md5' % (prefix)
    sql = 'CREATE table %s like cj_base_md5' % tablename
    print(Yes.excuSQL(sql))

    tablename = 'cj_%s_wxid' % (prefix)
    sql = 'CREATE table %s like cj_base_wxid' % tablename
    print(Yes.excuSQL(sql))


def update_status(rid, status):
    sql = f'update cj_table_config set status="{status}" where id={rid}'
    Yes.excuSQLRow(sql)
    return status


def get_current_info(configid):
    sql = f'select status,code from cj_table_config where id={configid}'
    items, _ = Yes.excuSQLRow(sql)
    if items:
        return items[0]
    else:
        return None


def update_code(configid, code):
    sql = f'update cj_table_config set code={code} where id={configid}'
    Yes.excuSQLRow(sql)


def insert_data(rid, data_bytes, prefix, need_detail):
    create_table(prefix)
    tablename = 'cj_%s_md5' % (prefix)
    try:
        data = str(data_bytes, 'utf-8')
        row = data.split('\n')
    except:
        raise update_status(rid, '编码格式错误，请使用UTF-8')

    phone = []
    for p in row:
        p = p.strip()
        if len(p) == 11 and p.startswith('1'):
            phone.append("0086" + p)
        elif len(p) == 15 and p.startswith('0086'):
            phone.append(p)
        else:
            phone.append(p)

    size = 1000
    page = math.ceil(len(phone) / size)
    success = 0
    total = 0
    for i in range(page):
        sub_phone = phone[i * size:(i + 1) * size]
        datas = []
        for sss in sub_phone:
            md5 = tomd5(sss)
            if need_detail == 1:
                detail = '0'
            else:
                detail = '1'
            datas.append({
                'phone': sss,
                'md5': md5,
                'detail': detail,
            })
        if datas:
            try:
                res = Yes.MultiFastCreate(tablename, datas=datas)
                print(res)
                assert res['ret'] == 200
                assert res['data']['err_code'] == 0
                total += res['data']['total']
                success += 1
            except BaseException as e:
                print(e)
            update_status(rid, f'上传进度{int(success / page * 100)}%')

    if success / page == 1:
        update_status(rid, f'上传完成:{Yes.GetTableCount(tablename)}条数据')
    else:
        update_status(rid, f'上传异常:完成度{success / page * 100}%')


def get_phones(size: int = 200):
    config_id, prefix = query_vaild_prefix()
    if not prefix:
        return []
    tablename = 'cj_%s_md5' % prefix
    sql = f'select id,phone,md5,detail from {tablename} where status=0 order by rand() limit {size}'
    items, _ = Yes.excuSQLRow(sql, database='fans')
    if not items:
        print("取空了,更新code状态为完成")
        update_code(config_id, 2)
        return []

    ids = []
    for x in items:
        x['prefix'] = prefix  # 表标识记起来
        ids.append(x['id'])
    sql = f'update {tablename} set status = 1 where id in ({",".join(ids)})'
    Yes.excuSQL(sql, database='fans')
    return items


def get_phone(md5: str, prefix):
    res = Yes.FreeFindOne('cj_%s_md5' % prefix, where=[['md5', '=', md5]], fields=['phone'], database='fans')
    one = res['data']['data']
    if 'phone' in one:
        return one['phone']
    return None


def upload_bind_data(phone, Request, prefix):
    data = {
        'user': phone,
        'wxid': Request.fromusername,
        'nickname': Request.fromnickname,
        'alias': Request.get('alias', ''),
        'sex': Request.sex,
        'province': Request.province,
        'city': Request.city,
    }
    Yes.Create('cj_%s_wxid' % prefix, data=data, database='fans')


def query_list():
    '''获取所有记录'''
    sql = "select * from cj_table_config"
    items, count = Yes.excuSQLRow(sql)
    return {'count': count, 'items': items}


def query_vaild_prefix():
    '''获取等待执行的表'''
    sql = 'select id,table_prefix from cj_table_config where `code`=1 order by id asc limit 1'
    items, _ = Yes.excuSQLRow(sql)
    if items:
        return items[0]['id'], items[0]['table_prefix']
    else:
        return 0, None


@lru_cache(maxsize=256)
def get_table_prefix(id):
    sql = f'select table_prefix from cj_table_config where id={id}'
    items, _ = Yes.excuSQLRow(sql)
    table_prefix = None
    if items:
        table_prefix = items[0]['table_prefix']
    return table_prefix


def query_id_progress(id):
    msg = ''
    table_prefix = get_table_prefix(id)
    if table_prefix:
        progress = f"select `status`,count(*)'cnt' from cj_{table_prefix}_md5 group by `status`"
        items, _ = Yes.excuSQLRow(progress)
        wait = begin = total = 0
        for item in items:
            status = item['status']
            if status == '0':
                wait += int(item['cnt'])
            if status == '1':
                begin += int(item['cnt'])
            total += int(item['cnt'])

        if total > 0:
            msg += f'进度: {"{:.2f}".format(begin / total * 100)}%, '

        count = Yes.GetTableCount(f"cj_{table_prefix}_wxid")
        msg += f"已生成数量：{count}"
    else:
        msg = '找不到对应数据表'
    return msg


def get_table_data(id):
    table_prefix = get_table_prefix(id)
    sql = f'select * from cj_{table_prefix}_wxid'
    items, _ = Yes.excuSQLRow(sql)
    style = XFStyle()
    pattern = Pattern()
    pattern.pattern = Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = Style.colour_map['yellow']  # 设置单元格背景色为黄色
    style.pattern = pattern

    if items:
        ws = xlwt.Workbook(encoding='utf-8')
        w = ws.add_sheet('sheet1')
        w.write(0, 0, '标识', style=style)
        w.write(0, 1, '昵称', style=style)
        w.write(0, 2, '联系方式', style=style)
        w.write(0, 3, '微信号', style=style)
        w.write(0, 4, '性别', style=style)
        w.write(0, 5, '省份', style=style)
        w.write(0, 6, '城市', style=style)
        excel_row = 1
        for obj in items:
            w.write(excel_row, 0, obj['wxid'])
            w.write(excel_row, 1, obj['nickname'])
            w.write(excel_row, 2, obj['user'])
            w.write(excel_row, 3, obj['alias'])
            w.write(excel_row, 4, obj['sex'])
            w.write(excel_row, 5, obj['province'])
            w.write(excel_row, 6, obj['city'])
            excel_row += 1
        output = BytesIO()
        ws.save(output)
        output.seek(0)
        return output

    return None


def delete_from_id(id):
    # sql=f'update cj_table_config set code=0 where id={id}'
    # Yes.excuSQL(sql)
    try:
        sql = f'delete from cj_table_config where id={id}'
        Yes.excuSQL(sql)
    except:
        print("删除行异常")
    finally:
        table_prefix = get_table_prefix(id)
        sql = f'drop table cj_{table_prefix}_wxid'
        Yes.excuSQL(sql)
        sql = f'drop table cj_{table_prefix}_md5'
        Yes.excuSQL(sql)
    return 'ok'