F016 - Manuscript Version Comparison

-- Manuscript versions
CREATE TABLE analyzer.manuscript_versions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  manuscript_id UUID REFERENCES analyzer.manuscripts(id),
  version_number INTEGER NOT NULL,
  version_name VARCHAR(100),
  upload_id UUID REFERENCES analyzer.manuscripts(id), -- Links to the actual uploaded file
  word_count INTEGER,
  analysis_session_id UUID REFERENCES analyzer.analysis_sessions(id),
  parent_version_id UUID REFERENCES analyzer.manuscript_versions(id),
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Version comparisons
CREATE TABLE analyzer.version_comparisons (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  manuscript_id UUID REFERENCES analyzer.manuscripts(id),
  from_version_id UUID REFERENCES analyzer.manuscript_versions(id),
  to_version_id UUID REFERENCES analyzer.manuscript_versions(id),
  overall_improvement DECIMAL(5,2), -- Percentage change
  category_improvements JSONB,
  key_changes JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Improvement tracking
CREATE TABLE analyzer.improvement_metrics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  manuscript_id UUID REFERENCES analyzer.manuscripts(id),
  metric_type VARCHAR(50), -- 'overall', 'structure', 'character', etc.
  current_score DECIMAL(5,2),
  previous_score DECIMAL(5,2),
  change_percentage DECIMAL(5,2),
  trend VARCHAR(20), -- 'improving', 'declining', 'stable'
  recorded_at TIMESTAMP DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_manuscript_versions_manuscript_id ON analyzer.manuscript_versions(manuscript_id);
CREATE INDEX idx_version_comparisons_manuscript_id ON analyzer.version_comparisons(manuscript_id);
CREATE INDEX idx_improvement_metrics_manuscript_id ON analyzer.improvement_metrics(manuscript_id);

// packages/manuscript-analysis/src/services/version-tracker.ts
import { createClient } from '@mystoryflow/database/server'
 
interface VersionComparison {
  overallImprovement: number
  categoryImprovements: Record<string, number>
  keyChanges: {
    biggestImprovement: string
    biggestDecline: string
    newStrengths: string[]
    resolvedWeaknesses: string[]
    persistentIssues: string[]
  }
}
 
export class VersionTracker {
  private supabase = getSupabaseBrowserClient()
 
  async createVersion(
    manuscriptId: string,
    uploadId: string,
    versionName?: string
  ): Promise<string> {
    // Get current version count
    const { count } = await this.supabase
      .from('analyzer.manuscript_versions')
      .select('*', { count: 'exact' })
      .eq('manuscript_id', manuscriptId)
 
    const versionNumber = (count || 0) + 1
 
    // Get word count from the uploaded manuscript
    const { data: upload } = await this.supabase
      .from('analyzer.manuscripts')
      .select('word_count')
      .eq('id', uploadId)
      .single()
 
    // Create version record
    const { data: version } = await this.supabase
      .from('analyzer.manuscript_versions')
      .insert({
        manuscript_id: manuscriptId,
        version_number: versionNumber,
        version_name: versionName || `Version ${versionNumber}`,
        upload_id: uploadId,
        word_count: upload?.word_count
      })
      .select()
      .single()
 
    return version.id
  }
 
  async compareVersions(
    fromVersionId: string,
    toVersionId: string
  ): Promise<VersionComparison> {
    // Get analysis results for both versions
    const [fromAnalysis, toAnalysis] = await Promise.all([
      this.getVersionAnalysis(fromVersionId),
      this.getVersionAnalysis(toVersionId)
    ])
 
    if (!fromAnalysis || !toAnalysis) {
      throw new Error('Analysis not found for one or both versions')
    }
 
    // Calculate improvements
    const comparison = this.calculateImprovements(fromAnalysis, toAnalysis)
 
    // Save comparison
    await this.saveComparison(
      fromAnalysis.manuscript_id,
      fromVersionId,
      toVersionId,
      comparison
    )
 
    // Update improvement metrics
    await this.updateImprovementMetrics(
      fromAnalysis.manuscript_id,
      fromAnalysis,
      toAnalysis
    )
 
    return comparison
  }
 
  private calculateImprovements(
    fromAnalysis: any,
    toAnalysis: any
  ): VersionComparison {
    // Overall improvement
    const overallImprovement = 
      ((toAnalysis.overall_score - fromAnalysis.overall_score) / 
       fromAnalysis.overall_score) * 100
 
    // Category improvements
    const categoryImprovements: Record<string, number> = {}
    for (const category in fromAnalysis.category_scores) {
      const fromScore = fromAnalysis.category_scores[category]
      const toScore = toAnalysis.category_scores[category]
      categoryImprovements[category] = 
        ((toScore - fromScore) / fromScore) * 100
    }
 
    // Find biggest changes
    const improvements = Object.entries(categoryImprovements)
      .sort(([,a], [,b]) => b - a)
    
    const biggestImprovement = improvements[0]?.[0] || 'none'
    const biggestDecline = improvements[improvements.length - 1]?.[0] || 'none'
 
    // Compare strengths and weaknesses
    const newStrengths = toAnalysis.strengths.filter(
      (s: string) => !fromAnalysis.strengths.includes(s)
    )
    
    const resolvedWeaknesses = fromAnalysis.weaknesses.filter(
      (w: string) => !toAnalysis.weaknesses.includes(w)
    )
    
    const persistentIssues = fromAnalysis.weaknesses.filter(
      (w: string) => toAnalysis.weaknesses.includes(w)
    )
 
    return {
      overallImprovement,
      categoryImprovements,
      keyChanges: {
        biggestImprovement,
        biggestDecline,
        newStrengths,
        resolvedWeaknesses,
        persistentIssues
      }
    }
  }
 
  private async getVersionAnalysis(versionId: string): Promise<any> {
    const { data: version } = await this.supabase
      .from('analyzer.manuscript_versions')
      .select('analysis_session_id, manuscript_id')
      .eq('id', versionId)
      .single()
 
    if (!version?.analysis_session_id) return null
 
    const { data: analysis } = await this.supabase
      .from('analyzer.manuscript_analyses')
      .select('*')
      .eq('session_id', version.analysis_session_id)
      .single()
 
    return analysis
  }
 
  private async saveComparison(
    manuscriptId: string,
    fromVersionId: string,
    toVersionId: string,
    comparison: VersionComparison
  ): Promise<void> {
    await this.supabase
      .from('analyzer.version_comparisons')
      .insert({
        manuscript_id: manuscriptId,
        from_version_id: fromVersionId,
        to_version_id: toVersionId,
        overall_improvement: comparison.overallImprovement,
        category_improvements: comparison.categoryImprovements,
        key_changes: comparison.keyChanges
      })
  }
 
  private async updateImprovementMetrics(
    manuscriptId: string,
    fromAnalysis: any,
    toAnalysis: any
  ): Promise<void> {
    const metrics = []
 
    // Overall metric
    metrics.push({
      manuscript_id: manuscriptId,
      metric_type: 'overall',
      current_score: toAnalysis.overall_score,
      previous_score: fromAnalysis.overall_score,
      change_percentage: 
        ((toAnalysis.overall_score - fromAnalysis.overall_score) / 
         fromAnalysis.overall_score) * 100,
      trend: this.getTrend(fromAnalysis.overall_score, toAnalysis.overall_score)
    })
 
    // Category metrics
    for (const category in toAnalysis.category_scores) {
      const currentScore = toAnalysis.category_scores[category]
      const previousScore = fromAnalysis.category_scores[category]
      
      metrics.push({
        manuscript_id: manuscriptId,
        metric_type: category,
        current_score: currentScore,
        previous_score: previousScore,
        change_percentage: ((currentScore - previousScore) / previousScore) * 100,
        trend: this.getTrend(previousScore, currentScore)
      })
    }
 
    await this.supabase
      .from('analyzer.improvement_metrics')
      .insert(metrics)
  }
 
  private getTrend(previousScore: number, currentScore: number): string {
    const change = currentScore - previousScore
    if (change > 5) return 'improving'
    if (change < -5) return 'declining'
    return 'stable'
  }
}

// apps/analyzer-app/src/components/manuscript/VersionComparison.tsx
'use client'
 
import { useState } from 'react'
import { 
  Card, 
  Select, 
  Badge, 
  Progress,
  DataGrid,
  LineChart
} from '@mystoryflow/ui'
import { ArrowUp, ArrowDown, Minus } from 'lucide-react'
 
interface VersionComparisonProps {
  manuscriptId: string
  versions: any[]
  comparisons: any[]
}
 
export function VersionComparison({ 
  manuscriptId, 
  versions, 
  comparisons 
}: VersionComparisonProps) {
  const [fromVersion, setFromVersion] = useState(versions[0]?.id)
  const [toVersion, setToVersion] = useState(versions[1]?.id)
  
  const comparison = comparisons.find(
    c => c.from_version_id === fromVersion && c.to_version_id === toVersion
  )
 
  const getImprovementIcon = (change: number) => {
    if (change > 5) return <ArrowUp className="text-green-500" />
    if (change < -5) return <ArrowDown className="text-red-500" />
    return <Minus className="text-gray-400" />
  }
 
  const getImprovementColor = (change: number) => {
    if (change > 5) return 'text-green-600'
    if (change < -5) return 'text-red-600'
    return 'text-gray-600'
  }
 
  return (
    <div className="space-y-6">
      {/* Version Selector */}
      <Card className="p-6">
        <h3 className="text-lg font-semibold mb-4">Compare Versions</h3>
        
        <div className="grid md:grid-cols-2 gap-4">
          <div>
            <label className="text-sm font-medium mb-2 block">
              From Version
            </label>
            <Select
              value={fromVersion}
              onValueChange={setFromVersion}
            >
              {versions.map(v => (
                <option key={v.id} value={v.id}>
                  {v.version_name} ({new Date(v.created_at).toLocaleDateString()})
                </option>
              ))}
            </Select>
          </div>
          
          <div>
            <label className="text-sm font-medium mb-2 block">
              To Version
            </label>
            <Select
              value={toVersion}
              onValueChange={setToVersion}
            >
              {versions.map(v => (
                <option key={v.id} value={v.id}>
                  {v.version_name} ({new Date(v.created_at).toLocaleDateString()})
                </option>
              ))}
            </Select>
          </div>
        </div>
      </Card>
 
      {comparison && (
        <>
          {/* Overall Improvement */}
          <Card className="p-6">
            <div className="flex items-center justify-between mb-4">
              <h3 className="text-lg font-semibold">Overall Improvement</h3>
              <div className={`flex items-center gap-2 text-2xl font-bold ${
                getImprovementColor(comparison.overall_improvement)
              }`}>
                {getImprovementIcon(comparison.overall_improvement)}
                {comparison.overall_improvement > 0 ? '+' : ''}
                {comparison.overall_improvement.toFixed(1)}%
              </div>
            </div>
            
            <Progress 
              value={Math.abs(comparison.overall_improvement)} 
              max={100}
              className="h-3"
            />
          </Card>
 
          {/* Category Improvements */}
          <Card className="p-6">
            <h3 className="text-lg font-semibold mb-4">Category Changes</h3>
            
            <div className="space-y-3">
              {Object.entries(comparison.category_improvements).map(
                ([category, change]) => (
                  <div key={category} className="flex items-center justify-between">
                    <span className="text-sm font-medium">
                      {category.replace(/_/g, ' ').toUpperCase()}
                    </span>
                    <div className={`flex items-center gap-2 font-medium ${
                      getImprovementColor(change as number)
                    }`}>
                      {getImprovementIcon(change as number)}
                      {(change as number) > 0 ? '+' : ''}
                      {(change as number).toFixed(1)}%
                    </div>
                  </div>
                )
              )}
            </div>
          </Card>
 
          {/* Key Changes */}
          <Card className="p-6">
            <h3 className="text-lg font-semibold mb-4">Key Changes</h3>
            
            <div className="grid md:grid-cols-2 gap-6">
              <div>
                <h4 className="font-medium text-green-600 mb-2">
                  Improvements
                </h4>
                <ul className="space-y-2">
                  <li className="text-sm">
                    <strong>Biggest Improvement:</strong>{' '}
                    {comparison.key_changes.biggestImprovement.replace(/_/g, ' ')}
                  </li>
                  {comparison.key_changes.newStrengths.map((s: string, i: number) => (
                    <li key={i} className="text-sm">✓ {s}</li>
                  ))}
                </ul>
              </div>
              
              <div>
                <h4 className="font-medium text-red-600 mb-2">
                  Areas Needing Work
                </h4>
                <ul className="space-y-2">
                  {comparison.key_changes.persistentIssues.length > 0 ? (
                    comparison.key_changes.persistentIssues.map((issue: string, i: number) => (
                      <li key={i} className="text-sm">• {issue}</li>
                    ))
                  ) : (
                    <li className="text-sm text-gray-500">
                      No persistent issues
                    </li>
                  )}
                </ul>
              </div>
            </div>
          </Card>
        </>
      )}
    </div>
  )
}

// apps/analyzer-app/src/app/api/manuscripts/[id]/versions/route.ts
import { NextRequest, NextResponse } from 'next/server'
import { VersionTracker } from '@mystoryflow/manuscript-analysis'
import { withAuth } from '@/lib/auth'
 
// Create new version
export async function POST(
  req: NextRequest,
  { params }: { params: { id: string } }
) {
  const session = await withAuth(req)
  if (!session) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
  }
 
  try {
    const { uploadId, versionName } = await req.json()
    
    const tracker = new VersionTracker()
    const versionId = await tracker.createVersion(
      params.id,
      uploadId,
      versionName
    )
    
    return NextResponse.json({ 
      success: true,
      versionId 
    })
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to create version' },
      { status: 500 }
    )
  }
}
 
// Get versions
export async function GET(
  req: NextRequest,
  { params }: { params: { id: string } }
) {
  const session = await withAuth(req)
  if (!session) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
  }
 
  const supabase = getSupabaseBrowserClient()
  
  const { data: versions } = await supabase
    .from('analyzer.manuscript_versions')
    .select('*')
    .eq('manuscript_id', params.id)
    .order('version_number', { ascending: false })
 
  return NextResponse.json({ versions })
}