# NCD Analytics + Visualization

This repository includes a complete NCD program analytics pipeline and Streamlit dashboard that operate across multiple clinic databases on a remote MySQL server. The pipeline creates reusable SQL views, decrypts encrypted fields using the Laravel app key, and exports CSV outputs for dashboards and reporting.

## What This Adds

- MySQL 8 views for reusable metrics (`metrics_views.sql`)
- Python analytics pipeline (`ncd_analysis.py`)
- Streamlit + Plotly dashboard (`dashboard_app.py`)
- Laravel-based decryptor helper (`tools/laravel_decryptor.php`)
- Config, requirements, and sanity tests

## Architecture

1) **Views** are created per database to standardize joins and basic parsing.
2) **Python** reads the views, decrypts configured columns by calling the Laravel decryptor, computes metrics, and exports CSVs.
3) **Streamlit** reads the CSVs and renders interactive dashboards.

## Setup

```bash
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

## Configuration

Edit `config.yaml`:

- MySQL host/port/user/password
- Database list
- Date range
- Thresholds
- Other medication text grouping (optional)
- Encrypted columns
- Laravel path (root of this repo on the server)
- Privacy options (masking IDs)

Encrypted columns must map to table names as shown in `config.yaml`. Decryption uses the Laravel app key from `.env` and runs inside the app context.

## Run Analysis

```bash
python ncd_analysis.py --config config.yaml
```

Or run via Laravel so it uses your `.env` MySQL credentials:

```bash
php artisan ncd:analyze
```

Outputs go to `outputs/{db_name}/` for each clinic and `outputs/overall/` for the combined dataset.

## Run Dashboard

```bash
streamlit run dashboard_app.py --server.address 0.0.0.0 --server.port 8501
```

## Output Files

Per clinic (and overall) the pipeline writes:

- `monthly_summary.csv`
- `registers_clean.csv`
- `followups_clean.csv`
- `patient_latest.csv`
- `bp_control_trend.csv`
- `dm_control_trend.csv`
- `continuity_metrics.csv`
- `quality_metrics.csv`
- `risk_metrics.csv`
- `operations_metrics.csv`
- `operations_distributions.csv`
- `other_medications.csv`
- `equity_bp_control.csv`
- `equity_dm_control.csv`
- `kpi_summary.csv`
- `data_quality_report.csv`

Doctor action lists (LTFU and missed appointments) are saved at:

- `outputs/{db_name}/doctor/doctor_action_lists.csv`

## Laravel Decryptor

`tools/laravel_decryptor.php` boots the Laravel app and decrypts the configured fields. The Python pipeline sends batches of rows to this script via stdin and receives decrypted rows back via stdout.

## Sanity Tests

```bash
python tests_sanity.py
```

## Deployment Notes

### Nginx reverse proxy (optional)

```nginx
server {
    listen 80;
    server_name your.domain.example;

    location / {
        proxy_pass http://127.0.0.1:8501;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_set_header Host $host;
    }
}
```

Cloudflare Tunnel can also be used to expose the Streamlit port without opening firewall rules.

## Security

- Do not commit or expose `.env` or `APP_KEY`.
- Keep doctor lists restricted to clinical staff.