283 lines
9.9 KiB
TypeScript
283 lines
9.9 KiB
TypeScript
import { users, organizations, forms, tasks, safeUserColumns, type User, type SafeUser, type Organization, type Task } from "@shared/schema";
|
|
import { taskRelations, type TaskRelation } from "@shared/schema";
|
|
import { taskReminders, type TaskReminder, type InsertTaskReminder } from "@shared/schema";
|
|
import { delayedStatusChanges, type DelayedStatusChange, type InsertDelayedStatusChange } from "@shared/schema";
|
|
import { taskAuditLog, type TaskAuditLog, type InsertTaskAuditLog } from "@shared/schema";
|
|
import { roleMembers, roles } from "@shared/schema";
|
|
import { db } from "../db";
|
|
import { eq, and, or, desc, asc, sql, ilike, lte } from "drizzle-orm";
|
|
import crypto from "crypto";
|
|
import { ContentStorage } from "./content.storage";
|
|
export class TaskMetaStorage extends ContentStorage {
|
|
// Task Relations
|
|
async upsertTaskRelation(data: { parentTaskId: number; childTaskId: number; fieldId?: number | null; organizationId: number }): Promise<TaskRelation> {
|
|
const [existing] = await db
|
|
.select()
|
|
.from(taskRelations)
|
|
.where(and(
|
|
eq(taskRelations.parentTaskId, data.parentTaskId),
|
|
eq(taskRelations.childTaskId, data.childTaskId),
|
|
data.fieldId != null
|
|
? eq(taskRelations.fieldId, data.fieldId)
|
|
: sql`${taskRelations.fieldId} IS NULL`,
|
|
));
|
|
if (existing) return existing;
|
|
const [row] = await db
|
|
.insert(taskRelations)
|
|
.values({ ...data, fieldId: data.fieldId ?? null })
|
|
.returning();
|
|
return row;
|
|
}
|
|
|
|
async deleteTaskRelationByField(fieldId: number, taskId: number, organizationId: number): Promise<void> {
|
|
await db.delete(taskRelations)
|
|
.where(and(
|
|
eq(taskRelations.fieldId, fieldId),
|
|
eq(taskRelations.organizationId, organizationId),
|
|
// taskId could be parent or child
|
|
sql`(${taskRelations.parentTaskId} = ${taskId} OR ${taskRelations.childTaskId} = ${taskId})`,
|
|
));
|
|
}
|
|
|
|
async getTaskRelations(taskId: number, organizationId: number): Promise<TaskRelation[]> {
|
|
return await db
|
|
.select()
|
|
.from(taskRelations)
|
|
.where(and(
|
|
eq(taskRelations.organizationId, organizationId),
|
|
sql`(${taskRelations.parentTaskId} = ${taskId} OR ${taskRelations.childTaskId} = ${taskId})`,
|
|
));
|
|
}
|
|
|
|
// === Task Reminders ===
|
|
|
|
async getTaskReminders(taskId: number, organizationId: number): Promise<TaskReminder[]> {
|
|
return db
|
|
.select()
|
|
.from(taskReminders)
|
|
.where(and(
|
|
eq(taskReminders.taskId, taskId),
|
|
eq(taskReminders.organizationId, organizationId),
|
|
eq(taskReminders.isSent, false),
|
|
))
|
|
.orderBy(asc(taskReminders.remindAt));
|
|
}
|
|
|
|
async createTaskReminder(data: InsertTaskReminder): Promise<TaskReminder> {
|
|
const [reminder] = await db
|
|
.insert(taskReminders)
|
|
.values(data)
|
|
.returning();
|
|
return reminder;
|
|
}
|
|
|
|
async deleteTaskReminder(id: number, organizationId: number): Promise<void> {
|
|
await db
|
|
.delete(taskReminders)
|
|
.where(and(
|
|
eq(taskReminders.id, id),
|
|
eq(taskReminders.organizationId, organizationId),
|
|
));
|
|
}
|
|
|
|
async getDueReminders(): Promise<TaskReminder[]> {
|
|
return db
|
|
.select()
|
|
.from(taskReminders)
|
|
.where(and(
|
|
eq(taskReminders.isSent, false),
|
|
lte(taskReminders.remindAt, new Date()),
|
|
));
|
|
}
|
|
|
|
async markReminderSent(id: number): Promise<void> {
|
|
await db
|
|
.update(taskReminders)
|
|
.set({ isSent: true })
|
|
.where(eq(taskReminders.id, id));
|
|
}
|
|
|
|
// Отложенная смена статуса (автоматизации: ctx.tasks.scheduleStatusChange)
|
|
async createDelayedStatusChange(data: InsertDelayedStatusChange): Promise<DelayedStatusChange> {
|
|
const [row] = await db.insert(delayedStatusChanges).values(data).returning();
|
|
return row;
|
|
}
|
|
|
|
async getDueDelayedStatusChanges(limit = 50): Promise<DelayedStatusChange[]> {
|
|
return db
|
|
.select()
|
|
.from(delayedStatusChanges)
|
|
.where(and(
|
|
sql`${delayedStatusChanges.executedAt} IS NULL`,
|
|
lte(delayedStatusChanges.runAt, new Date()),
|
|
))
|
|
.orderBy(asc(delayedStatusChanges.runAt))
|
|
.limit(limit);
|
|
}
|
|
|
|
async markDelayedStatusChangeExecuted(id: number): Promise<void> {
|
|
await db
|
|
.update(delayedStatusChanges)
|
|
.set({ executedAt: new Date() })
|
|
.where(eq(delayedStatusChanges.id, id));
|
|
}
|
|
|
|
// Массовые выборки пользователей — только безопасные колонки (без хэша пароля и токенов)
|
|
async getUsersByRole(role: string, organizationId: number): Promise<SafeUser[]> {
|
|
return db
|
|
.select(safeUserColumns)
|
|
.from(users)
|
|
.where(and(
|
|
eq(users.organizationId, organizationId),
|
|
eq(users.appRole, role),
|
|
eq(users.isActive, true),
|
|
));
|
|
}
|
|
|
|
async getUsersByOrgRoleId(roleId: number, organizationId: number): Promise<SafeUser[]> {
|
|
return db
|
|
.select(safeUserColumns)
|
|
.from(users)
|
|
.innerJoin(roleMembers, eq(roleMembers.userId, users.id))
|
|
.innerJoin(roles, eq(roleMembers.roleId, roles.id))
|
|
.where(and(
|
|
eq(roleMembers.roleId, roleId),
|
|
eq(roles.organizationId, organizationId),
|
|
eq(users.isActive, true),
|
|
));
|
|
}
|
|
|
|
// === Task Audit Log ===
|
|
async addTaskAuditLog(entry: InsertTaskAuditLog): Promise<TaskAuditLog> {
|
|
const [row] = await db.insert(taskAuditLog).values(entry).returning();
|
|
return row;
|
|
}
|
|
|
|
async getTaskAuditLog(taskId: number, organizationId: number): Promise<TaskAuditLog[]> {
|
|
return db
|
|
.select()
|
|
.from(taskAuditLog)
|
|
.where(and(
|
|
eq(taskAuditLog.taskId, taskId),
|
|
eq(taskAuditLog.organizationId, organizationId),
|
|
))
|
|
.orderBy(desc(taskAuditLog.createdAt));
|
|
}
|
|
|
|
// === Super Admins ===
|
|
async getSuperAdminByEmail(email: string) {
|
|
const { superAdmins } = await import('@shared/schema');
|
|
const [row] = await db.select().from(superAdmins).where(ilike(superAdmins.email, email));
|
|
return row;
|
|
}
|
|
|
|
async createSuperAdmin(data: import('@shared/schema').InsertSuperAdmin) {
|
|
const { superAdmins } = await import('@shared/schema');
|
|
const [row] = await db.insert(superAdmins).values(data).returning();
|
|
return row;
|
|
}
|
|
|
|
async getSuperAdminCount(): Promise<number> {
|
|
const { superAdmins } = await import('@shared/schema');
|
|
const [row] = await db.select({ count: sql<number>`count(*)::int` }).from(superAdmins);
|
|
return row?.count ?? 0;
|
|
}
|
|
|
|
async getSuperAdminById(id: number) {
|
|
const { superAdmins } = await import('@shared/schema');
|
|
const [row] = await db.select().from(superAdmins).where(eq(superAdmins.id, id));
|
|
return row;
|
|
}
|
|
|
|
async getAllSuperAdmins() {
|
|
const { superAdmins } = await import('@shared/schema');
|
|
return db.select().from(superAdmins).orderBy(asc(superAdmins.createdAt));
|
|
}
|
|
|
|
async deleteSuperAdmin(id: number): Promise<void> {
|
|
const { superAdmins } = await import('@shared/schema');
|
|
await db.delete(superAdmins).where(eq(superAdmins.id, id));
|
|
}
|
|
|
|
async updateSuperAdminLastLogin(id: number): Promise<void> {
|
|
const { superAdmins } = await import('@shared/schema');
|
|
await db.update(superAdmins).set({ lastLogin: new Date() }).where(eq(superAdmins.id, id));
|
|
}
|
|
|
|
async updateSuperAdminPassword(id: number, passwordHash: string): Promise<void> {
|
|
const { superAdmins } = await import('@shared/schema');
|
|
await db.update(superAdmins).set({ passwordHash }).where(eq(superAdmins.id, id));
|
|
}
|
|
|
|
async getAllOrganizationsWithStats() {
|
|
const { organizationBilling } = await import('@shared/schema');
|
|
|
|
const [orgs, allUserStats, allTaskStats, allBilling] = await Promise.all([
|
|
db.select().from(organizations).orderBy(asc(organizations.createdAt)),
|
|
|
|
// Aggregate user stats per org in one query
|
|
db
|
|
.select({
|
|
organizationId: users.organizationId,
|
|
total: sql<number>`count(*)::int`,
|
|
active: sql<number>`count(*) filter (where ${users.isActive} = true)::int`,
|
|
lastLogin: sql<string | null>`max(${users.lastLogin})`,
|
|
})
|
|
.from(users)
|
|
.groupBy(users.organizationId),
|
|
|
|
// Aggregate task stats per org in one query (via forms join)
|
|
db
|
|
.select({
|
|
organizationId: forms.organizationId,
|
|
total: sql<number>`count(*)::int`,
|
|
lastCreatedAt: sql<string | null>`max(${tasks.createdAt})`,
|
|
})
|
|
.from(tasks)
|
|
.innerJoin(forms, eq(tasks.formId, forms.id))
|
|
.groupBy(forms.organizationId),
|
|
|
|
// All billing settings in one query
|
|
db.select().from(organizationBilling),
|
|
]);
|
|
|
|
// Index results by organizationId for O(1) lookup
|
|
const userStatsByOrg = new Map(allUserStats.map(r => [r.organizationId, r]));
|
|
const taskStatsByOrg = new Map(allTaskStats.map(r => [r.organizationId, r]));
|
|
const billingByOrg = new Map(allBilling.map(r => [r.organizationId, r]));
|
|
|
|
return orgs.map(org => {
|
|
const userStats = userStatsByOrg.get(org.id);
|
|
const taskStats = taskStatsByOrg.get(org.id);
|
|
const billing = billingByOrg.get(org.id);
|
|
|
|
const lastTaskAt = taskStats?.lastCreatedAt ? new Date(taskStats.lastCreatedAt) : null;
|
|
const lastLoginAt = userStats?.lastLogin ? new Date(userStats.lastLogin) : null;
|
|
const lastActivity = lastTaskAt && lastLoginAt
|
|
? (lastTaskAt > lastLoginAt ? lastTaskAt : lastLoginAt)
|
|
: (lastTaskAt ?? lastLoginAt);
|
|
|
|
return {
|
|
...org,
|
|
totalUsers: userStats?.total ?? 0,
|
|
activeUsers: userStats?.active ?? 0,
|
|
totalTasks: taskStats?.total ?? 0,
|
|
lastActivity,
|
|
billingBalance: billing?.balance ?? "0",
|
|
billingCurrency: billing?.currency ?? "RUB",
|
|
};
|
|
});
|
|
}
|
|
|
|
async updateOrganization(id: number, updates: Partial<import('@shared/schema').Organization>) {
|
|
const [row] = await db
|
|
.update(organizations)
|
|
.set({ ...updates, updatedAt: new Date() })
|
|
.where(eq(organizations.id, id))
|
|
.returning();
|
|
return row;
|
|
}
|
|
|
|
// =====================
|
|
}
|