# Publishing Delivery Recorder

A Streamlit application for updating delivery dates in The Orchard's Publishing system. This app replaces the manual process of running the TypeScript delivery updater script with a user-friendly web interface.

## Features

- **File Upload**: Upload XLSX or CSV files containing publishing delivery data
- **Data Validation**: Automatic validation of Pub Song IDs and timestamps
- **Multi-Environment**: Support for both QA and Production environments
- **Progress Tracking**: Real-time progress bars and status updates
- **Error Handling**: Detailed error reporting and recovery options
- **Batch Processing**: Efficient processing in configurable chunks

## Project Structure

```
.
├── .env.example             # Environment variables template
├── .gitignore              # Git ignore rules
├── Dockerfile              # Container configuration
├── entrypoint.sh           # Docker entrypoint script
├── pyproject.toml          # Project dependencies
├── README.md               # This file
├── secrets.toml.example    # Auth0 configuration template
├── src/
│   ├── __init__.py
│   ├── allowed_auth0_users.csv  # User authorization list
│   ├── app.py                   # Main Streamlit application
│   ├── auth_authorization.py    # User authorization module
│   ├── data_processing.py       # Data validation and transformation
│   ├── graphql_client.py        # GraphQL API client
│   └── users.csv                # Orchard identity selection
└── tests/
    ├── test_auth_authorization.py
    ├── test_data_processing.py
    └── test_graphql_client.py
```

## Setup

### 1. Environment Setup

Create a `.env` file from the template:

```bash
cp .env.example .env
```

Edit the `.env` file with your configuration:

```env
# GraphQL API URLs
QA_GRAPHQL_URL=https://qa-graphql-router.theorchard.io/graphql
PROD_GRAPHQL_URL=https://prod-graphql-router.theorchard.io/graphql

# Authentication headers
ORCHARD_PROFILE_ID=your_profile_id
ORCHARD_PROFILE_TYPE=PublishingProfile
ORCHARD_IDENTITY_ID=your_identity_id

# Processing configuration (optional)
CHUNK_SIZE=50
CHUNK_DELAY_MS=10
```

### 2. Local Development

If you don't have uv package manager installed, install it according to the [uv documentation](https://docs.astral.sh/uv/getting-started/installation/).

Then, install dependencies using uv:

```bash
# Create virtual environment
uv venv
source .venv/bin/activate  # On Windows: .venv\Scripts\activate

# Install dependencies
uv sync
```

Run the application:

```bash
make start
```

The app will be available at http://localhost:8501

### 3. Testing

Run the test suite to ensure everything is working correctly:

```bash
make test
```

The tests cover:
- **Data Processing**: Timestamp conversion, data validation, CSV/Excel parsing
- **GraphQL Client**: Configuration validation, client initialization, method signatures
- **Edge Cases**: Invalid data handling, timezone conversions, error scenarios

Tests are designed to be lightweight "smoke tests" that verify core functionality without requiring external dependencies or complex mocking.

### 4. Docker Deployment

Build the Docker image:

```bash
docker build -t publishing-delivery-updater .
```

Run the container:

```bash
docker run -p 8080:8080 --env-file .env publishing-delivery-updater
```

## Usage

### Input File Format

The application accepts both XLSX and CSV files with the following columns:

- **Pub Song ID**: Integer values representing publishing song IDs
- **timestamp**: Date/time values in various formats

**Supported timestamp formats:**
- `8/19/2025 3:20 PM EST` (with explicit timezone)
- `08/19/2025 20:20` (24-hour format, assumes EST/EDT)
- `2025-08-19 15:20` (ISO-like format, assumes EST/EDT)

**Supported timezones:** EST, EDT, PST, PDT, CST, CDT, MST, MDT

Example CSV (semicolon-separated):
```
Pub Song ID;timestamp
134224;8/19/2025 3:20 PM EST
134246;8/19/2025 3:20 PM EST
134229;8/19/2025 3:20 PM EST
```

Example XLSX:
```
Pub Song ID | timestamp
134224      | 8/19/2025 3:20 PM EST
134246      | 8/19/2025 3:20 PM EST
134229      | 8/19/2025 3:20 PM EST
```

### Workflow

1. **Upload File**: Select and upload your XLSX or CSV file
2. **Validation**: Review validation results and handle any errors
3. **QA Upload**: Upload to QA environment and review results
4. **Production Upload**: Confirm and upload to Production environment
5. **Complete**: Review final results and optionally start over

### Error Handling

The application provides detailed error reporting for:

- File format issues
- Invalid Pub Song IDs (non-integer or negative values)
- Invalid timestamps (unparseable date/time values)
- GraphQL API errors
- Network connectivity issues

## Configuration

### Environment Variables

| Variable | Required | Description                                         |
|----------|----------|-----------------------------------------------------|
| `QA_GRAPHQL_URL` | Yes | GraphQL endpoint for QA environment                 |
| `PROD_GRAPHQL_URL` | Yes | GraphQL endpoint for Production environment         |
| `ORCHARD_PROFILE_ID` | Yes | Profile ID for authentication                       |
| `ORCHARD_IDENTITY_ID` | Yes | Identity ID for authentication                      |
| `ORCHARD_PROFILE_TYPE` | No | Profile type (default: PublishingProfile)           |
| `CHUNK_SIZE` | No | Batch size for API requests (default: 50)           |
| `CHUNK_DELAY_MS` | No | Delay between batches in milliseconds (default: 10) |

### Authentication

The application uses the same authentication mechanism as the original TypeScript script:

- `Orchard-Profile-Id`: Your publishing profile ID
- `Orchard-Profile-Type`: Set to "PublishingProfile"
- `Orchard-Identity-Id`: Your identity UUID
- `apollographql-client-name`: Set to "delivery-updater-streamlit-app"

## User Authorization

### Overview

The application uses Auth0 for authentication and implements environment-based authorization through the `src/allowed_auth0_users.csv` file. This allows you to control which users can access the application in different environments (dev, qa, prod).

**Important:** Auth0 authentication is **always required** for `qa` and `prod` environments. For local development, you can optionally disable Auth0 using the `DISABLE_AUTH0` environment variable.

### Managing Authorized Users

Edit the `src/allowed_auth0_users.csv` file to add or remove authorized users:

```csv
EMAIL,QA_ACCESS,PROD_ACCESS
user1@company.com,true,true
qa_tester@company.com,true,false
prod_admin@company.com,false,true
```

**Column Descriptions:**

- **EMAIL**: User's email address from Auth0 (case-insensitive)
- **QA_ACCESS**: Set to `true` to grant access in non-production environments (dev, qa, staging, etc.)
- **PROD_ACCESS**: Set to `true` to grant access in production environment

### Authorization Rules

The `Environment` variable in your `.env` file determines which access column is checked:

- **When `Environment=prod`**: Only users with `PROD_ACCESS=true` can access the app
- **When `Environment≠prod`** (dev, qa, staging, etc.): Only users with `QA_ACCESS=true` can access the app

### Common Access Patterns

**Full Access** (all environments):
```csv
admin@company.com,true,true
```

**QA/Dev Only** (cannot access production):
```csv
qa_tester@company.com,true,false
developer@company.com,true,false
```

**Production Only** (cannot access non-prod):
```csv
prod_operator@company.com,false,true
```

**No Access** (user exists but blocked):
```csv
blocked_user@company.com,false,false
```

### Adding a New User

1. Ensure the user has an Auth0 account in your tenant
2. Add a new row to `src/allowed_auth0_users.csv`:
   ```csv
   newuser@company.com,true,true
   ```
3. Save the file and restart the application (or redeploy if using Docker)
4. The user can now log in with their Auth0 credentials

### Removing User Access

1. Locate the user's row in `src/allowed_auth0_users.csv`
2. Either:
   - **Delete the row entirely** (user will see "User not found" error)
   - **Set both columns to `false`** (user will see "no access" error)
3. Save the file and restart the application

### Authorization vs Authentication

- **Authentication** (Auth0): Verifies the user's identity ("Who are you?")
- **Authorization** (CSV file): Determines access permissions ("What can you do?")

A user must pass both checks to access the application:
1. Successfully log in via Auth0
2. Have appropriate access in `allowed_auth0_users.csv` for the current environment

### Disabling Auth0 for Local Development

For faster local development iteration, you can disable Auth0 authentication when working in non-production environments:

**Option 1: Environment Variable**
```bash
# Disable Auth0 for local development
DISABLE_AUTH0=true streamlit run src/app.py
```

**Option 2: .env File**
```env
Environment=dev
DISABLE_AUTH0=true
```

**Important Security Notes:**
- ⚠️ Auth0 is **always enforced** in `qa` and `prod` environments, regardless of the `DISABLE_AUTH0` setting
- 🔒 `DISABLE_AUTH0=true` only works when `Environment` is set to something other than `qa` or `prod` (e.g., `dev`, `local`, `staging`)
- 💡 When auth is disabled, you'll see "🔓 Auth disabled for local development" in the sidebar
- 🚫 Never set `DISABLE_AUTH0=true` in deployed environments

**When to Use:**
- ✅ Local development and testing
- ✅ Quick iterations without Auth0 setup
- ✅ CI/CD testing pipelines
- ❌ QA environment (always requires Auth0)
- ❌ Production environment (always requires Auth0)

### Testing Authorization

To test authorization rules:

```bash
# Test with Auth0 enabled (default)
Environment=dev streamlit run src/app.py

# Test without Auth0 (local dev only)
Environment=dev DISABLE_AUTH0=true streamlit run src/app.py

# Test in QA environment (Auth0 always required)
Environment=qa streamlit run src/app.py

# Test in prod environment (Auth0 always required)
Environment=prod streamlit run src/app.py
```

Run the authorization tests:

```bash
make test
# Look for: tests/test_auth_authorization.py
```

## Development

### Adding New Features

The modular structure makes it easy to extend:

- **Data processing**: Modify `src/data_processing.py`
- **API interactions**: Update `src/graphql_client.py`
- **UI components**: Enhance `src/app.py`

### Testing

The application is designed to be testable with proper separation of concerns:

- Data processing functions are pure functions
- GraphQL client is isolated and mockable
- UI logic is separated from business logic

### Monitoring

For production deployment, consider adding:

- Application logging with structured logs
- Metrics collection (success rates, processing times)
- Error alerting integration

## Troubleshooting

### Common Issues

1. **"Missing environment variable" errors**
   - Ensure your `.env` file is properly configured
   - Check that all required environment variables are set

2. **GraphQL authentication errors**
   - Verify your Profile ID and Identity ID are correct
   - Ensure you have the necessary permissions

3. **File upload errors**
   - Check that your file is a valid XLSX format
   - Ensure column names match exactly: "Pub Song ID" and "timestamp"

4. **Network timeout errors**
   - Check your internet connection
   - Verify the GraphQL endpoints are accessible

### Support

For issues related to:
- **Application bugs**: Check the application logs
- **Authentication**: Contact your system administrator
- **API errors**: Review the GraphQL API documentation