1 files changed, 137 insertions, 0 deletions
diff --git a/db/schema/evaluationCriteria.ts b/db/schema/evaluationCriteria.ts
new file mode 100644
index 00000000..23a987cf
--- /dev/null
+++ b/db/schema/evaluationCriteria.ts
@@ -0,0 +1,137 @@
+/* IMPORT */
+import {
+  decimal,
+  integer,
+  pgTable,
+  pgView,
+  serial,
+  text,
+  timestamp,
+  varchar,
+} from 'drizzle-orm/pg-core';
+import { eq, relations } from 'drizzle-orm';
+
+// ----------------------------------------------------------------------------------------------------
+
+/* CONSTANTS */
+const REG_EVAL_CRITERIA_CATEGORY = [
+  { label: 'CS', value: 'customer-service' },
+  { label: '관리자', value: 'administrator' },
+  { label: '구매', value: 'procurement' },
+  { label: '설계', value: 'design' },
+  { label: '조달', value: 'sourcing' },
+  { label: '품질', value: 'quality' },
+];
+const REG_EVAL_CRITERIA_ITEM = [
+  { label: '가점항목', value: 'customer-service' },
+  { label: '납기', value: 'delivery' },
+  { label: '경영현황', value: 'management-status' },
+  { label: '감점항목', value: 'penalty-item' },
+  { label: '구매', value: 'procurement' },
+  { label: '품질', value: 'quality' },
+];
+const REG_EVAL_CRITERIA_CATEGORY2 = [
+  { label: '공정', value: 'processScore' },
+  { label: '가격', value: 'priceScore' },
+  { label: '납기', value: 'deliveryScore' },
+  { label: '자율평가', value: 'selfEvaluationScore' },
+];
+
+const REG_EVAL_CRITERIA_CATEGORY_ENUM = REG_EVAL_CRITERIA_CATEGORY.map(c => c.value) as [string, ...string[]];
+const REG_EVAL_CRITERIA_CATEGORY_ENUM2 = REG_EVAL_CRITERIA_CATEGORY2.map(c => c.value) as [string, ...string[]];
+const REG_EVAL_CRITERIA_ITEM_ENUM = REG_EVAL_CRITERIA_ITEM.map(c => c.value) as [string, ...string[]];
+
+// ----------------------------------------------------------------------------------------------------
+
+/* TABLE SCHEMATA */
+const regEvalCriteria = pgTable('reg_eval_criteria', {
+  id: serial('id').primaryKey(),
+  category: varchar('category', { enum: REG_EVAL_CRITERIA_CATEGORY_ENUM, length: 32 }).default('quality').notNull(),
+  category2: varchar('category', { enum: REG_EVAL_CRITERIA_CATEGORY_ENUM2, length: 32 }).default('processScore').notNull(),
+  item: varchar('item', { enum: REG_EVAL_CRITERIA_ITEM_ENUM, length: 32 }).default('quality').notNull(),
+  classification: varchar('classification', { length: 255 }).notNull(),
+  range: varchar('range', { length: 255 }),
+  remarks: text('remarks'),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+const regEvalCriteriaDetails = pgTable('reg_eval_criteria_details', {
+  id: serial('id').primaryKey(),
+  criteriaId: integer('criteria_id')
+    .notNull()
+    .references(() => regEvalCriteria.id, { onDelete: 'cascade' }),
+  detail: text('detail').notNull(),
+  orderIndex: integer('order_index').default(0).notNull(),
+  scoreEquipShip: decimal('score_equip_ship', { precision: 5, scale: 2 }),
+  scoreEquipMarine: decimal('score_equip_marine', { precision: 5, scale: 2 }),
+  scoreBulkShip: decimal('score_bulk_ship', { precision: 5, scale: 2 }),
+  scoreBulkMarine: decimal('score_bulk_marine', { precision: 5, scale: 2 }),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// ----------------------------------------------------------------------------------------------------
+
+/* VIEWS */
+const regEvalCriteriaView = pgView('reg_eval_criteria_view').as((qb) =>
+  qb
+    .select({
+      id: regEvalCriteria.id,
+      category: regEvalCriteria.category,
+      item: regEvalCriteria.item,
+      classification: regEvalCriteria.classification,
+      range: regEvalCriteria.range,
+      detailId: regEvalCriteriaDetails.id,
+      detail: regEvalCriteriaDetails.detail,
+      orderIndex: regEvalCriteriaDetails.orderIndex,
+      scoreEquipShip: regEvalCriteriaDetails.scoreEquipShip,
+      scoreEquipMarine: regEvalCriteriaDetails.scoreEquipMarine,
+      scoreBulkShip: regEvalCriteriaDetails.scoreBulkShip,
+      scoreBulkMarine: regEvalCriteriaDetails.scoreBulkMarine,
+    })
+    .from(regEvalCriteria)
+    .leftJoin(regEvalCriteriaDetails, eq(regEvalCriteria.id, regEvalCriteriaDetails.criteriaId))
+    .orderBy(regEvalCriteria.id, regEvalCriteriaDetails.orderIndex)
+);
+
+// ----------------------------------------------------------------------------------------------------
+
+/* RELATIONS */
+const regEvalCriteriaRelations = relations(regEvalCriteria, ({ many }) => ({
+  details: many(regEvalCriteriaDetails),
+}));
+const regEvalCriteriaDetailsRelations = relations(regEvalCriteriaDetails, ({one}) => ({
+  criteria: one(regEvalCriteria, {
+    fields: [regEvalCriteriaDetails.criteriaId],
+    references: [regEvalCriteria.id],
+  }),
+}));
+
+// ----------------------------------------------------------------------------------------------------
+
+/* TYPES */
+type RegEvalCriteria = typeof regEvalCriteria.$inferSelect;
+type NewRegEvalCriteria = typeof regEvalCriteria.$inferInsert;
+type RegEvalCriteriaDetails = typeof regEvalCriteriaDetails.$inferSelect;
+type NewRegEvalCriteriaDetails = typeof regEvalCriteriaDetails.$inferInsert;
+type RegEvalCriteriaWithDetails = RegEvalCriteria & { criteriaDetails: RegEvalCriteriaDetails[] };
+type RegEvalCriteriaView = typeof regEvalCriteriaView.$inferSelect;
+
+// ----------------------------------------------------------------------------------------------------
+
+/* Export */
+export {
+  REG_EVAL_CRITERIA_CATEGORY,
+  REG_EVAL_CRITERIA_ITEM,
+  regEvalCriteria,
+  regEvalCriteriaDetails,
+  regEvalCriteriaDetailsRelations,
+  regEvalCriteriaRelations,
+  regEvalCriteriaView,
+  type NewRegEvalCriteria,
+  type NewRegEvalCriteriaDetails,
+  type RegEvalCriteriaView,
+  type RegEvalCriteriaWithDetails,
+  type RegEvalCriteria,
+  type RegEvalCriteriaDetails,
+};
+\ No newline at end of file