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

spreadsheet_id = '1uVd-xZFF4TEQGqtvw9z6W8fffeaifPCoLsek83GmEoQ'
account = 'jonny@jonnyshannon.com'

# Sheets to update
sheets = ['USA', 'South Africa', 'Australia', 'New Zealand', 'Canada', 'UK', 'Other Countries']

# New column order (0-indexed positions)
# Current: Company Name, Website, Country, State/Region, Primary Fruit, CA Storage Confirmed, 
#          Employee Count, Est. Revenue, Contact Name, Contact Title, Contact Email, Contact Phone, Notes, Source, Qualified
# 
# New order: Company Name, Website, Country, State/Region, Primary Fruit, CA Storage Confirmed, 
#            Qualified, Employee Count, Revenue, Notes, Contact Name, Contact Title, Contact Email, Contact Phone, Source

column_mapping = {
    0: 0,   # Company Name
    1: 1,   # Website
    2: 2,   # Country
    3: 3,   # State/Region
    4: 4,   # Primary Fruit
    5: 5,   # CA Storage Confirmed
    14: 6,  # Qualified (was 14, now 6)
    6: 7,   # Employee Count (was 6, now 7)
    7: 8,   # Revenue (was 7, now 8)
    12: 9,  # Notes (was 12, now 9)
    8: 10,  # Contact Name (was 8, now 10)
    9: 11,  # Contact Title (was 9, now 11)
    10: 12, # Contact Email (was 10, now 12)
    11: 13, # Contact Phone (was 11, now 13)
    13: 14, # Source (was 13, now 14)
}

for sheet_name in sheets:
    print(f"\nProcessing {sheet_name}...")
    
    # Download sheet
    result = subprocess.run([
        'gog', 'sheets', 'get',
        spreadsheet_id,
        f'{sheet_name}!A:Z',
        '--account', account,
        '--json'
    ], capture_output=True, text=True)
    
    if result.returncode != 0:
        print(f"  Error downloading: {result.stderr}")
        continue
    
    data = json.loads(result.stdout)
    rows = data.get('values', [])
    
    if not rows:
        print(f"  No data in {sheet_name}")
        continue
    
    print(f"  Original rows: {len(rows)}")
    
    # Reorder columns for each row
    reordered = []
    for row in rows:
        # Pad row to ensure we have all columns
        while len(row) < 15:
            row.append('')
        
        # Build new row with reordered columns
        new_row = [''] * 15  # Initialize with empty strings
        for old_idx, new_idx in column_mapping.items():
            if old_idx < len(row):
                new_row[new_idx] = row[old_idx]
        
        reordered.append(new_row)
    
    print(f"  Reordered {len(reordered)} rows")
    
    # Clear and update sheet
    subprocess.run([
        'gog', 'sheets', 'clear',
        spreadsheet_id,
        f'{sheet_name}!A:Z',
        '--account', account
    ], capture_output=True)
    
    result = subprocess.run([
        'gog', 'sheets', 'update',
        spreadsheet_id,
        f'{sheet_name}!A1',
        '--values-json', json.dumps(reordered),
        '--input', 'USER_ENTERED',
        '--account', account
    ], capture_output=True, text=True)
    
    if result.returncode == 0:
        print(f"  ✅ Updated {sheet_name}")
    else:
        print(f"  ❌ Error: {result.stderr}")

print("\n✅ All sheets reordered!")