#!/usr/bin/env python3
"""
Durchsucht ALLE Sheets nach den 5 Krediten mit den korrekten Werten.
"""

import openpyxl
from datetime import datetime

# Excel-Datei laden
datei = "Kopie von Kostenrechnung der Nächsten jahre (3).xlsx"
wb = openpyxl.load_workbook(datei, data_only=True)

# Target-Werte vom User
target_kredite = {
    "DSL Bank": 64656.88,
    "PSD Nord": 50384.50,
    "Zingelstr. 14 DSL": 24382.38,
    "Zingelstr. 14 Sparkasse": 8140.11,
    "PVCreditplus": 1666.53
}

print("SUCHE NACH KREDITEN MIT FOLGENDEN RESTSCHULDEN:")
for name, betrag in target_kredite.items():
    print(f"  {name}: {betrag:,.2f} EUR")

print("\n" + "="*80)

for sheet_name in wb.sheetnames:
    print(f"\n=== SHEET: {sheet_name} ===")
    ws = wb[sheet_name]
    
    # Suche nach den Zielwerten
    for row in range(1, min(ws.max_row + 1, 100)):
        for col in range(1, min(ws.max_column + 1, 30)):
            val = ws.cell(row=row, column=col).value
            if val and isinstance(val, (int, float)):
                for kredit_name, target in target_kredite.items():
                    # Toleranz von 100 EUR
                    if abs(val - target) < 100:
                        print(f"  ZEILE {row}, Spalte {col}: {val:,.2f} = {kredit_name}!")
                        # Kontext zeigen
                        context = []
                        for c in range(max(1, col-3), min(col+3, ws.max_column + 1)):
                            v = ws.cell(row=row, column=c).value
                            context.append(str(v)[:15] if v else "-")
                        print(f"    Kontext: {context}")
                        # Header suchen
                        headers = []
                        for c in range(max(1, col-3), min(col+3, ws.max_column + 1)):
                            h = ws.cell(row=1, column=c).value
                            headers.append(str(h)[:15] if h else "-")
                        print(f"    Header: {headers}")

print("\n" + "="*80)
print("SUCHE NACH ZINSSÄTZEN (Text 'Zins' oder '%'):")
for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    for row in range(1, min(20, ws.max_row + 1)):
        for col in range(1, min(30, ws.max_column + 1)):
            val = ws.cell(row=row, column=col).value
            if val:
                if isinstance(val, str) and ("zins" in val.lower() or "%" in val):
                    print(f"  [{sheet_name}] Zeile {row}, Spalte {col}: {val}")
                elif isinstance(val, (int, float)) and 0.001 < val < 0.2:
                    # Möglicher Zinssatz als Dezimal
                    label = ws.cell(row=row-1, column=col).value if row > 1 else None
                    print(f"  [{sheet_name}] Zeile {row}, Spalte {col}: {val} ({val*100:.2f}%) - {label}")