"""Direct sheet calculation - last column only."""
import gspread
from google.oauth2.credentials import Credentials
import json

# Load credentials
with open('token.json', 'r') as f:
    token_data = json.load(f)

creds = Credentials(
    token=token_data.get('token'),
    refresh_token=token_data.get('refresh_token'),
    token_uri=token_data.get('token_uri'),
    client_id=token_data.get('client_id'),
    client_secret=token_data.get('client_secret'),
    scopes=token_data.get('scopes')
)

# Connect to sheet
gc = gspread.authorize(creds)
spreadsheet = gc.open_by_key('1ayEGU0h_R7CY55COC1U94-p0rJch109YBGvezjYjHWw')
worksheet = spreadsheet.worksheets()[1]  # BASE sheet

print(f"Sheet: {worksheet.title}")
print(f"Total rows: {worksheet.row_count}")
print(f"Total columns: {worksheet.col_count}")

# Get ALL data
print("\nGetting ALL data...")
all_values = worksheet.get_all_values()
headers = all_values[0]
data_rows = all_values[1:]

print(f"\nHeaders: {headers}")
print(f"Total data rows: {len(data_rows)}")

# Find Faturaçao column
fat_col_index = None
for i, h in enumerate(headers):
    if 'fatura' in h.lower():
        fat_col_index = i
        print(f"Found Faturaçao at column index {i}: '{h}'")
        break

if fat_col_index is None:
    print("ERROR: Could not find Faturaçao column!")
    exit(1)

# Show first 30 values from Faturaçao column
print(f"\nFirst 30 values from Faturaçao column:")
for i in range(min(30, len(data_rows))):
    val = data_rows[i][fat_col_index] if fat_col_index < len(data_rows[i]) else ''
    print(f"  Row {i+1}: '{val}'")

# Clean and sum
def clean_value(val):
    if not val or (isinstance(val, str) and val.strip() == ''):
        return None
    try:
        val = str(val).strip()
        val = val.replace('€', '').strip()
        val = val.replace(' ', '')  # Remove space separator
        val = val.replace('.', '')  # Remove dot thousand separator
        val = val.replace(',', '.')  # Decimal separator
        return float(val)
    except Exception as e:
        print(f"ERROR parsing '{val}': {e}")
        return None

# Calculate total
total = 0.0
null_count = 0
non_null_count = 0
for i, row in enumerate(data_rows, 1):
    if fat_col_index >= len(row):
        null_count += 1
        continue
    
    val = row[fat_col_index]
    cleaned = clean_value(val)
    if cleaned is None:
        null_count += 1
    else:
        total += cleaned
        non_null_count += 1

print(f"\n{'='*60}")
print(f"TOTAL FATURAÇÃO: €{total:,.2f}")
print(f"Rows processed: {len(data_rows)}")
print(f"Non-null values: {non_null_count}")
print(f"Null/empty values: {null_count}")
print(f"{'='*60}")