entity-relationship-diagram.dbml

Table User {
    id UUID [pk]
    username String
    profilePicture String // Adjust type as needed (e.g., URL or BLOB)
    firstName String
    lastName String
    bio String
}

Table FriendTag {
    id UUID [pk]
    displayName String
    color String
}

// Junction table for User-FriendTag relationship (One-to-Many)
Table UserFriendTag {
    userId UUID [ref: > User.id]
    friendTagId UUID [ref: > FriendTag.id]
    indexes {
        (userId, friendTagId) [pk]
    }
}

Table Event {
    id UUID [pk]
    title String
    startTime Timestamp
    endTime Timestamp
    locationId UUID [ref: > Location.id]
    note String
    creatorId UUID [unique, ref: > User.id]
}

// Junction table for Event Tags (Many-to-Many)
Table EventTag {
    eventId UUID [ref: > Event.id]
    tagId UUID [ref: > FriendTag.id]
    indexes {
        (eventId, tagId) [pk]
    }
}

// Junction table for Event Participants (One-to-Many)
Table EventParticipants {
    eventId UUID [ref: > Event.id]
    userId UUID [ref: > User.id]
    indexes {
        (eventId, userId) [pk]
    }
}

// Junction table for Event Invited (One-to-Many)
Table EventInvited {
    eventId UUID [ref: > Event.id]
    userId UUID [ref: > User.id]
    indexes {
        (eventId, userId) [pk]
    }
}

Table ChatMessage {
    id UUID [pk]
    timestamp Timestamp
    userId UUID [ref: > User.id, unique]
    content String
    eventId UUID [ref: > Event.id] // Association with Event
}

// Junction table for ChatMessage Likes (Many-to-Many)
Table ChatMessageLikes {
    chatMessageId UUID [ref: > ChatMessage.id]
    userId UUID [ref: > User.id]
    indexes {
        (chatMessageId, userId) [pk]
    }
}

// Table for Friend relationships (Many-to-Many)
Table UserFriends {
    userId1 UUID [ref: > User.id]
    userId2 UUID [ref: > User.id]
    indexes {
        (userId1, userId2) [pk]
    }
}

Table UserFriendTagMapping {
    id UUID [pk]
    userId1 UUID [ref: > User.id]
    userId2 UUID [ref: > User.id]
    friendTagId UUID [ref: > FriendTag.id]
    indexes {
        (userId1, userId2, friendTagId) [pk]
    }
}


// Table for Friend Requests (Many-to-Many)
Table FriendRequests {
    senderId UUID [ref: > User.id]
    receiverId UUID [ref: > User.id]
    indexes {
        (senderId, receiverId) [pk]
    }
}

Table Location {
    id UUID [pk]
    name String
    latitude Double
    longitude Double
}