"""
Client Intelligence Panel Helper
Provides analytics and recommendations for commercial agents visiting clients
"""

import pandas as pd
import re
from datetime import datetime, timedelta

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

def find_column(df, *keywords):
    """Find a column by keywords (case-insensitive)."""
    for col in df.columns:
        name = col.lower()
        if all(k in name for k in keywords):
            return col
    return None

def safe_numeric(value):
    """Safely convert value to float."""
    if value is None or pd.isna(value):
        return 0
    try:
        if isinstance(value, str):
            # Remove currency symbols and spaces
            val = re.sub(r'[^0-9\.,\-]', '', value)
            val = val.replace(',', '.') if '.' in val else val.replace(',', '')
            return float(val)
        return float(value)
    except:
        return 0

def parse_date(value):
    """Parse date from various formats."""
    if value is None or pd.isna(value):
        return None
    
    s = str(value).strip()
    if not s:
        return None
    
    # Try common formats
    formats = ['%Y-%m-%d', '%d-%m-%Y', '%d/%m/%Y', '%Y/%m/%d', '%d.%m.%Y']
    for fmt in formats:
        try:
            return datetime.strptime(s, fmt)
        except:
            pass
    
    # Try year-month extraction
    match = re.search(r'(\d{4})[/-](\d{1,2})', s)
    if match:
        try:
            return datetime(int(match.group(1)), int(match.group(2)), 1)
        except:
            pass
    
    return None

# ============================================================================
# CORE METRICS CALCULATION
# ============================================================================

def get_client_sales(df, client_name):
    """
    Filter data for a specific client.
    Returns: DataFrame with only this client's sales
    """
    if df is None or df.empty:
        return pd.DataFrame()
    
    cliente_col = find_column(df, 'cliente')
    if not cliente_col:
        return pd.DataFrame()
    
    client_data = df[df[cliente_col].astype(str).str.strip() == client_name].copy()
    return client_data

def parse_periods(df):
    """
    Add year, month columns to DataFrame for date-based filtering.
    Modifies df in-place.
    """
    mes_col = find_column(df, 'mês') or find_column(df, 'mes')
    
    if mes_col:
        def parse_period(value):
            if value is None:
                return (None, None)
            s = str(value).strip()
            if not s:
                return (None, None)
            s = s.replace('-', '/').replace('.', '/')
            
            # Try YYYY/MM format
            m = re.search(r"(\d{4})\D?(\d{1,2})", s)
            if m:
                return (m.group(1), m.group(2).zfill(2))
            
            # Try MM/YYYY format
            m = re.search(r"(\d{1,2})\D?(\d{4})", s)
            if m:
                return (m.group(2), m.group(1).zfill(2))
            
            return (None, None)
        
        ym = df[mes_col].apply(parse_period)
        df['__year'] = ym.apply(lambda x: x[0])
        df['__month'] = ym.apply(lambda x: x[1])
    else:
        df['__year'] = None
        df['__month'] = None
    
    return df

def compute_revenue_last_3_years(client_sales_df):
    """
    Calculate revenue breakdown for last 3 calendar years.
    Returns: {
        'year': [year_2, year_1, year_0],
        'revenue': [rev_2, rev_1, rev_0],
        'currency': '€'
    }
    """
    if client_sales_df.empty:
        return {'years': [], 'revenues': [], 'currency': '€', 'total': 0}
    
    fat_col = find_column(client_sales_df, 'fatura')
    if not fat_col:
        return {'years': [], 'revenues': [], 'currency': '€', 'total': 0}
    
    # Ensure we have year column
    if '__year' not in client_sales_df.columns:
        parse_periods(client_sales_df)
    
    current_year = datetime.now().year
    result_years = [current_year - 2, current_year - 1, current_year]
    revenues = []
    
    for year in result_years:
        year_data = client_sales_df[client_sales_df['__year'] == str(year)]
        revenue = year_data[fat_col].apply(safe_numeric).sum()
        revenues.append(revenue)
    
    return {
        'years': result_years,
        'revenues': revenues,
        'currency': '€',
        'total': sum(revenues)
    }

def compute_product_mix(client_sales_df):
    """
    Calculate revenue breakdown by product line (last 12 months).
    Returns: {
        'lines': ['line1', 'line2', ...],
        'revenues': [1000, 500, ...],
        'percentages': [66.7, 33.3, ...],
        'total': 1500
    }
    """
    if client_sales_df.empty:
        return {'lines': [], 'revenues': [], 'percentages': [], 'total': 0}
    
    fat_col = find_column(client_sales_df, 'fatura')
    familia_col = find_column(client_sales_df, 'familia') or find_column(client_sales_df, 'linha')
    
    if not fat_col or not familia_col:
        return {'lines': [], 'revenues': [], 'percentages': [], 'total': 0}
    
    # Filter to last 12 months
    if '__year' not in client_sales_df.columns:
        parse_periods(client_sales_df)
    
    current_year = str(datetime.now().year)
    current_month = str(datetime.now().month).zfill(2)
    
    # Include current month and previous 11 months
    last_12m = client_sales_df[
        (client_sales_df['__year'] == current_year) |
        (client_sales_df['__year'] == str(int(current_year) - 1))
    ].copy()
    
    # Group by product line
    grouped = last_12m.groupby(familia_col)[fat_col].apply(
        lambda x: x.apply(safe_numeric).sum()
    ).sort_values(ascending=False)
    
    total = grouped.sum()
    
    lines = grouped.index.tolist()
    revenues = grouped.values.tolist()
    percentages = [(rev / total * 100) if total > 0 else 0 for rev in revenues]
    
    return {
        'lines': lines,
        'revenues': revenues,
        'percentages': percentages,
        'total': total
    }

def compute_avg_margin(client_sales_df):
    """
    Calculate average margin (last 12 months).
    Returns: {
        'margin_pct': 25.5,
        'margin_eur': 5000,
        'available': True,
        'note': 'Based on last 12 months'
    }
    """
    if client_sales_df.empty:
        return {'margin_pct': 0, 'margin_eur': 0, 'available': False, 'note': 'Sem dados'}
    
    fat_col = find_column(client_sales_df, 'fatura')
    margin_col = find_column(client_sales_df, 'margem')
    
    # If no margin column, try to calculate from cost
    if not margin_col:
        custo_col = find_column(client_sales_df, 'custo')
        if not custo_col or not fat_col:
            return {'margin_pct': 0, 'margin_eur': 0, 'available': False, 'note': 'Margem não disponível'}
        
        # Last 12 months
        if '__year' not in client_sales_df.columns:
            parse_periods(client_sales_df)
        
        current_year = str(datetime.now().year)
        last_12m = client_sales_df[
            (client_sales_df['__year'] == current_year) |
            (client_sales_df['__year'] == str(int(current_year) - 1))
        ].copy()
        
        total_revenue = last_12m[fat_col].apply(safe_numeric).sum()
        total_cost = last_12m[custo_col].apply(safe_numeric).sum()
        margin_eur = total_revenue - total_cost
        margin_pct = (margin_eur / total_revenue * 100) if total_revenue > 0 else 0
        
        return {
            'margin_pct': round(margin_pct, 1),
            'margin_eur': round(margin_eur, 2),
            'available': True,
            'note': 'Calculada a partir do custo'
        }
    
    # If margin column exists, use it
    last_12m = client_sales_df.copy()
    if '__year' not in last_12m.columns:
        parse_periods(last_12m)
    
    current_year = str(datetime.now().year)
    last_12m = last_12m[
        (last_12m['__year'] == current_year) |
        (last_12m['__year'] == str(int(current_year) - 1))
    ]
    
    avg_margin_pct = last_12m[margin_col].apply(safe_numeric).mean()
    total_revenue = last_12m[fat_col].apply(safe_numeric).sum()
    margin_eur = total_revenue * avg_margin_pct / 100 if total_revenue > 0 else 0
    
    return {
        'margin_pct': round(avg_margin_pct, 1),
        'margin_eur': round(margin_eur, 2),
        'available': True,
        'note': 'Últimos 12 meses'
    }

def compute_purchase_frequency(client_sales_df):
    """
    Calculate average days between orders and number of orders (last 12 months).
    Returns: {
        'orders_count': 12,
        'avg_days_between': 30,
        'frequency_label': 'Mensal'
    }
    """
    if client_sales_df.empty:
        return {'orders_count': 0, 'avg_days_between': 0, 'frequency_label': 'N/A'}
    
    mes_col = find_column(client_sales_df, 'mês') or find_column(client_sales_df, 'mes')
    fat_col = find_column(client_sales_df, 'fatura')
    
    if not mes_col or not fat_col:
        return {'orders_count': 0, 'avg_days_between': 0, 'frequency_label': 'N/A'}
    
    # Parse dates and filter last 12 months
    if '__year' not in client_sales_df.columns:
        parse_periods(client_sales_df)
    
    current_year = str(datetime.now().year)
    last_12m = client_sales_df[
        (client_sales_df['__year'] == current_year) |
        (client_sales_df['__year'] == str(int(current_year) - 1))
    ].copy()
    
    # Group by period and count orders
    unique_periods = last_12m.groupby(['__year', '__month']).size()
    orders_count = len(unique_periods)
    
    if orders_count <= 1:
        return {'orders_count': orders_count, 'avg_days_between': 365, 'frequency_label': 'Irregular'}
    
    # Estimate average days between orders
    avg_days = 365 / orders_count
    
    # Label frequency
    if avg_days < 15:
        label = 'Semanal/Bi-semanal'
    elif avg_days < 40:
        label = 'Mensal'
    elif avg_days < 100:
        label = 'Trimestral'
    else:
        label = 'Anual/Irregular'
    
    return {
        'orders_count': orders_count,
        'avg_days_between': round(avg_days, 0),
        'frequency_label': label
    }

def compute_avg_order_value(client_sales_df):
    """
    Calculate average revenue per order (last 12 months).
    Returns: float (€)
    """
    if client_sales_df.empty:
        return 0
    
    fat_col = find_column(client_sales_df, 'fatura')
    mes_col = find_column(client_sales_df, 'mês') or find_column(client_sales_df, 'mes')
    
    if not fat_col or not mes_col:
        return 0
    
    # Last 12 months
    if '__year' not in client_sales_df.columns:
        parse_periods(client_sales_df)
    
    current_year = str(datetime.now().year)
    last_12m = client_sales_df[
        (client_sales_df['__year'] == current_year) |
        (client_sales_df['__year'] == str(int(current_year) - 1))
    ].copy()
    
    # Count unique order periods
    unique_periods = last_12m.groupby(['__year', '__month']).size()
    if len(unique_periods) == 0:
        return 0
    
    total_revenue = last_12m[fat_col].apply(safe_numeric).sum()
    return round(total_revenue / len(unique_periods), 2)

def get_last_visit(visit_logs_df, client_name):
    """
    Get last visit date for a client.
    visit_logs_df should have 'cliente' and 'data_visita' (or similar) columns.
    Returns: {
        'date': datetime or None,
        'date_str': 'DD/MM/YYYY' or 'Nunca visitado',
        'days_ago': int or None
    }
    """
    if visit_logs_df is None or visit_logs_df.empty:
        return {'date': None, 'date_str': 'Sem registos de visita', 'days_ago': None}
    
    cliente_col = find_column(visit_logs_df, 'cliente')
    data_col = find_column(visit_logs_df, 'data') or find_column(visit_logs_df, 'visita')
    
    if not cliente_col or not data_col:
        return {'date': None, 'date_str': 'Sem registos de visita', 'days_ago': None}
    
    client_visits = visit_logs_df[
        visit_logs_df[cliente_col].astype(str).str.strip() == client_name
    ]
    
    if client_visits.empty:
        return {'date': None, 'date_str': 'Sem registos de visita', 'days_ago': None}
    
    # Parse dates and find most recent
    dates = [parse_date(d) for d in client_visits[data_col]]
    dates = [d for d in dates if d is not None]
    
    if not dates:
        return {'date': None, 'date_str': 'Sem registos de visita', 'days_ago': None}
    
    last_visit = max(dates)
    days_ago = (datetime.now() - last_visit).days
    
    return {
        'date': last_visit,
        'date_str': last_visit.strftime('%d/%m/%Y'),
        'days_ago': days_ago
    }

# ============================================================================
# PRODUCT RECOMMENDATIONS
# ============================================================================

def get_similar_clients(all_sales_df, client_name, similarity_mode='revenue_band'):
    """
    Find clients similar to the target client.
    Modes:
      - 'revenue_band': clients with similar annual revenue (±25%)
      - 'all': return all other clients
    
    Returns: list of client names (excluding the target client)
    """
    if all_sales_df is None or all_sales_df.empty:
        return []
    
    # Get target client revenue
    target_sales = get_client_sales(all_sales_df, client_name)
    if target_sales.empty:
        return []
    
    fat_col = find_column(target_sales, 'fatura')
    if not fat_col:
        return []
    
    target_revenue = target_sales[fat_col].apply(safe_numeric).sum()
    
    # Get all clients and their revenues
    cliente_col = find_column(all_sales_df, 'cliente')
    if not cliente_col:
        return []
    
    all_clients = all_sales_df[cliente_col].unique()
    similar = []
    
    for other_client in all_clients:
        if other_client == client_name or pd.isna(other_client):
            continue
        
        other_sales = get_client_sales(all_sales_df, other_client)
        if other_sales.empty:
            continue
        
        other_revenue = other_sales[fat_col].apply(safe_numeric).sum()
        
        # Check similarity
        if similarity_mode == 'revenue_band':
            # ±25% of target revenue
            min_rev = target_revenue * 0.75
            max_rev = target_revenue * 1.25
            if min_rev <= other_revenue <= max_rev:
                similar.append(other_client)
        else:  # 'all'
            similar.append(other_client)
    
    return similar[:20]  # Limit to 20 similar clients

def recommend_missing_products(all_sales_df, client_name, similar_clients_list):
    """
    Recommend products the client doesn't buy but similar clients do.
    Returns: [
        {
            'product_line': 'Premium',
            'similar_clients_count': 8,
            'similar_clients_pct': 72,
            'reason': 'População forte em clientes de receita similar'
        },
        ...
    ]
    Maximum 5 recommendations.
    """
    if not similar_clients_list:
        return []
    
    fat_col = find_column(all_sales_df, 'fatura')
    familia_col = find_column(all_sales_df, 'familia') or find_column(all_sales_df, 'linha')
    
    if not fat_col or not familia_col:
        return []
    
    # Get client's product lines
    client_sales = get_client_sales(all_sales_df, client_name)
    client_lines = set(
        client_sales[familia_col].astype(str).str.strip().unique()
    ) if not client_sales.empty else set()
    
    # Get similar clients' product lines
    similar_lines = {}  # {line: count}
    
    for similar_client in similar_clients_list:
        similar_sales = get_client_sales(all_sales_df, similar_client)
        if not similar_sales.empty:
            lines = similar_sales[familia_col].astype(str).str.strip().unique()
            for line in lines:
                if line not in similar_lines:
                    similar_lines[line] = 0
                similar_lines[line] += 1
    
    # Find missing lines (in similar clients, but not in our client)
    missing_recommendations = []
    
    for line, count in sorted(similar_lines.items(), key=lambda x: x[1], reverse=True):
        if line not in client_lines and line:
            pct = round(count / len(similar_clients_list) * 100, 0)
            missing_recommendations.append({
                'product_line': line,
                'similar_clients_count': count,
                'similar_clients_pct': int(pct),
                'reason': f'{int(pct)}% dos clientes similares compram {line}'
            })
    
    return missing_recommendations[:5]

# ============================================================================
# COMPLETE PROFILE GENERATOR
# ============================================================================

def generate_client_intelligence_profile(all_sales_df, client_name, visit_logs_df=None):
    """
    Generate complete intelligence profile for a client.
    Returns: dict with all metrics
    """
    client_sales = get_client_sales(all_sales_df, client_name)
    
    if client_sales.empty:
        return {
            'client_name': client_name,
            'error': 'Sem dados para este cliente',
            'available': False
        }
    
    # Ensure date columns exist
    parse_periods(client_sales)
    
    profile = {
        'client_name': client_name,
        'available': True,
        'error': None,
        
        # Revenue
        'revenue_3years': compute_revenue_last_3_years(client_sales),
        
        # Product mix
        'product_mix': compute_product_mix(client_sales),
        
        # Margin
        'margin': compute_avg_margin(client_sales),
        
        # Purchase frequency
        'frequency': compute_purchase_frequency(client_sales),
        
        # Average order value
        'avg_order_value': compute_avg_order_value(client_sales),
        
        # Last visit
        'last_visit': get_last_visit(visit_logs_df, client_name) if visit_logs_df is not None else {
            'date': None,
            'date_str': 'Sem registos de visita',
            'days_ago': None
        },
        
        # Recommendations
        'recommendations': []
    }
    
    # Generate recommendations
    similar = get_similar_clients(all_sales_df, client_name)
    if similar:
        profile['recommendations'] = recommend_missing_products(all_sales_df, client_name, similar)
    
    return profile