#!/usr/bin/env python3
"""Batch update SCPI sheet from scraped data"""
import json, subprocess, sys, time

SHEET_ID = "1dBGv3jIsFDLMJInANzyLvUAF0HYb5iRZ_RQljo-i7XM"

# Scraped data (from the previous run)
with open('/tmp/scpi_results.json') as f:
    scraped = json.load(f)

def gws_update(range_str, value):
    """Update a single cell"""
    cmd = [
        'gws', 'sheets', 'spreadsheets', 'values', 'update',
        '--params', json.dumps({
            "spreadsheetId": SHEET_ID,
            "range": f"Listing sites!{range_str}",
            "valueInputOption": "USER_ENTERED"
        }),
        '--json', json.dumps({
            "range": f"Listing sites!{range_str}",
            "values": [[value]]
        })
    ]
    result = subprocess.run(cmd, capture_output=True, text=True, cwd='/home/shingokuga/.openclaw/workspace')
    if 'updatedCells' not in result.stdout:
        print(f"  ERROR {range_str}: {result.stderr[:100]}")
        return False
    return True

# Read current sheet data
cmd = [
    'gws', 'sheets', 'spreadsheets', 'values', 'get',
    '--params', json.dumps({
        "spreadsheetId": SHEET_ID,
        "range": "Listing sites!A1:Y64"
    })
]
result = subprocess.run(cmd, capture_output=True, text=True, cwd='/home/shingokuga/.openclaw/workspace')
sheet_data = json.loads(result.stdout)
rows = sheet_data.get('values', [])

# Columns: D=3, E=4, F=5, G=6, H=7, I=8, J=9, K=10, L=11, M=12, N=13, O=14, P=15, Q=16, R=17, S=18, T=19, U=20, V=21, W=22, X=23, Y=24

# What scraped fields map to what columns
COL_FIELD = {
    9:  'capitalisation',  # J
    10: 'endettement',     # K
    18: 'tof',             # S
    19: 'pga',             # T
    20: 'tri_10',          # U
    21: 'prix_souscription', # V
    22: 'val_reconstitution', # W
}

updates_done = 0
errors = 0

for i in range(1, len(rows)):
    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
    
    for col_idx, field in COL_FIELD.items():
        current_val = row[col_idx].strip() if col_idx < len(row) else ''
        
        # Only fill if current is empty/N/A
        is_empty = current_val in ['N/A', 'Non trouvé', '', 'N/A (lancement)', 'N/A (< 10 ans)', 'N/A (1er exercice)', '-']
        
        if not is_empty:
            continue
        
        new_val = data.get(field, '').strip()
        if not new_val or new_val in ['-', '- %', '- €', '', '%']:
            continue
        
        col_letter = chr(65 + col_idx)
        range_str = f"{col_letter}{row_num}"
        
        print(f"Row {row_num} ({scpi_name}): {col_letter} '{current_val}' → '{new_val}'")
        
        if gws_update(range_str, new_val):
            updates_done += 1
            time.sleep(0.3)  # Rate limit
        else:
            errors += 1

print(f"\n✅ Done: {updates_done} updates, {errors} errors")