# Artist Roster Streamlit App — Plan (Non‑SME brand via ARTIST_ROSTER)

## 1. Project Overview

Build a Streamlit app to **view** and **add** artists to vendor rosters in **Snowflake**, enforcing rules for **SME vs non‑SME** brands.

**Read/Write tables (fully qualified):**
- `fansifter_app_reporting.prod.ARTIST_ROSTER_MAIN_REP`
- `fansifter_app_reporting.prod.ARTIST_ROSTER_LOCAL_REP`
- `fansifter_app_reporting.prod.ARTIST_ROSTER`  
  *Holds all artists for vendors whose brand is **non‑SME***

**Lookup tables:**
- `orchard_app_reporting.delphi_prod.VENDOR`  (`VENDOR_ID`, `NAME`)
- `orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT`  (`ID`, `NAME`, `NORMALIZED_NAME`)

**Canonical mapping used in code:**
- `vendor_id` ⇢ `VENDOR.VENDOR_ID`
- `vendor_name` ⇢ `VENDOR.NAME`
- `artist_uuid` ⇢ `GLOBAL_PARTICIPANT.ID`  (in roster tables: `GLOBAL_PARTICIPANT_ID`)
- `artist_name` ⇢ `GLOBAL_PARTICIPANT.NAME`
- `subaccount_id` ⇢ roster tables `SUBACCOUNT_ID`
- `country_code` ⇢ **only** `ARTIST_ROSTER_LOCAL_REP.COUNTRY_CODE`
- `main_status` ⇢ **only** `ARTIST_ROSTER_MAIN_REP.STATUS`
- `created_at` ⇢ `*_ROSTER.CREATED_AT`

> Roster tables are **HYBRID TABLES** with uniqueness constraints:
> - MAIN_REP: `UNIQUE (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID)` + `STATUS`, `IS_ARTIST_TEAM`
> - LOCAL_REP: `UNIQUE (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID, COUNTRY_CODE)`
> - ARTIST_ROSTER (non‑SME): `UNIQUE (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID)`

---

## 2. Proposed Project Structure

```
.
├── .env
├── .gitignore
├── Dockerfile
├── pyproject.toml
└── src/
    ├── __init__.py
    ├── app.py            # Streamlit UI (tabs: Roster, Add artist)
    ├── db.py             # Snowflake connection, param queries, tx helpers
    ├── adapters.py       # Column mapping for roster tables (introspection)
    ├── queries.py        # SQL templates/builders
    ├── services.py       # Non‑SME detection, validation, insert logic
    ├── utils.py          # Pagination, UI helpers
    └── types.py          # Pydantic models for forms/rows
```

---

## 3. Dependencies & Environment

Use **uv**.

```toml
# pyproject.toml
[project]
name = "artist-roster-app"
version = "0.1.0"
requires-python = ">=3.11"
dependencies = [
  "streamlit",
  "pandas",
  "snowflake-connector-python",
  "python-dotenv",
  "pydantic",
]
```

```bash
uv venv && source .venv/bin/activate
uv pip install -e .
```

Snowflake secrets in `.streamlit/secrets.toml`:

```toml
[snowflake]
account = "..."
user = "..."
password = "..."     # or keypair
warehouse = "..."
database = "FANSIFTER_APP_REPORTING"
schema = "PROD"      # working schema for roster tables
role = "..."
```

---

## 4. Development Steps

### Step 1 — App layout (`src/app.py`)
- Two tabs: **Roster** and **Add artist**.
- `st.session_state` for filters, pagination, and last results.
- Roster tab: filters (vendor id/name, artist uuid/name, optional subaccount), table with sort & pagination.

### Step 2 — DB & introspection (`src/db.py`, `src/adapters.py`)
- Cached connector via `st.cache_resource`.
- All SQL **parameterized**.
- Introspect roster tables to confirm column names and types (focus: `GLOBAL_PARTICIPANT_ID`, `VENDOR_ID`, `SUBACCOUNT_ID`, `COUNTRY_CODE` where applicable, `STATUS`, `CREATED_AT`).

```sql
SELECT table_catalog, table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_catalog='FANSIFTER_APP_REPORTING'
  AND table_schema='PROD'
  AND table_name IN ('ARTIST_ROSTER_MAIN_REP','ARTIST_ROSTER_LOCAL_REP','ARTIST_ROSTER');
```

- Maintain a small adapter mapping to canonical names used by queries and UI.

### Step 3 — Roster view (`src/queries.py`, `src/app.py`)
- Unified query with a **roster_type** label and aligned columns.

```sql
WITH main AS (
  SELECT VENDOR_ID,
         GLOBAL_PARTICIPANT_ID AS artist_uuid,
         SUBACCOUNT_ID,
         CREATED_AT,
         'MAIN_REP' AS roster_type,
         STATUS       AS main_status,
         CAST(NULL AS VARCHAR) AS country_code
  FROM fansifter_app_reporting.prod.ARTIST_ROSTER_MAIN_REP
), local AS (
  SELECT VENDOR_ID,
         GLOBAL_PARTICIPANT_ID AS artist_uuid,
         SUBACCOUNT_ID,
         CREATED_AT,
         'LOCAL_REP' AS roster_type,
         CAST(NULL AS VARCHAR) AS main_status,
         COUNTRY_CODE
  FROM fansifter_app_reporting.prod.ARTIST_ROSTER_LOCAL_REP
), non_sme AS (
  SELECT VENDOR_ID,
         GLOBAL_PARTICIPANT_ID AS artist_uuid,
         SUBACCOUNT_ID,
         CREATED_AT,
         'ARTIST_ROSTER' AS roster_type,
         CAST(NULL AS VARCHAR) AS main_status,
         CAST(NULL AS VARCHAR) AS country_code
  FROM fansifter_app_reporting.prod.ARTIST_ROSTER
), roster AS (
  SELECT * FROM main
  UNION ALL
  SELECT * FROM local
  UNION ALL
  SELECT * FROM non_sme
)
SELECT r.VENDOR_ID                AS vendor_id,
       v.NAME                     AS vendor_name,
       r.artist_uuid,
       gp.NAME                    AS artist_name,
       r.SUBACCOUNT_ID           AS subaccount_id,
       r.roster_type,
       r.main_status,
       r.country_code,
       r.CREATED_AT              AS created_at
FROM roster r
LEFT JOIN orchard_app_reporting.delphi_prod.VENDOR v
       ON v.VENDOR_ID = r.VENDOR_ID
LEFT JOIN orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT gp
       ON gp.ID = r.artist_uuid
WHERE (:vendor_id   IS NULL OR r.VENDOR_ID = :vendor_id)
  AND (:vendor_name IS NULL OR v.NAME ILIKE CONCAT('%', :vendor_name, '%'))
  AND (:artist_uuid IS NULL OR r.artist_uuid = :artist_uuid)
  AND (:artist_name IS NULL OR gp.NAME ILIKE CONCAT('%', :artist_name, '%'))
  AND (:subaccount_id IS NULL OR r.SUBACCOUNT_ID = :subaccount_id)
ORDER BY v.NAME, gp.NAME
LIMIT :limit OFFSET :offset;
```

- Display: `vendor_id`, `vendor_name`, `artist_uuid`, `artist_name`, `subaccount_id`, `roster_type`, `main_status`, `country_code`, `created_at`.

### Step 4 — Add artist flow (`src/services.py`, `src/app.py`)
Form fields:
- **Vendor**: by ID or by name (autocomplete).
- **Subaccount**: required (NUMBER).
- **Artist**: by **UUID** or search by name in `GLOBAL_PARTICIPANT`.
- **Target roster**: `MAIN_REP` | `LOCAL_REP` | `ARTIST_ROSTER`.
- **Country code**: required **only** when target is `LOCAL_REP`.
- **Status** (optional for MAIN_REP; defaults to `INACTIVE` if omitted).

Validations:
- Vendor exists (`VENDOR.VENDOR_ID`).
- Artist exists (`GLOBAL_PARTICIPANT.ID`).
- Subaccount provided (NUMBER).
- **Brand rules**:
  - **Non‑SME vendors** (as detected below) → only `ARTIST_ROSTER`.
  - **SME vendors** → only `ARTIST_ROSTER_MAIN_REP` or `ARTIST_ROSTER_LOCAL_REP`.
- Uniqueness check must match table’s unique key:
  - MAIN: `(VENDOR_ID, GLOBAL_PARTICIPANT_ID, SUBACCOUNT_ID)`
  - LOCAL: `(VENDOR_ID, GLOBAL_PARTICIPANT_ID, SUBACCOUNT_ID, COUNTRY_CODE)`
  - ARTIST_ROSTER: `(VENDOR_ID, GLOBAL_PARTICIPANT_ID, SUBACCOUNT_ID)`

Duplicate checks:
```sql
-- MAIN
SELECT 1 FROM fansifter_app_reporting.prod.ARTIST_ROSTER_MAIN_REP
WHERE VENDOR_ID=:vendor_id AND GLOBAL_PARTICIPANT_ID=:artist_uuid AND SUBACCOUNT_ID=:subaccount_id
LIMIT 1;

-- LOCAL
SELECT 1 FROM fansifter_app_reporting.prod.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID=:vendor_id AND GLOBAL_PARTICIPANT_ID=:artist_uuid
  AND SUBACCOUNT_ID=:subaccount_id AND COUNTRY_CODE=:country_code
LIMIT 1;

-- ARTIST_ROSTER (non‑SME)
SELECT 1 FROM fansifter_app_reporting.prod.ARTIST_ROSTER
WHERE VENDOR_ID=:vendor_id AND GLOBAL_PARTICIPANT_ID=:artist_uuid AND SUBACCOUNT_ID=:subaccount_id
LIMIT 1;
```

Inserts (transactional):
```sql
-- MAIN (STATUS optional; defaults to 'INACTIVE')
INSERT INTO fansifter_app_reporting.prod.ARTIST_ROSTER_MAIN_REP
  (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID{, STATUS})
VALUES
  (:artist_uuid, :vendor_id, :subaccount_id{, :status});

-- LOCAL (COUNTRY_CODE required)
INSERT INTO fansifter_app_reporting.prod.ARTIST_ROSTER_LOCAL_REP
  (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID, COUNTRY_CODE)
VALUES
  (:artist_uuid, :vendor_id, :subaccount_id, :country_code);

-- ARTIST_ROSTER (non‑SME brand)
INSERT INTO fansifter_app_reporting.prod.ARTIST_ROSTER
  (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID)
VALUES
  (:artist_uuid, :vendor_id, :subaccount_id);
```

### Step 5 — Non‑SME vendor detection (`src/services.py`) — **UPDATED**
**Definition:** A vendor is considered **non‑SME** **only if** it already appears in `fansifter_app_reporting.prod.ARTIST_ROSTER`.

- Detection query:
```sql
SELECT 1
FROM fansifter_app_reporting.prod.ARTIST_ROSTER
WHERE VENDOR_ID = :vendor_id
LIMIT 1;
```

- **Implications:**
  - Adding to `ARTIST_ROSTER` is allowed **only** for vendors that already have **at least one** row in `ARTIST_ROSTER`.
    If the vendor has no prior rows, block with: _"Vendor is SME brand (no entries in ARTIST_ROSTER). Contact admin to seed the first record or confirm brand mapping."_
  - **Non‑SME** vendors **cannot** add MAIN/LOCAL rows.
  - **SME** vendors **cannot** add rows to `ARTIST_ROSTER`.

**Vendor search**:
```sql
SELECT VENDOR_ID, NAME
FROM orchard_app_reporting.delphi_prod.VENDOR
WHERE (:vendor_id IS NOT NULL AND VENDOR_ID = :vendor_id)
   OR (:vendor_name IS NOT NULL AND NAME ILIKE CONCAT('%', :vendor_name, '%'))
ORDER BY NAME
LIMIT 50;
```

**Artist search**:
```sql
SELECT ID AS artist_uuid, NAME AS artist_name
FROM orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT
WHERE (:artist_uuid IS NOT NULL AND ID = :artist_uuid)
   OR (:artist_name IS NOT NULL AND NAME ILIKE CONCAT('%', :artist_name, '%'))
ORDER BY NAME
LIMIT 50;
```

---

## 5. Dockerfile for Deployment

```dockerfile
FROM ghcr.io/astral-sh/uv:python3.12-bookworm-slim

WORKDIR /app
COPY pyproject.toml .
RUN uv pip install --system --no-cache -e .
COPY ./src ./src

EXPOSE 8080
HEALTHCHECK CMD streamlit hello
CMD ["streamlit", "run", "src/app.py", "--server.port=8080", "--server.address=0.0.0.0"]
```

---

## 6. Configuration & Best Practices

- **Types**: Use type hints and `pydantic` models for form inputs/rows.
- **Security**: No secrets in code or logs. Use `.streamlit/secrets.toml` and env vars in deployment.
- **SQL**: All queries parameterized. No string interpolation of values.
- **Caching**: `st.cache_resource` for the Snowflake connection.
- **UX**: Clear errors (`st.error`), success toasts, spinners.
- **Tests (manual)**:
  - Non‑SME vendor + MAIN/LOCAL → blocked.
  - SME vendor + ARTIST_ROSTER → blocked.
  - Duplicate insert rules per unique keys above.
  - Searches by name/ID return correct IDs.
  - New insert visible in Roster view.

---

## 7. Notes

- Roster tables are **HYBRID**; respect uniqueness keys and required columns (`SUBACCOUNT_ID` everywhere, `COUNTRY_CODE` for LOCAL, optional `STATUS` for MAIN). Defaults exist for `ID` and `CREATED_AT`.
- Vendor & Global Participant schemas are fixed as provided.
- Keep a definitive mapping for brand classification outside of the app if possible; the app currently infers **non‑SME** from presence in `ARTIST_ROSTER` due to lack of a brand field in `VENDOR`.