path: root/db/schema/pq.ts

import {
  pgTable, serial, integer, varchar, text,
  timestamp, uniqueIndex, pgView, boolean,
  jsonb, decimal
} from "drizzle-orm/pg-core";
import { vendorTypes, vendors } from "./vendors";
import { projects } from "./projects";
import { sql, eq } from "drizzle-orm";
import { users } from "./users";

// ---------------------------------------------------------------------------
// PQ Lists – 관리자가 생성하는 PQ 목록 정의 (GENERAL / PROJECT / NON_INSPECTION)
// ---------------------------------------------------------------------------

export const pqLists = pgTable("pq_lists", {
  id: serial("id").primaryKey(),

  // 목록 명칭 – "General PQ", "Project PQ – EPC-1234" 등
  name: varchar("name", { length: 255 }).notNull(),

  // GENERAL | PROJECT | NON_INSPECTION
  type: varchar("type", { length: 20 }).notNull(),

  // 프로젝트 PQ의 경우 연결될 프로젝트
  projectId: integer("project_id").references(() => projects.id),

  // 삭제 플래그 (실제 데이터는 보존)
  isDeleted: boolean("is_deleted").notNull().default(false),

  // 프로젝트 PQ 사용 기한 (선택)
  validTo: timestamp("valid_to"),
  // 생성자
  createdBy: integer("created_by").references(() => users.id),
  // 수정자
  updatedBy: integer("updated_by").references(() => users.id),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export type PqList = typeof pqLists.$inferSelect;

export const pqCriterias = pgTable("pq_criterias", {
  id: serial("id").primaryKey(),
  code: varchar("code", { length: 50 }).notNull(),        // 예: "1-1"
  checkPoint: varchar("check_point", { length: 255 }).notNull(),
  description: text("description"),
  remarks: text("remarks"),

  // (선택) "GENERAL", "Quality Management System" 등 큰 분류
  groupName: varchar("group_name", { length: 255 }),
  subGroupName: varchar("sub_group_name", { length: 255 }),

  // 해당 항목이 속하는 PQ 목록 (General/Project/Non-Inspection)
  pqListId: integer("pq_list_id")
    .notNull()
    .references(() => pqLists.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),

  // 협력업체 입력 형식 – TEXT, FILE, EMAIL, PHONE, NUMBER 등
  inputFormat: varchar("input_format", { length: 50 })
    .notNull()
    .default("TEXT"),

  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


export const vendorCriteriaAttachments = pgTable("vendor_criteria_attachments", {
  id: serial("id").primaryKey(),
  vendorCriteriaAnswerId: integer("vendor_criteria_answer_id")
    .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
    .notNull(),

  fileName: varchar("file_name", { length: 255 }).notNull(),
  originalFileName: varchar("original_file_name", { length: 255 }),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  fileType: varchar("file_type", { length: 50 }),
  fileSize: integer("file_size"),

  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const vendorPqReviewLogs = pgTable("vendor_pq_review_logs", {
  id: serial("id").primaryKey(),

  // Each log references a single vendorPqCriteriaAnswers row
  vendorPqCriteriaAnswerId: integer("vendor_pq_criteria_answer_id")
    .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
    .notNull(),

  // The reviewer's comment text
  reviewerComment: text("reviewer_comment").notNull(),

  // Optionally store the reviewer name or user ID, if you have it
  reviewerName: text("reviewer_name"),

  createdAt: timestamp("created_at").defaultNow().notNull(),
})
export type PqCriterias = typeof pqCriterias.$inferSelect


// 협력업체와 프로젝트 PQ 요청 연결 테이블
export const vendorPQSubmissions = pgTable("vendor_pq_submissions", {
  id: serial("id").primaryKey(),

  pqNumber: varchar("pq_number", { length: 50 }).notNull().unique(),

  requesterId: integer("requester_id")
    .references(() => users.id),

  vendorId: integer("vendor_id")
    .notNull()
    .references(() => vendors.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),

  // null이면 일반 PQ, 값이 있으면 프로젝트 PQ
  projectId: integer("project_id")
    .references(() => projects.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),

  // PQ 유형 구분을 명시적으로
  type: varchar("type", { length: 20 }).notNull(), // "GENERAL" or "PROJECT" or "NON_INSPECTION"

  status: varchar("status", { length: 20 }).notNull().default("REQUESTED"),
  dueDate: timestamp("due_date"),
  agreements: jsonb("agreements").notNull().default({}), // ✅ 체크 항목들을 JSON으로 저장

  // PQ 대상품목
  pqItems: varchar("pq_items", { length: 1000 }),

  submittedAt: timestamp("submitted_at"),
  approvedAt: timestamp("approved_at"),
  rejectedAt: timestamp("rejected_at"),
  rejectReason: text("reject_reason"),

  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => {
  return {
    // 협력업체별로 일반 PQ는 하나만, 프로젝트 PQ는 프로젝트당 하나만
    uniqueConstraint: uniqueIndex("unique_pq_submission").on(
      table.vendorId,
      table.projectId,
      table.type
    ),
  };
});

// 기존 vendorPqCriteriaAnswers 테이블에 projectId 필드 추가
export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
  id: serial("id").primaryKey(),

  vendorId: integer("vendor_id")
    .notNull()
    .references(() => vendors.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),

  criteriaId: integer("criteria_id")
    .notNull()
    .references(() => pqCriterias.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),

  // 추가: 프로젝트 ID (null은 일반 PQ를 의미)
  projectId: integer("project_id")
    .references(() => projects.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),

  answer: text("answer"),
  shiComment: text("shi_comment"),
  vendorReply: text("vendor_reply"),

  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


export const projectApprovedVendors = pgView("project_approved_vendors").as((qb) => {
  return qb
    .select({
      vendor_id: vendors.id,
      vendor_name: vendors.vendorName,
      vendor_code: vendors.vendorCode,
      tax_id: vendors.taxId,
      vendor_email: vendors.email,
      vendor_phone: vendors.phone,
      vendor_status: vendors.status,
      // vendor_type_code: vendorTypes.code,
      vendor_type_name_ko: vendorTypes.nameKo,
      vendor_type_name_en: vendorTypes.nameEn,
      project_code: projects.code,
      project_name: projects.name,
      project_type: projects.type,
      // pq_status: vendorProjectPQs.status,
      submitted_at: vendorPQSubmissions.submittedAt,
      approved_at: vendorPQSubmissions.approvedAt
    })
    .from(vendors)
    .innerJoin(
      vendorPQSubmissions,
      sql`${vendorPQSubmissions.vendorId} = ${vendors.id}`
    )
    .innerJoin(
      projects,
      sql`${vendorPQSubmissions.projectId} = ${projects.id}`
    )
    .leftJoin(
      vendorTypes,
      sql`${vendors.vendorTypeId} = ${vendorTypes.id}`
    )
    .where(sql`${vendorPQSubmissions.status} = 'APPROVED'`);
});

export type ProjectApprovedVendors = typeof projectApprovedVendors.$inferSelect




export const vendorInvestigations = pgTable("vendor_investigations", {
  id: serial("id").primaryKey(),

  // 어떤 벤더에 대한 실사인지 참조
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),

  // PQ 제출에 대한 참조
  pqSubmissionId: integer("pq_submission_id")
    .references(() => vendorPQSubmissions.id, {
      onDelete: "set null",
      onUpdate: "cascade",
    }),

  requesterId: integer("requester_id")
    .references(() => users.id),

  qmManagerId: integer("qm_manager_id")
    .references(() => users.id),

  // 실사 상태
  investigationStatus: varchar("investigation_status", {
    length: 50,
    enum: [
      "PLANNED",      // 계획됨
      "IN_PROGRESS",  // 진행 중
      "COMPLETED",    // 완료됨
      "CANCELED",     // 취소됨
      "RESULT_SENT",  // 실사결과발송 - 구매담당자가 Vendor측으로 실사결과를 발송한 상태
    ],
  })
    .notNull()
    .default("PLANNED"),

  // 실사 주소
  investigationAddress: text("investigation_address"),

  // 실사 방법
  investigationMethod: varchar("investigation_method", {
    length: 100,
    enum: [
      "PURCHASE_SELF_EVAL",   // 구매자체평가
      "DOCUMENT_EVAL",        // 서류평가
      "PRODUCT_INSPECTION",   // 제품검사평가
      "SITE_VISIT_EVAL"       // 방문실사평가
    ],
  }),

  // 실사 일정 시작일 / 종료일
  scheduledStartAt: timestamp("scheduled_start_at"),
  scheduledEndAt: timestamp("scheduled_end_at"),


  // 실사 예정일
  forecastedAt: timestamp("forecasted_at"),

  // 실사 의뢰일
  requestedAt: timestamp("requested_at"),

  // 실사 확정일
  confirmedAt: timestamp("confirmed_at"),

  // 실제 실사 완료일
  completedAt: timestamp("completed_at"),

  // 실사 평가점수
  evaluationScore: integer("evaluation_score"),

  // 실사 평가 결과
  evaluationResult: varchar("evaluation_result", {
    length: 50,
    enum: [
      "APPROVED",     // 승인
      "SUPPLEMENT",   // 보완
      "REJECTED",     // 불가
    ],
  }),

  // 실사 내용이나 특이사항
  investigationNotes: text("investigation_notes"),

  // 구매 담당자 추가 의견 (실사 결과 발송 시 사용)
  purchaseComment: text("purchase_comment"),

  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 타입 정의
export type VendorInvestigation = typeof vendorInvestigations.$inferSelect;
export const vendorInvestigationAttachments = pgTable(
  "vendor_investigation_attachments",
  {
    id: serial("id").primaryKey(),
    
    // 어떤 실사 (investigation)에 대한 첨부파일인지
    investigationId: integer("investigation_id")
      .notNull()
      .references(() => vendorInvestigations.id, { onDelete: "cascade" }),
    
    fileName: varchar("file_name", { length: 255 }).notNull(),
    originalFileName: varchar("original_file_name", { length: 255 }),
    filePath: varchar("file_path", { length: 1024 }).notNull(),
    
    // 권장: 사용자 경험과 기능성을 위해 추가
    fileSize: integer("file_size"), // bytes (nullable로 기존 데이터 호환)
    mimeType: varchar("mime_type", { length: 100 }), // nullable로 기존 데이터 호환
    
    // 첨부파일 종류 (예: 보고서, 사진, 기타 등 구분)
    attachmentType: varchar("attachment_type", { length: 50 }).default("REPORT"),
    
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  }
);

// 타입 정의
export type VendorInvestigationAttachment = typeof vendorInvestigationAttachments.$inferSelect;
export type NewVendorInvestigationAttachment = typeof vendorInvestigationAttachments.$inferInsert;

// 파일 업로드 관련 유틸리티 타입
export interface FileUploadResult {
  id: number;
  fileName: string;
  originalFileName: string;
  filePath: string;
  fileSize: number;
  mimeType: string;
  fileType: string;
  uploadedBy: number;
  description?: string;
}

// 파일 다운로드용 타입
export interface FileDownloadInfo {
  fileName: string;
  originalFileName: string;
  filePath: string;
  mimeType: string;
  fileSize: number;
}

/**
 * A view that joins vendor_investigations + vendors,
 * and also embeds contacts & possibleItems as JSON arrays.
 */

export const vendorInvestigationsView = pgView(
  "vendor_investigations_view"
).as((qb) => {
  return qb
    .select({
      // Investigation fields - use investigationId as the primary identifier
      investigationId: vendorInvestigations.id,
      vendorId: vendorInvestigations.vendorId,
      pqSubmissionId: vendorInvestigations.pqSubmissionId,
      requesterId: vendorInvestigations.requesterId,
      qmManagerId: vendorInvestigations.qmManagerId,
      investigationStatus: vendorInvestigations.investigationStatus,
      investigationAddress: vendorInvestigations.investigationAddress,
      investigationMethod: vendorInvestigations.investigationMethod,
      scheduledStartAt: vendorInvestigations.scheduledStartAt,
      scheduledEndAt: vendorInvestigations.scheduledEndAt,
      forecastedAt: vendorInvestigations.forecastedAt,
      requestedAt: vendorInvestigations.requestedAt,
      confirmedAt: vendorInvestigations.confirmedAt,
      completedAt: vendorInvestigations.completedAt,
      evaluationScore: vendorInvestigations.evaluationScore,
      evaluationResult: vendorInvestigations.evaluationResult,
      investigationNotes: vendorInvestigations.investigationNotes,
      createdAt: vendorInvestigations.createdAt,
      updatedAt: vendorInvestigations.updatedAt,
      
      // Essential vendor fields
      vendorName: vendors.vendorName,
      vendorCode: vendors.vendorCode,
      
      // User names and emails instead of just IDs
      requesterName: sql<string>`requester.name`.as("requesterName"),
      requesterEmail: sql<string>`requester.email`.as("requesterEmail"),
      qmManagerName: sql<string>`qm_manager.name`.as("qmManagerName"),
      qmManagerEmail: sql<string>`qm_manager.email`.as("qmManagerEmail"),
      
      // File attachment status
      hasAttachments: sql<boolean>`(
        CASE 
          WHEN EXISTS (
            SELECT 1 FROM vendor_investigation_attachments via 
            WHERE via.investigation_id = ${vendorInvestigations.id}
          ) 
          THEN true 
          ELSE false 
        END
      )`.as("hasAttachments"),
    })
    .from(vendorInvestigations)
    .leftJoin(
      vendors,
      eq(vendorInvestigations.vendorId, vendors.id)
    )
    .leftJoin(
      sql`users AS requester`,
      eq(vendorInvestigations.requesterId, sql`requester.id`)
    )
    .leftJoin(
      sql`users AS qm_manager`, 
      eq(vendorInvestigations.qmManagerId, sql`qm_manager.id`)
    )
})

// 방문실사 요청 테이블
export const siteVisitRequests = pgTable("site_visit_requests", {
  id: serial("id").primaryKey(),
  
  // 어떤 실사(investigation)에 대한 방문실사 요청인지
  investigationId: integer("investigation_id")
    .notNull()
    .references(() => vendorInvestigations.id, { onDelete: "cascade" }),
  
  // 요청자
  requesterId: integer("requester_id")
    .references(() => users.id),
  
  // 실사 기간 (W/D 기준)
  inspectionDuration: decimal("inspection_duration", { precision: 4, scale: 1 }),
  
  // 실사 요청일 (시작일, 종료일)
  requestedStartDate: timestamp("requested_start_date"),
  requestedEndDate: timestamp("requested_end_date"),
  
  // SHI 실사참석 예정부문 (JSON으로 저장)
  shiAttendees: jsonb("shi_attendees").notNull().default({}),
  
  // 협력업체 요청정보 및 자료 (JSON으로 저장)
  vendorRequests: jsonb("vendor_requests").notNull().default({}),
  
  // 추가 요청사항
  additionalRequests: text("additional_requests"),
  
  // 상태
  status: varchar("status", { length: 20 }).notNull().default("REQUESTED"),
  
  // 메일 발송일
  sentAt: timestamp("sent_at"),
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 방문실사 요청 첨부파일
export const siteVisitRequestAttachments = pgTable("site_visit_request_attachments", {
  id: serial("id").primaryKey(),
  
  siteVisitRequestId: integer("site_visit_request_id")
    .notNull()
    .references(() => siteVisitRequests.id, { onDelete: "cascade" }),
  
  // 협력업체가 제출한 첨부파일인 경우 vendor_site_visit_info와 연결
  vendorSiteVisitInfoId: integer("vendor_site_visit_info_id")
    .references(() => vendorSiteVisitInfo.id, { onDelete: "cascade" }),
  
  fileName: varchar("file_name", { length: 255 }).notNull(),
  originalFileName: varchar("original_file_name", { length: 255 }),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  fileSize: integer("file_size"),
  mimeType: varchar("mime_type", { length: 100 }),
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 협력업체 방문실사 정보 테이블
export const vendorSiteVisitInfo = pgTable("vendor_site_visit_info", {
  id: serial("id").primaryKey(),
  
  // 방문실사 요청과 연결
  siteVisitRequestId: integer("site_visit_request_id")
    .notNull()
    .references(() => siteVisitRequests.id, { onDelete: "cascade" }),
  
  // 공장 정보
  factoryName: varchar("factory_name", { length: 255 }).notNull(),
  factoryLocation: varchar("factory_location", { length: 255 }).notNull(),
  factoryAddress: text("factory_address").notNull(),
  
  // 담당자 정보
  factoryPicName: varchar("factory_pic_name", { length: 255 }).notNull(),
  factoryPicPhone: varchar("factory_pic_phone", { length: 50 }).notNull(),
  factoryPicEmail: varchar("factory_pic_email", { length: 255 }).notNull(),
  
  // 방문 정보
  factoryDirections: text("factory_directions"),
  accessProcedure: text("access_procedure"),
  

  
  // 첨부파일 여부
  hasAttachments: boolean("has_attachments").notNull().default(false),
  
  // 기타 정보
  otherInfo: text("other_info"),
  
  // 제출 정보
  submittedAt: timestamp("submitted_at").defaultNow().notNull(),
  submittedBy: integer("submitted_by")
    .notNull()
    .references(() => users.id),
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 타입 정의
export type SiteVisitRequest = typeof siteVisitRequests.$inferSelect;
export type NewSiteVisitRequest = typeof siteVisitRequests.$inferInsert;
export type SiteVisitRequestAttachment = typeof siteVisitRequestAttachments.$inferSelect;
export type NewSiteVisitRequestAttachment = typeof siteVisitRequestAttachments.$inferInsert;
export type VendorSiteVisitInfo = typeof vendorSiteVisitInfo.$inferSelect;
export type NewVendorSiteVisitInfo = typeof vendorSiteVisitInfo.$inferInsert;