app.py

import streamlit as st
import pandas as pd
import io
import re

from datetime import datetime

# Configure the page
st.set_page_config(
    page_title="CareCoordinaor Viewer",
    page_icon="📅",
    layout="wide"
)

# Custom CSS with hover effects and better styling
# Custom CSS with trendy, compact styling and dark borders
st.markdown("""
<style>
    .main-header {
        font-size: 2.2rem;
        color: #2c3e50;
        text-align: center;
        margin-bottom: 1rem;
        font-weight: 700;
    }
    .sub-header {
        font-size: 1.1rem;
        color: #7f8c8d;
        text-align: center;
        margin-bottom: 1.5rem;
    }
    .client-card {
        background: linear-gradient(145deg, #ffffff, #f8f9fa);
        border-radius: 12px;
        padding: 16px;
        margin: 12px 0;
        box-shadow: 0 4px 12px rgba(0,0,0,0.08);
        border: 2px solid #2c3e50;
        transition: all 0.3s ease;
        cursor: pointer;
    }
    .client-card:hover {
        box-shadow: 0 6px 20px rgba(44, 62, 80, 0.15);
        border-color: #3498db;
        transform: translateY(-2px);
    }
    .client-header {
        background: linear-gradient(135deg, #2c3e50, #34495e);
        color: white;
        padding: 12px 16px;
        border-radius: 8px;
        text-align: center;
        font-weight: 600;
        font-size: 1rem;
        margin-bottom: 12px;
        border: 1px solid #1a252f;
        box-shadow: 0 2px 4px rgba(0,0,0,0.1);
    }
    .assignment-container {
        background: white;
        border-radius: 8px;
        padding: 10px;
        margin: 8px 0;
        border: 1.5px solid #bdc3c7;
        box-shadow: 0 2px 6px rgba(0,0,0,0.05);
    }
    .assignment-item {
        display: flex;
        justify-content: space-between;
        align-items: center;
        padding: 8px 6px;
        border-radius: 6px;
        margin: 4px 0;
        border: 1px solid #ecf0f1;
        background: #fafbfc;
    }
    .assignment-item:hover {
        background: linear-gradient(135deg, #f8f9fa, #e9ecef);
        border-color: #3498db;
    }
    .nap-time {
        background: linear-gradient(135deg, #fff9e6, #ffecb3) !important;
        color: #d35400;
        font-weight: 600;
        padding: 6px 10px;
        border-radius: 6px;
        border: 1.5px solid #f39c12;
        font-size: 0.85rem;
        box-shadow: 0 1px 3px rgba(243, 156, 18, 0.2);
    }
    .regular-assignment {
        background: linear-gradient(135deg, #e8f4fd, #d4e6f1);
        padding: 6px 10px;
        border-radius: 6px;
        border: 1.5px solid #3498db;
        font-weight: 500;
        font-size: 0.85rem;
        color: #2c3e50;
        box-shadow: 0 1px 3px rgba(52, 152, 219, 0.2);
    }
    .time-slot {
        color: #2c3e50;
        font-weight: 600;
        font-size: 0.8rem;
        background: white;
        padding: 4px 8px;
        border-radius: 4px;
        border: 1px solid #bdc3c7;
        font-family: 'Courier New', monospace;
    }
    .tech-name {
        color: #2c3e50;
        font-weight: 600;
        font-size: 0.85rem;
    }
    .no-assignments {
        text-align: center;
        color: #7f8c8d;
        font-style: italic;
        padding: 16px;
        background: linear-gradient(135deg, #f8f9fa, #e9ecef);
        border-radius: 8px;
        border: 2px dashed #95a5a6;
        font-size: 0.9rem;
        margin: 8px 0;
    }
    .legend-item {
        display: inline-flex;
        align-items: center;
        margin-right: 20px;
        margin-bottom: 8px;
        background: white;
        padding: 6px 12px;
        border-radius: 16px;
        box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        border: 1px solid #bdc3c7;
        font-size: 0.85rem;
    }
    .legend-color {
        width: 16px;
        height: 16px;
        border-radius: 50%;
        margin-right: 6px;
        border: 2px solid white;
        box-shadow: 0 1px 2px rgba(0,0,0,0.1);
    }
    .stats-card {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        color: white;
        padding: 12px;
        border-radius: 8px;
        text-align: center;
        margin: 4px;
        border: 1px solid #5a6fd8;
        box-shadow: 0 2px 6px rgba(0,0,0,0.1);
    }
    .stats-card h3 {
        font-size: 0.9rem;
        margin-bottom: 4px;
        opacity: 0.9;
    }
    .stats-card h2 {
        font-size: 1.4rem;
        margin: 0;
        font-weight: 700;
    }
    .modal-content {
        background: white;
        padding: 16px;
        border-radius: 10px;
        box-shadow: 0 8px 20px rgba(0,0,0,0.15);
        border: 2px solid #2c3e50;
    }
    
    /* Compact column adjustments */
    .stButton button {
        font-size: 0.8rem;
        padding: 6px 12px;
        border-radius: 6px;
    }
    
    /* Make everything more compact */
    .row-widget.stButton {
        margin-top: 8px;
    }
</style>
""", unsafe_allow_html=True)

def parse_excel_file(uploaded_file):
    """Parse the uploaded Excel file and extract schedule data"""
    try:
        # Read the Excel file
        df = pd.read_excel(uploaded_file, header=0, engine='openpyxl')
        
        # Extract data
        times = df.iloc[:, 0].dropna().astype(str).tolist()
        clients = df.columns[1:].tolist()
        
        # Extract assignments
        assignments = []
        for i in range(len(times)):
            row_assignments = df.iloc[i, 1:].fillna('').astype(str).tolist()
            while len(row_assignments) < len(clients):
                row_assignments.append('')
            assignments.append(row_assignments)
        
        return {
            'times': times,
            'clients': clients,
            'assignments': assignments
        }
    except Exception as e:
        st.error(f"Error parsing Excel file: {str(e)}")
        return None

def render_client_modal(client, client_index, schedule_data):
    """Render a modal with hourly view for a specific client"""
    with st.expander(f"📊 Hourly Schedule for {client}", expanded=True):
        st.markdown(f"### 📋 Detailed Schedule for {client}")
        
        # Create hourly view for this client
        hourly_data = []
        for i, time in enumerate(schedule_data['times']):
            assignment = schedule_data['assignments'][i][client_index]
            hourly_data.append({
                'Time Slot': time,
                'Assignment': assignment
            })
        
        df_hourly = pd.DataFrame(hourly_data)
        
        # Display with styling
        for _, row in df_hourly.iterrows():
            col1, col2 = st.columns([2, 3])
            with col1:
                st.markdown(f"**{row['Time Slot']}**")
            with col2:
                assignment = row['Assignment']
                if assignment and 'NAP TIME' in assignment.upper():
                    st.markdown(f'<div class="nap-time">{assignment}</div>', unsafe_allow_html=True)
                elif assignment and assignment.strip():
                    st.markdown(f'<div class="regular-assignment">{assignment}</div>', unsafe_allow_html=True)
                else:
                    st.markdown("<div style='padding: 8px 12px; color: #7f8c8d;'>No assignment</div>", unsafe_allow_html=True)

def render_client_card(client, client_index, schedule_data):
    """Render a single client card with all assignments"""
    # Use container for the card with hover effects
    with st.container():
        st.markdown(f'<div class="client-card">', unsafe_allow_html=True)
        st.markdown(f'<div class="client-header">{client}</div>', unsafe_allow_html=True)
        
        # Group consecutive time slots with the same assignment
        assignments = group_assignments(client_index, schedule_data)
        
        if not assignments:
            st.markdown('<div class="no-assignments">No assignments scheduled</div>', unsafe_allow_html=True)
        else:
            # Use a container for assignments
            st.markdown('<div class="assignment-container">', unsafe_allow_html=True)
            for assignment in assignments:
                col1, col2 = st.columns([2, 3])
                with col1:
                    st.markdown(f'<div class="time-slot">{assignment["start"]} - {assignment["end"]}</div>', unsafe_allow_html=True)
                with col2:
                    tech = assignment['tech']
                    if 'NAP TIME' in tech.upper():
                        st.markdown(f'<div class="nap-time">{tech}</div>', unsafe_allow_html=True)
                    else:
                        st.markdown(f'<div class="regular-assignment">{tech}</div>', unsafe_allow_html=True)
            st.markdown('</div>', unsafe_allow_html=True)
        
        st.markdown('</div>', unsafe_allow_html=True)
        
        # Add click functionality using Streamlit's button
        if st.button(f"📋 View {client}", key=f"btn_{client_index}", use_container_width=True):
            render_client_modal(client, client_index, schedule_data)
            
def render_condensed_view(schedule_data):
    """Render the condensed view with enhanced styling"""
    clients = schedule_data['clients']
    
    # Create columns for clients (3 per row)
    cols_per_row = 3
    for i in range(0, len(clients), cols_per_row):
        cols = st.columns(cols_per_row)
        
        for j, col in enumerate(cols):
            client_index = i + j
            if client_index < len(clients):
                client = clients[client_index]
                with col:
                    render_client_card(client, client_index, schedule_data)



def group_assignments(client_index, schedule_data):
    """Group consecutive time slots with the same assignment"""
    assignments = []
    current_tech = None
    start_time = None
    
    for i, time in enumerate(schedule_data['times']):
        tech = schedule_data['assignments'][i][client_index]
        time_parts = str(time).split(' - ')
        current_start = time_parts[0]
        
        if tech != current_tech:
            if current_tech is not None and str(current_tech).strip():
                prev_time_parts = str(schedule_data['times'][i-1]).split(' - ')
                end_time = prev_time_parts[1] if len(prev_time_parts) > 1 else prev_time_parts[0]
                
                assignments.append({
                    'start': start_time,
                    'end': end_time,
                    'tech': current_tech
                })
            
            current_tech = tech
            start_time = current_start
    
    if current_tech is not None and str(current_tech).strip():
        last_time = str(schedule_data['times'][-1]).split(' - ')
        end_time = last_time[1] if len(last_time) > 1 else last_time[0]
        assignments.append({
            'start': start_time,
            'end': end_time,
            'tech': current_tech
        })
    
    return assignments

def create_sample_data():
    """Create sample data for demonstration"""
    sample_data = {
        'Time of Day': ['0700 - 0730', '0730 - 0800', '0800 - 0830', '0830 - 0900', 
                       '0900 - 0930', '0930 - 1000', '1000 - 1030', '1300 - 1330',
                       '1330 - 1400', '1400 - 1430', '1500 - 1530', '1530 - 1600'],
        'Client A': ['', 'Tech1', 'Tech1', 'Tech1', 'Tech2', 'Tech2', 'Tech2', 
                    'NAP TIME', 'Tech1', 'Tech1', 'Tech3', 'Tech3'],
        'Client B': ['Tech3', 'Tech3', '', 'Tech4', 'Tech4', 'Tech4', 'Tech3',
                    'Tech3', 'NAP TIME', 'Tech4', 'Tech1', 'Tech1'],
        'Client C': ['', '', 'Tech5', 'Tech5', 'Tech5', 'NAP TIME', 'NAP TIME',
                    'Tech6', 'Tech6', 'Tech6', 'Tech4', 'Tech4'],
        'Client D': ['Tech7', 'Tech7', 'Tech7', 'NAP TIME', 'NAP TIME', 'Tech8',
                    'Tech8', 'Tech8', 'Tech7', 'Tech7', '', ''],
        'Client E': ['', 'Tech9', 'Tech9', 'Tech9', 'Tech10', 'Tech10', 'NAP TIME',
                    'NAP TIME', 'Tech9', 'Tech9', 'Tech10', 'Tech10'],
        'Client F': ['Tech11', 'Tech11', 'Tech11', 'Tech11', '', '', 'Tech12',
                    'Tech12', 'Tech12', 'NAP TIME', 'NAP TIME', 'Tech11']
    }
    return pd.DataFrame(sample_data)

def calculate_statistics(schedule_data):
    """Calculate and return schedule statistics"""
    total_slots = len(schedule_data['times']) * len(schedule_data['clients'])
    assigned_slots = sum(1 for i in range(len(schedule_data['times'])) 
                       for j in range(len(schedule_data['clients']))
                       if schedule_data['assignments'][i][j] and 
                       schedule_data['assignments'][i][j].strip() and
                       'NAP TIME' not in schedule_data['assignments'][i][j].upper())
    
    nap_slots = sum(1 for i in range(len(schedule_data['times'])) 
                  for j in range(len(schedule_data['clients']))
                  if schedule_data['assignments'][i][j] and 
                  'NAP TIME' in schedule_data['assignments'][i][j].upper())
    
    empty_slots = total_slots - assigned_slots - nap_slots
    
    return {
        'total_assignments': assigned_slots,
        'nap_slots': nap_slots,
        'empty_slots': empty_slots,
        'total_clients': len(schedule_data['clients']),
        'total_timeslots': len(schedule_data['times'])
    }



def extract_date_from_excel(uploaded_file):
    """Extract date from Excel file metadata or filename"""
    try:
        # First try to get the file creation/modification date
        if hasattr(uploaded_file, 'name'):
            # Try filename pattern first (schedule_YYYY_MM_DD.xlsx)
            filename = uploaded_file.name
            date_match = re.search(r'schedule_(\d{4})_(\d{2})_(\d{2})', filename)
            if date_match:
                year, month, day = date_match.groups()
                return f"{year}-{month}-{day}"
            
            # If no pattern in filename, use current date as fallback
            return datetime.now().strftime("%Y-%m-%d")
        else:
            return datetime.now().strftime("%Y-%m-%d")
    except:
        return datetime.now().strftime("%Y-%m-%d")

def generate_soap_notes_table(schedule_data, uploaded_file):
    """Generate SOAP notes table with client-tech assignments"""
    soap_notes_data = []
    
    # Extract date from Excel file
    schedule_date = extract_date_from_excel(uploaded_file)
    
    # Collect unique client-tech combinations
    client_tech_combinations = set()
    
    for client_index, client in enumerate(schedule_data['clients']):
        for time_index, time in enumerate(schedule_data['times']):
            tech = schedule_data['assignments'][time_index][client_index]
            
            # Only include actual tech assignments (not empty or nap time)
            if tech and tech.strip() and 'NAP TIME' not in tech.upper():
                client_tech_combinations.add((client, tech))
    
    # Convert to list and create table data
    for idx, (client, tech) in enumerate(sorted(client_tech_combinations)):
        filename = f"{client}_{tech}.csv".replace(" ", "_")
        soap_notes_data.append({
            'ID': idx + 1,
            'Filename': filename,
            'Client Name': client,
            'Tech Name': tech,
            'Date': schedule_date
        })
    
    return pd.DataFrame(soap_notes_data)

def export_individual_client_tech_files(schedule_data, uploaded_file):
    """Export individual CSV files for each client-tech combination"""
    # Extract date from Excel file
    schedule_date = extract_date_from_excel(uploaded_file)
    
    client_tech_files = {}
    
    for client_index, client in enumerate(schedule_data['clients']):
        # Get all time slots and assignments for this client
        client_data = []
        for time_index, time in enumerate(schedule_data['times']):
            tech = schedule_data['assignments'][time_index][client_index]
            client_data.append({
                'Time Slot': time,
                'Tech Assignment': tech,
                'Date': schedule_date,
                'Client': client
            })
        
        # Group by tech and create individual files
        tech_assignments = {}
        for entry in client_data:
            tech = entry['Tech Assignment']
            if tech and tech.strip() and 'NAP TIME' not in tech.upper():
                if tech not in tech_assignments:
                    tech_assignments[tech] = []
                tech_assignments[tech].append(entry)
        
        # Create CSV files for each tech
        for tech, assignments in tech_assignments.items():
            filename = f"{client}_{tech}.csv".replace(" ", "_")
            df_tech = pd.DataFrame(assignments)
            csv_data = df_tech.to_csv(index=False)
            client_tech_files[filename] = csv_data
    
    return client_tech_files


def main():
    # Header
    st.markdown('<div class="main-header">📅 CareCoordinator Viewer</div>', unsafe_allow_html=True)
    st.markdown('<div class="sub-header">Upload your Excel schedule file to view it in an interactive calendar format</div>', unsafe_allow_html=True)
    
    # Legend
    st.markdown("""
    <div style="background: white; padding: 15px; border-radius: 10px; margin-bottom: 20px; box-shadow: 0 2px 4px rgba(0,0,0,0.1);">
        <strong style="font-size: 1.1rem;">📖 Legend:</strong>
        <div class="legend-item">
            <div class="legend-color" style="background: linear-gradient(135deg, #ffeaa7, #fdcb6e);"></div>
            <span>Nap Time</span>
        </div>
        <div class="legend-item">
            <div class="legend-color" style="background: linear-gradient(135deg, #d6eaf8, #aed6f1);"></div>
            <span>Assigned Tech</span>
        </div>
        <div class="legend-item">
            <div class="legend-color" style="background: #f8f9fa; border: 2px solid #bdc3c7;"></div>
            <span>No Assignment</span>
        </div>
    </div>
    """, unsafe_allow_html=True)
    
    # File upload
    uploaded_file = st.file_uploader(
        "📁 Upload Excel File", 
        type=['xlsx', 'xls'],
        help="Upload your schedule Excel file. First column should be time slots, first row should be client names."
    )
    
    # Use sample data if no file uploaded
    use_sample = st.checkbox("Use sample data for demonstration", value=(uploaded_file is None))
    
    if uploaded_file is not None or use_sample:
        if use_sample and uploaded_file is None:
            # Create sample Excel file in memory
            sample_df = create_sample_data()
            excel_buffer = io.BytesIO()
            sample_df.to_excel(excel_buffer, index=False, engine='openpyxl')
            excel_buffer.seek(0)
            uploaded_file = excel_buffer
            st.info("🔬 Using sample data for demonstration")
        
        # Parse the Excel file
        with st.spinner("🔄 Processing Excel file..."):
            schedule_data = parse_excel_file(uploaded_file)
        
        if schedule_data:
            st.success(f"✅ Successfully loaded schedule with {len(schedule_data['clients'])} clients and {len(schedule_data['times'])} time slots")
            
            # Calculate and display statistics
            stats = calculate_statistics(schedule_data)
            col1, col2, col3, col4 = st.columns(4)
            
            with col1:
                st.markdown(f'<div class="stats-card"><h3>👥 Clients</h3><h2>{stats["total_clients"]}</h2></div>', unsafe_allow_html=True)
            with col2:
                st.markdown(f'<div class="stats-card"><h3>⏰ Assignments</h3><h2>{stats["total_assignments"]}</h2></div>', unsafe_allow_html=True)
            with col3:
                st.markdown(f'<div class="stats-card"><h3>😴 Nap Times</h3><h2>{stats["nap_slots"]}</h2></div>', unsafe_allow_html=True)
            with col4:
                st.markdown(f'<div class="stats-card"><h3>📊 Time Slots</h3><h2>{stats["total_timeslots"]}</h2></div>', unsafe_allow_html=True)
            
            st.markdown("---")
            
            # Render the condensed view
            render_condensed_view(schedule_data)
            
            # Export section
            st.markdown("---")
            st.subheader("📤 Export Data")
            
            # Create downloadable data
            export_data = []
            for i, time in enumerate(schedule_data['times']):
                row = {'Time Slot': time}
                for j, client in enumerate(schedule_data['clients']):
                    row[client] = schedule_data['assignments'][i][j]
                export_data.append(row)
            
            df_export = pd.DataFrame(export_data)
            
            # CSV download
            csv = df_export.to_csv(index=False)
            st.download_button(
                label="📥 Download as CSV",
                data=csv,
                file_name="schedule_export.csv",
                mime="text/csv",
                help="Download the schedule data as CSV file")

                        # SOAP Notes Section
            st.markdown("---")
            st.subheader("📝 SOAP Notes of the Clients")
            
            # Generate SOAP notes table
            soap_notes_df = generate_soap_notes_table(schedule_data, uploaded_file)
            
            if not soap_notes_df.empty:
                # Display the table
                st.dataframe(
                    soap_notes_df,
                    use_container_width=True,
                    height=min(400, 35 * len(soap_notes_df) + 38)
                )
                
                # Show the extracted date
                schedule_date = extract_date_from_excel(uploaded_file)
                st.info(f"📅 Schedule Date: {schedule_date}")
                
                # Export individual client-tech files
                st.markdown("**Export Individual Client-Tech Files:**")
                client_tech_files = export_individual_client_tech_files(schedule_data, uploaded_file)
                
                if client_tech_files:
                    col1, col2 = st.columns([3, 1])
                    
                    with col1:
                        # Dropdown to select individual files
                        selected_file = st.selectbox(
                            "Select file to download:",
                            options=list(client_tech_files.keys())
                        )
                    
                    with col2:
                        if selected_file:
                            st.download_button(
                                label=f"📥 {selected_file}",
                                data=client_tech_files[selected_file],
                                file_name=selected_file,
                                mime="text/csv",
                                key=f"download_{selected_file}"
                            )
                    
                    # Bulk download all files as ZIP
                    import zipfile
                    
                    if st.button("📦 Download All as ZIP", key="download_all_zip"):
                        zip_buffer = io.BytesIO()
                        with zipfile.ZipFile(zip_buffer, 'w') as zip_file:
                            for filename, csv_data in client_tech_files.items():
                                zip_file.writestr(filename, csv_data)
                        zip_buffer.seek(0)
                        
                        st.download_button(
                            label="📥 Download ZIP File",
                            data=zip_buffer,
                            file_name="client_tech_files.zip",
                            mime="application/zip",
                            key="download_zip"
                        )
            else:
                st.info("No client-tech assignments found for SOAP notes.")
            
            
    
    else:
        # Show instructions when no file is uploaded
        st.info("👆 Please upload an Excel file or check 'Use sample data' to get started")
        
        # Example of expected format
        with st.expander("📋 Expected Excel File Format"):
            st.markdown("""
            ### Expected Excel Structure:
            
            | Time of Day | Client A | Client B | Client C | ... |
            |-------------|----------|----------|----------|-----|
            | 0700 - 0730 |          | Tech1    |          | ... |
            | 0730 - 0800 | Tech2    | Tech1    |          | ... |
            | 0800 - 0830 | Tech2    |          | Tech3    | ... |
            | ...         | ...      | ...      | ...      | ... |
            | NAP TIME    | NAP TIME | Tech4    | NAP TIME | ... |
            
            **Requirements:**
            - **First column**: Time slots (e.g., "0700 - 0730")
            - **First row**: Client names as headers  
            - **Data cells**: Technician names or "NAP TIME"
            - **File format**: .xlsx or .xls
            """)
            
            # Show sample data preview
            st.markdown("### Sample Data Preview:")
            sample_df = create_sample_data()
            st.dataframe(sample_df, use_container_width=True)

if __name__ == "__main__":
    main()