import streamlit as st
import psycopg2
import psycopg2.extras
import os
from datetime import datetime, date, timedelta
import json
import random
from reportlab.lib.pagesizes import letter, A4
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch
from reportlab.lib import colors
import io
from auth import require_auth, check_permission, show_user_info, show_user_management, show_change_password
from version import get_version, get_version_badge, get_dashboard_version_card, get_changelog, format_version_display
from api_key_management import show_api_key_management, show_api_usage_monitoring
from api_docs import show_api_documentation

# PostgreSQL database connection
def get_db_connection():
    """Get PostgreSQL database connection"""
    return psycopg2.connect(os.environ['DATABASE_URL'])

def delete_client_completely(client_id, client_name):
    """Delete client and all associated data completely from the database"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # Start transaction
        cursor.execute("BEGIN")
        
        # Get all usage record IDs for this client (for protocol usage deletion)
        cursor.execute("SELECT id FROM usage_records WHERE client_id = %s", (client_id,))
        usage_record_ids = [row[0] for row in cursor.fetchall()]
        
        # Delete protocol usage data for all usage records of this client
        if usage_record_ids:
            cursor.execute("DELETE FROM protocol_usage WHERE usage_record_id = ANY(%s)", (usage_record_ids,))
        
        # Delete usage records
        cursor.execute("DELETE FROM usage_records WHERE client_id = %s", (client_id,))
        
        # Delete SIM numbers
        cursor.execute("DELETE FROM sim_numbers WHERE client_id = %s", (client_id,))
        
        # Delete client
        cursor.execute("DELETE FROM clients WHERE client_id = %s", (client_id,))
        
        # Commit transaction
        cursor.execute("COMMIT")
        
        st.success(f"Client '{client_name}' and all associated data deleted successfully!")
        
    except Exception as e:
        # Rollback on error
        cursor.execute("ROLLBACK")
        st.error(f"Error deleting client: {str(e)}")
    
    finally:
        cursor.close()
        conn.close()

def delete_all_clients_and_sims():
    """Delete ALL clients and ALL SIM cards from the database to start completely fresh"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # Start transaction
        cursor.execute("BEGIN")
        
        # Delete all data in the correct order to avoid foreign key constraints
        # 1. Delete all protocol usage data
        cursor.execute("DELETE FROM protocol_usage")
        
        # 2. Delete all usage records
        cursor.execute("DELETE FROM usage_records")
        
        # 3. Delete all SIM numbers
        cursor.execute("DELETE FROM sim_numbers")
        
        # 4. Delete all clients
        cursor.execute("DELETE FROM clients")
        
        # Commit transaction
        cursor.execute("COMMIT")
        
        st.success("✅ Successfully deleted ALL clients and SIM cards from the system!")
        st.success("🔄 System is now completely fresh and ready for new data.")
        
    except Exception as e:
        # Rollback on error
        cursor.execute("ROLLBACK")
        st.error(f"❌ Error deleting all data: {str(e)}")
    
    finally:
        cursor.close()
        conn.close()

def generate_protocol_usage_for_record(cursor, usage_record_id, total_usage_gb):
    """Generate realistic protocol usage for a usage record"""
    
    # Protocol categories and distributions
    protocols = {
        'HTTP': {'category': 'Web', 'base_percentage': 25},
        'HTTPS': {'category': 'Web', 'base_percentage': 35},
        'YouTube': {'category': 'Video', 'base_percentage': 15},
        'Netflix': {'category': 'Video', 'base_percentage': 8},
        'WhatsApp': {'category': 'Messaging', 'base_percentage': 3},
        'Facebook': {'category': 'Social', 'base_percentage': 4},
        'Instagram': {'category': 'Social', 'base_percentage': 3},
        'Spotify': {'category': 'Audio', 'base_percentage': 2},
        'Teams': {'category': 'Business', 'base_percentage': 2},
        'Zoom': {'category': 'Business', 'base_percentage': 3}
    }
    
    # Generate usage for each protocol
    total_percentage = 0
    for protocol_name, data in protocols.items():
        # Add some randomness to the base percentage
        variation = random.uniform(-0.3, 0.3)
        percentage = max(0.1, data['base_percentage'] + variation)
        usage_gb = round((percentage / 100) * total_usage_gb, 3)
        
        cursor.execute("""
            INSERT INTO protocol_usage (usage_record_id, protocol_name, category, usage_gb, percentage)
            VALUES (%s, %s, %s, %s, %s)
        """, (usage_record_id, protocol_name, data['category'], usage_gb, percentage))
        
        total_percentage += percentage
    
    # Add remaining usage to "Other" category if needed
    if total_percentage < 100:
        remaining_percentage = 100 - total_percentage
        remaining_usage = round((remaining_percentage / 100) * total_usage_gb, 3)
        cursor.execute("""
            INSERT INTO protocol_usage (usage_record_id, protocol_name, category, usage_gb, percentage)
            VALUES (%s, %s, %s, %s, %s)
        """, (usage_record_id, 'Other', 'Miscellaneous', remaining_usage, remaining_percentage))

def generate_automatic_usage_for_sim(client_id, sim_number, msisdn, cursor):
    """Generate automatic usage for a SIM from 1st of current month to current date"""
    from datetime import datetime, timedelta
    import random
    
    current_date = datetime.now().date()
    month_start = current_date.replace(day=1)
    
    # Calculate total days from month start to current date
    total_days = (current_date - month_start).days + 1
    
    # Generate daily usage data (1-38GB per day as requested)
    daily_usage_data = {}
    total_usage_gb = 0
    
    for i in range(total_days):
        day = month_start + timedelta(days=i)
        daily_gb = round(random.uniform(1.0, 38.0), 2)  # 1-38GB per day
        daily_usage_data[day.strftime('%Y-%m-%d')] = daily_gb
        total_usage_gb += daily_gb
    
    total_usage_gb = round(total_usage_gb, 2)
    
    # Insert usage record
    cursor.execute("""
        INSERT INTO usage_records 
        (client_id, sim_number, msisdn, total_usage_gb, date_from, date_to, fup_reached, daily_breakdown)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING id
    """, (client_id, sim_number, msisdn, total_usage_gb, month_start, current_date, 
          total_usage_gb > 50, json.dumps(daily_usage_data)))
    
    usage_result = cursor.fetchone()
    usage_record_id = usage_result[0] if usage_result else None
    
    # Generate protocol usage for this record
    if usage_record_id:
        generate_protocol_usage_for_record(cursor, usage_record_id, total_usage_gb)
    
    return usage_record_id, total_usage_gb, total_days

def check_and_reset_monthly_usage():
    """Check if it's a new month and reset usage for all SIMs"""
    from datetime import datetime
    
    current_date = datetime.now().date()
    
    # Check if we're on the 1st of the month
    if current_date.day == 1:
        conn = get_db_connection()
        cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        
        # Get all SIMs
        cursor.execute("""
            SELECT s.client_id, s.sim_number, s.msisdn
            FROM sim_numbers s
        """)
        sims = cursor.fetchall()
        
        # Clear old usage records for current month (if any exist)
        month_start = current_date.replace(day=1)
        cursor.execute("""
            DELETE FROM protocol_usage WHERE usage_record_id IN (
                SELECT id FROM usage_records WHERE date_from >= %s
            )
        """, (month_start,))
        
        cursor.execute("""
            DELETE FROM usage_records WHERE date_from >= %s
        """, (month_start,))
        
        # Generate fresh usage for each SIM
        reset_count = 0
        for sim in sims:
            try:
                generate_automatic_usage_for_sim(
                    sim['client_id'], 
                    sim['sim_number'], 
                    sim['msisdn'], 
                    cursor
                )
                reset_count += 1
            except Exception as e:
                st.error(f"Error resetting usage for SIM {sim['sim_number']}: {str(e)}")
        
        conn.commit()
        cursor.close()
        conn.close()
        
        if reset_count > 0:
            st.success(f"🔄 Monthly reset completed! Generated fresh usage for {reset_count} SIMs")
    
    return current_date.day == 1

def update_daily_usage_for_all_sims():
    """Update daily usage for all SIMs (add today's usage if not already added)"""
    from datetime import datetime
    import random
    
    current_date = datetime.now().date()
    today_str = current_date.strftime('%Y-%m-%d')
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    # Get all current month usage records
    month_start = current_date.replace(day=1)
    cursor.execute("""
        SELECT id, sim_number, daily_breakdown, total_usage_gb, date_to
        FROM usage_records 
        WHERE date_from >= %s AND date_to < %s
    """, (month_start, current_date))
    
    current_records = cursor.fetchall()
    updated_count = 0
    
    for record in current_records:
        daily_breakdown = json.loads(record['daily_breakdown']) if record['daily_breakdown'] else {}
        
        # Check if today's usage is already recorded
        if today_str not in daily_breakdown:
            # Add today's usage (1-38GB)
            today_usage = round(random.uniform(1.0, 38.0), 2)
            daily_breakdown[today_str] = today_usage
            
            # Update total usage
            new_total = float(record['total_usage_gb']) + today_usage
            
            # Update the record
            cursor.execute("""
                UPDATE usage_records 
                SET total_usage_gb = %s, daily_breakdown = %s, date_to = %s, fup_reached = %s
                WHERE id = %s
            """, (new_total, json.dumps(daily_breakdown), current_date, new_total > 50, record['id']))
            
            updated_count += 1
    
    conn.commit()
    cursor.close()
    conn.close()
    
    return updated_count

def main():
    st.set_page_config(
        page_title="SIM Analytics Dashboard",
        page_icon="📊",
        layout="wide"
    )
    
    # Check authentication
    if not require_auth():
        return
    
    # Title with version badge
    col1, col2 = st.columns([4, 1])
    with col1:
        st.title("📊 4G/5G SIM Card Usage Analytics")
    with col2:
        st.markdown(get_version_badge(), unsafe_allow_html=True)
    st.markdown("---")
    
    # Show user info in sidebar
    show_user_info()
    st.sidebar.markdown("---")
    
    # Automatic Usage Management (run on app start)
    if check_permission('admin'):
        # Check for monthly reset
        is_first_of_month = check_and_reset_monthly_usage()
        
        # Update daily usage for all SIMs
        if not is_first_of_month:
            updated_count = update_daily_usage_for_all_sims()
            if updated_count > 0:
                st.sidebar.success(f"📊 Updated daily usage for {updated_count} SIMs")
        
        # Manual controls for admin
        st.sidebar.markdown("### 🔧 Usage Management")
        
        if st.sidebar.button("🔄 Reset All Monthly Usage"):
            conn = get_db_connection()
            cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            
            # Get all SIMs
            cursor.execute("SELECT client_id, sim_number, msisdn FROM sim_numbers")
            sims = cursor.fetchall()
            
            # Clear existing usage
            cursor.execute("DELETE FROM protocol_usage")
            cursor.execute("DELETE FROM usage_records")
            
            # Generate fresh usage for each SIM
            reset_count = 0
            for sim in sims:
                try:
                    generate_automatic_usage_for_sim(
                        sim['client_id'], sim['sim_number'], sim['msisdn'], cursor
                    )
                    reset_count += 1
                except Exception as e:
                    st.sidebar.error(f"Error: {sim['sim_number']}")
            
            conn.commit()
            cursor.close()
            conn.close()
            st.sidebar.success(f"Reset {reset_count} SIMs")
        
        if st.sidebar.button("📈 Update Today's Usage"):
            updated = update_daily_usage_for_all_sims()
            if updated > 0:
                st.sidebar.success(f"Updated {updated} SIMs")
            else:
                st.sidebar.info("All SIMs already updated today")
        
        st.sidebar.markdown("---")
        st.sidebar.markdown("### ⚠️ Danger Zone")
        
        # Delete all clients and SIMs button with confirmation (Admin only)
        if 'show_delete_all_confirmation' not in st.session_state:
            st.session_state.show_delete_all_confirmation = False
        
        if not st.session_state.show_delete_all_confirmation:
            if st.sidebar.button("🗑️ Delete ALL Clients & SIMs", type="secondary"):
                st.session_state.show_delete_all_confirmation = True
                st.rerun()
        else:
            st.sidebar.error("⚠️ THIS WILL DELETE EVERYTHING!")
            st.sidebar.write("This will permanently delete:")
            st.sidebar.write("• ALL Clients")
            st.sidebar.write("• ALL SIM Cards")
            st.sidebar.write("• ALL Usage Records")
            st.sidebar.write("• ALL Protocol Usage Data")
            
            col1, col2 = st.sidebar.columns(2)
            with col1:
                if st.button("✅ YES, DELETE ALL", type="primary", use_container_width=True):
                    delete_all_clients_and_sims()
                    st.session_state.show_delete_all_confirmation = False
                    st.rerun()
            with col2:
                if st.button("❌ Cancel", use_container_width=True):
                    st.session_state.show_delete_all_confirmation = False
                    st.rerun()
    
    st.sidebar.markdown("---")
    
    # Version display in sidebar
    st.sidebar.markdown(f"**{format_version_display()}**")
    
    # Sidebar navigation with role-based access
    st.sidebar.title("Navigation")
    
    # Available pages based on user role
    pages = ["Dashboard", "Client Management", "SIM Management", "Usage Analytics", "Protocol Analysis", "Reports"]
    
    # Add Usage Data Entry and Data Population only for admin users
    if check_permission('admin'):
        pages.insert(3, "Usage Data Entry")
        pages.insert(4, "Data Population")
    else:
        # Show restricted access notice for staff users
        st.sidebar.info("📝 Usage Data Entry - Admin Only")
        st.sidebar.info("🔄 Data Population - Admin Only")
    
    # Add changelog page for all users
    pages.append("Changelog")
    
    # Add admin-only pages
    if check_permission('admin'):
        pages.extend(["API Key Management", "API Documentation", "API Usage Monitoring", "User Management", "Change Password"])
    else:
        pages.extend(["API Documentation", "Change Password"])
    
    page = st.sidebar.selectbox("Select Page", pages)
    
    # Route to appropriate page with permission checks
    if page == "Dashboard":
        show_dashboard()
    elif page == "Client Management":
        show_client_management()
    elif page == "SIM Management":
        show_sim_management()
    elif page == "Usage Data Entry":
        if check_permission('admin'):
            show_usage_entry()
        else:
            st.error("Access denied. Admin privileges required for Usage Data Entry.")
    elif page == "Data Population":
        if check_permission('admin'):
            show_data_population()
        else:
            st.error("Access denied. Admin privileges required for Data Population.")
    elif page == "Usage Analytics":
        show_usage_analytics()
    elif page == "Protocol Analysis":
        show_protocol_analysis()
    elif page == "Reports":
        show_reports()
    elif page == "Changelog":
        show_changelog()
    elif page == "API Key Management":
        if check_permission('admin'):
            show_api_key_management()
        else:
            st.error("Access denied. Admin privileges required.")
    elif page == "API Documentation":
        show_api_documentation()
    elif page == "API Usage Monitoring":
        if check_permission('admin'):
            show_api_usage_monitoring()
        else:
            st.error("Access denied. Admin privileges required.")
    elif page == "User Management":
        if check_permission('admin'):
            show_user_management()
        else:
            st.error("Access denied. Admin privileges required.")
    elif page == "Change Password":
        show_change_password()

def show_dashboard():
    st.header("📈 Analytics Dashboard")
    
    # Beautiful version card on dashboard
    st.markdown(get_dashboard_version_card(), unsafe_allow_html=True)
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    # Get statistics
    cursor.execute("SELECT COUNT(*) as count FROM clients")
    result = cursor.fetchone()
    total_clients = result['count'] if result else 0
    
    cursor.execute("SELECT COUNT(*) as count FROM usage_records")
    result = cursor.fetchone()
    total_records = result['count'] if result else 0
    
    cursor.execute("SELECT COALESCE(SUM(total_usage_gb), 0) as total FROM usage_records")
    result = cursor.fetchone()
    total_usage = result['total'] if result else 0
    
    cursor.execute("SELECT COUNT(*) as count FROM usage_records WHERE fup_reached = true")
    result = cursor.fetchone()
    fup_exceeded = result['count'] if result else 0
    
    # Display metrics
    col1, col2, col3, col4 = st.columns(4)
    
    with col1:
        st.metric("Total Clients", total_clients)
    
    with col2:
        st.metric("Usage Records", total_records)
    
    with col3:
        st.metric("Total Usage (GB)", f"{total_usage:.2f}")
    
    with col4:
        st.metric("FUP Exceeded", fup_exceeded)
    
    st.markdown("---")
    
    # Individual SIM Statistics
    st.subheader("Individual SIM Card Statistics")
    cursor.execute("""
        SELECT ur.client_id, ur.sim_number, ur.msisdn, ur.total_usage_gb, 
               ur.date_from, ur.date_to, ur.fup_reached, c.client_name
        FROM usage_records ur
        JOIN clients c ON ur.client_id = c.client_id
        ORDER BY c.client_name, ur.sim_number, ur.created_at DESC
    """)
    sim_records = cursor.fetchall()
    
    if sim_records:
        # Group by client for better organization
        current_client = None
        for record in sim_records:
            if current_client != record['client_name']:
                current_client = record['client_name']
                st.write(f"### {current_client}")
            
            sim_display = record['sim_number'] if record['sim_number'] else record['client_id']
            msisdn_display = record['msisdn'] if record['msisdn'] else 'N/A'
            fup_status = "🔴 Exceeded" if record['fup_reached'] else "🟢 Normal"
            
            col1, col2, col3, col4 = st.columns(4)
            with col1:
                st.write(f"**SIM:** {sim_display}")
            with col2:
                st.write(f"**MSISDN:** {msisdn_display}")
            with col3:
                st.write(f"**Usage:** {record['total_usage_gb']} GB")
            with col4:
                st.write(f"**FUP:** {fup_status}")
            
            st.write(f"*Period: {record['date_from']} to {record['date_to']}*")
            st.markdown("---")
    else:
        st.info("No SIM usage records found.")
    
    st.markdown("---")
    
    # Recent usage records
    st.subheader("Recent Usage Records")
    cursor.execute("""
        SELECT ur.id, ur.client_id, ur.total_usage_gb, ur.sim_number, ur.msisdn,
               ur.date_from, ur.date_to, ur.fup_reached, ur.created_at, c.client_name 
        FROM usage_records ur
        JOIN clients c ON ur.client_id = c.client_id
        ORDER BY ur.created_at DESC
        LIMIT 10
    """)
    recent_records = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    if recent_records:
        for record in recent_records:
            sim_display = record['sim_number'] if record['sim_number'] else record['client_id']
            with st.expander(f"{record['client_name']} - {sim_display} ({record['total_usage_gb']} GB)"):
                col1, col2, col3 = st.columns(3)
                with col1:
                    st.write(f"**Period:** {record['date_from']} to {record['date_to']}")
                with col2:
                    st.write(f"**MSISDN:** {record['msisdn'] or 'N/A'}")
                with col3:
                    st.write(f"**FUP Status:** {'Exceeded' if record['fup_reached'] else 'Normal'}")
    else:
        st.info("No usage records found. Add some data to get started.")

def show_client_management():
    st.header("👥 Client Management")
    
    # Add new client form
    with st.expander("Add New Client", expanded=True):
        with st.form("add_client"):
            client_name = st.text_input("Client Name")
            
            if st.form_submit_button("Add Client"):
                if client_name:
                    # Generate unique client ID based on name
                    import re
                    import secrets
                    
                    # Clean the name for ID generation
                    clean_name = re.sub(r'[^a-zA-Z0-9\s]', '', client_name)
                    name_parts = clean_name.strip().split()
                    
                    if len(name_parts) >= 2:
                        # Use first letter of first name + last name + random number
                        client_id = f"{name_parts[0][0].upper()}{name_parts[-1].capitalize()}{secrets.randbelow(9999):04d}"
                    else:
                        # Use name + random number
                        client_id = f"{clean_name.replace(' ', '')}{secrets.randbelow(9999):04d}"
                    
                    conn = get_db_connection()
                    cursor = conn.cursor()
                    try:
                        # Check if ID already exists and regenerate if needed
                        cursor.execute("SELECT COUNT(*) FROM clients WHERE client_id = %s", (client_id,))
                        count_result = cursor.fetchone()
                        if count_result and count_result[0] > 0:
                            # Add timestamp suffix if ID exists
                            import time
                            client_id = f"{client_id}_{int(time.time()) % 10000}"
                        
                        cursor.execute("INSERT INTO clients (client_id, client_name) VALUES (%s, %s)", 
                                     (client_id, client_name))
                        conn.commit()
                        st.success(f"Client '{client_name}' added successfully with ID: {client_id}")
                    except psycopg2.IntegrityError:
                        st.error("Error adding client. Please try again.")
                    finally:
                        cursor.close()
                        conn.close()
                else:
                    st.error("Please enter a client name.")
    
    # Display existing clients
    st.subheader("Existing Clients")
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cursor.execute("SELECT * FROM clients ORDER BY client_name")
    clients = cursor.fetchall()
    cursor.close()
    conn.close()
    
    if clients:
        for client in clients:
            col1, col2 = st.columns([4, 1])
            with col1:
                st.write(f"**{client['client_name']}** (ID: {client['client_id']})")
            with col2:
                # Delete button (Admin only)
                if check_permission('admin'):
                    if st.button("🗑️ Delete", key=f"delete_{client['client_id']}", type="secondary"):
                        # Show confirmation dialog
                        st.session_state[f"confirm_delete_{client['client_id']}"] = True
            
            # Confirmation dialog
            if st.session_state.get(f"confirm_delete_{client['client_id']}", False):
                st.warning(f"⚠️ Are you sure you want to delete client '{client['client_name']}'?")
                st.write("This will permanently remove:")
                st.write("- The client record")
                st.write("- All associated SIM cards")
                st.write("- All usage records")
                st.write("- All protocol usage data")
                
                col1, col2, col3 = st.columns(3)
                with col1:
                    if st.button("✅ Yes, Delete", key=f"confirm_yes_{client['client_id']}", type="primary"):
                        delete_client_completely(client['client_id'], client['client_name'])
                        st.session_state[f"confirm_delete_{client['client_id']}"] = False
                        st.rerun()
                with col2:
                    if st.button("❌ Cancel", key=f"confirm_no_{client['client_id']}"):
                        st.session_state[f"confirm_delete_{client['client_id']}"] = False
                        st.rerun()
                
                st.markdown("---")
    else:
        st.info("No clients found.")

def show_sim_management():
    st.header("📱 SIM Management")
    
    # Get clients for dropdown
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cursor.execute("SELECT client_id, client_name FROM clients ORDER BY client_name")
    clients = cursor.fetchall()
    cursor.close()
    conn.close()
    
    if not clients:
        st.warning("No clients found. Please add clients first.")
        return
    
    # Add new SIM form
    with st.expander("Add New SIM", expanded=True):
        with st.form("add_sim"):
            client_options = {f"{c['client_name']} ({c['client_id']})": c['client_id'] for c in clients}
            selected_client = st.selectbox("Select Client", options=list(client_options.keys()))
            sim_number = st.text_input("SIM Number")
            msisdn = st.text_input("MSISDN (Optional)")
            fup_gb = st.number_input("FUP (GB)", min_value=50, step=1, help="Fair Usage Policy limit in GB (minimum 50GB)")
            
            if st.form_submit_button("Add SIM"):
                if selected_client and sim_number and fup_gb >= 50:
                    client_id = client_options[selected_client]
                    conn = get_db_connection()
                    cursor = conn.cursor()
                    
                    # Insert SIM with FUP
                    cursor.execute("INSERT INTO sim_numbers (client_id, sim_number, msisdn, fup_gb) VALUES (%s, %s, %s, %s)",
                                 (client_id, sim_number, msisdn if msisdn else None, fup_gb))
                    conn.commit()
                    
                    # Generate automatic usage (1-38GB per day from 1st of month to current date)
                    try:
                        usage_record_id, total_usage_gb, total_days = generate_automatic_usage_for_sim(
                            client_id, sim_number, msisdn, cursor
                        )
                        
                        conn.commit()
                        cursor.close()
                        conn.close()
                        
                        st.success(f"SIM '{sim_number}' added successfully with FUP of {fup_gb}GB!")
                        st.info(f"Auto-generated usage: {total_usage_gb}GB over {total_days} days (1-38GB per day)")
                        
                        # Show FUP status
                        if total_usage_gb > fup_gb:
                            st.warning(f"⚠️ Usage exceeds FUP limit ({total_usage_gb}GB > {fup_gb}GB)")
                        else:
                            st.success(f"✅ Usage within FUP limit ({total_usage_gb}GB ≤ {fup_gb}GB)")
                            
                    except Exception as e:
                        conn.rollback()
                        cursor.close()
                        conn.close()
                        st.error(f"Error generating automatic usage: {str(e)}")
                        return
                else:
                    if fup_gb < 50:
                        st.error("FUP must be at least 50GB.")
                    else:
                        st.error("Please fill in all required fields.")
    
    # Display existing SIMs
    st.subheader("Existing SIM Cards")
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cursor.execute("""
        SELECT s.*, c.client_name 
        FROM sim_numbers s
        JOIN clients c ON s.client_id = c.client_id
        ORDER BY c.client_name, s.sim_number
    """)
    sims = cursor.fetchall()
    cursor.close()
    conn.close()
    
    if sims:
        for sim in sims:
            fup_display = f"{sim['fup_gb']}GB" if sim.get('fup_gb') else "Not Set"
            st.write(f"**{sim['sim_number']}** - {sim['client_name']}")
            st.write(f"  MSISDN: {sim['msisdn'] or 'N/A'} | FUP: {fup_display}")
            st.markdown("---")
    else:
        st.info("No SIM cards found.")

def show_usage_entry():
    st.header("📊 Usage Data Entry")
    
    # Get clients for dropdown
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cursor.execute("SELECT client_id, client_name FROM clients ORDER BY client_name")
    clients = cursor.fetchall()
    cursor.close()
    conn.close()
    
    if not clients:
        st.warning("No clients found. Please add clients first.")
        return
    
    with st.form("add_usage"):
        # Client selection
        client_options = {f"{c['client_name']} ({c['client_id']})": c['client_id'] for c in clients}
        selected_client = st.selectbox("Select Client", options=list(client_options.keys()))
        
        # Initialize variables
        selected_sim = None
        sim_options = {}
        
        # Get SIMs for selected client
        if selected_client:
            client_id = client_options[selected_client]
            conn = get_db_connection()
            cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            cursor.execute("SELECT * FROM sim_numbers WHERE client_id = %s", (client_id,))
            sims = cursor.fetchall()
            cursor.close()
            conn.close()
            
            if sims:
                sim_options = {f"{s['sim_number']} (MSISDN: {s['msisdn'] or 'N/A'})": (s['sim_number'], s['msisdn']) for s in sims}
                selected_sim = st.selectbox("Select SIM", options=list(sim_options.keys()))
            else:
                st.warning("No SIM cards found for this client.")
                selected_sim = None
        
        # Usage data inputs
        col1, col2 = st.columns(2)
        with col1:
            date_from = st.date_input("Date From")
        with col2:
            date_to = st.date_input("Date To")
        
        total_usage_gb = st.number_input("Total Usage (GB)", min_value=0.0, step=0.1)
        fup_reached = st.checkbox("FUP Reached")
        
        if st.form_submit_button("Add Usage Record"):
            if selected_client and selected_sim and sim_options and total_usage_gb > 0:
                client_id = client_options[selected_client]
                sim_number, msisdn = sim_options[selected_sim]
                
                conn = get_db_connection()
                cursor = conn.cursor()
                cursor.execute("""
                    INSERT INTO usage_records 
                    (client_id, sim_number, msisdn, total_usage_gb, date_from, date_to, fup_reached)
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """, (client_id, sim_number, msisdn, total_usage_gb, date_from, date_to, fup_reached))
                conn.commit()
                cursor.close()
                conn.close()
                st.success("Usage record added successfully!")
            else:
                st.error("Please fill in all required fields.")

def generate_daily_usage_data(total_usage_gb, date_from, date_to):
    """Generate random daily usage distribution"""
    start_date = datetime.strptime(str(date_from), '%Y-%m-%d').date()
    end_date = datetime.strptime(str(date_to), '%Y-%m-%d').date()
    
    # Convert decimal to float if needed
    total_usage_float = float(total_usage_gb)
    
    # Calculate number of days
    days = (end_date - start_date).days + 1
    
    # Generate random weights for each day
    weights = [random.uniform(0.5, 2.0) for _ in range(days)]
    total_weight = sum(weights)
    
    # Normalize weights to distribute total usage
    daily_usage = []
    dates = []
    
    for i in range(days):
        current_date = start_date + timedelta(days=i)
        usage = (weights[i] / total_weight) * total_usage_float
        daily_usage.append(usage)
        dates.append(current_date)
    
    return dates, daily_usage

def generate_enhanced_usage_pdf(record, dates, daily_usage, protocol_data=None):
    """Generate enhanced PDF report with graphs and detailed SIM information"""
    buffer = io.BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=A4)
    styles = getSampleStyleSheet()
    story = []
    
    # Enhanced title with SIM details
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Heading1'],
        fontSize=20,
        spaceAfter=20,
        textColor=colors.HexColor('#1f4e79'),
        alignment=1  # Center alignment
    )
    
    sim_display = record['sim_number'] if record['sim_number'] else 'N/A'
    story.append(Paragraph(f"Detailed Usage Analytics Report", title_style))
    story.append(Spacer(1, 10))
    
    # SIM and Client Information Header
    header_style = ParagraphStyle(
        'HeaderStyle',
        parent=styles['Normal'],
        fontSize=14,
        textColor=colors.HexColor('#2c5282'),
        alignment=1,
        spaceAfter=20
    )
    story.append(Paragraph(f"SIM: {sim_display} | MSISDN: {record['msisdn'] or 'N/A'} | Client: {record['client_name']}", header_style))
    story.append(Spacer(1, 20))
    
    # Enhanced Client Information Table
    client_info = [
        ['Field', 'Details'],
        ['Client Name', record['client_name']],
        ['SIM Number', sim_display],
        ['MSISDN', record['msisdn'] or 'N/A'],
        ['Usage Period', f"{record['date_from']} to {record['date_to']}"],
        ['Total Data Usage', f"{record['total_usage_gb']} GB"],
        ['FUP Status', 'Exceeded' if record['fup_reached'] else 'Within Limit'],
        ['Report Generated', datetime.now().strftime('%Y-%m-%d %H:%M:%S')]
    ]
    
    client_table = Table(client_info, colWidths=[2.5*inch, 3.5*inch])
    client_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4e79')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('BACKGROUND', (0, 1), (0, -1), colors.HexColor('#f8f9fa')),
        ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTNAME', (0, 1), (0, -1), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, -1), 11),
        ('GRID', (0, 0), (-1, -1), 1, colors.HexColor('#dee2e6')),
        ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
        ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f8f9fa')])
    ]))
    
    story.append(client_table)
    story.append(Spacer(1, 30))
    
    # Daily Usage Analysis
    story.append(Paragraph("Daily Usage Analysis", styles['Heading2']))
    story.append(Spacer(1, 10))
    
    # Usage Statistics
    total_usage = float(record['total_usage_gb'])
    avg_daily = total_usage / len(daily_usage) if daily_usage else 0
    max_daily = max(daily_usage) if daily_usage else 0
    min_daily = min(daily_usage) if daily_usage else 0
    peak_date = dates[daily_usage.index(max_daily)] if daily_usage else 'N/A'
    
    stats_info = [
        ['Metric', 'Value'],
        ['Average Daily Usage', f"{avg_daily:.2f} GB"],
        ['Peak Daily Usage', f"{max_daily:.2f} GB"],
        ['Minimum Daily Usage', f"{min_daily:.2f} GB"],
        ['Peak Usage Date', str(peak_date)],
        ['Usage Variance', f"{sum((x - avg_daily)**2 for x in daily_usage) / len(daily_usage):.2f}" if daily_usage else "0.00"]
    ]
    
    stats_table = Table(stats_info, colWidths=[2.5*inch, 2*inch])
    stats_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#28a745')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('BACKGROUND', (0, 1), (0, -1), colors.HexColor('#f8f9fa')),
        ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTNAME', (0, 1), (0, -1), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, -1), 10),
        ('GRID', (0, 0), (-1, -1), 1, colors.HexColor('#dee2e6')),
        ('VALIGN', (0, 0), (-1, -1), 'MIDDLE')
    ]))
    
    story.append(stats_table)
    story.append(Spacer(1, 20))
    
    # ASCII Chart for Daily Usage
    if dates and daily_usage:
        story.append(Paragraph("Daily Usage Distribution Chart", styles['Heading3']))
        
        # Create enhanced ASCII chart
        max_usage = max(daily_usage)
        chart_lines = []
        chart_lines.append("Daily Usage Pattern (GB)")
        chart_lines.append("=" * 60)
        chart_lines.append("")
        
        for date, usage in zip(dates, daily_usage):
            bar_length = int((usage / max_usage) * 40) if max_usage > 0 else 0
            bar = "█" * bar_length + "░" * (40 - bar_length)
            percentage = (usage / total_usage * 100) if total_usage > 0 else 0
            chart_lines.append(f"{str(date):10} {bar} {usage:6.2f}GB ({percentage:5.1f}%)")
        
        chart_text = "\n".join(chart_lines)
        chart_style = ParagraphStyle(
            'ChartStyle',
            parent=styles['Normal'],
            fontName='Courier',
            fontSize=8,
            leftIndent=10,
            spaceAfter=20
        )
        story.append(Paragraph(chart_text.replace('\n', '<br/>'), chart_style))
    
    # Detailed Daily Usage Table
    story.append(Paragraph("Detailed Daily Breakdown", styles['Heading3']))
    daily_data = [['Date', 'Usage (GB)', 'Percentage of Total', 'Usage Level']]
    
    for date, usage in zip(dates, daily_usage):
        percentage = (usage / total_usage * 100) if total_usage > 0 else 0
        if percentage > 10:
            level = "High"
        elif percentage > 5:
            level = "Medium" 
        else:
            level = "Low"
        daily_data.append([str(date), f"{usage:.2f}", f"{percentage:.1f}%", level])
    
    daily_table = Table(daily_data, colWidths=[1.5*inch, 1.2*inch, 1.5*inch, 1*inch])
    daily_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4e79')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, -1), 9),
        ('GRID', (0, 0), (-1, -1), 1, colors.black),
        ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f0f0f0')])
    ]))
    
    story.append(daily_table)
    story.append(Spacer(1, 30))
    
    # Protocol Usage Analysis
    if protocol_data:
        story.append(Paragraph("Protocol Usage Analysis", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        protocol_table_data = [['Rank', 'Protocol', 'Category', 'Usage (GB)', 'Percentage']]
        for i, protocol in enumerate(protocol_data[:10]):
            protocol_table_data.append([
                str(i + 1),
                protocol['protocol_name'],
                protocol['category'],
                f"{protocol['usage_gb']:.2f}",
                f"{protocol['percentage']:.1f}%"
            ])
        
        protocol_table = Table(protocol_table_data, colWidths=[0.6*inch, 1.5*inch, 1.2*inch, 1*inch, 1*inch])
        protocol_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#dc3545')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 9),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f0f0f0')])
        ]))
        
        story.append(protocol_table)
    
    # Footer
    story.append(Spacer(1, 30))
    footer_style = ParagraphStyle(
        'FooterStyle',
        parent=styles['Normal'],
        fontSize=10,
        textColor=colors.HexColor('#6c757d'),
        alignment=1
    )
    story.append(Paragraph("End of Detailed Usage Report", footer_style))
    
    doc.build(story)
    buffer.seek(0)
    return buffer

def generate_usage_pdf(record, dates, daily_usage, protocol_data=None):
    """Generate comprehensive PDF report"""
    buffer = io.BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=A4)
    styles = getSampleStyleSheet()
    story = []
    
    # Title
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Heading1'],
        fontSize=18,
        spaceAfter=30,
        textColor=colors.HexColor('#1f4e79')
    )
    story.append(Paragraph(f"Usage Analytics Report", title_style))
    story.append(Spacer(1, 20))
    
    # Client Information
    client_info = [
        ['Client Name:', record['client_name']],
        ['SIM Number:', record['sim_number'] or 'N/A'],
        ['MSISDN:', record['msisdn'] or 'N/A'],
        ['Period:', f"{record['date_from']} to {record['date_to']}"],
        ['Total Usage:', f"{record['total_usage_gb']} GB"],
        ['FUP Status:', 'Exceeded' if record['fup_reached'] else 'Normal']
    ]
    
    client_table = Table(client_info, colWidths=[2*inch, 3*inch])
    client_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (0, -1), colors.HexColor('#f0f0f0')),
        ('TEXTCOLOR', (0, 0), (-1, -1), colors.black),
        ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
        ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, -1), 10),
        ('GRID', (0, 0), (-1, -1), 1, colors.black)
    ]))
    
    story.append(Paragraph("Client Information", styles['Heading2']))
    story.append(client_table)
    story.append(Spacer(1, 30))
    
    # Daily Usage Table
    daily_data = [['Date', 'Usage (GB)']]
    for date, usage in zip(dates, daily_usage):
        daily_data.append([str(date), f"{usage:.2f}"])
    
    daily_table = Table(daily_data, colWidths=[2*inch, 2*inch])
    daily_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4e79')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, -1), 9),
        ('GRID', (0, 0), (-1, -1), 1, colors.black)
    ]))
    
    story.append(Paragraph("Daily Usage Breakdown", styles['Heading2']))
    story.append(daily_table)
    
    # Protocol data if available
    if protocol_data:
        story.append(Spacer(1, 30))
        story.append(Paragraph("Top Protocol Usage", styles['Heading2']))
        
        protocol_table_data = [['Protocol', 'Category', 'Usage (GB)', 'Percentage']]
        for protocol in protocol_data[:10]:
            protocol_table_data.append([
                protocol['protocol_name'],
                protocol['category'],
                f"{protocol['usage_gb']:.2f}",
                f"{protocol['percentage']:.1f}%"
            ])
        
        protocol_table = Table(protocol_table_data, colWidths=[1.5*inch, 1.5*inch, 1*inch, 1*inch])
        protocol_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4e79')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 8),
            ('GRID', (0, 0), (-1, -1), 1, colors.black)
        ]))
        
        story.append(protocol_table)
    
    # Build PDF
    doc.build(story)
    buffer.seek(0)
    return buffer

def show_usage_analytics():
    st.header("📈 Usage Analytics")
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cursor.execute("""
        SELECT ur.id, ur.client_id, ur.sim_number, ur.msisdn, ur.total_usage_gb, ur.date_from, ur.date_to, 
               ur.fup_reached, ur.created_at, c.client_name 
        FROM usage_records ur
        JOIN clients c ON ur.client_id = c.client_id
        ORDER BY ur.date_from DESC
    """)
    records = cursor.fetchall()
    cursor.close()
    conn.close()
    
    if not records:
        st.info("No usage records found.")
        return
    
    # Display usage records with daily breakdown and export
    for record in records:
        sim_display = record['sim_number'] if record['sim_number'] else record['client_id']
        with st.expander(f"{record['client_name']} - {sim_display} ({record['total_usage_gb']} GB)"):
            col1, col2, col3 = st.columns(3)
            with col1:
                st.write(f"**Client:** {record['client_name']}")
            with col2:
                st.write(f"**SIM:** {sim_display}")
            with col3:
                st.write(f"**MSISDN:** {record['msisdn'] or 'N/A'}")
            
            col1, col2 = st.columns(2)
            with col1:
                st.write(f"**Period:** {record['date_from']} to {record['date_to']}")
            with col2:
                st.write(f"**Total Usage:** {record['total_usage_gb']} GB")
            
            st.write(f"**FUP Status:** {'Exceeded' if record['fup_reached'] else 'Normal'}")
            
            # Generate daily usage data
            dates, daily_usage = generate_daily_usage_data(
                record['total_usage_gb'], 
                record['date_from'], 
                record['date_to']
            )
            
            # Display daily usage as a beautiful graph
            st.subheader("📊 Daily Usage Visualization")
            
            # Create HTML bar chart for daily usage
            if dates and daily_usage:
                max_daily = max(daily_usage)
                st.markdown(f"""
                <div style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); padding: 20px; border-radius: 10px; margin-bottom: 20px;">
                    <h4 style="color: white; text-align: center; margin-bottom: 15px;">
                        📱 {sim_display} | 📞 {record['msisdn'] or 'N/A'} | 📈 {record['total_usage_gb']} GB
                    </h4>
                </div>
                """, unsafe_allow_html=True)
                
                for i, (date, usage) in enumerate(zip(dates, daily_usage)):
                    percentage = (usage / max_daily * 100) if max_daily > 0 else 0
                    usage_percent = (usage / float(record['total_usage_gb']) * 100) if float(record['total_usage_gb']) > 0 else 0
                    
                    # Color gradient based on usage level
                    if usage_percent > 10:
                        color = '#d32f2f'  # High usage - red
                    elif usage_percent > 5:
                        color = '#f57c00'  # Medium usage - orange
                    else:
                        color = '#388e3c'  # Low usage - green
                    
                    st.markdown(f"""
                    <div style="margin-bottom: 8px;">
                        <div style="display: flex; justify-content: space-between; margin-bottom: 2px;">
                            <span style="font-weight: bold;">{date}</span>
                            <span>{usage:.2f} GB ({usage_percent:.1f}%)</span>
                        </div>
                        <div style="background-color: #f0f0f0; border-radius: 8px; height: 18px;">
                            <div style="background-color: {color}; width: {percentage}%; height: 100%; border-radius: 8px;"></div>
                        </div>
                    </div>
                    """, unsafe_allow_html=True)
            
            st.markdown("---")
            
            # Display daily usage as a table
            st.subheader("📋 Daily Usage Breakdown")
            daily_df_data = []
            total_usage_float = float(record['total_usage_gb'])
            for date, usage in zip(dates, daily_usage):
                daily_df_data.append({
                    'Date': str(date),
                    'Usage (GB)': f"{usage:.2f}",
                    'Percentage': f"{(usage/total_usage_float*100):.1f}%"
                })
            
            # Create columns for daily usage display
            for i in range(0, len(daily_df_data), 3):
                cols = st.columns(3)
                for j, col in enumerate(cols):
                    if i + j < len(daily_df_data):
                        data = daily_df_data[i + j]
                        with col:
                            st.write(f"**{data['Date']}**")
                            usage_gb = data['Usage (GB)']
                            percentage = data['Percentage']
                            st.write(f"{usage_gb} ({percentage})")
            
            # Get protocol data for this record
            conn = get_db_connection()
            cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            cursor.execute("""
                SELECT protocol_name, category, usage_gb, percentage
                FROM protocol_usage
                WHERE usage_record_id = %s
                ORDER BY usage_gb DESC
                LIMIT 10
            """, (record['id'],))
            protocol_data = cursor.fetchall()
            cursor.close()
            conn.close()
            
            # Export to PDF button with enhanced styling
            st.markdown("---")
            col1, col2, col3 = st.columns([1, 2, 1])
            with col2:
                if st.button(f"📄 Export Detailed PDF Report", key=f"export_{record['id']}", type="primary"):
                    pdf_buffer = generate_enhanced_usage_pdf(record, dates, daily_usage, protocol_data)
                    
                    st.download_button(
                        label="Download Enhanced PDF Report",
                        data=pdf_buffer.getvalue(),
                        file_name=f"detailed_usage_report_{record['client_name'].replace(' ', '_')}_{sim_display}_{record['date_from']}.pdf",
                        mime="application/pdf",
                        key=f"download_{record['id']}"
                    )

def create_protocol_chart_ascii(protocol_data):
    """Create ASCII bar chart for protocol usage"""
    if not protocol_data:
        return "No data available"
    
    max_usage = max(float(p['total_usage_gb']) for p in protocol_data[:10])
    chart_lines = []
    chart_lines.append("Protocol Usage Distribution (Top 10)")
    chart_lines.append("=" * 50)
    
    for i, protocol in enumerate(protocol_data[:10]):
        name = protocol['protocol_name'][:15]  # Truncate long names
        usage = float(protocol['total_usage_gb'])
        bar_length = int((usage / max_usage) * 30) if max_usage > 0 else 0
        bar = "█" * bar_length + "░" * (30 - bar_length)
        chart_lines.append(f"{name:15} {bar} {usage:.1f}GB")
    
    return "\n".join(chart_lines)

def generate_individual_sim_report(record, protocols, categories, client_name):
    """Generate PDF report for individual SIM with protocol analysis"""
    buffer = io.BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=A4)
    styles = getSampleStyleSheet()
    story = []
    
    # Custom styles
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Heading1'],
        fontSize=20,
        spaceAfter=30,
        textColor=colors.HexColor('#1f4e79'),
        alignment=1  # Center alignment
    )
    
    header_style = ParagraphStyle(
        'CustomHeader',
        parent=styles['Heading2'],
        fontSize=14,
        spaceAfter=15,
        textColor=colors.HexColor('#2c5282')
    )
    
    # Header
    story.append(Paragraph("SIM Usage & Protocol Analysis Report", title_style))
    story.append(Spacer(1, 20))
    
    # Client and SIM Information
    story.append(Paragraph("Client & SIM Information", header_style))
    
    info_data = [
        ['Client Name:', client_name],
        ['SIM Number:', record['sim_number'] if record['sim_number'] else 'N/A'],
        ['MSISDN:', record['msisdn'] if record['msisdn'] else 'N/A'],
        ['Period:', f"{record['date_from']} to {record['date_to']}"],
        ['Total Usage:', f"{record['total_usage_gb']:.2f} GB"],
        ['Report Generated:', datetime.now().strftime('%Y-%m-%d %H:%M:%S')]
    ]
    
    info_table = Table(info_data, colWidths=[2*inch, 3*inch])
    info_table.setStyle(TableStyle([
        ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
        ('FONTNAME', (1, 0), (1, -1), 'Helvetica'),
        ('FONTSIZE', (0, 0), (-1, -1), 11),
        ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
        ('VALIGN', (0, 0), (-1, -1), 'TOP'),
        ('TOPPADDING', (0, 0), (-1, -1), 8),
        ('BOTTOMPADDING', (0, 0), (-1, -1), 8),
        ('GRID', (0, 0), (-1, -1), 0.5, colors.lightgrey)
    ]))
    
    story.append(info_table)
    story.append(Spacer(1, 30))
    
    # Executive Summary
    story.append(Paragraph("Executive Summary", header_style))
    if protocols:
        top_protocol = protocols[0]
        num_protocols = len(protocols)
        summary_text = f"""
        This report provides a detailed analysis of data usage for the specified SIM card during the period 
        {record['date_from']} to {record['date_to']}. A total of {record['total_usage_gb']:.2f} GB was consumed 
        across {num_protocols} different protocols. The most utilized protocol was {top_protocol['protocol_name']} 
        in the {top_protocol['category']} category, accounting for {top_protocol['usage_gb']:.2f} GB 
        ({top_protocol['percentage']:.1f}%) of total usage.
        """
        story.append(Paragraph(summary_text, styles['Normal']))
    story.append(Spacer(1, 20))
    
    # Category Breakdown
    if categories:
        story.append(Paragraph("Usage by Category", header_style))
        category_table_data = [['Category', 'Usage (GB)', 'Percentage', 'Protocols']]
        
        total_usage = float(record['total_usage_gb'])
        for category in categories:
            # Count protocols in this category
            protocol_count = len([p for p in protocols if p['category'] == category['category']])
            # Support both 'category_total' and 'total_gb' field names
            usage_value = category.get('category_total', category.get('total_gb', 0))
            category_table_data.append([
                category['category'],
                f"{float(usage_value):.2f}",
                f"{(float(usage_value)/total_usage*100) if total_usage > 0 else 0:.1f}%",
                str(protocol_count)
            ])
        
        category_table = Table(category_table_data, colWidths=[1.5*inch, 1.2*inch, 1.2*inch, 1*inch])
        category_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4e79')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 10),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f0f0f0')])
        ]))
        
        story.append(category_table)
        story.append(Spacer(1, 30))
    
    # Detailed Protocol Usage
    if protocols:
        story.append(Paragraph("Detailed Protocol Analysis", header_style))
        protocol_table_data = [['Rank', 'Protocol', 'Category', 'Usage (GB)', 'Percentage']]
        
        for i, protocol in enumerate(protocols[:20]):  # Top 20 protocols
            protocol_table_data.append([
                str(i + 1),
                protocol['protocol_name'],
                protocol['category'],
                f"{protocol['usage_gb']:.2f}",
                f"{protocol['percentage']:.1f}%"
            ])
        
        protocol_table = Table(protocol_table_data, colWidths=[0.6*inch, 1.8*inch, 1.2*inch, 1*inch, 1*inch])
        protocol_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4e79')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 9),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f0f0f0')])
        ]))
        
        story.append(protocol_table)
        story.append(Spacer(1, 20))
    
    # Usage Insights
    story.append(Paragraph("Usage Insights", header_style))
    if protocols and categories:
        insights = []
        
        # Top category insight
        top_category = categories[0]
        insights.append(f"• Primary usage category: {top_category['category']} ({top_category['total_gb']:.2f} GB)")
        
        # Protocol diversity
        web_protocols = [p for p in protocols if p['category'] == 'Web']
        video_protocols = [p for p in protocols if p['category'] == 'Video']
        
        if web_protocols:
            insights.append(f"• Web browsing accounts for {len(web_protocols)} different protocols")
        if video_protocols:
            insights.append(f"• Video streaming detected across {len(video_protocols)} different services")
        
        # Data efficiency
        if float(record['total_usage_gb']) > 100:
            insights.append("• High data usage detected - consider reviewing usage patterns")
        elif float(record['total_usage_gb']) < 10:
            insights.append("• Low data usage indicates efficient consumption")
        
        for insight in insights:
            story.append(Paragraph(insight, styles['Normal']))
    
    story.append(Spacer(1, 30))
    
    # Footer
    footer_text = f"""
    This report was generated by the SIM Analytics Dashboard on {datetime.now().strftime('%Y-%m-%d at %H:%M:%S')}. 
    The data represents actual network usage patterns and protocol analysis for the specified period.
    For questions about this report, please contact your network administrator.
    """
    story.append(Paragraph(footer_text, styles['Normal']))
    
    # Build PDF
    doc.build(story)
    buffer.seek(0)
    return buffer

def generate_protocol_analysis_pdf(protocol_data, category_data, selected_client="All Clients"):
    """Generate PDF report for protocol analysis"""
    buffer = io.BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=A4)
    styles = getSampleStyleSheet()
    story = []
    
    # Title
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Heading1'],
        fontSize=18,
        spaceAfter=30,
        textColor=colors.HexColor('#1f4e79')
    )
    story.append(Paragraph(f"Protocol Analysis Report - {selected_client}", title_style))
    story.append(Spacer(1, 20))
    
    # Executive Summary
    story.append(Paragraph("Executive Summary", styles['Heading2']))
    if protocol_data:
        total_protocols = len(protocol_data)
        top_protocol = protocol_data[0]
        summary_text = f"""
        This report analyzes protocol usage patterns for {selected_client}. 
        A total of {total_protocols} different protocols were identified. 
        The most used protocol is {top_protocol['protocol_name']} with {top_protocol['total_usage_gb']:.2f} GB 
        in the {top_protocol['category']} category.
        """
        story.append(Paragraph(summary_text, styles['Normal']))
    story.append(Spacer(1, 20))
    
    # Protocol Usage Table
    if protocol_data:
        story.append(Paragraph("Top Protocol Usage", styles['Heading2']))
        protocol_table_data = [['Rank', 'Protocol', 'Category', 'Usage (GB)', 'Avg %']]
        
        for i, protocol in enumerate(protocol_data[:15]):
            protocol_table_data.append([
                str(i + 1),
                protocol['protocol_name'],
                protocol['category'],
                f"{protocol['total_usage_gb']:.2f}",
                f"{protocol.get('avg_percentage', 0):.1f}%"
            ])
        
        protocol_table = Table(protocol_table_data, colWidths=[0.8*inch, 1.5*inch, 1.2*inch, 1*inch, 0.8*inch])
        protocol_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4e79')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 9),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f0f0f0')])
        ]))
        
        story.append(protocol_table)
        story.append(Spacer(1, 30))
    
    # Category Analysis
    if category_data:
        story.append(Paragraph("Usage by Category", styles['Heading2']))
        category_table_data = [['Category', 'Total Usage (GB)', 'Percentage']]
        
        total_category_usage = sum(float(cat['total_usage_gb']) for cat in category_data)
        for category in category_data:
            percentage = (float(category['total_usage_gb']) / total_category_usage * 100) if total_category_usage > 0 else 0
            category_table_data.append([
                category['category'],
                f"{category['total_usage_gb']:.2f}",
                f"{percentage:.1f}%"
            ])
        
        category_table = Table(category_table_data, colWidths=[2*inch, 1.5*inch, 1.5*inch])
        category_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4e79')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 10),
            ('GRID', (0, 0), (-1, -1), 1, colors.black)
        ]))
        
        story.append(category_table)
        story.append(Spacer(1, 30))
    
    # ASCII Chart
    if protocol_data:
        story.append(Paragraph("Protocol Usage Chart", styles['Heading2']))
        chart_text = create_protocol_chart_ascii(protocol_data)
        
        chart_style = ParagraphStyle(
            'ChartStyle',
            parent=styles['Normal'],
            fontName='Courier',
            fontSize=8,
            leftIndent=20
        )
        story.append(Paragraph(chart_text.replace('\n', '<br/>'), chart_style))
    
    # Generate timestamp
    from datetime import datetime
    story.append(Spacer(1, 30))
    story.append(Paragraph(f"Report generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}", styles['Normal']))
    
    doc.build(story)
    buffer.seek(0)
    return buffer

def show_protocol_analysis():
    st.header("🔍 Protocol Analysis - Per SIM Basis")
    st.info("📱 Analyze protocol usage for individual SIM cards with exact usage matching.")
    
    # Get all SIM cards with their usage records
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    # Get all SIM cards that have usage records with protocol data
    cursor.execute("""
        SELECT DISTINCT 
            s.sim_number,
            s.msisdn,
            s.client_id,
            c.client_name,
            COUNT(ur.id) as usage_record_count,
            SUM(ur.total_usage_gb) as total_usage_gb
        FROM sim_numbers s
        JOIN clients c ON s.client_id = c.client_id
        JOIN usage_records ur ON s.sim_number = ur.sim_number
        WHERE EXISTS (
            SELECT 1 FROM protocol_usage pu 
            JOIN usage_records ur2 ON pu.usage_record_id = ur2.id 
            WHERE ur2.sim_number = s.sim_number
        )
        GROUP BY s.sim_number, s.msisdn, s.client_id, c.client_name
        ORDER BY c.client_name, s.sim_number
    """)
    sims = cursor.fetchall()
    
    if not sims:
        st.warning("No SIM cards found with protocol usage data.")
        cursor.close()
        conn.close()
        return
    
    # SIM selection
    sim_options = [
        f"{sim['client_name']} - SIM: {sim['sim_number']} (MSISDN: {sim['msisdn']}) - {sim['total_usage_gb']:.2f} GB"
        for sim in sims
    ]
    
    selected_sim_index = st.selectbox(
        "Select SIM Card for Protocol Analysis", 
        range(len(sim_options)),
        format_func=lambda x: sim_options[x]
    )
    
    selected_sim = sims[selected_sim_index]
    
    # Display SIM information
    col1, col2, col3 = st.columns(3)
    with col1:
        st.metric("Client", selected_sim['client_name'])
    with col2:
        st.metric("SIM Number", selected_sim['sim_number'])
    with col3:
        st.metric("MSISDN", selected_sim['msisdn'])
    
    st.markdown("---")
    
    # Get all usage records for this SIM
    cursor.execute("""
        SELECT 
            ur.id,
            ur.total_usage_gb,
            ur.date_from,
            ur.date_to,
            ur.fup_reached
        FROM usage_records ur
        WHERE ur.sim_number = %s
        ORDER BY ur.date_from DESC
    """, (selected_sim['sim_number'],))
    usage_records = cursor.fetchall()
    
    if not usage_records:
        st.warning("No usage records found for this SIM.")
        cursor.close()
        conn.close()
        return
    
    # Usage record selection
    if len(usage_records) > 1:
        st.subheader("📅 Select Usage Period")
        record_options = [
            f"{record['date_from']} to {record['date_to']} - {record['total_usage_gb']:.3f} GB"
            for record in usage_records
        ]
        
        selected_record_index = st.selectbox(
            "Select Usage Record Period",
            range(len(record_options)),
            format_func=lambda x: record_options[x]
        )
        selected_record = usage_records[selected_record_index]
    else:
        selected_record = usage_records[0]
        st.subheader(f"📅 Usage Period: {selected_record['date_from']} to {selected_record['date_to']}")
    
    # Data validation check
    cursor.execute("""
        SELECT 
            ur.total_usage_gb as recorded_usage,
            COALESCE(SUM(pu.usage_gb), 0) as protocol_total,
            ABS(ur.total_usage_gb - COALESCE(SUM(pu.usage_gb), 0)) as discrepancy
        FROM usage_records ur
        LEFT JOIN protocol_usage pu ON ur.id = pu.usage_record_id
        WHERE ur.id = %s
        GROUP BY ur.id, ur.total_usage_gb
    """, (selected_record['id'],))
    validation_result = cursor.fetchone()
    
    # Display usage validation
    col1, col2, col3 = st.columns(3)
    with col1:
        st.metric("Recorded Usage", f"{validation_result['recorded_usage']:.3f} GB")
    with col2:
        st.metric("Protocol Total", f"{validation_result['protocol_total']:.3f} GB")
    with col3:
        discrepancy = validation_result['discrepancy']
        if discrepancy > 0.001:
            st.metric("Discrepancy", f"{discrepancy:.3f} GB", delta=f"⚠️ MISMATCH")
            st.error(f"🚨 Data Mismatch: Protocol usage ({validation_result['protocol_total']:.3f} GB) does not match recorded usage ({validation_result['recorded_usage']:.3f} GB). Discrepancy: {discrepancy:.3f} GB")
        else:
            st.metric("Data Match", "✅ EXACT", delta="Perfect match")
            st.success("✅ Protocol usage data matches recorded usage exactly!")
    
    st.markdown("---")
    
    # Get protocol data for this specific usage record
    cursor.execute("""
        SELECT 
            pu.protocol_name,
            pu.category,
            pu.usage_gb,
            pu.percentage
        FROM protocol_usage pu
        WHERE pu.usage_record_id = %s
        ORDER BY pu.usage_gb DESC
    """, (selected_record['id'],))
    protocol_data = cursor.fetchall()
    
    if not protocol_data:
        st.warning("No protocol data found for this usage record.")
        cursor.close()
        conn.close()
        return
    
    # Protocol visualization
    st.subheader("📊 Protocol Usage Breakdown")
    
    if protocol_data:
        # Create visual chart using HTML/CSS bars
        top_10_protocols = protocol_data[:10]
        if top_10_protocols:
            max_usage = max(float(p['usage_gb']) for p in top_10_protocols)
            
            for i, protocol in enumerate(top_10_protocols):
                usage = float(protocol['usage_gb'])
                percentage = (usage / max_usage * 100) if max_usage > 0 else 0
                
                # Color based on category
                colors = {
                    'Web': '#1f77b4', 'Video': '#ff7f0e', 'Messaging': '#2ca02c',
                    'Social': '#d62728', 'Audio': '#9467bd', 'Business': '#8c564b',
                    'Miscellaneous': '#e377c2'
                }
                color = colors.get(protocol['category'], '#7f7f7f')
                
                st.markdown(f"""
                <div style="margin-bottom: 10px;">
                    <div style="display: flex; justify-content: space-between; margin-bottom: 2px;">
                        <span style="font-weight: bold;">{protocol['protocol_name']}</span>
                        <span>{usage:.3f} GB ({protocol['percentage']:.1f}%)</span>
                    </div>
                    <div style="background-color: #f0f0f0; border-radius: 10px; height: 20px;">
                        <div style="background-color: {color}; width: {percentage}%; height: 100%; border-radius: 10px; display: flex; align-items: center; padding-left: 8px;">
                            <span style="color: white; font-size: 12px;">{protocol['category']}</span>
                        </div>
                    </div>
                </div>
                """, unsafe_allow_html=True)
        
        # Category breakdown
        cursor.execute("""
            SELECT 
                pu.category,
                SUM(pu.usage_gb) as category_total,
                SUM(pu.percentage) as total_percentage,
                COUNT(*) as protocol_count
            FROM protocol_usage pu
            WHERE pu.usage_record_id = %s
            GROUP BY pu.category
            ORDER BY category_total DESC
        """, (selected_record['id'],))
        category_data = cursor.fetchall()
        
        st.subheader("📈 Usage by Category")
        if category_data:
            # Custom display format to prevent truncation
            for category in category_data:
                usage_gb = float(category['category_total'])
                protocol_count = category['protocol_count']
                
                # Format with appropriate precision
                if usage_gb >= 100:
                    usage_display = f"{usage_gb:.2f}"
                else:
                    usage_display = f"{usage_gb:.3f}"
                
                # Use custom HTML for better control over display
                st.markdown(f"""
                <div style="
                    background-color: #f0f2f6; 
                    border-radius: 8px; 
                    padding: 12px; 
                    margin-bottom: 8px; 
                    border-left: 4px solid #0066cc;
                    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
                ">
                    <div style="
                        display: flex; 
                        justify-content: space-between; 
                        align-items: center;
                    ">
                        <div>
                            <strong style="font-size: 16px; color: #1f2937;">{category['category']}</strong>
                            <div style="color: #6b7280; font-size: 12px;">{protocol_count} protocols</div>
                        </div>
                        <div style="text-align: right;">
                            <div style="font-size: 18px; font-weight: bold; color: #0066cc;">
                                {usage_display} GB
                            </div>
                        </div>
                    </div>
                </div>
                """, unsafe_allow_html=True)
        
        st.markdown("---")
        
        # Display detailed protocol table
        st.subheader("📋 Detailed Protocol Breakdown")
        
        # Create a more detailed table
        protocol_table_data = []
        protocol_table_data.append(["Rank", "Protocol", "Category", "Usage (GB)", "Percentage", "Exact Usage"])
        
        for i, protocol in enumerate(protocol_data):
            protocol_table_data.append([
                str(i + 1),
                protocol['protocol_name'],
                protocol['category'],
                f"{protocol['usage_gb']:.3f}",
                f"{protocol['percentage']:.1f}%",
                f"{protocol['usage_gb']:.6f} GB"
            ])
        
        # Display as table
        import pandas as pd
        df = pd.DataFrame(protocol_table_data[1:], columns=protocol_table_data[0])
        st.dataframe(df, use_container_width=True)
        
        # Export to PDF button for SIM
        st.markdown("---")
        st.subheader("📄 Export Options")
        
        if st.button("📄 Export SIM Protocol Analysis to PDF"):
            # Generate detailed PDF for this specific SIM
            from datetime import datetime
            protocols = [dict(p) for p in protocol_data]
            categories = [dict(c) for c in category_data]
            
            # Create usage record info for PDF
            sim_info = {
                'client_name': selected_sim['client_name'],
                'sim_number': selected_sim['sim_number'],
                'msisdn': selected_sim['msisdn'],
                'total_usage_gb': selected_record['total_usage_gb'],
                'date_from': selected_record['date_from'],
                'date_to': selected_record['date_to']
            }
            
            pdf_buffer = generate_individual_sim_report(sim_info, protocols, categories, selected_sim['client_name'])
            
            sim_clean = selected_sim['sim_number'].replace('/', '_').replace(' ', '_')
            client_clean = selected_sim['client_name'].replace(' ', '_').replace('/', '_')
            
            st.download_button(
                label=f"Download Protocol Analysis for SIM {selected_sim['sim_number']}",
                data=pdf_buffer.getvalue(),
                file_name=f"SIM_Protocol_Analysis_{client_clean}_{sim_clean}_{selected_record['date_from']}_to_{selected_record['date_to']}.pdf",
                mime="application/pdf",
                key="download_sim_protocol_analysis"
            )
    
    # Data integrity information
    st.markdown("---")
    st.subheader("ℹ️ Data Integrity Information")
    st.info("""
    **Per-SIM Protocol Analysis Features:**
    • Analysis is performed on individual SIM cards, not aggregated across clients
    • Protocol usage data is validated to match recorded usage to the last byte
    • Discrepancies are highlighted when protocol totals don't match usage records
    • All usage values are displayed with 3 decimal places for precision
    • Each usage period is analyzed separately for accurate tracking
    """)
    
    # Fix data discrepancies button (admin only)
    if check_permission('admin') and validation_result['discrepancy'] > 0.001:
        st.markdown("---")
        st.subheader("🔧 Admin Tools")
        st.warning(f"Data discrepancy detected: {validation_result['discrepancy']:.3f} GB difference")
        
        if st.button("🔄 Regenerate Protocol Data for this Usage Record"):
            # Regenerate protocol analysis for this specific usage record
            from protocol_analyzer import ProtocolAnalyzer
            analyzer = ProtocolAnalyzer()
            
            try:
                # Remove existing protocol data
                cursor.execute("DELETE FROM protocol_usage WHERE usage_record_id = %s", (selected_record['id'],))
                
                # Regenerate protocol analysis
                analyzer.generate_protocol_analysis(
                    usage_record_id=selected_record['id'],
                    total_usage_gb=float(selected_record['total_usage_gb']),
                    usage_type='normal'
                )
                
                conn.commit()
                st.success("✅ Protocol data regenerated successfully! Please refresh the page to see updated data.")
                
            except Exception as e:
                conn.rollback()
                st.error(f"❌ Error regenerating protocol data: {str(e)}")
    
    cursor.close()
    conn.close()

def show_data_population():
    st.header("📈 Extend Existing Usage Records")
    st.info("Extend existing usage records by adding usage to missing days. This modifies existing records to include additional days.")
    
    conn = get_db_connection()
    if not conn:
        st.error("Database connection failed")
        return
    
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    # Get current usage records for all SIMs
    cursor.execute("""
        SELECT ur.id, c.client_id, c.client_name, s.sim_number, s.msisdn,
               ur.date_from, ur.date_to, ur.total_usage_gb, ur.daily_breakdown
        FROM usage_records ur
        JOIN sim_numbers s ON ur.msisdn = s.msisdn
        JOIN clients c ON s.client_id = c.client_id
        ORDER BY c.client_name, s.sim_number
    """)
    
    existing_records = cursor.fetchall()
    
    if not existing_records:
        st.warning("No existing usage records found")
        cursor.close()
        conn.close()
        return
    
    # Display current records
    st.subheader("📊 Current Usage Records")
    
    records_data = []
    for record in existing_records:
        records_data.append({
            "Client": record['client_name'],
            "SIM": record['sim_number'],
            "MSISDN": record['msisdn'],
            "Current Period": f"{record['date_from']} to {record['date_to']}",
            "Current Usage (GB)": record['total_usage_gb'],
            "Days": (record['date_to'] - record['date_from']).days + 1
        })
    
    st.dataframe(records_data, use_container_width=True)
    
    # Extension form
    st.subheader("📈 Extend Records with Additional Days")
    
    with st.form("extend_records_form"):
        col1, col2 = st.columns(2)
        
        with col1:
            min_usage = st.number_input("Min Usage per Day (GB)", min_value=0.1, max_value=50.0, value=1.0, step=0.1)
            days_to_extend = st.number_input("Days to Extend", min_value=1, max_value=30, value=3)
            
        with col2:
            max_usage = st.number_input("Max Usage per Day (GB)", min_value=0.1, max_value=100.0, value=5.0, step=0.1)
            
        unique_clients = list(set([r['client_name'] for r in existing_records]))
        selected_client = st.selectbox("Select Client", ["All Clients"] + unique_clients)
        
        preview_mode = st.checkbox("Preview Mode", value=True)
        if not preview_mode:
            st.warning("This will permanently modify existing records")
            confirm = st.checkbox("I confirm I want to extend existing records")
        else:
            confirm = True
        
        submit_button = st.form_submit_button("Preview Extensions" if preview_mode else "Extend Records")
        
        if submit_button and min_usage < max_usage and confirm:
            target_records = existing_records
            if selected_client != "All Clients":
                target_records = [r for r in existing_records if r['client_name'] == selected_client]
            
            preview_data = []
            success_count = 0
            
            with st.spinner("Processing extensions..."):
                for record in target_records:
                    try:
                        # Calculate new end date
                        new_end_date = record['date_to'] + timedelta(days=days_to_extend)
                        
                        # Generate additional daily usage
                        additional_usage = 0
                        current_breakdown = json.loads(record['daily_breakdown']) if record['daily_breakdown'] else {}
                        
                        # Add usage for extension days
                        for i in range(1, days_to_extend + 1):
                            extension_date = record['date_to'] + timedelta(days=i)
                            daily_usage = round(random.uniform(min_usage, max_usage), 2)
                            current_breakdown[extension_date.strftime('%Y-%m-%d')] = daily_usage
                            additional_usage += daily_usage
                        
                        new_total_usage = float(record['total_usage_gb']) + additional_usage
                        
                        if preview_mode:
                            preview_data.append({
                                "Client": record['client_name'],
                                "SIM": record['sim_number'],
                                "Original Period": f"{record['date_from']} to {record['date_to']}",
                                "Extended Period": f"{record['date_from']} to {new_end_date}",
                                "Original Usage": f"{float(record['total_usage_gb']):.2f} GB",
                                "Additional Usage": f"{additional_usage:.2f} GB",
                                "New Total": f"{new_total_usage:.2f} GB"
                            })
                        else:
                            # Update the existing record
                            cursor.execute("""
                                UPDATE usage_records 
                                SET date_to = %s, total_usage_gb = %s, daily_breakdown = %s
                                WHERE id = %s
                            """, (new_end_date, new_total_usage, json.dumps(current_breakdown), record['id']))
                            
                            # Generate additional protocol usage
                            generate_protocol_usage_for_record(cursor, record['id'], additional_usage)
                            
                            success_count += 1
                            conn.commit()
                        
                    except Exception as e:
                        st.error(f"Error extending record for {record['sim_number']}: {str(e)}")
                        if not preview_mode:
                            conn.rollback()
            
            if preview_mode:
                if preview_data:
                    st.success(f"Preview: {len(preview_data)} records would be extended")
                    st.dataframe(preview_data, use_container_width=True)
                    st.warning("No changes made. Disable preview mode to apply extensions.")
                else:
                    st.info("No records to extend")
            else:
                st.success(f"Extended {success_count} records successfully!")
                st.balloons()
    
    cursor.close()
    conn.close()

def show_changelog():
    st.header("📋 Version History & Changelog")
    
    # Current version highlight
    st.markdown(get_dashboard_version_card(), unsafe_allow_html=True)
    
    # Changelog entries
    changelog = get_changelog()
    
    for version, info in changelog.items():
        with st.expander(f"Version {version} - Released {info.get('date', 'Unknown')}", expanded=(version == get_version())):
            
            # Features
            if info.get('features'):
                st.subheader("✨ New Features")
                for feature in info['features']:
                    st.write(f"• {feature}")
            
            # Improvements
            if info.get('improvements'):
                st.subheader("🔧 Improvements")
                for improvement in info['improvements']:
                    st.write(f"• {improvement}")
            
            # Bug fixes
            if info.get('fixes'):
                st.subheader("🐛 Bug Fixes")
                for fix in info['fixes']:
                    st.write(f"• {fix}")
    
    # Version info footer
    st.markdown("---")
    st.markdown(f"**Current Version:** {format_version_display()}")
    st.markdown("System automatically updates version display across all pages.")

def show_reports():
    st.header("📄 Reports")
    st.info("Additional report generation features available through PDF export in Usage Analytics.")

if __name__ == "__main__":
    main()