# Prompt 1

I would like to build a Streamlit app to optimize one of the internal business processes.

At the moment, the Publishing team receives an XLSX file (see attached) from an external partner.
An engineer then uses an ad-hoc code to:
1. Read the file (see the XLSX attached)
2. Convert EDT timestamps in the MM/DD/YYYY format to UTC
3. Generate an output CSV file (see the CSV file attached)
4. Upload the resulting CSV into the Jenkins job (see Jenkinsfile attached) which runs a Typescript script (see .ts file attached) that makes an API call to the graphql-publishing service for each row in the file.

I would like to replace the above steps with a Streamlit app.
This is how it should work:
1. The user uploads the XLSX file
2. The app validates each row and converts the timestamps to UTC
    - For each row, validates that 
      - Pub Song ID is a valid number 
      - Timestamp is a valid datetime in EST
    - In case there's an error with a row, the app:
      - shows rows with errors
      - lets user decide whether to:
        - ignore rows with error
        - start from scratch with a new file
3. For each row, makes an HTTP API call to use updatePublishingCompositionsDeliveryDate mutation on graphql-publishing in QA (see attached delivery-updater.ts for details).
   - The app should implement a parallel upload with async coroutines because the mutation only supports 1 Pub Song ID at a time
4. After the upload into QA, the app should show the upload stats:
   - number of songs updated
   - number of rows with errors
5. When there were NO errors in the QA upload, the user is asked to click the CONFIRM button, which will trigger the same process as in step 3 (graphql-publishing API calls) but for PROD.
6. After the upload into PROD, the app should show the upload stats:
   - number of songs updated
   - number of rows with errors (if any)

A few notes on the engineering requirements:
- the Streamlit app should be written in Python
- the Streamlit app should use uv as a package manager
- A Dockerfile should be generated to let deploy this app on AWS ECS Fargate as a service behind a load balancer.
- all variables in the app should be typed as much as possible
- the code should adhere to common Python code style best practices. It should only rely on the source files to copy the logic, not the style.

Important! As an output, please ONLY produce a PLAN.md file with the detailed plan of the project that Claude Code could pick up. Please include the references to the files as they will also be available to Claude Code.
Do not produce any Python code as a part of this query, your task is only to produce a detailed plan of actions.

## Gemini response

# PLAN.md

## 1\. Project Overview

The goal of this project is to build a Streamlit application to replace a manual, multi-step business process. The application will allow the Publishing team to upload an XLSX file, validate its content, convert timestamps, and trigger a series of API calls to update song delivery dates in both QA and Production environments through a user-friendly web interface.

This plan outlines the necessary steps and structure for a developer to implement the application.

-----

## 2\. Proposed Project Structure

A clean and modular structure will make the application easier to develop, test, and maintain.

```
.
├── .env                # For storing environment variables locally
├── .gitignore          # To exclude virtual environments, caches, etc.
├── Dockerfile          # For containerizing the application for deployment
├── pyproject.toml      # Project metadata and dependencies managed by uv
└── src/
    ├── __init__.py
    ├── app.py          # Main Streamlit application logic and UI
    ├── data_processing.py  # Functions for file reading, validation, and transformation
    └── graphql_client.py   # Functions for interacting with the GraphQL API
```

-----

## 3\. Dependencies and Environment Setup

The project will use **uv** as the package manager.

1.  **Initialize the Environment**:

    ```bash
    # Install uv if you haven't already
    pip install uv

    # Create a virtual environment
    uv venv
    source .venv/bin/activate
    ```

2.  **`pyproject.toml`**: Create this file with the following dependencies.

    ```toml
    [project]
    name = "publishing-delivery-updater"
    version = "0.1.0"
    requires-python = ">=3.9"
    dependencies = [
        "streamlit",
        "pandas",
        "openpyxl", # Required by pandas to read .xlsx files
        "pytz",
        "httpx",
        "python-dotenv",
        "aiohttp", # For async GraphQL client
    ]
    ```

3.  **Install Dependencies**:

    ```bash
    uv pip install -e .
    ```

-----

## 4\. Development Steps

### Step 1: Basic Streamlit App Layout (`src/app.py`)

  - Create the main application file `src/app.py`.
  - Set up the basic UI structure with titles and placeholders for different sections:
      - File uploader widget.
      - An area to display validation errors.
      - Buttons for controlling the workflow (e.g., "Process File", "Proceed to QA", "Confirm for PROD").
      - Placeholders for displaying statistics after API calls.
  - Use `st.session_state` to manage the state of the application throughout the user's journey (e.g., storing the uploaded data, validation results, etc.).

### Step 2: File Upload and Data Processing (`src/data_processing.py`)

  - **`read_and_prepare_data(uploaded_file)` function**:
      - Accepts the uploaded file object from Streamlit's `st.file_uploader`.
      - Uses `pandas.read_excel()` to read the data into a DataFrame.
      - The columns are `Pub Song ID` and `timestamp` as seen in `Orchard Publishing_Product_Song_Submission_20250819.xlsx - Sheet1.csv`.
  - **`validate_and_transform_row(row)` function**:
      - This function will process a single row (a pandas Series).
      - **Validation**:
          - Check if `Pub Song ID` is a valid integer.
          - Check if `timestamp` is a valid datetime string. Use a `try-except` block with `pd.to_datetime`.
      - **Timestamp Conversion**:
          - The input timezone is specified as EST/EDT. The most reliable way to handle this is to assume the timezone is `America/New_York`, which correctly handles both EST and EDT.
          - Use the `pytz` library to localize the timestamp to `America/New_York` and then convert it to UTC.
          - Format the final UTC timestamp as `MM/DD/YYYY HH:mm` as seen in `Orchard Publishing_Product_Song_Submission_20250819.csv`.
  - **`process_dataframe(df)` function**:
      - Iterates through the input DataFrame.
      - Applies the `validate_and_transform_row` function to each row.
      - Separates the rows into two DataFrames: `valid_rows` and `invalid_rows`.
      - Returns both DataFrames.

### Step 3: GraphQL Client (`src/graphql_client.py`)

This module will handle all communication with the `graphql-publishing` service. The logic should be adapted from `delivery-updater.ts`.

  - **Configuration**:
      - Store QA and PROD GraphQL URLs in a `.env` file and load them as environment variables.
      - `QA_GRAPHQL_URL=...`
      - `PROD_GRAPHQL_URL=...`
      - Store required headers (e.g., `Orchard-Profile-Id`, `Orchard-Identity-Id`) in the `.env` file as well. Reference the `HEADERS` constant in `delivery-updater.ts` and the parameters in `Jenkinsfile`.
  - **Define the GraphQL Mutation**:
      - Define the `updatePublishingCompositionsDeliveryDate` mutation as a Python string. This can be extracted directly from the `gql` tag in `delivery-updater.ts`.
  - **`update_delivery_date(session, url, headers, pub_song_id, delivery_date)` async function**:
      - This function will be an `async` coroutine.
      - It takes an `aiohttp.ClientSession`, API URL, headers, and the song data as arguments.
      - It constructs the request variables (payload) for the mutation.
      - It makes a single `POST` request to the GraphQL endpoint.
      - It should include error handling to catch API errors (e.g., HTTP status codes, GraphQL errors in the response body) and return a status (success/failure) and any error message.

### Step 4: Integrate Logic and UI Workflow (`src/app.py`)

1.  **File Upload**:
      - Use `st.file_uploader("Upload XLSX file", type=["xlsx"])`.
      - Once a file is uploaded, store it in `st.session_state` and trigger the processing step.
2.  **Validation Step**:
      - Call `data_processing.process_dataframe()` on the uploaded data.
      - Store `valid_rows` and `invalid_rows` in `st.session_state`.
      - If `invalid_rows` is not empty:
          - Display the `invalid_rows` DataFrame using `st.dataframe`.
          - Show two buttons:
              - `st.button("Ignore errors and proceed with valid rows")`
              - `st.button("Start over with a new file")`
      - If there are no invalid rows, automatically proceed to the QA upload step.
3.  **QA Upload Step**:
      - Add a button "Upload to QA".
      - When clicked, initiate the parallel upload process:
          - Create a list of async tasks by calling `graphql_client.update_delivery_date` for each row in the `valid_rows` DataFrame.
          - Use `asyncio.gather` to execute these tasks concurrently.
          - Display a progress bar (`st.progress`) that updates as tasks are completed.
4.  **QA Statistics**:
      - After the upload finishes, collect the results.
      - Display the statistics using `st.metric`:
          - Number of songs successfully updated.
          - Number of rows with errors.
      - If there were any errors, display the specific rows and the error messages returned by the API.
5.  **Production Confirmation and Upload**:
      - If and only if the QA upload had zero errors, display a confirmation button: `st.button("CONFIRM and Upload to PROD")`.
      - Use `st.warning` to inform the user that this action is irreversible and will affect the production environment.
      - When the button is clicked, repeat the same async upload process, but this time passing the PROD GraphQL URL and credentials to the `graphql_client`.
6.  **PROD Statistics**:
      - After the PROD upload is complete, display the final success and error counts, similar to the QA statistics step.

-----

## 5\. Dockerfile for Deployment

Create a `Dockerfile` to containerize the Streamlit app for deployment on AWS ECS Fargate.

```dockerfile
# Use an official Python runtime as a parent image
FROM python:3.11-slim

# Install uv
RUN pip install uv

# Set the working directory in the container
WORKDIR /app

# Copy the project files into the container
COPY pyproject.toml .

# Install project dependencies
RUN uv pip install --system --no-cache -e .

# Copy the application source code
COPY ./src ./src

# Expose the port Streamlit runs on
EXPOSE 8501

# Set health check for the load balancer
HEALTHCHECK CMD streamlit hello

# Command to run the application
CMD ["streamlit", "run", "src/app.py", "--server.port=8501", "--server.address=0.0.0.0"]
```

-----

## 6\. Configuration and Best Practices

  - **Typed Variables**: Use Python's type hinting for all function signatures and variables to improve code clarity and maintainability.
  - **Error Handling**: Implement robust error handling for file parsing, data validation, and API interactions. Provide clear, user-friendly error messages in the UI.
  - **Environment Variables**: All sensitive information (API URLs, credentials, headers) must be managed via environment variables and **never** hard-coded. Use a `.env` file for local development and configure environment variables directly in the ECS Task Definition for deployment.
  - **Code Style**: Adhere to PEP 8 and use a consistent code formatter like Black.
  - **User Feedback**: Provide continuous feedback to the user, such as spinners during processing, progress bars during uploads, and clear status messages (`st.success`, `st.error`, `st.info`).