path: root/db/schema/evaluation.ts

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;
};