import requests
import pandas as pd
from datetime import datetime
import json
import sys

# ============================================================================
# CONFIGURATION - UPDATE THESE VALUES
# ============================================================================
API_BASE_URL = "https://your-tenant.signals.revvity.com/api"  # Update with your tenant URL
API_TOKEN = "your-api-token-here"  # Update with your API token
OUTPUT_FILENAME = f"signals_groups_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"

# API Headers
HEADERS = {
    'x-api-key': API_TOKEN,
    'Content-Type': 'application/vnd.api+json',
    'Accept-Encoding': 'gzip, deflate, br'
}

# ============================================================================
# MAIN SCRIPT
# ============================================================================

def make_api_request(url, headers):
    """Make API request with error handling"""
    try:
        response = requests.get(url, headers=headers)
        response.raise_for_status()
        return response.json()
    except requests.exceptions.RequestException as e:
        print(f"API request failed for {url}: {e}")
        return None

def get_all_groups():
    """Fetch all system groups from the tenant"""
    print("Fetching all system groups...")
    url = f"{API_BASE_URL}/groups"
    
    groups_data = make_api_request(url, HEADERS)
    if not groups_data:
        return []
    
    # Extract groups from the response (adjust based on actual API response structure)
    groups = groups_data.get('data', [])
    print(f"Found {len(groups)} groups")
    return groups

def get_group_members(group_id):
    """Fetch members for a specific group"""
    print(f"Fetching members for group ID: {group_id}")
    url = f"{API_BASE_URL}/groups/{group_id}/members"
    
    members_data = make_api_request(url, HEADERS)
    if not members_data:
        return []
    
    # Extract members from the response (adjust based on actual API response structure)
    members = members_data.get('data', [])
    print(f"Found {len(members)} members in group {group_id}")
    return members

def process_groups_and_members():
    """Main processing function to get groups and their members"""
    # Get all groups
    groups = get_all_groups()
    if not groups:
        print("No groups found or API call failed")
        return None
    
    # Process each group and get members
    groups_with_members = []
    
    for group in groups:
        group_id = group.get('id')
        group_name = group.get('attributes', {}).get('name', 'Unknown')
        group_type = group.get('attributes', {}).get('type', 'Unknown')
        
        print(f"\nProcessing group: {group_name} (ID: {group_id})")
        
        # Get members for this group
        members = get_group_members(group_id)
        
        # Process members data
        for member in members:
            member_data = {
                'Group_ID': group_id,
                'Group_Name': group_name,
                'Group_Type': group_type,
                'Member_ID': member.get('id'),
                'Member_Email': member.get('attributes', {}).get('email', ''),
                'Member_First_Name': member.get('attributes', {}).get('firstName', ''),
                'Member_Last_Name': member.get('attributes', {}).get('lastName', ''),
                'Member_Status': member.get('attributes', {}).get('status', ''),
                'Member_Role': member.get('attributes', {}).get('role', '')
            }
            groups_with_members.append(member_data)
        
        # If group has no members, still add group info
        if not members:
            group_data = {
                'Group_ID': group_id,
                'Group_Name': group_name,
                'Group_Type': group_type,
                'Member_ID': '',
                'Member_Email': '',
                'Member_First_Name': '',
                'Member_Last_Name': '',
                'Member_Status': '',
                'Member_Role': ''
            }
            groups_with_members.append(group_data)
    
    return groups_with_members

def export_to_excel(data):
    """Export data to Excel with multiple sheets"""
    if not data:
        print("No data to export")
        return
    
    # Create DataFrame
    df = pd.DataFrame(data)
    
    # Create Excel writer object
    with pd.ExcelWriter(OUTPUT_FILENAME, engine='openpyxl') as writer:
        # Main sheet with all data
        df.to_excel(writer, sheet_name='All_Groups_Members', index=False)
        
        # Create separate sheets for each group
        unique_groups = df['Group_Name'].unique()
        
        for group_name in unique_groups:
            # Clean sheet name (Excel sheet names have limitations)
            sheet_name = str(group_name)[:31]  # Excel sheet name limit
            sheet_name = sheet_name.replace('/', '_').replace('\\', '_').replace('*', '_')
            sheet_name = sheet_name.replace('[', '_').replace(']', '_').replace(':', '_')
            sheet_name = sheet_name.replace('?', '_')
            
            group_data = df[df['Group_Name'] == group_name]
            group_data.to_excel(writer, sheet_name=sheet_name, index=False)
        
        # Summary sheet
        summary_data = df.groupby('Group_Name').agg({
            'Group_ID': 'first',
            'Group_Type': 'first',
            'Member_ID': 'count'
        }).rename(columns={'Member_ID': 'Member_Count'}).reset_index()
        
        summary_data.to_excel(writer, sheet_name='Groups_Summary', index=False)
    
    print(f"\nExport completed successfully!")
    print(f"File saved as: {OUTPUT_FILENAME}")
    print(f"Total groups processed: {len(unique_groups)}")
    print(f"Total member records: {len(df)}")

def main():
    """Main execution function"""
    print("=== Signals Groups and Members Export Tool ===")
    print(f"Target API: {API_BASE_URL}")
    print(f"Output file: {OUTPUT_FILENAME}")
    print("=" * 50)
    
    # Validate configuration
    if "your-api-token-here" in API_TOKEN or "your-tenant" in API_BASE_URL:
        print("ERROR: Please update the configuration section with your actual API details!")
        sys.exit(1)
    
    try:
        # Process groups and members
        data = process_groups_and_members()
        
        if data:
            # Export to Excel
            export_to_excel(data)
        else:
            print("No data retrieved. Please check your API configuration and connectivity.")
    
    except Exception as e:
        print(f"An error occurred: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()