// 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;