"""
SIMPLIFIED DIAGNOSTIC APP - Verify raw data first
Shows raw totals with NO filters to verify data is being read correctly
"""
from flask import Flask, jsonify, session, render_template_string
from flask_login import LoginManager, login_required, current_user
import gspread
from google.oauth2.credentials import Credentials
import pandas as pd
import sys
# Import from main app
sys.path.insert(0, '.')
from app import (
app, login_manager, users, get_google_credentials,
find_column, parse_currency
)
@app.route('/diagnostic')
@login_required
def diagnostic():
"""Diagnostic page to verify raw data."""
creds = get_google_credentials()
if not creds:
return "Not authenticated", 401
try:
SPREADSHEET_ID = session.get('spreadsheet_id')
if not SPREADSHEET_ID:
return "Spreadsheet ID not configured", 400
# Fetch raw data
gc = gspread.authorize(creds)
spreadsheet = gc.open_by_key(SPREADSHEET_ID)
# List all worksheets
all_worksheets = spreadsheet.worksheets()
sheets_info = []
for idx, ws in enumerate(all_worksheets):
sheets_info.append(f"[{idx}] {ws.title} ({ws.row_count} rows x {ws.col_count} cols)")
# Read from second sheet (index 1)
worksheet = spreadsheet.worksheets()[1]
all_values = worksheet.get_all_values()
if not all_values:
return "No data in sheet", 400
headers = all_values[0]
data = all_values[1:]
df = pd.DataFrame(data, columns=headers)
df.columns = df.columns.str.strip()
# Find columns
mes_col = find_column(df, 'mes')
fat_col = find_column(df, 'faturacao')
quant_col = find_column(df, 'quantidade')
cliente_col = find_column(df, 'cliente')
# Show RAW samples BEFORE parsing
raw_samples = []
if fat_col:
raw_samples = df[fat_col].head(20).tolist()
# Parse faturacao column
if fat_col:
df[fat_col] = df[fat_col].apply(parse_currency)
if quant_col:
df[quant_col] = df[quant_col].apply(parse_currency)
# Calculate RAW totals (no filters)
total_rows = len(df)
total_fat_raw = df[fat_col].sum() if fat_col else 0
total_quant_raw = df[quant_col].sum() if quant_col else 0
unique_clients_raw = df[cliente_col].nunique() if cliente_col else 0
# Calculate totals by year
year_totals = {}
if mes_col and fat_col:
for year in ['2024', '2025', '2026']:
year_df = df[df[mes_col].astype(str).str.contains(year, na=False, regex=False)]
year_totals[year] = {
'rows': len(year_df),
'faturacao': year_df[fat_col].sum(),
'quantidade': year_df[quant_col].sum() if quant_col else 0,
'clientes': year_df[cliente_col].nunique() if cliente_col else 0
}
# Sample dates
sample_dates = df[mes_col].head(20).tolist() if mes_col else []
# Build HTML response
html = f"""
Data Diagnostic
📊 DATA DIAGNOSTIC REPORT
📁 Spreadsheet Information
Spreadsheet ID: {SPREADSHEET_ID}
Total Worksheets: {len(all_worksheets)}
{''.join(f'- {info}
' for info in sheets_info)}
⚠️ App is reading from: [{1}] {worksheet.title}
📋 Columns Found
All Columns: {', '.join(df.columns.tolist())}
Mês Column: {mes_col or 'NOT FOUND'}
Faturação Column: {fat_col or 'NOT FOUND'}
Quantidade Column: {quant_col or 'NOT FOUND'}
Cliente Column: {cliente_col or 'NOT FOUND'}
🔍 RAW Data Samples (BEFORE Parsing)
First 20 values from Faturação column:
{''.join(f'- {val}
' for val in raw_samples)}
📅 Date Samples
First 20 dates from Mês column:
{''.join(f'- {date}
' for date in sample_dates)}
💰 RAW TOTALS (NO FILTERS)
| Metric |
Value |
| Total Rows |
{total_rows:,} |
| Total Faturação |
€ {total_fat_raw:,.2f} |
| Total Quantidade |
{total_quant_raw:,.0f} |
| Unique Clientes |
{unique_clients_raw:,} |
📊 TOTALS BY YEAR
| Year |
Rows |
Faturação |
Quantidade |
Clientes |
{''.join(f'''
| {year} |
{data["rows"]:,} |
€ {data["faturacao"]:,.2f} |
{data["quantidade"]:,.0f} |
{data["clientes"]:,} |
''' for year, data in year_totals.items())}
✅ Comparison with Looker Studio (Year 2025)
| Source |
Faturação |
Status |
| Looker Studio (Expected) |
€ 6,005,182.69 |
✓ Reference |
| This App (Current) |
€ {year_totals.get('2025', {}).get('faturacao', 0):,.2f} |
{"✓ MATCH!" if abs(year_totals.get('2025', {}).get('faturacao', 0) - 6005182.69) < 1 else "✗ MISMATCH"}
|
← Back to Dashboard
"""
return html
except Exception as e:
return f"ERROR: {str(e)}\n\nImport traceback:\n{__import__('traceback').format_exc()}", 500
if __name__ == '__main__':
print("Navigate to: http://localhost:5000/diagnostic")
print("This will show raw data totals to verify the issue")