#!/usr/bin/env python3
"""
Google Sheets Integration
Uploads analysis results to Google Sheets
"""

import csv
import sys
import os
import json
from datetime import datetime

def create_ssv_output(results_file, target_sheet_url):
    """Create SSV (semicolon-separated values) output for Google Sheets"""
    
    if not os.path.exists(results_file):
        print(f"Error: Results file {results_file} not found")
        return False
    
    # Read the results
    results = []
    with open(results_file, 'r', encoding='utf-8') as file:
        reader = csv.DictReader(file)
        for row in reader:
            results.append(row)
    
    if not results:
        print("No results to export")
        return False
    
    # Create SSV output
    ssv_file = 'google_sheets_export.ssv'
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    with open(ssv_file, 'w', encoding='utf-8', newline='') as file:
        # Write header with timestamp
        file.write(f"# GEO URL Analysis Results - Generated: {timestamp}\n")
        file.write(f"# Target Google Sheets: {target_sheet_url}\n")
        file.write("# Format: Country;Position;Total_Rows;moy_URL;Original_Order\n")
        file.write("\n")
        
        # Write SSV header
        file.write("Country;Position;Total_Rows;moy_URL;Original_Order\n")
        
        # Write data
        for result in results:
            line = f"{result['country']};{result['position']};{result['total_rows']};{result['moy_url']};{result['original_order']}\n"
            file.write(line)
    
    print(f"✅ SSV export created: {ssv_file}")
    print(f"📊 Contains {len(results)} country results")
    print(f"🌐 Target Google Sheets: {target_sheet_url}")
    print("\n📋 Results summary:")
    print("-" * 50)
    
    for result in results:
        print(f"{result['country']:8} | Position: {result['position']:2}/{result['total_rows']:2} | Order: {result['original_order']}")
    
    print(f"\n💡 To upload to Google Sheets:")
    print(f"   1. Open: {target_sheet_url}")
    print(f"   2. Copy content from: {ssv_file}")
    print(f"   3. Paste to Google Sheets (File > Import > Paste)")
    print(f"   4. Choose separator: Semicolon (;)")
    
    return ssv_file

def main():
    target_sheet = "https://docs.google.com/spreadsheets/d/1Wna-30aqljYMgGtTQhgOp11LIP5GV-RugX2iqlVUs_Q/edit?gid=0#gid=0"
    results_file = "analysis_results.csv"
    
    if len(sys.argv) > 1:
        results_file = sys.argv[1]
    
    if len(sys.argv) > 2:
        target_sheet = sys.argv[2]
    
    print(f"🚀 Creating Google Sheets export...")
    print(f"📁 Results file: {results_file}")
    print(f"🌐 Target sheet: {target_sheet}")
    print()
    
    ssv_file = create_ssv_output(results_file, target_sheet)
    
    if ssv_file:
        print(f"\n✅ Export completed successfully!")
        return True
    else:
        print(f"\n❌ Export failed!")
        return False

if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)