(대표님, 최겸) 작업사항 - 이메일 템플릿, 메일링, 기술영업 요구사항 반영

3 files changed, 304 insertions, 3 deletions

diff --git a/db/schema/techSales.ts b/db/schema/techSales.ts
index 70d812f3..09b32500 100644
--- a/db/schema/techSales.ts
+++ b/db/schema/techSales.ts
@@ -41,7 +41,7 @@ import { relations } from "drizzle-orm";
 import { biddingProjects } from "./projects";
 import { users } from "./users";
 import { itemOffshoreHull, itemOffshoreTop, itemShipbuilding } from "./items";
-import { techVendors } from "./techVendors";
+import { techVendors, techVendorContacts, techVendorPossibleItems } from "./techVendors";
 
 // ===== 기술영업 상태 관리 상수 및 타입 =====
 
@@ -237,6 +237,7 @@ export const techSalesVendorQuotations = pgTable(
     validUntil: date("valid_until", { mode: "date" }).$type<Date>(),
 
     // === [끝] 견적 응답 정보 ===
+    
     // 상태 관리
     status: varchar("status", { length: 30 })
       .$type<TechSalesQuotationStatus>()
@@ -333,6 +334,7 @@ export const techSalesRfqCommentAttachments = pgTable("tech_sales_rfq_comment_at
     { onDelete: "cascade" }
   ),
   fileName: varchar("file_name", { length: 255 }).notNull(),
+  originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
   fileSize: integer("file_size").notNull(),
   fileType: varchar("file_type", { length: 100 }),
   filePath: varchar("file_path", { length: 500 }).notNull(),
@@ -567,4 +569,59 @@ export const techSalesVendorQuotationAttachmentsRelations = relations(techSalesV
     fields: [techSalesVendorQuotationAttachments.vendorId],
     references: [techVendors.id],
   }),
+}));
+
+// ===== 기술영업 견적서-담당자 관계 테이블 =====
+
+// 기술영업 벤더 견적서와 담당자의 관계 테이블 (RFQ 발송 시 어떤 담당자에게 발송했는지 추적)
+export const techSalesVendorQuotationContacts = pgTable("tech_sales_vendor_quotation_contacts", {
+  id: serial("id").primaryKey(),
+  quotationId: integer("quotation_id").notNull(),
+  contactId: integer("contact_id").notNull(),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+// 기술영업 견적서-담당자 관계 relations
+export const techSalesVendorQuotationContactsRelations = relations(techSalesVendorQuotationContacts, ({ one }) => ({
+  // 견적서 관계
+  quotation: one(techSalesVendorQuotations, {
+    fields: [techSalesVendorQuotationContacts.quotationId],
+    references: [techSalesVendorQuotations.id],
+  }),
+}));
+
+// ===== 기술영업 담당자별 아이템 매핑 테이블 =====
+
+// 기술영업 담당자별 가능한 아이템들 매핑 테이블 (RFQ 발송 시 담당자별 아이템 정보 저장)
+export const techSalesContactPossibleItems = pgTable("tech_sales_contact_possible_items", {
+  id: serial("id").primaryKey(),
+  
+  // 기술영업 벤더 연락처 FK
+  contactId: integer("contact_id")
+    .notNull()
+    .references(() => techVendorContacts.id, { onDelete: "cascade" }),
+  
+  // 기술영업 벤더 가능 아이템 FK
+  vendorPossibleItemId: integer("vendor_possible_item_id")
+    .notNull()
+    .references(() => techVendorPossibleItems.id, { onDelete: "cascade" }),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+// 기술영업 담당자별 아이템 relations
+export const techSalesContactPossibleItemsRelations = relations(techSalesContactPossibleItems, ({ one }) => ({
+  // 연락처 관계
+  contact: one(techVendorContacts, {
+    fields: [techSalesContactPossibleItems.contactId],
+    references: [techVendorContacts.id],
+  }),
+  
+  // 벤더 가능 아이템 관계
+  vendorPossibleItem: one(techVendorPossibleItems, {
+    fields: [techSalesContactPossibleItems.vendorPossibleItemId],
+    references: [techVendorPossibleItems.id],
+  }),
 }));
\ No newline at end of file
diff --git a/db/schema/techVendors.ts b/db/schema/techVendors.ts
index b5624b85..e34d3e60 100644
--- a/db/schema/techVendors.ts
+++ b/db/schema/techVendors.ts
@@ -55,7 +55,7 @@ export const techVendorContacts = pgTable("tech_vendor_contacts", {
   contactPosition: varchar("contact_position", { length: 100 }),

   contactEmail: varchar("contact_email", { length: 255 }).notNull(),

   contactPhone: varchar("contact_phone", { length: 50 }),

-  country: varchar("country", { length: 100 }),

+  contactCountry: varchar("contact_country", { length: 100 }),

   isPrimary: boolean("is_primary").default(false).notNull(),

   createdAt: timestamp("created_at").defaultNow().notNull(),

   updatedAt: timestamp("updated_at").defaultNow().notNull(),

@@ -68,7 +68,7 @@ export const techVendorPossibleItems = pgTable("tech_vendor_possible_items", {
   vendorCode: varchar("vendor_code", { length: 100 }), // 검색/필터용 중복 저장

   vendorEmail: varchar("vendor_email", { length: 255 }), // 검색/필터용 중복 저장

   

-  // 아이템 정보 (필수: itemCode, 나머지는 nullable)

+  // 아이템 정보 (itemCode도 nullable로 변경 - 해양 HULL 등에서 없을 수 있음)

   itemCode: varchar("item_code", { length: 100 }).notNull(),

   workType: varchar("work_type", { length: 100 }), // 공종 (nullable)

   shipTypes: varchar("ship_types", { length: 255 }), // 선종 (nullable)

diff --git a/db/schema/templates.ts b/db/schema/templates.ts
new file mode 100644
index 00000000..e9efc777
--- /dev/null
+++ b/db/schema/templates.ts
@@ -0,0 +1,244 @@
+// db/schema/templates.ts & template-views.ts (subject 필드 추가)
+import {
+  pgView,
+  pgTable,
+  text,
+  timestamp,
+  uuid,
+  boolean,
+  jsonb,
+  integer,
+} from 'drizzle-orm/pg-core';
+import { relations, sql } from 'drizzle-orm';
+import { users } from './users';
+
+// ────────────────────────────────────────────────────────────────────────────────
+// Template base tables (subject 필드 추가)
+// ────────────────────────────────────────────────────────────────────────────────
+
+export const templates = pgTable('templates', {
+  id: uuid('id').primaryKey().defaultRandom(),
+  name: text('name').notNull(),
+  slug: text('slug').notNull().unique(),
+  subject: text('subject').notNull(), // 🆕 이메일 제목 템플릿 추가
+  content: text('content').notNull(),
+  description: text('description'),
+  category: text('category'),
+  sampleData: jsonb('sample_data').$type<Record<string, any>>().default({}),
+  isActive: boolean('is_active').default(true),
+  version: integer('version').default(1),
+
+  // integer FK → users.id
+  createdBy: integer('created_by')
+    .notNull()
+    .references(() => users.id, { onDelete: 'set null' }),
+
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+export const templateVariables = pgTable('template_variables', {
+  id: uuid('id').primaryKey().defaultRandom(),
+  templateId: uuid('template_id')
+    .references(() => templates.id, { onDelete: 'cascade' })
+    .notNull(),
+  variableName: text('variable_name').notNull(),
+  variableType: text('variable_type').notNull(),
+  defaultValue: text('default_value'),
+  isRequired: boolean('is_required').default(false),
+  description: text('description'),
+  validationRule: jsonb('validation_rule').$type<{
+    minLength?: number;
+    maxLength?: number;
+    pattern?: string;
+    options?: string[];
+  }>(),
+  displayOrder: integer('display_order').default(0),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+export const templateHistory = pgTable('template_history', {
+  id: uuid('id').primaryKey().defaultRandom(),
+  templateId: uuid('template_id')
+    .references(() => templates.id, { onDelete: 'cascade' })
+    .notNull(),
+  version: integer('version').notNull(),
+  subject: text('subject').notNull(), // 🆕 히스토리에도 subject 추가
+  content: text('content').notNull(),
+  changeDescription: text('change_description'),
+  changedBy: integer('changed_by')
+    .notNull()
+    .references(() => users.id, { onDelete: 'set null' }),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+});
+
+// ────────────────────────────────────────────────────────────────────────────────
+// Relations
+// ────────────────────────────────────────────────────────────────────────────────
+export const templatesRelations = relations(templates, ({ many }) => ({
+  variables: many(templateVariables),
+  history: many(templateHistory),
+}));
+export const templateVariablesRelations = relations(templateVariables, ({ one }) => ({
+  template: one(templates, {
+    fields: [templateVariables.templateId],
+    references: [templates.id],
+  }),
+}));
+export const templateHistoryRelations = relations(templateHistory, ({ one }) => ({
+  template: one(templates, {
+    fields: [templateHistory.templateId],
+    references: [templates.id],
+  }),
+}));
+
+// ────────────────────────────────────────────────────────────────────────────────
+// Types
+// ────────────────────────────────────────────────────────────────────────────────
+export type Template = typeof templates.$inferSelect;
+export type NewTemplate = typeof templates.$inferInsert;
+export type TemplateVariable = typeof templateVariables.$inferSelect;
+export type NewTemplateVariable = typeof templateVariables.$inferInsert;
+export type TemplateHistory = typeof templateHistory.$inferSelect;
+export type NewTemplateHistory = typeof templateHistory.$inferInsert;
+
+export type TemplateWithVariables = Template & { variables: TemplateVariable[] };
+export type TemplateWithFull = Template & {
+  variables: TemplateVariable[];
+  history: TemplateHistory[];
+};
+
+export const TEMPLATE_CATEGORIES = {
+  WELCOME: 'welcome-email',
+  PASSWORD_RESET: 'password-reset',
+  NOTIFICATION: 'notification',
+  INVOICE: 'invoice',
+  MARKETING: 'marketing',
+  SYSTEM: 'system',
+} as const;
+export type TemplateCategory =
+  (typeof TEMPLATE_CATEGORIES)[keyof typeof TEMPLATE_CATEGORIES];
+
+// ────────────────────────────────────────────────────────────────────────────────
+// Views (subject 필드 포함하여 업데이트)
+// ────────────────────────────────────────────────────────────────────────────────
+
+// Template list view (subject 추가)
+export const templateListView = pgView('template_list_view', {
+  id: uuid('id').notNull(),
+  name: text('name').notNull(),
+  slug: text('slug').notNull(),
+  subject: text('subject').notNull(), // 🆕 subject 추가
+  description: text('description'),
+  category: text('category'),
+  isActive: boolean('is_active'),
+  version: integer('version'),
+  createdBy: integer('created_by'),
+  createdByName: text('created_by_name'),
+  createdByEmail: text('created_by_email'),
+  createdAt: timestamp('created_at'),
+  updatedAt: timestamp('updated_at'),
+  variableCount: integer('variable_count').notNull(),
+  requiredVariableCount: integer('required_variable_count').notNull(),
+}).as(sql`
+  SELECT
+    t.id,
+    t.name,
+    t.slug,
+    t.subject,
+    t.description,
+    t.category,
+    t.is_active,
+    t.version,
+    t.created_by,
+    u.name  AS created_by_name,
+    u.email AS created_by_email,
+    t.created_at,
+    t.updated_at,
+    COALESCE(v.variable_count, 0) AS variable_count,
+    COALESCE(v.required_variable_count, 0) AS required_variable_count
+  FROM ${templates} t
+  LEFT JOIN ${users} u ON t.created_by = u.id
+  LEFT JOIN (
+    SELECT
+      template_id,
+      COUNT(*) AS variable_count,
+      COUNT(*) FILTER (WHERE is_required) AS required_variable_count
+    FROM ${templateVariables}
+    GROUP BY template_id
+  ) v ON t.id = v.template_id
+`);
+
+// Template detail view (subject 추가)
+export const templateDetailView = pgView('template_detail_view', {
+  id: uuid('id').notNull(),
+  name: text('name').notNull(),
+  slug: text('slug').notNull(),
+  subject: text('subject').notNull(), // 🆕 subject 추가
+  content: text('content').notNull(),
+  description: text('description'),
+  category: text('category'),
+  sampleData: jsonb('sample_data'),
+  isActive: boolean('is_active'),
+  version: integer('version'),
+  createdBy: integer('created_by'),
+  createdByName: text('created_by_name'),
+  createdByEmail: text('created_by_email'),
+  createdAt: timestamp('created_at'),
+  updatedAt: timestamp('updated_at'),
+  variables: jsonb('variables'),
+}).as(sql`
+  SELECT
+    t.id,
+    t.name,
+    t.slug,
+    t.subject,
+    t.content,
+    t.description,
+    t.category,
+    t.sample_data,
+    t.is_active,
+    t.version,
+    t.created_by,
+    u.name  AS created_by_name,
+    u.email AS created_by_email,
+    t.created_at,
+    t.updated_at,
+    COALESCE(
+      json_agg(
+        json_build_object(
+          'id', v.id,
+          'variableName', v.variable_name,
+          'variableType', v.variable_type,
+          'defaultValue', v.default_value,
+          'isRequired', v.is_required,
+          'description', v.description,
+          'displayOrder', v.display_order
+        ) ORDER BY v.display_order
+      ) FILTER (WHERE v.id IS NOT NULL),
+      '[]'::json
+    ) AS variables
+  FROM ${templates} t
+  LEFT JOIN ${users} u ON t.created_by = u.id
+  LEFT JOIN ${templateVariables} v ON t.id = v.template_id
+  GROUP BY
+    t.id,
+    t.name,
+    t.slug,
+    t.subject,
+    t.content,
+    t.description,
+    t.category,
+    t.sample_data,
+    t.is_active,
+    t.version,
+    t.created_by,
+    u.name,
+    u.email,
+    t.created_at,
+    t.updated_at
+`);
+
+export type TemplateListView = typeof templateListView.$inferSelect;
+export type TemplateDetailView = typeof templateDetailView.$inferSelect;
\ No newline at end of file