import json
from sqlite3 import Cursor
from db.sop import sop_node_get_list_by_parent
from common.sql_lite import init_new_db_connection

"""
message_records 相关Model和CURD方法
"""


# 获取 查询聊天的最新一条信息，以及这条信息关联的sop_node列表
def get_next_answer(cursor: Cursor, bot_wxid: str, contact_wxid: str, contact_type: int):

    # 在message_records表中，查询bot_wxid == bot_wxid and contact_wxid == contact_wxid contact_type == 1 source_type == 3 status == 1 的最新一条记录，按created_at字段排序
    sql_query = "SELECT * FROM message_records WHERE bot_wxid = ? AND contact_wxid = ? AND contact_type = ? AND content_type = 1 AND (source_type = 3 OR source_type = 4) AND status = 3 ORDER BY id DESC LIMIT 1"
    sql_data = (bot_wxid, contact_wxid, contact_type)
    cursor.execute(sql_query, sql_data)
    message_record = cursor.fetchone()

    if message_record:
        if message_record['source_type'] == 3:
            # sql_query = "SELECT * FROM sop_node WHERE parent_id = ? AND stage_id = ?"
            # sql_data = (0, message_record['source_id'])
            sop_nodes = sop_node_get_list_by_parent(cursor, 0, message_record['source_id'])
        else:
            # sql_query = "SELECT * FROM sop_node WHERE parent_id = ?"
            # sql_data = (message_record['source_id'],)
            sop_nodes = sop_node_get_list_by_parent(cursor, message_record['source_id'], None)

        return message_record, sop_nodes

    return None, None


# 判断发送记录是否已存在
def check_message_record(cursor: Cursor, contact_wxid: str, source_type: int, source_id: int, sub_source_id: int) -> bool:
        sql_query = "SELECT * FROM message_records WHERE contact_wxid = ? AND source_type = ? AND source_id = ? AND sub_source_id = ?"
        record_tuple = (contact_wxid, source_type, source_id, sub_source_id)

        cursor.execute(sql_query, record_tuple)
        message_record = cursor.fetchone()

        return True if message_record else False


# 创建 message_records 记录
def create_message_record(cursor: Cursor, status: int, bot_wxid: str, contact_id: int, contact_type: int, contact_wxid: str, content_type: int, content: str, meta: dict, source_type: int, source_id: int, sub_source_id: int, organization_id: int):
        meta_str = json.dumps(meta) if isinstance(meta, dict) else meta
        sql_insert = "INSERT INTO message_records (status, bot_wxid, contact_id, contact_type, contact_wxid, content_type, content, meta, source_type, source_id, sub_source_id, organization_id, send_time, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), NOW())"
        data_insert = (status, bot_wxid, contact_id, contact_type, contact_wxid,content_type,
                       content, meta_str, source_type,source_id, sub_source_id, organization_id)
        return cursor.execute(sql_insert, data_insert)


# 根据条件获取 message_record 记录
def get_message_by_condition(cursor: Cursor, status, source_type, source_id, sub_source_id: int, start_time: int, end_time: int):
    query_sql = "SELECT * from message_records WHERE status=? and source_type=? and source_id=? and sub_source_id=? and send_time>=? and send_time<=?"
    query_data = (status, source_type, source_id, sub_source_id, start_time, end_time)
    cursor.execute(query_sql, query_data)
    message_records = cursor.fetchall()
    return message_records


# 根据条件获取 message_record 最新一条记录
def get_message_by_contact_new_one(cursor: Cursor, contact_id, status):
    query_sql = "SELECT * from message_records WHERE status=? and contact_id=? ORDER BY created_at DESC LIMIT 1"
    query_data = (status, contact_id)
    cursor.execute(query_sql, query_data)
    message_record = cursor.fetchone()
    return message_record