# Insert a new message into the database
from flask import jsonify

from database import get_cached_connection


def insert_message(sender_id, receiver_id, message):
    conn = get_cached_connection()
    cursor = conn.cursor()
    query = """
        INSERT INTO chats (sender_id, receiver_id, message) 
        VALUES (%s, %s, %s)
    """
    cursor.execute(query, (sender_id, receiver_id, message))
    conn.commit()
    cursor.close()
    conn.close()

# Fetch all messages between two users


def get_messages(sender_id, receiver_id):
    conn = get_cached_connection()
    cursor = conn.cursor()
    query = """
        SELECT * FROM chats 
        WHERE (sender_id = %s AND receiver_id = %s)
        OR (sender_id = %s AND receiver_id = %s)
        ORDER BY created_at ASC
    """
    print(
        f"Executing query with sender_id: {sender_id}, receiver_id: {receiver_id}")
    cursor.execute(query, (sender_id, receiver_id, receiver_id, sender_id))
    results = cursor.fetchall()
    print(f"Results: {results}")
    cursor.close()
    conn.close()
    return results


def send_message(request):
    try:
        data = request.get_json()
        sender_id = data['sender_id']
        receiver_id = data['receiver_id']
        message = data['message']

        # Insert message into database
        insert_message(sender_id, receiver_id, message)
        return jsonify({'status': 'success'})
    except Exception as e:
        return jsonify({'status': 'error', 'message': str(e)}), 500


def get_messages_route(request):
    try:
        sender_id = request.args.get('sender_id')
        receiver_id = request.args.get('receiver_id')

        messages = get_messages(sender_id, receiver_id)
        return jsonify({'status': 'success', 'messages': messages})
    except Exception as e:
        return jsonify({'status': 'error', 'message': str(e)}), 500
