""" Supplier Invoices Router - Leverandørfakturaer (Kassekladde) Backend API for managing supplier invoices that integrate with e-conomic """ from fastapi import APIRouter, HTTPException, UploadFile, File from typing import List, Dict, Optional from datetime import datetime, date, timedelta from decimal import Decimal from pathlib import Path from app.core.database import execute_query, execute_insert, execute_update from app.core.config import settings from app.services.economic_service import get_economic_service from app.services.ollama_service import ollama_service from app.services.template_service import template_service from app.services.invoice2data_service import get_invoice2data_service import logging import os import re logger = logging.getLogger(__name__) router = APIRouter() def _smart_extract_lines(text: str) -> List[Dict]: """ Universal line extraction using pdfplumber layout mode. Tries pdfplumber columnar format first, then falls back to vendor-specific patterns. """ lines_arr = text.split('\n') items = [] i = 0 while i < len(lines_arr): line = lines_arr[i].strip() # Skip empty or header lines if not line or re.search(r'(Position|Varenr|Beskrivelse|Antal|Pris|Total|Model)', line, re.IGNORECASE): i += 1 continue # Pattern 1: pdfplumber layout mode - " 1 95006 Betalingsmetode... 1 41,20 41,20" # Whitespace-separated columns: position item_number description quantity unit_price total_price # Most specific pattern - try first! layout_match = re.match(r'^\s*(\d{1,2})\s+(\d{4,10})\s+(.+?)\s(\d{1,2})\s+([\d\s]+,\d{2})\s+([\d\s]+,\d{2})\s*$', line) if layout_match: items.append({ 'line_number': len(items) + 1, 'position': layout_match.group(1), 'item_number': layout_match.group(2), 'description': layout_match.group(3).strip(), 'quantity': layout_match.group(4), 'unit_price': layout_match.group(5).replace(' ', '').replace(',', '.'), 'total_price': layout_match.group(6).replace(' ', '').replace(',', '.'), 'raw_text': line }) logger.info(f"✅ pdfplumber layout: {layout_match.group(2)} - {layout_match.group(3)[:30]}...") i += 1 continue # Pattern 2: ALSO format - "100 48023976 REFURB LENOVO..." (multi-line) item_match = re.match(r'^(\d{1,3})\s+(\d{6,})\s+(.+)', line) if item_match: position = item_match.group(1) item_number = item_match.group(2) description = item_match.group(3).strip() # Find næste linje med antal+priser quantity = None unit_price = None total_price = None for j in range(i+1, min(i+10, len(lines_arr))): price_line = lines_arr[j].strip() price_match = re.match(r'^(\d+)\s*(?:ST|stk|pc|pcs)\s+([\d.,]+)\s+([\d.,]+)', price_line, re.IGNORECASE) if price_match: quantity = price_match.group(1) unit_price = price_match.group(2).replace(',', '.') total_price = price_match.group(3).replace(',', '.') break if quantity and unit_price: items.append({ 'line_number': len(items) + 1, 'position': position, 'item_number': item_number, 'description': description, 'quantity': quantity, 'unit_price': unit_price, 'total_price': total_price, 'raw_text': f"{line} ... {quantity}ST {unit_price} {total_price}" }) logger.info(f"✅ ALSO: {item_number} - {description[:30]}...") i += 1 continue # Pattern 3: DCS single-line - "195006Betalingsmetode... 141,2041,20" (legacy PyPDF2 format) # Position: 1 digit, Item: 4-10 digits, Description starts with letter # Prices: Danish format 1-3 digits, comma, 2 decimals (e.g., 41,20 or 619,00) # Quantity: 1-2 digits (non-greedy) before first price dcs_match = re.match(r'^(\d)(\d{4,10})([A-Za-z].+?)(\d{1,2}?)(\d{1,3},\d{2})(\d{1,3},\d{2})$', line) if dcs_match: items.append({ 'line_number': len(items) + 1, 'position': dcs_match.group(1), 'item_number': dcs_match.group(2), 'description': dcs_match.group(3).strip(), 'quantity': dcs_match.group(4), 'unit_price': dcs_match.group(5).replace(',', '.'), 'total_price': dcs_match.group(6).replace(',', '.'), 'raw_text': line }) logger.info(f"✅ DCS single-line: {dcs_match.group(2)} - {dcs_match.group(3)[:30]}...") i += 1 continue # Pattern 4: DCS multi-line - "2994922511Ubiquiti..." then search for "...USW-FLEX 1619,00619,00" (legacy) dcs_multi_match = re.match(r'^(\d)(\d{4,10})([A-Za-z].+)$', line) if dcs_multi_match and not re.search(r'KN8|EAN|Model|Position|Varenr|Tekst', line): position = dcs_multi_match.group(1) item_number = dcs_multi_match.group(2) description = dcs_multi_match.group(3).strip() # Search next 5 lines for quantity/prices (Danish format 1-3 digits before comma) for j in range(1, 6): if i + j >= len(lines_arr): break price_line = lines_arr[i + j].strip() # Match: "S/N: ...USW-FLEX 1619,00619,00" - qty (1-2 digits, non-greedy) + TWO prices price_match = re.search(r'(\d{1,2}?)(\d{1,3},\d{2})(\d{1,3},\d{2})\s*$', price_line) if price_match: quantity = price_match.group(1) unit_price = price_match.group(2).replace(',', '.') total_price = price_match.group(3).replace(',', '.') items.append({ 'line_number': len(items) + 1, 'position': position, 'item_number': item_number, 'description': description, 'quantity': quantity, 'unit_price': unit_price, 'total_price': total_price, 'raw_text': f"{line} ... {price_line}" }) logger.info(f"✅ DCS multi-line: {item_number} - {description[:30]}...") break i += 1 continue i += 1 if items: logger.info(f"📦 Multi-line extraction found {len(items)} items") else: logger.warning("⚠️ Multi-line extraction found no items") return items # ========== CRUD OPERATIONS ========== @router.get("/supplier-invoices") async def list_supplier_invoices( status: Optional[str] = None, vendor_id: Optional[int] = None, overdue_only: bool = False ): """ List all supplier invoices with filtering options Args: status: Filter by status (pending, approved, sent_to_economic, paid, overdue, cancelled) vendor_id: Filter by vendor overdue_only: Only show overdue unpaid invoices """ try: query = """ SELECT si.*, v.name as vendor_full_name, v.economic_supplier_number as vendor_economic_id, CASE WHEN si.paid_date IS NOT NULL THEN 'paid' WHEN si.due_date < CURRENT_DATE AND si.paid_date IS NULL THEN 'overdue' ELSE si.status END as computed_status FROM supplier_invoices si LEFT JOIN vendors v ON si.vendor_id = v.id WHERE 1=1 """ params = [] if status: query += " AND si.status = %s" params.append(status) if vendor_id: query += " AND si.vendor_id = %s" params.append(vendor_id) if overdue_only: query += " AND si.due_date < CURRENT_DATE AND si.paid_date IS NULL" query += " ORDER BY si.due_date ASC, si.invoice_date DESC" invoices = execute_query(query, tuple(params) if params else ()) # Add lines to each invoice for invoice in invoices: lines = execute_query( "SELECT * FROM supplier_invoice_lines WHERE supplier_invoice_id = %s ORDER BY line_number", (invoice['id'],) ) invoice['lines'] = lines return invoices except Exception as e: logger.error(f"❌ Failed to list supplier invoices: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.get("/pending-supplier-invoice-files") async def get_pending_files(): """Hent alle filer der venter på behandling, inkl. AI-extracted""" try: # Hent både pending files OG ai_extracted files files = execute_query( """SELECT DISTINCT ON (f.file_id) f.file_id, f.filename, f.status, f.uploaded_at, f.error_message, f.template_id, f.file_path, -- Quick analysis results (available immediately on upload) f.detected_cvr, f.detected_vendor_id, f.detected_document_type, f.detected_document_number, f.is_own_invoice, v_detected.name as detected_vendor_name, v_detected.cvr_number as detected_vendor_cvr, -- Get vendor info from latest extraction ext.vendor_name, ext.vendor_cvr, ext.vendor_matched_id, v.name as matched_vendor_name, v.cvr_number as matched_vendor_cvr_number, -- Check if already has invoice via latest extraction only si.id as existing_invoice_id, si.invoice_number as existing_invoice_number FROM incoming_files f LEFT JOIN vendors v_detected ON v_detected.id = f.detected_vendor_id LEFT JOIN LATERAL ( SELECT extraction_id, file_id, vendor_name, vendor_cvr, vendor_matched_id FROM extractions WHERE file_id = f.file_id ORDER BY created_at DESC LIMIT 1 ) ext ON true LEFT JOIN vendors v ON v.id = ext.vendor_matched_id LEFT JOIN supplier_invoices si ON si.extraction_id = ext.extraction_id WHERE f.status IN ('pending', 'processing', 'failed', 'ai_extracted', 'processed', 'duplicate') AND si.id IS NULL -- Only show files without invoice yet ORDER BY f.file_id, f.uploaded_at DESC""" ) # Convert to regular dicts so we can add new keys files = [dict(file) for file in files] if files else [] # Check for invoice2data templates for each file try: from app.services.invoice2data_service import get_invoice2data_service invoice2data = get_invoice2data_service() logger.info(f"📋 Checking invoice2data templates: {len(invoice2data.templates)} loaded") for file in files: # Check if there's an invoice2data template for this vendor's CVR vendor_cvr = file.get('matched_vendor_cvr_number') or file.get('detected_vendor_cvr') or file.get('vendor_cvr') file['has_invoice2data_template'] = False logger.debug(f" File {file['file_id']}: CVR={vendor_cvr}") if vendor_cvr: # Check all templates for this CVR in keywords for template_name, template_data in invoice2data.templates.items(): keywords = template_data.get('keywords', []) logger.debug(f" Template {template_name}: keywords={keywords}") if str(vendor_cvr) in [str(k) for k in keywords]: file['has_invoice2data_template'] = True file['invoice2data_template_name'] = template_name logger.info(f" ✅ File {file['file_id']} matched template: {template_name}") break except Exception as e: logger.error(f"❌ Failed to check invoice2data templates: {e}", exc_info=True) # Continue without invoice2data info return {"files": files if files else [], "count": len(files) if files else 0} except Exception as e: logger.error(f"❌ Failed to get pending files: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/files/{file_id}/pdf-text") async def get_file_pdf_text(file_id: int): """Hent fuld PDF tekst fra en uploaded fil (til template builder)""" try: # Get file info file_info = execute_query( "SELECT file_path, filename FROM incoming_files WHERE file_id = %s", (file_id,), fetchone=True ) if not file_info: raise HTTPException(status_code=404, detail="Fil ikke fundet") # Read PDF text from pathlib import Path file_path = Path(file_info['file_path']) if not file_path.exists(): raise HTTPException(status_code=404, detail=f"Fil ikke fundet på disk: {file_path}") pdf_text = await ollama_service._extract_text_from_file(file_path) return { "file_id": file_id, "filename": file_info['filename'], "pdf_text": pdf_text } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to get PDF text: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/files/{file_id}/extracted-data") async def get_file_extracted_data(file_id: int): """Hent AI-extracted data fra en uploaded fil""" try: # Get file info file_info = execute_query( "SELECT * FROM incoming_files WHERE file_id = %s", (file_id,), fetchone=True ) if not file_info: raise HTTPException(status_code=404, detail="Fil ikke fundet") # Get extraction results if exists extraction = execute_query( "SELECT * FROM extractions WHERE file_id = %s ORDER BY created_at DESC LIMIT 1", (file_id,), fetchone=True ) # Parse llm_response_json if it exists (from AI or template extraction) llm_json_data = None if extraction and extraction.get('llm_response_json'): import json try: llm_json_data = json.loads(extraction['llm_response_json']) if isinstance(extraction['llm_response_json'], str) else extraction['llm_response_json'] logger.info(f"📊 Parsed llm_response_json: invoice_number={llm_json_data.get('invoice_number')}") except Exception as e: logger.warning(f"⚠️ Failed to parse llm_response_json: {e}") # Get extraction lines if exist extraction_lines = [] if extraction: extraction_lines = execute_query( """SELECT * FROM extraction_lines WHERE extraction_id = %s ORDER BY line_number""", (extraction['extraction_id'],) ) # Read PDF text if needed pdf_text = None if file_info['file_path']: from pathlib import Path file_path = Path(file_info['file_path']) if file_path.exists(): pdf_text = await ollama_service._extract_text_from_file(file_path) # Format line items for frontend formatted_lines = [] if extraction_lines: for line in extraction_lines: formatted_lines.append({ "description": line.get('description'), "quantity": float(line.get('quantity')) if line.get('quantity') else None, "unit_price": float(line.get('unit_price')) if line.get('unit_price') else None, "vat_rate": float(line.get('vat_rate')) if line.get('vat_rate') else None, "line_total": float(line.get('line_total')) if line.get('line_total') else None, "vat_note": line.get('vat_note') }) elif llm_json_data and llm_json_data.get('lines'): # Use lines from LLM JSON response for line in llm_json_data['lines']: formatted_lines.append({ "description": line.get('description'), "quantity": float(line.get('quantity')) if line.get('quantity') else None, "unit_price": float(line.get('unit_price')) if line.get('unit_price') else None, "vat_rate": float(line.get('vat_rate')) if line.get('vat_rate') else None, "line_total": float(line.get('line_total')) if line.get('line_total') else None, "vat_note": line.get('vat_note') }) # Build llm_data response llm_data = None if llm_json_data: # Use invoice_number from LLM JSON (works for both AI and template extraction) llm_data = { "invoice_number": llm_json_data.get('invoice_number'), "invoice_date": llm_json_data.get('invoice_date'), "due_date": llm_json_data.get('due_date'), "total_amount": float(llm_json_data.get('total_amount')) if llm_json_data.get('total_amount') else None, "currency": llm_json_data.get('currency') or 'DKK', "document_type": llm_json_data.get('document_type'), "lines": formatted_lines } elif extraction: # Fallback to extraction table columns if no LLM JSON llm_data = { "invoice_number": extraction.get('document_id'), "invoice_date": extraction.get('document_date').isoformat() if extraction.get('document_date') else None, "due_date": extraction.get('due_date').isoformat() if extraction.get('due_date') else None, "total_amount": float(extraction.get('total_amount')) if extraction.get('total_amount') else None, "currency": extraction.get('currency') or 'DKK', "document_type": extraction.get('document_type'), "lines": formatted_lines } # Get vendor from extraction vendor_matched_id = extraction.get('vendor_matched_id') if extraction else None return { "file_id": file_id, "filename": file_info['filename'], "status": file_info['status'], "uploaded_at": file_info['uploaded_at'], "vendor_matched_id": vendor_matched_id, "llm_data": llm_data, "extraction": extraction, "extraction_lines": extraction_lines if extraction_lines else [], "pdf_text_preview": pdf_text[:5000] if pdf_text else None } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to get extracted data: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/files/{file_id}/download") async def download_pending_file(file_id: int): """View PDF in browser""" from fastapi.responses import FileResponse from pathlib import Path try: # Get file info file_info = execute_query( "SELECT * FROM incoming_files WHERE file_id = %s", (file_id,), fetchone=True ) if not file_info: raise HTTPException(status_code=404, detail="Fil ikke fundet") file_path = Path(file_info['file_path']) if not file_path.exists(): raise HTTPException(status_code=404, detail="Fil findes ikke på disk") # Return with inline disposition so browser displays it instead of downloading return FileResponse( path=str(file_path), media_type='application/pdf', headers={"Content-Disposition": f"inline; filename={file_info['filename']}"} ) except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to view file: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/files/{file_id}/pdf") async def get_file_pdf(file_id: int): """Get PDF file for viewing (alias for download endpoint)""" return await download_pending_file(file_id) @router.post("/supplier-invoices/files/{file_id}/link-vendor") async def link_vendor_to_extraction(file_id: int, data: dict): """Link an existing vendor to the extraction""" try: vendor_id = data.get('vendor_id') if not vendor_id: raise HTTPException(status_code=400, detail="vendor_id is required") # Verify vendor exists vendor = execute_query( "SELECT id, name FROM vendors WHERE id = %s", (vendor_id,), fetchone=True ) if not vendor: raise HTTPException(status_code=404, detail="Leverandør ikke fundet") # Get latest extraction for this file extraction = execute_query( "SELECT extraction_id FROM extractions WHERE file_id = %s ORDER BY created_at DESC LIMIT 1", (file_id,), fetchone=True ) if not extraction: raise HTTPException(status_code=404, detail="Ingen extraction fundet for denne fil") # Update extraction with vendor match execute_update( """UPDATE extractions SET vendor_matched_id = %s WHERE extraction_id = %s""", (vendor_id, extraction['extraction_id']) ) logger.info(f"✅ Linked vendor {vendor['name']} (ID: {vendor_id}) to extraction for file {file_id}") return { "status": "success", "vendor_id": vendor_id, "vendor_name": vendor['name'] } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to link vendor: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.delete("/supplier-invoices/files/{file_id}") async def delete_pending_file_endpoint(file_id: int): """Slet uploaded fil og relateret data""" import os from pathlib import Path try: # Get file info file_info = execute_query( "SELECT * FROM incoming_files WHERE file_id = %s", (file_id,), fetchone=True ) if not file_info: raise HTTPException(status_code=404, detail="Fil ikke fundet") # Check if already converted to invoice invoice_exists = execute_query( """SELECT si.id FROM supplier_invoices si JOIN extractions e ON si.extraction_id = e.extraction_id WHERE e.file_id = %s""", (file_id,), fetchone=True ) if invoice_exists: raise HTTPException( status_code=400, detail="Kan ikke slette fil - der er allerede oprettet en faktura fra denne fil" ) # Delete from database (cascade will handle extractions) execute_update( "DELETE FROM incoming_files WHERE file_id = %s", (file_id,) ) # Delete physical file if file_info['file_path']: file_path = Path(file_info['file_path']) if file_path.exists(): os.remove(file_path) logger.info(f"🗑️ Deleted file: {file_path}") return {"message": "Fil slettet", "file_id": file_id} except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to delete file: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.patch("/supplier-invoices/files/{file_id}") async def update_file_status(file_id: int, data: dict): """Opdater status på uploadet fil""" try: allowed_statuses = ['pending', 'processing', 'processed', 'ai_extracted', 'completed', 'failed'] new_status = data.get('status') if not new_status or new_status not in allowed_statuses: raise HTTPException(status_code=400, detail=f"Ugyldig status. Tilladte: {', '.join(allowed_statuses)}") execute_update( "UPDATE incoming_files SET status = %s, processed_at = CURRENT_TIMESTAMP WHERE file_id = %s", (new_status, file_id) ) logger.info(f"✅ Updated file {file_id} status to {new_status}") return {"status": "success", "file_id": file_id, "new_status": new_status} except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to update file status: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.post("/supplier-invoices/files/{file_id}/link-vendor") async def link_vendor_to_extraction(file_id: int, data: dict): """Link en eksisterende leverandør til en extraction""" try: vendor_id = data.get('vendor_id') if not vendor_id: raise HTTPException(status_code=400, detail="vendor_id er påkrævet") # Verify vendor exists vendor = execute_query( "SELECT id, name FROM vendors WHERE id = %s", (vendor_id,), fetchone=True ) if not vendor: raise HTTPException(status_code=404, detail=f"Leverandør {vendor_id} ikke fundet") # Get latest extraction for this file extraction = execute_query( "SELECT extraction_id FROM extractions WHERE file_id = %s ORDER BY created_at DESC LIMIT 1", (file_id,), fetchone=True ) if not extraction: raise HTTPException(status_code=404, detail="Ingen extraction fundet for denne fil") # Update extraction with vendor match execute_update( "UPDATE extractions SET vendor_matched_id = %s WHERE extraction_id = %s", (vendor_id, extraction['extraction_id']) ) logger.info(f"✅ Linked vendor {vendor['name']} (ID: {vendor_id}) to extraction {extraction['extraction_id']}") return { "status": "success", "vendor_id": vendor_id, "vendor_name": vendor['name'], "extraction_id": extraction['extraction_id'] } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to link vendor: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.post("/supplier-invoices/from-extraction/{file_id}") async def create_invoice_from_extraction(file_id: int): """Opret leverandørfaktura fra extraction data""" try: # Get latest extraction for this file extraction = execute_query( """SELECT e.*, v.name as vendor_name FROM extractions e LEFT JOIN vendors v ON v.id = e.vendor_matched_id WHERE e.file_id = %s ORDER BY e.created_at DESC LIMIT 1""", (file_id,), fetchone=True ) if not extraction: raise HTTPException(status_code=404, detail="Ingen extraction fundet for denne fil") # Check if vendor is matched if not extraction['vendor_matched_id']: raise HTTPException( status_code=400, detail="Leverandør skal linkes før faktura kan oprettes. Brug 'Link eller Opret Leverandør' først." ) # Check if invoice already exists existing = execute_query( "SELECT id FROM supplier_invoices WHERE extraction_id = %s", (extraction['extraction_id'],), fetchone=True ) if existing: raise HTTPException(status_code=400, detail="Faktura er allerede oprettet fra denne extraction") # Get extraction lines lines = execute_query( """SELECT * FROM extraction_lines WHERE extraction_id = %s ORDER BY line_number""", (extraction['extraction_id'],) ) # Parse LLM response JSON if it's a string import json llm_data = extraction.get('llm_response_json') if isinstance(llm_data, str): try: llm_data = json.loads(llm_data) except: llm_data = {} elif not llm_data: llm_data = {} # Get invoice number and type from LLM data or generate one invoice_number = llm_data.get('invoice_number') if llm_data else None if not invoice_number: invoice_number = f"INV-{file_id}" # Detect document type (invoice or credit_note) document_type = llm_data.get('document_type', 'invoice') if llm_data else 'invoice' invoice_type = 'credit_note' if document_type == 'credit_note' else 'invoice' # Get dates - use today as fallback if missing from datetime import datetime, timedelta invoice_date = extraction.get('document_date') if not invoice_date: invoice_date = datetime.now().strftime('%Y-%m-%d') logger.warning(f"⚠️ No invoice_date found, using today: {invoice_date}") due_date = extraction.get('due_date') if not due_date: # Default to 30 days from invoice date inv_date_obj = datetime.strptime(invoice_date, '%Y-%m-%d') due_date = (inv_date_obj + timedelta(days=30)).strftime('%Y-%m-%d') logger.warning(f"⚠️ No due_date found, using invoice_date + 30 days: {due_date}") # Create supplier invoice invoice_id = execute_insert( """INSERT INTO supplier_invoices ( vendor_id, invoice_number, invoice_date, due_date, total_amount, currency, status, extraction_id, notes, invoice_type ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id""", ( extraction['vendor_matched_id'], invoice_number, invoice_date, due_date, extraction['total_amount'], extraction['currency'], 'credited' if invoice_type == 'credit_note' else 'unpaid', extraction['extraction_id'], f"Oprettet fra AI extraction (file_id: {file_id})", invoice_type ) ) # Create invoice lines if lines: for line in lines: execute_update( """INSERT INTO supplier_invoice_lines ( supplier_invoice_id, description, quantity, unit_price, line_total, vat_rate, vat_amount ) VALUES (%s, %s, %s, %s, %s, %s, %s)""", ( invoice_id, line['description'], line.get('quantity') or 1, line.get('unit_price') or 0, line.get('line_total') or 0, line.get('vat_rate') or 25.00, # Default 25% Danish VAT if NULL line.get('vat_amount') ) ) # Update file status execute_update( "UPDATE incoming_files SET status = 'completed' WHERE file_id = %s", (file_id,) ) logger.info(f"✅ Created supplier invoice {invoice_id} from extraction {extraction['extraction_id']}") return { "status": "success", "invoice_id": invoice_id, "invoice_number": invoice_number, "vendor_name": extraction['vendor_name'], "total_amount": extraction['total_amount'], "currency": extraction['currency'] } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to create invoice from extraction: {e}") raise HTTPException(status_code=500, detail=str(e)) # Keep existing endpoints below... except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to delete file: {e}") raise HTTPException(status_code=500, detail=str(e)) # ========== TEMPLATE MANAGEMENT (must be before {invoice_id} route) ========== @router.get("/supplier-invoices/templates") async def list_templates(): """Hent alle templates (både database og invoice2data YAML)""" try: # Get database templates query = """ SELECT t.*, v.name as vendor_name FROM supplier_invoice_templates t LEFT JOIN vendors v ON t.vendor_id = v.id WHERE t.is_active = true ORDER BY t.created_at DESC """ db_templates = execute_query(query) or [] # Get invoice2data templates invoice2data_service = get_invoice2data_service() invoice2data_templates = [] for template_name, template_data in invoice2data_service.templates.items(): # Extract vendor CVR from keywords vendor_cvr = None keywords = template_data.get('keywords', []) for keyword in keywords: if isinstance(keyword, str) and keyword.isdigit() and len(keyword) == 8: vendor_cvr = keyword break # Get vendor info from database if CVR found vendor_name = template_data.get('issuer', 'Ukendt') vendor_id = None if vendor_cvr: vendor = execute_query( "SELECT id, name FROM vendors WHERE cvr_number = %s", (vendor_cvr,), fetchone=True ) if vendor: vendor_id = vendor['id'] vendor_name = vendor['name'] invoice2data_templates.append({ 'template_id': -1, # Negative ID to distinguish from DB templates 'template_name': f"Invoice2Data: {template_name}", 'template_type': 'invoice2data', 'yaml_filename': template_name, 'vendor_id': vendor_id, 'vendor_name': vendor_name, 'vendor_cvr': vendor_cvr, 'default_product_category': template_data.get('default_product_category', 'varesalg'), 'default_product_group_number': template_data.get('default_product_group_number', 1), 'usage_count': 0, # Could track this separately 'is_active': True, 'detection_patterns': keywords, 'field_mappings': template_data.get('fields', {}), 'created_at': None }) # Combine both types all_templates = db_templates + invoice2data_templates return all_templates except Exception as e: logger.error(f"❌ Failed to list templates: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/templates/{template_id}") async def get_template(template_id: int): """Hent et specifikt template med vendor info""" try: query = """ SELECT t.*, v.name as vendor_name, v.cvr_number as vendor_cvr FROM supplier_invoice_templates t LEFT JOIN vendors v ON t.vendor_id = v.id WHERE t.template_id = %s AND t.is_active = true """ template = execute_query(query, (template_id,), fetchone=True) if not template: raise HTTPException(status_code=404, detail="Template not found") return template except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to get template {template_id}: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.post("/supplier-invoices/search-vendor") async def search_vendor_by_info(request: Dict): """ Søg efter vendor baseret på navn, CVR, eller opret ny Request body: { "vendor_name": "DCS ApS", "vendor_cvr": "12345678", "vendor_address": "Vej 1, 2000 By", "create_if_missing": true } """ try: vendor_name = request.get('vendor_name') vendor_cvr = request.get('vendor_cvr') vendor_address = request.get('vendor_address') create_if_missing = request.get('create_if_missing', False) # Search by CVR first (most accurate) if vendor_cvr: vendor = execute_query( "SELECT id, name, cvr_number FROM vendors WHERE cvr_number = %s", (vendor_cvr,), fetchone=True ) if vendor: return { "found": True, "vendor_id": vendor['id'], "vendor_name": vendor['name'], "source": "cvr_match" } # Search by name (fuzzy) if vendor_name: vendors = execute_query( "SELECT id, name, cvr_number FROM vendors WHERE LOWER(name) LIKE LOWER(%s) LIMIT 5", (f"%{vendor_name}%",) ) if vendors: return { "found": True, "matches": vendors, "source": "name_search", "message": "Flere mulige matches - vælg en eller opret ny" } # Create new vendor if requested if create_if_missing and vendor_name: from app.core.config import settings # Validate not creating vendor with own CVR if vendor_cvr and settings.OWN_CVR in vendor_cvr: raise HTTPException( status_code=400, detail=f"Kan ikke oprette vendor med eget CVR ({settings.OWN_CVR})" ) new_vendor_id = execute_insert( """INSERT INTO vendors (name, cvr_number, address, created_at) VALUES (%s, %s, %s, CURRENT_TIMESTAMP)""", (vendor_name, vendor_cvr, vendor_address) ) logger.info(f"✅ Created new vendor: {vendor_name} (ID: {new_vendor_id})") return { "found": False, "created": True, "vendor_id": new_vendor_id, "vendor_name": vendor_name, "source": "newly_created" } return { "found": False, "message": "Ingen vendor fundet - angiv create_if_missing=true for at oprette" } except HTTPException: raise except Exception as e: logger.error(f"❌ Vendor search failed: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.post("/supplier-invoices/ai/analyze") async def ai_analyze_invoice(request: Dict): """Brug AI til at analysere faktura og foreslå template felter""" try: pdf_text = request.get('pdf_text', '') vendor_id = request.get('vendor_id') if not pdf_text: raise HTTPException(status_code=400, detail="Ingen PDF tekst angivet") # Build enhanced PDF text with instruction from app.core.config import settings enhanced_text = f"""OPGAVE: Analyser denne danske faktura og udtræk information til template-generering. RETURNER KUN VALID JSON - ingen forklaring, ingen markdown, kun ren JSON! REQUIRED STRUKTUR (alle felter skal med): {{ "invoice_number": "5082481", "invoice_date": "24/10-25", "total_amount": "1471.20", "cvr": "29522790", "detection_patterns": ["DCS ApS", "WWW.DCS.DK", "Høgemosevænget"], "lines_start": "Nr.VarenrTekst", "lines_end": "Subtotal" }} FIND FØLGENDE: 1. invoice_number: Fakturanummer (efter "Nummer", "Faktura nr", "Invoice") 2. invoice_date: Dato (format DD/MM-YY eller DD-MM-YYYY) 3. total_amount: Total beløb - Søg efter "Total", "I alt", "Totalbeløb" - Hvis beløbet er på næste linje, match sidste tal - Format: [\d.,]+ (f.eks. 1.471,20 eller 1471.20) 4. cvr: CVR nummer (8 cifre efter "CVR", "Momsnr", "DK") - IGNORER CVR {settings.OWN_CVR} - dette er KØBERS CVR, ikke leverandør! - Find LEVERANDØRENS CVR (normalt i toppen/header) 5. detection_patterns: 3-5 UNIKKE tekststrenge der identificerer leverandøren - Leverandørens navn (f.eks. "DCS ApS", "ALSO A/S") - Website eller email (f.eks. "WWW.DCS.DK") - Adresse element (f.eks. "Høgemosevænget", "Mårkærvej") - UNDGÅ generiske ord som "Faktura", "Danmark", "Side" 6. lines_start: Tekst LIGE FØR varelinjer (f.eks. "Nr.VarenrTekst", "Position Varenr") 7. lines_end: Tekst EFTER varelinjer (f.eks. "Subtotal", "I alt", "Side 1 af") VIGTIGT: - detection_patterns SKAL være mindst 3 specifikke tekststrenge - Vælg tekststrenge der er UNIKKE for denne leverandør - CVR SKAL være leverandørens - IKKE {settings.OWN_CVR} (det er køber) - LAD VÆRE med at lave patterns eller line_item regex - kun udtræk rå data PDF TEKST: {pdf_text[:2000]} RETURNER KUN JSON - intet andet!""" # Call Ollama logger.info(f"🤖 Starter AI analyse af {len(pdf_text)} tegn PDF tekst") result = await ollama_service.extract_from_text(enhanced_text) if not result: raise HTTPException(status_code=500, detail="AI kunne ikke analysere fakturaen") logger.info(f"✅ AI analyse gennemført: {result}") return result except Exception as e: logger.error(f"❌ AI analyse fejlede: {e}") raise HTTPException(status_code=500, detail=f"AI analyse fejlede: {str(e)}") @router.post("/supplier-invoices/templates") async def create_template(request: Dict): """ Opret ny template Request body: { "vendor_id": 1, "template_name": "Test Template", "detection_patterns": [{"type": "text", "pattern": "BMC Denmark", "weight": 0.5}], "field_mappings": {"invoice_number": {"pattern": r"Nummer\s*(\d+)", "group": 1}} } """ try: import json from app.core.config import settings vendor_id = request.get('vendor_id') template_name = request.get('template_name') detection_patterns = request.get('detection_patterns', []) field_mappings = request.get('field_mappings', {}) default_product_category = request.get('default_product_category', 'varesalg') if not vendor_id or not template_name: raise HTTPException(status_code=400, detail="vendor_id og template_name er påkrævet") # Validate that vendor CVR is not own company vendor_cvr_mapping = field_mappings.get('vendor_cvr', {}) if vendor_cvr_mapping: # Extract CVR value from pattern or value field cvr_value = vendor_cvr_mapping.get('value') or vendor_cvr_mapping.get('pattern', '') if settings.OWN_CVR in str(cvr_value): raise HTTPException( status_code=400, detail=f"CVR {cvr_value} matcher egen virksomhed ({settings.OWN_CVR}). Brug leverandørens CVR, ikke købers!" ) # Insert template and get template_id query = """ INSERT INTO supplier_invoice_templates (vendor_id, template_name, detection_patterns, field_mappings, default_product_category) VALUES (%s, %s, %s, %s, %s) RETURNING template_id """ result = execute_query(query, (vendor_id, template_name, json.dumps(detection_patterns), json.dumps(field_mappings), default_product_category)) template_id = result[0]['template_id'] if result else None if not template_id: raise HTTPException(status_code=500, detail="Kunne ikke oprette template") # Reload templates in cache template_service.reload_templates() logger.info(f"✅ Template created: {template_name} (ID: {template_id}) for vendor {vendor_id}") return {"template_id": template_id, "message": "Template oprettet"} except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to create template: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/{invoice_id}") async def get_supplier_invoice(invoice_id: int): """Get single supplier invoice with lines""" try: invoice = execute_query( """SELECT si.*, v.name as vendor_full_name, v.economic_supplier_number as vendor_economic_id FROM supplier_invoices si LEFT JOIN vendors v ON si.vendor_id = v.id WHERE si.id = %s""", (invoice_id,), fetchone=True ) if not invoice: raise HTTPException(status_code=404, detail=f"Invoice {invoice_id} not found") # Get lines lines = execute_query( "SELECT * FROM supplier_invoice_lines WHERE supplier_invoice_id = %s ORDER BY line_number", (invoice_id,) ) invoice['lines'] = lines return invoice except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to get supplier invoice {invoice_id}: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.post("/supplier-invoices") async def create_supplier_invoice(data: Dict): """ Create new supplier invoice Required fields: - invoice_number: str - vendor_id: int - invoice_date: str (YYYY-MM-DD) - total_amount: float Optional fields: - due_date: str (YYYY-MM-DD) - defaults to invoice_date + 30 days - vat_amount: float - net_amount: float - currency: str (default 'DKK') - description: str - notes: str - lines: List[Dict] with line items """ try: # Validate required fields required = ['invoice_number', 'vendor_id', 'invoice_date', 'total_amount'] missing = [f for f in required if f not in data] if missing: raise HTTPException(status_code=400, detail=f"Missing required fields: {', '.join(missing)}") # Calculate due_date if not provided (30 days default) invoice_date = datetime.fromisoformat(data['invoice_date']) due_date = data.get('due_date') if not due_date: due_date = (invoice_date + timedelta(days=30)).strftime('%Y-%m-%d') # Determine invoice type (default to invoice) invoice_type = data.get('invoice_type', 'invoice') if invoice_type not in ['invoice', 'credit_note']: invoice_type = 'invoice' # Insert supplier invoice invoice_id = execute_insert( """INSERT INTO supplier_invoices (invoice_number, vendor_id, vendor_name, invoice_date, due_date, total_amount, vat_amount, net_amount, currency, description, notes, status, created_by, invoice_type) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", ( data['invoice_number'], data['vendor_id'], data.get('vendor_name'), data['invoice_date'], due_date, data['total_amount'], data.get('vat_amount', 0), data.get('net_amount', data['total_amount']), data.get('currency', 'DKK'), data.get('description'), data.get('notes'), 'credited' if invoice_type == 'credit_note' else 'pending', data.get('created_by'), invoice_type ) ) # Insert lines if provided if data.get('lines'): for idx, line in enumerate(data['lines'], start=1): # Map vat_code: I52 for reverse charge, I25 for standard vat_code = line.get('vat_code', 'I25') execute_insert( """INSERT INTO supplier_invoice_lines (supplier_invoice_id, line_number, description, quantity, unit_price, line_total, vat_code, vat_rate, vat_amount, contra_account, sku) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", ( invoice_id, line.get('line_number', idx), line.get('description'), line.get('quantity', 1), line.get('unit_price', 0), line.get('line_total', 0), vat_code, line.get('vat_rate', 25.00), line.get('vat_amount', 0), line.get('contra_account', '5810'), line.get('sku') ) ) logger.info(f"✅ Created supplier invoice: {data['invoice_number']} (ID: {invoice_id})") return { "success": True, "invoice_id": invoice_id, "invoice_number": data['invoice_number'], "due_date": due_date } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to create supplier invoice: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.put("/supplier-invoices/{invoice_id}") async def update_supplier_invoice(invoice_id: int, data: Dict): """Update supplier invoice details""" try: # Check if invoice exists existing = execute_query( "SELECT id, status FROM supplier_invoices WHERE id = %s", (invoice_id,), fetchone=True ) if not existing: raise HTTPException(status_code=404, detail=f"Invoice {invoice_id} not found") # Don't allow editing if already sent to e-conomic if existing['status'] == 'sent_to_economic': raise HTTPException( status_code=400, detail="Cannot edit invoice that has been sent to e-conomic" ) # Build update query dynamically based on provided fields update_fields = [] params = [] allowed_fields = ['invoice_number', 'vendor_id', 'vendor_name', 'invoice_date', 'due_date', 'total_amount', 'vat_amount', 'net_amount', 'currency', 'description', 'notes', 'status'] for field in allowed_fields: if field in data: update_fields.append(f"{field} = %s") params.append(data[field]) if not update_fields: raise HTTPException(status_code=400, detail="No fields to update") params.append(invoice_id) query = f""" UPDATE supplier_invoices SET {', '.join(update_fields)}, updated_at = CURRENT_TIMESTAMP WHERE id = %s """ execute_update(query, tuple(params)) logger.info(f"✅ Updated supplier invoice {invoice_id}") return {"success": True, "invoice_id": invoice_id} except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to update supplier invoice {invoice_id}: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.delete("/supplier-invoices/{invoice_id}") async def delete_supplier_invoice(invoice_id: int): """Delete supplier invoice (soft delete if integrated with e-conomic)""" try: invoice = execute_query( "SELECT id, invoice_number, economic_voucher_number FROM supplier_invoices WHERE id = %s", (invoice_id,), fetchone=True ) if not invoice: raise HTTPException(status_code=404, detail=f"Invoice {invoice_id} not found") # If sent to e-conomic, only mark as cancelled (don't delete) if invoice.get('economic_voucher_number'): execute_update( "UPDATE supplier_invoices SET status = 'cancelled', updated_at = CURRENT_TIMESTAMP WHERE id = %s", (invoice_id,) ) logger.info(f"⚠️ Marked supplier invoice {invoice['invoice_number']} as cancelled (sent to e-conomic)") return {"success": True, "message": "Invoice marked as cancelled", "invoice_id": invoice_id} # Otherwise, delete invoice and lines execute_update("DELETE FROM supplier_invoice_lines WHERE supplier_invoice_id = %s", (invoice_id,)) execute_update("DELETE FROM supplier_invoices WHERE id = %s", (invoice_id,)) logger.info(f"🗑️ Deleted supplier invoice {invoice['invoice_number']} (ID: {invoice_id})") return {"success": True, "message": "Invoice deleted", "invoice_id": invoice_id} except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to delete supplier invoice {invoice_id}: {e}") raise HTTPException(status_code=500, detail=str(e)) # ========== E-CONOMIC INTEGRATION ========== @router.post("/supplier-invoices/{invoice_id}/approve") async def approve_supplier_invoice(invoice_id: int, approved_by: str): """Approve supplier invoice for payment""" try: invoice = execute_query( "SELECT id, invoice_number, status FROM supplier_invoices WHERE id = %s", (invoice_id,), fetchone=True ) if not invoice: raise HTTPException(status_code=404, detail=f"Invoice {invoice_id} not found") if invoice['status'] != 'pending': raise HTTPException(status_code=400, detail=f"Invoice is already {invoice['status']}") execute_update( """UPDATE supplier_invoices SET status = 'approved', approved_by = %s, approved_at = CURRENT_TIMESTAMP WHERE id = %s""", (approved_by, invoice_id) ) logger.info(f"✅ Approved supplier invoice {invoice['invoice_number']} by {approved_by}") return {"success": True, "invoice_id": invoice_id, "approved_by": approved_by} except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to approve invoice {invoice_id}: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.post("/supplier-invoices/{invoice_id}/send-to-economic") async def send_to_economic(invoice_id: int): """ Send approved supplier invoice to e-conomic kassekladde Creates voucher entry in e-conomic journals """ try: # Get invoice with lines invoice = execute_query( """SELECT si.*, v.economic_supplier_number as vendor_economic_id, v.name as vendor_full_name FROM supplier_invoices si LEFT JOIN vendors v ON si.vendor_id = v.id WHERE si.id = %s""", (invoice_id,), fetchone=True ) if not invoice: raise HTTPException(status_code=404, detail=f"Invoice {invoice_id} not found") if invoice['status'] != 'approved': raise HTTPException(status_code=400, detail="Invoice must be approved before sending to e-conomic") if invoice.get('economic_voucher_number'): raise HTTPException(status_code=400, detail="Invoice already sent to e-conomic") # Get lines lines = execute_query( "SELECT * FROM supplier_invoice_lines WHERE supplier_invoice_id = %s ORDER BY line_number", (invoice_id,) ) if not lines: raise HTTPException(status_code=400, detail="Invoice must have at least one line item") # Check if vendor exists in e-conomic economic = get_economic_service() vendor_economic_id = invoice.get('vendor_economic_id') # If vendor not in e-conomic, create it if not vendor_economic_id: vendor_result = await economic.search_supplier_by_name(invoice.get('vendor_full_name') or invoice.get('vendor_name')) if vendor_result: vendor_economic_id = vendor_result['supplierNumber'] # Update local vendor record execute_update( "UPDATE vendors SET economic_supplier_number = %s WHERE id = %s", (vendor_economic_id, invoice['vendor_id']) ) else: # Create new supplier in e-conomic new_supplier = await economic.create_supplier({ 'name': invoice.get('vendor_full_name') or invoice.get('vendor_name'), 'currency': invoice.get('currency', 'DKK') }) if new_supplier and new_supplier.get('supplierNumber'): vendor_economic_id = new_supplier['supplierNumber'] else: raise HTTPException(status_code=500, detail="Failed to create supplier in e-conomic") # Get default journal number from settings journal_setting = execute_query( "SELECT setting_value FROM supplier_invoice_settings WHERE setting_key = 'economic_default_journal'", fetchone=True ) journal_number = int(journal_setting['setting_value']) if journal_setting else 1 # Build VAT breakdown from lines vat_breakdown = {} line_items = [] for line in lines: vat_code = line.get('vat_code', 'I25') if vat_code not in vat_breakdown: vat_breakdown[vat_code] = { 'net': 0, 'vat': 0, 'gross': 0, 'rate': line.get('vat_rate', 25.00) } line_total = float(line.get('line_total', 0)) vat_amount = float(line.get('vat_amount', 0)) net_amount = line_total - vat_amount vat_breakdown[vat_code]['net'] += net_amount vat_breakdown[vat_code]['vat'] += vat_amount vat_breakdown[vat_code]['gross'] += line_total line_items.append({ 'description': line.get('description'), 'quantity': float(line.get('quantity', 1)), 'unit_price': float(line.get('unit_price', 0)), 'line_total': line_total, 'vat_code': vat_code, 'vat_amount': vat_amount, 'contra_account': line.get('contra_account', '5810'), 'sku': line.get('sku') }) # Send to e-conomic result = await economic.create_journal_supplier_invoice( journal_number=journal_number, supplier_number=vendor_economic_id, invoice_number=invoice['invoice_number'], invoice_date=invoice['invoice_date'].isoformat() if isinstance(invoice['invoice_date'], date) else invoice['invoice_date'], total_amount=float(invoice['total_amount']), vat_breakdown=vat_breakdown, line_items=line_items, due_date=invoice['due_date'].isoformat() if invoice.get('due_date') and isinstance(invoice['due_date'], date) else invoice.get('due_date'), text=invoice.get('description') or f"Supplier invoice {invoice['invoice_number']}" ) if result.get('error'): raise HTTPException(status_code=500, detail=result.get('message', 'Failed to create voucher in e-conomic')) # Update invoice with e-conomic details execute_update( """UPDATE supplier_invoices SET status = 'sent_to_economic', economic_supplier_number = %s, economic_journal_number = %s, economic_voucher_number = %s, economic_accounting_year = %s, sent_to_economic_at = CURRENT_TIMESTAMP WHERE id = %s""", ( vendor_economic_id, result['journal_number'], result['voucher_number'], result['accounting_year'], invoice_id ) ) # Upload attachment if file_path exists if invoice.get('file_path') and os.path.exists(invoice['file_path']): attachment_result = await economic.upload_voucher_attachment( journal_number=result['journal_number'], accounting_year=result['accounting_year'], voucher_number=result['voucher_number'], pdf_path=invoice['file_path'], filename=f"{invoice['invoice_number']}.pdf" ) if attachment_result.get('success'): logger.info(f"📎 Uploaded attachment for voucher {result['voucher_number']}") logger.info(f"✅ Sent supplier invoice {invoice['invoice_number']} to e-conomic (voucher #{result['voucher_number']})") return { "success": True, "invoice_id": invoice_id, "voucher_number": result['voucher_number'], "journal_number": result['journal_number'], "accounting_year": result['accounting_year'] } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to send invoice {invoice_id} to e-conomic: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/economic/journals") async def get_economic_journals(): """Get available e-conomic journals (kassekladder)""" try: economic = get_economic_service() journals = await economic.get_supplier_invoice_journals() return {"journals": journals} except Exception as e: logger.error(f"❌ Failed to get e-conomic journals: {e}") raise HTTPException(status_code=500, detail=str(e)) # ========== STATISTICS & REPORTS ========== @router.get("/supplier-invoices/stats/overview") async def get_payment_overview(): """ Get overview of supplier invoices payment status Returns stats for total, paid, overdue, due soon, and pending invoices """ try: today = date.today().isoformat() stats = execute_query(""" SELECT COUNT(*) as total_count, SUM(CASE WHEN paid_date IS NOT NULL THEN 1 ELSE 0 END) as paid_count, SUM(CASE WHEN paid_date IS NULL AND due_date < %s THEN 1 ELSE 0 END) as overdue_count, SUM(CASE WHEN paid_date IS NULL AND due_date >= %s AND due_date <= (%s::date + INTERVAL '7 days') THEN 1 ELSE 0 END) as due_soon_count, SUM(CASE WHEN paid_date IS NULL AND (due_date IS NULL OR due_date > (%s::date + INTERVAL '7 days')) THEN 1 ELSE 0 END) as pending_count, SUM(total_amount) as total_amount, SUM(CASE WHEN paid_date IS NOT NULL THEN total_amount ELSE 0 END) as paid_amount, SUM(CASE WHEN paid_date IS NULL THEN total_amount ELSE 0 END) as unpaid_amount, SUM(CASE WHEN paid_date IS NULL AND due_date < %s THEN total_amount ELSE 0 END) as overdue_amount FROM supplier_invoices WHERE status != 'cancelled' """, (today, today, today, today, today), fetchone=True) return { "total_invoices": stats.get('total_count', 0) if stats else 0, "paid_count": stats.get('paid_count', 0) if stats else 0, "overdue_count": stats.get('overdue_count', 0) if stats else 0, "due_soon_count": stats.get('due_soon_count', 0) if stats else 0, "pending_count": stats.get('pending_count', 0) if stats else 0, "total_amount": float(stats.get('total_amount', 0) or 0) if stats else 0, "paid_amount": float(stats.get('paid_amount', 0) or 0) if stats else 0, "unpaid_amount": float(stats.get('unpaid_amount', 0) or 0) if stats else 0, "overdue_amount": float(stats.get('overdue_amount', 0) or 0) if stats else 0 } except Exception as e: logger.error(f"❌ Failed to get payment overview: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/stats/by-vendor") async def get_stats_by_vendor(): """Get supplier invoice statistics grouped by vendor""" try: stats = execute_query(""" SELECT v.id as vendor_id, v.name as vendor_name, COUNT(si.id) as invoice_count, SUM(si.total_amount) as total_amount, SUM(CASE WHEN si.paid_date IS NULL THEN si.total_amount ELSE 0 END) as unpaid_amount, MAX(si.due_date) as latest_due_date FROM vendors v LEFT JOIN supplier_invoices si ON v.id = si.vendor_id WHERE si.status != 'cancelled' OR si.status IS NULL GROUP BY v.id, v.name HAVING COUNT(si.id) > 0 ORDER BY unpaid_amount DESC """) return {"vendor_stats": stats} except Exception as e: logger.error(f"❌ Failed to get vendor stats: {e}") raise HTTPException(status_code=500, detail=str(e)) # ========== UPLOAD & AI EXTRACTION ========== @router.post("/supplier-invoices/upload") async def upload_supplier_invoice(file: UploadFile = File(...)): """ Upload supplier invoice (PDF/image) and extract data using templates Process: 1. Validate file type and size 2. Calculate SHA256 checksum for duplicate detection 3. Save file to uploads directory 4. Extract text (PDF/OCR) 5. Match template based on PDF content 6. Extract fields using template regex patterns 7. Show form with pre-filled data for user review Returns: { "status": "success|duplicate|needs_review", "file_id": int, "template_matched": bool, "template_id": int, "extracted_fields": dict, "confidence": float, "pdf_text": str # For manual review } """ from app.core.config import settings try: # Validate file extension suffix = Path(file.filename).suffix.lower() if suffix not in settings.ALLOWED_EXTENSIONS: raise HTTPException( status_code=400, detail=f"Filtype {suffix} ikke tilladt. Tilladte: {', '.join(settings.ALLOWED_EXTENSIONS)}" ) # Create upload directory upload_dir = Path(settings.UPLOAD_DIR) upload_dir.mkdir(parents=True, exist_ok=True) # Save file temporarily to calculate checksum temp_path = upload_dir / f"temp_{datetime.now().timestamp()}_{file.filename}" try: # Validate file size while saving max_size = settings.MAX_FILE_SIZE_MB * 1024 * 1024 total_size = 0 with open(temp_path, "wb") as buffer: while chunk := await file.read(8192): total_size += len(chunk) if total_size > max_size: temp_path.unlink(missing_ok=True) raise HTTPException( status_code=413, detail=f"Fil for stor (max {settings.MAX_FILE_SIZE_MB}MB)" ) buffer.write(chunk) logger.info(f"📥 Uploaded file: {file.filename} ({total_size} bytes)") # Calculate SHA256 checksum checksum = ollama_service.calculate_file_checksum(temp_path) # Check for duplicate file existing_file = execute_query( "SELECT file_id, status FROM incoming_files WHERE checksum = %s", (checksum,), fetchone=True ) if existing_file: temp_path.unlink(missing_ok=True) logger.warning(f"⚠️ Duplicate file detected: {checksum[:16]}...") # Get existing invoice if linked existing_invoice = execute_query( """SELECT si.* FROM supplier_invoices si JOIN extractions e ON si.extraction_id = e.extraction_id WHERE e.file_id = %s""", (existing_file['file_id'],), fetchone=True ) return { "status": "duplicate", "message": "Denne fil er allerede uploadet", "file_id": existing_file['file_id'], "invoice_id": existing_invoice['id'] if existing_invoice else None } # Rename to permanent name final_path = upload_dir / file.filename counter = 1 while final_path.exists(): final_path = upload_dir / f"{final_path.stem}_{counter}{final_path.suffix}" counter += 1 temp_path.rename(final_path) logger.info(f"💾 Saved file as: {final_path.name}") # Insert file record file_record = execute_query( """INSERT INTO incoming_files (filename, original_filename, file_path, file_size, mime_type, checksum, status) VALUES (%s, %s, %s, %s, %s, %s, 'processing') RETURNING file_id""", (final_path.name, file.filename, str(final_path), total_size, ollama_service._get_mime_type(final_path), checksum), fetchone=True ) file_id = file_record['file_id'] # Extract text from file logger.info(f"📄 Extracting text from {final_path.suffix}...") text = await ollama_service._extract_text_from_file(final_path) # QUICK ANALYSIS: Extract CVR, document type, invoice number IMMEDIATELY logger.info(f"⚡ Running quick analysis...") quick_result = await ollama_service.quick_analysis_on_upload(text) # Update file record with quick analysis results execute_update( """UPDATE incoming_files SET detected_cvr = %s, detected_vendor_id = %s, detected_document_type = %s, detected_document_number = %s, is_own_invoice = %s WHERE file_id = %s""", (quick_result.get('cvr'), quick_result.get('vendor_id'), quick_result.get('document_type'), quick_result.get('document_number'), quick_result.get('is_own_invoice', False), file_id) ) logger.info(f"📋 Quick analysis saved: CVR={quick_result.get('cvr')}, " f"Vendor={quick_result.get('vendor_name')}, " f"Type={quick_result.get('document_type')}, " f"Number={quick_result.get('document_number')}") # DUPLICATE CHECK: Check if invoice number already exists document_number = quick_result.get('document_number') if document_number: logger.info(f"🔍 Checking for duplicate invoice number: {document_number}") # Check 1: Search in local database (supplier_invoices table) existing_invoice = execute_query( """SELECT si.id, si.invoice_number, si.created_at, v.name as vendor_name FROM supplier_invoices si LEFT JOIN vendors v ON v.id = si.vendor_id WHERE si.invoice_number = %s ORDER BY si.created_at DESC LIMIT 1""", (document_number,), fetchone=True ) if existing_invoice: # DUPLICATE FOUND IN DATABASE logger.error(f"🚫 DUPLICATE: Invoice {document_number} already exists in database (ID: {existing_invoice['id']})") # Mark file as duplicate execute_update( """UPDATE incoming_files SET status = 'duplicate', error_message = %s, processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (f"DUBLET: Fakturanummer {document_number} findes allerede i systemet (Faktura #{existing_invoice['id']}, {existing_invoice['vendor_name'] or 'Ukendt leverandør'})", file_id) ) raise HTTPException( status_code=409, # 409 Conflict detail=f"🚫 DUBLET: Fakturanummer {document_number} findes allerede i systemet (Faktura #{existing_invoice['id']}, oprettet {existing_invoice['created_at'].strftime('%d-%m-%Y')})" ) # Check 2: Search in e-conomic (if configured) from app.services.economic_service import economic_service if hasattr(economic_service, 'app_secret_token') and economic_service.app_secret_token: logger.info(f"🔍 Checking e-conomic for invoice number: {document_number}") economic_duplicate = await economic_service.check_invoice_number_exists(document_number) if economic_duplicate: # DUPLICATE FOUND IN E-CONOMIC logger.error(f"🚫 DUPLICATE: Invoice {document_number} found in e-conomic (Voucher #{economic_duplicate.get('voucher_number')})") # Mark file as duplicate execute_update( """UPDATE incoming_files SET status = 'duplicate', error_message = %s, processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (f"DUBLET: Fakturanummer {document_number} findes i e-conomic (Bilag #{economic_duplicate.get('voucher_number')})", file_id) ) raise HTTPException( status_code=409, # 409 Conflict detail=f"🚫 DUBLET: Fakturanummer {document_number} findes i e-conomic (Bilag #{economic_duplicate.get('voucher_number')}, {economic_duplicate.get('date')})" ) logger.info(f"✅ No duplicate found for invoice {document_number}") # Try template matching logger.info(f"📋 Matching template...") template_id, confidence = template_service.match_template(text) extracted_fields = {} vendor_id = None if template_id and confidence >= 0.5: # Extract fields using template logger.info(f"✅ Using template {template_id} ({confidence:.0%} confidence)") extracted_fields = template_service.extract_fields(text, template_id) # Get vendor from template template = template_service.templates_cache.get(template_id) if template: vendor_id = template.get('vendor_id') # Save extraction to database import json extraction_id = execute_insert( """INSERT INTO extractions (file_id, vendor_matched_id, document_id, document_date, due_date, total_amount, currency, document_type, confidence, llm_response_json, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'extracted')""", (file_id, vendor_id, extracted_fields.get('invoice_number'), extracted_fields.get('invoice_date'), extracted_fields.get('due_date'), extracted_fields.get('total_amount'), extracted_fields.get('currency', 'DKK'), extracted_fields.get('document_type'), confidence, json.dumps(extracted_fields)) ) # Insert line items if extracted if extracted_fields.get('lines'): for idx, line in enumerate(extracted_fields['lines'], start=1): execute_insert( """INSERT INTO extraction_lines (extraction_id, line_number, description, quantity, unit_price, line_total, vat_rate, vat_note, confidence) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING line_id""", (extraction_id, idx, line.get('description'), line.get('quantity'), line.get('unit_price'), line.get('line_total'), line.get('vat_rate'), line.get('vat_note'), confidence) ) # Log usage template_service.log_usage(template_id, file_id, True, confidence, extracted_fields) # Update file record execute_update( """UPDATE incoming_files SET status = 'processed', template_id = %s, processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (template_id, file_id) ) else: # NO AI FALLBACK - Require template logger.warning(f"⚠️ No template matched (confidence: {confidence:.0%}) - rejecting file") execute_update( """UPDATE incoming_files SET status = 'failed', error_message = 'Ingen template match - opret template for denne leverandør', processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (file_id,) ) raise HTTPException( status_code=400, detail=f"Ingen template match ({confidence:.0%} confidence) - opret template for denne leverandør" ) # Return data for user to review and confirm return { "status": "needs_review", "file_id": file_id, "template_matched": template_id is not None, "template_id": template_id, "vendor_id": vendor_id, "confidence": confidence, "extracted_fields": extracted_fields, "pdf_text": text[:500], # First 500 chars for reference # Quick analysis results (available IMMEDIATELY on upload) "quick_analysis": { "cvr": quick_result.get('cvr'), "vendor_id": quick_result.get('vendor_id'), "vendor_name": quick_result.get('vendor_name'), "document_type": quick_result.get('document_type'), "document_number": quick_result.get('document_number') }, "message": "Upload gennemført - gennemgå og bekræft data" } except HTTPException as he: # Mark file as failed if we have file_id if 'file_id' in locals(): execute_update( """UPDATE incoming_files SET status = 'failed', error_message = %s, processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (str(he.detail), file_id) ) raise except Exception as e: logger.error(f"❌ Upload failed (inner): {e}", exc_info=True) # Mark file as failed if we have file_id if 'file_id' in locals(): execute_update( """UPDATE incoming_files SET status = 'failed', error_message = %s, processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (str(e), file_id) ) raise HTTPException(status_code=500, detail=f"Upload fejlede: {str(e)}") except HTTPException: raise except Exception as e: logger.error(f"❌ Upload failed (outer): {e}", exc_info=True) raise HTTPException(status_code=500, detail=f"Upload fejlede: {str(e)}") # ========== ECONOMIC SYNC ========== @router.post("/supplier-invoices/{invoice_id}/send-to-economic") async def send_invoice_to_economic(invoice_id: int): """Send supplier invoice to e-conomic - requires separate implementation""" raise HTTPException(status_code=501, detail="e-conomic integration kommer senere") @router.post("/supplier-invoices/reprocess/{file_id}") async def reprocess_uploaded_file(file_id: int): """ Genbehandl en uploadet fil med template matching Bruges til at behandle filer der fejlede eller ikke blev færdigbehandlet """ import json from datetime import datetime, timedelta try: # Get file record file_record = execute_query( "SELECT * FROM incoming_files WHERE file_id = %s", (file_id,), fetchone=True ) if not file_record: raise HTTPException(status_code=404, detail=f"Fil {file_id} ikke fundet") file_path = Path(file_record['file_path']) if not file_path.exists(): raise HTTPException(status_code=404, detail=f"Fil ikke fundet på disk: {file_path}") logger.info(f"�� Genbehandler fil {file_id}: {file_record['filename']}") # Extract text from file text = await ollama_service._extract_text_from_file(file_path) # Try template matching template_id, confidence = template_service.match_template(text) extracted_fields = {} vendor_id = None if template_id and confidence >= 0.5: logger.info(f"✅ Matched template {template_id} ({confidence:.0%})") extracted_fields = template_service.extract_fields(text, template_id) # Check if this is an invoice2data template (ID -1) is_invoice2data = (template_id == -1) if is_invoice2data: # Invoice2data doesn't have vendor in cache logger.info(f"📋 Using invoice2data template") # Try to find vendor from extracted CVR if extracted_fields.get('vendor_vat'): vendor = execute_query( "SELECT id FROM vendors WHERE cvr_number = %s", (extracted_fields['vendor_vat'],), fetchone=True ) if vendor: vendor_id = vendor['id'] # Store invoice2data extraction in database extraction_id = execute_insert( """INSERT INTO extractions (file_id, vendor_matched_id, vendor_name, vendor_cvr, document_id, document_date, due_date, document_type, document_type_detected, total_amount, currency, confidence, llm_response_json, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING extraction_id""", (file_id, vendor_id, extracted_fields.get('issuer'), # vendor_name extracted_fields.get('vendor_vat'), # vendor_cvr str(extracted_fields.get('invoice_number')), # document_id extracted_fields.get('invoice_date'), # document_date extracted_fields.get('due_date'), 'invoice', # document_type 'invoice', # document_type_detected extracted_fields.get('amount_total'), extracted_fields.get('currency', 'DKK'), 1.0, # invoice2data always 100% confidence json.dumps(extracted_fields), # llm_response_json 'extracted') # status ) # Insert line items if extracted if extracted_fields.get('lines'): for idx, line in enumerate(extracted_fields['lines'], start=1): execute_insert( """INSERT INTO extraction_lines (extraction_id, line_number, description, quantity, unit_price, line_total, vat_rate, vat_note, confidence, ip_address, contract_number, location_street, location_zip, location_city) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING line_id""", (extraction_id, idx, line.get('description'), line.get('quantity'), line.get('unit_price'), line.get('line_total'), None, None, 1.0, line.get('ip_address'), line.get('contract_number'), line.get('location_street'), line.get('location_zip'), line.get('location_city')) ) logger.info(f"✅ Saved {len(extracted_fields['lines'])} line items") else: # Custom template from database template = template_service.templates_cache.get(template_id) if template: vendor_id = template.get('vendor_id') template_service.log_usage(template_id, file_id, True, confidence, extracted_fields) # Update file - use NULL for invoice2data templates to avoid FK constraint db_template_id = None if is_invoice2data else template_id execute_update( """UPDATE incoming_files SET status = 'processed', template_id = %s, processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (db_template_id, file_id) ) else: # FALLBACK TO AI EXTRACTION logger.info(f"⚠️ Ingen template match (confidence: {confidence:.0%}) - bruger AI extraction") # Use detected vendor from quick analysis if available vendor_id = file_record.get('detected_vendor_id') # Call Ollama for full extraction logger.info(f"🤖 Calling Ollama for AI extraction...") llm_result = await ollama_service.extract_from_text(text) if not llm_result or 'error' in llm_result: error_msg = llm_result.get('error') if llm_result else 'AI extraction fejlede' logger.error(f"❌ AI extraction failed: {error_msg}") execute_update( """UPDATE incoming_files SET status = 'failed', error_message = %s, processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (f"AI extraction fejlede: {error_msg}", file_id) ) raise HTTPException(status_code=500, detail=f"AI extraction fejlede: {error_msg}") extracted_fields = llm_result confidence = llm_result.get('confidence', 0.75) # Store AI extracted data in extractions table extraction_id = execute_insert( """INSERT INTO supplier_invoice_extractions (file_id, vendor_id, invoice_number, invoice_date, due_date, total_amount, currency, document_type, confidence, llm_data) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING extraction_id""", (file_id, vendor_id, llm_result.get('invoice_number'), llm_result.get('invoice_date'), llm_result.get('due_date'), llm_result.get('total_amount'), llm_result.get('currency', 'DKK'), llm_result.get('document_type'), confidence, json.dumps(llm_result)) ) # Insert line items if extracted if llm_result.get('lines'): for idx, line in enumerate(llm_result['lines'], start=1): execute_insert( """INSERT INTO extraction_lines (extraction_id, line_number, description, quantity, unit_price, line_total, vat_rate, vat_note, confidence) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING line_id""", (extraction_id, idx, line.get('description'), line.get('quantity'), line.get('unit_price'), line.get('line_total'), line.get('vat_rate'), line.get('vat_note'), confidence) ) # Update file status to ai_extracted execute_update( """UPDATE incoming_files SET status = 'ai_extracted', processed_at = CURRENT_TIMESTAMP WHERE file_id = %s""", (file_id,) ) logger.info(f"✅ AI extraction completed for file {file_id}") # Return success with template data or AI extraction result result = { "status": "success", "file_id": file_id, "filename": file_record['filename'], "template_matched": template_id is not None, "template_id": template_id, "vendor_id": vendor_id, "confidence": confidence if template_id else llm_result.get('confidence', 0.75), "extracted_fields": extracted_fields, "pdf_text": text[:1000] if not template_id else text } # Add warning if no template exists if not template_id and vendor_id: vendor = execute_query( "SELECT name FROM vendors WHERE id = %s", (vendor_id,), fetchone=True ) if vendor: result["warning"] = f"⚠️ Ingen template fundet for {vendor['name']} - brugte AI extraction (langsommere)" return result except HTTPException: raise except Exception as e: raise HTTPException(status_code=500, detail=f"Genbehandling fejlede: {str(e)}") @router.put("/supplier-invoices/templates/{template_id}") async def update_template( template_id: int, template_name: Optional[str] = None, detection_patterns: Optional[List[Dict]] = None, field_mappings: Optional[Dict] = None, default_product_category: Optional[str] = None, is_active: Optional[bool] = None ): """Opdater eksisterende template""" try: import json updates = [] params = [] if template_name: updates.append("template_name = %s") params.append(template_name) if detection_patterns is not None: updates.append("detection_patterns = %s") params.append(json.dumps(detection_patterns)) if field_mappings is not None: updates.append("field_mappings = %s") params.append(json.dumps(field_mappings)) if default_product_category is not None: updates.append("default_product_category = %s") params.append(default_product_category) if is_active is not None: updates.append("is_active = %s") params.append(is_active) if not updates: raise HTTPException(status_code=400, detail="Ingen opdateringer angivet") updates.append("updated_at = CURRENT_TIMESTAMP") params.append(template_id) execute_update( f"UPDATE supplier_invoice_templates SET {', '.join(updates)} WHERE template_id = %s", tuple(params) ) # Reload templates template_service.reload_templates() logger.info(f"✅ Template {template_id} opdateret") return {"message": "Template opdateret"} except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to update template: {e}") raise HTTPException(status_code=500, detail=str(e)) @router.post("/supplier-invoices/templates/invoice2data/{template_name}/test") async def test_invoice2data_template(template_name: str, request: Dict): """ Test invoice2data YAML template mod PDF tekst Request body: { "pdf_text": "Full PDF text content..." } Returns samme format som test_template endpoint """ try: pdf_text = request.get('pdf_text', '') if not pdf_text: raise HTTPException(status_code=400, detail="pdf_text er påkrævet") # Get invoice2data service invoice2data_service = get_invoice2data_service() # Check if template exists if template_name not in invoice2data_service.templates: raise HTTPException(status_code=404, detail=f"Template '{template_name}' ikke fundet") template_data = invoice2data_service.templates[template_name] # Test extraction result = invoice2data_service.extract_with_template(pdf_text, template_name) if not result: # Template didn't match keywords = template_data.get('keywords', []) detection_results = [] for keyword in keywords: found = str(keyword).lower() in pdf_text.lower() detection_results.append({ "pattern": str(keyword), "type": "keyword", "found": found, "weight": 0.5 }) return { "matched": False, "confidence": 0.0, "extracted_fields": {}, "line_items": [], "detection_results": detection_results, "template_name": template_name, "error": "Template matchede ikke PDF'en" } # Extract line items line_items = [] if 'lines' in result: for line in result['lines']: line_items.append({ "line_number": line.get('line_number', ''), "item_number": line.get('item_number', ''), "description": line.get('description_raw', '') or line.get('description', ''), "quantity": line.get('quantity', ''), "unit_price": line.get('unit_price', ''), "line_total": line.get('line_total', ''), # Context fields (circuit/location info) "circuit_id": line.get('circuit_id', ''), "ip_address": line.get('ip_address', ''), "contract_number": line.get('contract_number', ''), "location_street": line.get('location_street', ''), "location_zip": line.get('location_zip', ''), "location_city": line.get('location_city', ''), }) # Build detection results keywords = template_data.get('keywords', []) detection_results = [] matched_count = 0 for keyword in keywords: found = str(keyword).lower() in pdf_text.lower() if found: matched_count += 1 detection_results.append({ "pattern": str(keyword), "type": "keyword", "found": found, "weight": 0.5 }) confidence = matched_count / len(keywords) if keywords else 1.0 # Remove 'lines' from extracted_fields to avoid duplication extracted_fields = {k: v for k, v in result.items() if k != 'lines'} return { "matched": True, "confidence": confidence, "extracted_fields": extracted_fields, "line_items": line_items, "detection_results": detection_results, "template_name": template_name } except HTTPException: raise except Exception as e: logger.error(f"❌ Invoice2data template test failed: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) @router.post("/supplier-invoices/templates/{template_id}/test") async def test_template(template_id: int, request: Dict): """ Test template mod PDF tekst Request body: { "pdf_text": "Full PDF text content..." } Returns: { "matched": true/false, "confidence": 0.85, "extracted_fields": { "invoice_number": "12345", "invoice_date": "01/12-25", "total_amount": "1234.56", "vendor_cvr": "12345678" }, "detection_results": [ {"pattern": "BMC Denmark ApS", "found": true, "weight": 0.5} ] } """ try: import re import json pdf_text = request.get('pdf_text', '') if not pdf_text: raise HTTPException(status_code=400, detail="pdf_text er påkrævet") # Fetch template query = "SELECT * FROM supplier_invoice_templates WHERE template_id = %s" template = execute_query(query, (template_id,)) if not template: raise HTTPException(status_code=404, detail="Template ikke fundet") template = template[0] detection_patterns = template.get('detection_patterns', []) field_mappings = template.get('field_mappings', {}) # Test detection patterns total_score = 0.0 max_score = 0.0 detection_results = [] for pattern in detection_patterns: pattern_type = pattern.get('type', 'text') pattern_value = pattern.get('pattern', '') weight = float(pattern.get('weight', 0.5)) max_score += weight found = False if pattern_type == 'text' and pattern_value in pdf_text: found = True total_score += weight detection_results.append({ "pattern": pattern_value, "type": pattern_type, "found": found, "weight": weight }) confidence = (total_score / max_score) if max_score > 0 else 0.0 matched = confidence >= 0.7 # Match threshold # Extract fields if matched extracted_fields = {} if matched: for field_name, field_config in field_mappings.items(): pattern = field_config.get('pattern', '') group = field_config.get('group', 1) # Skip non-field patterns (lines_start, lines_end, line_item) if field_name in ['lines_start', 'lines_end', 'line_item']: continue try: match = re.search(pattern, pdf_text, re.IGNORECASE | re.MULTILINE) if match and len(match.groups()) >= group: extracted_fields[field_name] = match.group(group).strip() except Exception as e: logger.warning(f"Pattern match failed for {field_name}: {e}") # Extract line items if matched line_items = [] if matched: # Extract line items using smart extraction lines_start = field_mappings.get('lines_start', {}).get('pattern') lines_end = field_mappings.get('lines_end', {}).get('pattern') line_pattern = field_mappings.get('line_item', {}).get('pattern') line_fields = field_mappings.get('line_item', {}).get('fields', []) if line_pattern or lines_start: # Extract section between start and end markers text_section = pdf_text if lines_start: try: start_match = re.search(lines_start, pdf_text, re.IGNORECASE) if start_match: text_section = pdf_text[start_match.end():] logger.debug(f"Found lines_start at position {start_match.end()}") except Exception as e: logger.warning(f"Failed to find lines_start: {e}") if lines_end: try: end_match = re.search(lines_end, text_section, re.IGNORECASE) if end_match: text_section = text_section[:end_match.start()] logger.debug(f"Found lines_end at position {end_match.start()}") except Exception as e: logger.warning(f"Failed to find lines_end: {e}") # Try pattern first, then smart extraction if line_pattern: try: for match in re.finditer(line_pattern, text_section, re.MULTILINE): line_data = { 'line_number': len(line_items) + 1, 'raw_text': match.group(0) } for idx, field_name in enumerate(line_fields, start=1): if idx <= len(match.groups()): line_data[field_name] = match.group(idx).strip() line_items.append(line_data) except Exception as e: logger.error(f"❌ Pattern extraction failed: {e}") # Fallback to smart extraction if no lines found if not line_items: logger.info("🧠 Trying smart extraction...") logger.debug(f"Text section length: {len(text_section)}, first 500 chars: {text_section[:500]}") line_items = _smart_extract_lines(text_section) logger.info(f"🧠 Smart extraction returned {len(line_items)} items") if line_items: logger.info(f"📦 Extracted {len(line_items)} line items from test") else: logger.warning(f"⚠️ No line items matched. Section length: {len(text_section)} chars") logger.debug(f"Section preview: {text_section[:300]}") logger.info(f"🧪 Template {template_id} test: matched={matched}, confidence={confidence:.2f}, lines={len(line_items)}") return { "matched": matched, "confidence": round(confidence, 2), "extracted_fields": extracted_fields, "line_items": line_items, "detection_results": detection_results, "template_name": template.get('template_name', '') } except HTTPException: raise except Exception as e: logger.error(f"❌ Template test failed: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) @router.put("/supplier-invoices/templates/invoice2data/{template_name}/category") async def update_yaml_category(template_name: str, request: Dict): """ Opdater default_product_category i YAML template fil Request body: { "category": "drift" // varesalg, drift, anlæg, abonnement, lager, udlejning } """ try: import yaml from pathlib import Path new_category = request.get('category') if not new_category: raise HTTPException(status_code=400, detail="category er påkrævet") # Validate category valid_categories = ['varesalg', 'drift', 'anlæg', 'abonnement', 'lager', 'udlejning'] if new_category not in valid_categories: raise HTTPException(status_code=400, detail=f"Ugyldig kategori. Skal være en af: {', '.join(valid_categories)}") # Find YAML file templates_dir = Path(__file__).parent.parent.parent.parent / 'data' / 'invoice_templates' yaml_file = templates_dir / f"{template_name}.yml" if not yaml_file.exists(): raise HTTPException(status_code=404, detail=f"YAML fil ikke fundet: {template_name}.yml") # Load YAML with open(yaml_file, 'r', encoding='utf-8') as f: template_data = yaml.safe_load(f) # Update category template_data['default_product_category'] = new_category # Save YAML with preserved formatting with open(yaml_file, 'w', encoding='utf-8') as f: yaml.dump(template_data, f, default_flow_style=False, allow_unicode=True, sort_keys=False) # Reload invoice2data service to pick up changes invoice2data_service = get_invoice2data_service() invoice2data_service.__init__() # Reinitialize to reload templates logger.info(f"✅ Updated category for {template_name}.yml to {new_category}") return { "message": "Kategori opdateret", "template_name": template_name, "new_category": new_category } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to update YAML category: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) @router.get("/supplier-invoices/templates/invoice2data/{template_name}/content") async def get_yaml_content(template_name: str): """ Hent råt YAML indhold fra template fil Returns: { "content": "issuer: DCS ApS\nkeywords: ..." } """ try: from pathlib import Path # Find template file template_dir = Path("data/invoice_templates") template_file = template_dir / f"{template_name}.yml" if not template_file.exists(): raise HTTPException(status_code=404, detail=f"Template fil ikke fundet: {template_name}.yml") # Read file content content = template_file.read_text(encoding='utf-8') return { "template_name": template_name, "filename": f"{template_name}.yml", "content": content } except HTTPException: raise except Exception as e: logger.error(f"❌ Failed to read YAML content: {e}", exc_info=True) raise HTTPException(status_code=500, detail=str(e)) @router.delete("/supplier-invoices/templates/{template_id}") async def delete_template(template_id: int): """Slet template (soft delete - sæt is_active=false)""" try: execute_update( "UPDATE supplier_invoice_templates SET is_active = false WHERE template_id = %s", (template_id,) ) template_service.reload_templates() logger.info(f"✅ Template {template_id} deaktiveret") return {"message": "Template slettet"} except Exception as e: logger.error(f"❌ Failed to delete template: {e}") raise HTTPException(status_code=500, detail=str(e))