""" Invoice Error Finder API router. """ import json import logging import re from datetime import date from typing import Any, Dict, List, Optional from fastapi import APIRouter, Depends, HTTPException, Query, Request from pydantic import BaseModel, Field from app.core.auth_dependencies import require_permission from app.core.database import execute_query, execute_query_single from app.modules.invoice_error_finder.services.economic_import_service import EconomicImportService from app.modules.invoice_error_finder.services.simply_import_service import SimplyImportService from app.modules.invoice_error_finder.services.detection_service import DetectionService logger = logging.getLogger(__name__) router = APIRouter() ALLOWED_ISSUE_STATUSES = { "open", "investigating", "approved_change", "error_found", "ready_to_invoice", "invoiced", "ignored", "resolved", } ISSUE_STATUS_LABELS = { "open": "Åben", "investigating": "Under undersøgelse", "approved_change": "Godkendt ændring", "error_found": "Fejl fundet", "ready_to_invoice": "Opret ordrekladde", "invoiced": "Faktureret", "ignored": "Ignoreret", "resolved": "Løst", } ISSUE_TYPE_LABELS = { "missing_line": "Manglende varelinje", "open_order_not_invoiced": "Åben salgsordre ikke faktureret", "quantity_drop": "Antalsfald", "price_change": "Prisændring", "new_item_never_invoiced": "Ny vare aldrig faktureret", } class IssueStatusUpdate(BaseModel): status: str notes: Optional[str] = None assigned_user_id: Optional[int] = None class CreateSagRequest(BaseModel): titel: str = Field(..., min_length=1) beskrivelse: Optional[str] = "" status: Optional[str] = "åben" class LinkSagRequest(BaseModel): sag_id: int class CreateOrdreDraftRequest(BaseModel): description: Optional[str] = None def _tokenize_product_text(value: Optional[str]) -> List[str]: if not value: return [] return re.findall(r"[a-z0-9]+", value.lower()) _PRODUCT_MATCH_STOP_TOKENS = { "periode", "period", "forbrugsperiode", "jan", "januar", "january", "feb", "februar", "february", "mar", "marts", "march", "apr", "april", "maj", "may", "jun", "juni", "june", "jul", "juli", "july", "aug", "august", "sep", "sept", "september", "okt", "oct", "october", "oktober", "nov", "november", "dec", "december", "til", "from", "to", "fra", } def _normalized_product_tokens(value: Optional[str]) -> List[str]: tokens = [] for token in _tokenize_product_text(value): if token in _PRODUCT_MATCH_STOP_TOKENS: continue if token.isdigit() and len(token) == 4: continue tokens.append(token) return tokens def _line_matches_issue_product( line_product_number: Optional[str], line_product_name: Optional[str], line_description: Optional[str], issue_product_number: Optional[str], issue_product_name: Optional[str], extra_text: Optional[str] = None, ) -> bool: line_number = (line_product_number or "").strip().lower() issue_number = (issue_product_number or "").strip().lower() issue_name = (issue_product_name or "").strip().lower() combined_text = " ".join( part.strip().lower() for part in [line_product_name or "", line_description or "", extra_text or ""] if part and part.strip() ) if not issue_name: return bool(line_number and issue_number and line_number == issue_number) if not combined_text: return False issue_tokens = _normalized_product_tokens(issue_name) line_tokens = _normalized_product_tokens(combined_text) issue_token_set = set(issue_tokens) line_token_set = set(line_tokens) shared_tokens = issue_token_set & line_token_set alpha_shared = {token for token in shared_tokens if any(ch.isalpha() for ch in token)} if issue_name in combined_text or combined_text in issue_name: return True if line_number and issue_number and line_number == issue_number: if not issue_token_set: return True if len(shared_tokens) >= max(1, min(2, len(issue_token_set))): return True if not issue_token_set or not line_token_set: return False coverage = len(shared_tokens) / max(1, len(issue_token_set)) if len(issue_token_set) == 1: return len(shared_tokens) >= 1 if len(issue_token_set) == 2: return len(shared_tokens) >= 2 if coverage >= 0.75: return True if coverage >= 0.5 and len(alpha_shared) >= 1: return True return len(shared_tokens) >= 3 and len(alpha_shared) >= 1 def _get_user_id(request: Request) -> Optional[int]: value = getattr(request.state, "user_id", None) if value is not None: try: return int(value) except (TypeError, ValueError): return None return None @router.post("/import/economic") async def import_economic( request: Request, current_user: dict = Depends(require_permission("invoice_error_finder.run_import")), ): """Trigger e-conomic invoice import.""" try: service = EconomicImportService() result = await service.import_invoices( triggered_by_user_id=_get_user_id(request), is_scheduled=False, ) return result except Exception as exc: logger.error("❌ Economic import endpoint failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.post("/import/simply") async def import_simply( request: Request, current_user: dict = Depends(require_permission("invoice_error_finder.run_import")), ): """Trigger Simply CRM sales order import.""" try: service = SimplyImportService() result = await service.import_sales_orders( triggered_by_user_id=_get_user_id(request), is_scheduled=False, ) return result except Exception as exc: logger.error("❌ Simply import endpoint failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.post("/analyze") async def analyze_issues( reference_month: Optional[str] = Query(None), current_user: dict = Depends(require_permission("invoice_error_finder.analyze")), ): """Run detection rules and create/update issues.""" try: ref_date = None if reference_month: ref_date = date.fromisoformat(reference_month + "-01") service = DetectionService() counts = service.analyze(reference_month=ref_date) return {"reference_month": (ref_date or date.today().replace(day=1)).isoformat(), "counts": counts} except Exception as exc: logger.error("❌ Analyze endpoint failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.get("/dashboard") async def get_dashboard( current_user: dict = Depends(require_permission("invoice_error_finder.view")), ) -> Dict[str, Any]: """Summary counts for the dashboard.""" try: status_counts = execute_query( """ SELECT issue_type, status, COUNT(*) AS count, COALESCE(SUM(amount_impact), 0) AS total_impact FROM invoice_error_finder_issues GROUP BY issue_type, status """, (), ) or [] summary = {} for row in status_counts: itype = row["issue_type"] status = row["status"] summary.setdefault(itype, {})[status] = { "count": row["count"], "total_impact": float(row["total_impact"] or 0), } def count_by_type(issue_type: str, statuses: List[str]) -> Dict[str, Any]: total = 0 impact = 0.0 if issue_type == "*": for issue_summary in summary.values(): for status in statuses: data = issue_summary.get(status, {}) total += data.get("count", 0) impact += data.get("total_impact", 0.0) else: for status in statuses: data = summary.get(issue_type, {}).get(status, {}) total += data.get("count", 0) impact += data.get("total_impact", 0.0) return {"count": total, "total_impact": impact} last_runs = execute_query( """ SELECT source_type, status, records_imported, records_failed, started_at FROM invoice_error_finder_import_runs ORDER BY started_at DESC LIMIT 5 """, (), ) or [] return { "missing_line": count_by_type("missing_line", ["open", "investigating"]), "open_order_not_invoiced": count_by_type("open_order_not_invoiced", ["open", "investigating"]), "quantity_drop": count_by_type("quantity_drop", ["open", "investigating"]), "price_change": count_by_type("price_change", ["open", "investigating"]), "ready_to_invoice": count_by_type("*", ["ready_to_invoice"]), "no_owner": { "count": execute_query_single( "SELECT COUNT(*) AS c FROM invoice_error_finder_issues WHERE status IN ('open','investigating','ready_to_invoice') AND assigned_user_id IS NULL" )["c"], "total_impact": 0.0, }, "last_import_runs": [dict(r) for r in last_runs], } except Exception as exc: logger.error("❌ Dashboard endpoint failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.get("/issues") async def list_issues( status: Optional[str] = Query(None), issue_type: Optional[str] = Query(None), customer_id: Optional[int] = Query(None), assigned_user_id: Optional[str] = Query(None), limit: int = Query(100, ge=1, le=500), offset: int = Query(0, ge=0), current_user: dict = Depends(require_permission("invoice_error_finder.view")), ): """List detected issues with optional filters.""" try: filters = ["1=1"] params: List[Any] = [] if status: filters.append("i.status = %s") params.append(status) if issue_type: filters.append("i.issue_type = %s") params.append(issue_type) if customer_id: filters.append("i.customer_id = %s") params.append(customer_id) if assigned_user_id is not None: normalized_assigned = assigned_user_id.strip().lower() if normalized_assigned == "null": filters.append("i.assigned_user_id IS NULL") elif normalized_assigned != "": try: assigned_user_id_int = int(assigned_user_id) except (TypeError, ValueError): assigned_user_id_int = None if assigned_user_id_int is not None: filters.append("i.assigned_user_id IS NOT DISTINCT FROM %s") params.append(assigned_user_id_int) where_clause = " AND ".join(filters) count_row = execute_query_single( f"SELECT COUNT(*) AS c FROM invoice_error_finder_issues i WHERE {where_clause}", tuple(params), ) total = count_row["c"] if count_row else 0 params.extend([limit, offset]) rows = execute_query( f""" SELECT i.*, COALESCE(NULLIF(i.product_name, ''), latest_line.product_label) AS resolved_product_name, COALESCE(u.full_name, u.username) AS assigned_user_name, sg.titel AS sag_title FROM invoice_error_finder_issues i LEFT JOIN customers c ON c.id = i.customer_id LEFT JOIN LATERAL ( SELECT COALESCE(NULLIF(line.description, ''), NULLIF(line.product_name, ''), NULLIF(line.product_number, '')) AS product_label FROM invoice_error_finder_economic_invoices inv JOIN invoice_error_finder_economic_invoice_lines line ON line.invoice_id = inv.id WHERE c.economic_customer_number IS NOT NULL AND inv.customer_number = c.economic_customer_number AND LOWER(TRIM(COALESCE(line.product_number, ''))) = LOWER(TRIM(COALESCE(i.product_number, ''))) ORDER BY inv.invoice_date DESC, inv.id DESC, line.line_number DESC LIMIT 1 ) latest_line ON TRUE LEFT JOIN users u ON u.user_id = i.assigned_user_id LEFT JOIN sag_sager sg ON sg.id = i.sag_id WHERE {where_clause} ORDER BY i.created_at DESC LIMIT %s OFFSET %s """, tuple(params), ) or [] return { "total": total, "limit": limit, "offset": offset, "items": [dict(r) for r in rows], } except Exception as exc: logger.error("❌ Issues list endpoint failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.get("/issues/{issue_id}") async def get_issue( issue_id: int, current_user: dict = Depends(require_permission("invoice_error_finder.view")), ): """Get a single issue.""" try: row = execute_query_single( """ SELECT i.*, COALESCE(NULLIF(i.product_name, ''), latest_line.product_label) AS resolved_product_name, COALESCE(u.full_name, u.username) AS assigned_user_name, sg.titel AS sag_title FROM invoice_error_finder_issues i LEFT JOIN customers c ON c.id = i.customer_id LEFT JOIN LATERAL ( SELECT COALESCE(NULLIF(line.description, ''), NULLIF(line.product_name, ''), NULLIF(line.product_number, '')) AS product_label FROM invoice_error_finder_economic_invoices inv JOIN invoice_error_finder_economic_invoice_lines line ON line.invoice_id = inv.id WHERE c.economic_customer_number IS NOT NULL AND inv.customer_number = c.economic_customer_number AND LOWER(TRIM(COALESCE(line.product_number, ''))) = LOWER(TRIM(COALESCE(i.product_number, ''))) ORDER BY inv.invoice_date DESC, inv.id DESC, line.line_number DESC LIMIT 1 ) latest_line ON TRUE LEFT JOIN users u ON u.user_id = i.assigned_user_id LEFT JOIN sag_sager sg ON sg.id = i.sag_id WHERE i.id = %s """, (issue_id,), ) if not row: raise HTTPException(status_code=404, detail="Issue not found") return dict(row) except HTTPException: raise except Exception as exc: logger.error("❌ Get issue endpoint failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.get("/issues/{issue_id}/invoice-history") async def get_issue_invoice_history( issue_id: int, current_user: dict = Depends(require_permission("invoice_error_finder.view")), ): """Return monthly invoice history around an issue for the same customer/product.""" try: issue = execute_query_single( """ SELECT i.id, i.customer_id, i.customer_name, i.product_number, i.product_name, i.reference_period_start, i.reference_period_end FROM invoice_error_finder_issues i WHERE i.id = %s """, (issue_id,), ) if not issue: raise HTTPException(status_code=404, detail="Issue not found") customer_id = issue.get("customer_id") product_number = issue.get("product_number") reference_period_start = issue.get("reference_period_start") if not customer_id: raise HTTPException(status_code=400, detail="Issue has no mapped customer") if not product_number: raise HTTPException(status_code=400, detail="Issue has no product number") if not reference_period_start: raise HTTPException(status_code=400, detail="Issue has no reference period") customer = execute_query_single( "SELECT id, name, economic_customer_number FROM customers WHERE id = %s", (customer_id,), ) if not customer or not customer.get("economic_customer_number"): raise HTTPException(status_code=400, detail="Customer has no e-conomic mapping") source_rank = {"paid": 1, "booked": 2, "unpaid": 3, "draft": 4} def dedupe_invoice_rows(rows: List[Dict[str, Any]]) -> List[Dict[str, Any]]: best_by_number: Dict[str, Dict[str, Any]] = {} for row in rows: invoice_number = row.get("source_invoice_number") if not invoice_number: continue current_best = best_by_number.get(invoice_number) candidate_rank = ( source_rank.get(row.get("source_type"), 9), -(row.get("invoice_date").toordinal() if row.get("invoice_date") else 0), -(int(row.get("invoice_id") or 0)), ) if current_best is None: best_by_number[invoice_number] = row continue current_rank = ( source_rank.get(current_best.get("source_type"), 9), -(current_best.get("invoice_date").toordinal() if current_best.get("invoice_date") else 0), -(int(current_best.get("invoice_id") or 0)), ) if candidate_rank < current_rank: best_by_number[invoice_number] = row selected_ids = {row.get("invoice_id") for row in best_by_number.values() if row.get("invoice_id")} return [row for row in rows if row.get("invoice_id") in selected_ids] def build_invoice_payloads(rows: List[Dict[str, Any]]) -> tuple[Dict[int, Dict[str, Any]], Dict[str, List[Dict[str, Any]]]]: invoices_by_id: Dict[int, Dict[str, Any]] = {} invoices_by_month: Dict[str, List[Dict[str, Any]]] = {} for row in rows: invoice_id = row["invoice_id"] month_key = row["month_start"].isoformat() if row.get("month_start") else None if invoice_id not in invoices_by_id: payload = { "invoice_id": invoice_id, "invoice_number": row.get("source_invoice_number"), "invoice_date": row["invoice_date"].isoformat() if row.get("invoice_date") else None, "total_amount": float(row.get("total_amount") or 0), "net_amount": float(row.get("net_amount") or 0), "vat_amount": float(row.get("vat_amount") or 0), "currency": row.get("currency") or "DKK", "source_type": row.get("source_type"), "heading": row.get("heading") or None, "note_text": row.get("note_text") or None, "lines": [], } invoices_by_id[invoice_id] = payload if month_key: invoices_by_month.setdefault(month_key, []).append(payload) invoices_by_id[invoice_id]["lines"].append( { "line_number": int(row.get("line_number") or 0), "product_number": row.get("product_number"), "product_name": row.get("product_name"), "description": row.get("description"), "quantity": float(row.get("quantity") or 0), "unit_price": float(row.get("unit_price") or 0), "line_net_amount": float(row.get("line_net_amount") or 0), } ) return invoices_by_id, invoices_by_month def aggregate_months(month_rows: List[Dict[str, Any]], matched_rows: List[Dict[str, Any]], invoices_by_month: Dict[str, List[Dict[str, Any]]]) -> List[Dict[str, Any]]: agg_by_month: Dict[str, Dict[str, Any]] = {} for row in matched_rows: month_key = row["month_start"].isoformat() if row.get("month_start") else None if not month_key: continue bucket = agg_by_month.setdefault( month_key, { "line_count": 0, "total_quantity": 0.0, "total_amount": 0.0, "invoice_numbers": [], "invoice_dates": [], "descriptions": [], "_seen_invoice_numbers": set(), "_seen_descriptions": set(), }, ) bucket["line_count"] += 1 bucket["total_quantity"] += float(row.get("quantity") or 0) bucket["total_amount"] += float(row.get("line_net_amount") or 0) invoice_number = row.get("source_invoice_number") if invoice_number and invoice_number not in bucket["_seen_invoice_numbers"]: bucket["invoice_numbers"].append(invoice_number) bucket["invoice_dates"].append(row["invoice_date"].isoformat() if row.get("invoice_date") else None) bucket["_seen_invoice_numbers"].add(invoice_number) description = row.get("description") if description and description not in bucket["_seen_descriptions"]: bucket["descriptions"].append(description) bucket["_seen_descriptions"].add(description) month_payloads: List[Dict[str, Any]] = [] for month_row in month_rows: month_key = month_row["month_start"].isoformat() if month_row.get("month_start") else None bucket = agg_by_month.get(month_key) or {} month_payloads.append( { "month_start": month_key, "line_count": int(bucket.get("line_count") or 0), "total_quantity": float(bucket.get("total_quantity") or 0), "total_amount": float(bucket.get("total_amount") or 0), "invoice_numbers": bucket.get("invoice_numbers") or [], "invoice_dates": bucket.get("invoice_dates") or [], "descriptions": bucket.get("descriptions") or [], "invoices": invoices_by_month.get(month_key, []), "is_reference_month": month_key == reference_period_start.replace(day=1).isoformat(), "is_fallback_history": False, } ) return month_payloads month_rows = execute_query( """ SELECT generate_series( date_trunc('month', %s::date) - interval '13 months', date_trunc('month', %s::date) + interval '2 months', interval '1 month' )::date AS month_start ORDER BY month_start """, (reference_period_start, reference_period_start), ) or [] candidate_window_rows = execute_query( """ SELECT inv.id AS invoice_id, inv.source_invoice_number, inv.invoice_date, inv.total_amount, inv.net_amount, inv.vat_amount, inv.currency, inv.source_type, date_trunc('month', inv.invoice_date)::date AS month_start, COALESCE(inv.source_raw::jsonb -> 'notes' ->> 'heading', '') AS heading, NULLIF( CONCAT_WS( E'\n', NULLIF(inv.source_raw::jsonb -> 'notes' ->> 'textLine1', ''), NULLIF(inv.source_raw::jsonb -> 'notes' ->> 'textLine2', '') ), '' ) AS note_text, line.line_number, line.product_number, line.product_name, line.description, line.quantity, line.unit_price, line.line_net_amount FROM invoice_error_finder_economic_invoices inv JOIN invoice_error_finder_economic_invoice_lines line ON line.invoice_id = inv.id WHERE inv.customer_number = %s AND inv.invoice_date >= date_trunc('month', %s::date) - interval '13 months' AND inv.invoice_date < date_trunc('month', %s::date) + interval '3 months' ORDER BY inv.invoice_date DESC, inv.source_invoice_number DESC, line.line_number """, ( customer["economic_customer_number"], reference_period_start, reference_period_start, ), ) or [] window_rows = dedupe_invoice_rows(candidate_window_rows) matched_window_rows = [ row for row in window_rows if _line_matches_issue_product( row.get("product_number"), row.get("product_name"), row.get("description"), product_number, issue.get("product_name"), " ".join(part for part in [row.get("heading") or "", row.get("note_text") or ""] if part), ) ] matched_window_invoice_ids = {row["invoice_id"] for row in matched_window_rows} invoice_rows = [row for row in window_rows if row.get("invoice_id") in matched_window_invoice_ids] _, invoices_by_month = build_invoice_payloads(invoice_rows) months_payload = aggregate_months(month_rows, matched_window_rows, invoices_by_month) fallback_month_rows: List[Dict[str, Any]] = [] if not matched_window_invoice_ids: candidate_older_rows = execute_query( """ SELECT inv.id AS invoice_id, inv.source_invoice_number, inv.invoice_date, inv.total_amount, inv.net_amount, inv.vat_amount, inv.currency, inv.source_type, date_trunc('month', inv.invoice_date)::date AS month_start, COALESCE(inv.source_raw::jsonb -> 'notes' ->> 'heading', '') AS heading, NULLIF( CONCAT_WS( E'\n', NULLIF(inv.source_raw::jsonb -> 'notes' ->> 'textLine1', ''), NULLIF(inv.source_raw::jsonb -> 'notes' ->> 'textLine2', '') ), '' ) AS note_text, line.line_number, line.product_number, line.product_name, line.description, line.quantity, line.unit_price, line.line_net_amount FROM invoice_error_finder_economic_invoices inv JOIN invoice_error_finder_economic_invoice_lines line ON line.invoice_id = inv.id WHERE inv.customer_number = %s AND inv.invoice_date < date_trunc('month', %s::date) - interval '13 months' ORDER BY inv.invoice_date DESC, inv.source_invoice_number DESC, line.line_number """, ( customer["economic_customer_number"], reference_period_start, ), ) or [] older_rows = dedupe_invoice_rows(candidate_older_rows) matched_older_rows = [ row for row in older_rows if _line_matches_issue_product( row.get("product_number"), row.get("product_name"), row.get("description"), product_number, issue.get("product_name"), " ".join(part for part in [row.get("heading") or "", row.get("note_text") or ""] if part), ) ] top3_invoice_ids: List[int] = [] seen_ids = set() for row in matched_older_rows: invoice_id = row.get("invoice_id") if invoice_id and invoice_id not in seen_ids: seen_ids.add(invoice_id) top3_invoice_ids.append(invoice_id) if len(top3_invoice_ids) == 3: break fallback_invoice_rows = [row for row in older_rows if row.get("invoice_id") in set(top3_invoice_ids)] _, fallback_invoices_by_month = build_invoice_payloads(fallback_invoice_rows) fallback_month_map: Dict[str, Dict[str, Any]] = {} for row in matched_older_rows: if row.get("invoice_id") not in top3_invoice_ids or not row.get("month_start"): continue month_key = row["month_start"].isoformat() bucket = fallback_month_map.setdefault( month_key, { "month_start": month_key, "line_count": 0, "total_quantity": 0.0, "total_amount": 0.0, "invoice_numbers": [], "invoice_dates": [], "descriptions": [], "invoices": fallback_invoices_by_month.get(month_key, []), "is_reference_month": False, "is_fallback_history": True, "_seen_invoice_numbers": set(), "_seen_descriptions": set(), }, ) bucket["line_count"] += 1 bucket["total_quantity"] += float(row.get("quantity") or 0) bucket["total_amount"] += float(row.get("line_net_amount") or 0) invoice_number = row.get("source_invoice_number") if invoice_number and invoice_number not in bucket["_seen_invoice_numbers"]: bucket["invoice_numbers"].append(invoice_number) bucket["invoice_dates"].append(row["invoice_date"].isoformat() if row.get("invoice_date") else None) bucket["_seen_invoice_numbers"].add(invoice_number) description = row.get("description") if description and description not in bucket["_seen_descriptions"]: bucket["descriptions"].append(description) bucket["_seen_descriptions"].add(description) fallback_month_rows = sorted( [ {key: value for key, value in month.items() if not key.startswith("_")} for month in fallback_month_map.values() ], key=lambda item: item["month_start"], ) if not matched_window_invoice_ids and not fallback_month_rows: candidate_global_rows = execute_query( """ SELECT inv.id AS invoice_id, inv.source_invoice_number, inv.invoice_date, inv.total_amount, inv.net_amount, inv.vat_amount, inv.currency, inv.source_type, date_trunc('month', inv.invoice_date)::date AS month_start, COALESCE(inv.source_raw::jsonb -> 'notes' ->> 'heading', '') AS heading, NULLIF( CONCAT_WS( E'\n', NULLIF(inv.source_raw::jsonb -> 'notes' ->> 'textLine1', ''), NULLIF(inv.source_raw::jsonb -> 'notes' ->> 'textLine2', '') ), '' ) AS note_text, line.line_number, line.product_number, line.product_name, line.description, line.quantity, line.unit_price, line.line_net_amount FROM invoice_error_finder_economic_invoices inv JOIN invoice_error_finder_economic_invoice_lines line ON line.invoice_id = inv.id WHERE inv.invoice_date < date_trunc('month', %s::date) + interval '3 months' ORDER BY inv.invoice_date DESC, inv.source_invoice_number DESC, line.line_number """, (reference_period_start,), ) or [] global_rows = dedupe_invoice_rows(candidate_global_rows) matched_global_rows = [ row for row in global_rows if _line_matches_issue_product( row.get("product_number"), row.get("product_name"), row.get("description"), product_number, issue.get("product_name"), " ".join(part for part in [row.get("heading") or "", row.get("note_text") or ""] if part), ) ] top3_global_invoice_ids: List[int] = [] seen_ids = set() for row in matched_global_rows: invoice_id = row.get("invoice_id") if invoice_id and invoice_id not in seen_ids: seen_ids.add(invoice_id) top3_global_invoice_ids.append(invoice_id) if len(top3_global_invoice_ids) == 3: break global_fallback_invoice_rows = [ row for row in global_rows if row.get("invoice_id") in set(top3_global_invoice_ids) ] _, global_fallback_invoices_by_month = build_invoice_payloads(global_fallback_invoice_rows) global_fallback_month_map: Dict[str, Dict[str, Any]] = {} for row in matched_global_rows: if row.get("invoice_id") not in top3_global_invoice_ids: continue if not row.get("month_start"): continue month_key = row["month_start"].isoformat() bucket = global_fallback_month_map.setdefault( month_key, { "month_start": month_key, "line_count": 0, "total_quantity": 0.0, "total_amount": 0.0, "invoice_numbers": [], "invoice_dates": [], "descriptions": [], "invoices": global_fallback_invoices_by_month.get(month_key, []), "is_reference_month": False, "is_fallback_history": True, "fallback_label": "Seneste lignende fakturaer", "_seen_invoice_numbers": set(), "_seen_descriptions": set(), }, ) bucket["line_count"] += 1 bucket["total_quantity"] += float(row.get("quantity") or 0) bucket["total_amount"] += float(row.get("line_net_amount") or 0) invoice_number = row.get("source_invoice_number") if invoice_number and invoice_number not in bucket["_seen_invoice_numbers"]: bucket["invoice_numbers"].append(invoice_number) bucket["invoice_dates"].append(row["invoice_date"].isoformat() if row.get("invoice_date") else None) bucket["_seen_invoice_numbers"].add(invoice_number) description = row.get("description") if description and description not in bucket["_seen_descriptions"]: bucket["descriptions"].append(description) bucket["_seen_descriptions"].add(description) fallback_month_rows = sorted( [ {key: value for key, value in month.items() if not key.startswith("_")} for month in global_fallback_month_map.values() ], key=lambda item: item["month_start"], ) result = { "issue_id": issue_id, "customer_id": customer_id, "customer_name": customer.get("name") or issue.get("customer_name"), "economic_customer_number": customer.get("economic_customer_number"), "product_number": product_number, "product_name": issue.get("product_name"), "reference_period_start": reference_period_start.isoformat(), "months": [*fallback_month_rows, *months_payload], } return result except HTTPException: raise except Exception as exc: logger.error("❌ Get issue invoice history failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.patch("/issues/{issue_id}/status") async def update_issue_status( issue_id: int, payload: IssueStatusUpdate, current_user: dict = Depends(require_permission("invoice_error_finder.update_status")), ): """Update issue status, notes and/or assignee.""" try: if payload.status not in ALLOWED_ISSUE_STATUSES: raise HTTPException(status_code=400, detail="Invalid status") resolved_at = None if payload.status in {"invoiced", "ignored", "resolved"}: resolved_at = "CURRENT_TIMESTAMP" extra_fields = [] extra_values: List[Any] = [] if payload.assigned_user_id is not None: extra_fields.append("assigned_user_id = %s") extra_values.append(payload.assigned_user_id) if payload.notes is not None: extra_fields.append("notes = COALESCE(notes, '') || E'\\n' || %s") extra_values.append(payload.notes) resolved_sql = ( f"resolved_at = COALESCE(resolved_at, {resolved_at})" if resolved_at else "resolved_at = NULL" ) execute_query( f""" UPDATE invoice_error_finder_issues SET status = %s, updated_at = CURRENT_TIMESTAMP, {resolved_sql} {',' + ','.join(extra_fields) if extra_fields else ''} WHERE id = %s """, (payload.status, *extra_values, issue_id), ) return {"id": issue_id, "status": payload.status} except HTTPException: raise except Exception as exc: logger.error("❌ Update issue status failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.post("/issues/{issue_id}/create-sag") async def create_sag_for_issue( issue_id: int, payload: CreateSagRequest, request: Request, current_user: dict = Depends(require_permission("invoice_error_finder.create_sag")), ): """Create a new sag/case from an issue and link it.""" try: issue = execute_query_single( "SELECT * FROM invoice_error_finder_issues WHERE id = %s", (issue_id,), ) if not issue: raise HTTPException(status_code=404, detail="Issue not found") customer_id = issue.get("customer_id") if not customer_id: raise HTTPException(status_code=400, detail="Issue has no mapped customer") user_id = _get_user_id(request) or 1 sag_result = execute_query( """ INSERT INTO sag_sager (titel, beskrivelse, status, customer_id, created_by_user_id) VALUES (%s, %s, %s, %s, %s) RETURNING id """, ( payload.titel, payload.beskrivelse, payload.status or "åben", customer_id, user_id, ), ) if not sag_result: raise HTTPException(status_code=500, detail="Failed to create sag") sag_id = sag_result[0]["id"] execute_query( """ UPDATE invoice_error_finder_issues SET sag_id = %s, status = 'investigating', updated_at = CURRENT_TIMESTAMP WHERE id = %s """, (sag_id, issue_id), ) return {"issue_id": issue_id, "sag_id": sag_id} except HTTPException: raise except Exception as exc: logger.error("❌ Create sag for issue failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.post("/issues/{issue_id}/link-sag") async def link_sag_to_issue( issue_id: int, payload: LinkSagRequest, current_user: dict = Depends(require_permission("invoice_error_finder.create_sag")), ): """Link an existing sag to an issue.""" try: issue = execute_query_single( "SELECT id FROM invoice_error_finder_issues WHERE id = %s", (issue_id,), ) if not issue: raise HTTPException(status_code=404, detail="Issue not found") sag = execute_query_single( "SELECT id FROM sag_sager WHERE id = %s AND deleted_at IS NULL", (payload.sag_id,), ) if not sag: raise HTTPException(status_code=404, detail="Sag not found") execute_query( """ UPDATE invoice_error_finder_issues SET sag_id = %s, status = 'investigating', updated_at = CURRENT_TIMESTAMP WHERE id = %s """, (payload.sag_id, issue_id), ) return {"issue_id": issue_id, "sag_id": payload.sag_id} except HTTPException: raise except Exception as exc: logger.error("❌ Link sag to issue failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.post("/issues/{issue_id}/create-ordre-draft") async def create_ordre_draft_for_issue( issue_id: int, payload: CreateOrdreDraftRequest, request: Request, current_user: dict = Depends(require_permission("invoice_error_finder.create_ordre_draft")), ): """ Create a local ordre_draft from an issue. IMPORTANT: This only creates a local draft; it does NOT send anything to e-conomic. """ try: issue = execute_query_single( "SELECT * FROM invoice_error_finder_issues WHERE id = %s", (issue_id,), ) if not issue: raise HTTPException(status_code=404, detail="Issue not found") customer_id = issue.get("customer_id") if not customer_id: raise HTTPException(status_code=400, detail="Issue has no mapped customer") customer = execute_query_single( "SELECT id, name FROM customers WHERE id = %s", (customer_id,), ) if not customer: raise HTTPException(status_code=404, detail="Customer not found") user_id = _get_user_id(request) or 1 description = payload.description or issue.get("product_name") or issue.get("product_number") or "Fakturakorrektion" quantity = issue.get("expected_quantity") or 1 unit_price = issue.get("expected_price") or issue.get("amount_impact") or 0 line = { "description": description, "quantity": float(quantity) if quantity else 1, "unit_price": float(unit_price) if unit_price else 0, "line_total": float(quantity or 1) * float(unit_price or 0), } draft_result = execute_query( """ INSERT INTO ordre_drafts (title, customer_id, lines_json, notes, created_by_user_id) VALUES (%s, %s, %s::jsonb, %s, %s) RETURNING id """, ( f"Fakturakorrektion: {customer['name']}", customer_id, json.dumps([line], ensure_ascii=False), f"Oprettet fra faktura-fejl-finder issue #{issue_id}", user_id, ), ) if not draft_result: raise HTTPException(status_code=500, detail="Failed to create ordre draft") draft_id = draft_result[0]["id"] execute_query( """ UPDATE invoice_error_finder_issues SET status = 'ready_to_invoice', updated_at = CURRENT_TIMESTAMP WHERE id = %s """, (issue_id,), ) return {"issue_id": issue_id, "draft_id": draft_id} except HTTPException: raise except Exception as exc: logger.error("❌ Create ordre draft for issue failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.post("/issues/{issue_id}/ignore") async def ignore_issue( issue_id: int, ignored_until: Optional[str] = Query(None), current_user: dict = Depends(require_permission("invoice_error_finder.ignore")), ): """Ignore an issue (optionally until a date).""" try: issue = execute_query_single( "SELECT id FROM invoice_error_finder_issues WHERE id = %s", (issue_id,), ) if not issue: raise HTTPException(status_code=404, detail="Issue not found") until_date = None if ignored_until: until_date = date.fromisoformat(ignored_until) execute_query( """ UPDATE invoice_error_finder_issues SET status = 'ignored', ignored_until = %s, resolved_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP WHERE id = %s """, (until_date, issue_id), ) return {"id": issue_id, "status": "ignored"} except HTTPException: raise except Exception as exc: logger.error("❌ Ignore issue failed: %s", exc, exc_info=True) raise HTTPException(status_code=500, detail=str(exc)) @router.get("/config") async def get_config( current_user: dict = Depends(require_permission("invoice_error_finder.view")), ): """Return module configuration for the frontend.""" return { "issue_statuses": ISSUE_STATUS_LABELS, "issue_types": ISSUE_TYPE_LABELS, }