Database Migration & Data Upload Guide

This project uses Flask-Migrate (powered by Alembic) for managing database schema changes and a custom Flask CLI command for uploading XLS data.

Migration Workflow

Follow these steps to manage database schema and upload data:

Set Flask App Environment Variable: Open your terminal in the project root (digifi-Analytics/) and set the FLASK_APP variable:
```
# For Windows
set FLASK_APP=run.py

# For macOS/Linux
export FLASK_APP=run.py
```
Initialize Migration Repository (First-time setup only): This command sets up the migrations/ directory structure. You've likely already run this, so you might get a "Directory already exists" message, which is fine.
```
flask --app run.py db init
```
Generate a New Migration Script: After making changes to salary_analytics/app/models.py (e.g., adding a new table or column), use this command to create a migration file. This file will contain the upgrade() and downgrade() logic.
```
flask --app run.py db migrate -m "Descriptive message for your changes"
```
Review the Generated Migration: Open the newly created .py file in migrations/versions/ (e.g., XXXXXXXXXXXX_your_message.py) and review the upgrade() and downgrade() functions. Ensure they accurately reflect your intended schema changes.
Apply Migrations to the Database: This command executes the pending migration scripts, updating your database schema. Always apply migrations after generating them.
```
flask --app run.py db upgrade
```

Once your analytics_raw_transactions table is created via migration, you can upload your XLS files using the custom command:

flask --app run.py upload-xls <path_to_your_xls_file>

Example:

flask --app run.py upload-xls data/transactions.xls