#!/usr/bin/env python3
"""
Upload PHT Top 1,000 to Google Sheets
Sheet ID: 14WPFM_wwPv7aq25_r3csudwoNBrYTT-Fz8NOb6by2i4
Account: jonny@jonnyshannon.com
"""

import csv
import json
import subprocess
import sys

# Configuration
SHEET_ID = '14WPFM_wwPv7aq25_r3csudwoNBrYTT-Fz8NOb6by2i4'
CSV_FILE = 'pht_top_1000_apple_pear_citrus.csv'
ACCOUNT = 'jonny@jonnyshannon.com'

print("=" * 80)
print("UPLOADING PHT TOP 1,000 TO GOOGLE SHEETS")
print("=" * 80)
print(f"Sheet ID: {SHEET_ID}")
print(f"CSV File: {CSV_FILE}")
print(f"Account: {ACCOUNT}")
print()

# Read CSV data
print("[1/3] Reading CSV file...")
rows = []
with open(CSV_FILE, 'r', encoding='utf-8') as f:
    reader = csv.reader(f)
    for row in reader:
        rows.append(row)

print(f"✓ Read {len(rows)} rows (including header)")

# Prepare for upload using gsheet CLI
print("\n[2/3] Checking gsheet CLI availability...")

try:
    result = subprocess.run(['which', 'gsheet'], capture_output=True, text=True)
    if result.returncode != 0:
        print("⚠️  gsheet CLI not found. Will use alternative upload method.")
        use_gsheet = False
    else:
        print(f"✓ gsheet CLI found at: {result.stdout.strip()}")
        use_gsheet = True
except Exception as e:
    print(f"⚠️  Error checking for gsheet: {e}")
    use_gsheet = False

# Upload data
print("\n[3/3] Uploading to Google Sheets...")

if use_gsheet:
    # Try using gsheet CLI
    try:
        # First, clear the sheet
        print("  Clearing existing data...")
        subprocess.run([
            'gsheet', 'clear', 
            '--account', ACCOUNT,
            SHEET_ID
        ], check=True)
        
        # Upload new data
        print("  Uploading 1,000 companies...")
        with open(CSV_FILE, 'r') as f:
            subprocess.run([
                'gsheet', 'append',
                '--account', ACCOUNT,
                SHEET_ID
            ], stdin=f, check=True)
        
        print("✓ Successfully uploaded to Google Sheets")
    except subprocess.CalledProcessError as e:
        print(f"✗ Error using gsheet CLI: {e}")
        use_gsheet = False

if not use_gsheet:
    # Alternative: Show instructions for manual upload
    print("\n" + "=" * 80)
    print("MANUAL UPLOAD INSTRUCTIONS")
    print("=" * 80)
    print()
    print("Please upload the file manually:")
    print(f"1. Open: https://docs.google.com/spreadsheets/d/{SHEET_ID}/edit")
    print(f"2. File → Import → Upload → Select '{CSV_FILE}'")
    print("3. Import location: Replace current sheet")
    print("4. Separator: Comma")
    print()
    print(f"CSV file location: /Users/max/.openclaw/workspace/postharvest/{CSV_FILE}")
    print()
    
    # Generate CSV statistics
    print("=" * 80)
    print("FILE READY FOR UPLOAD")
    print("=" * 80)
    print(f"Total companies: {len(rows) - 1}")
    print(f"File size: {sum(len(','.join(row).encode('utf-8')) for row in rows) / 1024:.1f} KB")

print("\n" + "=" * 80)
print("✓ UPLOAD PROCESS COMPLETE")
print("=" * 80)