419 lines
13 KiB
JavaScript
419 lines
13 KiB
JavaScript
|
const express = require('express');
|
||
|
|
const path = require('path');
|
||
|
|
const sqlite3 = require('sqlite3').verbose();
|
||
|
|
|
||
|
|
const app = express();
|
||
|
|
const port = 3000;
|
||
|
|
|
||
|
|
// Serve static files from the 'public' directory
|
||
|
|
app.use(express.static(path.join(__dirname, 'public')));
|
||
|
|
|
||
|
|
// Serve HTML files from the 'views' directory
|
||
|
|
app.get('/', (req, res) => {
|
||
|
|
res.sendFile(path.join(__dirname, 'views', 'index.html'));
|
||
|
|
});
|
||
|
|
|
||
|
|
app.get('/admin', (req, res) => {
|
||
|
|
res.sendFile(path.join(__dirname, 'views', 'admin.html'));
|
||
|
|
});
|
||
|
|
|
||
|
|
// Middleware to parse JSON bodies
|
||
|
|
app.use(express.json());
|
||
|
|
|
||
|
|
// Helper function to create database tables
|
||
|
|
const createDatabaseTables = (callback) => {
|
||
|
|
db.serialize(() => {
|
||
|
|
// Enable foreign key support
|
||
|
|
db.run('PRAGMA foreign_keys = ON', (err) => {
|
||
|
|
if (err) {
|
||
|
|
callback(new Error(`Failed to enable foreign keys: ${err.message}`));
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Create uids table
|
||
|
|
db.run(`CREATE TABLE IF NOT EXISTS uids (
|
||
|
|
uid TEXT PRIMARY KEY,
|
||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
|
|
)`, (err) => {
|
||
|
|
if (err) {
|
||
|
|
callback(new Error(`Failed to create uids table: ${err.message}`));
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Create availability table
|
||
|
|
db.run(`CREATE TABLE IF NOT EXISTS availability (
|
||
|
|
uid TEXT,
|
||
|
|
date TEXT,
|
||
|
|
times TEXT,
|
||
|
|
PRIMARY KEY (uid, date),
|
||
|
|
FOREIGN KEY (uid) REFERENCES uids(uid) ON DELETE CASCADE
|
||
|
|
)`, (err) => {
|
||
|
|
if (err) {
|
||
|
|
callback(new Error(`Failed to create availability table: ${err.message}`));
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
callback(null);
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
};
|
||
|
|
|
||
|
|
// Helper function to initialize database
|
||
|
|
const initializeDatabase = () => {
|
||
|
|
// Ensure db directory exists
|
||
|
|
const fs = require('fs');
|
||
|
|
const dbDir = 'db';
|
||
|
|
if (!fs.existsSync(dbDir)) {
|
||
|
|
fs.mkdirSync(dbDir);
|
||
|
|
}
|
||
|
|
|
||
|
|
// Connect to SQLite database
|
||
|
|
db = new sqlite3.Database('db/freesched.db', (err) => {
|
||
|
|
if (err) {
|
||
|
|
console.error('Could not connect to database:', err.message);
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
console.log('Connected to SQLite database');
|
||
|
|
|
||
|
|
// Create tables
|
||
|
|
createDatabaseTables((err) => {
|
||
|
|
if (err) {
|
||
|
|
console.error('Error creating database tables:', err.message);
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
console.log('Database tables created successfully');
|
||
|
|
});
|
||
|
|
});
|
||
|
|
};
|
||
|
|
|
||
|
|
// Initialize database
|
||
|
|
let db;
|
||
|
|
initializeDatabase();
|
||
|
|
|
||
|
|
// Admin view - must be defined before the catch-all UID route
|
||
|
|
app.get('/admin', (req, res) => {
|
||
|
|
res.sendFile(path.join(__dirname, 'views', 'admin.html'));
|
||
|
|
});
|
||
|
|
|
||
|
|
// Public view - handle both root and UID-based routes
|
||
|
|
app.get(['/', '/:uid'], (req, res) => {
|
||
|
|
// Only validate UID if it's provided (not root path)
|
||
|
|
if (req.params.uid) {
|
||
|
|
// Validate UID format
|
||
|
|
if (!/^[a-z0-9-]+$/.test(req.params.uid)) {
|
||
|
|
res.sendFile(path.join(__dirname, 'views', 'index.html'));
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
res.sendFile(path.join(__dirname, 'views', 'index.html'));
|
||
|
|
});
|
||
|
|
|
||
|
|
// Helper function to convert time string to minutes for sorting
|
||
|
|
function timeToMinutes(timeStr) {
|
||
|
|
const match = timeStr.match(/([0-9]+)(?::([0-9]+))?(am|pm)/i);
|
||
|
|
if (!match) return 0;
|
||
|
|
|
||
|
|
let [_, hours, minutes, period] = match;
|
||
|
|
hours = parseInt(hours);
|
||
|
|
minutes = minutes ? parseInt(minutes) : 0;
|
||
|
|
period = period.toLowerCase();
|
||
|
|
|
||
|
|
if (period === 'pm' && hours !== 12) hours += 12;
|
||
|
|
if (period === 'am' && hours === 12) hours = 0;
|
||
|
|
|
||
|
|
return hours * 60 + minutes;
|
||
|
|
}
|
||
|
|
|
||
|
|
// API endpoint to get available dates and times for a specific UID
|
||
|
|
app.get('/api/availability/:uid', (req, res) => {
|
||
|
|
const { uid } = req.params;
|
||
|
|
|
||
|
|
// Verify UID exists
|
||
|
|
db.get('SELECT uid FROM uids WHERE uid = ?', [uid], (err, row) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
if (!row) {
|
||
|
|
res.status(404).json({ error: 'UID not found' });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Get availability for this UID
|
||
|
|
db.all('SELECT date, times FROM availability WHERE uid = ?', [uid], (err, rows) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
const availability = {};
|
||
|
|
rows.forEach(row => {
|
||
|
|
// Handle case where times might be null or undefined
|
||
|
|
if (row.times) {
|
||
|
|
// Split, trim, and sort times chronologically
|
||
|
|
const times = row.times.split(',').map(t => t.trim());
|
||
|
|
times.sort((a, b) => timeToMinutes(a) - timeToMinutes(b));
|
||
|
|
availability[row.date] = times;
|
||
|
|
} else {
|
||
|
|
availability[row.date] = [];
|
||
|
|
}
|
||
|
|
});
|
||
|
|
res.json(availability);
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
// API endpoint to add/update availability for a specific UID
|
||
|
|
app.post('/api/availability/:uid', (req, res) => {
|
||
|
|
const { uid } = req.params;
|
||
|
|
const { date, times } = req.body;
|
||
|
|
|
||
|
|
if (!date) {
|
||
|
|
res.status(400).json({ error: 'Date is required' });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Verify UID exists
|
||
|
|
db.get('SELECT uid FROM uids WHERE uid = ?', [uid], (err, row) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
if (!row) {
|
||
|
|
res.status(404).json({ error: 'UID not found' });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (times.length === 0) {
|
||
|
|
// Delete the entry if no times remain
|
||
|
|
db.run('DELETE FROM availability WHERE uid = ? AND date = ?', [uid, date], (err) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
res.json({ message: 'Availability removed successfully' });
|
||
|
|
});
|
||
|
|
} else {
|
||
|
|
// Sort times before storing
|
||
|
|
const sortedTimes = [...times].sort((a, b) => timeToMinutes(a) - timeToMinutes(b));
|
||
|
|
|
||
|
|
// Insert or replace with the sorted times
|
||
|
|
db.run('INSERT OR REPLACE INTO availability (uid, date, times) VALUES (?, ?, ?)',
|
||
|
|
[uid, date, sortedTimes.join(',')],
|
||
|
|
(err) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
res.json({ message: 'Availability updated successfully' });
|
||
|
|
}
|
||
|
|
);
|
||
|
|
}
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
// API endpoint to get all UIDs
|
||
|
|
app.get('/api/uids', (req, res) => {
|
||
|
|
createDatabaseTables((err) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Get all UIDs
|
||
|
|
db.all('SELECT uid, created_at FROM uids ORDER BY created_at DESC', (err, rows) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: `Failed to fetch UIDs: ${err.message}` });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
res.json(rows || []);
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
// API endpoint to create a new UID
|
||
|
|
app.post('/api/uids', (req, res) => {
|
||
|
|
const { uid } = req.body;
|
||
|
|
|
||
|
|
// Validate UID format (lowercase letters, numbers, and hyphens only)
|
||
|
|
if (!uid || !/^[a-z0-9-]+$/.test(uid)) {
|
||
|
|
res.status(400).json({ error: 'Invalid UID format. Use only lowercase letters, numbers, and hyphens.' });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
createDatabaseTables((err) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
db.run('INSERT INTO uids (uid) VALUES (?)', [uid], function(err) {
|
||
|
|
if (err) {
|
||
|
|
if (err.code === 'SQLITE_CONSTRAINT') {
|
||
|
|
res.status(409).json({ error: 'UID already exists' });
|
||
|
|
} else {
|
||
|
|
res.status(500).json({ error: `Failed to insert UID: ${err.message}` });
|
||
|
|
}
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
res.status(201).json({ uid, created_at: new Date().toISOString() });
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
// API endpoint to delete a UID and its availability
|
||
|
|
app.delete('/api/uids/:uid', (req, res) => {
|
||
|
|
const { uid } = req.params;
|
||
|
|
|
||
|
|
// Begin transaction
|
||
|
|
db.serialize(() => {
|
||
|
|
db.run('BEGIN TRANSACTION');
|
||
|
|
|
||
|
|
// Delete from availability first due to foreign key constraint
|
||
|
|
db.run('DELETE FROM availability WHERE uid = ?', [uid], (err) => {
|
||
|
|
if (err) {
|
||
|
|
db.run('ROLLBACK');
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Then delete from uids
|
||
|
|
db.run('DELETE FROM uids WHERE uid = ?', [uid], function(err) {
|
||
|
|
if (err) {
|
||
|
|
db.run('ROLLBACK');
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
if (this.changes === 0) {
|
||
|
|
db.run('ROLLBACK');
|
||
|
|
res.status(404).json({ error: 'UID not found' });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Commit transaction
|
||
|
|
db.run('COMMIT', (err) => {
|
||
|
|
if (err) {
|
||
|
|
db.run('ROLLBACK');
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
res.json({ message: 'UID deleted successfully' });
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
// API endpoint to flush (delete) all availability entries for a specific UID
|
||
|
|
app.delete('/api/availability/:uid/flush', (req, res) => {
|
||
|
|
const { uid } = req.params;
|
||
|
|
|
||
|
|
// Verify UID exists
|
||
|
|
db.get('SELECT uid FROM uids WHERE uid = ?', [uid], (err, row) => {
|
||
|
|
if (err) {
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
if (!row) {
|
||
|
|
res.status(404).json({ error: 'UID not found' });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Delete all availability for this UID
|
||
|
|
db.run('DELETE FROM availability WHERE uid = ?', [uid], (err) => {
|
||
|
|
if (err) {
|
||
|
|
console.error('Error deleting availability:', err);
|
||
|
|
res.status(500).json({ error: err.message });
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
console.log(`All availability entries for UID ${uid} deleted successfully`);
|
||
|
|
res.json({ message: `All availability entries for UID ${uid} deleted successfully` });
|
||
|
|
});
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
// API endpoint to reset the entire database
|
||
|
|
app.post('/api/reset', async (req, res) => {
|
||
|
|
const fs = require('fs').promises;
|
||
|
|
const path = require('path');
|
||
|
|
const dbDir = 'db';
|
||
|
|
const dbPath = path.join(dbDir, 'freesched.db');
|
||
|
|
|
||
|
|
try {
|
||
|
|
// Close current database connection
|
||
|
|
await new Promise((resolve, reject) => {
|
||
|
|
if (!db) {
|
||
|
|
resolve();
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
db.close((err) => {
|
||
|
|
if (err) reject(err);
|
||
|
|
else resolve();
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
// Ensure db directory exists
|
||
|
|
try {
|
||
|
|
await fs.access(dbDir);
|
||
|
|
} catch {
|
||
|
|
await fs.mkdir(dbDir);
|
||
|
|
}
|
||
|
|
|
||
|
|
// Delete existing database if it exists
|
||
|
|
try {
|
||
|
|
await fs.unlink(dbPath);
|
||
|
|
} catch (err) {
|
||
|
|
if (err.code !== 'ENOENT') throw err; // Ignore if file doesn't exist
|
||
|
|
}
|
||
|
|
|
||
|
|
// Initialize fresh database
|
||
|
|
initializeDatabase();
|
||
|
|
|
||
|
|
// Wait for database to be ready
|
||
|
|
await new Promise((resolve, reject) => {
|
||
|
|
const checkDb = () => {
|
||
|
|
if (!db) {
|
||
|
|
setTimeout(checkDb, 100); // Check every 100ms
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Test database connection
|
||
|
|
db.get('SELECT 1', (err) => {
|
||
|
|
if (err) {
|
||
|
|
setTimeout(checkDb, 100);
|
||
|
|
} else {
|
||
|
|
resolve();
|
||
|
|
}
|
||
|
|
});
|
||
|
|
};
|
||
|
|
checkDb();
|
||
|
|
|
||
|
|
// Set a timeout of 5 seconds
|
||
|
|
setTimeout(() => {
|
||
|
|
reject(new Error('Database initialization timeout'));
|
||
|
|
}, 5000);
|
||
|
|
});
|
||
|
|
|
||
|
|
res.json({ message: 'Database completely wiped and recreated successfully' });
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error in flush-global:', error);
|
||
|
|
|
||
|
|
// Try to reinitialize database
|
||
|
|
try {
|
||
|
|
initializeDatabase();
|
||
|
|
} catch (reinitError) {
|
||
|
|
console.error('Failed to reinitialize database:', reinitError);
|
||
|
|
}
|
||
|
|
|
||
|
|
// Ensure we haven't already sent a response
|
||
|
|
if (!res.headersSent) {
|
||
|
|
res.status(500).json({ error: error.message });
|
||
|
|
}
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
app.listen(port, () => {
|
||
|
|
console.log(`FreeSched app listening at http://localhost:${port}`);
|
||
|
|
});
|