# Organization Database Configuration

## Overview

This document describes the configuration and setup for the organization database system in the MyDesk Core API.

## Environment Variables

Add these variables to your `.env` file:

```bash
# Main Database Configuration
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mydesk_core
DB_USERNAME=root
DB_PASSWORD=your_password

# Organization Database Configuration
DB_HOST_ORG=127.0.0.1
DB_PORT_ORG=3306
DB_DATABASE_ORG=mydesk
DB_USERNAME_ORG=root
DB_PASSWORD_ORG=your_password

# Database Security Settings
DB_SSL_ENABLED=false
MYSQL_ATTR_SSL_CA=

# Organization Settings
ORG_LOGO_BASE_URL=/storage/organizations
ORG_DATABASE_PREFIX=mydesk_

# Performance Settings
DB_CONNECTION_POOL_SIZE=10
DB_CONNECTION_TIMEOUT=30
DB_QUERY_TIMEOUT=60

# Monitoring Settings
HEALTH_CHECK_CACHE_TTL=300
HEALTH_CHECK_ENABLED=true

# Backup Settings
BACKUP_ENABLED=true
BACKUP_RETENTION_DAYS=30
BACKUP_STORAGE_PATH=storage/backups

# Rate Limiting
DB_RATE_LIMIT_ENABLED=true
DB_RATE_LIMIT_MAX_ATTEMPTS=100
DB_RATE_LIMIT_WINDOW=60

# Logging
DB_QUERY_LOG_ENABLED=false
DB_SLOW_QUERY_THRESHOLD=1000
```

## Database Setup

### 1. Main Database
The main database stores organization metadata and user information.

### 2. Organization Databases
Each organization gets its own database with the naming convention: `mydesk_{orgcode}`

### 3. Required Tables
Each organization database must have these tables:
- `company` - Organization information
- `user` - Organization users
- `role` - User roles
- `settings` - Organization settings
- `migrations` - Migration tracking
- `{orgcode}_api_tokens` - API tokens for the organization

## Security Considerations

1. **Database Credentials**: Never hardcode database credentials
2. **Connection Limits**: Implement rate limiting for database connections
3. **SSL/TLS**: Enable SSL for production environments
4. **Access Control**: Use dedicated database users with minimal privileges
5. **Audit Logging**: Log all database operations

## Performance Optimization

1. **Connection Pooling**: Use connection pooling for better performance
2. **Query Optimization**: Monitor and optimize slow queries
3. **Indexing**: Ensure proper indexing on frequently queried columns
4. **Caching**: Implement caching for frequently accessed data

## Monitoring

Use the health check command to monitor organization databases:

```bash
# Check specific organization
php artisan organization:health-check {orgcode}

# Check all organizations
php artisan organization:health-check --all

# Check only organizations with issues
php artisan organization:health-check --all --issues-only

# Export results as JSON
php artisan organization:health-check --all --format=json
```

## Backup Strategy

1. **Automated Backups**: Set up automated daily backups
2. **Retention Policy**: Keep backups for 30 days
3. **Testing**: Regularly test backup restoration
4. **Encryption**: Encrypt backup files

## Troubleshooting

### Common Issues

1. **Connection Timeout**: Check network connectivity and database server status
2. **Migration Failures**: Verify migration files and database permissions
3. **Performance Issues**: Check query performance and database size
4. **Permission Errors**: Verify database user permissions

### Debug Commands

```bash
# Test database connection
php artisan test:database-connection {orgcode}

# Test organization creation
php artisan test:organization-creation --name="Test Org" --email="test@example.com"

# Check migration status
php artisan migrate:status --database=mydesk_{orgcode}
```

## Best Practices

1. **Naming Convention**: Use consistent naming for databases and tables
2. **Error Handling**: Implement comprehensive error handling
3. **Logging**: Log all important operations
4. **Testing**: Write comprehensive tests for all database operations
5. **Documentation**: Keep documentation up to date
6. **Version Control**: Track all database schema changes
7. **Rollback Strategy**: Always have a rollback plan for migrations