path: root/db/schema/vendors.ts

// db/schema/vendors.ts
import {  pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core";
import { items, materials } from "./items";
import { sql, eq, relations } from "drizzle-orm";
import { users } from "./users";
import { vendorPQSubmissions } from "./pq";


// vendorTypes 테이블 생성
export const vendorTypes = pgTable("vendor_types", {
  id: serial("id").primaryKey(),
  code: varchar("code", { length: 50 }).notNull().unique(),
  nameKo: varchar("name_ko", { length: 255 }).notNull(),
  nameEn: varchar("name_en", { length: 255 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


export const vendors = pgTable("vendors", {
  id: serial("id").primaryKey(),
  vendorName: varchar("vendor_name", { length: 255 }).notNull(),
  vendorCode: varchar("vendor_code", { length: 100 }),
  taxId: varchar("tax_id", { length: 100 }).notNull(),
  address: text("address"),
  country: varchar("country", { length: 100 }),
  phone: varchar("phone", { length: 50 }),
  email: varchar("email", { length: 255 }),
  website: varchar("website", { length: 255 }),
  status: varchar("status", {
    length: 30,
    enum: [
      "PENDING_REVIEW",   // 가입 신청 중 (초기 신청)
      "IN_REVIEW",        // 심사 중
      "REJECTED",         // 심사 거부됨
      "IN_PQ",            // PQ 진행 중
      "PQ_SUBMITTED",     // PQ 제출
      "PQ_FAILED",        // PQ 실패
      "PQ_APPROVED",      // PQ 통과, 승인됨
      "APPROVED",         // PQ 통과, 승인됨
      "READY_TO_SEND",         // PQ 통과, 승인됨
      "ACTIVE",           // 활성 상태 (실제 거래 중)
      "INACTIVE",         // 비활성 상태 (일시적)
      "BLACKLISTED",      // 거래 금지 상태
    ]
  })
    .notNull()
    .default("PENDING_REVIEW"),
  vendorTypeId: integer("vendor_type_id").references(() => vendorTypes.id),
  representativeName: varchar("representative_name", { length: 255 }),
  representativeBirth: varchar("representative_birth", { length: 20 }),
  representativeEmail: varchar("representative_email", { length: 255 }),
  representativePhone: varchar("representative_phone", { length: 50 }),
  corporateRegistrationNumber: varchar("corporate_registration_number", {
    length: 100,
  }),
  items: text("items"),

  creditAgency: varchar("credit_agency", { length: 50 }),
  creditRating: varchar("credit_rating", { length: 50 }),
  cashFlowRating: varchar("cash_flow_rating", { length: 50 }),

  businessSize: varchar("business_size", { length: 255 }),

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

  // --- [시작] Oracle DB 추가 필드들 (CMCTB_VENDOR_GENERAL 기준) ---
  accountGroup: varchar("account_group", { length: 4 }), // ACNT_GRP - 계정그룹
  accountGroupType: varchar("account_group_type", { length: 2 }), // ACNT_GRP_TP - 계정그룹종류
  customerCode: varchar("customer_code", { length: 10 }), // CSTM_CD - 고객코드
  postingHoldIndicator: varchar("posting_hold_indicator", { length: 1 }), // PST_HOLD_ORDR - 전기보류지시자
  purchaseHoldIndicator: varchar("purchase_hold_indicator", { length: 1 }), // PUR_HOLD_ORDR - 구매보류지시자
  holdReason: varchar("hold_reason", { length: 200 }), // HOLD_CAUS - 보류사유
  deleteIndicator: varchar("delete_indicator", { length: 1 }), // DEL_ORDR - 삭제지시자
  companyId: varchar("company_id", { length: 6 }), // CO_ID - 법인ID
  businessType: varchar("business_type", { length: 90 }), // BIZTP - 사업유형
  industryType: varchar("industry_type", { length: 90 }), // BIZCON - 산업유형
  registrationDate: varchar("registration_date", { length: 8 }), // REG_DT - 등록일자
  registrationTime: varchar("registration_time", { length: 6 }), // REG_DTM - 등록시간
  registrarId: varchar("registrar_id", { length: 13 }), // REGR_ID - 등록자
  approvalDate: varchar("approval_date", { length: 8 }), // AGR_DT - 승인일자
  approvalTime: varchar("approval_time", { length: 6 }), // AGR_TM - 승인시간
  approverId: varchar("approver_id", { length: 13 }), // AGR_R_ID - 승인자ID
  changeDate: varchar("change_date", { length: 8 }), // CHG_DT - 변경일자
  changeTime: varchar("change_time", { length: 6 }), // CHG_TM - 변경시간
  changerId: varchar("changer_id", { length: 13 }), // CHGR_ID - 변경자ID
  nationCode: varchar("nation_code", { length: 3 }), // NTN_CD - 국가코드
  representativeTelNumber: varchar("representative_tel_number", { length: 30 }), // REP_TEL_NO - 대표전화번호
  representativeFaxNumber: varchar("representative_fax_number", { length: 31 }), // REP_FAX_NO - 대표FAX번호
  businessRegistrationNumber: varchar("business_registration_number", { length: 10 }), // BIZR_NO - 사업자번호
  corporateRegistrationNumberOracle: varchar("corporate_registration_number_oracle", { length: 18 }), // CO_REG_NO - 법인등록번호
  taxCode4: varchar("tax_code_4", { length: 54 }), // TX_CD_4 - 세금번호4
  companyEstablishmentDate: varchar("company_establishment_date", { length: 8 }), // CO_INST_DT - 설립일자
  vendorType: varchar("vendor_type", { length: 2 }), // VNDR_TP - 구매처유형
  globalTopCode: varchar("global_top_code", { length: 11 }), // GBL_TOP_CD - GLOBALTOP코드
  globalTopName: varchar("global_top_name", { length: 120 }), // GBL_TOP_NM - GLOBALTOP명
  domesticTopCode: varchar("domestic_top_code", { length: 11 }), // DMST_TOP_CD - 국내TOP코드
  domesticTopName: varchar("domestic_top_name", { length: 120 }), // DMST_TOP_NM - 국내TOP명
  businessUnitCode: varchar("business_unit_code", { length: 11 }), // BIZ_UOM_CD - 사업단위코드
  businessUnitName: varchar("business_unit_name", { length: 120 }), // BIZ_UOM_NM - 사업단위명
  dunsNumber: varchar("duns_number", { length: 11 }), // DNS_NO - DUNS번호
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  title: varchar("title", { length: 45 }), // TTL - 타이틀
  vatRegistrationNumber: varchar("vat_registration_number", { length: 20 }), // VAT_REG_NO - 부가세등록번호
  giroVendorIndicator: varchar("giro_vendor_indicator", { length: 1 }), // GIRO_VNDR_ORDR - 지로VENDOR지시자
  vendorName1: varchar("vendor_name_1", { length: 120 }), // VNDRNM_1 - Vendor명1
  vendorName2: varchar("vendor_name_2", { length: 120 }), // VNDRNM_2 - VENDOR명2
  vendorName3: varchar("vendor_name_3", { length: 120 }), // VNDRNM_3 - VENDOR명3
  vendorName4: varchar("vendor_name_4", { length: 120 }), // VNDRNM_4 - VENDOR명4
  vendorNameAbbreviation1: varchar("vendor_name_abbreviation_1", { length: 60 }), // VNDRNM_ABRV_1 - VENDOR명약어1
  vendorNameAbbreviation2: varchar("vendor_name_abbreviation_2", { length: 60 }), // VNDRNM_ABRV_2 - VENDOR명약어2
  potentialVendorCode: varchar("potential_vendor_code", { length: 10 }), // PTNT_VNDRCD - 잠재VENDOR코드
  address1: varchar("address_1", { length: 120 }), // ADR_1 - 주소1
  address2: varchar("address_2", { length: 512 }), // ADR_2 - 주소2
  qualityManagerName: varchar("quality_manager_name", { length: 60 }), // QLT_CHRGR_NM - 품질담당자명
  qualityManagerTelNumber: varchar("quality_manager_tel_number", { length: 30 }), // QLT_CHRGR_TELNO - 품질담당자전화번호
  qualityManagerEmail: varchar("quality_manager_email", { length: 241 }), // QLT_CHRGR_EMAIL - 품질담당자이메일
  subWorkplaceSequence: varchar("sub_workplace_sequence", { length: 16 }), // SB_WKA_SEQ - SUB작업장순서
  overlapCauseCode: varchar("overlap_cause_code", { length: 2 }), // OVLAP_CAUS_CD - 중복사유코드
  documentType: varchar("document_type", { length: 3 }), // DOC_TP - 문서유형
  documentNumber: varchar("document_number", { length: 25 }), // DOC_NO - 문서번호
  partialDocument: varchar("partial_document", { length: 3 }), // PTN_DOC - 부분문서
  documentVersion: varchar("document_version", { length: 2 }), // DOC_VER - 문서버전
  inboundFlag: varchar("inbound_flag", { length: 1 }), // INB_FLAG - 인바운드플래그
  deleteHoldIndicator: varchar("delete_hold_indicator", { length: 1 }), // DEL_HOLD_ORDR - 삭제보류지시자
  purchaseHoldDate: varchar("purchase_hold_date", { length: 8 }), // PUR_HOLD_DT - 구매보류일자
  postBox: varchar("post_box", { length: 30 }), // POBX - 사서함
  internationalLocationCheckNumber: integer("international_location_check_number"), // INTL_LCTN_CHK_NUM - 국제LOCATION점검숫자
  withholdingTaxGenderKey: varchar("withholding_tax_gender_key", { length: 1 }), // SRCETX_RP_SEX_KEY - 원천세의무자성별키
  vendorContractManager1: varchar("vendor_contract_manager_1", { length: 105 }), // VNDR_CNRT_CHRGR_1 - VENDOR계약담당자1
  vendorContractManager2: varchar("vendor_contract_manager_2", { length: 105 }), // VNDR_CNRT_CHRGR_2 - VENDOR계약담당자2
  representativeResidentNumber: varchar("representative_resident_number", { length: 13 }), // REPR_RESNO - 대표생년월일
  companyVolume: varchar("company_volume", { length: 1 }), // CO_VLM - 기업규모
  // --- [끝] Oracle DB 추가 필드들 (CMCTB_VENDOR_GENERAL 기준) ---
});

// ------- [시작] MDZ 인터페이스 목적 테이블 추가 -------------

// 벤더 업무그룹 테이블 (CMCTB_VENDOR_GRP 대응)
export const vendorBusinessGroups = pgTable("vendor_business_groups", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  businessGroupCode: varchar("business_group_code", { length: 3 }).notNull(), // BIZ_GRP_CD - 업무그룹코드
  createdDate: varchar("created_date", { length: 8 }), // CRTE_DT - 생성일자
  createdTime: varchar("created_time", { length: 6 }), // CRTE_TM - 생성시간
  creatorId: varchar("creator_id", { length: 13 }), // CRTER_ID - 생성자ID
  changeDate: varchar("change_date", { length: 8 }), // CHG_DT - 변경일자
  changeTime: varchar("change_time", { length: 6 }), // CHG_TM - 변경시간
  changerId: varchar("changer_id", { length: 13 }), // CHGR_ID - 변경자ID
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 사내협력사 벤더 테이블 (CMCTB_VENDOR_INCO 대응)
export const vendorInternalPartners = pgTable("vendor_internal_partners", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  vendorName: varchar("vendor_name", { length: 120 }), // VNDRNM - VENDOR코명
  representativeName: varchar("representative_name", { length: 30 }), // REPR_NM - 대표자명
  partnerType: varchar("partner_type", { length: 1 }), // PRTNR_GB - 협력사구분
  internalPartnerCode: varchar("internal_partner_code", { length: 3 }), // INCO_PRTNR_CD - 사내협력사코드
  internalPartnerWorkplace1: varchar("internal_partner_workplace_1", { length: 1 }), // INCO_PRTNR_WKA_1 - 사내협력사작업장1
  internalPartnerWorkplace2: varchar("internal_partner_workplace_2", { length: 1 }), // INCO_PRTNR_WKA_2 - 사내협력사작업장2
  internalPartnerWorkplace3: varchar("internal_partner_workplace_3", { length: 1 }), // INCO_PRTNR_WKA_3 - 사내협력사작업장3
  jobTypeCode: varchar("job_type_code", { length: 2 }), // JBTYPE_CD - 직종코드
  jobTypeCode2: varchar("job_type_code_2", { length: 2 }), // JBTYPE_CD_2 - 직종코드2
  individualCorporateType: varchar("individual_corporate_type", { length: 2 }), // INDV_CO_GB - 개인법인구분
  internalFoundationYn: varchar("internal_foundation_yn", { length: 1 }), // INCO_FOND_YN - 사내창립유무
  dockNumber: varchar("dock_number", { length: 25 }), // DOCK_NO - 도크번호
  companyInputDate: varchar("company_input_date", { length: 8 }), // OCMP_INP_DT - 당사투입일자
  internalWithdrawalDate: varchar("internal_withdrawal_date", { length: 8 }), // INCO_DUSE_DT - 사내철수일자
  industrialInsurancePremiumRate: integer("industrial_insurance_premium_rate"), // INDST_INS_PMRAT - 산재보험요율
  contractPerformanceGuarantee: integer("contract_performance_guarantee"), // CNRT_PFRM_GRAMT - 계약이행보증금
  wageRate: integer("wage_rate"), // WGE_RAT - 임금율
  correspondingDepartmentCode1: varchar("corresponding_department_code_1", { length: 30 }), // CRSPD_DEPTCD_1 - 해당부서코드1
  correspondingDepartmentCode2: varchar("corresponding_department_code_2", { length: 30 }), // CRSPD_DEPTCD_2 - 해당부서코드2
  correspondingTeamBelonging: varchar("corresponding_team_belonging", { length: 100 }), // CRSPD_TEAM_BLNG - 해당팀소속
  internalPartnerItem1: varchar("internal_partner_item_1", { length: 120 }), // INCO_PRTNR_ITM_1 - 사내협력사종목1
  internalPartnerItem2: varchar("internal_partner_item_2", { length: 120 }), // INCO_PRTNR_ITM_2 - 사내협력사종목2
  officeLocation: varchar("office_location", { length: 240 }), // OFC_LOC - 사무실위치
  representativeCompanyCareer: varchar("representative_company_career", { length: 300 }), // REP_OCMP_CARR - 대표당사경력
  internalWithdrawalReason: varchar("internal_withdrawal_reason", { length: 600 }), // INCO_DUSE_CAUS - 사내철수사유
  telephoneNumber: varchar("telephone_number", { length: 30 }), // TEL_NO - 전화번호
  address1: varchar("address_1", { length: 200 }), // ADR1 - 주소
  address2: varchar("address_2", { length: 200 }), // ADR2 - 상세주소
  oldVendorCode: varchar("old_vendor_code", { length: 10 }), // OLD_VNDRCD - 이전 VENDOR코드
  treeNumber: varchar("tree_number", { length: 1 }), // TREE_NUM - 하위 VENDOR 갯수
  createdDate: varchar("created_date", { length: 8 }), // CRTE_DT - 생성일자
  createdTime: varchar("created_time", { length: 6 }), // CRTE_TM - 생성시간
  createdUserId: varchar("created_user_id", { length: 13 }), // CRTE_USR_ID - 생성사용자ID
  changeDate: varchar("change_date", { length: 8 }), // CHG_DT - 수정일자
  changeTime: varchar("change_time", { length: 6 }), // CHG_TM - 수정시간
  changeUserId: varchar("change_user_id", { length: 13 }), // CHG_USR_ID - 수정사용자ID
  upperJobType: varchar("upper_job_type", { length: 2 }), // UPR_JBTYPE - 직종단가
  supplierBusinessPlaceCode: varchar("supplier_business_place_code", { length: 4 }), // ZBYBP - 공급받는자 종사업장 식별코드
  remark: varchar("remark", { length: 4000 }), // RMK - 비고
  withdrawalPlanYn: varchar("withdrawal_plan_yn", { length: 1 }), // WDL_PLN_YN - 철수예정유무
  wageDelayOccurrence: varchar("wage_delay_occurrence", { length: 8 }), // WGE_DELY_DVL - 임금체불발생
  escrowYn: varchar("escrow_yn", { length: 1 }), // ESCROW_YN - 에스크로가입유무
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 벤더 구매조직 테이블 (CMCTB_VENDOR_PORG 대응)
export const vendorPurchaseOrganizations = pgTable("vendor_purchase_organizations", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  purchaseOrgCode: varchar("purchase_org_code", { length: 4 }).notNull(), // PUR_ORG_CD - 구매조직
  purchaseOrderCurrency: varchar("purchase_order_currency", { length: 5 }), // PUR_ORD_CUR - 구매오더통화
  paymentTerms: varchar("payment_terms", { length: 4 }), // SPLY_COND - 지급조건
  deliveryTerms1: varchar("delivery_terms_1", { length: 3 }), // DL_COND_1 - 인도조건1
  deliveryTerms2: varchar("delivery_terms_2", { length: 90 }), // DL_COND_2 - 인도조건2
  calculationSchemaGroup: varchar("calculation_schema_group", { length: 2 }), // CALC_SHM_GRP - 계산스키마그룹
  grBasedInvoiceVerification: varchar("gr_based_invoice_verification", { length: 1 }), // GR_BSE_INVC_VR - GR기준송장검증
  automaticPurchaseOrderIndicator: varchar("automatic_purchase_order_indicator", { length: 1 }), // AT_PUR_ORD_ORDR - 자동구매오더지시자
  purchaseHoldIndicator: varchar("purchase_hold_indicator", { length: 1 }), // PUR_HOLD_ORDR - 구매보류지시자
  deleteIndicator: varchar("delete_indicator", { length: 1 }), // DEL_ORDR - 삭제지시자
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  orderConfirmationRequestIndicator: varchar("order_confirmation_request_indicator", { length: 1 }), // ORD_CNFM_REQ_ORDR - 오더확인요청지시자
  salesManagerName: varchar("sales_manager_name", { length: 120 }), // SALE_CHRGR_NM - 영업담당자명
  vendorTelephoneNumber: varchar("vendor_telephone_number", { length: 30 }), // VNDR_TELNO - VENDOR전화번호
  confirmationControlKey: varchar("confirmation_control_key", { length: 4 }), // CNFM_CTL_KEY - 확정제어키
  purchaseHoldDate: varchar("purchase_hold_date", { length: 8 }), // PUR_HOLD_DT - 구매보류일자
  purchaseHoldReason: varchar("purchase_hold_reason", { length: 120 }), // PUR_HOLD_CAUS - 구매보류사유
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 벤더 대표자 이메일 테이블 (CMCTB_VENDOR_REPREMAIL 대응)
export const vendorRepresentativeEmails = pgTable("vendor_representative_emails", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  addressNumber: varchar("address_number", { length: 10 }), // ADR_NO - 주소번호
  representativeSequence: varchar("representative_sequence", { length: 3 }).notNull(), // REPR_SER - 대표자순번
  validStartDate: varchar("valid_start_date", { length: 8 }).notNull(), // VLD_ST_DT - 유효시작일자
  emailAddress: varchar("email_address", { length: 241 }), // EMAIL_ADR - 이메일주소
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 벤더 대표자 팩스 테이블 (CMCTB_VENDOR_REPRFAX 대응)
export const vendorRepresentativeFaxes = pgTable("vendor_representative_faxes", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  addressNumber: varchar("address_number", { length: 10 }), // ADR_NO - 주소번호
  representativeSequence: varchar("representative_sequence", { length: 3 }).notNull(), // REPR_SER - 대표자순번
  validStartDate: varchar("valid_start_date", { length: 8 }).notNull(), // VLD_ST_DT - 유효시작일자
  nationCode: varchar("nation_code", { length: 3 }), // NTN_CD - 국가코드
  faxNumber: varchar("fax_number", { length: 30 }), // FAXNO - 팩스번호
  faxExtensionNumber: varchar("fax_extension_number", { length: 10 }), // FAX_ETS_NO - 팩스내선번호
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 벤더 대표자 전화번호 테이블 (CMCTB_VENDOR_REPRTEL 대응)
export const vendorRepresentativeTelephones = pgTable("vendor_representative_telephones", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  addressNumber: varchar("address_number", { length: 10 }), // ADR_NO - 주소번호
  representativeSequence: varchar("representative_sequence", { length: 3 }).notNull(), // REPR_SER - 대표자순번
  validStartDate: varchar("valid_start_date", { length: 8 }).notNull(), // VLD_ST_DT - 유효시작일자
  nationCode: varchar("nation_code", { length: 3 }), // NTN_CD - 국가코드
  telephoneNumber: varchar("telephone_number", { length: 30 }), // TELNO - 전화번호
  extensionNumber: varchar("extension_number", { length: 10 }), // ETX_NO - 내선번호
  mobileIndicator: varchar("mobile_indicator", { length: 1 }), // HP_ORDR - 핸드폰지시자
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 벤더 대표자 URL 테이블 (CMCTB_VENDOR_REPRURL 대응)
export const vendorRepresentativeUrls = pgTable("vendor_representative_urls", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  addressNumber: varchar("address_number", { length: 10 }), // ADR_NO - 주소번호
  representativeSequence: varchar("representative_sequence", { length: 3 }).notNull(), // REPR_SER - 대표자순번
  validStartDate: varchar("valid_start_date", { length: 8 }).notNull(), // VLD_ST_DT - 유효시작일자
  url: varchar("url", { length: 2048 }), // URL - URL
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 벤더 세금번호 테이블 (CMCTB_VENDOR_TAXNUM 대응)
export const vendorTaxNumbers = pgTable("vendor_tax_numbers", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  taxNumberCategory: varchar("tax_number_category", { length: 4 }).notNull(), // TX_NO_CTG - 세금번호범주
  businessPartnerTaxNumber: varchar("business_partner_tax_number", { length: 20 }), // BIZ_PTNR_TX_NO - 사업파트너세금번호
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 벤더 파트너역할 테이블 (CMCTB_VENDOR_VFPN 대응)
export const vendorPartnerFunctions = pgTable("vendor_partner_functions", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  purchaseOrgCode: varchar("purchase_org_code", { length: 4 }).notNull(), // PUR_ORG_CD - 구매조직
  vendorSubNumber: varchar("vendor_sub_number", { length: 6 }).notNull(), // VNDR_SUB_NO - VENDOR서브번호
  plantCode: varchar("plant_code", { length: 4 }).notNull(), // PLNT_CD - 플랜트코드
  partnerFunction: varchar("partner_function", { length: 2 }).notNull(), // PTNR_SKL - 파트너기능
  partnerCounter: varchar("partner_counter", { length: 3 }).notNull(), // PTNR_CNT - 파트너카운터
  otherReferenceVendorCode: varchar("other_reference_vendor_code", { length: 10 }), // ETC_REF_VNDRCD - 기타참조VENDOR코드
  defaultPartnerIndicator: varchar("default_partner_indicator", { length: 1 }), // BSE_PTNR_ORDR - 기본파트너지시자
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 벤더 원천세 테이블 (CMCTB_VENDOR_WHTHX 대응)
export const vendorWithholdingTax = pgTable("vendor_withholding_tax", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  companyCode: varchar("company_code", { length: 4 }).notNull(), // CO_CD - 회사코드
  withholdingTaxType: varchar("withholding_tax_type", { length: 2 }).notNull(), // SRCE_TX_TP - 원천세유형
  withholdingTaxRelatedIndicator: varchar("withholding_tax_related_indicator", { length: 1 }), // SRCE_TX_REL_ORDR - 원천세관련지시자
  recipientType: varchar("recipient_type", { length: 2 }), // RECIP_TP - 수취인유형
  withholdingTaxIdentificationNumber: varchar("withholding_tax_identification_number", { length: 16 }), // SRCE_TX_IDENT_NO - 원천세식별번호
  withholdingTaxCode: varchar("withholding_tax_code", { length: 2 }), // SRCE_TX_NO - 원천세코드
  exemptionCertificateNumber: varchar("exemption_certificate_number", { length: 15 }), // DCHAG_CERT_NO - 면제증명서번호
  exemptionRate: integer("exemption_rate"), // DCHAG_RAT - 면제율
  exemptionStartDate: varchar("exemption_start_date", { length: 8 }), // DCHAG_ST_DT - 면제시작일자
  exemptionEndDate: varchar("exemption_end_date", { length: 8 }), // DCHAG_ED_DT - 면제종료일
  exemptionReason: varchar("exemption_reason", { length: 200 }), // DCHAG_CAUS - 면제사유
  interfaceDate: varchar("interface_date", { length: 8 }), // IF_DT - 인터페이스일자
  interfaceTime: varchar("interface_time", { length: 6 }), // IF_TM - 인터페이스시간
  interfaceStatus: varchar("interface_status", { length: 1 }), // IF_STAT - 인터페이스상태
  interfaceMessage: varchar("interface_message", { length: 100 }), // IF_MSG - 인터페이스메시지
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// ------- [끝] MDZ 인터페이스 목적 테이블 추가 -------------

export const vendorContacts = pgTable("vendor_contacts", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  contactName: varchar("contact_name", { length: 255 }).notNull(),
  contactPosition: varchar("contact_position", { length: 100 }),
  contactEmail: varchar("contact_email", { length: 255 }).notNull(),
  contactPhone: varchar("contact_phone", { length: 50 }),
  isPrimary: boolean("is_primary").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


export const vendorPossibleItems = pgTable("vendor_possible_items", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  // itemId: integer("item_id"), // 별도 item 테이블 연동시
  itemCode: varchar("item_code", { length: 100 })
    .notNull()
    .references(() => items.itemCode, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


export const vendorPossibleMateirals = pgTable("vendor_possible_materials", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  // itemId: integer("item_id"), // 별도 item 테이블 연동시
  itemCode: varchar("item_code", { length: 100 })
    .notNull()
    .references(() => materials.itemCode, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const vendorItemsView = pgView("vendor_items_view").as((qb) => {
  return qb
    .select({
      // vendorPossibleItems의 "id" -> "vendorItemId"
      vendorItemId: vendorPossibleItems.id,
      vendorId: vendorPossibleItems.vendorId,

      // items의 "id" -> "itemId"
      // itemId: items.id,
      itemName: items.itemName,
      itemCode: items.itemCode,
      description: items.description,

      createdAt: vendorPossibleItems.createdAt,
      updatedAt: vendorPossibleItems.updatedAt,
    })
    .from(vendorPossibleItems)
    .leftJoin(items, eq(vendorPossibleItems.itemCode, items.itemCode))
})

export const vendorMaterialsView = pgView("vendor_materials_view").as((qb) => {
  return qb
    .select({
      // vendorPossibleItems의 "id" -> "vendorItemId"
      vendorItemId: vendorPossibleMateirals.id,
      vendorId: vendorPossibleMateirals.vendorId,

      // items의 "id" -> "itemId"
      // itemId: items.id,
      itemName: materials.itemName,
      itemCode: materials.itemCode,
      description: materials.description,
      unitOfMeasure: materials.unitOfMeasure,
      steelType: materials.steelType,
      gradeMaterial: materials.gradeMaterial,

      createdAt: vendorPossibleMateirals.createdAt,
      updatedAt: vendorPossibleMateirals.updatedAt,
    })
    .from(vendorPossibleMateirals)
    .leftJoin(materials, eq(vendorPossibleMateirals.itemCode, materials.itemCode))
})

export const vendorAttachments = pgTable("vendor_attachments", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").references(() => vendors.id),
  fileName: varchar("file_name", { length: 255 }).notNull(),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  attachmentType: varchar("attachment_type", {
    length: 50,
  }).default("GENERAL"),

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


export type Vendor = typeof vendors.$inferSelect
export type VendorContact = typeof vendorContacts.$inferSelect
export type VendorItem = typeof vendorPossibleItems.$inferSelect
export type VendorAttach = typeof vendorAttachments.$inferSelect

export type VendorWithAttachments = Vendor & {
  hasAttachments?: boolean;
  attachmentsList?: VendorAttach[];
}

export type VendorItemsView = typeof vendorItemsView.$inferSelect
export type VendorMaterialsView = typeof vendorMaterialsView.$inferSelect

// ------- [시작] MDG 대응을 위한 새로운 테이블 타입 정의 -------------
export type VendorBusinessGroup = typeof vendorBusinessGroups.$inferSelect
export type VendorInternalPartner = typeof vendorInternalPartners.$inferSelect
export type VendorPurchaseOrganization = typeof vendorPurchaseOrganizations.$inferSelect
export type VendorRepresentativeEmail = typeof vendorRepresentativeEmails.$inferSelect
export type VendorRepresentativeFax = typeof vendorRepresentativeFaxes.$inferSelect
export type VendorRepresentativeTelephone = typeof vendorRepresentativeTelephones.$inferSelect
export type VendorRepresentativeUrl = typeof vendorRepresentativeUrls.$inferSelect
export type VendorTaxNumber = typeof vendorTaxNumbers.$inferSelect
export type VendorPartnerFunction = typeof vendorPartnerFunctions.$inferSelect
export type VendorWithholdingTax = typeof vendorWithholdingTax.$inferSelect
// ------- [끝] MDG 대응을 위한 새로운 테이블 타입 정의 -------------


export const vendorCandidates = pgTable("vendor_candidates", {
  id: serial("id").primaryKey(),
  companyName: varchar("company_name", { length: 255 }).notNull(),
  contactEmail: varchar("contact_email", { length: 255 }),
  contactPhone: varchar("contact_phone", { length: 50 }),
  
  taxId: varchar("tax_id", { length: 100 }).notNull(),
  address: text("address"),

  country: varchar("country", { length: 100 }),
  // 웹 크롤링 등으로 얻은 상태나 분류
  source: varchar("source", { length: 100 }),        // 수집 출처
  status: varchar("status", {
    length: 30,
    enum: [
      "COLLECTED",  // 단순 데이터 수집 완료
      "INVITED",    // 초청 메일 발송
      "DISCARDED",  // 불필요, 검토 후 배제됨
    ],
  })
    .notNull()
    .default("COLLECTED"),
  remark: text("remark"),
  items: text("items").notNull(),
  vendorId: integer("vendor_id")
    .references(() => vendors.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export type VendorCandidates = typeof vendorCandidates.$inferSelect;


export const vendorDetailView = pgView("vendor_detail_view").as((qb) => {
  return qb
    .select({
      // 협력업체 기본 정보
      id: vendors.id,
      vendorName: vendors.vendorName,
      vendorCode: vendors.vendorCode,
      taxId: vendors.taxId,
      address: vendors.address,
      businessSize:vendors.businessSize,
      country: vendors.country,
      phone: vendors.phone,
      email: vendors.email,
      website: vendors.website,
      status: vendors.status,
      representativeName: vendors.representativeName,
      representativeBirth: vendors.representativeBirth,
      representativeEmail: vendors.representativeEmail,
      representativePhone: vendors.representativePhone,
      corporateRegistrationNumber: vendors.corporateRegistrationNumber,
      creditAgency: vendors.creditAgency,
      creditRating: vendors.creditRating,
      cashFlowRating: vendors.cashFlowRating,
      createdAt: vendors.createdAt,
      updatedAt: vendors.updatedAt,
      
      // 연락처 정보 (수정된 버전)
      contacts: sql<string>`
        (SELECT COALESCE(
          json_agg(
            json_build_object(
              'id', c.id,
              'contactName', c.contact_name,
              'contactPosition', c.contact_position,
              'contactEmail', c.contact_email,
              'contactPhone', c.contact_phone,
              'isPrimary', c.is_primary
            )
          ),
          '[]'::json
        )
        FROM vendor_contacts c
        WHERE c.vendor_id = vendors.id)
      `.as("contacts"),
      
      // 첨부파일 정보 (수정된 버전)
      attachments: sql<string>`
        (SELECT COALESCE(
          json_agg(
            json_build_object(
              'id', a.id,
              'fileName', a.file_name,
              'filePath', a.file_path,
              'attachmentType', a.attachment_type,
              'createdAt', a.created_at
            )
            ORDER BY a.attachment_type, a.created_at DESC
          ),
          '[]'::json
        )
        FROM vendor_attachments a
        WHERE a.vendor_id = vendors.id)
      `.as("attachments"),
      
      // 첨부파일 수 (수정된 버전)
      attachmentCount: sql<number>`
        (SELECT COUNT(*)
        FROM vendor_attachments a
        WHERE a.vendor_id = vendors.id)
      `.as("attachment_count"),
      
      // 연락처 수 (수정된 버전)
      contactCount: sql<number>`
        (SELECT COUNT(*) 
        FROM vendor_contacts c
        WHERE c.vendor_id = vendors.id)
      `.as("contact_count")
    })
    .from(vendors)
});

// 타입 정의
export type VendorDetailView = typeof vendorDetailView.$inferSelect;


// 관계 설정 (DrizzleORM을 사용한다고 가정)
export const vendorRelations = relations(vendors, ({ one }) => ({
  vendorType: one(vendorTypes, {
    fields: [vendors.vendorTypeId],
    references: [vendorTypes.id],
  }),
}));

export const vendorTypeRelations = relations(vendorTypes, ({ many }) => ({
  vendors: many(vendors),
}));

export type VendorTypes = typeof vendorTypes.$inferSelect;




export const vendorCandidatesWithVendorInfo = pgView("vendor_candidates_with_vendor_info").as((qb) => {
  return qb
    .select({
      // ----------------------------------------
      // 1) vendorCandidates 기본 필드
      id: vendorCandidates.id,
      companyName: vendorCandidates.companyName,
      contactEmail: vendorCandidates.contactEmail,
      contactPhone: vendorCandidates.contactPhone,
      taxId: vendorCandidates.taxId,
      address: vendorCandidates.address,
      country: vendorCandidates.country,
      source: vendorCandidates.source,
      status: vendorCandidates.status,
      items: vendorCandidates.items,
      remark: vendorCandidates.remark,
      // remark, items, vendorId 등 필요한 필드도 추가
      createdAt: vendorCandidates.createdAt,
      updatedAt: vendorCandidates.updatedAt,

      // ----------------------------------------
      // 2) vendors 조인해서 가져올 필드
      vendorName: vendors.vendorName,
      vendorCode: vendors.vendorCode,
      vendorCreatedAt: sql<Date>`${vendors.createdAt}`.as("vendor_created_at"),

      // ----------------------------------------
      // 3) 마지막 상태 변경 시각, 변경자 (action = 'status_change')
      lastStatusChangeAt: sql<Date>`(
        SELECT l2."created_at"
          FROM "vendor_candidate_logs" l2
         WHERE l2."vendor_candidate_id" = "vendor_candidates"."id"
           AND l2."action" = 'status_change'
         ORDER BY l2."created_at" DESC
         LIMIT 1
      )`.as("last_status_change_at"),

      lastStatusChangeBy: sql<string>`(
        SELECT u."name"
          FROM "users" u
          JOIN "vendor_candidate_logs" l3
            ON l3."user_id" = u."id"
         WHERE l3."vendor_candidate_id" = "vendor_candidates"."id"
           AND l3."action" = 'status_change'
         ORDER BY l3."created_at" DESC
         LIMIT 1
      )`.as("last_status_change_by"),

      // ----------------------------------------
      // 4) 마지막 초청(Invite) 시각, 초청자 (action = 'invite_sent')
      lastInvitationAt: sql<Date>`(
        SELECT l4."created_at"
          FROM "vendor_candidate_logs" l4
         WHERE l4."vendor_candidate_id" = "vendor_candidates"."id"
           AND l4."action" = 'invite_sent'
         ORDER BY l4."created_at" DESC
         LIMIT 1
      )`.as("last_invitation_at"),

      lastInvitationBy: sql<string>`(
        SELECT u2."name"
          FROM "users" u2
          JOIN "vendor_candidate_logs" l5
            ON l5."user_id" = u2."id"
         WHERE l5."vendor_candidate_id" = "vendor_candidates"."id"
           AND l5."action" = 'invite_sent'
         ORDER BY l5."created_at" DESC
         LIMIT 1
      )`.as("last_invitation_by"),
    })
    .from(vendorCandidates)
    .leftJoin(vendors, eq(vendorCandidates.vendorId, vendors.id));
});

export type VendorCandidatesWithVendorInfo = typeof vendorCandidatesWithVendorInfo.$inferSelect;


export const vendorsWithTypesView = pgView("vendors_with_types").as((qb) => {
  return qb
    .select({
      // Vendor fields
      id: sql<number>`${vendors.id}`.as("id"),
      vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
      vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"),
      taxId: sql<string>`${vendors.taxId}`.as("tax_id"),
      address: sql<string>`${vendors.address}`.as("address"),
      country: sql<string>`${vendors.country}`.as("country"),
      phone: sql<string>`${vendors.phone}`.as("phone"),
      email: sql<string>`${vendors.email}`.as("email"),
      businessSize: sql<string>`${vendors.businessSize}`.as("business_size"),
      website: sql<string>`${vendors.website}`.as("website"),
      status: sql<string>`${vendors.status}`.as("status"),
      vendorTypeId: sql<number>`${vendors.vendorTypeId}`.as("vendor_type_id"),
      representativeName: sql<string>`${vendors.representativeName}`.as("representative_name"),
      representativeBirth: sql<string>`${vendors.representativeBirth}`.as("representative_birth"),
      representativeEmail: sql<string>`${vendors.representativeEmail}`.as("representative_email"),
      representativePhone: sql<string>`${vendors.representativePhone}`.as("representative_phone"),
      corporateRegistrationNumber: sql<string>`${vendors.corporateRegistrationNumber}`.as("corporate_registration_number"),
      items: sql<string>`${vendors.items}`.as("items"),
      creditAgency: sql<string>`${vendors.creditAgency}`.as("credit_agency"),
      creditRating: sql<string>`${vendors.creditRating}`.as("credit_rating"),
      cashFlowRating: sql<string>`${vendors.cashFlowRating}`.as("cash_flow_rating"),
      createdAt: sql<Date>`${vendors.createdAt}`.as("created_at"),
      updatedAt: sql<Date>`${vendors.updatedAt}`.as("updated_at"),

      // Vendor type fields
      vendorTypeName: sql<string>`${vendorTypes.nameKo}`.as("vendor_type_name"),
      vendorTypeNameEn: sql<string>`${vendorTypes.nameEn}`.as("vendor_type_name_en"),
      vendorTypeCode: sql<string>`${vendorTypes.code}`.as("vendor_type_code"),

      // Computed vendor category field
      vendorCategory: sql<string>`
        CASE
          WHEN ${vendors.status} = 'ACTIVE' THEN '정규업체'
          WHEN ${vendors.status} IN ('INACTIVE', 'BLACKLISTED', 'REJECTED') THEN ''
          ELSE '잠재업체'
        END
      `.as("vendor_category")
    })
    .from(vendors)
    .leftJoin(
      vendorTypes,
      sql`${vendors.vendorTypeId} = ${vendorTypes.id}`
    );
});


// You can also create interfaces for the view
export type VendorWithType = typeof vendorsWithTypesView.$inferSelect;