4
5// User operations
6export async function findOrCreateUser(email: string): Promise<User> {
7 // Try to find existing user
8 const { rows } = await sqlite.execute(
25
26// Conversation operations
27export async function getUserConversations(userId: number): Promise<Conversation[]> {
28 // Use the new participant-based query but return simple Conversation objects for backward compatibility
29 const conversations = await getUserConversationsWithParticipants(userId);
31}
32
33export async function createConversation(userId: number, title: string): Promise<Conversation> {
34 const result = await sqlite.execute(
35 `INSERT INTO ${CONVERSATIONS_TABLE} (user_id, title) VALUES (?, ?) RETURNING *`,
45}
46
47export async function updateConversationTimestamp(conversationId: number): Promise<void> {
48 await sqlite.execute(
49 `UPDATE ${CONVERSATIONS_TABLE} SET updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
52}
53
54export async function deleteConversation(conversationId: number, userId: number): Promise<boolean> {
55 // Check if user is admin
56 const isAdmin = await isUserAdmin(conversationId, userId);
87
88// Message operations
89export async function getConversationMessages(conversationId: number): Promise<Message[]> {
90 const { rows } = await sqlite.execute(
91 `SELECT * FROM ${MESSAGES_TABLE} WHERE conversation_id = ? ORDER BY timestamp ASC`,
96}
97
98export async function addMessage(conversationId: number, role: 'user' | 'assistant', content: string): Promise<Message> {
99 const result = await sqlite.execute(
100 `INSERT INTO ${MESSAGES_TABLE} (conversation_id, role, content) VALUES (?, ?, ?) RETURNING *`,
105}
106
107export async function getConversationWithMessages(conversationId: number, userId: number): Promise<ConversationWithMessages | null> {
108 // Check if user is participant
109 const isParticipant = await isUserParticipant(conversationId, userId);
131}
132
133// Helper function to generate conversation title from first message
134export function generateConversationTitle(firstMessage: string): string {
135 // Take first 50 characters and add ellipsis if longer
136 const title = firstMessage.trim();
139
140// Participant operations
141export async function addParticipant(conversationId: number, userId: number, role: 'admin' | 'participant'): Promise<ConversationParticipant> {
142 const result = await sqlite.execute(
143 `INSERT INTO ${PARTICIPANTS_TABLE} (conversation_id, user_id, role) VALUES (?, ?, ?) RETURNING *`,
148}
149
150export async function getConversationParticipants(conversationId: number): Promise<ConversationParticipant[]> {
151 const { rows } = await sqlite.execute(`
152 SELECT p.*, u.email as user_email
160}
161
162export async function isUserParticipant(conversationId: number, userId: number): Promise<boolean> {
163 const { rows } = await sqlite.execute(
164 `SELECT 1 FROM ${PARTICIPANTS_TABLE} WHERE conversation_id = ? AND user_id = ?`,
169}
170
171export async function isUserAdmin(conversationId: number, userId: number): Promise<boolean> {
172 const { rows } = await sqlite.execute(
173 `SELECT 1 FROM ${PARTICIPANTS_TABLE} WHERE conversation_id = ? AND user_id = ? AND role = 'admin'`,
178}
179
180export async function removeParticipant(conversationId: number, userId: number): Promise<boolean> {
181 const result = await sqlite.execute(
182 `DELETE FROM ${PARTICIPANTS_TABLE} WHERE conversation_id = ? AND user_id = ?`,
187}
188
189export async function getUserConversationsWithParticipants(userId: number): Promise<(Conversation & { participant_count: number, is_group: boolean })[]> {
190 const { rows } = await sqlite.execute(`
191 SELECT c.*,
202}
203
204// Migration function to fix existing conversations without participants
205export async function ensureUserIsParticipant(conversationId: number, userId: number): Promise<void> {
206 // Check if user is already a participant
207 const isParticipant = await isUserParticipant(conversationId, userId);
222
223// Invite operations
224export async function createInvite(conversationId: number, invitedEmail: string, invitedBy: number): Promise<ConversationInvite> {
225 // Generate unique token
226 const token = crypto.randomUUID();
238}
239
240export async function getInviteByToken(token: string): Promise<(ConversationInvite & { conversation_title: string, invited_by_email: string }) | null> {
241 const { rows } = await sqlite.execute(`
242 SELECT i.*, c.title as conversation_title, u.email as invited_by_email
250}
251
252export async function deleteInvite(token: string): Promise<boolean> {
253 const result = await sqlite.execute(
254 `DELETE FROM ${INVITES_TABLE} WHERE invite_token = ?`,
259}
260
261export async function isEmailAlreadyInvited(conversationId: number, email: string): Promise<boolean> {
262 const { rows } = await sqlite.execute(`
263 SELECT 1 FROM ${INVITES_TABLE}
268}
269
270export async function isEmailAlreadyParticipant(conversationId: number, email: string): Promise<boolean> {
271 const { rows } = await sqlite.execute(`
272 SELECT 1 FROM ${PARTICIPANTS_TABLE} p
