#!/usr/bin/env python3
"""Test inventory sheet access without needing Flask session"""

import gspread
from google.oauth2.service_account import Credentials
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials as UserCredentials
import json
import time

SHEET_ID = "1_r06d4IolTc65P7KLN8XSq2ikG4gf-MG"
INVENTORY_COLUMNS = [
    'Linha', 'Quantidade em stock', 'Tipo', 'Ref', 'Modelo', 'Submodelo',
    'Tipo de tampa', 'Medida', 'Tipo de Medida', 'Madeira', 'Laminado', 'Cor',
    'Acabamento', 'Zinco/Inox', 'Estofo', 'Tecido', 'Cor Tecido', 'Renda',
    'Conjunto', 'Asas', 'Qtd Asas', 'Cruz/Cristo', 'Fecho', 'Acessórios',
    'Extras/Observações', 'Cliente'
]

print("=" * 70)
print("INVENTORY SHEET TEST")
print("=" * 70)

try:
    # Try to read credentials
    print("\n📋 Loading credentials...")
    try:
        with open('credentials.json', 'r') as f:
            creds_data = json.load(f)
        print("✅ Credentials file found")
    except FileNotFoundError:
        print("❌ credentials.json not found - this is expected if using session auth")
        print("   The app uses Flask session authentication")
        print("\n⚠️  This test requires interactive browser-based authentication")
        print("   Please visit: https://gawsy-gregg-overrudely.ngrok-free.dev/init-inventory-sheet")
        exit(0)
    
    # Try credentials from file
    print(f"\n🔐 Testing credentials...")
    try:
        credentials = UserCredentials(
            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())
        
        print("✅ Credentials valid")
    except Exception as e:
        print(f"❌ Credential error: {e}")
        exit(1)
    
    # Test 1: Open spreadsheet
    print(f"\n📂 Opening spreadsheet: {SHEET_ID}")
    try:
        gc = gspread.authorize(credentials)
        spreadsheet = gc.open_by_key(SHEET_ID)
        print(f"✅ Opened: {spreadsheet.title}")
    except Exception as e:
        print(f"❌ Error: {e}")
        exit(1)
    
    # Test 2: List worksheets
    print(f"\n📑 Listing worksheets...")
    try:
        worksheets = spreadsheet.worksheets()
        print(f"✅ Found {len(worksheets)} sheet(s):")
        for ws in worksheets:
            print(f"   • '{ws.title}' ({ws.row_count} rows, {ws.col_count} cols)")
    except Exception as e:
        print(f"❌ Error: {e}")
    
    # Test 3: Try to read sheet1
    print(f"\n📖 Reading Sheet1...")
    try:
        ws = spreadsheet.sheet1
        data = ws.get_all_values()
        print(f"✅ Sheet1 has {len(data)} rows")
        if len(data) > 0:
            print(f"   First row: {data[0][:5]}...")
        
        records = ws.get_all_records()
        print(f"✅ Total records: {len(records)}")
        
    except Exception as e:
        print(f"❌ Error: {e}")
    
    # Test 4: Try to append
    print(f"\n✏️  Testing append capability...")
    try:
        ws = spreadsheet.sheet1
        test_row = ["TEST"] + [""] * (len(INVENTORY_COLUMNS) - 1)
        ws.append_row(test_row)
        print(f"✅ Append successful")
        
        # Delete the test row
        try:
            all_rows = ws.get_all_values()
            ws.delete_rows(len(all_rows))
            print(f"✅ Cleanup successful")
        except:
            pass
            
    except Exception as e:
        print(f"❌ Append error: {e}")
    
    # Test 5: Check headers
    print(f"\n🏷️  Checking headers...")
    try:
        ws = spreadsheet.sheet1
        first_row = ws.row_values(1)
        
        if first_row == INVENTORY_COLUMNS:
            print(f"✅ Headers match perfectly ({len(INVENTORY_COLUMNS)} columns)")
        elif len(first_row) == len(INVENTORY_COLUMNS):
            print(f"⚠️  Headers exist but don't match")
            print(f"   Configured: {INVENTORY_COLUMNS[:3]}...")
            print(f"   Sheet has: {first_row[:3]}...")
        else:
            print(f"ℹ️  Sheet has {len(first_row)} columns (need {len(INVENTORY_COLUMNS)})")
            if len(first_row) == 0:
                print(f"     → Sheet is empty, headers will be added")
    except Exception as e:
        print(f"⚠️  Error checking headers: {e}")
    
    print("\n" + "=" * 70)
    print("✅ SHEET IS ACCESSIBLE AND FUNCTIONAL")
    print("=" * 70)
    print("\n📝 Next steps:")
    print("1. Visit: https://gawsy-gregg-overrudely.ngrok-free.dev/init-inventory-sheet")
    print("2. This will initialize headers if needed")
    print("3. Then access the Inventory page to add products")
    print()
    
except Exception as e:
    print(f"\n❌ Unexpected error: {e}")
    import traceback
    traceback.print_exc()