#!/usr/bin/env python3
"""Build batch updates for SCPI sheet - fill N/A gaps from ideal-investisseur data"""
import json, subprocess, sys

SHEET_ID = "1dBGv3jIsFDLMJInANzyLvUAF0HYb5iRZ_RQljo-i7XM"

# Headers: D=Type capital, E=Secteur geo, F=Secteur activite, G=Label ISR, H=Souscription min
# I=Date creation, J=Capitalisation, K=Taux endettement, L=Nb parts, M=Nb parts attente
# N=RAN, O=PGE, P=Fiscalite, Q=Delai jouissance, R=Freq distribution, S=TOF, T=PGA
# U=TRI 10ans, V=Val souscription, W=Val reconstitution, X=Comm souscription, Y=Comm gestion

# Load scraped data
scraped = json.load(open('/tmp/scpi_scraped.json'))

# Load current sheet
import os
os.chdir('/home/shingokuga/.openclaw/workspace')
result = subprocess.run(
    ['gws', 'sheets', 'spreadsheets', 'values', 'get',
     '--params', json.dumps({
         "spreadsheetId": SHEET_ID,
         "range": "Listing sites!A1:Y64"
     })],
    capture_output=True, text=True
)
sheet_data = json.loads(result.stdout)
rows = sheet_data.get('values', [])
headers = rows[0] if rows else []

# Build list of updates needed
updates = []

for i in range(1, len(rows)):  # Skip header
    row = rows[i]
    scpi_name = row[1] if len(row) > 1 else ''
    if scpi_name not in scraped:
        continue
    
    data = scraped[scpi_name]
    row_num = i + 1
    
    # Map scraped data to columns
    field_map = {
        'J': ('capitalisation', lambda d: d.get('capitalisation', '').replace(' M€', 'M€').replace(' ', '') if d.get('capitalisation') else ''),
        'K': ('endettement', lambda d: d.get('endettement', '') if d.get('endettement') else ''),
        'S': ('tof', lambda d: d.get('tof', '') if d.get('tof') else ''),
        'T': ('pga', lambda d: d.get('pga', '') if d.get('pga') else ''),
        'U': ('tri_10', lambda d: d.get('tri_10', '') if d.get('tri_10') else ''),
        'V': ('prix_souscription', lambda d: d.get('prix_souscription', '') if d.get('prix_souscription') else ''),
        'W': ('val_reconstitution', lambda d: d.get('val_reconstitution', '') if d.get('val_reconstitution') else ''),
    }
    
    for col_letter, (field, extractor) in field_map.items():
        col_idx = ord(col_letter) - ord('A')
        current_val = row[col_idx] if col_idx < len(row) else ''
        
        # Check if current value is N/A, empty, or Non trouvé
        if current_val.strip() in ['N/A', 'Non trouvé', '', 'N/A (lancement)', 'N/A (< 10 ans)', 'N/A (1er exercice)']:
            new_val = extractor(data).strip()
            if new_val and new_val not in ['-', '- %', '- €', '']:
                # Clean up
                new_val = new_val.replace('   ', ' ').strip()
                if new_val in ['- %', '- €']:
                    continue
                print(f"Row {row_num} ({scpi_name}): {col_letter} '{current_val}' → '{new_val}'")
                updates.append((row_num, col_letter, new_val))

print(f"\nTotal updates: {len(updates)}")

# Save for batch processing
with open('/tmp/scpi_updates.json', 'w') as f:
    json.dump(updates, f)