# Seated List Export

Streamlit in Snowflake app that transforms Seated fan opt-in data into the Fansifter CRM upload template format and provides a CSV download with unmasked PII.

## Problem

Seated fan opt-in data lands in `FAN_LIVE_EVENTS.PROD.RAW_SEATED_OPT_INS` (SME-DELPHI) via the `swf-feed-ingestion` Jenkins pipeline, but importing into Fansifter CRM requires manual reformatting into a specific template. PII columns are masked for general access roles, making Sigma-based exports unusable for CRM upload.

This app solves both problems by running under `FANSIFTER_ENGINEERING_PRIVACY` (which can unmask PII) and outputting data in the exact CRM template format.

## Features

- Artist selector populated from distinct artists in the source table
- Date range filter on `REPORT_DATE`
- Source filter (reminder, order, waitlist, follower)
- Label input (required CRM field, varies per artist)
- Email deduplication with preference for rows with more populated fields
- Preview table and row count summary
- CSV download in Fansifter CRM upload template format

## CRM Template Mapping

| CRM Column | Source |
|------------|--------|
| Email (Required) | `EMAIL` |
| Opt-in (Required) | Static `TRUE` |
| Mailing List | `ARTIST_NAME` |
| File Source Description | Static `Seated List` |
| Territory (2 digit ISO) | `VENUE_COUNTRY` |
| Label (Required) | User input |
| First Name | `FIRST_NAME` |
| Last Name | `LAST_NAME` |
| Mobile Phone | `PHONE_NUMBER` |
| City | `VENUE_CITY` |
| State | `VENUE_STATE` |
| Country/Region | `VENUE_COUNTRY` |
| Postal Code | `VENUE_POSTAL_CODE` |

## Deployment

Deployed as a Streamlit Container Runtime app on SME-DELPHI.

```
Database:      FANSIFTER_APP_REPORTING
Schema:        DEV_MMACHADO
Stage:         SEATED_LIST_EXPORT_STAGE
Compute Pool:  SEATED_LIST_EXPORT_POOL
Owner Role:    FANSIFTER_ENGINEERING_PRIVACY
Warehouse:     PROD_OWS_WH
```

### Deploy commands

```sql
USE ROLE FANSIFTER_ENGINEERING_PRIVACY;

PUT file://app.py @FANSIFTER_APP_REPORTING.DEV_MMACHADO.SEATED_LIST_EXPORT_STAGE/ AUTO_COMPRESS=FALSE OVERWRITE=TRUE;
PUT file://queries.py @FANSIFTER_APP_REPORTING.DEV_MMACHADO.SEATED_LIST_EXPORT_STAGE/ AUTO_COMPRESS=FALSE OVERWRITE=TRUE;
PUT file://pyproject.toml @FANSIFTER_APP_REPORTING.DEV_MMACHADO.SEATED_LIST_EXPORT_STAGE/ AUTO_COMPRESS=FALSE OVERWRITE=TRUE;

CREATE OR REPLACE STREAMLIT FANSIFTER_APP_REPORTING.DEV_MMACHADO.SEATED_LIST_EXPORT
    FROM '@FANSIFTER_APP_REPORTING.DEV_MMACHADO.SEATED_LIST_EXPORT_STAGE/'
    MAIN_FILE = 'app.py'
    QUERY_WAREHOUSE = PROD_OWS_WH
    COMPUTE_POOL = SEATED_LIST_EXPORT_POOL
    RUNTIME_NAME = SYSTEM$ST_CONTAINER_RUNTIME_PY3_11
    EXTERNAL_ACCESS_INTEGRATIONS = (HELLO_WORLD_ACCESS)
    COMMENT = 'Export Seated opt-in data formatted for Fansifter CRM upload';
```

## Access Control

Only users with the `FANSIFTER_ENGINEERING_PRIVACY` role can open the app. This role is required because the masking policy on PII columns uses `current_role()` — role inheritance does not apply.

## Files

- `app.py` — Streamlit UI (sidebar filters, preview table, CSV download)
- `queries.py` — SQL query builders (dedup, export, summary, date range)
- `pyproject.toml` — Python dependencies
- `deploy.sql` — Initial deployment script (reference only; use commands above for current deploy)
- `.streamlit/config.toml` — Theme configuration