import { serial, varchar, timestamp, text, boolean, numeric, decimal, integer, real, bigint, uniqueIndex } from "drizzle-orm/pg-core";
import { pgSchema } from "drizzle-orm/pg-core";

export const sErpSchema = pgSchema("S-ERP");

export const tbSAPEquipInfo = sErpSchema.table("TB_SAP_EquipInfo", {
  id: serial().primaryKey(),
  Equipment: varchar({ length: 20 }), // pk
  Description: varchar({ length: 100 }),
  EquipCategory: varchar({ length: 2 }),
  SystemStatus: varchar({ length: 10 }),
  UserStatus: varchar({ length: 10 }),
  ObjectType: varchar({ length: 10 }),
  ABCIndicator: varchar({ length: 2 }),
  PlanningPlant: varchar({ length: 10 }),
  PlannerGroup: varchar({ length: 10 }),
  MainWorkCenter: varchar({ length: 10 }),
  CostCenter: varchar({ length: 10 }),
  CatalogProfile: varchar({ length: 10 }),
  FunctionLocation: varchar({ length: 20 }),
  FunctionLocationDesc: varchar({ length: 100 }),
  SuperordEquip: varchar({ length: 10 }),
  COL1: varchar({ length: 10 }),
  ConstType: varchar({ length: 10 }),
  AssetNo: varchar({ length: 10 }),
  TechnivalIdenNo: varchar({ length: 10 }),
  Class: varchar({ length: 10 }),
  MaintenancePlant: varchar({ length: 10 }),
  PlantSection: varchar({ length: 10 }),
  PlantSectionDescription: varchar({ length: 10 }),
  ObjectTypeDesc: varchar({ length: 100 }),
  MainWorkCenterDesc: varchar({ length: 100 }),
  CostCenterDesc: varchar({ length: 100 }),
  ObjectNumber: varchar({ length: 30 }),
  SubAssetNo: varchar({ length: 10 }),
  AuthorizGroup: varchar({ length: 10 }),
  Weight: numeric({ precision: 16, scale: 3 }),
  WeightUnit: varchar({ length: 10 }),
  SizeDimension: varchar({ length: 20 }),
  InventoryNumber: varchar({ length: 50 }),
  StartUpDate: varchar({ length: 10 }),
  AcquistnValue: decimal({ precision: 19, scale: 4 }), // MONEY 타입을 decimal로 변환
  AcquistnValueCurr: varchar({ length: 10 }),
  AcquistionDate: varchar({ length: 10 }),
  Manufacturer: varchar({ length: 50 }),
  ModelNumber: varchar({ length: 10 }),
  ManufPartNo: varchar({ length: 10 }),
  ManufCountry: varchar({ length: 4 }),
  ManufSerialNo: varchar({ length: 50 }),
  ConstrYr: varchar({ length: 4 }),
  ConstrMth: varchar({ length: 2 }),
  Location: varchar({ length: 10 }),
  Room: varchar({ length: 10 }),
  WorkCenter: varchar({ length: 10 }),
  PPWorkCenter: varchar({ length: 10 }),
  PPWorkCenterDesc: varchar({ length: 100 }),
  SortField: varchar({ length: 10 }),
  CompanyCode: varchar({ length: 10 }),
  BusinessArea: varchar({ length: 10 }),
  WBSElement: varchar({ length: 10 }),
  MainWorkCtrPlant: varchar({ length: 10 }),
  Position: varchar({ length: 10 }),
  FinancialManagementArea: varchar({ length: 10 }),
  CommitmentItem: varchar({ length: 20 }),
  SysStatus: varchar({ length: 1 }),
}, (table) => ({
  // Equipment 컬럼에 UNIQUE 인덱스 추가
  uniqueEquipment: uniqueIndex("unique_equipment").on(table.Equipment),
}));

export const tbSAPOrder = sErpSchema.table("TB_SAP_Order", {
  id: serial().primaryKey(),
  BreakdownIndicator: varchar({ length: 50 }),
  Order: varchar({ length: 50 }), // pk
  OrderType: varchar({ length: 50 }),
  OrderDescription: varchar({ length: 150 }),
  MainWorkCenterDesc: varchar({ length: 100 }),
  FunctionalLocation: varchar({ length: 50 }),
  Equipment: varchar({ length: 50 }),
  EquipmentDescriprion: varchar({ length: 100 }),
  EquipmentSortField: varchar({ length: 50 }),
  TagNumber: varchar({ length: 50 }),
  Notification: varchar({ length: 50 }),
  Priority: varchar({ length: 50 }),
  PriorityDesc: varchar({ length: 50 }),
  PlantSection: varchar({ length: 50 }),
  PMActivityType: varchar({ length: 10 }),
  MaintenancePlan: varchar({ length: 50 }),
  StartDate: varchar({ length: 10 }),
  FinishDate: varchar({ length: 10 }),
  SystemStatus: varchar({ length: 50 }),
  UserStatus: varchar({ length: 50 }),
  CreatedBy: varchar({ length: 50 }),
  Name: varchar({ length: 50 }),
  PlanningPlant: varchar({ length: 50 }),
  PlannerGroupName: varchar({ length: 50 }),
  MainWorkCenter: varchar({ length: 50 }),
  MaintenancePlant: varchar({ length: 50 }),
  PlantSectionDescription: varchar({ length: 50 }),
  COL1: varchar({ length: 50 }),
  COL2: varchar({ length: 50 }),
  BasicStartTime: varchar({ length: 50 }),
  ActualStartDate: varchar({ length: 50 }),
  ActualFinishTime: varchar({ length: 50 }),
  PlanCost: bigint({ mode: 'number' }),
  ActualCost: bigint({ mode: 'number' }),
  SettlementCost: bigint({ mode: 'number' }),
  Balance: bigint({ mode: 'number' }),
  Currency: varchar({ length: 50 }),
  EquiCategory: varchar({ length: 50 }),
  ObjectType: varchar({ length: 50 }),
  ABCOmdocatior: varchar({ length: 50 }),
  EWRNo: varchar({ length: 50 }),
}, (table) => ({
  // Order 컬럼에 UNIQUE 인덱스 추가
  uniqueOrder: uniqueIndex("unique_order").on(table.Order),
}));

export const tbSAPOrderConfirm = sErpSchema.table("TB_SAP_OrderConfirm", {
  id: serial().primaryKey(),
  Confirmation: varchar({ length: 50 }), // pk
  Count: varchar({ length: 50 }), // pk
  Order: varchar({ length: 50 }),
  OpAc: varchar({ length: 50 }),
  OperationText: varchar({ length: 200 }),
  ActualWork: decimal({ precision: 10, scale: 1 }),
  Unit: varchar({ length: 50 }),
  CreateOn: varchar({ length: 10 }),
  CreateBy: varchar({ length: 50 }),
  ActualWorker: varchar({ length: 50 }),
  Name: varchar({ length: 50 }),
  Reversed: varchar({ length: 50 }),
  CanceledConfirmation: varchar({ length: 50 }),
  OrderDescription: varchar({ length: 200 }),
  BUDAT: varchar({ length: 10 }),
  STATUS: varchar({ length: 10 }),
  IWERK: varchar({ length: 50 }),
  INGRP: varchar({ length: 50 }),
  GRWRK: varchar({ length: 50 }),
  AUART: varchar({ length: 50 }),
}, (table) => ({
  // Confirmation, Count 복합 PK에 UNIQUE 인덱스 추가
  uniqueConfirmationCount: uniqueIndex("unique_confirmation_count").on(table.Confirmation, table.Count),
}));

export const tbSAPOrderNotice = sErpSchema.table("TB_SAP_OrderNotice", {
  id: serial().primaryKey(),
  Status: varchar({ length: 50 }),
  Notification: bigint({ mode: 'number' }), // pk
  Type: varchar({ length: 50 }),
  Description: varchar({ length: 64 }),
  PlanningPlant: varchar({ length: 50 }),
  PlannerGroup: varchar({ length: 50 }),
  NameOfPlannerGroup: varchar({ length: 50 }),
  WorkCenter: varchar({ length: 50 }),
  NameOfWorkCenter: varchar({ length: 50 }),
  FunctionalLocation: varchar({ length: 50 }),
  Equipment: bigint({ mode: 'number' }),
  TagNumber: varchar({ length: 50 }),
  EquipmentDesc: varchar({ length: 50 }),
  PlantSection: varchar({ length: 50 }),
  CostCenter: varchar({ length: 50 }),
  CostCenterDesc: varchar({ length: 50 }),
  ABCIndicator: varchar({ length: 50 }),
  NotificationDate: varchar({ length: 50 }),
  Createdby: varchar({ length: 50 }),
  SystemStatus: varchar({ length: 50 }),
  UserStatus: varchar({ length: 50 }),
  Order: bigint({ mode: 'number' }),
  Requester: varchar({ length: 50 }),
  RequesterName: varchar({ length: 50 }),
  CodeGroup: varchar({ length: 50 }),
  Code: bigint({ mode: 'number' }),
  CodeDescription: varchar({ length: 50 }),
  BreakdownIndicator: varchar({ length: 50 }),
  Priority: varchar({ length: 50 }),
  PriorityDesc: varchar({ length: 50 }),
  NotifcatnType: varchar({ length: 50 }),
  Name1: varchar({ length: 50 }),
  WorkCenterInt: bigint({ mode: 'number' }),
  ObjectNumber: varchar({ length: 50 }),
  CHAR05: varchar({ length: 50 }),
  Link: varchar({ length: 50 }),
  Link1: varchar({ length: 50 }),
  Tooltip: varchar({ length: 50 }),
  PlantSectionDesc: varchar({ length: 50 }),
  Catalog: varchar({ length: 50 }),
  Partner: varchar({ length: 50 }),
  NotificationTime: varchar({ length: 50 }),
  CompletionDate: varchar({ length: 50 }),
  CompletionTime: varchar({ length: 50 }),
  ExtRequester: varchar({ length: 50 }),
  COL1: varchar({ length: 50 }), // 일상점검미흡
  OrderBasicStart: varchar({ length: 50 }),
  EWRNo: varchar({ length: 50 }),
  MalfunctionStart: varchar({ length: 50 }),
  StartTime: varchar({ length: 50 }),
  MalfunctionEnd: varchar({ length: 50 }),
  EndTime: varchar({ length: 50 }),
  BreakdownDuration: real(), // float4 타입을 real로 변환
}, (table) => ({
  // Notification 컬럼에 UNIQUE 인덱스 추가
  uniqueNotification: uniqueIndex("unique_notification").on(table.Notification),
}));

export const tbSAPOrderBreakdown = sErpSchema.table("TB_SAP_OrderBreakdown", {
  id: serial().primaryKey(),
  Order: bigint({ mode: 'number' }), // pk
  OrderType: varchar({ length: 50 }),
  FunctionalLocation: varchar({ length: 50 }),
  FLDesc: varchar({ length: 50 }),
  Equipment: bigint({ mode: 'number' }),
  EquipmentDesc: varchar({ length: 50 }),
  ABCIndicator: varchar({ length: 50 }),
  MainWorkCenter: varchar({ length: 50 }),
  WorkCenterDesc: varchar({ length: 50 }),
  PlannerGroup: varchar({ length: 50 }),
  PlannerGroupDesc: varchar({ length: 50 }),
  ActStartDate: varchar({ length: 50 }),
  ActEndDate: varchar({ length: 50 }),
  WorkTimeHR: real(), // float4 타입을 real로 변환
  ObjectPart: varchar({ length: 50 }),
  Damage: varchar({ length: 50 }),
  Cause: varchar({ length: 50 }),
  Activity: varchar({ length: 50 }),
  MaterialCost: varchar({ length: 50 }),
  LaborCost: varchar({ length: 50 }),
  TotalCost: varchar({ length: 50 }),
  Currency: varchar({ length: 50 }),
  Breakdown: varchar({ length: 50 }),
  MalfunctionStart: varchar({ length: 50 }),
  StartTime: varchar({ length: 50 }),
  MalfunctionEnd: varchar({ length: 50 }),
  EndTime: varchar({ length: 50 }),
  BreakdownDuration: real(), // float4 타입을 real로 변환
  Plant: varchar({ length: 50 }),
  Notification: bigint({ mode: 'number' }),
  ObjectNumber: varchar({ length: 50 }),
  OrderDesc: varchar({ length: 64 }),
  PlantSectionDesc: varchar({ length: 50 }),
  TagNumber: varchar({ length: 50 }),
}, (table) => ({
  // Order 컬럼에 UNIQUE 인덱스 추가
  uniqueOrder: uniqueIndex("unique_order_breakdown").on(table.Order),
}));

export const tbSAPMainternanceBOM = sErpSchema.table("TB_SAP_MainternanceBOM", {
  id: serial().primaryKey(),
  Date: varchar({ length: 50 }), // pk
  Order: bigint({ mode: 'number' }), // pk
  Description: varchar({ length: 50 }),
  FuncLocation: varchar({ length: 50 }),
  Description_1: varchar({ length: 50 }),
  WorkCenter: varchar({ length: 50 }),
  ObjectType: varchar({ length: 50 }),
  Equipment: bigint({ mode: 'number' }),
  Discription: varchar({ length: 50 }),
  TagNumber: varchar({ length: 50 }),
  ICT: varchar({ length: 50 }),
  Component: varchar({ length: 50 }), // pk
  Quantity: bigint({ mode: 'number' }),
  Unit: varchar({ length: 50 }),
  Description_2: varchar({ length: 50 }),
  Specification: varchar({ length: 200 }),
  GEWRK: bigint({ mode: 'number' }),
  STLNR: varchar({ length: 50 }),
  STLAL: varchar({ length: 50 }),
  WERKS: varchar({ length: 50 }),
  TPLNR_T: varchar({ length: 50 }),
  EQUNR_T: bigint({ mode: 'number' }),
}, (table) => ({
  // Date, Order, Component 복합 PK에 UNIQUE 인덱스 추가
  uniqueDateOrderComponent: uniqueIndex("unique_date_order_component").on(table.Date, table.Order, table.Component),
}));

export const tbSAPMaterialRepair = sErpSchema.table("TB_SAP_MaterialRepair", {
  id: serial().primaryKey(),
  BreakdownIndicator: varchar({ length: 50 }),
  Order: bigint({ mode: 'number' }), // pk
  OrderType: varchar({ length: 50 }),
  OrderDescription: varchar({ length: 50 }),
  MainWorkCenterDescription: varchar({ length: 50 }),
  FunctionalLocation: varchar({ length: 50 }),
  Equipment: bigint({ mode: 'number' }),
  EquipmentDescription: varchar({ length: 50 }),
  EquipmentSortField: varchar({ length: 50 }),
  TagNumber: varchar({ length: 50 }),
  Notification: bigint({ mode: 'number' }),
  Priority: varchar({ length: 50 }),
  PriorityDesc: varchar({ length: 50 }),
  PlantSection: varchar({ length: 50 }),
  PMActivityType: varchar({ length: 50 }),
  MaintenancePlan: varchar({ length: 50 }),
  StartDate: varchar({ length: 50 }),
  FinishDate: varchar({ length: 50 }),
  SystemStatus: varchar({ length: 50 }),
  UserStatus: varchar({ length: 50 }),
  CreatedBy: varchar({ length: 50 }),
  Name: varchar({ length: 50 }),
  PlanningPlant: varchar({ length: 50 }),
  PlannerGroup: varchar({ length: 50 }),
  PlannerGroupName: varchar({ length: 50 }),
  MainWorkCenter: varchar({ length: 50 }),
  Maintenanceplant: varchar({ length: 50 }),
  PlantSectionDescription: varchar({ length: 50 }),
  Priority_1: varchar({ length: 50 }),
  Priority_2: varchar({ length: 50 }),
  BasicStartTime: varchar({ length: 50 }),
  BasicFinishTime: varchar({ length: 50 }),
  ActualStartDate: varchar({ length: 50 }),
  ActualStartTime: varchar({ length: 50 }),
  ActualFinishDate: varchar({ length: 50 }),
  ActualFinishTIme: varchar({ length: 50 }),
  PlanCost: varchar({ length: 50 }),
  ActualCost: varchar({ length: 50 }),
  SettlementCost: varchar({ length: 50 }),
  Balance: varchar({ length: 50 }),
  Currency: varchar({ length: 50 }),
  EquiCategory: bigint({ mode: 'number' }),
  ObjectType: varchar({ length: 50 }),
  ABCIndicatior: varchar({ length: 50 }),
  EWRNo: varchar({ length: 50 }),
}, (table) => ({
  // Order 컬럼에 UNIQUE 인덱스 추가
  uniqueOrderRepair: uniqueIndex("unique_order_repair").on(table.Order),
}));

export const tbSAPMaterialInfo = sErpSchema.table("TB_SAP_MaterialInfo", {
  id: serial().primaryKey(),
  Plnt: varchar({ length: 50 }), // pk
  Material: varchar({ length: 50 }), // pk
  Sloc: varchar({ length: 50 }), // pk
  Bin: varchar({ length: 50 }),
  MatlGroup: varchar({ length: 50 }),
  MaterialDescription: varchar({ length: 500 }),
  MaterialName: varchar({ length: 500 }),
  Specification: varchar({ length: 500 }),
  Unrestr: bigint({ mode: 'number' }),
  QualInsp: bigint({ mode: 'number' }),
  Blocked: bigint({ mode: 'number' }),
  Bun: varchar({ length: 50 }),
  Warehouse: varchar({ length: 50 }),
  Message: varchar({ length: 500 }),
}, (table) => ({
  // Plnt, Material, Sloc 복합 PK에 UNIQUE 인덱스 추가
  uniquePlntMaterialSloc: uniqueIndex("unique_plnt_material_sloc").on(table.Plnt, table.Material, table.Sloc),
}));

export const tbSAPMaterialStock = sErpSchema.table("TB_SAP_MaterialStock", {
  id: serial().primaryKey(),
  WBS: varchar({ length: 50 }),
  OldProjec: varchar({ length: 50 }),
  Material: varchar({ length: 50 }), // pk
  MaterialD: varchar({ length: 50 }),
  Specificat: varchar({ length: 50 }),
  MRPType: varchar({ length: 50 }),
  Batch: varchar({ length: 50 }), // pk
  Amount: varchar({ length: 50 }),
  StorageLo: varchar({ length: 50 }), // pk
  StorageLo_1: varchar({ length: 50 }),
  StorageBin: varchar({ length: 50 }),
  Warehouse: varchar({ length: 50 }),
  Warehouse_1: varchar({ length: 50 }),
  MMTNo: varchar({ length: 50 }),
  TNo: varchar({ length: 50 }),
  BOXNo: varchar({ length: 50 }),
  MaterialT: varchar({ length: 50 }),
  Unrestrict: bigint({ mode: 'number' }),
  QIStock: bigint({ mode: 'number' }),
  BlockedSt: bigint({ mode: 'number' }),
  REMARK: varchar({ length: 50 }),
  TotalStoc: bigint({ mode: 'number' }),
  Unit: varchar({ length: 50 }),
  MAP: varchar({ length: 50 }),
  NetWeight: real(), // float4 타입을 real로 변환
  TotalWeig: real(), // float4 타입을 real로 변환
  WeightUni: varchar({ length: 50 }),
  Individual: varchar({ length: 50 }),
  SpecialSt: varchar({ length: 50 }),
  Vendor: varchar({ length: 50 }),
  VendorDes: varchar({ length: 50 }),
  MaterialG: varchar({ length: 50 }),
  Procuremen: varchar({ length: 50 }),
  PLMID: varchar({ length: 50 }),
  ValveFitt: varchar({ length: 50 }),
  BLNo: varchar({ length: 50 }),
  TAGNO: varchar({ length: 50 }),
}, (table) => ({
  // Material, Batch, StorageLo 복합 PK에 UNIQUE 인덱스 추가
  uniqueMaterialBatchStorage: uniqueIndex("unique_material_batch_storage").on(table.Material, table.Batch, table.StorageLo),
}));

export const tbSAPMaterialRelease = sErpSchema.table("TB_SAP_MaterialRelease", {
  id: serial().primaryKey(),
  PostingGIdate: varchar({ length: 50 }), // pk
  Plant: varchar({ length: 50 }), // pk
  StorageLoc: varchar({ length: 50 }), // pk
  StockWBS: varchar({ length: 50 }),
  InputWBS: varchar({ length: 50 }),
  Material: varchar({ length: 50 }),
  OldMaterialNumber: varchar({ length: 50 }),
  MaterialDesc: varchar({ length: 50 }),
  Spec: varchar({ length: 100 }),
  Batch: varchar({ length: 50 }),
  MovementType: varchar({ length: 50 }),
  Quantity: bigint({ mode: 'number' }),
  BUn: varchar({ length: 50 }),
  GoodsRecipinet: varchar({ length: 50 }), // Goods Recipinet(Ship-To Party)
  NetWeight: real(), // float4 타입을 real로 변환
  GrossWeight: real(), // float4 타입을 real로 변환
  Unit: varchar({ length: 50 }),
  Amount: varchar({ length: 50 }),
  Currency: varchar({ length: 50 }),
  MvtTypeTxt: varchar({ length: 50 }),
  MaterialDocument: bigint({ mode: 'number' }), // pk
  DocumentYear: bigint({ mode: 'number' }), // pk
  S: varchar({ length: 50 }),
  MatName: varchar({ length: 50 }),
  MatGroupDesc: varchar({ length: 50 }),
  InputProject: varchar({ length: 50 }),
  HeaderText: varchar({ length: 50 }),
}, (table) => ({
  // PostingGIdate, Plant, StorageLoc, MaterialDocument, DocumentYear 복합 PK에 UNIQUE 인덱스 추가
  uniqueReleaseComposite: uniqueIndex("unique_release_composite").on(table.PostingGIdate, table.Plant, table.StorageLoc, table.MaterialDocument, table.DocumentYear),
}));

export const tbSAPMaterialReceiving = sErpSchema.table("TB_SAP_MaterialReceiving", {
  id: serial().primaryKey(),
  PostingGRDate: varchar({ length: 50 }), // pk
  Plant: varchar({ length: 50 }), // pk
  StorageLoc: varchar({ length: 50 }), // pk
  StockWBS: varchar({ length: 50 }),
  InputWBS: varchar({ length: 50 }),
  Material: varchar({ length: 50 }),
  OldMaterialNumber: varchar({ length: 50 }),
  MaterialDesc: varchar({ length: 50 }),
  Spec: varchar({ length: 100 }),
  Batch: varchar({ length: 50 }),
  MovementType: varchar({ length: 50 }),
  Quantity: bigint({ mode: 'number' }),
  BUn: varchar({ length: 50 }),
  GoodsRecipinet: varchar({ length: 50 }), // Goods Recipinet(Ship-To Party)
  PO: varchar({ length: 50 }),
  POItem: bigint({ mode: 'number' }),
  Vendor: varchar({ length: 50 }),
  VendorName: varchar({ length: 50 }),
  NetWeight: real(), // float4 타입을 real로 변환
  GrossWeight: real(), // float4 타입을 real로 변환
  Unit: varchar({ length: 50 }),
  Amount: varchar({ length: 50 }),
  Currency: varchar({ length: 50 }),
  MvtTypeTxt: varchar({ length: 50 }),
  MatDoc: bigint({ mode: 'number' }), // pk
  Year: bigint({ mode: 'number' }), // pk
  S: varchar({ length: 50 }),
  MatName: varchar({ length: 50 }),
  MatGroupDesc: varchar({ length: 50 }),
  InputProject: varchar({ length: 50 }),
  HeaderText: varchar({ length: 50 }),
}, (table) => ({
  // PostingGRDate, Plant, StorageLoc, MatDoc, Year 복합 PK에 UNIQUE 인덱스 추가
  uniqueReceivingComposite: uniqueIndex("unique_receiving_composite").on(table.PostingGRDate, table.Plant, table.StorageLoc, table.MatDoc, table.Year),
}));

export const tbSAPMaterialPurchase = sErpSchema.table("TB_SAP_MaterialPurchase", {
  id: serial().primaryKey(),
  Status: varchar({ length: 50 }),
  Notification: bigint({ mode: 'number' }),
  Type: varchar({ length: 50 }),
  Description: varchar({ length: 64 }),
  PlanningPlant: varchar({ length: 50 }),
  PlannerGroup: varchar({ length: 50 }),
  NameOfPlannerGroup: varchar({ length: 50 }),
  WorkCenter: varchar({ length: 50 }),
  NameOfWorkCenter: varchar({ length: 50 }),
  FunctionalLocation: varchar({ length: 50 }),
  Equipment: bigint({ mode: 'number' }),
  TagNumber: varchar({ length: 50 }),
  EquipmentDesc: varchar({ length: 50 }),
  PlantSection: varchar({ length: 50 }),
  CostCenter: varchar({ length: 50 }),
  CostCenterDesc: varchar({ length: 50 }),
  ABCIndicator: varchar({ length: 50 }),
  NotificationDate: varchar({ length: 50 }),
  CreatedBy: varchar({ length: 50 }),
  SystemStatus: varchar({ length: 50 }),
  UserStatus: varchar({ length: 50 }),
  Order: bigint({ mode: 'number' }),
  Requester: varchar({ length: 50 }),
  RequesterName: varchar({ length: 50 }),
  CodeGroup: varchar({ length: 50 }),
  Code: bigint({ mode: 'number' }),
  CodeDescription: varchar({ length: 50 }),
  BreakdownIndicator: varchar({ length: 50 }),
  Priority: varchar({ length: 50 }),
  PriorityDesc: varchar({ length: 50 }),
  NotifcatnType: varchar({ length: 50 }),
  Name1: varchar({ length: 50 }),
  WorkCenterInt: bigint({ mode: 'number' }),
  ObjectNumber: varchar({ length: 50 }),
  CHAR05: varchar({ length: 50 }),
  Link: varchar({ length: 50 }),
  Link1: varchar({ length: 50 }),
  Tooltip: varchar({ length: 50 }),
  PlantSectionDesc: varchar({ length: 50 }),
  Catalog: varchar({ length: 50 }),
  Partner: varchar({ length: 50 }),
  NotificationTime: varchar({ length: 50 }),
  CompletionDate: varchar({ length: 50 }),
  CompletionTime: varchar({ length: 50 }),
  ExtRequester: varchar({ length: 50 }),
  COL1: varchar({ length: 50 }), // 일상점검미흡
  OrderBasicStart: varchar({ length: 50 }),
  EWRNo: varchar({ length: 50 }),
  MalfunctionStart: varchar({ length: 50 }),
  StartTime: varchar({ length: 50 }),
  MalfunctionEnd: varchar({ length: 50 }),
  EndTime: varchar({ length: 50 }),
  BreakdownDuration: real(), // float4 타입을 real로 변환
});

export const technicalGuidanceInvestment = sErpSchema.table("Technical_Guidance_Investment", {
  id: serial().primaryKey(),
  FUMNR: varchar({ length: 10 }), // pk
  FUMIT: bigint({ mode: 'number' }), // pk
  DESCR: varchar({ length: 50 }),
  FU_APPDATE: varchar({ length: 10 }),
  COST_INVEST: varchar({ length: 15 })
}, (table) => ({
  // FUMNR, FUMIT 복합 PK에 UNIQUE 인덱스 추가
  uniqueFumnrFumit: uniqueIndex("unique_fumnr_fumit").on(table.FUMNR, table.FUMIT),
}));

export const technicalGuidanceOrder = sErpSchema.table("Technical_Guidance_Order", {
  id: serial().primaryKey(),
  AUFNR: varchar({ length: 12 }), // pk (Order)
  VORNR: varchar({ length: 4 }), // pk (Operation)
  ORD_ERDAT: varchar({ length: 10 }),
  FUMNR: varchar({ length: 10 }),
  FUMIT: bigint({ mode: 'number' }),
  DESCR: varchar({ length: 50 }),
  FU_APPDATE: varchar({ length: 10 }),
  BANFN: varchar({ length: 10 }),
  BNFPO: bigint({ mode: 'number' }),
  BADAT: varchar({ length: 10 }),
  EBELN: varchar({ length: 10 }),
  EBELP: bigint({ mode: 'number' }),
  PO_ERDAT: varchar({ length: 10 }),
  AUART: varchar({ length: 4 }),
  STTXT: varchar({ length: 40 }),
  EQUNR: varchar({ length: 18 }),
  KTEXT: varchar({ length: 20 }),
  INGRP: varchar({ length: 3 }),
  INNAM: varchar({ length: 18 }),
  CON_STEP: varchar({ length: 20 }),
  CON_DESC: varchar({ length: 40 }),
  CON_CO: varchar({ length: 30 }),
  COST_INVEST: varchar({ length: 15 }),
  COST_ORD: varchar({ length: 13 }),
  COST_PR: varchar({ length: 13 }),
  COST_PO: varchar({ length: 13 }),
  COST_IV: varchar({ length: 13 }),
}, (table) => ({
  // AUFNR, VORNR 복합 PK에 UNIQUE 인덱스 추가
  uniqueAufnrVornr: uniqueIndex("unique_aufnr_vornr").on(table.AUFNR, table.VORNR),
}));