┌─────────────────────────────────────────────────────────────────────────┐
│                     GENERATION ENGINE PIPELINE                           │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│   INPUTS                                                                 │
│   ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐               │
│   │  Voice   │  │ Template │  │ Context  │  │ Platform │               │
│   │ Profile  │  │          │  │  (topic, │  │ Patterns │               │
│   │          │  │          │  │keywords) │  │          │               │
│   └────┬─────┘  └────┬─────┘  └────┬─────┘  └────┬─────┘               │
│        └─────────────┴─────────────┴─────────────┘                      │
│                              │                                           │
│                              ▼                                           │
│   ┌─────────────────────────────────────────────────────────────────┐   │
│   │                    PROMPT ASSEMBLY                               │   │
│   │   Voice instructions + Template structure + Context + Patterns   │   │
│   └─────────────────────────────────────────────────────────────────┘   │
│                              │                                           │
│                              ▼                                           │
│   ┌─────────────────────────────────────────────────────────────────┐   │
│   │                    MODEL ROUTER                                  │   │
│   │   Task complexity → Model selection → Cost optimization          │   │
│   └─────────────────────────────────────────────────────────────────┘   │
│                              │                                           │
│                              ▼                                           │
│   ┌─────────────────────────────────────────────────────────────────┐   │
│   │                    AI GENERATION                                 │   │
│   │   Claude / GPT-4 / GPT-4o-mini / Gemini Flash                   │   │
│   └─────────────────────────────────────────────────────────────────┘   │
│                              │                                           │
│                              ▼                                           │
│   ┌─────────────────────────────────────────────────────────────────┐   │
│   │                    QUALITY GATE                                  │   │
│   │   Length check → Voice consistency → Factual accuracy → Score   │   │
│   └─────────────────────────────────────────────────────────────────┘   │
│                              │                                           │
│              ┌───────────────┴───────────────┐                          │
│              ▼                               ▼                          │
│        ┌──────────┐                   ┌──────────┐                      │
│        │ APPROVED │                   │ REJECTED │                      │
│        │  Ready   │                   │ Regenerate│                      │
│        │for review│                   │or escalate│                      │
│        └──────────┘                   └──────────┘                      │
│                                                                          │
└─────────────────────────────────────────────────────────────────────────┘

CREATE TABLE voice_profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Identity
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  description text,
  
  -- Ownership
  business_unit text,                     -- 'publisher', 'platform', 'agency', 'store'
  vertical_id uuid REFERENCES publisher_verticals(id),
  client_id uuid REFERENCES agency_clients(id),
  
  -- Voice definition
  tone text[],                            -- ['conversational', 'authoritative', 'witty']
  personality text,                       -- Overall personality description
  
  -- Writing style
  sentence_length text,                   -- 'short', 'medium', 'long', 'varied'
  vocabulary_level text,                  -- 'simple', 'intermediate', 'advanced', 'technical'
  formality text,                         -- 'casual', 'neutral', 'formal'
  
  -- Specific instructions
  do_instructions text[],                 -- Things to always do
  dont_instructions text[],               -- Things to never do
  
  -- Examples
  example_excerpts text[],                -- Sample content in this voice
  
  -- Technical
  system_prompt text,                     -- Full system prompt for AI
  
  -- Usage stats
  times_used integer DEFAULT 0,
  avg_quality_score numeric,
  
  -- Status
  status text DEFAULT 'active',
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_voice_profiles_business_unit ON voice_profiles(business_unit);
CREATE INDEX idx_voice_profiles_vertical ON voice_profiles(vertical_id);
CREATE INDEX idx_voice_profiles_client ON voice_profiles(client_id);
CREATE INDEX idx_voice_profiles_status ON voice_profiles(status);

CREATE TABLE content_templates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Identity
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  description text,
  
  -- Template type
  template_type text NOT NULL,            -- 'article', 'social', 'email', 'product_description', 'ad_copy'
  platform_id uuid REFERENCES platforms(id),
  
  -- Ownership
  business_unit text,
  vertical_id uuid REFERENCES publisher_verticals(id),
  
  -- Template content
  structure text NOT NULL,                -- Template with {{variables}}
  
  -- Output specs
  min_length integer,
  max_length integer,
  
  -- Voice
  default_voice_id uuid REFERENCES voice_profiles(id),
  
  -- Usage stats
  times_used integer DEFAULT 0,
  avg_quality_score numeric,
  
  -- Status
  status text DEFAULT 'active',
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_content_templates_type ON content_templates(template_type);
CREATE INDEX idx_content_templates_platform ON content_templates(platform_id);
CREATE INDEX idx_content_templates_business_unit ON content_templates(business_unit);

CREATE TABLE template_variables (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  template_id uuid REFERENCES content_templates(id) NOT NULL,
  
  -- Variable definition
  variable_name text NOT NULL,            -- Name in template: {{variable_name}}
  variable_type text NOT NULL,            -- 'text', 'keyword', 'list', 'number', 'date', 'reference'
  
  -- Constraints
  required boolean DEFAULT true,
  default_value text,
  min_length integer,
  max_length integer,
  allowed_values text[],                  -- For enum-like variables
  
  -- Description
  description text,
  example_value text,
  
  -- Order
  display_order integer,
  
  UNIQUE(template_id, variable_name)
);

CREATE INDEX idx_template_variables_template ON template_variables(template_id);

CREATE TABLE generated_content (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Source
  template_id uuid REFERENCES content_templates(id),
  voice_id uuid REFERENCES voice_profiles(id),
  
  -- Generation inputs
  input_variables jsonb DEFAULT '{}',     -- Variables passed to template
  context jsonb DEFAULT '{}',             -- Additional context
  
  -- Output
  content_type text NOT NULL,             -- 'article', 'social', 'email', etc.
  title text,
  content text NOT NULL,
  metadata jsonb DEFAULT '{}',            -- Extracted metadata (keywords, etc.)
  
  -- Quality
  quality_score integer,                  -- 0-100
  quality_checks jsonb DEFAULT '{}',      -- Individual check results
  
  -- Generation details
  model_used text,
  prompt_tokens integer,
  completion_tokens integer,
  generation_cost numeric(10,6),
  generation_time_ms integer,
  
  -- Revisions
  revision_number integer DEFAULT 1,
  parent_id uuid REFERENCES generated_content(id),
  revision_reason text,
  
  -- Status
  status text DEFAULT 'draft',            -- 'draft', 'approved', 'published', 'rejected'
  
  -- Usage tracking
  used_in_post_id uuid,
  used_in_distribution_id uuid,
  
  -- Ownership
  business_unit text,
  client_id uuid,
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_generated_content_template ON generated_content(template_id);
CREATE INDEX idx_generated_content_voice ON generated_content(voice_id);
CREATE INDEX idx_generated_content_type ON generated_content(content_type);
CREATE INDEX idx_generated_content_status ON generated_content(status);
CREATE INDEX idx_generated_content_quality ON generated_content(quality_score DESC);
CREATE INDEX idx_generated_content_parent ON generated_content(parent_id);

CREATE TABLE generation_queue (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Job definition
  job_type text NOT NULL,                 -- 'article', 'batch', 'repurpose'
  
  -- Inputs
  template_id uuid REFERENCES content_templates(id),
  voice_id uuid REFERENCES voice_profiles(id),
  variables jsonb DEFAULT '{}',
  context jsonb DEFAULT '{}',
  
  -- Scheduling
  priority integer DEFAULT 100,           -- Lower = higher priority
  scheduled_for timestamptz DEFAULT now(),
  
  -- Processing
  status text DEFAULT 'pending',          -- 'pending', 'processing', 'completed', 'failed'
  started_at timestamptz,
  completed_at timestamptz,
  
  -- Result
  generated_content_id uuid REFERENCES generated_content(id),
  error_message text,
  retry_count integer DEFAULT 0,
  
  -- Ownership
  business_unit text,
  requested_by uuid,
  
  created_at timestamptz DEFAULT now()
);

CREATE INDEX idx_generation_queue_status ON generation_queue(status);
CREATE INDEX idx_generation_queue_scheduled ON generation_queue(scheduled_for) 
  WHERE status = 'pending';
CREATE INDEX idx_generation_queue_priority ON generation_queue(priority, scheduled_for);

CREATE TABLE creative_components (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Identity
  name text NOT NULL,
  component_type text NOT NULL,           -- 'hook', 'cta', 'headline', 'transition', 'closing'
  
  -- Content
  content text NOT NULL,
  
  -- Applicability
  platform_id uuid REFERENCES platforms(id),
  vertical_id uuid REFERENCES publisher_verticals(id),
  content_types text[],                   -- What content types this works for
  
  -- Performance
  times_used integer DEFAULT 0,
  avg_engagement numeric,
  avg_conversion numeric,
  
  -- A/B testing
  variant_group text,                     -- Group related variants
  is_control boolean DEFAULT false,
  
  -- Status
  status text DEFAULT 'active',
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_creative_components_type ON creative_components(component_type);
CREATE INDEX idx_creative_components_platform ON creative_components(platform_id);
CREATE INDEX idx_creative_components_vertical ON creative_components(vertical_id);

const publisherVoice = {
  name: 'Trending Society Editorial',
  slug: 'ts-editorial',
  business_unit: 'publisher',
  
  tone: ['conversational', 'authoritative', 'helpful'],
  personality: `
    A knowledgeable friend who researches everything thoroughly.
    Confident but not arrogant. Practical over theoretical.
    Gets to the point quickly but includes necessary context.
  `,
  
  sentence_length: 'varied',
  vocabulary_level: 'intermediate',
  formality: 'neutral',
  
  do_instructions: [
    'Start with a compelling hook that addresses the reader\'s problem',
    'Use "you" to speak directly to the reader',
    'Include specific numbers and data when available',
    'Break up long sections with subheadings',
    'End sections with actionable takeaways'
  ],
  
  dont_instructions: [
    'Never use clickbait or sensationalism',
    'Avoid jargon without explanation',
    'Don\'t make claims without backing them up',
    'Never start with "In this article..."',
    'Don\'t use passive voice excessively'
  ],
  
  example_excerpts: [
    `Here's the thing about golf drivers: the right one can add 20 yards to your drive, but the wrong one will cost you strokes and confidence. After testing 15 drivers over 6 months, we found three clear winners for different skill levels.`,
    `Sleep supplements flood the market with big promises and small results. We cut through the noise by analyzing 47 products for ingredient quality, dosing accuracy, and real user outcomes.`
  ],
  
  system_prompt: `
You are a writer for Trending Society, a lifestyle publication focused on helping readers make informed decisions. Your tone is conversational but authoritative—like a knowledgeable friend who has done the research.

Key principles:
- Lead with value, not fluff
- Support claims with specifics
- Speak directly to the reader using "you"
- Be practical over theoretical
- Include actionable takeaways

Avoid:
- Clickbait or sensationalism
- Unexplained jargon
- Vague generalities
- Passive voice
- Starting with "In this article"
`
}

const clientVoice = {
  name: 'Golf Insider',
  slug: 'golf-insider',
  business_unit: 'agency',
  client_id: 'client-uuid-here',
  
  tone: ['enthusiastic', 'insider', 'technical'],
  personality: `
    A passionate golfer who lives and breathes the game.
    Knows the technical details but explains them accessibly.
    Speaks like someone who's been on the course this morning.
  `,
  
  sentence_length: 'medium',
  vocabulary_level: 'intermediate',
  formality: 'casual',
  
  do_instructions: [
    'Use golf terminology naturally',
    'Reference specific courses, tournaments, or players when relevant',
    'Include personal anecdotes or observations',
    'Write like you\'re talking to a fellow golfer at the 19th hole'
  ],
  
  dont_instructions: [
    'Don\'t explain basic golf terms',
    'Avoid corporate-speak',
    'Never sound like a sales pitch',
    'Don\'t use generic stock photo descriptions'
  ],
  
  system_prompt: `
You write for Golf Insider, a publication for serious golfers who want insider knowledge. Write like you're a passionate golfer sharing insights with friends at the clubhouse.

You can assume readers know golf basics—no need to explain what a birdie is. But do break down advanced concepts when discussing technique or equipment.

Your enthusiasm for the game should come through, but keep it grounded in practical advice and real experiences.
`
}

const seoArticleTemplate = {
  name: 'SEO Article - Listicle',
  slug: 'seo-listicle',
  template_type: 'article',
  business_unit: 'publisher',
  
  structure: `
# {{title}}

{{hook}}

**Quick Summary:** {{summary}}

---

## What We'll Cover
{{toc}}

---

{{#each items}}
## {{position}}. {{item_name}}

{{item_description}}

**Key Features:**
{{item_features}}

**Best For:** {{item_best_for}}

{{#if item_price}}
**Price:** {{item_price}}
{{/if}}

{{/each}}

---

## How We Chose These {{category}}

{{methodology}}

---

## Final Thoughts

{{conclusion}}

{{cta}}
`,
  
  min_length: 1500,
  max_length: 3000,
  
  variables: [
    { name: 'title', type: 'text', required: true },
    { name: 'hook', type: 'text', required: true, max_length: 200 },
    { name: 'summary', type: 'text', required: true, max_length: 150 },
    { name: 'toc', type: 'list', required: true },
    { name: 'items', type: 'list', required: true },
    { name: 'category', type: 'text', required: true },
    { name: 'methodology', type: 'text', required: true },
    { name: 'conclusion', type: 'text', required: true },
    { name: 'cta', type: 'text', required: true }
  ]
}

const socialTemplate = {
  name: 'Twitter Thread - Tips',
  slug: 'twitter-thread-tips',
  template_type: 'social',
  platform_id: 'twitter-platform-id',
  
  structure: `
🧵 {{hook}}

Here's what most people get wrong about {{topic}}:

{{mistake_1}}

---

Instead, try this:

{{tip_1}}

{{example_1}}

---

{{tip_2}}

{{example_2}}

---

{{tip_3}}

{{example_3}}

---

TL;DR:
{{summary}}

{{cta}}
`,
  
  min_length: 800,
  max_length: 2000,
  
  variables: [
    { name: 'hook', type: 'text', required: true, max_length: 200 },
    { name: 'topic', type: 'keyword', required: true },
    { name: 'mistake_1', type: 'text', required: true },
    { name: 'tip_1', type: 'text', required: true },
    { name: 'example_1', type: 'text', required: false },
    { name: 'tip_2', type: 'text', required: true },
    { name: 'example_2', type: 'text', required: false },
    { name: 'tip_3', type: 'text', required: true },
    { name: 'example_3', type: 'text', required: false },
    { name: 'summary', type: 'text', required: true, max_length: 200 },
    { name: 'cta', type: 'text', required: true }
  ]
}

async function generateContent(request: {
  templateId?: string
  voiceId?: string
  contentType: string
  variables: Record<string, any>
  context?: Record<string, any>
  businessUnit?: string
}) {
  // 1. Load template and voice
  const template = request.templateId
    ? await getTemplate(request.templateId)
    : await getDefaultTemplate(request.contentType)
  
  const voice = request.voiceId
    ? await getVoiceProfile(request.voiceId)
    : template?.default_voice_id
      ? await getVoiceProfile(template.default_voice_id)
      : await getDefaultVoice(request.businessUnit)
  
  // 2. Load platform patterns if applicable
  const patterns = template?.platform_id
    ? await getPlatformPatterns(template.platform_id)
    : []
  
  // 3. Assemble prompt
  const prompt = assemblePrompt(template, voice, request.variables, request.context, patterns)
  
  // 4. Select model based on complexity
  const model = selectModel(request.contentType, template?.max_length || 2000)
  
  // 5. Generate
  const startTime = Date.now()
  const response = await callAI(prompt, model)
  const generationTime = Date.now() - startTime
  
  // 6. Parse output
  const parsed = parseGeneratedContent(response, template)
  
  // 7. Quality check
  const qualityResult = await runQualityChecks(parsed, template, voice)
  
  // 8. Store result
  const { data: generated } = await supabase
    .from('generated_content')
    .insert({
      template_id: template?.id,
      voice_id: voice?.id,
      input_variables: request.variables,
      context: request.context,
      content_type: request.contentType,
      title: parsed.title,
      content: parsed.content,
      metadata: parsed.metadata,
      quality_score: qualityResult.score,
      quality_checks: qualityResult.checks,
      model_used: model,
      prompt_tokens: response.usage?.prompt_tokens,
      completion_tokens: response.usage?.completion_tokens,
      generation_cost: calculateCost(response.usage, model),
      generation_time_ms: generationTime,
      status: qualityResult.score >= 70 ? 'draft' : 'rejected',
      business_unit: request.businessUnit
    })
    .select()
    .single()
  
  // 9. Update usage stats
  await updateUsageStats(template?.id, voice?.id, qualityResult.score)
  
  return generated
}

function assemblePrompt(
  template: ContentTemplate | null,
  voice: VoiceProfile | null,
  variables: Record<string, any>,
  context: Record<string, any>,
  patterns: PlatformPattern[]
) {
  const parts: string[] = []
  
  // System instructions
  if (voice?.system_prompt) {
    parts.push(`<voice>\n${voice.system_prompt}\n</voice>`)
  }
  
  // Platform patterns
  if (patterns.length > 0) {
    const patternInstructions = patterns
      .filter(p => p.confidence_score > 0.5)
      .map(p => `- ${p.pattern_name}: ${p.pattern_description}`)
      .join('\n')
    
    parts.push(`<platform_patterns>\n${patternInstructions}\n</platform_patterns>`)
  }
  
  // Context
  if (context && Object.keys(context).length > 0) {
    parts.push(`<context>\n${JSON.stringify(context, null, 2)}\n</context>`)
  }
  
  // Template with variables filled
  if (template?.structure) {
    const filledTemplate = fillTemplate(template.structure, variables)
    parts.push(`<template>\n${filledTemplate}\n</template>`)
  } else {
    // No template - direct generation
    parts.push(`<request>\nGenerate ${variables.content_type || 'content'} about: ${variables.topic}\n\nRequirements:\n- ${variables.requirements?.join('\n- ') || 'High quality, engaging content'}\n</request>`)
  }
  
  // Output instructions
  const outputInstructions = []
  if (template?.min_length) {
    outputInstructions.push(`Minimum length: ${template.min_length} words`)
  }
  if (template?.max_length) {
    outputInstructions.push(`Maximum length: ${template.max_length} words`)
  }
  
  if (outputInstructions.length > 0) {
    parts.push(`<output_requirements>\n${outputInstructions.join('\n')}\n</output_requirements>`)
  }
  
  return parts.join('\n\n')
}

function fillTemplate(template: string, variables: Record<string, any>) {
  let filled = template
  
  // Simple variable replacement: {{variable}}
  for (const [key, value] of Object.entries(variables)) {
    const regex = new RegExp(`{{${key}}}`, 'g')
    filled = filled.replace(regex, String(value))
  }
  
  // Handle conditionals: {{#if variable}}...{{/if}}
  filled = filled.replace(
    /{{#if (\w+)}}([\s\S]*?){{\/if}}/g,
    (match, varName, content) => variables[varName] ? content : ''
  )
  
  // Handle loops: {{#each items}}...{{/each}}
  filled = filled.replace(
    /{{#each (\w+)}}([\s\S]*?){{\/each}}/g,
    (match, varName, content) => {
      const items = variables[varName]
      if (!Array.isArray(items)) return ''
      
      return items.map((item, index) => {
        let itemContent = content
        itemContent = itemContent.replace(/{{position}}/g, String(index + 1))
        for (const [key, value] of Object.entries(item)) {
          itemContent = itemContent.replace(
            new RegExp(`{{${key}}}`, 'g'),
            String(value)
          )
        }
        return itemContent
      }).join('\n\n')
    }
  )
  
  return filled
}

function selectModel(contentType: string, maxLength: number): string {
  // High-complexity content
  if (
    contentType === 'article' && maxLength > 2000 ||
    contentType === 'whitepaper' ||
    contentType === 'case_study'
  ) {
    return 'claude-sonnet-4-20250514'
  }
  
  // Medium-complexity content
  if (
    contentType === 'article' ||
    contentType === 'email' ||
    contentType === 'landing_page'
  ) {
    return 'gpt-4o'
  }
  
  // Simple/short content
  if (
    contentType === 'social' ||
    contentType === 'product_description' ||
    contentType === 'meta_description'
  ) {
    return 'gpt-4o-mini'
  }
  
  // Batch processing
  if (contentType === 'batch') {
    return 'gemini-2.0-flash'
  }
  
  // Default
  return 'gpt-4o-mini'
}

async function runQualityChecks(
  content: ParsedContent,
  template: ContentTemplate | null,
  voice: VoiceProfile | null
) {
  const checks: Record<string, QualityCheck> = {}
  
  // Length check
  const wordCount = content.content.split(/\s+/).length
  checks.length = {
    name: 'Length',
    passed: template 
      ? wordCount >= (template.min_length || 0) && wordCount <= (template.max_length || Infinity)
      : wordCount >= 100,
    score: calculateLengthScore(wordCount, template?.min_length, template?.max_length),
    details: `${wordCount} words`
  }
  
  // Voice consistency check
  if (voice) {
    checks.voice = await checkVoiceConsistency(content.content, voice)
  }
  
  // Completeness check (all template sections filled)
  if (template) {
    checks.completeness = checkCompleteness(content.content, template)
  }
  
  // Readability check
  checks.readability = {
    name: 'Readability',
    passed: true,
    score: calculateReadabilityScore(content.content),
    details: `Flesch-Kincaid grade level`
  }
  
  // Originality check (not too similar to training data)
  checks.originality = await checkOriginality(content.content)
  
  // Calculate overall score
  const scores = Object.values(checks).map(c => c.score)
  const overallScore = Math.round(
    scores.reduce((sum, s) => sum + s, 0) / scores.length
  )
  
  return {
    score: overallScore,
    checks,
    passed: overallScore >= 70 && Object.values(checks).every(c => c.passed || c.score >= 50)
  }
}

async function checkVoiceConsistency(content: string, voice: VoiceProfile) {
  // Use AI to check voice consistency
  const prompt = `
Analyze if this content matches the voice profile:

Voice Profile:
- Tone: ${voice.tone.join(', ')}
- Formality: ${voice.formality}
- Do: ${voice.do_instructions.join('; ')}
- Don't: ${voice.dont_instructions.join('; ')}

Content:
${content.slice(0, 2000)}

Rate voice consistency from 0-100 and explain any issues.
Respond in JSON: {"score": number, "issues": string[]}
`
  
  const response = await callAI(prompt, 'gpt-4o-mini')
  const result = JSON.parse(response)
  
  return {
    name: 'Voice Consistency',
    passed: result.score >= 70,
    score: result.score,
    details: result.issues.length > 0 ? result.issues.join(', ') : 'Voice matches profile'
  }
}

function checkCompleteness(content: string, template: ContentTemplate) {
  // Check for required sections
  const requiredSections = extractRequiredSections(template.structure)
  const missingSection = requiredSections.find(section => 
    !content.toLowerCase().includes(section.toLowerCase())
  )
  
  return {
    name: 'Completeness',
    passed: !missingSection,
    score: missingSection ? 50 : 100,
    details: missingSection ? `Missing: ${missingSection}` : 'All sections present'
  }
}

// Cron: Every 5 minutes
async function processGenerationQueue() {
  // Get pending jobs
  const { data: jobs } = await supabase
    .from('generation_queue')
    .select('*')
    .eq('status', 'pending')
    .lte('scheduled_for', new Date().toISOString())
    .order('priority')
    .order('scheduled_for')
    .limit(10)
  
  for (const job of jobs) {
    try {
      // Mark as processing
      await supabase
        .from('generation_queue')
        .update({ 
          status: 'processing',
          started_at: new Date().toISOString()
        })
        .eq('id', job.id)
      
      // Generate content
      const generated = await generateContent({
        templateId: job.template_id,
        voiceId: job.voice_id,
        contentType: job.job_type,
        variables: job.variables,
        context: job.context,
        businessUnit: job.business_unit
      })
      
      // Update job
      await supabase
        .from('generation_queue')
        .update({
          status: 'completed',
          completed_at: new Date().toISOString(),
          generated_content_id: generated.id
        })
        .eq('id', job.id)
      
    } catch (error) {
      await supabase
        .from('generation_queue')
        .update({
          status: job.retry_count >= 3 ? 'failed' : 'pending',
          retry_count: job.retry_count + 1,
          error_message: error.message,
          scheduled_for: new Date(Date.now() + 300000).toISOString() // 5 min retry
        })
        .eq('id', job.id)
    }
  }
}

async function queueBatchGeneration(batch: {
  templateId: string
  voiceId?: string
  itemsToGenerate: Array<{ variables: Record<string, any> }>
  priority?: number
  businessUnit: string
}) {
  const jobs = batch.itemsToGenerate.map((item, index) => ({
    job_type: 'batch',
    template_id: batch.templateId,
    voice_id: batch.voiceId,
    variables: item.variables,
    priority: batch.priority || 100,
    scheduled_for: new Date(Date.now() + index * 1000).toISOString(), // Stagger
    business_unit: batch.businessUnit
  }))
  
  await supabase
    .from('generation_queue')
    .insert(jobs)
  
  return { queued: jobs.length }
}

async function repurposeContent(
  sourceContentId: string,
  targetPlatforms: string[]
) {
  // Get source content
  const { data: source } = await supabase
    .from('generated_content')
    .select('*')
    .eq('id', sourceContentId)
    .single()
  
  if (!source) throw new Error('Source content not found')
  
  const results = []
  
  for (const platformId of targetPlatforms) {
    // Get platform specs
    const { data: platform } = await supabase
      .from('platforms')
      .select('*')
      .eq('id', platformId)
      .single()
    
    // Get platform-specific template
    const { data: template } = await supabase
      .from('content_templates')
      .select('*')
      .eq('platform_id', platformId)
      .eq('status', 'active')
      .order('times_used', { ascending: false })
      .limit(1)
      .single()
    
    // Generate repurposed version
    const repurposed = await generateContent({
      templateId: template?.id,
      voiceId: source.voice_id,
      contentType: 'social',
      variables: {
        source_content: source.content,
        source_title: source.title,
        platform: platform.name
      },
      context: {
        repurpose_from: sourceContentId,
        target_platform: platform.name,
        max_length: platform.max_content_length
      }
    })
    
    results.push({
      platform: platform.name,
      content_id: repurposed.id,
      quality_score: repurposed.quality_score
    })
  }
  
  return results
}

SELECT 
  ct.name as template,
  ct.template_type,
  COUNT(gc.id) as generations,
  ROUND(AVG(gc.quality_score), 1) as avg_quality,
  ROUND(AVG(gc.generation_cost), 4) as avg_cost,
  ROUND(AVG(gc.generation_time_ms), 0) as avg_time_ms,
  COUNT(CASE WHEN gc.status = 'approved' THEN 1 END) as approved,
  COUNT(CASE WHEN gc.status = 'rejected' THEN 1 END) as rejected
FROM content_templates ct
LEFT JOIN generated_content gc ON ct.id = gc.template_id
WHERE gc.created_at > now() - interval '30 days'
GROUP BY ct.id, ct.name, ct.template_type
ORDER BY generations DESC;

SELECT 
  vp.name as voice,
  vp.business_unit,
  COUNT(gc.id) as generations,
  ROUND(AVG(gc.quality_score), 1) as avg_quality,
  ROUND(AVG(
    (gc.quality_checks->>'voice')::jsonb->>'score'
  )::numeric, 1) as avg_voice_consistency
FROM voice_profiles vp
LEFT JOIN generated_content gc ON vp.id = gc.voice_id
WHERE gc.created_at > now() - interval '30 days'
GROUP BY vp.id, vp.name, vp.business_unit
ORDER BY avg_quality DESC;

SELECT 
  status,
  COUNT(*) as job_count,
  AVG(retry_count) as avg_retries,
  MIN(scheduled_for) as earliest_scheduled,
  MAX(completed_at) as latest_completed
FROM generation_queue
WHERE created_at > now() - interval '24 hours'
GROUP BY status;

SELECT 
  model_used,
  COUNT(*) as generations,
  SUM(prompt_tokens) as total_prompt_tokens,
  SUM(completion_tokens) as total_completion_tokens,
  SUM(generation_cost) as total_cost,
  ROUND(AVG(quality_score), 1) as avg_quality
FROM generated_content
WHERE created_at > now() - interval '30 days'
GROUP BY model_used
ORDER BY total_cost DESC;