import { pgTable, serial, varchar, text, timestamp, boolean, integer, unique } from "drizzle-orm/pg-core"
import { relations } from "drizzle-orm"
import { projects } from "./projects"

// ===== Code Groups 테이블 =====
export const codeGroups = pgTable("code_groups", {
  id: serial("id").primaryKey(),
  projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조
  groupId: varchar("group_id", { length: 50 }).notNull(), // GROUP ID (Code_번호 형태)
  description: varchar("description", { length: 100 }).notNull(), // Description (예: PROJECT NO, Phase)
  codeFormat: varchar("code_format", { length: 50 }), // Code Format (예: AANNN)
  expressions: text("expressions"), // Expressions (자동 생성된 정규식)
  controlType: varchar("control_type", { length: 20 }).notNull(), // Control Type (Textbox, Combobox)
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
  return {
    // 같은 프로젝트 내에서 groupId는 유니크해야 함
    uniqueProjectGroupId: unique("unique_project_group_id").on(
      table.projectId,
      table.groupId
    ),
  }
})

// ===== Document Classes 테이블 =====
export const documentClasses = pgTable("document_classes", {
  id: serial("id").primaryKey(),
  projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조
  code: varchar("code", { length: 50 }).notNull(), // CODE (자동 생성)
  value: varchar("value", { length: 100 }), // 사용자가 선택할 수 있는 값
  description: varchar("description", { length: 200 }).notNull(), // 값의 의미 설명
  codeGroupId: integer("code_group_id").references(() => codeGroups.id), // 참조하는 Code Group ID
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
  return {
    // 같은 프로젝트 내에서 code는 유니크해야 함
    uniqueProjectCode: unique("unique_project_code").on(
      table.projectId,
      table.code
    ),
    // 같은 프로젝트 내에서 value는 유니크해야 함 (null 제외)
    uniqueProjectValue: unique("unique_project_value").on(
      table.projectId,
      table.value
    ),
  }
})

// ===== Document Class Options 테이블 =====
export const documentClassOptions = pgTable("document_class_options_new", {
  id: serial("id").primaryKey(),
  documentClassId: integer("document_class_id").notNull().references(() => documentClasses.id),
  description: varchar("description", { length: 100 }).notNull(), // 하위 옵션 설명 (예: "General", "Technical")
  optionCode: varchar("option_code", { length: 50 }), // 하위 옵션 코드 (선택사항)
  sdq: integer("sdq").notNull(), // 순서 번호 (1, 2, 3, 4, 5, 6)
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
  return {
    // 같은 Document Class 내에서 optionCode는 유니크해야 함
    uniqueDocumentClassOption: unique("unique_document_class_option").on(
      table.documentClassId,
      table.optionCode
    ),
    // 같은 documentClassId 내에서 sdq는 유니크해야 함
    uniqueDocumentClassSdq: unique("unique_document_class_sdq").on(
      table.documentClassId,
      table.sdq
    ),
  }
})

// ===== ComboBox Settings 테이블 =====
export const comboBoxSettings = pgTable("combo_box_settings", {
  id: serial("id").primaryKey(),
  codeGroupId: integer("code_group_id").notNull().references(() => codeGroups.id), // Code Group과 연결
  code: varchar("code", { length: 50 }).notNull(), // CODE (예: 100, 201, 202)
  description: varchar("description", { length: 200 }).notNull(), // Description (예: General, Feed Gas Reveive)
  remark: text("remark"), // Remark (비고)
  sdq: integer("sdq").notNull(), // 순서 번호 (1, 2, 3, 4, 5, 6)
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
  return {
    // 같은 codeGroupId 내에서 code는 유니크해야 함
    uniqueCodeGroupCode: unique("unique_code_group_code").on(
      table.codeGroupId,
      table.code
    ),
    // 같은 codeGroupId 내에서 sdq는 유니크해야 함
    uniqueCodeGroupSdq: unique("unique_code_group_sdq").on(
      table.codeGroupId,
      table.sdq
    ),
  }
})

// ===== Document Number Types 테이블 =====
export const documentNumberTypes = pgTable("document_number_types", {
  id: serial("id").primaryKey(),
  projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조
  name: varchar("name", { length: 100 }).notNull(), // Number Type 이름 (예: Project No, SHI No, Vendor No)
  description: varchar("description", { length: 200 }), // 설명
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
  return {
    // 같은 프로젝트 내에서 name은 유니크해야 함
    uniqueProjectName: unique("unique_project_name").on(
      table.projectId,
      table.name
    ),
  }
})

// ===== Document Number Type Configs 테이블 =====
export const documentNumberTypeConfigs = pgTable("document_number_type_configs", {
  id: serial("id").primaryKey(),
  documentNumberTypeId: integer("document_number_type_id").notNull().references(() => documentNumberTypes.id, { onDelete: "cascade" }),
  codeGroupId: integer("code_group_id").references(() => codeGroups.id), // Code Group 참조
  sdq: integer("sdq").notNull(), // 순서 번호 (1, 2, 3, 4, 5, 6)
  description: varchar("description", { length: 200 }), // Description (예: [001] PROJECT NO)
  remark: text("remark"), // Remark (비고)
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
  return {
    // 같은 documentNumberTypeId 내에서 sdq는 유니크해야 함
    uniqueDocumentNumberTypeSdq: unique("unique_document_number_type_sdq").on(
      table.documentNumberTypeId,
      table.sdq
    ),
  }
})

// ===== 관계 정의 =====

// Projects 관계
export const projectsRelations = relations(projects, ({ many }) => ({
  codeGroups: many(codeGroups),
  documentClasses: many(documentClasses),
  documentNumberTypes: many(documentNumberTypes),
}))

// Code Groups 관계
export const codeGroupsRelations = relations(codeGroups, ({ many, one }) => ({
  project: one(projects, {
    fields: [codeGroups.projectId],
    references: [projects.id],
  }),
  comboBoxSettings: many(comboBoxSettings), // Code Group에 속한 ComboBox Settings
  documentNumberTypeConfigs: many(documentNumberTypeConfigs), // Code Group을 참조하는 Number Type Configs
}))

// Document Classes 관계
export const documentClassesRelations = relations(documentClasses, ({ many, one }) => ({
  project: one(projects, {
    fields: [documentClasses.projectId],
    references: [projects.id],
  }),
  documentClassOptions: many(documentClassOptions), // Document Class 하위 옵션들
}))

// Document Class Options 관계
export const documentClassOptionsRelations = relations(documentClassOptions, ({ one }) => ({
  documentClass: one(documentClasses, {
    fields: [documentClassOptions.documentClassId],
    references: [documentClasses.id],
  }),
}))

// ComboBox Settings 관계
export const comboBoxSettingsRelations = relations(comboBoxSettings, ({ one }) => ({
  codeGroup: one(codeGroups, {
    fields: [comboBoxSettings.codeGroupId],
    references: [codeGroups.id],
  }),
}))

// Document Number Types 관계
export const documentNumberTypesRelations = relations(documentNumberTypes, ({ many, one }) => ({
  project: one(projects, {
    fields: [documentNumberTypes.projectId],
    references: [projects.id],
  }),
  configs: many(documentNumberTypeConfigs), // Number Type의 설정들
}))

// Document Number Type Configs 관계
export const documentNumberTypeConfigsRelations = relations(documentNumberTypeConfigs, ({ one }) => ({
  documentNumberType: one(documentNumberTypes, {
    fields: [documentNumberTypeConfigs.documentNumberTypeId],
    references: [documentNumberTypes.id],
  }),
  codeGroup: one(codeGroups, {
    fields: [documentNumberTypeConfigs.codeGroupId],
    references: [codeGroups.id],
  }),
}))

// ===== 타입 정의 =====
export type CodeGroup = typeof codeGroups.$inferSelect
export type NewCodeGroup = typeof codeGroups.$inferInsert

export type DocumentClass = typeof documentClasses.$inferSelect
export type NewDocumentClass = typeof documentClasses.$inferInsert

export type DocumentClassOption = typeof documentClassOptions.$inferSelect
export type NewDocumentClassOption = typeof documentClassOptions.$inferInsert

export type ComboBoxSetting = typeof comboBoxSettings.$inferSelect
export type NewComboBoxSetting = typeof comboBoxSettings.$inferInsert

export type DocumentNumberType = typeof documentNumberTypes.$inferSelect
export type NewDocumentNumberType = typeof documentNumberTypes.$inferInsert

export type DocumentNumberTypeConfig = typeof documentNumberTypeConfigs.$inferSelect
export type NewDocumentNumberTypeConfig = typeof documentNumberTypeConfigs.$inferInsert