"""
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
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'
]
REDIRECT_URI = 'https://regulative-clotilde-subflexuously.ngrok-free.dev/oauth2callback'

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

# ============================================================================
# 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)
os.environ['OAUTHLIB_INSECURE_TRANSPORT'] = '1'

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

users = {}

# ============================================================================
# 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() -> Flow:
    """Create and return Google OAuth Flow object."""
    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 apply_filters(df: pd.DataFrame, filters: Dict[str, str]) -> pd.DataFrame:
    """
    Apply filters to dataframe based on provided filter dictionary.
    Handles column name variations automatically.
    """
    if df.empty:
        return df
    
    filter_mapping = {
        'cliente': 'cliente',
        'zona': 'zona',
        'comercial': 'comercial',
        'familia': 'familia',
        'mes': 'mes'
    }
    
    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:
                df = df[df[col_name] == filter_value]
    
    return df

def fetch_sheets_data(creds: Credentials) -> pd.DataFrame:
    """
    Fetch data from Google Sheets and return as pandas DataFrame.
    Handles column name deduplication and numeric conversion.
    """
    try:
        SPREADSHEET_ID = session.get('spreadsheet_id')
        if not SPREADSHEET_ID:
            raise ValueError("Spreadsheet ID not configured.")
        
        gc = gspread.authorize(creds)
        spreadsheet = gc.open_by_key(SPREADSHEET_ID)
        worksheet = spreadsheet.worksheets()[1]  # Use second sheet
        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)
        
        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:
                df[col_name] = pd.to_numeric(df[col_name], errors='coerce')
        
        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('/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 = {
            '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 = {
            '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-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 = {
            '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('/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
    flow = get_flow()
    authorization_url, state = flow.authorization_url(
        access_type='offline',
        include_granted_scopes='true',
        prompt='consent'
    )
    session['oauth_state'] = state
    session.modified = True
    return redirect(authorization_url)

@app.route("/oauth2callback")
def oauth2callback():
    """Handle OAuth callback from Google."""
    try:
        flow = get_flow()
        flow.fetch_token(authorization_response=request.url)
        credentials = flow.credentials
        
        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:
                return f"Error: {user_info.get('error')}", 400
            
            user_email = user_info.get('email')
            if not user_email:
                return "Could not get email from Google", 400
        
        except Exception as 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])
        return redirect(url_for("dashboard"))
    
    except Exception as e:
        print(f"OAuth callback error: {str(e)}")
        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("/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)