// First touch: the first piece of content a user encountered
export const getFirstTouchAttribution = createServerFn({ method: "GET" }).handler(
 async ({ data, context }: { data: { period: string } }) => {
 const result = await context.env.DB.prepare(`
 SELECT
 first_utm_source as source,
 first_utm_campaign as campaign,
 first_utm_content as content,
 COUNT(*) as signups,
 SUM(CASE WHEN subscription_status = 'active' THEN 1 ELSE 0 END) as conversions
 FROM users
 WHERE created_at > datetime('now', ?)
 GROUP BY first_utm_source, first_utm_campaign
 ORDER BY signups DESC
 `).bind(data.period).all()

 return result.results
 }
)

// Last touch: the content that drove the final conversion
export const getLastTouchAttribution = createServerFn({ method: "GET" }).handler(
 async ({ data, context }: { data: { period: string } }) => {
 const result = await context.env.DB.prepare(`
 SELECT
 source,
 campaign,
 COUNT(*) as conversions
 FROM utm_clicks
 WHERE user_id IN (
 SELECT id FROM users
 WHERE created_at > datetime('now', ?)
 AND subscription_status = 'active'
 )
 ORDER BY created_at DESC
 LIMIT 1
 `).bind(data.period).all()

 return result.results
 }
)

// Track content production costs
export const trackContentCost = createServerFn({ method: "POST" }).handler(
 async ({ data }: { data: {
 contentId: string
 hoursSpent: number
 hourlyRate: number
 distributionCost: number
 }}) => {
 const totalCost = data.hoursSpent * data.hourlyRate + data.distributionCost

 await contentDB.prepare(`
 INSERT INTO content_costs
 (id, content_id, hours_spent, hourly_rate,
 distribution_cost, total_cost, created_at)
 VALUES (?, ?, ?, ?, ?, ?, ?)
 `).bind(
 crypto.randomUUID(),
 data.contentId,
 data.hoursSpent,
 data.hourlyRate,
 data.distributionCost,
 totalCost,
 Date.now()
 ).run()

 return { totalCost }
 }
)

export const getContentCac = createServerFn({ method: "GET" }).handler(
 async ({}, { context }) => {
 const results = await context.env.DB.prepare(`
 SELECT
 c.content_type,
 SUM(cc.total_cost) as total_cost,
 COUNT(DISTINCT u.id) as attributed_signups,
 SUM(cc.total_cost) / COUNT(DISTINCT u.id) as cac
 FROM content_costs cc
 JOIN content c ON c.id = cc.content_id
 LEFT JOIN users u ON u.first_utm_content = c.slug
 GROUP BY c.content_type
 ORDER BY cac ASC
 `).all()

 return results.results
 }
)

export const getLtvByContentSource = createServerFn({ method: "GET" }).handler(
 async ({}, { context }) => {
 const results = await context.env.DB.prepare(`
 SELECT
 first_utm_source as source,
 COUNT(*) as users,
 AVG(CASE WHEN status = 'active' THEN mrr ELSE 0 END) as avg_mrr,
 SUM(CASE WHEN status = 'active' THEN mrr ELSE 0 END) as total_mrr
 FROM users u
 LEFT JOIN subscriptions s ON s.user_id = u.id
 WHERE u.created_at > datetime('now', '-180 days')
 GROUP BY first_utm_source
 HAVING users > 5
 ORDER BY total_mrr DESC
 `).all()

 return results.results
 }
)

export const getContentROIDashboard = createServerFn({ method: "GET" }).handler(
 async ({}, { context }) => {
 const [performance, cac, ltv] = await Promise.all([
 // Content performance overview
 context.env.DB.prepare(`
 SELECT
 c.title,
 c.slug,
 c.published_at,
 c.views,
 c.avg_read_time,
 at.conversions
 FROM content c
 LEFT JOIN attribution at ON at.content_slug = c.slug
 ORDER BY at.conversions DESC
 LIMIT 20
 `).all(),

 // CAC by content type
 getContentCac(),

 // LTV analysis
 getLtvByContentSource(),
 ])

 return { performance, cac, ltv }
 }
)

export const createContentPlan = createServerFn({ method: "POST" }).handler(
 async ({ data, context }: { data: {
 title: string
 type: string
 targetKeyword: string
 estimatedCost: number
 expectedSignups: number
 }}) => {
 const roiProjection = {
 cac: data.estimatedCost / data.expectedSignups,
 projectedMonthlyRevenue: data.expectedSignups * 29, // $29 avg MRR
 breakEvenMonths: data.estimatedCost / (data.expectedSignups * 29),
 sixMonthReturn: (data.expectedSignups * 29 * 6) / data.estimatedCost,
 }

 // Store in database for tracking
 await context.env.DB.prepare(`
 INSERT INTO content_plan
 (title, type, target_keyword, estimated_cost,
 expected_signups, projected_cac, projected_roi, created_at)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
 `).bind(
 data.title, data.type, data.targetKeyword,
 data.estimatedCost, data.expectedSignups,
 roiProjection.cac, roiProjection.sixMonthReturn,
 Date.now()
 ).run()

 return roiProjection
 }
)