# Database Initialization and Management Guide

## Quick Start

Your database has been successfully initialized! Here's what you need to know:

### ✅ Current Status
- **Database**: MariaDB 11.2 running in Docker container `infoscreen-db`
- **Schema**: Up to date (Alembic revision: `b5a6c3d4e7f8`)
- **Default Data**: Admin user and client group created
- **Academic Periods**: Austrian school years 2024/25 (active), 2025/26, 2026/27

### 🔐 Default Credentials
- **Admin Username**: `infoscreen_admin`
- **Admin Password**: Check your `.env` file for `DEFAULT_ADMIN_PASSWORD`
- **Database User**: `infoscreen_admin`
- **Database Name**: `infoscreen_by_taa`

## Database Management Commands

### Initialize/Reinitialize Database
```bash
cd /workspace/server
python initialize_database.py
```

### Check Migration Status
```bash
cd /workspace/server
alembic current
alembic history --verbose
```

### Run Migrations Manually
```bash
cd /workspace/server
alembic upgrade head         # Apply all pending migrations
alembic upgrade +1           # Apply next migration
alembic downgrade -1         # Rollback one migration
```

### Create New Migration
```bash
cd /workspace/server
alembic revision --autogenerate -m "Description of changes"
```

### Database Connection Test
```bash
cd /workspace/server
python -c "
from database import Session
session = Session()
print('✅ Database connection successful')
session.close()
"
```

## Initialization Scripts

### Core Scripts (recommended order):
1. **`alembic upgrade head`** - Apply database schema migrations
2. **`init_defaults.py`** - Create default user groups and admin user  
3. **`init_academic_periods.py`** - Set up Austrian school year periods

### All-in-One Script:
- **`initialize_database.py`** - Complete database initialization (runs all above scripts)

### Development/Testing Scripts:
- **`dummy_clients.py`** - Creates test client data for development
- **`dummy_events.py`** - Creates test event data for development  
- **`sync_existing_clients.py`** - One-time MQTT sync for existing clients

## Database Schema Overview

### Main Tables:
- **`users`** - User authentication and roles
- **`clients`** - Registered client devices  
- **`client_groups`** - Client organization groups
- **`events`** - Scheduled events and presentations
- **`event_media`** - Media files for events
- **`conversions`** - File conversion jobs (PPT → PDF)
- **`academic_periods`** - School year/semester management
- **`school_holidays`** - Holiday calendar
- **`alembic_version`** - Migration tracking

### Environment Variables:
```bash
DB_CONN=mysql+pymysql://infoscreen_admin:KqtpM7wmNdM1DamFKs@db/infoscreen_by_taa
DB_USER=infoscreen_admin
DB_PASSWORD=KqtpM7wmNdM1DamFKs
DB_NAME=infoscreen_by_taa
DB_HOST=db
```

## Troubleshooting

### Database Connection Issues:
```bash
# Check if database container is running
docker ps | grep db

# Check database logs
docker logs infoscreen-db

# Test direct connection
docker exec -it infoscreen-db mysql -u infoscreen_admin -p infoscreen_by_taa
```

### Migration Issues:
```bash
# Check current state
cd /workspace/server && alembic current

# Show migration history
cd /workspace/server && alembic history

# Show pending migrations  
cd /workspace/server && alembic show head
```

### Reset Database (⚠️ DESTRUCTIVE):
```bash
# Stop services
docker-compose down

# Remove database volume
docker volume rm infoscreen_2025_db-data

# Restart and reinitialize
docker-compose up -d db
cd /workspace/server && python initialize_database.py
```

## Production Deployment

The production setup in `docker-compose.prod.yml` includes automatic database initialization:

```yaml
server:
  command: >
    bash -c "alembic -c /app/server/alembic.ini upgrade head &&
             python /app/server/init_defaults.py &&
             exec gunicorn server.wsgi:app --bind 0.0.0.0:8000"
```

This ensures the database is properly initialized on every deployment.