initial commit

1 files changed, 67 insertions, 0 deletions

diff --git a/db/schema/pq.ts b/db/schema/pq.ts
new file mode 100644
index 00000000..59ec8f07
--- /dev/null
+++ b/db/schema/pq.ts
@@ -0,0 +1,67 @@
+import {
+    pgTable, serial, integer, varchar, text,
+    timestamp, uniqueIndex
+} from "drizzle-orm/pg-core";
+import { vendors } from "./vendors";
+
+export const pqCriterias = pgTable("pq_criterias", {
+    id: serial("id").primaryKey(),
+    code: varchar("code", { length: 50 }).notNull(),        // 예: "1-1"
+    checkPoint: varchar("check_point", { length: 255 }).notNull(),
+    description: text("description"),
+    remarks: text("remarks"),
+
+    // (선택) "GENERAL", "Quality Management System" 등 큰 분류
+    groupName: varchar("group_name", { length: 255 }),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
+    id: serial("id").primaryKey(),
+    vendorId: integer("vendor_id")
+    .notNull()
+    .references(() => vendors.id, { onDelete: "cascade" }),
+
+    criteriaId: integer("criteria_id")
+        .notNull()
+        .references(() => pqCriterias.id, { onDelete: "cascade" }),
+
+    answer: text("answer"),
+
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const vendorCriteriaAttachments = pgTable("vendor_criteria_attachments", {
+    id: serial("id").primaryKey(),
+    vendorCriteriaAnswerId: integer("vendor_criteria_answer_id")
+        .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
+        .notNull(),
+
+    fileName: varchar("file_name", { length: 255 }).notNull(),
+    filePath: varchar("file_path", { length: 1024 }).notNull(),
+    fileType: varchar("file_type", { length: 50 }),
+    fileSize: integer("file_size"),
+
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const vendorPqReviewLogs = pgTable("vendor_pq_review_logs", {
+    id: serial("id").primaryKey(),
+  
+    // Each log references a single vendorPqCriteriaAnswers row
+    vendorPqCriteriaAnswerId: integer("vendor_pq_criteria_answer_id")
+      .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
+      .notNull(),
+  
+    // The reviewer's comment text
+    reviewerComment: text("reviewer_comment").notNull(),
+  
+    // Optionally store the reviewer name or user ID, if you have it
+    reviewerName: text("reviewer_name"),
+  
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+  })
+export type PqCriterias = typeof pqCriterias.$inferSelect