import streamlit as st
import psycopg2
import psycopg2.extras
import os
from datetime import datetime, date
import json

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

def init_database():
    """Initialize database tables - PostgreSQL tables already exist"""
    # Tables already exist in PostgreSQL, no need to recreate
    pass

def main():
    st.set_page_config(
        page_title="SIM Analytics Dashboard",
        page_icon="📊",
        layout="wide"
    )
    
    # Initialize database
    init_database()
    
    st.title("📊 4G/5G SIM Card Usage Analytics")
    st.markdown("---")
    
    # Sidebar navigation
    st.sidebar.title("Navigation")
    page = st.sidebar.selectbox("Select Page", [
        "Dashboard",
        "Client Management", 
        "SIM Management",
        "Usage Data Entry",
        "Usage Analytics",
        "Protocol Analysis",
        "Reports"
    ])
    
    if page == "Dashboard":
        show_dashboard()
    elif page == "Client Management":
        show_client_management()
    elif page == "SIM Management":
        show_sim_management()
    elif page == "Usage Data Entry":
        show_usage_entry()
    elif page == "Usage Analytics":
        show_usage_analytics()
    elif page == "Protocol Analysis":
        show_protocol_analysis()
    elif page == "Reports":
        show_reports()

def show_dashboard():
    st.header("📈 Analytics Dashboard")
    
    conn = get_db_connection()
    
    # Get statistics
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    cursor.execute("SELECT COUNT(*) as count FROM clients")
    total_clients = cursor.fetchone()['count']
    
    cursor.execute("SELECT COUNT(*) as count FROM sim_numbers")
    total_sims = cursor.fetchone()['count']
    
    cursor.execute("SELECT COALESCE(SUM(total_usage_gb), 0) as total FROM usage_records")
    total_usage = cursor.fetchone()['total']
    
    cursor.execute("SELECT COUNT(*) as count FROM usage_records WHERE fup_reached = true")
    fup_exceeded = cursor.fetchone()['count']
    
    cursor.close()
    conn.close()
    
    # Display metrics
    col1, col2, col3, col4 = st.columns(4)
    
    with col1:
        st.metric("Total Clients", total_clients)
    
    with col2:
        st.metric("Total SIM Cards", total_sims)
    
    with col3:
        st.metric("Total Usage (GB)", f"{total_usage:.2f}")
    
    with col4:
        st.metric("FUP Exceeded", fup_exceeded)
    
    st.markdown("---")
    
    # Recent usage records
    st.subheader("Recent Usage Records")
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cursor.execute("""
        SELECT ur.id, ur.client_id, 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.created_at DESC
        LIMIT 10
    """)
    recent_records = cursor.fetchall()
    cursor.close()
    conn.close()
    
    if recent_records:
        for record in recent_records:
            # Convert sqlite3.Row to dict for safe access
            record_dict = dict(record)
            with st.expander(f"{record_dict['client_name']} - {record_dict['client_id']} ({record_dict['total_usage_gb']} GB)"):
                col1, col2, col3 = st.columns(3)
                with col1:
                    st.write(f"**Period:** {record_dict['date_from']} to {record_dict['date_to']}")
                with col2:
                    st.write(f"**Client ID:** {record_dict['client_id']}")
                with col3:
                    st.write(f"**FUP Status:** {'Exceeded' if record_dict['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_id = st.text_input("Client ID")
            client_name = st.text_input("Client Name")
            
            if st.form_submit_button("Add Client"):
                if client_id and client_name:
                    conn = get_db_connection()
                    try:
                        conn.execute("INSERT INTO clients (client_id, client_name) VALUES (?, ?)", 
                                   (client_id, client_name))
                        conn.commit()
                        st.success(f"Client '{client_name}' added successfully!")
                    except sqlite3.IntegrityError:
                        st.error("Client ID already exists!")
                    finally:
                        conn.close()
                else:
                    st.error("Please fill in all fields.")
    
    # Display existing clients
    st.subheader("Existing Clients")
    conn = get_db_connection()
    clients = conn.execute("SELECT * FROM clients ORDER BY client_name").fetchall()
    conn.close()
    
    if clients:
        for client in clients:
            st.write(f"**{client['client_name']}** (ID: {client['client_id']})")
    else:
        st.info("No clients found.")

def show_sim_management():
    st.header("📱 SIM Management")
    
    # Get clients for dropdown
    conn = get_db_connection()
    clients = conn.execute("SELECT client_id, client_name FROM clients ORDER BY client_name").fetchall()
    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)")
            
            if st.form_submit_button("Add SIM"):
                if selected_client and sim_number:
                    client_id = client_options[selected_client]
                    conn = get_db_connection()
                    conn.execute("INSERT INTO sim_numbers (client_id, sim_number, msisdn) VALUES (?, ?, ?)",
                               (client_id, sim_number, msisdn if msisdn else None))
                    conn.commit()
                    conn.close()
                    st.success(f"SIM '{sim_number}' added successfully!")
                else:
                    st.error("Please fill in required fields.")
    
    # Display existing SIMs
    st.subheader("Existing SIM Cards")
    conn = get_db_connection()
    sims = conn.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
    """).fetchall()
    conn.close()
    
    if sims:
        for sim in sims:
            st.write(f"**{sim['sim_number']}** - {sim['client_name']} (MSISDN: {sim['msisdn'] or 'N/A'})")
    else:
        st.info("No SIM cards found.")

def show_usage_entry():
    st.header("📊 Usage Data Entry")
    
    # Get clients and SIMs for dropdowns
    conn = get_db_connection()
    clients = conn.execute("SELECT client_id, client_name FROM clients ORDER BY client_name").fetchall()
    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()
            sims = conn.execute("SELECT * FROM sim_numbers WHERE client_id = ?", (client_id,)).fetchall()
            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()
                conn.execute("""
                    INSERT INTO usage_records 
                    (client_id, total_usage_gb, date_from, date_to, fup_reached)
                    VALUES (?, ?, ?, ?, ?)
                """, (client_id, total_usage_gb, date_from, date_to, fup_reached))
                conn.commit()
                conn.close()
                st.success("Usage record added successfully!")
            else:
                st.error("Please fill in all required fields.")

def show_usage_analytics():
    st.header("📈 Usage Analytics")
    
    conn = get_db_connection()
    records = conn.execute("""
        SELECT ur.id, ur.client_id, 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
    """).fetchall()
    conn.close()
    
    if not records:
        st.info("No usage records found.")
        return
    
    # Display usage records
    for record in records:
        record_dict = dict(record)
        with st.expander(f"{record_dict['client_name']} - {record_dict['client_id']} ({record_dict['total_usage_gb']} GB)"):
            col1, col2, col3 = st.columns(3)
            with col1:
                st.write(f"**Client:** {record_dict['client_name']}")
            with col2:
                st.write(f"**Client ID:** {record_dict['client_id']}")
            with col3:
                st.write(f"**Period:** {record_dict['date_from']} to {record_dict['date_to']}")
            
            st.write(f"**Total Usage:** {record_dict['total_usage_gb']} GB")
            st.write(f"**FUP Status:** {'Exceeded' if record_dict['fup_reached'] else 'Normal'}")

def show_protocol_analysis():
    st.header("🔍 Protocol Analysis")
    st.info("Protocol analysis features will be available once the numpy dependency issue is resolved.")

def show_reports():
    st.header("📄 Reports")
    st.info("Report generation features will be available once the dependency issues are resolved.")

if __name__ == "__main__":
    main()