প্ল্যান-কোড-এক্সিকিউট: ডিজাইনিং এজেন্ট যারা ডেটা সায়েন্সের দিকে তাদের নিজস্ব টুল তৈরি করে

import json
from ..llm.client import GeminiClient

class Planner:
    def __init__(self):
        self.llm = GeminiClient()

    def create_plan(self, user_prompt, dataset_context=""):
        """Decomposes user prompt into execution steps."""
        system_prompt = f"""
        You are a Senior Project Manager for a Data Science team.
        Your goal is to break down a high-level user request into a list of specific, executable steps.
        
        --- DATASET AVAILABLE (Use EXACT Filenames) ---
        {dataset_context}
        -----------------------------------------------
        
        --- DATASET GUIDELINES ---
        - The dataset contains both 'Raw Data' (Historical) and 'Model Results' (Predictions/Anomalies).
        - CAREFULLY infer which source is needed based on the user's goal. 
        - Example: "Sales Analysis" -> Raw Data. "Forecast Accuracy" -> Model Results.
        
        Output format: JSON list of objects with fields:
        - step_id: int
        - name: str (short title)
        - description: str (detailed instruction for the coder)
        - type: "CODE" (if it requires writing a script) or "TEXT" (if just a summary)
        
        --- SCOPE RESTRICTION ---
        - DO NOT USE Machine Learning (LinearRegression, Training, Random Forest).
        - USE ONLY Statistical Analysis (Mean, Median, Std Dev, Z-Score, Rolling Averages).
        - FOCUS on Data Visualization (Line plots, Histograms).
        - If the user asks for "forecast" or "accuracy", PREFER WAPE (Weighted MAPE) over standard MAPE.
        
        Example:
        User: "Analyze SKU001"
        Output:
        [
            {{"step_id": 1, "name": "Load Data", "description": "Load Nodes.csv and filter for SKU001 to get basic info.", "type": "CODE"}},
            {{"step_id": 2, "name": "Temporal Analysis", "description": "Load Production.csv and Sales.csv. Calculate monthly volume and trend for SKU001.", "type": "CODE"}}
        ]
        """
        
        full_prompt = f"{system_prompt}\n\nUser Request: {user_prompt}\nJSON Plan:"
        response = self.llm.generate_content(full_prompt)
        
        # basic cleanup for JSON markdown blocks
        clean_response = response.replace("```json", "").replace("```", "").strip()
        print(f"\n[DEBUG] Raw Planner Response:\n{response}\n") # Added for debugging
        try:
            return json.loads(clean_response)
        except json.JSONDecodeError:
            print(f"Failed to parse plan: {response}")
            return []

[
    {
        "step_id": 1,
        "name": "Load Node and Edge Data",
        "description": "Load 'Nodes.csv' to confirm 'SOS001L12P' as a valid node. Load all edge datasets: 'Edges (Plant).csv', 'Edges (Product Group).csv', 'Edges (Product Sub-Group).csv', and 'Edges (Storage Location).csv'. These files contain the structural information needed for spatial analysis.",
        "type": "CODE"
    },
    {
        "step_id": 2,
        "name": "Extract Direct Neighbors and Edge Attributes",
        "description": "Iterate through each loaded edge dataset. For each dataset, filter rows where 'SOS001L12P' is present in either the 'node1' or 'node2' column. For each matching record, identify the neighboring node and extract the specific edge attribute that defines the relationship (e.g., 'Plant' from 'Edges (Plant).csv', 'GroupCode' from 'Edges (Product Group).csv', 'SubGroupCode' from 'Edges (Product Sub-Group).csv', 'Storage Location' from 'Edges (Storage Location).csv'). Consolidate all unique neighbors and their corresponding labeled relationships into a single structure.",
        "type": "CODE"
    },
    {
        "step_id": 3,
        "name": "Prepare Graph for Visualization",
        "description": "Construct a graph object (e.g., using NetworkX) with 'SOS001L12P' as the central node. Add all identified direct neighbors as nodes and create edges between 'SOS001L12P' and each neighbor. Assign the extracted edge attributes (e.g., 'Plant: Plant A', 'GroupCode: GRP1') as labels to these edges for clarity in the visualization.",
        "type": "CODE"
    },
    {
        "step_id": 4,
        "name": "Visualize Graph Edges with Labels",
        "description": "Generate a clear graph visualization of the spatial structure around 'SOS001L12P'. The plot should display 'SOS001L12P' prominently in the center, with its direct neighbors connected by edges. Each edge must be explicitly labeled with the type of relationship and its specific attribute value (e.g., 'Plant: P001', 'Product Group: PG002'). Ensure appropriate layout and styling for readability.",
        "type": "CODE"
    },
    {
        "step_id": 5,
        "name": "Summarize Spatial Connectivity",
        "description": "Provide a textual summary of the spatial structure around 'SOS001L12P'. Describe the types of entities it is connected to (Plants, Product Groups, Product Sub-Groups, Storage Locations), list the specific neighbors identified, and briefly explain the nature of these connections based on the edge labels.",
        "type": "TEXT"
    }
]

A thorough analysis of the spatial structure around node `SOS001L12P` has been completed.

**Analysis Findings:**
1.  **Node Validation:** The node `SOS001L12P` was confirmed as a valid node within the master data.
2.  **Neighbor Relationships:** The analysis identified 39 distinct relationships connecting `SOS001L12P` to 39 unique neighboring nodes. These relationships are categorized by `RelationshipType` and `RelationshipValue`:
    *   **Plant:** `SOS001L12P` is connected to multiple neighbors via various 'Plant' codes (e.g., 2103, 2111, 2112, 2114, 2115, 2116, 2117, 2118, 2119, 2120, 2121, 2122).
    *   **GroupCode:** `SOS001L12P` shares 'GroupCode' 'S' with neighbors like `SOS008L02P`, `SOS005L04P`, `SOS003L04P`, `SOS002L09P`, `SOS500M24P`, `SOS250M48P`, `SOP001L12P`, `SO0005L04P`, `SO0002L09P`, `SO0001L12P`, `SO0500M24P`, `SE500G24P`, and `SE200G24P`.
    *   **SubGroupCode:** `SOS001L12P` is linked by 'SubGroupCode' 'SOS' to nodes such as `SOS008L02P`, `SOS005L04P`, `SOS003L04P`, `SOS002L09P`, `SOS500M24P`, and `SOS250M48P`.
    *   **Storage Location:** `SOS001L12P` is associated with its neighbors through various 'Storage Location' codes (e.g., 330.0, 1130.0, 1230.0, 1430.0, 1530.0, 1630.0, 1730.0, 1830.0, 1930.0, 2030.0, 2130.0, 2230.0).

The full list of neighbors and their specific relationships can be found in `C:/Auto-agent/workspace/output\SOS001L12P_neighbors_relationships.csv`.

**Visualization:**
A graph visualization has been generated, centering on `SOS001L12P` and displaying all its direct neighbors and their connecting edges with labels indicating the `RelationshipType` and `RelationshipValue`. The resulting graph contains 40 nodes (including `SOS001L12P`) and 39 edges.

The visualization is available at: `C:/Auto-agent/workspace/output\SOS001L12P_neighbor_graph.png`

[
    {
        "step_id": 1,
        "name": "Load Sales Order Data for SOS001L12P",
        "description": "Load the 'Sales Order.csv' file from C:\\AnomalyGNN\\dataset\\Raw Dataset\\Temporal Data\\Unit\\Sales Order.csv. Focus on the 'Date' and 'SOS001L12P' columns, treating 'SOS001L12P' as the sales volume.",
        "type": "CODE"
    },
    {
        "step_id": 2,
        "name": "Preprocess Temporal Data",
        "description": "Convert the 'Date' column to datetime objects and set it as the DataFrame index. Ensure the sales volume column (SOS001L12P) is numerical. Handle any missing values, potentially by filling with 0 or forward/backward fill if appropriate for sales data (specify chosen method).",
        "type": "CODE"
    },
    {
        "step_id": 3,
        "name": "Analyze Sales Volume Trends",
        "description": "Calculate 7-day and 30-day rolling averages for the 'SOS001L12P' sales volume. These will help identify short-term and long-term trends in the sales signal. Store these as new columns in the DataFrame.",
        "type": "CODE"
    },
    {
        "step_id": 4,
        "name": "Detect Outliers using Z-Score",
        "description": "Calculate the Z-score for the 'SOS001L12P' sales volume. Identify potential outliers by marking data points where the absolute Z-score exceeds a predefined threshold (e.g., 2 or 3 standard deviations). Store the Z-score and an 'Is_Outlier' boolean column.",
        "type": "CODE"
    },
    {
        "step_id": 5,
        "name": "Visualize Sales Order Temporal Signal",
        "description": "Create a time-series line plot showing the 'SOS001L12P' sales volume over time. Overlay the 7-day and 30-day rolling averages. Highlight the identified outliers on the plot to visually represent abnormalities in volume trends.",
        "type": "CODE"
    },
    {
        "step_id": 6,
        "name": "Summarize Trends and Abnormalities",
        "description": "Provide a textual summary of the observed trends (e.g., seasonality, growth/decline), the frequency and magnitude of identified outliers, and any other notable abnormalities or shifts in the sales volume patterns for SOS001L12P. This summary should interpret the visualizations and statistical measures.",
        "type": "TEXT"
    }
]

**Analysis of SOS001L12P Sales Order Temporal Signal**

This analysis covers sales data for product SOS001L12P from January 1, 2023, to August 9, 2023.

**1. Volume Trend During the Duration:**
The sales volume for SOS001L12P shows a clear and persistent declining trend throughout the analyzed period.
*   In early January 2023, daily sales volumes were high, with individual days reaching up to 21,394 units. The 30-day rolling average was approximately 12,629 by January 10th.
*   By early August 2023, the sales volume had significantly decreased. The 30-day rolling average plummeted to approximately 5,165 by August 9th, indicating a substantial drop in consistent sales activity over the eight-month period.

**2. Persistent Declines and Other Issues:**
*   **Persistent Decline:** The consistent decrease in both daily sales and the rolling averages (7-day and 30-day) from January to August clearly indicates a persistent decline in sales volume for SOS001L12P.
*   **Anomalies:** The analysis identified 14 specific days classified as anomalies. These were characterized by unusually low sales volumes, either due to a Z-score below -2.0 or three consecutive days of negative percentage change. Notable severe drops include:
    *   January 4, 2023: Sales dropped to 10,920.08.
    *   March 8, 2023: Sales recorded an exceptionally low value of 200.00.
*   **Zero Sales:** Towards the end of the analyzed period, there were instances of zero sales recorded (e.g., August 8th and 9th), further exacerbating the declining trend and highlighting critical issues in sales performance.

[
    {
        "step_id": 1,
        "name": "Load Production Data for SOS001L12P",
        "description": "Load the 'Production .csv' file from 'C:\\AnomalyGNN\\dataset\\Raw Dataset\\Temporal Data\\Unit\\' into a pandas DataFrame. Select only the 'Date' and 'SOS001L12P' columns for analysis.",
        "type": "CODE"
    },
    {
        "step_id": 2,
        "name": "Clean and Prepare Production Data",
        "description": "Convert the 'Date' column to datetime objects and set it as the DataFrame index. Ensure the 'SOS001L12P' column is numeric. Handle any missing values in 'SOS001L12P' by filling with 0, assuming missing production data means zero units produced.",
        "type": "CODE"
    },
    {
        "step_id": 3,
        "name": "Calculate Rolling Averages for Trend",
        "description": "Compute a 7-day and a 30-day rolling average for the 'SOS001L12P' production volume. These rolling averages will help to visualize and identify short-term and long-term trends in the data.",
        "type": "CODE"
    },
    {
        "step_id": 4,
        "name": "Visualize Production Volume and Trends",
        "description": "Create a time series line plot showing the daily 'SOS001L12P' production volume. Overlay the 7-day and 30-day rolling averages on the same plot to visually represent the trend. Ensure proper labeling for axes and a clear title.",
        "type": "CODE"
    },
    {
        "step_id": 5,
        "name": "Detect Outliers using Z-Score",
        "description": "Calculate the Z-score for each data point in the 'SOS001L12P' production volume. Identify potential outliers by flagging data points where the absolute Z-score exceeds a predefined threshold (e.g., |Z-score| > 2.5 or 3). Store the dates and values of these outliers.",
        "type": "CODE"
    },
    {
        "step_id": 6,
        "name": "Visualize Outliers and Abnormalities",
        "description": "Generate a time series line plot of the 'SOS001L12P' production volume. On this plot, distinctly mark or color the data points that were identified as outliers in the previous step to highlight abnormalities in volume trends.",
        "type": "CODE"
    },
    {
        "step_id": 7,
        "name": "Summarize Trend, Outliers, and Abnormalities",
        "description": "Provide a textual summary of the observed overall trend (e.g., increasing, decreasing, stable, seasonal), describe the characteristics of the identified outliers (e.g., magnitude, frequency, specific dates), and comment on any notable abnormalities in the production volume patterns based on the visualizations and statistical analysis.",
        "type": "TEXT"
    }
]

Here's an analysis of the production temporal signal for SOS001L12P:

**1. Data Overview:**
The production data for SOS001L12P was successfully loaded and preprocessed, covering 221 days from January 1, 2023, to August 9, 2023. The 'Date' column was correctly parsed, and the 'SOS001L12P' production volume column was confirmed to be numeric with no missing values.

**2. Trend Analysis:**
*   **Rolling Averages:** 7-day and 30-day rolling averages were computed to smooth out daily fluctuations and highlight underlying trends.
*   The plot `SOS001L12P_production_trends.png` (saved to `C://Auto-agent/workspace/output`) visually represents the daily production volume alongside these rolling averages, allowing for observation of the overall temporal trend.
*   Looking at the tail of the data, the 7-day and 30-day rolling averages show a general decline in production volume towards the end of the observed period (early August), with the 30-day average dropping from 5582.97 on August 7th to 5075.73 on August 9th, and the 7-day average dropping from 3724.71 to 2911.00 in the same period.

**3. Outliers (Z-score Method):**
*   Using a Z-score threshold of |Z-score| > 2.5, **no potential outliers were identified** in the SOS001L12P production data. This suggests that while there might be variations, no individual data points significantly deviated from the mean (7480.90) by more than 2.5 standard deviations (4729.55).

**4. Abnormalities in Volume Trends:**
*   Despite the lack of Z-score outliers, a more sophisticated anomaly detection method **identified 17 anomalous data points** in the SOS001L12P production.
*   These anomalies are visualized in the plot `SOS001L12P_production_with_anomalies_plot.png` (saved to `C://Auto-agent/workspace/output`), which would highlight specific dates where production volume exhibited unusual behavior compared to its typical pattern. A significant drop to 0 on August 9th, for instance, is likely to be one such anomaly, suggesting a halt or severe reduction in production.

[
    {
        "step_id": 1,
        "name": "Load and Consolidate SOS001L12P Data",
        "description": "Load the following datasets for SOS001L12P: \n1. Production rolling averages: C:\\Auto-agent\\workspace\\output\\production analysis - 2\\production_rolling_averages_SOS001L12P.csv (columns: Date, SOS001L12P, SOS001L12P_7_day_avg, SOS001L12P_30_day_avg)\n2. Production Z-Scores: C:\\Auto-agent\\workspace\\output\\production analysis - 2\\SOS001L12P_production_z_scores.csv (columns: Date, Actual_Production, Z_Score)\n3. Anomaly detection results: C:\\AnomalyGNN\\results\\top_sku_SOS001L12P_predictions.csv (columns: Date, Is_Anomaly)\n4. Demand vs. Production Forecast: C:\\GNN\\results\\demand_vs_production_SOS001L12P.csv (columns: Date, Predicted_Demand, Actual_Sales, Actual_Production)\n\nMerge all these dataframes on the 'Date' column, ensuring 'Date' is converted to a datetime format for accurate time-series analysis.",
        "type": "CODE"
    },
    {
        "step_id": 2,
        "name": "Analyze and Visualize Production Trends and Anomalies",
        "description": "Create a time-series line plot showing the 'Actual_Production' for SOS001L12P, overlaid with 'SOS001L12P_7_day_avg' and 'SOS001L12P_30_day_avg'. Highlight points where 'Is_Anomaly' is True (from top_sku_SOS001L12P_predictions.csv) or where 'Z_Score' exceeds a certain threshold (e.g., |Z_Score| > 2 or 3) to visually confirm the user's observation of a downward trend and anomalies in the second half of the duration. Identify the start date of the 'second half' based on the data's time range.",
        "type": "CODE"
    },
    {
        "step_id": 3,
        "name": "Correlate Production, Sales, and Demand Forecast",
        "description": "Generate a single time-series line plot comparing 'Actual_Production', 'Actual_Sales', and 'Predicted_Demand' for SOS001L12P. Analyze the visual correlation and note periods of significant divergence or alignment, especially during the identified 'second half' where production anomalies and downward trends were observed.",
        "type": "CODE"
    },
    {
        "step_id": 4,
        "name": "Evaluate Forecasting Model Performance (WAPE)",
        "description": "Calculate the Weighted Average Percentage Error (WAPE) for the 'Predicted_Demand' against 'Actual_Sales' for SOS001L12P using the consolidated data. Calculate WAPE for the entire duration and separately for the 'second half' of the duration (as identified in Step 2) to assess how the model performed during the period of observed production issues. Present the WAPE values and interpret their meaning regarding forecast accuracy.",
        "type": "CODE"
    },
    {
        "step_id": 5,
        "name": "Generate Comprehensive End-to-End Report for SOS001L12P",
        "description": "Provide a narrative report summarizing all findings. \n1. Confirm the observed downward trend in production for SOS001L12P during the second half of the duration, citing specific dates or periods. \n2. Validate the user's observation of many anomalies, providing a count or highlighting key anomaly dates. \n3. Discuss the correlation between actual production, actual sales, and predicted demand, explaining how they interact, especially during the problematic second half. \n4. Comment on the forecasting model's performance based on the calculated WAPE values, noting any significant differences in accuracy between the overall duration and the 'second half'. \n5. Offer insights into potential reasons for observed trends or anomalies and their impact on supply chain planning.",
        "type": "TEXT"
    }
]

Here is an end-to-end report for SKU SOS001L12P, considering your observations and the recent analyses.

---

### **End-to-End Report: SKU SOS001L12P Analysis (2023-01-01 to 2023-08-09)**

**1. Overview**
This report synthesizes recent production analysis, anomaly detection, and demand forecasting data for SKU SOS001L12P, covering the period from January 1, 2023, to August 9, 2023. The analysis specifically focuses on trends and model performance during the second half of this duration, starting approximately April 21, 2023.

**2. Production Trends and Anomalies**
The analysis confirms your observation regarding production trends in the second half of the duration (from 2023-04-21).

*   **Downward Trend and Volatility**: While the time series generally shows a positive visual correlation between Actual Production, Actual Sales, and Predicted Demand, Actual Production appears to be more volatile and exhibits sharper drops compared to Actual Sales and Predicted Demand in certain segments of the second half.
*   **Production vs. Sales**: In the second half, Actual Production (average 4866 units) was notably lower than Actual Sales (average 5136 units).
*   **Anomalies**: A total of 17 combined anomaly points (based on 'Is_Anomaly=True' or |Z_Score| > 2.5) were identified across the entire dataset. Several of these production anomalies were detected in the second half, specifically on dates such as 2023-04-21, 2023-05-05, 2023-05-21, 2023-05-22, and 2023-05-23. These anomaly periods often coincide with significant divergences where Actual Production deviates sharply from Actual Sales and Predicted Demand.

**3. Correlation: Production, Demand Forecast, and Sales**
The correlation analysis in the second half of the duration (from 2023-04-21) reveals distinct relationships:

*   **Strong Sales-Demand Correlation**: Actual Sales and Predicted Demand show a strong positive correlation of **0.85**. This suggests the forecasting model is generally effective in tracking actual sales patterns.
*   **Weak Production Correlation**: Actual Production's correlation with both Actual Sales and Predicted Demand is significantly weaker:
    *   Actual Production vs. Actual Sales: **0.35**
    *   Actual Production vs. Predicted Demand: **0.38**
*   **Divergence**: Despite a general visual correlation across the entire period, the second half exhibits noticeable periods where Actual Production diverges from Actual Sales and Predicted Demand. This is particularly evident during the observed anomalies, indicating that production output did not consistently align with market demand or sales in these instances.

**4. Demand Forecasting Model Performance**
The forecasting model's performance was assessed using the Weighted Average Percentage Error (WAPE):

*   **Overall Performance**: The overall WAPE for SOS001L12P across the entire duration is **35.44%**.
*   **Second Half Performance**: For the 'second half' of the duration (from 2023-04-21), the WAPE is **29.70%**.
*   **Interpretation**: The WAPE in the second half is lower than the overall WAPE. This indicates that, despite the observed production issues and anomalies during this period, the demand forecasting model's accuracy actually **improved** in the second half. The model's Predicted Demand consistently tracks Actual Sales closely, suggesting it is reasonably effective in capturing demand patterns for planning purposes.

**Conclusion**
For SKU SOS001L12P, the second half of the analyzed duration (April 21, 2023 - August 9, 2023) was characterized by increased volatility and a downward trend in Actual Production, accompanied by numerous anomalies. Actual Production during this period was also lower than Actual Sales. While the demand forecasting model effectively captured Actual Sales trends (WAPE of 29.70% in the second half, showing an improvement from the overall 35.44%), production struggled to align with these demands, leading to weak correlations between production and both sales and predicted demand. These divergences, often coinciding with identified production anomalies, suggest potential supply constraints or operational disruptions impacting our ability to meet demand consistently.