sandbox-reports/README.md

# AI Tools Usage Report System

Automated reporting system for AI tool usage analytics with Microsoft Azure AD Single Sign-On. Track usage across multiple AI tools including VEO3 video generation, text-to-image, text-to-voice, and more.

## Features

- **Multi-Tool Support**: Track usage for 6+ AI tools:
  - VEO3 Video Generation
  - Text to Image (Dalle, SD3, Leo, etc.)
  - Text to Voice (ElevenLabs)
  - Speech to Speech
  - Document Translation
  - Video Query Analysis
- **Dynamic Tool Detection**: Automatically adapts to new tool types
- **PHP Web Interface**: Interactive dashboard with tool filtering
- **SSO Authentication**: Microsoft Azure AD (MSAL) integration for secure access
- **Python Automation**: Automated daily/weekly/monthly email reports via SMTP
- **Granular Analytics**:
  - Last 24 hours activity
  - Last 7 days trends
  - Last 30 days overview
  - Tool usage breakdown
  - Top 25 users per period
- **Professional Styling**: Montserrat font, custom yellow (#FFC407) branding

## Installation

### Prerequisites

- **PHP**: 7.4 or higher
- **Python**: 3.7 or higher
- **Composer**: For PHP dependency management
- **Web Server**: Apache or Nginx (for production) or PHP built-in server (for development)

### Step 1: Clone the Repository

```bash
git clone git@bitbucket.org:zlalani/sandbox-reports.git
cd sandbox-reports
```

### Step 2: Install PHP Dependencies

```bash
composer install
```

This installs:
- Microsoft Authentication Library (MSAL)
- Firebase JWT for token validation
- GuzzleHTTP for API requests

### Step 3: Install Python Dependencies

```bash
pip install -r requirements.txt
```

This installs:
- `requests` - For webhook API calls
- `python-dotenv` - For environment variable management
- `schedule` - For automated task scheduling
- `pytz` - For timezone handling

### Step 4: Configure Environment Variables

```bash
cp .env.example .env
nano .env  # or use your preferred editor
```

Required configuration:

```bash
# Webhook Configuration
WEBHOOK_URL=https://hook.us1.make.celonis.com/your-webhook-id

# SMTP Settings (for email reports)
SMTP_SERVER=smtp.mailgun.org
SMTP_PORT=587
SMTP_USER=your-smtp-username
SMTP_PASSWORD=your-smtp-password
SENDER_EMAIL=reports@yourdomain.com

# Email Recipients (comma-separated)
REPORT_RECIPIENTS=user1@example.com,user2@example.com

# SSO Configuration (see SSO-SETUP.md for details)
SSO_ENABLED=true
AZURE_TENANT_ID=your-tenant-id
AZURE_CLIENT_ID=your-client-id
REDIRECT_URI=http://localhost:8000/auth.php

# Session Configuration
SESSION_SECRET=your-random-secret-key-here
SESSION_LIFETIME=86400
```

### Step 5: Set Up SSO (Optional)

For production deployments with authentication:

1. Follow the complete guide in [SSO-SETUP.md](SSO-SETUP.md)
2. Register your application in Azure AD
3. Configure redirect URIs
4. Set `SSO_ENABLED=true` in `.env`

For development without SSO:

```bash
# In .env
SSO_ENABLED=false
```

### Step 6: Set Up Directory Permissions

```bash
# Create logs directory
mkdir -p logs
chmod 755 logs

# Ensure PHP can write webhook response
chmod 755 .
```

### Step 7: Start the Application

#### For Development (Local)

```bash
# Start PHP built-in server
php -S localhost:8000
```

Access the application:
- **Fetch Data**: http://localhost:8000/webhook_caller.php
- **View Report**: http://localhost:8000/report.php

#### For Production (Apache/Nginx)

See [DEPLOYMENT.md](DEPLOYMENT.md) for complete production deployment instructions.

## Usage

### Web Interface

1. **Fetch Data from Webhook**
   - Navigate to `webhook_caller.php`
   - Select date range (defaults to last 12 weeks)
   - Click "Fetch Data"
   - Data is saved to `webhook_response.json`

2. **View Reports**
   - Navigate to `report.php`
   - View comprehensive analytics dashboard
   - Filter by specific AI tools using the filter dropdown
   - Use "Refresh Data" button to update from webhook

### Python Email Reports

Generate and send email reports manually:

```bash
python veo3_report.py
```

The script will:
1. Fetch data from the webhook
2. Analyze usage for 24h, 7d, and 30d periods
3. Generate HTML report (`email_report.html`)
4. Send email to configured recipients

### Automated Reporting

#### Production Server (Systemd Service - Recommended)

```bash
# Deploy to server
scp veo3_report.py veo3_scheduler.py requirements.txt .env \
    veo3-report.service deploy.sh user@your-server:/tmp/veo3-deploy/

# Run deployment script
ssh user@your-server
cd /tmp/veo3-deploy
chmod +x deploy.sh
sudo ./deploy.sh
```

The service runs daily at 7:00 PM EST and includes:
- ✅ Auto-restart on failure
- ✅ Starts on server boot
- ✅ Logging with journalctl
- ✅ Status monitoring

See [DEPLOYMENT.md](DEPLOYMENT.md) for full details.

#### Alternative: Cron Job

```bash
# Edit crontab
crontab -e

# Add line for daily report at 9 AM
0 9 * * * cd /path/to/sandbox-reports && /usr/bin/python3 veo3_report.py
```

## Configuration

### Tool Costs

You can adjust estimated costs per tool in both files:

**Python** (`veo3_report.py`):
```python
TOOL_COSTS = {
    'VEO3': 3.20,
    'TEXT2IMAGE': 0.04,
    'TEXT2VOICE': 0.30,
    'SPEECH2SPEECH': 0.50,
    'DOCUMENT_TRANSLATION': 0.10,
    'VIDEOQUERY': 0.25,
}
```

**PHP** (`report.php`):
```php
$toolCosts = [
    'VEO3' => 3.20,
    'TEXT2IMAGE' => 0.04,
    'TEXT2VOICE' => 0.30,
    'SPEECH2SPEECH' => 0.50,
    'DOCUMENT_TRANSLATION' => 0.10,
    'VIDEOQUERY' => 0.25,
];
```

### Tool Display Names

Customize how tool names appear in reports:

**Python** (`veo3_report.py`):
```python
TOOL_NAMES = {
    'VEO3': 'VEO3 Video Generation',
    'TEXT2IMAGE': 'Text to Image',
    # ... add more
}
```

**PHP** (`report.php`):
```php
$toolNames = [
    'VEO3' => 'VEO3 Video Generation',
    'TEXT2IMAGE' => 'Text to Image',
    // ... add more
];
```

## Project Structure

```
sandbox-reports/
├── README.md                    # This file
├── .env.example                # Environment variable template
├── .env                        # Your configuration (not in git)
├── .gitignore                  # Git ignore rules
├── .htaccess                   # Apache security & configuration
│
├── Web Interface
│   ├── report.php              # Main dashboard with filtering
│   ├── webhook_caller.php      # Data fetching interface
│   ├── config.php              # PHP configuration
│   ├── env_loader.php          # Environment loader
│   └── webhook_response.json   # Cached data (auto-generated)
│
├── SSO Authentication
│   ├── AuthMiddleware.php      # Authentication middleware
│   ├── JWTValidator.php        # JWT token validation
│   ├── auth.php                # Auth API endpoint
│   ├── composer.json           # PHP dependencies
│   └── SSO-SETUP.md           # SSO setup guide
│
├── Python Reports
│   ├── veo3_report.py          # Email report generator
│   ├── veo3_scheduler.py       # Scheduling daemon
│   ├── requirements.txt        # Python dependencies
│   └── email_report.html       # Generated report (auto-created)
│
└── Deployment
    ├── deploy.sh               # Deployment script
    ├── veo3-report.service     # Systemd service file
    ├── DEPLOYMENT.md           # Deployment guide
    └── QUICKSTART.md          # Quick start guide
```

## Features in Detail

### Tool Filtering

The web interface allows filtering by specific AI tools:
- Select "All Tools" to see complete usage
- Select individual tools to focus on specific usage
- Filter applies to all user tables and statistics

### Dynamic Tool Detection

The system automatically handles new tool types:
- Unknown tools are displayed with their raw name
- Cost defaults to $0 for undefined tools
- No code changes needed when new tools are added

### Multi-Period Analytics

Each report shows three time periods:
- **Last 24 Hours**: Recent activity snapshot
- **Last 7 Days**: Weekly trends
- **Last 30 Days**: Monthly overview

Each period includes:
- Total requests and costs
- Unique user count
- Top 25 most active users
- Activity distribution

### Performance Optimization

- Refresh button fetches only last 12 weeks (not all data)
- Efficient data caching with `webhook_response.json`
- Responsive design for mobile viewing
- Collapsible sections to reduce page load

## Troubleshooting

### PHP Issues

**Problem**: "Class 'Dotenv' not found"
```bash
# Solution: Install composer dependencies
composer install
```

**Problem**: "Permission denied" writing webhook_response.json
```bash
# Solution: Fix directory permissions
chmod 755 .
chmod 666 webhook_response.json  # if file exists
```

### Python Issues

**Problem**: "ModuleNotFoundError: No module named 'dotenv'"
```bash
# Solution: Install Python dependencies
pip install -r requirements.txt
```

**Problem**: Email not sending
```bash
# Solution: Check SMTP credentials in .env
# Test with:
python veo3_report.py
# Check console output for specific error
```

### SSO Issues

**Problem**: Redirect URI mismatch
- Ensure Azure AD app registration matches exactly
- Check REDIRECT_URI in .env
- See [SSO-SETUP.md](SSO-SETUP.md) for complete guide

### Data Issues

**Problem**: No data showing in reports
```bash
# Solution: Fetch data from webhook first
# 1. Visit webhook_caller.php
# 2. Click "Fetch Data"
# 3. Verify webhook_response.json was created
```

## Development

### Adding New Tool Types

The system is designed to be extensible. To add a new tool type:

1. Add to tool costs configuration (optional):
```python
# Python
TOOL_COSTS['NEW_TOOL'] = 0.50

# PHP
$toolCosts['NEW_TOOL'] = 0.50;
```

2. Add display name (optional):
```python
# Python
TOOL_NAMES['NEW_TOOL'] = 'New Tool Name'

# PHP
$toolNames['NEW_TOOL'] = 'New Tool Name';
```

If you don't add the tool, it will still be tracked and displayed with its raw name and $0 cost.

## Security

### Application Security
- **SSO Authentication**: Microsoft Azure AD integration
- **Session Management**: Secure cookies with httponly and samesite flags
- **JWT Token Validation**: Cryptographic verification of tokens
- **Environment Variables**: Sensitive data stored in `.env` (not in git)
- **HTTPS**: Strongly recommended for production (force HTTPS in .htaccess)

### Apache Security (.htaccess)
The included `.htaccess` file provides basic security:

- **Directory Browsing Disabled**: Prevents listing of files
- **Default Document**: Sets `report.php` as the default page
- **Environment File Protection**: Blocks access to `.env` files
- **Git Directory Protection**: Blocks access to `.git` directory

## License

Internal use only.

## Support

For issues or questions, contact your system administrator or refer to:
- [SSO-SETUP.md](SSO-SETUP.md) - SSO configuration
- [DEPLOYMENT.md](DEPLOYMENT.md) - Production deployment
- [QUICKSTART.md](QUICKSTART.md) - Quick start guide