# Fansifter Roster Management

Streamlit app for viewing, adding, and deleting artists in vendor rosters within 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
- Compact table display with delete action buttons
- Delete artists from rosters with confirmation dialogs

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

### 🗑️ Delete Artist
- Remove artists from roster tables with confirmation
- For MAIN_REP and ARTIST_ROSTER: single delete button
- For LOCAL_REP with multiple countries:
  - Delete all countries at once
  - Or select specific country to delete
- Transaction-safe deletions

## 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` (or equivalent)
- **Database**: `FANSIFTER_APP_REPORTING`
- **Data Schema**: `QA` (for testing) or `PROD` (for production)
- **Deploy Schema**: `QA_STREAMLIT_ROSTER_MANAGEMENT` or `PROD_STREAMLIT_ROSTER_MANAGEMENT`
- **Warehouse**: `EXPLORATION_WH`
- **Read access** to:
  - `fansifter_app_reporting.{qa|prod}.ARTIST_ROSTER_*` tables
  - `orchard_app_reporting.delphi_prod.VENDOR`
  - `orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT`
  - `orchard_app_reporting.delphi_prod.COMPANY_BRAND`
- **Write access** to roster tables for adding/deleting 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/streamlit-fansifter-roster-management-app
```

### 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"
database = "FANSIFTER_APP_REPORTING"
schema = "QA_STREAMLIT_ROSTER_MANAGEMENT"
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",
    "warehouse": "EXPLORATION_WH",
    "database": "FANSIFTER_APP_REPORTING",
    "schema": "QA",
    "private_file": "/path/to/.ssh/snowflake/rsa_key.p8",
    "private_key_password": "your_password"
  }
}
```

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

## Usage

### Running Locally

**Important**: Local runs are **QA-only** for safety. PROD environment is accessible only through Snowflake deployment.

```bash
make run_streamlit_locally
```

Or directly:
```bash
export target=qa && poetry run streamlit run streamlit_app/main.py
```

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

The app will automatically connect to QA schema and display an info banner.

### Deploying to Snowflake

**Deploy to QA (for testing):**
```bash
make deploy_qa_streamlit
```

**Deploy to PROD:**
```bash
make deploy_prod_streamlit
```

**Remove deployments:**
```bash
# Remove QA deployment
make drop_qa_streamlit

# Remove PROD deployment
make drop_prod_streamlit
```

### Multi-Environment Support

The app supports both QA and PROD environments with automatic detection:

- **QA Environment** (`target=qa`)
  - Database: `FANSIFTER_APP_REPORTING.QA`
  - Safe for testing and development
  - Writes enabled
  - **Used for all local development**
  - Auto-detected when deployed to `QA_STREAMLIT_ROSTER_MANAGEMENT` schema

- **PROD Environment** (Snowflake deployment only)
  - Database: `FANSIFTER_APP_REPORTING.PROD`
  - Deploy schema: `PROD_STREAMLIT_ROSTER_MANAGEMENT`
  - Production data
  - **Accessible ONLY via Snowflake deployment** (`make deploy_prod_streamlit`)
  - Local runs are QA-only for safety
  - Auto-detected when deployed to `PROD_STREAMLIT_ROSTER_MANAGEMENT` schema

**Environment Detection**:
- Deployed apps: Automatically detect schema from Snowflake session context
- Local runs: Use `target` environment variable (defaults to `qa`)
- Centralized in `common/environment.py` module

**Safety Model**:
- ✅ Local development → Always QA
- ✅ Testing changes → QA deployment
- ✅ Production use → PROD deployment only
- ❌ Local PROD access → Blocked for safety

### Code Quality

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

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

### Available Make Commands

```bash
make help                    # Show all available commands
make deploy_qa_streamlit     # Deploy to Snowflake QA
make drop_qa_streamlit       # Remove QA deployment
make deploy_prod_streamlit   # Deploy to Snowflake PROD
make drop_prod_streamlit     # Remove PROD deployment
make run_streamlit_locally   # Run locally (QA only)
make fmt                     # Format code (ruff)
make lint                    # Run linters (mypy, ruff)
```

## Architecture

### Project Structure
```
streamlit-fansifter-roster-management-app/
├── streamlit_app/
│   ├── main.py                      # Entry point with welcome page
│   ├── snowflake.yml                # Snowflake deployment config
│   ├── environment.yml              # Snowflake environment dependencies
│   ├── common/                      # Shared utilities
│   │   ├── connections.json.shadow  # Connection template
│   │   ├── constants_general.py     # General constants (lookup tables)
│   │   ├── constants_prod.py        # PROD environment constants
│   │   ├── constants_qa.py          # QA environment constants
│   │   ├── db.py                    # Database connection & queries
│   │   ├── environment.py           # Centralized environment detection
│   │   ├── local_connection.py      # Local dev connection helper
│   │   ├── queries.py               # SQL query templates (env-aware)
│   │   ├── services.py              # Business logic (env-aware)
│   │   ├── types.py                 # Pydantic models
│   │   └── utils.py                 # UI helpers
│   └── pages/                       # Streamlit pages
│       ├── roster_view.py           # Search/view/delete roster
│       └── add_artist.py            # Add artist form
├── pyproject.toml                   # Poetry dependencies
├── Makefile                         # Development commands
└── README.md                        # This file
```

### Key Design Patterns

**Environment Detection** (`environment.py`)
- Centralized module for environment/schema detection
- Auto-detects schema from Snowflake session context (deployed apps)
- Falls back to environment variable for local development
- Exports: `TARGET_SCHEMA`, `TARGET_DATABASE`, `SCHEMA_NAME`, `IS_QA`, `IS_PROD`

**Database Layer** (`db.py`)
- Cached Snowpark session using `st.cache_resource`
- Dynamic SQL queries with f-strings for flexibility
- Support for both Snowflake deployment and local development
- Query execution helpers: `execute_query()`, `execute_non_query()`

**Business Logic** (`services.py`)
- Brand detection based on vendor's COMPANY_BRAND (Sony Music vs others)
- Validation before inserts/deletes
- Duplicate prevention using MERGE queries
- Vendor and artist search with deduplication (`DISTINCT`, `_FIVETRAN_DELETED` filtering)
- Environment-aware write protection

**Environment Configuration**
- `environment.py` - centralized environment detection and constants
- `constants_general.py` - shared lookup table constants
- `constants_prod.py` - PROD environment settings
- `constants_qa.py` - QA environment settings
- Runtime detection via Snowflake session context or `target` env var

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

## Brand Classification Rules

### SME Vendors (Sony Music Entertainment)
- **Definition**: Vendors with "Sony Music" brand in `COMPANY_BRAND_HAS_LABEL_VENDOR` table
- **Allowed rosters**: `MAIN_REP`, `LOCAL_REP` only
- **Blocked rosters**: `ARTIST_ROSTER`
- **Detection**: Automatic via `COMPANY_BRAND.DISPLAY_NAME = 'Sony Music'`

### Non-SME Vendors (All Others)
- **Definition**: Vendors without "Sony Music" brand
- **Allowed rosters**: `ARTIST_ROSTER` only
- **Blocked rosters**: `MAIN_REP`, `LOCAL_REP`
- **Detection**: Automatic via absence of Sony Music brand

**Important**: Brand classification is determined by COMPANY_BRAND association, not by roster table presence.

## 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 Alpha-2)

Supports multiple countries per artist.

### 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** (auto-detected via COMPANY_BRAND):
   - SME vendors (Sony Music) → only `MAIN_REP` or `LOCAL_REP`
   - Non-SME vendors → only `ARTIST_ROSTER`
4. **Roster-specific rules**:
   - `LOCAL_REP` requires `COUNTRY_CODE` (2-letter ISO Alpha-2, uppercase)
   - `MAIN_REP` allows `STATUS` and `IS_ARTIST_TEAM`
5. **No duplicates** based on each table's unique key
6. **Deleted records excluded**: Only active records (`NOT _FIVETRAN_DELETED`) are shown/searchable

## 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 "Sony Music" in COMPANY_BRAND
- SME vendors can only use MAIN_REP or LOCAL_REP rosters
- Use the appropriate roster type for Sony Music vendors

### "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
```

### Duplicate vendors/artists in search dropdown
- App filters `_FIVETRAN_DELETED` records and uses `DISTINCT`
- If you see duplicates, they may be legitimate (same name, different IDs)

## Security Notes

- Never commit `connections.json` or private keys
- All SQL queries built with f-strings (no user input interpolation in WHERE clauses)
- User input is escaped/validated before query building
- Private keys stored in DER format in memory
- Connection parameters cached securely in Streamlit session
- Write operations require explicit user action (button clicks with confirmation)

## Deployed App Information

### Snowflake Object Names
- **App Name**: `FANSIFTER_ROSTER_MANAGEMENT`
- **Stage**: `fansifter_roster_management_stage`
- **QA URL**: `https://app.snowflake.com/SME/delphi/#/streamlit-apps/FANSIFTER_APP_REPORTING.QA_STREAMLIT_ROSTER_MANAGEMENT.FANSIFTER_ROSTER_MANAGEMENT`
- **PROD URL**: (deploy with `make deploy_prod_streamlit`)

### User Permissions

For Snowflake deployment, users need:
- Snowflake role: `FANSIFTER_ENGINEERING` (or equivalent with access to roster tables)

## Support

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

## License

Proprietary - Sony Music Entertainment / The Orchard