1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
import { pgTable, integer, varchar, text, timestamp, boolean, decimal, serial } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { basicContract } from './basicContractDocumnet';
import { users } from './users';
// 1. 설문조사 템플릿 (어떤 계약 타입에 어떤 설문을 적용할지)
export const complianceSurveyTemplates = pgTable('compliance_survey_templates', {
id: serial('id').primaryKey(),
name: varchar('name', { length: 255 }).notNull(), // '기본 준법 설문', '금융업 준법 설문' 등
description: text('description'),
version: varchar('version', { length: 50 }).notNull().default('1.0'),
isActive: boolean('is_active').notNull().default(true),
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at').defaultNow(),
});
// 2. 설문 질문들
export const complianceQuestions = pgTable('compliance_questions', {
id: serial('id').primaryKey(),
templateId: integer('template_id').references(() => complianceSurveyTemplates.id).notNull(),
questionNumber: varchar('question_number', { length: 10 }).notNull(), // '4', '10-1' 등
questionText: text('question_text').notNull(),
questionType: varchar('question_type', { length: 20 }).notNull(),
// 'RADIO', 'CHECKBOX', 'TEXT', 'TEXTAREA', 'DROPDOWN', 'FILE', 'PERCENTAGE', 'CONDITIONAL'
isRequired: boolean('is_required').notNull().default(true),
hasDetailText: boolean('has_detail_text').notNull().default(false), // 상세 기술 필요 여부
hasFileUpload: boolean('has_file_upload').notNull().default(false), // 첨부파일 필요 여부
parentQuestionId: integer('parent_question_id'), // 조건부 질문의 부모
conditionalValue: varchar('conditional_value', { length: 100 }), // 부모 질문의 어떤 답변에서 나타날지
displayOrder: integer('display_order').notNull(),
createdAt: timestamp('created_at').defaultNow(),
});
// 3. 선택형 질문의 옵션들
export const complianceQuestionOptions = pgTable('compliance_question_options', {
id: serial('id').primaryKey(),
questionId: integer('question_id').references(() => complianceQuestions.id).notNull(),
optionValue: varchar('option_value', { length: 100 }).notNull(), // 'YES', 'NO', 'COMPANY_CORP' 등
optionText: varchar('option_text', { length: 255 }).notNull(), // '네', '아니오', '주식회사/유한회사' 등
allowsOtherInput: boolean('allows_other_input').notNull().default(false), // '기타' 선택 시 수기입력 가능
displayOrder: integer('display_order').notNull(),
});
// 4. 설문 응답 (기본계약과 연결)
export const complianceResponses = pgTable('compliance_responses', {
id: serial('id').primaryKey(),
basicContractId: integer('basic_contract_id').references(() => basicContract.id).notNull(),
templateId: integer('template_id').references(() => complianceSurveyTemplates.id).notNull(),
status: varchar('status', { length: 20 }).notNull().default('IN_PROGRESS'), // 'IN_PROGRESS', 'COMPLETED', 'REVIEWED'
completedAt: timestamp('completed_at'),
reviewedBy: integer('reviewed_by').references(() => users.id), // 검토자
reviewedAt: timestamp('reviewed_at'),
reviewNotes: text('review_notes'), // 검토 의견
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at').defaultNow(),
});
// 5. 각 질문에 대한 답변
export const complianceResponseAnswers = pgTable('compliance_response_answers', {
id: serial('id').primaryKey(),
responseId: integer('response_id').references(() => complianceResponses.id).notNull(),
questionId: integer('question_id').references(() => complianceQuestions.id).notNull(),
answerValue: text('answer_value'), // 선택값 또는 입력값
detailText: text('detail_text'), // 상세 기술 내용
otherText: varchar('other_text', { length: 500 }), // '기타' 선택 시 수기입력 내용
percentageValue: decimal('percentage_value', { precision: 5, scale: 2 }), // 지분율 등
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at').defaultNow(),
});
// 6. 응답에 첨부된 파일들
export const complianceResponseFiles = pgTable('compliance_response_files', {
id: serial('id').primaryKey(),
answerId: integer('answer_id').references(() => complianceResponseAnswers.id).notNull(),
fileName: varchar('file_name', { length: 255 }).notNull(),
filePath: varchar('file_path', { length: 1024 }).notNull(),
fileSize: integer('file_size'),
mimeType: varchar('mime_type', { length: 100 }),
uploadedAt: timestamp('uploaded_at').defaultNow(),
});
// Relations 정의
export const complianceSurveyTemplatesRelations = relations(complianceSurveyTemplates, ({ many }) => ({
questions: many(complianceQuestions),
responses: many(complianceResponses),
}));
export const complianceQuestionsRelations = relations(complianceQuestions, ({ one, many }) => ({
template: one(complianceSurveyTemplates, {
fields: [complianceQuestions.templateId],
references: [complianceSurveyTemplates.id],
}),
parentQuestion: one(complianceQuestions, {
fields: [complianceQuestions.parentQuestionId],
references: [complianceQuestions.id],
}),
childQuestions: many(complianceQuestions),
options: many(complianceQuestionOptions),
answers: many(complianceResponseAnswers),
}));
export const complianceQuestionOptionsRelations = relations(complianceQuestionOptions, ({ one }) => ({
question: one(complianceQuestions, {
fields: [complianceQuestionOptions.questionId],
references: [complianceQuestions.id],
}),
}));
export const complianceResponsesRelations = relations(complianceResponses, ({ one, many }) => ({
basicContract: one(basicContract, {
fields: [complianceResponses.basicContractId],
references: [basicContract.id],
}),
template: one(complianceSurveyTemplates, {
fields: [complianceResponses.templateId],
references: [complianceSurveyTemplates.id],
}),
answers: many(complianceResponseAnswers),
}));
export const complianceResponseAnswersRelations = relations(complianceResponseAnswers, ({ one, many }) => ({
response: one(complianceResponses, {
fields: [complianceResponseAnswers.responseId],
references: [complianceResponses.id],
}),
question: one(complianceQuestions, {
fields: [complianceResponseAnswers.questionId],
references: [complianceQuestions.id],
}),
files: many(complianceResponseFiles),
}));
export const complianceResponseFilesRelations = relations(complianceResponseFiles, ({ one }) => ({
answer: one(complianceResponseAnswers, {
fields: [complianceResponseFiles.answerId],
references: [complianceResponseAnswers.id],
}),
}));
// 타입 정의
export type ComplianceSurveyTemplate = typeof complianceSurveyTemplates.$inferSelect;
export type ComplianceQuestion = typeof complianceQuestions.$inferSelect;
export type ComplianceQuestionOption = typeof complianceQuestionOptions.$inferSelect;
export type ComplianceResponse = typeof complianceResponses.$inferSelect;
export type ComplianceResponseAnswer = typeof complianceResponseAnswers.$inferSelect;
export type ComplianceResponseFile = typeof complianceResponseFiles.$inferSelect;
// Insert 타입
export type NewComplianceSurveyTemplate = typeof complianceSurveyTemplates.$inferInsert;
export type NewComplianceQuestion = typeof complianceQuestions.$inferInsert;
export type NewComplianceQuestionOption = typeof complianceQuestionOptions.$inferInsert;
export type NewComplianceResponse = typeof complianceResponses.$inferInsert;
export type NewComplianceResponseAnswer = typeof complianceResponseAnswers.$inferInsert;
export type NewComplianceResponseFile = typeof complianceResponseFiles.$inferInsert;
// 질문 타입 상수
export const QUESTION_TYPES = {
RADIO: 'RADIO', // 라디오 버튼 (단일 선택)
CHECKBOX: 'CHECKBOX', // 체크박스 (다중 선택)
TEXT: 'TEXT', // 단일 라인 텍스트
TEXTAREA: 'TEXTAREA', // 다중 라인 텍스트
DROPDOWN: 'DROPDOWN', // 드롭다운 선택
FILE: 'FILE', // 파일 업로드
PERCENTAGE: 'PERCENTAGE', // 지분율 등 퍼센트 입력
CONDITIONAL: 'CONDITIONAL', // 조건부 질문
} as const;
// 응답 상태 상수
export const RESPONSE_STATUS = {
IN_PROGRESS: 'IN_PROGRESS',
COMPLETED: 'COMPLETED',
REVIEWED: 'REVIEWED',
} as const;
|
