Code Editor for app.py

import streamlit as st
import pandas as pd
import plotly.express as px
from datetime import datetime, timedelta
from sqlalchemy import create_engine, Column, Integer, Float, String, Date
from sqlalchemy.orm import DeclarativeBase, Session

# Database setup
class Base(DeclarativeBase):
    pass

class ManHourRecord(Base):
    __tablename__ = 'manhours'
    
    id = Column(Integer, primary_key=True)
    date = Column(Date)
    platform = Column(String)
    department = Column(String)
    personnel_count = Column(Integer)
    hours_worked = Column(Float)
    total_manhours = Column(Float)

# Create database engine
engine = create_engine('sqlite:///offshore_manhours.sqlite')
Base.metadata.create_all(bind=engine)

# Page config
st.set_page_config(page_title="Offshore Man-Hours Tracking", layout="wide")
st.title("⛽ Offshore Man-Hours Tracking System")

# Sidebar for data entry
with st.sidebar:
    st.header("Add New Record")
    
    # Input fields
    date_input = st.date_input("Date", datetime.now())
    platform = st.selectbox("Platform", ["Platform A", "Platform B", "Platform C"])
    department = st.selectbox("Department", [
        "Operations",
        "Maintenance",
        "Drilling",
        "HSE",
        "Catering",
        "Marine"
    ])
    personnel_count = st.number_input("Number of Personnel", min_value=1, value=1)
    hours_worked = st.number_input("Hours Worked", min_value=0.0, max_value=24.0, value=12.0)
    
    if st.button("Add Record"):
        total_hours = personnel_count * hours_worked
        new_record = ManHourRecord(
            date=date_input,
            platform=platform,
            department=department,
            personnel_count=personnel_count,
            hours_worked=hours_worked,
            total_manhours=total_hours
        )
        
        with Session(engine) as session:
            session.add(new_record)
            session.commit()
        st.success("Record added successfully!")

# Main content
col1, col2 = st.columns([2, 1])

with col1:
    st.subheader("Man-Hours Overview")
    
    # Date range filter
    date_range = st.date_input(
        "Select Date Range",
        value=(datetime.now() - timedelta(days=30), datetime.now()),
        key="date_range"
    )
    
    # Load data
    with Session(engine) as session:
        query = f"""
        SELECT * FROM manhours 
        WHERE date BETWEEN '{date_range[0]}' AND '{date_range[1]}'
        """
        df = pd.read_sql(query, engine)
    
    if not df.empty:
        # Summary metrics
        total_manhours = df['total_manhours'].sum()
        avg_daily_hours = df.groupby('date')['total_manhours'].sum().mean()
        
        # Metrics display
        metrics_col1, metrics_col2 = st.columns(2)
        metrics_col1.metric("Total Man-Hours", f"{total_manhours:,.0f}")
        metrics_col2.metric("Average Daily Hours", f"{avg_daily_hours:,.1f}")
        
        # Charts
        tab1, tab2 = st.tabs(["By Platform", "By Department"])
        
        with tab1:
            fig1 = px.bar(
                df.groupby(['date', 'platform'])['total_manhours'].sum().reset_index(),
                x='date',
                y='total_manhours',
                color='platform',
                title="Man-Hours by Platform",
                labels={'total_manhours': 'Total Man-Hours', 'date': 'Date'}
            )
            st.plotly_chart(fig1, use_container_width=True)
            
        with tab2:
            fig2 = px.bar(
                df.groupby(['date', 'department'])['total_manhours'].sum().reset_index(),
                x='date',
                y='total_manhours',
                color='department',
                title="Man-Hours by Department",
                labels={'total_manhours': 'Total Man-Hours', 'date': 'Date'}
            )
            st.plotly_chart(fig2, use_container_width=True)
    else:
        st.info("No data available for the selected date range.")

with col2:
    st.subheader("Recent Records")
    if not df.empty:
        # Show recent records table
        st.dataframe(
            df.sort_values('date', ascending=False)
            [['date', 'platform', 'department', 'personnel_count', 'total_manhours']]
            .head(10),
            hide_index=True
        )
        
        # Download button
        csv = df.to_csv(index=False)
        st.download_button(
            label="Download Data",
            data=csv,
            file_name="offshore_manhours.csv",
            mime="text/csv"
        )
    else:
        st.info("No recent records to display.")