import { 
    pgTable, 
    serial, 
    varchar, 
    text, 
    integer, 
    timestamp, 
    pgEnum,
    boolean,
    index,
    uniqueIndex,  pgView, jsonb  ,decimal, json
  } from "drizzle-orm/pg-core"
  import { relations ,sql, eq} from "drizzle-orm"
import { projects } from "./projects"
import { users } from "./users"
import { vendors } from "./vendors"
  
  // GTC 구분 enum
  export const gtcTypeEnum = pgEnum("gtc_type", ["standard", "project"])
  
  // GTC 문서 테이블
  export const gtcDocuments = pgTable("gtc_documents", {
    id: serial("id").primaryKey(),
    
    // 구분 (표준/프로젝트)
    type: gtcTypeEnum("type").notNull(),
    
    // 프로젝트 참조 (프로젝트 타입인 경우만)
    projectId: integer("project_id").references(() => projects.id, { 
      onDelete: "cascade" 
    }),

    title:varchar("title", { length: 255 }),
    
    // 리비전 번호
    revision: integer("revision").notNull().default(0),
    
    // 파일 정보
    fileName: varchar("file_name", { length: 255 }),
    filePath: varchar("file_path", { length: 500 }),
    fileSize: integer("file_size"), // bytes
    
    // 최초 등록 정보
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    createdById: integer("created_by_id")
      .references(() => users.id, { onDelete: "set null" })
      .notNull(),
    
    // 최종 수정 정보  
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updatedById: integer("updated_by_id")
      .references(() => users.id, { onDelete: "set null" }),
    
    // 편집 사유
    editReason: text("edit_reason"),
    
    // 활성 상태
    isActive: boolean("is_active").default(true).notNull(),
    
  }, (table) => {
    return {
      // 프로젝트별 리비전 유니크 (표준의 경우 projectId가 null)
      projectRevisionIdx: uniqueIndex("gtc_project_revision_idx")
        .on(table.projectId, table.revision, table.type),
      
      // 조회 성능을 위한 인덱스들
      typeIdx: index("gtc_type_idx").on(table.type),
      projectIdx: index("gtc_project_idx").on(table.projectId),
      createdAtIdx: index("gtc_created_at_idx").on(table.createdAt),
      updatedAtIdx: index("gtc_updated_at_idx").on(table.updatedAt),
    }
  })
  
  // 관계 정의 (필요한 경우)
  export const gtcDocumentsRelations = relations(gtcDocuments, ({ one }) => ({
    project: one(projects, {
      fields: [gtcDocuments.projectId],
      references: [projects.id],
    }),
    createdBy: one(users, {
      fields: [gtcDocuments.createdById],
      references: [users.id],
    }),
    updatedBy: one(users, {
      fields: [gtcDocuments.updatedById],
      references: [users.id],
    }),
  }))
  
  // 타입 정의
  export type GtcDocument = typeof gtcDocuments.$inferSelect
  export type NewGtcDocument = typeof gtcDocuments.$inferInsert
  
  // 조인된 결과를 위한 타입
  export type GtcDocumentWithRelations = GtcDocument & {
    project?: {
      id: number
      projectCode: string
      projectName: string
    }
    createdBy?: {
      id: number
      name: string
    }
    updatedBy?: {
      id: number
      name: string
    }
  }


  export const gtcDocumentsView = pgView('gtc_documents_view').as((qb) =>
    qb
      .select({
        // GTC 문서 기본 필드
        id: gtcDocuments.id,
        type: gtcDocuments.type,
        projectId: gtcDocuments.projectId,
        revision: gtcDocuments.revision,
        title: gtcDocuments.title,
        fileName: gtcDocuments.fileName,
        filePath: gtcDocuments.filePath,
        fileSize: gtcDocuments.fileSize,
        createdAt: gtcDocuments.createdAt,
        createdById: gtcDocuments.createdById,
        updatedAt: gtcDocuments.updatedAt,
        updatedById: gtcDocuments.updatedById,
        editReason: gtcDocuments.editReason,
        isActive: gtcDocuments.isActive,
        
        // 프로젝트 정보
        projectCode: projects.code,
        projectName: projects.name,
        
        // 생성자 정보  
        createdByName: sql<string>`created_by_user.name`.as('created_by_name'),
        createdByEmail: sql<string>`created_by_user.email`.as('created_by_email'),
        
        // 수정자 정보
        updatedByName: sql<string>`updated_by_user.name`.as('updated_by_name'),
        updatedByEmail: sql<string>`updated_by_user.email`.as('updated_by_email'),
        
        // 집계 필드들
        // 같은 타입/프로젝트의 총 문서 수
        totalDocumentsInGroup: sql<number>`
          (
            SELECT count(*)
            FROM gtc_documents gd2
            WHERE gd2.type = ${gtcDocuments.type}
            AND gd2.is_active = true
            AND (
              (${gtcDocuments.type} = 'project' AND gd2.project_id = ${gtcDocuments.projectId}) OR
              (${gtcDocuments.type} = 'standard' AND gd2.project_id IS NULL)
            )
          )
        `.as('total_documents_in_group'),
        
        // 최신 리비전 번호
        latestRevision: sql<number>`
          (
            SELECT max(revision)
            FROM gtc_documents gd3
            WHERE gd3.type = ${gtcDocuments.type}
            AND gd3.is_active = true
            AND (
              (${gtcDocuments.type} = 'project' AND gd3.project_id = ${gtcDocuments.projectId}) OR
              (${gtcDocuments.type} = 'standard' AND gd3.project_id IS NULL)
            )
          )
        `.as('latest_revision'),
        
        // 현재 문서가 최신인지 여부
        isLatestRevision: sql<boolean>`
          ${gtcDocuments.revision} = (
            SELECT max(revision)
            FROM gtc_documents gd4
            WHERE gd4.type = ${gtcDocuments.type}
            AND gd4.is_active = true
            AND (
              (${gtcDocuments.type} = 'project' AND gd4.project_id = ${gtcDocuments.projectId}) OR
              (${gtcDocuments.type} = 'standard' AND gd4.project_id IS NULL)
            )
          )
        `.as('is_latest_revision'),
        
        // 이전 리비전 ID (있다면)
        previousRevisionId: sql<number>`
          (
            SELECT id
            FROM gtc_documents gd5
            WHERE gd5.type = ${gtcDocuments.type}
            AND gd5.is_active = true
            AND gd5.revision < ${gtcDocuments.revision}
            AND (
              (${gtcDocuments.type} = 'project' AND gd5.project_id = ${gtcDocuments.projectId}) OR
              (${gtcDocuments.type} = 'standard' AND gd5.project_id IS NULL)
            )
            ORDER BY gd5.revision DESC
            LIMIT 1
          )
        `.as('previous_revision_id'),
        
        // 다음 리비전 ID (있다면)
        nextRevisionId: sql<number>`
          (
            SELECT id
            FROM gtc_documents gd6
            WHERE gd6.type = ${gtcDocuments.type}
            AND gd6.is_active = true
            AND gd6.revision > ${gtcDocuments.revision}
            AND (
              (${gtcDocuments.type} = 'project' AND gd6.project_id = ${gtcDocuments.projectId}) OR
              (${gtcDocuments.type} = 'standard' AND gd6.project_id IS NULL)
            )
            ORDER BY gd6.revision ASC
            LIMIT 1
          )
        `.as('next_revision_id'),
        
        // 파일 크기 (사람이 읽기 쉬운 형태)
        fileSizeFormatted: sql<string>`
          CASE 
            WHEN ${gtcDocuments.fileSize} IS NULL THEN NULL
            WHEN ${gtcDocuments.fileSize} < 1024 THEN ${gtcDocuments.fileSize} || ' B'
            WHEN ${gtcDocuments.fileSize} < 1024 * 1024 THEN round(${gtcDocuments.fileSize} / 1024.0, 1) || ' KB'
            WHEN ${gtcDocuments.fileSize} < 1024 * 1024 * 1024 THEN round(${gtcDocuments.fileSize} / (1024.0 * 1024), 1) || ' MB'
            ELSE round(${gtcDocuments.fileSize} / (1024.0 * 1024 * 1024), 1) || ' GB'
          END
        `.as('file_size_formatted'),
        
        // 프로젝트가 있는 경우, 해당 프로젝트의 총 GTC 문서 수
        projectTotalDocuments: sql<number>`
          CASE 
            WHEN ${gtcDocuments.projectId} IS NOT NULL THEN (
              SELECT count(*)
              FROM gtc_documents gd7
              WHERE gd7.project_id = ${gtcDocuments.projectId}
              AND gd7.is_active = true
            )
            ELSE NULL
          END
        `.as('project_total_documents'),
        
        // 리비전 히스토리 (배열로 반환)
        revisionHistory: sql<number[]>`
          (
            SELECT array_agg(revision ORDER BY revision)
            FROM gtc_documents gd8
            WHERE gd8.type = ${gtcDocuments.type}
            AND gd8.is_active = true
            AND (
              (${gtcDocuments.type} = 'project' AND gd8.project_id = ${gtcDocuments.projectId}) OR
              (${gtcDocuments.type} = 'standard' AND gd8.project_id IS NULL)
            )
          )
        `.as('revision_history'),
        
        // 수정 이력이 있는지 여부
        hasEditHistory: sql<boolean>`
          ${gtcDocuments.createdById} != ${gtcDocuments.updatedById} OR 
          ${gtcDocuments.createdAt} != ${gtcDocuments.updatedAt}
        `.as('has_edit_history'),
      })
      .from(gtcDocuments)
      .leftJoin(projects, sql`${gtcDocuments.projectId} = ${projects.id}`)
      .leftJoin(
        sql`users created_by_user`, 
        sql`${gtcDocuments.createdById} = created_by_user.id`
      )
      .leftJoin(
        sql`users updated_by_user`, 
        sql`${gtcDocuments.updatedById} = updated_by_user.id`
      )
  );

  export type GtcDocumentView = typeof gtcDocumentsView.$inferSelect;

  // 협의 상태 enum
export const reviewStatusEnum = pgEnum("review_status", [
  "draft",        // 초안
  "pending",      // 협의 대기
  "reviewing",    // 협의 중
  "approved",     // 승인됨
  "rejected",     // 거부됨
  "revised"       // 수정됨
])

  
  // 템플릿 파일 관리 테이블
export const gtcTemplates = pgTable("gtc_templates", {
  id: serial("id").primaryKey(),

  documentId: integer("document_id")
  .references(() => gtcDocuments.id, { onDelete: "cascade" })
  .notNull(),
  
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  version: varchar("version", { length: 50 }).default("1.0").notNull(),
  
  // 파일 정보
  originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
  filePath: varchar("file_path", { length: 500 }).notNull(),
  fileSize: integer("file_size"),
  
  // PDFTron 변수 메타데이터
  variableMetadata: jsonb("variable_metadata"),
  
  isActive: boolean("is_active").default(true).notNull(),
  isDefault: boolean("is_default").default(false).notNull(),
  
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  createdById: integer("created_by_id")
    .references(() => users.id, { onDelete: "set null" })
    .notNull(),
    
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedById: integer("updated_by_id")
    .references(() => users.id, { onDelete: "set null" }),
    
}, (table) => {
  return {
    nameIdx: index("gtc_templates_name_idx").on(table.name),
    isDefaultIdx: index("gtc_templates_is_default_idx").on(table.isDefault),
  }
})

// 표준(마스터) GTC 조항 테이블
export const gtcClauses = pgTable("gtc_clauses", {
  id: serial("id").primaryKey(),
  
  // GTC 문서 참조
  documentId: integer("document_id")
    .references(() => gtcDocuments.id, { onDelete: "cascade" })
    .notNull(),
  
  // 계층구조
  parentId: integer("parent_id"),

  
  // 채번
  itemNumber: varchar("item_number", { length: 50 }).notNull(),
  
  // 분류 (수기입력)
  category: varchar("category", { length: 100 }),
  
  // 소제목
  subtitle: varchar("subtitle", { length: 500 }).notNull(),
  
  // 상세항목
  content: text("content"),
  
  // 순서
  sortOrder: decimal("sort_order", { precision: 10, scale: 2 })
    .default('0')
    .notNull(),
  
  // 계층 정보
  depth: integer("depth").default(0).notNull(),
  fullPath: varchar("full_path", { length: 200 }),
  
  // PDFTron 변수명들
  images: json("images"), // ✅ 이미지 JSON 배열 추가

  
  isActive: boolean("is_active").default(true).notNull(),
  
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  createdById: integer("created_by_id")
    .references(() => users.id, { onDelete: "set null" })
    .notNull(),
    
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedById: integer("updated_by_id")
    .references(() => users.id, { onDelete: "set null" }),
    
  editReason: text("edit_reason"),
  
}, (table) => {
  return {
    documentItemNumberIdx: uniqueIndex("gtc_clauses_document_item_number_idx")
      .on(table.documentId, table.parentId, table.itemNumber),
    documentIdx: index("gtc_clauses_document_idx").on(table.documentId),
    parentIdx: index("gtc_clauses_parent_idx").on(table.parentId),
    fullPathIdx: index("gtc_clauses_full_path_idx").on(table.fullPath),
  }
})

// 벤더별 GTC 문서 테이블
export const gtcVendorDocuments = pgTable("gtc_vendor_documents", {
  id: serial("id").primaryKey(),
  
  // 표준 GTC 문서 참조
  baseDocumentId: integer("base_document_id")
    .references(() => gtcDocuments.id, { onDelete: "cascade" })
    .notNull(),
  
  // 벤더 참조
  vendorId: integer("vendor_id")
    .references(() => vendors.id, { onDelete: "cascade" })
    .notNull(),
  
  // 벤더별 문서 정보
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  version: varchar("version", { length: 50 }).default("1.0").notNull(),
  
  // 협의 상태
  reviewStatus: reviewStatusEnum("review_status").default("draft").notNull(),
  
  // 협의 일정
  negotiationStartDate: timestamp("negotiation_start_date", { withTimezone: true }),
  negotiationEndDate: timestamp("negotiation_end_date", { withTimezone: true }),
  approvalDate: timestamp("approval_date", { withTimezone: true }),
  
  // 최종 파일 (협의 완료 후 생성된 파일)
  finalFileName: varchar("final_file_name", { length: 255 }),
  finalFilePath: varchar("final_file_path", { length: 500 }),
  finalFileSize: integer("final_file_size"),
  
  isActive: boolean("is_active").default(true).notNull(),
  
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  createdById: integer("created_by_id")
    .references(() => users.id, { onDelete: "set null" })
    .notNull(),
    
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedById: integer("updated_by_id")
    .references(() => users.id, { onDelete: "set null" }),
  
}, (table) => {
  return {
    // 벤더별 기본문서 유니크 (벤더당 하나의 기본문서에 대해 하나의 협의문서)
    baseDocumentVendorIdx: uniqueIndex("gtc_vendor_documents_base_vendor_idx")
      .on(table.baseDocumentId, table.vendorId),
    vendorIdx: index("gtc_vendor_documents_vendor_idx").on(table.vendorId),
    baseDocumentIdx: index("gtc_vendor_documents_base_document_idx").on(table.baseDocumentId),
    reviewStatusIdx: index("gtc_vendor_documents_review_status_idx").on(table.reviewStatus),
  }
})

// 벤더별 GTC 조항 테이블 (표준 조항을 참조하되 내용 수정 가능)
export const gtcVendorClauses = pgTable("gtc_vendor_clauses", {
  id: serial("id").primaryKey(),
  
  // 벤더 문서 참조
  vendorDocumentId: integer("vendor_document_id")
    .references(() => gtcVendorDocuments.id, { onDelete: "cascade" })
    .notNull(),
  
  // 표준 조항 참조 (기준이 되는 마스터 조항)
  baseClauseId: integer("base_clause_id")
    .references(() => gtcClauses.id, { onDelete: "cascade" })
    .notNull(),
  
  // 계층구조 (벤더별 문서 내에서)
  parentId: integer("parent_id"),
  
  // 벤더별로 수정된 내용 (null이면 표준 내용 사용)
  modifiedItemNumber: varchar("modified_item_number", { length: 50 }),
  modifiedCategory: varchar("modified_category", { length: 100 }),
  modifiedSubtitle: varchar("modified_subtitle", { length: 500 }),
  modifiedContent: text("modified_content"),
  
  // 순서 (벤더별 문서 내에서)
  sortOrder: decimal("sort_order", { precision: 10, scale: 2 })
    .default('0')
    .notNull(),
  
  // 계층 정보
  depth: integer("depth").default(0).notNull(),
  fullPath: varchar("full_path", { length: 200 }),
  
  // 수정 여부 플래그들
  isNumberModified: boolean("is_number_modified").default(false).notNull(),
  isCategoryModified: boolean("is_category_modified").default(false).notNull(),
  isSubtitleModified: boolean("is_subtitle_modified").default(false).notNull(),
  isContentModified: boolean("is_content_modified").default(false).notNull(),
  
  // 협의 관련
  reviewStatus: reviewStatusEnum("review_status").default("draft").notNull(),
  negotiationNote: text("negotiation_note"), // 협의 과정 메모
  
  // 활성/비활성 (특정 조항을 벤더와의 협의에서 제외할 수 있음)
  isActive: boolean("is_active").default(true).notNull(),
  isExcluded: boolean("is_excluded").default(false).notNull(), // 벤더 협의에서 제외
  
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  createdById: integer("created_by_id")
    .references(() => users.id, { onDelete: "set null" })
    .notNull(),
    
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedById: integer("updated_by_id")
    .references(() => users.id, { onDelete: "set null" }),
    
  editReason: text("edit_reason"),
  
}, (table) => {
  return {
    // 벤더 문서별 기본 조항 유니크
    vendorDocumentBaseClauseIdx: uniqueIndex("gtc_vendor_clauses_vendor_base_idx")
      .on(table.vendorDocumentId, table.baseClauseId),
    vendorDocumentIdx: index("gtc_vendor_clauses_vendor_document_idx").on(table.vendorDocumentId),
    baseClauseIdx: index("gtc_vendor_clauses_base_clause_idx").on(table.baseClauseId),
    parentIdx: index("gtc_vendor_clauses_parent_idx").on(table.parentId),
    reviewStatusIdx: index("gtc_vendor_clauses_review_status_idx").on(table.reviewStatus),
  }
})

// 협의 이력 테이블
export const gtcNegotiationHistory = pgTable("gtc_negotiation_history", {
  id: serial("id").primaryKey(),
  
  // 벤더 조항 참조
  vendorClauseId: integer("vendor_clause_id")
    .references(() => gtcVendorClauses.id, { onDelete: "cascade" })
    .notNull(),
  
  // 협의 정보
  action: varchar("action", { length: 50 }).notNull(), // "created", "modified", "approved", "rejected", "commented"
  previousStatus: reviewStatusEnum("previous_status"),
  newStatus: reviewStatusEnum("new_status"),
  
  // 변경 내용
  changedFields: jsonb("changed_fields"), // 어떤 필드가 변경되었는지
  comment: text("comment"),
  
  // 첨부파일
  attachments: jsonb("attachments"),
  
  // 협의 당사자
  actorType: varchar("actor_type", { length: 20 }).notNull(), // "internal", "vendor"
  actorId: integer("actor_id").references(() => users.id, { onDelete: "set null" }),
  actorName: varchar("actor_name", { length: 100 }), // 외부 벤더인 경우
  actorEmail: varchar("actor_email", { length: 255 }),
  
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
    
}, (table) => {
  return {
    vendorClauseIdx: index("gtc_negotiation_history_vendor_clause_idx").on(table.vendorClauseId),
    actionIdx: index("gtc_negotiation_history_action_idx").on(table.action),
    createdAtIdx: index("gtc_negotiation_history_created_at_idx").on(table.createdAt),
  }
})

// 관계 정의
export const vendorsRelationsForGTC = relations(vendors, ({ many, one }) => ({
  vendorDocuments: many(gtcVendorDocuments)

}))

export const gtcTemplatesRelations = relations(gtcTemplates, ({ one }) => ({
  createdBy: one(users, {
    fields: [gtcTemplates.createdById],
    references: [users.id],
  }),
  updatedBy: one(users, {
    fields: [gtcTemplates.updatedById],
    references: [users.id],
  }),
}))

export const gtcClausesRelations = relations(gtcClauses, ({ one, many }) => ({
  document: one(gtcDocuments, {
    fields: [gtcClauses.documentId],
    references: [gtcDocuments.id],
  }),
  parent: one(gtcClauses, {
    fields: [gtcClauses.parentId],
    references: [gtcClauses.id],
    relationName: "clauseHierarchy",
  }),
  children: many(gtcClauses, {
    relationName: "clauseHierarchy",
  }),
  vendorClauses: many(gtcVendorClauses),
  createdBy: one(users, {
    fields: [gtcClauses.createdById],
    references: [users.id],
  }),
  updatedBy: one(users, {
    fields: [gtcClauses.updatedById],
    references: [users.id],
  }),
}))

export const gtcVendorDocumentsRelations = relations(gtcVendorDocuments, ({ one, many }) => ({
  baseDocument: one(gtcDocuments, {
    fields: [gtcVendorDocuments.baseDocumentId],
    references: [gtcDocuments.id],
  }),
  vendor: one(vendors, {
    fields: [gtcVendorDocuments.vendorId],
    references: [vendors.id],
  }),
  vendorClauses: many(gtcVendorClauses),
  createdBy: one(users, {
    fields: [gtcVendorDocuments.createdById],
    references: [users.id],
  }),
  updatedBy: one(users, {
    fields: [gtcVendorDocuments.updatedById],
    references: [users.id],
  }),
}))

export const gtcVendorClausesRelations = relations(gtcVendorClauses, ({ one, many }) => ({
  vendorDocument: one(gtcVendorDocuments, {
    fields: [gtcVendorClauses.vendorDocumentId],
    references: [gtcVendorDocuments.id],
  }),
  baseClause: one(gtcClauses, {
    fields: [gtcVendorClauses.baseClauseId],
    references: [gtcClauses.id],
  }),
  parent: one(gtcVendorClauses, {
    fields: [gtcVendorClauses.parentId],
    references: [gtcVendorClauses.id],
    relationName: "vendorClauseHierarchy",
  }),
  children: many(gtcVendorClauses, {
    relationName: "vendorClauseHierarchy",
  }),
  negotiationHistory: many(gtcNegotiationHistory),
  createdBy: one(users, {
    fields: [gtcVendorClauses.createdById],
    references: [users.id],
  }),
  updatedBy: one(users, {
    fields: [gtcVendorClauses.updatedById],
    references: [users.id],
  }),
}))

export const gtcNegotiationHistoryRelations = relations(gtcNegotiationHistory, ({ one }) => ({
  vendorClause: one(gtcVendorClauses, {
    fields: [gtcNegotiationHistory.vendorClauseId],
    references: [gtcVendorClauses.id],
  }),
  actor: one(users, {
    fields: [gtcNegotiationHistory.actorId],
    references: [users.id],
  }),
}))

// 타입 정의

export type GtcTemplate = typeof gtcTemplates.$inferSelect
export type NewGtcTemplate = typeof gtcTemplates.$inferInsert

export type GtcClause = typeof gtcClauses.$inferSelect
export type NewGtcClause = typeof gtcClauses.$inferInsert

export type GtcVendorDocument = typeof gtcVendorDocuments.$inferSelect
export type NewGtcVendorDocument = typeof gtcVendorDocuments.$inferInsert

export type GtcVendorClause = typeof gtcVendorClauses.$inferSelect
export type NewGtcVendorClause = typeof gtcVendorClauses.$inferInsert

export type GtcNegotiationHistory = typeof gtcNegotiationHistory.$inferSelect
export type NewGtcNegotiationHistory = typeof gtcNegotiationHistory.$inferInsert

// 표준 GTC 조항 상세보기용 뷰 (계층구조 포함)
export const gtcClausesTreeView = pgView('gtc_clauses_tree_view').as((qb) =>
  qb
    .select({
      // GTC 조항 기본 필드
      id: gtcClauses.id,
      documentId: gtcClauses.documentId,
      parentId: gtcClauses.parentId,
      itemNumber: gtcClauses.itemNumber,
      category: gtcClauses.category,
      subtitle: gtcClauses.subtitle,
      content: gtcClauses.content,
      sortOrder: gtcClauses.sortOrder,
      depth: gtcClauses.depth,
      fullPath: gtcClauses.fullPath,
      images: gtcClauses.images,

      isActive: gtcClauses.isActive,
      createdAt: gtcClauses.createdAt,
      createdById: gtcClauses.createdById,
      updatedAt: gtcClauses.updatedAt,
      updatedById: gtcClauses.updatedById,
      editReason: gtcClauses.editReason,
      
      // 문서 정보
      documentType: gtcDocuments.type,
      documentFileName: gtcDocuments.fileName,
      documentRevision: gtcDocuments.revision,
      projectId: gtcDocuments.projectId,
      
      // 작성자/수정자 정보
      createdByName: sql<string>`created_by_user.name`.as('created_by_name'),
      createdByEmail: sql<string>`created_by_user.email`.as('created_by_email'),
      updatedByName: sql<string>`updated_by_user.name`.as('updated_by_name'),
      updatedByEmail: sql<string>`updated_by_user.email`.as('updated_by_email'),
      
      // 부모 정보
      parentItemNumber: sql<string>`parent_clause.item_number`.as('parent_item_number'),
      parentSubtitle: sql<string>`parent_clause.subtitle`.as('parent_subtitle'),
      
      // 자식 수
      childrenCount: sql<number>`
        (
          SELECT count(*)
          FROM gtc_clauses children
          WHERE children.parent_id = ${gtcClauses.id}
          AND children.is_active = true
        )
      `.as('children_count'),
      
      // 같은 부모 아래 형제 수
      siblingsCount: sql<number>`
        (
          SELECT count(*)
          FROM gtc_clauses siblings
          WHERE siblings.parent_id = ${gtcClauses.parentId}
          AND siblings.is_active = true
        )
      `.as('siblings_count'),
         
      // 수정 이력이 있는지 여부
      hasEditHistory: sql<boolean>`
        ${gtcClauses.createdById} != ${gtcClauses.updatedById} OR 
        ${gtcClauses.createdAt} != ${gtcClauses.updatedAt}
      `.as('has_edit_history'),
    })
    .from(gtcClauses)
    .leftJoin(gtcDocuments, sql`${gtcClauses.documentId} = ${gtcDocuments.id}`)
    .leftJoin(
      sql`users created_by_user`, 
      sql`${gtcClauses.createdById} = created_by_user.id`
    )
    .leftJoin(
      sql`users updated_by_user`, 
      sql`${gtcClauses.updatedById} = updated_by_user.id`
    )
    .leftJoin(
      sql`gtc_clauses parent_clause`, 
      sql`${gtcClauses.parentId} = parent_clause.id`
    )
)

export type GtcClauseTreeView = typeof gtcClausesTreeView.$inferSelect

// 벤더별 조항 상세보기용 뷰
export const gtcVendorClausesView = pgView('gtc_vendor_clauses_view').as((qb) =>
  qb
    .select({
      // 벤더 조항 정보
      id: gtcVendorClauses.id,
      vendorDocumentId: gtcVendorClauses.vendorDocumentId,
      baseClauseId: gtcVendorClauses.baseClauseId,
      parentId: gtcVendorClauses.parentId,
      
      // 실제 사용될 값들 (수정된 값이 있으면 수정값, 없으면 표준값)
      effectiveItemNumber: sql<string>`
        COALESCE(${gtcVendorClauses.modifiedItemNumber}, ${gtcClauses.itemNumber})
      `.as('effective_item_number'),
      
      effectiveCategory: sql<string>`
        COALESCE(${gtcVendorClauses.modifiedCategory}, ${gtcClauses.category})
      `.as('effective_category'),
      
      effectiveSubtitle: sql<string>`
        COALESCE(${gtcVendorClauses.modifiedSubtitle}, ${gtcClauses.subtitle})
      `.as('effective_subtitle'),
      
      effectiveContent: sql<string>`
        COALESCE(${gtcVendorClauses.modifiedContent}, ${gtcClauses.content})
      `.as('effective_content'),
      
      // 수정 정보
      isNumberModified: gtcVendorClauses.isNumberModified,
      isCategoryModified: gtcVendorClauses.isCategoryModified,
      isSubtitleModified: gtcVendorClauses.isSubtitleModified,
      isContentModified: gtcVendorClauses.isContentModified,
      
      // 표준 조항 정보
      baseItemNumber: gtcClauses.itemNumber,
      baseCategory: gtcClauses.category,
      baseSubtitle: gtcClauses.subtitle,
      baseContent: gtcClauses.content,
      
      // 벤더 정보
      vendorId: gtcVendorDocuments.vendorId,
      vendorCode: vendors.vendorCode,
      vendorName: vendors.vendorName,
      
      // 문서 정보
      baseDocumentId: gtcVendorDocuments.baseDocumentId,
      documentType: gtcDocuments.type,
      documentFileName: gtcDocuments.fileName,
      
      // 협의 상태
      reviewStatus: gtcVendorClauses.reviewStatus,
      negotiationNote: gtcVendorClauses.negotiationNote,
      isExcluded: gtcVendorClauses.isExcluded,
      
      // 계층 정보
      sortOrder: gtcVendorClauses.sortOrder,
      depth: gtcVendorClauses.depth,
      fullPath: gtcVendorClauses.fullPath,
      
      // 수정 여부 (하나라도 수정되었는지)
      hasModifications: sql<boolean>`
        ${gtcVendorClauses.isNumberModified} OR 
        ${gtcVendorClauses.isCategoryModified} OR 
        ${gtcVendorClauses.isSubtitleModified} OR 
        ${gtcVendorClauses.isContentModified}
      `.as('has_modifications'),
      
      
      createdAt: gtcVendorClauses.createdAt,
      updatedAt: gtcVendorClauses.updatedAt,
    })
    .from(gtcVendorClauses)
    .leftJoin(gtcClauses, sql`${gtcVendorClauses.baseClauseId} = ${gtcClauses.id}`)
    .leftJoin(gtcVendorDocuments, sql`${gtcVendorClauses.vendorDocumentId} = ${gtcVendorDocuments.id}`)
    .leftJoin(vendors, sql`${gtcVendorDocuments.vendorId} = ${vendors.id}`)
    .leftJoin(gtcDocuments, sql`${gtcVendorDocuments.baseDocumentId} = ${gtcDocuments.id}`)
)

export type GtcVendorClauseView = typeof gtcVendorClausesView.$inferSelect

// 벤더별 PDFTron 미리보기용 데이터 타입
export type GtcVendorClauseForPreview = {
  id: number
  itemNumber: string
  subtitle: string
  content: string | null
  depth: number
  fullPath: string | null
  sortOrder: number
  parentId: number | null
  vendorCode: string
  hasModifications: boolean
  children?: GtcVendorClauseForPreview[]
  variables: {
    number: string
    subtitle: string
    content: string| null
  }
}