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

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

# Load desktop CSV (has all countries)
print("Loading desktop CSV (all countries)...")
desktop = []
with open('/Users/max/Desktop/PHT-Master-List.csv', 'r', encoding='utf-8') as f:
    reader = csv.reader(f)
    desktop = list(reader)

header = desktop[0]
print(f"Loaded {len(desktop)-1} companies from desktop")

def normalize_country(country):
    """Normalize country codes"""
    if not country:
        return ''
    country = country.strip().upper()
    mapping = {
        'AUSTRALIA': 'AU',
        'NEW ZEALAND': 'NZ',
        'UNITED KINGDOM': 'UK',
        'GREAT BRITAIN': 'UK',
        'CANADA': 'CA',
        'SOUTH AFRICA': 'ZA',
        'UNITED STATES': 'USA',
        'US': 'USA',
    }
    return mapping.get(country, country)

def normalize_row(row):
    """Normalize a row"""
    normalized = list(row)
    # Normalize website
    if len(normalized) > 1 and normalized[1]:
        url = normalized[1].lower().strip()
        url = url.replace('https://', '').replace('http://', '').replace('www.', '')
        normalized[1] = url.rstrip('/')
    # Normalize country
    if len(normalized) > 2 and normalized[2]:
        normalized[2] = normalize_country(normalized[2])
    return normalized

# Normalize all rows
normalized_data = [header]
for row in desktop[1:]:
    normalized_data.append(normalize_row(row))

# Define country mappings to sheets
country_sheets = {
    'USA': ['USA'],
    'South Africa': ['ZA'],
    'Australia': ['AU'],
    'New Zealand': ['NZ'],
    'Canada': ['CA'],
    'UK': ['UK', 'GB']
}

# Filter and populate each country sheet
for sheet_name, country_codes in country_sheets.items():
    filtered = [header]
    for row in normalized_data[1:]:
        if len(row) > 2:
            country = (row[2] or '').strip().upper()
            if country in country_codes:
                filtered.append(row)
    
    print(f"\n{sheet_name}: {len(filtered)-1} companies")
    
    if len(filtered) > 1:
        # Clear sheet
        print(f"  Clearing {sheet_name}...")
        subprocess.run([
            'gog', 'sheets', 'clear',
            spreadsheet_id,
            f'{sheet_name}!A:Z',
            '--account', account
        ], capture_output=True)
        
        # Update sheet
        print(f"  Uploading...")
        result = subprocess.run([
            'gog', 'sheets', 'update',
            spreadsheet_id,
            f'{sheet_name}!A1',
            '--values-json', json.dumps(filtered),
            '--input', 'USER_ENTERED',
            '--account', account
        ], capture_output=True, text=True)
        
        if result.returncode == 0:
            print(f"  ✅ Done")
        else:
            print(f"  ❌ Error: {result.stderr}")
    else:
        print(f"  ⚠️  No companies")

# Handle "Other Countries" - everything NOT in the defined countries
print(f"\n--- Other Countries ---")
excluded_codes = []
for codes in country_sheets.values():
    excluded_codes.extend(codes)

other = [header]
for row in normalized_data[1:]:
    if len(row) > 2:
        country = (row[2] or '').strip().upper()
        if country and country not in excluded_codes:
            other.append(row)

print(f"Other Countries: {len(other)-1} companies")

if len(other) > 1:
    print("  Clearing...")
    subprocess.run([
        'gog', 'sheets', 'clear',
        spreadsheet_id,
        'Other Countries!A:Z',
        '--account', account
    ], capture_output=True)
    
    print("  Uploading...")
    result = subprocess.run([
        'gog', 'sheets', 'update',
        spreadsheet_id,
        'Other Countries!A1',
        '--values-json', json.dumps(other),
        '--input', 'USER_ENTERED',
        '--account', account
    ], capture_output=True, text=True)
    
    if result.returncode == 0:
        print("  ✅ Done")
    else:
        print(f"  ❌ Error: {result.stderr}")

print(f"\n✅ All country sheets populated!")
print(f"🔗 https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit")