#!/usr/bin/env python3
"""
Add 505 apple facilities to master PHT list
"""
import csv
import time
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build

# Google Sheet ID
SHEET_ID = "1uVd-xZFF4TEQGqtvw9z6W8fffeaifPCoLsek83GmEoQ"
SHEET_NAME = "USA"

# Load credentials
creds = Credentials.from_authorized_user_file(
    '/Users/max/.openclaw/agents/main/agent/goog-oauth-creds.json'
)

service = build('sheets', 'v4', credentials=creds)

# Read the 505 facilities
print("📖 Reading 505 facilities...")
facilities = []
with open('/Users/max/.openclaw/workspace/postharvest/4-states-apple-facilities.csv', 'r') as f:
    reader = csv.DictReader(f)
    for row in reader:
        facilities.append(row)

print(f"✅ Loaded {len(facilities)} facilities")

# Get current USA sheet data to find next empty row
print("\n📊 Getting current USA sheet...")
result = service.spreadsheets().values().get(
    spreadsheetId=SHEET_ID,
    range=f"{SHEET_NAME}!A:O"
).execute()

current_rows = result.get('values', [])
next_row = len(current_rows) + 1

print(f"Current rows: {len(current_rows)}")
print(f"Will append starting at row: {next_row}")

# Prepare rows for appending
# Format: Company, Website, Country, Region, Primary Fruit, CA Storage, Qualified, Employee, Revenue, Notes, Contact Name, Title, Email, Phone, Source
rows_to_add = []

for f in facilities:
    row = [
        f['Company'],           # Company
        f.get('Website', ''),   # Website
        'USA',                  # Country
        f['Region'],            # Region (state)
        'Apples',               # Primary Fruit
        '',                     # CA Storage (unknown)
        '',                     # Qualified (to verify)
        '',                     # Employee
        '',                     # Revenue
        'Apple orchard/packer from 4-state scrape',  # Notes
        '',                     # Contact Name
        '',                     # Title
        '',                     # Email
        f.get('Phone', ''),     # Phone
        f['Source']             # Source
    ]
    rows_to_add.append(row)

# Append in batches of 100 to avoid API limits
print(f"\n📤 Appending {len(rows_to_add)} rows in batches...")

batch_size = 100
for i in range(0, len(rows_to_add), batch_size):
    batch = rows_to_add[i:i+batch_size]
    
    body = {
        'values': batch
    }
    
    result = service.spreadsheets().values().append(
        spreadsheetId=SHEET_ID,
        range=f"{SHEET_NAME}!A{next_row}",
        valueInputOption='RAW',
        body=body
    ).execute()
    
    updates = result.get('updates', {})
    print(f"  ✅ Batch {i//batch_size + 1}: Added {updates.get('updatedRows', 0)} rows")
    
    next_row += len(batch)
    time.sleep(1)  # Rate limit

print(f"\n🎉 ✅ Successfully added {len(facilities)} facilities to master list!")
print(f"\n📊 New total USA facilities: {len(current_rows) - 1 + len(facilities)}")
print(f"\nSheet: https://docs.google.com/spreadsheets/d/{SHEET_ID}/edit")