# Artist Roster Streamlit App

Streamlit app for viewing and adding artists to vendor rosters in Snowflake.

## Overview

This application provides a user-friendly interface to manage artist rosters across three Snowflake tables:
- `ARTIST_ROSTER_MAIN_REP` (SME brands - main representatives)
- `ARTIST_ROSTER_LOCAL_REP` (SME brands - territory-specific)
- `ARTIST_ROSTER` (Non-SME brands)

## Features

### 📋 Roster View
- Search across all roster tables with flexible filters
- Filter by vendor ID/name, artist UUID/name, and subaccount
- Pagination for large result sets (100 rows per page)
- View STATUS and IS_ARTIST_TEAM fields for MAIN_REP entries

### ➕ Add Artist
- Add new artists to appropriate roster tables
- Automatic brand detection (SME vs non-SME)
- Built-in validation and duplicate prevention
- Vendor and artist search by ID or name
- Conditional form fields based on roster type:
  - **MAIN_REP**: STATUS (default: ACTIVE), IS_ARTIST_TEAM checkbox
  - **LOCAL_REP**: COUNTRY_CODE (required)
  - **ARTIST_ROSTER**: Basic fields only

## Prerequisites

### Required Software
- Python 3.11 or higher
- Poetry (Python package manager)
- Snowflake CLI ([installation guide](https://docs.snowflake.com/en/developer-guide/snowflake-cli/installation/installation))

### Snowflake Access
You need a Snowflake account with:
- **Role**: `FANSIFTER_ENGINEERING_PRIVACY` (or equivalent)
- **Database**: `FANSIFTER_APP_REPORTING`
- **Schema**: `PROD`
- **Warehouse**: `EXPLORATION_WH`
- **Read access** to:
  - `fansifter_app_reporting.prod.ARTIST_ROSTER_*` tables
  - `orchard_app_reporting.delphi_prod.VENDOR`
  - `orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT`
- **Write access** to roster tables for adding artists

### SSH Keys
For local development and Snowflake CLI deployment, set up keypair authentication:
1. Generate RSA key pair for Snowflake
2. Add public key to your Snowflake user
3. Store private key securely (e.g., `~/.ssh/snowflake/rsa_key.p8`)

## Installation

### 1. Clone Repository
```bash
cd /path/to/fansifter-artist-roster
```

### 2. Install Dependencies
```bash
poetry install
```

### 3. Configure Snowflake CLI
Create or update `~/.snowflake/config.toml`:

```toml
[connections.artist_roster]
account = "delphi.us-east-1"
user = "your.email@sonymusic-pde.com"
role = "FANSIFTER_ENGINEERING_PRIVACY"
database = "FANSIFTER_APP_REPORTING"
schema = "PROD"
warehouse = "EXPLORATION_WH"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/path/to/.ssh/snowflake/rsa_key.p8"
```

### 4. Configure Local Development (Optional)
For running locally, copy and configure connections file:

```bash
cp streamlit_app/common/connections.json.shadow streamlit_app/common/connections.json
```

Edit `connections.json` with your Snowflake credentials:
```json
{
  "artist_roster": {
    "account": "delphi.us-east-1",
    "user": "your.email@sonymusic-pde.com",
    "role": "FANSIFTER_ENGINEERING_PRIVACY",
    "warehouse": "EXPLORATION_WH",
    "database": "FANSIFTER_APP_REPORTING",
    "schema": "PROD",
    "private_file": "/path/to/.ssh/snowflake/rsa_key.p8"
  }
}
```

**Important**: Never commit `connections.json` (already in `.gitignore`)

## Usage

### Running Locally

```bash
make run_streamlit_locally
```

Or directly:
```bash
poetry run streamlit run streamlit_app/main.py
```

Access the app at: http://localhost:8501

### Deploying to Snowflake

Deploy to production:
```bash
make deploy_prod_streamlit
```

This deploys the app to Snowflake using Snowflake CLI, where it will be accessible to users with appropriate permissions.

### Code Quality

Format code:
```bash
make fmt
```

Run linters:
```bash
make lint
```

### Available Make Commands

```bash
make help                    # Show all available commands
make deploy_prod_streamlit   # Deploy to Snowflake PROD
make run_streamlit_locally   # Run locally
make fmt                     # Format code (ruff)
make lint                    # Run linters (mypy, ruff)
```

## Architecture

### Project Structure
```
fansifter-artist-roster/
├── streamlit_app/
│   ├── main.py                      # Entry point with welcome page
│   ├── snowflake.yml                # Snowflake deployment config
│   ├── common/                      # Shared utilities
│   │   ├── connections.json.shadow  # Connection template
│   │   ├── db.py                    # Database connection & queries
│   │   ├── local_connection.py      # Local dev connection helper
│   │   ├── queries.py               # SQL query templates
│   │   ├── services.py              # Business logic
│   │   ├── types.py                 # Pydantic models
│   │   └── utils.py                 # UI helpers
│   └── pages/                       # Streamlit pages
│       ├── roster_view.py           # Search/view roster
│       └── add_artist.py            # Add artist form
├── pyproject.toml                   # Poetry dependencies
├── Makefile                         # Development commands
└── README.md                        # This file
```

### Key Design Patterns

**Database Layer** (`db.py`)
- Cached Snowpark session using `st.cache_resource`
- Parameterized queries for security
- Support for both Snowflake deployment and local development

**Business Logic** (`services.py`)
- Brand detection based on ARTIST_ROSTER table presence
- Validation before inserts
- Duplicate prevention
- Vendor and artist search

**Type Safety** (`types.py`)
- Pydantic models for all data structures
- Type hints throughout codebase
- mypy strict mode enabled

## Brand Classification Rules

### Non-SME Vendors
- **Definition**: Vendors with at least one entry in `ARTIST_ROSTER` table
- **Allowed rosters**: `ARTIST_ROSTER` only
- **Blocked rosters**: `MAIN_REP`, `LOCAL_REP`

### SME Vendors
- **Definition**: Vendors with no entries in `ARTIST_ROSTER` table
- **Allowed rosters**: `MAIN_REP`, `LOCAL_REP`
- **Blocked rosters**: `ARTIST_ROSTER`

**Note**: If an SME vendor needs to be added to `ARTIST_ROSTER`, contact admin to seed the first record.

## Roster Table Schemas

### ARTIST_ROSTER_MAIN_REP
**Unique Key**: `(VENDOR_ID, GLOBAL_PARTICIPANT_ID, SUBACCOUNT_ID)`

Required fields:
- `VENDOR_ID` (NUMBER)
- `GLOBAL_PARTICIPANT_ID` (VARCHAR)
- `SUBACCOUNT_ID` (NUMBER)
- `STATUS` (VARCHAR) - defaults to 'ACTIVE'
- `IS_ARTIST_TEAM` (BOOLEAN) - defaults to FALSE

### ARTIST_ROSTER_LOCAL_REP
**Unique Key**: `(VENDOR_ID, GLOBAL_PARTICIPANT_ID, SUBACCOUNT_ID, COUNTRY_CODE)`

Required fields:
- `VENDOR_ID` (NUMBER)
- `GLOBAL_PARTICIPANT_ID` (VARCHAR)
- `SUBACCOUNT_ID` (NUMBER)
- `COUNTRY_CODE` (VARCHAR, 2-letter ISO)

### ARTIST_ROSTER
**Unique Key**: `(VENDOR_ID, GLOBAL_PARTICIPANT_ID, SUBACCOUNT_ID)`

Required fields:
- `VENDOR_ID` (NUMBER)
- `GLOBAL_PARTICIPANT_ID` (VARCHAR)
- `SUBACCOUNT_ID` (NUMBER)

All tables have auto-generated `ID` and `CREATED_AT` fields.

## Validation Rules

The app enforces the following validations:

1. **Vendor must exist** in `orchard_app_reporting.delphi_prod.VENDOR`
2. **Artist must exist** in `orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT`
3. **Brand rules**:
   - Non-SME vendors → only `ARTIST_ROSTER`
   - SME vendors → only `MAIN_REP` or `LOCAL_REP`
4. **Roster-specific rules**:
   - `LOCAL_REP` requires `COUNTRY_CODE` (2-letter ISO)
   - `MAIN_REP` allows `STATUS` and `IS_ARTIST_TEAM`
5. **No duplicates** based on each table's unique key

## Troubleshooting

### "No active session" error
- Ensure Snowflake CLI is configured with `artist_roster` connection
- For local dev, verify `connections.json` exists and has correct credentials

### "Vendor is SME brand" error when adding to ARTIST_ROSTER
- This vendor has no entries in `ARTIST_ROSTER` table
- Contact admin to seed first record or verify brand classification

### "Duplicate entry found" error
- Entry already exists with same unique key
- Check roster view to see existing entry
- Update search filters to find the duplicate

### Connection timeout
- Verify VPN connection if required
- Check Snowflake warehouse is running
- Verify role has access to required databases/schemas

### Type errors during development
Run type checker to identify issues:
```bash
make lint
```

## Security Notes

- Never commit `connections.json` or private keys
- All SQL queries use parameterized statements
- No raw user input is interpolated into SQL
- Private keys stored in DER format in memory
- Connection parameters cached securely in Streamlit session

## User Permissions

For Snowflake deployment, users need:
- Snowflake role: `CRM_TEAM_ROLE` (or equivalent)
- Database role: `FANSIFTER_APP_REPORTING.DB_PROD_SCHEMA_FILEUPLOAD_STREAMLIT`
- Permissions managed via Terraform module `streamlit_database_role`

## Support

For issues, questions, or feature requests:
- Contact: Fansifter Engineering Team
- Repository: `fansifter-artist-roster`

## License

Proprietary - Sony Music Entertainment / The Orchard