# Author: Lee Wu Love Lele
# Datetime: 2024/12/6 19:53

"""
    SELECT ff.reader_code FROM farm_field AS ff
    WHERE ff.is_deleted = 1
    AND ff.reader_code IS NOT NULL AND ff.reader_code != '' AND ff.rfid_reader_code IS NOT NULL AND ff.rfid_reader_code != ''
    GROUP BY ff.reader_code
"""
QUERY_DEVICES = "SELECT ff.reader_code FROM farm_field AS ff WHERE ff.is_deleted = 1 AND ff.reader_code IS NOT NULL AND ff.reader_code != '' AND ff.rfid_reader_code IS NOT NULL AND ff.rfid_reader_code != '' GROUP BY ff.reader_code;"

QUERY_EAR_TAG_ID_BY_RFID = """
    SELECT sb.shell_subject_code
    FROM subject_base AS sb
    WHERE is_deleted = 1
    AND lot_subject_code = %s
    AND shell_subject_code != ''
"""

"""
查询本地日志中是否分析过地磅或者分析到了什么时间
"""
QUERY_EXEC_LOG = "SELECT * FROM exec_log AS el WHERE el.time_std = %s;"

INSERT_WEIGHT_WAVE = """
    INSERT INTO weight_wave (floor_scale_id, start_time, end_time, time_std , max_frq_rfid , max_frq, weight, create_time,last_update_time) 
    VALUES (%s,%s,%s,%s,%s,%s,%s,NOW(),NOW())
"""

INSERT_RIFD_FRQ = """
    INSERT INTO rfid_value_frq(id_weight_wave,ear_tag,rfid,rfid_frq,time_std,create_time,last_update_time) 
    VALUES (%s,%s,%s,%s,%s,%s,%s)
"""


def sql_delete_weight_wave_all():
    return "TRUNCATE TABLE weight_wave"


def sql_delete_weight_wave_by_time_std():
    return "DELETE FROM weight_wave WHERE time_std = %s;"


DELETE_RFID_FRQ = "TRUNCATE TABLE rfid_value_frq"


def sql_query_buy_weight_by_rfid():
    return "SELECT buy_weight FROM subject_base WHERE lot_subject_code = %s;"


def sql_query_logs_count_group_by_biz_data(reader_names=None):
    """
    耳标RFID识别结果频次，biz_data返回的是RFID
    :param reader_names: RFID Reader ID (tuple)
    :return:
    """
    placeholders = ', '.join(['%s'] * len(reader_names))
    return f"SELECT biz_data, COUNT(*) AS repeat_count FROM notify_log WHERE notify_group = 'rfidCallback' AND reader_name IN ({placeholders}) AND biz_time BETWEEN %s AND %s GROUP BY biz_data ORDER BY repeat_count DESC;"


# def sql_query_logs_by_reader_name(reader_names=None):
#     """
#     根据设备ID查询全时间周期
#     :param reader_name: (tuple)
#     :return:
#     """
#     placeholders = ', '.join(['%s'] * len(reader_names))
#     return f"SELECT nl.biz_time, nl.biz_data, nl.create_time, nl.relation_reader_name FROM notify_log AS nl WHERE reader_name IN ({placeholders}) AND is_deleted = 1 ORDER BY biz_time ASC, create_time ASC;"


def sql_query_logs_by_reader_name_and_duartion(reader_names=None, time_std=1):
    """
    根据设备ID查询特定时间段
    :param reader_name: (tuple)
    :return:
    """
    placeholders = ', '.join(['%s'] * len(reader_names))
    first_order = 'biz_data'
    second_order = 'create_time'
    time = 'biz_time'
    if time_std == 1:
        pass
    elif time_std == 2:
        first_order = 'create_time'
        second_order = 'biz_data'
        time = 'create_time'
        pass
    return f"SELECT nl.notify_id, nl.biz_data ,nl.biz_time, nl.create_time, nl.relation_reader_name FROM notify_log AS nl WHERE reader_name IN({placeholders}) AND is_deleted = 1  AND {time} BETWEEN %s AND %s ORDER BY {first_order} ASC, {second_order} ASC, notify_id ASC;"


def sql_query_logs_group_by_relation_reader_name(reader_names=None):
    """
    根据地磅ID查询日志中对应的RFIDReaderID出现次数，识别一个时间段中地磅ID与RFIDReaderID对应关系的变化。
    :param reader_name: (tuple)
    :return:
    """
    placeholders = ', '.join(['%s'] * len(reader_names))
    return f"SELECT relation_reader_name, COUNT(*) AS repeat_count FROM notify_log WHERE reader_name IN ({placeholders}) AND biz_time BETWEEN %s AND %s GROUP BY relation_reader_name ORDER BY repeat_count DESC;"


def sql_insert_rfid_reader():
    return "INSERT INTO relation_rfid_reader(id_weight_wave,reader_name,create_time,last_update_time) VALUES (%s,%s,NOW(),NOW());"


def sql_insert_exec_log():
    return "INSERT INTO exec_log(floor_scale_id,start_time,end_time,time_std,create_time,last_update_time) VALUES(%s,%s,%s,%s,NOW(),NOW());"


def sql_delete_exec_log_all():
    return "TRUNCATE TABLE exec_log;"


def sql_delete_exec_log_by_time_std():
    return "DELETE FROM weight_wave WHERE time_std = %s;"


def sql_delete_relatioin_rfid_reader():
    return "TRUNCATE TABLE relation_rfid_reader;"


def sql_update_exec_log():
    return "UPDATE exec_log SET end_time = %s, last_update_time = NOW() WHERE floor_scale_id = %s;"


def sql_query_weight_wave_by_floor_scale_id():
    """
    通过地磅ID查询全部体重波
    :return:
    """
    return "SELECT * FROM weight_wave where floor_scale_id = %s"


def sql_query_weight_wave_by_rfid():
    """
    最高出现频率的RFID查询全部体重波
    :return:
    """
    return "SELECT * FROM weight_wave where max_frq_rfid = %s"


def sql_query_relation_reader_by_id_weight_wave():
    return "SELECT * FROM relation_rfid_reader AS rrr WHERE rrr.id_weight_wave = %s"


def sql_query_weight_wave_group_by_rfid():
    return "SELECT max_frq_rfid, COUNT(*) AS repeat_count FROM weight_wave WHERE time_std = %s GROUP BY max_frq_rfid;"


def sql_query_weight_wave_by_rfid():
    return "SELECT * FROM weight_wave WHERE max_frq_rfid = %s AND time_std = %s ORDER BY start_time ASC;"


def sql_query_rfid_all_in_cattle():
    return "SELECT subject_id AS id ,lot_subject_code AS rfid, shell_subject_code AS ear_tag FROM subject_base WHERE is_deleted = 1 AND buy_weight != 0 AND lot_subject_code != '' AND shell_subject_code != '';"