#!/usr/bin/env python
# -*- coding: utf-8 -*-

from data_fixture.mysql_db import DB
from data_fixture import config_data as Data
from datetime import datetime, date, timedelta
import time
from data_fixture.config_data import HOST_BOSS
import requests
import calendar
import uuid


db = DB()

## *********************************************************************************************************************
##                验证码
## *********************************************************************************************************************
def fet_authCode(mobile):
    url = HOST_BOSS + "/ozing/timer/user/fetchAuthCode"
    headers = {'Accept': '*/*'}
    postData = {'mobile': mobile, 'type': 'general'}
    r = requests.post(url, headers=headers, data=postData)
    result = r.json()
    if result['status'] == 100:
        return result['jsessionid']
    else :
        raise FetchException('fetch auth code Error!')


class FetchException(Exception):
    pass

## *********************************************************************************************************************
##                 保卡
## *********************************************************************************************************************
# 保卡数据 -- 新建保卡
def pre_elecCard(device_cus_bind='0', device_sam='0', device_cus_unbind='0'):

    if device_cus_bind != '0':
    # 客机，已绑定保卡
        select_customermachine = "select * from acornuser.ozing_customermachine where deviceNumber = '{}' ".format(device_cus_bind)
        select_machine_cus = "SELECT * FROM  acornuser.ozing_machine where deviceNumber = '{}' ".format(device_cus_bind)
        insert_customermachine_tabel = 'acornuser.ozing_customermachine'
        insert_customermachine_data = {
            'deviceNumber': device_cus_bind,
            'userId': Data.USER_ID,
            'customerName':'测试customer',
            'customerAddress':'内蒙古巴彦淖尔市',
            'customerPhone':'13822222222',
            'buyTime': datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            'buyAddress':'内蒙古巴彦淖尔市',
            'alterSaleCall':'51518888',
            'cmstate': '1',
            'createTime': datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        }
        insert_machine_cus_table = 'acornuser.ozing_machine'
        insert_machine_cus_data = {'productModel': Data.MODEL,
                                   'deviceNumber': device_cus_bind,
                                   'macAddress': Data.MAC_CUS_BIND,
                                   'state': '1'
                                   }
        if db.select(select_customermachine):
            pass
        else:
            db.insert(insert_customermachine_tabel, insert_customermachine_data)

        if db.select(select_machine_cus):
            pass
        else:
            db.insert(insert_machine_cus_table, insert_machine_cus_data)

# 样机
    if device_sam !='0':
        select_samplemachine = "SELECT * FROM acornuser.ozing_samplemachine where deviceNumber = '{}' ".format(device_sam)
        select_machine_sam = "SELECT * FROM  acornuser.ozing_machine where deviceNumber = '{}' ".format(device_sam)
        insert_samplemachine_tabel = 'acornuser.ozing_samplemachine'
        insert_samplemachine_data= {'deviceNumber': device_sam,
                                'userId': Data.USER_ID,
                                'terminalAddress': "内蒙古巴彦淖尔市",
                                'distributor': "新华书店",
                                'saleClerk':"张三",
                                'mobilePhone': "18622222222",
                                'photo':'[{"photoUrl":"static/upload/online_api/samplePhoto/2017-12-13/b6480129-e720-4109-a455-6130fd640f16.jpg"},{"photoUrl":"static/upload/online_api/samplePhoto/2017-12-13/b8317fa4-cfa5-4ea4-91d4-3ca020e06bca.jpg"},{"photoUrl":"static/upload/online_api/samplePhoto/2017-12-13/110dc30b-34f1-4e0b-88e0-a030b8ab4af7.jpg"}]',
                                'smstate': '1',
                                'createTime': datetime.now().strftime('%Y-%m-%d %H:%M:%S') }
        insert_machine_sam_tabel = 'acornuser.ozing_machine'
        insert_machine_sam_data = {'productModel': Data.MODEL,
                                  'deviceNumber': device_sam,
                                  'macAddress': Data.MAC_SAM,
                                  'state': '0'}

        if db.select(select_samplemachine):
            pass
        else:
            db.insert(insert_samplemachine_tabel, insert_samplemachine_data)

        if db.select(select_machine_sam):
            pass
        else:
            db.insert(insert_machine_sam_tabel, insert_machine_sam_data)

# 未绑定 -- 客机
    if device_cus_unbind != '0':
        select_customermachine_unbind = "select * from acornuser.ozing_customermachine where deviceNumber = '{}' " \
                                        .format(device_cus_unbind)
        update_customermachine_unbind_tabel = 'acornuser.ozing_customermachine'
        update_customermachine_unbind_set = {'deviceNumber':str(time.time())}
        update_customermachine_unbind_where = {'deviceNumber': device_cus_unbind}

        select_machine_cus_1 = "SELECT * FROM  acornuser.ozing_machine where deviceNumber = '{}' ".format(device_cus_unbind)
        select_machine_cus_2 = "SELECT * FROM  acornuser.ozing_machine where deviceNumber = '{}' and state = '1' ".format(device_cus_unbind)

        update_machine_cus_unbind_table = 'acornuser.ozing_machine'
        update_machine_cus_unbind_set = {'state': '1'}
        update_machine_cus_unbind_where = {'deviceNumber': device_cus_unbind}

        # 保卡表中有数据
        if db.select(select_customermachine_unbind):
            db.update(update_customermachine_unbind_tabel, update_customermachine_unbind_set, update_customermachine_unbind_where)
        # 机器表中有数据，并且状态是1
        if db.select(select_machine_cus_1):
            if db.select(select_machine_cus_2):
                pass
            else:
                # update state =1
                db.update(update_machine_cus_unbind_table, update_machine_cus_unbind_set, update_machine_cus_unbind_where)
        # 机器表中没数据
        else:
            pass

# #添加客机保卡
def pre_SetUpElecCard():
    select_customermachine_new = "select * from acornuser.ozing_customermachine where deviceNumber = '{}' ".format(Data.DEVICE_NUMBER_NEW)
    update_customermachine_new_tabel = 'acornuser.ozing_customermachine'
    update_customermachine_new_set = {'deviceNumber':str(time.time())}
    update_customermachine_new_where = {'deviceNumber': Data.DEVICE_NUMBER_NEW}

    select_machine_new = "select * from acornuser.ozing_machine where deviceNumber = '{}' ".format(Data.DEVICE_NUMBER_NEW)
    update_machine_new_tabel = 'acornuser.ozing_machine'
    update_machine_new_set = {'deviceNumber': str(time.time())}
    update_machine_new_where = {'deviceNumber': Data.DEVICE_NUMBER_NEW}

    if db.select(select_customermachine_new):
        db.update(update_customermachine_new_tabel, update_customermachine_new_set, update_customermachine_new_where)
    if db.select(select_machine_new):
        db.update(update_machine_new_tabel, update_machine_new_set, update_machine_new_where)


## *********************************************************************************************************************
##                 子账户
## *********************************************************************************************************************

def pre_subAccount(parent_id, sub_account_id, status, deviceNumber=Data.DEVICE_NUMBER_CUS_BIND):  #status: child status

    # 子账户数据
    TABEL_CHILD_USER = 'acornuser.child_user'
    if status == 1 : # make sure only 1 sub account's status marked as '1'
        update_all_0_sub = "update acornuser.child_user set status = 0 where parent_id = '{}' and deviceNumber = '{}'" \
                           .format(parent_id, deviceNumber)
        db.update_(update_all_0_sub)

    select_sub_acc = "select * from acornuser.child_user where parent_id = '{}' and subAccountId = '{}' ".format(parent_id, sub_account_id)
    select_sub_acc_status = "select * from acornuser.child_user where parent_id = '{}' and subAccountId = '{}' " \
                            "and status = {} ".format(parent_id, sub_account_id, status)
    update_set = {'status': status}
    update_where = {'subAccountId': sub_account_id }
    insert_data = {'parent_id':parent_id,
                   'image': Data.SUB_ACC_IMAGE,
                   'name' : '测试sub',
                   'grade_id':'6',
                   'school_id': Data.SUB_ACC_SCHOOL_ID_2,
                   'region_id': Data.SUB_ACC_REGION_ID_2,
                   'status':status,
                   'region_name': Data.SUB_ACC_REGION_NAME_2,
                   'deviceNumber':Data.DEVICE_NUMBER_CUS_BIND,
                   'subAccountId':sub_account_id
                   }

    if db.select(select_sub_acc_status):
        pass
    elif db.select(select_sub_acc):
        db.update(TABEL_CHILD_USER, update_set, update_where)
    else:
        db.insert(TABEL_CHILD_USER, insert_data)


    #主账户数据
    select_acc_1 = "select * from acornuser.acorn_user_status where userId = '{}' and deviceNumber = '{}' " \
        .format(parent_id, deviceNumber)
    if status == 1:   # if child status == 1, parent status should be 0
        select_acc_2 = "select * from acornuser.acorn_user_status where userId = '{}' and deviceNumber = '{}' and status = 0 " \
            .format(parent_id, deviceNumber)

        insert_data_parent_0 = {'userId': parent_id,
                              'status': 0,
                              'deviceNumber': Data.DEVICE_NUMBER_CUS_BIND
                              }
        set_0 = {'status': 0}
        where = {'userId': parent_id,
                 'deviceNumber': Data.DEVICE_NUMBER_CUS_BIND
                 }

        if db.select(select_acc_2):
            pass
        elif db.select(select_acc_1):
            db.update("acornuser.acorn_user_status", set_0, where)
        else:
            db.insert("acornuser.acorn_user_status", insert_data_parent_0)
    else:      # if child status == 0, parent status should be 1
        select_acc_3 = "select * from acornuser.acorn_user_status where userId = '{}' and deviceNumber = '{}' and status = 1 " \
            .format(parent_id, deviceNumber)
        set_1 = {'status': 1}
        where = {'userId': parent_id,
                 'deviceNumber': Data.DEVICE_NUMBER_CUS_BIND
                 }
        insert_data_parent_1 = {'userId': parent_id,
                                'status': 1,
                                'deviceNumber': Data.DEVICE_NUMBER_CUS_BIND
                                }
        if db.select(select_acc_3):
            pass
        elif db.select(select_acc_1):
            db.update("acornuser.acorn_user_status", set_1, where)
        else:
            db.insert("acornuser.acorn_user_status", insert_data_parent_1)




# 删除对应的parentID的所有数据 便于验证添加成功
def pre_AddSubAccount(parent_Id):
    where_data = {'parent_Id': parent_Id}
    set_data = {'parent_Id': calendar.timegm(time.gmtime())}
    if db.select_('acornuser.child_user', where_data):
        db.update('acornuser.child_user', set_data, where_data)


#检查signature存在
def checkSignatureExists(userId, type):
    #主账户
    if type == 1:
        sql_1 = 'select * from acornuser.acorn_user_extra  where user_id = {} '.format(userId)
        sql_2 = 'select * from acornuser.acorn_user_extra  where user_id = {} and signature is not NULL'.format(userId)

        if db.select(sql_2):
            pass
        elif db.select(sql_1):
            set = {'signature': '聪明的波利'}
            where = {'user_id': userId}
            db.update('acornuser.acorn_user_extra', set, where)
        else:
            insert = {'user_id': userId, 'signature': '聪明的波利'}
            db.insert('acornuser.acorn_user_extra', insert)

            # 子账户
    if type == 2:
        sql_1 = "select * from acornuser.subAccount_user_extra  where sub_account_id = '{}' ".format(userId)
        sql_2 = "select * from acornuser.subAccount_user_extra  where sub_account_id = '{}'and signature is not NULL".format(userId)

        if db.select(sql_2):
            pass
        elif db.select(sql_1):
            set = {'signature': '聪明的波利 sub~'}
            where = {'sub_account_id': userId}
            db.update('acornuser.subAccount_user_extra', set, where)
        else:
            insert = {'sub_account_id': userId, 'signature': '聪明的波利 sub~'}
            db.insert('acornuser.subAccount_user_extra', insert)


## *********************************************************************************************************************
##                 教材版本
## *********************************************************************************************************************

# 用户版本信息
def pre_GetUserPressInfo(userId):
    set = {'chinese': '北京师范大学出版社'}
    where = {'user_id': userId}
    db.update('acornuser.user_press', set, where)


## *********************************************************************************************************************
##                 登录注册
## *********************************************************************************************************************
# 检查用户注册信息是否完整
def pre_register_extrainfo_check(user_id, complete):
    #不完整
    if complete == False:
        sql = "select * from acornuser.ozing_student where user_id = {} and region_id is NULL and school_id is NULL".format(user_id)
        if db.select(sql):
            pass
        else:
            update_sql = "update acornuser.ozing_student set region_id = NULL, school_id = NULL where user_id = {}".format(user_id)
            db.update_(update_sql)

    # 完整
    else:
        sql = "select * from acornuser.ozing_student where user_id = {} and region_id is not NULL and school_id is not NULL".format(
            user_id)
        if db.select(sql):
            pass
        else:
            set = {'region_id': '140600', 'school_id': '496299', 'region_name':'江苏苏州吴中'}
            where = {'user_id': user_id}
            db.update('acornuser.ozing_student', set, where)

## *********************************************************************************************************************
##                 最近观看视频
## *********************************************************************************************************************
def pre_getRecentVideo(user_id, status):  # status=0 novideo, status=1 has video
    where = {'user_id': user_id}
    if status == 0 :
        set = {'user_id': str(time.time())}
        if db.select_('acornuser.user_video_watch', where):
            db.update('acornuser.user_video_watch', set, where)

    if status == 1 :
        if db.select_('acornuser.user_video_watch', where):
            pass
        else:
            insert_data = {'user_id': user_id,
                           'data_id': 486600,
                           'data_name':'人教7上_1 春_程诗尧.mpc',
            'play_online_url':'http://fd.xuexiao100.com/mp4/黄冈视频/初中语文/7年级上 人民教育出版社_2017版/人教7上_1 春_程诗尧.mp4?k=e8f8a7429a42aff00cb96faa6f48821e',
            'cover_url': 'http://hjxprodbucket.oss.aliyuncs.com/static/upload/boss_api/announcement/2017-10-18/34cfe338-2305-4aa0-96d8-c952be4dd800.jpg',
            'app_unique_name': 'famous-teacher',
            'created_time': '2017-12-27 14:52:08',
            'modified_time': '2017-12-27 14:52:08'
            }
            db.insert('acornuser.user_video_watch', insert_data)

## *********************************************************************************************************************
##                 手机号重复验证
## *********************************************************************************************************************
def pre_phoneUsedCheck(phone, used): #used = True :used
    where = {'username': phone}
    if used:
        if db.select_('acornuser.acorn_user', where):
            pass
        else:
            max_id = db.select('select max(id) from acornuser.acorn_user')[0]['max(id)']
            update_where = {'id': max_id - 150}
            count = db.select_('acornuser.acorn_user', update_where)

            db.update('acornuser.acorn_user', where, update_where)
    else:
        if db.select_('acornuser.acorn_user', where):
            update_set = {'username': str(time.time())}
            db.update('acornuser.acorn_user', update_set, where)


## *********************************************************************************************************************
##                 家长控制  密码
## *********************************************************************************************************************
def get_parentSpace_password(device_number):
    sql = "select password from acornuser.parents_space_pass where deviceNumber = '{}'".format(device_number)
    result = db.select(sql)
    if result:
        return result[0]['password']
    else:
        return '123456'


## *********************************************************************************************************************
##                 家长控制  app使用统计
## *********************************************************************************************************************
# "now" format: timestamp , create app using data about this week, month, year, last year(according 'now' time)
def create_app_use_record(now, user_id, device_number):
    today = date.fromtimestamp(now)
    year_start_time = int(str(time.mktime(date(today.year, 1, 1).timetuple())).split('.')[0])
    where_equal = {'user_id':user_id, 'device_number':device_number}
    where_unequal = ' time_end > {} '.format(year_start_time)
    if db.select_('analytics.app_record', where_equal, where_unequal):   # data existing
        update_data = "update analytics.app_record set device_number = '{}' where user_id = '{}' and device_number = '{}' " \
            .format(str(time.time()), user_id, device_number)
        db.update_(update_data)
    study_apps = [{'app_name':'百度英语资料大全', 'app_pid':'com.sailang.EnglishBook','category_id':'25', 'source_id':'1', \
                   'time_spent': 100, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '开心大学士', 'app_pid': 'com.ksense.study','category_id':'26', 'source_id':'1', \
                   'time_spent': 200, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '驾考宝典', 'app_pid': 'com.handsgo.jiakao.android' ,'category_id':'27', 'source_id':'1', \
                   'time_spent': 300, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '我爱汉字', 'app_pid': 'com.cronlygames.hanzi' ,'category_id':'28', 'source_id':'1',\
                   'time_spent': 400, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '拖拖乐3', 'app_pid': 'cn.com.wiisoft.tuotuo' ,'category_id':'57', 'source_id':'1', \
                   'time_spent': 100, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '幼儿数字算数学习', 'app_pid': 'com.syhrobert1991.infantlearning' ,'category_id':'25', \
                   'source_id':'1', 'time_spent': 200, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '轻松背单词之初中英语', 'app_pid': 'petpestzx.wordroid.model' ,'category_id':'26', \
                   'source_id':'2', 'time_spent': 300, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '有谱－爱学习（数理化）', 'app_pid': 'com.emingren.youpu' ,'category_id':'27', 'source_id':'2', \
                   'time_spent': 400, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '疯狂音标', 'app_pid': 'com.neo.crazyphonetic' ,'category_id':'28', 'source_id':'2', \
                   'time_spent': 100, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '互动作业V3.18.6', 'app_pid': 'com.v.study' ,'category_id':'57', 'source_id':'2', \
                   'time_spent': 250, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '发音背单词', 'app_pid': 'org.liberty.android.fantastischmemo' ,'category_id':'57', \
                   'source_id':'2', 'time_spent': 350, 'user_id': user_id, 'device_number': device_number},
                  {'app_name': '语文100', 'app_pid': 'com.kk.kkyuwen' ,'category_id':'57', 'source_id':'2', \
                   'time_spent': 450, 'user_id': user_id, 'device_number': device_number}
    ]

    game_apps = [{'app_name': '小伴龙新', 'app_pid': 'com.xiaobanlong.main' ,'category_id':'37', 'source_id':'1',\
                  'time_spent': 100, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '三国群英传', 'app_pid': 'com.tencent.tmgp.sgqyz' ,'category_id':'38', 'source_id':'1', \
                  'time_spent': 200, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '童言童语', 'app_pid': 'com.lingshi.kids' ,'category_id':'39', 'source_id':'2', \
                  'time_spent': 300, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '从前啊', 'app_pid': 'com.mojie.longlongago' ,'category_id':'37', 'source_id':'2', \
                  'time_spent': 400, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '永恒纪元', 'app_pid': 'com.m37.dtszj.uc' ,'category_id':'38', 'source_id':'2', \
                  'time_spent': 100, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '我的世界新', 'app_pid': 'com.netease.mc.aligames' ,'category_id':'39', 'source_id':'2', \
                  'time_spent': 200, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '球球大作战', 'app_pid': 'com.ztgame.bob', 'category_id': '37', 'source_id': '2', \
                  'time_spent': 300, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '葫芦侠我的世界', 'app_pid': 'com.huluxia.mctool', 'category_id': '38', 'source_id': '2',\
                  'time_spent': 400, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': 'QQ游戏V6.8.7', 'app_pid': 'com.tencent.qqgame', 'category_id': '39', 'source_id': '2',\
                  'time_spent': 100, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '99围棋最新', 'app_pid': 'com.r99weiqi.dvd', 'category_id': '37', 'source_id': '2', \
                  'time_spent': 50, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '凯蒂环球之旅', 'app_pid': 'com.tencent.HelloKitty', 'category_id': '37', 'source_id': '2',\
                  'time_spent': 150, 'user_id': user_id, 'device_number': device_number},
                 {'app_name': '贪吃蛇大作战', 'app_pid': 'com.wepie.snake.qihoo', 'category_id': '100', 'source_id': '2',\
                  'time_spent': 250, 'user_id': user_id, 'device_number': device_number}
    ]

    # get date of the last 7 days(include today)
    days = []                              # the day should insert app records
    if today.month == 1:                        # the first month of this year
        if today.day <= 7:                      # when today < 7th day of this month , 7 records chould cover this week ,this month, this year
            for i in range(0, 7):
                day_delta = timedelta(days=i)
                days.append(str(time.mktime((today - day_delta).timetuple())).split('.')[0])
        else:                                  # when today > 7th day of this month , should create 8 records to cover this week ,this month, this year
            for i in range(0, 8):
                day_delta = timedelta(days=i)
                days.append(str(time.mktime((today - day_delta).timetuple())).split('.')[0])
    else:                                      # create january data to cover this year
        for i in range(0, 8):
            day_delta = timedelta(days=i)
            days.append(str(time.mktime((today - day_delta).timetuple())).split('.')[0])
        days.append(str(time.mktime(date(today.year, 1, 1).timetuple())).split('.')[0])


    app_rec_to_insert = []
    for day in days:
        for i in range(0, 12):
            time_end_1 = {'time_end': int(day) + 28800 + i * 1000, 'id':str(uuid.uuid4()).replace('-', '')}
            time_end_2 = {'time_end': int(day) + 29800 + i * 500, 'id':str(uuid.uuid4()).replace('-', '')}
            study_app = study_apps[i].copy()
            study_app.update(time_end_1)
            game_app = game_apps[i].copy()
            game_app.update(time_end_2)
            app_rec_to_insert.append(study_app)
            app_rec_to_insert.append(game_app)

    for rec in app_rec_to_insert:
        db.insert('analytics.app_record', rec)