import { sqlite } from "https://esm.town/v/stevekrouse/sqlite"; export default async function server(request: Request): Promise { const SCHEMA_VERSION = 15; // Increment this to create new tables const KEY = new URL(import.meta.url).pathname.split("/").at(-1); await sqlite.execute(` CREATE TABLE IF NOT EXISTS ${KEY}_calendars_${SCHEMA_VERSION} ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, creator TEXT, start_day INTEGER, end_day INTEGER, start_time TEXT, end_time TEXT ) `); await sqlite.execute(` CREATE TABLE IF NOT EXISTS ${KEY}_calendar_users_${SCHEMA_VERSION} ( calendar_id INTEGER, user_id TEXT, PRIMARY KEY (calendar_id, user_id) ) `); await sqlite.execute(` CREATE TABLE IF NOT EXISTS ${KEY}_availability_${SCHEMA_VERSION} ( calendar_id INTEGER, user_id TEXT, day INTEGER, time INTEGER, PRIMARY KEY (calendar_id, user_id, day, time) ) `); const url = new URL(request.url); const path = url.pathname; if (path === "/calendars") { if (request.method === "POST") { const { name, creator, startDay, endDay, startTime, endTime } = await request.json(); const result = await sqlite.execute( `INSERT INTO ${KEY}_calendars_${SCHEMA_VERSION} (name, creator, start_day, end_day, start_time, end_time) VALUES (?, ?, ?, ?, ?, ?)`, [name, creator, startDay, endDay, startTime, endTime], ); const calendarId = result.lastInsertId; await sqlite.execute( `INSERT INTO ${KEY}_calendar_users_${SCHEMA_VERSION} (calendar_id, user_id) VALUES (?, ?)`, [calendarId, creator], ); return new Response(JSON.stringify({ id: calendarId }), { headers: { "Content-Type": "application/json" }, }); } else if (request.method === "GET") { const calendars = await sqlite.execute(`SELECT * FROM ${KEY}_calendars_${SCHEMA_VERSION}`); return new Response(JSON.stringify(calendars.rows), { headers: { "Content-Type": "application/json" }, }); } else if (request.method === "DELETE") { const { id } = await request.json(); await sqlite.execute(`DELETE FROM ${KEY}_calendars_${SCHEMA_VERSION} WHERE id = ?`, [id]); await sqlite.execute(`DELETE FROM ${KEY}_calendar_users_${SCHEMA_VERSION} WHERE calendar_id = ?`, [id]); await sqlite.execute(`DELETE FROM ${KEY}_availability_${SCHEMA_VERSION} WHERE calendar_id = ?`, [id]); return new Response("OK"); } } if (path === "/availability") { if (request.method === "POST") { const { calendarId, userId, day, time, available } = await request.json(); await sqlite.execute( `INSERT OR IGNORE INTO ${KEY}_calendar_users_${SCHEMA_VERSION} (calendar_id, user_id) VALUES (?, ?)`, [calendarId, userId], ); if (available) { await sqlite.execute( `INSERT OR IGNORE INTO ${KEY}_availability_${SCHEMA_VERSION} (calendar_id, user_id, day, time) VALUES (?, ?, ?, ?)`, [calendarId, userId, day, time], ); } else { await sqlite.execute( `DELETE FROM ${KEY}_availability_${SCHEMA_VERSION} WHERE calendar_id = ? AND user_id = ? AND day = ? AND time = ?`, [calendarId, userId, day, time], ); } console.log( `Availability ${ available ? "added" : "removed" } for user ${userId} on calendar ${calendarId} at day ${day}, time ${time}`, ); return new Response("OK"); } else if (request.method === "GET") { const calendarId = url.searchParams.get("calendarId"); if (!calendarId) { throw new Error("Missing calendarId in URL search parameters"); } const users = await sqlite.execute( `SELECT user_id FROM ${KEY}_calendar_users_${SCHEMA_VERSION} WHERE calendar_id = ?`, [calendarId], ); const availability = await sqlite.execute( `SELECT user_id, day, time FROM ${KEY}_availability_${SCHEMA_VERSION} WHERE calendar_id = ?`, [calendarId], ); console.log(`Retrieved availability for calendar ${calendarId}:`, availability.rows); return new Response(JSON.stringify({ users: users.rows, availability: availability.rows }), { headers: { "Content-Type": "application/json" }, }); } } return new Response( ` Scheduler

Scheduler

Start by entering in your username, then creating or viewing a calendar.

Username:

Create New Calendar

Calendar Name:

Start Day: Start Time:

End Day: End Time:

Show Debug Info

`, { headers: { "Content-Type": "text/html" }, }, ); }

scheduler