Source Content (scraped)
        ↓
Content Analysis (AI extracts facts)
        ↓
Content Brief (topic + keywords + structure)
        ↓
Generation (AI writes draft)
        ↓
Human Review (quality gate)
        ↓
SEO Optimization (meta tags, links)
        ↓
Scheduling (optimal timing)
        ↓
Publishing (to vertical subdomain)
        ↓
Performance Tracking (analytics)

-- publisher_posts.status values
'draft'      → Initial AI generation
'review'     → Awaiting human review
'scheduled'  → Approved, waiting to publish
'published'  → Live on site
'archived'   → Removed from site

draft → review → scheduled → published
  ↓        ↓
  └── archived (rejected or outdated)

CREATE TABLE publisher_posts (
  id uuid PRIMARY KEY,
  canonical_id text UNIQUE NOT NULL,
  vertical_id uuid REFERENCES publisher_verticals(id),
  author_id uuid REFERENCES users(id),
  
  -- Content
  title text NOT NULL,
  slug text NOT NULL,
  content text NOT NULL,          -- Markdown or HTML
  excerpt text,                   -- For previews
  
  -- Status
  status text DEFAULT 'draft',
  
  -- SEO
  meta_title text,
  meta_description text,
  featured_image_url text,
  
  -- AI generation tracking
  ai_generated boolean DEFAULT false,
  ai_model text,
  source_content_id uuid REFERENCES raw_content(id),
  generated_content_id uuid REFERENCES generated_content(id),
  
  -- Human review
  human_reviewed boolean DEFAULT false,
  reviewed_by uuid REFERENCES users(id),
  reviewed_at timestamptz,
  
  -- Publishing
  scheduled_at timestamptz,
  published_at timestamptz,
  
  UNIQUE(vertical_id, slug)
);

{
  "brief": {
    "topic": "Best Golf Drivers for High Handicappers",
    "target_keyword": "best golf drivers high handicap",
    "secondary_keywords": ["forgiving drivers", "easy to hit drivers"],
    "target_word_count": 2000,
    "structure": [
      {"type": "intro", "word_count": 200},
      {"type": "h2", "title": "What Makes a Driver Forgiving?", "word_count": 300},
      {"type": "h2", "title": "Top 5 Drivers for High Handicappers", "word_count": 800},
      {"type": "h2", "title": "How to Choose the Right Driver", "word_count": 400},
      {"type": "h2", "title": "FAQ", "word_count": 300}
    ],
    "tone": "helpful, encouraging",
    "affiliate_opportunities": ["driver reviews", "equipment guides"],
    "internal_links": ["golf-swing-basics", "choosing-golf-clubs"]
  }
}

-- Find unprocessed content with high engagement
SELECT rc.id, rc.caption, ca.topics, ca.hook_strength
FROM raw_content rc
JOIN content_analysis ca ON ca.content_id = rc.id
LEFT JOIN publisher_posts pp ON pp.source_content_id = rc.id
WHERE pp.id IS NULL  -- Not yet turned into a post
  AND ca.hook_strength >= 70
  AND rc.engagement_rate >= 0.05
ORDER BY rc.engagement_rate DESC
LIMIT 20;

const briefPrompt = `
Analyze this content and create a blog post brief.

Source content:
${content.caption}

Extracted topics: ${analysis.topics.join(', ')}
Vertical: ${vertical.name}

Create a brief with:
1. Target keyword (search-optimized)
2. Secondary keywords (3-5)
3. Article structure (H2 sections)
4. Target word count
5. Tone guidance
6. Affiliate opportunities
7. Internal linking opportunities

Output as JSON.
`;

-- From content_templates
SELECT system_prompt, user_prompt_template, model_name, temperature
FROM content_templates
WHERE output_type = 'article'
  AND is_active = true;

async function generateArticle(brief, voiceProfile, template) {
  // 1. Build prompt with fact injection
  const prompt = buildPrompt(template, {
    brief,
    voice: voiceProfile,
    facts: brief.extracted_facts,
    keywords: brief.target_keyword
  });
  
  // 2. Generate with cost-optimized model
  const response = await llm.generate({
    model: template.model_name || 'gpt-4o-mini',
    prompt,
    maxTokens: template.max_tokens || 3000,
    temperature: template.temperature || 0.7
  });
  
  // 3. Track costs
  await trackAiUsage({
    task: 'generate_article',
    model: template.model_name,
    inputTokens: response.usage.prompt_tokens,
    outputTokens: response.usage.completion_tokens,
    cost: calculateCost(response.usage)
  });
  
  // 4. Create draft post
  const post = await createPost({
    vertical_id: brief.vertical_id,
    title: response.title,
    slug: slugify(response.title),
    content: response.content,
    excerpt: response.excerpt,
    meta_title: response.meta_title,
    meta_description: response.meta_description,
    status: 'draft',
    ai_generated: true,
    ai_model: template.model_name,
    source_content_id: brief.source_content_id
  });
  
  return post;
}

{
  "title": "Best Golf Drivers for High Handicappers in 2025",
  "meta_title": "Best Golf Drivers for High Handicappers 2025 | Expert Guide",
  "meta_description": "Discover the most forgiving golf drivers for high handicappers. Our experts review the top 5 drivers that will help improve your game.",
  "excerpt": "Looking for a forgiving driver? We've tested the top options for high handicappers.",
  "content": "# Best Golf Drivers for High Handicappers\n\n[Full article content...]",
  "word_count": 2147,
  "reading_time": 9
}

-- Posts awaiting review
SELECT 
  pp.id,
  pp.title,
  pv.name as vertical,
  pp.created_at,
  u.full_name as author
FROM publisher_posts pp
JOIN publisher_verticals pv ON pp.vertical_id = pv.id
LEFT JOIN users u ON pp.author_id = u.id
WHERE pp.status = 'draft'
  AND pp.human_reviewed = false
ORDER BY pp.created_at ASC;

-- Approve and schedule
UPDATE publisher_posts SET
  status = 'scheduled',
  human_reviewed = true,
  reviewed_by = 'reviewer-uuid',
  reviewed_at = now(),
  scheduled_at = '2025-12-20 09:00:00'
WHERE id = 'post-uuid';

-- Reject
UPDATE publisher_posts SET
  status = 'archived',
  human_reviewed = true,
  reviewed_by = 'reviewer-uuid',
  reviewed_at = now()
WHERE id = 'post-uuid';

function validateSEO(post) {
  const issues = [];
  
  // Title tag length
  if (post.meta_title.length > 60) {
    issues.push('Meta title exceeds 60 characters');
  }
  
  // Meta description length
  if (post.meta_description.length > 155) {
    issues.push('Meta description exceeds 155 characters');
  }
  
  // Keyword in title
  if (!post.title.toLowerCase().includes(post.target_keyword)) {
    issues.push('Target keyword not in title');
  }
  
  // Keyword in first paragraph
  const firstPara = post.content.split('\n\n')[0];
  if (!firstPara.toLowerCase().includes(post.target_keyword)) {
    issues.push('Target keyword not in first paragraph');
  }
  
  // Internal links
  const internalLinks = post.content.match(/\[.*?\]\(\/.*?\)/g) || [];
  if (internalLinks.length < 3) {
    issues.push('Fewer than 3 internal links');
  }
  
  return { valid: issues.length === 0, issues };
}

// Auto-inject affiliate links
function injectAffiliateLinks(content, products) {
  let enhanced = content;
  
  for (const product of products) {
    // Find natural insertion points
    const pattern = new RegExp(`(${product.keywords.join('|')})`, 'gi');
    enhanced = enhanced.replace(pattern, (match) => {
      return `[${match}](${product.affiliate_url})`;
    });
  }
  
  return enhanced;
}

// Auto-inject internal links
function injectInternalLinks(content, relatedPosts) {
  // Similar logic for internal linking
}

-- Find best publish times based on historical performance
SELECT 
  EXTRACT(DOW FROM published_at) as day_of_week,
  EXTRACT(HOUR FROM published_at) as hour,
  AVG(ppa.views) as avg_views,
  AVG(ppa.engagement_score) as avg_engagement
FROM publisher_posts pp
JOIN publisher_post_analytics ppa ON pp.id = ppa.post_id
WHERE pp.published_at > now() - interval '90 days'
GROUP BY 1, 2
ORDER BY avg_views DESC
LIMIT 10;

async function schedulePost(postId, preferredTime = null) {
  if (preferredTime) {
    return updatePost(postId, { scheduled_at: preferredTime });
  }
  
  // Find next optimal slot
  const optimalSlots = await getOptimalPublishTimes();
  const scheduledPosts = await getScheduledPosts();
  
  // Find first available optimal slot
  for (const slot of optimalSlots) {
    const slotTime = getNextOccurrence(slot.day, slot.hour);
    const conflict = scheduledPosts.find(
      p => Math.abs(p.scheduled_at - slotTime) < 3600000 // 1 hour buffer
    );
    
    if (!conflict) {
      return updatePost(postId, { scheduled_at: slotTime });
    }
  }
}

// Runs every minute
async function publishScheduledPosts() {
  const due = await supabase
    .from('publisher_posts')
    .select('*')
    .eq('status', 'scheduled')
    .lte('scheduled_at', new Date().toISOString());
  
  for (const post of due.data) {
    try {
      // 1. Generate static page (if using SSG)
      await regenerateStaticPage(post);
      
      // 2. Update sitemap
      await updateSitemap(post.vertical_id);
      
      // 3. Notify search engines
      await pingSearchEngines(post.url);
      
      // 4. Update status
      await supabase
        .from('publisher_posts')
        .update({ 
          status: 'published',
          published_at: new Date().toISOString()
        })
        .eq('id', post.id);
      
      // 5. Trigger syndication
      await triggerSyndication(post.id);
      
    } catch (error) {
      console.error(`Failed to publish ${post.id}:`, error);
    }
  }
}

// Daily analytics sync
async function syncPostAnalytics() {
  const posts = await getPublishedPosts();
  
  for (const post of posts) {
    const analytics = await getAnalyticsForUrl(post.url);
    
    await supabase
      .from('publisher_post_analytics')
      .upsert({
        post_id: post.id,
        views: analytics.pageviews,
        unique_visitors: analytics.uniqueVisitors,
        avg_time_on_page: analytics.avgTimeOnPage,
        bounce_rate: analytics.bounceRate,
        scroll_depth_avg: analytics.scrollDepth,
        updated_at: new Date()
      });
  }
}

-- Underperforming posts (for optimization)
SELECT pp.title, ppa.views, ppa.bounce_rate
FROM publisher_posts pp
JOIN publisher_post_analytics ppa ON pp.id = ppa.post_id
WHERE pp.published_at > now() - interval '14 days'
  AND ppa.views < 100
  AND ppa.bounce_rate > 70;

-- Upcoming content calendar
SELECT 
  pp.title,
  pv.name as vertical,
  pp.status,
  pp.scheduled_at,
  pp.published_at,
  u.full_name as author
FROM publisher_posts pp
JOIN publisher_verticals pv ON pp.vertical_id = pv.id
LEFT JOIN users u ON pp.author_id = u.id
WHERE pp.status IN ('draft', 'review', 'scheduled')
   OR (pp.status = 'published' AND pp.published_at > now() - interval '7 days')
ORDER BY 
  CASE pp.status 
    WHEN 'scheduled' THEN pp.scheduled_at
    WHEN 'published' THEN pp.published_at
    ELSE pp.created_at
  END;