"""
Helper Script: Create Objetivos Sheet with 2026 Targets
This script analyzes your current sales data and creates realistic 2026 objectives
"""

import gspread
import pandas as pd
import json
from datetime import datetime

# Configuration
SPREADSHEET_ID = "1ayEGU0h_R7CY55COC1U94-p0rJch109YBGvezjYjHWw"
CREDENTIALS_FILE = "credentials.json"

def load_credentials():
    """Load credentials from credentials.json"""
    try:
        with open(CREDENTIALS_FILE, 'r') as f:
            creds_json = json.load(f)
        return creds_json
    except:
        print(f"❌ Error: Could not find {CREDENTIALS_FILE}")
        print("Make sure you have the credentials.json file in the project root")
        return None

def analyze_sales_data():
    """Analyze current sales data to extract client and URNAS information"""
    print("\n📊 Analyzing current sales data...")
    
    try:
        # We'll use the gspread library with OAuth flow from the Flask app
        # For now, provide manual instructions
        print("\n⚠️  Note: This script requires your Google account authentication.")
        print("Please ensure your credentials.json is set up correctly.\n")
        
        # Try to use gspread with service account if available
        try:
            gc = gspread.service_account(filename=CREDENTIALS_FILE)
        except:
            print("❌ Could not authenticate with service account.")
            print("Please check your credentials.json file.\n")
            return None
        
        spreadsheet = gc.open_by_key(SPREADSHEET_ID)
        
        # Get the BASE sheet (usually index 1)
        worksheet = spreadsheet.worksheets()[1]
        print(f"✓ Reading from sheet: {worksheet.title}")
        
        # Get all data
        all_values = worksheet.get_all_values()
        headers = all_values[0]
        data_rows = all_values[1:]
        
        # Create DataFrame
        df = pd.DataFrame(data_rows, columns=headers)
        print(f"✓ Loaded {len(df)} rows of data\n")
        
        # Find relevant columns
        def find_col(*keywords):
            for col in df.columns:
                name = col.lower()
                if all(k in name for k in keywords):
                    return col
            return None
        
        comercial_col = find_col('comercial')
        cliente_col = find_col('cliente')
        fat_col = find_col('fatura')
        quant_col = find_col('quant')
        familia_col = find_col('familia') or find_col('família')
        
        if not all([comercial_col, cliente_col, fat_col, quant_col]):
            print("❌ Could not find required columns")
            return None
        
        # Parse numeric columns
        df[fat_col] = pd.to_numeric(df[fat_col], errors='coerce')
        df[quant_col] = pd.to_numeric(df[quant_col], errors='coerce')
        
        print("="*80)
        print("SALES DATA ANALYSIS")
        print("="*80)
        
        # Analyze by comercial and client
        analysis = {}
        
        for comercial in df[comercial_col].unique():
            if pd.isna(comercial):
                continue
            
            comercial = str(comercial).strip()
            comercial_data = df[df[comercial_col] == comercial]
            
            # Total revenue and URNAS for comercial
            total_revenue = comercial_data[fat_col].sum()
            
            # URNAS: only from Familia containing 'urna'
            urnas_data = comercial_data[
                comercial_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)
            ]
            total_urnas = urnas_data[quant_col].sum()
            
            print(f"\n👤 {comercial}")
            print(f"   Total Revenue: €{total_revenue:,.2f}")
            print(f"   Total URNAS: {total_urnas:.0f} units")
            print(f"   Clients: {comercial_data[cliente_col].nunique()}")
            
            # Analyze by client
            analysis[comercial] = {
                'total_revenue': total_revenue,
                'total_urnas': total_urnas,
                'clients': {}
            }
            
            print(f"\n   📋 Client Breakdown:")
            for client in comercial_data[cliente_col].unique():
                if pd.isna(client):
                    continue
                
                client = str(client).strip()
                client_data = comercial_data[comercial_data[cliente_col] == client]
                client_revenue = client_data[fat_col].sum()
                
                client_urnas_data = client_data[
                    client_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)
                ]
                client_urnas = client_urnas_data[quant_col].sum()
                
                analysis[comercial]['clients'][client] = {
                    'revenue': client_revenue,
                    'urnas': client_urnas
                }
                
                print(f"      • {client}: €{client_revenue:,.2f} ({client_urnas:.0f} URNAS)")
        
        return analysis
        
    except Exception as e:
        print(f"❌ Error during analysis: {e}")
        import traceback
        traceback.print_exc()
        return None

def create_objectives_sheet(analysis):
    """Create the Objetivos sheet in the spreadsheet"""
    print("\n" + "="*80)
    print("CREATING OBJETIVOS SHEET")
    print("="*80)
    
    try:
        gc = gspread.service_account(filename=CREDENTIALS_FILE)
        spreadsheet = gc.open_by_key(SPREADSHEET_ID)
        
        # Check if Objetivos sheet already exists
        sheet_exists = False
        worksheet = None
        
        for sheet in spreadsheet.worksheets():
            if sheet.title.lower() == 'objetivos':
                sheet_exists = True
                worksheet = sheet
                break
        
        if sheet_exists:
            print(f"✓ 'Objetivos' sheet already exists")
            # Clear existing data (keep headers)
            worksheet.clear()
        else:
            print(f"✓ Creating new 'Objetivos' sheet...")
            worksheet = spreadsheet.add_worksheet(title="Objetivos", rows=100, cols=5)
        
        # Prepare data for the sheet
        headers = ["Comercial", "Cliente", "Target_Valor", "Target_Urnas", "Period"]
        
        # Calculate 2026 targets (using current year data as baseline)
        # Assumptions: 5% growth for 2026
        growth_factor = 1.05
        
        data_rows = [headers]
        
        for comercial, comercial_data in analysis.items():
            # Total row
            total_revenue_target = comercial_data['total_revenue'] * growth_factor
            total_urnas_target = comercial_data['total_urnas'] * growth_factor
            
            data_rows.append([
                comercial,
                "Total",
                round(total_revenue_target, 2),
                round(total_urnas_target, 0),
                "Annual"
            ])
            
            # Client rows
            for client, client_data in comercial_data['clients'].items():
                client_revenue_target = client_data['revenue'] * growth_factor
                client_urnas_target = client_data['urnas'] * growth_factor
                
                data_rows.append([
                    comercial,
                    client,
                    round(client_revenue_target, 2),
                    round(client_urnas_target, 0),
                    "Annual"
                ])
        
        # Write headers
        worksheet.update([headers], range_name='A1')
        
        # Write data rows
        if len(data_rows) > 1:
            worksheet.update(data_rows[1:], range_name=f'A2:E{len(data_rows)}')
            print(f"✓ Wrote {len(data_rows)-1} data rows")
        
        # Format the sheet
        # Set header background color to light blue
        worksheet.format("A1:E1", {
            "backgroundColor": {
                "red": 0.4,
                "green": 0.6,
                "blue": 1.0
            },
            "textFormat": {
                "bold": True,
                "foregroundColor": {
                    "red": 1,
                    "green": 1,
                    "blue": 1
                }
            }
        })
        
        # Auto-resize columns
        worksheet.update([range(1, 6)], range_name='A1:E1')
        
        print(f"✓ Sheet formatted")
        print(f"\n✅ 'Objetivos' sheet created successfully!")
        print(f"📍 Location: {SPREADSHEET_ID}")
        print(f"📊 Data: {len(data_rows)-1} objectives (Total + Clients)")
        
        return True
        
    except Exception as e:
        print(f"❌ Error creating sheet: {e}")
        import traceback
        traceback.print_exc()
        return False

def main():
    """Main execution"""
    print("\n" + "="*80)
    print("🎯 SALES OBJECTIVES SHEET CREATOR")
    print("="*80)
    print("\nThis script will:")
    print("1. Analyze your current sales data")
    print("2. Extract revenue and URNAS by client")
    print("3. Create 2026 objectives with 5% growth projection")
    print("4. Create/update the 'Objetivos' sheet in your spreadsheet\n")
    
    # Analyze data
    analysis = analyze_sales_data()
    
    if analysis is None:
        print("\n❌ Could not analyze data. Exiting.")
        return False
    
    # Create objectives sheet
    success = create_objectives_sheet(analysis)
    
    if success:
        print("\n" + "="*80)
        print("✅ SETUP COMPLETE")
        print("="*80)
        print("\nYou can now:")
        print("1. Visit /performance to see the Performance Dashboard")
        print("2. View objectives vs actual sales achievements")
        print("3. Modify targets directly in the Google Sheet if needed")
        print("\nTo adjust the growth factor or targets:")
        print("• Edit the 'Objetivos' sheet in Google Sheets directly")
        print("• Changes take effect immediately in the dashboard")
        return True
    else:
        print("\n❌ Could not create objectives sheet. Please check your credentials.")
        return False

if __name__ == '__main__':
    success = main()
    exit(0 if success else 1)