#!/bin/bash
# Module 5: Databases

cat << 'EOF'
---

## 5️⃣ DATABASES

### Database Storage

**Primary Location:** `/mnt/psql-data/`

All PostgreSQL databases are stored on a dedicated disk for performance and data isolation.

EOF

echo "### Database Directories"
echo ""

if [[ -d /mnt/psql-data ]]; then
    for db_dir in /mnt/psql-data/*; do
        if [[ -d "$db_dir" ]]; then
            DB_NAME=$(basename "$db_dir")
            DB_SIZE=$(du -sh "$db_dir" 2>/dev/null | cut -f1)
            FILE_COUNT=$(find "$db_dir" -type f 2>/dev/null | wc -l)
            LAST_MODIFIED=$(stat -c %y "$db_dir" 2>/dev/null | cut -d' ' -f1)
            
            cat << DBINFO

#### 📁 **$DB_NAME**

- **Path:** \`$db_dir\`
- **Size:** $DB_SIZE
- **Files:** $FILE_COUNT
- **Last Modified:** $LAST_MODIFIED
- **Backup Command:**
  \`\`\`bash
  docker exec postgres-main pg_dump -U aimpress_admin -d <database_name> | \
    gzip > /mnt/backups/${DB_NAME}-\$(date +%Y%m%d).sql.gz
  \`\`\`

DBINFO
        fi
    done
else
    echo "⚠️ Database directory /mnt/psql-data not found"
fi

cat << 'EOF'

### PostgreSQL Management Commands

```bash
# Connect to PostgreSQL
docker exec -it postgres-main psql -U aimpress_admin

# Inside psql:
\l                    # List all databases
\du                   # List all users
\dt                   # List tables in current database
\c database_name      # Connect to database
\q                    # Exit

# Backup single database
docker exec postgres-main pg_dump -U aimpress_admin <database> | \
  gzip > /mnt/backups/db-$(date +%Y%m%d).sql.gz

# Backup ALL databases
docker exec postgres-main pg_dumpall -U aimpress_admin | \
  gzip > /mnt/backups/postgres-all-$(date +%Y%m%d).sql.gz

# Restore database
gunzip < /mnt/backups/backup.sql.gz | \
  docker exec -i postgres-main psql -U aimpress_admin -d <database>

# Check database sizes
docker exec postgres-main psql -U aimpress_admin -c \
  "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) \
   FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;"
```

### Database Users

| Role | Attributes |
|------|------------|
EOF

# List database users in Markdown table format
docker exec postgres-main psql -U aimpress_admin -c "\du" --csv 2>/dev/null | tail -n +2 | while IFS=',' read -r role attrs; do
    # Clean up attributes
    attrs=$(echo "$attrs" | sed 's/"//g')
    [[ -z "$attrs" ]] && attrs="-"
    echo "| **$role** | $attrs |"
done

cat << 'EOF'

### Change Database Password

```bash
# 1. Generate strong password
NEW_PASS=$(openssl rand -base64 32)
echo "New password: $NEW_PASS"

# 2. Change in PostgreSQL
docker exec postgres-main psql -U aimpress_admin -c \
  "ALTER USER <username> WITH PASSWORD '$NEW_PASS';"

# 3. Save to Vault
export VAULT_ADDR="http://127.0.0.1:8200"
export VAULT_TOKEN=$(cat /opt/00-infrastructure/vault/.vault-token)
vault kv put aimpress/postgres/<service> password="$NEW_PASS"

# 4. Update application config
cd /opt/<service-path>
nano docker-compose.yml  # Update DB_PASSWORD or DATABASE_PASSWORD

# 5. Restart application
docker-compose restart
```
EOF