# coding: utf-8
from app.base.logger import log
from app.base.db import get_db, SQL
from app.base.utils import tp_timestamp_utc_now
from app.const import TPE_EXISTS, TPE_DATABASE, TPE_OK, TP_GROUP_HOST, TPE_NOT_EXISTS, TPE_FAILED


def get_host_list(sql_limit, os_type, ip, search, status):
    # 仿 host.get_hosts get_hosts
    s = SQL(get_db())
    s.select_from('remote_host',
                  ['id', 'assets_num', 'os_type', 'ip', 'status', 'username', 'password', 'name', 'remark', ],
                  alt_name='h')

    str_where = ''
    _where = list()

    if os_type:
        _where.append('h.os_type = {}'.format(os_type))

    if status:
        _where.append('h.status = {}'.format(status))
    if ip:
        _where.append('(h.ip LIKE "%{ip}%")'.format(ip=ip))
    if search:
        _where.append(
            '(h.name LIKE "%{search}%" OR h.ip LIKE "%{search}%" OR h.desc LIKE "%{search}%" OR h.cid LIKE "%{search}%")'.format(
                search=search))

    if len(_where) > 0:
        str_where = '( {} )'.format(' AND '.join(_where))

    s.where(str_where)

    if len(sql_limit) > 0:
        s.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = s.query()
    return err, s.total_count, s.page_index, s.recorder


def get_bind_info(host_id):
    s = SQL(get_db())
    s.select_from('remote_account_host_bind',
                  ['id', 'mch_no', 'comp_id', 'host_id', 'host_assigned', 'account_source', 'account', 'password',
                   'login_status', 'mch_name', 'create_tiem'], alt_name='a')

    # 判断
    if host_id:
        s.where('a.host_id={}'.format(host_id))

    err = s.query()
    if err != TPE_OK:
        return err, None

    return s.recorder


def get_account_info(host_id, mch_no):
    s = SQL(get_db())
    s.select_from('remote_account_host_bind',
                  ['id', 'mch_no', 'comp_id', 'host_id', 'host_assigned', 'account_source', 'account', 'password',
                   'login_status', 'mch_name', 'create_tiem'], alt_name='a')

    # 判断
    if mch_no:
        s.where('a.mch_no={}'.format(mch_no))
    elif host_id:
        s.where('a.host_id={}'.format(host_id))

    err = s.query()
    if err != TPE_OK:
        return err, None

    if s.recorder:
        host_id = host_id or s.recorder[0].host_id

    # 未发现设备
    if not host_id:
        return TPE_DATABASE, None

    sh = SQL(get_db())
    sh.select_from('remote_host',
                   ['id', 'assets_num', 'os_type', 'ip', 'status', 'username', 'password', 'name', 'remark', ],
                   alt_name='h')
    sh.where('h.id={}'.format(host_id))
    err = sh.query()
    if err != TPE_OK:
        return err, None

    # 使用id查询可能查询多个
    if len(sh.recorder) != 1:
        return TPE_DATABASE, None

    result = sh.recorder[0]
    result['bind'] = s.recorder

    return TPE_OK, result


def free_host():
    db = get_db()
    sql = """select a.id as id
  from tp_remote_host a LEFT join tp_remote_account_host_bind b on a.id = b.host_id
  	where b.id is null"""

    db_ret = db.query(sql)
    if db_ret is None and len(db_ret) == 0:
        return TPE_NOT_EXISTS, 0

    return TPE_OK, db_ret[0][0]


def add_remote_host(handler, args):
    db = get_db()
    _time_now = tp_timestamp_utc_now()
    operator = handler.get_current_user()

    # 1. 判断是否已经存在了
    sql = 'SELECT id FROM tp_remote_host WHERE ip="{}"'.format(args['ip'])
    db_ret = db.query(sql)
    if db_ret is not None and len(db_ret) > 0:
        return TPE_EXISTS, 0

    sql = 'INSERT INTO `tp_remote_host` (id,app_id, assets_num, os_type, ip, status, username, password, name, remark, create_time, create_by, update_time, update_by) VALUES ' \
          '({id},{app_id}, "{assets_num}", {os_type}, "{ip}", {status}, "{username}", "{password}", "{name}", "{remark}", "{create_time}", "{create_by}", "{update_time}", "{update_by}");' \
          ''.format(id=args.get("host_id", 0), app_id=args.get("app_id", 0), assets_num=args.get("assets_num", ""),
                    os_type=args.get("os_type", 0),
                    ip=args.get("ip", ""), status=args.get("status", 0), username=args.get("username", ""),
                    password=args.get("password", ""),
                    name=args.get("name", ""), remark=args.get("desc", ""), create_time=_time_now,
                    create_by=operator['id'],
                    update_time=_time_now, update_by=operator['id'], )

    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE, 0

    _id = db.last_insert_id()

    # acc_name = '{}@{}'.format(args['username'], args['host_ip'])
    # if len(args['router_ip']) > 0:
    #     acc_name += '（由{}:{}路由）'.format(args['router_ip'], args['router_port'])
    # syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "创建账号：{}".format(acc_name))

    # 更新主机相关账号数量
    return TPE_OK, _id


def update_host(handler, args):
    db = get_db()

    _time_now = tp_timestamp_utc_now()
    operator = handler.get_current_user()

    # 1. 判断此账号是否已经存在
    sql = 'SELECT `username`,`password`,`status`,`ip`,`assets_num`,`os_type` FROM tp_remote_host WHERE `id`={id};'.format(
        id=args.get("id", ""))
    db_ret = db.query(sql)
    if db_ret is None or len(db_ret) == 0:
        return TPE_NOT_EXISTS

    sql = 'UPDATE `tp_remote_host` SET ' \
          '`assets_num`="{assets_num}", `os_type`={os_type}, `ip`="{ip}", ' \
          '`status`={status}, `username`="{username}", `password`="{password}", `name`="{name}", ' \
          '`remark`="{remark}", `update_time`="{update_time}", `update_by`="{update_by}" WHERE `id`={id};' \
          ''.format(assets_num=args['assets_num'], os_type=args['os_type'], ip=args['ip'],
                    status=args['status'], username=args['username'], password=args['password'], name=args['name'],
                    remark=args['remark'], update_time=_time_now, update_by=operator['id'], id=args.get("id", ""))

    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    # operator = handler.get_current_user()
    # syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "更新用户信息：{}".format(args['username']))

    return TPE_OK


def add_account_host_bind(handler, args):
    db = get_db()
    _time_now = tp_timestamp_utc_now()
    operator = handler.get_current_user()

    # 1. 判断账户是否已经存在了
    sql = 'SELECT id FROM tp_remote_account_host_bind WHERE account="{}" '.format(args['account'])
    db_ret = db.query(sql)
    if db_ret is not None and len(db_ret) > 0:
        return TPE_EXISTS, 0

    # 2. 判断host_id 是否存在
    sql = 'SELECT id FROM tp_remote_host WHERE id="{}"'.format(args['host_id'])
    db_ret = db.query(sql)
    if not db_ret:
        return TPE_NOT_EXISTS, 0

    # 3. 是否已经存在不同资质公司
    sql = 'SELECT comp_id FROM tp_remote_account_host_bind WHERE host_id={}'.format(args['host_id'])
    db_ret = db.query(sql)
    comp_id = args.get('comp_id')
    if db_ret and (comp_id,) not in db_ret:
        return TPE_FAILED, 0

    sql = 'INSERT INTO `tp_remote_account_host_bind` (biz_id,mch_no, comp_id, host_id, host_assigned, account_source, account, password, login_status, mch_name, create_tiem, create_by, update_time, update_by) VALUES ' \
          '({biz_id},"{mch_no}", {comp_id}, {host_id}, {host_assigned}, {account_source}, "{account}", "{password}", {login_status},"{mch_name}", "{create_time}", "{create_by}", "{update_time}", "{update_by}");' \
          ''.format(mch_no=args.get("mch_no", ""), comp_id=args.get("comp_id", 0), host_id=args.get("host_id", 0),
                    host_assigned=args.get("host_assigned", 0), account_source=args.get("account_source", 1),
                    account=args.get("account", ""), password=args.get("password", "") or "",
                    login_status=args.get("login_status", 0), mch_name=args.get("mch_name", ""),
                    biz_id=args.get("biz_id") or 0,
                    create_time=_time_now, create_by=operator['id'], update_time=_time_now, update_by=operator['id'], )

    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE, 0

    _id = db.last_insert_id()

    # acc_name = '{}@{}'.format(args['username'], args['host_ip'])
    # if len(args['router_ip']) > 0:
    #     acc_name += '（由{}:{}路由）'.format(args['router_ip'], args['router_port'])
    # syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "创建账号：{}".format(acc_name))

    # 更新主机相关账号数量
    return TPE_OK, _id


def update_account_host_bind(handler, args):
    db = get_db()
    _time_now = tp_timestamp_utc_now()
    operator = handler.get_current_user()

    # 1. 判断账户是否已经存在了
    sql = 'SELECT comp_id FROM tp_remote_account_host_bind WHERE host_id="{}"'.format(args['host_id'])
    db_ret = db.query(sql)

    if not db_ret:
        return TPE_NOT_EXISTS

    # 不允许修改公司资质
    if db_ret[0][0] != args['comp_id']:
        return TPE_FAILED

    # 2. 判断host_id 是否存在
    sql = 'SELECT id FROM tp_remote_account_host_bind WHERE host_id="{}" and mch_no = "{}"'.format(args['host_id'],
                                                                                                   args['mch_no'])
    db_ret = db.query(sql)
    if not db_ret:
        return TPE_NOT_EXISTS

    id = db_ret[0][0]
    sql = 'UPDATE `tp_remote_account_host_bind` SET ' \
          '`mch_no`="{mch_no}", `comp_id`={comp_id}, `host_id`="{host_id}", ' \
          '`host_assigned`={host_assigned}, `account_source`="{account_source}", `account`="{account}", `password`="{password}", ' \
          '`login_status`="{login_status}", `mch_name`="{mch_name}", `update_time`="{update_time}", `update_by`="{update_by}" WHERE `id`={id};' \
        .format(mch_no=args.get("mch_no", ""), comp_id=args.get("comp_id", 0), host_id=args.get("host_id", 0),
                host_assigned=args.get("host_assigned", 0), account_source=args.get("account_source", 1),
                account=args.get("account", ""), password=args.get("password", ""),
                login_status=args.get("login_status", 0), mch_name=args.get("mch_name", ""),
                update_time=_time_now, update_by=operator['id'], id=id)

    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    # operator = handler.get_current_user()
    # syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "更新用户信息：{}".format(args['username']))

    return TPE_OK


def get_session_info(host_id, mch_no, ip):
    db = get_db()

    # 获取用户名
    if host_id:
        sql = 'SELECT username FROM tp_remote_host WHERE id="{}"'.format(host_id)
    elif ip:
        sql = """select a.username,a.id
    	from tp_remote_host a where ip = "{}" """.format(ip)
    elif mch_no:
        sql = """select a.username,a.id
	from tp_remote_host a left join tp_remote_account_host_bind b on a.id = b.host_id
		where b.mch_no = "" """.format(mch_no)
    else:
        return TPE_FAILED, 0

    db_ret = db.query(sql)

    username = db_ret[0][0]
    host_id = host_id or db_ret[0][1]

    sql = 'SELECT id FROM tp_acc WHERE host_id="{}" and username = "{}"'.format(host_id, username)
    db_ret = db.query(sql)
    acc_id = db_ret[0][0]
    return TPE_OK, acc_id, host_id


def get_host_ip(host_id):
    db = get_db()
    sql = 'SELECT ip FROM tp_remote_host WHERE id="{}"'.format(host_id)
    db_ret = db.query(sql)
    ip = db_ret[0][0]
    return ip


def query(table: str, fields: list, where: dict):
    s = SQL(get_db())
    s.select_from(table, fields, alt_name='a')

    if where:
        _where = ' and '.join(
            ['`{key}`={value}'.format(key=k, value='"{}"'.format(v) if isinstance(v, str) else v) for k, v in
             where.items()])
        s.where(_where)

    err = s.query()
    if err != TPE_OK:
        return {}

    return s.recorder


def query_one(table: str, fields: list, where: dict):
    s = SQL(get_db())
    s.select_from(table, fields, alt_name='a')

    if where:
        _where = ' and '.join(
            ['`{key}`={value}'.format(key=k, value='"{}"'.format(v) if isinstance(v, str) else v) for k, v in
             where.items()])
        s.where(_where)

    err = s.query()
    if err != TPE_OK:
        return {}
    if s.recorder.__len__() > 0:
        return s.recorder[0]
    else:
        return {}


def update(table: str, fields: dict, where: dict):
    db = get_db()

    _set = ','.join(['`{key}`={value}'.format(key=k, value='"{}"'.format(v) if isinstance(v, str) else v) for k, v in
                     fields.items()])
    _where = ' and '.join(
        ['`{key}`={value}'.format(key=k, value='"{}"'.format(v) if isinstance(v, str) else v) for k, v in
         where.items()])

    sql = 'UPDATE `{table}` SET {set} WHERE {where};'.format(table=table, set=_set, where=_where)
    print(sql)

    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    return TPE_OK
