""" Main Processing Script - Phase 3 (MySQLdb Version) Python PDF Processing + Extraction Compatible with Python 3.14.3 """ import sys import json from pathlib import Path sys.path.append(str(Path(__file__).parent)) from config import Config from pdf.text_extractor import PDFTextExtractor from ai.simple_extractor import SimpleCaseExtractor # Use MySQLdb (mysqlclient) - works with Python 3.14 import MySQLdb import MySQLdb.cursors print("✅ Using MySQLdb (mysqlclient)") # Make Tesseract optional try: import pytesseract TESSERACT_AVAILABLE = True print("✅ Tesseract OCR available") except ImportError: TESSERACT_AVAILABLE = False print("⚠️ Tesseract OCR not installed - scanned PDFs limited") from datetime import datetime def get_db_connection(): """Get MySQL database connection using MySQLdb""" try: conn = MySQLdb.connect( host=Config.DB_HOST, port=int(Config.DB_PORT), db=Config.DB_NAME, user=Config.DB_USER, passwd=Config.DB_PASS, charset='utf8mb4', connect_timeout=10 ) return conn except Exception as e: print(f"❌ Database connection failed: {e}") print(f" Check: {Config.DB_HOST}:{Config.DB_PORT}/{Config.DB_NAME}") return None class CaseProcessor: """Main case processing pipeline""" def __init__(self): Config.ensure_directories() tesseract_path = Config.TESSERACT_PATH if TESSERACT_AVAILABLE else None self.extractor = PDFTextExtractor(tesseract_path) self.ai_extractor = SimpleCaseExtractor() def process_case(self, case_id): """Process a single case""" print(f"\n{'='*60}") print(f"📋 PROCESSING CASE #{case_id}") print(f"{'='*60}") try: # 1. Get case info from database case_info = self._get_case_from_db(case_id) if not case_info: return {'success': False, 'error': 'Case not found in database'} if not case_info.get('original_pdf_path'): return {'success': False, 'error': 'No PDF file attached to this case'} pdf_path = Config.ORIGINAL_PDFS_DIR / case_info['original_pdf_path'] if not pdf_path.exists(): return {'success': False, 'error': f'PDF file not found: {pdf_path}'} print(f"📄 File: {pdf_path.name}") print(f"📏 Size: {pdf_path.stat().st_size / 1024:.1f} KB") # 2. Update status to processing self._update_status(case_id, 'processing') print("⏳ Status: processing...") # 3. Extract text from PDF print("\n📖 Extracting text from PDF...") formatted_text, pages = self.extractor.get_page_text(str(pdf_path)) print(f" 📑 Pages extracted: {len(pages)}") for page in pages: icon = "📝" if page['method'] == 'native_text' else "🔍" text_len = len(page['text']) print(f" {icon} Page {page['page']}: {page['method']} ({text_len} characters)") # Save pages to database self._save_pages(case_id, pages) print(" ✅ Page text saved to database") # 4. Extract fields using AI/Rules print("\n🤖 Extracting case fields...") extraction_result = self.ai_extractor.extract(formatted_text, pages) # Count extracted fields extracted_count = sum(1 for k, v in extraction_result.items() if v.get('value')) total_fields = len(extraction_result) print(f" 📊 Extracted: {extracted_count}/{total_fields} fields") # Print extracted values print("\n 📋 Extracted Values:") for field, data in extraction_result.items(): if data.get('value'): display_value = str(data['value'])[:80] confidence = data.get('confidence', 0) print(f" ✓ {field}: {display_value} ({confidence:.0%})") # 5. Save extraction JSON file self._save_extraction_json(case_id, extraction_result) print("\n ✅ Extraction JSON saved to file") # 6. Update database with extracted data self._update_extraction_data(case_id, extraction_result) print(" ✅ Database updated with extracted data") # 7. Calculate overall confidence confidence = self._calculate_confidence(extraction_result) # 8. Update final status self._update_status(case_id, 'extracted', confidence) # 9. Add success log self._add_log(case_id, 'INFO', f'Extraction completed. {extracted_count}/{total_fields} fields extracted.') print(f"\n{'='*60}") print(f"✅ CASE #{case_id} PROCESSED SUCCESSFULLY!") print(f" 📊 Confidence: {confidence:.1%}") print(f" 📋 Fields Extracted: {extracted_count}/{total_fields}") print(f" 📑 Pages Processed: {len(pages)}") print(f"{'='*60}") return { 'success': True, 'case_id': case_id, 'confidence': round(confidence, 2), 'fields_extracted': extracted_count, 'total_fields': total_fields, 'pages_processed': len(pages) } except Exception as e: error_msg = str(e)[:500] print(f"\n{'='*60}") print(f"❌ ERROR PROCESSING CASE #{case_id}") print(f" {error_msg}") print(f"{'='*60}") self._update_status(case_id, 'error', error=error_msg) self._add_log(case_id, 'ERROR', error_msg) import traceback traceback.print_exc() return {'success': False, 'error': error_msg} def _get_case_from_db(self, case_id): """Get case information from database""" conn = get_db_connection() if not conn: return None try: cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT * FROM cases WHERE id = %s", (case_id,)) case = cursor.fetchone() cursor.close() return case except Exception as e: print(f"Database query error: {e}") return None finally: conn.close() def _update_status(self, case_id, status, confidence=None, error=None): """Update case status in database""" conn = get_db_connection() if not conn: return try: cursor = conn.cursor() if status == 'extracted' and confidence is not None: cursor.execute(""" UPDATE cases SET extraction_status = %s, extraction_confidence = %s, needs_review = 1, updated_at = NOW() WHERE id = %s """, (status, confidence, case_id)) elif status == 'error': cursor.execute(""" UPDATE cases SET extraction_status = %s, processing_error = %s, updated_at = NOW() WHERE id = %s """, (status, error, case_id)) else: cursor.execute(""" UPDATE cases SET extraction_status = %s, updated_at = NOW() WHERE id = %s """, (status, case_id)) conn.commit() cursor.close() except Exception as e: print(f"Error updating status: {e}") conn.rollback() finally: conn.close() def _add_log(self, case_id, level, message): """Add processing log entry""" conn = get_db_connection() if not conn: return try: cursor = conn.cursor() cursor.execute(""" INSERT INTO processing_logs (case_id, log_level, log_message, created_at) VALUES (%s, %s, %s, NOW()) """, (case_id, level, message[:1000])) conn.commit() cursor.close() except Exception as e: print(f"Error adding log: {e}") finally: conn.close() def _save_pages(self, case_id, pages): """Save extracted page text to database""" conn = get_db_connection() if not conn: return try: cursor = conn.cursor() # Delete old pages for this case cursor.execute("DELETE FROM pdf_pages WHERE case_id = %s", (case_id,)) # Insert new pages for page in pages: cursor.execute(""" INSERT INTO pdf_pages (case_id, page_number, extraction_method, page_text, confidence_score) VALUES (%s, %s, %s, %s, %s) """, ( case_id, page['page'], page['method'], page['text'][:65535], # Truncate to fit MySQL TEXT field page['confidence'] )) conn.commit() print(f" 💾 {len(pages)} pages saved") cursor.close() except Exception as e: print(f"Error saving pages: {e}") conn.rollback() finally: conn.close() def _save_extraction_json(self, case_id, result): """Save extraction result as JSON file""" try: json_path = Config.EXTRACTED_JSON_DIR / f'case_{case_id}.json' Config.EXTRACTED_JSON_DIR.mkdir(parents=True, exist_ok=True) output = { 'case_id': case_id, 'extraction_date': datetime.now().isoformat(), 'processing_version': '1.0', 'fields': {} } for field, data in result.items(): output['fields'][field] = { 'value': str(data['value'])[:500] if data['value'] else None, 'page': data['page'], 'evidence': str(data['evidence'])[:500] if data['evidence'] else None, 'confidence': data['confidence'] } with open(json_path, 'w', encoding='utf-8') as f: json.dump(output, f, indent=2, ensure_ascii=False) print(f" 💾 JSON saved: case_{case_id}.json") except Exception as e: print(f"Error saving JSON: {e}") def _update_extraction_data(self, case_id, result): """Update database with extracted field values""" conn = get_db_connection() if not conn: return try: cursor = conn.cursor() # Field mapping: json_field -> database_column field_mapping = { 'defendant_name': 'defendant_name', 'defendant_address': 'defendant_address', 'city': 'city', 'state': 'state', 'zip_code': 'zip_code', 'plaintiff_name': 'plaintiff_name', 'plaintiff_attorney': 'plaintiff_attorney', 'index_number': 'index_number', 'county': 'county', 'filing_date': 'filing_date', 'case_type': 'case_type', 'amount_sued': 'amount_sued', 'current_case_status': 'current_case_status', 'court_document_names': 'court_document_names', 'nyscef_link': 'nyscef_link', 'ai_summary': 'ai_summary' } update_parts = [] values = [] for json_field, db_column in field_mapping.items(): if json_field in result: field_data = result[json_field] value = field_data.get('value') # Convert value to string for database if value is not None: value = str(value)[:1000] update_parts.append(f"`{db_column}` = %s") values.append(value) # Save evidence for non-null values if value: self._save_evidence(conn, case_id, json_field, field_data) if update_parts: values.append(case_id) sql = f"UPDATE cases SET {', '.join(update_parts)}, needs_review = 1, updated_at = NOW() WHERE id = %s" cursor.execute(sql, values) conn.commit() print(f" 💾 {len(update_parts)} database fields updated") cursor.close() except Exception as e: print(f"Error updating extraction data: {e}") conn.rollback() finally: conn.close() def _save_evidence(self, conn, case_id, field_name, field_data): """Save extraction evidence for a field""" try: cursor = conn.cursor() # Check if evidence already exists cursor.execute( "SELECT id FROM extraction_evidence WHERE case_id = %s AND field_name = %s", (case_id, field_name) ) field_value = str(field_data.get('value', ''))[:500] if field_data.get('value') else None page_number = field_data.get('page') evidence_text = str(field_data.get('evidence', ''))[:1000] if field_data.get('evidence') else None confidence = field_data.get('confidence', 0) if cursor.fetchone(): # Update existing cursor.execute(""" UPDATE extraction_evidence SET field_value = %s, page_number = %s, evidence_text = %s, confidence = %s WHERE case_id = %s AND field_name = %s """, (field_value, page_number, evidence_text, confidence, case_id, field_name)) else: # Insert new cursor.execute(""" INSERT INTO extraction_evidence (case_id, field_name, field_value, page_number, evidence_text, confidence) VALUES (%s, %s, %s, %s, %s, %s) """, (case_id, field_name, field_value, page_number, evidence_text, confidence)) except Exception as e: print(f"Error saving evidence for {field_name}: {e}") def _calculate_confidence(self, result): """Calculate overall confidence score""" confidences = [] skip_fields = ['ai_summary', 'current_case_status', 'court_document_names'] for field, data in result.items(): if field not in skip_fields: conf = data.get('confidence', 0) if conf > 0: # Only include fields with some confidence confidences.append(conf) if confidences: return sum(confidences) / len(confidences) return 0.0 # ============================================ # COMMAND LINE INTERFACE # ============================================ if __name__ == '__main__': print("\n" + "="*60) print("📋 CASE AI EXTRACTION SYSTEM - Phase 3") print("="*60) if len(sys.argv) > 1: try: case_id = int(sys.argv[1]) processor = CaseProcessor() result = processor.process_case(case_id) # Print final result as JSON print("\n📊 FINAL RESULT:") print(json.dumps(result, indent=2, default=str)) # Exit with appropriate code sys.exit(0 if result.get('success') else 1) except ValueError: print("❌ Invalid case ID. Please provide a number.") sys.exit(1) except Exception as e: print(f"❌ Unexpected error: {e}") import traceback traceback.print_exc() sys.exit(1) else: print("\n📝 USAGE:") print(" python main.py ") print("\n📋 EXAMPLES:") print(" python main.py 1") print(" python main.py 2") print("\n💡 TIPS:") print(" - Make sure MySQL is running in XAMPP") print(" - Case must exist in database with PDF uploaded") print(" - Check: http://localhost:8080/phpmyadmin") print()