# Data Access Layer (DAL) - SQLite and JSON Toggle

The application now supports switching between **SQLite** and **JSON** data storage through a simple configuration toggle.

## Configuration

Open `app/config.py` and set:

```python
# Set to True for SQLite, False for JSON files
USE_DATABASE = True  # or False
```

## Architecture

### Modules

1. **config.py** - Application configuration
   - `USE_DATABASE` - Toggle between SQLite and JSON
   - Database URI and other settings

2. **data_access_sqlite.py** - SQLite implementation
   - Uses SQLAlchemy models
   - Supports transactions and rollback

3. **data_access_json.py** - JSON file implementation
   - Thread-safe file operations
   - Immediate writes (no transactions)

4. **data_access.py** - Universal wrapper
   - Automatically imports correct module based on config
   - Provides unified interface to all blueprints

### Standardized Functions

Both modules implement the same functions:

**User Operations:**
- `get_user_by_username(username)`
- `get_user_by_id(user_id)`
- `get_all_users()`
- `create_user(user_data)`
- `update_user(user_id, user_data)`
- `delete_user(user_id)`
- `toggle_user_active(user_id, active)`
- `change_user_password(user_id, new_password)`

**Product Operations:**
- `get_all_products(location=None)`
- `get_product_by_code(product_code)`
- `create_product(product_data)`
- `update_product(product_code, product_data)`
- `delete_product(product_code)`

**Location Operations:**
- `get_all_locations()`
- `get_location_by_code(code)`

**Product Attributes:**
- `get_product_attributes()`
- `update_product_attributes(attributes_data)`

**Utilities:**
- `commit()` - Commit transaction (no-op for JSON)
- `rollback()` - Rollback transaction (no-op for JSON)

## Usage in Blueprints

All blueprints now import from the data access layer:

```python
import data_access as da

# Get user
user = da.get_user_by_username('Master')

# Get products
products = da.get_all_products(location='IOLA')

# Create product
product = da.create_product(product_data)
```

## Switching Between Modes

### To Use SQLite:

1. Set `USE_DATABASE = True` in `config.py`
2. Run migration: `python migrate_to_sqlite.py`
3. Start app: `python app.py`

### To Use JSON:

1. Set `USE_DATABASE = False` in `config.py`
2. Ensure JSON files exist in `data/` folder
3. Start app: `python app.py`

## Key Differences

| Feature | SQLite | JSON |
|---------|--------|------|
| **Transactions** | Yes | No (immediate writes) |
| **Concurrency** | Better | File locks |
| **Performance** | Faster for large datasets | Faster for small datasets |
| **Backup** | Single .db file | Multiple .json files |
| **Queries** | Complex SQL queries | Load entire file |
| **User IDs** | Database auto-increment | Array index |

## Important Notes

### User IDs
- **SQLite**: Uses database-generated IDs
- **JSON**: Uses array index (changes if users deleted)

### Session Management
- Both modes use username-based sessions (not user_id)
- Session keys: `username`, `currentLocation`, `accessibleLocations`

### Data Consistency
- **SQLite**: ACID compliant with rollback support
- **JSON**: No rollback - writes are immediate and final

### File Serving
- The `/quiz/data/<filename>` route still serves static JSON files
- When using SQLite, these JSON files may become stale
- Consider using API endpoints instead for dynamic data

## Migration

To migrate existing JSON data to SQLite:

```bash
cd app
python migrate_to_sqlite.py
```

This will:
- Create `data/products.db`
- Backup JSON files to `data/json_backup/`
- Migrate users, products, locations, and attributes
- Skip duplicates on subsequent runs

## Testing

Test both modes:

```bash
# Test with SQLite
# Set USE_DATABASE = True in config.py
python app.py

# Test with JSON
# Set USE_DATABASE = False in config.py
python app.py
```

## Troubleshooting

**"Import 'flask' could not be resolved"**
- This is just an editor warning, not a runtime error
- Install dependencies: `pip install -r requirements.txt`

**Data not updating**
- Check `USE_DATABASE` setting in config.py
- Verify correct data files exist (`.db` or `.json`)
- Check terminal for data access layer mode message

**User not found after switching modes**
- User IDs differ between SQLite and JSON
- Clear browser cookies/session
- Re-login to create new session