infoscreen/DATABASE_GUIDE.md

# 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 (check with `alembic current` in `server/`)
- **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
- **`event_exceptions`** - Overrides and skips for recurring events (per occurrence)
- **`system_settings`** - Key–value store for global settings
- **`alembic_version`** - Migration tracking

### Key details and relationships

- Users (`users`)
  - Fields: `username` (unique), `password_hash`, `role` (enum: user|editor|admin|superadmin), `is_active`

- Client groups (`client_groups`)
  - Fields: `name` (unique), `description`, `is_active`

- Clients (`clients`)
  - Fields: `uuid` (PK), network/device metadata, `group_id` (FK→client_groups, default 1), `last_alive` (updated on heartbeat), `is_active`

- Academic periods (`academic_periods`)
  - Fields: `name` (unique), optional `display_name`, `start_date`, `end_date`, `period_type` (enum: schuljahr|semester|trimester), `is_active` (at most one should be active)
  - Indexes: `is_active`, dates

- Event media (`event_media`)
  - Fields: `media_type` (enum, see below), `url`, optional `file_path`, optional `message_content`, optional `academic_period_id`
  - Used by events of types: presentation, video, website, message, other

- Events (`events`)
  - Core: `group_id` (FK), optional `academic_period_id` (FK), `title`, optional `description`, `start`, `end`, `event_type` (enum), optional `event_media_id` (FK)
  - Presentation/video extras: `autoplay`, `loop`, `volume`, `slideshow_interval`, `page_progress`, `auto_progress`
  - Recurrence: `recurrence_rule` (RFC 5545 RRULE), `recurrence_end`, `skip_holidays` (bool)
  - Audit/state: `created_by` (FK→users), `updated_by` (FK→users), `is_active`
  - Indexes: `start`, `end`, `recurrence_rule`, `recurrence_end`
  - Relationships: `event_media`, `academic_period`, `exceptions` (one-to-many to `event_exceptions` with cascade delete)

- Event exceptions (`event_exceptions`)
  - Purpose: track per-occurrence skips or overrides for a recurring master event
  - Fields: `event_id` (FK→events, ondelete CASCADE), `exception_date` (Date), `is_skipped`, optional overrides (`title`, `description`, `start`, `end`)

- School holidays (`school_holidays`)
  - Unique: (`name`, `start_date`, `end_date`, `region`)
  - Used in combination with `events.skip_holidays`

- Conversions (`conversions`)
  - Purpose: track PPT/PPTX/ODP → PDF processing
  - Fields: `source_event_media_id` (FK→event_media, ondelete CASCADE), `target_format`, `target_path`, `status` (enum), `file_hash`, timestamps, `error_message`
  - Indexes: (`source_event_media_id`, `target_format`), (`status`, `target_format`)
  - Unique: (`source_event_media_id`, `target_format`, `file_hash`) — idempotency per content

- System settings (`system_settings`)
  - Key–value store: `key` (PK), `value`, optional `description`, `updated_at`
  - Notable keys used by the app: `presentation_interval`, `presentation_page_progress`, `presentation_auto_progress`

### Enums (reference)

- UserRole: `user`, `editor`, `admin`, `superadmin`
- AcademicPeriodType: `schuljahr`, `semester`, `trimester`
- EventType: `presentation`, `website`, `video`, `message`, `other`, `webuntis`
- MediaType: `pdf`, `ppt`, `pptx`, `odp`, `mp4`, `avi`, `mkv`, `mov`, `wmv`, `flv`, `webm`, `mpg`, `mpeg`, `ogv`, `jpg`, `jpeg`, `png`, `gif`, `bmp`, `tiff`, `svg`, `html`, `website`
- ConversionStatus: `pending`, `processing`, `ready`, `failed`

### Timezones, recurrence, and holidays

- All timestamps are stored/compared as timezone-aware UTC. Any naive datetimes are normalized to UTC before comparisons.
- Recurrence is represented on events via `recurrence_rule` (RFC 5545 RRULE) and `recurrence_end`. Do not pre-expand series in the DB.
- Per-occurrence exclusions/overrides are stored in `event_exceptions`. The API also emits EXDATE tokens matching occurrence start times (UTC) so the frontend can exclude instances natively.
- When `skip_holidays` is true, occurrences that fall on school holidays are excluded via corresponding `event_exceptions`.

### 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.