#!/usr/bin/env python3
"""Verify stavaza_template_v1.1.xlsx"""
import openpyxl
from openpyxl.utils import get_column_letter

path="/root/projects/jg/2026-stavaza-portefeuille-template/deliverables/stavaza_template_v1.1.xlsx"
wb=openpyxl.load_workbook(path)

print("=" * 60)
print("VERIFICATIE: stavaza_template_v1.1.xlsx")
print("=" * 60)

# 1. Sheets
print(f"\n📋 Sheets ({len(wb.sheetnames)}): {wb.sheetnames}")
for name in wb.sheetnames:
    ws = wb[name]
    print(f"   • {name}: {ws.max_row} rijen × {ws.max_column} kolommen")

# 2. Named Ranges
print(f"\n🏷️  Named Ranges ({len(wb.defined_names)}):")
for dn in wb.defined_names:
    ref = wb.defined_names[dn]
    print(f"   • {dn} → {ref.attr_text}")

# 3. Sheet 1: Stavaza Update - headers
ws1 = wb["Stavaza Update"]
print(f"\n📊 Sheet 1 'Stavaza Update':")
print(f"   Freeze panes: {ws1.freeze_panes}")
print(f"   Tab color: {ws1.sheet_properties.tabColor}")
headers_row8 = []
for col in range(1, 13):
    v = ws1.cell(row=8, column=col).value
    headers_row8.append(v)
print(f"   Headers rij 8: {headers_row8}")
print(f"   Title A1: {ws1['A1'].value}")
print(f"   Subtitle A2: {ws1['A2'].value}")
print(f"   Portefeuille label A3: {ws1['A3'].value}")
print(f"   Instruction A6 (first 80 chars): {str(ws1['A6'].value)[:80]}...")
print(f"   Print orientation: {ws1.page_setup.orientation}")
print(f"   Fit to page: {ws1.sheet_properties.pageSetUpPr.fitToPage}")
print(f"   Print title rows: {ws1.print_title_rows}")

# 4. Data Validations
print(f"\n✅ Data Validations on 'Stavaza Update' ({len(ws1.data_validations.dataValidation)}):")
for dv in ws1.data_validations.dataValidation:
    ranges = str(dv.sqref)
    f1 = dv.formula1
    print(f"   • Range={ranges}, Formula={f1}, Type={dv.type}")

# 5. Conditional Formatting
print(f"\n🎨 Conditional Formatting on 'Stavaza Update':")
for cf_range in ws1.conditional_formatting:
    rules = ws1.conditional_formatting[cf_range]
    range_str = str(cf_range.sqref)
    print(f"   • Range {range_str}: {len(rules)} rule(s)")
    for rule in rules:
        rtype = rule.type
        formula = rule.formula if hasattr(rule, 'formula') else "N/A"
        print(f"     - Type={rtype}, Formula={formula}")

# 6. Sheet 2: Samenvatting
ws2 = wb["Samenvatting"]
print(f"\n📈 Sheet 2 'Samenvatting':")
print(f"   Title B2: {ws2['B2'].value}")
print(f"   Portefeuille formula C4: {ws2['C4'].value}")
# Count formulas
formula_count = 0
for row in ws2.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str) and cell.value.startswith("="):
            formula_count += 1
print(f"   Totaal formula's: {formula_count}")

# 7. Sheet 3: Lijsten
ws3 = wb["Lijsten"]
print(f"\n📝 Sheet 3 'Lijsten':")
print(f"   Title A1: {ws3['A1'].value}")
# Check preset values
for col_letter, col_name in [("A","Portefeuille"),("B","Status"),("C","Prioriteit"),("D","Categorie"),("F","Houder")]:
    vals = []
    for r in range(4, 12):
        v = ws3[f"{col_letter}{r}"].value
        if v: vals.append(v)
    print(f"   {col_name} waarden: {vals}")

# 8. Sheet 4: Toelichting
ws4 = wb["Toelichting"]
print(f"\n📖 Sheet 4 'Toelichting':")
print(f"   Title B2: {ws4['B2'].value}")
# Count sections
sections = 0
for row in ws4.iter_rows(min_col=2, max_col=2):
    for cell in row:
        if cell.value and cell.fill.start_color and cell.value == cell.value.upper():
            sections += 1
print(f"   Aantal sectie headers: {sections}")

# 9. File size
import os
size = os.path.getsize(path)
print(f"\n💾 File size: {size:,} bytes ({size/1024:.1f} KB)")

print("\n" + "=" * 60)
print("✅ VERIFICATIE VOLTOOID — Alle componenten aanwezig!")
print("=" * 60)