import streamlit as st
import pandas as pd
import altair as alt

st.set_page_config(page_title="Shipment Monitoring Dashboard", layout="wide")
st.title("Shipment Monitoring Dashboard")

tabs = st.tabs(["Main Dashboard", "Last Mile Summary"])

with tabs[0]:
    uploaded_file = st.file_uploader("Upload your Excel file", type=["xlsx"], key="main_upload")

    if uploaded_file:
        df = pd.read_excel(uploaded_file, sheet_name=0)

        # Rename columns
        df.columns = ['MAWB', 'MAWB Serial Number', 'Injection Gateway', 'Clearance Port', 'Latest Status',
                      'Cartons', 'Packages', 'Weights', 'Not online packages count', 'ETD', 'ETA', 'ATD', 'ATA',
                      'Last Mile Carrier', 'Readynotice has been notified', 'Vehicle has been arranged',
                      'Pick-upcompletedfrom', 'WH-IN Scan', 'Cargo Ready', 'Ship Out', 'Note',
                      'Exception Note', 'Exception Note Description']

        # Add status column
        def determine_status(row):
            if pd.isna(row['WH-IN Scan']):
                return "Pending"
            elif pd.isna(row['Cargo Ready']):
                return "WH-IN"
            elif pd.isna(row['Ship Out']):
                return "Cargo Ready"
            else:
                return "Ship Out"

        df['Shipment Status'] = df.apply(determine_status, axis=1)
        df['ETA'] = pd.to_datetime(df['ETA'])
        df['ATA'] = pd.to_datetime(df['ATA'])

        # Fallback date: ATA if available, otherwise ETA
        df['Final Arrival Date'] = df['ATA'].fillna(df['ETA'])
        df['Final Arrival Date'] = pd.to_datetime(df['Final Arrival Date']).dt.date

        # Filter for Injection Gateway
        injection_gateways = df['Injection Gateway'].dropna().unique().tolist()
        selected_gateway = st.selectbox("Filter by Injection Gateway", options=["All"] + injection_gateways)
        if selected_gateway != "All":
            df = df[df['Injection Gateway'] == selected_gateway]

        # Filter by ETA date range
        st.subheader("Filter by ETA Date Range")
        min_date, max_date = df['ETA'].min(), df['ETA'].max()
        start_date, end_date = st.date_input("Select ETA date range:", [min_date, max_date], min_value=min_date, max_value=max_date)
        df = df[(df['ETA'] >= pd.to_datetime(start_date)) & (df['ETA'] <= pd.to_datetime(end_date))]

        # Shipment status distribution
        st.subheader("Shipment Status Distribution")
        status_order = ["Pending", "WH-IN", "Cargo Ready", "Ship Out"]
        status_counts = df['Shipment Status'].value_counts().reindex(status_order).reset_index()
        status_counts.columns = ['Shipment Status', 'Count']
        status_counts = status_counts.dropna()

        chart = alt.Chart(status_counts).mark_bar().encode(
            x=alt.X('Shipment Status', sort=status_order),
            y='Count',
            color='Shipment Status',
            tooltip=['Shipment Status', 'Count']
        ).properties(height=300)

        st.altair_chart(chart, use_container_width=True)

        # Volume by final arrival date (ATA preferred, fallback to ETA)
        st.subheader("Shipment Volume by ETA")
        volume_by_arrival = df.groupby('Final Arrival Date').agg({
            'MAWB': 'count',
            'Cartons': 'sum',
            'Packages': 'sum',
            'Weights': 'sum'
        }).reset_index().rename(columns={'MAWB': 'Shipment Count'})

        st.dataframe(volume_by_arrival, use_container_width=True)

        line_chart = alt.Chart(volume_by_arrival).mark_line(point=True).encode(
            x='Final Arrival Date:T',
            y='Cartons:Q',
            tooltip=['Final Arrival Date:T', 'Shipment Count:Q', 'Cartons:Q', 'Packages:Q', 'Weights:Q']
        ).properties(height=400)

        st.altair_chart(line_chart, use_container_width=True)

        # Status filter before detail view
        st.subheader("Shipment Detail View by ETA")
        status_options = ["All"] + status_order
        selected_status = st.radio("Select a Shipment Status to filter the details below:", options=status_options, index=0)

        if selected_status == "All":
            filtered_df = df.copy()
        else:
            filtered_df = df[df['Shipment Status'] == selected_status]

        sorted_df = filtered_df[['MAWB', 'ETA', 'ATA', 'Shipment Status', 'Cartons', 'Last Mile Carrier']].sort_values(by='ETA')
        st.dataframe(sorted_df, use_container_width=True)

        # Add download as Excel button
        import io

        excel_buffer = io.BytesIO()
        with pd.ExcelWriter(excel_buffer, engine='xlsxwriter') as writer:
            sorted_df.to_excel(writer, index=False, sheet_name='Shipment Details')
        excel_data = excel_buffer.getvalue()

        st.download_button(
            label="📥 Download Shipment Details as Excel",
            data=excel_data,
            file_name="shipment_details.xlsx",
            mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )

with tabs[1]:
    st.header("Upload CTN File for Last Mile Summary")
    ctn_files = st.file_uploader("Upload one or more Excel files with Last Mile Service", 
                                  type=["xlsx"], key="ctn_upload", accept_multiple_files=True)

    if ctn_files:
        df_list = []
        for file in ctn_files:
            df_part = pd.read_excel(file, sheet_name=0)
            df_list.append(df_part)

        df_ctn = pd.concat(df_list, ignore_index=True)

        # Columns of interest
        carton_col = "No. Large box"
        last_mile_col = "Last mile service"

        # ✅ Count unique cartons by last mile service
        st.subheader("Unique Cartons by Last Mile Service")
        if carton_col in df_ctn.columns and last_mile_col in df_ctn.columns:
            unique_summary = (
                df_ctn.groupby(last_mile_col)[carton_col]
                .nunique()
                .reset_index(name="Unique Carton Count")
            )
            st.dataframe(unique_summary, use_container_width=True)
        else:
            st.warning(f"Columns '{carton_col}' or '{last_mile_col}' not found in uploaded file(s).")