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, json, 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
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'

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'

def get_flow():
    return Flow.from_client_secrets_file(CLIENT_SECRETS_FILE, scopes=SCOPES, redirect_uri=REDIRECT_URI)

def get_google_credentials():
    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:
            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}
        else:
            return None
    return creds

class User(UserMixin):
    def __init__(self, id_):
        self.id = id_
    def get_id(self):
        return self.id

users = {}

@login_manager.user_loader
def load_user(user_id):
    return users.get(user_id)

def fetch_sheets_data(creds):
    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]
        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()}")
        numeric_columns = ['Desconto', 'Prazo Pagamento Dias', 'Quant', 'Faturaçao']
        for col in numeric_columns:
            if col in df.columns:
                df[col] = pd.to_numeric(df[col], errors='coerce')
        return df
    except Exception as e:
        print(f"Error fetching sheets data: {str(e)}")
        return pd.DataFrame()
def clean_df_for_json(df):
    # Replace infinity values with None
    cleaned = df.replace([np.inf, -np.inf], None)
    # Replace NaN with None
    cleaned = cleaned.where(pd.notna(cleaned), None)
    return cleaned.astype(object).where(pd.notna(cleaned), None)
@app.route('/get-sheets-data')
@login_required
def get_sheets_data():
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify([])
        cliente_filter = request.args.get('cliente')
        zona_filter = request.args.get('zona')
        comercial_filter = request.args.get('comercial')
        familia_filter = request.args.get('familia')
        mes_filter = request.args.get('mes')
        if cliente_filter and cliente_filter != 'all':
            df = df[df['Cliente'] == cliente_filter]
        if zona_filter and zona_filter != 'all':
            df = df[df['Zona'] == zona_filter]
        if comercial_filter and comercial_filter != 'all':
            df = df[df['Comercial'] == comercial_filter]
        if familia_filter and familia_filter != 'all':
            df = df[df['Familia'] == familia_filter]
        if mes_filter and mes_filter != 'all':
            df = df[df['Mês'] == mes_filter]
        safe_df = clean_df_for_json(df)
        result = safe_df.to_dict(orient='records')
        return jsonify(result)
    except Exception as e:
        return jsonify({'error': str(e)}), 500
@app.route('/get-quantities-by-referencia')
@login_required
def get_quantities_by_referencia():
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify([])
        cliente_filter = request.args.get('cliente')
        zona_filter = request.args.get('zona')
        comercial_filter = request.args.get('comercial')
        familia_filter = request.args.get('familia')
        mes_filter = request.args.get('mes')
        if cliente_filter and cliente_filter != 'all':
            df = df[df['Cliente'] == cliente_filter]
        if zona_filter and zona_filter != 'all':
            df = df[df['Zona'] == zona_filter]
        if comercial_filter and comercial_filter != 'all':
            df = df[df['Comercial'] == comercial_filter]
        if familia_filter and familia_filter != 'all':
            df = df[df['Familia'] == familia_filter]
        if mes_filter and mes_filter != 'all':
            df = df[df['Mês'] == mes_filter]
        if df.empty:
            return jsonify([])
        
        # Check if required columns exist
        required_cols = ['Referencia', 'Código', 'Familia', 'Quant', 'Faturaçao']
        missing_cols = [col for col in required_cols if col not in df.columns]
        if missing_cols:
            return jsonify({'error': f'Missing columns: {", ".join(missing_cols)}. Available columns: {", ".join(df.columns.tolist())}'}), 400
        
        grouped = df.groupby(['Referencia', 'Código', 'Familia']).agg({'Quant': 'sum', 'Faturaçao': 'sum'}).reset_index()
        grouped = grouped.sort_values('Quant', 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': str(e)}), 500

@app.route('/get-quantities-by-codigo')
@login_required
def get_quantities_by_codigo():
    creds = get_google_credentials()
    if not creds:
        return jsonify({'error': 'Not authenticated'}), 401
    try:
        df = fetch_sheets_data(creds)
        if df.empty:
            return jsonify([])
        cliente_filter = request.args.get('cliente')
        zona_filter = request.args.get('zona')
        comercial_filter = request.args.get('comercial')
        familia_filter = request.args.get('familia')
        mes_filter = request.args.get('mes')
        if cliente_filter and cliente_filter != 'all':
            df = df[df['Cliente'] == cliente_filter]
        if zona_filter and zona_filter != 'all':
            df = df[df['Zona'] == zona_filter]
        if comercial_filter and comercial_filter != 'all':
            df = df[df['Comercial'] == comercial_filter]
        if familia_filter and familia_filter != 'all':
            df = df[df['Familia'] == familia_filter]
        if mes_filter and mes_filter != 'all':
            df = df[df['Mês'] == mes_filter]
        if df.empty:
            return jsonify([])
        
        # Check if required columns exist
        required_cols = ['Código', 'Familia', 'Quant', 'Faturaçao']
        missing_cols = [col for col in required_cols if col not in df.columns]
        if missing_cols:
            return jsonify({'error': f'Missing columns: {", ".join(missing_cols)}. Available columns: {", ".join(df.columns.tolist())}'}), 400
        
        grouped = df.groupby(['Código', 'Familia']).agg({'Quant': 'sum', 'Faturaçao': 'sum'}).reset_index()
        grouped = grouped.sort_values('Quant', 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': str(e)}), 500

@app.route('/set-spreadsheet-id', methods=['POST'])
@login_required
def set_spreadsheet_id():
    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'})

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

@app.route("/login")
def login():
    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():
    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}
        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
        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():
    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)
        summary = {'total_records': len(sheets_data), 'total_faturacao': sheets_data['Faturaçao'].sum() if 'Faturaçao' in sheets_data else 0, 'clientes_unicos': sheets_data['Cliente'].nunique() if 'Cliente' in sheets_data else 0, 'total_quant': sheets_data['Quant'].sum() if 'Quant' in sheets_data else 0}
        filters = {'clientes': sorted(sheets_data['Cliente'].unique().tolist()) if 'Cliente' in sheets_data else [], 'zonas': sorted(sheets_data['Zona'].unique().tolist()) if 'Zona' in sheets_data else [], 'comerciais': sorted(sheets_data['Comercial'].unique().tolist()) if 'Comercial' in sheets_data else [], 'familias': sorted(sheets_data['Familia'].unique().tolist()) if 'Familia' in sheets_data else [], 'meses': sorted(sheets_data['Mês'].unique().tolist()) if 'Mês' in sheets_data else []}
        return render_template('dashboard.html', summary=summary, filters=filters, user_email=current_user.id)
    except Exception as e:
        return f"Error: {str(e)}<br><a href='/logout'>Logout</a>", 500

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

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

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