Py.Cafe

###################################################################################################################################
# Library Imports
###################################################################################################################################
# --- Standard Libraries ---
import base64
import warnings
from datetime import datetime
import os
import sys

# --- Data Handling and Analysis ---
import pandas as pd

# --- GUI and Interactive Components ---
import panel as pn
from dateutil.relativedelta import relativedelta
import plotly.express as px
import plotly.graph_objects as go
import re

# --- Visualization Backend ---
# Required for Panel and Bokeh-based plots
import holoviews as hv

# --- Extensions and Global Configuration
# Suppress all warnings
warnings.filterwarnings("ignore")

# Initialize Panel, HoloViews, and Tabulator extensions
hv.extension('bokeh')
pn.extension('plotly')

##############################################################################################################################
# Define Pop-up windows for Project selection 
##############################################################################################################################
#Simplied Project call-out
Project = 'NorthropGrumman'

##############################################################################################################################
# Define date and path & Load workbook
##############################################################################################################################
# Define paths and file names
project_input = f'./{Project}.xlsx' #  current working directory,

# Load the Excel files into pandas DataFrames
try:

    df_Timeline = pd.read_excel(project_input, sheet_name='Timeline', index_col=False) 
    df_Priority = None
    df_Backlog = None
    df_SciformaReport_Team = pd.read_excel(project_input, sheet_name='SciformaReport_Team', index_col=False) 
    df_Project_charter = pd.read_excel(project_input, sheet_name='Project-Charter', index_col=False) 
    df_Project_Risk = pd.read_excel(project_input, sheet_name='Risk', index_col=False) 
    df_Project_phases = pd.read_excel(project_input, sheet_name='Phases', index_col=False)
    df_Project_EffortDistribution = pd.read_excel(project_input, sheet_name='Effort', index_col=False)
    df_rate = pd.read_excel(project_input, sheet_name='Hourly-Rate', index_col=False)
    df_Roadmapping = pd.read_excel(project_input, sheet_name='Roadmapping', index_col=False)
    df_UnitCost = pd.read_excel(project_input, sheet_name='UnitCost', index_col=False)
    df_Settings = pd.read_excel(project_input, sheet_name='Settings', index_col=False)
    print("Input files loaded successfully.") 
except FileNotFoundError as e:
    print(f"File not found: {e}")
    exit()


#------------------------------------------------------------------------------------------
# Setting the tracking date beginning of June
#------------------------------------------------------------------------------------------
# Extract and convert date from column named 'Start tracking'
if 'Start tracking' in df_Settings.columns:
    value = df_Settings['Start tracking'].dropna().values[0]
    start_tracking_date = pd.to_datetime(value) if value else None
else:
    start_tracking_date = datetime.today()

# Print in short format (YYYY-MM-DD) if date exists
if start_tracking_date:
    print('start_tracking_date:', start_tracking_date.strftime('%Y-%m-%d'))
else:
    print('start_tracking_date: None')

#start_tracking_date = pd.to_datetime('2025-05-01') 


#------------------------------------------------------------------------------------------
# Merge df_Project_EffortDistribution with df_SciformaReport_Team on 'Function' and 'EWO'
#------------------------------------------------------------------------------------------
# Define the key columns for grouping
key_cols = ['EWO', 'Program#', 'Program', 'Function']

# 1. Ensure month columns are strings (important if any are datetime objects)
df1 = df_Project_EffortDistribution.copy()
df2 = df_SciformaReport_Team.copy()

df1.columns = df1.columns.astype(str)
df2.columns = df2.columns.astype(str)

# 2. Get the full set of all columns (especially months)
all_columns = sorted(set(df1.columns).union(df2.columns))

# 3. Reindex both to align columns and fill missing with 0
df1 = df1.reindex(columns=all_columns, fill_value=0)
df2 = df2.reindex(columns=all_columns)

# 4. Concatenate both
combined_df = pd.concat([df1, df2], ignore_index=True)

# 5. Group by key columns and sum all numeric (month) values
df_SciformaReport_Team_Project = combined_df.groupby(key_cols, as_index=False).sum()

# Separate metadata and month columns
meta_cols = key_cols
all_month_cols = [col for col in df_SciformaReport_Team_Project.columns if col not in meta_cols]

# Filter month columns to keep only those >= start_tracking_date
filtered_month_cols = []
for col in all_month_cols:
    try:
        col_date = pd.to_datetime(col, format='%Y-%m')
        if col_date >= start_tracking_date:
            filtered_month_cols.append(col)
    except:
        # If column is not a date, ignore it
        pass

# Reorder columns to have meta + filtered months only
df_SciformaReport_Team_Project = df_SciformaReport_Team_Project[meta_cols + filtered_month_cols]

# Display the filtered DataFrame
#display(df_SciformaReport_Team_Project)

#------------------------------------------------------------------------------------------
# Create df_SciformaReport_Team_Project_Expense based on df_SciformaReport_Team_Project and df_rate
#------------------------------------------------------------------------------------------
# Create a copy of df_SciformaReport_Team_Project to store expenses
df_SciformaReport_Team_Project_Expense = df_SciformaReport_Team_Project.copy()

# Extract non-numeric columns to preserve them
non_numeric_cols = ['EWO', 'Program#', 'Program', 'Function']
numeric_cols = [col for col in df_SciformaReport_Team_Project.columns if col not in non_numeric_cols]

# Create a dictionary for rates from df_rate for easier lookup
rate_dict = df_rate.set_index('Function')[['Rate 2025 USD', 'Rate 2026 USD']].to_dict()

# Function to determine the rate based on the year of the column
def get_rate(function, column):
    year = int(column.split('-')[0])
    if year >= 2026:
        return rate_dict['Rate 2026 USD'].get(function, 0)
    else:
        return rate_dict['Rate 2025 USD'].get(function, 0)

# Calculate expenses for each numeric column
for col in numeric_cols:
    df_SciformaReport_Team_Project_Expense[col] = df_SciformaReport_Team_Project.apply(
        lambda row: row[col] * get_rate(row['Function'], col), axis=1
    )

# Display the result
#print('df_SciformaReport_Team_Project_Expense:')
#display(df_SciformaReport_Team_Project_Expense)

#------------------------------------------------------------------------------------------
# Display the DataFrames
#------------------------------------------------------------------------------------------
#print("\ndf_Timeline:")
#display(df_Timeline)
#print("\ndf_SciformaReport_Team:") # Contain ONLY the Project data 
#display(df_SciformaReport_Team)
#print("\ndf_Project_charter:") 
#display(df_Project_charter)
#print("\ndf_Project_Risk:") 
#display(df_Project_Risk)
#print("\ndf_Project_phases:") 
#display(df_Project_phases)
#print("\ndf_Roadmapping:") 
#display(df_Roadmapping)
#print('df_UnitCost')
#display(df_UnitCost)
#print('df_PromptvsGate')
#display(df_PromptvsGate)

#------------------------------------------------------------------------------------------
# Define latest_month_text
#------------------------------------------------------------------------------------------
# Define global variable
latest_month_text = None

# Step 1: Extract all month columns (e.g., columns starting with 'YYYY-MM')
all_months = [
    col for col in df_SciformaReport_Team_Project.columns
    if col[:4].isdigit() and col[4] == '-'
]

# Step 2: Find latest month with valid actual data (EV or AC hours > 0)
actual_months = [
    m for m in all_months
    if df_SciformaReport_Team_Project.loc[
        (df_SciformaReport_Team_Project['EWO'] != 'Planned (NorthropGrumman)') &
        (~df_SciformaReport_Team_Project['Function'].isin(['External (EE)', 'External (TM)'])), m
    ].sum() > 0 or
    df_SciformaReport_Team_Project.loc[
        (df_SciformaReport_Team_Project['EWO'] != 'Planned (NorthropGrumman)') &
        (~df_SciformaReport_Team_Project['Function'].isin(['External (EE)', 'External (TM)', 'Earn Value Balance'])), m
    ].sum() > 0
]

# Step 3: Set the global latest_month_text if valid actuals are found
if not actual_months:
    print("❌ No valid actuals found in any month for non-External roles.")
else:
    # Convert 'YYYY-MM' to 'Month YYYY' (e.g., '2025-06' to 'June 2025')
    latest_month = max(actual_months)
    latest_month_text = datetime.strptime(latest_month, '%Y-%m').strftime('%B %Y')

print('Latest month:', latest_month_text)

#------------------------------------------------------------------------------------------
# Defining phase calor
#------------------------------------------------------------------------------------------
'''
# Define phase colors
phase_colors = {
    'S3': '#88aee1',
    'S4': '#d8bfe3',
    'S5': '#a8b6d9',
    'S6': '#f6b17c',
    'S7': '#FFD966',
    'S8': '#aec7a3'
}
'''

# Define phase colors
phase_colors = {
    'S3': '#88aee1',       # Unique color for S3
    'S4': '#d8bfe3',       # Unique color for S4
    'S5': '#a8b6d9',       # Unique color for S5
    'S6': '#f6b17c',       # Same color as ECO
    'ECO': '#f6b17c',      # ECO color identical to S6
    'S7': '#FFD966',       # Same color as Proto
    'Proto': '#FFD966',    # Proto color identical to S7
    'S8': '#aec7a3',       # Same color as Prod
    'Prod': '#aec7a3'      # Prod color identical to S8
}


#------------------------------------------------------------------------------------------
# Defining card function
#------------------------------------------------------------------------------------------
def create_card_indicators(card_title, panel_object, styles):
    # Ensure the panel object has responsive sizing
    panel_object.sizing_mode = 'stretch_both'

    # Create inline HTML for the card header and card styles (border, round corners)
    card_style = f"""
    <style>
        .custom-card {{
            border: 2px solid {styles["background_color"]};  /* Border matching header background */
            border-radius: 10px;  /* Rounded corners for the card */
            overflow: hidden;  /* Ensure content fits within the card */
            box-shadow: 2px 2px 10px rgba(0, 0, 0, 0.1);  /* Add a subtle shadow for aesthetics */
        }}
        .custom-card-header {{
            background-color: {styles["background_color"]};  /* Header background */
            color: {styles["font_color"]};  /* Header font color */
            padding: 10px;
            font-size: 16px;
            text-align: center;
            font-weight: bold;  /* Make text bold */
            width: 100%;
            border-top-left-radius: 10px;  /* Rounded top corners */
            border-top-right-radius: 10px;  /* Rounded top corners */
        }}
    </style>
    """
    header_html = f"<div class='custom-card-header'>{card_title}</div>"

    # Create the card layout with the header and the panel object
    card = pn.Column(
        pn.pane.HTML(card_style + header_html),  # Header with custom style
        panel_object,  # The panel object (e.g., plot or indicator)
        width=styles["width"],
        height=styles["height"],
        sizing_mode='fixed',
        margin=styles["margin"],
        css_classes=['custom-card']  # Applying the custom card style
    )

    return card

#||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
#*****************************************************************************************************************************
##############################################################################################################################
# Tab  |Dev Dashboard|
##############################################################################################################################
#*****************************************************************************************************************************
#||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

# Define card styles
card_styles = {
    "Project Charter": {
        "background_color": "#98c6e5",
        "font_color": "white",
        "width": 600,
        "height": 500,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "EVM": {
        "background_color": "#98c6e5",
        "font_color": "white",
        "width": 360,
        "height": 500,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "Milestone and Tasks Follow-up": {
        "background_color": "#98c6e5",
        "font_color": "white",
        "width": 1050,
        "height": 500,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "Phase Indicator": {
        "background_color": "#98c6e5",
        "font_color": "white",
        "width": 800,
        "height": 500,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "Project Timeline": {
        "background_color": "#98c6e5",
        "font_color": "white",
        "width": 1220,
        "height": 650,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "NRE Tracking per Phase": {
        "background_color": "#aee0d9",
        "font_color": "white",
        "width": 800,
        "height": 650,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "NRC Follow-up per Phase": {
        "background_color": "#aee0d9",
        "font_color": "white",
        "width": 800,
        "height": 650,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "Roadmapping": {
        "background_color": "#98c6e5",
        "font_color": "white",
        "width": 450,
        "height": 650,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "Risk Map": {
        "background_color": "#ff6f61",
        "font_color": "white",
        "width": 750,
        "height": 650,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "NRE Tracking Total": {
        "background_color": "#aee0d9",
        "font_color": "white",
        "width": 800,
        "height": 650,
        "margin": (5, 5),
        "font_weight": "bold"
    },
    "Total Expense": {
        "background_color": "#aee0d9",
        "font_color": "white",
        "width": 800,
        "height": 650,
        "margin": (5, 5),
        "font_weight": "bold"
    }
}


# Panel style for the card 
common_panel_object_styles = { 
    'background': 'white',
    'padding': '20px',
    'border-radius': '8px',
    'box-shadow': '0 4px 6px rgba(0,0,0,0.1)',
    'border': '1px solid #ddd',
    'margin': '5px',  # shorthand for uniform spacing
}


#--------------------------------------------------------------------------------------------------------------------------
# Create card 'Project Charter'
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_Project_charter
#--------------------------------------------------------------------------------------------------------------------------

def get_base64_image(image_path):
    with open(image_path, "rb") as f:
        encoded = base64.b64encode(f.read()).decode("utf-8")
    return f"data:image/jpeg;base64,{encoded}"

def create_project_charter():
    try:
        df = df_Project_charter.copy()
        df.columns = ['Label', 'Value']

        # Encode logos
        safran_logo = get_base64_image('./safran-logo.png')
        project_logo = get_base64_image(f'./{Project}-logo.jpg')

        # Start HTML
        html_content = f"""
        <div style='display: flex; justify-content: space-between; align-items: center; width: 100%; margin-bottom: 40px;'>
            <div style='flex: 1; text-align: left;'>
                <img src='{safran_logo}' alt='Safran Logo' style='height: 30px; object-fit: contain;'>
            </div>
            <div style='flex: 1; text-align: center;'>
                <img src='{project_logo}' alt='{Project} Logo' style='height: 40px; object-fit: contain;'>
            </div>
            <div style='flex: 1;'></div>
        </div>
        <div style='width: 100%;'>
            <table style='width: 100%; border-collapse: separate; border-spacing: 0 4px; font-size: 14px;'>
        """

        for _, row in df.iterrows():
            label = row['Label']
            value = row['Value']

            if label == 'Latest Program Update':
                html_content += f"""
                    <tr>
                        <td colspan='2' style='background-color: #D9E1F2;
                                                border: 2px solid #D6E0F2;
                                                padding: 8px 12px;
                                                border-radius: 6px;'>
                            <span style='font-weight: bold;'>{label}:</span> {value}
                        </td>
                    </tr>
                """
            else:
                html_content += f"""
                    <tr>
                        <td style='font-weight: bold; padding: 6px 12px; vertical-align: top; white-space: nowrap;'>{label}</td>
                        <td style='padding: 6px 12px; vertical-align: top;'>{value}</td>
                    </tr>
                """

        html_content += """
            </table>
        </div>
        """

        panel_object = pn.pane.HTML(
            html_content,
            sizing_mode='stretch_both',
            styles=common_panel_object_styles,
            name='Project Charter'
        )

        return create_card_indicators("Project Charter", panel_object, card_styles["Project Charter"])

    except Exception as e:
        panel_object = pn.pane.HTML(
            f"""
            <div class='bg-white p-6 rounded-lg shadow-md'>
                <h2 class='text-xl font-bold mb-4'>Project Charter</h2>
                <p>Error displaying project charter: {str(e)}</p>
            </div>
            """,
            sizing_mode='stretch_both',
        )
        return create_card_indicators("Project Charter", panel_object, card_styles["Project Charter"])

#--------------------------------------------------------------------------------------------------------------------------
# Create card for 'SPI_CPI 
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_SciformaReport_Team_Project and df_SciformaReport_Team_Project_Expense

# EVM function
#--------------------------------------------------------------------------------------------------------------------------
color_map = {
    "red": "#ff4c4c",
    "yellow": "#ffd700",
    "green": "#4caf50",
    "off": "#666666"  # dimmed light
}

def create_spi_cpi():
    # --------------------------------------------
    # Step 1: Identify valid month columns
    # --------------------------------------------
    all_months = [
        col for col in df_SciformaReport_Team_Project.columns
        if col[:4].isdigit() and col[4] == '-'
    ]

    # Step 2: Find latest month with valid actual data (EV or AC hours > 0)
    actual_months = [
        m for m in all_months
        if df_SciformaReport_Team_Project.loc[
            (df_SciformaReport_Team_Project['EWO'] != 'Planned (NorthropGrumman)') &
            (~df_SciformaReport_Team_Project['Function'].isin(['External (EE)', 'External (TM)'])), m
        ].sum() > 0 or
        df_SciformaReport_Team_Project.loc[
            (df_SciformaReport_Team_Project['EWO'] != 'Planned (NorthropGrumman)') &
            (~df_SciformaReport_Team_Project['Function'].isin(['External (EE)', 'External (TM)', 'Earn Value Balance'])), m
        ].sum() > 0
    ]

    if not actual_months:
        print("❌ No valid actuals found in any month for non-External roles.")
        # Handle the case where no actual months are found.
        # We still need to return a Panel object.
        description_html_pane = pn.pane.HTML(
            """
            <div style="text-align: left; padding-top: 0px; padding-bottom: 0px;">
                <p style="margin: 0;"><strong>SPI:</strong> Schedule Performance Index.</p>
                <p style="margin: 0;"><strong>CPI:</strong> Cost Performance Index</p>
            </div>
            """,
            sizing_mode='stretch_width'
        )
        
        error_content = pn.Column(
            description_html_pane, # Add the description
            pn.pane.HTML("<div>No valid actuals data available to calculate SPI/CPI.</div>", sizing_mode='stretch_width'),
            sizing_mode='stretch_width',
            styles=common_panel_object_styles
        )
        return create_card_indicators("EVM", error_content, card_styles["EVM"])

    latest_month = max(actual_months)

    # --------------------------------------------
    # Step 3: Earned Value (EV) — actual hours, including Earn Value Balance
    # --------------------------------------------
    ev_df = df_SciformaReport_Team_Project[
        (df_SciformaReport_Team_Project['EWO'] != 'Planned (NorthropGrumman)') &
        (df_SciformaReport_Team_Project[latest_month] > 0) &
        (~df_SciformaReport_Team_Project['Function'].isin(['External (EE)', 'External (TM)']))
    ]
    ev_hour = ev_df[latest_month].sum()

    # Validate with df_SciformaReport_Team if available
    if latest_month in df_SciformaReport_Team.columns:
        team_ev_df = df_SciformaReport_Team[
            (df_SciformaReport_Team['EWO'] != 'Planned (NorthropGrumman)') &
            (df_SciformaReport_Team[latest_month] > 0) &
            (~df_SciformaReport_Team['Function'].isin(['External (EE)', 'External (TM)']))
        ]
        team_ev_hour = team_ev_df[latest_month].sum()
        if team_ev_hour != ev_hour:
            print(f"Warning: EV Hours mismatch between df_SciformaReport_Team_Project ({ev_hour}) and df_SciformaReport_Team ({team_ev_hour}) for {latest_month}.")

    # --------------------------------------------
    # Step 4: Actual Cost (AC) Hours — actual hours, excluding Earn Value Balance
    # --------------------------------------------
    ac_hours_df = df_SciformaReport_Team_Project[
        (df_SciformaReport_Team_Project['EWO'] != 'Planned (NorthropGrumman)') &
        (df_SciformaReport_Team_Project['Function'].isin([role for role in ev_df['Function'].unique() if role != 'Earn Value Balance'])) &
        (~df_SciformaReport_Team_Project['Function'].isin(['External (EE)', 'External (TM)', 'Earn Value Balance']))
    ]
    if latest_month in ac_hours_df.columns:
        ac_hours_df[latest_month] = pd.to_numeric(ac_hours_df[latest_month], errors='coerce')
        ac_hours = ac_hours_df[latest_month].sum()
        if pd.isna(ac_hours) or ac_hours <= 0:
            print(f"Warning: Actual Cost Hours for {latest_month} is zero or invalid, setting AC Hours to 0.")
            ac_hours = 0
    else:
        print(f"Error: Column {latest_month} not found in df_SciformaReport_Team_Project.")
        ac_hours = 0

    # --------------------------------------------
    # Step 5: Planned Value (PV) — planned hours for all non-External roles with planned hours
    # --------------------------------------------
    pv_df = df_SciformaReport_Team_Project[
        (df_SciformaReport_Team_Project['EWO'] == 'Planned (NorthropGrumman)') &
        (~df_SciformaReport_Team_Project['Function'].isin(['External (EE)', 'External (TM)'])) &
        (df_SciformaReport_Team_Project[latest_month] > 0)
    ]
    pv_hour = pv_df[latest_month].sum()

    # --------------------------------------------
    # Step 6: Calculate SPI and CPI
    # --------------------------------------------
    # SPI = Earned Value (EV) Hours / Planned Value (PV) Hours
    # CPI = Earned Value (EV) Hours / Actual Cost (AC) Hours
    spi = ev_hour / pv_hour if pv_hour > 0 else 0
    cpi = ev_hour / ac_hours if ac_hours > 0 else 0

    # --------------------------------------------
    # Step 7: Output (Optional, for debugging)
    # --------------------------------------------
    '''
    print(f"Latest Month: {latest_month}")
    print(f"EV (Earned Value hours): {ev_hour:,.2f}")
    print(f"AC (Actual Cost hours): {ac_hours:,.2f}")
    print(f"PV (Planned Value hours): {pv_hour:,.2f}")
    print(f"SPI (Schedule Performance Index): {spi:.2f}")
    print(f"CPI (Cost Performance Index): {cpi:.2f}")
    '''
    # --------------------------------------------
    # HTML Visual
    # --------------------------------------------
    # Assign traffic lights
    spi_light = 'red' if spi < 0.9 else 'yellow' if spi < 1.0 else 'green'
    cpi_light = 'red' if cpi < 0.9 else 'yellow' if cpi < 1.0 else 'green'

    # The markdown-like description # 07/23 seems to introduce spacing at bottom of the card
    description_html_pane = pn.pane.HTML(
        """
        <div style="text-align: left; padding-top: 0px; padding-bottom: 0px;">
            <p style="margin: 0;"><strong>SPI:</strong> Schedule Performance Index.</p>
            <p style="margin: 0;"><strong>CPI:</strong> Cost Performance Index</p>
        </div>
        """,
        sizing_mode='stretch_width',
        height = 0,
        margin=0,  # Explicitly set margin to 0 to prevent extra spacing
    )

    # HTML content using CSS Grid for the gauges
    html_gauges_content = f"""
    <div style='display: flex; justify-content: center; width: 100%; height: 360px; background-color: white; margin: 25px auto 0 auto; padding: 0;'>
        <div class='p-1 rounded-lg shadow-md' style='width: 360px; height: 360px; background-color: white; color: black; font-weight: bold; margin: 5px;'>
            <div style='display: grid; grid-template-columns: 1fr 0.25fr 1fr; gap: 12px; align-items: flex-start; height: 100%; background-color: white; padding-top: 10px;'>
    
                <div style='text-align: center; padding: 4px; border-radius: 5px; height: 100%; display: flex; flex-direction: column; justify-content: flex-start;'>
                    <div style='width: 80px; height: 220px; background: #333; border-radius: 12px; margin: 0 auto;'>
                        <div style='width: 50px; height: 50px; background: {color_map["red"] if spi_light=="red" else color_map["off"]}; border-radius: 50%; margin: 10px auto;'></div>
                        <div style='width: 50px; height: 50px; background: {color_map["yellow"] if spi_light=="yellow" else color_map["off"]}; border-radius: 50%; margin: 10px auto;'></div>
                        <div style='width: 50px; height: 50px; background: {color_map["green"] if spi_light=="green" else color_map["off"]}; border-radius: 50%; margin: 10px auto;'></div>
                    </div>
                    <div style='border: 2px solid {color_map[spi_light]}; border-radius: 5px; padding: 2px; display: inline-block; margin-top: 4px;'>
                        <span style='font-weight: bold; font-size: 1.3em;'>{spi:.2f}</span>
                    </div>
                    <p style='margin-top: 2px; font-size: 1em;'>SPI</p>
                </div>
    
                <div style='width: 30px; height: 300px; background: white; border-radius: 12px; position: relative; margin: 0 auto; align-self: flex-start;'>
                    <div style='width: 100%; height: 33%; background: {color_map["red"]};'></div>
                    <div style='width: 100%; height: 33%; background: {color_map["yellow"]}; position: absolute; top: 33%;'></div>
                    <div style='width: 100%; height: 34%; background: {color_map["green"]}; position: absolute; top: 66%;'></div>
                    <div style='position: absolute; top: 16.5%; left: 50%; transform: translateX(-50%); font-size: 1em;'>0.85</div>
                    <div style='position: absolute; top: 49.5%; left: 50%; transform: translateX(-50%); font-size: 1em;'>0.90</div>
                    <div style='position: absolute; top: 82.5%; left: 50%; transform: translateX(-50%); font-size: 1em;'>1.00</div>
                </div>
    
                <div style='text-align: center; padding: 4px; border-radius: 5px; height: 100%; display: flex; flex-direction: column; justify-content: flex-start;'>
                    <div style='width: 80px; height: 220px; background: #333; border-radius: 12px; margin: 0 auto;'>
                        <div style='width: 50px; height: 50px; background: {color_map["red"] if cpi_light=="red" else color_map["off"]}; border-radius: 50%; margin: 10px auto;'></div>
                        <div style='width: 50px; height: 50px; background: {color_map["yellow"] if cpi_light=="yellow" else color_map["off"]}; border-radius: 50%; margin: 10px auto;'></div>
                        <div style='width: 50px; height: 50px; background: {color_map["green"] if cpi_light=="green" else color_map["off"]}; border-radius: 50%; margin: 10px auto;'></div>
                    </div>
                    <div style='border: 2px solid {color_map[cpi_light]}; border-radius: 5px; padding: 2px; display: inline-block; margin-top: 4px;'>
                        <span style='font-weight: bold; font-size: 1.3em;'>{cpi:.2f}</span>
                    </div>
                    <p style='margin-top: 2px; font-size: 1em;'>CPI</p>
                </div>
    
            </div>
        </div>
    </div>
    """

    html_gauges_pane = pn.pane.HTML(
        html_gauges_content,
        sizing_mode='stretch_width' 
    )

    # Combine the description and the HTML gauges pane into a single Panel Column.
    # This Column will be the 'panel_object' passed to create_card_indicators.
    combined_panel_object_content = pn.Column(
        description_html_pane, # The new description comes first
        html_gauges_pane,      # Then your existing HTML content
        sizing_mode='stretch_width',
        styles=common_panel_object_styles # Apply the common styles to the entire content block
    )

    return create_card_indicators("EVM", combined_panel_object_content, card_styles["EVM"])

#--------------------------------------------------------------------------------------------------------------------------
# Create card 'Millestone and Tasks Follow-up'
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_Timeline
# Milestone and Tasks Follow-up function
#--------------------------------------------------------------------------------------------------------------------------
def create_milestone_tasks_followup():
    try:
        # Check for DataFrame and required columns
        required_columns = ['ID', 'Task', 'Description', 'Start', 'End', '% Completion']
        if df_Timeline.empty or not all(col in df_Timeline.columns for col in required_columns):
            panel_object = pn.pane.HTML(
                """
                <div class='bg-white rounded-lg shadow-md'>
                    <p>No tasks available or required columns missing.</p>
                </div>
                """,
                width=card_styles["Milestone and Tasks Follow-up"]["width"],
                height=card_styles["Milestone and Tasks Follow-up"]["height"]
            )
            return create_card_indicators("Milestone and Tasks Follow-up", panel_object, card_styles["Milestone and Tasks Follow-up"])

        # Filter out tasks where Description is 'Milestone' (case-insensitive)
        styled_df = df_Timeline[df_Timeline['Description'].str.lower() != 'milestone'][['Task', 'Description', 'Start', 'End', '% Completion']].copy()

        # Add revised date columns if missing
        for col in ['Revised Start', 'Revised End']:
            if col not in df_Timeline.columns:
                df_Timeline[col] = pd.NaT
        
        # Prepare dataframe and include revised dates if available
        styled_df = df_Timeline[df_Timeline['Description'].str.lower() != 'milestone'][['Task', 'Description', 'Start', 'End', 'Revised Start', 'Revised End', '% Completion']].copy()
        
        # Convert all date columns to datetime
        for col in ['Start', 'End', 'Revised Start', 'Revised End']:
            styled_df[col] = pd.to_datetime(styled_df[col], errors='coerce')
        
        # Use revised dates if available
        styled_df['Start_Final'] = styled_df['Revised Start'].combine_first(styled_df['Start'])
        styled_df['End_Final'] = styled_df['Revised End'].combine_first(styled_df['End'])
        
        # Sort by final start date
        styled_df = styled_df.sort_values('Start_Final')
        
        # Format for display
        styled_df['Start'] = styled_df['Start_Final'].apply(lambda x: x.strftime('%m-%d-%Y') if pd.notnull(x) else '')
        styled_df['End'] = styled_df['End_Final'].apply(lambda x: x.strftime('%m-%d-%Y') if pd.notnull(x) else '')
        default_color = '#A5A5A5'  # Default color for unassigned or out-of-phase tasks
        
        # Initialize phase dividers
        phase_dividers = {}
        valid_phases = []
        if not df_Project_phases.empty and all(col in df_Project_phases.columns for col in ['Phase', 'Start date', 'End date']):
            df_Project_phases['Start date'] = pd.to_datetime(df_Project_phases['Start date'], errors='coerce')
            df_Project_phases['End date'] = pd.to_datetime(df_Project_phases['End date'], errors='coerce')
            phase_dates = df_Project_phases.groupby('Phase').agg({
                'Start date': 'min',
                'End date': 'max'
            }).reset_index().sort_values(by='Start date').reset_index(drop=True)
            
            # Adjust phase start dates to avoid overlaps
            for i in range(1, len(phase_dates)):
                phase_dates.loc[i, 'Start date'] = max(phase_dates.loc[i, 'Start date'], phase_dates.loc[i-1, 'End date'])
            
            # Populate phase dividers
            for _, phase in phase_dates.iterrows():
                phase_name = phase['Phase']
                start_date = phase['Start date']
                end_date = phase['End date']
                if pd.notnull(start_date) and pd.notnull(end_date):
                    phase_dividers[phase_name] = (start_date, end_date)
                    valid_phases.append(phase_name)

        # Assign phases to tasks dynamically
        styled_df['Phase'] = 'Unassigned'
        for phase_name, (start_date, end_date) in phase_dividers.items():
            mask = (styled_df['Start_Final'].notna()) & (styled_df['Start_Final'] >= start_date) & (styled_df['Start_Final'] <= end_date)
            styled_df.loc[mask, 'Phase'] = phase_name
        
        # Assign tasks outside defined phases
        if not phase_dividers:
            valid_phases = ['Unassigned']
            phase_colors['Unassigned'] = default_color
        else:
            min_phase_date = min(phase_dividers.values(), key=lambda x: x[0])[0] if phase_dividers else pd.Timestamp.now()
            max_phase_date = max(phase_dividers.values(), key=lambda x: x[1])[1] if phase_dividers else pd.Timestamp.now()
            styled_df.loc[styled_df['Start_Final'] < min_phase_date, 'Phase'] = 'Before First Phase'
            styled_df.loc[styled_df['Start_Final'] > max_phase_date, 'Phase'] = 'After Last Phase'
            valid_phases = ['Before First Phase'] + valid_phases + ['After Last Phase']
            phase_colors['Before First Phase'] = default_color
            phase_colors['After Last Phase'] = default_color

        # Calculate totals per phase
        total_tasks = styled_df.groupby('Phase').size().reindex(valid_phases, fill_value=0)
        completed_tasks = styled_df[styled_df['% Completion'].isin([1.0, 100.0, 100])].groupby('Phase').size().reindex(valid_phases, fill_value=0)
        not_started_tasks = styled_df[styled_df['% Completion'].isna()].groupby('Phase').size().reindex(valid_phases, fill_value=0)
        wip_tasks = styled_df[(~styled_df['% Completion'].isna()) & (~styled_df['% Completion'].isin([1.0, 100.0, 100])) & (styled_df['% Completion'] > 0)].groupby('Phase').size().reindex(valid_phases, fill_value=0)

        # Filter out phases with zero total tasks
        valid_phases = [phase for phase in valid_phases if total_tasks.get(phase, 0) > 0]

        # Generate legend HTML dynamically
        legend_html = """
        <div style='font-size: 12px; height: 15px; margin-bottom: 5px;'>
        """
        for phase in valid_phases:
            if phase not in ['Before First Phase', 'After Last Phase', 'Unassigned']:  # Skip non-project phases for legend
                legend_html += f"""
            <span style='background-color: {phase_colors.get(phase, default_color)}; padding: 2px 5px; margin-right: 5px;'>{phase}</span>
            """
        legend_html += """
        </div>
        """

        # Summary table HTML with aligned styling and phase colors
        summary_html = """
        <div style='margin-bottom: 10px;'>
            <table style='width: 100%; border-collapse: collapse; border: 1px solid #ddd;'>
                <thead>
                    <tr style='background-color: #f5f5f5;'>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center;'>Total # of Tasks</th>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center;'>Completed</th>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center;'>WIP</th>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center;'>Not Started</th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td style='padding: 4px; border: 1px solid #ddd; text-align: center;'>
                            <div style='display: flex; justify-content: space-around;'>"""
        for phase in valid_phases:
            summary_html += f"""
                <span style='background-color: {phase_colors.get(phase, default_color)}; padding: 4px 8px; border-radius: 4px;'>{total_tasks.get(phase, 0)}</span>"""
        summary_html += """
                            </div>
                        </td>
                        <td style='padding: 4px; border: 1px solid #ddd; text-align: center;'>
                            <div style='display: flex; justify-content: space-around;'>"""
        for phase in valid_phases:
            summary_html += f"""
                <span style='background-color: {phase_colors.get(phase, default_color)}; padding: 4px 8px; border-radius: 4px;'>{completed_tasks.get(phase, 0)}</span>"""
        summary_html += """
                            </div>
                        </td>
                        <td style='padding: 4px; border: 1px solid #ddd; text-align: center;'>
                            <div style='display: flex; justify-content: space-around;'>"""
        for phase in valid_phases:
            summary_html += f"""
                <span style='background-color: {phase_colors.get(phase, default_color)}; padding: 4px 8px; border-radius: 4px;'>{wip_tasks.get(phase, 0)}</span>"""
        summary_html += """
                            </div>
                        </td>
                        <td style='padding: 4px; border: 1px solid #ddd; text-align: center;'>
                            <div style='display: flex; justify-content: space-around;'>"""
        for phase in valid_phases:
            summary_html += f"""
                <span style='background-color: {phase_colors.get(phase, default_color)}; padding: 4px 8px; border-radius: 4px;'>{not_started_tasks.get(phase, 0)}</span>"""
        summary_html += """
                            </div>
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>
        """

        # Generate HTML for task table with aligned styling and colored phases
        table_html = """
        <div style='max-height: 300px; overflow-y: auto; padding: 0;'>
            <table style='width: 100%; border-collapse: collapse; border: 1px solid #ddd;'>
                <thead style='position: sticky; top: 0; background-color: #f5f5f5;'>
                    <tr>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center; width: 160px;'>Task</th>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center; width: 250px;'>Description</th>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center; width: 100px;'>Start</th>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center; width: 100px;'>End</th>
                        <th style='padding: 8px; border: 1px solid #ddd; text-align: center; width: 60px;'>% Completion</th>
                    </tr>
                </thead>
                <tbody>
        """
        current_phase = None
        for _, row in styled_df.iterrows():
            task_phase = row['Phase']
            
            if task_phase and task_phase != current_phase:
                start_date = phase_dividers.get(task_phase, (pd.NaT, pd.NaT))[0] if task_phase in phase_dividers else pd.NaT
                end_date = phase_dividers.get(task_phase, (pd.NaT, pd.NaT))[1] if task_phase in phase_dividers else pd.NaT
                start_str = start_date.strftime('%m-%d-%Y') if pd.notnull(start_date) else ''
                end_str = end_date.strftime('%m-%d-%Y') if pd.notnull(end_date) else ''
                color = phase_colors.get(task_phase, default_color)
                table_html += f"""
                    <tr style='background-color: {color};'>
                        <td colspan='5' style='padding: 8px; text-align: center;'>
                            <strong>{task_phase}</strong> ({start_str} - {end_str})
                        </td>
                    </tr>
                """
                current_phase = task_phase

            completion = row['% Completion'] if pd.notnull(row['% Completion']) else 0
            bar_width = max(0, min(100, completion * 100)) if isinstance(completion, (int, float)) else 0
            table_html += f"""
                <tr>
                    <td style='padding: 8px; border: 1px solid #ddd; text-align: center;'>{row['Task']}</td>
                    <td style='padding: 8px; border: 1px solid #ddd; text-align: center;'>{row['Description']}</td>
                    <td style='padding: 8px; border: 1px solid #ddd; text-align: center;'>{row['Start']}</td>
                    <td style='padding: 8px; border: 1px solid #ddd; text-align: center;'>{row['End']}</td>
                    <td style='padding: 8px; border: 1px solid #ddd; text-align: center;'>
                        <div style='width: 120px; background-color: #e0e0e0; border-radius: 5px; height: 15px;'>
                            <div style='width: {bar_width}%; background-color: #4CAF50; height: 100%; border-radius: 5px; text-align: center; color: white; line-height: 15px;'>
                                {int(bar_width)}%
                            </div>
                        </div>
                    </td>
                </tr>
            """
        table_html += """
                </tbody>
            </table>
        </div>
        """

        # Combine legend, summary, and task table with aligned container
        combined_html = f"""
        <div class='bg-white rounded-lg shadow-md' style='padding: 10px;'>
            {legend_html}
            {summary_html}
            {table_html}
        </div>
        """

        panel_object = pn.pane.HTML(
            combined_html,
            width=card_styles["Milestone and Tasks Follow-up"]["width"],
            height=card_styles["Milestone and Tasks Follow-up"]["height"],
            styles={
                'background': 'white',
                'padding': '5px',
                'border-radius': '8px',
                'box-shadow': '0 4px 6px rgba(0,0,0,0.1)',
                'border': '1px solid #ddd',
                'margin': '5px',
            }
        )
        return create_card_indicators("Milestone and Tasks Follow-up", panel_object, card_styles["Milestone and Tasks Follow-up"])
    except Exception as e:
        panel_object = pn.pane.HTML(
            f"""
            <div class='bg-white p-6 rounded-lg shadow-md'>
                <p>Error processing data: {str(e)}</p>
            </div>
            """,
            width=card_styles["Milestone and Tasks Follow-up"]["width"],
            height=card_styles["Milestone and Tasks Follow-up"]["height"]
        )
        return create_card_indicators("Milestone and Tasks Follow-up", panel_object, card_styles["Milestone and Tasks Follow-up"])

#-------------------------------------------------------------------------------------------------------------------------
# Create card 'Phase Indicator'
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_Project_phases
#--------------------------------------------------------------------------------------------------------------------------

def create_phase_indicator():
    """
    Generates a Panel card with Plotly gauge figures for phase indicators in a 2x2 grid.
    Each gauge shows a 'Pty Indice' with a meter-like design, with the needle correctly
    positioned based on the current phase's progress. The size of each phase on the gauge
    is proportional to its actual duration.
    """
    try:
        # Clean and validate DataFrame
        df_Project_phases.columns = df_Project_phases.columns.str.strip()
        required_cols = ['Phase', 'Start date', 'End date', 'Pty Indice', 'Program#']
        if not all(col in df_Project_phases.columns for col in required_cols):
            raise ValueError(f"Missing columns: {', '.join([col for col in required_cols if col not in df_Project_phases.columns])}")

        df_Project_phases['Start date'] = pd.to_datetime(df_Project_phases['Start date'], errors='coerce')
        df_Project_phases['End date'] = pd.to_datetime(df_Project_phases['End date'], errors='coerce')

        # Use the last day of the month defined by latest_month_text
        if latest_month_text is None:
            raise ValueError("latest_month_text is not defined.")
        # Convert 'Month YYYY' (e.g., 'June 2025') to last day of that month
        last_day_of_month = pd.to_datetime(latest_month_text, format='%B %Y') + pd.offsets.MonthEnd(0)
        current_date = last_day_of_month

        unique_pty_indices = sorted(df_Project_phases['Pty Indice'].dropna().unique())

        # Create the HTML pane for the description
        phase_gauge_description = pn.pane.HTML(
            f"""
            <div style="text-align: left; padding-top: 1px; padding-bottom: 5px;">
                <p style="margin: 0;"><strong>Phase gauge:</strong> Expected project status at <b>{(pd.to_datetime(latest_month_text, format='%B %Y') + pd.offsets.MonthEnd(0)).strftime('%m/%d/%Y')}</b>.</p>
            </div>
            """,
            sizing_mode='stretch_width'
        )

        if not unique_pty_indices:
            return create_card_indicators(
                "Phase Indicator",
                pn.Column(
                    phase_gauge_description,
                    pn.pane.HTML("<div>No phase data available</div>", sizing_mode='stretch_width', height=card_styles["Phase Indicator"].get("height", 250)),
                    sizing_mode='stretch_width',
                    styles=common_panel_object_styles
                ),
                card_styles["Phase Indicator"]
            )

        # Grid and sizing setup
        n_cols, n_rows = 2, 2
        card_width = card_styles["Phase Indicator"].get("width", 800)
        card_height = card_styles["Phase Indicator"].get("height", 500)
        gauge_width = max(220, int((card_width - 80) / n_cols))
        gauge_height = max(183, int((card_height - 80) / n_rows))

        all_gauge_figures = []
        for pty_indice in unique_pty_indices[:4]:
            projects = df_Project_phases[df_Project_phases['Pty Indice'] == pty_indice].copy()
            
            percentage, phase_progress, needle_value = 0, 0, 0
            phase_label = "N/A"
            phase_ranges = {}

            valid_projects = projects[
                (projects['Start date'].notna()) &
                (projects['End date'].notna()) &
                (projects['End date'] > projects['Start date'])
            ].sort_values(by='Start date').reset_index()

            if not valid_projects.empty:
                valid_projects['duration'] = (valid_projects['End date'] - valid_projects['Start date']).dt.total_seconds()
                total_project_duration_secs = valid_projects['duration'].sum()
                
                cumulative_percentage = 0
                if total_project_duration_secs > 0:
                    for _, row in valid_projects.iterrows():
                        phase_percentage_size = (row['duration'] / total_project_duration_secs) * 100
                        start_range, end_range = cumulative_percentage, cumulative_percentage + phase_percentage_size
                        phase_ranges[row['Phase']] = [start_range, end_range]
                        cumulative_percentage = end_range
                    last_phase_name = valid_projects.iloc[-1]['Phase']
                    phase_ranges[last_phase_name][1] = 100.0

                total_start, total_end = valid_projects['Start date'].min(), valid_projects['End date'].max()
                
                if current_date < total_start:
                    phase_label = valid_projects.iloc[0]['Phase']
                elif current_date > total_end:
                    percentage, phase_progress, needle_value = 100, 100, 100
                    phase_label = valid_projects.iloc[-1]['Phase']
                else:
                    total_duration = (total_end - total_start).total_seconds()
                    if total_duration > 0:
                        percentage = max(0, min(100, ((current_date - total_start).total_seconds() / total_duration) * 100))
                    
                    active_phase = valid_projects[(valid_projects['Start date'] <= current_date) & (valid_projects['End date'] >= current_date)]
                    if not active_phase.empty:
                        current_phase_details = active_phase.iloc[0]
                        phase_label = current_phase_details['Phase']
                        if current_phase_details['duration'] > 0:
                            phase_progress = max(0, min(100, ((current_date - current_phase_details['Start date']).total_seconds() / current_phase_details['duration']) * 100))
                        
                        if phase_label in phase_ranges:
                            start_range, end_range = phase_ranges[phase_label]
                            needle_value = start_range + ((end_range - start_range) * (phase_progress / 100.0))
                    else:
                        completed_phases = valid_projects[valid_projects['End date'] < current_date]
                        if not completed_phases.empty:
                            last_phase = completed_phases.iloc[-1]
                            phase_label, phase_progress = last_phase['Phase'], 100
                            if phase_label in phase_ranges:
                                needle_value = phase_ranges[phase_label][1]

            # --- Figure Generation ---
            gauge_steps = [{'range': r, 'color': phase_colors.get(p, '#808080')} for p, r in phase_ranges.items()]
            
            tick_vals = [r[0] for r in phase_ranges.values()]
            tick_text = list(phase_ranges.keys())

            fig = go.Figure(go.Indicator(
                mode="gauge",
                value=needle_value,
                gauge={
                    'shape': "angular",
                    'axis': {'range': [0, 100], 'tickvals': tick_vals, 'ticktext': tick_text, 'tickwidth': 2, 'tickcolor': 'black', 'tickfont': {'size': 12}},
                    'bgcolor': 'white',
                    'borderwidth': 2,
                    'bordercolor': '#1e3a8a',
                    'steps': gauge_steps,
                    'threshold': {'line': {'color': 'red', 'width': 4}, 'thickness': 0.9, 'value': needle_value}
                }
            ))
            
            fig.add_annotation(
                x=0.5, y=1.25, xref="paper", yref="paper",
                text=f"<b>{pty_indice}</b>",
                showarrow=False, font=dict(size=16, color="#1e3a8a"), align="center"
            )
            
            fig.add_annotation(
                x=0.5, y=0.4, xref="paper", yref="paper",
                text=f"<b>Project: {percentage:.0f}%</b>",
                showarrow=False, font=dict(size=14, color="#1e3a8a"), align="center"
            )
            fig.add_annotation(
                x=0.5, y=0.15, xref="paper", yref="paper",
                text=f"Phase: {phase_label} ({phase_progress:.0f}%)",
                showarrow=False, font=dict(size=12, color="black"), align="center"
            )

            fig.update_layout(
                margin=dict(l=40, r=40, t=60, b=40),
                paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)',
                font=dict(family="Arial", color="#1e3a8a")
            )
            all_gauge_figures.append((pty_indice, fig))

        grid_layout = pn.GridSpec(ncols=n_cols, nrows=n_rows)
        for i, (pty_indice, fig) in enumerate(all_gauge_figures):
            row, col = i // n_cols, i % n_cols
            grid_layout[row, col] = pn.pane.Plotly(
                fig, 
                #height=gauge_height, 
                #width=gauge_width
            )
            
        combined_panel_object_content = pn.Column(
            phase_gauge_description,
            grid_layout,
            height=gauge_height, 
            width=gauge_width,
            styles=common_panel_object_styles
        )

        return create_card_indicators("Phase Indicator", combined_panel_object_content, card_styles["Phase Indicator"])

    except Exception as e:
        return create_card_indicators(
            "Phase Indicator",
            pn.Column(
                phase_gauge_description,
                pn.pane.HTML(f"<div>Error generating phase indicators: {e}</div>", sizing_mode='stretch_width', height=card_styles["Phase Indicator"].get("height", 250)),
                sizing_mode='stretch_width',
                styles=common_panel_object_styles
            ),
            card_styles["Phase Indicator"]
        )

#--------------------------------------------------------------------------------------------------------------------------
# Create card 'Project Timeline'
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_Timeline
# Project Timeline function
#--------------------------------------------------------------------------------------------------------------------------
# Variables 
one_month = relativedelta(months=1)

# Define colors for each category
category_colors = {
    'Project': '#1f77b4',
    'Documentation': '#9dcae6',
    'Engineering FTB': '#9dcae6',
    'Sourcing': '#ffc673',
    'Production': '#9bccb5', 
    'Prototyping': '#e5b6a5',
    'FAI': '#d1bbdf',
    'Vacation': '#D89794',
    'Rototellite': '#a3d5d3',
    'CPA': '#cfbcd5',
    'ISP': '#b4d7c1',
}

category_background_colors = {
    'Documentation': '#add8e6',
    'Engineering FTB': '#add8e6',
    'Sourcing': '#ffd6d1',
    'Production': '#bfeceb',
    'Project': '#87ceeb',
    'Prototyping': '#ffe5d4',
    'FAI': '#e6e6fa',
    'Vacation': '#F2DCDB',
    'Rototellite': 'white',
    'CPA': 'white',
    'ISP': 'white',
}

# Fluorescent colors for dependency arrows
Dependency_arrow_colors = {
    'Documentation': '#00DFDA',  # Fluorescent Cyan
    'Engineering FTB': '#00DFDA',  # Same as Documentation
    'Sourcing': '#fa9324',       # Fluorescent Orange
    'Production': '#00ff7f',     # Fluorescent Green 
    'Project': '#87ceeb',        # Sky Blue
    'FAI': '#771cb2',            # Purple 
    'Vacation': '#FF1493',       # Fluorescent Pink
    'Prototyping': '#ff4500',    # Fluorescent Orange Red
    'Rototellite': '#00bfff',    # Fluorescent Deep Sky Blue
    'CPA': '#ff00ff',            # Fluorescent Magenta
    'ISP': '#7fff00',            # Fluorescent Chartreuse
}

# Get the dimensions from your card_styles dictionary
timeline_card_style = card_styles.get("Project Timeline", {})
width = timeline_card_style.get("width", 1220)
height = timeline_card_style.get("height", 650)
#---------------------------------------------------
def calculate_dynamic_offset(start, finish, chart_width_ms, percentage=0.005, min_offset_ms=1000000):
    duration_ms = (finish - start).total_seconds() * 1000
    chart_offset = chart_width_ms * percentage
    return max(min_offset_ms, chart_offset)

def estimate_text_width_ms(text, chart_width_ms, chart_width_pixels=2400, avg_char_width_pixels=12):
    text_length = len(str(text))
    text_width_pixels = text_length * avg_char_width_pixels
    ms_per_pixel = chart_width_ms / chart_width_pixels
    return text_width_pixels * ms_per_pixel
    
#---------------------------------------------------
# 07/22 - create_project_timeline
#---------------------------------------------------
# 07/28
def create_project_timeline():
    try:
        # --- 1. Variable and DataFrame Validation ---
        df = df_Timeline.copy()  # Assumes df_Timeline is defined
        df = df[df['Category'] != 'Sourcing']
        proj_name = Project  # Assumes Project is defined
        
        required_columns = ['Task', 'Start', 'End', 'Category', 'Description', '% Completion', 'Comment']
        optional_columns = ['Revised Start', 'Revised End']
        if not all(col in df.columns for col in required_columns):
            raise ValueError(f"Missing one or more required columns: {required_columns}")

        df['Start'] = pd.to_datetime(df['Start'], errors='coerce')
        df['End'] = pd.to_datetime(df['End'], errors='coerce')
        if 'Revised Start' in df.columns:
            df['Revised Start'] = pd.to_datetime(df['Revised Start'], errors='coerce')
        if 'Revised End' in df.columns:
            df['Revised End'] = pd.to_datetime(df['Revised End'], errors='coerce')
        df.dropna(subset=['Start', 'End'], inplace=True)
        if 'Y_Label' not in df.columns:
            df['Y_Label'] = df['Task']

        # --- 2. Filter Milestones and Related Bars ---
        target_acronyms = ['ITR', 'PDR', 'CDR', 'AR', 'FAI', 'QRR', 'TTP', 'PRR', 'FSP']
        
        def contains_acronym(row):
            task_desc = f"{row['Task']} {row['Description']}"
            return any(acronym in task_desc for acronym in target_acronyms)

        milestones = df[(df['Start'] == df['End']) & df.apply(contains_acronym, axis=1)].copy()
        milestone_dates = milestones['End'].unique()
        related_bars = df[df['End'].isin(milestone_dates) & (df['Start'] != df['End'])].copy()

        if milestones.empty and related_bars.empty:
            raise ValueError(f"No milestones with target acronyms or related bars found.")

        # --- 3. Process Data for Gantt Chart ---
        milestone_categories = milestones.groupby('Category')['End'].max().sort_values().index.tolist()
        bar_categories = related_bars.groupby('Category')['End'].max().sort_values().index.tolist()

        gantt_data = []
        y_labels = []
        y_positions = []
        current_index = 0

        # Process Milestones
        for category in milestone_categories:
            if category in milestones['Category'].unique():
                y_labels.append(f"<b><span style='color:{category_colors.get(category, '#1f77b4')}'>MILESTONES: {category.upper()}</span></b>")
                y_positions.append(current_index)
                current_index += 1
                category_df = milestones[milestones['Category'] == category].sort_values('Start')
                for _, row in category_df.iterrows():
                    y_labels.append(f"  {row['Y_Label']}")
                    y_positions.append(current_index)
                    percentage = 1.0
                    if isinstance(row['Description'], str) and any(c.isdigit() for c in row['Description']):
                        percentage = float(next((float(s) for s in row['Description'].split() if s.replace('.','',1).isdigit()), 1.0))
                    
                    has_revised_end = 'Revised End' in df.columns and pd.notna(row['Revised End'])
                    milestone_date = row['Revised End'] if has_revised_end else row['End']

                    gantt_data.append({
                        'Task': f"  {row['Y_Label']}",
                        'Start': milestone_date,
                        'Finish': milestone_date,
                        'Resource': category,
                        'Color': category_colors.get(category, '#1f77b4'),
                        'Index': current_index,
                        'IsMilestone': True,
                        'Description': row['Description'],
                        'Percentage': percentage,
                        'Completion': row['% Completion'] if pd.notna(row['% Completion']) else 0.0,
                        'Comment': row['Comment'] if pd.notna(row['Comment']) else '',
                        'OriginalStart': row['Start'],
                        'OriginalEnd': row['End'],
                        'HasRevised': has_revised_end
                    })
                    
                    if has_revised_end:
                        gantt_data.append({
                            'Task': f"  {row['Y_Label']}",
                            'Start': row['End'],
                            'Finish': row['End'],
                            'Resource': category,
                            'Color': category_colors.get(category, '#1f77b4'),
                            'Index': current_index,
                            'IsMilestone': True,
                            'Description': row['Description'],
                            'Percentage': percentage,
                            'Completion': row['% Completion'] if pd.notna(row['% Completion']) else 0.0,
                            'Comment': row['Comment'] if pd.notna(row['Comment']) else '',
                            'OriginalStart': row['Start'],
                            'OriginalEnd': row['End'],
                            'IsOriginalRevisedMilestone': True,
                            'HasRevised': False
                        })
                    current_index += 1

        # Process Related Bars
        for category in bar_categories:
            if category in related_bars['Category'].unique():
                y_labels.append(f"<b><span style='color:{category_colors.get(category, '#1f77b4')}'>TIMELINES: {category.upper()}</span></b>")
                y_positions.append(current_index)
                current_index += 1
                category_df = related_bars[related_bars['Category'] == category].sort_values('Start')
                for _, row in category_df.iterrows():
                    y_labels.append(f"  {row['Y_Label']}")
                    y_positions.append(current_index)
                    percentage = 1.0
                    if isinstance(row['Description'], str) and any(c.isdigit() for c in row['Description']):
                        percentage = float(next((float(s) for s in row['Description'].split() if s.replace('.','',1).isdigit()), 1.0))
                    start_date = row['Start']
                    end_date = row['Revised End'] if 'Revised End' in df.columns and pd.notna(row['Revised End']) else row['End']
                    revised_start = row['Revised Start'] if 'Revised Start' in df.columns and pd.notna(row['Revised Start']) else start_date
                    gantt_data.append({
                        'Task': f"  {row['Y_Label']}",
                        'Start': start_date,
                        'Finish': end_date,
                        'RevisedStart': revised_start,
                        'Resource': category,
                        'Color': category_colors.get(category, '#1f77b4'),
                        'Index': current_index,
                        'IsMilestone': False,
                        'Description': row['Description'],
                        'Percentage': percentage,
                        'Completion': row['% Completion'] if pd.notna(row['% Completion']) else 0.0,
                        'Comment': row['Comment'] if pd.notna(row['Comment']) else '',
                        'OriginalStart': row['Start'],
                        'OriginalEnd': row['End'],
                        'HasRevised': ('Revised Start' in df.columns and pd.notna(row['Revised Start'])) or ('Revised End' in df.columns and pd.notna(row['Revised End']))
                    })
                    current_index += 1

        y_labels = list(reversed(y_labels))
        y_positions = list(reversed(y_positions))
        for item in gantt_data:
            item['Index'] = len(y_labels) - 1 - item['Index']

        def estimate_text_width_ms(text, chart_width_ms):
            char_width_ms = chart_width_ms / 100
            return len(str(text)) * char_width_ms

        def calculate_dynamic_offset(start, finish, chart_width_ms, percentage=0.015):
            if isinstance(start, (pd.Timestamp, datetime)) and isinstance(finish, (pd.Timestamp, datetime)):
                duration_ms = (finish - start).total_seconds() * 1000
            else:
                duration_ms = 0
            return chart_width_ms * percentage

        fig = go.Figure()

        chart_start_ms = min([d['Start'] for d in gantt_data]).timestamp() * 1000
        chart_end_ms = max([d['Finish'] for d in gantt_data]).timestamp() * 1000
        chart_width_ms = chart_end_ms - chart_start_ms

        for task in gantt_data:
            if not task['IsMilestone']:
                completion = task['Completion'] if task['Completion'] is not None else 0.0
                is_completed = isinstance(task['Description'], str) and 'completed' in task['Description'].lower()
                total_duration = (task['Finish'] - task['Start']).total_seconds() * 1000
                revised_duration = (task['Finish'] - task['RevisedStart']).total_seconds() * 1000 if task['HasRevised'] else 0
                original_delay = (task['RevisedStart'] - task['Start']).total_seconds() * 1000 if task['HasRevised'] else 0

                bar_color = category_colors.get(task['Resource'], '#1f77b4') if not is_completed else '#00fa85'
                if task['HasRevised']:
                    if original_delay > 0:
                        fig.add_trace(go.Bar(
                            x=[original_delay],
                            y=[task['Index']],
                            base=[task['Start'].timestamp() * 1000],
                            orientation='h',
                            marker=dict(
                                color='rgba(0,0,0,0)',
                                line=dict(color=bar_color, width=1),
                                pattern=dict(shape="/", fgcolor=bar_color, fgopacity=0.3)
                            ),
                            width=0.55,
                            showlegend=False,
                            hoverinfo='text',
                            hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Revised Start: {task['RevisedStart'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                            xaxis='x'
                        ))
                    fig.add_trace(go.Bar(
                        x=[revised_duration],
                        y=[task['Index']],
                        base=[task['RevisedStart'].timestamp() * 1000],
                        orientation='h',
                        marker=dict(color=bar_color, line=dict(color='black', width=1)),
                        width=0.55,
                        showlegend=False,
                        hoverinfo='text',
                        hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                        xaxis='x'
                    ))
                else:
                    if is_completed or completion <= 0:
                        fig.add_trace(go.Bar(
                            x=[total_duration],
                            y=[task['Index']],
                            base=[task['Start'].timestamp() * 1000],
                            orientation='h',
                            marker=dict(color=bar_color, line=dict(color='black', width=1)),
                            width=0.55,
                            showlegend=False,
                            hoverinfo='text',
                            hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                            xaxis='x'
                        ))
                    else:
                        completed_duration = total_duration * min(completion, 1.0)
                        remaining_duration = total_duration - completed_duration
                        fig.add_trace(go.Bar(
                            x=[completed_duration],
                            y=[task['Index']],
                            base=[task['Start'].timestamp() * 1000],
                            orientation='h',
                            marker=dict(color=Dependency_arrow_colors.get(task['Resource'], '#ff00ff'), line=dict(color='black', width=1)),  # Fluorescent for progression
                            width=0.55,
                            showlegend=False,
                            name='Completed',
                            hoverinfo='text',
                            hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                            xaxis='x'
                        ))
                        if remaining_duration > 0:
                            fig.add_trace(go.Bar(
                                x=[remaining_duration],
                                y=[task['Index']],
                                base=[task['Start'].timestamp() * 1000 + completed_duration],
                                orientation='h',
                                marker=dict(color=bar_color, line=dict(color='black', width=1)),
                                width=0.55,
                                showlegend=False,
                                name='Remaining',
                                hoverinfo='text',
                                hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                                xaxis='x'
                            ))

                start_text = "Initial Start: " if task['HasRevised'] else ""
                fig.add_annotation(
                    x=task['Start'].timestamp() * 1000,
                    y=task['Index'] + 0.2,
                    text=f"{start_text}<span style='color:black'>{task['Start'].strftime('%m/%d')}</span>",
                    showarrow=False,
                    font=dict(size=10, color=bar_color),
                    xanchor='left',
                    yanchor='bottom',
                    xref='x'
                )

                if task['HasRevised']:
                    fig.add_annotation(
                        x=task['RevisedStart'].timestamp() * 1000,
                        y=task['Index'] - 0.2,
                        text=f"Revised Start: <b><span style='color:black'>{task['RevisedStart'].strftime('%m/%d')}</span></b>",
                        showarrow=False,
                        font=dict(size=10, color=bar_color),
                        xanchor='left',
                        yanchor='top',
                        xref='x'
                    )

                    fig.add_annotation(
                        x=task['OriginalEnd'].timestamp() * 1000,
                        y=task['Index'] - 0.4,
                        text=f"Initial End: <span style='color:black'>{task['OriginalEnd'].strftime('%m/%d')}</span>",
                        showarrow=False,
                        font=dict(size=10, color=bar_color),
                        xanchor='left',
                        yanchor='top',
                        xref='x'
                    )

                fig.add_annotation(
                    x=task['Finish'].timestamp() * 1000 + 500000,
                    y=task['Index'],
                    text="▶",
                    showarrow=False,
                    font=dict(size=14, color=bar_color),
                    xanchor='center',
                    yanchor='middle',
                    xref='x'
                )

                if is_completed:
                    fig.add_annotation(
                        x=task['Start'].timestamp() * 1000 + (total_duration / 2),
                        y=task['Index'],
                        text="Completed",
                        showarrow=False,
                        font=dict(size=12, color='black', family='Arial', weight='bold'),
                        xanchor='center',
                        yanchor='middle',
                        xref='x'
                    )
                else:
                    display_text = f"{int(task['Percentage'] * 100)}%" if task['Percentage'] < 1.0 else task['Description']
                    fig.add_annotation(
                        x=task['Start'].timestamp() * 1000 + (completed_duration / 2) if task['Percentage'] < 1.0 else task['Start'].timestamp() * 1000 + (total_duration / 2),
                        y=task['Index'],
                        text=display_text,
                        showarrow=False,
                        font=dict(size=12, color='black', family='Arial', weight='bold'),
                        xanchor='center',
                        yanchor='middle',
                        xref='x'
                    )

                base_x = task['Finish'].timestamp() * 1000
                completion_offset = calculate_dynamic_offset(task['Start'], task['Finish'], chart_width_ms)
                padding_offset = calculate_dynamic_offset(task['Start'], task['Finish'], chart_width_ms, percentage=0.01)
                arrow_buffer = 1000000

                completion_x = base_x + completion_offset + arrow_buffer
                completion_text = ""
                has_completion = completion is not None and completion > 0
                if has_completion:
                    completion_text = f"{int(completion * 100)}%"
                    fig.add_annotation(
                        x=completion_x,
                        y=task['Index'] + 0.2,
                        text=completion_text,
                        showarrow=False,
                        font=dict(size=14, color=Dependency_arrow_colors.get(task['Resource'], '#00fa85') if completion > 0 else '#000000', family='Arial', weight='bold'),  # Fluorescent for % if > 0%
                        xanchor='left',
                        yanchor='middle',
                        xref='x'
                    )

                if task['Comment']:
                    if has_completion:
                        completion_width_ms = estimate_text_width_ms(completion_text, chart_width_ms)
                        comment_x = completion_x + completion_width_ms + padding_offset
                    else:
                        comment_x = base_x + completion_offset + arrow_buffer
                    fig.add_annotation(
                        x=comment_x,
                        y=task['Index'],
                        text=f"({task['Comment']})",
                        showarrow=False,
                        font=dict(size=12, color='black', family='Arial'),
                        xanchor='left',
                        yanchor='middle',
                        xref='x'
                    )

                # Add vertical gray bar for initial end, slightly longer than the bar
                bar_height_extension = 0.15  # Extend slightly above and below the bar
                fig.add_shape(
                    type="line",
                    x0=task['OriginalEnd'].timestamp() * 1000,
                    x1=task['OriginalEnd'].timestamp() * 1000,
                    y0=task['Index'] - 0.5 + bar_height_extension,
                    y1=task['Index'] + 0.5 - bar_height_extension,
                    line=dict(color="gray", width=3),
                    xref='x',
                    yref='y'
                )

        for task in gantt_data:
            if task['IsMilestone']:
                if task.get('IsOriginalRevisedMilestone', False):
                    fig.add_trace(go.Scatter(
                        x=[task['Start'].timestamp() * 1000],
                        y=[task['Index']],
                        mode='markers',
                        marker=dict(
                            symbol='star',
                            size=13,
                            color='gray'
                        ),
                        showlegend=False,
                        hoverinfo='text',
                        hovertext=f"<b>{task['Task'].strip()} (Original)</b><br>Date: {task['Start'].strftime('%Y-%m-%d')}",
                        xaxis='x'
                    ))
                    fig.add_annotation( 
                        x=task['Start'].timestamp() * 1000 - calculate_dynamic_offset(task['Start'], task['Finish'], chart_width_ms, percentage=0.1),
                        y=task['Index'] - 0.2,
                        text=f"<b><span style='color:gray'>was {task['Start'].strftime('%m/%d')}</span></b>",
                        showarrow=False,
                        font=dict(size=12, color='gray'),
                        xanchor='left',
                        yanchor='middle',
                        xref='x'
                    )
                else:
                    fig.add_trace(go.Scatter(
                        x=[task['Start'].timestamp() * 1000],
                        y=[task['Index']],
                        mode='markers',
                        marker=dict(
                            symbol='star',
                            size=13,
                            color=category_colors.get(task['Resource'], '#1f77b4')
                        ),
                        showlegend=False,
                        hoverinfo='text',
                        hovertext=f"<b>{task['Task'].strip()}</b><br>Date: {task['Start'].strftime('%Y-%m-%d')}",
                        xaxis='x'
                    ))
                    fig.add_annotation(
                        x=task['Start'].timestamp() * 1000 + calculate_dynamic_offset(task['Start'], task['Finish'], chart_width_ms, percentage=0.015),
                        y=task['Index'] - 0.2,
                        text=f"<b><span style='color:black'>{task['Task'].strip()} - </span><span style='color:{category_colors.get(task['Resource'], '#1f77b4')}'>{task['Start'].strftime('%m/%d')}</span></b>",
                        showarrow=False,
                        font=dict(size=12, color=category_colors.get(task['Resource'], '#1f77b4')),
                        xanchor='left',
                        yanchor='middle',
                        xref='x'
                    )
            
        today = datetime.now()
        fig.add_shape(
            type="line",
            x0=today.timestamp() * 1000,
            x1=today.timestamp() * 1000,
            y0=-0.5,
            y1=len(y_labels) - 0.5,
            line=dict(color="#006400", width=1, dash="dot"),
            xref='x',
            yref='y'
        )
        fig.add_annotation(
            x=today.timestamp() * 1000 + 500000,
            y=len(y_labels) - 0.5,
            text=f"<b>{today.strftime('%m/%d')}</b>",
            showarrow=False,
            font=dict(size=12, color="#006400"),
            xanchor='left',
            yanchor='top',
            xref='x'
        )

        one_month = relativedelta(months=2)
        start_date_gantt = min([d['Start'] for d in gantt_data]).replace(day=1)
        end_date_gantt = max([d['Finish'] for d in gantt_data])
        current_date_iter = start_date_gantt
        while current_date_iter <= end_date_gantt + relativedelta(months=1):
            fig.add_shape(
                type="line",
                x0=current_date_iter.timestamp() * 1000,
                x1=current_date_iter.timestamp() * 1000,
                y0=-0.5,
                y1=len(y_labels) - 0.5,
                line=dict(color="lightgray", width=1, dash="4,6"),
                layer='below',
                xref='x',
                yref='y'
            )
            current_date_iter += one_month
            current_date_iter = current_date_iter.replace(day=1)

        top_y_position = len(y_labels) + 0.5
        current_date_iter = start_date_gantt
        while current_date_iter <= end_date_gantt + relativedelta(months=1):
            fig.add_annotation(
                x=current_date_iter.timestamp() * 1000,
                y=top_y_position,
                text=current_date_iter.strftime('%b %d %Y'),
                showarrow=False,
                font=dict(size=12, color='black'),
                xanchor='center',
                yanchor='bottom',
                xref='x'
            )
            current_date_iter += one_month
            current_date_iter = current_date_iter.replace(day=1)

        x_min = min([d['Start'] for d in gantt_data]).timestamp() * 1000 - (30 * 24 * 60 * 60 * 1000)
        x_max = max([d['Finish'] for d in gantt_data]).timestamp() * 1000 + (30 * 24 * 60 * 60 * 1000)
        date_range = [x_min, x_max]

        fig.update_layout(
            title=dict(
                text=f'<b>{Project} - Gantt',
                font=dict(size=24, color='#002060'),
                x=0.05,
                xanchor='left'
            ),
            margin=dict(l=5, r=5, t=60, b=5),
            barmode='stack',
            xaxis=dict(
                type='date',
                tickformat='%b %d %Y',
                title='Date',
                tickfont=dict(size=14),
                showgrid=False,
                dtick="M1",
                range=date_range
            ),
            yaxis=dict(
                ticktext=y_labels,
                tickvals=list(range(len(y_labels))),
                title='Tasks',
                tickfont=dict(size=12),
                showgrid=False,
                range=[-0.5, len(y_labels) - 0.5]
            ),
            autosize=False, 
            height=1100, # Play on these values to define the size of the chart compare to the fixed size of the card.
            width=1800,
            plot_bgcolor='white',
            paper_bgcolor='white'
        )

        plotly_pane = pn.pane.Plotly(
            fig,
            sizing_mode='fixed',  # Disable auto-sizing
            config={'responsive': True}  # Prevent scaling
        )
        
        panel_object = pn.Column(
            plotly_pane,
            width=card_styles["Project Timeline"]["width"],
            height=card_styles["Project Timeline"]["height"],
            styles={
                "overflow": "auto",
                **common_panel_object_styles
            }
        )
        return create_card_indicators("Project Timeline", panel_object, card_styles["Project Timeline"])

    except (NameError, ValueError, KeyError) as e:
        print(f"Error creating project timeline: {e}")
        error_html = f"""
        <div class='bg-white p-4 rounded-lg shadow-md h-full flex flex-col'>
            <h2 class='text-xl font-bold mb-2 text-gray-700'>Project Timeline</h2>
            <div class='flex-grow flex items-center justify-center'>
                <p class='text-gray-500'>Timeline data not available or an error occurred: {e}</p>
            </div>
        </div>
        """
        panel_object = pn.pane.HTML(error_html, sizing_mode='stretch_both')
        return create_card_indicators("Project Timeline", panel_object, card_styles.get("Project Timeline", {}))



'''
def create_project_timeline():
    try:
        # --- 1. Variable and DataFrame Validation ---
        df = df_Timeline.copy()  # Assumes df_Timeline is defined
        df = df[df['Category'] != 'Sourcing']
        proj_name = Project  # Assumes Project is defined
        
        required_columns = ['Task', 'Start', 'End', 'Category', 'Description', '% Completion', 'Comment']
        optional_columns = ['Revised Start', 'Revised End']
        if not all(col in df.columns for col in required_columns):
            raise ValueError(f"Missing one or more required columns: {required_columns}")

        df['Start'] = pd.to_datetime(df['Start'], errors='coerce')
        df['End'] = pd.to_datetime(df['End'], errors='coerce')
        if 'Revised Start' in df.columns:
            df['Revised Start'] = pd.to_datetime(df['Revised Start'], errors='coerce')
        if 'Revised End' in df.columns:
            df['Revised End'] = pd.to_datetime(df['Revised End'], errors='coerce')
        df.dropna(subset=['Start', 'End'], inplace=True)
        if 'Y_Label' not in df.columns:
            df['Y_Label'] = df['Task']

        # --- 2. Filter Milestones and Related Bars ---
        target_acronyms = ['ITR', 'PDR', 'CDR', 'AR', 'FAI', 'QRR', 'TTP', 'PRR', 'FSP']
        
        def contains_acronym(row):
            task_desc = f"{row['Task']} {row['Description']}"
            return any(acronym in task_desc for acronym in target_acronyms)

        milestones = df[(df['Start'] == df['End']) & df.apply(contains_acronym, axis=1)].copy()
        milestone_dates = milestones['End'].unique()
        related_bars = df[df['End'].isin(milestone_dates) & (df['Start'] != df['End'])].copy()

        if milestones.empty and related_bars.empty:
            raise ValueError(f"No milestones with target acronyms or related bars found.")

        # --- 3. Process Data for Gantt Chart ---
        milestone_categories = milestones.groupby('Category')['End'].max().sort_values().index.tolist()
        bar_categories = related_bars.groupby('Category')['End'].max().sort_values().index.tolist()

        gantt_data = []
        y_labels = []
        y_positions = []
        current_index = 0

        # Process Milestones
        for category in milestone_categories:
            if category in milestones['Category'].unique():
                y_labels.append(f"<b><span style='color:{category_colors.get(category, '#1f77b4')}'>MILESTONES: {category.upper()}</span></b>")
                y_positions.append(current_index)
                current_index += 1
                category_df = milestones[milestones['Category'] == category].sort_values('Start')
                for _, row in category_df.iterrows():
                    y_labels.append(f"  {row['Y_Label']}")
                    y_positions.append(current_index)
                    percentage = 1.0
                    if isinstance(row['Description'], str) and any(c.isdigit() for c in row['Description']):
                        percentage = float(next((float(s) for s in row['Description'].split() if s.replace('.','',1).isdigit()), 1.0))
                    
                    has_revised_end = 'Revised End' in df.columns and pd.notna(row['Revised End'])
                    milestone_date = row['Revised End'] if has_revised_end else row['End']

                    gantt_data.append({
                        'Task': f"  {row['Y_Label']}",
                        'Start': milestone_date,
                        'Finish': milestone_date,
                        'Resource': category,
                        'Color': category_colors.get(category, '#1f77b4'),
                        'Index': current_index,
                        'IsMilestone': True,
                        'Description': row['Description'],
                        'Percentage': percentage,
                        'Completion': row['% Completion'] if pd.notna(row['% Completion']) else 0.0,
                        'Comment': row['Comment'] if pd.notna(row['Comment']) else '',
                        'OriginalStart': row['Start'],
                        'OriginalEnd': row['End'],
                        'HasRevised': has_revised_end
                    })
                    
                    if has_revised_end:
                        gantt_data.append({
                            'Task': f"  {row['Y_Label']}",
                            'Start': row['End'],
                            'Finish': row['End'],
                            'Resource': category,
                            'Color': category_colors.get(category, '#1f77b4'),
                            'Index': current_index,
                            'IsMilestone': True,
                            'Description': row['Description'],
                            'Percentage': percentage,
                            'Completion': row['% Completion'] if pd.notna(row['% Completion']) else 0.0,
                            'Comment': row['Comment'] if pd.notna(row['Comment']) else '',
                            'OriginalStart': row['Start'],
                            'OriginalEnd': row['End'],
                            'IsOriginalRevisedMilestone': True,
                            'HasRevised': False
                        })
                    current_index += 1

        # Process Related Bars
        for category in bar_categories:
            if category in related_bars['Category'].unique():
                y_labels.append(f"<b><span style='color:{category_colors.get(category, '#1f77b4')}'>TIMELINES: {category.upper()}</span></b>")
                y_positions.append(current_index)
                current_index += 1
                category_df = related_bars[related_bars['Category'] == category].sort_values('Start')
                for _, row in category_df.iterrows():
                    y_labels.append(f"  {row['Y_Label']}")
                    y_positions.append(current_index)
                    percentage = 1.0
                    if isinstance(row['Description'], str) and any(c.isdigit() for c in row['Description']):
                        percentage = float(next((float(s) for s in row['Description'].split() if s.replace('.','',1).isdigit()), 1.0))
                    start_date = row['Start']
                    end_date = row['Revised End'] if 'Revised End' in df.columns and pd.notna(row['Revised End']) else row['End']
                    revised_start = row['Revised Start'] if 'Revised Start' in df.columns and pd.notna(row['Revised Start']) else start_date
                    gantt_data.append({
                        'Task': f"  {row['Y_Label']}",
                        'Start': start_date,
                        'Finish': end_date,
                        'RevisedStart': revised_start,
                        'Resource': category,
                        'Color': category_colors.get(category, '#1f77b4'),
                        'Index': current_index,
                        'IsMilestone': False,
                        'Description': row['Description'],
                        'Percentage': percentage,
                        'Completion': row['% Completion'] if pd.notna(row['% Completion']) else 0.0,
                        'Comment': row['Comment'] if pd.notna(row['Comment']) else '',
                        'OriginalStart': row['Start'],
                        'OriginalEnd': row['End'],
                        'HasRevised': ('Revised Start' in df.columns and pd.notna(row['Revised Start'])) or ('Revised End' in df.columns and pd.notna(row['Revised End']))
                    })
                    current_index += 1

        y_labels = list(reversed(y_labels))
        y_positions = list(reversed(y_positions))
        for item in gantt_data:
            item['Index'] = len(y_labels) - 1 - item['Index']

        def estimate_text_width_ms(text, chart_width_ms):
            char_width_ms = chart_width_ms / 100
            return len(str(text)) * char_width_ms

        def calculate_dynamic_offset(start, finish, chart_width_ms, percentage=0.015):
            if isinstance(start, (pd.Timestamp, datetime)) and isinstance(finish, (pd.Timestamp, datetime)):
                duration_ms = (finish - start).total_seconds() * 1000
            else:
                duration_ms = 0
            return chart_width_ms * percentage

        fig = go.Figure()

        chart_start_ms = min([d['Start'] for d in gantt_data]).timestamp() * 1000
        chart_end_ms = max([d['Finish'] for d in gantt_data]).timestamp() * 1000
        chart_width_ms = chart_end_ms - chart_start_ms

        for task in gantt_data:
            if not task['IsMilestone']:
                completion = task['Completion'] if task['Completion'] is not None else 0.0
                is_completed = isinstance(task['Description'], str) and 'completed' in task['Description'].lower()
                total_duration = (task['Finish'] - task['Start']).total_seconds() * 1000
                revised_duration = (task['Finish'] - task['RevisedStart']).total_seconds() * 1000 if task['HasRevised'] else 0
                original_delay = (task['RevisedStart'] - task['Start']).total_seconds() * 1000 if task['HasRevised'] else 0

                bar_color = category_colors.get(task['Resource'], '#1f77b4') if not is_completed else '#00fa85'
                if task['HasRevised']:
                    if original_delay > 0:
                        fig.add_trace(go.Bar(
                            x=[original_delay],
                            y=[task['Index']],
                            base=[task['Start'].timestamp() * 1000],
                            orientation='h',
                            marker=dict(
                                color='rgba(0,0,0,0)',
                                line=dict(color=bar_color, width=1),
                                pattern=dict(shape="/", fgcolor=bar_color, fgopacity=0.3)
                            ),
                            width=0.55,
                            showlegend=False,
                            hoverinfo='text',
                            hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Revised Start: {task['RevisedStart'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                            xaxis='x'
                        ))
                    fig.add_trace(go.Bar(
                        x=[revised_duration],
                        y=[task['Index']],
                        base=[task['RevisedStart'].timestamp() * 1000],
                        orientation='h',
                        marker=dict(color=bar_color, line=dict(color='black', width=1)),
                        width=0.55,
                        showlegend=False,
                        hoverinfo='text',
                        hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                        xaxis='x'
                    ))
                else:
                    if is_completed or completion <= 0:
                        fig.add_trace(go.Bar(
                            x=[total_duration],
                            y=[task['Index']],
                            base=[task['Start'].timestamp() * 1000],
                            orientation='h',
                            marker=dict(color=bar_color, line=dict(color='black', width=1)),
                            width=0.55,
                            showlegend=False,
                            hoverinfo='text',
                            hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                            xaxis='x'
                        ))
                    else:
                        completed_duration = total_duration * min(completion, 1.0)
                        remaining_duration = total_duration - completed_duration
                        fig.add_trace(go.Bar(
                            x=[completed_duration],
                            y=[task['Index']],
                            base=[task['Start'].timestamp() * 1000],
                            orientation='h',
                            marker=dict(color=Dependency_arrow_colors.get(task['Resource'], '#ff00ff'), line=dict(color='black', width=1)),  # Fluorescent for progression
                            width=0.55,
                            showlegend=False,
                            name='Completed',
                            hoverinfo='text',
                            hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                            xaxis='x'
                        ))
                        if remaining_duration > 0:
                            fig.add_trace(go.Bar(
                                x=[remaining_duration],
                                y=[task['Index']],
                                base=[task['Start'].timestamp() * 1000 + completed_duration],
                                orientation='h',
                                marker=dict(color=bar_color, line=dict(color='black', width=1)),
                                width=0.55,
                                showlegend=False,
                                name='Remaining',
                                hoverinfo='text',
                                hovertext=f"<b>{task['Task'].strip()}</b><br>End date: {task['Finish'].strftime('%Y-%m-%d')}<br>Completion: {completion*100:.0f}%",
                                xaxis='x'
                            ))

                start_text = "Initial Start: " if task['HasRevised'] else ""
                fig.add_annotation(
                    x=task['Start'].timestamp() * 1000,
                    y=task['Index'] + 0.2,
                    text=f"{start_text}<span style='color:black'>{task['Start'].strftime('%m/%d')}</span>",
                    showarrow=False,
                    font=dict(size=10, color=bar_color),
                    xanchor='left',
                    yanchor='bottom',
                    xref='x'
                )

                if task['HasRevised']:
                    fig.add_annotation(
                        x=task['RevisedStart'].timestamp() * 1000,
                        y=task['Index'] - 0.2,
                        text=f"Revised Start: <b><span style='color:black'>{task['RevisedStart'].strftime('%m/%d')}</span></b>",
                        showarrow=False,
                        font=dict(size=10, color=bar_color),
                        xanchor='left',
                        yanchor='top',
                        xref='x'
                    )

                    fig.add_annotation(
                        x=task['OriginalEnd'].timestamp() * 1000,
                        y=task['Index'] - 0.4,
                        text=f"Initial End: <span style='color:black'>{task['OriginalEnd'].strftime('%m/%d')}</span>",
                        showarrow=False,
                        font=dict(size=10, color=bar_color),
                        xanchor='left',
                        yanchor='top',
                        xref='x'
                    )

                fig.add_annotation(
                    x=task['Finish'].timestamp() * 1000 + 500000,
                    y=task['Index'],
                    text="▶",
                    showarrow=False,
                    font=dict(size=14, color=bar_color),
                    xanchor='center',
                    yanchor='middle',
                    xref='x'
                )

                if is_completed:
                    fig.add_annotation(
                        x=task['Start'].timestamp() * 1000 + (total_duration / 2),
                        y=task['Index'],
                        text="Completed",
                        showarrow=False,
                        font=dict(size=12, color='black', family='Arial', weight='bold'),
                        xanchor='center',
                        yanchor='middle',
                        xref='x'
                    )
                else:
                    display_text = f"{int(task['Percentage'] * 100)}%" if task['Percentage'] < 1.0 else task['Description']
                    fig.add_annotation(
                        x=task['Start'].timestamp() * 1000 + (completed_duration / 2) if task['Percentage'] < 1.0 else task['Start'].timestamp() * 1000 + (total_duration / 2),
                        y=task['Index'],
                        text=display_text,
                        showarrow=False,
                        font=dict(size=12, color='black', family='Arial', weight='bold'),
                        xanchor='center',
                        yanchor='middle',
                        xref='x'
                    )

                base_x = task['Finish'].timestamp() * 1000
                completion_offset = calculate_dynamic_offset(task['Start'], task['Finish'], chart_width_ms)
                padding_offset = calculate_dynamic_offset(task['Start'], task['Finish'], chart_width_ms, percentage=0.01)
                arrow_buffer = 1000000

                completion_x = base_x + completion_offset + arrow_buffer
                completion_text = ""
                has_completion = completion is not None and completion > 0
                if has_completion:
                    completion_text = f"{int(completion * 100)}%"
                    fig.add_annotation(
                        x=completion_x,
                        y=task['Index'] + 0.2,
                        text=completion_text,
                        showarrow=False,
                        font=dict(size=14, color=Dependency_arrow_colors.get(task['Resource'], '#00fa85') if completion > 0 else '#000000', family='Arial', weight='bold'),  # Fluorescent for % if > 0%
                        xanchor='left',
                        yanchor='middle',
                        xref='x'
                    )

                if task['Comment']:
                    if has_completion:
                        completion_width_ms = estimate_text_width_ms(completion_text, chart_width_ms)
                        comment_x = completion_x + completion_width_ms + padding_offset
                    else:
                        comment_x = base_x + completion_offset + arrow_buffer
                    fig.add_annotation(
                        x=comment_x,
                        y=task['Index'],
                        text=f"({task['Comment']})",
                        showarrow=False,
                        font=dict(size=12, color='black', family='Arial'),
                        xanchor='left',
                        yanchor='middle',
                        xref='x'
                    )

                # Add vertical gray bar for initial end, slightly longer than the bar
                bar_height_extension = 0.15  # Extend slightly above and below the bar
                fig.add_shape(
                    type="line",
                    x0=task['OriginalEnd'].timestamp() * 1000,
                    x1=task['OriginalEnd'].timestamp() * 1000,
                    y0=task['Index'] - 0.5 + bar_height_extension,
                    y1=task['Index'] + 0.5 - bar_height_extension,
                    line=dict(color="gray", width=3),
                    xref='x',
                    yref='y'
                )

        for task in gantt_data:
            if task['IsMilestone']:
                if task.get('IsOriginalRevisedMilestone', False):
                    fig.add_trace(go.Scatter(
                        x=[task['Start'].timestamp() * 1000],
                        y=[task['Index']],
                        mode='markers',
                        marker=dict(
                            symbol='star',
                            size=13,
                            color='gray'
                        ),
                        showlegend=False,
                        hoverinfo='text',
                        hovertext=f"<b>{task['Task'].strip()} (Original)</b><br>Date: {task['Start'].strftime('%Y-%m-%d')}",
                        xaxis='x'
                    ))
                    fig.add_annotation( 
                        x=task['Start'].timestamp() * 1000 - calculate_dynamic_offset(task['Start'], task['Finish'], chart_width_ms, percentage=0.1),
                        y=task['Index'] - 0.2,
                        text=f"<b><span style='color:gray'>was {task['Start'].strftime('%m/%d')}</span></b>",
                        showarrow=False,
                        font=dict(size=12, color='gray'),
                        xanchor='left',
                        yanchor='middle',
                        xref='x'
                    )
                else:
                    fig.add_trace(go.Scatter(
                        x=[task['Start'].timestamp() * 1000],
                        y=[task['Index']],
                        mode='markers',
                        marker=dict(
                            symbol='star',
                            size=13,
                            color=category_colors.get(task['Resource'], '#1f77b4')
                        ),
                        showlegend=False,
                        hoverinfo='text',
                        hovertext=f"<b>{task['Task'].strip()}</b><br>Date: {task['Start'].strftime('%Y-%m-%d')}",
                        xaxis='x'
                    ))
                    fig.add_annotation(
                        x=task['Start'].timestamp() * 1000 + calculate_dynamic_offset(task['Start'], task['Finish'], chart_width_ms, percentage=0.015),
                        y=task['Index'] - 0.2,
                        text=f"<b><span style='color:black'>{task['Task'].strip()} - </span><span style='color:{category_colors.get(task['Resource'], '#1f77b4')}'>{task['Start'].strftime('%m/%d')}</span></b>",
                        showarrow=False,
                        font=dict(size=12, color=category_colors.get(task['Resource'], '#1f77b4')),
                        xanchor='left',
                        yanchor='middle',
                        xref='x'
                    )
            
        today = datetime.now()
        fig.add_shape(
            type="line",
            x0=today.timestamp() * 1000,
            x1=today.timestamp() * 1000,
            y0=-0.5,
            y1=len(y_labels) - 0.5,
            line=dict(color="#006400", width=1, dash="dot"),
            xref='x',
            yref='y'
        )
        fig.add_annotation(
            x=today.timestamp() * 1000 + 500000,
            y=len(y_labels) - 0.5,
            text=f"<b>{today.strftime('%m/%d')}</b>",
            showarrow=False,
            font=dict(size=12, color="#006400"),
            xanchor='left',
            yanchor='top',
            xref='x'
        )

        one_month = relativedelta(months=2)
        start_date_gantt = min([d['Start'] for d in gantt_data]).replace(day=1)
        end_date_gantt = max([d['Finish'] for d in gantt_data])
        current_date_iter = start_date_gantt
        while current_date_iter <= end_date_gantt + relativedelta(months=1):
            fig.add_shape(
                type="line",
                x0=current_date_iter.timestamp() * 1000,
                x1=current_date_iter.timestamp() * 1000,
                y0=-0.5,
                y1=len(y_labels) - 0.5,
                line=dict(color="lightgray", width=1, dash="4,6"),
                layer='below',
                xref='x',
                yref='y'
            )
            current_date_iter += one_month
            current_date_iter = current_date_iter.replace(day=1)

        top_y_position = len(y_labels) + 0.5
        current_date_iter = start_date_gantt
        while current_date_iter <= end_date_gantt + relativedelta(months=1):
            fig.add_annotation(
                x=current_date_iter.timestamp() * 1000,
                y=top_y_position,
                text=current_date_iter.strftime('%b %d %Y'),
                showarrow=False,
                font=dict(size=12, color='black'),
                xanchor='center',
                yanchor='bottom',
                xref='x'
            )
            current_date_iter += one_month
            current_date_iter = current_date_iter.replace(day=1)

        x_min = min([d['Start'] for d in gantt_data]).timestamp() * 1000 - (30 * 24 * 60 * 60 * 1000)
        x_max = max([d['Finish'] for d in gantt_data]).timestamp() * 1000 + (30 * 24 * 60 * 60 * 1000)
        date_range = [x_min, x_max]

        fig.update_layout(
            title=dict(
                text=f'<b>{Project} - Gantt',
                font=dict(size=24, color='#002060'),
                x=0.05,
                xanchor='left'
            ),
            margin=dict(l=5, r=5, t=60, b=5),
            barmode='stack',
            xaxis=dict(
                type='date',
                tickformat='%b %d %Y',
                title='Date',
                tickfont=dict(size=14),
                showgrid=False,
                dtick="M1",
                range=date_range
            ),
            yaxis=dict(
                ticktext=y_labels,
                tickvals=list(range(len(y_labels))),
                title='Tasks',
                tickfont=dict(size=12),
                showgrid=False,
                range=[-0.5, len(y_labels) - 0.5]
            ),
            autosize=False, 
            height=1100, # Play on these values to define the size of the chart compare to the fixed size of the card.
            width=1800,
            plot_bgcolor='white',
            paper_bgcolor='white'
        )

        plotly_pane = pn.pane.Plotly(
            fig,
            #sizing_mode=None,
            sizing_mode='stretch_both',
            config={'responsive': False}
        )
        
        panel_object = pn.Column(
            plotly_pane,
            width=card_styles["Project Timeline"]["width"],
            height=card_styles["Project Timeline"]["height"],
            styles={
                "overflow": "auto",
                **common_panel_object_styles
            }
        )
        return create_card_indicators("Project Timeline", panel_object, card_styles["Project Timeline"])

    except (NameError, ValueError, KeyError) as e:
        print(f"Error creating project timeline: {e}")
        error_html = f"""
        <div class='bg-white p-4 rounded-lg shadow-md h-full flex flex-col'>
            <h2 class='text-xl font-bold mb-2 text-gray-700'>Project Timeline</h2>
            <div class='flex-grow flex items-center justify-center'>
                <p class='text-gray-500'>Timeline data not available or an error occurred: {e}</p>
            </div>
        </div>
        """
        panel_object = pn.pane.HTML(error_html, sizing_mode='stretch_both')
        return create_card_indicators("Project Timeline", panel_object, card_styles.get("Project Timeline", {}))
'''      
#--------------------------------------------------------------------------------------------------------------------------
# Create card for 'NRE Tracking per phase'
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_SciformaReport_Team_Project and df_Project_phases
# NRE Tracking per Phase
# Since there are several projects in different phases, but no tracking of hours per individual project—only combined under the 'Project' program
# Let's simplify the tracking by considering the end date of Phase 7 as the latest end date among all projects. Similarly, for Phase 8, the latest end date across all projects will be considered the end of Phase 8.
# No overlap of hours for difference phases and need to define dates for each phases for the seake of tracking even thought each project has is own.
#--------------------------------------------------------------------------------------------------------------------------

def create_nre_tracking_per_phase():
    try:
        # Validate input DataFrames
        required_columns_phases = ['Phase', 'Start date', 'End date']
        required_columns_ng = ['EWO'] + [col for col in df_SciformaReport_Team_Project.columns if col.startswith('202')]
        if not all(col in df_Project_phases.columns for col in required_columns_phases):
            raise ValueError("Missing required columns in df_Project_phases: " + str(required_columns_phases))
        if not all(col in df_SciformaReport_Team_Project.columns for col in required_columns_ng):
            raise ValueError("Missing required columns in df_SciformaReport_Team_Project: " + str(required_columns_ng))

        # Ensure date columns are datetime
        df_Project_phases['End date'] = pd.to_datetime(df_Project_phases['End date'])
        df_Project_phases['Start date'] = pd.to_datetime(df_Project_phases['Start date'])

        # Check for NaN in date columns
        if df_Project_phases[['Start date', 'End date']].isna().any().any():
            raise ValueError("NaN values found in 'Start date' or 'End date' columns of df_Project_phases")

        # Aggregate earliest start and latest end for each phase across all programs
        phase_dates = df_Project_phases.groupby('Phase').agg({
            'Start date': 'min',
            'End date': 'max'
        }).reset_index().sort_values(by='Start date').reset_index(drop=True)

        # Enforce phase sequencing
        if len(phase_dates) >= 2:
            phase_dates.loc[1, 'Start date'] = max(phase_dates.loc[1, 'Start date'], phase_dates.loc[0, 'End date'])
        if len(phase_dates) >= 3:
            phase_dates.loc[2, 'Start date'] = max(phase_dates.loc[2, 'Start date'], phase_dates.loc[1, 'End date'])

        # Extract month columns
        month_columns = [col for col in df_SciformaReport_Team_Project.columns if col.startswith('202') and len(col) == 7]
        month_column_start_dates = pd.to_datetime([col + '-01' for col in month_columns])

        # Filter out rows where 'Function' contains 'External'
        df_SciformaReport_Team_Project_without_external = df_SciformaReport_Team_Project[~df_SciformaReport_Team_Project['Function'].str.contains('External', case=False, na=False)].copy()

        # Separate planned and actual data
        planned_data = df_SciformaReport_Team_Project_without_external[df_SciformaReport_Team_Project_without_external['EWO'].str.contains('Planned')]
        actual_data = df_SciformaReport_Team_Project_without_external[~df_SciformaReport_Team_Project_without_external['EWO'].str.contains('Planned')]

        # Check for NaN in numeric columns and replace with 0
        planned_data[month_columns] = planned_data[month_columns].fillna(0)
        actual_data[month_columns] = actual_data[month_columns].fillna(0)

        # Initialize phase tracking dictionary
        phase_hours = {}
        for _, row in phase_dates.iterrows():
            phase_name = row['Phase']
            phase_hours[phase_name] = {
                'planned': 0,
                'actual': 0,
                'Start Date': row['Start date'],
                'End Date': row['End date']
            }

        # Assign hours to phases based on date overlap
        for phase_name, phase_info in phase_hours.items():
            phase_start_date = phase_info['Start Date']
            phase_end_date = phase_info['End Date']

            cols_for_phase = []
            for i, month_col_start_date in enumerate(month_column_start_dates):
                month_col_end_date = month_col_start_date + pd.offsets.MonthEnd(0)
                month_col = month_columns[i]

                # Check if month overlaps with phase
                if (month_col_start_date <= phase_end_date and month_col_end_date >= phase_start_date):
                    cols_for_phase.append(month_col)

            # Sum planned and actual hours for the phase
            phase_info['planned'] = int(planned_data[cols_for_phase].sum().sum())
            phase_info['actual'] = int(actual_data[cols_for_phase].sum().sum())

        # Build DataFrame
        df_plot = pd.DataFrame([
            {
                'Phase': phase,
                'Planned': data['planned'],
                'Actual': data['actual'],
                'Remaining': data['planned'] - data['actual'],
                'Start Date': data['Start Date'],
                'End Date': data['End Date']
            } for phase, data in phase_hours.items()
        ])

        # Define custom phase order
        phase_order = ['ECO', 'Proto', 'Prod']
        df_plot['Phase_Order'] = df_plot['Phase'].apply(lambda x: phase_order.index(x) if x in phase_order else len(phase_order))
        df_plot = df_plot.sort_values('Phase_Order').drop(columns='Phase_Order')

        # Add percentage text
        df_plot['Actual_Percentage_Text'] = df_plot.apply(
            lambda row: f"<b>{int(row['Actual'] / row['Planned'] * 100)}%</b>" if row['Planned'] > 0 else '<b>0%</b>',
            axis=1
        )

        # Plotly bar chart
        fig = go.Figure()
        fig.add_trace(go.Bar(
            x=df_plot['Phase'],
            y=df_plot['Planned'],
            name='Hours Planned',
            marker_color='rgba(59, 130, 246, 0.5)',
            width=0.4
        ))
        fig.add_trace(go.Bar(
            x=df_plot['Phase'],
            y=df_plot['Actual'],
            name='Hours Spent',
            marker=dict(
                color='#10b981',  # Green
                line=dict(color='white', width=.5)  # white border
            ),
            width=0.2,
            text=df_plot['Actual_Percentage_Text'],
            textposition='outside',
            textfont=dict(color='black', size=12)
        ))

        fig.update_layout(
            title_text='NRE Tracking per Phase',
            plot_bgcolor='white',
            paper_bgcolor='white',
            margin=dict(l=20, r=20, t=80, b=20),
            barmode='overlay',
            bargap=0.3,
            title_x=0.1,
            title_y=0.95,
            legend_title_text='Legend',
            legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
        )

        fig.update_yaxes(title_text='Hours')
        fig.update_xaxes(title_text='Phase', tickfont=dict(size=12, family="Arial", color="black"),
                         categoryorder='array', categoryarray=df_plot['Phase'])

        # Create table
        df_table = df_plot[['Phase', 'Start Date', 'End Date', 'Planned', 'Actual', 'Remaining']].copy()
        df_table['Start Date'] = df_table['Start Date'].dt.strftime('%Y-%m-%d')
        df_table['End Date'] = df_table['End Date'].dt.strftime('%Y-%m-%d')
        df_table[['Planned', 'Actual', 'Remaining']] = df_table[['Planned', 'Actual', 'Remaining']].astype(int)

        styled_df_table = df_table.style.hide(axis="index").set_table_styles([
            {'selector': '', 'props': [('margin-left', 'auto'), ('margin-right', 'auto'), ('width', '100%')]},
            {'selector': 'th', 'props': [('text-align', 'center'), ('font-weight', 'bold')]},
            {'selector': 'td', 'props': [('text-align', 'center')]}
        ])

        panel_table = pn.pane.HTML(
            """
            <div style="text-align: left; margin-top: 5px; margin-bottom: 5px;">
                <p><strong>NRE</strong>: All hours included, excluding External hours.</p>
            </div>
            """ + styled_df_table.to_html(),
            sizing_mode='stretch_width'
        )

        panel_plot = pn.pane.Plotly(fig, height=400, sizing_mode='stretch_width')

        combined_layout = pn.Column(
            panel_plot,
            panel_table,
            sizing_mode='stretch_both',
            styles=common_panel_object_styles
        )

        return create_card_indicators("NRE Tracking per Phase", combined_layout, card_styles["NRE Tracking per Phase"])

    except Exception as e:
        print(f"Error in create_nre_tracking_per_phase: {str(e)}")
        panel_object = pn.pane.HTML(
            f"""
            <div class='bg-white p-6 rounded-lg shadow-md'>
                <h2 class='text-xl font-bold mb-4'>NRE Tracking per Phase</h2>
                <p>NRE data not available due to an error: {str(e)}</p>
            </div>
            """,
            sizing_mode='stretch_both',
        )
        return create_card_indicators("NRE Tracking per Phase", panel_object, card_styles["NRE Tracking per Phase"])

#-------------------------------------------------------------------------------------------------------------------------
# Create card for 'NRC Follow-up per phase'
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_SciformaReport_Team_Project and df_Project_phases
# NRC Follow-up per Phase
#--------------------------------------------------------------------------------------------------------------------------
def create_nrc_followup_per_phase():
    try:
        # Validate input DataFrames
        required_columns_phases = ['Phase', 'Start date', 'End date']
        required_columns_expense = ['Program', 'Function'] + [col for col in df_SciformaReport_Team_Project_Expense.columns if col.startswith('202')]
        if not all(col in df_Project_phases.columns for col in required_columns_phases):
            raise ValueError("Missing required columns in df_Project_phases: " + str(required_columns_phases))
        if not all(col in df_SciformaReport_Team_Project_Expense.columns for col in required_columns_expense):
            raise ValueError("Missing required columns in df_SciformaReport_Team_Project_Expense: " + str(required_columns_expense))

        # Ensure date columns in df_Project_phases are datetime objects
        df_Project_phases['End date'] = pd.to_datetime(df_Project_phases['End date'])
        df_Project_phases['Start date'] = pd.to_datetime(df_Project_phases['Start date'])

        # Check for NaN in date columns
        if df_Project_phases[['Start date', 'End date']].isna().any().any():
            raise ValueError("NaN values found in 'Start date' or 'End date' columns of df_Project_phases")

        # Aggregate phase dates: earliest start and latest end per phase
        phase_dates = df_Project_phases.groupby('Phase').agg({
            'Start date': 'min',
            'End date': 'max'
        }).reset_index()

        # Sort phases by start date to ensure phase order
        phase_dates = phase_dates.sort_values(by='Start date').reset_index(drop=True)

        # Enforce sequential phase dates
        if len(phase_dates) >= 2:  # Ensure there are at least 2 phases
            phase_dates.loc[phase_dates.index[1], 'Start date'] = max(phase_dates.loc[phase_dates.index[1], 'Start date'], phase_dates.loc[phase_dates.index[0], 'End date'])
        if len(phase_dates) >= 3:  # Ensure there are at least 3 phases
            phase_dates.loc[phase_dates.index[2], 'Start date'] = max(phase_dates.loc[phase_dates.index[2], 'Start date'], phase_dates.loc[phase_dates.index[1], 'End date'])

        # Get all month columns (YYYY-MM) from df_SciformaReport_Team_Project_Expense
        month_columns = [col for col in df_SciformaReport_Team_Project_Expense.columns
                         if col.startswith('202') and len(col) == 7]

        # Check for NaN in numeric columns and replace with 0
        df_SciformaReport_Team_Project_Expense[month_columns] = df_SciformaReport_Team_Project_Expense[month_columns].fillna(0)

        # Convert month column names to datetime objects (first day of the month) for comparison
        month_column_start_dates = pd.to_datetime([col + '-01' for col in month_columns])

        # Initialize phase dollar dictionary with aggregated start/end dates
        phase_dollars = {}
        for _, row in phase_dates.iterrows():
            phase_name = row['Phase']
            phase_dollars[phase_name] = {
                'planned_ee': 0,
                'actual_ee': 0,
                'planned_tm': 0,
                'actual_tm': 0,
                'Start Date': row['Start date'],
                'End Date': row['End date']
            }

        # Current date for actuals set as start_tracking_date
        current_date = start_tracking_date

        # Compute dollars for each phase and function type (EE and TM)
        for phase_name, phase_info in phase_dollars.items():
            phase_start_date = phase_info['Start Date']
            phase_end_date = phase_info['End Date']

            cols_for_planned = []
            cols_for_actual = []

            for i, month_col_start_date in enumerate(month_column_start_dates):
                month_col_end_date = month_col_start_date + pd.offsets.MonthEnd(0)  # Last day of the month
                month_col = month_columns[i]

                # Check for overlap with phase period
                if (month_col_start_date <= phase_end_date and
                    month_col_end_date >= phase_start_date):
                    cols_for_planned.append(month_col)

                # For actual dollars: must be in planned months AND the month must be fully or partially elapsed by current_date
                if (month_col in cols_for_planned and
                    month_col_start_date <= current_date):
                    cols_for_actual.append(month_col)

            # Aggregate dollars for EE and TM separately
            for function_type in ['External (EE)', 'External (TM)']:
                nrc_data = df_SciformaReport_Team_Project_Expense[
                    df_SciformaReport_Team_Project_Expense['Function'] == function_type
                ].groupby('Program')[month_columns].sum().reset_index()

                for _, nrc_row in nrc_data.iterrows():
                    key_prefix = 'planned_ee' if function_type == 'External (EE)' else 'planned_tm'
                    actual_key = 'actual_ee' if function_type == 'External (EE)' else 'actual_tm'
                    phase_dollars[phase_name][key_prefix] += int(nrc_row[cols_for_planned].sum())
                    phase_dollars[phase_name][actual_key] += int(nrc_row[cols_for_actual].sum())

        # Prepare DataFrame for plotting and table
        df_plot = pd.DataFrame([
            {
                'Phase': phase,
                'Planned EE': data['planned_ee'],
                'Actual EE': data['actual_ee'],
                'Planned TM': data['planned_tm'],
                'Actual TM': data['actual_tm'],
                'Remaining EE': data['planned_ee'] - data['actual_ee'],
                'Remaining TM': data['planned_tm'] - data['actual_tm'],
                'Start Date': data['Start Date'],
                'End Date': data['End Date']
            } for phase, data in phase_dollars.items()
        ])

        # Define custom phase order
        phase_order = ['ECO', 'Proto', 'Prod']
        df_plot['Phase_Order'] = df_plot['Phase'].apply(lambda x: phase_order.index(x) if x in phase_order else len(phase_order))
        df_plot = df_plot.sort_values('Phase_Order').drop(columns='Phase_Order')

        # Filter out phases where all values are 0 for the PLOT
        df_plot_filtered = df_plot[
            (df_plot['Planned EE'] > 0) | (df_plot['Actual EE'] > 0) |
            (df_plot['Planned TM'] > 0) | (df_plot['Actual TM'] > 0)
        ].copy()

        # Calculate percentage for bar annotation for filtered data
        df_plot_filtered['Actual_EE_Percentage_Text'] = df_plot_filtered.apply(
            lambda row: f"<b>{int(row['Actual EE'] / row['Planned EE'] * 100)}%</b>" if row['Planned EE'] > 0 else '<b>0%</b>',
            axis=1
        )
        df_plot_filtered['Actual_TM_Percentage_Text'] = df_plot_filtered.apply(
            lambda row: f"<b>{int(row['Actual TM'] / row['Planned TM'] * 100)}%</b>" if row['Planned TM'] > 0 else '<b>0%</b>',
            axis=1
        )

        # Create bar chart
        fig = go.Figure()

        # Create unique x-axis categories for each EE and TM bar to ensure correct grouping
        unique_x_labels = []
        for index, row in df_plot_filtered.iterrows():
            unique_x_labels.append(f"{row['Phase']} EE")
            if row['Planned TM'] > 0 or row['Actual TM'] > 0:
                unique_x_labels.append(f"{row['Phase']} TM")

        # Map phase-function combinations to an index for x-axis positioning
        x_positions = {label: i for i, label in enumerate(unique_x_labels)}

        # Add Planned EE bars
        fig.add_trace(go.Bar(
            x=[x_positions[f"{row['Phase']} EE"] for _, row in df_plot_filtered.iterrows()],
            y=df_plot_filtered['Planned EE'],
            name='Planned EE',
            marker=dict(
                color='rgba(59, 130, 246, 0.5)',  # Light blue alpha .5
                line=dict(color='rgba(0, 0, 0, 0)', width=0)  # No border
            ),
            width=0.4
        ))

        # Add Actual EE bars
        fig.add_trace(go.Bar(
            x=[x_positions[f"{row['Phase']} EE"] for _, row in df_plot_filtered.iterrows()],
            y=df_plot_filtered['Actual EE'],
            name='Actual EE',
            marker=dict(
                color='#10b981',  # Green
                line=dict(color='white', width=.5)  # white border
            ),
            width=0.2,
            text=df_plot_filtered['Actual_EE_Percentage_Text'],
            textposition='outside',
            textfont=dict(color='black', size=12)
        ))

        # Add Planned TM bars only if they exist
        tm_phases_for_plot = df_plot_filtered[
            (df_plot_filtered['Planned TM'] > 0) | (df_plot_filtered['Actual TM'] > 0)
        ]
        if not tm_phases_for_plot.empty:
            fig.add_trace(go.Bar(
                x=[x_positions[f"{row['Phase']} TM"] for _, row in tm_phases_for_plot.iterrows()],
                y=tm_phases_for_plot['Planned TM'],
                name='Planned TM',
                marker=dict(
                    color='rgba(173, 216, 230, 0.7)',  # Light blue
                    line=dict(color='rgb(0, 0, 139)', width=1)  # Dark blue border
                ),
                width=0.4
            ))

            # Add Actual TM bars
            fig.add_trace(go.Bar(
                x=[x_positions[f"{row['Phase']} TM"] for _, row in tm_phases_for_plot.iterrows()],
                y=tm_phases_for_plot['Actual TM'],
                name='Actual TM',
                marker=dict(
                    color='rgba(34, 197, 94, 1.0)',  # Green
                    line=dict(color='rgb(0, 0, 139)', width=1)  # Dark blue border
                ),
                width=0.2,
                text=tm_phases_for_plot['Actual_TM_Percentage_Text'],
                textposition='outside',
                textfont=dict(color='black', size=12)
            ))

        fig.update_layout(
            title_text='NRC Follow-up per Phase (EE and TM)',
            plot_bgcolor='white',
            paper_bgcolor='white',
            margin=dict(l=20, r=20, t=80, b=20),
            barmode='overlay',
            bargap=0.3,
            bargroupgap=0.0,
            xaxis=dict(
                tickmode='array',
                tickvals=list(x_positions.values()),
                ticktext=list(x_positions.keys()),
                tickangle=0
            ),
            annotations=[],
            title_x=0.1,
            title_y=0.95,
            legend_title_text='Legend',
            legend=dict(
                orientation="h",
                yanchor="bottom",
                y=1.02,
                xanchor="right",
                x=1
            )
        )

        # Set axis labels
        fig.update_yaxes(title_text='Dollars')
        fig.update_xaxes(title_text='Phase', tickfont=dict(size=12, family="Arial", color="black", weight='bold'))

        # Prepare table - use df_plot (unfiltered) to include all phases
        df_table = df_plot[['Phase', 'Planned EE', 'Actual EE', 'Remaining EE',
                            'Planned TM', 'Actual TM', 'Remaining TM']].copy()
        df_table['Total Planned NRC'] = df_table['Planned EE'] + df_table['Planned TM']
        df_table['Total Actual NRC'] = df_table['Actual EE'] + df_table['Actual TM']
        df_table['Total Remaining NRC'] = df_table['Remaining EE'] + df_table['Remaining TM']
        df_table.columns = ['Phase', 'Plan. EE', 'Act. EE', 'Rem. EE', 'Plan. TM', 'Act. TM', 'Rem. TM',
                           'Tot. Plan. NRC', 'Tot. Act. NRC', 'Tot. Rem. NRC']
        df_table['Plan. EE'] = df_table['Plan. EE'].astype(int)
        df_table['Act. EE'] = df_table['Act. EE'].astype(int)
        df_table['Rem. EE'] = df_table['Rem. EE'].astype(int)
        df_table['Plan. TM'] = df_table['Plan. TM'].astype(int)
        df_table['Act. TM'] = df_table['Act. TM'].astype(int)
        df_table['Rem. TM'] = df_table['Rem. TM'].astype(int)
        df_table['Tot. Plan. NRC'] = df_table['Tot. Plan. NRC'].astype(int)
        df_table['Tot. Act. NRC'] = df_table['Tot. Act. NRC'].astype(int)
        df_table['Tot. Rem. NRC'] = df_table['Tot. Rem. NRC'].astype(int)

        # Create styled table
        styled_df_table = df_table.style.hide(axis="index").set_table_styles([
            {'selector': '', 'props': [('margin-left', 'auto'), ('margin-right', 'auto'), ('width', '100%')]},
            {'selector': 'th', 'props': [('text-align', 'center'), ('font-weight', 'bold')]},
            {'selector': 'td', 'props': [('text-align', 'center')]}
        ])

        panel_plot = pn.pane.Plotly(
            fig,
            sizing_mode='stretch_width',
            height=400,
        )

        # Define panel_table with note and table combined
        panel_table = pn.pane.HTML(
            """
            <div style="text-align: left; margin-top: 5px; margin-bottom: 5px;">
                <p><strong>NRC</strong>: Includes External Expenses (EE) & Tools & Material costs only.</p>
            </div>
            """ + styled_df_table.to_html(),
            sizing_mode='stretch_width',
        )

        # Arrange plot, note text, and table
        combined_layout = pn.Column(
            panel_plot,
            panel_table,
            sizing_mode='stretch_both',
            styles=common_panel_object_styles
        )

        return create_card_indicators("NRC Follow-up per Phase (EE and TM)", combined_layout, card_styles["NRC Follow-up per Phase"])

    except Exception as e:
        print(f"Error in create_nrc_followup_per_phase: {str(e)}")
        panel_object = pn.pane.HTML(
            f"""
            <div class='bg-white p-6 rounded-lg shadow-md'>
                <h2 class='text-xl font-bold mb-4'>NRC Follow-up per Phase (EE and TM)</h2>
                <p>NRC data not available due to an error: {str(e)}</p>
            </div>
            """,
            sizing_mode='stretch_both',
        )
        return create_card_indicators("NRC Follow-up per Phase (EE and TM)", panel_object, card_styles["NRC Follow-up per Phase"])

#--------------------------------------------------------------------------------------------------------------------------
# Create card for 'Roadmapping' 
#--------------------------------------------------------------------------------------------------------------------------
# df_Roadmapping
#--------------------------------------------------------------------------------------------------------------------------
# --- df_PromptvsGate DEFINITION ---
df_PromptvsGate = pd.DataFrame({
    'Phase 1': ['S3', ''],     # S3 
    'Phase 2': ['S4', 'ITR'],     # S4 
    'Phase 3': ['S5', 'PDR'],     # S5 
    'Phase 4': ['S6 | S7', 'CDR, AR'], # S6|S7 
    'Prod':    ['S8', 'FAI, QRR TTP'],     # S8
})

def create_roadmapping():
    try:
        # Build acronym-to-phase mapping from df_PromptvsGate
        acronym_to_phase = {}

        for col in df_PromptvsGate.columns:
            s_phase = df_PromptvsGate.loc[0, col]
            acronyms_raw = df_PromptvsGate.loc[1, col]

            if pd.isnull(s_phase) or pd.isnull(acronyms_raw):
                continue

            # Pick last phase if multiple (e.g., S6 | S7)
            s_phase_split = [s.strip() for s in s_phase.split('|')]
            selected_phase = s_phase_split[-1] if s_phase_split else s_phase

            acronyms = re.split(r'[,\s]+', acronyms_raw)
            for acronym in acronyms:
                if acronym:
                    acronym_to_phase[acronym] = selected_phase

        # Manual override colors
        manual_colors = {
            'PRR': '#428D27',
            'FSP': '#35731F',
            'CDR': '#f6b17c'  # Explicitly set CDR to S6 color
        }

        # Build from df_PromptvsGate
        acronym_bg_color_map = {
            acronym: phase_colors.get(phase, '#555')
            for acronym, phase in acronym_to_phase.items()
        }
        
        # Add manual overrides (ensures they're included even if not in df_PromptvsGate)
        acronym_bg_color_map.update(manual_colors)

        # Ensure required columns exist for df_Roadmapping
        required_cols_roadmap = ['Acronym', 'Definition', 'Project Status']
        if not all(col in df_Roadmapping.columns for col in required_cols_roadmap):
            raise ValueError("Missing required columns in df_Roadmapping: " + ", ".join(required_cols_roadmap))

        # Ensure required columns exist for df_UnitCost
        required_cols_unitcost = ['Pty Indice', 'Material Cost', 'Sale Price', 'Backlog Qty']
        if not all(col in df_UnitCost.columns for col in required_cols_unitcost):
            raise ValueError("Missing required columns in df_UnitCost: " + ", ".join(required_cols_unitcost))

        # --- Start HTML ---
        table_html = """
        <div style='max-height: 590px; padding: 0px; margin: 1px; overflow-y: auto;'>
            <h3 style='color: #333; margin: 10px 0;'></h3>
        """

        # --- Unit Cost Table ---
        table_html_Cost = """
            <table style='width: 100%; min-width: 380px; border-collapse: collapse; font-size: 12px; margin-bottom: 5px; background-color: white;'>
                <thead>
                    <tr>
        """
        for idx, col in enumerate(df_UnitCost.columns):
            text_color = 'white' if idx == 0 else '#333'
            table_html_Cost += f"""
                        <th style='padding: 5px; border-bottom: 2px solid #e0e0e0; text-align: center; color: {text_color}; background-color: white;'>{col}</th>
            """
        table_html_Cost += """
                    </tr>
                </thead>
                <tbody>
        """
        for _, row in df_UnitCost.iterrows():
            table_html_Cost += "<tr>"
            for col in df_UnitCost.columns:
                value = row[col]
                formatted_value = f"${value}" if pd.notnull(value) and ('Cost' in col or 'Sale' in col) else str(value) if pd.notnull(value) else "N/A"
                table_html_Cost += f"""
                    <td style='padding: 5px; border-bottom: 1px solid #e0e0e0; text-align: center;'>{formatted_value}</td>
                """
            table_html_Cost += "</tr>"
        table_html_Cost += """
                </tbody>
            </table>
            <div style='height: 25px;'></div>
        """
        table_html += table_html_Cost

        # --- Roadmapping Table ---
        table_html += "<hr style='border: none; border-top: 1px solid #F2F2F2; margin: 1px 0;'>"
        table_html += "<h4 style='margin-top: 3px;'>Project current status</h4>"
        table_html += """
            <table style='width: 100%; min-width: 380px; border-collapse: collapse; font-size: 12px; margin-top: 5px;'>
                <tbody>
        """
        has_arrow = False
        start_border = None
        end_border = None
        for idx, row in df_Roadmapping.iterrows():
            if '→' in row['Definition']:
                has_arrow = True
                if start_border is None:
                    start_border = idx
                end_border = idx

        for idx, row in df_Roadmapping.iterrows():
            status_text = row['Project Status'] if pd.notnull(row['Project Status']) else ""
            acronyms = [a.strip() for a in row['Acronym'].split(',')]
            first_acronym = acronyms[0] if acronyms else ''
            acronym_bg_color = acronym_bg_color_map.get(first_acronym, '#555')

            status_html = ""
            if status_text:
                programs = [p.strip() for p in status_text.split(',')]
                html_spans = []
                for program in programs:
                    span_style = "display: inline-block; background-color: #28a745; color: white; padding: 4px 8px; border-radius: 12px; font-size: 12px; margin: 1px 2px 1px 0;"
                    html_spans.append(f"<span style='{span_style}'>{program}</span>")
                status_html = "".join(html_spans)

            # Make content after '→' bold in Definition with a space and set color to #002060 
            definition = row['Definition']
            if '→' in definition:
                parts = definition.split('→', 1)
                definition_html = f"{parts[0]}→ <strong style='color: #002060;'>{parts[1].strip()}</strong>"
            else:
                definition_html = definition

            # Apply thick #4472C4 external borders only to the largest square for rows with '→'
            if has_arrow and start_border is not None and end_border is not None:
                if idx == start_border:
                    acronym_style = f"padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; font-weight: bold; border-top: 4px solid #4472C4; border-left: 4px solid #4472C4; border-right: 0; border-bottom: 1px solid #e0e0e0;"
                    definition_style = f"padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; border-top: 4px solid #4472C4; border-left: 0; border-right: 4px solid #4472C4; border-bottom: 1px solid #e0e0e0;"
                elif idx == end_border:
                    acronym_style = f"padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; font-weight: bold; border-bottom: 4px solid #4472C4; border-left: 4px solid #4472C4; border-right: 0; border-top: 1px solid #e0e0e0;"
                    definition_style = f"padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; border-bottom: 4px solid #4472C4; border-left: 0; border-right: 4px solid #4472C4; border-top: 1px solid #e0e0e0;"
                elif start_border <= idx <= end_border:
                    acronym_style = f"padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; font-weight: bold; border-left: 4px solid #4472C4; border-right: 0; border-top: 1px solid #e0e0e0; border-bottom: 1px solid #e0e0e0;"
                    definition_style = f"padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; border-left: 0; border-right: 4px solid #4472C4; border-top: 1px solid #e0e0e0; border-bottom: 1px solid #e0e0e0;"
                else:
                    acronym_style = "padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; font-weight: bold; border-bottom: 1px solid #e0e0e0;"
                    definition_style = "padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; border-bottom: 1px solid #e0e0e0;"
            else:
                acronym_style = "padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; font-weight: bold; border-bottom: 1px solid #e0e0e0;"
                definition_style = "padding: 5px; text-align: center; background-color: {acronym_bg_color}; color: white; border-bottom: 1px solid #e0e0e0;"

            status_style = "padding: 0px 2px; text-align: left; vertical-align: middle; width: 1%; white-space: nowrap;"

            table_html += f"""
                <tr>
                    <td style='{status_style}'>{status_html}</td>
                    <td style='{acronym_style.format(acronym_bg_color=acronym_bg_color)}'>{row['Acronym']}</td>
                    <td style='{definition_style.format(acronym_bg_color=acronym_bg_color)}'>{definition_html}</td>
                </tr>
            """
        table_html += """
                </tbody>
            </table>
            <div style='height: 25px;'></div>
        """

        # --- Prompt vs Gate Table ---
        table_html += "<hr style='border: none; border-top: 1px solid #F2F2F2; margin: 1px 0;'>"
        table_html += "<h4 style='margin-top: 3px;'>Development Gates vs. Safran PROMPT</h4>"

        table_html_PromptvsGate = """
            <table style='width: 100%; min-width: 380px; border-collapse: collapse; font-size: 12px; margin-top: 5px; background-color: white;'>
                <thead>
                    <tr>
        """
        for col in df_PromptvsGate.columns:
            table_html_PromptvsGate += f"""
                        <th style='padding: 5px; border-bottom: 2px solid #e0e0e0; text-align: center; color: #333; background-color: white;'>{col}</th>
            """
        table_html_PromptvsGate += """
                    </tr>
                </thead>
                <tbody>
        """
        for _, row in df_PromptvsGate.iterrows():
            table_html_PromptvsGate += "<tr>"
            for col in df_PromptvsGate.columns:
                value = str(row[col])
                cell_style = ""

                if value == 'S6 | S7':
                    color_s6 = phase_colors.get('S6', 'white')
                    color_s7 = phase_colors.get('S7', 'white')
                    cell_style = f"background: linear-gradient(to right, {color_s6} 50%, {color_s7} 50%); color: white;" 
                else:
                    bg_color = phase_colors.get(value, 'white')
                    text_color = 'white' if bg_color != 'white' else '#333'
                    cell_style = f"background-color: {bg_color}; color: {text_color};"

                table_html_PromptvsGate += f"""
                    <td style='padding: 5px; border-bottom: 1px solid #e0e0e0; text-align: center; {cell_style}'>
                        {value}
                    </td>
                """
            table_html_PromptvsGate += "</tr>"
        table_html_PromptvsGate += """
                </tbody>
            </table>
        """
        table_html += table_html_PromptvsGate

        table_html += "</div>"

        # --- Return Panel Object ---
        panel_object = pn.pane.HTML(
            table_html,
            sizing_mode='stretch_both',
            styles={
                'background': 'white',
                'padding': '5px',
                'border-radius': '8px',
                'box-shadow': '0 4px 6px rgba(0,0,0,0.1)',
                'border': '1px solid #ddd',
                'margin': '0px 5px 0px 5px',
            }
        )

        return create_card_indicators("Roadmapping", panel_object, card_styles["Roadmapping"])

    except Exception as e:
        error_message = pn.pane.HTML(
            f"""
            <div style='background: white; padding: 20px; border-radius: 8px; color: red; font-weight: bold;'>
                Roadmapping Error: {str(e)}<br>Please check if df_Roadmapping, df_UnitCost, and df_PromptvsGate are properly defined.
            </div>
            """,
            width=card_styles["Roadmapping"]["width"],
            height=card_styles["Roadmapping"]["height"]
        )
        return create_card_indicators("Roadmapping", error_message, card_styles["Roadmapping"])


#--------------------------------------------------------------------------------------------------------------------------
# Create card for 'Risk Map'
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_Project_Risk
#--------------------------------------------------------------------------------------------------------------------------

def create_risk_map():
    """
    Create a risk map visualization with Impact on X, Likelihood on Y,
    a centered 5x5 heatmap, bold scatter labels, and a table without index.

    Returns:
        The result of create_card_indicators with the Risk Map panel object and styles.
    """
    try:
        # Access global data
        risk_map_styles = card_styles.get("Risk Map")
        risk_df = globals()['df_Project_Risk'].copy()

        # Assign labels to full dataset (used in the table)
        risk_df['Label'] = ['R' + str(i + 1) for i in range(len(risk_df))]

        # Prepare data for chart (exclude closed risks)
        risk_df_graph = risk_df[risk_df['Status'].str.lower() != 'closed'].copy()

        # Use integer Impact and Likelihood directly (assuming 1-5)
        risk_df_graph['Impact_num'] = risk_df_graph['Impact']
        risk_df_graph['Likelihood_num'] = risk_df_graph['Likelihood']

        # Heatmap risk level matrix (Impact on X-axis, Likelihood on Y-axis, 5x5)
        risk_levels_matrix = [
            [1, 2, 3, 4, 5],
            [2, 3, 4, 5, 6],
            [3, 4, 5, 6, 7],
            [4, 5, 6, 7, 8],
            [5, 6, 7, 8, 8.1]  # Top-right corner gets 8.1 for distinct color
        ]

        colorscale = [
            [0.0, '#90EE90'],   # Green
            [0.25, '#FFFF00'],  # Yellow
            [0.50, '#FFA500'],  # Orange
            [0.75, '#FF0000'],  # Red
            [1.0, '#C00000']    # Dark red for 8.1
        ]

        heatmap = go.Heatmap(
            x=[1, 2, 3, 4, 5],  # Impact
            y=[1, 2, 3, 4, 5],  # Likelihood
            z=risk_levels_matrix,
            colorscale=colorscale,
            showscale=False,
            opacity=0.8,
            hoverinfo='skip'
        )

        # Scatter points stacked symmetrically within each cell
        scatter_x = []
        scatter_y = []
        scatter_text = []
        custom_data = []

        cell_offset = 0.2
        grouped = risk_df_graph.groupby(['Impact_num', 'Likelihood_num'])

        for (impact, likelihood), group in grouped:
            num_items = len(group)
            start_y = likelihood + (cell_offset * (num_items - 1)) / 2
            for i, (_, row) in enumerate(group.iterrows()):
                scatter_x.append(impact)
                scatter_y.append(start_y - i * cell_offset)
                scatter_text.append(row['Label'])
                custom_data.append([row['Risk Description'], row['Status']])

        scatter = go.Scatter(
            x=scatter_x,
            y=scatter_y,
            mode='text',
            text=scatter_text,
            textfont=dict(family="Arial", size=14, color="black"),
            texttemplate='<b>%{text}</b>',
            textposition='middle center',
            hovertemplate=(
                "<b>%{text}</b><br>"
                "<b>Description:</b> %{customdata[0]}<br>"
                "<b>Status:</b> %{customdata[1]}"
            ),
            customdata=custom_data
        )

        # Create the figure
        fig = go.Figure(data=[heatmap, scatter])
        fig.update_layout(
            xaxis=dict(
                title='Impact',
                tickmode='array',
                tickvals=[1, 2, 3, 4, 5],
                ticktext=['1', '2', '3', '4', '5'],
                range=[0.5, 5.5],
                zeroline=False,
                showgrid=False
            ),
            yaxis=dict(
                title='Likelihood',
                tickmode='array',
                tickvals=[1, 2, 3, 4, 5],
                ticktext=['1', '2', '3', '4', '5'],
                range=[0.5, 5.5],
                zeroline=False,
                showgrid=False
            ),
            plot_bgcolor='rgba(0,0,0,0)',
            paper_bgcolor='rgba(0,0,0,0)',
            margin=dict(l=10, r=10, t=5, b=5),
            width=450,
            height=400,
            showlegend=False,
            autosize=False
        )

        chart_pane = pn.pane.Plotly(fig, sizing_mode='fixed', width=500, height=400)

        # Cleaned up table (includes all risks)
        table_df = risk_df[['Label', 'Risk Description', 'Impact', 'Likelihood', 'Status']].rename(columns={
            'Label': 'Tag',
            'Risk Description': 'Description'
        }).sort_values('Tag').reset_index(drop=True)

        table_pane = pn.pane.DataFrame(
            table_df,
            sizing_mode='stretch_height',
            height=None,
            index=False,
            css_classes=['scrollable-table-content']
        )

        panel_object = pn.Column(
            pn.Row(chart_pane, align='center', sizing_mode='stretch_width'),
            pn.Spacer(height=10),
            table_pane,
            sizing_mode='stretch_both',
            styles=common_panel_object_styles
        )

        return create_card_indicators("Risk Map", panel_object, card_styles["Risk Map"])

    except Exception as e:
        error_message = pn.pane.HTML(
            f"<div style='background:white;padding:20px;border-radius:8px;color:red;font-weight:bold;'>Risk Map Error:<br>{str(e)}<br><br>Please ensure 'df_Project_Risk' is available in the global scope and contains expected columns (Impact, Likelihood, Risk Description, Status, etc.). Also verify 'card_styles' dictionary is correctly defined.</div>",
            sizing_mode='stretch_width',
            height=650
        )
        return create_card_indicators("Risk Map", error_message, card_styles["Risk Map"])

#--------------------------------------------------------------------------------------------------------------------------
# Create card for 'NRE Tracking total'
#--------------------------------------------------------------------------------------------------------------------------
# Dataframe: df_SciformaReport_Team_Project
# NRE Tracking Total (cumulative hours)
#--------------------------------------------------------------------------------------------------------------------------
def create_nre_tracking_total():
    try:
        # Define current month for actuals (July 2025 based on context)
        current_month_for_actuals = pd.to_datetime('2025-07-01')

        # Filter out rows where 'Function' contains 'External' or 'Earn Value Balance'
        df_SciformaReport_Team_Project_without_external = df_SciformaReport_Team_Project[
            ~df_SciformaReport_Team_Project['Function'].str.contains('External', na=False) &
            (df_SciformaReport_Team_Project['Function'] != 'Earn Value Balance')
        ].copy()

        # Assume df_SciformaReport_Team_Project_without_external is now used instead of df_SciformaReport_Team_Project
        all_dataframe_months = [
            col for col in df_SciformaReport_Team_Project_without_external.columns
            if pd.to_datetime(col, errors='coerce', format='%Y-%m') is not pd.NaT
        ]
        all_dataframe_months.sort()

        display_start_month = all_dataframe_months[0] if all_dataframe_months else None
        months_for_plot = [m for m in all_dataframe_months if display_start_month and m >= display_start_month]

        # Filter actual and planned data
        actual_df = df_SciformaReport_Team_Project_without_external[~df_SciformaReport_Team_Project_without_external['EWO'].str.contains('Planned', na=False)]
        planned_df = df_SciformaReport_Team_Project_without_external[df_SciformaReport_Team_Project_without_external['EWO'].str.contains('Planned', na=False)]

        # Calculate cumulative totals
        months_for_actual_calc = [m for m in months_for_plot if m <= current_month_for_actuals.strftime('%Y-%m')]
        actual_totals_series = actual_df[months_for_actual_calc].sum()
        actual_cumulative_df = actual_totals_series.cumsum().reset_index()
        actual_cumulative_df.columns = ['Month', 'Actual Hours']
        actual_cumulative_df['Month'] = pd.to_datetime(actual_cumulative_df['Month'], format='%Y-%m')

        planned_totals_series = planned_df[months_for_plot].sum()
        planned_cumulative_df = planned_totals_series.cumsum().reset_index()
        planned_cumulative_df.columns = ['Month', 'Planned Hours']
        planned_cumulative_df['Month'] = pd.to_datetime(planned_cumulative_df['Month'], format='%Y-%m')

        # Combine data using the original month sequence
        total_hours_combined = pd.DataFrame({'Month': pd.to_datetime(months_for_plot, format='%Y-%m')})
        total_hours_combined = pd.merge(total_hours_combined, planned_cumulative_df, on='Month', how='left')
        total_hours_combined = pd.merge(total_hours_combined, actual_cumulative_df, on='Month', how='left')

        # Get the raw actual totals (non-cumulative) for the current month
        actual_raw_totals = actual_df[months_for_actual_calc].sum()

        # Explicitly control x-axis tick labels
        start_date = total_hours_combined['Month'].min()
        end_date = total_hours_combined['Month'].max()
        all_months_for_ticks = pd.date_range(start=start_date, end=end_date, freq='MS')
        tickvals = all_months_for_ticks
        
        # Melt the dataframe for plotting
        melted_df = total_hours_combined.melt(
            id_vars='Month',
            value_vars=['Actual Hours', 'Planned Hours'],
            var_name='Type',
            value_name='Hours'
        )

        # Apply filtering to 'Actual Hours' based on current month's raw actual value
        current_month_idx = total_hours_combined[total_hours_combined['Month'] == current_month_for_actuals].index
        if not current_month_idx.empty:
            current_month_raw_value = actual_raw_totals.loc[current_month_for_actuals.strftime('%Y-%m')] if current_month_for_actuals.strftime('%Y-%m') in actual_raw_totals.index else 0
            if pd.isna(current_month_raw_value) or current_month_raw_value == 0:
                melted_df.loc[
                    (melted_df['Type'] == 'Actual Hours') &
                    (melted_df['Month'] >= current_month_for_actuals),
                    'Hours'
                ] = None
            else:
                melted_df.loc[
                    (melted_df['Type'] == 'Actual Hours') &
                    (melted_df['Month'] > current_month_for_actuals),
                    'Hours'
                ] = None
        else:
            # If current month not in data, exclude all future months
            melted_df.loc[
                (melted_df['Type'] == 'Actual Hours') &
                (melted_df['Month'] > total_hours_combined['Month'].max()),
                'Hours'
            ] = None

        # Create the figure using the Month datetime
        fig = px.line(
            melted_df,
            x='Month',
            y='Hours',
            color='Type',
            title='Total NRE hours: Actual vs Planned (Cumulative hours)',
            color_discrete_sequence=['#10b981', '#3b82f6'],
            markers=True
        )

        # Explicitly control x-axis tick labels
        fig.update_xaxes(
            tickformat='%b',
            showticklabels=True,
            tickangle=0,
            tickfont=dict(size=11),
            dtick="M1",
            automargin=True
        )

        # Add year annotations below x-axis
        total_hours_combined['Year'] = total_hours_combined['Month'].dt.year
        for year in total_hours_combined['Year'].unique():
            months_of_year = total_hours_combined[total_hours_combined['Year'] == year]
            if not months_of_year.empty:
                mid_month = months_of_year.iloc[len(months_of_year) // 2]['Month']
                fig.add_annotation(
                    x=mid_month,
                    y=0,
                    text=str(year),
                    showarrow=False,
                    yshift=-35,
                    font=dict(size=14, color="gray")
                )

        # Update layout with reduced bottom margin
        fig.update_layout(
            plot_bgcolor='white',
            paper_bgcolor='white',
            margin=dict(l=20, r=20, t=50, b=0),  # Reduced bottom margin to 0
            xaxis_title='Month',
            yaxis_title='Cumulative Hours',
            legend_title='Hours Type',
            legend=dict(
                x=0.1,
                y=0.9,
                xanchor='left',
                yanchor='top',
                bgcolor='rgba(255,255,255,0.5)',
            )
        )

        # Create Panel object with markdown text
        panel_object = pn.Column(
            pn.pane.Plotly(
                fig,
                sizing_mode='stretch_both'
            ),
            pn.pane.HTML(
                """
                <div style="text-align: left; margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px;">
                    <p><strong>NRE</strong>: All hours included, excluding External hours.</p>
                </div>
                """,
                sizing_mode='stretch_width'
            ),
            sizing_mode='stretch_both',
            styles=common_panel_object_styles
        )

        return create_card_indicators("NRE Tracking Total", panel_object, card_styles["NRE Tracking Total"])

    except Exception as e:
        panel_object = pn.pane.HTML(
            f"""
            <div class='bg-white p-6 rounded-lg shadow-md'>
                <h2 class='text-xl font-bold mb-4'>NRE Tracking Total</h2>
                <p>Error: {e}</p>
            </div>
            """,
            sizing_mode='stretch_both',
        )
        return create_card_indicators("NRE Tracking Total", panel_object, card_styles["NRE Tracking Total"])

#--------------------------------------------------------------------------------------------------------------------------
# Create card for 'Total expense' 
#--------------------------------------------------------------------------------------------------------------------------
# Datafram: df_SciformaReport_Team
# Total Expense planned vs actual 
#--------------------------------------------------------------------------------------------------------------------------
def create_total_expense():
    try:
        non_month_cols = ['EWO', 'Program#', 'Program', 'Function']

        all_months = [col for col in df_SciformaReport_Team_Project_Expense.columns if col not in non_month_cols]
        month_cols = []
        for col in all_months:
            try:
                col_date = pd.to_datetime(col, format='%Y-%m')
                if col_date >= start_tracking_date:
                    month_cols.append(col)
            except:
                pass

        actual_df = df_SciformaReport_Team_Project_Expense[~df_SciformaReport_Team_Project_Expense['EWO'].str.contains('Planned', na=False)]
        planned_df = df_SciformaReport_Team_Project_Expense[df_SciformaReport_Team_Project_Expense['EWO'].str.contains('Planned', na=False)]

        actual_totals = actual_df[month_cols].sum().reset_index(drop=True)
        planned_totals = planned_df[month_cols].sum().reset_index(drop=True)

        total_expense = pd.DataFrame({
            'Month': month_cols,
            'Actual Expense': actual_totals,
            'Planned Expense': planned_totals
        })

        total_expense['Month'] = pd.to_datetime(total_expense['Month'], format='%Y-%m')
        total_expense = total_expense.sort_values('Month').reset_index(drop=True)

        total_expense['Cumulative Actual Expense'] = total_expense['Actual Expense'].cumsum()
        total_expense['Cumulative Planned Expense'] = total_expense['Planned Expense'].cumsum()

        # Define the current month (July 2025 based on context)
        current_month = pd.to_datetime('2025-07-01')  # Set to July 1, 2025

        # --- Corrected Data Preparation for Cumulative Traces ---
        plot_df_cumulative = total_expense.melt(
            id_vars=['Month'],
            value_vars=['Cumulative Actual Expense', 'Cumulative Planned Expense'],
            var_name='Type',
            value_name='Expense'
        ).sort_values('Month')

        # Apply filtering to 'Cumulative Actual Expense' based on current month's Actual Expense
        current_month_idx = total_expense[total_expense['Month'] == current_month].index
        if not current_month_idx.empty:
            current_month_value = total_expense.loc[current_month_idx, 'Actual Expense'].iloc[0]
            if current_month_value <= 0:
                plot_df_cumulative.loc[
                    (plot_df_cumulative['Type'] == 'Cumulative Actual Expense') &
                    (plot_df_cumulative['Month'] >= current_month),
                    'Expense'
                ] = None
            else:
                plot_df_cumulative.loc[
                    (plot_df_cumulative['Type'] == 'Cumulative Actual Expense') &
                    (plot_df_cumulative['Month'] > current_month),
                    'Expense'
                ] = None

        # --- Create the figure with cumulative traces first ---
        fig = px.line(
            plot_df_cumulative,
            x='Month',
            y='Expense',
            color='Type',
            title='Total Expense: Actual vs Planned (Cumulative & Monthly USD)',
            color_discrete_map={
                'Cumulative Actual Expense': '#10b981',  # Green
                'Cumulative Planned Expense': '#3b82f6'   # Blue
            },
            markers=True
        )

        # --- Corrected Data Preparation for Monthly Traces ---
        monthly_plot_df = total_expense.copy()

        # Filter 'Actual Expense' to stop at current month - 1 if current month's value is <= 0
        current_month_idx = monthly_plot_df[monthly_plot_df['Month'] == current_month].index
        if not current_month_idx.empty:
            current_month_value = monthly_plot_df.loc[current_month_idx, 'Actual Expense'].iloc[0]
            if current_month_value <= 0:
                monthly_plot_df.loc[monthly_plot_df['Month'] >= current_month, 'Actual Expense'] = None
            else:
                monthly_plot_df.loc[monthly_plot_df['Month'] > current_month, 'Actual Expense'] = None
        else:
            monthly_plot_df.loc[monthly_plot_df['Month'] > current_month, 'Actual Expense'] = None

        # --- ADD NEW MONTHLY TRACES TO THE FIGURE ---
        rgba_green = 'rgba(16, 185, 129, 0.5)'
        rgba_blue = 'rgba(59, 130, 246, 0.5)'

        # Add Monthly Planned Expense trace (transparent blue, dashed line)
        fig.add_trace(go.Scatter(
            x=monthly_plot_df['Month'],
            y=monthly_plot_df['Planned Expense'],
            mode='lines+markers',
            name='Monthly Planned Expense',
            line=dict(color=rgba_blue, width=1, dash='dash'),
            marker=dict(size=6, opacity=0.5),
            hovertemplate='<b>%{x|%b %Y}</b><br>Monthly Planned: $%{y:,.2f}<extra></extra>'
        ))

        # Add Monthly Actual Expense trace (transparent green, dashed line)
        fig.add_trace(go.Scatter(
            x=monthly_plot_df['Month'],
            y=monthly_plot_df['Actual Expense'],
            mode='lines+markers',
            name='Monthly Actual Expense',
            line=dict(color=rgba_green, width=1, dash='dash'),
            marker=dict(size=6, opacity=0.5),
            hovertemplate='<b>%{x|%b %Y}</b><br>Monthly Actual: $%{y:,.2f}<extra></extra>'
        ))

        # --- Existing x-axis and annotation logic ---
        fig.update_xaxes(
            tickformat='%b',
            showticklabels=True,
            tickangle=0,
            tickfont=dict(size=11),
            dtick="M1",
            automargin=True
        )

        total_expense['Year'] = total_expense['Month'].dt.year

        for year in total_expense['Year'].unique():
            months_of_year = total_expense[total_expense['Year'] == year]
            if not months_of_year.empty:
                mid_month = months_of_year.iloc[len(months_of_year) // 2]['Month']
                fig.add_annotation(
                    x=mid_month,
                    y=0,
                    text=str(year),
                    showarrow=False,
                    yshift=-35,
                    font=dict(size=14, color="gray")
                )

        # --- Update layout properties ---
        fig.update_layout(
            plot_bgcolor='white',
            paper_bgcolor='white',
            margin=dict(l=20, r=20, t=50, b=0),
            height=650,
            xaxis_title='Month',
            yaxis_title='Cumulative Expense (USD)',
            legend_title='Expense Type',
            yaxis=dict(range=[0, None]), # forces Y-axis to start at 0
            legend=dict(
                x=0.1,
                y=0.99,
                xanchor='left',
                yanchor='top',
                bgcolor='rgba(255,255,255,0.5)',
                traceorder="normal",
                #font=dict(size=10)
            )
        )

        # Create Panel object with markdown text
        panel_object = pn.Column(
            pn.pane.Plotly(
                fig,
                sizing_mode='stretch_both'
            ),
            pn.pane.HTML(
                """
                <div style="text-align: left; margin-top: 0px; margin-bottom: 0px; padding-top: 0px; padding-bottom: 0px;">
                    <p><strong>NRE</strong>: All cost included, Internal & External.</p>
                </div>
                """,
                sizing_mode='stretch_width'
            ),
            sizing_mode='stretch_both',
            styles=common_panel_object_styles
        )

        return create_card_indicators("Total Expense", panel_object, card_styles["Total Expense"])

    except Exception as e:
        panel_object = pn.pane.HTML(
            f"""
            <div class='bg-white p-6 rounded-lg shadow-md'>
                <h2 class='text-xl font-bold mb-4'>Total Expense</h2>
                <p>Expense data not available: {str(e)}</p>
            </div>
            """,
            sizing_mode='stretch_both',
        )
        return create_card_indicators("Total Expense", panel_object, card_styles["Total Expense"])

#////////////////////////////////////////////////////////
#--------------------------------------------------------------------------------------------------------------------------
# Create Dev_dashboard_layout
#--------------------------------------------------------------------------------------------------------------------------
#////////////////////////////////////////////////////////
line_color = "#4472C4"  # Change this to your desired color
font_top_color = "#4472C4"
subtitle_background_color  = "#aee0d9" # "#F2F2F2" #Gray
# Get today's date formatted nicely
today_text = datetime.today().strftime("%B %d, %Y")

# Create dashboard layout
title_dev_dashboard = pn.Row(
    pn.pane.HTML(f"""
        <div style='background-color: {font_top_color}; width: 100%; padding: 10px; box-sizing: border-box;'>
            <h1 style="font-size: 24px; color: #ffffff; margin: 0;">
              <span style="font-weight: 600;">{Project} Dashboard</span>
              <span style="font-size: 14px; color: #bbbbbb; display: block; margin-top: 2px;">
                <span style="color: #ffffff;">Period: {latest_month_text}</span> &nbsp;&bull;&nbsp; Generated: {today_text}
              </span>
            </h1>
        </div>
    """, sizing_mode='stretch_width'),
    sizing_mode='stretch_width',
    styles={'background': font_top_color}
)
#-----------------------------------------------------
# layout Expected 
# Line 1: Chart, SPI_CPI, Millestones and Task followup, Phase indicator
# Line 2: Timeline, NRE Tracking per phase, NRC Followup per phase
# Line 3: Risk Map, NRE Tracking total, Total expense  
#-----------------------------------------------------


# Create dashboard layout
Dev_dashboard_layout = pn.Column(
    pn.Row(
        create_project_charter(),
        create_spi_cpi(),
        create_milestone_tasks_followup(),
        create_phase_indicator(),
        sizing_mode='stretch_width'
    ),
    pn.Spacer(height=15),
    pn.Row(
        create_project_timeline(),
        create_nre_tracking_per_phase(),
        create_nrc_followup_per_phase(),
        sizing_mode='stretch_width'
    ),
    pn.Spacer(height=15),
    pn.Row(
        create_roadmapping(),
        create_risk_map(),
        create_nre_tracking_total(),
        create_total_expense(),
        sizing_mode='stretch_width'
    ),
    sizing_mode='stretch_width'
)

# Full dashboard
Dev_dashboard = pn.Column(
    title_dev_dashboard,
    pn.layout.Divider(margin=(-10, 0, 0, 0), sizing_mode='stretch_width'),
    Dev_dashboard_layout,
    sizing_mode='stretch_width'
)

#|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
#*****************************************************************************************************************************
##############################################################################################################################
# Define Tabs and serve
##############################################################################################################################
#*****************************************************************************************************************************
#|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
print('Script successfully completed')

tabs = pn.Tabs(
    (f"{Project} Dashboard", Dev_dashboard), 
)
# Create html file 
tabs.servable()

###################################################################################################################################
# PRINT in A3 Format, Landscape without margin --  297 mm x 420 mm
###################################################################################################################################