~20250428 작업사항

1 files changed, 75 insertions, 0 deletions

diff --git a/db/schema/basicContractDocumnet.ts b/db/schema/basicContractDocumnet.ts
new file mode 100644
index 00000000..ce22fdc9
--- /dev/null
+++ b/db/schema/basicContractDocumnet.ts
@@ -0,0 +1,75 @@
+import { pgTable, pgView, text, timestamp, integer, varchar } from 'drizzle-orm/pg-core';
+import { vendors } from './vendors';
+import { eq, sql } from "drizzle-orm";
+import { users } from './users';
+
+export const basicContractTemplates = pgTable('basic_contract_templates', {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  templateName: text('template_name').notNull(),
+  status: text('status').notNull().default('ACTIVE'), // ACTIVE, DISPOSED
+  fileName: varchar("file_name", { length: 255 }).notNull(),
+  filePath: varchar("file_path", { length: 1024 }).notNull(),
+  createdAt: timestamp('created_at').defaultNow(),
+  updatedAt: timestamp('updated_at').defaultNow(),
+  validityPeriod: integer('validity_period'), // 계약 유효기간(개월) - 새로 추가할 필드
+
+});
+
+export const basicContract = pgTable('basic_contract', {
+  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+  templateId: integer('template_id').references(() => basicContractTemplates.id),
+  vendorId: integer('vendor_id').references(() => vendors.id),
+  requestedBy: integer('requested_by').references(() => users.id),
+  status: text('status').notNull().default('PENDING'), // PENDING, COMPLETED, REJECTED
+  fileName: varchar("file_name", { length: 255 }).notNull(),
+  filePath: varchar("file_path", { length: 1024 }).notNull(),
+  createdAt: timestamp('created_at').defaultNow(),
+  updatedAt: timestamp('updated_at').defaultNow(),
+  completedAt: timestamp('completed_at'), // 계약 체결 완료 날짜 - 새로 추가할 필드
+
+});
+
+
+// 기본 계약 요청 뷰
+export const basicContractView = pgView('basic_contract_view').as((qb) => {
+  return qb
+    .select({
+      // 기본 계약 정보
+      id: sql<number>`${basicContract.id}`.as('id'),
+      templateId: sql<number | null>`${basicContract.templateId}`.as('template_id'),
+      vendorId: sql<number | null>`${basicContract.vendorId}`.as('vendor_id'),
+      requestedBy: sql<number | null>`${basicContract.requestedBy}`.as('requested_by'),
+      status: sql<string>`${basicContract.status}`.as('basic_contract_status'),
+      createdAt: sql<Date>`${basicContract.createdAt}`.as('created_at'),
+      updatedAt: sql<Date>`${basicContract.updatedAt}`.as('updated_at'),
+      completedAt: sql<Date>`${basicContract.updatedAt}`.as('completed_at'),
+      
+      // 벤더 정보
+      vendorCode: sql<string | null>`${vendors.vendorCode}`.as('vendor_code'),
+      vendorEmail: sql<string | null>`${vendors.email}`.as('vendor_email'),
+      vendorName: sql<string | null>`${vendors.vendorName}`.as('vendor_name'),
+      
+      // 사용자 정보
+      userName: sql<string | null>`${users.name}`.as('user_name'),
+      
+      // 템플릿 정보
+      templateName: sql<string | null>`${basicContractTemplates.templateName}`.as('template_name'),
+      validityPeriod: sql<number | null>`${basicContractTemplates.validityPeriod}`.as('validityPeriod'),
+
+      filePath: sql<string | null>`${basicContractTemplates.filePath}`.as('file_path'),
+      fileName: sql<string | null>`${basicContractTemplates.fileName}`.as('file_name'),
+
+      signedFilePath: sql<string | null>`${basicContract.filePath}`.as('signed_file_path'),
+
+    })
+    .from(basicContract)
+    .leftJoin(vendors, eq(basicContract.vendorId, vendors.id))
+    .leftJoin(users, eq(basicContract.requestedBy, users.id))
+    .leftJoin(basicContractTemplates, eq(basicContract.templateId, basicContractTemplates.id));
+});
+
+// 타입 정의
+export type BasicContractTemplate = typeof basicContractTemplates.$inferSelect;
+export type BasicContract = typeof basicContract.$inferSelect;
+export type BasicContractView = typeof basicContractView.$inferSelect;
+