F024: Coach Marketplace & Discovery (MyStoryFlow Integration)

-- Coach certifications (MyStoryFlow integrated)
CREATE TABLE coach_certifications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  code VARCHAR(50) UNIQUE NOT NULL,
  description TEXT,
  requirements TEXT[],
  badge_color VARCHAR(20),
  badge_icon VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Coach certification status
CREATE TABLE coach_cert_status (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  coach_id UUID REFERENCES coach_profiles(id),
  certification_id UUID REFERENCES coach_certifications(id),
  status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'approved', 'expired'
  issued_at TIMESTAMP,
  expires_at TIMESTAMP,
  verification_data JSONB,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(coach_id, certification_id)
);
 
-- Coach portfolios
CREATE TABLE analyzer.coach_portfolios (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  coach_id UUID REFERENCES analyzer.coach_profiles(id),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(50), -- 'success_story', 'testimonial', 'sample_work'
  content TEXT,
  author_name VARCHAR(255),
  author_consent BOOLEAN DEFAULT true,
  results_data JSONB, -- before/after scores, achievements
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Coach reviews
CREATE TABLE analyzer.coach_reviews (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  coach_id UUID REFERENCES analyzer.coach_profiles(id),
  author_id UUID REFERENCES auth.users(id),
  session_id UUID REFERENCES analyzer.consultation_sessions(id),
  rating INTEGER CHECK (rating >= 1 AND rating <= 5),
  review_text TEXT,
  is_verified BOOLEAN DEFAULT false,
  is_visible BOOLEAN DEFAULT true,
  helpful_count INTEGER DEFAULT 0,
  response_text TEXT,
  responded_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Featured coaches
CREATE TABLE analyzer.featured_coaches (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  coach_id UUID REFERENCES analyzer.coach_profiles(id) UNIQUE,
  featured_until DATE NOT NULL,
  featured_reason TEXT,
  priority INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Seed certifications
INSERT INTO analyzer.coach_certifications (code, name, description, requirements, badge_color, badge_icon) VALUES
('genre_specialist', 'Genre Specialist', 'Expert in specific genre coaching', 
  ARRAY['50+ hours genre-specific coaching', 'Published in genre', '90%+ satisfaction rating'], 
  'purple', 'bookmark'),
('bestseller_coach', 'Bestseller Coach', 'Has coached bestselling authors', 
  ARRAY['Coached 3+ bestselling authors', 'Verified sales data', '95%+ satisfaction'], 
  'gold', 'trophy'),
('industry_pro', 'Industry Professional', 'Publishing industry experience', 
  ARRAY['5+ years industry experience', 'Verified credentials', 'Active connections'], 
  'blue', 'briefcase'),
('master_editor', 'Master Editor', 'Professional editing certification', 
  ARRAY['Certified editor credentials', '100+ manuscripts edited', 'Portfolio review'], 
  'green', 'edit');
 
-- Indexes
CREATE INDEX idx_coach_reviews_coach_id ON analyzer.coach_reviews(coach_id);
CREATE INDEX idx_coach_reviews_rating ON analyzer.coach_reviews(rating);
CREATE INDEX idx_featured_coaches_until ON analyzer.featured_coaches(featured_until);
CREATE INDEX idx_coach_profiles_rating ON analyzer.coach_profiles(rating);
CREATE INDEX idx_coach_profiles_specialties ON coach_profiles USING GIN(specialties);

// packages/marketplace/src/services/marketplace-service.ts
import { createClient } from '@mystoryflow/database/server'
 
interface SearchFilters {
  genres?: string[]
  priceRange?: { min: number; max: number }
  rating?: number
  certifications?: string[]
  availability?: boolean
  sortBy?: 'rating' | 'price' | 'experience' | 'sessions'
}
 
interface CoachScore {
  coach: any
  score: number
  matchReasons: string[]
}
 
export class MarketplaceService {
  private supabase = getSupabaseBrowserClient()
 
  async searchCoaches(
    query: string,
    filters: SearchFilters,
    limit: number = 20,
    offset: number = 0
  ): Promise<{ coaches: any[], total: number }> {
    let queryBuilder = this.supabase
      .from('analyzer.coach_profiles')
      .select(`
        *,
        coach_cert_status (
          certification_id,
          status,
          coach_certifications (
            name,
            code,
            badge_color,
            badge_icon
          )
        ),
        coach_reviews (
          rating,
          review_text,
          author_id
        ),
        featured_coaches (
          featured_until,
          priority
        )
      `, { count: 'exact' })
      .eq('is_active', true)
 
    // Apply text search
    if (query) {
      queryBuilder = queryBuilder.or(
        `full_name.ilike.%${query}%,bio.ilike.%${query}%`
      )
    }
 
    // Apply filters
    if (filters.genres?.length) {
      queryBuilder = queryBuilder.contains('specialties', filters.genres)
    }
 
    if (filters.priceRange) {
      queryBuilder = queryBuilder
        .gte('hourly_rate', filters.priceRange.min * 100)
        .lte('hourly_rate', filters.priceRange.max * 100)
    }
 
    if (filters.rating) {
      queryBuilder = queryBuilder.gte('rating', filters.rating)
    }
 
    if (filters.certifications?.length) {
      // Filter by certification codes
      queryBuilder = queryBuilder.in(
        'coach_cert_status.coach_certifications.code',
        filters.certifications
      )
    }
 
    // Apply sorting
    switch (filters.sortBy) {
      case 'rating':
        queryBuilder = queryBuilder.order('rating', { ascending: false })
        break
      case 'price':
        queryBuilder = queryBuilder.order('hourly_rate', { ascending: true })
        break
      case 'experience':
        queryBuilder = queryBuilder.order('years_experience', { ascending: false })
        break
      case 'sessions':
        queryBuilder = queryBuilder.order('total_sessions', { ascending: false })
        break
      default:
        // Default: Featured coaches first, then by rating
        queryBuilder = queryBuilder
          .order('featured_coaches.priority', { ascending: false, nullsFirst: false })
          .order('rating', { ascending: false })
    }
 
    // Apply pagination
    queryBuilder = queryBuilder.range(offset, offset + limit - 1)
 
    const { data, count, error } = await queryBuilder
 
    if (error) throw error
 
    return {
      coaches: data || [],
      total: count || 0
    }
  }
 
  async getCoachDetails(coachId: string): Promise<any> {
    const { data: coach } = await this.supabase
      .from('analyzer.coach_profiles')
      .select(`
        *,
        coach_cert_status (
          *,
          coach_certifications (*)
        ),
        coach_portfolios (*),
        coach_reviews (
          *,
          authors:users!coach_reviews_author_id_fkey (
            id,
            email
          )
        ),
        coach_availability (*)
      `)
      .eq('id', coachId)
      .single()
 
    if (!coach) throw new Error('Coach not found')
 
    // Calculate additional metrics
    const metrics = await this.calculateCoachMetrics(coachId)
    
    return {
      ...coach,
      metrics
    }
  }
 
  async getRecommendedCoaches(
    userId: string,
    manuscriptId?: string
  ): Promise<CoachScore[]> {
    // Get user preferences and manuscript details
    const userProfile = await this.getUserProfile(userId)
    const manuscript = manuscriptId ? 
      await this.getManuscriptDetails(manuscriptId) : null
 
    // Get all active coaches
    const { data: coaches } = await this.supabase
      .from('analyzer.coach_profiles')
      .select(`
        *,
        coach_cert_status (
          certification_id,
          status,
          coach_certifications (*)
        )
      `)
      .eq('is_active', true)
 
    // Score and rank coaches
    const scoredCoaches = coaches?.map(coach => {
      const score = this.calculateMatchScore(coach, userProfile, manuscript)
      return score
    }) || []
 
    // Sort by score and return top matches
    return scoredCoaches
      .sort((a, b) => b.score - a.score)
      .slice(0, 10)
  }
 
  private calculateMatchScore(
    coach: any,
    userProfile: any,
    manuscript: any
  ): CoachScore {
    let score = 0
    const matchReasons: string[] = []
 
    // Genre match (40 points)
    if (manuscript?.genre && coach.specialties.includes(manuscript.genre)) {
      score += 40
      matchReasons.push(`Specializes in ${manuscript.genre}`)
    }
 
    // Rating score (30 points max)
    score += Math.min(30, coach.rating * 6)
    if (coach.rating >= 4.5) {
      matchReasons.push('Highly rated coach')
    }
 
    // Experience score (20 points max)
    score += Math.min(20, coach.years_experience * 2)
    if (coach.years_experience >= 10) {
      matchReasons.push('10+ years experience')
    }
 
    // Certification bonus (10 points per relevant cert)
    coach.coach_cert_status?.forEach((cert: any) => {
      if (cert.status === 'approved') {
        score += 10
        if (cert.coach_certifications.code === 'genre_specialist' && 
            manuscript?.genre) {
          score += 10
          matchReasons.push(cert.coach_certifications.name)
        }
      }
    })
 
    // Price match (within user budget)
    if (userProfile.budget && coach.hourly_rate <= userProfile.budget) {
      score += 10
      matchReasons.push('Within your budget')
    }
 
    return { coach, score, matchReasons }
  }
 
  async submitReview(
    coachId: string,
    authorId: string,
    sessionId: string,
    rating: number,
    reviewText: string
  ): Promise<void> {
    // Verify session completion
    const { data: session } = await this.supabase
      .from('analyzer.consultation_sessions')
      .select('status')
      .eq('id', sessionId)
      .eq('author_id', authorId)
      .eq('coach_id', coachId)
      .single()
 
    if (!session || session.status !== 'completed') {
      throw new Error('Can only review completed sessions')
    }
 
    // Check for existing review
    const { data: existing } = await this.supabase
      .from('analyzer.coach_reviews')
      .select('id')
      .eq('session_id', sessionId)
      .single()
 
    if (existing) {
      throw new Error('Session already reviewed')
    }
 
    // Create review
    await this.supabase
      .from('analyzer.coach_reviews')
      .insert({
        coach_id: coachId,
        author_id: authorId,
        session_id: sessionId,
        rating,
        review_text: reviewText,
        is_verified: true // Verified because session completed
      })
 
    // Update coach rating
    await this.updateCoachRating(coachId)
  }
 
  private async updateCoachRating(coachId: string): Promise<void> {
    const { data: reviews } = await this.supabase
      .from('analyzer.coach_reviews')
      .select('rating')
      .eq('coach_id', coachId)
      .eq('is_visible', true)
 
    if (!reviews || reviews.length === 0) return
 
    const avgRating = reviews.reduce((sum, r) => sum + r.rating, 0) / reviews.length
    
    await this.supabase
      .from('analyzer.coach_profiles')
      .update({ 
        rating: Math.round(avgRating * 100) / 100,
        total_sessions: reviews.length 
      })
      .eq('id', coachId)
  }
 
  private async calculateCoachMetrics(coachId: string): Promise<any> {
    // Response time
    const { data: sessions } = await this.supabase
      .from('analyzer.consultation_sessions')
      .select('created_at, scheduled_at')
      .eq('coach_id', coachId)
      .gte('created_at', new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString())
 
    const avgResponseTime = sessions?.length ? 
      sessions.reduce((sum, s) => {
        const created = new Date(s.created_at).getTime()
        const scheduled = new Date(s.scheduled_at).getTime()
        return sum + (scheduled - created)
      }, 0) / sessions.length / (1000 * 60 * 60) : 0 // Convert to hours
 
    // Repeat client rate
    const { data: repeatClients } = await this.supabase
      .from('analyzer.consultation_sessions')
      .select('author_id')
      .eq('coach_id', coachId)
      .eq('status', 'completed')
 
    const uniqueClients = new Set(repeatClients?.map(r => r.author_id))
    const repeatRate = repeatClients && uniqueClients.size > 0 ?
      ((repeatClients.length - uniqueClients.size) / repeatClients.length) * 100 : 0
 
    return {
      avgResponseTimeHours: Math.round(avgResponseTime),
      repeatClientRate: Math.round(repeatRate),
      totalReviews: await this.getReviewCount(coachId),
      successStories: await this.getSuccessStoryCount(coachId)
    }
  }
 
  private async getReviewCount(coachId: string): Promise<number> {
    const { count } = await this.supabase
      .from('analyzer.coach_reviews')
      .select('*', { count: 'exact' })
      .eq('coach_id', coachId)
      .eq('is_visible', true)
    
    return count || 0
  }
 
  private async getSuccessStoryCount(coachId: string): Promise<number> {
    const { count } = await this.supabase
      .from('analyzer.coach_portfolios')
      .select('*', { count: 'exact' })
      .eq('coach_id', coachId)
      .eq('category', 'success_story')
    
    return count || 0
  }
}

// apps/analyzer-app/src/app/coaches/page.tsx
'use client'
 
import { useState } from 'react'
import { Input, Select, Slider, Button, Badge } from '@mystoryflow/ui'
import { Search, Filter, Star, Award, TrendingUp } from 'lucide-react'
import { CoachGrid } from '@/components/marketplace/CoachGrid'
import { CoachFilters } from '@/components/marketplace/CoachFilters'
import { useCoachSearch } from '@/hooks/useCoachSearch'
 
export default function CoachMarketplace() {
  const [searchQuery, setSearchQuery] = useState('')
  const [filters, setFilters] = useState({
    genres: [],
    priceRange: { min: 0, max: 500 },
    rating: 0,
    certifications: [],
    sortBy: 'featured'
  })
 
  const { coaches, loading, total } = useCoachSearch(searchQuery, filters)
 
  return (
    <div className="container py-8">
      {/* Header */}
      <div className="text-center mb-12">
        <h1 className="text-4xl font-bold mb-4">
          Find Your Perfect Writing Coach
        </h1>
        <p className="text-xl text-muted-foreground max-w-2xl mx-auto">
          Connect with certified coaches who specialize in your genre and can help 
          take your manuscript to the next level.
        </p>
      </div>
 
      {/* Search Bar */}
      <div className="max-w-2xl mx-auto mb-8">
        <div className="relative">
          <Search className="absolute left-3 top-1/2 transform -translate-y-1/2 text-muted-foreground" />
          <Input
            value={searchQuery}
            onChange={(e) => setSearchQuery(e.target.value)}
            placeholder="Search by name, genre, or expertise..."
            className="pl-10 pr-4 py-3 text-lg"
          />
        </div>
      </div>
 
      {/* Quick Stats */}
      <div className="grid grid-cols-4 gap-4 mb-8">
        <Card className="p-4 text-center">
          <div className="text-2xl font-bold">{total}</div>
          <div className="text-sm text-muted-foreground">Active Coaches</div>
        </Card>
        <Card className="p-4 text-center">
          <div className="text-2xl font-bold">4.8</div>
          <div className="text-sm text-muted-foreground">Average Rating</div>
        </Card>
        <Card className="p-4 text-center">
          <div className="text-2xl font-bold">2,847</div>
          <div className="text-sm text-muted-foreground">Success Stories</div>
        </Card>
        <Card className="p-4 text-center">
          <div className="text-2xl font-bold">12</div>
          <div className="text-sm text-muted-foreground">Specializations</div>
        </Card>
      </div>
 
      {/* Main Content */}
      <div className="grid lg:grid-cols-4 gap-8">
        {/* Filters Sidebar */}
        <div className="lg:col-span-1">
          <CoachFilters 
            filters={filters} 
            onFiltersChange={setFilters} 
          />
        </div>
 
        {/* Coach Grid */}
        <div className="lg:col-span-3">
          {/* Sort Options */}
          <div className="flex items-center justify-between mb-6">
            <p className="text-sm text-muted-foreground">
              Showing {coaches.length} of {total} coaches
            </p>
            <Select
              value={filters.sortBy}
              onValueChange={(value) => 
                setFilters({ ...filters, sortBy: value })
              }
            >
              <option value="featured">Featured</option>
              <option value="rating">Highest Rated</option>
              <option value="price">Lowest Price</option>
              <option value="experience">Most Experienced</option>
              <option value="sessions">Most Sessions</option>
            </Select>
          </div>
 
          {/* Coach Cards */}
          <CoachGrid coaches={coaches} loading={loading} />
        </div>
      </div>
    </div>
  )
}

// apps/analyzer-app/src/components/marketplace/CoachCard.tsx
'use client'
 
import { Card, Badge, Button } from '@mystoryflow/ui'
import { Star, Award, Clock, Users, ChevronRight } from 'lucide-react'
import Link from 'next/link'
import { CoachProfileModal } from './CoachProfileModal'
import { useState } from 'react'
 
interface CoachCardProps {
  coach: {
    id: string
    full_name: string
    bio: string
    avatar_url?: string
    specialties: string[]
    hourly_rate: number
    rating: number
    total_sessions: number
    years_experience: number
    coach_cert_status: any[]
    featured_coaches?: any
  }
}
 
export function CoachCard({ coach }: CoachCardProps) {
  const [showProfile, setShowProfile] = useState(false)
  const isFeatured = coach.featured_coaches?.featured_until && 
    new Date(coach.featured_coaches.featured_until) > new Date()
 
  return (
    <>
      <Card className={`p-6 hover:shadow-lg transition-shadow ${
        isFeatured ? 'ring-2 ring-primary' : ''
      }`}>
        {isFeatured && (
          <Badge className="absolute -top-2 -right-2" variant="default">
            Featured
          </Badge>
        )}
 
        <div className="flex items-start gap-4">
          {/* Avatar */}
          {coach.avatar_url ? (
            <img
              src={coach.avatar_url}
              alt={coach.full_name}
              className="w-16 h-16 rounded-full object-cover"
            />
          ) : (
            <div className="w-16 h-16 rounded-full bg-primary/10 flex items-center justify-center">
              <span className="text-xl font-semibold">
                {coach.full_name.charAt(0)}
              </span>
            </div>
          )}
 
          {/* Info */}
          <div className="flex-1">
            <h3 className="font-semibold text-lg">{coach.full_name}</h3>
            <p className="text-sm text-muted-foreground line-clamp-2">
              {coach.bio}
            </p>
 
            {/* Stats */}
            <div className="flex items-center gap-4 mt-3 text-sm">
              <div className="flex items-center gap-1">
                <Star className="h-4 w-4 text-yellow-500 fill-current" />
                <span className="font-medium">{coach.rating}</span>
              </div>
              <div className="flex items-center gap-1">
                <Users className="h-4 w-4" />
                <span>{coach.total_sessions} sessions</span>
              </div>
              <div className="flex items-center gap-1">
                <Clock className="h-4 w-4" />
                <span>{coach.years_experience}+ years</span>
              </div>
            </div>
 
            {/* Specialties */}
            <div className="flex flex-wrap gap-2 mt-3">
              {coach.specialties.slice(0, 3).map(specialty => (
                <Badge key={specialty} variant="secondary" size="sm">
                  {specialty}
                </Badge>
              ))}
              {coach.specialties.length > 3 && (
                <Badge variant="outline" size="sm">
                  +{coach.specialties.length - 3} more
                </Badge>
              )}
            </div>
 
            {/* Certifications */}
            {coach.coach_cert_status.length > 0 && (
              <div className="flex items-center gap-2 mt-3">
                {coach.coach_cert_status
                  .filter(cert => cert.status === 'approved')
                  .slice(0, 2)
                  .map(cert => (
                    <div
                      key={cert.certification_id}
                      className="flex items-center gap-1"
                      title={cert.coach_certifications.name}
                    >
                      <Award 
                        className="h-4 w-4" 
                        style={{ color: cert.coach_certifications.badge_color }}
                      />
                    </div>
                  ))
                }
              </div>
            )}
          </div>
 
          {/* Price */}
          <div className="text-right">
            <div className="text-2xl font-bold">
              ${coach.hourly_rate / 100}
            </div>
            <div className="text-sm text-muted-foreground">per hour</div>
          </div>
        </div>
 
        {/* Actions */}
        <div className="flex gap-3 mt-4 pt-4 border-t">
          <Button
            variant="outline"
            size="sm"
            className="flex-1"
            onClick={() => setShowProfile(true)}
          >
            View Profile
          </Button>
          <Button size="sm" className="flex-1" asChild>
            <Link href={`/coaches/${coach.id}/book`}>
              Book Session
              <ChevronRight className="h-4 w-4 ml-1" />
            </Link>
          </Button>
        </div>
      </Card>
 
      {/* Profile Modal */}
      {showProfile && (
        <CoachProfileModal
          coachId={coach.id}
          onClose={() => setShowProfile(false)}
        />
      )}
    </>
  )
}

// apps/analyzer-app/src/components/marketplace/CertificationBadges.tsx
'use client'
 
import { Badge, Tooltip } from '@mystoryflow/ui'
import { Award, CheckCircle, Info } from 'lucide-react'
 
interface Certification {
  id: string
  name: string
  code: string
  description: string
  badge_color: string
  badge_icon: string
  status: string
  issued_at?: string
}
 
export function CertificationBadges({ 
  certifications 
}: { 
  certifications: Certification[] 
}) {
  const activeCerts = certifications.filter(c => c.status === 'approved')
 
  const certIcons = {
    'genre_specialist': '📚',
    'bestseller_coach': '🏆',
    'industry_pro': '💼',
    'master_editor': '✏️'
  }
 
  const certColors = {
    'purple': 'bg-purple-100 text-purple-800 dark:bg-purple-900 dark:text-purple-200',
    'gold': 'bg-yellow-100 text-yellow-800 dark:bg-yellow-900 dark:text-yellow-200',
    'blue': 'bg-blue-100 text-blue-800 dark:bg-blue-900 dark:text-blue-200',
    'green': 'bg-green-100 text-green-800 dark:bg-green-900 dark:text-green-200'
  }
 
  return (
    <div className="space-y-4">
      <h3 className="font-semibold flex items-center gap-2">
        <Award className="h-5 w-5" />
        Certifications & Credentials
      </h3>
 
      <div className="space-y-3">
        {activeCerts.map(cert => (
          <div key={cert.id} className="flex items-start gap-3">
            <div className={`
              p-2 rounded-lg ${certColors[cert.badge_color] || certColors.blue}
            `}>
              <span className="text-xl">
                {certIcons[cert.code] || '🎓'}
              </span>
            </div>
            
            <div className="flex-1">
              <div className="flex items-center gap-2">
                <h4 className="font-medium">{cert.name}</h4>
                <CheckCircle className="h-4 w-4 text-green-500" />
                <Tooltip content={cert.description}>
                  <Info className="h-4 w-4 text-muted-foreground cursor-help" />
                </Tooltip>
              </div>
              <p className="text-sm text-muted-foreground">
                {cert.description}
              </p>
              {cert.issued_at && (
                <p className="text-xs text-muted-foreground mt-1">
                  Certified since {new Date(cert.issued_at).getFullYear()}
                </p>
              )}
            </div>
          </div>
        ))}
      </div>
 
      {activeCerts.length === 0 && (
        <p className="text-sm text-muted-foreground italic">
          No verified certifications yet
        </p>
      )}
    </div>
  )
}