#!/usr/bin/env python3
"""
Add Inventory Sheet to existing Google Spreadsheet
"""

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

# Column headers for inventory
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'
]

SPREADSHEET_ID = "1_r06d4IolTc65P7KLN8XSq2ikG4gf-MG"

def setup_inventory_sheet():
    """Add inventory sheet to existing spreadsheet"""
    
    try:
        # Check for credentials file
        credentials_file = 'credentials.json'
        
        if not os.path.exists(credentials_file):
            print("❌ credentials.json not found!")
            return None
        
        print("📝 Reading credentials...")
        
        # Try service account credentials first
        try:
            with open(credentials_file) as f:
                creds_data = json.load(f)
            
            # Check if it's service account format
            if 'type' in creds_data and creds_data['type'] == 'service_account':
                credentials = Credentials.from_service_account_file(
                    credentials_file,
                    scopes=['https://www.googleapis.com/auth/spreadsheets', 
                            'https://www.googleapis.com/auth/drive']
                )
            else:
                print("⚠️  OAuth2 credentials detected")
                print("📌 Using Flask to create sheet with user credentials...")
                return None
                
        except Exception as e:
            print(f"⚠️  Could not load service account: {e}")
            return None
        
        # Authorize
        print("🔐 Authorizing...")
        gc = gspread.authorize(credentials)
        
        # Open spreadsheet
        print(f"📂 Opening spreadsheet: {SPREADSHEET_ID}")
        spreadsheet = gc.open_by_key(SPREADSHEET_ID)
        
        # Create new sheet
        print("➕ Creating 'Inventário' sheet...")
        try:
            worksheet = spreadsheet.add_worksheet(title="Inventário", rows=1000, cols=26)
        except gspread.exceptions.APIError as e:
            if "already exists" in str(e):
                print("⚠️  Sheet 'Inventário' already exists")
                worksheet = spreadsheet.worksheet("Inventário")
            else:
                raise
        
        # Add headers
        print(f"📋 Adding {len(INVENTORY_COLUMNS)} column headers...")
        worksheet.append_row(INVENTORY_COLUMNS)
        
        # Format header row
        print("🎨 Formatting header row...")
        header_range = f"A1:{chr(64 + len(INVENTORY_COLUMNS))}1"
        worksheet.format(header_range, {
            'textFormat': {'bold': True},
            'backgroundColor': {'red': 0.667, 'green': 0.784, 'blue': 0.922}
        })
        
        print(f"\n✅ SUCCESS! Inventory Sheet Created/Updated")
        print(f"📊 Spreadsheet ID: {SPREADSHEET_ID}")
        print(f"📑 Sheet Name: Inventário")
        print(f"🔗 URL: {spreadsheet.url}")
        print(f"\n📝 Columns ({len(INVENTORY_COLUMNS)}):")
        for i, col in enumerate(INVENTORY_COLUMNS, 1):
            print(f"   {i:2d}. {col}")
        
        print(f"\n✅ app.py is already configured with this Sheet ID")
        print(f"   INVENTORY_SPREADSHEET_ID = \"{SPREADSHEET_ID}\"")
        
        return True
        
    except Exception as e:
        print(f"❌ Error: {e}")
        import traceback
        traceback.print_exc()
        return None

if __name__ == '__main__':
    print("\n" + "="*70)
    print("SETUP INVENTORY SHEET - CONFIGURAR FOLHA DE INVENTÁRIO")
    print("="*70 + "\n")
    
    result = setup_inventory_sheet()
    
    if result is None:
        print("\n" + "="*70)
        print("⚠️  Using Flask endpoint to create sheet...")
        print("="*70)
        print("\nVisit: https://gawsy-gregg-overrudely.ngrok-free.dev/init-inventory-sheet")
        print("\nOr open in browser and login first, then visit the URL above.")
    elif result:
        print("\n" + "="*70)
        print("✅ Ready to use! Restart Flask and access Inventory module.")
        print("="*70 + "\n")