firewall_csv_output.py

import pandas as pd
import os
from datetime import datetime
import numpy as np
import warnings

# Suppress warnings
warnings.filterwarnings('ignore')

def create_output_directory():
    """Create output directory"""
    output_dir = "./Pivot"
    os.makedirs(output_dir, exist_ok=True)
    return output_dir

def load_csv_data():
    """Load multiple CSV files and combine them"""
    try:
        print("Loading multiple CSV files...")
        
        # Define the CSV file names
        file_names = [
            "cisco_0818_0800-0810 (Asia_Tokyo).csv",
            "cisco_0818_0811-0820 (Asia_Tokyo).csv", 
            "cisco_0818_0821-0830 (Asia_Tokyo).csv",
            "cisco_0818_0831-0840 (Asia_Tokyo).csv",
            "cisco_0818_0841-0850 (Asia_Tokyo).csv",
            "cisco_0818_0851-0900 (Asia_Tokyo).csv"
        ]
        
        # Check what CSV files are in the directory
        print(f"\nScanning current directory: {os.getcwd()}")
        print("All CSV files found:")
        try:
            all_files = os.listdir('.')
            csv_files = [f for f in all_files if f.lower().endswith('.csv')]
            if csv_files:
                for i, f in enumerate(csv_files, 1):
                    print(f"  {i:2d}. {f}")
            else:
                print("  No CSV files found in current directory!")
        except Exception as e:
            print(f"  Error listing directory: {e}")
        
        # Try to find files with pattern matching
        potential_matches = []
        
        try:
            all_files = os.listdir('.')
            for target_file in file_names:
                if target_file in all_files:
                    potential_matches.append(target_file)
                    continue
                
                base_name = target_file.replace('.csv', '').replace(' (Asia_Tokyo)', '')
                similar_files = []
                for f in all_files:
                    if f.lower().endswith('.csv') and base_name.lower() in f.lower():
                        similar_files.append(f)
                
                for sf in similar_files:
                    if sf not in potential_matches:
                        potential_matches.append(sf)
        
        except Exception as e:
            print(f"Error in pattern matching: {e}")
        
        if potential_matches:
            files_to_load = potential_matches
        else:
            files_to_load = file_names
        
        combined_df = pd.DataFrame()
        total_files_loaded = 0
        
        for file_name in files_to_load:
            if not os.path.exists(file_name):
                continue
                
            try:
                print(f"\nLoading: {file_name}")
                df = pd.read_csv(file_name)
                print(f"  Loaded rows: {len(df):,}")
                
                df['Source_File'] = file_name
                combined_df = pd.concat([combined_df, df], ignore_index=True)
                total_files_loaded += 1
                
            except Exception as e:
                print(f"  Error loading {file_name}: {e}")
                continue
        
        if total_files_loaded == 0:
            print("No files were successfully loaded!")
            return None
            
        print(f"Total combined rows: {len(combined_df):,}")
        return combined_df
        
    except Exception as e:
        print(f"Overall file loading error: {e}")
        return None

def filter_firewall_data(df):
    """Filter data to firewall entries only"""
    print("\n=== Filtering Firewall Data ===")
    
    # Find Type column
    type_col = None
    for col in df.columns:
        if 'type' in col.lower() and col != 'Content Type':
            type_col = col
            break
    
    if type_col:
        print(f"Found Type column: '{type_col}'")
        type_values = df[type_col].value_counts()
        print(f"Values in {type_col}:")
        for type_val, count in type_values.items():
            print(f"  {type_val}: {count:,}")
        
        # Filter for firewall data
        firewall_variations = ['Firewall', 'firewall', 'FIREWALL']
        firewall_df = None
        
        for variation in firewall_variations:
            temp_df = df[df[type_col] == variation]
            if not temp_df.empty:
                firewall_df = temp_df.copy()
                print(f"\nFiltered to firewall data: {len(firewall_df):,} records")
                break
        
        return firewall_df
    
    print("No Type column found")
    return None

def filter_proxy_data(df):
    """Filter data to proxy entries only"""
    print("\n=== Filtering Proxy Data ===")
    
    # Find Type column
    type_col = None
    for col in df.columns:
        if 'type' in col.lower() and col != 'Content Type':
            type_col = col
            break
    
    if type_col:
        print(f"Found Type column: '{type_col}'")
        
        # Filter for proxy data
        proxy_variations = ['Proxy', 'proxy', 'PROXY']
        proxy_df = None
        
        for variation in proxy_variations:
            temp_df = df[df[type_col] == variation]
            if not temp_df.empty:
                proxy_df = temp_df.copy()
                print(f"\nFiltered to proxy data: {len(proxy_df):,} records")
                break
        
        return proxy_df
    
    print("No Type column found")
    return None

def get_identity_data(data_df, data_type="firewall"):
    """Get AD Users and Network Tunnels data"""
    print(f"\n=== Analyzing {data_type.title()} Identity Data ===")
    
    # Find identity type column
    identity_type_col = None
    for col in data_df.columns:
        if any(keyword in col.lower() for keyword in ['identity type', 'identity_type']):
            identity_type_col = col
            break
    
    if not identity_type_col:
        print("No identity type column found")
        return None, None, None
    
    print(f"Using identity type column: '{identity_type_col}'")
    
    # Get unique identity types
    identity_types = data_df[identity_type_col].value_counts()
    print(f"Identity types found:")
    for identity_type, count in identity_types.items():
        print(f"  {identity_type}: {count:,}")
    
    # Get AD Users data
    ad_users_data = data_df[data_df[identity_type_col] == 'AD Users']
    print(f"AD Users records: {len(ad_users_data):,}")
    
    # Get Network Tunnels data
    network_tunnels_data = data_df[data_df[identity_type_col] == 'Network Tunnels']
    print(f"Network Tunnels records: {len(network_tunnels_data):,}")
    
    return ad_users_data, network_tunnels_data, identity_type_col

def create_firewall_categories_with_identities_csv(firewall_df, output_dir):
    """Create detailed breakdown of top 10 firewall categories with identity information"""
    print("Creating firewall categories with identities breakdown CSV...")
    
    if firewall_df.empty or 'Categories' not in firewall_df.columns:
        print("No firewall categories data available")
        return
    
    # Get total count including null values
    total_count = len(firewall_df)
    
    # Get categories counts (excluding null values)
    categories_with_data = firewall_df['Categories'].dropna()
    categories_counts = categories_with_data.value_counts()
    
    # Calculate null/missing count
    null_count = total_count - len(categories_with_data)
    
    # Get top 10 categories
    top10_categories = categories_counts.head(10)
    others_count = categories_counts.iloc[10:].sum() if len(categories_counts) > 10 else 0
    
    # Prepare detailed breakdown data
    breakdown_data = []
    
    # Find identity columns
    identity_type_col = None
    identities_col = None
    
    for col in firewall_df.columns:
        if any(keyword in col.lower() for keyword in ['identity type', 'identity_type']):
            identity_type_col = col
        if 'identities' in col.lower():
            identities_col = col
    
    if not identity_type_col:
        print("Warning: No identity type column found - creating basic breakdown")
        # Create basic breakdown without identity details
        for category, count in top10_categories.items():
            percentage = (count / total_count) * 100
            breakdown_data.append({
                'Category': category,
                'Identity_Type': 'Unknown',
                'Identity_Name': 'Unknown',
                'Count': count,
                'Percentage': round(percentage, 2)
            })
    else:
        # Create detailed breakdown with identity information
        for category in top10_categories.index:
            category_data = firewall_df[firewall_df['Categories'] == category]
            
            # Group by identity type
            identity_type_counts = category_data[identity_type_col].value_counts()
            
            for identity_type, identity_count in identity_type_counts.items():
                if identity_type == 'Network Tunnels' and identities_col:
                    # Further break down by specific Network Tunnels identities
                    nt_data = category_data[category_data[identity_type_col] == 'Network Tunnels']
                    nt_identities = nt_data[identities_col].dropna().value_counts()
                    
                    if len(nt_identities) > 0:
                        for nt_identity, nt_count in nt_identities.items():
                            percentage = (nt_count / total_count) * 100
                            breakdown_data.append({
                                'Category': category,
                                'Identity_Type': 'Network Tunnels',
                                'Identity_Name': nt_identity,
                                'Count': nt_count,
                                'Percentage': round(percentage, 2)
                            })
                    
                    # Handle Network Tunnels with missing identity names
                    nt_missing = len(nt_data) - len(nt_data[identities_col].dropna())
                    if nt_missing > 0:
                        percentage = (nt_missing / total_count) * 100
                        breakdown_data.append({
                            'Category': category,
                            'Identity_Type': 'Network Tunnels',
                            'Identity_Name': 'Unknown Network Tunnel',
                            'Count': nt_missing,
                            'Percentage': round(percentage, 2)
                        })
                else:
                    # AD Users or other identity types
                    percentage = (identity_count / total_count) * 100
                    breakdown_data.append({
                        'Category': category,
                        'Identity_Type': identity_type,
                        'Identity_Name': identity_type,
                        'Count': identity_count,
                        'Percentage': round(percentage, 2)
                    })
    
    # Add "Others" category if there are categories beyond top 10
    if others_count > 0:
        others_percentage = (others_count / total_count) * 100
        breakdown_data.append({
            'Category': 'Others',
            'Identity_Type': 'Mixed',
            'Identity_Name': 'Mixed',
            'Count': others_count,
            'Percentage': round(others_percentage, 2)
        })
    
    # Add null/missing categories if they exist
    if null_count > 0:
        null_percentage = (null_count / total_count) * 100
        breakdown_data.append({
            'Category': 'Unknown/Missing Category',
            'Identity_Type': 'Unknown',
            'Identity_Name': 'Unknown',
            'Count': null_count,
            'Percentage': round(null_percentage, 2)
        })
    
    # Save detailed breakdown CSV
    if breakdown_data:
        df_output = pd.DataFrame(breakdown_data)
        output_path = os.path.join(output_dir, "firewall_categories_with_identities_breakdown.csv")
        df_output.to_csv(output_path, index=False)
        print(f"Firewall categories with identities breakdown CSV saved: {output_path}")
        print(f"  Total records: {total_count:,}, Breakdown entries: {len(breakdown_data)}")
        
        # Also create a summary for easy plotting
        summary_data = []
        for category in top10_categories.index:
            category_total = top10_categories[category]
            category_percentage = (category_total / total_count) * 100
            summary_data.append({
                'Category': category,
                'Count': category_total,
                'Percentage': round(category_percentage, 2)
            })
        
        # Add Others and Missing
        if others_count > 0:
            summary_data.append({
                'Category': 'Others',
                'Count': others_count,
                'Percentage': round((others_count / total_count) * 100, 2)
            })
        
        if null_count > 0:
            summary_data.append({
                'Category': 'Unknown/Missing Category',
                'Count': null_count,
                'Percentage': round((null_count / total_count) * 100, 2)
            })
        
        summary_df = pd.DataFrame(summary_data)
        summary_path = os.path.join(output_dir, "firewall_top10_categories_summary.csv")
        summary_df.to_csv(summary_path, index=False)
        print(f"Firewall top 10 categories summary CSV saved: {summary_path}")

def create_identity_breakdown_csv(data_df, ad_users_data, network_tunnels_data, output_dir, data_type="firewall"):
    """Create identity breakdown CSV"""
    print(f"Creating {data_type} identity breakdown CSV...")
    
    total_records = len(data_df)
    ad_users_count = len(ad_users_data)
    network_tunnels_count = len(network_tunnels_data)
    other_count = total_records - ad_users_count - network_tunnels_count
    
    breakdown_data = []
    
    # AD Users
    if ad_users_count > 0:
        ad_percentage = (ad_users_count / total_records) * 100
        breakdown_data.append({
            'Category': 'AD Users',
            'Count': ad_users_count,
            'Percentage': round(ad_percentage, 2)
        })
    
    # Network Tunnels by Identities (preserve original case)
    if not network_tunnels_data.empty and 'Identities' in network_tunnels_data.columns:
        # Check for missing identities in Network Tunnels data
        identities_with_data = network_tunnels_data['Identities'].dropna()
        missing_identities_count = len(network_tunnels_data) - len(identities_with_data)
        
        identities_counts = identities_with_data.value_counts()
        for identity, count in identities_counts.items():
            percentage = (count / total_records) * 100
            breakdown_data.append({
                'Category': f'Network Tunnels - {identity}',
                'Count': count,
                'Percentage': round(percentage, 2)
            })
        
        # Add missing identities if significant
        if missing_identities_count > 0:
            missing_percentage = (missing_identities_count / total_records) * 100
            breakdown_data.append({
                'Category': 'Network Tunnels - Unknown/Missing Identity',
                'Count': missing_identities_count,
                'Percentage': round(missing_percentage, 2)
            })
    
    # Other identity types
    if other_count > 0:
        other_percentage = (other_count / total_records) * 100
        breakdown_data.append({
            'Category': 'Other Identity Types',
            'Count': other_count,
            'Percentage': round(other_percentage, 2)
        })
    
    # Save CSV
    if breakdown_data:
        df_output = pd.DataFrame(breakdown_data)
        output_path = os.path.join(output_dir, f"{data_type}_identity_breakdown.csv")
        df_output.to_csv(output_path, index=False)
        print(f"{data_type.title()} identity breakdown CSV saved: {output_path}")
        
        # Debug information
        if not network_tunnels_data.empty and 'Identities' in network_tunnels_data.columns:
            identities_with_data = network_tunnels_data['Identities'].dropna()
            missing_identities_count = len(network_tunnels_data) - len(identities_with_data)
            print(f"  Network Tunnels records: {len(network_tunnels_data):,}, With identities: {len(identities_with_data):,}, Missing identities: {missing_identities_count:,}")

def create_categories_breakdown_csv(data, output_prefix, output_dir):
    """Create categories breakdown CSV for any dataset"""
    if data.empty:
        print(f"No data for {output_prefix}")
        return
    
    if 'Categories' not in data.columns:
        print(f"Categories column not found for {output_prefix}")
        return
    
    # Get total count including null values
    total_count = len(data)
    
    # Get categories counts (excluding null values)
    categories_with_data = data['Categories'].dropna()
    categories_counts = categories_with_data.value_counts()
    
    # Calculate null/missing count
    null_count = total_count - len(categories_with_data)
    
    breakdown_data = []
    for category, count in categories_counts.items():
        percentage = (count / total_count) * 100  # Use total_count for percentage
        breakdown_data.append({
            'Category': category,
            'Count': count,
            'Percentage': round(percentage, 2)
        })
    
    # Add null/missing categories if they exist
    if null_count > 0:
        null_percentage = (null_count / total_count) * 100
        breakdown_data.append({
            'Category': 'Unknown/Missing Category',
            'Count': null_count,
            'Percentage': round(null_percentage, 2)
        })
    
    df_output = pd.DataFrame(breakdown_data)
    output_path = os.path.join(output_dir, f"{output_prefix}_categories_breakdown.csv")
    df_output.to_csv(output_path, index=False)
    print(f"{output_prefix} categories breakdown CSV saved: {output_path}")
    print(f"  Total records: {total_count:,}, Records with categories: {len(categories_with_data):,}, Missing categories: {null_count:,}")

def create_protocol_breakdown_csv(data, output_prefix, output_dir):
    """Create protocol breakdown CSV for any dataset"""
    if data.empty:
        print(f"No data for {output_prefix}")
        return
    
    if 'Protocol' not in data.columns:
        print(f"Protocol column not found for {output_prefix}")
        return
    
    # Get total count including null values
    total_count = len(data)
    
    # Get protocol counts (excluding null values)
    protocols_with_data = data['Protocol'].dropna()
    protocol_counts = protocols_with_data.value_counts()
    
    # Calculate null/missing count
    null_count = total_count - len(protocols_with_data)
    
    breakdown_data = []
    for protocol, count in protocol_counts.items():
        percentage = (count / total_count) * 100  # Use total_count for percentage
        breakdown_data.append({
            'Protocol': protocol,
            'Count': count,
            'Percentage': round(percentage, 2)
        })
    
    # Add null/missing protocols if they exist
    if null_count > 0:
        null_percentage = (null_count / total_count) * 100
        breakdown_data.append({
            'Protocol': 'Unknown/Missing Protocol',
            'Count': null_count,
            'Percentage': round(null_percentage, 2)
        })
    
    df_output = pd.DataFrame(breakdown_data)
    output_path = os.path.join(output_dir, f"{output_prefix}_protocol_breakdown.csv")
    df_output.to_csv(output_path, index=False)
    print(f"{output_prefix} protocol breakdown CSV saved: {output_path}")
    print(f"  Total records: {total_count:,}, Records with protocols: {len(protocols_with_data):,}, Missing protocols: {null_count:,}")

def create_network_tunnels_identities_breakdown_csv(network_tunnels_data, output_dir):
    """Create detailed breakdown for each Network Tunnels identity"""
    if network_tunnels_data.empty or 'Identities' not in network_tunnels_data.columns:
        print("No Network Tunnels identities data")
        return
    
    identities = network_tunnels_data['Identities'].dropna().unique()
    
    for identity in identities:
        identity_data = network_tunnels_data[network_tunnels_data['Identities'] == identity]
        if not identity_data.empty:
            # Keep original identity name, only replace special characters for filename
            safe_identity = str(identity).replace('/', '_').replace('\\', '_').replace(':', '_')
            create_categories_breakdown_csv(identity_data, f"nt_{safe_identity}", output_dir)

def create_action_breakdown_csv(data, output_prefix, output_dir):
    """Create action breakdown CSV for any dataset"""
    if data.empty:
        print(f"No data for {output_prefix}")
        return
    
    if 'Action' not in data.columns:
        print(f"Action column not found for {output_prefix}")
        return
    
    # Get total count including null values
    total_count = len(data)
    
    # Get action counts (excluding null values)
    actions_with_data = data['Action'].dropna()
    action_counts = actions_with_data.value_counts()
    
    # Calculate null/missing count
    null_count = total_count - len(actions_with_data)
    
    breakdown_data = []
    for action, count in action_counts.items():
        percentage = (count / total_count) * 100  # Use total_count for percentage
        breakdown_data.append({
            'Action': action,
            'Count': count,
            'Percentage': round(percentage, 2)
        })
    
    # Add null/missing actions if they exist
    if null_count > 0:
        null_percentage = (null_count / total_count) * 100
        breakdown_data.append({
            'Action': 'Unknown/Missing Action',
            'Count': null_count,
            'Percentage': round(null_percentage, 2)
        })
    
    df_output = pd.DataFrame(breakdown_data)
    output_path = os.path.join(output_dir, f"{output_prefix}_action_breakdown.csv")
    df_output.to_csv(output_path, index=False)
    print(f"{output_prefix} action breakdown CSV saved: {output_path}")
    print(f"  Total records: {total_count:,}, Records with actions: {len(actions_with_data):,}, Missing actions: {null_count:,}")

def create_allowed_rules_breakdown_csv(data, output_prefix, output_dir):
    """Create allowed rules breakdown CSV for any dataset"""
    if data.empty:
        print(f"No data for {output_prefix}")
        return
    
    # Filter for allowed actions only
    if 'Action' not in data.columns:
        print(f"Action column not found for {output_prefix}")
        return
    
    allowed_data = data[data['Action'] == 'Allowed']
    if allowed_data.empty:
        print(f"No allowed records found for {output_prefix}")
        return
    
    if 'Rule Name' not in allowed_data.columns:
        print(f"Rule Name column not found for {output_prefix}")
        return
    
    # Get total count including null values
    total_count = len(allowed_data)
    
    # Get rule name counts (excluding null values)
    rules_with_data = allowed_data['Rule Name'].dropna()
    rules_counts = rules_with_data.value_counts()
    
    # Calculate null/missing count
    null_count = total_count - len(rules_with_data)
    
    breakdown_data = []
    for rule_name, count in rules_counts.items():
        percentage = (count / total_count) * 100  # Use total_count for percentage
        breakdown_data.append({
            'Rule_Name': rule_name,
            'Count': count,
            'Percentage': round(percentage, 2)
        })
    
    # Add null/missing rules if they exist
    if null_count > 0:
        null_percentage = (null_count / total_count) * 100
        breakdown_data.append({
            'Rule_Name': 'Unknown/Missing Rule Name',
            'Count': null_count,
            'Percentage': round(null_percentage, 2)
        })
    
    df_output = pd.DataFrame(breakdown_data)
    output_path = os.path.join(output_dir, f"{output_prefix}_allowed_rules_breakdown.csv")
    df_output.to_csv(output_path, index=False)
    print(f"{output_prefix} allowed rules breakdown CSV saved: {output_path}")
    print(f"  Total allowed records: {total_count:,}, Records with rule names: {len(rules_with_data):,}, Missing rule names: {null_count:,}")

def create_network_tunnels_protocol_breakdown_csv(network_tunnels_data, output_dir, data_type="firewall"):
    """Create protocol breakdown for each Network Tunnels identity"""
    if network_tunnels_data.empty or 'Identities' not in network_tunnels_data.columns:
        print("No Network Tunnels identities data")
        return
    
    identities = network_tunnels_data['Identities'].dropna().unique()
    
    for identity in identities:
        identity_data = network_tunnels_data[network_tunnels_data['Identities'] == identity]
        if not identity_data.empty:
            # Keep original identity name, only replace special characters for filename
            safe_identity = str(identity).replace('/', '_').replace('\\', '_').replace(':', '_')
            prefix = f"{data_type}_nt_{safe_identity}"
            create_protocol_breakdown_csv(identity_data, prefix, output_dir)

def create_network_tunnels_allowed_rules_breakdown_csv(network_tunnels_data, output_dir, data_type="firewall"):
    """Create allowed rules breakdown for each Network Tunnels identity"""
    if network_tunnels_data.empty or 'Identities' not in network_tunnels_data.columns:
        print("No Network Tunnels identities data")
        return
    
    identities = network_tunnels_data['Identities'].dropna().unique()
    
    for identity in identities:
        identity_data = network_tunnels_data[network_tunnels_data['Identities'] == identity]
        if not identity_data.empty:
            # Keep original identity name, only replace special characters for filename
            safe_identity = str(identity).replace('/', '_').replace('\\', '_').replace(':', '_')
            prefix = f"{data_type}_nt_{safe_identity}"
            create_allowed_rules_breakdown_csv(identity_data, prefix, output_dir)
    """Create protocol breakdown for each Network Tunnels identity"""
    if network_tunnels_data.empty or 'Identities' not in network_tunnels_data.columns:
        print("No Network Tunnels identities data")
        return
    
    identities = network_tunnels_data['Identities'].dropna().unique()
    
    for identity in identities:
        identity_data = network_tunnels_data[network_tunnels_data['Identities'] == identity]
        if not identity_data.empty:
            # Keep original identity name, only replace special characters for filename
            safe_identity = str(identity).replace('/', '_').replace('\\', '_').replace(':', '_')
            prefix = f"{data_type}_nt_{safe_identity}"
            create_protocol_breakdown_csv(identity_data, prefix, output_dir)

def create_top20_csv(data, column_name, output_prefix, output_dir):
    """Create top 20 CSV for any column with total statistics"""
    if column_name not in data.columns:
        print(f"{column_name} column not found in {output_prefix}")
        return
    
    # Get total count including null values
    total_count = len(data)
    
    # Clean data (excluding null values)
    clean_data = data[column_name].dropna()
    
    if column_name == 'Destination Port':
        clean_data = pd.to_numeric(clean_data, errors='coerce').dropna()
        # Convert to integer to remove decimals
        clean_data = clean_data.astype(int)
    
    # Calculate null/missing count
    null_count = total_count - len(clean_data)
    
    if clean_data.empty:
        print(f"No valid {column_name} data in {output_prefix}")
        return
    
    # Get ALL value counts (not just top 20)
    all_value_counts = clean_data.value_counts()
    
    # Calculate total unique values and total records with data
    total_unique = len(all_value_counts)
    total_records_with_data = all_value_counts.sum()
    
    # Get top 20 for the chart
    top20_value_counts = all_value_counts.head(20)
    
    breakdown_data = []
    
    # Add metadata row with totals (this will be used by plotting function)
    metadata = {
        'total_records_all': total_count,
        'total_records_with_data': total_records_with_data,
        'total_unique_values': total_unique,
        'missing_records': null_count
    }
    
    for value, count in top20_value_counts.items():
        percentage = (count / total_count) * 100  # Use total_count for percentage
        if column_name == 'Destination Port':
            # Ensure destination port is integer without decimals
            breakdown_data.append({
                column_name.replace(' ', '_'): int(value),
                'Count': count,
                'Percentage': round(percentage, 2)
            })
        else:
            breakdown_data.append({
                column_name.replace(' ', '_'): value,
                'Count': count,
                'Percentage': round(percentage, 2)
            })
    
    # Add missing/null entry if significant
    if null_count > 0:
        null_percentage = (null_count / total_count) * 100
        breakdown_data.append({
            column_name.replace(' ', '_'): f'Unknown/Missing {column_name}',
            'Count': null_count,
            'Percentage': round(null_percentage, 2)
        })
    
    df_output = pd.DataFrame(breakdown_data)
    safe_column = column_name.lower().replace(' ', '_')
    output_path = os.path.join(output_dir, f"{output_prefix}_top20_{safe_column}.csv")
    df_output.to_csv(output_path, index=False)
    
    # Also save metadata as a separate file for plotting to use
    metadata_path = os.path.join(output_dir, f"{output_prefix}_metadata_{safe_column}.csv")
    metadata_df = pd.DataFrame([metadata])
    metadata_df.to_csv(metadata_path, index=False)
    
    print(f"{output_prefix} top 20 {column_name} CSV saved: {output_path}")
    print(f"  Total records: {total_count:,}, Records with {column_name}: {total_records_with_data:,}, Unique {column_name}s: {total_unique:,}, Missing: {null_count:,}")

def create_unique_source_ips_by_identity_csv(firewall_df, output_dir):
    """Create CSV showing unique source IP counts for each identity"""
    print("Creating unique source IPs by identity CSV...")
    
    if firewall_df.empty or 'Source IP' not in firewall_df.columns:
        print("No firewall source IP data available")
        return
    
    # Find identity columns
    identity_type_col = None
    identities_col = None
    
    for col in firewall_df.columns:
        if any(keyword in col.lower() for keyword in ['identity type', 'identity_type']):
            identity_type_col = col
        if 'identities' in col.lower():
            identities_col = col
    
    if not identity_type_col:
        print("Warning: No identity type column found")
        return
    
    unique_ip_data = []
    total_unique_ips = 0
    
    # Process AD Users
    ad_users_data = firewall_df[firewall_df[identity_type_col] == 'AD Users']
    if not ad_users_data.empty:
        unique_ips = ad_users_data['Source IP'].nunique()
        unique_ip_data.append({
            'Identity': 'AD Users',
            'Unique_Source_IPs': unique_ips
        })
        total_unique_ips += unique_ips
    
    # Process Network Tunnels
    if identities_col:
        network_tunnels_data = firewall_df[firewall_df[identity_type_col] == 'Network Tunnels']
        if not network_tunnels_data.empty:
            # Group by specific network tunnel identities
            identities_with_data = network_tunnels_data[identities_col].dropna()
            unique_identities = identities_with_data.unique()
            
            for identity in unique_identities:
                identity_data = network_tunnels_data[network_tunnels_data[identities_col] == identity]
                unique_ips = identity_data['Source IP'].nunique()
                unique_ip_data.append({
                    'Identity': identity,
                    'Unique_Source_IPs': unique_ips
                })
                total_unique_ips += unique_ips
            
            # Handle Network Tunnels with missing identity names
            missing_identities_data = network_tunnels_data[network_tunnels_data[identities_col].isna()]
            if not missing_identities_data.empty:
                unique_ips = missing_identities_data['Source IP'].nunique()
                unique_ip_data.append({
                    'Identity': 'Unknown Network Tunnel',
                    'Unique_Source_IPs': unique_ips
                })
                total_unique_ips += unique_ips
    
    # Process other identity types
    other_identities = firewall_df[~firewall_df[identity_type_col].isin(['AD Users', 'Network Tunnels'])]
    if not other_identities.empty:
        unique_identity_types = other_identities[identity_type_col].unique()
        for identity_type in unique_identity_types:
            if pd.notna(identity_type):  # Skip NaN values
                identity_data = other_identities[other_identities[identity_type_col] == identity_type]
                unique_ips = identity_data['Source IP'].nunique()
                unique_ip_data.append({
                    'Identity': identity_type,
                    'Unique_Source_IPs': unique_ips
                })
                total_unique_ips += unique_ips
    
    # Handle records with missing identity types
    missing_identity_data = firewall_df[firewall_df[identity_type_col].isna()]
    if not missing_identity_data.empty:
        unique_ips = missing_identity_data['Source IP'].nunique()
        unique_ip_data.append({
            'Identity': 'Unknown Identity Type',
            'Unique_Source_IPs': unique_ips
        })
        total_unique_ips += unique_ips
    
    # Sort by unique IP count descending
    unique_ip_data.sort(key=lambda x: x['Unique_Source_IPs'], reverse=True)
    
    # Calculate overall unique IPs (to account for overlap)
    overall_unique_ips = firewall_df['Source IP'].nunique()
    
    # Add total row
    # unique_ip_data.append({
    #     'Identity': 'TOTAL (with overlaps)',
    #     'Unique_Source_IPs': total_unique_ips
    # })
    
    unique_ip_data.append({
        'Identity': 'TOTAL (overall unique)',
        'Unique_Source_IPs': overall_unique_ips
    })
    
    # Save CSV
    if unique_ip_data:
        df_output = pd.DataFrame(unique_ip_data)
        output_path = os.path.join(output_dir, "firewall_unique_source_ips_by_identity.csv")
        df_output.to_csv(output_path, index=False)
        print(f"Unique source IPs by identity CSV saved: {output_path}")
        print(f"  Total unique IPs (overall): {overall_unique_ips:,}")
        print(f"  Total unique IPs (sum by identity): {total_unique_ips:,}")

def create_destination_port_by_protocol_csv(data, output_prefix, output_dir):
    """Create destination port breakdown by protocol (TCP/UDP)"""
    if 'Destination Port' not in data.columns or 'Protocol' not in data.columns:
        print(f"Required columns not found for {output_prefix} protocol breakdown")
        return
    
    print(f"Creating {output_prefix} destination port breakdown by protocol...")
    
    # Filter for TCP and UDP protocols
    tcp_data = data[data['Protocol'].str.upper() == 'TCP']
    udp_data = data[data['Protocol'].str.upper() == 'UDP']
    
    if not tcp_data.empty:
        print(f"  Processing TCP data ({len(tcp_data):,} records)")
        create_top20_csv(tcp_data, 'Destination Port', f"{output_prefix}_tcp", output_dir)
    
    if not udp_data.empty:
        print(f"  Processing UDP data ({len(udp_data):,} records)")
        create_top20_csv(udp_data, 'Destination Port', f"{output_prefix}_udp", output_dir)

def main():
    """Main processing function"""
    print("=== Firewall CSV Data Generator ===")
    
    # Create output directory
    output_dir = create_output_directory()
    print(f"Output directory: {output_dir}")
    
    # Load data
    df = load_csv_data()
    if df is None:
        return
    
    # Filter to firewall data
    firewall_df = filter_firewall_data(df)
    if firewall_df is None or firewall_df.empty:
        print("No firewall data found!")
        firewall_df = pd.DataFrame()
    
    # Filter to proxy data
    proxy_df = filter_proxy_data(df)
    if proxy_df is None or proxy_df.empty:
        print("No proxy data found!")
        proxy_df = pd.DataFrame()
    
    # Process firewall data if available
    if not firewall_df.empty:
        print("\n=== Processing Firewall Data ===")
        
        # Get identity data for firewall
        firewall_ad_users_data, firewall_network_tunnels_data, firewall_identity_type_col = get_identity_data(firewall_df, "firewall")
        if firewall_ad_users_data is not None:
            
            # Create CSV outputs for firewall
            print("\n=== Creating Firewall CSV Files ===")
            
            # 1. Identity breakdown
            create_identity_breakdown_csv(firewall_df, firewall_ad_users_data, firewall_network_tunnels_data, output_dir, "firewall")
            
            # 2. Categories breakdowns
            create_categories_breakdown_csv(firewall_df, "firewall", output_dir)
            create_categories_breakdown_csv(firewall_ad_users_data, "ad_users", output_dir)
            create_network_tunnels_identities_breakdown_csv(firewall_network_tunnels_data, output_dir)
            
            # 2.1. NEW: Create detailed firewall categories with identities breakdown
            create_firewall_categories_with_identities_csv(firewall_df, output_dir)
            
            # 2.2. NEW: Create unique source IPs by identity analysis
            create_unique_source_ips_by_identity_csv(firewall_df, output_dir)
            
            # 3. Protocol breakdowns
            create_protocol_breakdown_csv(firewall_df, "firewall", output_dir)
            create_protocol_breakdown_csv(firewall_ad_users_data, "firewall_ad_users", output_dir)
            create_network_tunnels_protocol_breakdown_csv(firewall_network_tunnels_data, output_dir, "firewall")
            
            # 4. Action breakdown (only for overall firewall)
            create_action_breakdown_csv(firewall_df, "firewall", output_dir)
            
            # 5. Allowed rules breakdowns
            create_allowed_rules_breakdown_csv(firewall_df, "firewall", output_dir)
            create_allowed_rules_breakdown_csv(firewall_ad_users_data, "firewall_ad_users", output_dir)
            create_network_tunnels_allowed_rules_breakdown_csv(firewall_network_tunnels_data, output_dir, "firewall")
            
            # 6. Top 20 analyses for firewall
            create_top20_csv(firewall_df, 'Destination Port', 'firewall', output_dir)
            create_top20_csv(firewall_df, 'Source IP', 'firewall', output_dir)
            create_top20_csv(firewall_df, 'Destination IP', 'firewall', output_dir)
            create_top20_csv(firewall_df, 'Application', 'firewall', output_dir)
            
            # 6.1. NEW: Create destination port breakdown by protocol (TCP/UDP)
            create_destination_port_by_protocol_csv(firewall_df, 'firewall', output_dir)
            
            # 7. Top 20 analyses for AD Users
            if not firewall_ad_users_data.empty:
                create_top20_csv(firewall_ad_users_data, 'Destination Port', 'ad_users', output_dir)
                create_top20_csv(firewall_ad_users_data, 'Source IP', 'ad_users', output_dir)
                create_top20_csv(firewall_ad_users_data, 'Destination IP', 'ad_users', output_dir)
                create_top20_csv(firewall_ad_users_data, 'Application', 'ad_users', output_dir)
                
                # 7.1. NEW: Create AD Users destination port breakdown by protocol
                create_destination_port_by_protocol_csv(firewall_ad_users_data, 'ad_users', output_dir)
            
            # 8. Top 20 analyses for each Network Tunnels identity
            if not firewall_network_tunnels_data.empty and 'Identities' in firewall_network_tunnels_data.columns:
                identities = firewall_network_tunnels_data['Identities'].dropna().unique()
                
                for identity in identities:
                    identity_data = firewall_network_tunnels_data[firewall_network_tunnels_data['Identities'] == identity]
                    if not identity_data.empty:
                        # Keep original identity name, only replace special characters for filename
                        safe_identity = str(identity).replace('/', '_').replace('\\', '_').replace(':', '_')
                        
                        create_top20_csv(identity_data, 'Destination Port', f'nt_{safe_identity}', output_dir)
                        create_top20_csv(identity_data, 'Source IP', f'nt_{safe_identity}', output_dir)
                        create_top20_csv(identity_data, 'Destination IP', f'nt_{safe_identity}', output_dir)
                        create_top20_csv(identity_data, 'Application', f'nt_{safe_identity}', output_dir)
                        
                        # 8.1. NEW: Create Network Tunnels destination port breakdown by protocol
                        create_destination_port_by_protocol_csv(identity_data, f'nt_{safe_identity}', output_dir)
    
    # Process proxy data if available
    if not proxy_df.empty:
        print("\n=== Processing Proxy Data ===")
        
        # Get identity data for proxy
        proxy_ad_users_data, proxy_network_tunnels_data, proxy_identity_type_col = get_identity_data(proxy_df, "proxy")
        if proxy_ad_users_data is not None:
            
            # Create CSV outputs for proxy
            print("\n=== Creating Proxy CSV Files ===")
            
            # 1. Identity breakdown
            create_identity_breakdown_csv(proxy_df, proxy_ad_users_data, proxy_network_tunnels_data, output_dir, "proxy")
            
            # 2. Categories breakdowns
            create_categories_breakdown_csv(proxy_df, "proxy", output_dir)
            create_categories_breakdown_csv(proxy_ad_users_data, "proxy_ad_users", output_dir)
            
            # 3. Top 20 analyses for proxy
            create_top20_csv(proxy_df, 'Destination Port', 'proxy', output_dir)
            create_top20_csv(proxy_df, 'Source IP', 'proxy', output_dir)
            create_top20_csv(proxy_df, 'Destination IP', 'proxy', output_dir)
            create_top20_csv(proxy_df, 'Application', 'proxy', output_dir)
            
            # 4. Top 20 analyses for proxy AD Users
            if not proxy_ad_users_data.empty:
                create_top20_csv(proxy_ad_users_data, 'Destination Port', 'proxy_ad_users', output_dir)
                create_top20_csv(proxy_ad_users_data, 'Source IP', 'proxy_ad_users', output_dir)
                create_top20_csv(proxy_ad_users_data, 'Destination IP', 'proxy_ad_users', output_dir)
                create_top20_csv(proxy_ad_users_data, 'Application', 'proxy_ad_users', output_dir)
            
            # 5. Network Tunnels identities breakdown for proxy
            if not proxy_network_tunnels_data.empty and 'Identities' in proxy_network_tunnels_data.columns:
                identities = proxy_network_tunnels_data['Identities'].dropna().unique()
                
                for identity in identities:
                    identity_data = proxy_network_tunnels_data[proxy_network_tunnels_data['Identities'] == identity]
                    if not identity_data.empty:
                        # Keep original identity name, only replace special characters for filename
                        safe_identity = str(identity).replace('/', '_').replace('\\', '_').replace(':', '_')
                        create_categories_breakdown_csv(identity_data, f"proxy_nt_{safe_identity}", output_dir)
                        
                        # Top 20 analyses for proxy Network Tunnels identities
                        create_top20_csv(identity_data, 'Destination Port', f'proxy_nt_{safe_identity}', output_dir)
                        create_top20_csv(identity_data, 'Source IP', f'proxy_nt_{safe_identity}', output_dir)
                        create_top20_csv(identity_data, 'Destination IP', f'proxy_nt_{safe_identity}', output_dir)
                        create_top20_csv(identity_data, 'Application', f'proxy_nt_{safe_identity}', output_dir)
    
    print(f"\nCSV generation completed! Files saved to:")
    print(f"{os.path.abspath(output_dir)}")
    
    # Summary
    print(f"\n=== Summary ===")
    if not firewall_df.empty:
        print(f"Total firewall records: {len(firewall_df):,}")
        if 'firewall_ad_users_data' in locals():
            print(f"Firewall AD Users records: {len(firewall_ad_users_data):,}")
            print(f"Firewall Network Tunnels records: {len(firewall_network_tunnels_data):,}")
    
    if not proxy_df.empty:
        print(f"Total proxy records: {len(proxy_df):,}")
        if 'proxy_ad_users_data' in locals():
            print(f"Proxy AD Users records: {len(proxy_ad_users_data):,}")
            print(f"Proxy Network Tunnels records: {len(proxy_network_tunnels_data):,}")

if __name__ == "__main__":
    main()