#!/usr/bin/env python3
import json, subprocess, sys

env = {'GOG_KEYRING_PASSWORD': '', 'PATH': '/usr/local/bin:/usr/bin:/bin', 'HOME': '/root'}
result = subprocess.run(
    ['gog', 'sheets', 'get', '1lPFmhuXnlTchsqE_V5Ntx7k46NFfHSGnldyeL6hH_jQ', 'A1:Z1000',
     '--account', 'alihajouoc@gmail.com', '--json'],
    capture_output=True, text=True, env=env,
    cwd='/root/.openclaw.pre-migration/workspace'
)
if result.returncode != 0:
    print(f'Error: {result.stderr}', file=sys.stderr)
    sys.exit(1)

data = json.loads(result.stdout)
rows = data.get('values', [])

# Read individual columns to handle merged cells
result_b = subprocess.run(
    ['gog', 'sheets', 'get', '1lPFmhuXnlTchsqE_V5Ntx7k46NFfHSGnldyeL6hH_jQ', 'B1:B1000',
     '--account', 'alihajouoc@gmail.com', '--json'],
    capture_output=True, text=True, env=env,
    cwd='/root/.openclaw.pre-migration/workspace'
)
result_d = subprocess.run(
    ['gog', 'sheets', 'get', '1lPFmhuXnlTchsqE_V5Ntx7k46NFfHSGnldyeL6hH_jQ', 'D1:D1000',
     '--account', 'alihajouoc@gmail.com', '--json'],
    capture_output=True, text=True, env=env,
    cwd='/root/.openclaw.pre-migration/workspace'
)

col_b = json.loads(result_b.stdout).get('values', [])
col_d = json.loads(result_d.stdout).get('values', [])

print(f'Header row B: {col_b[0] if col_b else "empty"}')
print(f'Header row D: {col_d[0] if col_d else "empty"}')
print(f'Total rows: {len(rows)}')
print(f'Col B rows: {len(col_b)}')
print(f'Col D rows: {len(col_d)}')
print()

# Map by actual row index
new_tasks = []  # status=1
in_progress = []  # status=2
done = []  # status=3

for i in range(1, min(len(col_d), 1000)):  # skip header row 0
    if i >= len(col_d):
        break
    d_val = col_d[i][0] if col_d[i] else ''
    status = str(d_val).strip()
    b_val = col_b[i][0] if (i < len(col_b) and col_b[i]) else ''
    if not status or status == 'Status':
        continue
    if status == '1':
        new_tasks.append({'row': i+1, 'id': col_d[i-1][0] if i > 0 and i-1 < len(col_d) and col_d[i-1] else '', 'name': b_val, 'status': status})
    elif status == '2':
        in_progress.append({'row': i+1, 'id': col_d[i-1][0] if i > 0 and i-1 < len(col_d) and col_d[i-1] else '', 'name': b_val, 'status': status})
    elif status == '3':
        done.append({'row': i+1, 'id': col_d[i-1][0] if i > 0 and i-1 < len(col_d) and col_d[i-1] else '', 'name': b_val, 'status': status})

print(f'New tasks (status=1): {len(new_tasks)}')
for t in new_tasks:
    print(f'  Row {t["row"]}: {t["id"]} - {t["name"]}')

print(f'In-progress tasks (status=2): {len(in_progress)}')
for t in in_progress:
    print(f'  Row {t["row"]}: {t["id"]} - {t["name"]}')

print(f'Done tasks (status=3): {len(done)}')
for t in done:
    print(f'  Row {t["row"]}: {t["id"]} - {t["name"]}')