Ranking Alert

Keyword: ${keyword}

Project: ${projectName} (${domain})

Alert: ${alertMessage}

Time: ${new Date().toISOString()}

\n \n

This alert was triggered by your RankWatch monitoring settings.

\n `,\n });\n \n console.log(`Alert sent for keyword ${keywordId}: ${alertMessage}`);\n } catch (error) {\n console.error(`Failed to send alert for keyword ${keywordId}:`, error);\n }\n }\n }\n }\n}\n\n// Send daily summary email for unlimited users\nasync function sendDailySummary(userEmail: string, summary: any): Promise {\n const subject = `RankWatch Daily Summary - ${summary.total_keywords} Keywords Checked`;\n \n const htmlContent = `\n

Daily Ranking Summary

Hello,

Here's your daily ranking summary:

\n \n

Overview

Keywords Checked: ${summary.total_keywords}
Currently Ranking: ${summary.ranking_keywords}
Top 10 Positions: ${summary.top_10}
Alerts Triggered: ${summary.alerts_sent}

\n \n ${summary.top_movers.length > 0 ? `\n

🔥 Top Movers

${item.keyword}: ${item.change > 0 ? `↗️ +${item.change}` : `↘️ ${item.change}`} to #${item.position}

\n ` : ''}\n \n

Best regards,
The RankWatch Team

\n \n

This is your daily summary for unlimited plan. To manage your alerts, log in to your RankWatch account.

\n `;\n\n await email({\n to: userEmail,\n subject: subject,\n html: htmlContent,\n });\n}\n\n// Main cron function - runs every day at 6 AM UTC\nexport default async function cronDaily() {\n console.log(\"🕒 Starting daily ranking check for unlimited accounts...\");\n \n try {\n // Initialize database\n await initializeDatabase();\n const db = getDatabase();\n \n // Get all unlimited users with active keywords\n const unlimitedUsers = await db.execute({\n sql: `SELECT DISTINCT u.id, u.email \n FROM users u \n JOIN projects p ON u.id = p.user_id \n JOIN keywords k ON p.id = k.project_id \n WHERE u.plan = 'unlimited' \n AND u.deleted_at IS NULL \n AND p.deleted_at IS NULL \n AND k.deleted_at IS NULL \n AND k.active = 1`,\n args: [],\n });\n\n console.log(`Found ${unlimitedUsers.rows.length} unlimited users with active keywords`);\n\n for (const userRow of unlimitedUsers.rows) {\n const userId = userRow[0] as number;\n const userEmail = userRow[1] as string;\n \n try {\n console.log(`Processing user ${userId} (${userEmail})`);\n \n // Get all active keywords for this user\n const userKeywords = await db.execute({\n sql: `SELECT k.id, k.keyword, p.domain, k.target_url, p.location, p.device\n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? \n AND k.deleted_at IS NULL \n AND p.deleted_at IS NULL \n AND k.active = 1`,\n args: [userId],\n });\n\n if (userKeywords.rows.length === 0) {\n console.log(`No active keywords found for user ${userId}`);\n continue;\n }\n\n console.log(`Checking ${userKeywords.rows.length} keywords for user ${userId}`);\n\n // Prepare keywords for batch checking\n const keywordsToCheck = userKeywords.rows.map(row => ({\n keywordId: row[0] as number,\n keyword: row[1] as string,\n domain: row[2] as string,\n target_url: row[3] as string | null,\n location: row[4] as string,\n device: row[5] as string,\n }));\n\n // Check rankings in smaller batches for unlimited users (more frequent updates)\n const batchSize = 10;\n const summaryData = {\n total_keywords: keywordsToCheck.length,\n ranking_keywords: 0,\n top_10: 0,\n alerts_sent: 0,\n top_movers: [] as any[],\n };\n\n for (let i = 0; i < keywordsToCheck.length; i += batchSize) {\n const batch = keywordsToCheck.slice(i, i + batchSize);\n \n console.log(`Processing batch ${Math.floor(i / batchSize) + 1}/${Math.ceil(keywordsToCheck.length / batchSize)} for user ${userId}`);\n \n // Check rankings for this batch\n const batchResults = await batchCheckKeywords(\n batch.map(k => ({\n keyword: k.keyword,\n domain: k.domain,\n target_url: k.target_url || undefined,\n location: k.location,\n device: k.device,\n })),\n 1500 // 1.5 second delay between requests (faster for unlimited)\n );\n\n // Process results\n for (let j = 0; j < batchResults.length; j++) {\n const result = batchResults[j];\n const keywordData = batch[j];\n \n try {\n // Get previous position for comparison\n const previousRanking = await db.execute({\n sql: `SELECT position FROM rankings \n WHERE keyword_id = ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordData.keywordId],\n });\n\n const previousPosition = previousRanking.rows.length > 0 \n ? previousRanking.rows[0][0] as number | null \n : null;\n\n // Save ranking result\n await saveRankingResult(keywordData.keywordId, result);\n \n // Process alerts\n await processAlerts(keywordData.keywordId, result.position, previousPosition);\n \n // Update summary statistics\n if (result.found && result.position) {\n summaryData.ranking_keywords++;\n \n if (result.position <= 10) {\n summaryData.top_10++;\n }\n\n // Track significant movements for top movers\n if (previousPosition && Math.abs(previousPosition - result.position) >= 5) {\n summaryData.top_movers.push({\n keyword: result.keyword,\n position: result.position,\n change: previousPosition - result.position,\n });\n }\n }\n\n console.log(`✓ Processed keyword \"${result.keyword}\" for user ${userId}: ${result.found ? `#${result.position}` : 'Not ranking'}`);\n \n } catch (error) {\n console.error(`Error saving ranking for keyword ${keywordData.keywordId}:`, error);\n }\n }\n\n // Add shorter delay between batches for unlimited users\n if (i + batchSize < keywordsToCheck.length) {\n await new Promise(resolve => setTimeout(resolve, 3000)); // 3 second delay between batches\n }\n }\n\n // Sort top movers by absolute change\n summaryData.top_movers.sort((a, b) => Math.abs(b.change) - Math.abs(a.change));\n summaryData.top_movers = summaryData.top_movers.slice(0, 10); // Top 10 movers\n\n // Send daily summary email\n await sendDailySummary(userEmail, summaryData);\n console.log(`✓ Daily summary sent to ${userEmail}`);\n\n } catch (error) {\n console.error(`Error processing user ${userId}:`, error);\n \n // Send error notification email\n try {\n await email({\n to: userEmail,\n subject: \"RankWatch Daily Check - Issue Occurred\",\n html: `\n

Hello,

We encountered an issue while checking your keyword rankings today. Our team has been notified and will resolve this shortly.

Best regards,
The RankWatch Team

\n `,\n });\n } catch (emailError) {\n console.error(`Failed to send error notification to ${userEmail}:`, emailError);\n }\n }\n }\n\n console.log(\"✅ Daily ranking check completed for all unlimited accounts\");\n\n } catch (error) {\n console.error(\"❌ Daily cron job failed:\", error);\n \n // Send admin notification about cron failure\n try {\n await email({\n to: \"admin@rankwatch.dev\", // Replace with actual admin email\n subject: \"RankWatch Daily Cron Job Failed\",\n html: `\n

Daily Cron Job Failure

Time: ${new Date().toISOString()}

Error: ${error.message}

${error.stack}

\n `,\n });\n } catch (emailError) {\n console.error(\"Failed to send admin notification:\", emailError);\n }\n }\n}","deleted","label",[98],"permalink","https://www.val.town/x/diegoivo/denoCrudTask/code/cronDaily.ts","extension","actionData","errors"]