app.py

import streamlit as st
import plotly.graph_objects as go
import pandas as pd

# Streamlit Dashboard
st.set_page_config(page_title="Mersino Dashboard", layout="wide")

# Add custom CSS for card styling
st.markdown(
    """
    <style>
    .card {
        background-color: #f9f9f9;
        border-radius: 10px;
        padding: 20px;
        text-align: center;
        box-shadow: 0px 4px 6px rgba(0, 0, 0, 0.1);
        margin: 10px;
        transition: transform 0.2s ease-in-out;
    }
    .card:hover {
        transform: scale(1.05);
    }
    .card-title {
        font-size: 18px;
        font-weight: bold;
        margin-bottom: 10px;
        color: #333333;
    }
    .card-value {
        font-size: 24px;
        font-weight: bold;
        color: #007BFF;
    }
    </style>
    """,
    unsafe_allow_html=True,
)

# Header Section
st.title("Mersino Dashboard")
st.subheader("Issue Metrics Overview")

# Cards Data
cards = [
    {"title": "Platform", "value": "Odoo"},
    {"title": "Database", "value": "PostgreSQL"},
    {"title": "Table Count", "value": "1,137"},
    {"title": "Total Issues", "value": "34"},
    {"title": "Issues", "value": "Invoice"},
    {"title": "Total Customer Invoices", "value": "10,166"},
]

# First Row of Cards
row1 = cards[:3]
cols1 = st.columns(3)
for col, card in zip(cols1, row1):
    col.markdown(
        f"""
        <div class="card">
            <div class="card-title">{card['title']}</div>
            <div class="card-value">{card['value']}</div>
        </div>
        """,
        unsafe_allow_html=True,
    )

# Second Row of Cards
row2 = cards[3:]
cols2 = st.columns(3)
for col, card in zip(cols2, row2):
    col.markdown(
        f"""
        <div class="card">
            <div class="card-title">{card['title']}</div>
            <div class="card-value">{card['value']}</div>
        </div>
        """,
        unsafe_allow_html=True,
    )

st.markdown("<br><br>", unsafe_allow_html=True) 
# Chart Data
# Pie Chart Data
pie_labels = ['Metrics We Can Retrieve', 'Metrics Requiring Attachments', 'Metrics That Need Clarification']
pie_sizes = [30, 4, 0]

# Value Count Data
offering_types = ['Sales', 'Transactional Rentals', 'Project', 'Repair / Services']
offering_counts = [4, 6, 24, 1]

# Layout for Charts (Pie Chart on top, Bar Chart below)
col1 = st.container()  # Use a single container to stack the charts

# Pie Chart in the Top Section
with col1:
    st.subheader("Metrics Distribution")
    fig_pie = go.Figure(data=[go.Pie(labels=pie_labels, values=pie_sizes, hole=0.4)])
    fig_pie.update_traces(
        hoverinfo="label+percent",
        textinfo="value+percent",
        textfont_size=15,
        marker=dict(colors=["#636EFA", "#EF553B", "#00CC96"], line=dict(color="#FFFFFF", width=2))
    )
    fig_pie.update_layout(showlegend=True)  # Only show legend in the pie chart
    st.plotly_chart(fig_pie)

# Bar Chart in the Bottom Section
with col1:
    st.subheader("Offering Type Distribution")
    fig_bar = go.Figure(
        data=[
            go.Bar(
                x=offering_types,
                y=offering_counts,
                marker_color=["#636EFA", "#EF553B", "#00CC96", "#AB63FA"]
            )
        ]
    )
    fig_bar.update_layout(
        xaxis_title="Offering Type",
        yaxis_title="Issues",
        xaxis=dict(tickangle=-45),
        showlegend=False,  # Remove legend from bar chart
    )
    st.plotly_chart(fig_bar)



# Data
# causes = ["Metrics Needing Attachments","Duplicate Metrics", 
#           "Invoice Issue Confusion"]
# frequencies = [7, 2, 1]
# colors = ['lightblue', 'lightgreen']  # Unique colors for each bar

# # Create the horizontal bar chart
# fig = go.Figure(
#     go.Bar(
#         x=frequencies,
#         y=causes,
#         orientation='h',
#         marker=dict(color=colors),  # Assign multiple colors to bars
#         width=0.4  # Slim bars
#     )
# )

# # Update layout for titles and axes
# fig.update_layout(
#     title="Issues in Data Mapping and Definitions",
#     xaxis_title="Frequency of Issues",
#     yaxis_title="Cause of Issue",
#     yaxis=dict(tickmode="linear"),
#     template="plotly_white"  # Streamlit-friendly template
# )

# # Display in Streamlit
# st.plotly_chart(fig)

# Footer Section with Tabs
st.markdown("<br>", unsafe_allow_html=True)  # Padding before tabs
st.markdown("## Additional Sections")

# Create Tabs
tabs = st.tabs(["Section 1: Metrics Insights and Overview", "Section 2: Metrics Requiring Attachments", "Section 3: Metrics Needing Clarification"])

# Tab 1 Content
with tabs[0]:
    st.subheader("Section : Insights")
    # Collapsible Section for Metric 1: Delivered Not Invoiced
    with st.expander("Metric : Delivered Not Invoiced"):
        st.markdown("""
        - **Metric**: Identify sales orders delivered but not invoiced.
        - **Offering Type**: Sales
        - **How to Verify**: Review "to Invoice" status in the sales app and delivery tickets for open sales orders.
        - **Description**: This issue identifies sales, rental, or dispatch items that have been delivered but are not yet invoiced, highlighting potential gaps in billing processes that could lead to revenue leakage.
        - **Key Tables**:
            - fleet_asset_transaction_log (Serialized Inventory Movement).
            - stock_move (Non-Serialized Inventory Movement).
            - account_move_line (Invoice Lines).
            - sale_order_line,sale_order_recurring_line , sale_dispatch_line (Sales -> we are using this one, Rental, Dispatch).
        - **How it was extracted?**: The query identifies delivered but uninvoiced items by cross-referencing serialized (fleet_asset_transaction_log) and non-serialized (stock_move) inventory movements with sales order lines (sale_order_line) and invoice lines (account_move_line). It checks for products marked as delivered but not reflected in invoicing records, highlighting gaps in the billing process. The result, derived from filtering and comparing key fields across these tables, reveals 1913 invoices where delivery occurred without corresponding invoices.
        - 
         """)

        # Adding Charts
        st.markdown("### **Charts**")
        
        # Chart 1: Total Invoices Delivered but Not Invoiced
        fig111 = go.Figure(data=[
            go.Bar(name="Sales", x=["Sales"], y=[1913], marker_color="blue"),
            # go.Bar(name="Sales + Rental + Dispatch", x=["Sales + Rental + Dispatch"], y=[1115], marker_color="orange")
        ])
        fig111.update_layout(
            title="Total Invoices Delivered but Not Invoiced",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Grouped bar chart mode
        )
        st.plotly_chart(fig111, key="delived_but_not_invoiced")


        # Sample data
        data = [
            {"Invoice Number": "128128", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128163", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128134", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128136", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128137", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128135", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128138", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128129", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128169", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128167", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128133", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128168", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128132", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128058", "Invoice Date": "2024-12-03"},
            {"Invoice Number": "128082", "Invoice Date": "2024-12-03"},
        ]

        # Convert data to a DataFrame for better rendering
        df = pd.DataFrame(data)

        # Display the table
        st.markdown("Below are a few sample invoices where this issue was identified:")
        st.dataframe(df, use_container_width=True)

        # # Chart 1: Total Products Delivered but Not Invoiced
        # fig2 = go.Figure(data=[
        #     go.Bar(name="Overall", x=["Overall"], y=[1655], marker_color="blue"),
        #     go.Bar(name="2024", x=["2024"], y=[767], marker_color="orange")
        # ])
        # fig2.update_layout(
        #     title="Total Products Delivered but Not Invoiced",
        #     yaxis_title="Count",
        #     xaxis_title="",
        #     barmode="group"
        # )
        # st.plotly_chart(fig2)

        # Chart 2: Unique Product Types Delivered but Not Invoiced
        # fig2 = go.Figure(data=[
        #     go.Bar(name="Overall", x=["Overall"], y=[374], marker_color="blue"),
        #     go.Bar(name="2024", x=["2024"], y=[306], marker_color="orange")
        # ])
        # fig2.update_layout(
        #     title="Unique Product Types Delivered but Not Invoiced",
        #     yaxis_title="Count",
        #     xaxis_title="",
        #     barmode="group"
        # )
        # st.plotly_chart(fig2)

        # # Chart 3: Total Quantity Delivered but Not Invoiced
        # fig3 = go.Figure(data=[
        #     go.Bar(name="Overall", x=["Overall"], y=[18544.05], marker_color="blue"),
        #     go.Bar(name="2024", x=["2024"], y=[17590.05], marker_color="orange")
        # ])
        # fig3.update_layout(
        #     title="Total Quantity Delivered but Not Invoiced",
        #     yaxis_title="Quantity",
        #     xaxis_title="",
        #     barmode="group"
        # )
        # st.plotly_chart(fig3)
    

    # Collapsible Section for Metric 2: Missed Taxes
    # Expander Section for Metric Explanation
    with st.expander("Metric: Missed Taxes"):
        # Clear Explanation
        st.markdown("""
        - **Metric**: Missed Taxes
        - **Offering Type**: Sales
        - **Key Tables**:
            - account_move - Invoice Header
            - account_move_line - Invoice Lines
            - res_partner - Customer Information
            - account_tax - Applied Taxes
        - **How it was extracted**:  
            This analysis identifies invoices with missing or incorrect taxes by checking if taxes (tax_line_id) are not applied in invoice lines (account_move_line).  
            Since property_account_position_id is not present in res_partner, we used the customer's ZIP code (res_partner.zip) and matched it with fiscal position-based exemption data. Customers with no valid exemption code in l10n_ca_pst were flagged, and we analyzed whether taxes were applied. This approach flagged 9985 invoices with potential tax issues.
        """)

        # Insights Section
        st.markdown("### Insights")
        st.markdown("""
        - **Total Invoices Flagged**: 9985
        """)

        # Charts Section
        st.markdown("### Charts")
        
        # Chart: Total Invoices with Issues
        fig = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Missed Taxes"], y=[9985], marker_color="red")
        ])
        fig.update_layout(
            title="Total Invoices with Missed Taxes",
            xaxis_title="Category",
            yaxis_title="Number of Invoices",
            barmode="group"
        )
        st.plotly_chart(fig)

        # Sample Data
        data = [
            {"Invoice Number": "128125", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128128", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128129", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128130", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128131", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128132", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128133", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128134", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128135", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128136", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128137", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128138", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128157", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128162", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128163", "Invoice Date": "2024-12-04"},
        ]

        # Convert Data to DataFrame
        df = pd.DataFrame(data)

        # Display the Sample Data Table
        st.markdown("### Sample Invoices")
        st.markdown("Below is a sample of invoices where this issue was identified:")
        st.dataframe(df, use_container_width=True)


    # Collapsible Section for Metric 3: Missed Freight
    with st.expander("Metric : Missed Freight"):
        st.markdown("""
        - **Metric**: Validate freight details (customer pick-up or delivery) and ensure they are billed.
        - **Offering Type**: Sales
        - **Description**: The issue identifies sales orders with freight mentioned in chatter logs but missing in sales or rental lines, indicating potential oversight in recording or billing freight charges.
        - **How to Verify**: Use chatter/notes in  mail_message  to locate freight-related terms and verify invoicing.
        - **Key Tables**:
            -  sale_order_line  (Sales Order Lines).
            -  sale_order_recurring_line  (Recurring Order Lines).
            -  mail_message  (Notes and Chatter).
        - **How it was extracted?**: The query identifies sales orders with freight mentioned in the chatter logs (mail_message.body) but missing in the sales order lines (sale_order_line.name) or recurring lines (sale_order_recurring_line.name). It cross-references chatter logs with sales and rental lines to detect potential omissions in recording or billing freight charges. This analysis flagged 140 sales orders with missing freight entries.
        """)

        st.markdown("### **Insights**")
        st.markdown("""
        1. **Sales Order with Issue**: **140**
        2. **Invoice Id with Issue**: **93**
        """)

        st.markdown("### **Charts**")

        # Data for the bar chart
        categories = ["Sales Order with Issue", "Invoice Id with Issue"]
        values = [0, 0]
        colors = ["blue", "orange"]

        # Create the bar chart
        fig9 = go.Figure(data=[
            go.Bar(name="Sales Order with Issue", x=["Sales Order with Issue"], y=[140], marker_color="blue"),
            go.Bar(name="Invoice Id with Issue", x=["Invoice Id with Issue"], y=[93], marker_color="orange")
        ])

        # Update the layout
        fig9.update_layout(
            title="Value Counts for Issues",
            yaxis_title="Count",
            xaxis_title="",
            barmode="group"
        )
        st.plotly_chart(fig9, key="chart2")
        # # Chart 2: Messages Linked to Sales Orders (Join Condition)
        # fig10 = go.Figure(data=[
        #     go.Bar(name="Overall", x=["Overall"], y=[60569], marker_color="blue"),
        #     go.Bar(name="2024", x=["2024"], y=[648], marker_color="orange")
        # ])
        # fig10.update_layout(
        #     title="Messages Linked to Sales Orders (Join Condition)",
        #     yaxis_title="Count",
        #     xaxis_title="",
        #     barmode="group"
        # )
        # st.plotly_chart(fig9, key="chart2")

        # # Chart 3: Messages with 'Freight' in the Body
        # fig11 = go.Figure(data=[
        #     go.Bar(name="Overall", x=["Overall"], y=[0], marker_color="blue"),
        #     go.Bar(name="2024", x=["2024"], y=[0], marker_color="orange")
        # ])
        # fig11.update_layout(
        #     title="Messages with 'Freight' in the Body",
        #     yaxis_title="Count",
        #     xaxis_title="",
        #     barmode="group"
        # )
        # st.plotly_chart(fig11, key="chart3")

    # Collapsible Section for Metric 5: Incorrect Rate Billed
    with st.expander("Metric : Incorrect Rate Billed"):
        st.markdown("""
        - **Metric**: Validate rental billing rates against quotes or recurring contracts.
        - **Offering Type**: Transactional Rentals
        - **How to Verify**: Compare `price_unit` in `account_move_line` with `sale_order_recurring_line`.
        - **Key Tables**:
            - `account_move_line` (Invoice Line Details).
            - `sale_order_recurring_line` (Recurring Contracts).
        - **Description**: The **Incorrect Rate Billed** metric identifies discrepancies between the recurring quoted price for products/services (in `sale_order_recurring_line`) and the actual invoiced price (in `account_move_line`). This ensures accurate billing and adherence to contracted terms.
        - **How it was extracted?**: The query identifies invoices where the invoiced price (`account_move_line.price_unit`) differs from the quoted price (`sale_order_recurring_line.price_unit`) for rental transactions. It also considers cases where quoted prices are negative, highlighting potential refunds or adjustments. This analysis flagged **1974 invoices** with discrepancies, ensuring pricing accuracy in transactional rentals.
        """)

        st.markdown("### **Insights**")
        st.markdown("""
        1. **Total No. of Invoices with this Issue**: **1974**
        """)

        st.markdown("### **Charts**")

        # Chart: Total No. of Invoices with this Issue
        fig12 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Issues"], y=[1974], marker_color="red")
        ])
        fig12.update_layout(
            title="Total No. of Invoices with this Issue",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="Number of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )
        st.plotly_chart(fig12)

        # Data for the table
        data = [
            {"Invoice Number": "128173", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128174", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128175", "Invoice Date": "2024-12-04"},
            {"Invoice Number": "128056", "Invoice Date": "2024-12-03"},
            {"Invoice Number": "128058", "Invoice Date": "2024-12-03"},
            {"Invoice Number": "128082", "Invoice Date": "2024-12-03"},
            {"Invoice Number": "127860", "Invoice Date": "2024-11-30"},
            {"Invoice Number": "127863", "Invoice Date": "2024-11-30"},
            {"Invoice Number": "127865", "Invoice Date": "2024-11-30"},
            {"Invoice Number": "127868", "Invoice Date": "2024-11-30"},
            {"Invoice Number": "127869", "Invoice Date": "2024-11-30"},
            {"Invoice Number": "127870", "Invoice Date": "2024-11-30"},
            {"Invoice Number": "127871", "Invoice Date": "2024-11-30"},
            {"Invoice Number": "127872", "Invoice Date": "2024-11-30"},
            {"Invoice Number": "127874", "Invoice Date": "2024-11-30"},
        ]

        # Convert data to a DataFrame
        df = pd.DataFrame(data)

        # Display the table in Streamlit
        st.markdown("### Sample Invoices")
        st.markdown("Below is a table of sample invoices with their corresponding dates:")
        st.dataframe(df, use_container_width=True)

    # Expander for Metric 6: Missed Fuel Used
    with st.expander("Metric : Missed Fuel Used"):
        st.markdown("""
        ### **Metric 6: Missed Fuel Used**

        - **Metric**: Ensure all fuel usage changes are billed.
        - **Offering Type**: Transactional Rentals
        - **How to Verify**: Check fuel usage logs in `fleet_asset_transaction_log` and confirm they are billed in corresponding invoices.
        - **Key Tables**:
            - `fleet_asset_transaction_log`: Logs fuel usage changes (`fuel_type`, `previous_fuel_type`, and associated `fleet_asset_id`).
            - `account_move_line`: Contains invoice line details for rentals and other billable transactions.
            - `account_move`: Links invoices to customers and transaction dates.
            - `res_partner`: Stores customer-related details (e.g., names, addresses).
        - **Purpose**: Ensure all logged fuel usage is properly billed to prevent revenue leakage.

        - **How it was extracted?**:
            - We reviewed the `fleet_asset_transaction_log` table to find any logged fuel usage for assets that occurred during rental transactions.
            - Next, we checked the invoices in `account_move_line` to see if these fuel charges were billed to the customer.
            - If no fuel-related charges were found in the invoice, the invoice was flagged as an issue.
            - We ensured the dates in the fuel log overlapped with the invoice date, ensuring the logs and transactions matched.

        ### **Insights**
        1. **Total Number of Invoices with this Issue**: **0**
        """)

        st.markdown("### **Charts**")
        
        # Chart 1: Total No. of Invoices with this Issue
        fig13 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices"], y=[0], marker_color="blue")
        ])
        fig13.update_layout(
            title="Total No. of Invoices with this Issue",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="Number of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display Chart 1
        st.plotly_chart(fig13)

        # # Chart 2: Total No. of Invoices with this Issue
        # fig14 = go.Figure(data=[
        #     go.Bar(name="Invoices with Issues", x=["Invoices"], y=[228], marker_color="red")
        # ])
        # fig14.update_layout(
        #     title="Total No. of Invoices with this Issue",  # Chart title
        #     xaxis_title="Category",  # X-axis label
        #     yaxis_title="Number of Invoices",  # Y-axis label
        #     barmode="group"  # Bar chart mode
        # )

        # # Display Chart 2
        # st.plotly_chart(fig14)

    # Expander for Metric 7
    data = {
        "invoice_number": ["128125", "128128", "128129", "128130", "128131", "128132", "128133", "128134", "128135", "128136", "128137", "128138", "128157", "128162", "128163"],
        "invoice_date": ["2024-12-04"] * 15
    }
    df = pd.DataFrame(data)

    # Display the Streamlit expander with metric details
    with st.expander("Metric: Missed Billing for Delivery/Pick-up (Transactional Rentals)"):
        st.write("""
        - **Metric**: Identify cases where delivery or pick-up services included in sales orders are not billed in invoices.
        - **Offering Type**: Transactional Rentals
        - **Description**: The Missed Billing for Delivery/Pick-up metric identifies instances where delivery or pick-up services included in sales orders are not billed in invoices. This ensures that all delivery-related charges are accurately captured and invoiced.
        - **Key Tables**: 
            - account_move_line - product_id, name
            - sale_order_line - product_template_id, product_uom_qty
        - **How it was extracted?**: The query identifies discrepancies where delivery or pick-up services in sales orders are not invoiced. The query compares account_move_line (invoices) and sale_order_line (sales orders) using product_id and product_template_id to detect missing billing entries.
        """)
        
        st.write("### Insights")
        st.write(f"""
        1. **Total Invoices with this Issue**: {len(df)} invoices have missing billing for delivery or pick-up services.
        """)

        # Display the data table of invoices
        st.dataframe(df)

        # Chart: Comparison of Total Invoices and Unique Product IDs
        fig = go.Figure(data=[
            go.Bar(x=["Total Invoices with Issues"], y=[len(df)],
                marker_color="red", text=[len(df)], textposition='auto')
        ])
        fig.update_layout(title="Comparison of Total Invoices and Unique Product IDs", xaxis_title="", yaxis_title="Count")
        st.plotly_chart(fig)

    # Expander for Metric 8
    with st.expander("Metric: Delivered Not Invoiced (Transactional Rentals)"):
        st.write("""
        - **Metric**: Identify delivered rental products not invoiced.
        - **Offering Type**: Transactional Rentals
        - **Description**: The Delivered Not Invoiced metric identifies rental items or assets that have been delivered but not yet invoiced, ensuring all delivered items are billed to prevent revenue leakage.
        - **Key Tables**: 
            - fleet_asset_transaction_log - project_id, to_stage_id, product_id, fleet_asset_id
            - stock_move - project_id, product_id, product_qty
            - account_move_line - x_studio_fleet_number, product_id, quantity, analytic_distribution
            - sale_order_recurring_line - product_id, product_uom_quantity, fleet_asset_ids
        - **How it was extracted?**: The query identifies rental items or assets that have been delivered (based on fleet_asset_transaction_log and stock_move) but are not yet invoiced (account_move_line). It compares delivered quantities from inventory and serialized transaction logs with billed quantities from invoice lines to detect discrepancies.
        """)
        
        st.write("### Insights")
        st.write("""
        1. **Total No. of Invoice has this Issue**: 764
        """)
        
        st.markdown("### **Charts**")
        # Chart: Total No. of Invoices with this Issue
        fig16 = go.Figure(data=[
            go.Bar(name="Invoices with Issue", x=["Invoices with Issue"], y=[764], 
                marker_color="red", text=[764], textposition='auto')
        ])
        fig16.update_layout(
            title="Total No. of Invoices with this Issue",
            xaxis_title="Issue",
            yaxis_title="Invoices",
            barmode="group"
        )

        # Display the chart
        st.plotly_chart(fig16)

    # Expander for Metric 9
    with st.expander("Metric: Incorrect Duration Billed (Transactional Rentals)"):
        st.write("""
        - **Metric**: Identify discrepancies between rental durations logged in fleet asset transactions, stock movements, or field team forms, and the duration billed on invoices.
        - **Offering Type**: Transactional Rentals
        - **Description**: This metric tracks mismatches between the actual rental periods documented in fleet logs and the periods billed to customers. Accurate tracking ensures that billing aligns with rental agreements.
        - **Key Tables**: 
            - fleet_asset_transaction_log (Tracks fleet asset movements, includes fields like project_id, to_stage_id, product_id, fleet_asset_id, start_date, end_date)
            - stock_move (Logs inventory movements, includes fields like project_id, product_id, product_qty, date)
            - account_move_line (Invoice line details, includes fields like x_studio_fleet_number, product_id, quantity, and potentially empty start and end date fields x_studio_start_date, x_studio_end_date)
        - **How it was extracted**: The SQL query checks for invoices where the duration billed does not match the actual rental duration documented. It looks for discrepancies between the start and end dates in fleet asset transactions versus those recorded on invoice lines. Notably, many invoice lines lack start and end dates, which might explain why discrepancies are not found.
        """)
        
        st.write("### Insights")
        st.write("""
        1. **Total No. of Invoices with this Issue**: 0
        It appears there are currently no invoices with duration discrepancies. This could be due to many invoices missing detailed start and end dates.
        """)

        st.markdown("### **Charts**")
        # Chart: Total No. of Invoices with this Issue
        fig17 = go.Figure(data=[
            go.Bar(name="Invoices with Issue", x=["Invoices with Issue"], y=[0], 
                marker_color="red", text=[0], textposition='auto')
        ])
        fig17.update_layout(
            title="Total No. of Invoices with Incorrect Duration Billed",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig17)


    # Expander for Metric 10
    with st.expander("Metric : Missed Billing for Damaged Equipment (Transactional Rentals)"):
        st.write("""
        - **Metric**: Check items consumed for replacement, track repair expenses, and ensure they are billed.
        - **Offering Type**: Transactional Rentals
        - **Description**: The Missed Billing for Damaged Equipment metric identifies instances where replacement or repair costs for damaged equipment are consumed but not fully invoiced, ensuring accurate billing.
        - **Key Tables**: 
               - stock_move: product_id, quantity_done, state, repair_id
               - account_move_line: product_id, quantity, move_id
               - account_move: id, move_type
        - **How it was extracted?**: The query compares the quantity consumed in stock_move (filtered for repair-specific transactions using repair_id and state='done') with the quantity billed in account_move_line. It joins the account_move table to ensure only invoices of type in_invoice are considered. The result identifies 3,360 invoices with this issue.
        """)
        
        st.write("### Insights")
        st.write("""
        1. **Total No. of Invoices with this Issue**: 3360
        """)

        st.markdown("### **Charts**")
        # Chart: Total No. of Invoices with this Issue
        fig18 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issues"], y=[3360], 
                marker_color="blue", text=[2154], textposition='auto')
        ])
        fig18.update_layout(
            title="Total No. of Invoices with Missed Billing for Damaged Equipment",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig18)


    # Expander for Metric 11
    with st.expander("Metric : Review Delivery Tickets (Project)"):
        st.write("""
        - **Metric**: Ensure delivery dates in sales orders and delivery tickets are accurate.
        - **Offering Type**: Project
        - **Description**: The Review Delivery Tickets metric identifies invoices with delivery date mismatches by comparing the actual delivery dates with the invoiced delivery dates to ensure accurate and consistent billing.
        - **Key Tables**: 
            - stock_picking: date_done, origin
            - account_move: id, date (invoiced delivery date)
            - account_move_line: move_id, product_id, quantity
        - **How it was extracted?**: The Review Delivery Tickets metric identifies posted invoices with delivery date mismatches by comparing actual delivery dates (stock_picking.date_done) with invoiced delivery dates (account_move.date) for completed deliveries. The query ensures discrepancies are flagged, ensuring all delivery-related transactions are accurately tracked and invoiced
        """)
        
        st.write("### Insights")
        st.write("""
        1. **Total No. of Invoices with this Issue**: 92
        """)

        st.markdown("### **Charts**")
        # Chart: Total No. of Invoices with this Issue
        fig19 = go.Figure(data=[
            go.Bar(name="Invoices with Issue", x=["Invoices with Issue"], y=[92], 
                marker_color="green", text=[92], textposition='auto')
        ])
        fig19.update_layout(
            title="Total No. of Invoices with Issue in Delivery Tickets",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig19)


    # Expander for Metric 12
    with st.expander("Metric: Start/End Dates (Project)"):
        st.write("""
        - **Metric**: Identify potential over/under billing by ensuring start and end dates in fleet/item movement logs align with invoices.
        - **Offering Type**: Project
        - **How to determine**: This metric evaluates alignment between actual project dates and invoiced dates across fleet movements and item dispatches to identify discrepancies in the 28-day billing cycle. This includes examining the days when billing should occur (day 17 of the cycle) and checking against delivery and demobilization activities recorded on delivery tickets.
        - **Description**: The metric checks for alignment between the fleet or item movement dates and the dates billed to customers. Discrepancies can indicate over or under billing, which affects revenue accuracy and customer satisfaction.
        - **Key Tables**:
            - **fleet_asset_transaction_log**: Contains logs of fleet movements including project IDs and dates (project_id, to_stage_id, product_id, fleet_asset_id, start_date, end_date).
            - **stock_move**: Records item movements associated with projects (project_id, product_id, product_qty, date).
            - **account_move_line**: Holds detailed invoicing data including fleet numbers and billing dates (x_studio_fleet_number, product_id, quantity, analytic_distribution, x_studio_start_date, x_studio_end_date).
        - **How it was extracted?**: The query identifies discrepancies between billed dates (from account_move_line.x_studio_start_date, x_studio_end_date) and actual dates logged in fleet and item movement records (from fleet_asset_transaction_log and stock_move). By linking data using identifiers like fleet_asset_id and project_id, the system flags invoices that potentially misalign with project timelines, indicating either premature or delayed billing.
        """)

        st.write("### Insights")
        st.write("""
        - **Total No. of Invoices with this Issue**: 68
        This reflects a detailed check across invoices to ensure that every transaction fits within the prescribed billing period, highlighting any discrepancies that could lead to financial inaccuracies.
        """)

        st.markdown("### **Charts**")
        # Chart displaying the total number of invoices with issues
        fig20 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issue"], y=[68], 
                marker_color="purple", text=[68], textposition='auto')
        ])
        fig20.update_layout(
            title="Total No. of Invoices with Issue in Start/End Dates",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig20)

    # Metric 13: Fleet Number Validation
    # Expander for Metric 13
    with st.expander("Metric: Fleet Number Validation (Project)"):
        st.write("""
        - **Metric**: Ensure all serialized equipment movements recorded in the fleet movement log are accurately reflected in invoices.
        - **Offering Type**: Project
        - **How to determine**: Cross-check Fleet Movement/Item Movement against invoices for potential over/under billing.
        - **Description**: This metric checks for discrepancies in serialized equipment movements between fleet logs and invoices to prevent billing errors.
        - **Key Tables**: 
                - fleet_asset_transaction_log - project_id, to_stage_id, product_id, fleet_asset_id, start_date, end_date
                - stock_move - project_id, product_id, product_qty, date
                - account_move_line - x_studio_fleet_number, product_id, quantity, analytic_distribution, x_studio_start_date, x_studio_end_date
        - **Current Findings**: No issues detected in the current invoice set.
        """)
        
        st.write("### Insights")
        st.write("""
        1. **Total Instances with this Issue**: 0
        """)

        st.markdown("### **Charts**")
        # Chart: Total Instances with this Issue
        fig21 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issues"], y=[0], 
                marker_color="green", text=[0], textposition='auto')
        ])
        fig21.update_layout(
            title="Total Instances with Fleet Issue",
            xaxis_title="Issue",
            yaxis_title="No. of Invoices",
            barmode="group"
        )

        # Display the chart
        st.plotly_chart(fig21)


    # Metric 14: Quantity
    # Expander for Metric 14
    with st.expander("Metric : Quantity"):
        st.write("""
        - **Metric**: Ensure accurate billing for project-based operations by identifying discrepancies between billed quantities and expected quantities calculated from fleet movement durations and actual item movements.
        - **Offering Type**: Project
        - **How to determine**: Depends on dates in Start/End to ensure billing for appropriate # of days - Potential over/under billing  - Could be tangible items or duration periods depending on how the Sales Order is entered.
        - **Description**: The Quantity metric ensures accurate billing for project-based operations by identifying discrepancies between billed quantities and expected quantities calculated from fleet movement durations and actual item movements.
        - **Key Tables**: 
                - fleet_asset_transaction_log - project_id, to_stage_id, product_id, fleet_asset_id, start_date, end_date
                - stock_move - project_id, product_id, product_qty, date
                - account_move_line - x_studio_fleet_number, product_id, quantity, analytic_distributionx_studio_start_date, x_studio_end_date
        - **How it was extracted?**: The query identifies invoices with potential over/under billing by comparing the billed quantity (account_move_line.quantity) with the expected quantity calculated from fleet movement duration (fleet_asset_transaction_log.start_date and end_date) and actual item movements (stock_move.product_qty). It flags discrepancies when the billed quantity differs from the calculated expected quantity for the rental period. This ensures accurate billing for project-based operations.
        """)
        
        st.write("### Insights")
        st.write("""
        1. **Total Instances with this Issue**: 0
        """)

        st.markdown("### **Charts**")
        # Chart: Total Instances with this Issue
        fig1111 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issues"], y=[0], 
                marker_color="green", text=[0], textposition='auto')
        ])
        fig1111.update_layout(
            title="Total Instances with Fleet Issue",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig1111, key="fleet_issue_chart")


    # Metric 16: Quantity
    # Expander for Metric 16
    with st.expander("Metric: Review Last Invoice"):
        st.write("""
        - **Metric**: Ensure billing continuity for projects by identifying gaps between the last billed period and the current date to prevent unbilled periods.
        - **Offering Type**: Project
        - **How to determine**: Ensure Billing continues and no dates are missed - Sales>Projects>Invoices.
        - **Description**: The query retrieves invoices flagged with potential billing continuity issues by comparing the last recurring invoice date to the most recent invoice details.
        - **Key Tables**: 
                - account_move - partner_id
                - account_move_line - id, x_studio_start_date, x_studio_end_date
                - sale_order - partner_id, last_recurring_invoice_date
        - **How it was extracted?**: The query identifies 6483 invoices with billing gaps by checking if the last_recurring_invoice_date is missing or earlier than the last billed x_studio_end_date. It flags invoices where billing continuity is at risk as "Potential Billing Continuity Issue."
        """)

        st.write("### Insights")
        st.write("""
        1. **Total Instances with this Issue**: 6483
        """)

        st.markdown("### **Charts**")
        # Chart: Total Instances with this Issue
        fig1112 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issue"], y=[6483], 
                marker_color="red", text=[6483], textposition='auto')
        ])
        fig1112.update_layout(
            title="Total Invoices with Billing Continuity Issues",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig1112)

    # Metric 17: Quantity
    # Expander for Metric 17
    with st.expander("Metric: Review Pump Activity and Billing Accuracy"):
        st.write("""
        - **Metric**: Ensure accuracy in billing for pump activity by verifying actual usage against contracted rates to identify potential overbilling or underbilling.
        - **Offering Type**: Project
        - **How to determine**: Reviews pump activity to tie back to the Sales Order, verifying continuous or single-rate charges and comparing actual usage against Contract/PO/SA.
        - **Description**: The query retrieves invoices flagged with overbilling or underbilling by comparing contracted billing rates (from `sale_order_recurring_line`) against actual logged hours (from `fleet_asset_hours_log`) and the invoiced amounts (from `account_move_line`).
        - **Key Tables**:
            - `sale_order_recurring_line` - shift, product_id, fleet_asset_id, product_uom_qty, price_unit, discount_percentage
            - `fleet_asset_hours_log` - fleet_asset_id, origin, hours
            - `account_move_line` - move_id, price_subtotal
        - **How it was extracted?**: The query identifies discrepancies by calculating the contracted subtotal, usage-based billing, and comparing it with the actual invoiced amount. Invoices where the invoiced amount differs significantly from usage-based billing are flagged as "Overbilling" or "Underbilling."
        """)

        st.write("### Insights")
        st.write("""
        1. **Total Instances with this Issue**: 0
        """)

        st.markdown("### **Charts**")
        # Chart: Total Instances with this Issue
        fig_pump_billing = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issue"], y=[0], 
                marker_color="orange", text=[0], textposition='auto')
        ])
        fig_pump_billing.update_layout(
            title="Total Invoices with Overbilling/Underbilling Issues",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig_pump_billing)


    # Metric 18: Fuel -> this is duplicated
        
    # Metric 19: Quantity Validation
    # Expander for Metric 19
    with st.expander("Metric 19: T&M Billing - Time Tracking (Project)"):
        st.write("""
        - **Metric**: Ensure time tracking data is accurately billed by comparing tracked time (from tasks) against billed quantities in invoices.
        - **Offering Type**: Project
        - **How to determine**: Compare time logged in project time tracking (`account_analytic_line`) to the billed quantities in invoices (`account_move_line`).
        - **Description**: The query identifies potential overbilling or underbilling by comparing total tracked hours (`unit_amount`) against total billed quantities (`quantity`) for each project and product.
        - **Key Tables and Columns**:
            - `account_analytic_line`: `project_id`, `unit_amount` (tracked hours), `product_id`
            - `account_move_line`: `product_id`, `quantity` (billed quantity), `move_id`
            - `account_move`: `invoice_origin`, `state`, `move_type`, `invoice_date`
        - **How it was extracted?**: The query aggregates tracked hours and billed quantities for each project and product by linking invoices (`account_move`) to time tracking entries (`account_analytic_line`) and invoice lines (`account_move_line`). Instances with mismatched tracked hours and billed quantities are flagged as "Overbilling" or "Underbilling."
        """)

        # Add insights section
        st.write("### Insights")
        total_issues = 0  # Replace this with the actual result from the query
        st.write(f"1. **Total Instances/Products/Services with this Issue**: {total_issues}")

        # Create a bar chart
        st.markdown("### **Charts**")
        fig_t_and_m = go.Figure(data=[
            go.Bar(name="Instances with Issues", x=["Instances with Issues"], y=[total_issues], 
                marker_color="blue", text=[total_issues], textposition='auto')
        ])
        fig_t_and_m.update_layout(
            title="Total Instances with T&M Billing (Time Tracking) Issues",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="Count of Instances",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig_t_and_m)


    # Expander for Metric 20
    with st.expander("Metric : T&M Billing - Vendor Bills (Project)"):
        st.write("""
        - **Metric**: Ensure vendor bills tied to projects are accurately billed to prevent billing gaps
        - **Offering Type**: Project
        - **How to determine**: Reviews vendor bills for any pass through costs that need to be billed - SO>Projects>Project Status>Vendor Bills.
        - **Description**: Identifies discrepancies between vendor bill quantities and invoiced quantities to ensure accurate project billing.
        - **Key Tables**: 
                - purchase_order - project_id
                - purchase_order_line - product_id, product_qty, price_unit
        - **How it was extracted?**: The query checks vendor bills (purchase_order_line) linked to projects (purchase_order) against invoiced quantities (account_move_line) to ensure accurate billing. It identifies 39 unique invoice IDs with billing gaps by comparing the billed quantities with the ordered quantities, highlighting discrepancies where vendor bills tied to projects are not fully invoiced.
        """)
        
        st.write("### Insights")
        st.write("""
        1. **Total Vendor Bills with this Issue**: 39
        """)

        st.markdown("### **Charts**")
        # Chart: Total Vendor Bills with this Issue
        fig23 = go.Figure(data=[
            go.Bar(name="Vendor Bills with Issues", x=["Vendor Bills with Issue"], y=[39], 
                marker_color="orange", text=[39], textposition='auto')
        ])
        fig23.update_layout(
            title="Total Vendor Bills with Issues",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="Count of Vendor Bills",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig23)

    # Expander for Metric 21
    with st.expander("Metric : Invoice Date (Project)"):
        st.write("""
        - **Metric**: Ensure accurate invoice dates to prevent pre/post billing issues.
        - **Offering Type**: Project
        - **How to determine**: Verifies invoice dates to ensure they fall within the appropriate billing period, identifying instances of:
            - **Pre-Billing**: Invoices generated before the project start date.
            - **Post-Billing**: Invoices generated after the project end date.
        - **Description**: 
        The Invoice Date metric ensures invoices are generated at the appropriate time by comparing the invoice date with the project's start and end dates. This helps prevent cash flow issues, maintains compliance with agreed terms, and ensures accurate billing practices.
        - **Key Tables**: 
            - `account_move`: Stores invoice details like ID, date, invoice_date, and move_type.
            - `project_project`: Stores project details including start and end dates.
            - `sale_order`: Links projects to invoices.
        - **How it was extracted?**: 
        The query identifies invoices where the invoice date falls:
            - **Before the project start date** (pre-billing issue).
            - **After the project end date** (post-billing issue).
        This ensures billing aligns with the project's agreed period.
        """)

        # Total Issues
        total_issues = 674  # Replace this with the count from the query
        st.write("### Insights")
        if total_issues == 0:
            st.write("1. **No Invoices with Pre/Post Billing Issues Detected**")
        else:
            st.write(f"1. **Total Invoices with Billing Issues**: {total_issues}")

        # Chart Section
        st.markdown("### **Charts**")
        fig24 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issues"], y=[total_issues], 
                marker_color="red", text=[total_issues], textposition='auto')
        ])
        fig24.update_layout(
            title="Total Invoices with Pre/Post Billing Issues",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig24)

        # Add the table for the first 15 rows
        st.markdown("### **First 15 Invoices with Issues**")
        
        # Sample data (first 15 rows)
        data = [
            ["128125", "2024-12-04"],
            ["128132", "2024-12-04"],
            ["128133", "2024-12-04"],
            ["128055", "2024-12-03"],
            ["127929", "2024-11-30"],
            ["127952", "2024-11-30"],
            ["128016", "2024-11-30"],
            ["128022", "2024-11-30"],
            ["128031", "2024-11-30"],
            ["128033", "2024-11-30"],
            ["128052", "2024-11-30"],
            ["128150", "2024-11-30"],
            ["178", "2024-11-29"],
            ["127839", "2024-11-28"],
            ["127773", "2024-11-26"]
        ]
        
        # Convert to a DataFrame for display
        df = pd.DataFrame(data, columns=["Invoice ID", "Invoice Date"])
        
        # Display the table
        st.table(df)

    # Expander for Metric 22
    with st.expander("Metric : Payment Terms (Project)"):
        st.write("""
        - **Metric**: Ensure payment terms in invoices match those in the associated project.
        - **Offering Type**: Project
        - **How to determine**: Check the project's payment terms and compare them with the invoice's payment terms.
        - **Description**: 
        The "Payment Terms Issue" metric identifies invoices where:
            1. Payment terms are missing.
            2. Payment terms in the invoice do not match the payment terms specified in the related project or associated documents.
        This ensures accurate billing and reduces discrepancies.
        - **Key Tables**: 
            - `account_move`: Contains invoice details like ID, partner, payment terms, and invoice date.
            - `sale_order`: Links invoices to their respective projects.
            - `project_project`: Holds details about the project and related payment terms.
        - **How it was extracted?**: 
        The query identifies invoices with the following conditions:
            1. The invoice is **posted** (`state='posted'`).
            2. The invoice is an **outgoing invoice** (`move_type='out_invoice'`).
            3. The invoice date is **after May 1, 2024** (`invoice_date > '2024-05-01'`).
        It then compares the payment terms in the invoice with the payment terms in the project. If they do not match, the invoice is flagged. The final output contains invoice numbers and dates for flagged invoices.
        """)

        st.write("### Insights")
        st.write("""
        1. **Total Unique Invoices with this Issue**: 24
        """)

        st.markdown("### **Charts**")
        # Chart: Total Unique Invoices with this Issue
        fig25 = go.Figure(data=[
            go.Bar(name="Invoices with Issue", x=["Invoices with Issue"], y=[24], 
                marker_color="red", text=[24], textposition='auto')
        ])
        fig25.update_layout(
            title="Total Invoices with Payment Terms Issue",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig25)

        st.markdown("### **Detailed Table**")
        
        # Define the table data
        data = [
            ["125510", "2024-10-31"],
            ["126121", "2024-10-29"],
            ["125813", "2024-10-25"],
            ["125164", "2024-10-14"],
            ["125031", "2024-10-10"],
            ["123216", "2024-09-10"],
            ["122806", "2024-08-29"],
            ["121146", "2024-07-30"],
            ["121341", "2024-07-29"],
            ["120017", "2024-06-27"],
            ["119788", "2024-06-27"],
            ["119795", "2024-06-27"],
            ["119696", "2024-06-26"],
            ["120018", "2024-06-24"],
            ["119205", "2024-06-11"]
        ]

        # Convert the data into a DataFrame
        df = pd.DataFrame(data, columns=["Invoice Number", "Invoice Date"])

        # Display the table
        st.table(df)


    # Expander for Metric 23
    with st.expander("Salesperson Assignment Validation (Project)"):
        st.write("""
        - **Metric**: Ensure that invoices are assigned to valid salespersons for accurate role-based tracking and accountability.
        - **Offering Type**: Project
        - **How to determine**: Validate invoice assignments and ensure the assigned user belongs to the appropriate role (e.g., "Sales").
        - **Description**: This metric identifies invoices where:
            1. No person is assigned at all, leaving the responsibility unclear.
            2. The assigned person is not part of the "Sales" department or related roles.
            This ensures accurate role assignments and avoids potential accountability issues in invoicing processes.
        - **Key Tables**: 
            - `account_move`: Holds invoice details (`invoice_user_id`, `name`, `invoice_date`).
            - `res_groups_users_rel` & `res_groups`: Used to identify the roles assigned to users (e.g., Sales, Admin).
            - `project_project`: Links the project to the responsible user (`user_id`).
        - **How it was extracted?**: 
            1. **Unassigned Users**: A query checks for invoices (`account_move`) with no assigned person (`invoice_user_id`) or where assignments mismatch the project user (`user_id` in `project_project`).
            2. **Invalid Roles**: A second query verifies if the assigned person's role includes "sales." If not, those invoices are flagged.
        """)

        # Insights Section
        st.write("### Insights")
        st.write("""
        1. **Total Invoices with No Assigned Person**: 1,441
        2. **Total Invoices with Assigned Person Not in Sales Role**: 334
        """)

        # Chart: Total Instances with Issues
        st.markdown("### **Charts**")
        fig26 = go.Figure(data=[
            go.Bar(name="No Assigned Person", x=["No Assigned Person"], y=[1441], 
                marker_color="red", text=[1441], textposition='auto'),
            go.Bar(name="Not in Sales Role", x=["Not in Sales Role"], y=[334], 
                marker_color="orange", text=[334], textposition='auto')
        ])
        fig26.update_layout(
            title="Invoices with Salesperson Assignment Issues",  # Chart title
            xaxis_title="Issue Type",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )
        st.plotly_chart(fig26)

        # Display Table: No Assigned Person
        st.write("### Latest 15 Invoices with No Assigned Person")
        no_assigned_person_table = pd.DataFrame({
            "Invoice Name": ["127885", "127992", "128052", "128060", "128150", 
                            "178", "127846", "127575", "127215", "127227", 
                            "126934", "126616", "126476", "177", "125914"],
            "Invoice Date": ["2024-11-30", "2024-11-30", "2024-11-30", "2024-11-30", "2024-11-30", 
                            "2024-11-29", "2024-11-27", "2024-11-21", "2024-11-20", "2024-11-20", 
                            "2024-11-14", "2024-11-06", "2024-10-31", "2024-10-31", "2024-10-28"]
        })
        st.table(no_assigned_person_table)

        # Display Table: Not in Sales Role
        st.write("### Latest 15 Invoices with Assigned Person Not in Sales Role")
        not_in_sales_table = pd.DataFrame({
            "Invoice Name": ["128125", "128128", "128129", "128130", "128131", 
                            "128132", "128133", "128134", "128135", "128136", 
                            "128137", "128138", "128157", "128162", "128163"],
            "Invoice Date": ["2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", 
                            "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", 
                            "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04"]
        })
        st.table(not_in_sales_table)


    # Expander for Metric 24
    with st.expander("Metric : Branch (Project)"):
        st.write("""
        - **Metric**: Ensure that invoices are assigned to the correct branch for accurate revenue allocation and reporting.
        - **Offering Type**: Project
        - **How to determine**: Verify correct Branch for Revenue.
        - **Description**: This metric identifies invoices where the branch assignment is inconsistent. It compares the branch (team_id) in invoices (`account_move`) with the branch in related sales orders (`sale_order.team_id`). Ensuring consistent branch assignments helps with accurate branch-based revenue reporting.
        - **Key Tables**: 
            - `account_move`: team_id
            - `sale_order`: team_id
            - `crm_team`: name
        - **How it was extracted?**: 
            - The query checks for mismatches between the branch (`team_id`) assigned to invoices (`account_move`) and sales orders (`sale_order.team_id`). 
            - **Total Issues Found**: 184 invoices with mismatched branch assignments.
        """)

        # Insights Section
        st.write("### Insights")
        st.write("""
        1. **Total Invoices with Incorrect Branch Assignments**: 184
        """)

        # Chart: Total Branch Issues
        st.markdown("### **Charts**")
        fig27 = go.Figure(data=[
            go.Bar(name="In-Correct Branch Assignments", x=["In-Correct Assignments"], y=[184], 
                marker_color="red", text=[184], textposition='auto')
        ])
        fig27.update_layout(
            title="Branch Assignments Verification",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )
        st.plotly_chart(fig27)

        # Display Table: Latest 15 Invoices with Incorrect Branch Assignments
        st.write("### Latest 15 Invoices with Incorrect Branch Assignments")
        branch_issue_table = pd.DataFrame({
            "Invoice Name": ["128170", "127931", "128050", "127686", "127277", 
                            "126892", "126893", "126894", "126784", "126655", 
                            "126593", "126594", "126618", "126257", "126510"],
            "Invoice Date": ["2024-12-04", "2024-11-30", "2024-11-30", "2024-11-25", "2024-11-20", 
                            "2024-11-13", "2024-11-13", "2024-11-13", "2024-11-12", "2024-11-07", 
                            "2024-11-06", "2024-11-06", "2024-11-06", "2024-10-31", "2024-10-31"]
        })
        st.table(branch_issue_table)


    # Expander for Metric 25
    with st.expander("Metric : Payment Terms - Payment Date"):
        st.write("""
        - **Metric**: Ensure invoices have accurate payment terms and due dates to prevent errors in billing and payment collection.
        - **Offering Type**: Project
        - **How to determine**: Verify for correct payment date.
        - **Description**: The Payment Terms metric identifies invoices where the due date (invoice_date_due) is incorrect. This is done by comparing it with the calculated due date based on the payment terms (account_payment_term) and payment term lines (account_payment_term_line). This ensures invoices adhere to the correct payment terms, preventing discrepancies in payment timelines.
        - **Key Tables**: 
            - `account_move`: Stores invoice details, including `invoice_payment_term_id`, `invoice_date`, and `invoice_date_due`.
            - `account_payment_term`: Contains payment term names and IDs.
            - `account_payment_term_line`: Defines payment term rules, such as days and months for due date calculation.
        - **How it was extracted?**:
            - The query calculates the expected due date by adding the number of days (from account_payment_term_line) to the invoice date (account_move).
            - The calculated due date is then compared to the due date recorded in the invoice (invoice_date_due).
            - This process flagged 44 invoices where the due date did not match the expected value.
        """)

        # Insights Section
        st.write("### Insights")
        st.write("""
        1. **Total Invoices with Incorrect Payment Terms**: 44
        """)

        # Chart: Payment Terms Issues
        st.markdown("### **Charts**")
        fig1113 = go.Figure(data=[
            go.Bar(
                name="Incorrect Payment Terms", 
                x=["Incorrect Payment Terms"], 
                y=[44], 
                marker_color="red", 
                text=["44"], 
                textposition='auto'
            )
        ])
        fig1113.update_layout(
            title="Invoices with Incorrect Payment Terms",  # Updated chart title
            xaxis_title="Payment Term Issues",  # Updated X-axis label
            yaxis_title="Number of Invoices",  # Updated Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig1113)

        # Display Table: Latest Invoices with Payment Term Issues
        st.write("### Latest Invoices with Payment Term Issues")
        payment_term_issues_table = pd.DataFrame({
            "Invoice Name": ["125755", "125587", "113662", "113158", "113159", 
                            "112963", "112719", "112257", "111888", "111301", 
                            "111306", "110035", "109970", "109751", "109505"],
            "Invoice Date": ["2024-10-24", "2024-10-22", "2024-01-29", "2024-01-15", "2024-01-15", 
                            "2024-01-08", "2023-12-28", "2023-12-14", "2023-12-05", "2023-11-17", 
                            "2023-11-17", "2023-10-19", "2023-10-17", "2023-10-09", "2023-09-29"]
        })
        st.table(payment_term_issues_table)


    # Expander for Metric 26
    with st.expander("Metric: Product (Project)"):
        st.write("""
        - **Metric**: Review product codes used in sales orders and invoices to identify issues such as missing product codes or mismatches.
        - **Offering Type**: Project
        - **Description**: This metric ensures that invoices accurately reflect the product details from the sales orders. By validating product IDs between invoices and their related sales orders, we can ensure proper booking to the correct PL/BS account and avoid errors caused by product mismatches.
        - **Key Tables**:
            - `account_move_line`: Contains product details used in invoices.
            - `sale_order_line`: Contains product details used in sales orders.
            - `account_move`: Links invoices to sales orders through the `invoice_origin` field.
        - **How it was extracted?**:
            - The query compares product IDs from the invoice lines (`account_move_line.product_id`) and sales order lines (`sale_order_line.product_id`).
            - It identifies discrepancies where the product IDs in the invoices do not match those in the corresponding sales orders.
            - Only invoices created after **May 1, 2024** were considered, and a total of **8,872 invoices** were flagged as having mismatched product IDs.
        """)

        # Insights Section
        st.write("### Insights")
        st.write("""
        1. **Total Invoices with Product Issues**: 8,872
        2. **Types of Issues**: 
            - Product Mismatch between Sales Orders and Invoices
        """)

        # Chart: Total Invoices with Product Issues
        st.markdown("### **Charts**")
        fig28 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Product Issues"], y=[8872], 
                marker_color="red", text=[8872], textposition='auto')
        ])
        fig28.update_layout(
            title="Total Invoices with Product Issues",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )
        st.plotly_chart(fig28)

        # Display Table: Latest 15 Invoices with Product Issues
        st.write("### Latest 15 Invoices with Product Issues")
        product_issue_table = pd.DataFrame({
            "Invoice Name": [
                "128125", "128128", "128129", "128130", "128131",
                "128132", "128133", "128134", "128135", "128136",
                "128137", "128138", "128157", "128162", "128163"
            ],
            "Invoice Date": [
                "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04",
                "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04",
                "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04"
            ]
        })
        st.table(product_issue_table)


    
    # Expander for Metric 28
    with st.expander("Metric: Label (Project) - Ensure Invoice Consistency with Sales Orders"):
        # Title and Description
        st.write("""
        - **Metric**: Ensure that the product price and details in the invoice match exactly with the corresponding sales order.
        - **Offering Type**: Project
        - **Description**: This metric ensures that each invoice's product ID, price, and quantity match exactly with the corresponding sales order line. If there are differences in the product, price, or quantity, it means there may be errors in the booking or invoicing process.
        - **Key Tables**:
            - account_move_line: product_id, price_unit
            - sale_order_line: product_id, price_unit
        - **How it was extracted**: The query compares the invoice line details (product ID and price) from account_move_line with the sales order line details from sale_order_line. Discrepancies are flagged where the product or price in the invoice does not match the corresponding sales order.
        """)

        # Chart: Total Invoices with Label Issues
        fig29 = go.Figure(data=[
            go.Bar(name="Invoices with Label Issues", x=["Invoices with Label Issues"], y=[6811], 
                marker_color="blue", text=[6811], textposition='auto')
        ])
        fig29.update_layout(
            title="Total Invoices with Label Issues",  # Chart title
            xaxis_title="Category",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )

        # Display the chart
        st.plotly_chart(fig29)

        # Latest 15 Invoices with Issues
        st.markdown("### Latest 15 Invoices with Issues")
        data = {
            "invoice_name": [
                "128128", "128129", "128130", "128131", "128132",
                "128133", "128134", "128135", "128136", "128137",
                "128138", "128157", "128163", "128164", "128166"
            ],
            "invoice_date": [
                "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04",
                "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04",
                "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04", "2024-12-04"
            ]
        }

        # Display the table
        df = pd.DataFrame(data)
        st.table(df)

    # Expander for Metric 29
    # Expander for the "Taxes (Project)" Metric
    with st.expander("Metric: Taxes (Project)"):
        # Metric Description
        st.write("""
            - **Metric**: Ensure that taxes are applied correctly based on the customer's status and the jurisdiction of the project.
            - **Offering Type**: Project
            - **How it was determined?**: Taxes are checked based on where the project is being performed (state, county, or city). The project's ZIP code is compared to the tax jurisdiction to ensure accuracy.
            - **Description**: This metric ensures that:
                1. **Tax-exempt customers** are not taxed.
                2. The correct **jurisdiction** is used for tax calculations based on the project's ZIP code.
            - **Key Tables**:
                - **account_move**: Stores invoice details (e.g., customer, date).
                - **res_partner**: Contains customer information, including ZIP codes.
                - **project_project**: Links projects to job site addresses.
                - **account_tax**: Stores tax names and jurisdictions.
            - **How it was extracted**: 
                The query compares the project's ZIP code (from `res_partner`) with the jurisdiction of the applied tax (`account_tax`) in the invoice lines (`account_move_line`). If the tax jurisdiction does not match the ZIP code, or other discrepancies are found, the invoice is flagged.
        """)

        # Insights
        st.write("### Insights")
        st.write("""
        1. **Total Invoices with Tax Issues**: 2,759
        2. **Types of Issues Identified**:
            - **Mismatched Jurisdiction**: Tax jurisdiction does not align with the project's ZIP code.
            - **Unknown Issues**: Other discrepancies in tax calculations.
        """)

        # Charts Section
        st.markdown("### **Charts**")
        
        # Chart: Total Invoices with Tax Issues
        fig30 = go.Figure(data=[
            go.Bar(name="Invoices with Tax Issues", x=["Invoices with Tax Issues"], y=[2759], 
                marker_color="orange", text=[2759], textposition='auto')
        ])
        fig30.update_layout(
            title="Total Invoices with Tax Issues",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )
        st.plotly_chart(fig30)

        # Sample Data
        sample_data = [
            {"Invoice Name": "128164", "Invoice Date": "2024-12-04"},
            {"Invoice Name": "128053", "Invoice Date": "2024-12-03"},
            {"Invoice Name": "128056", "Invoice Date": "2024-12-03"},
            {"Invoice Name": "127843", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127851", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127861", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127865", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127867", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127868", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127870", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127873", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127874", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127879", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127882", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "127883", "Invoice Date": "2024-11-30"},
        ]

        # Convert to DataFrame
        df = pd.DataFrame(sample_data)

        # Display the Table
        st.markdown("### Latest 15 Invoices with Tax Issues")
        st.write("Below is a sample of the latest invoices flagged for tax issues:")
        st.dataframe(df, use_container_width=True)


    # Expander for Metric 32
    with st.expander("Metric : Notes"):
        # Metric Description
        st.write("""
            - **Metric**: Detect and review notes on the project and sales order for any specific billing questions, concerns, or gaps in billing.
            - **Offering Type**: Project
            - **How to determine?**: Reviews notes on projects and sales orders for mentions of billing issues such as pricing errors, missing items, tax problems, or invoice disputes.
            - **Description**: 
                The **Notes** metric identifies invoices associated with projects or sales orders that contain notes mentioning potential billing discrepancies. These could include:
                - Mispriced items
                - Missing products
                - Incorrect taxes
                - General billing concerns

                Currently, this is achieved by searching for specific keywords in the notes (`mail_message` table). 
                In the future, **AI models** will allow us to perform advanced text analysis using Natural Language Processing (NLP), enabling:
                - Better context understanding
                - Sentiment detection
                - Detection of subtle billing issues.
                
            - **Key Tables**:
                - **sale_order**: Links sales orders to projects.
                - **project_task**: Tracks project tasks and descriptions.
                - **mail_message**: Stores notes and communications for sales orders and projects.
                - **account_move**: Tracks invoices and links to sales orders.

            - **How it was extracted?**:
                The query searched for specific keywords such as "billing issue", "pricing error", "missing items", "tax problem", or "invoice dispute" in the `subject` and `body` of notes in the `mail_message` table. These notes are linked to sales orders or projects, and invoices are flagged if such notes are found. **11 invoices** were identified with issues, and only invoices dated after **May 1, 2024** were included.
        """)

        # Insights Section
        st.write("### Insights")
        st.write("""
        1. **Total Invoices with Issues**: 11
        2. **Types of Issues Identified**:
            - Mispriced items
            - Missing products
            - Tax problems
            - Invoice disputes
        """)

        # Chart Section
        st.markdown("### **Charts**")
        fig40 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issues"], y=[11], 
                marker_color="orange", text=[11], textposition='auto')
        ])
        fig40.update_layout(
            title="Total Invoices with Issues",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )
        st.plotly_chart(fig40)

        # Sample Data
        sample_data = [
            {"Invoice Name": "127982", "Invoice Date": "2024-11-30"},
            {"Invoice Name": "126925", "Invoice Date": "2024-11-13"},
            {"Invoice Name": "126677", "Invoice Date": "2024-11-07"},
            {"Invoice Name": "125473", "Invoice Date": "2024-10-18"},
            {"Invoice Name": "125475", "Invoice Date": "2024-10-18"},
            {"Invoice Name": "125476", "Invoice Date": "2024-10-18"},
            {"Invoice Name": "124977", "Invoice Date": "2024-10-10"},
            {"Invoice Name": "124456", "Invoice Date": "2024-09-30"},
            {"Invoice Name": "124483", "Invoice Date": "2024-09-30"},
            {"Invoice Name": "124320", "Invoice Date": "2024-09-27"},
            {"Invoice Name": "123357", "Invoice Date": "2024-09-12"},
        ]

        # Convert to DataFrame
        df = pd.DataFrame(sample_data)

        # Display the Table
        st.markdown("### Latest 11 Invoices with Issues")
        st.write("Below is a sample of the latest invoices flagged for billing concerns:")
        st.dataframe(df, use_container_width=True)


    with st.expander("Metric : Log Notes"):
        # Metric Description
        st.write("""
            - **Metric**: Detect and review log notes related to invoices for any billing issues or concerns.
            - **Offering Type**: Project
            - **How to determine?**: Reviews notes on invoices for any discrepancies or customer feedback.
            - **Description**: 
                The Log Notes metric identifies invoices with relevant log notes mentioning billing discrepancies, customer feedback, or concerns that need attention. These notes could highlight:
                - Incorrect pricing
                - Missing items
                - Tax-related issues
                - Customer queries or disputes about the invoice

                The analysis involves searching for keywords in log notes linked to invoices. In the future, AI tools can enhance the process by using Natural Language Processing (NLP) to understand the context and sentiment of the notes, allowing for more accurate detection of subtle billing issues.
                
            - **Key Tables**:
                - **account_move**: Tracks invoices.
                - **mail_message**: Stores log notes and communication linked to invoices.
            
            - **How it was extracted?**:
                This analysis retrieves log notes from the `mail_message` table where the model is linked to invoices (`account.move`). Keywords such as "billing issue", "pricing error", "missing items", "tax problem", or "invoice dispute" are searched in the `subject` and `body` fields of these notes. Invoices with dates after **May 1, 2024**, and containing these keywords were flagged. A total of **5 invoices** were identified with such issues.
        """)

        # Insights Section
        st.write("### Insights")
        st.write("""
        1. **Total Invoices with Issues**: 5
        2. **Types of Issues Identified**:
            - Billing discrepancies (e.g., incorrect pricing, missing items)
            - Customer disputes or feedback
            - Tax-related concerns
        """)

        # Chart Section
        st.markdown("### **Charts**")
        fig41 = go.Figure(data=[
            go.Bar(name="Invoices with Issues", x=["Invoices with Issues"], y=[5], 
                marker_color="orange", text=[5], textposition='auto')
        ])
        fig41.update_layout(
            title="Total Invoices with Issues",  # Chart title
            xaxis_title="Issue",  # X-axis label
            yaxis_title="No. of Invoices",  # Y-axis label
            barmode="group"  # Bar chart mode
        )
        st.plotly_chart(fig41)

        # Sample Data Table
        sample_data = [
            {"Invoice Name": "126318", "Invoice Date": "2024-10-31"},
            {"Invoice Name": "119745", "Invoice Date": "2024-06-25"},
            {"Invoice Name": "119462", "Invoice Date": "2024-06-20"},
            {"Invoice Name": "113403", "Invoice Date": "2024-01-22"},
        ]

        # Convert to DataFrame
        df = pd.DataFrame(sample_data)

        # Display the Table
        st.markdown("### Latest Invoices with Issues")
        st.write("Below is a sample of invoices flagged based on log notes with potential billing issues:")
        st.dataframe(df, use_container_width=True)



    # # Metric 35: Repairs/Services Validation Expander
    # with st.expander("Metric : Repairs/Services Validation"):
    #     st.markdown("""
    #     ### **Metric Overview**

    #     - **Metric**: Verify pricing, hours billed, and shop supplies used for repairs or services.
    #     - **Offering Type**: Repairs/Services
    #     - **How to Verify**: Check parts, hours billed, and shop supply charges against repair orders and invoices.
    #     - **Key Tables**:
    #         -  repair_order - partner_id, project_id, invoice_method
    #         - repair_line - product_id, product_uom_qty
    #         - product_template - id, list_price
                    
    #     ### **Insights**

    #     - **Total Invoices**: **2908**

    #     ### **Summary**

    #     The analysis confirms that all repairs and services for the year 2024 have been logged and priced accurately. No discrepancies were identified in parts, hours billed, or shop supply charges. This reflects robust data consistency and accurate billing processes.

    #     ### **Metric**
    #     - **Metric**: This metric ensures that **repairs and services** have been accurately priced, and no discrepancies exist in **parts**, **hours billed**, or **shop supplies**.
        
    #     ### **Description**
    #     - The **Repairs/Services Validation** metric involves validating that **pricing**, **hours billed**, and **shop supplies used** for repairs or services align with the corresponding **repair orders** and **invoices**. This ensures that all charges are properly accounted for and that no errors in billing exist for the services rendered.

    #     ### **How it was extracted?**
    #     - The data for this metric was extracted by reviewing the **repair orders** and **repair lines**, and comparing them with the **invoiced items**. This analysis ensures that all billed parts, labor hours, and shop supplies match the corresponding repair order details. The query checks for any discrepancies between the **product pricing** in the **product_template** table and what is actually billed for the repair or service, ensuring accurate invoicing for all repair-related charges.
    #     """)


    #     st.markdown("### **Charts**")

    #     # Chart 1: Total Invoices
    #     fig35 = go.Figure(data=[
    #         go.Bar(name="Invoices Reviewed", x=["Total Invoices"], y=[2908],
    #             marker_color="blue", text=[2908], textposition='auto')
    #     ])
    #     fig35.update_layout(
    #         title="Total Invoices Reviewed",  # Chart title
    #         xaxis_title="Category",  # X-axis label
    #         yaxis_title="No. of Invoices",  # Y-axis label
    #         barmode="group"  # Bar chart mode
    #     )

    #     # Display the chart
    #     st.plotly_chart(fig35)

# Tab 2 Content
# Tab 2 Content: Issues Section with Two Tables
with tabs[1]:
    st.subheader("Section 2: Metrics with Attachment")
    st.write("""
        Below are the tables highlighting the metrics where we lack the necessary attachments for comparison. While the **ir_attachment** table generally stores attachment details in our database, we have found that the relevant fields for these attachments are consistently null in our system. As a result, we are unable to perform the required comparisons for these metrics, and the attachments are crucial to ensure accurate analysis and validation.
        """)


    # First Table: Metrics with Missing Columns
    st.markdown("### Metrics with Missing Attachment")
    missing_columns_data = [
        {"Metric": "Review Pricing", 
        "Description": "Matches Agreement/CO/PO - Sales Order>Project>Document.", 
        "Issue": "we do not have anything related to Agreement or PO"},
        
        {"Metric": "Messenger", 
        "Description": "Reviews pump Activity - Ties back to Sale Order to see if Continuous/Single rate charged - Reviews against Contract/PO/SA - Potential Over/Underbilling", 
        "Issue": "We do not have the PO"},
        
        {"Metric": "BCF", 
        "Description": "Ensure BCF is attached and ties out to what is being billed", 
        "Issue": "we have the information that is BCF attached or not? but we do not have the BCF attachment to compare it with the account_move cost and billing"},
        
        {"Metric": "T&M Biling", 
        "Description": "Reviews to ensure T&M workbook is completed and attached and all billing is accounted for", 
        "Issue": "but we do not have the T&M workbook attachment to compare it with the account_move cost and billing."},

        # {"Metric": "Daily Worksheets",
        # "Description": "Ensure that customer approval and daily work records are properly aligned with billed labor hours. This involves comparing the labor days recorded in timecards with the corresponding reports or worksheets completed for the project.",
        # "Issue": "Discrepancies in labor hours billed versus the daily work records or customer-approved worksheets."
        # }


        # {"Metric": "Notes", 
        # "Description": "Reviews notes on project - SO>Project for any specific billing questions/concerns/gaps in billing.", 
        # "Issue": "We do not have the notes attachment."},

        # {"Metric": "Log Notes", 
        # "Description": "Reviews notes on invoice.", 
        # "Issue": "We have checked that we do not have any log notes where anything metioned about issue or invoice. We do not have the log notes attachment, if there it is."},
        
        # {"Metric": "Daily Worksheets", 
        # "Description": "Obtains customer approval/daily work support for what has been completed to be able to be billed - Compare Labor days to reports completed.", 
        # "Issue": "We do not have the Daily Worksheets attachments files to review."}
    ]

    missing_columns_df = pd.DataFrame(missing_columns_data)
    st.dataframe(missing_columns_df)


# with tabs[2]:
#     st.subheader("Section 3: Metrics That Need Clarification")
#     st.write("Below are the tables outlining the metrics with identified issues, their descriptions, and the challenges we are encountering.")

#     # First Table: Metrics with Missing Columns
#     # st.markdown("### Metrics with Missing Columns")
#     missing_columns_data = missing_columns_data = [
#         # {"Metric": "26 - Product", 
#         # "Description": "Review the product code used on the Sales Order to ensure that the correct code is applied. An incorrect code could result in the product being booked to the wrong Profit and Loss (PL) or Balance Sheet (BS) account.",
#         # "Issue": "What exactly are we tracking in this metric? Are we verifying that the correct product code is associated with the appropriate Profit and Loss (PL) and Balance Sheet (BS) accounts on both the Sales Order and the Invoice? Additionally, should we also check for any pricing discrepancies that could affect the Profit and Loss accounts?" 
#         # },
#         # {"Metric": "27 - Analytics", 
#         # "Description": "Verify that the correct Branch, Vertical, and Job Analytics are applied to the Sales Order. This helps mitigate the risk of incorrectly booking expenses to the wrong branch, project type, or job.",
#         # "Issue": "Does this metric overlap with the Branch Difference metric, where we verify if the branch on the invoice matches the one on the Sales Order? Additionally, should we compare the Vertical and Job Analytics between the Sales Order and invoice? What exactly does the term 'Vertical' refer to in this context?"
#         # }
#     ]

#     missing_columns_df = pd.DataFrame(missing_columns_data)
#     st.dataframe(missing_columns_df)

# Footer Section
st.markdown("""
---
**By:** **Sutra.AI**
""")