#!/usr/bin/env python3
"""
Google Sheets API Setup Helper
==============================

This script helps set up Google Sheets API access for the GeoNames synchronizer.
"""

import json
import os
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

# Scopes needed for Google Sheets access
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

def setup_google_api():
    """Set up Google Sheets API authentication"""
    
    print("🔐 Google Sheets API Setup")
    print("=" * 50)
    
    # Check for credentials
    creds_file = "credentials.json"
    token_file = "token.json"
    
    if not os.path.exists(creds_file):
        print(f"❌ Missing {creds_file}")
        print("\nTo get Google API credentials:")
        print("1. Go to: https://console.cloud.google.com/")
        print("2. Create new project or select existing")
        print("3. Enable Google Sheets API")
        print("4. Create credentials (OAuth 2.0 Client IDs)")
        print("5. Download credentials.json")
        print(f"6. Place it in: {os.getcwd()}")
        return False
    
    creds = None
    
    # Load existing token
    if os.path.exists(token_file):
        creds = Credentials.from_authorized_user_file(token_file, SCOPES)
    
    # If no valid credentials, get new ones
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            print("🔄 Refreshing existing credentials...")
            creds.refresh(Request())
        else:
            print("🌐 Starting OAuth flow...")
            flow = InstalledAppFlow.from_client_secrets_file(creds_file, SCOPES)
            creds = flow.run_local_server(port=0)
        
        # Save credentials
        with open(token_file, 'w') as token:
            token.write(creds.to_json())
        print(f"✅ Credentials saved to {token_file}")
    
    # Test API access
    try:
        print("🧪 Testing API access...")
        service = build('sheets', 'v4', credentials=creds)
        
        # Simple test - get spreadsheet metadata
        spreadsheet_id = "1b4GKYaRVh_V3dN4Ok01EiUHdpoQxr0a_6ptoSZWsj_o"
        
        sheet = service.spreadsheets()
        result = sheet.get(spreadsheetId=spreadsheet_id).execute()
        
        print(f"✅ Successfully connected to spreadsheet: {result.get('properties', {}).get('title', 'Unknown')}")
        
        # Show available sheets
        sheets = result.get('sheets', [])
        print(f"\n📋 Available sheets ({len(sheets)}):")
        for i, sheet_info in enumerate(sheets):
            sheet_title = sheet_info.get('properties', {}).get('title', f'Sheet{i+1}')
            print(f"  - {sheet_title}")
        
        return True
        
    except Exception as e:
        print(f"❌ API test failed: {e}")
        return False

def test_write_access():
    """Test writing a small sample to the spreadsheet"""
    
    try:
        creds = Credentials.from_authorized_user_file("token.json", SCOPES)
        service = build('sheets', 'v4', credentials=creds)
        
        spreadsheet_id = "1b4GKYaRVh_V3dN4Ok01EiUHdpoQxr0a_6ptoSZWsj_o"
        
        # Write test data to a test range
        test_values = [
            ["Test", "Data", "From", "API"],
            ["Timestamp", str(datetime.now()), "Status", "Working"]
        ]
        
        body = {
            'values': test_values
        }
        
        result = service.spreadsheets().values().update(
            spreadsheetId=spreadsheet_id,
            range="A1:D2",  # Test range
            valueInputOption="RAW",
            body=body
        ).execute()
        
        print(f"✅ Test write successful! Updated {result.get('updatedCells')} cells")
        return True
        
    except Exception as e:
        print(f"❌ Write test failed: {e}")
        print("Make sure your Google account has edit access to the spreadsheet!")
        return False

if __name__ == "__main__":
    from datetime import datetime
    
    print("Setting up Google Sheets API access...\n")
    
    if setup_google_api():
        print("\n" + "=" * 50)
        response = input("Do you want to test write access? (y/n): ")
        if response.lower() == 'y':
            test_write_access()
    else:
        print("\n❌ Setup failed. Please check the instructions above.")