F022 - Word Count & Analysis Limits

-- Usage limits by resource type
CREATE TABLE analyzer.usage_limits (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  plan_id UUID REFERENCES analyzer.subscription_plans(id),
  resource_type VARCHAR(50) NOT NULL, -- 'analysis', 'word_count', 'export', 'consultation'
  limit_value INTEGER NOT NULL, -- -1 for unlimited
  period VARCHAR(20) NOT NULL, -- 'monthly', 'daily', 'per_use'
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Usage aggregates for performance
CREATE TABLE analyzer.usage_aggregates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  resource_type VARCHAR(50) NOT NULL,
  total_usage INTEGER NOT NULL DEFAULT 0,
  last_updated TIMESTAMP DEFAULT NOW(),
  UNIQUE(user_id, period_start, resource_type)
);
 
-- Usage alerts
CREATE TABLE analyzer.usage_alerts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  alert_type VARCHAR(50) NOT NULL, -- 'approaching_limit', 'limit_reached', 'overage'
  resource_type VARCHAR(50) NOT NULL,
  threshold_percentage INTEGER,
  sent_at TIMESTAMP DEFAULT NOW()
);
 
-- Insert default limits
INSERT INTO analyzer.usage_limits (plan_id, resource_type, limit_value, period) 
SELECT 
  sp.id,
  'analysis',
  COALESCE((sp.limits->>'analyses_per_month')::int, 0),
  'monthly'
FROM subscription_plans sp;
 
INSERT INTO analyzer.usage_limits (plan_id, resource_type, limit_value, period)
SELECT 
  sp.id,
  'word_count',
  COALESCE((sp.limits->>'max_words_per_analysis')::int, 5000),
  'per_use'
FROM subscription_plans sp;
 
-- Function to update usage aggregates
CREATE OR REPLACE FUNCTION update_usage_aggregate()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO analyzer.usage_aggregates (
    user_id,
    period_start,
    period_end,
    resource_type,
    total_usage
  ) VALUES (
    NEW.user_id,
    date_trunc('month', NEW.recorded_at),
    date_trunc('month', NEW.recorded_at) + interval '1 month' - interval '1 day',
    NEW.resource_type,
    NEW.quantity
  )
  ON CONFLICT (user_id, period_start, resource_type)
  DO UPDATE SET 
    total_usage = usage_aggregates.total_usage + NEW.quantity,
    last_updated = NOW();
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trigger_update_usage_aggregate
AFTER INSERT ON usage_records
FOR EACH ROW
EXECUTE FUNCTION update_usage_aggregate();

// packages/usage/src/services/usage-tracker.ts
import { createClient } from '@mystoryflow/database/server'
 
interface UsageCheck {
  allowed: boolean
  currentUsage: number
  limit: number
  remaining: number
  percentageUsed: number
  willExceedLimit: boolean
}
 
export class UsageTracker {
  private supabase = getSupabaseBrowserClient()
 
  async checkUsage(
    userId: string,
    resourceType: string,
    requestedAmount: number = 1
  ): Promise<UsageCheck> {
    // Get user's plan and limits
    const { data: subscription } = await this.supabase
      .from('analyzer.user_subscriptions')
      .select(`
        *,
        subscription_plans (
          *,
          usage_limits (*)
        )
      `)
      .eq('user_id', userId)
      .eq('status', 'active')
      .single()
 
    // Default to free plan if no active subscription
    const plan = subscription?.subscription_plans || await this.getFreePlan()
    const limit = this.getResourceLimit(plan, resourceType)
 
    // Get current usage
    const currentUsage = await this.getCurrentUsage(userId, resourceType)
    
    // Calculate metrics
    const remaining = Math.max(0, limit - currentUsage)
    const percentageUsed = limit > 0 ? (currentUsage / limit) * 100 : 0
    const willExceedLimit = currentUsage + requestedAmount > limit
    
    // Check alerts
    await this.checkAndSendAlerts(userId, resourceType, percentageUsed)
 
    return {
      allowed: !willExceedLimit || limit === -1,
      currentUsage,
      limit,
      remaining,
      percentageUsed: Math.round(percentageUsed),
      willExceedLimit
    }
  }
 
  async recordUsage(
    userId: string,
    resourceType: string,
    amount: number,
    metadata?: any
  ): Promise<void> {
    // Check if usage is allowed
    const check = await this.checkUsage(userId, resourceType, amount)
    
    if (!check.allowed) {
      throw new Error(`Usage limit exceeded for ${resourceType}`)
    }
 
    // Record the usage
    await this.supabase
      .from('analyzer.usage_records')
      .insert({
        user_id: userId,
        resource_type: resourceType,
        quantity: amount,
        metadata
      })
  }
 
  async getUsageSummary(userId: string): Promise<any> {
    // Get current period
    const periodStart = new Date()
    periodStart.setDate(1)
    periodStart.setHours(0, 0, 0, 0)
 
    // Get aggregated usage
    const { data: usage } = await this.supabase
      .from('analyzer.usage_aggregates')
      .select('*')
      .eq('user_id', userId)
      .gte('period_start', periodStart.toISOString())
 
    // Get user's limits
    const { data: subscription } = await this.supabase
      .from('analyzer.user_subscriptions')
      .select(`
        subscription_plans (
          *,
          usage_limits (*)
        )
      `)
      .eq('user_id', userId)
      .eq('status', 'active')
      .single()
 
    const plan = subscription?.subscription_plans || await this.getFreePlan()
 
    // Build summary
    const summary: any = {}
    
    for (const limit of plan.usage_limits || []) {
      const usageRecord = usage?.find(u => u.resource_type === limit.resource_type)
      const used = usageRecord?.total_usage || 0
      
      summary[limit.resource_type] = {
        used,
        limit: limit.limit_value,
        remaining: Math.max(0, limit.limit_value - used),
        percentage: limit.limit_value > 0 
          ? Math.round((used / limit.limit_value) * 100)
          : 0,
        unlimited: limit.limit_value === -1
      }
    }
 
    return summary
  }
 
  private async getCurrentUsage(
    userId: string,
    resourceType: string
  ): Promise<number> {
    const periodStart = new Date()
    periodStart.setDate(1)
    periodStart.setHours(0, 0, 0, 0)
 
    const { data } = await this.supabase
      .from('analyzer.usage_aggregates')
      .select('total_usage')
      .eq('user_id', userId)
      .eq('resource_type', resourceType)
      .gte('period_start', periodStart.toISOString())
      .single()
 
    return data?.total_usage || 0
  }
 
  private getResourceLimit(plan: any, resourceType: string): number {
    const limit = plan.usage_limits?.find(
      (l: any) => l.resource_type === resourceType
    )
    return limit?.limit_value || 0
  }
 
  private async checkAndSendAlerts(
    userId: string,
    resourceType: string,
    percentageUsed: number
  ): Promise<void> {
    // Check if we need to send alerts
    const thresholds = [50, 80, 90, 100]
    
    for (const threshold of thresholds) {
      if (percentageUsed >= threshold) {
        const alertType = threshold === 100 ? 'limit_reached' : 'approaching_limit'
        
        // Check if alert already sent
        const { data: existing } = await this.supabase
          .from('analyzer.usage_alerts')
          .select('id')
          .eq('user_id', userId)
          .eq('resource_type', resourceType)
          .eq('threshold_percentage', threshold)
          .gte('sent_at', new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString())
          .single()
 
        if (!existing) {
          // Send alert
          await this.supabase
            .from('analyzer.usage_alerts')
            .insert({
              user_id: userId,
              alert_type: alertType,
              resource_type: resourceType,
              threshold_percentage: threshold
            })
 
          // Trigger notification
          await this.sendUsageNotification(userId, alertType, resourceType, threshold)
        }
      }
    }
  }
 
  private async sendUsageNotification(
    userId: string,
    alertType: string,
    resourceType: string,
    threshold: number
  ): Promise<void> {
    // Import notification service
    const { NotificationService } = await import('@/packages/notifications')
    const notificationService = new NotificationService()
 
    const messages = {
      approaching_limit: `You've used ${threshold}% of your monthly ${resourceType} limit`,
      limit_reached: `You've reached your monthly ${resourceType} limit`
    }
 
    await notificationService.notify({
      userId,
      type: 'subscription_update',
      title: alertType === 'limit_reached' ? 'Usage Limit Reached' : 'Usage Alert',
      message: messages[alertType] || 'Usage alert',
      data: { resourceType, threshold }
    })
  }
 
  private async getFreePlan() {
    const { data } = await this.supabase
      .from('analyzer.subscription_plans')
      .select(`
        *,
        usage_limits (*)
      `)
      .eq('code', 'free')
      .single()
    
    return data
  }
}

// apps/analyzer-app/src/middleware/usage-limiter.ts
import { NextRequest, NextResponse } from 'next/server'
import { UsageTracker } from '@/packages/usage'
import { createClient } from '@mystoryflow/database/server'
 
export async function checkUsageLimit(
  req: NextRequest,
  resourceType: string,
  amount: number = 1
) {
  const supabase = getSupabaseBrowserClient()
  
  // Get user from session
  const { data: { user } } = await supabase.auth.getUser()
  if (!user) {
    return NextResponse.json(
      { error: 'Unauthorized' },
      { status: 401 }
    )
  }
 
  const tracker = new UsageTracker()
  const check = await tracker.checkUsage(user.id, resourceType, amount)
 
  if (!check.allowed) {
    return NextResponse.json(
      {
        error: 'Usage limit exceeded',
        limit: check.limit,
        used: check.currentUsage,
        upgrade_url: '/pricing'
      },
      { status: 429 }
    )
  }
 
  return null // Continue with request
}
 
// Example usage in API route
export async function POST(req: NextRequest) {
  // Check word count limit
  const { content } = await req.json()
  const wordCount = content.split(/\s+/).length
  
  const limitError = await checkUsageLimit(req, 'word_count', wordCount)
  if (limitError) return limitError
 
  // Check analysis limit
  const analysisLimitError = await checkUsageLimit(req, 'analysis', 1)
  if (analysisLimitError) return analysisLimitError
 
  // Continue with analysis...
}

// apps/analyzer-app/src/components/usage/UsageDashboard.tsx
'use client'
 
import { Card, Progress, Alert, Button } from '@mystoryflow/ui'
import { BarChart, LineChart } from '@mystoryflow/ui/charts'
import { AlertCircle, TrendingUp, FileText, MessageSquare } from 'lucide-react'
import { useUsage } from '@/hooks/useUsage'
 
export function UsageDashboard({ userId }: { userId: string }) {
  const { summary, history, loading } = useUsage(userId)
 
  if (loading) return <div>Loading usage data...</div>
 
  return (
    <div className="space-y-6">
      {/* Usage Summary Cards */}
      <div className="grid gap-4 md:grid-cols-3">
        <UsageCard
          title="Analyses"
          icon={<FileText />}
          usage={summary.analysis}
          color="blue"
        />
        <UsageCard
          title="Words Analyzed"
          icon={<FileText />}
          usage={summary.word_count}
          color="green"
          format="number"
        />
        <UsageCard
          title="Consultations"
          icon={<MessageSquare />}
          usage={summary.consultation}
          color="purple"
        />
      </div>
 
      {/* Usage Alerts */}
      {Object.entries(summary).map(([resource, data]: [string, any]) => {
        if (data.percentage >= 80 && !data.unlimited) {
          return (
            <Alert key={resource} variant="warning">
              <AlertCircle className="h-4 w-4" />
              <div className="flex items-center justify-between w-full">
                <span>
                  You've used {data.percentage}% of your {resource} limit this month
                </span>
                <Button size="sm" variant="outline" asChild>
                  <a href="/pricing">Upgrade Plan</a>
                </Button>
              </div>
            </Alert>
          )
        }
        return null
      })}
 
      {/* Usage History Chart */}
      <Card className="p-6">
        <h3 className="text-lg font-semibold mb-4">Usage History</h3>
        <div className="h-64">
          <LineChart
            data={history}
            lines={[
              { key: 'analyses', color: '#3b82f6', name: 'Analyses' },
              { key: 'consultations', color: '#8b5cf6', name: 'Consultations' }
            ]}
            xKey="date"
          />
        </div>
      </Card>
 
      {/* Detailed Usage Table */}
      <Card className="p-6">
        <h3 className="text-lg font-semibold mb-4">Recent Usage</h3>
        <UsageTable userId={userId} />
      </Card>
    </div>
  )
}
 
function UsageCard({ 
  title, 
  icon, 
  usage, 
  color,
  format = 'ratio'
}: { 
  title: string
  icon: React.ReactNode
  usage: any
  color: string
  format?: 'ratio' | 'number'
}) {
  const percentage = usage.unlimited ? 0 : usage.percentage
 
  return (
    <Card className="p-6">
      <div className="flex items-center justify-between mb-4">
        <div className={`p-2 bg-${color}-100 dark:bg-${color}-900 rounded-lg`}>
          {icon}
        </div>
        {percentage >= 80 && !usage.unlimited && (
          <Badge variant="warning">
            {percentage}% used
          </Badge>
        )}
      </div>
 
      <h3 className="font-medium mb-2">{title}</h3>
      
      {usage.unlimited ? (
        <div>
          <p className="text-2xl font-bold">Unlimited</p>
          <p className="text-sm text-muted-foreground">
            {usage.used.toLocaleString()} used this month
          </p>
        </div>
      ) : format === 'ratio' ? (
        <div>
          <p className="text-2xl font-bold">
            {usage.used} / {usage.limit}
          </p>
          <Progress value={percentage} className="mt-2" />
        </div>
      ) : (
        <div>
          <p className="text-2xl font-bold">
            {usage.used.toLocaleString()}
          </p>
          <p className="text-sm text-muted-foreground">
            of {usage.limit.toLocaleString()} limit
          </p>
          <Progress value={percentage} className="mt-2" />
        </div>
      )}
    </Card>
  )
}

// apps/analyzer-app/src/hooks/useUsage.ts
import { useEffect, useState } from 'react'
import { createClient } from '@mystoryflow/database/client'
 
export function useUsage(userId: string) {
  const [summary, setSummary] = useState<any>({})
  const [history, setHistory] = useState<any[]>([])
  const [loading, setLoading] = useState(true)
  const supabase = getSupabaseBrowserClient()
 
  useEffect(() => {
    if (userId) {
      loadUsageData()
    }
  }, [userId])
 
  const loadUsageData = async () => {
    try {
      // Get usage summary
      const summaryResponse = await fetch('/api/usage/summary')
      const summaryData = await summaryResponse.json()
      setSummary(summaryData)
 
      // Get usage history for charts
      const { data: records } = await supabase
        .from('analyzer.usage_records')
        .select('*')
        .eq('user_id', userId)
        .gte('recorded_at', new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString())
        .order('recorded_at')
 
      // Group by day
      const grouped = records?.reduce((acc, record) => {
        const date = new Date(record.recorded_at).toLocaleDateString()
        if (!acc[date]) {
          acc[date] = { date, analyses: 0, consultations: 0 }
        }
        if (record.resource_type === 'analysis') {
          acc[date].analyses += record.quantity
        } else if (record.resource_type === 'consultation') {
          acc[date].consultations += record.quantity
        }
        return acc
      }, {})
 
      setHistory(Object.values(grouped || {}))
    } catch (error) {
      console.error('Error loading usage:', error)
    } finally {
      setLoading(false)
    }
  }
 
  const checkLimit = async (resourceType: string, amount: number = 1): Promise<boolean> => {
    try {
      const response = await fetch('/api/usage/check', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ resourceType, amount })
      })
      
      const result = await response.json()
      return result.allowed
    } catch (error) {
      console.error('Error checking limit:', error)
      return false
    }
  }
 
  return {
    summary,
    history,
    loading,
    checkLimit,
    refresh: loadUsageData
  }
}

// apps/analyzer-app/src/app/admin/usage/page.tsx
import { Card } from '@mystoryflow/ui'
import { createClient } from '@mystoryflow/database/server'
import { BarChart, LineChart } from '@mystoryflow/ui/charts'
 
export default async function AdminUsagePage() {
  const supabase = getSupabaseBrowserClient()
  
  // Get overall usage statistics
  const { data: stats } = await supabase.rpc('get_platform_usage_stats')
  
  // Get usage by plan
  const { data: planUsage } = await supabase.rpc('get_usage_by_plan')
  
  // Get top users
  const { data: topUsers } = await supabase
    .from('analyzer.usage_aggregates')
    .select(`
      user_id,
      users (email),
      resource_type,
      total_usage
    `)
    .gte('period_start', new Date(new Date().getFullYear(), new Date().getMonth(), 1).toISOString())
    .order('total_usage', { ascending: false })
    .limit(10)
 
  return (
    <div className="space-y-6">
      <h1 className="text-2xl font-bold">Platform Usage Analytics</h1>
 
      {/* Overall Stats */}
      <div className="grid gap-4 md:grid-cols-4">
        <Card className="p-6">
          <h3 className="text-sm font-medium text-muted-foreground">
            Total Analyses (MTD)
          </h3>
          <p className="text-2xl font-bold">{stats?.total_analyses || 0}</p>
        </Card>
        <Card className="p-6">
          <h3 className="text-sm font-medium text-muted-foreground">
            Words Analyzed (MTD)
          </h3>
          <p className="text-2xl font-bold">
            {(stats?.total_words || 0).toLocaleString()}
          </p>
        </Card>
        <Card className="p-6">
          <h3 className="text-sm font-medium text-muted-foreground">
            Active Users
          </h3>
          <p className="text-2xl font-bold">{stats?.active_users || 0}</p>
        </Card>
        <Card className="p-6">
          <h3 className="text-sm font-medium text-muted-foreground">
            Limit Violations
          </h3>
          <p className="text-2xl font-bold">{stats?.limit_violations || 0}</p>
        </Card>
      </div>
 
      {/* Usage by Plan */}
      <Card className="p-6">
        <h2 className="text-lg font-semibold mb-4">Usage by Plan</h2>
        <div className="h-64">
          <BarChart
            data={planUsage}
            xKey="plan_name"
            bars={[
              { key: 'analyses', color: '#3b82f6', name: 'Analyses' },
              { key: 'consultations', color: '#8b5cf6', name: 'Consultations' }
            ]}
          />
        </div>
      </Card>
 
      {/* Top Users */}
      <Card className="p-6">
        <h2 className="text-lg font-semibold mb-4">Top Users This Month</h2>
        <div className="space-y-2">
          {topUsers?.map((user, i) => (
            <div key={i} className="flex items-center justify-between p-3 bg-muted/50 rounded-lg">
              <span className="font-medium">{user.users.email}</span>
              <div className="text-sm text-muted-foreground">
                {user.resource_type}: {user.total_usage}
              </div>
            </div>
          ))}
        </div>
      </Card>
    </div>
  )
}

// packages/admin/src/lib/analyzer-admin-service.ts
import { adminService } from '@mystoryflow/admin'
 
export const analyzerAdminService = {
  ...adminService,
  
  // Analyzer-specific metrics
  async getAnalyzerMetrics(dateRange?: DateRange) {
    const supabase = createClient()
    
    const { data: metrics } = await supabase
      .rpc('get_analyzer_platform_metrics', { 
        start_date: dateRange?.from,
        end_date: dateRange?.to 
      })
    
    return {
      overview: metrics.overview,
      aiUsage: metrics.ai_usage,
      costAnalysis: metrics.cost_analysis,
      userBehavior: metrics.user_behavior,
      performanceMetrics: metrics.performance
    }
  },
  
  // Real-time usage monitoring
  async monitorActiveAnalyses() {
    const supabase = createClient()
    
    return supabase
      .channel('analyzer-usage')
      .on('postgres_changes', {
        event: '*',
        schema: 'analyzer',
        table: 'usage_records'
      }, (payload) => {
        // Update admin dashboard in real-time
        this.broadcastUsageUpdate(payload)
      })
      .subscribe()
  }
}

// apps/web/src/app/(admin)/admin/analyzer/components/UsageMonitor.tsx
import { Card, Badge, Progress } from '@mystoryflow/admin'
import { useRealTimeUsage } from '@/hooks/useRealTimeUsage'
 
export function AnalyzerUsageMonitor() {
  const { activeAnalyses, resourceUsage, alerts } = useRealTimeUsage()
  
  return (
    <div className="grid gap-4">
      {/* Active Analyses */}
      <Card>
        <CardHeader>
          <CardTitle>Active Analyses</CardTitle>
          <Badge>{activeAnalyses.length} running</Badge>
        </CardHeader>
        <CardContent>
          {activeAnalyses.map(analysis => (
            <div key={analysis.id} className="flex items-center gap-4 py-2">
              <Progress value={analysis.progress} className="flex-1" />
              <span className="text-sm">{analysis.user}</span>
              <Badge variant={analysis.status}>{analysis.status}</Badge>
            </div>
          ))}
        </CardContent>
      </Card>
      
      {/* Resource Usage */}
      <Card>
        <CardHeader>
          <CardTitle>Resource Usage (Last Hour)</CardTitle>
        </CardHeader>
        <CardContent>
          <div className="space-y-3">
            <ResourceMeter 
              label="API Calls" 
              current={resourceUsage.apiCalls} 
              limit={1000}
            />
            <ResourceMeter 
              label="AI Tokens" 
              current={resourceUsage.tokens} 
              limit={1000000}
            />
            <ResourceMeter 
              label="Storage (GB)" 
              current={resourceUsage.storage} 
              limit={100}
            />
          </div>
        </CardContent>
      </Card>
      
      {/* Usage Alerts */}
      {alerts.length > 0 && (
        <Alert variant="warning">
          <AlertTitle>Usage Alerts</AlertTitle>
          <AlertDescription>
            <ul className="space-y-1">
              {alerts.map((alert, i) => (
                <li key={i}>{alert.message}</li>
              ))}
            </ul>
          </AlertDescription>
        </Alert>
      )}
    </div>
  )
}

// apps/web/src/app/(admin)/admin/layout.tsx
import { AdminSidebar } from '@mystoryflow/admin'
 
const analyzerMenuItems = [
  {
    title: 'Analyzer Dashboard',
    href: '/admin/analyzer',
    icon: 'chart-bar'
  },
  {
    title: 'Usage Analytics',
    href: '/admin/analyzer/usage',
    icon: 'trending-up'
  },
  {
    title: 'AI Models',
    href: '/admin/analyzer/ai-models',
    icon: 'cpu'
  },
  {
    title: 'Manuscripts',
    href: '/admin/analyzer/manuscripts',
    icon: 'file-text'
  },
  {
    title: 'Reference Data',
    href: '/admin/analyzer/reference-data',
    icon: 'database'
  }
]

// apps/web/src/app/(admin)/admin/analyzer/reference-data/page.tsx
import { ReferenceDataManager } from '@mystoryflow/admin'
import { analyzerCollections } from '@/lib/reference-data/collections'
 
export default function ReferenceDataPage() {
  return (
    <ReferenceDataManager
      collections={[
        'manuscript-genres',
        'analysis-statuses',
        'consultation-types',
        'coach-specialties',
        'scoring-categories',
        'manuscript-types'
      ]}
      onSave={async (collection, data) => {
        // Save to reference data system
        await updateReferenceData(collection, data)
        // Invalidate caches
        await revalidatePath('/api/reference-data')
      }}
    />
  )
}