# requirements.txt

# Core Framework
fastapi==0.109.0
uvicorn[standard]==0.27.0
starlette==0.35.1

# Database
sqlalchemy==2.0.25
alembic==1.13.1
asyncpg==0.29.0
psycopg2-binary==2.9.9

# Task Queue
celery==5.3.6
redis==5.0.1
flower==2.0.1  # Celery monitoring

# Authentication
python-jose[cryptography]==3.3.0
passlib[bcrypt]==1.7.4
python-multipart==0.0.6

# Validation & Settings
pydantic==2.5.3
pydantic-settings==2.1.0
email-validator==2.1.0

# HTTP & API
httpx==0.26.0
aiohttp==3.9.1
websockets==12.0

# PDF Generation
weasyprint==60.2
Jinja2==3.1.2
cairocffi==1.6.1
Pillow==10.2.0

# Data Processing
pandas==2.1.4
numpy==1.26.3
python-slugify==8.0.1

# Security
cryptography==41.0.7
secrets==1.0.2

# File Handling
aiofiles==23.2.1
python-magic==0.4.27
boto3==1.34.0  # S3 compatible storage

# Charts & Visualization
matplotlib==3.8.2
plotly==5.18.0

# Utilities
python-dateutil==2.8.2
pytz==2023.3
orjson==3.9.10  # Fast JSON
tenacity==8.2.3  # Retry logic
structlog==23.3.0  # Structured logging

# Testing
pytest==7.4.4
pytest-asyncio==0.23.3
pytest-cov==4.1.0
httpx==0.26.0  # For test client
factory-boy==3.3.0
faker==22.0.0

# Development
black==23.12.1
ruff==0.1.9
mypy==1.8.0
pre-commit==3.6.0

┌─────────────────────────────────────────────────────────────────────────────────┐
│                                   CLIENTS                                        │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐            │
│  │ Agency UI   │  │ Client UI   │  │ Admin UI    │  │ API Clients │            │
│  │ (React)     │  │ (React)     │  │ (React)     │  │ (CSV/Code)  │            │
│  └──────┬──────┘  └──────┬──────┘  └──────┬──────┘  └──────┬──────┘            │
└─────────┼────────────────┼────────────────┼────────────────┼────────────────────┘
          │                │                │                │
          └────────────────┴────────────────┴────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────────┐
│                              LOAD BALANCER                                       │
│                         (Nginx / Traefik / Dokploy)                             │
│  ┌─────────────────────────────────────────────────────────────────────────┐   │
│  │ • SSL Termination          • Domain Routing (*.contentstrategist.com)   │   │
│  │ • Rate Limiting (L7)       • Custom Domain Routing                      │   │
│  │ • WebSocket Upgrade        • Health Checks                              │   │
│  └─────────────────────────────────────────────────────────────────────────┘   │
└────────────────────────────────────┬────────────────────────────────────────────┘
                                     │
                    ┌────────────────┼────────────────┐
                    │                │                │
                    ▼                ▼                ▼
┌──────────────────────┐ ┌──────────────────────┐ ┌──────────────────────┐
│    API Server 1      │ │    API Server 2      │ │    API Server N      │
│    (FastAPI)         │ │    (FastAPI)         │ │    (FastAPI)         │
├──────────────────────┤ ├──────────────────────┤ ├──────────────────────┤
│ • REST Endpoints     │ │ • REST Endpoints     │ │ • REST Endpoints     │
│ • WebSocket Handler  │ │ • WebSocket Handler  │ │ • WebSocket Handler  │
│ • Request Validation │ │ • Request Validation │ │ • Request Validation │
│ • Auth Middleware    │ │ • Auth Middleware    │ │ • Auth Middleware    │
│ • Agency Resolution  │ │ • Agency Resolution  │ │ • Agency Resolution  │
└──────────┬───────────┘ └──────────┬───────────┘ └──────────┬───────────┘
           │                        │                        │
           └────────────────────────┼────────────────────────┘
                                    │
        ┌───────────────────────────┼───────────────────────────┐
        │                           │                           │
        ▼                           ▼                           ▼
┌───────────────────┐    ┌───────────────────┐    ┌───────────────────┐
│    PostgreSQL     │    │      Redis        │    │   File Storage    │
│    (Primary DB)   │    │  (Cache/Broker)   │    │   (Local/S3)      │
├───────────────────┤    ├───────────────────┤    ├───────────────────┤
│ • Users           │    │ • Session Cache   │    │ • PDF Documents   │
│ • Agencies        │    │ • Rate Limit Data │    │ • Cover Images    │
│ • Clients         │    │ • Celery Broker   │    │ • Agency Logos    │
│ • Documents       │    │ • WebSocket PubSub│    │ • Client Uploads  │
│ • Templates       │    │ • Job Status      │    │                   │
│ • Scheduled Tasks │    │                   │    │                   │
└───────────────────┘    └─────────┬─────────┘    └───────────────────┘
                                   │
                    ┌──────────────┼──────────────┐
                    │              │              │
                    ▼              ▼              ▼
          ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
          │  Celery Worker  │ │  Celery Worker  │ │  Celery Beat    │
          │  (Generation)   │ │  (Distribution) │ │  (Scheduler)    │
          ├─────────────────┤ ├─────────────────┤ ├─────────────────┤
          │ • Research      │ │ • LinkedIn Post │ │ • Daily Schedule│
          │ • Content Gen   │ │ • Facebook Post │ │ • Cleanup Jobs  │
          │ • PDF Render    │ │ • Twitter Post  │ │ • Usage Reset   │
          │ • Chart Gen     │ │ • GMB Post      │ │ • Expiry Check  │
          └────────┬────────┘ └────────┬────────┘ └─────────────────┘
                   │                   │
                   └─────────┬─────────┘
                             │
                             ▼
          ┌─────────────────────────────────────────────────────┐
          │                  EXTERNAL SERVICES                   │
          ├─────────────────┬─────────────────┬─────────────────┤
          │  Anthropic API  │   Freepik API   │  Social APIs    │
          │  (Claude)       │   (Images)      │  (Distribution) │
          ├─────────────────┼─────────────────┼─────────────────┤
          │ • Research      │ • Stock Photos  │ • LinkedIn      │
          │ • Summarization │ • Search        │ • Facebook      │
          │ • Writing       │ • Download      │ • Twitter/X     │
          │ • Structuring   │                 │ • Google Biz    │
          └─────────────────┴─────────────────┴─────────────────┘

content-strategist/
│
├── alembic/                           # Database migrations
│   ├── versions/                      # Migration files
│   │   ├── 001_initial_schema.py
│   │   ├── 002_add_api_keys_table.py
│   │   └── ...
│   ├── env.py                         # Alembic environment config
│   └── script.py.mako                 # Migration template
│
├── app/                               # Main application
│   ├── __init__.py
│   ├── main.py                        # FastAPI application entry point
│   ├── config.py                      # Settings and configuration
│   ├── database.py                    # Database connection and session
│   │
│   ├── models/                        # SQLAlchemy ORM models
│   │   ├── __init__.py                # Export all models
│   │   ├── base.py                    # Base model class with common fields
│   │   ├── user.py                    # User model
│   │   ├── agency.py                  # Agency model
│   │   ├── client.py                  # Client model
│   │   ├── document.py                # Document model
│   │   ├── template.py                # Template model
│   │   ├── plan.py                    # Plan/subscription model
│   │   ├── scheduled_content.py       # Scheduled content model
│   │   ├── generation_job.py          # Generation job tracking
│   │   ├── api_key.py                 # API key model
│   │   └── audit_log.py               # Audit log model
│   │
│   ├── schemas/                       # Pydantic schemas (request/response)
│   │   ├── __init__.py
│   │   ├── auth.py                    # Login, token schemas
│   │   ├── user.py                    # User CRUD schemas
│   │   ├── agency.py                  # Agency CRUD schemas
│   │   ├── client.py                  # Client CRUD schemas
│   │   ├── document.py                # Document schemas
│   │   ├── template.py                # Template schemas
│   │   ├── schedule.py                # Schedule schemas
│   │   ├── generation.py              # Generation request/response
│   │   └── common.py                  # Shared schemas (pagination, etc.)
│   │
│   ├── api/                           # API routes
│   │   ├── __init__.py
│   │   ├── deps.py                    # Shared dependencies
│   │   ├── v1/                        # API version 1
│   │   │   ├── __init__.py
│   │   │   ├── router.py              # Main v1 router aggregator
│   │   │   ├── auth.py                # Authentication endpoints
│   │   │   ├── admin/                 # Super admin endpoints
│   │   │   │   ├── __init__.py
│   │   │   │   ├── agencies.py        # Agency management
│   │   │   │   ├── templates.py       # Template management
│   │   │   │   ├── plans.py           # Plan management
│   │   │   │   └── system.py          # System health, stats
│   │   │   ├── agencies.py            # Agency self-management
│   │   │   ├── team.py                # Team member management
│   │   │   ├── clients.py             # Client CRUD
│   │   │   ├── documents.py           # Document operations
│   │   │   ├── generation.py          # Content generation
│   │   │   ├── distribution.py        # Social distribution
│   │   │   ├── schedule.py            # Scheduled content
│   │   │   ├── templates.py           # Template browsing
│   │   │   └── demo.py                # Demo/trial endpoints
│   │   └── websocket.py               # WebSocket handlers
│   │
│   ├── services/                      # Business logic layer
│   │   ├── __init__.py
│   │   ├── auth_service.py            # Authentication logic
│   │   ├── agency_service.py          # Agency operations
│   │   ├── client_service.py          # Client operations
│   │   ├── document_service.py        # Document CRUD
│   │   ├── generation/                # Generation subsystem
│   │   │   ├── __init__.py
│   │   │   ├── orchestrator.py        # Main generation flow
│   │   │   ├── research_service.py    # Web research
│   │   │   ├── content_service.py     # Content writing
│   │   │   ├── statistics_service.py  # Statistics extraction
│   │   │   ├── chart_service.py       # Chart generation
│   │   │   └── outline_service.py     # Content structuring
│   │   ├── pdf_service.py             # PDF generation
│   │   ├── distribution_service.py    # Social media posting
│   │   ├── storage_service.py         # File storage abstraction
│   │   ├── encryption_service.py      # API key encryption
│   │   ├── image_service.py           # Freepik integration
│   │   └── webhook_service.py         # External webhooks
│   │
│   ├── workers/                       # Celery background tasks
│   │   ├── __init__.py
│   │   ├── celery_app.py              # Celery application config
│   │   ├── generation_tasks.py        # Content generation tasks
│   │   ├── distribution_tasks.py      # Social posting tasks
│   │   ├── scheduled_tasks.py         # Cron-like scheduled tasks
│   │   └── maintenance_tasks.py       # Cleanup, expiry tasks
│   │
│   ├── templates/                     # PDF HTML templates
│   │   ├── base/                      # Base template components
│   │   │   ├── layout.html            # Page layout structure
│   │   │   ├── styles.css             # Base styles
│   │   │   ├── variables.css          # CSS custom properties
│   │   │   └── components/            # Reusable components
│   │   │       ├── cover.html
│   │   │       ├── section.html
│   │   │       ├── callout.html
│   │   │       ├── statistic.html
│   │   │       ├── chart.html
│   │   │       ├── quote.html
│   │   │       ├── table.html
│   │   │       └── footer.html
│   │   ├── executive_01/              # Executive template
│   │   │   ├── template.html
│   │   │   ├── styles.css
│   │   │   └── preview.png
│   │   ├── minimal_02/                # Minimal template
│   │   ├── modern_03/                 # Modern template
│   │   ├── corporate_04/              # Corporate template
│   │   └── bold_05/                   # Bold template
│   │
│   ├── utils/                         # Utility functions
│   │   ├── __init__.py
│   │   ├── slugify.py                 # URL-safe string generation
│   │   ├── validators.py              # Custom validators
│   │   ├── helpers.py                 # Misc helper functions
│   │   ├── constants.py               # Application constants
│   │   ├── exceptions.py              # Custom exceptions
│   │   └── color_utils.py             # Color manipulation
│   │
│   └── middleware/                    # FastAPI middleware
│       ├── __init__.py
│       ├── agency_resolver.py         # White-label domain routing
│       ├── rate_limiter.py            # Request rate limiting
│       ├── request_logging.py         # Request/response logging
│       └── error_handler.py           # Global error handling
│
├── storage/                           # Local file storage (dev/single-server)
│   ├── documents/                     # Generated PDFs
│   ├── covers/                        # Cover images
│   ├── logos/                         # Agency/client logos
│   ├── uploads/                       # Client uploads
│   └── temp/                          # Temporary files
│
├── tests/                             # Test suite
│   ├── __init__.py
│   ├── conftest.py                    # Pytest fixtures
│   ├── factories/                     # Test data factories
│   │   ├── __init__.py
│   │   ├── user_factory.py
│   │   ├── agency_factory.py
│   │   └── ...
│   ├── unit/                          # Unit tests
│   │   ├── test_auth_service.py
│   │   ├── test_generation.py
│   │   └── ...
│   ├── integration/                   # Integration tests
│   │   ├── test_auth_endpoints.py
│   │   ├── test_document_flow.py
│   │   └── ...
│   └── e2e/                           # End-to-end tests
│       └── test_full_generation.py
│
├── scripts/                           # Utility scripts
│   ├── seed_plans.py                  # Initialize plan data
│   ├── seed_templates.py              # Load template files
│   ├── create_super_admin.py          # Create initial admin
│   ├── migrate_data.py                # Data migration helpers
│   └── cleanup_expired.py             # Manual cleanup script
│
├── docker/                            # Docker configuration
│   ├── Dockerfile                     # Main API image
│   ├── Dockerfile.worker              # Celery worker image
│   ├── docker-compose.yml             # Development compose
│   ├── docker-compose.prod.yml        # Production compose
│   └── nginx/                         # Nginx configuration
│       ├── nginx.conf
│       └── sites/
│           └── default.conf
│
├── docs/                              # Documentation
│   ├── API.md                         # API documentation
│   ├── DEPLOYMENT.md                  # Deployment guide
│   └── DEVELOPMENT.md                 # Development setup
│
├── .env.example                       # Environment template
├── .gitignore
├── alembic.ini                        # Alembic configuration
├── pyproject.toml                     # Python project config
├── requirements.txt                   # Python dependencies
├── requirements-dev.txt               # Dev dependencies
└── README.md

┌─────────────────────────────────────────────────────────────────────────────────┐
│                              REQUEST LIFECYCLE                                   │
└─────────────────────────────────────────────────────────────────────────────────┘

1. CLIENT REQUEST
   │
   │  POST /api/v1/clients/abc123/documents/generate
   │  Headers:
   │    Authorization: Bearer eyJhbGciOiJIUzI1NiIs...
   │    Host: acme.contentstrategist.com
   │    Content-Type: application/json
   │  Body:
   │    {"topic": "AI Implementation", "tone": "professional", ...}
   │
   ▼
2. LOAD BALANCER (Nginx/Traefik)
   │
   │  • SSL termination
   │  • Check rate limits (IP-based, 1000 req/min)
   │  • Route based on Host header
   │  • Forward to available API instance
   │
   ▼
3. FASTAPI MIDDLEWARE STACK
   │
   │  ┌─────────────────────────────────────────────────────────────┐
   │  │ 3a. RequestLoggingMiddleware                                │
   │  │     • Generate request_id (UUID)                            │
   │  │     • Log request start (method, path, headers)             │
   │  │     • Attach request_id to response headers                 │
   │  └─────────────────────────────────────────────────────────────┘
   │                              │
   │  ┌─────────────────────────────────────────────────────────────┐
   │  │ 3b. AgencyResolverMiddleware                                │
   │  │     • Extract Host header                                   │
   │  │     • Check custom_domain table → agency lookup             │
   │  │     • Check subdomain pattern → agency lookup               │
   │  │     • Attach agency to request.state.agency                 │
   │  │     • If no agency found and not admin route → 404          │
   │  └─────────────────────────────────────────────────────────────┘
   │                              │
   │  ┌─────────────────────────────────────────────────────────────┐
   │  │ 3c. RateLimiterMiddleware                                   │
   │  │     • Check Redis for request count (agency + endpoint)     │
   │  │     • If limit exceeded → 429 Too Many Requests             │
   │  │     • Increment counter with TTL                            │
   │  └─────────────────────────────────────────────────────────────┘
   │                              │
   │  ┌─────────────────────────────────────────────────────────────┐
   │  │ 3d. CORSMiddleware                                          │
   │  │     • Validate Origin header                                │
   │  │     • Add CORS headers to response                          │
   │  └─────────────────────────────────────────────────────────────┘
   │
   ▼
4. ROUTE HANDLER (api/v1/generation.py)
   │
   │  @router.post("/clients/{client_id}/documents/generate")
   │  async def generate_document(
   │      client_id: UUID,
   │      request: GenerationRequest,
   │      current_user: User = Depends(get_current_user),
   │      agency: Agency = Depends(get_current_agency),
   │      db: AsyncSession = Depends(get_db)
   │  ):
   │
   ▼
5. DEPENDENCY INJECTION
   │
   │  ┌─────────────────────────────────────────────────────────────┐
   │  │ 5a. get_db()                                                │
   │  │     • Get database session from pool                        │
   │  │     • Yield session                                         │
   │  │     • Close/return session after request                    │
   │  └─────────────────────────────────────────────────────────────┘
   │                              │
   │  ┌─────────────────────────────────────────────────────────────┐
   │  │ 5b. get_current_user()                                      │
   │  │     • Extract Bearer token from Authorization header        │
   │  │     • Decode and validate JWT                               │
   │  │     • Load user from database                               │
   │  │     • Check user is active                                  │
   │  │     • Check user belongs to resolved agency                 │
   │  │     • Return User object or raise 401                       │
   │  └─────────────────────────────────────────────────────────────┘
   │                              │
   │  ┌─────────────────────────────────────────────────────────────┐
   │  │ 5c. get_current_agency()                                    │
   │  │     • Return request.state.agency from middleware           │
   │  │     • Check agency subscription is active                   │
   │  │     • Return Agency object or raise 403                     │
   │  └─────────────────────────────────────────────────────────────┘
   │
   ▼
6. REQUEST VALIDATION (Pydantic)
   │
   │  class GenerationRequest(BaseModel):
   │      topic: str = Field(..., min_length=3, max_length=500)
   │      tone: Literal["professional", "casual", "authoritative"]
   │      template_code: Optional[str]
   │      keywords: Optional[List[str]]
   │      ...
   │
   │  • Automatic validation
   │  • If invalid → 422 Unprocessable Entity with details
   │
   ▼
7. AUTHORIZATION CHECK
   │
   │  • Verify user has permission to create documents
   │  • Verify client belongs to user's agency
   │  • Verify template is available to agency
   │  • If unauthorized → 403 Forbidden
   │
   ▼
8. BUSINESS LOGIC (Service Layer)
   │
   │  ┌─────────────────────────────────────────────────────────────┐
   │  │ generation_service.start_generation()                       │
   │  │                                                             │
   │  │ • Create Document record (status='pending')                 │
   │  │ • Create GenerationJob record                               │
   │  │ • Dispatch Celery task                                      │
   │  │ • Return document_id and job_id                             │
   │  └─────────────────────────────────────────────────────────────┘
   │
   ▼
9. RESPONSE
   │
   │  HTTP 202 Accepted
   │  {
   │      "document_id": "doc_uuid",
   │      "job_id": "job_uuid",
   │      "status": "pending",
   │      "websocket_url": "wss://acme.contentstrategist.com/ws/generation/job_uuid",
   │      "estimated_duration_seconds": 120
   │  }
   │
   ▼
10. BACKGROUND PROCESSING (Celery)
    │
    │  ┌─────────────────────────────────────────────────────────────┐
    │  │ Task: generate_document_task(document_id)                   │
    │  │                                                             │
    │  │ • Runs asynchronously in worker                             │
    │  │ • Updates job progress via Redis pub/sub                    │
    │  │ • Client receives updates via WebSocket                     │
    │  │ • On completion: Update document status, store PDF          │
    │  └─────────────────────────────────────────────────────────────┘
    │
    ▼
11. WEBSOCKET UPDATES (Real-time)
    │
    │  Client connects to: wss://.../ws/generation/job_uuid
    │  
    │  Server sends:
    │  {"step": "researching", "progress": 25, "detail": "Reading 47 sources..."}
    │  {"step": "writing", "progress": 60, "detail": "Composing section 3 of 5..."}
    │  {"step": "complete", "progress": 100, "document_url": "https://..."}

┌─────────────────────────────────────────────────────────────────────────────────┐
│                           ENTITY RELATIONSHIP DIAGRAM                            │
└─────────────────────────────────────────────────────────────────────────────────┘

                                    ┌─────────────┐
                                    │   PLANS     │
                                    ├─────────────┤
                                    │ id (PK)     │
                                    │ name        │
                                    │ price_cents │
                                    │ max_seats   │
                                    │ max_templates
                                    │ ...         │
                                    └──────┬──────┘
                                           │
                                           │ 1:N
                                           │
                                           ▼
┌─────────────┐                    ┌─────────────┐                    ┌─────────────┐
│  API_KEYS   │                    │  AGENCIES   │                    │  TEMPLATES  │
├─────────────┤                    ├─────────────┤                    ├─────────────┤
│ id (PK)     │       N:1         │ id (PK)     │         M:N        │ id (PK)     │
│ agency_id(FK)├──────────────────►│ plan_id(FK) │◄────────┬─────────►│ code        │
│ name        │                    │ name        │         │          │ name        │
│ key_hash    │                    │ slug        │         │          │ html_template
│ scopes[]    │                    │ custom_domain         │          │ css_template│
│ ...         │                    │ colors      │         │          │ ...         │
└─────────────┘                    │ logos       │         │          └─────────────┘
                                   │ oauth_creds │         │
                                   │ ...         │         │
                                   └──────┬──────┘         │
                                          │                │
                    ┌─────────────────────┼────────────────┤
                    │                     │                │
                    │ 1:N                 │ 1:N            │ (junction table)
                    │                     │                │
                    ▼                     ▼                ▼
           ┌─────────────┐       ┌─────────────┐   ┌──────────────────┐
           │   USERS     │       │  CLIENTS    │   │ AGENCY_TEMPLATES │
           ├─────────────┤       ├─────────────┤   ├──────────────────┤
           │ id (PK)     │       │ id (PK)     │   │ agency_id (FK)   │
           │ agency_id(FK)│      │ agency_id(FK)│   │ template_id (FK) │
           │ client_id(FK)├─────►│ company_name│   │ assigned_at      │
           │ email       │       │ contact_*   │   └──────────────────┘
           │ password_hash       │ website_url │
           │ role        │       │ industry    │
           │ ...         │       │ colors      │
           └─────────────┘       │ logos       │
                                 │ oauth_creds │
                                 │ defaults    │
                                 │ ...         │
                                 └──────┬──────┘
                                        │
                         ┌──────────────┼──────────────┐
                         │              │              │
                         │ 1:N          │ 1:N          │ 1:N
                         │              │              │
                         ▼              ▼              ▼
                ┌─────────────┐ ┌─────────────┐ ┌─────────────────┐
                │ DOCUMENTS   │ │ SCHEDULED_  │ │ GENERATION_JOBS │
                ├─────────────┤ │ CONTENT     │ ├─────────────────┤
                │ id (PK)     │ ├─────────────┤ │ id (PK)         │
                │ client_id(FK)│ │ id (PK)     │ │ document_id(FK) │
                │ template_id │ │ client_id(FK)│ │ celery_task_id  │
                │ title       │ │ scheduled_* │ │ current_step    │
                │ topic       │ │ topic       │ │ progress        │
                │ content_json│ │ template_code │ step_timings   │
                │ pdf_url     │ │ status      │ │ ...             │
                │ status      │ │ document_id │ └─────────────────┘
                │ research_*  │ │ ...         │
                │ distribution│ └─────────────┘
                │ ...         │
                └─────────────┘
                        │
                        │ Logged to
                        ▼
                ┌─────────────┐
                │ AUDIT_LOGS  │
                ├─────────────┤
                │ id (PK)     │
                │ user_id     │
                │ agency_id   │
                │ action      │
                │ resource_*  │
                │ changes     │
                │ ip_address  │
                │ ...         │
                └─────────────┘

-- Plans define subscription tiers
-- Seeded by Super Admin, rarely changed

CREATE TABLE plans (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Identification
    name VARCHAR(50) NOT NULL UNIQUE,
    -- Valid values: 'pro_annual', 'pro_monthly', 'enterprise_annual', 'enterprise_monthly'
    
    display_name VARCHAR(100) NOT NULL,
    -- Human-readable: 'Pro (Annual)', 'Enterprise (Monthly)', etc.
    
    description TEXT,
    -- Marketing description for plan selection UI
    
    -- Pricing
    price_cents INTEGER NOT NULL,
    -- Price in cents: 1000000 = $10,000
    
    billing_period VARCHAR(20) NOT NULL CHECK (billing_period IN ('annual', 'monthly')),
    
    -- Limits
    max_seats INTEGER NOT NULL,
    -- Maximum number of clients agency can have
    -- Pro: 50, Enterprise: 200
    
    max_templates INTEGER,
    -- NULL = unlimited (all templates)
    -- Pro: 5, Enterprise: NULL
    
    -- Feature Flags
    custom_domain_allowed BOOLEAN NOT NULL DEFAULT FALSE,
    -- Enterprise only
    
    client_image_upload_allowed BOOLEAN NOT NULL DEFAULT FALSE,
    -- Enterprise only: clients can upload their own cover images
    
    -- API Configuration
    api_key_mode VARCHAR(20) NOT NULL DEFAULT 'byok' CHECK (api_key_mode IN ('byok', 'included')),
    -- 'byok' = Bring Your Own Key (Pro)
    -- 'included' = We provide API credits (Enterprise)
    
    included_api_credits_cents INTEGER NOT NULL DEFAULT 0,
    -- Monthly API credit allowance for 'included' mode
    -- Enterprise: 100000 = $1,000/month
    
    -- Display
    sort_order INTEGER NOT NULL DEFAULT 0,
    -- For ordering in plan selection UI
    
    badge_text VARCHAR(50),
    -- e.g., 'Most Popular', 'Best Value'
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    -- Inactive plans cannot be selected for new agencies
    
    is_visible BOOLEAN NOT NULL DEFAULT TRUE,
    -- Hidden plans don't show in public pricing
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_plans_is_active ON plans(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_plans_sort_order ON plans(sort_order);

-- Seed Data
INSERT INTO plans (
    name, display_name, description, price_cents, billing_period,
    max_seats, max_templates, custom_domain_allowed, client_image_upload_allowed,
    api_key_mode, included_api_credits_cents, sort_order, badge_text
) VALUES
(
    'pro_annual',
    'Pro (Annual)',
    'For growing agencies ready to scale their content operation',
    1000000,  -- $10,000/year
    'annual',
    50,       -- 50 clients
    5,        -- 5 templates
    FALSE,    -- No custom domain
    FALSE,    -- No image upload
    'byok',   -- Bring your own keys
    0,        -- No included credits
    1,
    'Best Value'
),
(
    'pro_monthly',
    'Pro (Monthly)',
    'For growing agencies ready to scale their content operation',
    200000,   -- $2,000/month
    'monthly',
    50,
    5,
    FALSE,
    FALSE,
    'byok',
    0,
    2,
    NULL
),
(
    'enterprise_annual',
    'Enterprise (Annual)',
    'For established agencies requiring full white-label capabilities',
    2000000,  -- $20,000/year
    'annual',
    200,      -- 200 clients
    NULL,     -- All templates
    TRUE,     -- Custom domain
    TRUE,     -- Image upload
    'included',
    100000,   -- $1,000/month in credits
    3,
    'Full Featured'
),
(
    'enterprise_monthly',
    'Enterprise (Monthly)',
    'For established agencies requiring full white-label capabilities',
    500000,   -- $5,000/month
    'monthly',
    200,
    NULL,
    TRUE,
    TRUE,
    'included',
    100000,
    4,
    NULL
);

-- Agencies are the primary customers (marketing agencies)
-- Each agency can have multiple clients (seats)

CREATE TABLE agencies (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Plan Relationship
    plan_id UUID NOT NULL REFERENCES plans(id) ON DELETE RESTRICT,
    -- Cannot delete a plan that has agencies
    
    -- ═══════════════════════════════════════════════════════════════
    -- IDENTIFICATION
    -- ═══════════════════════════════════════════════════════════════
    
    name VARCHAR(255) NOT NULL,
    -- Display name: "Acme Marketing Agency"
    
    slug VARCHAR(100) NOT NULL UNIQUE,
    -- URL-safe identifier for subdomains: "acme"
    -- Used for: acme.contentstrategist.com
    -- Constraints: lowercase, alphanumeric + hyphens, no leading/trailing hyphens
    
    -- ═══════════════════════════════════════════════════════════════
    -- CUSTOM DOMAIN (Enterprise Only)
    -- ═══════════════════════════════════════════════════════════════
    
    custom_domain VARCHAR(255) UNIQUE,
    -- e.g., 'content.acmeagency.com'
    -- NULL for Pro plans
    
    custom_domain_verified BOOLEAN NOT NULL DEFAULT FALSE,
    -- TRUE after DNS verification
    
    custom_domain_verification_token VARCHAR(64),
    -- TXT record value for DNS verification
    -- e.g., "content-strategist-verify=abc123xyz..."
    
    custom_domain_verified_at TIMESTAMP WITH TIME ZONE,
    -- When verification succeeded
    
    -- ═══════════════════════════════════════════════════════════════
    -- API KEYS (Encrypted - for BYOK Mode)
    -- ═══════════════════════════════════════════════════════════════
    
    anthropic_api_key_encrypted TEXT,
    -- Encrypted Anthropic API key
    -- Format: encrypted using Fernet symmetric encryption
    
    anthropic_api_key_last4 VARCHAR(4),
    -- Last 4 characters for display: "...abc1"
    
    freepik_api_key_encrypted TEXT,
    -- Encrypted Freepik API key
    
    freepik_api_key_last4 VARCHAR(4),
    
    -- ═══════════════════════════════════════════════════════════════
    -- BRANDING - UI THEME
    -- ═══════════════════════════════════════════════════════════════
    
    color_mode VARCHAR(10) NOT NULL DEFAULT 'light' CHECK (color_mode IN ('light', 'dark')),
    -- Base theme mode
    
    color_accent_1 VARCHAR(7) NOT NULL DEFAULT '#1A1A1A',
    -- Primary accent (headers, primary buttons)
    -- Must be valid hex: #RRGGBB
    
    color_accent_2 VARCHAR(7) NOT NULL DEFAULT '#6B7280',
    -- Secondary accent (secondary elements)
    
    color_accent_3 VARCHAR(7) NOT NULL DEFAULT '#3B82F6',
    -- Highlight accent (links, highlights)
    
    -- ═══════════════════════════════════════════════════════════════
    -- BRANDING - LOGOS
    -- ═══════════════════════════════════════════════════════════════
    
    logo_horizontal_url TEXT,
    -- Main header logo (recommended: 200x50px)
    -- Full URL to stored file
    
    logo_horizontal_width INTEGER,
    logo_horizontal_height INTEGER,
    
    logo_vertical_url TEXT,
    -- Square/stacked logo (recommended: 100x100px)
    
    logo_vertical_width INTEGER,
    logo_vertical_height INTEGER,
    
    logo_round_url TEXT,
    -- Circular avatar logo (recommended: 64x64px)
    
    logo_favicon_url TEXT,
    -- Favicon (recommended: 32x32px)
    
    -- ═══════════════════════════════════════════════════════════════
    -- BRANDING - COMPANY INFO
    -- ═══════════════════════════════════════════════════════════════
    
    company_website VARCHAR(255),
    -- https://acmeagency.com
    
    company_email VARCHAR(255),
    -- contact@acmeagency.com
    
    company_phone VARCHAR(50),
    
    company_address TEXT,
    -- Full mailing address
    
    footer_text TEXT,
    -- Custom footer for PDFs
    -- e.g., "© 2026 Acme Agency. Confidential."
    
    -- ═══════════════════════════════════════════════════════════════
    -- SOCIAL MEDIA URLS (Agency's own pages, not OAuth)
    -- ═══════════════════════════════════════════════════════════════
    
    social_linkedin_url VARCHAR(255),
    social_facebook_url VARCHAR(255),
    social_twitter_url VARCHAR(255),
    social_instagram_url VARCHAR(255),
    
    -- ═══════════════════════════════════════════════════════════════
    -- OAUTH CREDENTIALS (Encrypted JSON)
    -- For posting on behalf of clients
    -- ═══════════════════════════════════════════════════════════════
    
    oauth_linkedin_encrypted TEXT,
    -- JSON structure (encrypted):
    -- {
    --   "client_id": "...",
    --   "client_secret": "...",
    --   "configured_at": "2026-01-01T00:00:00Z"
    -- }
    
    oauth_linkedin_configured BOOLEAN NOT NULL DEFAULT FALSE,
    
    oauth_facebook_encrypted TEXT,
    oauth_facebook_configured BOOLEAN NOT NULL DEFAULT FALSE,
    
    oauth_twitter_encrypted TEXT,
    oauth_twitter_configured BOOLEAN NOT NULL DEFAULT FALSE,
    
    oauth_google_business_encrypted TEXT,
    oauth_google_business_configured BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- ═══════════════════════════════════════════════════════════════
    -- SUBSCRIPTION & BILLING
    -- ═══════════════════════════════════════════════════════════════
    
    subscription_status VARCHAR(20) NOT NULL DEFAULT 'active' 
        CHECK (subscription_status IN ('active', 'past_due', 'canceled', 'suspended', 'trial')),
    
    subscription_started_at TIMESTAMP WITH TIME ZONE,
    -- When current subscription began
    
    subscription_current_period_start TIMESTAMP WITH TIME ZONE,
    -- Start of current billing period
    
    subscription_current_period_end TIMESTAMP WITH TIME ZONE,
    -- End of current billing period
    
    subscription_canceled_at TIMESTAMP WITH TIME ZONE,
    -- When cancellation was requested (still active until period end)
    
    subscription_cancel_at_period_end BOOLEAN NOT NULL DEFAULT FALSE,
    -- If TRUE, subscription ends at period end
    
    -- External billing reference (Stripe, etc.)
    billing_customer_id VARCHAR(255),
    billing_subscription_id VARCHAR(255),
    
    -- ═══════════════════════════════════════════════════════════════
    -- API USAGE TRACKING (Enterprise with included credits)
    -- ═══════════════════════════════════════════════════════════════
    
    api_usage_cents_current_period INTEGER NOT NULL DEFAULT 0,
    -- Usage in current billing period (in cents)
    
    api_usage_period_start TIMESTAMP WITH TIME ZONE,
    api_usage_period_end TIMESTAMP WITH TIME ZONE,
    
    api_usage_warning_sent_at TIMESTAMP WITH TIME ZONE,
    -- When 80% usage warning was sent (NULL if not sent)
    
    -- ═══════════════════════════════════════════════════════════════
    -- SETTINGS & PREFERENCES
    -- ═══════════════════════════════════════════════════════════════
    
    default_timezone VARCHAR(50) NOT NULL DEFAULT 'America/New_York',
    -- For scheduled content
    
    notification_email VARCHAR(255),
    -- Where to send system notifications
    
    webhook_url TEXT,
    -- Optional webhook for generation complete events
    
    webhook_secret VARCHAR(64),
    -- For webhook signature verification
    
    -- ═══════════════════════════════════════════════════════════════
    -- METADATA
    -- ═══════════════════════════════════════════════════════════════
    
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    -- Soft delete flag
    
    notes TEXT,
    -- Internal notes (Super Admin only)
    
    onboarded_at TIMESTAMP WITH TIME ZONE,
    -- When agency completed onboarding
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMPS
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- CONSTRAINTS
    -- ═══════════════════════════════════════════════════════════════
    
    CONSTRAINT valid_slug CHECK (slug ~ '^[a-z0-9][a-z0-9-]*[a-z0-9]$|^[a-z0-9]$'),
    CONSTRAINT valid_hex_accent_1 CHECK (color_accent_1 ~ '^#[0-9A-Fa-f]{6}$'),
    CONSTRAINT valid_hex_accent_2 CHECK (color_accent_2 ~ '^#[0-9A-Fa-f]{6}$'),
    CONSTRAINT valid_hex_accent_3 CHECK (color_accent_3 ~ '^#[0-9A-Fa-f]{6}$')
);

-- Indexes
CREATE INDEX idx_agencies_slug ON agencies(slug);
CREATE INDEX idx_agencies_custom_domain ON agencies(custom_domain) WHERE custom_domain IS NOT NULL;
CREATE INDEX idx_agencies_plan_id ON agencies(plan_id);
CREATE INDEX idx_agencies_subscription_status ON agencies(subscription_status);
CREATE INDEX idx_agencies_is_active ON agencies(is_active) WHERE is_active = TRUE;

-- Full-text search on agency name (for admin search)
CREATE INDEX idx_agencies_name_search ON agencies USING gin(to_tsvector('english', name));

-- Users are people who log into the platform
-- Each user belongs to exactly one agency (except super_admin)
-- Client users also reference a specific client

CREATE TABLE users (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- RELATIONSHIPS
    -- ═══════════════════════════════════════════════════════════════
    
    agency_id UUID REFERENCES agencies(id) ON DELETE CASCADE,
    -- NULL for super_admin users
    -- Required for agency_admin, agency_member, client roles
    
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    -- Only set for 'client' role users
    -- NULL for all other roles
    
    -- ═══════════════════════════════════════════════════════════════
    -- AUTHENTICATION
    -- ═══════════════════════════════════════════════════════════════
    
    email VARCHAR(255) NOT NULL,
    -- Must be unique within agency context
    -- super_admin emails globally unique
    
    password_hash TEXT NOT NULL,
    -- bcrypt hash of password
    -- Format: $2b$12$...
    
    -- ═══════════════════════════════════════════════════════════════
    -- ROLE & PERMISSIONS
    -- ═══════════════════════════════════════════════════════════════
    
    role VARCHAR(20) NOT NULL CHECK (role IN ('super_admin', 'agency_admin', 'agency_member', 'client')),
    
    -- Role descriptions:
    -- super_admin: Oxford Pierpont staff, full system access
    -- agency_admin: Agency owner/manager, full agency access
    -- agency_member: Agency staff, limited to content operations
    -- client: End customer, view-only for their content
    
    -- ═══════════════════════════════════════════════════════════════
    -- PROFILE
    -- ═══════════════════════════════════════════════════════════════
    
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    
    display_name VARCHAR(200) GENERATED ALWAYS AS (
        COALESCE(first_name || ' ' || last_name, first_name, last_name, email)
    ) STORED,
    -- Computed display name for UI
    
    phone VARCHAR(50),
    
    avatar_url TEXT,
    -- URL to profile picture
    
    job_title VARCHAR(100),
    -- e.g., "Content Manager", "Account Executive"
    
    -- ═══════════════════════════════════════════════════════════════
    -- ACCOUNT STATUS
    -- ═══════════════════════════════════════════════════════════════
    
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    -- Soft delete / disable flag
    
    deactivated_at TIMESTAMP WITH TIME ZONE,
    deactivated_by UUID REFERENCES users(id),
    deactivation_reason TEXT,
    
    -- ═══════════════════════════════════════════════════════════════
    -- EMAIL VERIFICATION
    -- ═══════════════════════════════════════════════════════════════
    
    is_email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    
    email_verification_token VARCHAR(64),
    -- Random token sent in verification email
    
    email_verification_sent_at TIMESTAMP WITH TIME ZONE,
    email_verified_at TIMESTAMP WITH TIME ZONE,
    
    -- ═══════════════════════════════════════════════════════════════
    -- PASSWORD RESET
    -- ═══════════════════════════════════════════════════════════════
    
    password_reset_token VARCHAR(64),
    password_reset_expires_at TIMESTAMP WITH TIME ZONE,
    password_changed_at TIMESTAMP WITH TIME ZONE,
    
    -- ═══════════════════════════════════════════════════════════════
    -- SESSION & SECURITY
    -- ═══════════════════════════════════════════════════════════════
    
    last_login_at TIMESTAMP WITH TIME ZONE,
    last_login_ip VARCHAR(45),  -- Supports IPv6
    last_login_user_agent TEXT,
    
    failed_login_attempts INTEGER NOT NULL DEFAULT 0,
    locked_until TIMESTAMP WITH TIME ZONE,
    -- Account locked after too many failed attempts
    
    -- ═══════════════════════════════════════════════════════════════
    -- PREFERENCES
    -- ═══════════════════════════════════════════════════════════════
    
    timezone VARCHAR(50) DEFAULT 'America/New_York',
    locale VARCHAR(10) DEFAULT 'en-US',
    
    notification_preferences JSONB NOT NULL DEFAULT '{"email": true, "browser": true}'::jsonb,
    -- {
    --   "email": true,
    --   "browser": true,
    --   "generation_complete": true,
    --   "distribution_complete": true,
    --   "weekly_summary": false
    -- }
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMPS
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- CONSTRAINTS
    -- ═══════════════════════════════════════════════════════════════
    
    -- Email unique within agency (or globally for super_admin)
    CONSTRAINT unique_email_per_agency UNIQUE (email, agency_id),
    
    -- Role-based relationship requirements
    CONSTRAINT valid_role_relationships CHECK (
        CASE role
            WHEN 'super_admin' THEN agency_id IS NULL AND client_id IS NULL
            WHEN 'agency_admin' THEN agency_id IS NOT NULL AND client_id IS NULL
            WHEN 'agency_member' THEN agency_id IS NOT NULL AND client_id IS NULL
            WHEN 'client' THEN agency_id IS NOT NULL AND client_id IS NOT NULL
            ELSE FALSE
        END
    )
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_agency_id ON users(agency_id) WHERE agency_id IS NOT NULL;
CREATE INDEX idx_users_client_id ON users(client_id) WHERE client_id IS NOT NULL;
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_is_active ON users(is_active) WHERE is_active = TRUE;

-- Unique indexes for tokens (partial to ignore NULLs)
CREATE UNIQUE INDEX idx_users_email_verification_token 
    ON users(email_verification_token) 
    WHERE email_verification_token IS NOT NULL;

CREATE UNIQUE INDEX idx_users_password_reset_token 
    ON users(password_reset_token) 
    WHERE password_reset_token IS NOT NULL;

-- Clients are the end customers of agencies
-- Each client belongs to one agency
-- Clients inherit branding from agency but can override

CREATE TABLE clients (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- PARENT AGENCY
    -- ═══════════════════════════════════════════════════════════════
    
    agency_id UUID NOT NULL REFERENCES agencies(id) ON DELETE CASCADE,
    
    -- ═══════════════════════════════════════════════════════════════
    -- BASIC INFORMATION
    -- ═══════════════════════════════════════════════════════════════
    
    company_name VARCHAR(255) NOT NULL,
    -- "Acme Corporation"
    
    company_slug VARCHAR(100),
    -- URL-safe identifier: "acme-corp"
    -- Auto-generated from company_name if not provided
    
    contact_name VARCHAR(255),
    -- Primary contact person
    
    contact_email VARCHAR(255),
    contact_phone VARCHAR(50),
    contact_title VARCHAR(100),
    -- "VP of Marketing"
    
    -- ═══════════════════════════════════════════════════════════════
    -- BUSINESS INFORMATION
    -- ═══════════════════════════════════════════════════════════════
    
    website_url TEXT,
    -- https://acmecorp.com
    
    industry VARCHAR(255),
    -- Free text (not dropdown): "Healthcare Technology"
    
    company_size VARCHAR(50),
    -- "1-10", "11-50", "51-200", "201-500", "500+"
    
    company_description TEXT,
    -- Brief description for AI context
    
    -- ═══════════════════════════════════════════════════════════════
    -- BRANDING - LOGOS (Override agency defaults)
    -- ═══════════════════════════════════════════════════════════════
    
    logo_horizontal_url TEXT,
    logo_horizontal_width INTEGER,
    logo_horizontal_height INTEGER,
    
    logo_vertical_url TEXT,
    logo_vertical_width INTEGER,
    logo_vertical_height INTEGER,
    
    logo_round_url TEXT,
    
    -- ═══════════════════════════════════════════════════════════════
    -- BRANDING - COLORS (Override agency defaults)
    -- NULL = inherit from agency
    -- ═══════════════════════════════════════════════════════════════
    
    color_accent_1 VARCHAR(7),
    color_accent_2 VARCHAR(7),
    color_accent_3 VARCHAR(7),
    
    -- ═══════════════════════════════════════════════════════════════
    -- BRANDING - FOOTER
    -- ═══════════════════════════════════════════════════════════════
    
    footer_text TEXT,
    -- Override agency footer for this client's PDFs
    
    -- ═══════════════════════════════════════════════════════════════
    -- SOCIAL MEDIA OAUTH (Per-Client Tokens)
    -- These are the tokens for posting TO the client's accounts
    -- ═══════════════════════════════════════════════════════════════
    
    oauth_linkedin_encrypted TEXT,
    -- Encrypted JSON:
    -- {
    --   "access_token": "...",
    --   "refresh_token": "...",
    --   "expires_at": "2026-01-01T00:00:00Z",
    --   "organization_id": "...",  // For company pages
    --   "organization_name": "Acme Corporation"
    -- }
    
    oauth_linkedin_connected BOOLEAN NOT NULL DEFAULT FALSE,
    oauth_linkedin_connected_at TIMESTAMP WITH TIME ZONE,
    oauth_linkedin_page_name VARCHAR(255),
    -- Display: "Connected to: Acme Corporation"
    
    oauth_facebook_encrypted TEXT,
    oauth_facebook_connected BOOLEAN NOT NULL DEFAULT FALSE,
    oauth_facebook_connected_at TIMESTAMP WITH TIME ZONE,
    oauth_facebook_page_name VARCHAR(255),
    
    oauth_twitter_encrypted TEXT,
    oauth_twitter_connected BOOLEAN NOT NULL DEFAULT FALSE,
    oauth_twitter_connected_at TIMESTAMP WITH TIME ZONE,
    oauth_twitter_handle VARCHAR(100),
    -- @AcmeCorp
    
    oauth_google_business_encrypted TEXT,
    oauth_google_business_connected BOOLEAN NOT NULL DEFAULT FALSE,
    oauth_google_business_connected_at TIMESTAMP WITH TIME ZONE,
    oauth_google_business_location_name VARCHAR(255),
    
    -- ═══════════════════════════════════════════════════════════════
    -- CONTENT DEFAULTS
    -- Pre-filled when generating content for this client
    -- ═══════════════════════════════════════════════════════════════
    
    default_tone VARCHAR(20) DEFAULT 'professional' 
        CHECK (default_tone IN ('professional', 'casual', 'authoritative')),
    
    related_services TEXT[],
    -- Services to promote in content
    -- ["Cloud Migration", "Data Analytics", "AI Consulting"]
    
    target_keywords TEXT[],
    -- Default keywords for SEO
    -- ["enterprise AI", "digital transformation", "cloud strategy"]
    
    additional_context TEXT,
    -- Permanent context for all content
    -- "Focus on Fortune 500 audience. Avoid competitor mentions."
    
    brand_voice_notes TEXT,
    -- Notes about brand voice
    -- "Formal but approachable. Use data-driven language."
    
    -- ═══════════════════════════════════════════════════════════════
    -- CONTENT STATISTICS
    -- ═══════════════════════════════════════════════════════════════
    
    total_documents_generated INTEGER NOT NULL DEFAULT 0,
    total_documents_distributed INTEGER NOT NULL DEFAULT 0,
    last_document_generated_at TIMESTAMP WITH TIME ZONE,
    last_document_distributed_at TIMESTAMP WITH TIME ZONE,
    
    -- ═══════════════════════════════════════════════════════════════
    -- STATUS
    -- ═══════════════════════════════════════════════════════════════
    
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    -- Soft delete flag
    
    deactivated_at TIMESTAMP WITH TIME ZONE,
    deactivation_reason TEXT,
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMPS
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- CONSTRAINTS
    -- ═══════════════════════════════════════════════════════════════
    
    CONSTRAINT valid_hex_client_accent_1 CHECK (
        color_accent_1 IS NULL OR color_accent_1 ~ '^#[0-9A-Fa-f]{6}$'
    ),
    CONSTRAINT valid_hex_client_accent_2 CHECK (
        color_accent_2 IS NULL OR color_accent_2 ~ '^#[0-9A-Fa-f]{6}$'
    ),
    CONSTRAINT valid_hex_client_accent_3 CHECK (
        color_accent_3 IS NULL OR color_accent_3 ~ '^#[0-9A-Fa-f]{6}$'
    ),
    
    -- Unique slug per agency
    CONSTRAINT unique_client_slug_per_agency UNIQUE (agency_id, company_slug)
);

-- Indexes
CREATE INDEX idx_clients_agency_id ON clients(agency_id);
CREATE INDEX idx_clients_company_name ON clients(company_name);
CREATE INDEX idx_clients_company_slug ON clients(company_slug);
CREATE INDEX idx_clients_is_active ON clients(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_clients_industry ON clients(industry) WHERE industry IS NOT NULL;

-- Full-text search
CREATE INDEX idx_clients_search ON clients 
    USING gin(to_tsvector('english', company_name || ' ' || COALESCE(industry, '')));

-- Templates define PDF design layouts
-- Managed by Super Admin
-- Assigned to agencies based on plan

CREATE TABLE templates (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- IDENTIFICATION
    -- ═══════════════════════════════════════════════════════════════
    
    code VARCHAR(50) NOT NULL UNIQUE,
    -- Machine identifier: 'EXEC_01', 'MINIMAL_02', 'MODERN_03'
    -- Used in CSV uploads and API calls
    
    name VARCHAR(255) NOT NULL,
    -- Display name: 'Executive Professional'
    
    description TEXT,
    -- Description for template selection UI
    
    -- ═══════════════════════════════════════════════════════════════
    -- TEMPLATE CONTENT
    -- ═══════════════════════════════════════════════════════════════
    
    html_template TEXT NOT NULL,
    -- Main HTML structure with Jinja2 placeholders
    -- {{ title }}, {{ sections }}, {% for stat in statistics %}
    
    css_template TEXT NOT NULL,
    -- Stylesheet with CSS custom properties for colors
    -- var(--color-primary), var(--color-secondary)
    
    -- ═══════════════════════════════════════════════════════════════
    -- TEMPLATE CAPABILITIES
    -- ═══════════════════════════════════════════════════════════════
    
    supports_charts BOOLEAN NOT NULL DEFAULT TRUE,
    -- Template has chart placeholders
    
    supports_statistics BOOLEAN NOT NULL DEFAULT TRUE,
    -- Template has stat callout boxes
    
    supports_quotes BOOLEAN NOT NULL DEFAULT TRUE,
    -- Template has pull quote styling
    
    supports_tables BOOLEAN NOT NULL DEFAULT TRUE,
    -- Template has table styling
    
    max_sections INTEGER DEFAULT 10,
    -- Recommended maximum sections
    
    -- ═══════════════════════════════════════════════════════════════
    -- PREVIEW & DISPLAY
    -- ═══════════════════════════════════════════════════════════════
    
    preview_image_url TEXT,
    -- Thumbnail image for template selection
    -- Recommended: 400x520px (letter aspect ratio)
    
    preview_pdf_url TEXT,
    -- Sample PDF for preview
    
    -- ═══════════════════════════════════════════════════════════════
    -- CATEGORIZATION
    -- ═══════════════════════════════════════════════════════════════
    
    category VARCHAR(50),
    -- 'executive', 'minimal', 'modern', 'corporate', 'creative'
    
    tags TEXT[],
    -- ['professional', 'data-heavy', 'minimal', 'bold']
    
    -- ═══════════════════════════════════════════════════════════════
    -- AVAILABILITY
    -- ═══════════════════════════════════════════════════════════════
    
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    -- Available for use
    
    is_premium BOOLEAN NOT NULL DEFAULT FALSE,
    -- TRUE = Enterprise only
    -- FALSE = Available to all plans (subject to template limit)
    
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    -- Default template if none specified
    -- Only one template should be default
    
    -- ═══════════════════════════════════════════════════════════════
    -- METADATA
    -- ═══════════════════════════════════════════════════════════════
    
    version INTEGER NOT NULL DEFAULT 1,
    -- Increment when template updated
    
    author VARCHAR(100),
    -- Designer name
    
    release_notes TEXT,
    -- What's new in this version
    
    sort_order INTEGER NOT NULL DEFAULT 0,
    -- Display order in template selection
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMPS
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    published_at TIMESTAMP WITH TIME ZONE
    -- When template was made available
);

-- Indexes
CREATE UNIQUE INDEX idx_templates_code ON templates(code);
CREATE INDEX idx_templates_is_active ON templates(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_templates_is_premium ON templates(is_premium);
CREATE INDEX idx_templates_category ON templates(category);
CREATE INDEX idx_templates_sort_order ON templates(sort_order);

-- Ensure only one default template
CREATE UNIQUE INDEX idx_templates_single_default 
    ON templates(is_default) 
    WHERE is_default = TRUE;

-- Junction table for agency template assignments
-- Pro agencies get specific templates assigned
-- Enterprise agencies have access to all (no rows needed, checked by plan)

CREATE TABLE agency_templates (
    -- Composite Primary Key
    agency_id UUID NOT NULL REFERENCES agencies(id) ON DELETE CASCADE,
    template_id UUID NOT NULL REFERENCES templates(id) ON DELETE CASCADE,
    
    PRIMARY KEY (agency_id, template_id),
    
    -- ═══════════════════════════════════════════════════════════════
    -- ASSIGNMENT INFO
    -- ═══════════════════════════════════════════════════════════════
    
    assigned_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    assigned_by UUID REFERENCES users(id),
    -- Super Admin who assigned the template
    
    notes TEXT
    -- Why this template was assigned
);

-- Index for looking up agency's templates
CREATE INDEX idx_agency_templates_agency_id ON agency_templates(agency_id);

-- Documents are generated content pieces
-- Central to the application

CREATE TABLE documents (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- RELATIONSHIPS
    -- ═══════════════════════════════════════════════════════════════
    
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    
    template_id UUID REFERENCES templates(id) ON DELETE SET NULL,
    -- NULL if template was deleted
    
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    -- Who initiated generation
    
    scheduled_content_id UUID REFERENCES scheduled_content(id) ON DELETE SET NULL,
    -- If created from scheduled content
    
    -- ═══════════════════════════════════════════════════════════════
    -- CONTENT IDENTITY
    -- ═══════════════════════════════════════════════════════════════
    
    title VARCHAR(500) NOT NULL,
    -- Generated title: "AI Implementation: A Strategic Guide for Enterprise Leaders"
    
    subtitle VARCHAR(500),
    -- Optional subtitle
    
    topic VARCHAR(500) NOT NULL,
    -- Original topic input: "AI implementation strategies for mid-market companies"
    
    slug VARCHAR(200),
    -- URL-safe identifier: "ai-implementation-strategic-guide"
    
    -- ═══════════════════════════════════════════════════════════════
    -- GENERATED CONTENT (Structured JSON)
    -- ═══════════════════════════════════════════════════════════════
    
    content_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    /*
    {
        "version": 1,
        "title": "AI Implementation: A Strategic Guide",
        "subtitle": "How Forward-Thinking Companies Are Winning with Artificial Intelligence",
        "summary": "Executive summary paragraph...",
        
        "sections": [
            {
                "number": 1,
                "label": "Strategy 1:",
                "title": "Start with High-Impact, Low-Risk Use Cases",
                "lead": "Bold opening statement...",
                "paragraphs": [
                    "First paragraph content...",
                    "Second paragraph content..."
                ],
                "subheadings": [
                    {
                        "title": "Identifying Quick Wins",
                        "content": "Subheading content..."
                    }
                ],
                "callout": {
                    "type": "tip",
                    "content": "Pro tip: Start with customer service..."
                }
            }
        ],
        
        "statistics": [
            {
                "id": "stat_1",
                "value": "73%",
                "label": "of companies report exceeding ROI expectations",
                "source": "McKinsey Digital Survey 2025",
                "source_url": "https://...",
                "type": "percentage",
                "context": "Supporting context..."
            }
        ],
        
        "charts": [
            {
                "id": "chart_1",
                "type": "bar",
                "title": "AI Investment by Industry",
                "data": {
                    "labels": ["Healthcare", "Finance", "Retail"],
                    "values": [45, 62, 38]
                },
                "source": "Industry Report 2025"
            }
        ],
        
        "quotes": [
            {
                "id": "quote_1",
                "text": "AI is not just a technology...",
                "attribution": "Satya Nadella, CEO Microsoft",
                "source": "Interview, 2025"
            }
        ],
        
        "conclusion": {
            "title": "The Bottom Line",
            "lead": "Concluding statement...",
            "content": "Conclusion paragraphs...",
            "cta": "Contact us to discuss your AI strategy."
        },
        
        "metadata": {
            "word_count": 2500,
            "estimated_read_time_minutes": 12,
            "generated_at": "2026-01-02T10:30:00Z"
        }
    }
    */
    
    -- ═══════════════════════════════════════════════════════════════
    -- GENERATED FILES
    -- ═══════════════════════════════════════════════════════════════
    
    pdf_url TEXT,
    -- Public URL: https://authapi.net/files/agency-slug/doc-id.pdf
    
    pdf_file_path TEXT,
    -- Internal path: /storage/documents/agency-id/client-id/doc-id.pdf
    
    pdf_file_size_bytes INTEGER,
    
    pdf_page_count INTEGER,
    
    cover_image_url TEXT,
    -- Cover image used
    
    cover_image_source VARCHAR(20) CHECK (cover_image_source IN ('freepik', 'uploaded', 'generated', 'none')),
    
    cover_image_freepik_id VARCHAR(100),
    -- For attribution if using Freepik
    
    -- ═══════════════════════════════════════════════════════════════
    -- GENERATION INPUT (What was requested)
    -- ═══════════════════════════════════════════════════════════════
    
    input_tone VARCHAR(20),
    input_industry VARCHAR(255),
    input_keywords TEXT[],
    input_related_services TEXT[],
    input_custom_direction TEXT,
    input_additional_context TEXT,
    input_website_url TEXT,
    
    -- ═══════════════════════════════════════════════════════════════
    -- RESEARCH DATA (For transparency)
    -- ═══════════════════════════════════════════════════════════════
    
    research_sources JSONB DEFAULT '[]'::jsonb,
    /*
    [
        {
            "url": "https://example.com/article",
            "title": "Article Title",
            "domain": "example.com",
            "snippet": "Relevant excerpt...",
            "accessed_at": "2026-01-02T10:15:00Z",
            "relevance_score": 0.85
        }
    ]
    */
    
    research_source_count INTEGER DEFAULT 0,
    -- Number of sources consulted
    
    research_duration_seconds INTEGER,
    -- How long research took
    
    -- ═══════════════════════════════════════════════════════════════
    -- STATUS & WORKFLOW
    -- ═══════════════════════════════════════════════════════════════
    
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN (
        'pending',      -- Queued, not started
        'generating',   -- In progress
        'ready',        -- Complete, awaiting review/distribution
        'distributed',  -- Published to social channels
        'failed'        -- Generation failed
    )),
    
    -- ═══════════════════════════════════════════════════════════════
    -- GENERATION TIMING
    -- ═══════════════════════════════════════════════════════════════
    
    generation_queued_at TIMESTAMP WITH TIME ZONE,
    generation_started_at TIMESTAMP WITH TIME ZONE,
    generation_completed_at TIMESTAMP WITH TIME ZONE,
    generation_duration_seconds INTEGER,
    
    -- ═══════════════════════════════════════════════════════════════
    -- GENERATION ERROR (if failed)
    -- ═══════════════════════════════════════════════════════════════
    
    generation_error_code VARCHAR(20),
    -- GEN_001, GEN_002, etc.
    
    generation_error_message TEXT,
    -- Human-readable error
    
    generation_error_step VARCHAR(50),
    -- Which step failed
    
    generation_retry_count INTEGER DEFAULT 0,
    -- How many times generation was retried
    
    -- ═══════════════════════════════════════════════════════════════
    -- DISTRIBUTION
    -- ═══════════════════════════════════════════════════════════════
    
    distributed_at TIMESTAMP WITH TIME ZONE,
    distributed_by_user_id UUID REFERENCES users(id),
    
    distribution_channels JSONB DEFAULT '{}'::jsonb,
    /*
    {
        "linkedin": true,
        "facebook": true,
        "twitter": false,
        "google_business": false
    }
    */
    
    distribution_results JSONB DEFAULT '{}'::jsonb,
    /*
    {
        "linkedin": {
            "success": true,
            "post_id": "urn:li:share:123456789",
            "post_url": "https://linkedin.com/feed/update/...",
            "posted_at": "2026-01-02T11:00:00Z"
        },
        "facebook": {
            "success": false,
            "error_code": "OAUTH_EXPIRED",
            "error_message": "Access token expired"
        }
    }
    */
    
    -- ═══════════════════════════════════════════════════════════════
    -- API USAGE TRACKING
    -- ═══════════════════════════════════════════════════════════════
    
    api_tokens_input INTEGER DEFAULT 0,
    -- Input tokens used
    
    api_tokens_output INTEGER DEFAULT 0,
    -- Output tokens used
    
    api_cost_cents INTEGER DEFAULT 0,
    -- Estimated cost in cents
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMPS & RETENTION
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (NOW() + INTERVAL '3 years'),
    -- Document retention: 3 years
    
    deleted_at TIMESTAMP WITH TIME ZONE
    -- Soft delete timestamp
);

-- Indexes
CREATE INDEX idx_documents_client_id ON documents(client_id);
CREATE INDEX idx_documents_status ON documents(status);
CREATE INDEX idx_documents_created_at ON documents(created_at DESC);
CREATE INDEX idx_documents_expires_at ON documents(expires_at);
CREATE INDEX idx_documents_template_id ON documents(template_id);
CREATE INDEX idx_documents_created_by ON documents(created_by_user_id);

-- Partial index for active documents (common query)
CREATE INDEX idx_documents_active ON documents(client_id, created_at DESC) 
    WHERE status != 'failed' AND deleted_at IS NULL;

-- Index for cleanup job
CREATE INDEX idx_documents_expired ON documents(expires_at)
    WHERE deleted_at IS NULL;

-- Full-text search on title and topic
CREATE INDEX idx_documents_search ON documents 
    USING gin(to_tsvector('english', title || ' ' || topic));

-- Scheduled content for automated generation
-- Uploaded via CSV or created individually

CREATE TABLE scheduled_content (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- RELATIONSHIPS
    -- ═══════════════════════════════════════════════════════════════
    
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    -- Who scheduled this content
    
    -- ═══════════════════════════════════════════════════════════════
    -- SCHEDULE
    -- ═══════════════════════════════════════════════════════════════
    
    scheduled_date DATE NOT NULL,
    -- Date to generate: 2026-01-15
    
    scheduled_time TIME NOT NULL DEFAULT '09:00:00',
    -- Time to generate: 09:00:00
    
    timezone VARCHAR(50) NOT NULL DEFAULT 'America/New_York',
    -- Timezone for scheduling
    
    -- Computed scheduled datetime (for queries)
    scheduled_at TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS (
        (scheduled_date + scheduled_time) AT TIME ZONE timezone
    ) STORED,
    
    -- ═══════════════════════════════════════════════════════════════
    -- CONTENT SETTINGS
    -- ═══════════════════════════════════════════════════════════════
    
    topic VARCHAR(500) NOT NULL,
    -- Content topic
    
    template_code VARCHAR(50),
    -- Template to use (references templates.code)
    
    tone VARCHAR(20) DEFAULT 'professional' 
        CHECK (tone IN ('professional', 'casual', 'authoritative')),
    
    keywords TEXT[],
    related_services TEXT[],
    custom_direction TEXT,
    additional_context TEXT,
    
    -- ═══════════════════════════════════════════════════════════════
    -- AUTO-DISTRIBUTION SETTINGS
    -- ═══════════════════════════════════════════════════════════════
    
    auto_distribute BOOLEAN NOT NULL DEFAULT FALSE,
    -- If TRUE, distribute immediately after generation
    
    distribution_channels JSONB DEFAULT '{}'::jsonb,
    -- {"linkedin": true, "facebook": true, ...}
    
    -- ═══════════════════════════════════════════════════════════════
    -- STATUS
    -- ═══════════════════════════════════════════════════════════════
    
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN (
        'pending',      -- Waiting for scheduled time
        'processing',   -- Currently generating
        'completed',    -- Successfully generated
        'failed',       -- Generation failed
        'canceled'      -- Manually canceled
    )),
    
    -- ═══════════════════════════════════════════════════════════════
    -- RESULT
    -- ═══════════════════════════════════════════════════════════════
    
    document_id UUID REFERENCES documents(id) ON DELETE SET NULL,
    -- Created document (if successful)
    
    processed_at TIMESTAMP WITH TIME ZONE,
    -- When generation was attempted
    
    error_message TEXT,
    -- Error if failed
    
    retry_count INTEGER NOT NULL DEFAULT 0,
    -- Number of retry attempts
    
    -- ═══════════════════════════════════════════════════════════════
    -- IMPORT METADATA
    -- ═══════════════════════════════════════════════════════════════
    
    import_batch_id UUID,
    -- If imported via CSV, batch identifier
    
    import_row_number INTEGER,
    -- Row number in CSV
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMPS
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_scheduled_content_client_id ON scheduled_content(client_id);
CREATE INDEX idx_scheduled_content_scheduled_at ON scheduled_content(scheduled_at);
CREATE INDEX idx_scheduled_content_status ON scheduled_content(status);
CREATE INDEX idx_scheduled_content_import_batch ON scheduled_content(import_batch_id) 
    WHERE import_batch_id IS NOT NULL;

-- Index for scheduler query (find pending items ready to process)
CREATE INDEX idx_scheduled_content_pending_ready ON scheduled_content(scheduled_at)
    WHERE status = 'pending';

-- Tracks real-time progress of document generation
-- One job per document generation attempt

CREATE TABLE generation_jobs (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- RELATIONSHIPS
    -- ═══════════════════════════════════════════════════════════════
    
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    
    -- ═══════════════════════════════════════════════════════════════
    -- CELERY TASK INFO
    -- ═══════════════════════════════════════════════════════════════
    
    celery_task_id VARCHAR(255),
    -- Celery task UUID for status lookup
    
    worker_id VARCHAR(100),
    -- Which worker is processing
    
    -- ═══════════════════════════════════════════════════════════════
    -- PROGRESS TRACKING
    -- ═══════════════════════════════════════════════════════════════
    
    current_step VARCHAR(50),
    -- Current step ID: 'researching', 'writing', 'rendering'
    
    current_step_label VARCHAR(100),
    -- Human-readable: 'Researching Keywords'
    
    current_step_detail TEXT,
    -- Detail text: 'Reading 47 sources...'
    
    steps_completed TEXT[] DEFAULT '{}',
    -- List of completed step IDs
    
    progress_percent INTEGER NOT NULL DEFAULT 0 
        CHECK (progress_percent >= 0 AND progress_percent <= 100),
    
    -- ═══════════════════════════════════════════════════════════════
    -- STEP TIMINGS (Performance Analysis)
    -- ═══════════════════════════════════════════════════════════════
    
    step_timings JSONB DEFAULT '{}'::jsonb,
    /*
    {
        "topic_analysis": {
            "started_at": "2026-01-02T10:30:00Z",
            "completed_at": "2026-01-02T10:30:05Z",
            "duration_ms": 5000
        },
        "web_research": {
            "started_at": "2026-01-02T10:30:05Z",
            "completed_at": "2026-01-02T10:31:30Z",
            "duration_ms": 85000,
            "metadata": {
                "sources_found": 127,
                "sources_used": 47
            }
        }
    }
    */
    
    -- ═══════════════════════════════════════════════════════════════
    -- OVERALL TIMING
    -- ═══════════════════════════════════════════════════════════════
    
    queued_at TIMESTAMP WITH TIME ZONE,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    
    total_duration_ms INTEGER,
    -- Total time from start to complete
    
    -- ═══════════════════════════════════════════════════════════════
    -- ERROR INFO
    -- ═══════════════════════════════════════════════════════════════
    
    error_occurred BOOLEAN NOT NULL DEFAULT FALSE,
    error_step VARCHAR(50),
    error_code VARCHAR(20),
    error_message TEXT,
    error_traceback TEXT,
    -- Full traceback for debugging (not shown to users)
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMPS
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_generation_jobs_document_id ON generation_jobs(document_id);
CREATE INDEX idx_generation_jobs_celery_task_id ON generation_jobs(celery_task_id) 
    WHERE celery_task_id IS NOT NULL;
CREATE INDEX idx_generation_jobs_created_at ON generation_jobs(created_at DESC);

-- API keys for programmatic access
-- Agencies can create multiple keys with different scopes

CREATE TABLE api_keys (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- OWNERSHIP
    -- ═══════════════════════════════════════════════════════════════
    
    agency_id UUID NOT NULL REFERENCES agencies(id) ON DELETE CASCADE,
    
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    
    -- ═══════════════════════════════════════════════════════════════
    -- KEY IDENTIFICATION
    -- ═══════════════════════════════════════════════════════════════
    
    name VARCHAR(100) NOT NULL,
    -- User-friendly name: "Production Key", "Staging Key"
    
    description TEXT,
    -- Optional description
    
    -- ═══════════════════════════════════════════════════════════════
    -- KEY VALUE (Hashed)
    -- ═══════════════════════════════════════════════════════════════
    
    key_hash VARCHAR(255) NOT NULL,
    -- SHA-256 hash of the full key
    -- Original key only shown once at creation
    
    key_prefix VARCHAR(12) NOT NULL,
    -- First 8 chars for identification: "csa_live_ab"
    -- Format: csa_live_XXXXXXXX... or csa_test_XXXXXXXX...
    
    -- ═══════════════════════════════════════════════════════════════
    -- PERMISSIONS
    -- ═══════════════════════════════════════════════════════════════
    
    scopes TEXT[] NOT NULL DEFAULT '{}',
    -- Allowed operations:
    -- ['documents:create', 'documents:read', 'documents:distribute',
    --  'clients:read', 'schedule:create', 'schedule:read']
    
    -- ═══════════════════════════════════════════════════════════════
    -- RESTRICTIONS
    -- ═══════════════════════════════════════════════════════════════
    
    allowed_ips TEXT[],
    -- IP whitelist (NULL = all IPs allowed)
    -- ['192.168.1.1', '10.0.0.0/8']
    
    rate_limit_per_minute INTEGER DEFAULT 60,
    -- Requests per minute
    
    rate_limit_per_day INTEGER DEFAULT 10000,
    -- Requests per day
    
    -- ═══════════════════════════════════════════════════════════════
    -- STATUS
    -- ═══════════════════════════════════════════════════════════════
    
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    
    expires_at TIMESTAMP WITH TIME ZONE,
    -- NULL = never expires
    
    -- ═══════════════════════════════════════════════════════════════
    -- USAGE TRACKING
    -- ═══════════════════════════════════════════════════════════════
    
    last_used_at TIMESTAMP WITH TIME ZONE,
    last_used_ip VARCHAR(45),
    
    total_requests INTEGER NOT NULL DEFAULT 0,
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMPS
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    revoked_at TIMESTAMP WITH TIME ZONE,
    revoked_by_user_id UUID REFERENCES users(id)
);

-- Indexes
CREATE INDEX idx_api_keys_agency_id ON api_keys(agency_id);
CREATE INDEX idx_api_keys_key_prefix ON api_keys(key_prefix);
CREATE INDEX idx_api_keys_is_active ON api_keys(is_active) WHERE is_active = TRUE;
CREATE UNIQUE INDEX idx_api_keys_key_hash ON api_keys(key_hash);

-- Comprehensive audit trail for compliance and debugging

CREATE TABLE audit_logs (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ═══════════════════════════════════════════════════════════════
    -- WHO
    -- ═══════════════════════════════════════════════════════════════
    
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    -- NULL if action was system-initiated
    
    agency_id UUID REFERENCES agencies(id) ON DELETE SET NULL,
    -- Context agency
    
    api_key_id UUID REFERENCES api_keys(id) ON DELETE SET NULL,
    -- If action via API key
    
    -- ═══════════════════════════════════════════════════════════════
    -- WHAT
    -- ═══════════════════════════════════════════════════════════════
    
    action VARCHAR(50) NOT NULL,
    -- 'create', 'update', 'delete', 'login', 'logout', 'generate',
    -- 'distribute', 'export', 'import', 'api_key_create', etc.
    
    action_category VARCHAR(30) NOT NULL,
    -- 'auth', 'resource', 'generation', 'distribution', 'admin'
    
    resource_type VARCHAR(50) NOT NULL,
    -- 'user', 'client', 'document', 'agency', 'template', 'api_key'
    
    resource_id UUID,
    -- ID of affected resource
    
    resource_name VARCHAR(255),
    -- Human-readable identifier (for deleted resources)
    
    -- ═══════════════════════════════════════════════════════════════
    -- DETAILS
    -- ═══════════════════════════════════════════════════════════════
    
    changes JSONB,
    -- For updates: {"field": {"old": "...", "new": "..."}}
    
    metadata JSONB,
    -- Additional context
    -- {
    --   "reason": "User requested",
    --   "source": "web_ui",
    --   "duration_ms": 1500
    -- }
    
    status VARCHAR(20) DEFAULT 'success' CHECK (status IN ('success', 'failure', 'partial')),
    
    error_message TEXT,
    -- If status != 'success'
    
    -- ═══════════════════════════════════════════════════════════════
    -- REQUEST CONTEXT
    -- ═══════════════════════════════════════════════════════════════
    
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_id VARCHAR(36),
    -- Correlation ID from request
    
    -- ═══════════════════════════════════════════════════════════════
    -- TIMESTAMP
    -- ═══════════════════════════════════════════════════════════════
    
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Indexes for common queries
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX idx_audit_logs_agency_id ON audit_logs(agency_id) WHERE agency_id IS NOT NULL;
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at DESC);

-- Composite index for agency audit queries
CREATE INDEX idx_audit_logs_agency_time ON audit_logs(agency_id, created_at DESC)
    WHERE agency_id IS NOT NULL;

-- Consider partitioning by month for large deployments
-- This table can grow very large

-- Function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at
DO $$
DECLARE
    t text;
BEGIN
    FOR t IN 
        SELECT table_name 
        FROM information_schema.columns 
        WHERE column_name = 'updated_at' 
        AND table_schema = 'public'
    LOOP
        EXECUTE format('
            DROP TRIGGER IF EXISTS set_updated_at ON %I;
            CREATE TRIGGER set_updated_at
            BEFORE UPDATE ON %I
            FOR EACH ROW
            EXECUTE FUNCTION trigger_set_updated_at();
        ', t, t);
    END LOOP;
END;
$$;

-- Prevent agencies from exceeding their seat limit
CREATE OR REPLACE FUNCTION check_agency_seat_limit()
RETURNS TRIGGER AS $$
DECLARE
    current_count INTEGER;
    max_allowed INTEGER;
    agency_name VARCHAR;
BEGIN
    -- Only check on INSERT or when activating a deactivated client
    IF TG_OP = 'UPDATE' AND (NEW.is_active = OLD.is_active) THEN
        RETURN NEW;
    END IF;
    
    IF TG_OP = 'UPDATE' AND NEW.is_active = FALSE THEN
        RETURN NEW;  -- Always allow deactivation
    END IF;
    
    -- Count current active clients for this agency
    SELECT COUNT(*) INTO current_count
    FROM clients
    WHERE agency_id = NEW.agency_id 
    AND is_active = TRUE
    AND id != COALESCE(NEW.id, '00000000-0000-0000-0000-000000000000'::uuid);
    
    -- Get max seats from agency's plan
    SELECT p.max_seats, a.name INTO max_allowed, agency_name
    FROM agencies a
    JOIN plans p ON a.plan_id = p.id
    WHERE a.id = NEW.agency_id;
    
    -- Check limit
    IF current_count >= max_allowed THEN
        RAISE EXCEPTION 'Seat limit exceeded for agency "%". Maximum % clients allowed on current plan.', 
            agency_name, max_allowed
        USING ERRCODE = 'check_violation';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_seat_limit
BEFORE INSERT OR UPDATE ON clients
FOR EACH ROW
EXECUTE FUNCTION check_agency_seat_limit();

-- Update client statistics when documents change
CREATE OR REPLACE FUNCTION update_client_document_stats()
RETURNS TRIGGER AS $$
BEGIN
    -- Update on INSERT
    IF TG_OP = 'INSERT' THEN
        UPDATE clients SET
            total_documents_generated = total_documents_generated + 1,
            last_document_generated_at = NEW.created_at
        WHERE id = NEW.client_id;
        RETURN NEW;
    END IF;
    
    -- Update on status change to 'distributed'
    IF TG_OP = 'UPDATE' AND OLD.status != 'distributed' AND NEW.status = 'distributed' THEN
        UPDATE clients SET
            total_documents_distributed = total_documents_distributed + 1,
            last_document_distributed_at = NEW.distributed_at
        WHERE id = NEW.client_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_client_stats
AFTER INSERT OR UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION update_client_document_stats();

-- Generate URL-safe slug from text
CREATE OR REPLACE FUNCTION generate_slug(input_text TEXT)
RETURNS TEXT AS $$
DECLARE
    slug TEXT;
BEGIN
    -- Convert to lowercase
    slug := LOWER(input_text);
    
    -- Replace spaces and underscores with hyphens
    slug := REGEXP_REPLACE(slug, '[\s_]+', '-', 'g');
    
    -- Remove non-alphanumeric characters (except hyphens)
    slug := REGEXP_REPLACE(slug, '[^a-z0-9-]', '', 'g');
    
    -- Remove multiple consecutive hyphens
    slug := REGEXP_REPLACE(slug, '-+', '-', 'g');
    
    -- Remove leading/trailing hyphens
    slug := TRIM(BOTH '-' FROM slug);
    
    -- Truncate to reasonable length
    slug := LEFT(slug, 100);
    
    RETURN slug;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

# Access Token Payload
{
    # Standard Claims
    "sub": "550e8400-e29b-41d4-a716-446655440000",  # User ID
    "iat": 1704481490,                               # Issued At (Unix timestamp)
    "exp": 1704482390,                               # Expiration (15 min from iat)
    "jti": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",  # JWT ID (unique)
    
    # Custom Claims
    "type": "access",
    "role": "agency_admin",                          # User role
    "agency_id": "660e8400-e29b-41d4-a716-446655440001",  # Agency context
    "client_id": null,                               # Only for client role
    "email": "user@agency.com",
    
    # Session Info
    "session_id": "sess_abc123",                     # For session tracking
}

# Refresh Token Payload
{
    "sub": "550e8400-e29b-41d4-a716-446655440000",  # User ID
    "iat": 1704481490,
    "exp": 1705086290,                              # 7 days from iat
    "jti": "b2c3d4e5-f6a7-8901-bcde-f23456789012",
    "type": "refresh",
    "session_id": "sess_abc123",
}

# app/config.py

class AuthSettings(BaseSettings):
    # JWT Configuration
    JWT_SECRET_KEY: str  # From environment
    JWT_ALGORITHM: str = "HS256"
    
    # Token Lifetimes
    ACCESS_TOKEN_EXPIRE_MINUTES: int = 15
    REFRESH_TOKEN_EXPIRE_DAYS: int = 7
    
    # Security
    PASSWORD_MIN_LENGTH: int = 8
    PASSWORD_REQUIRE_UPPERCASE: bool = True
    PASSWORD_REQUIRE_LOWERCASE: bool = True
    PASSWORD_REQUIRE_DIGIT: bool = True
    PASSWORD_REQUIRE_SPECIAL: bool = False
    
    # Account Security
    MAX_LOGIN_ATTEMPTS: int = 5
    LOCKOUT_DURATION_MINUTES: int = 15
    
    # Session
    SESSION_COOKIE_NAME: str = "refresh_token"
    SESSION_COOKIE_SECURE: bool = True  # HTTPS only
    SESSION_COOKIE_HTTPONLY: bool = True
    SESSION_COOKIE_SAMESITE: str = "lax"