import os
import sys
import datetime



sys.path.append(os.getcwd())
from datetime import datetime
from common import MysqlHelper


class sql():

    #  获取音画美文类数量-抖音
    @classmethod
    def get_dy_yinmei_account_id(cls):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM video_audio WHERE time = '{formatted_time}' AND video_type = 0 GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    #  获取音画美文类数量-快手
    @classmethod
    def get_ks_yinmei_account_id(cls):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM video_audio WHERE time = '{formatted_time}' AND video_type = 2 GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    #  获取口播类数量
    @classmethod
    def get_koubo_account_id(cls):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM video_audio WHERE time = '{formatted_time}' AND video_type = 1 GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    #  获取口播类-节日数量
    @classmethod
    def get_jieri_account_id(cls):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM video_audio WHERE time = '{formatted_time}' AND video_type = 3 GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    #  获取春节自制-节日数量
    @classmethod
    def get_chunjie_zizhi_account_id(cls):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM video_audio WHERE time = '{formatted_time}' AND video_type = 4 GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    #  获取抖音拼接数量
    @classmethod
    def get_pjdouyin_account_id(cls):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM video_audio WHERE time = '{formatted_time}' AND video_type = 6 GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)

    #  获取快手拼接数量
    @classmethod
    def get_ksdouyin_account_id(cls):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d")
        count = f"""SELECT COUNT(*) AS total_count FROM ( SELECT audio, account_id FROM video_audio WHERE time = '{formatted_time}' AND video_type = 5 GROUP BY audio, account_id) AS subquery;"""
        count = MysqlHelper.get_values(count, "prod")
        if count == None:
            count = 0
        count = str(count).replace('(', '').replace(')', '').replace(',', '')
        return int(count)


    @classmethod
    def update_inconformity_id_list(cls, id):
        update_sql = f""" update video_url set status={0}  WHERE video_id ="{id}" """
        MysqlHelper.update_values(
            sql=update_sql,
            env="prod",
            machine="",
        )