567 lines
21 KiB
TypeScript
567 lines
21 KiB
TypeScript
import { forms, formTabs, formFields, formStatuses, statusTransitions, tasks, bookmarks, type Form, type FormTab, type FormField, type FormStatus, type StatusTransition, type InsertForm, type InsertFormTab, type InsertFormField, type InsertFormStatus, type InsertStatusTransition } from "@shared/schema";
|
||
import { db } from "../db";
|
||
import { eq, and, desc, exists, sql, isNull } from "drizzle-orm";
|
||
import crypto from "crypto";
|
||
import { UserProfileStorage } from "./user-profile.storage";
|
||
import { invalidateAccessibleTasksForOrg } from "../utils/cache";
|
||
export class FormsStorage extends UserProfileStorage {
|
||
// Forms
|
||
async getForm(id: number, organizationId: number): Promise<Form | undefined> {
|
||
const [form] = await db
|
||
.select()
|
||
.from(forms)
|
||
.where(and(eq(forms.id, id), eq(forms.organizationId, organizationId)));
|
||
return form || undefined;
|
||
}
|
||
|
||
async getFormsByOrganization(organizationId: number): Promise<Form[]> {
|
||
return await db
|
||
.select()
|
||
.from(forms)
|
||
.where(eq(forms.organizationId, organizationId))
|
||
.orderBy(desc(forms.createdAt));
|
||
}
|
||
|
||
async createForm(insertForm: InsertForm): Promise<Form> {
|
||
const [form] = await db
|
||
.insert(forms)
|
||
.values(insertForm)
|
||
.returning();
|
||
return form;
|
||
}
|
||
|
||
async updateForm(id: number, organizationId: number, updates: Partial<Form>): Promise<Form> {
|
||
const [form] = await db
|
||
.update(forms)
|
||
.set({ ...updates, updatedAt: new Date() })
|
||
.where(and(eq(forms.id, id), eq(forms.organizationId, organizationId)))
|
||
.returning();
|
||
// visibility/createdBy меняют доступ к задачам формы — сброс кэша по организации
|
||
if ('visibility' in updates || 'createdBy' in updates) {
|
||
invalidateAccessibleTasksForOrg(organizationId);
|
||
}
|
||
return form;
|
||
}
|
||
|
||
async deleteForm(id: number, organizationId: number): Promise<void> {
|
||
// Cleanup RAG embeddings for this form and all its tasks/messages
|
||
const { deleteRagEmbeddingsByForm } = await import('../services/embedding.service.js');
|
||
await deleteRagEmbeddingsByForm(organizationId, id);
|
||
|
||
// Удаляем закладки, ссылающиеся на эту форму
|
||
await db
|
||
.delete(bookmarks)
|
||
.where(eq(bookmarks.formId, id));
|
||
|
||
// Удаляем все задачи формы (каскадно удалятся их сообщения, значения полей и т.д.)
|
||
await db
|
||
.delete(tasks)
|
||
.where(eq(tasks.formId, id));
|
||
|
||
// Теперь удаляем саму форму
|
||
await db
|
||
.delete(forms)
|
||
.where(and(eq(forms.id, id), eq(forms.organizationId, organizationId)));
|
||
}
|
||
|
||
async getTasksCountByForm(formId: number): Promise<{ total: number; active: number; completed: number }> {
|
||
const result = await db
|
||
.select({
|
||
total: sql<number>`count(*)::int`,
|
||
active: sql<number>`count(*) filter (where is_completed = false)::int`,
|
||
completed: sql<number>`count(*) filter (where is_completed = true)::int`
|
||
})
|
||
.from(tasks)
|
||
.where(eq(tasks.formId, formId));
|
||
return {
|
||
total: result[0]?.total || 0,
|
||
active: result[0]?.active || 0,
|
||
completed: result[0]?.completed || 0
|
||
};
|
||
}
|
||
|
||
// Form Tabs
|
||
async getFormTabs(formId: number, organizationId: number): Promise<FormTab[]> {
|
||
return await db
|
||
.select()
|
||
.from(formTabs)
|
||
.innerJoin(forms, eq(formTabs.formId, forms.id))
|
||
.where(and(eq(formTabs.formId, formId), eq(forms.organizationId, organizationId)))
|
||
.orderBy(formTabs.displayOrder)
|
||
.then(results => results.map(r => r.form_tabs));
|
||
}
|
||
|
||
async getFormTabsByCreationOrder(formId: number, organizationId: number): Promise<FormTab[]> {
|
||
return await db
|
||
.select()
|
||
.from(formTabs)
|
||
.innerJoin(forms, eq(formTabs.formId, forms.id))
|
||
.where(and(eq(formTabs.formId, formId), eq(forms.organizationId, organizationId)))
|
||
.orderBy(formTabs.id)
|
||
.then(results => results.map(r => r.form_tabs));
|
||
}
|
||
|
||
async getFormTab(id: number, formId: number, organizationId: number): Promise<FormTab | undefined> {
|
||
const results = await db
|
||
.select()
|
||
.from(formTabs)
|
||
.innerJoin(forms, eq(formTabs.formId, forms.id))
|
||
.where(and(eq(formTabs.id, id), eq(formTabs.formId, formId), eq(forms.organizationId, organizationId)));
|
||
return results[0]?.form_tabs;
|
||
}
|
||
|
||
async createFormTab(insertTab: InsertFormTab): Promise<FormTab> {
|
||
const [tab] = await db
|
||
.insert(formTabs)
|
||
.values(insertTab)
|
||
.returning();
|
||
return tab;
|
||
}
|
||
|
||
async updateFormTab(id: number, formId: number, organizationId: number, updates: Partial<FormTab>): Promise<FormTab> {
|
||
const [tab] = await db
|
||
.update(formTabs)
|
||
.set(updates)
|
||
.where(and(
|
||
eq(formTabs.id, id),
|
||
eq(formTabs.formId, formId),
|
||
exists(db.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
))
|
||
.returning();
|
||
return tab;
|
||
}
|
||
|
||
async deleteFormTab(id: number, formId: number, organizationId: number): Promise<void> {
|
||
await db
|
||
.delete(formTabs)
|
||
.where(and(
|
||
eq(formTabs.id, id),
|
||
eq(formTabs.formId, formId),
|
||
exists(db.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
));
|
||
}
|
||
|
||
async bulkSaveFormTabs(
|
||
formId: number,
|
||
organizationId: number,
|
||
tabs: Array<{ id?: number; name: string; code: string; type: string; position?: number; displayOrder?: number; tableColumns?: unknown }>
|
||
): Promise<FormTab[]> {
|
||
return await db.transaction(async (tx) => {
|
||
const existingTabs = await tx
|
||
.select()
|
||
.from(formTabs)
|
||
.where(eq(formTabs.formId, formId));
|
||
|
||
const incomingIds = tabs.filter(t => t.id && t.id > 0).map(t => t.id!);
|
||
const tabsToDelete = existingTabs.filter(e => !incomingIds.includes(e.id));
|
||
|
||
for (const tab of tabsToDelete) {
|
||
await tx.delete(formTabs).where(and(eq(formTabs.id, tab.id), eq(formTabs.formId, formId)));
|
||
}
|
||
|
||
const savedTabs: FormTab[] = [];
|
||
for (let i = 0; i < tabs.length; i++) {
|
||
const tab = tabs[i];
|
||
const tabData = {
|
||
formId,
|
||
name: tab.name || '',
|
||
code: tab.code || `tab_${i + 1}`,
|
||
type: tab.type || 'fields',
|
||
position: tab.position ?? i,
|
||
displayOrder: tab.displayOrder ?? i,
|
||
tableColumns: tab.tableColumns || null,
|
||
};
|
||
|
||
if (tab.id && tab.id > 0) {
|
||
const [updated] = await tx
|
||
.update(formTabs)
|
||
.set(tabData)
|
||
.where(and(eq(formTabs.id, tab.id), eq(formTabs.formId, formId)))
|
||
.returning();
|
||
if (updated) savedTabs.push(updated);
|
||
} else {
|
||
const [created] = await tx
|
||
.insert(formTabs)
|
||
.values(tabData)
|
||
.returning();
|
||
savedTabs.push(created);
|
||
}
|
||
}
|
||
return savedTabs;
|
||
});
|
||
}
|
||
|
||
// Form Fields
|
||
async getFormFields(formId: number, organizationId: number): Promise<FormField[]> {
|
||
return await db
|
||
.select()
|
||
.from(formFields)
|
||
.innerJoin(forms, eq(formFields.formId, forms.id))
|
||
.where(and(eq(formFields.formId, formId), eq(forms.organizationId, organizationId), isNull(formFields.deletedAt)))
|
||
.orderBy(formFields.displayOrder)
|
||
.then(results => results.map(r => r.form_fields));
|
||
}
|
||
|
||
async getFormFieldsByCreationOrder(formId: number, organizationId: number): Promise<FormField[]> {
|
||
return await db
|
||
.select()
|
||
.from(formFields)
|
||
.innerJoin(forms, eq(formFields.formId, forms.id))
|
||
.where(and(eq(formFields.formId, formId), eq(forms.organizationId, organizationId), isNull(formFields.deletedAt)))
|
||
.orderBy(formFields.id)
|
||
.then(results => results.map(r => r.form_fields));
|
||
}
|
||
|
||
// Архив удалённых (мягко) полей формы — для восстановления и блока «Удалённые поля» в задаче
|
||
async getDeletedFormFields(formId: number, organizationId: number): Promise<FormField[]> {
|
||
return await db
|
||
.select()
|
||
.from(formFields)
|
||
.innerJoin(forms, eq(formFields.formId, forms.id))
|
||
.where(and(eq(formFields.formId, formId), eq(forms.organizationId, organizationId), sql`${formFields.deletedAt} IS NOT NULL`))
|
||
.orderBy(desc(formFields.deletedAt))
|
||
.then(results => results.map(r => r.form_fields));
|
||
}
|
||
|
||
// Восстановление удалённого поля. Код проверяем на конфликт с активным полем формы.
|
||
async restoreFormField(id: number, formId: number, organizationId: number): Promise<FormField> {
|
||
const [field] = await db
|
||
.select()
|
||
.from(formFields)
|
||
.innerJoin(forms, eq(formFields.formId, forms.id))
|
||
.where(and(eq(formFields.id, id), eq(formFields.formId, formId), eq(forms.organizationId, organizationId)));
|
||
if (!field?.form_fields) throw new Error(`Поле ${id} не найдено`);
|
||
if (!field.form_fields.deletedAt) throw new Error(`Поле "${field.form_fields.name}" не удалено`);
|
||
|
||
const [conflict] = await db
|
||
.select({ id: formFields.id, name: formFields.name })
|
||
.from(formFields)
|
||
.where(and(
|
||
eq(formFields.formId, formId),
|
||
eq(formFields.code, field.form_fields.code),
|
||
isNull(formFields.deletedAt),
|
||
sql`${formFields.id} <> ${id}`,
|
||
));
|
||
if (conflict) {
|
||
throw new Error(`Активное поле с кодом "${field.form_fields.code}" уже существует («${conflict.name}»). Переименуйте его или удалите.`);
|
||
}
|
||
|
||
const [restored] = await db
|
||
.update(formFields)
|
||
.set({ deletedAt: null })
|
||
.where(eq(formFields.id, id))
|
||
.returning();
|
||
return restored;
|
||
}
|
||
|
||
async getFormFieldById(id: number, organizationId: number): Promise<FormField | undefined> {
|
||
const [field] = await db
|
||
.select({ field: formFields })
|
||
.from(formFields)
|
||
.innerJoin(forms, eq(formFields.formId, forms.id))
|
||
.where(and(eq(formFields.id, id), eq(forms.organizationId, organizationId)));
|
||
return field?.field || undefined;
|
||
}
|
||
|
||
async createFormField(insertField: InsertFormField): Promise<FormField> {
|
||
const [field] = await db
|
||
.insert(formFields)
|
||
.values(insertField)
|
||
.returning();
|
||
return field;
|
||
}
|
||
|
||
async updateFormField(id: number, formId: number, organizationId: number, updates: Partial<FormField>): Promise<FormField> {
|
||
const [field] = await db
|
||
.update(formFields)
|
||
.set(updates)
|
||
.where(and(
|
||
eq(formFields.id, id),
|
||
eq(formFields.formId, formId),
|
||
exists(db.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
))
|
||
.returning();
|
||
return field;
|
||
}
|
||
|
||
async deleteFormField(id: number, formId: number, organizationId: number): Promise<void> {
|
||
// Мягкое удаление: значения в задачах сохраняются, поле доступно в архиве
|
||
await db
|
||
.update(formFields)
|
||
.set({ deletedAt: new Date() })
|
||
.where(and(
|
||
eq(formFields.id, id),
|
||
eq(formFields.formId, formId),
|
||
isNull(formFields.deletedAt),
|
||
// Убедимся что form принадлежит организации через subquery
|
||
exists(db.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
));
|
||
}
|
||
|
||
async bulkSaveFormFields(
|
||
formId: number,
|
||
organizationId: number,
|
||
fieldIdsToDelete: number[],
|
||
fields: Array<{
|
||
id?: number;
|
||
name: string;
|
||
code: string;
|
||
type: string;
|
||
isRequired?: boolean;
|
||
placeholder?: string | null;
|
||
defaultValue?: string | null;
|
||
validationRules?: unknown;
|
||
requiredRules?: unknown;
|
||
immutableRules?: unknown;
|
||
visibilityRules?: unknown;
|
||
options?: unknown;
|
||
position?: number | null;
|
||
displayOrder?: number | null;
|
||
tabId?: number | null;
|
||
linkedFormId?: number | null;
|
||
taskRelationType?: string | null;
|
||
buttonActionType?: string | null;
|
||
buttonUrl?: string | null;
|
||
buttonFormId?: number | null;
|
||
companyAutofill?: unknown;
|
||
maxFileCount?: number | null;
|
||
maxFileSizeMB?: number | null;
|
||
autoProlongation?: boolean | null;
|
||
autoProlongationLabel?: string | null;
|
||
linkToUserProfile?: boolean | null;
|
||
allowMultiple?: boolean | null;
|
||
ganttRole?: string | null;
|
||
}>
|
||
): Promise<FormField[]> {
|
||
return await db.transaction(async (tx) => {
|
||
for (const fieldId of fieldIdsToDelete) {
|
||
// Мягкое удаление: значения в задачах сохраняются, поле уходит в архив
|
||
await tx
|
||
.update(formFields)
|
||
.set({ deletedAt: new Date() })
|
||
.where(and(eq(formFields.id, fieldId), eq(formFields.formId, formId), isNull(formFields.deletedAt)));
|
||
}
|
||
|
||
const resultFields: FormField[] = [];
|
||
for (let i = 0; i < fields.length; i++) {
|
||
const field = fields[i];
|
||
const fieldData = {
|
||
formId,
|
||
name: field.name,
|
||
code: field.code,
|
||
type: field.type,
|
||
isRequired: field.isRequired || false,
|
||
placeholder: field.placeholder || null,
|
||
defaultValue: field.defaultValue || null,
|
||
validationRules: field.validationRules || null,
|
||
requiredRules: field.requiredRules || null,
|
||
immutableRules: field.immutableRules || null,
|
||
visibilityRules: field.visibilityRules || null,
|
||
options: field.options || null,
|
||
position: field.position ?? i,
|
||
displayOrder: field.displayOrder ?? i,
|
||
tabId: field.tabId || null,
|
||
linkedFormId: field.linkedFormId || null,
|
||
taskRelationType: field.taskRelationType || null,
|
||
buttonActionType: field.buttonActionType || null,
|
||
buttonUrl: field.buttonUrl || null,
|
||
buttonFormId: field.buttonFormId || null,
|
||
companyAutofill: field.companyAutofill || null,
|
||
maxFileCount: field.maxFileCount != null && field.maxFileCount > 0 ? field.maxFileCount : null,
|
||
maxFileSizeMB: field.maxFileSizeMB != null && field.maxFileSizeMB > 0 ? field.maxFileSizeMB : null,
|
||
autoProlongation: field.autoProlongation || false,
|
||
autoProlongationLabel: field.autoProlongationLabel || null,
|
||
linkToUserProfile: field.linkToUserProfile || false,
|
||
allowMultiple: field.allowMultiple || false,
|
||
ganttRole: field.ganttRole || null,
|
||
};
|
||
|
||
if (field.id && field.id > 0) {
|
||
const [updated] = await tx
|
||
.update(formFields)
|
||
.set(fieldData)
|
||
.where(and(
|
||
eq(formFields.id, field.id),
|
||
eq(formFields.formId, formId),
|
||
exists(tx.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
))
|
||
.returning();
|
||
if (updated) resultFields.push(updated);
|
||
} else {
|
||
const [created] = await tx
|
||
.insert(formFields)
|
||
.values(fieldData)
|
||
.returning();
|
||
resultFields.push(created);
|
||
}
|
||
}
|
||
return resultFields;
|
||
});
|
||
}
|
||
|
||
// Form Statuses
|
||
async getFormStatuses(formId: number, organizationId: number): Promise<FormStatus[]> {
|
||
return await db
|
||
.select()
|
||
.from(formStatuses)
|
||
.innerJoin(forms, eq(formStatuses.formId, forms.id))
|
||
.where(and(eq(formStatuses.formId, formId), eq(forms.organizationId, organizationId)))
|
||
.orderBy(formStatuses.position)
|
||
.then(results => results.map(r => r.form_statuses));
|
||
}
|
||
|
||
async createFormStatus(insertStatus: InsertFormStatus): Promise<FormStatus> {
|
||
const [status] = await db
|
||
.insert(formStatuses)
|
||
.values(insertStatus)
|
||
.returning();
|
||
return status;
|
||
}
|
||
|
||
async updateFormStatus(id: number, formId: number, organizationId: number, updates: Partial<FormStatus>): Promise<FormStatus> {
|
||
const [status] = await db
|
||
.update(formStatuses)
|
||
.set(updates)
|
||
.where(and(
|
||
eq(formStatuses.id, id),
|
||
eq(formStatuses.formId, formId),
|
||
exists(db.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
))
|
||
.returning();
|
||
return status;
|
||
}
|
||
|
||
async deleteFormStatus(id: number, formId: number, organizationId: number): Promise<void> {
|
||
await db
|
||
.delete(formStatuses)
|
||
.where(and(
|
||
eq(formStatuses.id, id),
|
||
eq(formStatuses.formId, formId),
|
||
exists(db.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
));
|
||
}
|
||
|
||
async bulkSaveFormStatuses(
|
||
formId: number,
|
||
organizationId: number,
|
||
statusIdsToDelete: number[],
|
||
statuses: Array<{ id?: number; name: string; color?: string; isInitial?: boolean; isFinal?: boolean; entryConditions?: { fieldCode: string; value: string }[] | null }>
|
||
): Promise<FormStatus[]> {
|
||
return await db.transaction(async (tx) => {
|
||
for (const statusId of statusIdsToDelete) {
|
||
await tx
|
||
.delete(formStatuses)
|
||
.where(and(eq(formStatuses.id, statusId), eq(formStatuses.formId, formId)));
|
||
}
|
||
|
||
const resultStatuses: FormStatus[] = [];
|
||
for (let i = 0; i < statuses.length; i++) {
|
||
const status = statuses[i];
|
||
const statusData: InsertFormStatus = {
|
||
formId,
|
||
name: status.name,
|
||
color: status.color || '#e5e7eb',
|
||
position: i,
|
||
isInitial: status.isInitial || false,
|
||
isFinal: status.isFinal || false,
|
||
entryConditions: status.entryConditions ?? null,
|
||
};
|
||
|
||
if (status.id && status.id > 0) {
|
||
const [updated] = await tx
|
||
.update(formStatuses)
|
||
.set(statusData)
|
||
.where(and(
|
||
eq(formStatuses.id, status.id),
|
||
eq(formStatuses.formId, formId),
|
||
exists(tx.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
))
|
||
.returning();
|
||
if (updated) resultStatuses.push(updated);
|
||
} else {
|
||
const [created] = await tx
|
||
.insert(formStatuses)
|
||
.values(statusData)
|
||
.returning();
|
||
resultStatuses.push(created);
|
||
}
|
||
}
|
||
return resultStatuses;
|
||
});
|
||
}
|
||
|
||
// Status Transitions
|
||
async getStatusTransitions(formId: number, organizationId: number): Promise<StatusTransition[]> {
|
||
return await db
|
||
.select()
|
||
.from(statusTransitions)
|
||
.innerJoin(forms, eq(statusTransitions.formId, forms.id))
|
||
.where(and(eq(statusTransitions.formId, formId), eq(forms.organizationId, organizationId)))
|
||
.orderBy(statusTransitions.id)
|
||
.then(results => results.map(r => r.status_transitions));
|
||
}
|
||
|
||
async createStatusTransition(insertTransition: InsertStatusTransition): Promise<StatusTransition> {
|
||
const [transition] = await db
|
||
.insert(statusTransitions)
|
||
.values(insertTransition)
|
||
.returning();
|
||
return transition;
|
||
}
|
||
|
||
async updateStatusTransition(id: number, formId: number, organizationId: number, updates: Partial<StatusTransition>): Promise<StatusTransition> {
|
||
const [transition] = await db
|
||
.update(statusTransitions)
|
||
.set(updates)
|
||
.where(and(
|
||
eq(statusTransitions.id, id),
|
||
eq(statusTransitions.formId, formId),
|
||
exists(db.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
))
|
||
.returning();
|
||
return transition;
|
||
}
|
||
|
||
async deleteStatusTransition(id: number, formId: number, organizationId: number): Promise<void> {
|
||
await db
|
||
.delete(statusTransitions)
|
||
.where(and(
|
||
eq(statusTransitions.id, id),
|
||
eq(statusTransitions.formId, formId),
|
||
exists(db.select().from(forms).where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId))))
|
||
));
|
||
}
|
||
|
||
// Offline cache configuration
|
||
async getFormsWithOfflineCache(organizationId: number): Promise<Form[]> {
|
||
return await db
|
||
.select()
|
||
.from(forms)
|
||
.where(and(
|
||
eq(forms.organizationId, organizationId),
|
||
sql`${forms.offlineCache} IS NOT NULL`,
|
||
sql`(${forms.offlineCache}->>'enabled')::boolean = true`
|
||
))
|
||
.orderBy(desc(forms.createdAt));
|
||
}
|
||
|
||
async getOfflineCacheConfig(id: number, organizationId: number): Promise<Form['offlineCache']> {
|
||
const [form] = await db
|
||
.select({ offlineCache: forms.offlineCache })
|
||
.from(forms)
|
||
.where(and(eq(forms.id, id), eq(forms.organizationId, organizationId)));
|
||
return form?.offlineCache ?? null;
|
||
}
|
||
|
||
async updateFormOfflineCache(id: number, organizationId: number, config: Form['offlineCache']): Promise<Form> {
|
||
const [form] = await db
|
||
.update(forms)
|
||
.set({ offlineCache: config, updatedAt: new Date() })
|
||
.where(and(eq(forms.id, id), eq(forms.organizationId, organizationId)))
|
||
.returning();
|
||
if (!form) throw new Error("Форма не найдена");
|
||
return form;
|
||
}
|
||
|
||
}
|