459 lines
15 KiB
TypeScript
459 lines
15 KiB
TypeScript
import Database from 'better-sqlite3';
|
|
import bcrypt from 'bcrypt';
|
|
import path from 'path';
|
|
import fs from 'fs';
|
|
import { fileURLToPath } from 'url';
|
|
|
|
const __filename = fileURLToPath(import.meta.url);
|
|
const __dirname = path.dirname(__filename);
|
|
|
|
const dbPath = process.env.DB_PATH || path.join(__dirname, '..', 'data', 'questionnaire.db');
|
|
|
|
// Ensure data directory exists
|
|
const dataDir = path.dirname(dbPath);
|
|
if (!fs.existsSync(dataDir)) {
|
|
fs.mkdirSync(dataDir, { recursive: true });
|
|
}
|
|
|
|
export const db = new Database(dbPath);
|
|
|
|
// Enable foreign keys
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
// Types
|
|
export interface User {
|
|
id: number;
|
|
username: string;
|
|
password_hash: string;
|
|
created_at: string;
|
|
}
|
|
|
|
export interface Questionnaire {
|
|
id: number;
|
|
uuid: string;
|
|
slug: string;
|
|
title: string;
|
|
description: string | null;
|
|
og_image: string | null;
|
|
is_private: boolean;
|
|
created_by: number;
|
|
created_at: string;
|
|
creator_name?: string;
|
|
activity_count?: number;
|
|
}
|
|
|
|
export interface Participant {
|
|
id: number;
|
|
questionnaire_id: number;
|
|
name: string;
|
|
phone: string | null;
|
|
token: string;
|
|
created_at: string;
|
|
}
|
|
|
|
export interface Activity {
|
|
id: number;
|
|
questionnaire_id: number;
|
|
name: string;
|
|
description: string | null;
|
|
added_by: string;
|
|
created_at: string;
|
|
upvotes?: number;
|
|
downvotes?: number;
|
|
net_votes?: number;
|
|
comment_count?: number;
|
|
}
|
|
|
|
export interface Vote {
|
|
id: number;
|
|
activity_id: number;
|
|
voter_name: string;
|
|
vote_type: number;
|
|
created_at: string;
|
|
}
|
|
|
|
export interface Comment {
|
|
id: number;
|
|
activity_id: number;
|
|
parent_id: number | null;
|
|
author_name: string;
|
|
content: string;
|
|
created_at: string;
|
|
replies?: Comment[];
|
|
}
|
|
|
|
// Initialize database schema
|
|
export function initializeDatabase(): void {
|
|
// Users table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Questionnaires table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS questionnaires (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
uuid TEXT UNIQUE NOT NULL,
|
|
slug TEXT UNIQUE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
created_by INTEGER,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
)
|
|
`);
|
|
|
|
// Migration: Add slug column if it doesn't exist
|
|
try {
|
|
db.exec(`ALTER TABLE questionnaires ADD COLUMN slug TEXT`);
|
|
db.exec(`UPDATE questionnaires SET slug = uuid WHERE slug IS NULL`);
|
|
} catch (e) {
|
|
// Column already exists, ignore
|
|
}
|
|
|
|
// Migration: Add is_private column if it doesn't exist
|
|
try {
|
|
db.exec(`ALTER TABLE questionnaires ADD COLUMN is_private INTEGER DEFAULT 0`);
|
|
} catch (e) {
|
|
// Column already exists, ignore
|
|
}
|
|
|
|
// Migration: Add og_image column if it doesn't exist
|
|
try {
|
|
db.exec(`ALTER TABLE questionnaires ADD COLUMN og_image TEXT`);
|
|
} catch (e) {
|
|
// Column already exists, ignore
|
|
}
|
|
|
|
// Participants table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS participants (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
questionnaire_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
token TEXT UNIQUE NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Migration: Add phone column to participants if it doesn't exist
|
|
try {
|
|
db.exec(`ALTER TABLE participants ADD COLUMN phone TEXT`);
|
|
} catch (e) {
|
|
// Column already exists, ignore
|
|
}
|
|
|
|
db.exec(`
|
|
CREATE INDEX IF NOT EXISTS idx_participants_questionnaire ON participants(questionnaire_id);
|
|
CREATE INDEX IF NOT EXISTS idx_participants_token ON participants(token);
|
|
`);
|
|
|
|
// Activities table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS activities (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
questionnaire_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
added_by TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Migration: Add description column to activities if it doesn't exist
|
|
try {
|
|
db.exec(`ALTER TABLE activities ADD COLUMN description TEXT`);
|
|
} catch (e) {
|
|
// Column already exists, ignore
|
|
}
|
|
|
|
// Votes table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS votes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
activity_id INTEGER NOT NULL,
|
|
voter_name TEXT NOT NULL,
|
|
vote_type INTEGER NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
|
|
UNIQUE(activity_id, voter_name)
|
|
)
|
|
`);
|
|
|
|
// Comments table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
activity_id INTEGER NOT NULL,
|
|
parent_id INTEGER,
|
|
author_name TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Create indexes
|
|
db.exec(`
|
|
CREATE INDEX IF NOT EXISTS idx_questionnaires_uuid ON questionnaires(uuid);
|
|
CREATE INDEX IF NOT EXISTS idx_activities_questionnaire ON activities(questionnaire_id);
|
|
CREATE INDEX IF NOT EXISTS idx_votes_activity ON votes(activity_id);
|
|
CREATE INDEX IF NOT EXISTS idx_comments_activity ON comments(activity_id);
|
|
CREATE INDEX IF NOT EXISTS idx_comments_parent ON comments(parent_id);
|
|
`);
|
|
|
|
// Create default admin user if none exists
|
|
createDefaultAdmin();
|
|
}
|
|
|
|
function createDefaultAdmin(): void {
|
|
const adminUser = process.env.DEFAULT_ADMIN_USER || 'admin';
|
|
const adminPass = process.env.DEFAULT_ADMIN_PASS || 'admin123';
|
|
|
|
const existingUser = db.prepare('SELECT id FROM users WHERE username = ?').get(adminUser);
|
|
|
|
if (!existingUser) {
|
|
const hash = bcrypt.hashSync(adminPass, 10);
|
|
db.prepare('INSERT INTO users (username, password_hash) VALUES (?, ?)').run(adminUser, hash);
|
|
console.log(`Default admin user '${adminUser}' created.`);
|
|
}
|
|
}
|
|
|
|
// User operations
|
|
export const userOps = {
|
|
findByUsername: (username: string): User | undefined => {
|
|
return db.prepare('SELECT * FROM users WHERE username = ?').get(username) as User | undefined;
|
|
},
|
|
|
|
findById: (id: number): Omit<User, 'password_hash'> | undefined => {
|
|
return db.prepare('SELECT id, username, created_at FROM users WHERE id = ?').get(id) as Omit<User, 'password_hash'> | undefined;
|
|
},
|
|
|
|
getAll: (): Omit<User, 'password_hash'>[] => {
|
|
return db.prepare('SELECT id, username, created_at FROM users ORDER BY created_at DESC').all() as Omit<User, 'password_hash'>[];
|
|
},
|
|
|
|
create: (username: string, password: string): number => {
|
|
const hash = bcrypt.hashSync(password, 10);
|
|
const result = db.prepare('INSERT INTO users (username, password_hash) VALUES (?, ?)').run(username, hash);
|
|
return result.lastInsertRowid as number;
|
|
},
|
|
|
|
updatePassword: (id: number, newPassword: string): void => {
|
|
const hash = bcrypt.hashSync(newPassword, 10);
|
|
db.prepare('UPDATE users SET password_hash = ? WHERE id = ?').run(hash, id);
|
|
},
|
|
|
|
delete: (id: number): void => {
|
|
db.prepare('DELETE FROM users WHERE id = ?').run(id);
|
|
},
|
|
|
|
verifyPassword: (user: User, password: string): boolean => {
|
|
return bcrypt.compareSync(password, user.password_hash);
|
|
},
|
|
|
|
count: (): number => {
|
|
const result = db.prepare('SELECT COUNT(*) as count FROM users').get() as { count: number };
|
|
return result.count;
|
|
},
|
|
};
|
|
|
|
// Questionnaire operations
|
|
export const questionnaireOps = {
|
|
create: (uuid: string, slug: string, title: string, description: string | null, ogImage: string | null, isPrivate: boolean, createdBy: number): number => {
|
|
const result = db.prepare(
|
|
'INSERT INTO questionnaires (uuid, slug, title, description, og_image, is_private, created_by) VALUES (?, ?, ?, ?, ?, ?, ?)'
|
|
).run(uuid, slug, title, description, ogImage, isPrivate ? 1 : 0, createdBy);
|
|
return result.lastInsertRowid as number;
|
|
},
|
|
|
|
findBySlug: (slug: string): Questionnaire | undefined => {
|
|
return db.prepare('SELECT * FROM questionnaires WHERE slug = ?').get(slug) as Questionnaire | undefined;
|
|
},
|
|
|
|
findByUuid: (uuid: string): Questionnaire | undefined => {
|
|
return db.prepare('SELECT * FROM questionnaires WHERE uuid = ?').get(uuid) as Questionnaire | undefined;
|
|
},
|
|
|
|
findById: (id: number): Questionnaire | undefined => {
|
|
return db.prepare('SELECT * FROM questionnaires WHERE id = ?').get(id) as Questionnaire | undefined;
|
|
},
|
|
|
|
getAll: (): Questionnaire[] => {
|
|
return db.prepare(`
|
|
SELECT q.*, u.username as creator_name,
|
|
(SELECT COUNT(*) FROM activities WHERE questionnaire_id = q.id) as activity_count
|
|
FROM questionnaires q
|
|
LEFT JOIN users u ON q.created_by = u.id
|
|
ORDER BY q.created_at DESC
|
|
`).all() as Questionnaire[];
|
|
},
|
|
|
|
update: (id: number, slug: string, title: string, description: string | null, ogImage: string | null, isPrivate: boolean): void => {
|
|
db.prepare('UPDATE questionnaires SET slug = ?, title = ?, description = ?, og_image = ?, is_private = ? WHERE id = ?').run(slug, title, description, ogImage, isPrivate ? 1 : 0, id);
|
|
},
|
|
|
|
delete: (id: number): void => {
|
|
db.prepare('DELETE FROM questionnaires WHERE id = ?').run(id);
|
|
},
|
|
|
|
isSlugAvailable: (slug: string, excludeId?: number): boolean => {
|
|
if (excludeId) {
|
|
const result = db.prepare('SELECT id FROM questionnaires WHERE slug = ? AND id != ?').get(slug, excludeId);
|
|
return !result;
|
|
}
|
|
const result = db.prepare('SELECT id FROM questionnaires WHERE slug = ?').get(slug);
|
|
return !result;
|
|
},
|
|
};
|
|
|
|
// Participant operations
|
|
export const participantOps = {
|
|
create: (questionnaireId: number, name: string, phone: string | null, token: string): number => {
|
|
const result = db.prepare(
|
|
'INSERT INTO participants (questionnaire_id, name, phone, token) VALUES (?, ?, ?, ?)'
|
|
).run(questionnaireId, name, phone, token);
|
|
return result.lastInsertRowid as number;
|
|
},
|
|
|
|
findByToken: (token: string): Participant | undefined => {
|
|
return db.prepare('SELECT * FROM participants WHERE token = ?').get(token) as Participant | undefined;
|
|
},
|
|
|
|
findById: (id: number): Participant | undefined => {
|
|
return db.prepare('SELECT * FROM participants WHERE id = ?').get(id) as Participant | undefined;
|
|
},
|
|
|
|
getByQuestionnaire: (questionnaireId: number): Participant[] => {
|
|
return db.prepare('SELECT * FROM participants WHERE questionnaire_id = ? ORDER BY created_at DESC').all(questionnaireId) as Participant[];
|
|
},
|
|
|
|
delete: (id: number): void => {
|
|
db.prepare('DELETE FROM participants WHERE id = ?').run(id);
|
|
},
|
|
|
|
update: (id: number, name: string, phone: string | null): void => {
|
|
db.prepare('UPDATE participants SET name = ?, phone = ? WHERE id = ?').run(name, phone, id);
|
|
},
|
|
|
|
isTokenAvailable: (token: string): boolean => {
|
|
const result = db.prepare('SELECT id FROM participants WHERE token = ?').get(token);
|
|
return !result;
|
|
},
|
|
};
|
|
|
|
// Activity operations
|
|
export const activityOps = {
|
|
create: (questionnaireId: number, name: string, addedBy: string, description: string | null = null): number => {
|
|
const result = db.prepare(
|
|
'INSERT INTO activities (questionnaire_id, name, added_by, description) VALUES (?, ?, ?, ?)'
|
|
).run(questionnaireId, name, addedBy, description);
|
|
return result.lastInsertRowid as number;
|
|
},
|
|
|
|
findById: (id: number): Activity | undefined => {
|
|
return db.prepare('SELECT * FROM activities WHERE id = ?').get(id) as Activity | undefined;
|
|
},
|
|
|
|
update: (id: number, name: string, description: string | null): void => {
|
|
db.prepare('UPDATE activities SET name = ?, description = ? WHERE id = ?').run(name, description, id);
|
|
},
|
|
|
|
getByQuestionnaire: (questionnaireId: number): Activity[] => {
|
|
const activities = db.prepare(`
|
|
SELECT a.*,
|
|
COALESCE(SUM(CASE WHEN v.vote_type = 1 THEN 1 ELSE 0 END), 0) as upvotes,
|
|
COALESCE(SUM(CASE WHEN v.vote_type = -1 THEN 1 ELSE 0 END), 0) as downvotes,
|
|
COALESCE(SUM(v.vote_type), 0) as net_votes
|
|
FROM activities a
|
|
LEFT JOIN votes v ON a.id = v.activity_id
|
|
WHERE a.questionnaire_id = ?
|
|
GROUP BY a.id
|
|
ORDER BY net_votes DESC, a.created_at ASC
|
|
`).all(questionnaireId) as Activity[];
|
|
|
|
// Add comment counts
|
|
activities.forEach(activity => {
|
|
activity.comment_count = commentOps.countByActivity(activity.id);
|
|
});
|
|
|
|
return activities;
|
|
},
|
|
|
|
delete: (id: number): void => {
|
|
db.prepare('DELETE FROM activities WHERE id = ?').run(id);
|
|
},
|
|
};
|
|
|
|
// Vote operations
|
|
export const voteOps = {
|
|
upsert: (activityId: number, voterName: string, voteType: number): { action: string; voteType: number } => {
|
|
const existing = db.prepare(
|
|
'SELECT id, vote_type FROM votes WHERE activity_id = ? AND voter_name = ?'
|
|
).get(activityId, voterName) as { id: number; vote_type: number } | undefined;
|
|
|
|
if (existing) {
|
|
if (existing.vote_type === voteType) {
|
|
db.prepare('DELETE FROM votes WHERE id = ?').run(existing.id);
|
|
return { action: 'removed', voteType: 0 };
|
|
} else {
|
|
db.prepare('UPDATE votes SET vote_type = ? WHERE id = ?').run(voteType, existing.id);
|
|
return { action: 'changed', voteType };
|
|
}
|
|
} else {
|
|
db.prepare(
|
|
'INSERT INTO votes (activity_id, voter_name, vote_type) VALUES (?, ?, ?)'
|
|
).run(activityId, voterName, voteType);
|
|
return { action: 'added', voteType };
|
|
}
|
|
},
|
|
|
|
getByQuestionnaireAndVoter: (questionnaireId: number, voterName: string): { activity_id: number; vote_type: number }[] => {
|
|
return db.prepare(`
|
|
SELECT v.activity_id, v.vote_type
|
|
FROM votes v
|
|
JOIN activities a ON v.activity_id = a.id
|
|
WHERE a.questionnaire_id = ? AND v.voter_name = ?
|
|
`).all(questionnaireId, voterName) as { activity_id: number; vote_type: number }[];
|
|
},
|
|
};
|
|
|
|
// Comment operations
|
|
export const commentOps = {
|
|
create: (activityId: number, authorName: string, content: string, parentId: number | null = null): number => {
|
|
const result = db.prepare(
|
|
'INSERT INTO comments (activity_id, author_name, content, parent_id) VALUES (?, ?, ?, ?)'
|
|
).run(activityId, authorName, content, parentId);
|
|
return result.lastInsertRowid as number;
|
|
},
|
|
|
|
getByActivity: (activityId: number): Comment[] => {
|
|
return db.prepare(`
|
|
SELECT * FROM comments
|
|
WHERE activity_id = ?
|
|
ORDER BY created_at ASC
|
|
`).all(activityId) as Comment[];
|
|
},
|
|
|
|
getById: (id: number): Comment | undefined => {
|
|
return db.prepare('SELECT * FROM comments WHERE id = ?').get(id) as Comment | undefined;
|
|
},
|
|
|
|
countByActivity: (activityId: number): number => {
|
|
const result = db.prepare('SELECT COUNT(*) as count FROM comments WHERE activity_id = ?').get(activityId) as { count: number };
|
|
return result.count;
|
|
},
|
|
|
|
delete: (id: number): void => {
|
|
db.prepare('DELETE FROM comments WHERE id = ?').run(id);
|
|
},
|
|
};
|
|
|