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`count(distinct ${esgEvaluationItems.id})`.as('total_evaluation_items'), totalAnswerOptions: sql`count(${esgAnswerOptions.id})`.as('total_answer_options'), maxPossibleScore: sql`coalesce(sum(${esgAnswerOptions.score}), 0)`.as('max_possible_score'), // ✅ 서브쿼리로 평가항목 리스트 조회 evaluationItemsList: sql` ( 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; };