┌─────────────────────────────────────────────────────────────────────────────┐
│                              PSTN NETWORK                                    │
│                    (Traditional Phone System)                                │
│                                                                              │
│    📱 Caller's Phone ─────────────────────────────────────────┐             │
│                                                                │             │
└────────────────────────────────────────────────────────────────│─────────────┘
                                                                 │
                                                                 ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                           GOTOCONNECT                                        │
│                    (Cloud Telephony Provider)                                │
│                                                                              │
│    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐                   │
│    │   Phone     │    │    Call     │    │  Webhook    │                   │
│    │  Numbers    │    │   Control   │    │   Events    │                   │
│    │   (DIDs)    │    │    API      │    │             │                   │
│    └─────────────┘    └─────────────┘    └──────┬──────┘                   │
│                                                  │                          │
└──────────────────────────────────────────────────│──────────────────────────┘
                                                   │
                    ┌──────────────────────────────┼───────────────────┐
                    │                              │                   │
                    ▼                              ▼                   │
┌─────────────────────────────────────┐  ┌─────────────────────────┐  │
│         ORCHESTRATION LAYER         │  │    WEBRTC BRIDGE        │  │
│              (n8n)                   │  │      SERVICE            │  │
│                                      │  │                         │  │
│  ┌────────────────────────────────┐ │  │  ┌───────────────────┐  │  │
│  │  • Receive call webhooks       │ │  │  │ Ooma WebRTC       │  │  │
│  │  • Route to appropriate flow   │ │  │  │ Softphone         │  │  │
│  │  • Trigger call setup          │ │  │  │ (Browser-based)   │  │  │
│  │  • Handle post-call processing │ │  │  └─────────┬─────────┘  │  │
│  └────────────────────────────────┘ │  │            │            │  │
│                                      │  │  ┌─────────▼─────────┐  │  │
└──────────────────────────────────────┘  │  │ Audio Capture &   │  │  │
                                          │  │ Forwarding        │  │  │
                                          │  └─────────┬─────────┘  │  │
                                          │            │            │  │
                                          └────────────│────────────┘  │
                                                       │               │
                                                       ▼               │
┌─────────────────────────────────────────────────────────────────────────────┐
│                            LIVEKIT CLOUD                                     │
│                    (Real-Time Media Infrastructure)                          │
│                                                                              │
│    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐                   │
│    │    Room     │    │   Audio     │    │  Recording  │                   │
│    │ Management  │    │   Routing   │    │   (Egress)  │                   │
│    └─────────────┘    └─────────────┘    └─────────────┘                   │
│                              │                                              │
│                              │ Audio Streams                                │
│                              ▼                                              │
└──────────────────────────────│──────────────────────────────────────────────┘
                               │
                               │
                               ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         AI AGENT SERVICE                                     │
│                 (LiveKit Agents Framework + Our Logic)                       │
│                                                                              │
│    ┌─────────────────────────────────────────────────────────────────────┐  │
│    │                        VOICE PIPELINE                                │  │
│    │                                                                      │  │
│    │   ┌─────────┐      ┌─────────┐      ┌─────────┐      ┌─────────┐   │  │
│    │   │ CALLER  │      │  STT    │      │  LLM    │      │  TTS    │   │  │
│    │   │ AUDIO   │─────▶│Deepgram │─────▶│ Claude  │─────▶│Chatter- │   │  │
│    │   │   IN    │      │ Nova-2  │      │ Sonnet  │      │  box    │   │  │
│    │   └─────────┘      └─────────┘      └────┬────┘      └────┬────┘   │  │
│    │                                          │                │        │  │
│    │                                          ▼                │        │  │
│    │                                    ┌──────────┐           │        │  │
│    │                                    │ Knowledge│           │        │  │
│    │                                    │   Base   │           │        │  │
│    │                                    │  (RAG)   │           │        │  │
│    │                                    └──────────┘           │        │  │
│    │                                                           ▼        │  │
│    │   ┌─────────┐                                      ┌─────────┐    │  │
│    │   │ AGENT   │◀─────────────────────────────────────│  AUDIO  │    │  │
│    │   │ AUDIO   │                                      │   OUT   │    │  │
│    │   │   OUT   │                                      │         │    │  │
│    │   └─────────┘                                      └─────────┘    │  │
│    │                                                                      │  │
│    └─────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘
                               │
                               │ Writes to
                               ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                          DATA LAYER                                          │
│                                                                              │
│    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐                   │
│    │ PostgreSQL  │    │   Redis     │    │   S3/DO    │                   │
│    │  (Primary   │    │  (Cache,    │    │  Spaces    │                   │
│    │   Data)     │    │   State)    │    │(Recordings)│                   │
│    └─────────────┘    └─────────────┘    └─────────────┘                   │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘
                               │
                               │ Powers
                               ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                      MANAGEMENT LAYER                                        │
│                                                                              │
│    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐                   │
│    │   REST API  │    │  Web UI     │    │  Webhooks   │                   │
│    │  (Backend)  │    │ (Frontend)  │    │   (Out)     │                   │
│    └─────────────┘    └─────────────┘    └─────────────┘                   │
│                                                                              │
│              Used by: Agencies, Tenants, Platform Admins                    │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

{
  "event": "call.ringing",
  "callId": "call-123456",
  "from": "+15559876543",
  "to": "+15551234567",
  "timestamp": "2026-01-25T10:00:00Z"
}

{
  "id": "call-123456",
  "tenant_id": "smile-dental",
  "from_number": "+15559876543",
  "to_number": "+15551234567",
  "direction": "inbound",
  "status": "completed",
  "started_at": "2026-01-25T10:00:00Z",
  "answered_at": "2026-01-25T10:00:01Z",
  "ended_at": "2026-01-25T10:03:00Z",
  "duration_seconds": 180,
  "recording_url": "https://storage.example.com/recordings/call-123456.wav",
  "transcript_id": "transcript-789",
  "outcome": "appointment_scheduled",
  "cost_cents": 45
}

GOTOCONNECT_CLIENT_ID=your_client_id_here
GOTOCONNECT_CLIENT_SECRET=your_client_secret_here
GOTOCONNECT_REDIRECT_URI=https://yourapp.com/oauth/callback
GOTOCONNECT_WEBHOOK_SECRET=your_webhook_secret

LIVEKIT_API_KEY=your_api_key_here
LIVEKIT_API_SECRET=your_api_secret_here
LIVEKIT_WS_URL=wss://your-project.livekit.cloud

DEEPGRAM_API_KEY=your_api_key_here

ANTHROPIC_API_KEY=your_api_key_here

RUNPOD_API_KEY=your_api_key_here
CHATTERBOX_ENDPOINT_URL=https://your-endpoint.runpod.ai

DO_API_TOKEN=your_token_here
DO_SPACES_KEY=your_spaces_key
DO_SPACES_SECRET=your_spaces_secret
DO_SPACES_ENDPOINT=nyc3.digitaloceanspaces.com
DO_SPACES_BUCKET=voice-aiconnected-recordings

DATABASE_URL=postgresql://postgres:password@localhost:5432/voice_aiconnected
REDIS_URL=redis://localhost:6379

DATABASE_URL=postgresql://user:pass@db-host:5432/voice_aiconnected?sslmode=require
REDIS_URL=rediss://user:pass@redis-host:6379

# Using nvm (recommended)
curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.0/install.sh | bash
nvm install 20
nvm use 20
node --version  # Should show v20.x.x

# Using pyenv (recommended)
curl https://pyenv.run | bash
pyenv install 3.11.7
pyenv global 3.11.7
python --version  # Should show 3.11.x

# macOS
brew install --cask docker

# Ubuntu
sudo apt-get update
sudo apt-get install docker.io docker-compose-v2
sudo usermod -aG docker $USER  # Log out and back in after this

# Verify
docker --version
docker compose version

# macOS
brew install postgresql@15

# Ubuntu
sudo apt-get install postgresql-client-15

# Verify
psql --version

# macOS
brew install redis

# Ubuntu
sudo apt-get install redis-tools

# Verify
redis-cli --version

# Should already be installed, verify:
git --version

# If not:
# macOS: xcode-select --install
# Ubuntu: sudo apt-get install git

{
  "python.defaultInterpreterPath": "~/.pyenv/shims/python",
  "editor.formatOnSave": true,
  "editor.defaultFormatter": "esbenp.prettier-vscode",
  "[python]": {
    "editor.defaultFormatter": "ms-python.black-formatter"
  },
  "files.exclude": {
    "**/__pycache__": true,
    "**/.pytest_cache": true,
    "**/node_modules": true
  }
}

# HTTPie - Better than curl for API testing
brew install httpie  # or pip install httpie

# jq - JSON processor
brew install jq  # or sudo apt-get install jq

# ngrok - Expose local server for webhooks
brew install ngrok  # or download from ngrok.com

# lazydocker - Docker TUI
brew install lazydocker

voice-aiconnected/
├── README.md                    # Project overview
├── CLAUDE-CODE-CONTINUATION-PROMPT.md  # Handoff document
├── docker-compose.yml           # Local development services
├── docker-compose.prod.yml      # Production compose file
├── .env.example                 # Example environment variables
├── .gitignore                   # Git ignore rules
│
├── docs/                        # Documentation
│   ├── JUNIOR-DEV-PRD-PART-01.md
│   ├── JUNIOR-DEV-PRD-PART-02.md
│   ├── ... (through PART-10)
│   ├── 01-SYSTEM-ARCHITECTURE-OVERVIEW.md
│   ├── 02-GOTOCONNECT-INTEGRATION-SPECIFICATION.md
│   ├── 03-VOICE-PIPELINE-ARCHITECTURE.md
│   ├── 04-WEBRTC-BRIDGE-TECHNICAL-DESIGN.md
│   └── 05-LIVEKIT-INTEGRATION-SPECIFICATION.md
│
├── services/                    # Backend services
│   │
│   ├── api/                     # REST API service
│   │   ├── Dockerfile
│   │   ├── requirements.txt
│   │   ├── pyproject.toml
│   │   ├── src/
│   │   │   ├── __init__.py
│   │   │   ├── main.py          # FastAPI application
│   │   │   ├── config.py        # Configuration
│   │   │   ├── database.py      # Database connection
│   │   │   ├── models/          # SQLAlchemy models
│   │   │   ├── schemas/         # Pydantic schemas
│   │   │   ├── routers/         # API route handlers
│   │   │   ├── services/        # Business logic
│   │   │   └── utils/           # Utilities
│   │   └── tests/
│   │
│   ├── agent/                   # AI Agent service
│   │   ├── Dockerfile
│   │   ├── requirements.txt
│   │   ├── pyproject.toml
│   │   ├── src/
│   │   │   ├── __init__.py
│   │   │   ├── main.py          # Agent entry point
│   │   │   ├── config.py
│   │   │   ├── agent.py         # Agent implementation
│   │   │   ├── pipeline/        # Voice pipeline components
│   │   │   │   ├── stt.py       # Speech-to-text
│   │   │   │   ├── llm.py       # LLM integration
│   │   │   │   ├── tts.py       # Text-to-speech
│   │   │   │   └── vad.py       # Voice activity detection
│   │   │   ├── knowledge/       # RAG implementation
│   │   │   └── actions/         # Agent actions (transfer, etc.)
│   │   └── tests/
│   │
│   ├── bridge/                  # WebRTC Bridge service
│   │   ├── Dockerfile
│   │   ├── package.json
│   │   ├── src/
│   │   │   ├── index.ts         # Entry point
│   │   │   ├── config.ts
│   │   │   ├── browser.ts       # Browser automation
│   │   │   ├── softphone.ts     # Ooma softphone control
│   │   │   ├── livekit.ts       # LiveKit connection
│   │   │   └── audio.ts         # Audio routing
│   │   └── tests/
│   │
│   └── worker/                  # Background job worker
│       ├── Dockerfile
│       ├── requirements.txt
│       ├── src/
│       │   ├── __init__.py
│       │   ├── main.py
│       │   ├── jobs/            # Job definitions
│       │   └── utils/
│       └── tests/
│
├── web/                         # Frontend application
│   ├── Dockerfile
│   ├── package.json
│   ├── next.config.js
│   ├── src/
│   │   ├── app/                 # Next.js app router
│   │   ├── components/          # React components
│   │   ├── lib/                 # Utilities
│   │   └── styles/              # CSS
│   └── tests/
│
├── migrations/                  # Database migrations
│   ├── alembic.ini
│   ├── env.py
│   └── versions/                # Migration files
│
├── scripts/                     # Utility scripts
│   ├── setup-dev.sh             # Development setup
│   ├── seed-data.py             # Seed database
│   └── deploy.sh                # Deployment script
│
└── infra/                       # Infrastructure configuration
    ├── dokploy/                 # Dokploy configuration
    ├── nginx/                   # Nginx configuration
    └── monitoring/              # Monitoring setup

# =============================================================================
# ENVIRONMENT CONFIGURATION
# =============================================================================

# Environment: development, staging, production
NODE_ENV=development
PYTHON_ENV=development

# =============================================================================
# DATABASE
# =============================================================================

# PostgreSQL connection string
DATABASE_URL=postgresql://postgres:password@localhost:5432/voice_aiconnected

# Redis connection string
REDIS_URL=redis://localhost:6379

# =============================================================================
# GOTOCONNECT (TELEPHONY)
# =============================================================================

# OAuth 2.0 credentials
GOTOCONNECT_CLIENT_ID=your_client_id
GOTOCONNECT_CLIENT_SECRET=your_client_secret
GOTOCONNECT_REDIRECT_URI=http://localhost:3000/oauth/gotoconnect/callback

# API base URL
GOTOCONNECT_API_URL=https://api.goto.com

# Webhook validation
GOTOCONNECT_WEBHOOK_SECRET=your_webhook_secret

# Ooma Softphone credentials (for browser automation)
OOMA_USERNAME=your_ooma_username
OOMA_PASSWORD=your_ooma_password

# =============================================================================
# LIVEKIT
# =============================================================================

# LiveKit Cloud credentials
LIVEKIT_API_KEY=your_api_key
LIVEKIT_API_SECRET=your_api_secret
LIVEKIT_WS_URL=wss://your-project.livekit.cloud

# Webhook validation
LIVEKIT_WEBHOOK_SECRET=your_webhook_secret

# =============================================================================
# DEEPGRAM (STT)
# =============================================================================

DEEPGRAM_API_KEY=your_api_key

# Model configuration
DEEPGRAM_MODEL=nova-2
DEEPGRAM_LANGUAGE=en-US

# =============================================================================
# ANTHROPIC (LLM)
# =============================================================================

ANTHROPIC_API_KEY=your_api_key

# Model configuration
ANTHROPIC_MODEL=claude-sonnet-4-20250514
ANTHROPIC_MAX_TOKENS=1024

# =============================================================================
# CHATTERBOX (TTS)
# =============================================================================

# RunPod endpoint
CHATTERBOX_ENDPOINT_URL=https://your-endpoint.runpod.ai
RUNPOD_API_KEY=your_api_key

# Voice configuration
CHATTERBOX_DEFAULT_VOICE=default
CHATTERBOX_SAMPLE_RATE=24000

# =============================================================================
# OBJECT STORAGE (RECORDINGS)
# =============================================================================

# DigitalOcean Spaces (S3-compatible)
DO_SPACES_KEY=your_key
DO_SPACES_SECRET=your_secret
DO_SPACES_ENDPOINT=nyc3.digitaloceanspaces.com
DO_SPACES_BUCKET=voice-aiconnected-recordings
DO_SPACES_REGION=nyc3

# =============================================================================
# AUTHENTICATION
# =============================================================================

# JWT configuration
JWT_SECRET=your_very_long_random_secret_at_least_32_characters
JWT_ALGORITHM=HS256
JWT_EXPIRATION_HOURS=24

# Refresh token
REFRESH_TOKEN_SECRET=another_very_long_random_secret
REFRESH_TOKEN_EXPIRATION_DAYS=30

# =============================================================================
# APPLICATION
# =============================================================================

# API service
API_HOST=0.0.0.0
API_PORT=8000
API_BASE_URL=http://localhost:8000

# Frontend
NEXT_PUBLIC_API_URL=http://localhost:8000
NEXT_PUBLIC_WS_URL=ws://localhost:8000

# CORS
CORS_ORIGINS=http://localhost:3000,http://localhost:8000

# =============================================================================
# WEBHOOKS (INBOUND)
# =============================================================================

# n8n webhook URLs (where GoToConnect/LiveKit send events)
N8N_WEBHOOK_BASE_URL=http://localhost:5678/webhook

# =============================================================================
# LOGGING & MONITORING
# =============================================================================

# Log level: DEBUG, INFO, WARNING, ERROR
LOG_LEVEL=INFO

# Sentry (error tracking)
SENTRY_DSN=https://your_sentry_dsn

# =============================================================================
# FEATURE FLAGS
# =============================================================================

# Enable/disable features
FEATURE_OUTBOUND_CALLS=false
FEATURE_RECORDING=true
FEATURE_TRANSCRIPTION=true

# =============================================================================
# RATE LIMITING
# =============================================================================

# API rate limits
RATE_LIMIT_PER_MINUTE=100
RATE_LIMIT_PER_HOUR=1000

# =============================================================================
# DEVELOPMENT ONLY
# =============================================================================

# Debug mode (never enable in production)
DEBUG=true

# Skip webhook signature validation (never in production)
SKIP_WEBHOOK_VALIDATION=false

# Use mock services (for testing without external APIs)
USE_MOCK_STT=false
USE_MOCK_LLM=false
USE_MOCK_TTS=false

git clone https://github.com/oxfordpierpont/Voice-aiConnected.git
cd Voice-aiConnected

cp .env.example .env

# Start PostgreSQL, Redis, and other infrastructure
docker compose up -d postgres redis

# Verify they're running
docker compose ps

# Create database
docker compose exec postgres psql -U postgres -c "CREATE DATABASE voice_aiconnected;"

# Run migrations
cd services/api
python -m alembic upgrade head

# Seed development data (optional)
cd ../../scripts
python seed-data.py

# From project root
docker compose up -d api agent bridge worker

# View logs
docker compose logs -f api

cd services/api
python -m venv venv
source venv/bin/activate
pip install -r requirements.txt
uvicorn src.main:app --reload --port 8000

cd services/agent
python -m venv venv
source venv/bin/activate
pip install -r requirements.txt
python src/main.py

cd services/bridge
npm install
npm run dev

cd web
npm install
npm run dev

docker compose up -d n8n

# Start ngrok tunnel
ngrok http 8000

# Note the URL, e.g., https://abc123.ngrok.io
# Configure this URL in GoToConnect webhook settings

# Check API health
curl http://localhost:8000/health
# Expected: {"status": "healthy", "version": "0.1.0"}

# Check database connection
curl http://localhost:8000/health/db
# Expected: {"status": "connected"}

# Check Redis connection
curl http://localhost:8000/health/redis
# Expected: {"status": "connected"}

Connection refused to localhost:5432

Connection refused to localhost:6379

401 Unauthorized from Deepgram/Anthropic/etc

GoToConnect shows webhook failed

Puppeteer cannot launch browser

Error: listen EADDRINUSE: address already in use :::8000

- **Foreign keys**: `{referenced_table_singular}_id`: `tenant_id`, `user_id`, `call_id`  

Format: `ix_{table}_{column(s)}`

**Primary Keys**: `pk_{table}`

**Foreign Keys**: `fk_{table}_{referenced_table}`

**Unique Constraints**: `uq_{table}_{column(s)}`

**Check Constraints**: `ck_{table}_{description}`

Format: `{table}_{column}_enum` or descriptive name

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Applied to each table:
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

deleted_at TIMESTAMPTZ DEFAULT NULL

SELECT * FROM tenants WHERE deleted_at IS NULL;

SELECT * FROM tenants WHERE deleted_at IS NOT NULL;

settings JSONB NOT NULL DEFAULT '{}'::jsonb,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb

CREATE TYPE call_status_enum AS ENUM (
    'pending',
    'ringing',
    'answered',
    'completed',
    'failed',
    'cancelled'
);

-- Usage in table:
status call_status_enum NOT NULL DEFAULT 'pending'

tenant_id UUID NOT NULL REFERENCES tenants(id)

-- CORRECT: Filter by tenant
SELECT * FROM calls WHERE tenant_id = $1 AND id = $2;

-- WRONG: No tenant filter (data leak risk)
SELECT * FROM calls WHERE id = $1;

created_by UUID REFERENCES users(id),
updated_by UUID REFERENCES users(id)

-- =============================================================================
-- AGENCIES TABLE
-- =============================================================================
-- Agencies are businesses that resell Voice by aiConnected to their clients.
-- Each agency can have multiple tenants (their clients).
-- =============================================================================

CREATE TABLE agencies (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Basic Information
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL,  -- URL-friendly identifier: "oxford-pierpont"
    
    -- Contact Information
    contact_email VARCHAR(255) NOT NULL,
    contact_phone VARCHAR(50),
    contact_name VARCHAR(255),
    
    -- Address (for billing/legal)
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(2) DEFAULT 'US',  -- ISO 3166-1 alpha-2
    
    -- Business Details
    company_name VARCHAR(255),  -- Legal company name if different from "name"
    tax_id VARCHAR(50),         -- EIN for US companies
    
    -- Status
    status VARCHAR(50) NOT NULL DEFAULT 'active',  -- active, suspended, cancelled
    is_verified BOOLEAN NOT NULL DEFAULT FALSE,     -- Email/identity verified
    
    -- Limits & Quotas
    max_tenants INTEGER NOT NULL DEFAULT 100,       -- Maximum tenants allowed
    max_concurrent_calls INTEGER NOT NULL DEFAULT 50, -- Across all tenants
    
    -- Billing
    billing_email VARCHAR(255),
    stripe_customer_id VARCHAR(255),  -- For payment processing
    billing_plan VARCHAR(50) DEFAULT 'starter',  -- starter, growth, scale, enterprise
    
    -- Settings (flexible JSON for agency-specific config)
    settings JSONB NOT NULL DEFAULT '{}'::jsonb,
    -- Example settings:
    -- {
    --   "branding": {
    --     "logo_url": "https://...",
    --     "primary_color": "#1a73e8"
    --   },
    --   "defaults": {
    --     "voice_id": "default",
    --     "timezone": "America/New_York"
    --   },
    --   "notifications": {
    --     "email_on_new_tenant": true
    --   }
    -- }
    
    -- Metadata (for internal use)
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ DEFAULT NULL,
    
    -- Constraints
    CONSTRAINT uq_agencies_slug UNIQUE (slug),
    CONSTRAINT uq_agencies_contact_email UNIQUE (contact_email),
    CONSTRAINT ck_agencies_status CHECK (status IN ('active', 'suspended', 'cancelled'))
);

-- Indexes
CREATE INDEX ix_agencies_status ON agencies(status) WHERE deleted_at IS NULL;
CREATE INDEX ix_agencies_created_at ON agencies(created_at);
CREATE INDEX ix_agencies_billing_plan ON agencies(billing_plan) WHERE deleted_at IS NULL;

-- Trigger for updated_at
CREATE TRIGGER update_agencies_updated_at
    BEFORE UPDATE ON agencies
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comments
COMMENT ON TABLE agencies IS 'Businesses that resell Voice by aiConnected to their clients';
COMMENT ON COLUMN agencies.slug IS 'URL-friendly identifier, must be unique';
COMMENT ON COLUMN agencies.max_tenants IS 'Maximum number of tenants this agency can create';
COMMENT ON COLUMN agencies.settings IS 'Agency-specific configuration as JSON';

-- =============================================================================
-- TENANTS TABLE
-- =============================================================================
-- Tenants are end-customer businesses that use Voice AI.
-- Each tenant belongs to one agency.
-- Tenants have their own phone numbers, knowledge base, and configuration.
-- =============================================================================

CREATE TABLE tenants (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Relationship
    agency_id UUID NOT NULL REFERENCES agencies(id),
    
    -- Basic Information
    name VARCHAR(255) NOT NULL,           -- Display name: "Smile Dental"
    slug VARCHAR(100) NOT NULL,           -- Unique within agency: "smile-dental"
    
    -- Business Information
    business_type VARCHAR(100),           -- dental, legal, hvac, etc.
    timezone VARCHAR(50) NOT NULL DEFAULT 'America/New_York',
    
    -- Contact (for the business)
    contact_email VARCHAR(255),
    contact_phone VARCHAR(50),
    contact_name VARCHAR(255),
    website_url VARCHAR(500),
    
    -- Status
    status VARCHAR(50) NOT NULL DEFAULT 'active',  -- active, suspended, cancelled
    
    -- Limits & Quotas
    max_concurrent_calls INTEGER NOT NULL DEFAULT 10,
    max_monthly_minutes INTEGER,  -- NULL = unlimited
    
    -- Configuration
    settings JSONB NOT NULL DEFAULT '{}'::jsonb,
    -- Example settings:
    -- {
    --   "voice": {
    --     "voice_id": "alloy",
    --     "speaking_rate": 1.0,
    --     "language": "en-US"
    --   },
    --   "behavior": {
    --     "greeting_delay_ms": 500,
    --     "silence_timeout_ms": 5000,
    --     "max_call_duration_seconds": 1800
    --   },
    --   "features": {
    --     "call_recording": true,
    --     "transcription": true,
    --     "sentiment_analysis": false
    --   },
    --   "transfer": {
    --     "default_number": "+15551234567",
    --     "business_hours_only": true
    --   }
    -- }
    
    -- Metadata
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ DEFAULT NULL,
    
    -- Constraints
    CONSTRAINT uq_tenants_agency_slug UNIQUE (agency_id, slug),
    CONSTRAINT ck_tenants_status CHECK (status IN ('active', 'suspended', 'cancelled'))
);

-- Indexes
CREATE INDEX ix_tenants_agency_id ON tenants(agency_id) WHERE deleted_at IS NULL;
CREATE INDEX ix_tenants_status ON tenants(status) WHERE deleted_at IS NULL;
CREATE INDEX ix_tenants_created_at ON tenants(created_at);
CREATE INDEX ix_tenants_business_type ON tenants(business_type) WHERE deleted_at IS NULL;

-- Trigger for updated_at
CREATE TRIGGER update_tenants_updated_at
    BEFORE UPDATE ON tenants
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comments
COMMENT ON TABLE tenants IS 'End-customer businesses that use Voice AI, belonging to an agency';
COMMENT ON COLUMN tenants.slug IS 'URL-friendly identifier, unique within agency';
COMMENT ON COLUMN tenants.timezone IS 'IANA timezone identifier for business hours calculations';
COMMENT ON COLUMN tenants.settings IS 'Tenant-specific configuration as JSON';

-- =============================================================================
-- USERS TABLE
-- =============================================================================
-- Users are humans who log into the platform.
-- A user can belong to an agency (agency staff) or a tenant (tenant staff).
-- Platform admins have neither agency_id nor tenant_id.
-- =============================================================================

CREATE TABLE users (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Relationships (one or none, not both)
    agency_id UUID REFERENCES agencies(id),  -- NULL if platform admin or tenant user
    tenant_id UUID REFERENCES tenants(id),   -- NULL if platform admin or agency user
    
    -- Authentication
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,  -- bcrypt hash
    
    -- Profile
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(50),
    avatar_url VARCHAR(500),
    
    -- Role (see user_roles table for detailed permissions)
    role VARCHAR(50) NOT NULL DEFAULT 'user',
    -- Possible roles:
    -- 'platform_admin' - Full platform access (aiConnected staff)
    -- 'agency_admin' - Full agency access
    -- 'agency_user' - Limited agency access
    -- 'tenant_admin' - Full tenant access
    -- 'tenant_user' - Limited tenant access
    
    -- Status
    status VARCHAR(50) NOT NULL DEFAULT 'active',  -- active, suspended, invited
    is_verified BOOLEAN NOT NULL DEFAULT FALSE,     -- Email verified
    
    -- Security
    last_login_at TIMESTAMPTZ,
    last_login_ip VARCHAR(45),  -- IPv6 can be 45 chars
    failed_login_attempts INTEGER NOT NULL DEFAULT 0,
    locked_until TIMESTAMPTZ,
    
    -- Password Reset
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMPTZ,
    
    -- Email Verification
    email_verification_token VARCHAR(255),
    email_verified_at TIMESTAMPTZ,
    
    -- Preferences
    preferences JSONB NOT NULL DEFAULT '{}'::jsonb,
    -- Example preferences:
    -- {
    --   "theme": "dark",
    --   "timezone": "America/Los_Angeles",
    --   "notifications": {
    --     "email": true,
    --     "sms": false
    --   },
    --   "dashboard": {
    --     "default_view": "calls"
    --   }
    -- }
    
    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ DEFAULT NULL,
    
    -- Constraints
    CONSTRAINT uq_users_email UNIQUE (email),
    CONSTRAINT ck_users_status CHECK (status IN ('active', 'suspended', 'invited')),
    CONSTRAINT ck_users_role CHECK (role IN ('platform_admin', 'agency_admin', 'agency_user', 'tenant_admin', 'tenant_user')),
    -- User must belong to agency OR tenant OR neither (platform admin), not both
    CONSTRAINT ck_users_ownership CHECK (
        (agency_id IS NULL AND tenant_id IS NULL) OR  -- platform admin
        (agency_id IS NOT NULL AND tenant_id IS NULL) OR  -- agency user
        (agency_id IS NULL AND tenant_id IS NOT NULL)  -- tenant user
    )
);

-- Indexes
CREATE INDEX ix_users_email ON users(email) WHERE deleted_at IS NULL;
CREATE INDEX ix_users_agency_id ON users(agency_id) WHERE deleted_at IS NULL;
CREATE INDEX ix_users_tenant_id ON users(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX ix_users_role ON users(role) WHERE deleted_at IS NULL;
CREATE INDEX ix_users_status ON users(status) WHERE deleted_at IS NULL;

-- Trigger for updated_at
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comments
COMMENT ON TABLE users IS 'Human users who log into the platform';
COMMENT ON COLUMN users.password_hash IS 'bcrypt hashed password, never store plaintext';
COMMENT ON COLUMN users.role IS 'User role determining permission level';
COMMENT ON COLUMN users.failed_login_attempts IS 'Counter for rate limiting, reset on successful login';

-- =============================================================================
-- PERMISSIONS TABLE
-- =============================================================================
-- Defines all possible permissions in the system.
-- These are referenced by roles.
-- =============================================================================

CREATE TABLE permissions (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Permission Definition
    code VARCHAR(100) NOT NULL,  -- 'calls.view', 'tenants.create', etc.
    name VARCHAR(255) NOT NULL,   -- Human-readable name
    description TEXT,
    
    -- Grouping
    category VARCHAR(100) NOT NULL,  -- 'calls', 'tenants', 'analytics', etc.
    
    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT uq_permissions_code UNIQUE (code)
);

-- Seed default permissions
INSERT INTO permissions (code, name, description, category) VALUES
    -- Call permissions
    ('calls.view', 'View Calls', 'View call list and details', 'calls'),
    ('calls.listen', 'Listen to Recordings', 'Listen to call recordings', 'calls'),
    ('calls.export', 'Export Calls', 'Export call data to CSV/Excel', 'calls'),
    ('calls.delete', 'Delete Calls', 'Delete call records', 'calls'),
    
    -- Tenant permissions
    ('tenants.view', 'View Tenants', 'View tenant list and details', 'tenants'),
    ('tenants.create', 'Create Tenants', 'Create new tenants', 'tenants'),
    ('tenants.edit', 'Edit Tenants', 'Modify tenant settings', 'tenants'),
    ('tenants.delete', 'Delete Tenants', 'Delete tenants', 'tenants'),
    
    -- Phone number permissions
    ('phone_numbers.view', 'View Phone Numbers', 'View phone number list', 'phone_numbers'),
    ('phone_numbers.provision', 'Provision Numbers', 'Add new phone numbers', 'phone_numbers'),
    ('phone_numbers.configure', 'Configure Numbers', 'Change number settings', 'phone_numbers'),
    ('phone_numbers.release', 'Release Numbers', 'Remove phone numbers', 'phone_numbers'),
    
    -- Knowledge base permissions
    ('knowledge.view', 'View Knowledge Base', 'View knowledge documents', 'knowledge'),
    ('knowledge.create', 'Add Knowledge', 'Add documents to knowledge base', 'knowledge'),
    ('knowledge.edit', 'Edit Knowledge', 'Modify knowledge documents', 'knowledge'),
    ('knowledge.delete', 'Delete Knowledge', 'Remove knowledge documents', 'knowledge'),
    
    -- Analytics permissions
    ('analytics.view', 'View Analytics', 'View basic analytics', 'analytics'),
    ('analytics.export', 'Export Analytics', 'Export analytics data', 'analytics'),
    ('analytics.advanced', 'Advanced Analytics', 'Access advanced analytics features', 'analytics'),
    
    -- User management permissions
    ('users.view', 'View Users', 'View user list', 'users'),
    ('users.create', 'Create Users', 'Invite new users', 'users'),
    ('users.edit', 'Edit Users', 'Modify user profiles', 'users'),
    ('users.delete', 'Delete Users', 'Remove users', 'users'),
    
    -- Settings permissions
    ('settings.view', 'View Settings', 'View configuration', 'settings'),
    ('settings.edit', 'Edit Settings', 'Modify configuration', 'settings'),
    
    -- Billing permissions (agency only)
    ('billing.view', 'View Billing', 'View invoices and usage', 'billing'),
    ('billing.manage', 'Manage Billing', 'Update payment methods', 'billing')
;

-- =============================================================================
-- ROLE_PERMISSIONS TABLE
-- =============================================================================
-- Maps roles to their permissions.
-- This defines what each role can do.
-- =============================================================================

CREATE TABLE role_permissions (
    -- Composite Primary Key
    role VARCHAR(50) NOT NULL,
    permission_id UUID NOT NULL REFERENCES permissions(id),
    
    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT pk_role_permissions PRIMARY KEY (role, permission_id)
);

-- Seed role permissions

-- Platform Admin: Everything
INSERT INTO role_permissions (role, permission_id)
SELECT 'platform_admin', id FROM permissions;

-- Agency Admin: Everything except platform-level
INSERT INTO role_permissions (role, permission_id)
SELECT 'agency_admin', id FROM permissions
WHERE code NOT LIKE 'platform.%';

-- Agency User: View + limited actions
INSERT INTO role_permissions (role, permission_id)
SELECT 'agency_user', id FROM permissions
WHERE code IN (
    'calls.view', 'calls.listen',
    'tenants.view',
    'phone_numbers.view',
    'knowledge.view',
    'analytics.view'
);

-- Tenant Admin: Full tenant access
INSERT INTO role_permissions (role, permission_id)
SELECT 'tenant_admin', id FROM permissions
WHERE code IN (
    'calls.view', 'calls.listen', 'calls.export',
    'phone_numbers.view', 'phone_numbers.configure',
    'knowledge.view', 'knowledge.create', 'knowledge.edit', 'knowledge.delete',
    'analytics.view', 'analytics.export',
    'users.view', 'users.create', 'users.edit',
    'settings.view', 'settings.edit'
);

-- Tenant User: View only
INSERT INTO role_permissions (role, permission_id)
SELECT 'tenant_user', id FROM permissions
WHERE code IN (
    'calls.view', 'calls.listen',
    'knowledge.view',
    'analytics.view'
);

-- Index for lookup
CREATE INDEX ix_role_permissions_role ON role_permissions(role);