import { 
    pgTable, 
    serial, 
    varchar, 
    text, 
    integer, 
    timestamp, 
    pgEnum,
    boolean,
    index,
    uniqueIndex,  pgView  
  } from "drizzle-orm/pg-core"
  import { relations ,sql, eq} from "drizzle-orm"
import { projects } from "./projects"
import { users } from "./users"
  
  // 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" 
    }),
    
    // 리비전 번호
    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
      code: string
      name: 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,
        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;