1// Database query functions for TaskSwap
2
3import { sqlite } from "https://esm.town/v/stevekrouse/sqlite";
5
6// User queries
7export async function createUser(userData: CreateUserRequest & { password_hash: string }): Promise<User> {
8 const result = await sqlite.execute(`
9 INSERT INTO users (username, email, password_hash, full_name, bio, skills)
21}
22
23export async function getUserById(id: number): Promise<User> {
24 const result = await sqlite.execute('SELECT * FROM users WHERE id = ?', [id]);
25 if (result.length === 0) throw new Error('User not found');
33}
34
35export async function getUserByEmail(email: string): Promise<User | null> {
36 const result = await sqlite.execute('SELECT * FROM users WHERE email = ?', [email]);
37 if (result.length === 0) return null;
45}
46
47export async function getUserByUsername(username: string): Promise<User | null> {
48 const result = await sqlite.execute('SELECT * FROM users WHERE username = ?', [username]);
49 if (result.length === 0) return null;
57}
58
59export async function updateUserTokens(userId: number, tokenChange: number): Promise<void> {
60 await sqlite.execute(`
61 UPDATE users
65}
66
67export async function updateUserRating(userId: number, newRating: number): Promise<void> {
68 await sqlite.execute(`
69 UPDATE users
74
75// Task queries
76export async function createTask(taskData: CreateTaskRequest, creatorId: number): Promise<Task> {
77 const tokenCost = calculateTokenCost(taskData.difficulty, taskData.estimated_hours);
78
95}
96
97export async function getTaskById(id: number): Promise<Task> {
98 const result = await sqlite.execute(`
99 SELECT t.*,
127}
128
129export async function getTasks(filters: {
130 category?: string;
131 difficulty?: string;
212}
213
214export async function updateTaskStatus(taskId: number, status: string, assigneeId?: number): Promise<void> {
215 if (assigneeId) {
216 await sqlite.execute(`
229
230// Message queries
231export async function createMessage(senderId: number, receiverId: number, taskId: number, content: string): Promise<Message> {
232 const result = await sqlite.execute(`
233 INSERT INTO messages (sender_id, receiver_id, task_id, content)
238}
239
240export async function getMessageById(id: number): Promise<Message> {
241 const result = await sqlite.execute(`
242 SELECT m.*,
269}
270
271export async function getTaskMessages(taskId: number): Promise<Message[]> {
272 const result = await sqlite.execute(`
273 SELECT m.*,
299
300// Exchange queries
301export async function createExchange(taskId: number, creatorId: number, assigneeId: number): Promise<Exchange> {
302 const result = await sqlite.execute(`
303 INSERT INTO exchanges (task_id, creator_id, assignee_id, status)
308}
309
310export async function getExchangeById(id: number): Promise<Exchange> {
311 const result = await sqlite.execute('SELECT * FROM exchanges WHERE id = ?', [id]);
312 if (result.length === 0) throw new Error('Exchange not found');
314}
315
316export async function updateExchangeStatus(exchangeId: number, status: string): Promise<void> {
317 const completedAt = status === 'completed' ? new Date().toISOString() : null;
318
324}
325
326export async function addExchangeRating(exchangeId: number, isCreator: boolean, rating: number, feedback?: string): Promise<void> {
327 if (isCreator) {
328 await sqlite.execute(`
341
342// Session management
343export async function createSession(userId: number): Promise<string> {
344 const sessionId = crypto.randomUUID();
345 const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000); // 7 days
353}
354
355export async function getSessionUser(sessionId: string): Promise<User | null> {
356 const result = await sqlite.execute(`
357 SELECT u.* FROM users u
370}
371
372export async function deleteSession(sessionId: string): Promise<void> {
373 await sqlite.execute('DELETE FROM sessions WHERE id = ?', [sessionId]);
374}
375
376// Utility functions
377function calculateTokenCost(difficulty: string, estimatedHours: number): number {
378 const baseRates = {
379 easy: 2,
