#!/usr/bin/env python3
"""Diagnose Google Sheet access and capabilities"""

import gspread
import json
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials

# Load credentials
try:
    with open('credentials.json', 'r') as f:
        creds_data = json.load(f)
    
    credentials = Credentials(
        token=creds_data.get('token'),
        refresh_token=creds_data.get('refresh_token'),
        token_uri=creds_data.get('token_uri'),
        client_id=creds_data.get('client_id'),
        client_secret=creds_data.get('client_secret'),
        scopes=creds_data.get('scopes')
    )
    
    # Refresh token if needed
    if credentials.expired:
        credentials.refresh(Request())
    
    gc = gspread.authorize(credentials)
    
    SHEET_ID = "1_r06d4IolTc65P7KLN8XSq2ikG4gf-MG"
    
    print("=" * 60)
    print("SHEET DIAGNOSTIC REPORT")
    print("=" * 60)
    print(f"\n📋 Sheet ID: {SHEET_ID}\n")
    
    # Test 1: Open spreadsheet
    print("1️⃣  Testing: Open spreadsheet...")
    try:
        spreadsheet = gc.open_by_key(SHEET_ID)
        print(f"   ✅ SUCCESS - Opened: {spreadsheet.title}")
        print(f"   📍 URL: {spreadsheet.url}")
    except Exception as e:
        print(f"   ❌ FAILED: {e}")
        print("\n❗ DIAGNOSIS: Cannot open sheet. Possible causes:")
        print("   - Sheet ID is incorrect")
        print("   - You don't have access")
        print("   - Spreadsheet was deleted")
        exit(1)
    
    # Test 2: List worksheets
    print("\n2️⃣  Testing: List worksheets...")
    try:
        worksheets = spreadsheet.worksheets()
        print(f"   ✅ SUCCESS - Found {len(worksheets)} sheet(s):")
        for ws in worksheets:
            print(f"      • '{ws.title}' ({ws.row_count} rows x {ws.col_count} cols)")
    except Exception as e:
        print(f"   ❌ FAILED: {e}")
    
    # Test 3: Check for 'Inventário' sheet
    print("\n3️⃣  Testing: Check for 'Inventário' sheet...")
    try:
        inv_sheet = spreadsheet.worksheet("Inventário")
        print(f"   ✅ EXISTS - 'Inventário' sheet found")
        print(f"      Rows: {inv_sheet.row_count}, Cols: {inv_sheet.col_count}")
        
        # Try to read first row
        try:
            first_row = inv_sheet.row_values(1)
            print(f"      First row has {len(first_row)} values")
            if first_row:
                print(f"      Sample: {first_row[:3]}...")
        except Exception as e:
            print(f"      ⚠️  Could not read first row: {e}")
            
    except gspread.exceptions.WorksheetNotFound:
        print(f"   ℹ️  'Inventário' sheet NOT found - will be created")
    except Exception as e:
        print(f"   ❌ FAILED: {e}")
    
    # Test 4: Try to add worksheet
    print("\n4️⃣  Testing: Add worksheet capability...")
    try:
        # Try with a test name first
        test_ws = spreadsheet.add_worksheet(title="TEST_DELETE_ME", rows=10, cols=5)
        print(f"   ✅ SUCCESS - Can add worksheets")
        print(f"      Created temporary sheet: '{test_ws.title}'")
        
        # Delete the test sheet
        try:
            spreadsheet.del_worksheet(test_ws)
            print(f"      ✅ Cleaned up test sheet")
        except:
            pass
            
    except gspread.exceptions.APIError as e:
        print(f"   ❌ API ERROR: {e}")
        if "not supported" in str(e).lower():
            print("\n⚠️  DIAGNOSIS: This spreadsheet doesn't support adding sheets!")
            print("   Possible reasons:")
            print("   - It might be a shared drive file")
            print("   - It might have editing restrictions")
            print("   - It might be protected or read-only")
        
    except Exception as e:
        print(f"   ❌ FAILED: {e}")
    
    # Test 5: Try to format cells
    print("\n5️⃣  Testing: Cell formatting capability...")
    try:
        if inv_sheet:
            ws = spreadsheet.worksheet("Inventário")
        else:
            ws = spreadsheet.sheet1
            
        # Try to format A1
        ws.format("A1", {"textFormat": {"bold": True}})
        print(f"   ✅ SUCCESS - Can format cells")
        
    except gspread.exceptions.APIError as e:
        print(f"   ❌ API ERROR: {e}")
        if "not supported" in str(e).lower():
            print("   ⚠️  Document doesn't support this formatting operation")
            
    except Exception as e:
        print(f"   ❌ FAILED: {e}")
    
    # Test 6: Try to append row
    print("\n6️⃣  Testing: Append row capability...")
    try:
        if inv_sheet:
            ws = spreadsheet.worksheet("Inventário")
        else:
            ws = spreadsheet.sheet1
            
        # Check current state
        all_values = ws.get_all_values()
        print(f"   Current rows: {len(all_values)}")
        
        # Try to insert a test row
        ws.append_row(["TEST", "DATA", "DELETE"])
        print(f"   ✅ SUCCESS - Can append rows")
        
        # Remove it
        try:
            ws.delete_rows(len(ws.get_all_values()))
        except:
            pass
            
    except gspread.exceptions.APIError as e:
        print(f"   ❌ API ERROR: {e}")
        
    except Exception as e:
        print(f"   ❌ FAILED: {e}")
    
    print("\n" + "=" * 60)
    print("RECOMMENDATION:")
    print("=" * 60)
    print("""
If the sheet is showing as 'not supported', try:

OPTION A: Share the sheet directly with your Google account
  1. Open the sheet in Google Drive
  2. Click 'Share' (top right)
  3. Make sure your email has edit access

OPTION B: Create a new sheet in your own Google Drive
  Run: python create_new_inventory_sheet.py
  
OPTION C: Check sheet permissions
  - Is it in a shared drive?
  - Is it view-only?
  - Do you have owner/editor access?
    """)

except FileNotFoundError:
    print("❌ credentials.json not found. Please authenticate first.")
except Exception as e:
    print(f"❌ Error: {e}")
    import traceback
    traceback.print_exc()