summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/index.ts3
-rw-r--r--db/schema/ocr.ts135
-rw-r--r--db/schema/vendorDocu.ts48
3 files changed, 183 insertions, 3 deletions
diff --git a/db/schema/index.ts b/db/schema/index.ts
index 309af050..5c0b3135 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -14,4 +14,5 @@ export * from './logs';
export * from './basicContractDocumnet';
export * from './procurementRFQ';
export * from './setting';
-export * from './techSales'; \ No newline at end of file
+export * from './techSales';
+export * from './ocr'; \ No newline at end of file
diff --git a/db/schema/ocr.ts b/db/schema/ocr.ts
new file mode 100644
index 00000000..289eb297
--- /dev/null
+++ b/db/schema/ocr.ts
@@ -0,0 +1,135 @@
+// db/schema/ocr.ts
+import {
+ pgTable,
+ uuid,
+ varchar,
+ integer,
+ decimal,
+ timestamp,
+ boolean,
+ jsonb,
+ text,
+ serial
+} from 'drizzle-orm/pg-core';
+import { relations } from 'drizzle-orm';
+
+// OCR 세션 테이블 (전체 처리 정보)
+export const ocrSessions = pgTable('ocr_sessions', {
+ id: uuid('id').defaultRandom().primaryKey(),
+ fileName: varchar('file_name', { length: 255 }).notNull(),
+ fileSize: integer('file_size').notNull(),
+ fileType: varchar('file_type', { length: 50 }).notNull(), // 'pdf' | 'image'
+ processingTime: integer('processing_time').notNull(), // milliseconds
+ bestRotation: integer('best_rotation').notNull().default(0),
+ totalTables: integer('total_tables').notNull().default(0),
+ totalRows: integer('total_rows').notNull().default(0),
+ imageEnhanced: boolean('image_enhanced').notNull().default(false),
+ pdfConverted: boolean('pdf_converted').notNull().default(false),
+ success: boolean('success').notNull().default(true),
+ errorMessage: text('error_message'),
+ warnings: jsonb('warnings').$type<string[]>(),
+ createdAt: timestamp('created_at').notNull().defaultNow(),
+ updatedAt: timestamp('updated_at').notNull().defaultNow(),
+});
+
+// 추출된 테이블 정보
+export const ocrTables = pgTable('ocr_tables', {
+ id: uuid('id').defaultRandom().primaryKey(),
+ sessionId: uuid('session_id').notNull().references(() => ocrSessions.id, { onDelete: 'cascade' }),
+ tableIndex: integer('table_index').notNull(), // 세션 내에서 테이블 순서
+ rowCount: integer('row_count').notNull().default(0),
+ createdAt: timestamp('created_at').notNull().defaultNow(),
+});
+
+// 추출된 행 데이터
+export const ocrRows = pgTable('ocr_rows', {
+ id: uuid('id').defaultRandom().primaryKey(),
+ tableId: uuid('table_id').notNull().references(() => ocrTables.id, { onDelete: 'cascade' }),
+ sessionId: uuid('session_id').notNull().references(() => ocrSessions.id, { onDelete: 'cascade' }),
+ rowIndex: integer('row_index').notNull(), // 테이블 내에서 행 순서
+ reportNo: varchar('report_no', { length: 100 }), // Report No. (예: SN2661FT20250526)
+ no: varchar('no', { length: 50 }),
+ identificationNo: varchar('identification_no', { length: 100 }),
+ tagNo: varchar('tag_no', { length: 100 }),
+ jointNo: varchar('joint_no', { length: 100 }),
+ jointType: varchar('joint_type', { length: 100 }),
+ weldingDate: varchar('welding_date', { length: 50 }),
+ confidence: decimal('confidence', { precision: 5, scale: 4 }), // 0.0000 ~ 1.0000
+ sourceTable: integer('source_table'),
+ sourceRow: integer('source_row'),
+ createdAt: timestamp('created_at').notNull().defaultNow(),
+});
+
+// 회전 시도 결과
+export const ocrRotationAttempts = pgTable('ocr_rotation_attempts', {
+ id: uuid('id').defaultRandom().primaryKey(),
+ sessionId: uuid('session_id').notNull().references(() => ocrSessions.id, { onDelete: 'cascade' }),
+ rotation: integer('rotation').notNull(), // 0, 90, 180, 270
+ confidence: decimal('confidence', { precision: 5, scale: 4 }), // OCR 신뢰도
+ tablesFound: integer('tables_found').notNull().default(0),
+ textQuality: decimal('text_quality', { precision: 5, scale: 4 }),
+ keywordCount: integer('keyword_count').notNull().default(0),
+ score: decimal('score', { precision: 5, scale: 4 }), // 계산된 점수
+ extractedRowsCount: integer('extracted_rows_count').notNull().default(0),
+ createdAt: timestamp('created_at').notNull().defaultNow(),
+});
+
+// Relations 정의
+export const ocrSessionsRelations = relations(ocrSessions, ({ many }) => ({
+ tables: many(ocrTables),
+ rows: many(ocrRows),
+ rotationAttempts: many(ocrRotationAttempts),
+}));
+
+export const ocrTablesRelations = relations(ocrTables, ({ one, many }) => ({
+ session: one(ocrSessions, {
+ fields: [ocrTables.sessionId],
+ references: [ocrSessions.id],
+ }),
+ rows: many(ocrRows),
+}));
+
+export const ocrRowsRelations = relations(ocrRows, ({ one }) => ({
+ session: one(ocrSessions, {
+ fields: [ocrRows.sessionId],
+ references: [ocrSessions.id],
+ }),
+ table: one(ocrTables, {
+ fields: [ocrRows.tableId],
+ references: [ocrTables.id],
+ }),
+}));
+
+export const ocrRotationAttemptsRelations = relations(ocrRotationAttempts, ({ one }) => ({
+ session: one(ocrSessions, {
+ fields: [ocrRotationAttempts.sessionId],
+ references: [ocrSessions.id],
+ }),
+}));
+
+// 타입 정의
+export type OcrSession = typeof ocrSessions.$inferSelect;
+export type OcrTable = typeof ocrTables.$inferSelect;
+export type OcrRow = typeof ocrRows.$inferSelect;
+export type OcrRotationAttempt = typeof ocrRotationAttempts.$inferSelect;
+
+export type NewOcrSession = typeof ocrSessions.$inferInsert;
+export type NewOcrTable = typeof ocrTables.$inferInsert;
+export type NewOcrRow = typeof ocrRows.$inferInsert;
+export type NewOcrRotationAttempt = typeof ocrRotationAttempts.$inferInsert;
+
+export interface BaseExtractedRow {
+ no: string;
+ identificationNo: string;
+ tagNo: string;
+ jointNo: string;
+ jointType: string;
+ weldingDate: string;
+ confidence: number;
+ sourceTable: number;
+ sourceRow: number;
+}
+
+export interface ExtractedRow extends BaseExtractedRow {
+ reportNo: string;
+} \ No newline at end of file
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index 8c144f35..ebae7630 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -15,14 +15,47 @@ export const documents = pgTable(
contractId: integer("contract_id")
.notNull()
.references(() => contracts.id, { onDelete: "cascade" }),
+
+ // 기본 문서 정보
docNumber: varchar("doc_number", { length: 100 }).notNull(),
- // ✅ 벤더용 문서 번호 추가 (옵셔널)
vendorDocNumber: varchar("vendor_doc_number", { length: 100 }),
title: varchar("title", { length: 255 }).notNull(),
status: varchar("status", { length: 50 })
.notNull()
.default("ACTIVE"),
issuedDate: date("issued_date"),
+
+ // ✅ DOLCE 연동을 위한 새로운 필드들
+ drawingKind: varchar("drawing_kind", { length: 10 }), // B3, B4, B5
+ drawingMoveGbn: varchar("drawing_move_gbn", { length: 50 }), // 도면입수, 도면제출, GTT Deliverable, SHI Input Information
+ discipline: varchar("discipline", { length: 10 }), // DE, ME, etc.
+
+ // ✅ 외부 시스템 연동 정보
+ externalDocumentId: varchar("external_document_id", { length: 100 }), // DOLCE 시스템의 문서 ID
+ externalSystemType: varchar("external_system_type", { length: 20 }), // DOLCE, SWP
+ externalSyncedAt: timestamp("external_synced_at"), // 마지막 동기화 시간
+
+ // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
+ cGbn: varchar("c_gbn", { length: 50 }), // CGbn
+ dGbn: varchar("d_gbn", { length: 50 }), // DGbn
+ degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn
+ deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn
+ jGbn: varchar("j_gbn", { length: 50 }), // JGbn
+ sGbn: varchar("s_gbn", { length: 50 }), // SGbn
+
+ // ✅ DOLCE 응답의 추가 정보들
+ shiDrawingNo: varchar("shi_drawing_no", { length: 100 }), // SHI 도면 번호
+ manager: varchar("manager", { length: 100 }), // 담당자
+ managerENM: varchar("manager_enm", { length: 100 }), // 담당자 영문명
+ managerNo: varchar("manager_no", { length: 50 }), // 담당자 번호
+ registerGroup: integer("register_group"), // 등록 그룹
+ registerGroupId: integer("register_group_id"), // 등록 그룹 ID
+
+ // ✅ 생성자 정보 (DOLCE에서 가져온 정보)
+ createUserNo: varchar("create_user_no", { length: 50 }), // 생성자 번호
+ createUserId: varchar("create_user_id", { length: 100 }), // 생성자 ID (한글명)
+ createUserENM: varchar("create_user_enm", { length: 100 }), // 생성자 영문명
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
},
@@ -33,14 +66,24 @@ export const documents = pgTable(
table.docNumber,
table.status
),
- // ✅ 벤더 문서 번호가 있는 경우 유니크 제약 조건 (옵셔널이므로 별도 인덱스)
uniqueContractVendorDoc: uniqueIndex("unique_contract_vendor_doc").on(
table.contractId,
table.vendorDocNumber
).where(sql`${table.vendorDocNumber} IS NOT NULL`),
+
+ // ✅ 외부 시스템 문서 ID 유니크 인덱스
+ uniqueExternalDoc: uniqueIndex("unique_external_doc").on(
+ table.contractId,
+ table.externalDocumentId,
+ table.externalSystemType
+ ).where(sql`${table.externalDocumentId} IS NOT NULL`),
+
+ // ✅ drawingKind 인덱스 (자주 검색될 것 같음)
+ drawingKindIndex: index("drawing_kind_idx").on(table.drawingKind),
}
}
)
+
// 확장된 issueStages 테이블
export const issueStages = pgTable(
"issue_stages",
@@ -115,6 +158,7 @@ export const revisions = pgTable(
reviewerId: integer("reviewer_id"),
reviewerName: varchar("reviewer_name", { length: 100 }),
reviewComments: varchar("review_comments", { length: 1000 }),
+ externalUploadId: varchar("external_upload_id", { length: 255 }),
comment: varchar("comment", { length: 500 }),
createdAt: timestamp("created_at").defaultNow().notNull(),
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 12:04:30 +0000