(대표님) 20250619 1844 KST 작업사항

1 files changed, 314 insertions, 0 deletions

diff --git a/db/schema/evaluation.ts b/db/schema/evaluation.ts
new file mode 100644
index 00000000..4db3a25a
--- /dev/null
+++ b/db/schema/evaluation.ts
@@ -0,0 +1,314 @@
+import { pgView , pgTable, serial, varchar, text, timestamp, integer, decimal, boolean, uuid } from 'drizzle-orm/pg-core';
+import { relations , sql} from 'drizzle-orm';
+import { Vendor, vendors } from './vendors';
+
+// 1. 일반 평가표 테이블
+export const generalEvaluations = pgTable('general_evaluations', {
+  id: serial('id').primaryKey(),
+  serialNumber: varchar('serial_number', { length: 50 }).notNull().unique(),
+  category: varchar('category', { length: 100 }).notNull(),
+  inspectionItem: text('inspection_item').notNull(),
+  remarks: text('remarks'),
+  isActive: boolean('is_active').default(true).notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// 2. ESG 자가진단표 메인 테이블
+export const esgEvaluations = pgTable('esg_evaluations', {
+  id: serial('id').primaryKey(),
+  serialNumber: varchar('serial_number', { length: 50 }).notNull().unique(),
+  category: varchar('category', { length: 100 }).notNull(),
+  inspectionItem: text('inspection_item').notNull(),
+  isActive: boolean('is_active').default(true).notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// 3. ESG 평가항목 테이블 (각 ESG 진단표의 세부 평가항목들)
+export const esgEvaluationItems = pgTable('esg_evaluation_items', {
+  id: serial('id').primaryKey(),
+  esgEvaluationId: integer('esg_evaluation_id').references(() => esgEvaluations.id, { onDelete: 'cascade' }).notNull(),
+  evaluationItem: text('evaluation_item').notNull(), // 평가항목
+  evaluationItemDescription: text('evaluation_item_description'), // 평가항목
+  orderIndex: integer('order_index').default(0).notNull(), // 정렬 순서
+  isActive: boolean('is_active').default(true).notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// 4. ESG 답변 옵션 테이블 (각 평가항목에 대한 선택 가능한 답변들)
+export const esgAnswerOptions = pgTable('esg_answer_options', {
+  id: serial('id').primaryKey(),
+  esgEvaluationItemId: integer('esg_evaluation_item_id').references(() => esgEvaluationItems.id, { onDelete: 'cascade' }).notNull(),
+  answerText: text('answer_text').notNull(), // 답변 내용
+  score: decimal('score', { precision: 5, scale: 2 }).notNull(), // 점수 (소수점 2자리까지)
+  orderIndex: integer('order_index').default(0).notNull(), // 정렬 순서
+  isActive: boolean('is_active').default(true).notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+
+
+// === Relations 정의 ===
+
+// ESG 평가표와 평가항목 관계
+export const esgEvaluationsRelations = relations(esgEvaluations, ({ many }) => ({
+  evaluationItems: many(esgEvaluationItems),
+}));
+
+export const esgEvaluationItemsRelations = relations(esgEvaluationItems, ({ one, many }) => ({
+  esgEvaluation: one(esgEvaluations, {
+    fields: [esgEvaluationItems.esgEvaluationId],
+    references: [esgEvaluations.id],
+  }),
+  answerOptions: many(esgAnswerOptions),
+}));
+
+export const esgAnswerOptionsRelations = relations(esgAnswerOptions, ({ one }) => ({
+  evaluationItem: one(esgEvaluationItems, {
+    fields: [esgAnswerOptions.esgEvaluationItemId],
+    references: [esgEvaluationItems.id],
+  }),
+}));
+
+// === 타입 정의 ===
+export type GeneralEvaluation = typeof generalEvaluations.$inferSelect;
+export type NewGeneralEvaluation = typeof generalEvaluations.$inferInsert;
+
+export type EsgEvaluation = typeof esgEvaluations.$inferSelect;
+export type NewEsgEvaluation = typeof esgEvaluations.$inferInsert;
+
+export type EsgEvaluationItem = typeof esgEvaluationItems.$inferSelect;
+export type NewEsgEvaluationItem = typeof esgEvaluationItems.$inferInsert;
+
+export type EsgAnswerOption = typeof esgAnswerOptions.$inferSelect;
+export type NewEsgAnswerOption = typeof esgAnswerOptions.$inferInsert;
+
+// === 조인된 타입 정의 ===
+export type EsgEvaluationWithItems = EsgEvaluation & {
+  evaluationItems: (EsgEvaluationItem & {
+    answerOptions: EsgAnswerOption[];
+  })[];
+};
+
+export type EsgEvaluationItemWithOptions = EsgEvaluationItem & {
+  answerOptions: EsgAnswerOption[];
+};
+
+
+// ESG 평가표 목록을 위한 뷰 (평가항목 수와 총 점수 등 요약 정보 포함)
+export const esgEvaluationsView = pgView('esg_evaluations_view').as((qb) =>
+  qb
+    .select({
+      id: esgEvaluations.id,
+      serialNumber: esgEvaluations.serialNumber,
+      category: esgEvaluations.category,
+      inspectionItem: esgEvaluations.inspectionItem,
+      isActive: esgEvaluations.isActive,
+      createdAt: esgEvaluations.createdAt,
+      updatedAt: esgEvaluations.updatedAt,
+      // 집계 필드들
+      totalEvaluationItems: sql<number>`count(distinct ${esgEvaluationItems.id})`.as('total_evaluation_items'),
+      totalAnswerOptions: sql<number>`count(${esgAnswerOptions.id})`.as('total_answer_options'),
+      maxPossibleScore: sql<number>`coalesce(sum(${esgAnswerOptions.score}), 0)`.as('max_possible_score'),
+      // ✅ 서브쿼리로 평가항목 리스트 조회
+      evaluationItemsList: sql<string[]>`
+        (
+          SELECT array_agg(evaluation_item order by order_index) 
+          FROM esg_evaluation_items 
+          WHERE esg_evaluation_id = ${esgEvaluations.id} 
+          AND is_active = true 
+          AND evaluation_item is not null
+        )
+      `.as('evaluation_items_list'),
+    })
+    .from(esgEvaluations)
+    .leftJoin(esgEvaluationItems, sql`${esgEvaluations.id} = ${esgEvaluationItems.esgEvaluationId} AND ${esgEvaluationItems.isActive} = true`)
+    .leftJoin(esgAnswerOptions, sql`${esgEvaluationItems.id} = ${esgAnswerOptions.esgEvaluationItemId} AND ${esgAnswerOptions.isActive} = true`)
+    .groupBy(
+      esgEvaluations.id,
+      esgEvaluations.serialNumber,
+      esgEvaluations.category,
+      esgEvaluations.inspectionItem,
+      esgEvaluations.isActive,
+      esgEvaluations.createdAt,
+      esgEvaluations.updatedAt
+    )
+);
+// 타입도 업데이트
+export type EsgEvaluationsView = typeof esgEvaluationsView.$inferSelect;
+
+
+
+export const evaluationSubmissions = pgTable('evaluation_submissions', {
+  id: serial('id').primaryKey(),
+  submissionId: uuid('submission_id').defaultRandom().notNull().unique(),
+  companyId: integer('company_id').references(() => vendors.id, { onDelete: 'cascade' }).notNull(),
+  evaluationYear: integer('evaluation_year').notNull(),
+  evaluationRound: varchar('evaluation_round', { length: 50 }),
+  submissionStatus: varchar('submission_status', { length: 50 }).default('draft').notNull(),
+  submittedAt: timestamp('submitted_at'),
+  reviewedAt: timestamp('reviewed_at'),
+  reviewedBy: varchar('reviewed_by', { length: 100 }),
+  reviewComments: text('review_comments'),
+  
+  // 🔄 ESG 평균 점수만 저장 (일반평가는 점수 없음)
+  averageEsgScore: decimal('average_esg_score', { precision: 5, scale: 2 }), // ESG 평균 점수만
+  
+  // 📊 진행률 통계
+  totalGeneralItems: integer('total_general_items').default(0), // 일반평가 총 항목 수
+  completedGeneralItems: integer('completed_general_items').default(0), // 완료된 일반평가 항목 수
+  totalEsgItems: integer('total_esg_items').default(0), // ESG 총 항목 수  
+  completedEsgItems: integer('completed_esg_items').default(0), // 완료된 ESG 항목 수
+  
+  isActive: boolean('is_active').default(true).notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+
+// 일반평가 응답 - 점수 필드 제거
+export const generalEvaluationResponses = pgTable('general_evaluation_responses', {
+  id: serial('id').primaryKey(),
+  submissionId: integer('submission_id').references(() => evaluationSubmissions.id, { onDelete: 'cascade' }).notNull(),
+  generalEvaluationId: integer('general_evaluation_id').references(() => generalEvaluations.id, { onDelete: 'cascade' }).notNull(),
+  responseText: text('response_text').notNull(), // 텍스트 응답
+  hasAttachments: boolean('has_attachments').default(false).notNull(),
+  // ❌ score 필드 제거 (점수 없음)
+  reviewComments: text('review_comments'), // 검토자 의견 (선택적)
+  isActive: boolean('is_active').default(true).notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// ESG는 기존 그대로 (점수 있음)
+export const esgEvaluationResponses = pgTable('esg_evaluation_responses', {
+  id: serial('id').primaryKey(),
+  submissionId: integer('submission_id').references(() => evaluationSubmissions.id, { onDelete: 'cascade' }).notNull(),
+  esgEvaluationItemId: integer('esg_evaluation_item_id').references(() => esgEvaluationItems.id, { onDelete: 'cascade' }).notNull(),
+  esgAnswerOptionId: integer('esg_answer_option_id').references(() => esgAnswerOptions.id, { onDelete: 'cascade' }).notNull(),
+  selectedScore: decimal('selected_score', { precision: 5, scale: 2 }).notNull(), // ESG는 점수 있음
+  additionalComments: text('additional_comments'),
+  isActive: boolean('is_active').default(true).notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// 5. 첨부파일 테이블
+export const vendorEvaluationAttachments = pgTable('vendor_evaluation_attachments', {
+  id: serial('id').primaryKey(),
+  fileId: uuid('file_id').defaultRandom().notNull().unique(), // 외부 노출용 파일 ID
+  submissionId: integer('submission_id').references(() => evaluationSubmissions.id, { onDelete: 'cascade' }).notNull(),
+  generalEvaluationResponseId: integer('general_evaluation_response_id').references(() => generalEvaluationResponses.id, { onDelete: 'cascade' }),
+  originalFileName: varchar('original_file_name', { length: 500 }).notNull(),
+  storedFileName: varchar('stored_file_name', { length: 500 }).notNull(),
+  filePath: text('file_path').notNull(),
+  fileSize: integer('file_size').notNull(), // bytes
+  mimeType: varchar('mime_type', { length: 200 }),
+  uploadedBy: varchar('uploaded_by', { length: 100 }).notNull(),
+  isActive: boolean('is_active').default(true).notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// === Relations 정의 ===
+
+// 협력업체와 평가 제출 관계
+export const companiesRelations = relations(vendors, ({ many }) => ({
+  evaluationSubmissions: many(evaluationSubmissions),
+}));
+
+// 평가 제출과 응답들 관계
+export const evaluationSubmissionsRelations = relations(evaluationSubmissions, ({ one, many }) => ({
+  company: one(vendors, {
+    fields: [evaluationSubmissions.companyId],
+    references: [vendors.id],
+  }),
+  generalEvaluationResponses: many(generalEvaluationResponses),
+  esgEvaluationResponses: many(esgEvaluationResponses),
+  vendorEvaluationAttachments: many(vendorEvaluationAttachments),
+}));
+
+// 일반 평가표 응답 관계
+export const generalEvaluationResponsesRelations = relations(generalEvaluationResponses, ({ one, many }) => ({
+  submission: one(evaluationSubmissions, {
+    fields: [generalEvaluationResponses.submissionId],
+    references: [evaluationSubmissions.id],
+  }),
+  generalEvaluation: one(generalEvaluations, {
+    fields: [generalEvaluationResponses.generalEvaluationId],
+    references: [generalEvaluations.id],
+  }),
+  vendorEvaluationAttachments: many(vendorEvaluationAttachments),
+}));
+
+// ESG 평가표 응답 관계
+export const esgEvaluationResponsesRelations = relations(esgEvaluationResponses, ({ one }) => ({
+  submission: one(evaluationSubmissions, {
+    fields: [esgEvaluationResponses.submissionId],
+    references: [evaluationSubmissions.id],
+  }),
+  esgEvaluationItem: one(esgEvaluationItems, {
+    fields: [esgEvaluationResponses.esgEvaluationItemId],
+    references: [esgEvaluationItems.id],
+  }),
+  esgAnswerOption: one(esgAnswerOptions, {
+    fields: [esgEvaluationResponses.esgAnswerOptionId],
+    references: [esgAnswerOptions.id],
+  }),
+}));
+
+// 첨부파일 관계
+export const attachmentsRelations = relations(vendorEvaluationAttachments, ({ one }) => ({
+  submission: one(evaluationSubmissions, {
+    fields: [vendorEvaluationAttachments.submissionId],
+    references: [evaluationSubmissions.id],
+  }),
+  generalEvaluationResponse: one(generalEvaluationResponses, {
+    fields: [vendorEvaluationAttachments.generalEvaluationResponseId],
+    references: [generalEvaluationResponses.id],
+  }),
+}));
+
+// === 타입 정의 ===
+
+
+export type EvaluationSubmission = typeof evaluationSubmissions.$inferSelect;
+export type NewEvaluationSubmission = typeof evaluationSubmissions.$inferInsert;
+
+export type GeneralEvaluationResponse = typeof generalEvaluationResponses.$inferSelect;
+export type NewGeneralEvaluationResponse = typeof generalEvaluationResponses.$inferInsert;
+
+export type EsgEvaluationResponse = typeof esgEvaluationResponses.$inferSelect;
+export type NewEsgEvaluationResponse = typeof esgEvaluationResponses.$inferInsert;
+
+export type Attachment = typeof vendorEvaluationAttachments.$inferSelect;
+export type NewAttachment = typeof vendorEvaluationAttachments.$inferInsert;
+
+// === 조인된 타입 정의 ===
+export type EvaluationSubmissionWithDetails = EvaluationSubmission & {
+  company: Vendor;
+  generalEvaluationResponses: (GeneralEvaluationResponse & {
+    generalEvaluation: GeneralEvaluation;
+    vendorEvaluationAttachments: Attachment[];
+  })[];
+  esgEvaluationResponses: (EsgEvaluationResponse & {
+    esgEvaluationItem: EsgEvaluationItem;
+    esgAnswerOption: EsgAnswerOption;
+  })[];
+  vendorEvaluationAttachments: Attachment[];
+};
+
+export type GeneralEvaluationResponseWithDetails = GeneralEvaluationResponse & {
+  generalEvaluation: GeneralEvaluation;
+  vendorEvaluationAttachments: Attachment[];
+};
+
+export type EsgEvaluationResponseWithDetails = EsgEvaluationResponse & {
+  esgEvaluationItem: EsgEvaluationItem & {
+    esgEvaluation: EsgEvaluation;
+  };
+  esgAnswerOption: EsgAnswerOption;
+};
\ No newline at end of file