"""
Sales Dashboard Application
A Flask-based dashboard for viewing and analyzing sales data from Google Sheets.
"""

from flask import Flask, redirect, url_for, session, request, render_template, jsonify
from flask_login import LoginManager, UserMixin, login_user, logout_user, login_required, current_user
import os
import json
import requests
from google_auth_oauthlib.flow import Flow
from google.oauth2.credentials import Credentials
from google.auth.transport.requests import Request
import gspread
from datetime import timedelta, datetime
import pandas as pd
import numpy as np
from typing import Optional, Dict, List, Any

# ============================================================================
# CONFIGURATION
# ============================================================================

CLIENT_SECRETS_FILE = "credentials.json"
SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets.readonly',
    'https://www.googleapis.com/auth/drive.readonly',
    'https://www.googleapis.com/auth/userinfo.email',
    'https://www.googleapis.com/auth/userinfo.profile',
    'openid'
]

# Read redirect_uri from credentials.json for consistency
with open(CLIENT_SECRETS_FILE, 'r') as f:
    creds_data = json.load(f)
    REDIRECT_URI = creds_data['web']['redirect_uris'][0]

print(f"Using REDIRECT_URI from credentials.json: {REDIRECT_URI}")

# Column mapping: handles different column names in sheets
COLUMN_MAPPING = {
    'cliente': ['Cliente', 'cliente', 'CLIENTE', 'Cliente '],
    'zona': ['Zona', 'zona', 'ZONA', 'Zona '],
    'comercial': ['Comercial', 'comercial', 'COMERCIAL', 'Comercial '],
    'desconto': ['Desconto', 'desconto', 'DESCONTO', 'Discount', 'Desconto '],
    'prazo_pagamento': ['Prazo Pagamento Dias', 'Prazo Pagamento', 'Payment Days', 'Prazo Pagamento Dias '],
    'codigo': ['Código', 'codigo', 'CÓDIGO', 'Code', 'Código '],
    'referencia': ['Referencia', 'referencia', 'REFERENCIA', 'Reference', 'Referencia '],
    'familia': ['Familia', 'familia', 'FAMILIA', 'Family', 'Familia '],
    'mes': ['Mês', 'mes', 'MÊS', 'Month', 'Mês '],
    'quantidade': ['Quant', 'Quantidade', 'quantidade', 'Quantity', 'Quant '],
    'faturacao': ['Faturaçao', 'Faturacao', 'faturacao', 'FATURAÇÃO', 'Revenue', 'Faturaçao ']
}

# ============================================================================
# APP INITIALIZATION
# ============================================================================

app = Flask(__name__)
app.secret_key = 'e7ac3a5f4f6e4d0d8c3b7a5e9f2a1b0c9e8d7c6b5a4f3e2d1c0b9a8f7e6d5c4b'
app.config['SESSION_COOKIE_SECURE'] = False
app.config['SESSION_COOKIE_HTTPONLY'] = True
app.config['SESSION_COOKIE_SAMESITE'] = "Lax"
app.config['SESSION_COOKIE_DOMAIN'] = None
app.config['SESSION_COOKIE_PATH'] = '/'
app.permanent_session_lifetime = timedelta(minutes=60)
app.config['SERVER_NAME'] = None  # Disable host checking for ngrok compatibility
os.environ['OAUTHLIB_INSECURE_TRANSPORT'] = '1'

# Allow Flask to trust forwarded headers from ngrok
from werkzeug.middleware.proxy_fix import ProxyFix
app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_proto=1, x_host=1)

login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'

users = {}

# Data cache: stores fetched Google Sheets data to avoid repeated API calls
data_cache = {}
CACHE_TIMEOUT = 300  # Cache for 5 minutes

# ============================================================================
# MODELS
# ============================================================================

class User(UserMixin):
    """User model for Flask-Login."""
    def __init__(self, id_: str):
        self.id = id_
    
    def get_id(self) -> str:
        return self.id

@login_manager.user_loader
def load_user(user_id: str) -> Optional[User]:
    """Load user from users dictionary."""
    return users.get(user_id)

# ============================================================================
# UTILITY FUNCTIONS
# ============================================================================

def get_flow(redirect_uri: str = None) -> Flow:
    """Create and return Google OAuth Flow object."""
    if redirect_uri is None:
        redirect_uri = REDIRECT_URI
    
    return Flow.from_client_secrets_file(
        CLIENT_SECRETS_FILE, 
        scopes=SCOPES, 
        redirect_uri=redirect_uri
    )

def get_google_credentials() -> Optional[Credentials]:
    """
    Get Google credentials from session and refresh if needed.
    Returns None if credentials are not available or invalid.
    """
    if 'credentials' not in session:
        return None
    
    creds_data = session['credentials']
    creds = Credentials.from_authorized_user_info(info=creds_data)
    
    if not creds.valid:
        if creds.expired and creds.refresh_token:
            try:
                creds.refresh(Request())
                session['credentials'] = {
                    'token': creds.token,
                    'refresh_token': creds.refresh_token,
                    'token_uri': creds.token_uri,
                    'client_id': creds.client_id,
                    'client_secret': creds.client_secret,
                    'scopes': creds.scopes
                }
            except Exception as e:
                print(f"Error refreshing credentials: {str(e)}")
                return None
        else:
            return None
    
    return creds

def find_column(df: pd.DataFrame, column_key: str) -> Optional[str]:
    """
    Find the actual column name in the dataframe using the column mapping.
    Returns the first matching column name or None if not found.
    """
    possible_names = COLUMN_MAPPING.get(column_key, [])
    for name in possible_names:
        if name in df.columns:
            return name
    return None

def standardize_column_names(df: pd.DataFrame) -> pd.DataFrame:
    """
    Rename columns to standardized names using the column mapping.
    """
    rename_map = {}
    for standard_name, possible_names in COLUMN_MAPPING.items():
        for name in possible_names:
            if name in df.columns:
                rename_map[name] = standard_name
                break
    
    return df.rename(columns=rename_map)

def clean_df_for_json(df: pd.DataFrame) -> pd.DataFrame:
    """
    Clean DataFrame for JSON serialization by replacing NaN and infinity values.
    """
    cleaned = df.replace([np.inf, -np.inf], None)
    cleaned = cleaned.where(pd.notna(cleaned), None)
    return cleaned.astype(object).where(pd.notna(cleaned), None)

def parse_currency(value: Any) -> Optional[float]:
    """
    Parse currency values that may be formatted as strings.
    Handles formats like "245,00 €", "245.00", "245,00", "6.005.182,69", etc.
    """
    if pd.isna(value) or value == '' or value is None:
        return 0.0  # Changed from None to 0.0 to avoid sum issues
    
    if isinstance(value, (int, float)):
        return float(value)
    
    # Convert to string and clean
    str_value = str(value).strip()
    
    if str_value == '' or str_value.lower() in ['nan', 'none', 'null']:
        return 0.0
    
    # Remove currency symbols and whitespace
    str_value = str_value.replace('€', '').replace('$', '').replace('R$', '').strip()
    str_value = str_value.replace(' ', '')  # Remove all spaces
    
    # Handle European format with thousands separators
    # Examples: 6.005.182,69 or 1.234,56 or 123,45
    if ',' in str_value:
        # Count dots and commas to determine format
        dot_count = str_value.count('.')
        comma_count = str_value.count(',')
        
        if dot_count > 0 and comma_count > 0:
            # Both exist - determine which is thousands separator
            last_dot = str_value.rfind('.')
            last_comma = str_value.rfind(',')
            
            if last_comma > last_dot:
                # European: 1.234.567,89 (dots for thousands, comma for decimal)
                str_value = str_value.replace('.', '').replace(',', '.')
            else:
                # US: 1,234,567.89 (commas for thousands, dot for decimal)
                str_value = str_value.replace(',', '')
        elif comma_count > 0:
            # Only commas - could be decimal or thousands
            # If comma is followed by exactly 2 digits at the end, it's decimal
            if ',' in str_value and len(str_value.split(',')[-1]) == 2:
                # European decimal: 1234,56
                str_value = str_value.replace(',', '.')
            else:
                # Thousands separator: 1,234 -> remove comma
                str_value = str_value.replace(',', '')
    
    try:
        result = float(str_value)
        return result if result >= 0 else 0.0
    except (ValueError, TypeError) as e:
        # Log parsing errors for debugging
        print(f"[PARSE ERROR] Failed to parse '{value}' (processed as '{str_value}'): {e}")
        return 0.0

def apply_filters(df: pd.DataFrame, filters: Dict[str, str]) -> pd.DataFrame:
    """
    Apply filters to dataframe based on provided filter dictionary.
    Handles column name variations and date range filtering automatically.
    Supports: year (quick filter), date_from/date_to (range), or mes (specific month)
    """
    if df.empty:
        return df
    
    print(f"\n{'='*80}")
    print(f"[FILTERS] Input: {len(df)} rows")
    print(f"[FILTERS] Filters received: {filters}")
    
    # Show total faturacao before filtering
    fat_col = find_column(df, 'faturacao')
    if fat_col:
        print(f"[FILTERS] Total faturacao BEFORE filters: {df[fat_col].sum():.2f}")
    
    mes_col = find_column(df, 'mes')
    
    # Priority 1: Specific month filter (overrides everything)
    mes_filter = filters.get('mes')
    if mes_filter and mes_filter != 'all' and mes_col and mes_col in df.columns:
        df = df[df[mes_col] == mes_filter]
        print(f"[FILTERS] After mes filter ({mes_filter}): {len(df)} rows")
    
    # Priority 2: Date range filter (if no specific month)
    elif mes_col and mes_col in df.columns:
        date_from = filters.get('date_from')
        date_to = filters.get('date_to')
        year_filter = filters.get('year')
        
        # Use year quick filter if set (and no date range)
        if year_filter and year_filter != 'all' and not date_from and not date_to:
            # Handle different date formats: YYYY/MM, MM/YYYY, DD/MM/YYYY, etc.
            # Check if the year appears anywhere in the date string
            print(f"[FILTERS] Applying year filter: {year_filter}")
            print(f"[FILTERS] Sample dates before filter: {df[mes_col].head(5).tolist()}")
            
            # Filter by year
            df = df[df[mes_col].astype(str).str.contains(year_filter, na=False, regex=False)]
            
            print(f"[FILTERS] After year filter ({year_filter}): {len(df)} rows")
            if len(df) > 0:
                print(f"[FILTERS] Sample dates after filter: {df[mes_col].head(5).tolist()}")
            if fat_col:
                print(f"[FILTERS] Total faturacao after year filter: {df[fat_col].sum():.2f}")
        else:
            # Use date range
            if date_from:
                df = df[df[mes_col].astype(str).str.replace('-', '/') >= date_from.replace('-', '/')]
                print(f"[FILTERS] After date_from filter ({date_from}): {len(df)} rows")
                if fat_col:
                    print(f"[FILTERS] Total faturacao: {df[fat_col].sum():.2f}")
            
            if date_to:
                df = df[df[mes_col].astype(str).str.replace('-', '/') <= date_to.replace('-', '/')]
                print(f"[FILTERS] After date_to filter ({date_to}): {len(df)} rows")
                if fat_col:
                    print(f"[FILTERS] Total faturacao: {df[fat_col].sum():.2f}")
    
    # Apply other filters
    filter_mapping = {
        'cliente': 'cliente',
        'zona': 'zona',
        'comercial': 'comercial',
        'familia': 'familia'
    }
    
    for filter_key, column_key in filter_mapping.items():
        filter_value = filters.get(filter_key)
        if filter_value and filter_value != 'all':
            col_name = find_column(df, column_key)
            if col_name and col_name in df.columns:
                before = len(df)
                df = df[df[col_name] == filter_value]
                print(f"[FILTERS] After {filter_key} filter ({filter_value}): {before} -> {len(df)} rows")
            else:
                print(f"[FILTERS] Column not found for {filter_key}")
    
    print(f"[FILTERS] Final output: {len(df)} rows")
    if fat_col and fat_col in df.columns:
        print(f"[FILTERS] FINAL total faturacao: {df[fat_col].sum():.2f}")
    print(f"{'='*80}\n")
    return df

def fetch_sheets_data(creds: Credentials) -> pd.DataFrame:
    """
    Fetch data from Google Sheets and return as pandas DataFrame.
    Uses cache to avoid repeated API calls.
    """
    try:
        SPREADSHEET_ID = session.get('spreadsheet_id')
        if not SPREADSHEET_ID:
            raise ValueError("Spreadsheet ID not configured.")
        
        # Check cache first
        cache_key = f"{current_user.id}_{SPREADSHEET_ID}"
        now = datetime.now()
        
        if cache_key in data_cache:
            cached_data, timestamp = data_cache[cache_key]
            age = (now - timestamp).total_seconds()
            if age < CACHE_TIMEOUT:
                print(f"[CACHE] Using cached data (age: {age:.1f}s)")
                return cached_data.copy()
            else:
                print(f"[CACHE] Cache expired (age: {age:.1f}s)")
        
        print("[CACHE] Fetching fresh data from Google Sheets...")
        gc = gspread.authorize(creds)
        spreadsheet = gc.open_by_key(SPREADSHEET_ID)
        
        # List all worksheets
        all_worksheets = spreadsheet.worksheets()
        print(f"[DEBUG] Found {len(all_worksheets)} worksheets/tabs:")
        for idx, ws in enumerate(all_worksheets):
            print(f"[DEBUG]   [{idx}] {ws.title} ({ws.row_count} rows x {ws.col_count} cols)")
        
        worksheet = spreadsheet.worksheets()[1]  # Use second sheet
        print(f"[DEBUG] Using worksheet: [{1}] {worksheet.title}")
        all_values = worksheet.get_all_values()
        
        if not all_values:
            return pd.DataFrame()
        
        headers = all_values[0]
        
        # Handle duplicate column names by making them unique
        seen = {}
        unique_headers = []
        for header in headers:
            if header in seen:
                seen[header] += 1
                unique_headers.append(f"{header}_{seen[header]}")
            else:
                seen[header] = 0
                unique_headers.append(header)
        
        data = all_values[1:]
        df = pd.DataFrame(data, columns=unique_headers)
        
        # Strip whitespace from column names
        df.columns = df.columns.str.strip()
        
        print(f"DataFrame columns: {df.columns.tolist()}")
        print(f"DataFrame shape: {df.shape}")
        
        # Convert numeric columns
        numeric_column_keys = ['desconto', 'prazo_pagamento', 'quantidade', 'faturacao']
        for col_key in numeric_column_keys:
            col_name = find_column(df, col_key)
            if col_name:
                print(f"[PARSE] Converting column '{col_name}' ({col_key})...")
                # Sample values before parsing
                sample_before = df[col_name].head(10).tolist()
                print(f"[PARSE] Sample values BEFORE: {sample_before}")
                
                # Use custom parser for currency values
                df[col_name] = df[col_name].apply(parse_currency)
                
                # Sample values after parsing
                sample_after = df[col_name].head(10).tolist()
                print(f"[PARSE] Sample values AFTER: {sample_after}")
                
                # Check for parsing issues
                zero_count = (df[col_name] == 0).sum()
                null_count = df[col_name].isna().sum()
                total_rows = len(df)
                print(f"[PARSE] Column '{col_name}': {zero_count} zeros, {null_count} nulls out of {total_rows} rows")
                if col_key == 'faturacao':
                    total_sum = df[col_name].sum()
                    print(f"[PARSE] Total sum of '{col_name}': {total_sum:.2f}")
        
        # DEBUG: Show sample data
        mes_col = find_column(df, 'mes')
        fat_col = find_column(df, 'faturacao')
        if mes_col and fat_col:
            print(f"\n[DEBUG] Sample dates from '{mes_col}' column:")
            print(df[mes_col].head(20).tolist())
            print(f"\n[DEBUG] Unique years found:")
            unique_dates = df[mes_col].astype(str).unique()
            years_found = set()
            for date_str in unique_dates:
                if '2024' in str(date_str):
                    years_found.add('2024')
                if '2025' in str(date_str):
                    years_found.add('2025')
                if '2026' in str(date_str):
                    years_found.add('2026')
            print(f"Years in data: {sorted(years_found)}")
            print(f"\n[DEBUG] Total faturacao in RAW data: {df[fat_col].sum():.2f}")
            
            # Show faturacao by year
            for year in sorted(years_found):
                year_df = df[df[mes_col].astype(str).str.contains(year, na=False, regex=False)]
                year_total = year_df[fat_col].sum()
                print(f"[DEBUG] Total faturacao for {year}: {year_total:.2f} ({len(year_df)} rows)")
        
        # Store in cache
        cache_key = f"{current_user.id}_{SPREADSHEET_ID}"
        data_cache[cache_key] = (df.copy(), datetime.now())
        print(f"[CACHE] Data cached for {cache_key}")
        
        return df
    
    except gspread.exceptions.APIError as e:
        print(f"Google Sheets API error: {str(e)}")
        return pd.DataFrame()
    except Exception as e:
        print(f"Error fetching sheets data: {str(e)}")
        return pd.DataFrame()

# ============================================================================
# API ROUTES
# ============================================================================

@app.route('/clear-cache')
@login_required
def clear_cache():
    """Clear data cache to force fresh data load."""
    try:
        SPREADSHEET_ID = session.get('spreadsheet_id')
        if SPREADSHEET_ID:
            cache_key = f"{current_user.id}_{SPREADSHEET_ID}"
            if cache_key in data_cache:
                del data_cache[cache_key]
                return jsonify({'success': True, 'message': 'Cache cleared'})
        return jsonify({'success': True, 'message': 'No cache to clear'})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)}), 500

@app.route('/diagnostic')
@login_required
def diagnostic():
    """Show raw data diagnostic page."""
    creds = get_google_credentials()
    if not creds:
        return "Not authenticated - please login first", 401
    
    try:
        # Clear cache first
        SPREADSHEET_ID = session.get('spreadsheet_id')
        if SPREADSHEET_ID:
            cache_key = f"{current_user.id}_{SPREADSHEET_ID}"
            if cache_key in data_cache:
                del data_cache[cache_key]
        
        # Fetch fresh data
        df = fetch_sheets_data(creds)
        
        if df.empty:
            return "<h1>No data found</h1>", 400
        
        # Find columns
        mes_col = find_column(df, 'mes')
        fat_col = find_column(df, 'faturacao')
        
        # Calculate totals by year
        results = {"columns": df.columns.tolist()}
        
        if mes_col and fat_col:
            # All data
            results['total_rows'] = len(df)
            results['total_faturacao'] = float(df[fat_col].sum())
            
            # By year
            for year in ['2024', '2025', '2026']:
                year_df = df[df[mes_col].astype(str).str.contains(year, na=False, regex=False)]
                results[f'year_{year}'] = {
                    'rows': len(year_df),
                    'faturacao': float(year_df[fat_col].sum())
                }
            
            # Sample data
            results['sample_dates'] = df[mes_col].head(10).tolist()
            results['sample_faturacao'] = df[fat_col].head(10).tolist()
        
        html = f"""
        <!DOCTYPE html>
        <html>
        <head><title>Diagnostic</title>
        <style>
            body {{ font-family: Arial; padding: 20px; background: #1a1a1a; color: #0f0; }}
            h1 {{ color: #0ff; }}
            table {{ border-collapse: collapse; width: 100%; margin: 20px 0; background: #2a2a2a; }}
            th, td {{ border: 1px solid #0f0; padding: 10px; text-align: left; }}
            th {{ background: #0f0; color: #000; font-weight: bold; }}
            .match {{ color: #0f0; font-weight: bold; }}
            .error {{ color: #f00; font-weight: bold; }}
        </style>
        </head>
        <body>
            <h1>📊 DATA DIAGNOSTIC</h1>
            
            <h2>Columns Found:</h2>
            <p>{', '.join(results['columns'])}</p>
            
            <h2>Raw Totals (ALL DATA, NO FILTERS):</h2>
            <table>
                <tr><th>Metric</th><th>Value</th></tr>
                <tr><td>Total Rows</td><td>{results.get('total_rows', 0):,}</td></tr>
                <tr><td>Total Faturação</td><td>€ {results.get('total_faturacao', 0):,.2f}</td></tr>
            </table>
            
            <h2>Totals by Year:</h2>
            <table>
                <tr><th>Year</th><th>Rows</th><th>Faturação</th><th>Status</th></tr>
                <tr>
                    <td>2024</td>
                    <td>{results.get('year_2024', {}).get('rows', 0):,}</td>
                    <td>€ {results.get('year_2024', {}).get('faturacao', 0):,.2f}</td>
                    <td>-</td>
                </tr>
                <tr>
                    <td>2025</td>
                    <td>{results.get('year_2025', {}).get('rows', 0):,}</td>
                    <td>€ {results.get('year_2025', {}).get('faturacao', 0):,.2f}</td>
                    <td class="{'match' if abs(results.get('year_2025', {}).get('faturacao', 0) - 6005182.69) < 1 else 'error'}">
                        {'✓ MATCH!' if abs(results.get('year_2025', {}).get('faturacao', 0) - 6005182.69) < 1 else '✗ WRONG (Expected: €6,005,182.69)'}
                    </td>
                </tr>
                <tr>
                    <td>2026</td>
                    <td>{results.get('year_2026', {}).get('rows', 0):,}</td>
                    <td>€ {results.get('year_2026', {}).get('faturacao', 0):,.2f}</td>
                    <td>-</td>
                </tr>
            </table>
            
            <h2>Sample Dates (First 10):</h2>
            <ul>{''.join(f'<li>{d}</li>' for d in results.get('sample_dates', []))}</ul>
            
            <h2>Sample Faturação Values (First 10):</h2>
            <ul>{''.join(f'<li>€ {v:,.2f}</li>' for v in results.get('sample_faturacao', []))}</ul>
            
            <p><a href="/dashboard" style="color: #0ff;">← Back to Dashboard</a></p>
        </body>
        </html>
        """
        return html
        
    except Exception as e:
        import traceback
        return f"<pre style='color:red'>ERROR:\n{traceback.format_exc()}</pre>", 500

@app.route('/get-summary')
@login_required
def get_summary():
    """Get summary statistics with optional filters."""
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify({
                'total_records': 0,
                'total_faturacao': 0,
                'clientes_unicos': 0,
                'total_quant': 0
            })
        
        # Apply filters
        filters = {
            'year': request.args.get('year'),
            'date_from': request.args.get('date_from'),
            'date_to': request.args.get('date_to'),
            'cliente': request.args.get('cliente'),
            'zona': request.args.get('zona'),
            'comercial': request.args.get('comercial'),
            'familia': request.args.get('familia'),
            'mes': request.args.get('mes')
        }
        
        df = apply_filters(df, filters)
        
        # Find actual column names for summary
        quant_col = find_column(df, 'quantidade')
        fat_col = find_column(df, 'faturacao')
        cliente_col = find_column(df, 'cliente')
        
        summary = {
            'total_records': len(df),
            'total_faturacao': float(df[fat_col].sum()) if fat_col and fat_col in df.columns else 0,
            'clientes_unicos': int(df[cliente_col].nunique()) if cliente_col and cliente_col in df.columns else 0,
            'total_quant': float(df[quant_col].sum()) if quant_col and quant_col in df.columns else 0
        }
        
        return jsonify(summary)
    
    except Exception as e:
        print(f"Error in get_summary: {str(e)}")
        return jsonify({
            'error': f'Failed to get summary: {str(e)}',
            'total_records': 0,
            'total_faturacao': 0,
            'clientes_unicos': 0,
            'total_quant': 0
        }), 500

@app.route('/get-sheets-data')
@login_required
def get_sheets_data():
    """Get all sheets data with optional filters."""
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify([])
        
        # Apply filters
        filters = {
            'year': request.args.get('year'),
            'date_from': request.args.get('date_from'),
            'date_to': request.args.get('date_to'),
            'cliente': request.args.get('cliente'),
            'zona': request.args.get('zona'),
            'comercial': request.args.get('comercial'),
            'familia': request.args.get('familia'),
            'mes': request.args.get('mes')
        }
        
        df = apply_filters(df, filters)
        
        if df.empty:
            return jsonify([])
        
        safe_df = clean_df_for_json(df)
        result = safe_df.to_dict(orient='records')
        return jsonify(result)
    
    except Exception as e:
        print(f"Error in get_sheets_data: {str(e)}")
        return jsonify({'error': f'Failed to load data: {str(e)}'}), 500

@app.route('/get-quantities-by-referencia')
@login_required
def get_quantities_by_referencia():
    """Get aggregated data grouped by reference."""
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify([])
        
        # Apply filters
        filters = {
            'year': request.args.get('year'),
            'cliente': request.args.get('cliente'),
            'zona': request.args.get('zona'),
            'comercial': request.args.get('comercial'),
            'familia': request.args.get('familia'),
            'mes': request.args.get('mes')
        }
        
        df = apply_filters(df, filters)
        
        if df.empty:
            return jsonify([])
        
        # Find actual column names
        ref_col = find_column(df, 'referencia')
        cod_col = find_column(df, 'codigo')
        fam_col = find_column(df, 'familia')
        quant_col = find_column(df, 'quantidade')
        fat_col = find_column(df, 'faturacao')
        
        # Check if required columns exist
        if not all([ref_col, cod_col, fam_col, quant_col, fat_col]):
            missing = []
            if not ref_col: missing.append('Referencia')
            if not cod_col: missing.append('Código')
            if not fam_col: missing.append('Familia')
            if not quant_col: missing.append('Quantidade')
            if not fat_col: missing.append('Faturação')
            
            return jsonify({
                'error': f'Missing required columns: {", ".join(missing)}. Available: {", ".join(df.columns.tolist())}'
            }), 400
        
        # Group and aggregate
        grouped = df.groupby([ref_col, cod_col, fam_col]).agg({
            quant_col: 'sum',
            fat_col: 'sum'
        }).reset_index()
        
        grouped = grouped.sort_values(quant_col, ascending=False)
        grouped = clean_df_for_json(grouped)
        result = grouped.to_dict(orient='records')
        return jsonify(result)
    
    except Exception as e:
        print(f"Error in get_quantities_by_referencia: {str(e)}")
        return jsonify({'error': f'Failed to load reference data: {str(e)}'}), 500

@app.route('/get-reference-by-client')
@login_required
def get_reference_by_client():
    """Get reference metrics grouped by client."""
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify([])
        
        filters = {
            'year': request.args.get('year'),
            'cliente': request.args.get('cliente'),
            'zona': request.args.get('zona'),
            'comercial': request.args.get('comercial'),
            'familia': request.args.get('familia'),
            'mes': request.args.get('mes')
        }
        
        df = apply_filters(df, filters)
        if df.empty:
            return jsonify([])
        
        cliente_col = find_column(df, 'cliente')
        ref_col = find_column(df, 'referencia')
        cod_col = find_column(df, 'codigo')
        fam_col = find_column(df, 'familia')
        quant_col = find_column(df, 'quantidade')
        fat_col = find_column(df, 'faturacao')
        
        if not all([cliente_col, ref_col, cod_col, fam_col, quant_col, fat_col]):
            missing = []
            if not cliente_col: missing.append('Cliente')
            if not ref_col: missing.append('Referencia')
            if not cod_col: missing.append('Código')
            if not fam_col: missing.append('Familia')
            if not quant_col: missing.append('Quantidade')
            if not fat_col: missing.append('Faturação')
            return jsonify({'error': f'Missing required columns: {", ".join(missing)}. Available: {", ".join(df.columns.tolist())}'}), 400
        
        grouped = df.groupby([cliente_col, ref_col, cod_col, fam_col]).agg({
            quant_col: 'sum',
            fat_col: 'sum'
        }).reset_index()
        
        grouped = grouped.sort_values(fat_col, ascending=False)
        grouped = clean_df_for_json(grouped)
        return jsonify(grouped.to_dict(orient='records'))
    
    except Exception as e:
        print(f"Error in get_reference_by_client: {str(e)}")
        return jsonify({'error': f'Failed to load reference by client data: {str(e)}'}), 500

@app.route('/get-quantities-by-codigo')
@login_required
def get_quantities_by_codigo():
    """Get aggregated data grouped by code."""
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify([])
        
        # Apply filters
        filters = {
            'year': request.args.get('year'),
            'cliente': request.args.get('cliente'),
            'zona': request.args.get('zona'),
            'comercial': request.args.get('comercial'),
            'familia': request.args.get('familia'),
            'mes': request.args.get('mes')
        }
        
        df = apply_filters(df, filters)
        
        if df.empty:
            return jsonify([])
        
        # Find actual column names
        cod_col = find_column(df, 'codigo')
        fam_col = find_column(df, 'familia')
        quant_col = find_column(df, 'quantidade')
        fat_col = find_column(df, 'faturacao')
        
        # Check if required columns exist
        if not all([cod_col, fam_col, quant_col, fat_col]):
            missing = []
            if not cod_col: missing.append('Código')
            if not fam_col: missing.append('Familia')
            if not quant_col: missing.append('Quantidade')
            if not fat_col: missing.append('Faturação')
            
            return jsonify({
                'error': f'Missing required columns: {", ".join(missing)}. Available: {", ".join(df.columns.tolist())}'
            }), 400
        
        # Group and aggregate
        grouped = df.groupby([cod_col, fam_col]).agg({
            quant_col: 'sum',
            fat_col: 'sum'
        }).reset_index()
        
        grouped = grouped.sort_values(quant_col, ascending=False)
        grouped = clean_df_for_json(grouped)
        result = grouped.to_dict(orient='records')
        return jsonify(result)
    
    except Exception as e:
        print(f"Error in get_quantities_by_codigo: {str(e)}")
        return jsonify({'error': f'Failed to load code data: {str(e)}'}), 500

@app.route('/get-breakdowns')
@login_required
def get_breakdowns():
    """Get breakdown by zone, comercial, or familia with percentages."""
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    
    breakdown_type = request.args.get('type', 'zona')  # zona, comercial, or familia
    
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify([])
        
        # Apply filters
        filters = {
            'year': request.args.get('year'),
            'cliente': request.args.get('cliente'),
            'zona': request.args.get('zona'),
            'comercial': request.args.get('comercial'),
            'familia': request.args.get('familia'),
            'mes': request.args.get('mes')
        }
        
        df = apply_filters(df, filters)
        
        if df.empty:
            return jsonify([])
        
        # Find columns
        group_col = find_column(df, breakdown_type)
        fat_col = find_column(df, 'faturacao')
        quant_col = find_column(df, 'quantidade')
        
        if not all([group_col, fat_col, quant_col]):
            return jsonify({'error': 'Required columns not found'}), 400
        
        # Group and calculate
        grouped = df.groupby(group_col).agg({
            fat_col: 'sum',
            quant_col: 'sum'
        }).reset_index()
        
        # Calculate percentages
        total_fat = grouped[fat_col].sum()
        grouped['percentage'] = (grouped[fat_col] / total_fat * 100) if total_fat > 0 else 0
        
        # Sort by revenue
        grouped = grouped.sort_values(fat_col, ascending=False)
        
        # Clean and format
        grouped = clean_df_for_json(grouped)
        result = grouped.to_dict(orient='records')
        
        return jsonify(result)
    
    except Exception as e:
        print(f"Error in get_breakdowns: {str(e)}")
        return jsonify({'error': f'Failed to get breakdowns: {str(e)}'}), 500

@app.route('/export-data')
@login_required
def export_data():
    """Export filtered data to Excel."""
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify({'error': 'No data to export'}), 400
        
        # Apply filters
        filters = {
            'year': request.args.get('year'),
            'cliente': request.args.get('cliente'),
            'zona': request.args.get('zona'),
            'comercial': request.args.get('comercial'),
            'familia': request.args.get('familia'),
            'mes': request.args.get('mes')
        }
        
        df = apply_filters(df, filters)
        
        if df.empty:
            return jsonify({'error': 'No data matches the filters'}), 400
        
        # Create Excel file in memory
        from io import BytesIO
        output = BytesIO()
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            df.to_excel(writer, sheet_name='Sales Data', index=False)
        
        output.seek(0)
        
        from flask import send_file
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name='sales_data_export.xlsx'
        )
    
    except Exception as e:
        print(f"Error in export_data: {str(e)}")
        return jsonify({'error': f'Failed to export: {str(e)}'}), 500

@app.route('/set-spreadsheet-id', methods=['POST'])
@login_required
def set_spreadsheet_id():
    """Save spreadsheet ID to session."""
    data = request.get_json()
    spreadsheet_id = data.get('spreadsheet_id')
    
    if not spreadsheet_id:
        return jsonify({'error': 'Spreadsheet ID is required'}), 400
    
    session['spreadsheet_id'] = spreadsheet_id
    session.modified = True
    return jsonify({'message': 'Spreadsheet ID saved successfully'})

# ============================================================================
# PAGE ROUTES
# ============================================================================

@app.route("/")
def index():
    """Landing page."""
    return render_template('index.html')

@app.route("/login")
def login():
    """Initiate Google OAuth login flow."""
    session['permanent'] = True
    session['oauth_redirect_uri'] = REDIRECT_URI
    
    flow = get_flow(REDIRECT_URI)
    authorization_url, state = flow.authorization_url(
        access_type='offline',
        prompt='consent'
    )
    session['oauth_state'] = state
    session.modified = True
    print(f"\n✓ Login - Using registered redirect_uri: {REDIRECT_URI}")
    print(f"✓ Authorization URL generated\n")
    return redirect(authorization_url)

@app.route("/oauth2callback")
def oauth2callback():
    """Handle OAuth callback from Google."""
    try:
        print(f"\n{'='*60}")
        print(f"OAuth callback received")
        print(f"Request URL: {request.url}")
        print(f"Request host: {request.host}")
        print(f"Query params: {dict(request.args)}")
        print(f"{'='*60}")
        
        # Check for error from Google
        if 'error' in request.args:
            error = request.args.get('error')
            error_description = request.args.get('error_description', 'No description')
            print(f"❌ OAuth error from Google: {error} - {error_description}\n")
            return f"OAuth Error: {error} - {error_description}", 400
        
        # Check for authorization code
        if 'code' not in request.args:
            print("❌ Missing authorization code in callback\n")
            return "Missing authorization code in callback", 400
        
        print(f"✓ Authorization code found")
        print(f"✓ Using REDIRECT_URI: {REDIRECT_URI}")
        
        # Use the registered redirect_uri
        flow = get_flow(REDIRECT_URI)
        
        try:
            flow.fetch_token(authorization_response=request.url)
            credentials = flow.credentials
            print(f"✓ Token fetched successfully")
        except Exception as token_error:
            print(f"❌ Token fetch failed: {str(token_error)}")
            import traceback
            traceback.print_exc()
            return f"Token Error: {str(token_error)}", 400
        
        session['credentials'] = {
            'token': credentials.token,
            'refresh_token': credentials.refresh_token,
            'token_uri': credentials.token_uri,
            'client_id': credentials.client_id,
            'client_secret': credentials.client_secret,
            'scopes': credentials.scopes
        }
        
        # Get user info
        try:
            user_info_response = requests.get(
                'https://www.googleapis.com/oauth2/v2/userinfo',
                headers={'Authorization': f'Bearer {credentials.token}'}
            )
            user_info = user_info_response.json()
            
            if 'error' in user_info:
                print(f"❌ User info error: {user_info.get('error')}")
                return f"Error: {user_info.get('error')}", 400
            
            user_email = user_info.get('email')
            if not user_email:
                print(f"❌ Could not get email from user info")
                return "Could not get email from Google", 400
            
            print(f"✓ User authenticated: {user_email}")
        
        except Exception as e:
            print(f"❌ Auth error: {str(e)}")
            return f"Auth error: {str(e)}", 400
        
        # Create or get user
        if user_email not in users:
            users[user_email] = User(user_email)
        
        login_user(users[user_email])
        print(f"✓ User logged in successfully\n")
        return redirect(url_for("dashboard"))
    
    except Exception as e:
        print(f"❌ OAuth callback error: {str(e)}\n")
        import traceback
        traceback.print_exc()
        return f"OAuth Error: {str(e)}", 400

@app.route("/dashboard")
@login_required
def dashboard():
    """Main dashboard page."""
    if not current_user.is_authenticated:
        return redirect(url_for('login'))
    
    creds = get_google_credentials()
    if not creds:
        return redirect(url_for('login'))
    
    try:
        sheets_data = fetch_sheets_data(creds)
        
        # Find actual column names for summary
        quant_col = find_column(sheets_data, 'quantidade')
        fat_col = find_column(sheets_data, 'faturacao')
        cliente_col = find_column(sheets_data, 'cliente')
        
        summary = {
            'total_records': len(sheets_data),
            'total_faturacao': sheets_data[fat_col].sum() if fat_col and fat_col in sheets_data else 0,
            'clientes_unicos': sheets_data[cliente_col].nunique() if cliente_col and cliente_col in sheets_data else 0,
            'total_quant': sheets_data[quant_col].sum() if quant_col and quant_col in sheets_data else 0
        }
        
        # Build filter lists
        filters = {}
        for filter_key, column_key in [
            ('clientes', 'cliente'),
            ('zonas', 'zona'),
            ('comerciais', 'comercial'),
            ('familias', 'familia'),
            ('meses', 'mes')
        ]:
            col = find_column(sheets_data, column_key)
            if col and col in sheets_data.columns:
                filters[filter_key] = sorted(sheets_data[col].unique().tolist())
            else:
                filters[filter_key] = []
        
        return render_template(
            'dashboard.html',
            summary=summary,
            filters=filters,
            user_email=current_user.id
        )
    
    except Exception as e:
        print(f"Dashboard error: {str(e)}")
        return f"Error: {str(e)}<br><a href='/logout'>Logout</a>", 500

@app.route('/get-filter-options')
@login_required
def get_filter_options():
    """Get available filter options based on current filters."""
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify({})
        
        print(f"\n[GET-FILTER-OPTIONS] Starting with {len(df)} rows")
        print(f"[GET-FILTER-OPTIONS] Query params: year={request.args.get('year')}, comercial={request.args.get('comercial')}, cliente={request.args.get('cliente')}")
        
        # Apply filters from query params
        filters = {
            'year': request.args.get('year'),
            'cliente': request.args.get('cliente'),
            'zona': request.args.get('zona'),
            'comercial': request.args.get('comercial'),
            'familia': request.args.get('familia'),
            'mes': request.args.get('mes')
        }
        
        print(f"[GET-FILTER-OPTIONS] Filters: {filters}")
        
        # Apply existing filters
        df_filtered = apply_filters(df, filters)
        
        print(f"[GET-FILTER-OPTIONS] After filtering: {len(df_filtered)} rows")
        
        # Get available options for each filter
        options = {}
        
        # Get clientes
        cliente_col = find_column(df_filtered, 'cliente')
        if cliente_col and cliente_col in df_filtered.columns:
            clientes = sorted(df_filtered[cliente_col].unique().tolist())
            options['clientes'] = clientes
            print(f"[GET-FILTER-OPTIONS] Clientes ({len(clientes)}): {clientes[:3]}...")
        else:
            options['clientes'] = []
            print(f"[GET-FILTER-OPTIONS] Clientes: Column not found!")
        
        # Get zonas
        zona_col = find_column(df_filtered, 'zona')
        if zona_col and zona_col in df_filtered.columns:
            zonas = sorted(df_filtered[zona_col].unique().tolist())
            options['zonas'] = zonas
            print(f"[GET-FILTER-OPTIONS] Zonas ({len(zonas)}): {zonas[:3]}...")
        else:
            options['zonas'] = []
        
        # Get comerciais
        comercial_col = find_column(df_filtered, 'comercial')
        if comercial_col and comercial_col in df_filtered.columns:
            comerciais = sorted(df_filtered[comercial_col].unique().tolist())
            options['comerciais'] = comerciais
            print(f"[GET-FILTER-OPTIONS] Comerciais ({len(comerciais)}): {comerciais[:3]}...")
        else:
            options['comerciais'] = []
        
        # Get familias
        familia_col = find_column(df_filtered, 'familia')
        if familia_col and familia_col in df_filtered.columns:
            familias = sorted(df_filtered[familia_col].unique().tolist())
            options['familias'] = familias
            print(f"[GET-FILTER-OPTIONS] Familias ({len(familias)}): {familias[:3]}...")
        else:
            options['familias'] = []
        
        # Get meses
        mes_col = find_column(df_filtered, 'mes')
        if mes_col and mes_col in df_filtered.columns:
            meses = sorted(df_filtered[mes_col].unique().tolist())
            options['meses'] = meses
            print(f"[GET-FILTER-OPTIONS] Meses ({len(meses)}): {meses[:3]}...")
        else:
            options['meses'] = []
        
        print(f"[GET-FILTER-OPTIONS] Returning options: clientes={len(options.get('clientes', []))}, zonas={len(options.get('zonas', []))}, comerciais={len(options.get('comerciais', []))}")
        
        return jsonify(options)
    
    except Exception as e:
        print(f"[GET-FILTER-OPTIONS] ERROR: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({'error': f'Failed to get filter options: {str(e)}'}), 500

@app.route("/logout")
@login_required
def logout():
    """Logout user and clear session."""
    session.pop('credentials', None)
    logout_user()
    return redirect(url_for("index"))

@app.route("/clearsession")
def clearsession():
    """Clear all session data (for debugging)."""
    session.clear()
    return "Session cleared!<br><a href='/'>Home</a>"

# ============================================================================
# MAIN
# ============================================================================

if __name__ == "__main__":
    app.run(debug=True)