import { pgTable, pgView, timestamp, integer, varchar, serial } from 'drizzle-orm/pg-core';
import { projects } from './projects';
import { eq, sql } from "drizzle-orm";

export const projectGtcFiles = pgTable('project_gtc_files', {
  id: serial("id").primaryKey(),
  projectId: integer('project_id').references(() => projects.id).notNull(),
  fileName: varchar("file_name", { length: 255 }).notNull(),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
  fileSize: integer('file_size'),
  mimeType: varchar("mime_type", { length: 100 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// Project GTC 뷰 (프로젝트 정보와 파일 정보를 조인)
export const projectGtcView = pgView('project_gtc_view').as((qb) => {
  return qb
    .select({
      // 프로젝트 정보
      id: sql<number>`${projects.id}`.as('id'),
      code: sql<string>`${projects.code}`.as('code'),
      name: sql<string>`${projects.name}`.as('name'),
      type: sql<string>`${projects.type}`.as('type'),
      projectCreatedAt: sql<Date>`${projects.createdAt}`.as('project_created_at'),
      projectUpdatedAt: sql<Date>`${projects.updatedAt}`.as('project_updated_at'),
      
      // GTC 파일 정보
      gtcFileId: sql<number | null>`${projectGtcFiles.id}`.as('gtc_file_id'),
      fileName: sql<string | null>`${projectGtcFiles.fileName}`.as('fileName'),
      filePath: sql<string | null>`${projectGtcFiles.filePath}`.as('filePath'),
      originalFileName: sql<string | null>`${projectGtcFiles.originalFileName}`.as('originalFileName'),
      fileSize: sql<number | null>`${projectGtcFiles.fileSize}`.as('fileSize'),
      mimeType: sql<string | null>`${projectGtcFiles.mimeType}`.as('mimeType'),
      gtcCreatedAt: sql<Date | null>`${projectGtcFiles.createdAt}`.as('gtcCreatedAt'),
      gtcUpdatedAt: sql<Date | null>`${projectGtcFiles.updatedAt}`.as('gtcUpdatedAt'),
    })
    .from(projects)
    .leftJoin(projectGtcFiles, eq(projects.id, projectGtcFiles.projectId));
});

// 타입 정의
export type ProjectGtcFile = typeof projectGtcFiles.$inferSelect;
export type ProjectGtcView = typeof projectGtcView.$inferSelect;