// 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;
}