#!/usr/bin/env python3
"""
Upload PHT Top 1,000 using OAuth2 credentials
"""

import csv
import pickle
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SPREADSHEET_ID = '14WPFM_wwPv7aq25_r3csudwoNBrYTT-Fz8NOb6by2i4'
CSV_FILE = 'pht_top_1000_apple_pear_citrus.csv'
CREDS_FILE = '/Users/max/.openclaw/credentials.json'
TOKEN_FILE = 'token.pickle'

print("=" * 80)
print("PHT TOP 1,000 - GOOGLE SHEETS UPLOAD (OAuth2)")
print("=" * 80)

# Read CSV
print("\n[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"✓ Loaded {len(rows)} rows (including header)")

# Authenticate
print("\n[2/3] Authenticating...")
creds = None

# Check for existing token
if os.path.exists(TOKEN_FILE):
    with open(TOKEN_FILE, 'rb') as token:
        creds = pickle.load(token)

# If no valid credentials, let user log in
if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        print("Refreshing expired token...")
        creds.refresh(Request())
    else:
        print("No valid credentials found. Opening browser for authentication...")
        flow = InstalledAppFlow.from_client_secrets_file(CREDS_FILE, SCOPES)
        creds = flow.run_local_server(port=0)
    
    # Save credentials for next run
    with open(TOKEN_FILE, 'wb') as token:
        pickle.dump(creds, token)

print("✓ Authenticated successfully")

# Upload to Google Sheets
print("\n[3/3] Uploading to Google Sheets...")
try:
    service = build('sheets', 'v4', credentials=creds)
    
    # Clear existing data
    print("  Clearing existing data...")
    service.spreadsheets().values().clear(
        spreadsheetId=SPREADSHEET_ID,
        range='A:Z'
    ).execute()
    
    # Upload new data
    print(f"  Uploading {len(rows)} rows...")
    body = {'values': rows}
    result = service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID,
        range='A1',
        valueInputOption='USER_ENTERED',
        body=body
    ).execute()
    
    print(f"✓ {result.get('updatedCells')} cells updated")
    
    # Format header
    print("  Formatting header...")
    requests = [{
        'repeatCell': {
            'range': {
                'sheetId': 0,
                'startRowIndex': 0,
                'endRowIndex': 1
            },
            'cell': {
                'userEnteredFormat': {
                    'backgroundColor': {'red': 0.2, 'green': 0.6, 'blue': 0.9},
                    'textFormat': {'bold': True, 'fontSize': 11},
                    'horizontalAlignment': 'CENTER'
                }
            },
            'fields': 'userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)'
        }
    }, {
        'updateSheetProperties': {
            'properties': {
                'sheetId': 0,
                'gridProperties': {'frozenRowCount': 1}
            },
            'fields': 'gridProperties.frozenRowCount'
        }
    }]
    
    service.spreadsheets().batchUpdate(
        spreadsheetId=SPREADSHEET_ID,
        body={'requests': requests}
    ).execute()
    
    print("✓ Formatting complete")
    
    print("\n" + "=" * 80)
    print("✅ SUCCESS - 1,000 COMPANIES UPLOADED!")
    print("=" * 80)
    print(f"\n🔗 View at: https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/edit")
    
except HttpError as err:
    print(f"✗ Error: {err}")
    
except Exception as e:
    print(f"✗ Unexpected error: {e}")
    import traceback
    traceback.print_exc()