c-stone-invoice-check/backend/routers/emails.py

"""
C-Stone Invoice Check — Email Router
IMAP Email-Verarbeitung und Filterung
"""

import imaplib
import email
import os
import tempfile
from datetime import datetime
from typing import List, Optional, Dict, Any
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from email.header import decode_header

from backend.database import get_db
from backend.models import EmailLog, Invoice

router = APIRouter()

# IMAP Konfiguration
IMAP_HOST = os.getenv("IMAP_HOST", "imap.strato.de")
IMAP_PORT = int(os.getenv("IMAP_PORT", 993))
IMAP_USER = os.getenv("IMAP_USER", "")
IMAP_PASSWORD = os.getenv("IMAP_PASSWORD", "")

# Gmail Konfiguration
GMAIL_IMAP_HOST = os.getenv("GMAIL_IMAP_HOST", "imap.gmail.com")
GMAIL_IMAP_PORT = int(os.getenv("GMAIL_IMAP_PORT", 993))
GMAIL_IMAP_USER = os.getenv("GMAIL_IMAP_USER", "")
GMAIL_IMAP_PASSWORD = os.getenv("GMAIL_IMAP_PASSWORD", "")

# Filter-Kriterien
INVOICE_KEYWORDS = ["rechnung", "invoice", "receipt", "bill", "factuur"]

# Ablagepfad (Evo-X2) - optional
INVOICE_STORAGE_PATH = os.getenv("INVOICE_STORAGE_PATH", "/tmp/invoice-storage")


def connect_imap(host: str, port: int, user: str, password: str) -> imaplib.IMAP4_SSL:
    """Verbindet mit IMAP-Server"""
    mail = imaplib.IMAP4_SSL(host, port)
    mail.login(user, password)
    return mail


def search_invoices(mail: imaplib.IMAP4_SSL, folder: str = "INBOX") -> List[str]:
    """Sucht nach Emails mit Rechnungs-Stichworten"""
    mail.select(folder)
    result, data = mail.search(None, 'ALL')
    if result != "OK":
        return []

    email_ids = data[0].split()
    matching_ids = []

    for email_id in email_ids:
        result, msg_data = mail.fetch(email_id, '(RFC822)')
        if result != "OK":
            continue

        msg = email.message_from_bytes(msg_data[0][1])
        subject = msg.get("Subject", "")
        from_addr = msg.get("From", "")

        # Subject decodieren
        decoded_subject, encoding = decode_header(subject)[0]
        if isinstance(decoded_subject, bytes):
            subject = decoded_subject.decode(encoding or "utf-8")

        # Prüfen auf Keywords
        subject_lower = subject.lower()
        if any(kw in subject_lower for kw in INVOICE_KEYWORDS):
            matching_ids.append(email_id.decode())

    return matching_ids


def extract_attachments(msg: email.message.Message) -> List[Dict[str, Any]]:
    """Extrahiert Anhänge aus Email"""
    attachments = []
    for part in msg.walk():
        if part.get_content_maintype() == 'multipart':
            continue
        if part.get('Content-Disposition') is None:
            continue

        filename = part.get_filename()
        if filename:
            decoded_filename, encoding = decode_header(filename)[0]
            if isinstance(decoded_filename, bytes):
                filename = decoded_filename.decode(encoding or "utf-8")

            content_type = part.get_content_type()
            payload = part.get_payload(decode=True)

            attachments.append({
                "filename": filename,
                "content_type": content_type,
                "data": payload,
                "size": len(payload) if payload else 0
            })

    return attachments


def parse_email_date(date_str: str) -> Optional[datetime]:
    """Parses various email date formats"""
    if not date_str:
        return None

    from email.utils import parsedate_to_datetime

    try:
        # Versuche email.utils parsedate_to_datetime
        return parsedate_to_datetime(date_str)
    except:
        pass

    # Fallback: Versuche verschiedene Formate
    formats = [
        "%a, %d %b %Y %H:%M:%S %z",
        "%a, %d %b %Y %H:%M:%S %z (%Z)",
        "%d %b %Y %H:%M:%S %z",
        "%Y-%m-%d %H:%M:%S",
    ]

    for fmt in formats:
        try:
            return datetime.strptime(date_str.strip(), fmt)
        except:
            continue

    return None


@router.get("/search")
async def search_emails(db: Session = Depends(get_db)):
    """Sucht nach Rechnungs-Emails bei Strato"""
    results = {
        "processed": 0,
        "invoices_found": 0,
        "errors": []
    }

    try:
        mail = connect_imap(IMAP_HOST, IMAP_PORT, IMAP_USER, IMAP_PASSWORD)

        # Alle Ordner durchsuchen
        for folder in ["INBOX", "Sent", "Gesendet"]:
            try:
                email_ids = search_invoices(mail, folder)
                results[f"{folder}_count"] = len(email_ids)

                for email_id in email_ids:
                    # Verarbeitung auslagern
                    pass

            except Exception as e:
                results["errors"].append(f"Folder {folder}: {str(e)}")

        mail.logout()
        results["processed"] = len(email_ids) if 'email_ids' in dir() else 0

    except Exception as e:
        results["errors"].append(f"IMAP Connection failed: {str(e)}")

    return results


@router.get("/process/{email_id}")
async def process_email(email_id: str, db: Session = Depends(get_db)):
    """Verarbeitet einzelne Email"""
    # TODO: Implementierung
    pass


def extract_and_process_attachments(msg: email.message.Message, email_id: str, db: Session) -> List[Dict[str, Any]]:
    """Extrahiert und verarbeitet PDF-Anhänge aus Email"""
    processed = []

    for part in msg.walk():
        if part.get_content_maintype() == 'multipart':
            continue
        if part.get('Content-Disposition') is None:
            continue

        filename = part.get_filename()
        if not filename:
            continue

        decoded_filename, encoding = decode_header(filename)[0]
        if isinstance(decoded_filename, bytes):
            filename = decoded_filename.decode(encoding or "utf-8")

        content_type = part.get_content_type()

        # Nur PDFs verarbeiten
        if content_type != 'application/pdf' and not filename.lower().endswith('.pdf'):
            continue

        payload = part.get_payload(decode=True)
        if not payload:
            continue

        # Temporäre Datei erstellen
        with tempfile.NamedTemporaryFile(suffix='.pdf', delete=False) as tmp:
            tmp.write(payload)
            tmp_path = tmp.name

        try:
            # PDF verarbeiten
            from backend.routers.invoices import extract_pdf_data, get_storage_path

            pdf_data = extract_pdf_data(tmp_path)

            if "error" not in pdf_data and pdf_data.get("amount"):
                # Rechnung speichern
                tax_type = pdf_data.get("tax_info", {}).get("tax_type", "unbekannt")
                tax_rate = pdf_data.get("tax_info", {}).get("tax_rate", 0)

                # Speicherpfad erstellen
                storage_year = 2026  # Default, könnte aus Rechnungsdatum extrahiert werden
                storage_path = get_storage_path(storage_year, tax_type)

                # PDF in Zielverzeichnis kopieren (optional)
                if INVOICE_STORAGE_PATH:
                    os.makedirs(storage_path, exist_ok=True)

                    # Dateiname generieren
                    invoice_number = pdf_data.get("invoice_number") or "unbekannt"
                    file_name = f"Rechnung_{invoice_number}_{email_id}.pdf"
                    dest_path = os.path.join(storage_path, file_name)

                    import shutil
                    shutil.copy(tmp_path, dest_path)

                    # In DB speichern
                    invoice = Invoice(
                        email_id=email_id,
                        file_path=dest_path,
                        file_name=file_name,
                        storage_year=storage_year,
                        storage_path=storage_path.replace(INVOICE_STORAGE_PATH, ""),
                        invoice_number=invoice_number,
                        invoice_amount=pdf_data.get("amount"),
                        issuer_name=pdf_data.get("issuer"),
                        invoice_tax_type=tax_type,
                        invoice_tax_rate=tax_rate,
                        processed=True,
                        confidence_score=pdf_data.get("tax_info", {}).get("confidence", 0)
                    )
                    db.add(invoice)
                    db.commit()

                    processed.append({
                        "filename": filename,
                        "invoice_number": invoice_number,
                        "amount": pdf_data.get("amount"),
                        "tax_type": tax_type,
                        "saved_to": dest_path
                    })

        finally:
            # Temporäre Datei löschen
            try:
                os.unlink(tmp_path)
            except:
                pass

    return processed


@router.get("/scan-all")
async def scan_all_emails(db: Session = Depends(get_db)):
    """Scant alle Emails, extrahiert PDFs und verarbeitet Rechnungen"""
    results = {
        "start_time": datetime.utcnow().isoformat(),
        "processed": 0,
        "invoices_found": 0,
        "pdfs_processed": 0,
        "errors": []
    }

    # Strato Mail
    try:
        mail = connect_imap(IMAP_HOST, IMAP_PORT, IMAP_USER, IMAP_PASSWORD)
        mail.select("INBOX")

        result, data = mail.search(None, 'ALL')
        if result == "OK":
            email_ids = data[0].split()

            for email_id in email_ids:
                email_id_str = email_id.decode()

                # Prüfen ob bereits verarbeitet
                log_entry = db.query(EmailLog).filter(EmailLog.email_id == email_id_str).first()
                if log_entry and log_entry.processed:
                    continue

                result, msg_data = mail.fetch(email_id, '(RFC822)')
                if result != "OK":
                    continue

                msg = email.message_from_bytes(msg_data[0][1])
                subject = msg.get("Subject", "")
                from_addr = msg.get("From", "")
                date_str = msg.get("Date", "")

                # Datum parsen
                email_date = parse_email_date(date_str)

                # Subject decodieren
                decoded_subject, encoding = decode_header(subject)[0]
                if isinstance(decoded_subject, bytes):
                    subject = decoded_subject.decode(encoding or "utf-8")

                # Prüfen auf Keywords
                subject_lower = subject.lower()
                matched_keywords = [kw for kw in INVOICE_KEYWORDS if kw in subject_lower]

                if matched_keywords:
                    # Email als Rechnung markieren (auch wenn keine PDF dabei ist)
                    log_entry = EmailLog(
                        email_id=email_id_str,
                        email_subject=subject,
                        email_from=from_addr,
                        email_date=email_date,
                        email_folder="INBOX",
                        matched_keywords=matched_keywords,
                        has_attachment=True,
                        processed=True
                    )
                    db.add(log_entry)
                    results["invoices_found"] += 1

                # PDFs extrahieren und verarbeiten
                pdfs = extract_and_process_attachments(msg, email_id_str, db)
                results["pdfs_processed"] += len(pdfs)

                # Log aktualisieren mit Ergebnis
                if pdfs and log_entry:
                    log_entry.processing_result = f"PDFs processed: {len(pdfs)}"
                    log_entry.processed = True
                    db.commit()

                results["processed"] += 1

            db.commit()

        mail.logout()

    except Exception as e:
        results["errors"].append(f"Strato scan failed: {str(e)}")
        import traceback
        results["errors"].append(traceback.format_exc())

    # Gmail Mail (optional)
    if GMAIL_IMAP_USER and GMAIL_IMAP_PASSWORD:
        try:
            mail = connect_imap(GMAIL_IMAP_HOST, GMAIL_IMAP_PORT, GMAIL_IMAP_USER, GMAIL_IMAP_PASSWORD)
            mail.select("INBOX")

            result, data = mail.search(None, 'ALL')
            if result == "OK":
                email_ids = data[0].split()

                for email_id in email_ids:
                    email_id_str = email_id.decode()

                    log_entry = db.query(EmailLog).filter(EmailLog.email_id == email_id_str).first()
                    if log_entry and log_entry.processed:
                        continue

                    result, msg_data = mail.fetch(email_id, '(RFC822)')
                    if result != "OK":
                        continue

                    msg = email.message_from_bytes(msg_data[0][1])
                    subject = msg.get("Subject", "")
                    from_addr = msg.get("From", "")
                    date_str = msg.get("Date", "")

                    email_date = parse_email_date(date_str)

                    decoded_subject, encoding = decode_header(subject)[0]
                    if isinstance(decoded_subject, bytes):
                        subject = decoded_subject.decode(encoding or "utf-8")

                    subject_lower = subject.lower()
                    matched_keywords = [kw for kw in INVOICE_KEYWORDS if kw in subject_lower]

                    if matched_keywords:
                        log_entry = EmailLog(
                            email_id=email_id_str,
                            email_subject=subject,
                            email_from=from_addr,
                            email_date=email_date,
                            email_folder="INBOX",
                            matched_keywords=matched_keywords,
                            has_attachment=True,
                            processed=True
                        )
                        db.add(log_entry)
                        results["invoices_found"] += 1

                    pdfs = extract_and_process_attachments(msg, email_id_str, db)
                    results["pdfs_processed"] += len(pdfs)

                    if pdfs:
                        log_entry.processing_result = f"PDFs processed: {len(pdfs)}"
                        log_entry.processed = True
                        db.commit()

                    results["processed"] += 1

                db.commit()

            mail.logout()

        except Exception as e:
            results["errors"].append(f"Gmail scan failed: {str(e)}")
            import traceback
            results["errors"].append(traceback.format_exc())

    results["end_time"] = datetime.utcnow().isoformat()
    return results