import pymysql
import datetime
import json
import redis


def get_esmm_users():
    db = pymysql.connect(host='172.16.40.158', port=4000, user='root',
                         passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
    cursor = db.cursor()
    stat_date = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y-%m-%d")
    print(stat_date)
    sql = "select distinct device_id,city_id from data_feed_exposure_precise " \
          "where stat_date = '{}'".format(stat_date)
    cursor.execute(sql)
    result = list(cursor.fetchall())
    db.close()
    return result


def get_user_profile(device_id = "9C5E7C73-380C-4623-8F48-A64C8034E315"):
    r = redis.Redis(host="172.16.40.135", port=5379, password="", db=2)
    key = "user:portrait_tags:cl_id:" + str(device_id)
    tmp = json.loads(r.get(key).decode('utf-8'))
    tag_score = {}
    for i in tmp:
        if i["type"] == "tag":
            tag_score[i["content"]] = i["score"]
        elif i["content"] in name_tag.keys():
            tag_score[name_tag[i["content"]]] = i["score"]
    tag_sort = sorted(tag_score.items(), key=lambda x: x[1], reverse=True)
    tags = []
    if len(tag_sort) > 5:
        for i in range(5):
            tags.append(i[0])
    else:
        for i in tag_sort:
            tags.append(i[0])

    return tags


def get_searchworlds_to_tagid():
    try:
        sql = 'select id, name from api_tag where is_online = 1 and tag_type < 4'
        db = pymysql.connect(host='172.16.30.141', port=3306, user='work',
                             passwd='BJQaT9VzDcuPBqkd', db='zhengxing')
        cursor = db.cursor()
        cursor.execute(sql)
        tag_id = cursor.fetchall()
        db.close()
        searchworlds_to_tagid = {}

        for i in tag_id:
            searchworlds_to_tagid[i[1]] = i[0]

        return searchworlds_to_tagid
    except Exception as e:
        print(e)


def transform_to_tag(user_profile):
    pass


def get_queues(device_id,city_id):
    db = pymysql.connect(host='172.16.40.158', port=4000, user='root',
                         passwd='3SYz54LS9#^9sBvC', db='jerry_test')
    cursor = db.cursor()
    # sql = "select device_id,city_id from esmm_device_diary_queue " \
    #       "where device_id = '{}' and city_id = '{}'".format(device_id,city_id)
    sql = "select device_id,city_id from esmm_device_diary_queue limit 6"
    cursor.execute(sql)
    # native_queue, nearby_queue, nation_queue, megacity_queue
    # if 判断是不是空
    # result = cursor.fetchone()
    # print(result)
    print("a")
    print(cursor.fetchall())
    return result


def tag_boost(cid_list, tag_score):
    pass


def to_data_base(df,table_name = "tag_boost_device_diary_queue"):
    pass


def make_sample(uid,city_id,native_queue,nearby_queue,megacity_queue,nation_queue):
    pass


if __name__ == "__main__":
    # users_list = get_esmm_users()
    # total_samples = list()
    # name_tag = get_searchworlds_to_tagid()
    # for i in users_list:
    #     tag_list = get_user_profile(i[0])
    #     native,nearby,megacity,nation = get_queues(i[0],i[1])
    #
    #     native_sort_list = tag_boost(native, tag_score)
    #     nearby_sort_list = tag_boost(nearby, tag_score)
    #
    #     sample = make_sample(uid,city_id,native_queue,nearby_queue,megacity_queue,nation_queue)
    #     total_samples.append(sample)
    #
    # total_samples.todf
    # to_data_base(df)
    # to kv
    get_queues("868973042175678","beijing")














