Py.Cafe

############ Imports ##############
import vizro.models as vm
import vizro.figures as vf
from vizro.models.types import capture
from vizro import Vizro
import pandas as pd
from vizro.managers import data_manager
import plotly.graph_objects as go
import re
from datetime import datetime


####### Helper Functions for Data Preprocessing ######
def parse_planisware_date(value) -> pd.Timestamp:
    """Parse mixed Planisware date formats into a pandas Timestamp (or NaT)."""
    if pd.isna(value):
        return pd.NaT
    s = str(value).strip()
    if not s or s.lower() == 'nan':
        return pd.NaT

    # Examples: 121825123125 (MMDDYYhhmmss)
    if re.fullmatch(r'\d{12}', s):
        try:
            return pd.Timestamp(datetime.strptime(s, '%m%d%y%H%M%S'))
        except ValueError:
            return pd.NaT

    # Examples: 070125 (MMDDYY)
    if re.fullmatch(r'\d{6}', s):
        try:
            return pd.Timestamp(datetime.strptime(s, '%m%d%y'))
        except ValueError:
            return pd.NaT

    # Fallback for standard formats
    return pd.to_datetime(s, errors='coerce')


def normalize_dim(series: pd.Series, unknown_label: str = 'Unknown') -> pd.Series:
    """Strip whitespace and replace blanks/NaN with 'Unknown'."""
    s = series.fillna('').astype(str).str.strip()
    s = s.replace({'': unknown_label, 'nan': unknown_label, 'None': unknown_label})
    return s


def preprocess_planisware_data(df):
    """Clean and preprocess the Planisware data."""
    # Handle ID column
    id_col = 'qa_id'
    df[id_col] = normalize_dim(df.get(id_col, pd.Series([None] * len(df))), unknown_label='')
    
    # If IDs are blank, fallback to index
    mask_blank_id = df[id_col].eq('')
    df.loc[mask_blank_id, id_col] = df.loc[mask_blank_id].index.astype(str)

    # Normalize dimensions
    cols_to_normalize = [
        'Submitted by', 'Business/Project Owner', 'Global Approver', 
        'Projected Timeline', 'Idea status', 'Project Location'
    ]
    for col in cols_to_normalize:
        if col in df.columns:
            df[col] = normalize_dim(df[col])

    # Date Parsing
    if 'Date Created' in df.columns:
        df['_created_dt'] = df['Date Created'].apply(parse_planisware_date)
        # Create YYYY-MM string for grouping
        df['Created YM'] = df['_created_dt'].dt.to_period('M').astype(str)
        df['Created YM'] = df['Created YM'].replace({'NaT': 'Unknown'}).fillna('Unknown')
    
    return df


####### Custom Chart Functions ######
@capture("graph")
def submitter_bar_chart(data_frame):
    """Horizontal bar chart showing top 15 submitters by project count."""
    # Aggregate data
    data = (
        data_frame.groupby("Submitted by")["qa_id"]
        .nunique()
        .reset_index(name="Projects")
        .sort_values("Projects", ascending=False)
        .head(15)
        .sort_values("Projects", ascending=True)
    )

    # Create figure
    fig = go.Figure()
    fig.add_trace(
        go.Bar(
            x=data["Projects"],
            y=data["Submitted by"],
            orientation="h",
            marker=dict(color="#636EFA"),
        )
    )

    fig.update_layout(
        xaxis_title="Number of Projects",
        yaxis_title="",
        height=400,
        margin=dict(l=20, r=20, t=10, b=20),
        plot_bgcolor="rgba(0,0,0,0)",
        paper_bgcolor="rgba(0,0,0,0)",
    )

    return fig


@capture("graph")
def monthly_trend_chart(data_frame):
    """Bar chart showing projects created by month."""
    # Aggregate data by month
    data = (
        data_frame[data_frame["Created YM"] != "Unknown"]
        .groupby("Created YM")["qa_id"]
        .nunique()
        .reset_index(name="Projects")
        .sort_values("Created YM")
    )

    # Create figure
    fig = go.Figure()
    fig.add_trace(
        go.Bar(x=data["Created YM"], y=data["Projects"], marker=dict(color="#636EFA"))
    )

    fig.update_layout(
        xaxis_title="Month",
        yaxis_title="Number of Projects",
        height=400,
        margin=dict(l=20, r=20, t=10, b=20),
        plot_bgcolor="rgba(0,0,0,0)",
        paper_bgcolor="rgba(0,0,0,0)",
    )

    fig.update_xaxes(tickangle=-45)

    return fig


@capture("graph")
def timeline_donut_chart(data_frame):
    """Donut chart showing projects by projected timeline."""
    # Aggregate data
    data = (
        data_frame.groupby("Projected Timeline")["qa_id"]
        .nunique()
        .reset_index(name="Projects")
        .sort_values("Projects", ascending=False)
    )

    # Create figure
    fig = go.Figure()
    fig.add_trace(
        go.Pie(
            labels=data["Projected Timeline"],
            values=data["Projects"],
            hole=0.5,
            textposition="inside",
            textinfo="percent+label",
        )
    )

    fig.update_layout(
        height=400,
        margin=dict(l=20, r=20, t=10, b=20),
        showlegend=False,
        paper_bgcolor="rgba(0,0,0,0)",
    )

    return fig


@capture("graph")
def status_donut_chart(data_frame):
    """Donut chart showing projects by status."""
    # Aggregate data
    data = (
        data_frame.groupby("Idea status")["qa_id"]
        .nunique()
        .reset_index(name="Projects")
        .sort_values("Projects", ascending=False)
    )

    # Create figure
    fig = go.Figure()
    fig.add_trace(
        go.Pie(
            labels=data["Idea status"],
            values=data["Projects"],
            hole=0.5,
            textposition="inside",
            textinfo="percent+label",
        )
    )

    fig.update_layout(
        height=400,
        margin=dict(l=20, r=20, t=10, b=20),
        showlegend=False,
        paper_bgcolor="rgba(0,0,0,0)",
    )

    return fig


@capture("graph")
def location_bar_chart(data_frame):
    """Horizontal bar chart showing top 15 locations by project count."""
    # Aggregate data
    data = (
        data_frame.groupby("Project Location")["qa_id"]
        .nunique()
        .reset_index(name="Projects")
        .sort_values("Projects", ascending=False)
        .head(15)
        .sort_values("Projects", ascending=True)
    )

    # Create figure
    fig = go.Figure()
    fig.add_trace(
        go.Bar(
            x=data["Projects"],
            y=data["Project Location"],
            orientation="h",
            marker=dict(color="#636EFA"),
        )
    )

    fig.update_layout(
        xaxis_title="Number of Projects",
        yaxis_title="",
        height=400,
        margin=dict(l=20, r=20, t=10, b=20),
        plot_bgcolor="rgba(0,0,0,0)",
        paper_bgcolor="rgba(0,0,0,0)",
    )

    return fig


####### Data Manager Settings #####
# Load and preprocess data
df = pd.read_csv("Planisware-Idea-Details-Data.csv")
df = preprocess_planisware_data(df)
data_manager["planisware_data"] = df


########### Dashboard Model ############
dashboard = vm.Dashboard(
    pages=[
        vm.Page(
            components=[
                # Row 1: KPI Cards
                vm.Container(
                    components=[
                        vm.Figure(
                            id="kpi_total_projects",
                            figure=vf.kpi_card(
                                data_frame="planisware_data",
                                value_column="qa_id",
                                value_format="{value:,.0f}",
                                agg_func="nunique",
                                title="Total Projects",
                                icon="folder",
                            ),
                        ),
                        vm.Figure(
                            id="kpi_total_submitter",
                            figure=vf.kpi_card(
                                data_frame="planisware_data",
                                value_column="Submitted by",
                                value_format="{value:,.0f}",
                                agg_func="nunique",
                                title="Total Submitters",
                                icon="person",
                            ),
                        ),
                        vm.Figure(
                            id="kpi_total_owner",
                            figure=vf.kpi_card(
                                data_frame="planisware_data",
                                value_column="Business/Project Owner",
                                value_format="{value:,.0f}",
                                agg_func="nunique",
                                title="Total Owners",
                                icon="business_center",
                            ),
                        ),
                        vm.Figure(
                            id="kpi_total_approver",
                            figure=vf.kpi_card(
                                data_frame="planisware_data",
                                value_column="Global Approver",
                                value_format="{value:,.0f}",
                                agg_func="nunique",
                                title="Total Approvers",
                                icon="check_circle",
                            ),
                        ),
                    ],
                    layout=vm.Flex(direction="row", gap="160px", wrap=False),
                ),
                # Row 2: Submitter and Monthly Trend Charts
                vm.Container(
                    components=[
                        vm.Graph(
                            id="submitter_chart",
                            figure=submitter_bar_chart(data_frame="planisware_data"),
                            title="Total Projects by Submitter",
                        ),
                        vm.Graph(
                            id="monthly_trend_chart",
                            figure=monthly_trend_chart(data_frame="planisware_data"),
                            title="Total Projects Created by Month",
                        ),
                    ],
                    layout=vm.Flex(direction="row", gap="16px", wrap=False),
                ),
                # Row 3: Timeline, Status, and Location Charts
                vm.Container(
                    components=[
                        vm.Graph(
                            id="timeline_chart",
                            figure=timeline_donut_chart(data_frame="planisware_data"),
                            title="Total Projects by Timeline",
                        ),
                        vm.Graph(
                            id="status_chart",
                            figure=status_donut_chart(data_frame="planisware_data"),
                            title="Total Projects by Status",
                        ),
                        vm.Graph(
                            id="location_chart",
                            figure=location_bar_chart(data_frame="planisware_data"),
                            title="Total Projects by Location",
                        ),
                    ],
                    layout=vm.Flex(direction="row", gap="16px", wrap=False),
                ),
            ],
            title="Planisware Project Overview",
            layout=vm.Flex(direction="column", gap="24px"),
        )
    ],
    theme="vizro_light",
    title="Planisware Dashboard",
)

# Build and run the app
app = Vizro().build(dashboard)

if __name__ == "__main__":
    app.run(debug=True, port=8050)