How to Integrate Lovable with Screaming Frog SEO Spider

1import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
2import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
3
4const corsHeaders = {
5  'Access-Control-Allow-Origin': '*',
6  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
7}
8
9function parseCsv(text: string): Record<string, string>[] {
10  const lines = text.replace(/\r/g, '').split('\n').filter(l => l.trim())
11  if (lines.length < 2) return []
12  const headers = lines[0].split(',').map(h => h.trim().replace(/^"|"$/g, ''))
13  return lines.slice(1).map(line => {
14    const values = line.split(',').map(v => v.trim().replace(/^"|"$/g, ''))
15    return Object.fromEntries(headers.map((h, i) => [h, values[i] ?? '']))
16  })
17}
18
19serve(async (req) => {
20  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })
21
22  try {
23    const supabase = createClient(
24      Deno.env.get('SUPABASE_URL')!,
25      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!,
26    )
27
28    const formData = await req.formData()
29    const file = formData.get('file') as File
30    const siteDomain = formData.get('site_domain') as string || 'unknown'
31
32    if (!file) return new Response(JSON.stringify({ error: 'No file uploaded' }),
33      { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } })
34
35    const text = await file.text()
36    const rows = parseCsv(text)
37
38    // Create crawl session
39    const { data: session, error: sessionError } = await supabase
40      .from('crawl_sessions')
41      .insert({ site_domain: siteDomain, crawled_at: new Date().toISOString(), total_pages: rows.length })
42      .select().single()
43
44    if (sessionError) throw new Error(sessionError.message)
45
46    // Categorize issues
47    const seenTitles = new Map<string, number>()
48    rows.forEach(r => {
49      const t = r['Title 1'] || r['title_1'] || ''
50      if (t) seenTitles.set(t, (seenTitles.get(t) || 0) + 1)
51    })
52
53    const issues = rows.flatMap(row => {
54      const url = row['Address'] || row['address'] || row['URL'] || ''
55      const title = row['Title 1'] || row['title_1'] || ''
56      const metaDesc = row['Meta Description 1'] || row['meta_description_1'] || ''
57      const h1 = row['H1-1'] || row['h1_1'] || ''
58      const statusCode = parseInt(row['Status Code'] || row['status_code'] || '200')
59      const found: any[] = []
60      const base = { crawl_id: session.id, url, page_title: title, meta_description: metaDesc, h1 }
61
62      if (statusCode >= 400) found.push({ ...base, issue_type: 'broken_link', status_code: statusCode })
63      if (!title && statusCode < 400) found.push({ ...base, issue_type: 'missing_title', status_code: statusCode })
64      if (!metaDesc && statusCode < 400) found.push({ ...base, issue_type: 'missing_meta', status_code: statusCode })
65      if (!h1 && statusCode < 400) found.push({ ...base, issue_type: 'missing_h1', status_code: statusCode })
66      if (title && (seenTitles.get(title) || 0) > 1 && statusCode < 400) found.push({ ...base, issue_type: 'duplicate_title', status_code: statusCode })
67      return found
68    })
69
70    if (issues.length > 0) await supabase.from('seo_issues').insert(issues)
71
72    // Update session with issue count
73    await supabase.from('crawl_sessions').update({
74      issue_count: issues.length,
75      health_score: Math.round(((rows.length - issues.filter((i,idx,arr) => arr.findIndex(a => a.url === i.url) === idx).length) / Math.max(rows.length, 1)) * 100)
76    }).eq('id', session.id)
77
78    const summary = issues.reduce((acc, i) => { acc[i.issue_type] = (acc[i.issue_type] || 0) + 1; return acc }, {} as Record<string, number>)
79
80    return new Response(JSON.stringify({ success: true, crawl_id: session.id, total_issues: issues.length, summary }),
81      { headers: { ...corsHeaders, 'Content-Type': 'application/json' } })
82  } catch (error) {
83    return new Response(JSON.stringify({ error: error.message }),
84      { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } })
85  }
86})