import pandas as pd
import numpy as np
from collections import defaultdict
import os
from datetime import datetime, timedelta

# Hardcoded file path
FILE_PATH = "C:/Users/Administrator/Downloads/search-results_2025-07-11T18_11_48.csv"

def load_and_analyze_process_data():
    """
    Load CSV file and analyze process data by grouping same paths
    """
    try:
        # Read CSV file
        print("Loading CSV file...")
        df = pd.read_csv(FILE_PATH)
        
        print(f"Total records loaded (before duplicate removal): {len(df)}")
        print(f"Columns in file: {list(df.columns)}")
        
        # Remove duplicates based on key columns
        key_columns = ['src.process.image.path', 'event.time', 'event.type']
        
        # Check which columns exist for duplicate removal
        existing_key_columns = [col for col in key_columns if col in df.columns]
        
        if existing_key_columns:
            print(f"Removing duplicates based on: {existing_key_columns}")
            df = df.drop_duplicates(subset=existing_key_columns)
            print(f"Records after duplicate removal: {len(df)}")
        else:
            print("Key columns not found, removing duplicates based on all columns")
            df = df.drop_duplicates()
            print(f"Records after duplicate removal: {len(df)}")
        
        # Convert event.time to datetime if it exists
        if 'event.time' in df.columns:
            df['event.time'] = pd.to_datetime(df['event.time'], errors='coerce')
            df['date'] = df['event.time'].dt.date
            df['hour'] = df['event.time'].dt.hour
        
        # Check if required columns exist
        required_columns = ['src.process.image.path', 'event.time']
        missing_columns = [col for col in required_columns if col not in df.columns]
        
        if missing_columns:
            print(f"Warning: Missing columns: {missing_columns}")
            # Try to find similar column names
            for col in missing_columns:
                similar_cols = [c for c in df.columns if 'path' in c.lower() or 'time' in c.lower()]
                if similar_cols:
                    print(f"Similar columns found: {similar_cols}")
        
        return df
        
    except FileNotFoundError:
        print(f"Error: File not found at {FILE_PATH}")
        return None
    except Exception as e:
        print(f"Error loading file: {str(e)}")
        return None

def filter_and_group_by_path(df):
    """
    Filter and group processes by same image path
    """
    if df is None:
        return None
    
    # Clean the data - remove rows with empty paths
    df_clean = df.dropna(subset=['src.process.image.path'])
    
    # Group by process image path
    grouped = df_clean.groupby('src.process.image.path')
    
    results = {}
    
    print("\n" + "="*80)
    print("PROCESS PATH ANALYSIS")
    print("="*80)
    
    for path, group in grouped:
        if len(group) > 1:  # Only show paths that appear multiple times
            results[path] = {
                'count': len(group),
                'events': group[['event.time', 'event.type', 'src.process.integrityLevel']].copy() if 'event.type' in group.columns else group[['event.time']].copy(),
                'first_seen': group['event.time'].min() if 'event.time' in group.columns else None,
                'last_seen': group['event.time'].max() if 'event.time' in group.columns else None
            }
    
    return results

def display_results(results):
    """
    Display the filtered results in a readable format
    """
    if not results:
        print("No results to display")
        return
    
    # Sort by count (most frequent first)
    sorted_results = sorted(results.items(), key=lambda x: x[1]['count'], reverse=True)
    
    for i, (path, data) in enumerate(sorted_results, 1):
        print(f"\n{i}. PATH: {path}")
        print(f"   Count: {data['count']} occurrences")
        
        if data['first_seen'] and data['last_seen']:
            print(f"   First seen: {data['first_seen']}")
            print(f"   Last seen: {data['last_seen']}")
        
        print("   Event Timeline:")
        print("   " + "-"*60)
        
        # Display events sorted by time
        events_df = data['events'].copy()
        if 'event.time' in events_df.columns:
            events_df = events_df.sort_values('event.time')
        
        for idx, row in events_df.iterrows():
            if 'event.type' in row and 'src.process.integrityLevel' in row:
                print(f"   {row['event.time']} | {row['event.type']} | {row['src.process.integrityLevel']}")
            else:
                print(f"   {row['event.time']}")
        
        print("   " + "-"*60)

def export_filtered_data(results, output_path="filtered_process_data.csv"):
    """
    Export filtered results to Excel
    """
    if not results:
        print("No data to export")
        return
    
    # Create a list to store all filtered data
    export_data = []
    
    for path, data in results.items():
        for _, row in data['events'].iterrows():
            export_row = {
                'process_path': path,
                'count': data['count'],
                'event_time': row['event.time'],
            }
            
            # Add additional columns if they exist
            if 'event.type' in row:
                export_row['event_type'] = row['event.type']
            if 'src.process.integrityLevel' in row:
                export_row['integrity_level'] = row['src.process.integrityLevel']
            
            export_data.append(export_row)
    
    # Create DataFrame and export
    export_df = pd.DataFrame(export_data)
    export_df.to_csv(output_path, index=False)
    print(f"\nFiltered data exported to: {output_path}")

def daily_application_usage_analysis(df):
    """
    Analyze daily application usage patterns with session duration
    """
    if df is None or 'event.time' not in df.columns:
        print("Cannot perform daily analysis - event.time column not found")
        return
    
    print("\n" + "="*80)
    print("DAILY APPLICATION USAGE ANALYSIS - SESSION DURATION REPORT")
    print("="*80)
    
    # Group by date and application path
    daily_usage = df.groupby(['date', 'src.process.image.path']).agg({
        'event.time': ['min', 'max', 'count'],
        'hour': lambda x: list(set(x))  # Get unique hours
    }).reset_index()
    
    # Flatten column names
    daily_usage.columns = ['date', 'application_path', 'first_seen', 'last_seen', 'event_count', 'active_hours']
    
    # Get application name from path
    daily_usage['application_name'] = daily_usage['application_path'].apply(
        lambda x: os.path.basename(x).replace('.exe', '') if pd.notna(x) else 'Unknown'
    )
    
    # Calculate session duration
    daily_usage['session_duration'] = daily_usage['last_seen'] - daily_usage['first_seen']
    daily_usage['duration_hours'] = daily_usage['session_duration'].dt.total_seconds() / 3600
    daily_usage['duration_minutes'] = daily_usage['session_duration'].dt.total_seconds() / 60
    
    # Format times for display
    daily_usage['first_time'] = daily_usage['first_seen'].dt.strftime('%H:%M:%S')
    daily_usage['last_time'] = daily_usage['last_seen'].dt.strftime('%H:%M:%S')
    
    # Sort by date and session duration
    daily_usage = daily_usage.sort_values(['date', 'duration_hours'], ascending=[True, False])
    
    # Display results for each day
    for date in daily_usage['date'].unique():
        day_data = daily_usage[daily_usage['date'] == date]
        
        print(f"\n📅 DATE: {date}")
        print("-" * 90)
        
        total_apps = len(day_data)
        longest_session = day_data['duration_hours'].max()
        
        print(f"Total Applications: {total_apps}")
        print(f"Longest Session: {longest_session:.1f} hours")
        
        print(f"\n{'Application':<25} | {'First Active':<10} | {'Last Active':<10} | {'Duration':<12} | {'Events':<8}")
        print("-" * 90)
        
        for idx, row in day_data.iterrows():
            app_name = row['application_name']
            first_time = row['first_time']
            last_time = row['last_time']
            events = row['event_count']
            
            # Format duration
            if row['duration_hours'] < 1:
                duration_str = f"{row['duration_minutes']:.0f} min"
            else:
                hours = int(row['duration_hours'])
                minutes = int((row['duration_hours'] - hours) * 60)
                duration_str = f"{hours}h {minutes}m"
            
            print(f"{app_name:<25} | {first_time:<10} | {last_time:<10} | {duration_str:<12} | {events:<8}")
    
    return daily_usage

def application_session_report(df, target_date=None, target_app=None):
    """
    Generate detailed session report for specific application and date
    """
    if df is None or 'event.time' not in df.columns:
        return
    
    print("\n" + "="*80)
    print("DETAILED APPLICATION SESSION REPORT")
    print("="*80)
    
    # Filter by target date if specified
    if target_date:
        df_filtered = df[df['date'] == pd.to_datetime(target_date).date()]
        if df_filtered.empty:
            print(f"No data found for date: {target_date}")
            return
    else:
        df_filtered = df
    
    # Filter by target application if specified
    if target_app:
        df_filtered = df_filtered[df_filtered['src.process.image.path'].str.contains(target_app, case=False, na=False)]
        if df_filtered.empty:
            print(f"No data found for application: {target_app}")
            return
    
    # Group by date and application
    session_data = df_filtered.groupby(['date', 'src.process.image.path']).agg({
        'event.time': ['min', 'max', 'count', list]
    }).reset_index()
    
    # Flatten columns
    session_data.columns = ['date', 'application_path', 'first_activity', 'last_activity', 'total_events', 'all_events']
    
    session_data['application_name'] = session_data['application_path'].apply(
        lambda x: os.path.basename(x).replace('.exe', '') if pd.notna(x) else 'Unknown'
    )
    
    # Calculate session details
    session_data['session_duration'] = session_data['last_activity'] - session_data['first_activity']
    session_data['duration_hours'] = session_data['session_duration'].dt.total_seconds() / 3600
    session_data['duration_minutes'] = session_data['session_duration'].dt.total_seconds() / 60
    
    # Format times
    session_data['login_time'] = session_data['first_activity'].dt.strftime('%H:%M:%S')
    session_data['logout_time'] = session_data['last_activity'].dt.strftime('%H:%M:%S')
    
    # Display detailed report
    for _, row in session_data.iterrows():
        app_name = row['application_name']
        date = row['date']
        login_time = row['login_time']
        logout_time = row['logout_time']
        total_events = row['total_events']
        
        print(f"\n📱 APPLICATION: {app_name}")
        print(f"📅 DATE: {date}")
        print(f"🕐 LOGIN TIME: {login_time}")
        print(f"🕐 LOGOUT TIME: {logout_time}")
        
        # Calculate and display duration
        if row['duration_hours'] < 1:
            duration_display = f"{row['duration_minutes']:.0f} minutes"
        else:
            hours = int(row['duration_hours'])
            minutes = int((row['duration_hours'] - hours) * 60)
            duration_display = f"{hours} hours {minutes} minutes"
        
        print(f"⏱️  TOTAL DURATION: {duration_display}")
        print(f"📊 TOTAL EVENTS: {total_events}")
        
        # Show activity timeline (first 10 events)
        print(f"\n📈 ACTIVITY TIMELINE (first 10 events):")
        events_sorted = sorted(row['all_events'])[:10]
        for event_time in events_sorted:
            print(f"   {event_time.strftime('%H:%M:%S')}")
        
        if len(row['all_events']) > 10:
            print(f"   ... and {len(row['all_events']) - 10} more events")
        
        print("-" * 60)
    
    return session_data

def export_session_report(daily_usage, output_path="application_session_report.csv"):
    """
    Export detailed session report to CSV
    """
    if daily_usage is None:
        return
    
    # Create export data with session details
    export_data = daily_usage.copy()
    
    # Format duration for export
    export_data['duration_formatted'] = export_data.apply(
        lambda row: f"{int(row['duration_hours'])}h {int((row['duration_hours'] - int(row['duration_hours'])) * 60)}m" 
        if row['duration_hours'] >= 1 
        else f"{int(row['duration_minutes'])}m", axis=1
    )
    
    # Select columns for export
    export_columns = [
        'date', 'application_name', 'first_time', 'last_time', 
        'duration_formatted', 'duration_hours', 'event_count', 'application_path'
    ]
    
    export_data = export_data[export_columns]
    export_data.to_csv(output_path, index=False)
    print(f"\nSession report exported to: {output_path}")

def search_specific_app_date(df, app_name, target_date):
    """
    Search for specific application on a specific date
    """
    if df is None:
        return
    
    print(f"\n🔍 SEARCHING FOR: {app_name} on {target_date}")
    print("="*60)
    
    # Convert target_date to datetime
    target_date_dt = pd.to_datetime(target_date).date()
    
    # Filter data
    filtered_df = df[
        (df['date'] == target_date_dt) & 
        (df['src.process.image.path'].str.contains(app_name, case=False, na=False))
    ]
    
    if filtered_df.empty:
        print(f"❌ No activity found for {app_name} on {target_date}")
        return
    
    # Generate session report for this specific case
    session_report = application_session_report(filtered_df)
    
    return session_report

def hourly_application_analysis(df):
    """
    Analyze hourly application usage patterns
    """
    if df is None or 'event.time' not in df.columns:
        return
    
    print("\n" + "="*80)
    print("HOURLY APPLICATION PATTERNS")
    print("="*80)
    
    # Group by hour and application
    hourly_usage = df.groupby(['hour', 'src.process.image.path']).size().reset_index(name='event_count')
    hourly_usage['application_name'] = hourly_usage['src.process.image.path'].apply(
        lambda x: os.path.basename(x).replace('.exe', '') if pd.notna(x) else 'Unknown'
    )
    
    # Find peak hours for each application
    peak_hours = hourly_usage.groupby('application_name')['event_count'].idxmax()
    
    print("\nPeak Activity Hours by Application:")
    for app in hourly_usage['application_name'].unique():
        app_data = hourly_usage[hourly_usage['application_name'] == app]
        peak_hour = app_data.loc[app_data['event_count'].idxmax(), 'hour']
        peak_count = app_data['event_count'].max()
        total_events = app_data['event_count'].sum()
        
        if total_events > 5:  # Only show apps with significant activity
            print(f"  {app:<25} | Peak: {peak_hour:02d}:00 ({peak_count} events) | Total: {total_events}")

def export_daily_summary(daily_usage, output_path="daily_application_summary.csv"):
    """
    Export daily application usage summary
    """
    if daily_usage is None:
        return
    
    # Create summary with readable format
    summary = daily_usage.copy()
    summary['active_hours_range'] = summary['active_hours'].apply(
        lambda x: f"{min(x):02d}:00-{max(x):02d}:59" if x else "N/A"
    )
    
    summary = summary[['date', 'application_name', 'event_count', 'active_hours_range', 'application_path']]
    summary.to_csv(output_path, index=False)
    print(f"\nDaily summary exported to: {output_path}")

def get_top_applications_summary(df):
    """
    Get overall top applications summary
    """
    if df is None:
        return
    
    print("\n" + "="*80)
    print("OVERALL TOP APPLICATIONS SUMMARY")
    print("="*80)
    
    # Group by application path
    app_summary = df.groupby('src.process.image.path').agg({
        'event.time': 'count',
        'date': 'nunique'
    }).reset_index()
    
    app_summary.columns = ['application_path', 'total_events', 'days_active']
    app_summary['application_name'] = app_summary['application_path'].apply(
        lambda x: os.path.basename(x).replace('.exe', '') if pd.notna(x) else 'Unknown'
    )
    
    # Sort by total events
    app_summary = app_summary.sort_values('total_events', ascending=False)
    
    print(f"\nTop 15 Most Active Applications:")
    print("-" * 70)
    
    for idx, row in app_summary.head(15).iterrows():
        app_name = row['application_name']
        events = row['total_events']
        days = row['days_active']
        avg_per_day = events / days if days > 0 else 0
        
        print(f"{app_name:<25} | {events:>5} events | {days:>2} days | Avg: {avg_per_day:>5.1f}/day")

def main():
    print("Process Data Filter - Session Duration Analysis")
    print("="*60)
    
    # Load data
    df = load_and_analyze_process_data()
    if df is None:
        return
    
    # Filter and group data
    results = filter_and_group_by_path(df)
    
    # Display results
    display_results(results)
    
    # Daily application usage analysis with session duration
    daily_usage = daily_application_usage_analysis(df)
    
    # Detailed session report
    print("\n" + "="*80)
    print("GENERATING DETAILED SESSION REPORTS")
    print("="*80)
    
    # Example: Search for Adobe on specific date
    search_specific_app_date(df, "adobe", "2025-07-11")
    
    # Example: Search for Chrome on specific date
    search_specific_app_date(df, "chrome", "2025-07-11")
    
    # Overall top applications summary
    get_top_applications_summary(df)
    
    # Export session report
    if daily_usage is not None:
        export_session_report(daily_usage)
    
    # Export filtered data
    export_filtered_data(results)
    
    print(f"\n🎉 Processing complete!")
    print(f"📊 Session duration analysis completed.")
    print(f"📁 Files generated:")
    print(f"   - application_session_report.csv (Session durations)")
    print(f"   - filtered_process_data.csv (Filtered data)")
    print(f"\n💡 Example usage patterns:")
    print(f"   - Adobe: Login 09:30, Logout 20:16, Duration: 10h 46m")
    print(f"   - Chrome: Login 08:15, Logout 18:45, Duration: 10h 30m")

if __name__ == "__main  __":
    main()  in this now the excel have agent.uuid	event.type	event.time	src.process.uid	src.process.storyline.id	src.process.user	src.process.signedStatus	src.process.publisher	osSrc.process.uid	src.process.parent.uid	tgt.file.path	tgt.file.type	tgt.file.size
thwse columns