# Stream Tycoon

A data-driven streaming service simulator showcasing SQL and data management skills through an interactive browser-based game. Build your streaming empire, manage content, and compete with Netflix!

## About This Project

Stream Tycoon demonstrates SQL database design and data management through an engaging business simulation. The game runs entirely in the browser using **SQL.js** (SQLite compiled to WebAssembly), showcasing:

- **Normalized Database Schema**: 8 interconnected tables with foreign key relationships
- **Real-time Analytics**: KPIs, trend analysis, and performance metrics
- **Complex SQL Operations**: JOINs, aggregations, subqueries, and prepared statements
- **Business Intelligence**: Subscriber churn modeling, genre trend tracking, and ROI calculations
- **Budget Allocation System**: Genre-specific quality weights with multi-phase production budgets
- **Data Persistence**: SQLite database stored in browser localStorage

## Technologies

- **SQL.js**: Browser-based SQLite implementation (WebAssembly)
- **JavaScript (ES6+)**: Game engine and business logic
- **HTML5/CSS3**: Responsive UI with dark/light theme
- **LocalStorage API**: Database persistence

## How to Play

### Quick Start

1. Open `game.html` in any modern web browser
2. Click **"Start New Game"** to begin
3. Or click **"Continue Game"** to resume saved progress

No installation, no dependencies, no server required!

### Game Mechanics

**Starting Resources:**
- $15,000 cash
- 500 subscribers
- 1 starter show ("Man in the Moon")

**Core Actions:**

1. **Acquire License** - License existing content from studios
   - Immediate availability
   - Temporary (expires after X days)
   - Lower risk, moderate reward

2. **Greenlight Original** - Produce your own content
   - Choose from 3 production scales:
     - **Indie Films** (< $50k): 4 budget tiers, 7-18 days
     - **Standard Films** (< $1M): 4 budget tiers, 30-75 days
     - **Blockbusters** (> $1M): 4 budget tiers, 90-180 days
   - Allocate budget across 9 production categories:
     - Pre-Production: Casting, Writing, Set Design
     - Production: Camera, Lighting, Sound
     - Post-Production: Editing, VFX, Music
   - Genre-specific quality weights (Action values VFX, Romance values Casting)
   - Permanent ownership (no expiration)

3. **Marketing Campaigns** - Attract new subscribers
   - Small ($2k), Medium ($5k), Large ($10k)
   - Target general audience or specific genres
   - Immediate subscriber boost

**Daily Simulation:**
- Content in development releases
- Licenses expire
- Subscribers watch content (quality + trends affect engagement)
- Natural churn based on content quality
- Organic growth from word-of-mouth
- Revenue collection ($9.99/subscriber/month)

## Database Schema

### Core Tables

**1. game_state**
- Current day, cash, company name
- Subscriber count
- Career stats (JSON)
- Subscriber segments (JSON)

**2. content**
- Title, genre, kind (licensed/original)
- Status, cost, release day, quality
- License expiration
- View counts and watch time

**3. licenses**
- Available content to license
- Title, genre, cost, quality, duration
- Loaded from external JSON file

**4. genres**
- Genre definitions (Action, Comedy, Drama, etc.)
- Trend values (popularity)
- Quality weights for production (JSON)

**5. achievements**
- Unlockable achievements
- Requirement types and values
- Unlock tracking

**6. unlockables**
- Feature unlocks (Original Content, Marketing, etc.)
- Cost and requirements
- Auto-unlock flags

**7. kpis**
- Daily performance metrics
- Subscribers, churn, revenue, watch time

**8. marketing_campaigns**
- Campaign history
- Spend, channel, lift metrics

### Key SQL Patterns Demonstrated

```sql
-- Multi-table JOIN with aggregation
SELECT c.title, g.name as genre,
       COUNT(*) as views, SUM(c.total_minutes) as watch_time
FROM content c
JOIN genres g ON c.genre_id = g.id
WHERE c.status = 'released'
GROUP BY c.id
ORDER BY watch_time DESC;

-- Subscriber growth tracking
SELECT day, subscribers, churn_rate,
       new_subscribers - churned as net_growth,
       revenue
FROM kpis
ORDER BY day DESC
LIMIT 30;

-- Genre trend analysis
SELECT name, trend,
       CASE
         WHEN trend > 0.8 THEN 'Hot'
         WHEN trend > 0.6 THEN 'Trending'
         ELSE 'Cool'
       END as status
FROM genres
ORDER BY trend DESC;

-- Content ROI calculation
SELECT title,
       view_count,
       quality,
       (view_count * 0.10 - cost) / cost as roi
FROM content
WHERE kind = 'original'
ORDER BY roi DESC;
```

## Strategy Tips

- **Balance Your Catalog**: Diversify across multiple genres
- **Watch Trends**: Genre popularity fluctuates - invest in hot genres
- **Quality Over Quantity**: High quality reduces churn
- **Budget Allocation**: Study genre priorities before allocating production budget
  - Action: Invest in VFX, Sound, Camera
  - Romance: Focus on Casting, Writing, Music
  - Documentary: Prioritize Camera, Editing, Sound
- **Marketing Timing**: Run campaigns when you have strong content
- **Cash Management**: Keep reserves for trending opportunities

## Data Management Features

### 1. Schema Design
- Normalized database structure (3NF)
- Foreign key constraints for referential integrity
- CHECK constraints for data validation
- Primary keys and auto-increment IDs

### 2. Data Quality
- NOT NULL constraints on critical fields
- Range validation (quality: 0.0-1.0)
- Enumerated types (status, kind, segment)
- Default values for sensible initialization

### 3. Analytics Capabilities
- Aggregation functions (SUM, AVG, COUNT)
- Multi-table JOINs
- Subqueries for complex calculations
- Time-series KPI tracking
- JSON storage for complex objects

### 4. Performance
- Prepared statements to prevent SQL injection
- Indexed foreign keys
- Efficient query patterns
- Transaction management via saveDatabase()

## File Structure

```
streaming-service-simulator/
├── game.html              # Main game interface
├── game-engine.js         # Game logic and UI management
├── game-styles.css        # Responsive styling (dark/light themes)
├── database.js            # SQLite database manager (SQL.js)
├── licenses-data.json     # External license data
├── README.md              # This file
├── CHANGELOG.md           # Version history
├── DATABASE_GUIDE.md      # Detailed schema documentation
├── LICENSES_README.md     # Guide for adding licenses
├── PLAY_GUIDE.md          # Gameplay instructions
├── BUDGET_ALLOCATION_GUIDE.md  # Production budget guide
├── GENRE_COMPARISON.md    # Genre priority reference
└── PROGRESSION_SYSTEM.md  # Unlocks and achievements
```

## Expanding the Game

### Adding New Licenses

1. Open `licenses-data.json`
2. Add new license entry:
```json
{
  "title": "Your Show Title",
  "genre": "Drama",
  "cost": 12000,
  "quality": 0.85,
  "days": 90
}
```
3. Clear browser cache: `localStorage.removeItem('streamTycoonDb');`
4. Refresh and start new game

See [LICENSES_README.md](LICENSES_README.md) for detailed guidelines.

## Resetting the Game

To start fresh:
1. Open browser developer console (F12)
2. Run: `localStorage.removeItem('streamTycoonDb');`
3. Refresh the page
4. Click "Start New Game"

## Technical Highlights for Portfolio

This project demonstrates:

- **Database Design**: Designed normalized relational schema with 8 tables, foreign key constraints, and data validation using SQLite
- **SQL Proficiency**: Implemented complex queries with JOINs, aggregations, subqueries, and prepared statements
- **Data-Driven Simulation**: Built business logic engine that processes daily KPIs, churn analysis, and trend tracking
- **Browser-Based Database**: Leveraged SQL.js (WebAssembly) for client-side SQLite with localStorage persistence
- **External Data Loading**: Asynchronous JSON loading for scalable content management
- **Business Intelligence**: Created analytics dashboard with subscriber retention, content ROI, and genre trend analysis

## Browser Compatibility

- Chrome/Edge: ✅ Full support
- Firefox: ✅ Full support
- Safari: ✅ Full support
- Mobile browsers: ✅ Responsive design

Requires modern browser with:
- ES6+ JavaScript support
- LocalStorage API
- WebAssembly support

## Future Enhancements

- [ ] Export analytics data to CSV
- [ ] Advanced SQL views and stored procedures
- [ ] Data visualization with Chart.js
- [ ] Multiplayer mode with IndexedDB
- [ ] A/B testing framework
- [ ] Machine learning churn prediction
- [ ] Integration with Python backend for advanced analytics

## License

Portfolio project for educational and demonstration purposes.

## Author

Alyssa Manchester
Showcasing SQL, data management, and full-stack development skills.