database.js•4.51 kB
import Database from 'better-sqlite3';
import { randomUUID } from 'node:crypto';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
// Initialize SQLite database
const db = new Database(path.join(__dirname, 'weather.db'));
// Enable foreign keys
db.pragma('foreign_keys = ON');
// Initialize database schema
function initDatabase() {
// Create users table with OAuth support
db.exec(`
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
github_id TEXT UNIQUE,
github_username TEXT,
github_email TEXT,
name TEXT,
avatar_url TEXT,
access_token TEXT,
refresh_token TEXT,
token_expires_at DATETIME,
auth_token TEXT UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`);
// Create locations table
db.exec(`
CREATE TABLE IF NOT EXISTS locations (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
label TEXT NOT NULL,
location_name TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE(user_id, label)
)
`);
console.log('✅ Database initialized');
}
// User management functions
export function createUser(name = 'Default User') {
const userId = randomUUID();
const authToken = randomUUID();
const stmt = db.prepare('INSERT INTO users (id, auth_token, name) VALUES (?, ?, ?)');
stmt.run(userId, authToken, name);
return { userId, authToken };
}
export function getUserByToken(authToken) {
const stmt = db.prepare('SELECT * FROM users WHERE auth_token = ?');
return stmt.get(authToken);
}
// OAuth user management functions
export function upsertOAuthUser(githubId, userData) {
const userId = randomUUID();
const stmt = db.prepare(`
INSERT INTO users (id, github_id, github_username, github_email, name, avatar_url, access_token, refresh_token, token_expires_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
ON CONFLICT(github_id)
DO UPDATE SET
github_username = excluded.github_username,
github_email = excluded.github_email,
name = excluded.name,
avatar_url = excluded.avatar_url,
access_token = excluded.access_token,
refresh_token = excluded.refresh_token,
token_expires_at = excluded.token_expires_at,
updated_at = CURRENT_TIMESTAMP
RETURNING *
`);
return stmt.get(
userId,
githubId,
userData.username,
userData.email,
userData.name,
userData.avatar_url,
userData.access_token,
userData.refresh_token,
userData.token_expires_at
);
}
export function getUserByAccessToken(accessToken) {
const stmt = db.prepare('SELECT * FROM users WHERE access_token = ?');
return stmt.get(accessToken);
}
export function getUserByGithubId(githubId) {
const stmt = db.prepare('SELECT * FROM users WHERE github_id = ?');
return stmt.get(githubId);
}
// Location management functions
export function addLocation(userId, label, locationName) {
const locationId = randomUUID();
const stmt = db.prepare(
'INSERT INTO locations (id, user_id, label, location_name) VALUES (?, ?, ?, ?) ' +
'ON CONFLICT(user_id, label) DO UPDATE SET location_name = excluded.location_name'
);
try {
stmt.run(locationId, userId, label.toLowerCase(), locationName);
return { success: true, locationId, label: label.toLowerCase(), locationName };
} catch (error) {
return { success: false, error: error.message };
}
}
export function getUserLocations(userId) {
const stmt = db.prepare('SELECT * FROM locations WHERE user_id = ? ORDER BY created_at DESC');
return stmt.all(userId);
}
export function getLocationByLabel(userId, label) {
const stmt = db.prepare('SELECT * FROM locations WHERE user_id = ? AND label = ?');
return stmt.get(userId, label.toLowerCase());
}
export function deleteLocation(userId, label) {
const stmt = db.prepare('DELETE FROM locations WHERE user_id = ? AND label = ?');
const result = stmt.run(userId, label.toLowerCase());
return result.changes > 0;
}
export function deleteAllLocations(userId) {
const stmt = db.prepare('DELETE FROM locations WHERE user_id = ?');
const result = stmt.run(userId);
return result.changes;
}
// Initialize the database on module load
initDatabase();
export default db;