# sme-feed-file-export

This tool prepares various SME "STARS" feed files. It splits a Snowflake View containing Sony Music Entertainment "STARS" data into per-store files for delivery. The files are in specific format with a header file, a footer file, and a content file. There is also one, lone control file.

## Table of Contents

- [sme-feed-file-export](#sme-feed-file-export)
  - [Table of Contents](#table-of-contents)
  - [Quick Start: SFTP Delivery](#quick-start-sftp-delivery)
    - [🖥️ CLI (Command Line)](#️-cli-command-line)
    - [🌐 Streamlit Interface (Web UI)](#-streamlit-interface-web-ui)
    - [🤖 Jenkins (Automated)](#-jenkins-automated)
  - [Deployment](#deployment)
  - [Execution Methods](#execution-methods)
    - [CLI (Command Line)](#cli-command-line)
    - [Streamlit Interface (Web UI)](#streamlit-interface-web-ui)
    - [Jenkins (Automated)](#jenkins-automated)
  - [Environment Configuration](#environment-configuration)
  - [CLI Flags Reference](#cli-flags-reference)
  - [Interactive Prompts (Missing Required Fields)](#interactive-prompts-missing-required-fields)
  - [Development Setup (UV)](#development-setup-uv)
  - [Running Tests (UV)](#running-tests-uv)
  - [Linting \& Formatting (UV)](#linting--formatting-uv)
  - [Additional Documentation](#additional-documentation)

## Quick Start: SFTP Delivery

Transfer feed files to Sony's SFTP server automatically. **Choose your method:**

### 🖥️ CLI (Command Line)
```bash
# Source environment (contains PROCESS_MODE and other config)
source .env

# Generate feed files and transfer via SFTP
python feed_file_exporter.py --enable-sftp
```

### 🌐 Streamlit Interface (Web UI)
```bash
# Launch the browser-based interface
streamlit run streamlit_sftp_interface_v2.py
```
Perfect for interactive configuration, file browsing, and real-time monitoring.

### 🤖 Jenkins (Automated)
Use the [Jenkins job](https://scheduler.theorchard.io/job/sme-feed-file-exporter/) with parameters:
- **Transfer Files**: `Yes`
- **SFTP Host**: `ftp.sme-dsr.com`
- **SFTP User**: `FTP_Orchard`
- **SFTP Folder**: `monthly`

**📖 Full Documentation:**
- [Complete Execution Guide](docs/EXECUTION_GUIDE.md) - All methods with workflows
- [CLI Reference](docs/CLI_REFERENCE.md) - All command-line flags
- [Jenkins Guide](docs/JENKINS_GUIDE.md) - Jenkins setup and parameters
- [Streamlit Interface Guide](docs/streamlit_interface_guide.md) - Web UI walkthrough

## Deployment

This tool is currently deployed [on scheduler](https://scheduler.theorchard.io/job/sme-feed-file-exporter/). Files generated on scheduler are available in the workspace folders enumerated in the example config below.

## Execution Methods

This tool supports three execution methods, each suited for different use cases:

### CLI (Command Line)

**Best for:** Automated scripts, CI/CD pipelines, developers who prefer terminal workflows

**Basic usage:**

```bash
# Source environment (PROCESS_MODE set in .env)
source .env

# Run feed generation only (no SFTP)
python feed_file_exporter.py

# Run with SFTP transfer enabled
python feed_file_exporter.py --enable-sftp
```

**Development flags:**

```bash
# Test mode with mock SFTP (no real network)
python feed_file_exporter.py --enable-sftp --mock-sftp

# Prompt for password interactively
python feed_file_exporter.py --enable-sftp --prompt-sftp-password

# Inject password directly (DEV only, never commit)
python feed_file_exporter.py --enable-sftp --sftp-password 'your_password'
```

**Available processing modes (`PROCESS_MODE`):**
- `US` - US Stars monthly feed, Early Spotify, Early iTunes
- `GB` - UK Stars monthly feed
- `AGGREGATE` - Aggregate and Physical Aggregate feeds

**📖 See:** [CLI Reference](docs/CLI_REFERENCE.md) for complete flag documentation

### Streamlit Interface (Web UI)

**Best for:** Interactive configuration, file browsing, visual feedback, non-technical users

**Launch command:**

```bash
streamlit run streamlit_sftp_interface_v2.py
```

**Features:**
- 🖱️ Point-and-click configuration interface
- 📁 Visual file browser for selecting uploads
- 🎯 Real-time transfer progress with streaming logs
- 💾 Save/load configurations as JSON
- 🧪 Test mode with local SFTP server
- 🎭 Mock mode for testing without network
- 🔐 Secure password handling (never logged)

**📖 See:** [Streamlit Interface Guide](docs/streamlit_interface_guide.md) for complete UI walkthrough

### Jenkins (Automated)

**Best for:** Scheduled production runs, team workflows, centralized job management

**Jenkins job:** [https://scheduler.theorchard.io/job/sme-feed-file-exporter/](https://scheduler.theorchard.io/job/sme-feed-file-exporter/)

**Key parameters:**
- **Transfer Files**: `Yes` to enable SFTP, `No` to skip
- **SFTP Host**: Default `ftp.sme-dsr.com`
- **SFTP User**: Default `FTP_Orchard`
- **SFTP Folder**: Remote directory, default `monthly`
- **Feed Type**: Select from dropdown (Regular US, Regular UK, etc.)
- **Period ID**: Reporting period identifier

**Credentials:** SFTP password is injected via Jenkins Credentials store (credential ID: `sftp-creds`)

**📖 See:** [Jenkins Guide](docs/JENKINS_GUIDE.md) for setup, parameters, and troubleshooting

## Environment Configuration

**.env File:**

```bash
# Environment
export ENVIRONMENT=

export LOGGER_NAME=sme-feed-file-exporter
export LOGGER_LEVEL=WARN
export CONSOLE_LOG_LEVEL=INFO
export DEBUG_LOG_RESPONSE_CONSOLE=False

# Databases -----------------------------------------
# Snowflake Vars
export SNOWFLAKE_ACCOUNT=
export SNOWFLAKE_ROLE=DEV_ENGINEERING
export SNOWFLAKE_WAREHOUSE=DEV_OWS_WAREHOUSE
export SNOWFLAKE_KEY_PASSPHRASE=
export SNOWFLAKE_PASSWORD=any_not_empty
export SNOWFLAKE_PRIVATE_KEY_PATH=
export SNOWFLAKE_DATABASE=
export SNOWFLAKE_SCHEMA=
export SNOWFLAKE_USER=

export SNOWFLAKE_SOURCE_TABLE=${SNOWFLAKE_SOURCE_TABLE}

# Logging and Errors
# export SENTRY_DSN=
export LOGGER_DSN=

# Bulk_upload_feeder specific ------------------------
export APPLICATION_NAME=sme-feed-file-exporter

# Period ID
export PERIOD_ID=

# Processing Mode: US | GB | AGGREGATE
export PROCESS_MODE=US

# Output path (optional - defaults to output/{date})
# export FILE_OUTPUT_PATH=output/sony_stars_monthly_feed_US/sony_stars_monthly_feed_US-{date}

# Whether to make a copy of the source table
export COPY_TABLE=True

# Whether to delete the copied table
export DELETE_COPY=True

# --- SFTP (Optional) -------------------------------------------------
# Enable transfer of files prior to/after archive creation.
export TRANSFER_FILES=No            # Yes/No (mapped internally to boolean)
export TRANSFER_MODE="Individual Files"  # Individual Files, Zip File, or Both
export SFTP_HOST=ftp.sme-dsr.com    # Override if needed
export SFTP_USER=FTP_Orchard        # Jenkins parameter
export SFTP_FOLDER=monthly          # Remote directory (no leading slash)
export SFTP_PASSWORD=****           # Inject via Jenkins Credentials / secret
export SFTP_RETRIES=3               # Retry attempts after initial
export SFTP_RETRY_BACKOFF_SEC=2     # Backoff base seconds
export SFTP_TIMEOUT_SEC=30          # Socket/connect timeout
export SFTP_STRICT_HOST_KEY=True    # Use RejectPolicy (False for dev only)
export SFTP_CONTINUE_ON_ERROR=False # Continue despite failures
export SFTP_MOCK=False              # Enable in dev to avoid real network (mock client)
export SFTP_PROMPT_PASSWORD=False   # Prompt interactively for password if missing (DEV/LOCAL interactive only)

# Dev: Local SFTP test server
# Set `SFTP_HOST=localhost` to run against an ephemeral local SFTP server
# (started via dev helper/fixture). Credentials follow `SFTP_USER`/`SFTP_PASSWORD`.
# Never run this in CI/prod.

```

## CLI Flags Reference

For local runs you can supply convenience flags instead of exporting env vars:

```bash
python feed_file_exporter.py \
    --enable-sftp \
    --mock-sftp \
    --prompt-sftp-password \
    --sftp-password 'your_pw_here'
```

**Flags:**

- `--enable-sftp` - Force SFTP transfer regardless of `TRANSFER_FILES` env variable
- `--mock-sftp` - Use in-memory mock client (no real network connection)
- `--prompt-sftp-password` - Prompt interactively for password if missing (DEV/LOCAL only)
- `--sftp-password` - Inject password directly (DEV only, never commit real secrets)
- `--clear-local-sftp` - Clear `tmp/local_sftp_root` before run (DEV only)

**Order of precedence:** CLI flags override environment variables for the current process only.

**📖 See:** [CLI Reference](docs/CLI_REFERENCE.md) for detailed documentation and more examples

## Interactive Prompts (Missing Required Fields)

When running locally in an interactive terminal (TTY) with `ENVIRONMENT` set to `DEV`, `LOCAL`, or left blank, the script will prompt for missing critical variables:

Required variables (prompted when missing):

- `SNOWFLAKE_SOURCE_TABLE`
- `PERIOD_ID`

Secrets (prompted securely if missing in interactive dev):

- `SNOWFLAKE_KEY_PASSPHRASE`
- `SNOWFLAKE_PASSWORD` (if you choose password auth instead of key)

Secret prompts use hidden input (no echo). Leave blank to skip; operations needing them will fail fast.


If you leave a prompt blank the variable remains unset and downstream operations may fail fast (e.g., temp table creation). To disable prompting set `ENVIRONMENT` to a non-dev value or run in a non-interactive context (CI). Prompts run after CLI flag parsing but before any Snowflake operations.

## Development Setup (UV)

**⚠️ CRITICAL: See [Development Workflow Guide](docs/DEVELOPMENT_WORKFLOW.md) for comprehensive tooling requirements**

**Local Tooling Requirements:**
- **Python**: ALWAYS use `.venv/bin/python` (NOT system python)
- **Git**: ALWAYS prefix with `export GIT_EDITOR=true &&` to prevent editors
- See full guide for Node.js, Ruby, Go, and other toolchains

```bash
# Install uv if not present
curl -LsSf https://astral.sh/uv/install.sh | sh

# Create/refresh local venv at .venv with Python 3.11.2
uv venv --python 3.11.2 --clear

# Activate the venv (zsh/macOS)
source .venv/bin/activate

# Sync project dependencies (installs from pyproject/lock or requirements)
uv sync

# Optional: install dev extras
uv pip install -r requirements-dev.txt
```

**Notes:**

- If `.venv` already exists, use `uv venv --python 3.11.2 --clear` or set `UV_VENV_CLEAR=1` to replace without prompt.
- `uv sync` will install dependencies defined in `pyproject.toml` and `uv.lock` if present; it also respects `requirements.txt`.

## Running Tests (UV)

**⚠️ ALWAYS use local python executable:**

```bash
# Activate venv
source .venv/bin/activate

# Use venv python directly (REQUIRED)
.venv/bin/python -m pytest -q

# With coverage
.venv/bin/python -m pytest --cov=.

# Alternative: UV runner (if available)
uvx pytest -q
```

Alternatively, using UV runner:

```bash
uv run pytest -q
```

## Linting & Formatting (UV)

**⚠️ ALWAYS use local python executable:**

```bash
# Activate venv
source .venv/bin/activate

# Use venv python (REQUIRED - must show 0 errors)
.venv/bin/python -m flake8 . --config=.flake8 --count

# Alternative: UV runner
uvx flake8
```

You can also run other tools via UVX, e.g. `uvx ruff`, `uvx black`.

## Additional Documentation

For more detailed information, see:

- **[Complete Execution Guide](docs/EXECUTION_GUIDE.md)** - Comprehensive guide to all execution methods with workflows and decision tree
- **[CLI Reference](docs/CLI_REFERENCE.md)** - Complete command-line flags documentation with examples
- **[Jenkins Guide](docs/JENKINS_GUIDE.md)** - Jenkins job setup, parameters, and troubleshooting
- **[Streamlit Interface Guide](docs/streamlit_interface_guide.md)** - Complete web UI walkthrough with screenshots
- **[SFTP Transfer Module](docs/sftp_transfer.md)** - Technical documentation for the SFTP module
- **[Test Mode Guide](docs/test_mode_guide.md)** - Using local SFTP server for testing
- **[Session Management Guide](docs/session_management_guide.md)** - Run → Deliver workflow documentation