rusty-petroleum/README.md

# rusty-petroleum

A petroleum transaction invoice generator with MariaDB backend.

## Overview

This project processes petroleum/fuel station transaction data from CSV files and generates customer invoices. It stores transaction data in MariaDB for both invoicing and sales reporting.

## Features

- **CSV Import**: Import transaction data from fuel station CSV files into MariaDB
- **Invoice Generation**: Generate HTML invoices from CSV data (file-to-file mode)
- **Multi-Environment**: Separate databases for development, testing, and production
- **Sales Reporting**: Query transactions by customer, product, date range
- **Test-Driven Development**: Comprehensive test suite with 45 tests

## Project Structure

```
rusty-petroleum/
├── Cargo.toml              # Rust dependencies
├── Cargo.lock              # Locked dependency versions
├── config.example.toml     # Config template
├── migrations/            # SQL schema files
│   └── 002_schema.sql    # Current schema
├── input/                  # CSV input files
├── output/                 # Generated invoices
├── src/
│   ├── lib.rs            # Library crate (for testing)
│   ├── main.rs           # CLI entry point
│   ├── config.rs         # Configuration loading
│   ├── db/               # Database layer
│   │   ├── mod.rs
│   │   ├── connection.rs
│   │   ├── models.rs
│   │   └── repository.rs
│   ├── commands/         # CLI commands
│   │   ├── mod.rs
│   │   ├── db.rs         # db setup/reset
│   │   └── import.rs     # CSV import
│   └── invoice_generator.rs
├── templates/             # HTML invoice templates
│   ├── index.html
│   └── customer.html
└── tests/                # Integration tests
    ├── common/           # Test utilities
    │   ├── mod.rs
    │   ├── fixtures.rs
    │   └── test_db.rs
    ├── config_test.rs   # Config module tests
    ├── import_test.rs    # CSV parsing tests
    ├── models_test.rs    # Model tests
    └── repository_test.rs # Database tests
```

## Database Schema

### customers
| Column | Type | Description |
|--------|------|-------------|
| id | INT UNSIGNED | Primary key |
| customer_number | VARCHAR | Unique customer identifier |
| card_report_group | TINYINT UNSIGNED | Customer classification (1=fleet, 3/4=retail) |
| created_at | TIMESTAMP | Record creation time |
| updated_at | TIMESTAMP | Last update time |

### cards
| Column | Type | Description |
|--------|------|-------------|
| id | INT UNSIGNED | Primary key |
| card_number | VARCHAR | Unique card identifier |
| customer_id | INT UNSIGNED | FK to customers |
| created_at | TIMESTAMP | Record creation time |
| updated_at | TIMESTAMP | Last update time |

### transactions
| Column | Type | Description |
|--------|------|-------------|
| id | BIGINT UNSIGNED | Primary key |
| transaction_date | DATETIME | Transaction timestamp |
| batch_number | VARCHAR | Batch identifier |
| amount | DECIMAL(10,2) | Transaction amount |
| volume | DECIMAL(10,3) | Volume in liters |
| price | DECIMAL(8,4) | Price per liter |
| quality_code | INT | Product code |
| quality_name | VARCHAR | Product name (95 Oktan, Diesel) |
| card_number | VARCHAR | Card used (including anonymized) |
| station | VARCHAR | Station ID |
| terminal | VARCHAR | Terminal ID |
| pump | VARCHAR | Pump number |
| receipt | VARCHAR | Receipt number |
| control_number | VARCHAR | Control/verification number |
| customer_id | INT UNSIGNED | FK to customers (NULL for anonymized) |
| created_at | TIMESTAMP | Record creation time |

## Configuration

Copy the example config and edit with your database credentials:

```bash
cp config.example.toml config.dev.toml  # or config.test.toml or config.prod.toml
```

Edit `config.dev.toml`:
```toml
[database]
host = "localhost"
port = 3306
user = "your_user"
password = "your_password"
name = "rusty_petroleum_dev"
```

### Environment Config Loading

Config files are loaded in order:
1. `config.toml` (local override, gitignored)
2. `config.<env>.toml` (environment-specific, gitignored)
3. `config.example.toml` (fallback, tracked)

### Database Names by Environment

- `rusty_petroleum_dev` - Development
- `rusty_petroleum_test` - Testing
- `rusty_petroleum_prod` - Production

## Commands

```bash
# Database management
cargo run -- db setup --env <dev|test|prod>   # Create database and schema
cargo run -- db reset --env <dev|test|prod>   # Drop and recreate database

# Import data
cargo run -- import <csv-file> --env <dev|test|prod>   # Import to database (default: prod)

# Generate invoices (file-to-file, no database)
cargo run -- generate <csv-file> <output-dir>
```

### Usage Examples

```bash
# Setup development database
cargo run -- db setup --env dev

# Import transactions to dev database
cargo run -- import input/409.csv --env dev

# Reset development database
cargo run -- db reset --env dev

# Generate HTML invoices from CSV
cargo run -- generate input/409.csv output/
```

## Testing

The project has a comprehensive test suite with 45 tests covering config, CSV parsing, models, and database operations.

```bash
# Run all tests (lib + integration)
cargo test

# Run only lib/unit tests (fast, no database needed)
cargo test --lib

# Run only integration tests (requires test database)
cargo test --tests

# Run a specific test file
cargo test --test config_test
cargo test --test import_test
cargo test --test repository_test

# Run a specific test
cargo test customer_insert_returns_id

# Run tests in release mode
cargo test --release
```

### Test Database Setup

Repository tests require a test database. Run setup before testing:

```bash
cargo run -- db setup --env test
```

## Production Build

Build an optimized binary for production:

```bash
# Build release binary
cargo build --release

# Run the binary
./target/release/invoice-generator db setup --env prod
./target/release/invoice-generator import data.csv --env prod
```

## Current Status

### Implemented
- [x] Database schema for transactions, customers, cards
- [x] CSV import to MariaDB
- [x] Multi-environment support (dev/test/prod)
- [x] Configuration via TOML files
- [x] Invoice generation (HTML output)
- [x] Database setup/reset commands
- [x] Unit tests (45 tests)

### TODO
- [ ] Sales reporting queries (dashboard/API)
- [ ] Customer invoice retrieval from database
- [ ] Batch import across multiple CSV files
- [ ] CI/CD pipeline

## Technology Stack

- **Language**: Rust (Edition 2021)
- **Database**: MariaDB
- **ORM**: sqlx (async MySQL)
- **Templating**: Askama (HTML templates)
- **Config**: TOML
- **Testing**: tokio-test, tempfile

## Getting Started

1. Install Rust (if not already installed)
   ```bash
   curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
   ```

2. Create database user and grant permissions in MariaDB
   ```sql
   CREATE USER 'your_user'@'%' IDENTIFIED BY 'your_password';
   GRANT ALL PRIVILEGES ON rusty_petroleum_dev.* TO 'your_user'@'%';
   CREATE DATABASE rusty_petroleum_dev;
   ```

3. Setup configuration
   ```bash
   cp config.example.toml config.dev.toml
   # Edit config.dev.toml with your credentials
   ```

4. Setup database and import data
   ```bash
   cargo run -- db setup --env dev
   cargo run -- import input/409.csv --env dev
   ```

5. Run tests
   ```bash
   cargo test --lib        # Unit tests (fast)
   cargo test --tests      # Integration tests (requires DB)
   ```

## License

See LICENSE file.