A local-first personal finance app
// @ts-strict-ignore
import { getAccountDb, isAdmin } from '#account-db';
export function getUserByUsername(userName) {
if (!userName || typeof userName !== 'string') {
return null;
}
const { id } =
getAccountDb().first('SELECT id FROM users WHERE user_name = ?', [
userName,
]) || {};
return id || null;
}
export function getUserById(userId) {
if (!userId) {
return null;
}
const { id } =
getAccountDb().first('SELECT * FROM users WHERE id = ?', [userId]) || {};
return id || null;
}
export function getFileById(fileId) {
if (!fileId) {
return null;
}
const { id } =
getAccountDb().first('SELECT * FROM files WHERE files.id = ?', [fileId]) ||
{};
return id || null;
}
export function validateRole(roleId) {
const possibleRoles = ['BASIC', 'ADMIN'];
return possibleRoles.some(a => a === roleId);
}
export function getOwnerCount(): number {
const { ownerCount } = getAccountDb().first(
`SELECT count(*) as ownerCount FROM users WHERE users.user_name <> '' and users.owner = 1`,
) || { ownerCount: 0 };
return ownerCount;
}
export function getOwnerId() {
const { id } =
getAccountDb().first(
`SELECT users.id FROM users WHERE users.user_name <> '' and users.owner = 1`,
) || {};
return id;
}
export function getAllUsers() {
return getAccountDb().all(
`SELECT users.id, user_name as userName, display_name as displayName, enabled, ifnull(owner,0) as owner, role
FROM users
WHERE users.user_name <> ''`,
);
}
export function insertUser(userId, userName, displayName, enabled, role) {
getAccountDb().mutate(
'INSERT INTO users (id, user_name, display_name, enabled, owner, role) VALUES (?, ?, ?, ?, 0, ?)',
[userId, userName, displayName, enabled, role],
);
}
export function updateUserWithRole(
userId,
userName,
displayName,
enabled,
roleId,
) {
getAccountDb().transaction(() => {
getAccountDb().mutate(
'UPDATE users SET user_name = ?, display_name = ?, enabled = ?, role = ? WHERE id = ?',
[userName, displayName, enabled, roleId, userId],
);
if (!enabled) {
getAccountDb().mutate('DELETE FROM sessions WHERE user_id = ?', [userId]);
}
});
}
export function deleteUser(userId) {
return getAccountDb().transaction(() => {
const { changes } = getAccountDb().mutate(
'DELETE FROM users WHERE id = ? and owner = 0',
[userId],
);
if (changes > 0) {
getAccountDb().mutate('DELETE FROM sessions WHERE user_id = ?', [userId]);
}
return changes;
});
}
export function deleteUserAccess(userId) {
try {
return getAccountDb().mutate('DELETE FROM user_access WHERE user_id = ?', [
userId,
]).changes;
} catch (error) {
throw new Error(`Failed to delete user access: ${error.message}`);
}
}
export function transferAllFilesFromUser(ownerId, oldUserId) {
if (!ownerId || !oldUserId) {
throw new Error('Invalid user IDs');
}
try {
getAccountDb().transaction(() => {
const ownerExists = getUserById(ownerId);
if (!ownerExists) {
throw new Error('New owner not found');
}
getAccountDb().mutate('UPDATE files set owner = ? WHERE owner = ?', [
ownerId,
oldUserId,
]);
});
} catch (error) {
throw new Error(`Failed to transfer files: ${error.message}`);
}
}
export function updateFileOwner(ownerId, fileId) {
if (!ownerId || !fileId) {
throw new Error('Invalid parameters');
}
try {
const result = getAccountDb().mutate(
'UPDATE files set owner = ? WHERE id = ?',
[ownerId, fileId],
);
if (result.changes === 0) {
throw new Error('File not found');
}
} catch (error) {
throw new Error(`Failed to update file owner: ${error.message}`);
}
}
export function getUserAccess(fileId, userId, isAdmin) {
return getAccountDb().all(
`SELECT users.id as userId, user_name as userName, files.owner, display_name as displayName
FROM users
JOIN user_access ON user_access.user_id = users.id
JOIN files ON files.id = user_access.file_id
WHERE files.id = ? and (files.owner = ? OR 1 = ?)`,
[fileId, userId, isAdmin ? 1 : 0],
);
}
export function canAccessActiveFile(fileId: string, userId: string): boolean {
const file = getAccountDb().first(
'SELECT id FROM files WHERE id = ? AND deleted = 0',
[fileId],
);
return !!file && (isAdmin(userId) || countUserAccess(fileId, userId) > 0);
}
export function countUserAccess(fileId, userId) {
const { accessCount } =
getAccountDb().first(
`SELECT COUNT(*) as accessCount
FROM files
WHERE files.id = ? AND (files.owner = ? OR EXISTS (
SELECT 1 FROM user_access
WHERE user_access.user_id = ? AND user_access.file_id = ?)
)`,
[fileId, userId, userId, fileId],
) || {};
return accessCount || 0;
}
export function checkFilePermission(fileId, userId) {
return (
getAccountDb().first(
`SELECT 1 as granted
FROM files
WHERE files.id = ? and (files.owner = ?)`,
[fileId, userId],
) || { granted: 0 }
);
}
export function addUserAccess(userId, fileId) {
if (!userId || !fileId) {
throw new Error('Invalid parameters');
}
try {
const userExists = getUserById(userId);
const fileExists = getFileById(fileId);
if (!userExists || !fileExists) {
throw new Error('User or file not found');
}
getAccountDb().mutate(
'INSERT INTO user_access (user_id, file_id) VALUES (?, ?)',
[userId, fileId],
);
} catch (error) {
if (error.message.includes('UNIQUE constraint')) {
throw new Error('Access already exists');
}
throw new Error(`Failed to add user access: ${error.message}`);
}
}
export function deleteUserAccessByFileId(userIds, fileId) {
if (!Array.isArray(userIds) || userIds.length === 0) {
throw new Error('The provided userIds must be a non-empty array.');
}
const CHUNK_SIZE = 999;
let totalChanges = 0;
try {
getAccountDb().transaction(() => {
for (let i = 0; i < userIds.length; i += CHUNK_SIZE) {
const chunk = userIds.slice(i, i + CHUNK_SIZE);
const placeholders = chunk.map(() => '?').join(',');
const sql = `DELETE FROM user_access WHERE user_id IN (${placeholders}) AND file_id = ?`;
const result = getAccountDb().mutate(sql, [...chunk, fileId]);
totalChanges += result.changes;
}
});
} catch (error) {
throw new Error(`Failed to delete user access: ${error.message}`);
}
return totalChanges;
}
export function getAllUserAccess(fileId) {
//This can't be used here until we can create user invite links:
//const isLoginMode = config.get('userCreationMode') === 'login';
const isLoginMode = false;
const joinType = isLoginMode ? 'JOIN' : 'LEFT JOIN';
return getAccountDb().all(
`
SELECT
users.id as userId,
user_name as userName,
display_name as displayName,
CASE WHEN user_access.file_id IS NULL THEN 0 ELSE 1 END as haveAccess,
CASE WHEN files.id IS NULL THEN 0 ELSE 1 END as owner
FROM users
${joinType} user_access ON user_access.file_id = ? AND user_access.user_id = users.id
${joinType} files ON files.id = ? AND files.owner = users.id
WHERE users.enabled = 1
AND users.user_name <> ''
`,
[fileId, fileId],
);
}
export function getOpenIDConfig() {
return (
getAccountDb().first(`SELECT * FROM auth WHERE method = ?`, ['openid']) ||
null
);
}