const SQL = require("../_helper/database")
const {getUserByID} = require("./user.service")

module.exports = {addMessage, getMessage, getConversation}
async function addMessage(content, senderID, receiverID, password){
    let sender = await getUserByID(senderID, true)
    let receiver = await getUserByID(receiverID)
    if(!sender || !receiver){
        throw "no such user"
    }
    if(sender.password !== password){
        throw "unauthorized"
    }
    let db = await SQL.getConnection()
    await db.query(
        `INSERT INTO messages (sender_id, receiver_id, content) VALUES
        (${senderID}, ${receiverID}, "${content}")`
    )

}

async function getMessage(senderID, receiverID, password){
    let sender = await getUserByID(senderID, true)
    let receiver = await getUserByID(receiverID)
    if(!sender || !receiver){
        throw "no such user"
    }
    if(sender.password !== password){
        throw "unauthorized"
    }
    let db = await SQL.getConnection()
    let messages = await db.query(
        `SELECT messages.*, s.name AS sender_name, r.name AS receiver_name FROM messages
        JOIN user s ON messages.sender_id = s.id
        JOIN user r on messages.receiver_id = r.id
        WHERE (messages.sender_id = ${senderID} AND messages.receiver_id = ${receiverID}) 
        OR (messages.sender_id = ${receiverID} AND messages.receiver_id = ${senderID})
        ORDER BY messages.time
        `
    )
    return messages[0]
}

async function getConversation(userID, password){
    let user = await getUserByID(userID, true)
    if(!user){
        throw "no such user"
    }
    if(user.password !== password){
        throw "unauthorized"
    }
    let db = await SQL.getConnection()
    let conversations = await db.query(
        `
        SELECT DISTINCT 
            CASE 
                WHEN messages.sender_id = ${userID} THEN r.id
                ELSE s.id
            END AS id,
            CASE 
                WHEN messages.sender_id = ${userID} THEN r.name
                ELSE s.name
            END AS name
        FROM messages
        JOIN user s ON messages.sender_id = s.id
        JOIN user r ON messages.receiver_id = r.id
        WHERE messages.sender_id = ${userID} OR messages.receiver_id = ${userID};

        `
    )
    return conversations[0]
}