From 610d3bccf1cb640e2a21df28d8d2a954c2bf337e Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Thu, 5 Jun 2025 01:53:35 +0000
Subject: (대표님) 변경사항 0604 - OCR 관련 및 drizzle generated sqls
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations/0111_fancy_exiles.sql | 60 +++++++++++++++++++++++++++++++++++++
 1 file changed, 60 insertions(+)
 create mode 100644 db/migrations/0111_fancy_exiles.sql

(limited to 'db/migrations/0111_fancy_exiles.sql')

diff --git a/db/migrations/0111_fancy_exiles.sql b/db/migrations/0111_fancy_exiles.sql
new file mode 100644
index 00000000..b523e0f5
--- /dev/null
+++ b/db/migrations/0111_fancy_exiles.sql
@@ -0,0 +1,60 @@
+CREATE TABLE "ocr_rotation_attempts" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"session_id" uuid NOT NULL,
+	"rotation" integer NOT NULL,
+	"confidence" numeric(5, 4),
+	"tables_found" integer DEFAULT 0 NOT NULL,
+	"text_quality" numeric(5, 4),
+	"keyword_count" integer DEFAULT 0 NOT NULL,
+	"score" numeric(5, 4),
+	"extracted_rows_count" integer DEFAULT 0 NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "ocr_rows" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"table_id" uuid NOT NULL,
+	"session_id" uuid NOT NULL,
+	"row_index" integer NOT NULL,
+	"no" varchar(50),
+	"identification_no" varchar(100),
+	"tag_no" varchar(100),
+	"joint_no" varchar(100),
+	"joint_type" varchar(100),
+	"welding_date" varchar(50),
+	"confidence" numeric(5, 4),
+	"source_table" integer,
+	"source_row" integer,
+	"created_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "ocr_sessions" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"file_name" varchar(255) NOT NULL,
+	"file_size" integer NOT NULL,
+	"file_type" varchar(50) NOT NULL,
+	"processing_time" integer NOT NULL,
+	"best_rotation" integer DEFAULT 0 NOT NULL,
+	"total_tables" integer DEFAULT 0 NOT NULL,
+	"total_rows" integer DEFAULT 0 NOT NULL,
+	"image_enhanced" boolean DEFAULT false NOT NULL,
+	"pdf_converted" boolean DEFAULT false NOT NULL,
+	"success" boolean DEFAULT true NOT NULL,
+	"error_message" text,
+	"warnings" jsonb,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "ocr_tables" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"session_id" uuid NOT NULL,
+	"table_index" integer NOT NULL,
+	"row_count" integer DEFAULT 0 NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+ALTER TABLE "ocr_rotation_attempts" ADD CONSTRAINT "ocr_rotation_attempts_session_id_ocr_sessions_id_fk" FOREIGN KEY ("session_id") REFERENCES "public"."ocr_sessions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "ocr_rows" ADD CONSTRAINT "ocr_rows_table_id_ocr_tables_id_fk" FOREIGN KEY ("table_id") REFERENCES "public"."ocr_tables"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "ocr_rows" ADD CONSTRAINT "ocr_rows_session_id_ocr_sessions_id_fk" FOREIGN KEY ("session_id") REFERENCES "public"."ocr_sessions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "ocr_tables" ADD CONSTRAINT "ocr_tables_session_id_ocr_sessions_id_fk" FOREIGN KEY ("session_id") REFERENCES "public"."ocr_sessions"("id") ON DELETE cascade ON UPDATE no action;
\ No newline at end of file
-- 
cgit v1.2.3

