diff options
Diffstat (limited to 'db/schema/templates.ts')
| -rw-r--r-- | db/schema/templates.ts | 244 |
1 files changed, 244 insertions, 0 deletions
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 |