"""Conversation CRUD with unchanged response data."""

from .db import connect, now_str
from .visitor_service import visitor_id, owns_conversation
from .customer_service import current_customer_id, current_scope_key


WELCOME_TEXT = '您好！欢迎咨询柏悦物流智能客服，可以咨询运费、时效或联系人工客服。'


def new_chat():
    now = now_str()
    conn = connect()
    cur = conn.cursor()
    cur.execute('INSERT INTO chat_history(title, ctime, utime, visitor_id) VALUES (?,?,?,?)',
                ('新对话', now, now, visitor_id()))
    conv_id = cur.lastrowid
    cur.execute('INSERT INTO messages(conv_id, role, content, ctime) VALUES (?,?,?,?)',
                (conv_id, 'assistant', WELCOME_TEXT, now))
    conn.commit()
    conn.close()
    return {'id': conv_id, 'welcome': WELCOME_TEXT}


def get_history():
    conn = connect()
    rows = conn.execute('SELECT id, title, ctime, COALESCE(utime, ctime) FROM chat_history WHERE visitor_id=? '
                        'AND (customer_id IS NULL OR (customer_id=? AND customer_scope=?)) '
                        'ORDER BY COALESCE(utime, ctime) DESC, id DESC', (visitor_id(),current_customer_id(),current_scope_key())).fetchall()
    conn.close()
    return [{'id': r[0], 'title': r[1], 'ctime': r[2], 'utime': r[3]} for r in rows]


def get_messages(conv_id):
    if not owns_conversation(conv_id):
        return []
    conn = connect()
    rows = conn.execute('SELECT role, content, ctime FROM messages WHERE conv_id=? ORDER BY id DESC LIMIT 100', (conv_id,)).fetchall()
    conn.close()
    rows = rows[::-1]
    return [{'role': r[0], 'content': r[1], 'ctime': r[2]} for r in rows]


def del_history(hid):
    if not owns_conversation(hid):
        return {'ok': False}, 404
    conn = connect()
    cur = conn.cursor()
    cur.execute('DELETE FROM human_messages WHERE session_id IN (SELECT id FROM human_sessions WHERE conv_id=?)', (hid,))
    cur.execute('DELETE FROM human_sessions WHERE conv_id=?', (hid,))
    cur.execute('DELETE FROM messages WHERE conv_id=?', (hid,))
    cur.execute('DELETE FROM chat_history WHERE id=?', (hid,))
    conn.commit()
    conn.close()
    return {'ok': True}


def clear_all():
    conn = connect()
    cur = conn.cursor()
    owner = visitor_id()
    owned = 'SELECT id FROM chat_history WHERE visitor_id=? AND (customer_id IS NULL OR (customer_id=? AND customer_scope=?))'
    scope = (owner,current_customer_id(),current_scope_key())
    cur.execute('DELETE FROM human_messages WHERE session_id IN (SELECT id FROM human_sessions '
                'WHERE conv_id IN ('+owned+'))', scope)
    cur.execute('DELETE FROM human_sessions WHERE conv_id IN ('+owned+')', scope)
    cur.execute('DELETE FROM messages WHERE conv_id IN ('+owned+')', scope)
    cur.execute('DELETE FROM chat_history WHERE id IN ('+owned+')', scope)
    conn.commit()
    conn.close()
    return {'ok': True}