Hi

\"\n});\n```\n\n## Val Town Utility Functions\n\nVal Town provides several utility functions to help with common project tasks.\n\n### Importing Utilities\n\nAlways import utilities with version pins to avoid breaking changes:\n\n```ts\nimport { parseProject, readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\n```\n\n### Available Utilities\n\n\n#### **serveFile** - Serve project files with proper content types\n\nFor example, in Hono:\n\n```ts\n// serve all files in frontend/ and shared/\napp.get(\"/frontend/*\", c => serveFile(c.req.path, import.meta.url));\napp.get(\"/shared/*\", c => serveFile(c.req.path, import.meta.url));\n```\n\n#### **readFile** - Read files from within the project:\n\n```ts\n// Read a file from the project\nconst fileContent = await readFile(\"/frontend/index.html\", import.meta.url);\n```\n\n#### **listFiles** - List all files in the project\n\n```ts\nconst files = await listFiles(import.meta.url);\n```\n\n#### **parseProject** - Extract information about the current project from import.meta.url\n\nThis is useful for including info for linking back to a val, ie in \"view source\" urls:\n\n```ts\nconst projectVal = parseProject(import.meta.url);\nconsole.log(projectVal.username); // Owner of the project\nconsole.log(projectVal.name); // Project name\nconsole.log(projectVal.version); // Version number\nconsole.log(projectVal.branch); // Branch name\nconsole.log(projectVal.links.self.project); // URL to the project page\n```\n\nHowever, it's *extremely importing* to note that `parseProject` and other Standard Library utilities ONLY RUN ON THE SERVER.\nIf you need access to this data on the client, run it in the server and pass it to the client by splicing it into the HTML page\nor by making an API request for it.\n\n## Val Town Platform Specifics\n\n- **Redirects:** Use `return new Response(null, { status: 302, headers: { Location: \"/place/to/redirect\" }})` instead of `Response.redirect` which is broken\n- **Images:** Avoid external images or base64 images. Use emojis, unicode symbols, or icon fonts/libraries instead\n- **AI Image:** To inline generate an AI image use: `

`\n- **Storage:** DO NOT use the Deno KV module for storage\n- **Browser APIs:** DO NOT use the `alert()`, `prompt()`, or `confirm()` methods\n- **Weather Data:** Use open-meteo for weather data (doesn't require API keys) unless otherwise specified\n- **View Source:** Add a view source link by importing & using `import.meta.url.replace(\"ems.sh\", \"val.town)\"` (or passing this data to the client) and include `target=\"_top\"` attribute\n- **Error Debugging:** Add `` to HTML to capture client-side errors\n- **Error Handling:** Only use try...catch when there's a clear local resolution; Avoid catches that merely log or return 500s. Let errors bubble up with full context\n- **Environment Variables:** Use `Deno.env.get('keyname')` when you need to, but generally prefer APIs that don't require keys\n- **Imports:** Use `https://esm.sh` for npm and Deno dependencies to ensure compatibility on server and browser\n- **Storage Strategy:** Only use backend storage if explicitly required; prefer simple static client-side sites\n- **React Configuration:** When using React libraries, pin versions with `?deps=react@18.2.0,react-dom@18.2.0` and start the file with `/** @jsxImportSource https://esm.sh/react@18.2.0 */`\n- Ensure all React dependencies and sub-dependencies are pinned to the same version\n- **Styling:** Default to using TailwindCSS via `` unless otherwise specified\n\n## Project Structure and Design Patterns\n\n### Recommended Directory Structure\n```\n├── backend/\n│ ├── database/\n│ │ ├── migrations.ts # Schema definitions\n│ │ ├── queries.ts # DB query functions\n│ │ └── README.md\n│ └── routes/ # Route modules\n│ ├── [route].ts\n│ └── static.ts # Static file serving\n│ ├── index.ts # Main entry point\n│ └── README.md\n├── frontend/\n│ ├── components/\n│ │ ├── App.tsx\n│ │ └── [Component].tsx\n│ ├── favicon.svg\n│ ├── index.html # Main HTML template\n│ ├── index.tsx # Frontend JS entry point\n│ ├── README.md\n│ └── style.css\n├── README.md\n└── shared/\n ├── README.md\n └── utils.ts # Shared types and functions\n```\n\n### Backend (Hono) Best Practices\n\n- Hono is the recommended API framework\n- Main entry point should be `backend/index.ts`\n- **Static asset serving:** Use the utility functions to read and serve project files:\n ```ts\n import { readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\n \n // serve all files in frontend/ and shared/\n app.get(\"/frontend/*\", c => serveFile(c.req.path, import.meta.url));\n app.get(\"/shared/*\", c => serveFile(c.req.path, import.meta.url));\n \n // For index.html, often you'll want to bootstrap with initial data\n app.get(\"/\", async c => {\n let html = await readFile(\"/frontend/index.html\", import.meta.url);\n \n // Inject data to avoid extra round-trips\n const initialData = await fetchInitialData();\n const dataScript = ``;\n \n html = html.replace(\"\", `${dataScript}`);\n return c.html(html);\n });\n ```\n- Create RESTful API routes for CRUD operations\n- Always include this snippet at the top-level Hono app to re-throwing errors to see full stack traces:\n ```ts\n // Unwrap Hono errors to see original error details\n app.onError((err, c) => {\n throw err;\n });\n ```\n\n### Database Patterns\n- Run migrations on startup or comment out for performance\n- Change table names when modifying schemas rather than altering\n- Export clear query functions with proper TypeScript typing\n\n## Common Gotchas and Solutions\n\n1. **Environment Limitations:** \n - Val Town runs on Deno in a serverless context, not Node.js\n - Code in `shared/` must work in both frontend and backend environments\n - Cannot use `Deno` keyword in shared code\n - Use `https://esm.sh` for imports that work in both environments\n\n2. **SQLite Peculiarities:**\n - Limited support for ALTER TABLE operations\n - Create new tables with updated schemas and copy data when needed\n - Always run table creation before querying\n\n3. **React Configuration:**\n - All React dependencies must be pinned to 18.2.0\n - Always include `@jsxImportSource https://esm.sh/react@18.2.0` at the top of React files\n - Rendering issues often come from mismatched React versions\n\n4. **File Handling:**\n - Val Town only supports text files, not binary\n - Use the provided utilities to read files across branches and forks\n - For files in the project, use `readFile` helpers\n\n5. **API Design:**\n - `fetch` handler is the entry point for HTTP vals\n - Run the Hono app with `export default app.fetch // This is the entry point for HTTP vals`\n\n",[],[173],{"_104":181,"_95":182,"_153":183,"_155":-5,"_156":138,"_157":184,"_159":160,"_106":185,"_100":32,"_162":163,"_164":186,"_166":22,"_167":-5,"_168":187,"_98":188},"42693876-8e98-11f0-9ab0-0224a6c84d84","README.md","42693934-8e98-11f0-9ab0-0224a6c84d84",20,["D",1757544504879],"# RankWatch API\n\nA powerful keyword ranking monitoring API built with Val Town. Track your SEO rankings with precision and reliability.\n\n## 🚀 Features\n\n### Core Functionality\n- **Keyword Ranking Monitoring**: Track Google rankings for unlimited keywords\n- **Project Management**: Organize keywords by website/domain\n- **Historical Data**: Complete ranking history with change tracking\n- **Real-time Checks**: Force immediate ranking checks (unlimited plan)\n- **Competitor Analysis**: See who ranks for your keywords\n\n### Advanced Features\n- **JWT Authentication**: Secure token-based authentication\n- **Rate Limiting**: SQLite-based rate limiting per user/endpoint\n- **Soft Deletes**: All deletes are reversible\n- **Global Search**: Search across projects, keywords, and rankings\n- **CSV Export**: Export all data types to CSV format\n- **Bulk Operations**: Bulk import/update/delete keywords\n- **Email Alerts**: Automated ranking change notifications\n\n### Plans\n- **Freemium**: 10 keywords, 1 project, weekly updates\n- **Unlimited**: Unlimited keywords/projects, daily updates\n\n## 📚 API Documentation\n\n### Swagger UI\nAccess interactive documentation at: `GET /docs`\n\n### OpenAPI Specification \nGet the OpenAPI spec at: `GET /docs/openapi.json`\n\n### Alternative Documentation\nReDoc interface at: `GET /docs/redoc`\n\n## 🏗️ Architecture\n\n### Tech Stack\n- **Runtime**: Deno on Val Town\n- **Framework**: Hono for HTTP routing\n- **Database**: SQLite (Turso-powered)\n- **External API**: Serper.dev for Google search data\n- **Email**: Val Town std/email (Resend integration ready)\n\n### Key Components\n- `main.ts` - Main application entry point\n- `authApi.ts` - Authentication endpoints\n- `projectsApi.ts` - Project management\n- `keywordsApi.ts` - Keyword management with bulk operations\n- `rankingsApi.ts` - Ranking data and analysis\n- `searchApi.ts` - Global search functionality\n- `exportApi.ts` - CSV export endpoints\n- `swaggerApi.ts` - API documentation\n- `serperClient.ts` - Serper.dev integration\n- `cronDaily.ts` - Daily ranking checks (unlimited users)\n- `cronWeekly.ts` - Weekly ranking checks (freemium users)\n\n## ⚙️ Configuration\n\n### Required Environment Variables\n\n```bash\n# JWT Secret (generate a secure random string)\nJWT_SECRET=your-secure-jwt-secret-key\n\n# Serper.dev API Key (get from https://serper.dev)\nSERPER_API_KEY=your-serper-api-key\n\n# Resend API Key (for email notifications)\nRESEND_API_KEY=your-resend-api-key\n\n# Optional: Custom base URL\nBASE_URL=https://your-custom-domain.com\n\n# Optional: Database URL (for Turso migration)\nDATABASE_URL=sqlite://local.db\n```\n\n### Val Town Setup\n\n1. **Create Vals**: Create separate Vals for each main component:\n - Main API: `main.ts`\n - Weekly Cron: `cronWeekly.ts` \n - Daily Cron: `cronDaily.ts`\n\n2. **Configure Environment**: Set environment variables in Val Town settings\n\n3. **Setup Cron Jobs**: \n - Weekly: Monday 9 AM UTC\n - Daily: Every day 6 AM UTC\n\n## 🔧 API Endpoints\n\n### Authentication\n```\nPOST /auth/register - Create account\nPOST /auth/login - Login user\nGET /auth/me - Get user info\nPOST /auth/refresh - Refresh token\nPOST /auth/logout - Logout\n```\n\n### Projects\n```\nGET /projects - List projects\nPOST /projects - Create project\nGET /projects/{id} - Get project\nPUT /projects/{id} - Update project\nDELETE /projects/{id} - Delete project (soft)\nGET /projects/search - Search projects\n```\n\n### Keywords\n```\nGET /projects/{id}/keywords - List keywords\nPOST /projects/{id}/keywords - Add keyword\nPUT /keywords/{id} - Update keyword\nDELETE /keywords/{id} - Delete keyword (soft)\nPOST /keywords/bulk-create - Bulk add keywords\nPUT /keywords/bulk-update - Bulk update keywords\nDELETE /keywords/bulk-delete - Bulk delete keywords\nGET /keywords/search - Search keywords\n```\n\n### Rankings\n```\nGET /keywords/{id}/rankings - Current ranking\nGET /keywords/{id}/history - Ranking history\nGET /keywords/{id}/competitors - Competitor analysis\nPOST /keywords/{id}/check-now - Force check (unlimited)\nGET /projects/{id}/summary - Project summary\n```\n\n### Search\n```\nGET /search - Global search\nGET /search/suggestions - Search autocomplete\nGET /search/recent - Recent items\n```\n\n### Export\n```\nGET /projects/{id}/export - Export project CSV\nGET /keywords/{id}/export - Export keyword history CSV\nGET /reports/usage - Export usage report CSV\nGET /export/all-keywords - Export all keywords CSV\nGET /export/format-info - Export format info\n```\n\n## 📊 Database Schema\n\n### Core Tables\n- `users` - User accounts and plans\n- `projects` - Website/domain projects \n- `keywords` - Keywords to track\n- `rankings` - Ranking snapshots\n- `rank_history` - Historical ranking changes\n- `alerts` - Email alert configurations\n\n### Support Tables\n- `rate_limits` - API rate limiting\n- `usage_limits` - Plan usage tracking\n\n## 🔒 Authentication\n\n### JWT Authentication\nAll authenticated endpoints require:\n```\nAuthorization: Bearer \n```\n\n### Rate Limiting\n- **Freemium**: 100 requests/hour per endpoint\n- **Unlimited**: 1000 requests/hour per endpoint\n\nHeaders included in responses:\n- `X-RateLimit-Limit`: Request limit\n- `X-RateLimit-Remaining`: Remaining requests \n- `X-RateLimit-Reset`: Reset timestamp\n\n## 📧 Email Notifications\n\n### Alert Types\n- Position improvements/declines\n- Top 10 entry/exit\n- Weekly/daily summaries\n\n### Email Configuration\nConfigure email settings in `config.ts`:\n```typescript\nresend: {\n apiKey: Deno.env.get(\"RESEND_API_KEY\"),\n fromEmail: \"noreply@rankwatch.dev\",\n}\n```\n\n## 🚦 Getting Started\n\n1. **Clone the project** to your Val Town workspace\n2. **Set environment variables** in Val Town settings\n3. **Deploy main API** using `main.ts`\n4. **Setup cron jobs** for automated ranking checks\n5. **Test endpoints** using Swagger UI at `/docs`\n\n## 🔄 Migration to Turso\n\nWhen ready to migrate from Val Town SQLite to Turso:\n\n1. Update `DATABASE_URL` environment variable\n2. Modify connection in `database.ts`\n3. Run database migrations\n4. Update backup/restore procedures\n\n## 🧪 Testing\n\n### Manual Testing\nUse Swagger UI at `/docs` for interactive testing\n\n### Automated Testing\nThe API includes health checks:\n- `GET /health` - Service health\n- `GET /status` - API status and features\n- `GET /docs/health` - Documentation health\n\n## 📈 Monitoring\n\n### Health Endpoints\n- `GET /` - API welcome/info\n- `GET /health` - Database connectivity check\n- `GET /status` - Feature and limits overview\n\n### Error Handling\n- Standardized error responses\n- Rate limit headers\n- Comprehensive logging\n- Admin error notifications\n\n## 🤝 Contributing\n\n1. Follow existing code patterns\n2. Update documentation for new features\n3. Test all endpoints thoroughly\n4. Ensure rate limiting works correctly\n\n## 📄 License\n\nMIT License - Built with ❤️ using Val Town\n\n---\n\n**Need help?** Check the Swagger documentation at `/docs` or contact support.",[],[182],{"_104":190,"_95":191,"_153":192,"_155":-5,"_156":138,"_157":193,"_159":160,"_106":194,"_100":32,"_162":195,"_164":196,"_166":22,"_167":-5,"_168":197,"_98":198},"7c2de338-8e96-11f0-88eb-0224a6c84d84","auth.ts","7c2de414-8e96-11f0-88eb-0224a6c84d84",6,["D",1757543742803],"script","// auth.ts - JWT Authentication system for RankWatch\n\nimport { create, getNumericDate, verify } from \"https://deno.land/x/djwt@v3.0.1/mod.ts\";\nimport { encodeBase64 } from \"https://deno.land/std@0.208.0/encoding/base64.ts\";\nimport { config, type UserPlan } from \"./config.ts\";\nimport { getDatabase } from \"./database.ts\";\n\n// Hash password using Web Crypto API\nexport async function hashPassword(password: string): Promise {\n const encoder = new TextEncoder();\n const data = encoder.encode(password);\n const hashBuffer = await crypto.subtle.digest(\"SHA-256\", data);\n return encodeBase64(new Uint8Array(hashBuffer));\n}\n\n// Verify password\nexport async function verifyPassword(password: string, hash: string): Promise {\n const hashedPassword = await hashPassword(password);\n return hashedPassword === hash;\n}\n\n// Generate API key\nexport function generateApiKey(): string {\n const array = new Uint8Array(32);\n crypto.getRandomValues(array);\n return encodeBase64(array);\n}\n\n// JWT token creation\nexport async function createJWT(userId: number, email: string, plan: UserPlan): Promise {\n const payload = {\n user_id: userId,\n email,\n plan,\n exp: getNumericDate(new Date(Date.now() + 7 * 24 * 60 * 60 * 1000)), // 7 days\n iat: getNumericDate(new Date()),\n };\n\n const key = await crypto.subtle.importKey(\n \"raw\",\n new TextEncoder().encode(config.jwt.secret),\n { name: \"HMAC\", hash: \"SHA-256\" },\n false,\n [\"sign\", \"verify\"]\n );\n\n return await create({ alg: \"HS256\", typ: \"JWT\" }, payload, key);\n}\n\n// Verify JWT token\nexport async function verifyJWT(token: string): Promise {\n try {\n const key = await crypto.subtle.importKey(\n \"raw\",\n new TextEncoder().encode(config.jwt.secret),\n { name: \"HMAC\", hash: \"SHA-256\" },\n false,\n [\"sign\", \"verify\"]\n );\n\n const payload = await verify(token, key);\n return payload;\n } catch (error) {\n throw new Error(\"Invalid token\");\n }\n}\n\n// User interface\nexport interface User {\n id: number;\n email: string;\n plan: UserPlan;\n api_key?: string;\n created_at: string;\n}\n\n// Register new user\nexport async function registerUser(email: string, password: string, plan: UserPlan = \"freemium\"): Promise<{ user: User; token: string }> {\n const db = getDatabase();\n \n // Check if user already exists\n const existingUser = await db.execute({\n sql: \"SELECT id FROM users WHERE email = ? AND deleted_at IS NULL\",\n args: [email],\n });\n\n if (existingUser.rows.length > 0) {\n throw new Error(\"User already exists\");\n }\n\n // Hash password and generate API key\n const passwordHash = await hashPassword(password);\n const apiKey = generateApiKey();\n\n // Insert user\n const result = await db.execute({\n sql: `INSERT INTO users (email, password_hash, plan, api_key) \n VALUES (?, ?, ?, ?) RETURNING id, email, plan, api_key, created_at`,\n args: [email, passwordHash, plan, apiKey],\n });\n\n const userData = result.rows[0];\n const user: User = {\n id: userData[0] as number,\n email: userData[1] as string,\n plan: userData[2] as UserPlan,\n api_key: userData[3] as string,\n created_at: userData[4] as string,\n };\n\n // Create JWT token\n const token = await createJWT(user.id, user.email, user.plan);\n\n return { user, token };\n}\n\n// Login user\nexport async function loginUser(email: string, password: string): Promise<{ user: User; token: string }> {\n const db = getDatabase();\n \n // Get user\n const result = await db.execute({\n sql: \"SELECT id, email, password_hash, plan, api_key, created_at FROM users WHERE email = ? AND deleted_at IS NULL\",\n args: [email],\n });\n\n if (result.rows.length === 0) {\n throw new Error(\"User not found\");\n }\n\n const userData = result.rows[0];\n const passwordHash = userData[2] as string;\n\n // Verify password\n const isValidPassword = await verifyPassword(password, passwordHash);\n if (!isValidPassword) {\n throw new Error(\"Invalid password\");\n }\n\n const user: User = {\n id: userData[0] as number,\n email: userData[1] as string,\n plan: userData[3] as UserPlan,\n api_key: userData[4] as string,\n created_at: userData[5] as string,\n };\n\n // Create JWT token\n const token = await createJWT(user.id, user.email, user.plan);\n\n return { user, token };\n}\n\n// Get user by ID\nexport async function getUserById(userId: number): Promise {\n const db = getDatabase();\n \n const result = await db.execute({\n sql: \"SELECT id, email, plan, api_key, created_at FROM users WHERE id = ? AND deleted_at IS NULL\",\n args: [userId],\n });\n\n if (result.rows.length === 0) {\n return null;\n }\n\n const userData = result.rows[0];\n return {\n id: userData[0] as number,\n email: userData[1] as string,\n plan: userData[2] as UserPlan,\n api_key: userData[3] as string,\n created_at: userData[4] as string,\n };\n}\n\n// Middleware to extract user from Authorization header\nexport async function extractUserFromToken(authHeader: string): Promise {\n if (!authHeader || !authHeader.startsWith(\"Bearer \")) {\n throw new Error(\"Missing or invalid authorization header\");\n }\n\n const token = authHeader.substring(7); // Remove \"Bearer \" prefix\n const payload = await verifyJWT(token);\n \n const user = await getUserById(payload.user_id);\n if (!user) {\n throw new Error(\"User not found\");\n }\n\n return user;\n}",[],[191],{"_104":200,"_95":201,"_153":202,"_155":-5,"_156":138,"_157":203,"_159":160,"_106":204,"_100":32,"_162":195,"_164":205,"_166":22,"_167":-5,"_168":206,"_98":207},"b0df238a-8e96-11f0-bee6-0224a6c84d84","authApi.ts","b0df2420-8e96-11f0-bee6-0224a6c84d84",9,["D",1757543831197],"// authApi.ts - Authentication API endpoints for RankWatch\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\nimport { registerUser, loginUser, getUserById, extractUserFromToken } from \"./auth.ts\";\nimport { createSuccessResponse, createErrorResponse, parseJsonBody, validateRequiredFields, isValidEmail, isValidPassword, logRequest } from \"./utils.ts\";\nimport { rateLimitMiddleware, checkRateLimit, getRateLimitHeaders } from \"./rateLimit.ts\";\nimport { initializeDatabase } from \"./database.ts\";\n\nconst app = new Hono();\n\n// CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\"],\n}));\n\n// Initialize database on startup\nlet dbInitialized = false;\nasync function ensureDatabase() {\n if (!dbInitialized) {\n await initializeDatabase();\n dbInitialized = true;\n }\n}\n\n// POST /auth/register - Create new user account\napp.post(\"/auth/register\", async (c) => {\n await ensureDatabase();\n logRequest(c.req.raw);\n\n try {\n const body = await parseJsonBody(c.req.raw);\n \n // Validate required fields\n const validation = validateRequiredFields(body, [\"email\", \"password\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { email, password, plan = \"freemium\" } = body;\n\n // Validate email format\n if (!isValidEmail(email)) {\n return createErrorResponse(\"Invalid email format\", 400);\n }\n\n // Validate password strength\n const passwordValidation = isValidPassword(password);\n if (!passwordValidation.valid) {\n return createErrorResponse(passwordValidation.message!, 400);\n }\n\n // Validate plan\n if (plan !== \"freemium\" && plan !== \"unlimited\") {\n return createErrorResponse(\"Invalid plan. Must be 'freemium' or 'unlimited'\", 400);\n }\n\n // Register user\n const result = await registerUser(email, password, plan);\n \n return createSuccessResponse({\n user: {\n id: result.user.id,\n email: result.user.email,\n plan: result.user.plan,\n created_at: result.user.created_at,\n },\n token: result.token,\n }, \"User registered successfully\");\n\n } catch (error) {\n console.error(\"Registration error:\", error);\n return createErrorResponse(error.message || \"Registration failed\", 400);\n }\n});\n\n// POST /auth/login - Authenticate user\napp.post(\"/auth/login\", async (c) => {\n await ensureDatabase();\n logRequest(c.req.raw);\n\n try {\n const body = await parseJsonBody(c.req.raw);\n \n // Validate required fields\n const validation = validateRequiredFields(body, [\"email\", \"password\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { email, password } = body;\n\n // Validate email format\n if (!isValidEmail(email)) {\n return createErrorResponse(\"Invalid email format\", 400);\n }\n\n // Login user\n const result = await loginUser(email, password);\n \n return createSuccessResponse({\n user: {\n id: result.user.id,\n email: result.user.email,\n plan: result.user.plan,\n created_at: result.user.created_at,\n },\n token: result.token,\n }, \"Login successful\");\n\n } catch (error) {\n console.error(\"Login error:\", error);\n return createErrorResponse(error.message || \"Login failed\", 401);\n }\n});\n\n// GET /auth/me - Get current user info\napp.get(\"/auth/me\", async (c) => {\n await ensureDatabase();\n \n try {\n const authHeader = c.req.header(\"authorization\");\n if (!authHeader) {\n return createErrorResponse(\"Authorization header required\", 401);\n }\n\n const user = await extractUserFromToken(authHeader);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/auth/me\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n const response = createSuccessResponse({\n id: user.id,\n email: user.email,\n plan: user.plan,\n created_at: user.created_at,\n }, \"User info retrieved successfully\");\n\n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Get user error:\", error);\n return createErrorResponse(error.message || \"Failed to get user info\", 401);\n }\n});\n\n// POST /auth/refresh - Refresh JWT token\napp.post(\"/auth/refresh\", async (c) => {\n await ensureDatabase();\n \n try {\n const authHeader = c.req.header(\"authorization\");\n if (!authHeader) {\n return createErrorResponse(\"Authorization header required\", 401);\n }\n\n const user = await extractUserFromToken(authHeader);\n logRequest(c.req.raw, user.id);\n\n // Create new token\n const { createJWT } = await import(\"./auth.ts\");\n const newToken = await createJWT(user.id, user.email, user.plan);\n \n return createSuccessResponse({\n token: newToken,\n user: {\n id: user.id,\n email: user.email,\n plan: user.plan,\n created_at: user.created_at,\n },\n }, \"Token refreshed successfully\");\n\n } catch (error) {\n console.error(\"Token refresh error:\", error);\n return createErrorResponse(error.message || \"Failed to refresh token\", 401);\n }\n});\n\n// POST /auth/logout - Logout user (for completeness, stateless JWT)\napp.post(\"/auth/logout\", async (c) => {\n logRequest(c.req.raw);\n \n // Since we're using stateless JWT, logout is handled client-side\n // In a production app, you might want to maintain a blacklist of tokens\n return createSuccessResponse(null, \"Logout successful. Please remove the token from your client.\");\n});\n\n// Health check endpoint\napp.get(\"/auth/health\", async (c) => {\n await ensureDatabase();\n \n return createSuccessResponse({\n status: \"healthy\",\n timestamp: new Date().toISOString(),\n service: \"RankWatch Auth API\",\n });\n});\n\n// Export the Hono app\nexport default {\n fetch: app.fetch,\n};",[],[201],{"_104":209,"_95":210,"_153":211,"_155":-5,"_156":138,"_157":212,"_159":160,"_106":213,"_100":32,"_162":195,"_164":214,"_166":22,"_167":-5,"_168":215,"_98":216},"e9556bc6-8e95-11f0-95f6-0224a6c84d84","config.ts","e9556c84-8e95-11f0-95f6-0224a6c84d84",4,["D",1757543496435],"// config.ts - Configurações centralizadas do RankWatch API\n\nexport const config = {\n // Database\n database: {\n type: \"sqlite\" as const,\n // Preparado para migração futura para Turso\n url: Deno.env.get(\"DATABASE_URL\") || \":memory:\",\n },\n\n // JWT Configuration\n jwt: {\n secret: Deno.env.get(\"JWT_SECRET\") || \"rankwatch-secret-key-change-in-production\",\n expiresIn: \"7d\",\n algorithm: \"HS256\" as const,\n },\n\n // Rate Limiting\n rateLimit: {\n freemium: {\n requests: 100,\n windowMs: 60 * 60 * 1000, // 1 hour\n },\n unlimited: {\n requests: 1000,\n windowMs: 60 * 60 * 1000, // 1 hour\n },\n },\n\n // External APIs\n serper: {\n apiKey: Deno.env.get(\"SERPER_API_KEY\") || \"\",\n baseUrl: \"https://google.serper.dev\",\n timeout: 30000,\n },\n\n // Email (Resend)\n resend: {\n apiKey: Deno.env.get(\"RESEND_API_KEY\") || \"\",\n fromEmail: \"noreply@rankwatch.dev\",\n },\n\n // Plans\n plans: {\n freemium: {\n maxKeywords: 10,\n maxProjects: 1,\n updateFrequency: \"weekly\",\n historyDays: 30,\n },\n unlimited: {\n maxKeywords: -1, // unlimited\n maxProjects: -1, // unlimited\n updateFrequency: \"daily\",\n historyDays: -1, // unlimited\n },\n },\n\n // Server\n server: {\n port: parseInt(Deno.env.get(\"PORT\") || \"8000\"),\n baseUrl: Deno.env.get(\"BASE_URL\") || \"https://your-val.val.town\",\n },\n} as const;\n\nexport type Plan = keyof typeof config.plans;\nexport type UserPlan = \"freemium\" | \"unlimited\";",[],[210],{"_104":218,"_95":219,"_153":220,"_155":-5,"_156":138,"_157":221,"_159":160,"_106":222,"_100":32,"_162":223,"_164":224,"_166":22,"_167":-5,"_168":225,"_98":226},"259ac048-8e98-11f0-9bc4-0224a6c84d84","cronDaily.ts","259ac106-8e98-11f0-9bc4-0224a6c84d84",19,["D",1757544456546],"interval","// cronDaily.ts - Daily ranking checker for unlimited accounts\n\nimport { getDatabase, initializeDatabase } from \"./database.ts\";\nimport { batchCheckKeywords, type RankingResult } from \"./serperClient.ts\";\nimport { email } from \"https://esm.town/v/std/email?v=10\";\n\n// Save ranking result to database\nasync function saveRankingResult(keywordId: number, result: RankingResult): Promise {\n const db = getDatabase();\n \n // Insert ranking record\n await db.execute({\n sql: `INSERT INTO rankings (keyword_id, position, url, title, serp_features, checked_at) \n VALUES (?, ?, ?, ?, ?, ?)`,\n args: [\n keywordId,\n result.position,\n result.url,\n result.title,\n JSON.stringify(result.serp_features),\n result.checked_at,\n ],\n });\n\n // Get previous ranking for change calculation\n const previousRanking = await db.execute({\n sql: `SELECT position FROM rankings \n WHERE keyword_id = ? AND checked_at < ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordId, result.checked_at],\n });\n\n let changeValue = 0;\n if (previousRanking.rows.length > 0 && result.position) {\n const previousPosition = previousRanking.rows[0][0] as number | null;\n if (previousPosition) {\n changeValue = previousPosition - result.position; // Positive = moved up, Negative = moved down\n }\n }\n\n // Insert ranking history record\n if (result.position) {\n await db.execute({\n sql: `INSERT INTO rank_history (keyword_id, position, change_value, checked_at) \n VALUES (?, ?, ?, ?)`,\n args: [keywordId, result.position, changeValue, result.checked_at],\n });\n }\n}\n\n// Process alerts for keyword ranking changes\nasync function processAlerts(keywordId: number, currentPosition: number | null, previousPosition: number | null): Promise {\n if (!currentPosition && !previousPosition) return;\n \n const db = getDatabase();\n \n // Get active alerts for this keyword\n const alerts = await db.execute({\n sql: `SELECT id, trigger_type, threshold, email \n FROM alerts \n WHERE keyword_id = ? AND active = 1 AND deleted_at IS NULL`,\n args: [keywordId],\n });\n\n for (const alertRow of alerts.rows) {\n const alertId = alertRow[0] as number;\n const triggerType = alertRow[1] as string;\n const threshold = alertRow[2] as number;\n const alertEmail = alertRow[3] as string;\n \n let shouldTrigger = false;\n let alertMessage = \"\";\n\n switch (triggerType) {\n case \"position_up\":\n if (previousPosition && currentPosition && (previousPosition - currentPosition) >= threshold) {\n shouldTrigger = true;\n alertMessage = `Keyword moved up ${previousPosition - currentPosition} positions to #${currentPosition}`;\n }\n break;\n \n case \"position_down\":\n if (previousPosition && currentPosition && (currentPosition - previousPosition) >= threshold) {\n shouldTrigger = true;\n alertMessage = `Keyword moved down ${currentPosition - previousPosition} positions to #${currentPosition}`;\n }\n break;\n \n case \"top_10_entry\":\n if (currentPosition && currentPosition <= 10 && (!previousPosition || previousPosition > 10)) {\n shouldTrigger = true;\n alertMessage = `Keyword entered top 10 at position #${currentPosition}`;\n }\n break;\n \n case \"page_1_lost\":\n if (previousPosition && previousPosition <= 10 && (!currentPosition || currentPosition > 10)) {\n shouldTrigger = true;\n alertMessage = `Keyword lost top 10 position (was #${previousPosition}, now ${currentPosition ? `#${currentPosition}` : 'not ranking'})`;\n }\n break;\n }\n\n if (shouldTrigger) {\n // Get keyword and project details for the alert\n const keywordDetails = await db.execute({\n sql: `SELECT k.keyword, p.name as project_name, p.domain \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id = ?`,\n args: [keywordId],\n });\n\n if (keywordDetails.rows.length > 0) {\n const keyword = keywordDetails.rows[0][0] as string;\n const projectName = keywordDetails.rows[0][1] as string;\n const domain = keywordDetails.rows[0][2] as string;\n\n // Send alert email\n try {\n await email({\n to: alertEmail,\n subject: `RankWatch Alert: ${keyword} - ${alertMessage}`,\n html: `\n

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}",[],[219],{"_104":228,"_95":229,"_153":230,"_155":-5,"_156":138,"_157":231,"_159":160,"_106":232,"_100":32,"_162":223,"_164":233,"_166":22,"_167":-5,"_168":234,"_98":235},"09203006-8e98-11f0-aff2-0224a6c84d84","cronWeekly.ts","092030ec-8e98-11f0-aff2-0224a6c84d84",18,["D",1757544408767],"// cronWeekly.ts - Weekly ranking checker for freemium accounts\n\nimport { getDatabase, initializeDatabase } from \"./database.ts\";\nimport { batchCheckKeywords, type RankingResult } from \"./serperClient.ts\";\nimport { email } from \"https://esm.town/v/std/email?v=10\";\n\n// Save ranking result to database\nasync function saveRankingResult(keywordId: number, result: RankingResult): Promise {\n const db = getDatabase();\n \n // Insert ranking record\n await db.execute({\n sql: `INSERT INTO rankings (keyword_id, position, url, title, serp_features, checked_at) \n VALUES (?, ?, ?, ?, ?, ?)`,\n args: [\n keywordId,\n result.position,\n result.url,\n result.title,\n JSON.stringify(result.serp_features),\n result.checked_at,\n ],\n });\n\n // Get previous ranking for change calculation\n const previousRanking = await db.execute({\n sql: `SELECT position FROM rankings \n WHERE keyword_id = ? AND checked_at < ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordId, result.checked_at],\n });\n\n let changeValue = 0;\n if (previousRanking.rows.length > 0 && result.position) {\n const previousPosition = previousRanking.rows[0][0] as number | null;\n if (previousPosition) {\n changeValue = previousPosition - result.position; // Positive = moved up, Negative = moved down\n }\n }\n\n // Insert ranking history record\n if (result.position) {\n await db.execute({\n sql: `INSERT INTO rank_history (keyword_id, position, change_value, checked_at) \n VALUES (?, ?, ?, ?)`,\n args: [keywordId, result.position, changeValue, result.checked_at],\n });\n }\n}\n\n// Send weekly summary email\nasync function sendWeeklySummary(userEmail: string, summary: any): Promise {\n const subject = `RankWatch Weekly Summary - ${summary.total_keywords} Keywords Checked`;\n \n const htmlContent = `\n

Weekly Ranking Summary

Hello,

Here's your weekly ranking summary for your RankWatch account:

\n \n

Overview

Keywords Checked: ${summary.total_keywords}
Currently Ranking: ${summary.ranking_keywords}
Top 10 Positions: ${summary.top_10}
Top 20 Positions: ${summary.top_20}

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

🎉 Improvements

${item.keyword}: Moved up ${item.change} positions to #${item.position}

\n ` : ''}\n \n ${summary.declines.length > 0 ? `\n

📉 Declines

${item.keyword}: Moved down ${Math.abs(item.change)} positions to #${item.position}

\n ` : ''}\n \n

Best regards,
The RankWatch Team

\n \n

This is an automated weekly summary. 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 Monday at 9 AM UTC\nexport default async function cronWeekly() {\n console.log(\"🕒 Starting weekly ranking check for freemium accounts...\");\n \n try {\n // Initialize database\n await initializeDatabase();\n const db = getDatabase();\n \n // Get all freemium users with active keywords\n const freemiumUsers = 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 = 'freemium' \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 ${freemiumUsers.rows.length} freemium users with active keywords`);\n\n for (const userRow of freemiumUsers.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 batches to avoid overwhelming Serper API\n const batchSize = 5;\n const summaryData = {\n total_keywords: keywordsToCheck.length,\n ranking_keywords: 0,\n top_10: 0,\n top_20: 0,\n improvements: [] as any[],\n declines: [] 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 2000 // 2 second delay between requests\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 // Save ranking result\n await saveRankingResult(keywordData.keywordId, result);\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 if (result.position <= 20) {\n summaryData.top_20++;\n }\n }\n\n // Check for improvements/declines\n const historyResult = await db.execute({\n sql: `SELECT change_value \n FROM rank_history \n WHERE keyword_id = ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordData.keywordId],\n });\n\n if (historyResult.rows.length > 0) {\n const change = historyResult.rows[0][0] as number;\n if (change > 0) {\n summaryData.improvements.push({\n keyword: result.keyword,\n position: result.position,\n change: change,\n });\n } else if (change < 0) {\n summaryData.declines.push({\n keyword: result.keyword,\n position: result.position,\n change: change,\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 delay between batches\n if (i + batchSize < keywordsToCheck.length) {\n await new Promise(resolve => setTimeout(resolve, 5000)); // 5 second delay between batches\n }\n }\n\n // Send weekly summary email\n await sendWeeklySummary(userEmail, summaryData);\n console.log(`✓ Weekly 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 Weekly Check - Issue Occurred\",\n html: `\n

Hello,

We encountered an issue while checking your keyword rankings this week. 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(\"✅ Weekly ranking check completed for all freemium accounts\");\n\n } catch (error) {\n console.error(\"❌ Weekly 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 Weekly Cron Job Failed\",\n html: `\n

Weekly 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}",[],[229],{"_104":237,"_95":238,"_153":239,"_155":-5,"_156":138,"_157":240,"_159":160,"_106":241,"_100":32,"_162":195,"_164":242,"_166":22,"_167":-5,"_168":243,"_98":244},"70086a38-8e96-11f0-b661-0224a6c84d84","database.ts","70086af6-8e96-11f0-b661-0224a6c84d84",5,["D",1757543722416],"// database.ts - Database setup and schema for RankWatch\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite?v=4\";\n\n// Database connection singleton\nlet db: any = null;\n\nexport function getDatabase() {\n if (!db) {\n db = sqlite(\"rankwatch\");\n }\n return db;\n}\n\n// Initialize database schema\nexport async function initializeDatabase() {\n const database = getDatabase();\n\n // Users table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS users (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n email TEXT UNIQUE NOT NULL,\n password_hash TEXT NOT NULL,\n plan TEXT NOT NULL DEFAULT 'freemium' CHECK (plan IN ('freemium', 'unlimited')),\n api_key TEXT UNIQUE,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n deleted_at DATETIME NULL\n )\n `);\n\n // Rate limiting table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS rate_limits (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_id INTEGER NOT NULL,\n endpoint TEXT NOT NULL,\n requests_made INTEGER NOT NULL DEFAULT 0,\n window_start DATETIME NOT NULL,\n window_end DATETIME NOT NULL,\n FOREIGN KEY (user_id) REFERENCES users (id),\n UNIQUE(user_id, endpoint, window_start)\n )\n `);\n\n // Projects table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS projects (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_id INTEGER NOT NULL,\n name TEXT NOT NULL,\n domain TEXT NOT NULL,\n location TEXT DEFAULT 'us',\n device TEXT DEFAULT 'desktop' CHECK (device IN ('desktop', 'mobile')),\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n deleted_at DATETIME NULL,\n FOREIGN KEY (user_id) REFERENCES users (id)\n )\n `);\n\n // Keywords table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS keywords (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n project_id INTEGER NOT NULL,\n keyword TEXT NOT NULL,\n target_url TEXT,\n active BOOLEAN DEFAULT TRUE,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n deleted_at DATETIME NULL,\n FOREIGN KEY (project_id) REFERENCES projects (id)\n )\n `);\n\n // Rankings table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS rankings (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n keyword_id INTEGER NOT NULL,\n position INTEGER,\n url TEXT,\n title TEXT,\n serp_features TEXT, -- JSON string\n checked_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (keyword_id) REFERENCES keywords (id)\n )\n `);\n\n // Rank history table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS rank_history (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n keyword_id INTEGER NOT NULL,\n position INTEGER,\n change_value INTEGER, -- positive = up, negative = down\n checked_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (keyword_id) REFERENCES keywords (id)\n )\n `);\n\n // Alerts table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS alerts (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n keyword_id INTEGER NOT NULL,\n trigger_type TEXT NOT NULL CHECK (trigger_type IN ('position_up', 'position_down', 'top_10_entry', 'page_1_lost')),\n threshold INTEGER NOT NULL,\n email TEXT NOT NULL,\n active BOOLEAN DEFAULT TRUE,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n deleted_at DATETIME NULL,\n FOREIGN KEY (keyword_id) REFERENCES keywords (id)\n )\n `);\n\n // Usage limits table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS usage_limits (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_id INTEGER NOT NULL,\n searches_used INTEGER DEFAULT 0,\n period_start DATETIME NOT NULL,\n period_end DATETIME NOT NULL,\n FOREIGN KEY (user_id) REFERENCES users (id)\n )\n `);\n\n // Create indexes for performance\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_users_email ON users (email)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_users_api_key ON users (api_key)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects (user_id)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_keywords_project_id ON keywords (project_id)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_rankings_keyword_id ON rankings (keyword_id)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_rankings_checked_at ON rankings (checked_at)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_rank_history_keyword_id ON rank_history (keyword_id)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_alerts_keyword_id ON alerts (keyword_id)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_rate_limits_user_endpoint ON rate_limits (user_id, endpoint)`);\n\n console.log(\"Database schema initialized successfully\");\n return database;\n}\n\n// Helper function to run migrations\nexport async function runMigrations() {\n // Future migrations can be added here\n console.log(\"Running database migrations...\");\n await initializeDatabase();\n console.log(\"Migrations completed\");\n}",[],[238],{"_104":246,"_95":247,"_153":248,"_155":-5,"_156":138,"_157":249,"_159":160,"_106":250,"_100":32,"_162":163,"_164":251,"_166":22,"_167":-5,"_168":252,"_98":253},"d469e2ee-8e92-11f0-8874-0224a6c84d84","deno.json","d469e460-8e92-11f0-8874-0224a6c84d84",1,["D",1757542172847],"{\n \"$schema\": \"https://raw.githubusercontent.com/denoland/deno/348900b8b79f4a434cab4c74b3bc8d4d2fa8ee74/cli/schemas/config-file.v1.json\",\n \"lock\": false,\n \"compilerOptions\": {\n \"noImplicitAny\": false,\n \"strict\": false,\n \"types\": [\n \"https://www.val.town/types/valtown.d.ts\"\n ],\n \"lib\": [\n \"dom\",\n \"dom.iterable\",\n \"dom.asynciterable\",\n \"deno.ns\",\n \"deno.unstable\"\n ]\n },\n \"lint\": {\n \"files\": {\n \"include\": [\n \"deno:/https/esm.town/**/*\"\n ]\n },\n \"rules\": {\n \"exclude\": [\n \"no-explicit-any\"\n ]\n }\n },\n \"node_modules_dir\": false,\n \"experimental\": {\n \"unstable-node-globals\": true,\n \"unstable-temporal\": true,\n \"unstable-worker-options\": true,\n \"unstable-sloppy-imports\": true\n }\n}",[],[247],{"_104":255,"_95":256,"_153":257,"_155":-5,"_156":138,"_157":258,"_159":160,"_106":259,"_100":32,"_162":195,"_164":260,"_166":22,"_167":-5,"_168":261,"_98":262},"b3117d6e-8e97-11f0-b3fc-0224a6c84d84","exportApi.ts","b3117e0e-8e97-11f0-b3fc-0224a6c84d84",15,["D",1757544264387],"// exportApi.ts - CSV export API endpoints for RankWatch\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\nimport { extractUserFromToken } from \"./auth.ts\";\nimport { getDatabase } from \"./database.ts\";\nimport { \n createSuccessResponse, \n createErrorResponse, \n logRequest,\n arrayToCSV,\n formatDate\n} from \"./utils.ts\";\nimport { checkRateLimit, getRateLimitHeaders } from \"./rateLimit.ts\";\n\nconst app = new Hono();\n\n// CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\"],\n}));\n\n// Middleware to extract and validate user\nasync function authenticateUser(c: any) {\n const authHeader = c.req.header(\"authorization\");\n if (!authHeader) {\n throw new Error(\"Authorization header required\");\n }\n return await extractUserFromToken(authHeader);\n}\n\n// Check if project belongs to user\nasync function verifyProjectOwnership(projectId: number, userId: number): Promise {\n const db = getDatabase();\n const result = await db.execute({\n sql: \"SELECT id FROM projects WHERE id = ? AND user_id = ? AND deleted_at IS NULL\",\n args: [projectId, userId],\n });\n return result.rows.length > 0;\n}\n\n// Check if keyword belongs to user\nasync function verifyKeywordOwnership(keywordId: number, userId: number): Promise {\n const db = getDatabase();\n const result = await db.execute({\n sql: `SELECT k.id \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id = ? AND p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [keywordId, userId],\n });\n return result.rows.length > 0;\n}\n\n// GET /projects/:projectId/export - Export project summary as CSV\napp.get(\"/projects/:projectId/export\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const projectId = parseInt(c.req.param(\"projectId\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(projectId)) {\n return createErrorResponse(\"Invalid project ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:projectId/export\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Verify project ownership\n const hasAccess = await verifyProjectOwnership(projectId, user.id);\n if (!hasAccess) {\n return createErrorResponse(\"Project not found or access denied\", 404);\n }\n\n const db = getDatabase();\n \n // Get project details\n const projectResult = await db.execute({\n sql: \"SELECT name, domain, location, device, created_at FROM projects WHERE id = ?\",\n args: [projectId],\n });\n\n if (projectResult.rows.length === 0) {\n return createErrorResponse(\"Project not found\", 404);\n }\n\n const project = projectResult.rows[0];\n\n // Get all keywords with their latest rankings\n const keywordsWithRankings = await db.execute({\n sql: `SELECT \n k.keyword,\n k.target_url,\n k.active,\n k.created_at as keyword_created,\n r.position,\n r.url as ranking_url,\n r.title as ranking_title,\n r.checked_at as last_check,\n rh.change_value as last_change\n FROM keywords k\n LEFT JOIN rankings r ON k.id = r.keyword_id \n AND r.id = (SELECT id FROM rankings WHERE keyword_id = k.id ORDER BY checked_at DESC LIMIT 1)\n LEFT JOIN rank_history rh ON k.id = rh.keyword_id\n AND rh.id = (SELECT id FROM rank_history WHERE keyword_id = k.id ORDER BY checked_at DESC LIMIT 1)\n WHERE k.project_id = ? AND k.deleted_at IS NULL\n ORDER BY k.keyword`,\n args: [projectId],\n });\n\n // Format data for CSV\n const csvData = keywordsWithRankings.rows.map(row => ({\n project_name: project[0],\n project_domain: project[1],\n keyword: row[0],\n target_url: row[1] || '',\n active: row[2] ? 'Yes' : 'No',\n current_position: row[4] || 'Not ranking',\n ranking_url: row[5] || '',\n ranking_title: row[6] || '',\n last_change: row[8] ? (row[8] > 0 ? `+${row[8]}` : row[8].toString()) : 'No change',\n last_checked: row[7] || 'Never',\n keyword_created: row[3],\n }));\n\n // Generate CSV\n const csvHeaders = [\n 'project_name',\n 'project_domain', \n 'keyword',\n 'target_url',\n 'active',\n 'current_position',\n 'ranking_url',\n 'ranking_title',\n 'last_change',\n 'last_checked',\n 'keyword_created'\n ];\n\n const csvContent = arrayToCSV(csvData, csvHeaders);\n const filename = `rankwatch_project_${projectId}_${formatDate(new Date()).split('T')[0]}.csv`;\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n // Return CSV file\n const response = new Response(csvContent, {\n status: 200,\n headers: {\n \"Content-Type\": \"text/csv\",\n \"Content-Disposition\": `attachment; filename=\"${filename}\"`,\n \"Cache-Control\": \"no-cache\",\n ...headers,\n },\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Project export error:\", error);\n return createErrorResponse(error.message || \"Failed to export project\", 401);\n }\n});\n\n// GET /keywords/:id/export - Export keyword ranking history as CSV\napp.get(\"/keywords/:id/export\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const keywordId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(keywordId)) {\n return createErrorResponse(\"Invalid keyword ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id/export\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Verify keyword ownership\n const hasAccess = await verifyKeywordOwnership(keywordId, user.id);\n if (!hasAccess) {\n return createErrorResponse(\"Keyword not found or access denied\", 404);\n }\n\n const url = new URL(c.req.url);\n const fromDate = url.searchParams.get(\"from\");\n const toDate = url.searchParams.get(\"to\");\n\n const db = getDatabase();\n \n // Get keyword and project details\n const keywordResult = await db.execute({\n sql: `SELECT k.keyword, k.target_url, p.name as project_name, p.domain as project_domain\n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id = ?`,\n args: [keywordId],\n });\n\n if (keywordResult.rows.length === 0) {\n return createErrorResponse(\"Keyword not found\", 404);\n }\n\n const keyword = keywordResult.rows[0];\n\n // Build query with optional date filters\n let whereClause = \"WHERE keyword_id = ?\";\n const args: any[] = [keywordId];\n \n if (fromDate) {\n whereClause += \" AND checked_at >= ?\";\n args.push(fromDate);\n }\n \n if (toDate) {\n whereClause += \" AND checked_at <= ?\";\n args.push(toDate);\n }\n\n // Get ranking history\n const historyResult = await db.execute({\n sql: `SELECT position, change_value, checked_at \n FROM rank_history ${whereClause} \n ORDER BY checked_at DESC`,\n args,\n });\n\n // Format data for CSV\n const csvData = historyResult.rows.map(row => ({\n project_name: keyword[2],\n project_domain: keyword[3],\n keyword: keyword[0],\n target_url: keyword[1] || '',\n date: row[2] ? row[2].split('T')[0] : '', // Date only\n time: row[2] ? row[2].split('T')[1]?.replace('Z', '') : '', // Time only\n position: row[0],\n change: row[1] ? (row[1] > 0 ? `+${row[1]}` : row[1].toString()) : '0',\n full_datetime: row[2],\n }));\n\n // Generate CSV\n const csvHeaders = [\n 'project_name',\n 'project_domain',\n 'keyword',\n 'target_url',\n 'date',\n 'time',\n 'position',\n 'change',\n 'full_datetime'\n ];\n\n const csvContent = arrayToCSV(csvData, csvHeaders);\n const dateRange = fromDate && toDate ? `_${fromDate}_to_${toDate}` : '';\n const filename = `rankwatch_keyword_${keywordId}_history${dateRange}_${formatDate(new Date()).split('T')[0]}.csv`;\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n // Return CSV file\n const response = new Response(csvContent, {\n status: 200,\n headers: {\n \"Content-Type\": \"text/csv\",\n \"Content-Disposition\": `attachment; filename=\"${filename}\"`,\n \"Cache-Control\": \"no-cache\",\n ...headers,\n },\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Keyword export error:\", error);\n return createErrorResponse(error.message || \"Failed to export keyword history\", 401);\n }\n});\n\n// GET /reports/usage - Export user usage statistics as CSV\napp.get(\"/reports/usage\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/reports/usage\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n \n // Get user stats\n const [projectCount, keywordCount, rankingCount, alertCount] = await Promise.all([\n db.execute({\n sql: \"SELECT COUNT(*) FROM projects WHERE user_id = ? AND deleted_at IS NULL\",\n args: [user.id],\n }),\n db.execute({\n sql: `SELECT COUNT(*) \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [user.id],\n }),\n db.execute({\n sql: `SELECT COUNT(*) \n FROM rankings r \n JOIN keywords k ON r.keyword_id = k.id \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ?`,\n args: [user.id],\n }),\n db.execute({\n sql: `SELECT COUNT(*) \n FROM alerts a \n JOIN keywords k ON a.keyword_id = k.id \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? AND a.deleted_at IS NULL`,\n args: [user.id],\n }),\n ]);\n\n // Get recent activity (last 30 days)\n const recentActivity = await db.execute({\n sql: `SELECT \n DATE(r.checked_at) as date,\n COUNT(*) as rankings_checked\n FROM rankings r \n JOIN keywords k ON r.keyword_id = k.id \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? \n AND r.checked_at >= datetime('now', '-30 days')\n GROUP BY DATE(r.checked_at)\n ORDER BY date DESC`,\n args: [user.id],\n });\n\n // Format usage data\n const usageData = [\n {\n user_email: user.email,\n user_plan: user.plan,\n account_created: user.created_at,\n total_projects: projectCount.rows[0][0],\n total_keywords: keywordCount.rows[0][0],\n total_ranking_checks: rankingCount.rows[0][0],\n total_alerts: alertCount.rows[0][0],\n report_generated: formatDate(new Date()),\n }\n ];\n\n // Format activity data\n const activityData = recentActivity.rows.map(row => ({\n user_email: user.email,\n date: row[0],\n rankings_checked: row[1],\n report_generated: formatDate(new Date()),\n }));\n\n // Combine both datasets\n const combinedData = [\n ...usageData.map(item => ({ ...item, type: 'usage_summary' })),\n ...activityData.map(item => ({ ...item, type: 'daily_activity' })),\n ];\n\n // Generate CSV\n const csvContent = arrayToCSV(combinedData);\n const filename = `rankwatch_usage_report_${formatDate(new Date()).split('T')[0]}.csv`;\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n // Return CSV file\n const response = new Response(csvContent, {\n status: 200,\n headers: {\n \"Content-Type\": \"text/csv\",\n \"Content-Disposition\": `attachment; filename=\"${filename}\"`,\n \"Cache-Control\": \"no-cache\",\n ...headers,\n },\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Usage report export error:\", error);\n return createErrorResponse(error.message || \"Failed to export usage report\", 401);\n }\n});\n\n// GET /export/all-keywords - Export all user keywords across all projects\napp.get(\"/export/all-keywords\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/export/all-keywords\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n \n // Get all keywords with their latest rankings across all projects\n const allKeywords = await db.execute({\n sql: `SELECT \n p.name as project_name,\n p.domain as project_domain,\n p.location,\n p.device,\n k.keyword,\n k.target_url,\n k.active,\n k.created_at as keyword_created,\n r.position,\n r.url as ranking_url,\n r.title as ranking_title,\n r.checked_at as last_check,\n rh.change_value as last_change\n FROM projects p\n JOIN keywords k ON p.id = k.project_id\n LEFT JOIN rankings r ON k.id = r.keyword_id \n AND r.id = (SELECT id FROM rankings WHERE keyword_id = k.id ORDER BY checked_at DESC LIMIT 1)\n LEFT JOIN rank_history rh ON k.id = rh.keyword_id\n AND rh.id = (SELECT id FROM rank_history WHERE keyword_id = k.id ORDER BY checked_at DESC LIMIT 1)\n WHERE p.user_id = ? AND p.deleted_at IS NULL AND k.deleted_at IS NULL\n ORDER BY p.name, k.keyword`,\n args: [user.id],\n });\n\n if (allKeywords.rows.length === 0) {\n return createErrorResponse(\"No keywords found\", 404);\n }\n\n // Format data for CSV\n const csvData = allKeywords.rows.map(row => ({\n project_name: row[0],\n project_domain: row[1],\n location: row[2],\n device: row[3],\n keyword: row[4],\n target_url: row[5] || '',\n active: row[6] ? 'Yes' : 'No',\n current_position: row[8] || 'Not ranking',\n ranking_url: row[9] || '',\n ranking_title: row[10] || '',\n last_change: row[12] ? (row[12] > 0 ? `+${row[12]}` : row[12].toString()) : 'No change',\n last_checked: row[11] || 'Never',\n keyword_created: row[7],\n }));\n\n // Generate CSV\n const csvHeaders = [\n 'project_name',\n 'project_domain',\n 'location',\n 'device',\n 'keyword',\n 'target_url',\n 'active',\n 'current_position',\n 'ranking_url',\n 'ranking_title',\n 'last_change',\n 'last_checked',\n 'keyword_created'\n ];\n\n const csvContent = arrayToCSV(csvData, csvHeaders);\n const filename = `rankwatch_all_keywords_${formatDate(new Date()).split('T')[0]}.csv`;\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n // Return CSV file\n const response = new Response(csvContent, {\n status: 200,\n headers: {\n \"Content-Type\": \"text/csv\",\n \"Content-Disposition\": `attachment; filename=\"${filename}\"`,\n \"Cache-Control\": \"no-cache\",\n ...headers,\n },\n });\n\n return response;\n\n } catch (error) {\n console.error(\"All keywords export error:\", error);\n return createErrorResponse(error.message || \"Failed to export all keywords\", 401);\n }\n});\n\n// GET /export/format-info - Get information about available export formats\napp.get(\"/export/format-info\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit (lightweight check)\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/export/format-info\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const formatInfo = {\n available_exports: [\n {\n endpoint: \"/projects/{id}/export\",\n description: \"Export project summary with all keywords and current rankings\",\n format: \"CSV\",\n parameters: [\"project_id\"],\n filename_pattern: \"rankwatch_project_{id}_{date}.csv\"\n },\n {\n endpoint: \"/keywords/{id}/export\",\n description: \"Export keyword ranking history\",\n format: \"CSV\",\n parameters: [\"keyword_id\", \"from (optional)\", \"to (optional)\"],\n filename_pattern: \"rankwatch_keyword_{id}_history_{date}.csv\"\n },\n {\n endpoint: \"/reports/usage\",\n description: \"Export user usage statistics and activity\",\n format: \"CSV\",\n parameters: [],\n filename_pattern: \"rankwatch_usage_report_{date}.csv\"\n },\n {\n endpoint: \"/export/all-keywords\",\n description: \"Export all keywords across all projects\",\n format: \"CSV\",\n parameters: [],\n filename_pattern: \"rankwatch_all_keywords_{date}.csv\"\n }\n ],\n csv_features: {\n encoding: \"UTF-8\",\n delimiter: \",\",\n quote_character: \"\\\"\",\n headers_included: true,\n date_format: \"ISO 8601 (YYYY-MM-DDTHH:mm:ss.sssZ)\"\n },\n notes: [\n \"All exports include rate limiting headers\",\n \"Files are generated on-demand and not stored server-side\",\n \"Date ranges use ISO 8601 format (YYYY-MM-DD)\",\n \"Export availability depends on user plan limits\"\n ]\n };\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(formatInfo, \"Export format information retrieved successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Export format info error:\", error);\n return createErrorResponse(error.message || \"Failed to get export format info\", 401);\n }\n});\n\n// Export the Hono app\nexport default {\n fetch: app.fetch,\n};",[],[256],{"_104":264,"_95":265,"_153":266,"_155":-5,"_156":138,"_157":267,"_159":160,"_106":268,"_100":32,"_162":195,"_164":269,"_166":22,"_167":-5,"_168":270,"_98":271},"0735f7ae-8e97-11f0-bcfc-0224a6c84d84","keywordsApi.ts","0735fb78-8e97-11f0-bcfc-0224a6c84d84",11,["D",1757543976057],"// keywordsApi.ts - Keywords CRUD API endpoints with soft delete for RankWatch\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\nimport { extractUserFromToken } from \"./auth.ts\";\nimport { getDatabase } from \"./database.ts\";\nimport { config } from \"./config.ts\";\nimport { \n createSuccessResponse, \n createErrorResponse, \n parseJsonBody, \n validateRequiredFields, \n isValidUrl,\n sanitizeString,\n logRequest,\n parsePaginationParams,\n createPaginationMeta,\n parseSearchQuery,\n getSoftDeleteTimestamp,\n isValidKeyword\n} from \"./utils.ts\";\nimport { checkRateLimit, getRateLimitHeaders } from \"./rateLimit.ts\";\n\nconst app = new Hono();\n\n// CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\"],\n}));\n\n// Interface for Keyword\ninterface Keyword {\n id: number;\n project_id: number;\n keyword: string;\n target_url?: string;\n active: boolean;\n created_at: string;\n updated_at: string;\n}\n\n// Middleware to extract and validate user\nasync function authenticateUser(c: any) {\n const authHeader = c.req.header(\"authorization\");\n if (!authHeader) {\n throw new Error(\"Authorization header required\");\n }\n return await extractUserFromToken(authHeader);\n}\n\n// Check if project belongs to user\nasync function verifyProjectOwnership(projectId: number, userId: number): Promise {\n const db = getDatabase();\n const result = await db.execute({\n sql: \"SELECT id FROM projects WHERE id = ? AND user_id = ? AND deleted_at IS NULL\",\n args: [projectId, userId],\n });\n return result.rows.length > 0;\n}\n\n// Check user plan limits for keywords\nasync function checkKeywordLimits(userId: number, plan: string): Promise<{ allowed: boolean; current: number; max: number }> {\n const db = getDatabase();\n const result = await db.execute({\n sql: `SELECT COUNT(*) as count \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [userId],\n });\n \n const currentCount = result.rows[0][0] as number;\n const maxKeywords = plan === \"unlimited\" ? -1 : config.plans.freemium.maxKeywords;\n \n return {\n allowed: maxKeywords === -1 || currentCount < maxKeywords,\n current: currentCount,\n max: maxKeywords,\n };\n}\n\n// GET /projects/:projectId/keywords - List keywords for a project\napp.get(\"/projects/:projectId/keywords\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const projectId = parseInt(c.req.param(\"projectId\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(projectId)) {\n return createErrorResponse(\"Invalid project ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:projectId/keywords\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Verify project ownership\n const hasAccess = await verifyProjectOwnership(projectId, user.id);\n if (!hasAccess) {\n return createErrorResponse(\"Project not found or access denied\", 404);\n }\n\n const url = new URL(c.req.url);\n const { page, limit } = parsePaginationParams(url);\n const searchQuery = parseSearchQuery(url);\n const offset = (page - 1) * limit;\n\n const db = getDatabase();\n \n // Build query with search\n let whereClause = \"WHERE project_id = ? AND deleted_at IS NULL\";\n const args: any[] = [projectId];\n \n if (searchQuery) {\n whereClause += \" AND (keyword LIKE ? OR target_url LIKE ?)\";\n args.push(`%${searchQuery}%`, `%${searchQuery}%`);\n }\n\n // Get total count\n const countResult = await db.execute({\n sql: `SELECT COUNT(*) as total FROM keywords ${whereClause}`,\n args,\n });\n const total = countResult.rows[0][0] as number;\n\n // Get keywords with pagination\n const keywordsResult = await db.execute({\n sql: `SELECT id, keyword, target_url, active, created_at, updated_at \n FROM keywords ${whereClause} \n ORDER BY created_at DESC \n LIMIT ? OFFSET ?`,\n args: [...args, limit, offset],\n });\n\n const keywords = keywordsResult.rows.map(row => ({\n id: row[0],\n project_id: projectId,\n keyword: row[1],\n target_url: row[2],\n active: row[3],\n created_at: row[4],\n updated_at: row[5],\n }));\n\n const meta = createPaginationMeta(page, limit, total);\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n const response = createSuccessResponse(keywords, \"Keywords retrieved successfully\", meta);\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Get keywords error:\", error);\n return createErrorResponse(error.message || \"Failed to get keywords\", 401);\n }\n});\n\n// POST /projects/:projectId/keywords - Add keyword to project\napp.post(\"/projects/:projectId/keywords\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const projectId = parseInt(c.req.param(\"projectId\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(projectId)) {\n return createErrorResponse(\"Invalid project ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:projectId/keywords\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Verify project ownership\n const hasAccess = await verifyProjectOwnership(projectId, user.id);\n if (!hasAccess) {\n return createErrorResponse(\"Project not found or access denied\", 404);\n }\n\n // Check keyword limits\n const limits = await checkKeywordLimits(user.id, user.plan);\n if (!limits.allowed) {\n return createErrorResponse(\n `Keyword limit reached. ${user.plan} plan allows ${limits.max} keywords. Current: ${limits.current}`, \n 403\n );\n }\n\n const body = await parseJsonBody(c.req.raw);\n \n // Validate required fields\n const validation = validateRequiredFields(body, [\"keyword\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { keyword, target_url, active = true } = body;\n\n // Validate keyword\n const keywordValidation = isValidKeyword(keyword);\n if (!keywordValidation.valid) {\n return createErrorResponse(keywordValidation.message!, 400);\n }\n\n const sanitizedKeyword = sanitizeString(keyword);\n\n // Validate target URL if provided\n if (target_url && !isValidUrl(target_url)) {\n return createErrorResponse(\"Invalid target URL format\", 400);\n }\n\n const db = getDatabase();\n \n // Check if keyword already exists in this project\n const existingKeyword = await db.execute({\n sql: \"SELECT id FROM keywords WHERE project_id = ? AND keyword = ? AND deleted_at IS NULL\",\n args: [projectId, sanitizedKeyword],\n });\n\n if (existingKeyword.rows.length > 0) {\n return createErrorResponse(\"Keyword already exists in this project\", 409);\n }\n\n // Create keyword\n const result = await db.execute({\n sql: `INSERT INTO keywords (project_id, keyword, target_url, active) \n VALUES (?, ?, ?, ?) \n RETURNING id, keyword, target_url, active, created_at, updated_at`,\n args: [projectId, sanitizedKeyword, target_url || null, active],\n });\n\n const keywordData = result.rows[0];\n const newKeyword = {\n id: keywordData[0],\n project_id: projectId,\n keyword: keywordData[1],\n target_url: keywordData[2],\n active: keywordData[3],\n created_at: keywordData[4],\n updated_at: keywordData[5],\n };\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(newKeyword, \"Keyword created successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Create keyword error:\", error);\n return createErrorResponse(error.message || \"Failed to create keyword\", 400);\n }\n});\n\n// PUT /keywords/:id - Update keyword\napp.put(\"/keywords/:id\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const keywordId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(keywordId)) {\n return createErrorResponse(\"Invalid keyword ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const body = await parseJsonBody(c.req.raw);\n const { keyword, target_url, active } = body;\n\n // Validate at least one field is provided\n if (keyword === undefined && target_url === undefined && active === undefined) {\n return createErrorResponse(\"At least one field must be provided for update\", 400);\n }\n\n const db = getDatabase();\n \n // Check if keyword exists and user has access (through project ownership)\n const existingKeyword = await db.execute({\n sql: `SELECT k.id, k.project_id \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id = ? AND p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [keywordId, user.id],\n });\n\n if (existingKeyword.rows.length === 0) {\n return createErrorResponse(\"Keyword not found or access denied\", 404);\n }\n\n const projectId = existingKeyword.rows[0][1] as number;\n\n // Build update query dynamically\n const updateFields: string[] = [];\n const updateValues: any[] = [];\n\n if (keyword !== undefined) {\n const keywordValidation = isValidKeyword(keyword);\n if (!keywordValidation.valid) {\n return createErrorResponse(keywordValidation.message!, 400);\n }\n \n const sanitizedKeyword = sanitizeString(keyword);\n \n // Check if updated keyword already exists in this project (excluding current keyword)\n const duplicateCheck = await db.execute({\n sql: \"SELECT id FROM keywords WHERE project_id = ? AND keyword = ? AND id != ? AND deleted_at IS NULL\",\n args: [projectId, sanitizedKeyword, keywordId],\n });\n\n if (duplicateCheck.rows.length > 0) {\n return createErrorResponse(\"Keyword already exists in this project\", 409);\n }\n\n updateFields.push(\"keyword = ?\");\n updateValues.push(sanitizedKeyword);\n }\n\n if (target_url !== undefined) {\n if (target_url && !isValidUrl(target_url)) {\n return createErrorResponse(\"Invalid target URL format\", 400);\n }\n updateFields.push(\"target_url = ?\");\n updateValues.push(target_url || null);\n }\n\n if (active !== undefined) {\n updateFields.push(\"active = ?\");\n updateValues.push(active);\n }\n\n updateFields.push(\"updated_at = CURRENT_TIMESTAMP\");\n updateValues.push(keywordId);\n\n // Update keyword\n await db.execute({\n sql: `UPDATE keywords SET ${updateFields.join(\", \")} WHERE id = ?`,\n args: updateValues,\n });\n\n // Get updated keyword\n const result = await db.execute({\n sql: `SELECT id, project_id, keyword, target_url, active, created_at, updated_at \n FROM keywords \n WHERE id = ? AND deleted_at IS NULL`,\n args: [keywordId],\n });\n\n const keywordData = result.rows[0];\n const updatedKeyword = {\n id: keywordData[0],\n project_id: keywordData[1],\n keyword: keywordData[2],\n target_url: keywordData[3],\n active: keywordData[4],\n created_at: keywordData[5],\n updated_at: keywordData[6],\n };\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(updatedKeyword, \"Keyword updated successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Update keyword error:\", error);\n return createErrorResponse(error.message || \"Failed to update keyword\", 400);\n }\n});\n\n// DELETE /keywords/:id - Soft delete keyword\napp.delete(\"/keywords/:id\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const keywordId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(keywordId)) {\n return createErrorResponse(\"Invalid keyword ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n \n // Check if keyword exists and user has access\n const existingKeyword = await db.execute({\n sql: `SELECT k.id \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id = ? AND p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [keywordId, user.id],\n });\n\n if (existingKeyword.rows.length === 0) {\n return createErrorResponse(\"Keyword not found or access denied\", 404);\n }\n\n // Soft delete keyword\n const deletedAt = getSoftDeleteTimestamp();\n \n await db.execute({\n sql: \"UPDATE keywords SET deleted_at = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?\",\n args: [deletedAt, keywordId],\n });\n\n // Also soft delete related alerts\n await db.execute({\n sql: \"UPDATE alerts SET deleted_at = ? WHERE keyword_id = ?\",\n args: [deletedAt, keywordId],\n });\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(\n { deleted: true, deleted_at: deletedAt }, \n \"Keyword deleted successfully\"\n );\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Delete keyword error:\", error);\n return createErrorResponse(error.message || \"Failed to delete keyword\", 400);\n }\n});\n\n// POST /keywords/bulk-create - Bulk create keywords\napp.post(\"/keywords/bulk-create\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/bulk-create\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const body = await parseJsonBody(c.req.raw);\n \n // Validate required fields\n const validation = validateRequiredFields(body, [\"project_id\", \"keywords\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { project_id, keywords } = body;\n\n if (!Array.isArray(keywords) || keywords.length === 0) {\n return createErrorResponse(\"Keywords must be a non-empty array\", 400);\n }\n\n if (keywords.length > 100) {\n return createErrorResponse(\"Maximum 100 keywords allowed per bulk operation\", 400);\n }\n\n // Verify project ownership\n const hasAccess = await verifyProjectOwnership(project_id, user.id);\n if (!hasAccess) {\n return createErrorResponse(\"Project not found or access denied\", 404);\n }\n\n // Check keyword limits\n const limits = await checkKeywordLimits(user.id, user.plan);\n if (!limits.allowed || (limits.max !== -1 && limits.current + keywords.length > limits.max)) {\n return createErrorResponse(\n `Keyword limit would be exceeded. ${user.plan} plan allows ${limits.max} keywords. Current: ${limits.current}, Trying to add: ${keywords.length}`, \n 403\n );\n }\n\n const db = getDatabase();\n const createdKeywords: any[] = [];\n const errors: string[] = [];\n\n // Process each keyword\n for (let i = 0; i < keywords.length; i++) {\n const keywordData = keywords[i];\n \n try {\n // Validate keyword structure\n if (typeof keywordData === \"string\") {\n // Simple string format\n const keywordValidation = isValidKeyword(keywordData);\n if (!keywordValidation.valid) {\n errors.push(`Keyword ${i + 1}: ${keywordValidation.message}`);\n continue;\n }\n\n const sanitizedKeyword = sanitizeString(keywordData);\n\n // Check for duplicates\n const duplicate = await db.execute({\n sql: \"SELECT id FROM keywords WHERE project_id = ? AND keyword = ? AND deleted_at IS NULL\",\n args: [project_id, sanitizedKeyword],\n });\n\n if (duplicate.rows.length > 0) {\n errors.push(`Keyword ${i + 1}: \"${sanitizedKeyword}\" already exists`);\n continue;\n }\n\n // Create keyword\n const result = await db.execute({\n sql: `INSERT INTO keywords (project_id, keyword, active) \n VALUES (?, ?, ?) \n RETURNING id, keyword, target_url, active, created_at, updated_at`,\n args: [project_id, sanitizedKeyword, true],\n });\n\n const newKeywordData = result.rows[0];\n createdKeywords.push({\n id: newKeywordData[0],\n project_id: project_id,\n keyword: newKeywordData[1],\n target_url: newKeywordData[2],\n active: newKeywordData[3],\n created_at: newKeywordData[4],\n updated_at: newKeywordData[5],\n });\n\n } else if (typeof keywordData === \"object\") {\n // Object format with additional fields\n const keywordValidation = isValidKeyword(keywordData.keyword);\n if (!keywordValidation.valid) {\n errors.push(`Keyword ${i + 1}: ${keywordValidation.message}`);\n continue;\n }\n\n const sanitizedKeyword = sanitizeString(keywordData.keyword);\n\n if (keywordData.target_url && !isValidUrl(keywordData.target_url)) {\n errors.push(`Keyword ${i + 1}: Invalid target URL`);\n continue;\n }\n\n // Check for duplicates\n const duplicate = await db.execute({\n sql: \"SELECT id FROM keywords WHERE project_id = ? AND keyword = ? AND deleted_at IS NULL\",\n args: [project_id, sanitizedKeyword],\n });\n\n if (duplicate.rows.length > 0) {\n errors.push(`Keyword ${i + 1}: \"${sanitizedKeyword}\" already exists`);\n continue;\n }\n\n // Create keyword\n const result = await db.execute({\n sql: `INSERT INTO keywords (project_id, keyword, target_url, active) \n VALUES (?, ?, ?, ?) \n RETURNING id, keyword, target_url, active, created_at, updated_at`,\n args: [project_id, sanitizedKeyword, keywordData.target_url || null, keywordData.active !== false],\n });\n\n const newKeywordData = result.rows[0];\n createdKeywords.push({\n id: newKeywordData[0],\n project_id: project_id,\n keyword: newKeywordData[1],\n target_url: newKeywordData[2],\n active: newKeywordData[3],\n created_at: newKeywordData[4],\n updated_at: newKeywordData[5],\n });\n\n } else {\n errors.push(`Keyword ${i + 1}: Invalid format`);\n }\n\n } catch (error) {\n errors.push(`Keyword ${i + 1}: ${error.message}`);\n }\n }\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(\n {\n created: createdKeywords,\n created_count: createdKeywords.length,\n error_count: errors.length,\n errors: errors.length > 0 ? errors : undefined,\n }, \n `Bulk operation completed. Created: ${createdKeywords.length}, Errors: ${errors.length}`\n );\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Bulk create keywords error:\", error);\n return createErrorResponse(error.message || \"Failed to bulk create keywords\", 400);\n }\n});\n\n// DELETE /keywords/bulk-delete - Bulk soft delete keywords\napp.delete(\"/keywords/bulk-delete\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/bulk-delete\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const body = await parseJsonBody(c.req.raw);\n \n // Validate required fields\n const validation = validateRequiredFields(body, [\"keyword_ids\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { keyword_ids } = body;\n\n if (!Array.isArray(keyword_ids) || keyword_ids.length === 0) {\n return createErrorResponse(\"keyword_ids must be a non-empty array\", 400);\n }\n\n if (keyword_ids.length > 100) {\n return createErrorResponse(\"Maximum 100 keywords allowed per bulk operation\", 400);\n }\n\n // Validate all IDs are numbers\n const invalidIds = keyword_ids.filter(id => !Number.isInteger(id) || id <= 0);\n if (invalidIds.length > 0) {\n return createErrorResponse(\"All keyword IDs must be positive integers\", 400);\n }\n\n const db = getDatabase();\n \n // Check which keywords exist and user has access to\n const placeholders = keyword_ids.map(() => \"?\").join(\",\");\n const existingKeywords = await db.execute({\n sql: `SELECT k.id \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id IN (${placeholders}) AND p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [...keyword_ids, user.id],\n });\n\n const existingIds = existingKeywords.rows.map(row => row[0] as number);\n const notFoundIds = keyword_ids.filter(id => !existingIds.includes(id));\n\n if (existingIds.length === 0) {\n return createErrorResponse(\"No valid keywords found for deletion\", 404);\n }\n\n // Soft delete keywords\n const deletedAt = getSoftDeleteTimestamp();\n const deletePlaceholders = existingIds.map(() => \"?\").join(\",\");\n \n await db.execute({\n sql: `UPDATE keywords SET deleted_at = ?, updated_at = CURRENT_TIMESTAMP WHERE id IN (${deletePlaceholders})`,\n args: [deletedAt, ...existingIds],\n });\n\n // Also soft delete related alerts\n await db.execute({\n sql: `UPDATE alerts SET deleted_at = ? WHERE keyword_id IN (${deletePlaceholders})`,\n args: [deletedAt, ...existingIds],\n });\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(\n {\n deleted_count: existingIds.length,\n deleted_ids: existingIds,\n not_found_count: notFoundIds.length,\n not_found_ids: notFoundIds.length > 0 ? notFoundIds : undefined,\n deleted_at: deletedAt,\n }, \n `Bulk delete completed. Deleted: ${existingIds.length} keywords`\n );\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Bulk delete keywords error:\", error);\n return createErrorResponse(error.message || \"Failed to bulk delete keywords\", 400);\n }\n});\n\n// GET /keywords/search - Search keywords across all user projects\napp.get(\"/keywords/search\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/search\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const url = new URL(c.req.url);\n const searchQuery = parseSearchQuery(url);\n \n if (!searchQuery) {\n return createErrorResponse(\"Search query 'q' parameter is required\", 400);\n }\n\n const { page, limit } = parsePaginationParams(url);\n const offset = (page - 1) * limit;\n\n const db = getDatabase();\n \n // Search in keywords and target URLs across all user projects\n const whereClause = `WHERE p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL \n AND (k.keyword LIKE ? OR k.target_url LIKE ?)`;\n const args = [user.id, `%${searchQuery}%`, `%${searchQuery}%`];\n\n // Get total count\n const countResult = await db.execute({\n sql: `SELECT COUNT(*) as total \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n ${whereClause}`,\n args,\n });\n const total = countResult.rows[0][0] as number;\n\n // Get keywords with project info\n const keywordsResult = await db.execute({\n sql: `SELECT k.id, k.project_id, k.keyword, k.target_url, k.active, k.created_at, k.updated_at,\n p.name as project_name, p.domain as project_domain\n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n ${whereClause}\n ORDER BY \n CASE \n WHEN k.keyword LIKE ? THEN 1 \n WHEN k.target_url LIKE ? THEN 2 \n ELSE 3 \n END,\n k.created_at DESC \n LIMIT ? OFFSET ?`,\n args: [...args, `%${searchQuery}%`, `%${searchQuery}%`, limit, offset],\n });\n\n const keywords = keywordsResult.rows.map(row => ({\n id: row[0],\n project_id: row[1],\n keyword: row[2],\n target_url: row[3],\n active: row[4],\n created_at: row[5],\n updated_at: row[6],\n project: {\n name: row[7],\n domain: row[8],\n },\n }));\n\n const meta = createPaginationMeta(page, limit, total);\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n const response = createSuccessResponse(\n keywords, \n `Found ${total} keywords matching \"${searchQuery}\"`, \n { ...meta, query: searchQuery }\n );\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Search keywords error:\", error);\n return createErrorResponse(error.message || \"Failed to search keywords\", 401);\n }\n});\n\n// Export the Hono app\nexport default {\n fetch: app.fetch,\n};",[],[265],{"_104":273,"_95":274,"_153":275,"_155":-5,"_156":138,"_157":276,"_159":160,"_106":277,"_100":32,"_162":195,"_164":278,"_166":22,"_167":-5,"_168":279,"_98":280},"f0cba512-8e97-11f0-9a31-0224a6c84d84","main.ts","f0cba5c6-8e97-11f0-9a31-0224a6c84d84",17,["D",1757544367937],"// main.ts - Main entry point for RankWatch API\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\nimport { initializeDatabase } from \"./database.ts\";\nimport { createSuccessResponse, createErrorResponse, logRequest } from \"./utils.ts\";\n\n// Import all API modules\nimport authApi from \"./authApi.ts\";\nimport projectsApi from \"./projectsApi.ts\";\nimport keywordsApi from \"./keywordsApi.ts\";\nimport rankingsApi from \"./rankingsApi.ts\";\nimport searchApi from \"./searchApi.ts\";\nimport exportApi from \"./exportApi.ts\";\nimport swaggerApi from \"./swaggerApi.ts\";\n\nconst app = new Hono();\n\n// Global CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\", \"X-Requested-With\"],\n exposeHeaders: [\"X-RateLimit-Limit\", \"X-RateLimit-Remaining\", \"X-RateLimit-Reset\"],\n}));\n\n// Initialize database on startup\nlet dbInitialized = false;\nasync function ensureDatabase() {\n if (!dbInitialized) {\n console.log(\"Initializing RankWatch database...\");\n await initializeDatabase();\n dbInitialized = true;\n console.log(\"Database initialized successfully\");\n }\n}\n\n// Global middleware to ensure database is initialized\napp.use(\"*\", async (c, next) => {\n await ensureDatabase();\n await next();\n});\n\n// Global error handler\napp.onError((err, c) => {\n console.error(\"Global error handler:\", err);\n logRequest(c.req.raw);\n \n return createErrorResponse(\n \"Internal server error\", \n 500\n );\n});\n\n// Mount API routes\napp.route(\"/auth\", authApi);\napp.route(\"/\", projectsApi);\napp.route(\"/\", keywordsApi);\napp.route(\"/\", rankingsApi);\napp.route(\"/\", searchApi);\napp.route(\"/\", exportApi);\napp.route(\"/\", swaggerApi);\n\n// Root endpoint\napp.get(\"/\", (c) => {\n logRequest(c.req.raw);\n \n return createSuccessResponse({\n service: \"RankWatch API\",\n version: \"1.0.0\",\n description: \"Keyword ranking monitoring API built with Val Town\",\n documentation: {\n swagger: `${new URL(c.req.url).origin}/docs`,\n redoc: `${new URL(c.req.url).origin}/docs/redoc`,\n openapi: `${new URL(c.req.url).origin}/docs/openapi.json`,\n },\n endpoints: {\n authentication: \"/auth/*\",\n projects: \"/projects/*\",\n keywords: \"/keywords/*\",\n rankings: \"/rankings/*\",\n search: \"/search/*\",\n export: \"/export/*\",\n },\n status: \"operational\",\n timestamp: new Date().toISOString(),\n }, \"Welcome to RankWatch API\");\n});\n\n// Health check endpoint\napp.get(\"/health\", async (c) => {\n logRequest(c.req.raw);\n \n try {\n // Test database connection\n await ensureDatabase();\n \n return createSuccessResponse({\n status: \"healthy\",\n service: \"RankWatch API\",\n version: \"1.0.0\",\n database: \"connected\",\n timestamp: new Date().toISOString(),\n uptime: process.uptime ? `${Math.floor(process.uptime())}s` : \"unknown\",\n }, \"Service is healthy\");\n \n } catch (error) {\n console.error(\"Health check failed:\", error);\n \n return createErrorResponse(\"Service unhealthy\", 503);\n }\n});\n\n// API status endpoint\napp.get(\"/status\", (c) => {\n logRequest(c.req.raw);\n \n return createSuccessResponse({\n api: {\n name: \"RankWatch API\",\n version: \"1.0.0\",\n status: \"operational\",\n },\n features: {\n authentication: \"JWT Bearer Token\",\n rate_limiting: \"SQLite-based per user/endpoint\",\n soft_deletes: \"All delete operations are soft deletes\",\n search: \"Global search across projects, keywords, and rankings\",\n export: \"CSV export for all data types\",\n documentation: \"OpenAPI 3.0 with Swagger UI\",\n },\n limits: {\n freemium: {\n projects: 1,\n keywords: 10,\n updates: \"weekly\",\n rate_limit: \"100 requests/hour\",\n },\n unlimited: {\n projects: \"unlimited\",\n keywords: \"unlimited\", \n updates: \"daily\",\n rate_limit: \"1000 requests/hour\",\n },\n },\n timestamp: new Date().toISOString(),\n }, \"API status information\");\n});\n\n// 404 handler\napp.notFound((c) => {\n logRequest(c.req.raw);\n \n return createErrorResponse(\n `Endpoint not found: ${c.req.method} ${c.req.path}`,\n 404\n );\n});\n\n// Export the main app\nexport default {\n fetch: app.fetch,\n};",[],[274],{"_104":282,"_95":283,"_153":284,"_155":-5,"_156":138,"_157":285,"_159":160,"_106":286,"_100":32,"_162":195,"_164":287,"_166":22,"_167":-5,"_168":288,"_98":289},"d02d9410-8e96-11f0-be01-0224a6c84d84","projectsApi.ts","d02d94ec-8e96-11f0-be01-0224a6c84d84",10,["D",1757543883720],"// projectsApi.ts - Projects CRUD API endpoints for RankWatch\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\nimport { extractUserFromToken } from \"./auth.ts\";\nimport { getDatabase } from \"./database.ts\";\nimport { config } from \"./config.ts\";\nimport { \n createSuccessResponse, \n createErrorResponse, \n parseJsonBody, \n validateRequiredFields, \n isValidUrl,\n sanitizeString,\n logRequest,\n parsePaginationParams,\n createPaginationMeta,\n parseSearchQuery,\n getSoftDeleteTimestamp\n} from \"./utils.ts\";\nimport { checkRateLimit, getRateLimitHeaders } from \"./rateLimit.ts\";\n\nconst app = new Hono();\n\n// CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\"],\n}));\n\n// Interface for Project\ninterface Project {\n id: number;\n user_id: number;\n name: string;\n domain: string;\n location: string;\n device: string;\n created_at: string;\n updated_at: string;\n}\n\n// Middleware to extract and validate user\nasync function authenticateUser(c: any) {\n const authHeader = c.req.header(\"authorization\");\n if (!authHeader) {\n throw new Error(\"Authorization header required\");\n }\n return await extractUserFromToken(authHeader);\n}\n\n// Check user plan limits for projects\nasync function checkProjectLimits(userId: number, plan: string): Promise {\n if (plan === \"unlimited\") return true;\n \n const db = getDatabase();\n const result = await db.execute({\n sql: \"SELECT COUNT(*) as count FROM projects WHERE user_id = ? AND deleted_at IS NULL\",\n args: [userId],\n });\n \n const currentCount = result.rows[0][0] as number;\n const maxProjects = config.plans.freemium.maxProjects;\n \n return currentCount < maxProjects;\n}\n\n// GET /projects - List user projects with pagination and search\napp.get(\"/projects\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const url = new URL(c.req.url);\n const { page, limit } = parsePaginationParams(url);\n const searchQuery = parseSearchQuery(url);\n const offset = (page - 1) * limit;\n\n const db = getDatabase();\n \n // Build query with search\n let whereClause = \"WHERE user_id = ? AND deleted_at IS NULL\";\n const args: any[] = [user.id];\n \n if (searchQuery) {\n whereClause += \" AND (name LIKE ? OR domain LIKE ?)\";\n args.push(`%${searchQuery}%`, `%${searchQuery}%`);\n }\n\n // Get total count\n const countResult = await db.execute({\n sql: `SELECT COUNT(*) as total FROM projects ${whereClause}`,\n args,\n });\n const total = countResult.rows[0][0] as number;\n\n // Get projects with pagination\n const projectsResult = await db.execute({\n sql: `SELECT id, name, domain, location, device, created_at, updated_at \n FROM projects ${whereClause} \n ORDER BY created_at DESC \n LIMIT ? OFFSET ?`,\n args: [...args, limit, offset],\n });\n\n const projects = projectsResult.rows.map(row => ({\n id: row[0],\n name: row[1],\n domain: row[2],\n location: row[3],\n device: row[4],\n created_at: row[5],\n updated_at: row[6],\n }));\n\n const meta = createPaginationMeta(page, limit, total);\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n const response = createSuccessResponse(projects, \"Projects retrieved successfully\", meta);\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Get projects error:\", error);\n return createErrorResponse(error.message || \"Failed to get projects\", 401);\n }\n});\n\n// POST /projects - Create new project\napp.post(\"/projects\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Check project limits\n const canCreateProject = await checkProjectLimits(user.id, user.plan);\n if (!canCreateProject) {\n return createErrorResponse(\n `Project limit reached. Freemium plan allows ${config.plans.freemium.maxProjects} project only.`, \n 403\n );\n }\n\n const body = await parseJsonBody(c.req.raw);\n \n // Validate required fields\n const validation = validateRequiredFields(body, [\"name\", \"domain\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { name, domain, location = \"us\", device = \"desktop\" } = body;\n\n // Validate and sanitize inputs\n const sanitizedName = sanitizeString(name);\n if (!sanitizedName || sanitizedName.length < 1) {\n return createErrorResponse(\"Project name is required\", 400);\n }\n\n if (!isValidUrl(domain) && !domain.includes(\".\")) {\n return createErrorResponse(\"Invalid domain format\", 400);\n }\n\n if (![\"desktop\", \"mobile\"].includes(device)) {\n return createErrorResponse(\"Device must be 'desktop' or 'mobile'\", 400);\n }\n\n // Clean domain (remove protocol if present)\n const cleanDomain = domain.replace(/^https?:\\/\\//, \"\").replace(/\\/$/, \"\");\n\n const db = getDatabase();\n \n // Check if project with same domain already exists for user\n const existingProject = await db.execute({\n sql: \"SELECT id FROM projects WHERE user_id = ? AND domain = ? AND deleted_at IS NULL\",\n args: [user.id, cleanDomain],\n });\n\n if (existingProject.rows.length > 0) {\n return createErrorResponse(\"Project with this domain already exists\", 409);\n }\n\n // Create project\n const result = await db.execute({\n sql: `INSERT INTO projects (user_id, name, domain, location, device) \n VALUES (?, ?, ?, ?, ?) \n RETURNING id, name, domain, location, device, created_at, updated_at`,\n args: [user.id, sanitizedName, cleanDomain, location, device],\n });\n\n const projectData = result.rows[0];\n const project = {\n id: projectData[0],\n name: projectData[1],\n domain: projectData[2],\n location: projectData[3],\n device: projectData[4],\n created_at: projectData[5],\n updated_at: projectData[6],\n };\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(project, \"Project created successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Create project error:\", error);\n return createErrorResponse(error.message || \"Failed to create project\", 400);\n }\n});\n\n// GET /projects/:id - Get specific project\napp.get(\"/projects/:id\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const projectId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(projectId)) {\n return createErrorResponse(\"Invalid project ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:id\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n const result = await db.execute({\n sql: `SELECT id, name, domain, location, device, created_at, updated_at \n FROM projects \n WHERE id = ? AND user_id = ? AND deleted_at IS NULL`,\n args: [projectId, user.id],\n });\n\n if (result.rows.length === 0) {\n return createErrorResponse(\"Project not found\", 404);\n }\n\n const projectData = result.rows[0];\n const project = {\n id: projectData[0],\n name: projectData[1],\n domain: projectData[2],\n location: projectData[3],\n device: projectData[4],\n created_at: projectData[5],\n updated_at: projectData[6],\n };\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(project, \"Project retrieved successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Get project error:\", error);\n return createErrorResponse(error.message || \"Failed to get project\", 401);\n }\n});\n\n// PUT /projects/:id - Update project\napp.put(\"/projects/:id\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const projectId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(projectId)) {\n return createErrorResponse(\"Invalid project ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:id\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const body = await parseJsonBody(c.req.raw);\n const { name, domain, location, device } = body;\n\n // Validate at least one field is provided\n if (!name && !domain && !location && !device) {\n return createErrorResponse(\"At least one field must be provided for update\", 400);\n }\n\n const db = getDatabase();\n \n // Check if project exists and belongs to user\n const existingProject = await db.execute({\n sql: \"SELECT id FROM projects WHERE id = ? AND user_id = ? AND deleted_at IS NULL\",\n args: [projectId, user.id],\n });\n\n if (existingProject.rows.length === 0) {\n return createErrorResponse(\"Project not found\", 404);\n }\n\n // Build update query dynamically\n const updateFields: string[] = [];\n const updateValues: any[] = [];\n\n if (name) {\n const sanitizedName = sanitizeString(name);\n if (!sanitizedName) {\n return createErrorResponse(\"Invalid project name\", 400);\n }\n updateFields.push(\"name = ?\");\n updateValues.push(sanitizedName);\n }\n\n if (domain) {\n if (!isValidUrl(domain) && !domain.includes(\".\")) {\n return createErrorResponse(\"Invalid domain format\", 400);\n }\n const cleanDomain = domain.replace(/^https?:\\/\\//, \"\").replace(/\\/$/, \"\");\n updateFields.push(\"domain = ?\");\n updateValues.push(cleanDomain);\n }\n\n if (location) {\n updateFields.push(\"location = ?\");\n updateValues.push(location);\n }\n\n if (device) {\n if (![\"desktop\", \"mobile\"].includes(device)) {\n return createErrorResponse(\"Device must be 'desktop' or 'mobile'\", 400);\n }\n updateFields.push(\"device = ?\");\n updateValues.push(device);\n }\n\n updateFields.push(\"updated_at = CURRENT_TIMESTAMP\");\n updateValues.push(projectId, user.id);\n\n // Update project\n await db.execute({\n sql: `UPDATE projects SET ${updateFields.join(\", \")} WHERE id = ? AND user_id = ?`,\n args: updateValues,\n });\n\n // Get updated project\n const result = await db.execute({\n sql: `SELECT id, name, domain, location, device, created_at, updated_at \n FROM projects \n WHERE id = ? AND user_id = ? AND deleted_at IS NULL`,\n args: [projectId, user.id],\n });\n\n const projectData = result.rows[0];\n const project = {\n id: projectData[0],\n name: projectData[1],\n domain: projectData[2],\n location: projectData[3],\n device: projectData[4],\n created_at: projectData[5],\n updated_at: projectData[6],\n };\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(project, \"Project updated successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Update project error:\", error);\n return createErrorResponse(error.message || \"Failed to update project\", 400);\n }\n});\n\n// DELETE /projects/:id - Soft delete project\napp.delete(\"/projects/:id\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const projectId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(projectId)) {\n return createErrorResponse(\"Invalid project ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:id\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n \n // Check if project exists and belongs to user\n const existingProject = await db.execute({\n sql: \"SELECT id FROM projects WHERE id = ? AND user_id = ? AND deleted_at IS NULL\",\n args: [projectId, user.id],\n });\n\n if (existingProject.rows.length === 0) {\n return createErrorResponse(\"Project not found\", 404);\n }\n\n // Soft delete project and all related keywords\n const deletedAt = getSoftDeleteTimestamp();\n \n await db.execute({\n sql: \"UPDATE projects SET deleted_at = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND user_id = ?\",\n args: [deletedAt, projectId, user.id],\n });\n\n // Also soft delete all keywords in this project\n await db.execute({\n sql: \"UPDATE keywords SET deleted_at = ?, updated_at = CURRENT_TIMESTAMP WHERE project_id = ?\",\n args: [deletedAt, projectId],\n });\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(\n { deleted: true, deleted_at: deletedAt }, \n \"Project deleted successfully\"\n );\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Delete project error:\", error);\n return createErrorResponse(error.message || \"Failed to delete project\", 400);\n }\n});\n\n// GET /projects/search - Search projects\napp.get(\"/projects/search\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/search\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const url = new URL(c.req.url);\n const searchQuery = parseSearchQuery(url);\n \n if (!searchQuery) {\n return createErrorResponse(\"Search query 'q' parameter is required\", 400);\n }\n\n const { page, limit } = parsePaginationParams(url);\n const offset = (page - 1) * limit;\n\n const db = getDatabase();\n \n // Search in name and domain\n const whereClause = \"WHERE user_id = ? AND deleted_at IS NULL AND (name LIKE ? OR domain LIKE ?)\";\n const args = [user.id, `%${searchQuery}%`, `%${searchQuery}%`];\n\n // Get total count\n const countResult = await db.execute({\n sql: `SELECT COUNT(*) as total FROM projects ${whereClause}`,\n args,\n });\n const total = countResult.rows[0][0] as number;\n\n // Get projects with pagination\n const projectsResult = await db.execute({\n sql: `SELECT id, name, domain, location, device, created_at, updated_at \n FROM projects ${whereClause} \n ORDER BY \n CASE \n WHEN name LIKE ? THEN 1 \n WHEN domain LIKE ? THEN 2 \n ELSE 3 \n END,\n created_at DESC \n LIMIT ? OFFSET ?`,\n args: [...args, `%${searchQuery}%`, `%${searchQuery}%`, limit, offset],\n });\n\n const projects = projectsResult.rows.map(row => ({\n id: row[0],\n name: row[1],\n domain: row[2],\n location: row[3],\n device: row[4],\n created_at: row[5],\n updated_at: row[6],\n }));\n\n const meta = createPaginationMeta(page, limit, total);\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n const response = createSuccessResponse(\n projects, \n `Found ${total} projects matching \"${searchQuery}\"`, \n { ...meta, query: searchQuery }\n );\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Search projects error:\", error);\n return createErrorResponse(error.message || \"Failed to search projects\", 401);\n }\n});\n\n// Export the Hono app\nexport default {\n fetch: app.fetch,\n};",[],[283],{"_104":291,"_95":292,"_153":293,"_155":-5,"_156":138,"_157":294,"_159":160,"_106":295,"_100":32,"_162":195,"_164":296,"_166":22,"_167":-5,"_168":297,"_98":298},"6287202e-8e97-11f0-ad40-0224a6c84d84","rankingsApi.ts","628720f6-8e97-11f0-ad40-0224a6c84d84",13,["D",1757544129250],"// rankingsApi.ts - Rankings and ranking history API endpoints for RankWatch\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\nimport { extractUserFromToken } from \"./auth.ts\";\nimport { getDatabase } from \"./database.ts\";\nimport { config } from \"./config.ts\";\nimport { \n createSuccessResponse, \n createErrorResponse, \n logRequest,\n parsePaginationParams,\n createPaginationMeta,\n formatDate\n} from \"./utils.ts\";\nimport { checkRateLimit, getRateLimitHeaders } from \"./rateLimit.ts\";\nimport { \n checkKeywordRanking, \n getCompetitorAnalysis, \n type RankingResult \n} from \"./serperClient.ts\";\n\nconst app = new Hono();\n\n// CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\"],\n}));\n\n// Middleware to extract and validate user\nasync function authenticateUser(c: any) {\n const authHeader = c.req.header(\"authorization\");\n if (!authHeader) {\n throw new Error(\"Authorization header required\");\n }\n return await extractUserFromToken(authHeader);\n}\n\n// Check if keyword belongs to user\nasync function verifyKeywordOwnership(keywordId: number, userId: number): Promise<{\n hasAccess: boolean;\n keyword?: any;\n project?: any;\n}> {\n const db = getDatabase();\n const result = await db.execute({\n sql: `SELECT k.id, k.keyword, k.target_url, p.id as project_id, p.name as project_name, \n p.domain, p.location, p.device\n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id = ? AND p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [keywordId, userId],\n });\n \n if (result.rows.length === 0) {\n return { hasAccess: false };\n }\n\n const row = result.rows[0];\n return {\n hasAccess: true,\n keyword: {\n id: row[0],\n keyword: row[1],\n target_url: row[2],\n },\n project: {\n id: row[3],\n name: row[4],\n domain: row[5],\n location: row[6],\n device: row[7],\n },\n };\n}\n\n// Save ranking result to database\nasync function saveRankingResult(keywordId: number, result: RankingResult): Promise {\n const db = getDatabase();\n \n // Insert ranking record\n await db.execute({\n sql: `INSERT INTO rankings (keyword_id, position, url, title, serp_features, checked_at) \n VALUES (?, ?, ?, ?, ?, ?)`,\n args: [\n keywordId,\n result.position,\n result.url,\n result.title,\n JSON.stringify(result.serp_features),\n result.checked_at,\n ],\n });\n\n // Get previous ranking for change calculation\n const previousRanking = await db.execute({\n sql: `SELECT position FROM rankings \n WHERE keyword_id = ? AND checked_at < ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordId, result.checked_at],\n });\n\n let changeValue = 0;\n if (previousRanking.rows.length > 0 && result.position) {\n const previousPosition = previousRanking.rows[0][0] as number | null;\n if (previousPosition) {\n changeValue = previousPosition - result.position; // Positive = moved up, Negative = moved down\n }\n }\n\n // Insert ranking history record\n if (result.position) {\n await db.execute({\n sql: `INSERT INTO rank_history (keyword_id, position, change_value, checked_at) \n VALUES (?, ?, ?, ?)`,\n args: [keywordId, result.position, changeValue, result.checked_at],\n });\n }\n}\n\n// GET /keywords/:id/rankings - Get current ranking for keyword\napp.get(\"/keywords/:id/rankings\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const keywordId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(keywordId)) {\n return createErrorResponse(\"Invalid keyword ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id/rankings\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Verify keyword ownership\n const ownership = await verifyKeywordOwnership(keywordId, user.id);\n if (!ownership.hasAccess) {\n return createErrorResponse(\"Keyword not found or access denied\", 404);\n }\n\n const db = getDatabase();\n \n // Get latest ranking\n const rankingResult = await db.execute({\n sql: `SELECT position, url, title, serp_features, checked_at \n FROM rankings \n WHERE keyword_id = ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordId],\n });\n\n let currentRanking = null;\n if (rankingResult.rows.length > 0) {\n const row = rankingResult.rows[0];\n currentRanking = {\n position: row[0],\n url: row[1],\n title: row[2],\n serp_features: row[3] ? JSON.parse(row[3] as string) : null,\n checked_at: row[4],\n };\n }\n\n // Get ranking change from history\n let change = null;\n if (currentRanking) {\n const historyResult = await db.execute({\n sql: `SELECT change_value \n FROM rank_history \n WHERE keyword_id = ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordId],\n });\n\n if (historyResult.rows.length > 0) {\n change = historyResult.rows[0][0] as number;\n }\n }\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse({\n keyword: ownership.keyword,\n project: ownership.project,\n current_ranking: currentRanking,\n change: change,\n last_updated: currentRanking?.checked_at || null,\n }, \"Current ranking retrieved successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Get current ranking error:\", error);\n return createErrorResponse(error.message || \"Failed to get current ranking\", 401);\n }\n});\n\n// GET /keywords/:id/history - Get ranking history for keyword\napp.get(\"/keywords/:id/history\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const keywordId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(keywordId)) {\n return createErrorResponse(\"Invalid keyword ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id/history\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Verify keyword ownership\n const ownership = await verifyKeywordOwnership(keywordId, user.id);\n if (!ownership.hasAccess) {\n return createErrorResponse(\"Keyword not found or access denied\", 404);\n }\n\n const url = new URL(c.req.url);\n const { page, limit } = parsePaginationParams(url);\n const offset = (page - 1) * limit;\n\n // Optional date range filters\n const fromDate = url.searchParams.get(\"from\");\n const toDate = url.searchParams.get(\"to\");\n\n const db = getDatabase();\n \n // Build query with optional date filters\n let whereClause = \"WHERE keyword_id = ?\";\n const args: any[] = [keywordId];\n \n if (fromDate) {\n whereClause += \" AND checked_at >= ?\";\n args.push(fromDate);\n }\n \n if (toDate) {\n whereClause += \" AND checked_at <= ?\";\n args.push(toDate);\n }\n\n // Get total count\n const countResult = await db.execute({\n sql: `SELECT COUNT(*) as total FROM rank_history ${whereClause}`,\n args,\n });\n const total = countResult.rows[0][0] as number;\n\n // Get ranking history with pagination\n const historyResult = await db.execute({\n sql: `SELECT position, change_value, checked_at \n FROM rank_history ${whereClause} \n ORDER BY checked_at DESC \n LIMIT ? OFFSET ?`,\n args: [...args, limit, offset],\n });\n\n const history = historyResult.rows.map(row => ({\n position: row[0],\n change: row[1],\n checked_at: row[2],\n }));\n\n const meta = createPaginationMeta(page, limit, total);\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n \n const response = createSuccessResponse({\n keyword: ownership.keyword,\n project: ownership.project,\n history,\n date_range: {\n from: fromDate || null,\n to: toDate || null,\n },\n }, \"Ranking history retrieved successfully\", meta);\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Get ranking history error:\", error);\n return createErrorResponse(error.message || \"Failed to get ranking history\", 401);\n }\n});\n\n// GET /keywords/:id/competitors - Get competitor analysis for keyword\napp.get(\"/keywords/:id/competitors\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const keywordId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(keywordId)) {\n return createErrorResponse(\"Invalid keyword ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id/competitors\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Verify keyword ownership\n const ownership = await verifyKeywordOwnership(keywordId, user.id);\n if (!ownership.hasAccess) {\n return createErrorResponse(\"Keyword not found or access denied\", 404);\n }\n\n const url = new URL(c.req.url);\n const topN = Math.min(20, Math.max(5, parseInt(url.searchParams.get(\"limit\") || \"10\")));\n\n // Get competitor analysis using Serper\n const competitorAnalysis = await getCompetitorAnalysis(\n ownership.keyword!.keyword,\n ownership.project!.location,\n ownership.project!.device,\n topN\n );\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse({\n keyword: ownership.keyword,\n project: ownership.project,\n competitors: competitorAnalysis.competitors,\n checked_at: competitorAnalysis.checked_at,\n top_n: topN,\n }, \"Competitor analysis retrieved successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Get competitor analysis error:\", error);\n return createErrorResponse(error.message || \"Failed to get competitor analysis\", 400);\n }\n});\n\n// POST /keywords/:id/check-now - Force immediate ranking check (unlimited plan only)\napp.post(\"/keywords/:id/check-now\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const keywordId = parseInt(c.req.param(\"id\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(keywordId)) {\n return createErrorResponse(\"Invalid keyword ID\", 400);\n }\n\n // Check if user has unlimited plan\n if (user.plan !== \"unlimited\") {\n return createErrorResponse(\"Immediate ranking checks are only available for unlimited plan users\", 403);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id/check-now\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Verify keyword ownership\n const ownership = await verifyKeywordOwnership(keywordId, user.id);\n if (!ownership.hasAccess) {\n return createErrorResponse(\"Keyword not found or access denied\", 404);\n }\n\n // Perform ranking check\n const rankingResult = await checkKeywordRanking(\n ownership.keyword!.keyword,\n ownership.project!.domain,\n ownership.keyword!.target_url,\n ownership.project!.location,\n ownership.project!.device\n );\n\n // Save result to database\n await saveRankingResult(keywordId, rankingResult);\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse({\n keyword: ownership.keyword,\n project: ownership.project,\n ranking_result: {\n position: rankingResult.position,\n url: rankingResult.url,\n title: rankingResult.title,\n found: rankingResult.found,\n serp_features: rankingResult.serp_features,\n checked_at: rankingResult.checked_at,\n error: rankingResult.error,\n },\n }, \"Ranking check completed successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Manual ranking check error:\", error);\n return createErrorResponse(error.message || \"Failed to perform ranking check\", 400);\n }\n});\n\n// GET /projects/:projectId/summary - Get project summary with ranking stats\napp.get(\"/projects/:projectId/summary\", async (c) => {\n try {\n const user = await authenticateUser(c);\n const projectId = parseInt(c.req.param(\"projectId\"));\n logRequest(c.req.raw, user.id);\n\n if (isNaN(projectId)) {\n return createErrorResponse(\"Invalid project ID\", 400);\n }\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:projectId/summary\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n \n // Verify project ownership\n const projectResult = await db.execute({\n sql: \"SELECT id, name, domain FROM projects WHERE id = ? AND user_id = ? AND deleted_at IS NULL\",\n args: [projectId, user.id],\n });\n\n if (projectResult.rows.length === 0) {\n return createErrorResponse(\"Project not found or access denied\", 404);\n }\n\n const project = {\n id: projectResult.rows[0][0],\n name: projectResult.rows[0][1],\n domain: projectResult.rows[0][2],\n };\n\n // Get summary statistics\n const [keywordCount, topRankings, improvements, declines] = await Promise.all([\n // Total keywords count\n db.execute({\n sql: \"SELECT COUNT(*) FROM keywords WHERE project_id = ? AND deleted_at IS NULL\",\n args: [projectId],\n }),\n \n // Top 10 rankings count\n db.execute({\n sql: `SELECT COUNT(DISTINCT k.id) \n FROM keywords k \n LEFT JOIN rankings r ON k.id = r.keyword_id \n WHERE k.project_id = ? AND k.deleted_at IS NULL \n AND r.id = (SELECT id FROM rankings WHERE keyword_id = k.id ORDER BY checked_at DESC LIMIT 1)\n AND r.position <= 10 AND r.position IS NOT NULL`,\n args: [projectId],\n }),\n\n // Recent improvements (moved up in last 7 days)\n db.execute({\n sql: `SELECT COUNT(*) \n FROM rank_history rh \n JOIN keywords k ON rh.keyword_id = k.id \n WHERE k.project_id = ? AND k.deleted_at IS NULL \n AND rh.change_value > 0 \n AND rh.checked_at >= datetime('now', '-7 days')`,\n args: [projectId],\n }),\n\n // Recent declines (moved down in last 7 days)\n db.execute({\n sql: `SELECT COUNT(*) \n FROM rank_history rh \n JOIN keywords k ON rh.keyword_id = k.id \n WHERE k.project_id = ? AND k.deleted_at IS NULL \n AND rh.change_value < 0 \n AND rh.checked_at >= datetime('now', '-7 days')`,\n args: [projectId],\n }),\n ]);\n\n // Get recent ranking changes (top movers)\n const recentChanges = await db.execute({\n sql: `SELECT k.keyword, rh.position, rh.change_value, rh.checked_at \n FROM rank_history rh \n JOIN keywords k ON rh.keyword_id = k.id \n WHERE k.project_id = ? AND k.deleted_at IS NULL \n AND rh.checked_at >= datetime('now', '-7 days')\n AND rh.change_value != 0\n ORDER BY ABS(rh.change_value) DESC \n LIMIT 10`,\n args: [projectId],\n });\n\n const topMovers = recentChanges.rows.map(row => ({\n keyword: row[0],\n position: row[1],\n change: row[2],\n checked_at: row[3],\n }));\n\n const summary = {\n project,\n stats: {\n total_keywords: keywordCount.rows[0][0],\n top_10_rankings: topRankings.rows[0][0],\n recent_improvements: improvements.rows[0][0],\n recent_declines: declines.rows[0][0],\n },\n top_movers: topMovers,\n summary_date: formatDate(new Date()),\n };\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse(summary, \"Project summary retrieved successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Get project summary error:\", error);\n return createErrorResponse(error.message || \"Failed to get project summary\", 401);\n }\n});\n\n// Export the Hono app\nexport default {\n fetch: app.fetch,\n};",[],[292],{"_104":300,"_95":301,"_153":302,"_155":-5,"_156":138,"_157":101,"_159":22,"_106":303,"_100":249,"_162":195,"_164":304,"_166":22,"_167":-5,"_168":305,"_98":306},"fccd2a8c-8efd-11f0-a8fa-0224a6c84d84","rankwatch.ts","6210349e-8e9e-11f0-9dc8-0224a6c84d84",["D",1757588196751],"// rankwatch.ts - Complete RankWatch API in a single file for Val Town\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\nimport { create, getNumericDate, verify } from \"https://deno.land/x/djwt@v3.0.1/mod.ts\";\nimport { encodeBase64 } from \"https://deno.land/std@0.208.0/encoding/base64.ts\";\nimport { sqlite } from \"https://esm.town/v/std/sqlite\";\n\nconst app = new Hono();\n\n// =====================================================\n// CONFIGURATION\n// =====================================================\n\nconst config = {\n jwt: {\n secret: Deno.env.get(\"JWT_SECRET\") || \"rankwatch-secret-key-change-in-production\",\n expiresIn: \"7d\",\n },\n rateLimit: {\n freemium: { requests: 100, windowMs: 60 * 60 * 1000 },\n unlimited: { requests: 1000, windowMs: 60 * 60 * 1000 },\n },\n serper: {\n apiKey: Deno.env.get(\"SERPER_API_KEY\") || \"\",\n baseUrl: \"https://google.serper.dev\",\n },\n plans: {\n freemium: { maxKeywords: 10, maxProjects: 1 },\n unlimited: { maxKeywords: -1, maxProjects: -1 },\n },\n};\n\n// =====================================================\n// DATABASE SETUP\n// =====================================================\n\nlet db: any = null;\n\nfunction getDatabase() {\n if (!db) {\n db = sqlite(\"rankwatch\");\n }\n return db;\n}\n\nasync function initializeDatabase() {\n const database = getDatabase();\n\n // Users table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS users (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n email TEXT UNIQUE NOT NULL,\n password_hash TEXT NOT NULL,\n plan TEXT NOT NULL DEFAULT 'freemium' CHECK (plan IN ('freemium', 'unlimited')),\n api_key TEXT UNIQUE,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n deleted_at DATETIME NULL\n )\n `);\n\n // Rate limiting table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS rate_limits (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_id INTEGER NOT NULL,\n endpoint TEXT NOT NULL,\n requests_made INTEGER NOT NULL DEFAULT 0,\n window_start DATETIME NOT NULL,\n window_end DATETIME NOT NULL,\n FOREIGN KEY (user_id) REFERENCES users (id),\n UNIQUE(user_id, endpoint, window_start)\n )\n `);\n\n // Projects table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS projects (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_id INTEGER NOT NULL,\n name TEXT NOT NULL,\n domain TEXT NOT NULL,\n location TEXT DEFAULT 'us',\n device TEXT DEFAULT 'desktop' CHECK (device IN ('desktop', 'mobile')),\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n deleted_at DATETIME NULL,\n FOREIGN KEY (user_id) REFERENCES users (id)\n )\n `);\n\n // Keywords table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS keywords (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n project_id INTEGER NOT NULL,\n keyword TEXT NOT NULL,\n target_url TEXT,\n active BOOLEAN DEFAULT TRUE,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n deleted_at DATETIME NULL,\n FOREIGN KEY (project_id) REFERENCES projects (id)\n )\n `);\n\n // Rankings table\n await database.execute(`\n CREATE TABLE IF NOT EXISTS rankings (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n keyword_id INTEGER NOT NULL,\n position INTEGER,\n url TEXT,\n title TEXT,\n serp_features TEXT,\n checked_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (keyword_id) REFERENCES keywords (id)\n )\n `);\n\n // Create indexes for performance\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_users_email ON users (email)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects (user_id)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_keywords_project_id ON keywords (project_id)`);\n await database.execute(`CREATE INDEX IF NOT EXISTS idx_rankings_keyword_id ON rankings (keyword_id)`);\n\n console.log(\"Database schema initialized successfully\");\n return database;\n}\n\n// =====================================================\n// UTILITY FUNCTIONS\n// =====================================================\n\nfunction createSuccessResponse(data: any, message?: string): Response {\n const response = {\n success: true,\n data,\n ...(message && { message }),\n };\n\n return new Response(JSON.stringify(response), {\n status: 200,\n headers: { \"Content-Type\": \"application/json\" },\n });\n}\n\nfunction createErrorResponse(error: string, status: number = 400): Response {\n const response = {\n success: false,\n error,\n };\n\n return new Response(JSON.stringify(response), {\n status,\n headers: { \"Content-Type\": \"application/json\" },\n });\n}\n\nfunction isValidEmail(email: string): boolean {\n const emailRegex = /^[^\\s@]+@[^\\s@]+\\.[^\\s@]+$/;\n return emailRegex.test(email);\n}\n\nfunction isValidPassword(password: string): { valid: boolean; message?: string } {\n if (password.length < 8) {\n return { valid: false, message: \"Password must be at least 8 characters long\" };\n }\n return { valid: true };\n}\n\nasync function parseJsonBody(request: Request): Promise {\n try {\n const text = await request.text();\n if (!text) {\n throw new Error(\"Request body is empty\");\n }\n return JSON.parse(text);\n } catch (error) {\n throw new Error(\"Invalid JSON in request body\");\n }\n}\n\nfunction validateRequiredFields(obj: any, requiredFields: string[]): { valid: boolean; missing?: string[] } {\n const missing: string[] = [];\n \n for (const field of requiredFields) {\n if (obj[field] === undefined || obj[field] === null || obj[field] === \"\") {\n missing.push(field);\n }\n }\n\n return {\n valid: missing.length === 0,\n ...(missing.length > 0 && { missing }),\n };\n}\n\n// =====================================================\n// AUTHENTICATION FUNCTIONS\n// =====================================================\n\nasync function hashPassword(password: string): Promise {\n const encoder = new TextEncoder();\n const data = encoder.encode(password);\n const hashBuffer = await crypto.subtle.digest(\"SHA-256\", data);\n return encodeBase64(new Uint8Array(hashBuffer));\n}\n\nasync function verifyPassword(password: string, hash: string): Promise {\n const hashedPassword = await hashPassword(password);\n return hashedPassword === hash;\n}\n\nfunction generateApiKey(): string {\n const array = new Uint8Array(32);\n crypto.getRandomValues(array);\n return encodeBase64(array);\n}\n\nasync function createJWT(userId: number, email: string, plan: string): Promise {\n const payload = {\n user_id: userId,\n email,\n plan,\n exp: getNumericDate(new Date(Date.now() + 7 * 24 * 60 * 60 * 1000)), // 7 days\n iat: getNumericDate(new Date()),\n };\n\n const key = await crypto.subtle.importKey(\n \"raw\",\n new TextEncoder().encode(config.jwt.secret),\n { name: \"HMAC\", hash: \"SHA-256\" },\n false,\n [\"sign\", \"verify\"]\n );\n\n return await create({ alg: \"HS256\", typ: \"JWT\" }, payload, key);\n}\n\nasync function verifyJWT(token: string): Promise {\n try {\n const key = await crypto.subtle.importKey(\n \"raw\",\n new TextEncoder().encode(config.jwt.secret),\n { name: \"HMAC\", hash: \"SHA-256\" },\n false,\n [\"sign\", \"verify\"]\n );\n\n const payload = await verify(token, key);\n return payload;\n } catch (error) {\n throw new Error(\"Invalid token\");\n }\n}\n\nasync function extractUserFromToken(authHeader: string): Promise {\n if (!authHeader || !authHeader.startsWith(\"Bearer \")) {\n throw new Error(\"Missing or invalid authorization header\");\n }\n\n const token = authHeader.substring(7);\n const payload = await verifyJWT(token);\n \n const db = getDatabase();\n const result = await db.execute({\n sql: \"SELECT id, email, plan, created_at FROM users WHERE id = ? AND deleted_at IS NULL\",\n args: [payload.user_id],\n });\n\n if (result.rows.length === 0) {\n throw new Error(\"User not found\");\n }\n\n const userData = result.rows[0];\n return {\n id: userData[0],\n email: userData[1],\n plan: userData[2],\n created_at: userData[3],\n };\n}\n\n// =====================================================\n// RATE LIMITING\n// =====================================================\n\nasync function checkRateLimit(userId: number, plan: string, endpoint: string): Promise<{ allowed: boolean; remaining: number }> {\n const db = getDatabase();\n const config_limit = config.rateLimit[plan as keyof typeof config.rateLimit];\n const now = new Date();\n const windowStart = new Date(now.getTime() - config_limit.windowMs);\n\n // Clean up old records\n await db.execute({\n sql: \"DELETE FROM rate_limits WHERE window_end < ?\",\n args: [new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString()],\n });\n\n // Check current usage\n const existingLimit = await db.execute({\n sql: `SELECT requests_made FROM rate_limits \n WHERE user_id = ? AND endpoint = ? AND window_end > ?\n ORDER BY window_start DESC LIMIT 1`,\n args: [userId, endpoint, now.toISOString()],\n });\n\n let requestsMade = 0;\n if (existingLimit.rows.length > 0) {\n requestsMade = existingLimit.rows[0][0] as number;\n }\n\n if (requestsMade >= config_limit.requests) {\n return { allowed: false, remaining: 0 };\n }\n\n // Increment request count\n const newRequestCount = requestsMade + 1;\n const windowEnd = new Date(now.getTime() + config_limit.windowMs);\n\n await db.execute({\n sql: `INSERT OR REPLACE INTO rate_limits (user_id, endpoint, requests_made, window_start, window_end) \n VALUES (?, ?, ?, ?, ?)`,\n args: [userId, endpoint, newRequestCount, now.toISOString(), windowEnd.toISOString()],\n });\n\n return {\n allowed: true,\n remaining: config_limit.requests - newRequestCount,\n };\n}\n\n// =====================================================\n// SERPER CLIENT (Simplified)\n// =====================================================\n\ninterface RankingResult {\n keyword: string;\n position: number | null;\n url: string | null;\n title: string | null;\n found: boolean;\n checked_at: string;\n error?: string;\n}\n\nasync function checkKeywordRanking(keyword: string, domain: string): Promise {\n const checkedAt = new Date().toISOString();\n \n try {\n if (!config.serper.apiKey) {\n throw new Error(\"Serper API key not configured\");\n }\n\n const response = await fetch(\"https://google.serper.dev/search\", {\n method: \"POST\",\n headers: {\n \"X-API-KEY\": config.serper.apiKey,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n q: keyword,\n gl: \"us\",\n hl: \"en\",\n num: 100,\n }),\n });\n\n if (!response.ok) {\n throw new Error(`Serper API error: ${response.status}`);\n }\n\n const data = await response.json();\n const organic = data.organic || [];\n\n // Find domain ranking\n const cleanDomain = domain.replace(/^https?:\\/\\//, \"\").replace(/^www\\./, \"\").toLowerCase();\n \n for (let i = 0; i < organic.length; i++) {\n const result = organic[i];\n const resultDomain = result.displayLink?.replace(/^www\\./, \"\").toLowerCase();\n \n if (resultDomain === cleanDomain) {\n return {\n keyword,\n position: i + 1,\n url: result.link,\n title: result.title,\n found: true,\n checked_at: checkedAt,\n };\n }\n }\n\n return {\n keyword,\n position: null,\n url: null,\n title: null,\n found: false,\n checked_at: checkedAt,\n };\n\n } catch (error) {\n return {\n keyword,\n position: null,\n url: null,\n title: null,\n found: false,\n checked_at: checkedAt,\n error: error.message,\n };\n }\n}\n\n// =====================================================\n// MIDDLEWARE SETUP\n// =====================================================\n\n// Global CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\"],\n}));\n\n// Initialize database on startup\nlet dbInitialized = false;\nasync function ensureDatabase() {\n if (!dbInitialized) {\n await initializeDatabase();\n dbInitialized = true;\n }\n}\n\napp.use(\"*\", async (c, next) => {\n await ensureDatabase();\n await next();\n});\n\n// Global error handler\napp.onError((err, c) => {\n console.error(\"Global error:\", err);\n return createErrorResponse(\"Internal server error\", 500);\n});\n\n// =====================================================\n// AUTHENTICATION ENDPOINTS\n// =====================================================\n\n// POST /auth/register\napp.post(\"/auth/register\", async (c) => {\n try {\n const body = await parseJsonBody(c.req.raw);\n \n const validation = validateRequiredFields(body, [\"email\", \"password\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { email, password, plan = \"freemium\" } = body;\n\n if (!isValidEmail(email)) {\n return createErrorResponse(\"Invalid email format\", 400);\n }\n\n const passwordValidation = isValidPassword(password);\n if (!passwordValidation.valid) {\n return createErrorResponse(passwordValidation.message!, 400);\n }\n\n const db = getDatabase();\n \n // Check if user exists\n const existingUser = await db.execute({\n sql: \"SELECT id FROM users WHERE email = ? AND deleted_at IS NULL\",\n args: [email],\n });\n\n if (existingUser.rows.length > 0) {\n return createErrorResponse(\"User already exists\", 400);\n }\n\n // Create user\n const passwordHash = await hashPassword(password);\n const apiKey = generateApiKey();\n\n const result = await db.execute({\n sql: `INSERT INTO users (email, password_hash, plan, api_key) \n VALUES (?, ?, ?, ?) RETURNING id, email, plan, created_at`,\n args: [email, passwordHash, plan, apiKey],\n });\n\n const userData = result.rows[0];\n const user = {\n id: userData[0],\n email: userData[1],\n plan: userData[2],\n created_at: userData[3],\n };\n\n const token = await createJWT(user.id, user.email, user.plan);\n\n return createSuccessResponse({ user, token }, \"User registered successfully\");\n\n } catch (error) {\n console.error(\"Registration error:\", error);\n return createErrorResponse(error.message || \"Registration failed\", 400);\n }\n});\n\n// POST /auth/login\napp.post(\"/auth/login\", async (c) => {\n try {\n const body = await parseJsonBody(c.req.raw);\n \n const validation = validateRequiredFields(body, [\"email\", \"password\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { email, password } = body;\n\n const db = getDatabase();\n const result = await db.execute({\n sql: \"SELECT id, email, password_hash, plan, created_at FROM users WHERE email = ? AND deleted_at IS NULL\",\n args: [email],\n });\n\n if (result.rows.length === 0) {\n return createErrorResponse(\"User not found\", 401);\n }\n\n const userData = result.rows[0];\n const passwordHash = userData[2] as string;\n\n const isValidPassword = await verifyPassword(password, passwordHash);\n if (!isValidPassword) {\n return createErrorResponse(\"Invalid password\", 401);\n }\n\n const user = {\n id: userData[0],\n email: userData[1],\n plan: userData[3],\n created_at: userData[4],\n };\n\n const token = await createJWT(user.id, user.email, user.plan);\n\n return createSuccessResponse({ user, token }, \"Login successful\");\n\n } catch (error) {\n console.error(\"Login error:\", error);\n return createErrorResponse(error.message || \"Login failed\", 401);\n }\n});\n\n// GET /auth/me\napp.get(\"/auth/me\", async (c) => {\n try {\n const authHeader = c.req.header(\"authorization\");\n if (!authHeader) {\n return createErrorResponse(\"Authorization header required\", 401);\n }\n\n const user = await extractUserFromToken(authHeader);\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/auth/me\");\n \n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n return createSuccessResponse(user, \"User info retrieved successfully\");\n\n } catch (error) {\n return createErrorResponse(error.message || \"Failed to get user info\", 401);\n }\n});\n\n// =====================================================\n// PROJECT ENDPOINTS\n// =====================================================\n\n// GET /projects\napp.get(\"/projects\", async (c) => {\n try {\n const user = await extractUserFromToken(c.req.header(\"authorization\") || \"\");\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects\");\n \n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n const result = await db.execute({\n sql: `SELECT id, name, domain, location, device, created_at, updated_at \n FROM projects \n WHERE user_id = ? AND deleted_at IS NULL \n ORDER BY created_at DESC`,\n args: [user.id],\n });\n\n const projects = result.rows.map(row => ({\n id: row[0],\n name: row[1],\n domain: row[2],\n location: row[3],\n device: row[4],\n created_at: row[5],\n updated_at: row[6],\n }));\n\n return createSuccessResponse(projects, \"Projects retrieved successfully\");\n\n } catch (error) {\n return createErrorResponse(error.message || \"Failed to get projects\", 401);\n }\n});\n\n// POST /projects\napp.post(\"/projects\", async (c) => {\n try {\n const user = await extractUserFromToken(c.req.header(\"authorization\") || \"\");\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects\");\n \n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Check project limits\n if (user.plan === \"freemium\") {\n const db = getDatabase();\n const projectCount = await db.execute({\n sql: \"SELECT COUNT(*) as count FROM projects WHERE user_id = ? AND deleted_at IS NULL\",\n args: [user.id],\n });\n \n if ((projectCount.rows[0][0] as number) >= config.plans.freemium.maxProjects) {\n return createErrorResponse(\"Project limit reached. Freemium plan allows 1 project only.\", 403);\n }\n }\n\n const body = await parseJsonBody(c.req.raw);\n const validation = validateRequiredFields(body, [\"name\", \"domain\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { name, domain, location = \"us\", device = \"desktop\" } = body;\n const cleanDomain = domain.replace(/^https?:\\/\\//, \"\").replace(/\\/$/, \"\");\n\n const db = getDatabase();\n const result = await db.execute({\n sql: `INSERT INTO projects (user_id, name, domain, location, device) \n VALUES (?, ?, ?, ?, ?) \n RETURNING id, name, domain, location, device, created_at, updated_at`,\n args: [user.id, name.trim(), cleanDomain, location, device],\n });\n\n const projectData = result.rows[0];\n const project = {\n id: projectData[0],\n name: projectData[1],\n domain: projectData[2],\n location: projectData[3],\n device: projectData[4],\n created_at: projectData[5],\n updated_at: projectData[6],\n };\n\n return createSuccessResponse(project, \"Project created successfully\");\n\n } catch (error) {\n return createErrorResponse(error.message || \"Failed to create project\", 400);\n }\n});\n\n// =====================================================\n// KEYWORD ENDPOINTS\n// =====================================================\n\n// GET /projects/:id/keywords\napp.get(\"/projects/:id/keywords\", async (c) => {\n try {\n const user = await extractUserFromToken(c.req.header(\"authorization\") || \"\");\n const projectId = parseInt(c.req.param(\"id\"));\n \n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:id/keywords\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n \n // Verify project ownership\n const projectCheck = await db.execute({\n sql: \"SELECT id FROM projects WHERE id = ? AND user_id = ? AND deleted_at IS NULL\",\n args: [projectId, user.id],\n });\n\n if (projectCheck.rows.length === 0) {\n return createErrorResponse(\"Project not found\", 404);\n }\n\n const result = await db.execute({\n sql: `SELECT id, keyword, target_url, active, created_at, updated_at \n FROM keywords \n WHERE project_id = ? AND deleted_at IS NULL \n ORDER BY created_at DESC`,\n args: [projectId],\n });\n\n const keywords = result.rows.map(row => ({\n id: row[0],\n project_id: projectId,\n keyword: row[1],\n target_url: row[2],\n active: row[3],\n created_at: row[4],\n updated_at: row[5],\n }));\n\n return createSuccessResponse(keywords, \"Keywords retrieved successfully\");\n\n } catch (error) {\n return createErrorResponse(error.message || \"Failed to get keywords\", 401);\n }\n});\n\n// POST /projects/:id/keywords\napp.post(\"/projects/:id/keywords\", async (c) => {\n try {\n const user = await extractUserFromToken(c.req.header(\"authorization\") || \"\");\n const projectId = parseInt(c.req.param(\"id\"));\n \n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/projects/:id/keywords\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Check keyword limits\n if (user.plan === \"freemium\") {\n const db = getDatabase();\n const keywordCount = await db.execute({\n sql: `SELECT COUNT(*) as count \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [user.id],\n });\n \n if ((keywordCount.rows[0][0] as number) >= config.plans.freemium.maxKeywords) {\n return createErrorResponse(\"Keyword limit reached. Freemium plan allows 10 keywords.\", 403);\n }\n }\n\n const body = await parseJsonBody(c.req.raw);\n const validation = validateRequiredFields(body, [\"keyword\"]);\n if (!validation.valid) {\n return createErrorResponse(`Missing required fields: ${validation.missing?.join(\", \")}`, 400);\n }\n\n const { keyword, target_url, active = true } = body;\n\n const db = getDatabase();\n \n // Verify project ownership\n const projectCheck = await db.execute({\n sql: \"SELECT id FROM projects WHERE id = ? AND user_id = ? AND deleted_at IS NULL\",\n args: [projectId, user.id],\n });\n\n if (projectCheck.rows.length === 0) {\n return createErrorResponse(\"Project not found\", 404);\n }\n\n const result = await db.execute({\n sql: `INSERT INTO keywords (project_id, keyword, target_url, active) \n VALUES (?, ?, ?, ?) \n RETURNING id, keyword, target_url, active, created_at, updated_at`,\n args: [projectId, keyword.trim(), target_url || null, active],\n });\n\n const keywordData = result.rows[0];\n const newKeyword = {\n id: keywordData[0],\n project_id: projectId,\n keyword: keywordData[1],\n target_url: keywordData[2],\n active: keywordData[3],\n created_at: keywordData[4],\n updated_at: keywordData[5],\n };\n\n return createSuccessResponse(newKeyword, \"Keyword created successfully\");\n\n } catch (error) {\n return createErrorResponse(error.message || \"Failed to create keyword\", 400);\n }\n});\n\n// =====================================================\n// RANKING ENDPOINTS\n// =====================================================\n\n// GET /keywords/:id/rankings\napp.get(\"/keywords/:id/rankings\", async (c) => {\n try {\n const user = await extractUserFromToken(c.req.header(\"authorization\") || \"\");\n const keywordId = parseInt(c.req.param(\"id\"));\n \n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id/rankings\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n \n // Verify keyword ownership\n const keywordCheck = await db.execute({\n sql: `SELECT k.keyword, p.domain \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id = ? AND p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [keywordId, user.id],\n });\n\n if (keywordCheck.rows.length === 0) {\n return createErrorResponse(\"Keyword not found\", 404);\n }\n\n // Get latest ranking\n const rankingResult = await db.execute({\n sql: `SELECT position, url, title, checked_at \n FROM rankings \n WHERE keyword_id = ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordId],\n });\n\n let currentRanking = null;\n if (rankingResult.rows.length > 0) {\n const row = rankingResult.rows[0];\n currentRanking = {\n position: row[0],\n url: row[1],\n title: row[2],\n checked_at: row[3],\n };\n }\n\n return createSuccessResponse({\n keyword: keywordCheck.rows[0][0],\n domain: keywordCheck.rows[0][1],\n current_ranking: currentRanking,\n }, \"Current ranking retrieved successfully\");\n\n } catch (error) {\n return createErrorResponse(error.message || \"Failed to get ranking\", 401);\n }\n});\n\n// POST /keywords/:id/check-now\napp.post(\"/keywords/:id/check-now\", async (c) => {\n try {\n const user = await extractUserFromToken(c.req.header(\"authorization\") || \"\");\n const keywordId = parseInt(c.req.param(\"id\"));\n \n if (user.plan !== \"unlimited\") {\n return createErrorResponse(\"Immediate ranking checks are only available for unlimited plan users\", 403);\n }\n\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/keywords/:id/check-now\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const db = getDatabase();\n \n // Get keyword and project details\n const keywordResult = await db.execute({\n sql: `SELECT k.keyword, p.domain \n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE k.id = ? AND p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL`,\n args: [keywordId, user.id],\n });\n\n if (keywordResult.rows.length === 0) {\n return createErrorResponse(\"Keyword not found\", 404);\n }\n\n const keyword = keywordResult.rows[0][0] as string;\n const domain = keywordResult.rows[0][1] as string;\n\n // Check ranking using Serper\n const rankingResult = await checkKeywordRanking(keyword, domain);\n\n // Save result to database\n await db.execute({\n sql: `INSERT INTO rankings (keyword_id, position, url, title, checked_at) \n VALUES (?, ?, ?, ?, ?)`,\n args: [\n keywordId,\n rankingResult.position,\n rankingResult.url,\n rankingResult.title,\n rankingResult.checked_at,\n ],\n });\n\n return createSuccessResponse({\n keyword,\n domain,\n ranking_result: rankingResult,\n }, \"Ranking check completed successfully\");\n\n } catch (error) {\n return createErrorResponse(error.message || \"Failed to perform ranking check\", 400);\n }\n});\n\n// =====================================================\n// ROOT ENDPOINTS\n// =====================================================\n\n// Root endpoint\napp.get(\"/\", (c) => {\n return createSuccessResponse({\n service: \"RankWatch API\",\n version: \"1.0.0\",\n description: \"Keyword ranking monitoring API built with Val Town\",\n endpoints: {\n authentication: \"/auth/*\",\n projects: \"/projects/*\",\n keywords: \"/keywords/*\",\n rankings: \"/rankings/*\",\n },\n status: \"operational\",\n timestamp: new Date().toISOString(),\n }, \"Welcome to RankWatch API\");\n});\n\n// Health check endpoint\napp.get(\"/health\", async (c) => {\n try {\n await ensureDatabase();\n \n return createSuccessResponse({\n status: \"healthy\",\n service: \"RankWatch API\",\n version: \"1.0.0\",\n database: \"connected\",\n timestamp: new Date().toISOString(),\n }, \"Service is healthy\");\n \n } catch (error) {\n console.error(\"Health check failed:\", error);\n return createErrorResponse(\"Service unhealthy\", 503);\n }\n});\n\n// 404 handler\napp.notFound((c) => {\n return createErrorResponse(`Endpoint not found: ${c.req.method} ${c.req.path}`, 404);\n});\n\n// Export the main app\nexport default {\n fetch: app.fetch,\n};",[],[301],{"_104":308,"_95":309,"_153":310,"_155":-5,"_156":138,"_157":311,"_159":160,"_106":312,"_100":32,"_162":195,"_164":313,"_166":22,"_167":-5,"_168":314,"_98":315},"8edcebd2-8e96-11f0-a1ac-0224a6c84d84","rateLimit.ts","8edcec90-8e96-11f0-a1ac-0224a6c84d84",7,["D",1757543774152],"// rateLimit.ts - SQLite-based rate limiting system for RankWatch\n\nimport { config, type UserPlan } from \"./config.ts\";\nimport { getDatabase } from \"./database.ts\";\n\nexport interface RateLimitResult {\n allowed: boolean;\n remaining: number;\n resetTime: Date;\n message?: string;\n}\n\n// Get rate limit configuration for user plan\nfunction getRateLimitConfig(plan: UserPlan) {\n return config.rateLimit[plan];\n}\n\n// Clean up old rate limit records\nexport async function cleanupRateLimits(): Promise {\n const db = getDatabase();\n const oneDayAgo = new Date(Date.now() - 24 * 60 * 60 * 1000);\n \n await db.execute({\n sql: \"DELETE FROM rate_limits WHERE window_end < ?\",\n args: [oneDayAgo.toISOString()],\n });\n}\n\n// Check and update rate limit for user and endpoint\nexport async function checkRateLimit(\n userId: number, \n plan: UserPlan, \n endpoint: string\n): Promise {\n const db = getDatabase();\n const config = getRateLimitConfig(plan);\n const now = new Date();\n const windowStart = new Date(now.getTime() - config.windowMs);\n const windowEnd = new Date(now.getTime());\n\n // First, cleanup old records\n await cleanupRateLimits();\n\n // Check current usage in this window\n const existingLimit = await db.execute({\n sql: `SELECT id, requests_made, window_start, window_end \n FROM rate_limits \n WHERE user_id = ? AND endpoint = ? AND window_end > ?\n ORDER BY window_start DESC \n LIMIT 1`,\n args: [userId, endpoint, now.toISOString()],\n });\n\n let requestsMade = 0;\n let rateLimitId = null;\n let currentWindowStart = windowStart;\n let currentWindowEnd = windowEnd;\n\n if (existingLimit.rows.length > 0) {\n const limitData = existingLimit.rows[0];\n rateLimitId = limitData[0] as number;\n requestsMade = limitData[1] as number;\n currentWindowStart = new Date(limitData[2] as string);\n currentWindowEnd = new Date(limitData[3] as string);\n\n // If window has expired, create new window\n if (currentWindowEnd <= now) {\n currentWindowStart = windowStart;\n currentWindowEnd = windowEnd;\n requestsMade = 0;\n rateLimitId = null;\n }\n }\n\n // Check if limit exceeded\n if (requestsMade >= config.requests) {\n return {\n allowed: false,\n remaining: 0,\n resetTime: currentWindowEnd,\n message: `Rate limit exceeded. Maximum ${config.requests} requests per hour allowed for ${plan} plan.`,\n };\n }\n\n // Increment request count\n const newRequestCount = requestsMade + 1;\n\n if (rateLimitId) {\n // Update existing record\n await db.execute({\n sql: \"UPDATE rate_limits SET requests_made = ? WHERE id = ?\",\n args: [newRequestCount, rateLimitId],\n });\n } else {\n // Create new record\n await db.execute({\n sql: `INSERT INTO rate_limits (user_id, endpoint, requests_made, window_start, window_end) \n VALUES (?, ?, ?, ?, ?)`,\n args: [userId, endpoint, newRequestCount, currentWindowStart.toISOString(), currentWindowEnd.toISOString()],\n });\n }\n\n return {\n allowed: true,\n remaining: config.requests - newRequestCount,\n resetTime: currentWindowEnd,\n };\n}\n\n// Middleware function to check rate limits\nexport async function rateLimitMiddleware(\n userId: number,\n plan: UserPlan,\n endpoint: string\n): Promise {\n const result = await checkRateLimit(userId, plan, endpoint);\n\n if (!result.allowed) {\n return new Response(\n JSON.stringify({\n error: \"Rate limit exceeded\",\n message: result.message,\n resetTime: result.resetTime.toISOString(),\n }),\n {\n status: 429,\n headers: {\n \"Content-Type\": \"application/json\",\n \"X-RateLimit-Limit\": getRateLimitConfig(plan).requests.toString(),\n \"X-RateLimit-Remaining\": \"0\",\n \"X-RateLimit-Reset\": Math.ceil(result.resetTime.getTime() / 1000).toString(),\n \"Retry-After\": Math.ceil((result.resetTime.getTime() - Date.now()) / 1000).toString(),\n },\n }\n );\n }\n\n // Add rate limit headers to successful responses\n return null; // No error, continue with request\n}\n\n// Get rate limit headers for successful responses\nexport function getRateLimitHeaders(result: RateLimitResult, plan: UserPlan): HeadersInit {\n const config = getRateLimitConfig(plan);\n \n return {\n \"X-RateLimit-Limit\": config.requests.toString(),\n \"X-RateLimit-Remaining\": result.remaining.toString(),\n \"X-RateLimit-Reset\": Math.ceil(result.resetTime.getTime() / 1000).toString(),\n };\n}\n\n// Get current rate limit status for a user\nexport async function getRateLimitStatus(\n userId: number,\n plan: UserPlan,\n endpoint?: string\n): Promise<{ [endpoint: string]: RateLimitResult }> {\n const db = getDatabase();\n const now = new Date();\n\n let query = `SELECT endpoint, requests_made, window_start, window_end \n FROM rate_limits \n WHERE user_id = ? AND window_end > ?`;\n const args: any[] = [userId, now.toISOString()];\n\n if (endpoint) {\n query += \" AND endpoint = ?\";\n args.push(endpoint);\n }\n\n const result = await db.execute({ sql: query, args });\n const config = getRateLimitConfig(plan);\n const status: { [endpoint: string]: RateLimitResult } = {};\n\n for (const row of result.rows) {\n const endpointName = row[0] as string;\n const requestsMade = row[1] as number;\n const windowEnd = new Date(row[3] as string);\n\n status[endpointName] = {\n allowed: requestsMade < config.requests,\n remaining: Math.max(0, config.requests - requestsMade),\n resetTime: windowEnd,\n };\n }\n\n return status;\n}\n\n// Reset rate limits for a user (admin function)\nexport async function resetUserRateLimits(userId: number): Promise {\n const db = getDatabase();\n \n await db.execute({\n sql: \"DELETE FROM rate_limits WHERE user_id = ?\",\n args: [userId],\n });\n}",[],[309],{"_104":317,"_95":318,"_153":319,"_155":-5,"_156":138,"_157":320,"_159":160,"_106":321,"_100":32,"_162":195,"_164":322,"_166":22,"_167":-5,"_168":323,"_98":324},"8e60e52c-8e97-11f0-98a6-0224a6c84d84","searchApi.ts","8e60e5ea-8e97-11f0-98a6-0224a6c84d84",14,["D",1757544202835],"// searchApi.ts - Global search API endpoints for RankWatch\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\nimport { extractUserFromToken } from \"./auth.ts\";\nimport { getDatabase } from \"./database.ts\";\nimport { \n createSuccessResponse, \n createErrorResponse, \n logRequest,\n parsePaginationParams,\n createPaginationMeta,\n parseSearchQuery\n} from \"./utils.ts\";\nimport { checkRateLimit, getRateLimitHeaders } from \"./rateLimit.ts\";\n\nconst app = new Hono();\n\n// CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\"],\n}));\n\n// Middleware to extract and validate user\nasync function authenticateUser(c: any) {\n const authHeader = c.req.header(\"authorization\");\n if (!authHeader) {\n throw new Error(\"Authorization header required\");\n }\n return await extractUserFromToken(authHeader);\n}\n\n// Search projects\nasync function searchProjects(userId: number, query: string, limit: number): Promise {\n const db = getDatabase();\n \n const result = await db.execute({\n sql: `SELECT id, name, domain, location, device, created_at, updated_at \n FROM projects \n WHERE user_id = ? AND deleted_at IS NULL \n AND (name LIKE ? OR domain LIKE ?) \n ORDER BY \n CASE \n WHEN name LIKE ? THEN 1 \n WHEN domain LIKE ? THEN 2 \n ELSE 3 \n END,\n created_at DESC \n LIMIT ?`,\n args: [userId, `%${query}%`, `%${query}%`, `%${query}%`, `%${query}%`, limit],\n });\n\n return result.rows.map(row => ({\n type: \"project\",\n id: row[0],\n name: row[1],\n domain: row[2],\n location: row[3],\n device: row[4],\n created_at: row[5],\n updated_at: row[6],\n }));\n}\n\n// Search keywords\nasync function searchKeywords(userId: number, query: string, limit: number): Promise {\n const db = getDatabase();\n \n const result = await db.execute({\n sql: `SELECT k.id, k.keyword, k.target_url, k.active, k.created_at, k.updated_at,\n p.id as project_id, p.name as project_name, p.domain as project_domain\n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL \n AND (k.keyword LIKE ? OR k.target_url LIKE ?) \n ORDER BY \n CASE \n WHEN k.keyword LIKE ? THEN 1 \n WHEN k.target_url LIKE ? THEN 2 \n ELSE 3 \n END,\n k.created_at DESC \n LIMIT ?`,\n args: [userId, `%${query}%`, `%${query}%`, `%${query}%`, `%${query}%`, limit],\n });\n\n return result.rows.map(row => ({\n type: \"keyword\",\n id: row[0],\n keyword: row[1],\n target_url: row[2],\n active: row[3],\n created_at: row[4],\n updated_at: row[5],\n project: {\n id: row[6],\n name: row[7],\n domain: row[8],\n },\n }));\n}\n\n// Search ranking results\nasync function searchRankings(userId: number, query: string, limit: number): Promise {\n const db = getDatabase();\n \n const result = await db.execute({\n sql: `SELECT DISTINCT r.id, r.position, r.url, r.title, r.checked_at,\n k.id as keyword_id, k.keyword,\n p.id as project_id, p.name as project_name, p.domain as project_domain\n FROM rankings r \n JOIN keywords k ON r.keyword_id = k.id \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL \n AND (r.title LIKE ? OR r.url LIKE ? OR k.keyword LIKE ?) \n ORDER BY r.checked_at DESC \n LIMIT ?`,\n args: [userId, `%${query}%`, `%${query}%`, `%${query}%`, limit],\n });\n\n return result.rows.map(row => ({\n type: \"ranking\",\n id: row[0],\n position: row[1],\n url: row[2],\n title: row[3],\n checked_at: row[4],\n keyword: {\n id: row[5],\n keyword: row[6],\n },\n project: {\n id: row[7],\n name: row[8],\n domain: row[9],\n },\n }));\n}\n\n// GET /search - Global search across all user data\napp.get(\"/search\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/search\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const url = new URL(c.req.url);\n const searchQuery = parseSearchQuery(url);\n \n if (!searchQuery) {\n return createErrorResponse(\"Search query 'q' parameter is required\", 400);\n }\n\n if (searchQuery.length < 2) {\n return createErrorResponse(\"Search query must be at least 2 characters long\", 400);\n }\n\n // Get search type filter\n const type = url.searchParams.get(\"type\") || \"all\";\n const validTypes = [\"all\", \"projects\", \"keywords\", \"rankings\"];\n \n if (!validTypes.includes(type)) {\n return createErrorResponse(`Invalid type. Must be one of: ${validTypes.join(\", \")}`, 400);\n }\n\n const { page, limit } = parsePaginationParams(url);\n const maxResultsPerType = Math.ceil(limit / 3); // Distribute results across types\n\n let results: any[] = [];\n let totalFound = 0;\n\n // Search based on type filter\n if (type === \"all\" || type === \"projects\") {\n const projects = await searchProjects(user.id, searchQuery, maxResultsPerType);\n results.push(...projects);\n totalFound += projects.length;\n }\n\n if (type === \"all\" || type === \"keywords\") {\n const keywords = await searchKeywords(user.id, searchQuery, maxResultsPerType);\n results.push(...keywords);\n totalFound += keywords.length;\n }\n\n if (type === \"all\" || type === \"rankings\") {\n const rankings = await searchRankings(user.id, searchQuery, maxResultsPerType);\n results.push(...rankings);\n totalFound += rankings.length;\n }\n\n // Sort results by relevance (exact matches first)\n results.sort((a, b) => {\n const aExact = (\n a.name?.toLowerCase().includes(searchQuery.toLowerCase()) ||\n a.keyword?.toLowerCase().includes(searchQuery.toLowerCase()) ||\n a.title?.toLowerCase().includes(searchQuery.toLowerCase())\n ) ? 1 : 0;\n \n const bExact = (\n b.name?.toLowerCase().includes(searchQuery.toLowerCase()) ||\n b.keyword?.toLowerCase().includes(searchQuery.toLowerCase()) ||\n b.title?.toLowerCase().includes(searchQuery.toLowerCase())\n ) ? 1 : 0;\n \n return bExact - aExact;\n });\n\n // Apply pagination to sorted results\n const offset = (page - 1) * limit;\n const paginatedResults = results.slice(offset, offset + limit);\n\n // Group results by type for better presentation\n const groupedResults = {\n projects: paginatedResults.filter(r => r.type === \"project\"),\n keywords: paginatedResults.filter(r => r.type === \"keyword\"),\n rankings: paginatedResults.filter(r => r.type === \"ranking\"),\n };\n\n const meta = {\n query: searchQuery,\n type: type,\n total_found: totalFound,\n page: page,\n limit: limit,\n results_breakdown: {\n projects: groupedResults.projects.length,\n keywords: groupedResults.keywords.length,\n rankings: groupedResults.rankings.length,\n },\n };\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse({\n results: type === \"all\" ? groupedResults : paginatedResults,\n summary: {\n total_found: totalFound,\n showing: paginatedResults.length,\n query: searchQuery,\n type: type,\n },\n }, `Found ${totalFound} results for \"${searchQuery}\"`, meta);\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Global search error:\", error);\n return createErrorResponse(error.message || \"Failed to perform search\", 401);\n }\n});\n\n// GET /search/suggestions - Get search suggestions/autocomplete\napp.get(\"/search/suggestions\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/search/suggestions\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n const url = new URL(c.req.url);\n const searchQuery = parseSearchQuery(url);\n \n if (!searchQuery) {\n return createErrorResponse(\"Search query 'q' parameter is required\", 400);\n }\n\n if (searchQuery.length < 1) {\n return createErrorResponse(\"Search query must be at least 1 character long\", 400);\n }\n\n const maxSuggestions = Math.min(20, parseInt(url.searchParams.get(\"limit\") || \"10\"));\n const db = getDatabase();\n\n // Get suggestions from projects\n const projectSuggestions = await db.execute({\n sql: `SELECT DISTINCT name as suggestion, 'project' as type, name as display\n FROM projects \n WHERE user_id = ? AND deleted_at IS NULL \n AND name LIKE ? \n ORDER BY name \n LIMIT ?`,\n args: [user.id, `${searchQuery}%`, Math.ceil(maxSuggestions / 3)],\n });\n\n // Get suggestions from keywords\n const keywordSuggestions = await db.execute({\n sql: `SELECT DISTINCT k.keyword as suggestion, 'keyword' as type, k.keyword as display\n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL \n AND k.keyword LIKE ? \n ORDER BY k.keyword \n LIMIT ?`,\n args: [user.id, `${searchQuery}%`, Math.ceil(maxSuggestions / 3)],\n });\n\n // Get suggestions from domains\n const domainSuggestions = await db.execute({\n sql: `SELECT DISTINCT domain as suggestion, 'domain' as type, domain as display\n FROM projects \n WHERE user_id = ? AND deleted_at IS NULL \n AND domain LIKE ? \n ORDER BY domain \n LIMIT ?`,\n args: [user.id, `${searchQuery}%`, Math.ceil(maxSuggestions / 3)],\n });\n\n // Combine and format suggestions\n const suggestions = [\n ...projectSuggestions.rows.map(row => ({ suggestion: row[0], type: row[1], display: row[2] })),\n ...keywordSuggestions.rows.map(row => ({ suggestion: row[0], type: row[1], display: row[2] })),\n ...domainSuggestions.rows.map(row => ({ suggestion: row[0], type: row[1], display: row[2] })),\n ];\n\n // Remove duplicates and limit results\n const uniqueSuggestions = suggestions\n .filter((suggestion, index, self) => \n index === self.findIndex(s => s.suggestion === suggestion.suggestion)\n )\n .slice(0, maxSuggestions);\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse({\n query: searchQuery,\n suggestions: uniqueSuggestions,\n count: uniqueSuggestions.length,\n }, `Found ${uniqueSuggestions.length} suggestions for \"${searchQuery}\"`);\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Search suggestions error:\", error);\n return createErrorResponse(error.message || \"Failed to get search suggestions\", 401);\n }\n});\n\n// GET /search/recent - Get recent search history (client-side implementation recommended)\napp.get(\"/search/recent\", async (c) => {\n try {\n const user = await authenticateUser(c);\n logRequest(c.req.raw, user.id);\n\n // Check rate limit\n const rateLimitResult = await checkRateLimit(user.id, user.plan, \"/search/recent\");\n if (!rateLimitResult.allowed) {\n return createErrorResponse(\"Rate limit exceeded\", 429);\n }\n\n // Since we don't store search history in the database for privacy,\n // this endpoint returns commonly accessed items instead\n const db = getDatabase();\n \n // Get recently created/updated items\n const recentItems = await db.execute({\n sql: `\n SELECT 'project' as type, id, name as title, domain as subtitle, updated_at \n FROM projects \n WHERE user_id = ? AND deleted_at IS NULL \n UNION ALL\n SELECT 'keyword' as type, k.id, k.keyword as title, p.name as subtitle, k.updated_at\n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? AND k.deleted_at IS NULL AND p.deleted_at IS NULL \n ORDER BY updated_at DESC \n LIMIT 10`,\n args: [user.id, user.id],\n });\n\n const recent = recentItems.rows.map(row => ({\n type: row[0],\n id: row[1],\n title: row[2],\n subtitle: row[3],\n updated_at: row[4],\n }));\n\n const headers = getRateLimitHeaders(rateLimitResult, user.plan);\n const response = createSuccessResponse({\n recent_items: recent,\n count: recent.length,\n note: \"This shows recently updated items. For search history, implement client-side storage.\",\n }, \"Recent items retrieved successfully\");\n \n // Add rate limit headers\n Object.entries(headers).forEach(([key, value]) => {\n response.headers.set(key, value);\n });\n\n return response;\n\n } catch (error) {\n console.error(\"Recent search error:\", error);\n return createErrorResponse(error.message || \"Failed to get recent items\", 401);\n }\n});\n\n// Export the Hono app\nexport default {\n fetch: app.fetch,\n};",[],[318],{"_104":326,"_95":327,"_153":328,"_155":-5,"_156":138,"_157":329,"_159":160,"_106":330,"_100":32,"_162":195,"_164":331,"_166":22,"_167":-5,"_168":332,"_98":333},"235980cc-8e97-11f0-9aab-0224a6c84d84","serperClient.ts","2359818a-8e97-11f0-9aab-0224a6c84d84",12,["D",1757544023268],"// serperClient.ts - Serper.dev API client for Google search ranking\n\nimport { config } from \"./config.ts\";\nimport { retryWithBackoff, sleep } from \"./utils.ts\";\n\n// Serper API response interfaces\nexport interface SerperResult {\n position: number;\n title: string;\n link: string;\n displayLink: string;\n snippet: string;\n date?: string;\n sitelinks?: {\n title: string;\n link: string;\n }[];\n}\n\nexport interface SerperSearchResponse {\n searchParameters: {\n q: string;\n type: string;\n engine: string;\n gl?: string;\n hl?: string;\n device?: string;\n num?: number;\n };\n organic: SerperResult[];\n answerBox?: any;\n peopleAlsoAsk?: any[];\n relatedSearches?: any[];\n knowledgeGraph?: any;\n pagination?: {\n current: number;\n next?: string;\n other: any[];\n };\n searchInformation: {\n totalResults: string;\n timeTaken: number;\n formattedTotalResults: string;\n formattedTimeTaken: string;\n };\n}\n\nexport interface RankingResult {\n keyword: string;\n position: number | null;\n url: string | null;\n title: string | null;\n snippet: string | null;\n found: boolean;\n serp_features: {\n answer_box: boolean;\n knowledge_graph: boolean;\n featured_snippet: boolean;\n local_pack: boolean;\n related_questions: boolean;\n sitelinks: boolean;\n };\n total_results: string;\n search_time: number;\n checked_at: string;\n error?: string;\n}\n\n// Rate limiting for Serper API calls\nconst serperRateLimit = {\n requests: 0,\n windowStart: Date.now(),\n maxRequests: 100, // Conservative limit\n windowMs: 60 * 1000, // 1 minute\n};\n\nfunction checkSerperRateLimit(): boolean {\n const now = Date.now();\n \n // Reset window if expired\n if (now - serperRateLimit.windowStart >= serperRateLimit.windowMs) {\n serperRateLimit.requests = 0;\n serperRateLimit.windowStart = now;\n }\n \n return serperRateLimit.requests < serperRateLimit.maxRequests;\n}\n\nfunction incrementSerperRateLimit(): void {\n serperRateLimit.requests++;\n}\n\n// Make request to Serper API\nasync function makeSerperRequest(\n keyword: string,\n location: string = \"us\",\n device: string = \"desktop\",\n num: number = 100\n): Promise {\n if (!config.serper.apiKey) {\n throw new Error(\"Serper API key not configured\");\n }\n\n // Check rate limit\n if (!checkSerperRateLimit()) {\n throw new Error(\"Serper API rate limit exceeded\");\n }\n\n const params = {\n q: keyword,\n gl: location, // Geographic location\n hl: \"en\", // Language\n device: device === \"mobile\" ? \"mobile\" : \"desktop\",\n num: num, // Number of results (max 100)\n type: \"search\",\n };\n\n const response = await fetch(\"https://google.serper.dev/search\", {\n method: \"POST\",\n headers: {\n \"X-API-KEY\": config.serper.apiKey,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify(params),\n });\n\n incrementSerperRateLimit();\n\n if (!response.ok) {\n const errorText = await response.text();\n throw new Error(`Serper API error: ${response.status} - ${errorText}`);\n }\n\n const data = await response.json();\n return data as SerperSearchResponse;\n}\n\n// Find ranking position for a specific domain\nexport function findDomainRanking(\n results: SerperResult[],\n targetDomain: string,\n targetUrl?: string\n): { position: number; result: SerperResult } | null {\n // Clean domain for comparison\n const cleanTargetDomain = targetDomain.replace(/^https?:\\/\\//, \"\").replace(/^www\\./, \"\").toLowerCase();\n \n for (let i = 0; i < results.length; i++) {\n const result = results[i];\n const resultDomain = result.displayLink.replace(/^www\\./, \"\").toLowerCase();\n \n // Check domain match\n if (resultDomain === cleanTargetDomain || resultDomain.endsWith(`.${cleanTargetDomain}`)) {\n // If specific URL provided, check for URL match\n if (targetUrl) {\n const cleanTargetUrl = targetUrl.replace(/^https?:\\/\\//, \"\").replace(/\\/$/, \"\").toLowerCase();\n const cleanResultUrl = result.link.replace(/^https?:\\/\\//, \"\").replace(/\\/$/, \"\").toLowerCase();\n \n // Check if URLs match (exact or path contains)\n if (cleanResultUrl === cleanTargetUrl || cleanResultUrl.includes(cleanTargetUrl)) {\n return { position: result.position || i + 1, result };\n }\n } else {\n // Domain match is sufficient\n return { position: result.position || i + 1, result };\n }\n }\n }\n \n return null;\n}\n\n// Detect SERP features\nfunction detectSerpFeatures(data: SerperSearchResponse): RankingResult['serp_features'] {\n return {\n answer_box: !!data.answerBox,\n knowledge_graph: !!data.knowledgeGraph,\n featured_snippet: !!data.organic?.some((result: any) => result.snippet?.includes(\"...\")),\n local_pack: !!data.organic?.some((result: any) => result.type === \"local\"),\n related_questions: !!(data.peopleAlsoAsk && data.peopleAlsoAsk.length > 0),\n sitelinks: !!data.organic?.some((result: any) => result.sitelinks && result.sitelinks.length > 0),\n };\n}\n\n// Check keyword ranking\nexport async function checkKeywordRanking(\n keyword: string,\n domain: string,\n targetUrl?: string,\n location: string = \"us\",\n device: string = \"desktop\"\n): Promise {\n const checkedAt = new Date().toISOString();\n \n try {\n // Make request with retry logic\n const data = await retryWithBackoff(\n () => makeSerperRequest(keyword, location, device),\n 3, // max retries\n 1000 // base delay\n );\n\n // Find domain ranking\n const ranking = findDomainRanking(data.organic || [], domain, targetUrl);\n \n // Detect SERP features\n const serpFeatures = detectSerpFeatures(data);\n\n const result: RankingResult = {\n keyword,\n position: ranking?.position || null,\n url: ranking?.result.link || null,\n title: ranking?.result.title || null,\n snippet: ranking?.result.snippet || null,\n found: !!ranking,\n serp_features: serpFeatures,\n total_results: data.searchInformation?.totalResults || \"0\",\n search_time: data.searchInformation?.timeTaken || 0,\n checked_at: checkedAt,\n };\n\n return result;\n\n } catch (error) {\n console.error(`Error checking ranking for \"${keyword}\":`, error);\n \n return {\n keyword,\n position: null,\n url: null,\n title: null,\n snippet: null,\n found: false,\n serp_features: {\n answer_box: false,\n knowledge_graph: false,\n featured_snippet: false,\n local_pack: false,\n related_questions: false,\n sitelinks: false,\n },\n total_results: \"0\",\n search_time: 0,\n checked_at: checkedAt,\n error: error.message,\n };\n }\n}\n\n// Batch check multiple keywords with delay between requests\nexport async function batchCheckKeywords(\n keywords: Array<{\n keyword: string;\n domain: string;\n target_url?: string;\n location?: string;\n device?: string;\n }>,\n delayMs: number = 1000\n): Promise {\n const results: RankingResult[] = [];\n \n for (let i = 0; i < keywords.length; i++) {\n const keywordData = keywords[i];\n \n try {\n const result = await checkKeywordRanking(\n keywordData.keyword,\n keywordData.domain,\n keywordData.target_url,\n keywordData.location || \"us\",\n keywordData.device || \"desktop\"\n );\n \n results.push(result);\n \n // Add delay between requests to be respectful\n if (i < keywords.length - 1) {\n await sleep(delayMs);\n }\n \n } catch (error) {\n console.error(`Batch check error for keyword \"${keywordData.keyword}\":`, error);\n \n results.push({\n keyword: keywordData.keyword,\n position: null,\n url: null,\n title: null,\n snippet: null,\n found: false,\n serp_features: {\n answer_box: false,\n knowledge_graph: false,\n featured_snippet: false,\n local_pack: false,\n related_questions: false,\n sitelinks: false,\n },\n total_results: \"0\",\n search_time: 0,\n checked_at: new Date().toISOString(),\n error: error.message,\n });\n }\n }\n \n return results;\n}\n\n// Get competitor analysis for a keyword\nexport async function getCompetitorAnalysis(\n keyword: string,\n location: string = \"us\",\n device: string = \"desktop\",\n topN: number = 10\n): Promise<{\n keyword: string;\n competitors: Array<{\n position: number;\n domain: string;\n url: string;\n title: string;\n snippet: string;\n }>;\n checked_at: string;\n}> {\n const data = await makeSerperRequest(keyword, location, device, Math.min(topN, 20));\n \n const competitors = (data.organic || [])\n .slice(0, topN)\n .map((result, index) => ({\n position: result.position || index + 1,\n domain: result.displayLink,\n url: result.link,\n title: result.title,\n snippet: result.snippet,\n }));\n\n return {\n keyword,\n competitors,\n checked_at: new Date().toISOString(),\n };\n}\n\n// Validate Serper API configuration\nexport function validateSerperConfig(): { valid: boolean; error?: string } {\n if (!config.serper.apiKey) {\n return { valid: false, error: \"Serper API key not configured\" };\n }\n \n if (!config.serper.baseUrl) {\n return { valid: false, error: \"Serper base URL not configured\" };\n }\n \n return { valid: true };\n}\n\n// Get current rate limit status\nexport function getSerperRateLimitStatus(): {\n requests: number;\n maxRequests: number;\n remaining: number;\n resetTime: Date;\n} {\n const now = Date.now();\n const resetTime = new Date(serperRateLimit.windowStart + serperRateLimit.windowMs);\n \n return {\n requests: serperRateLimit.requests,\n maxRequests: serperRateLimit.maxRequests,\n remaining: Math.max(0, serperRateLimit.maxRequests - serperRateLimit.requests),\n resetTime,\n };\n}",[],[327],{"_104":335,"_95":99,"_153":336,"_155":-5,"_156":138,"_157":337,"_159":160,"_106":338,"_100":32,"_162":195,"_164":339,"_166":22,"_167":-5,"_168":340,"_98":341},"e433233e-8e97-11f0-870d-0224a6c84d84","e43323e8-8e97-11f0-870d-0224a6c84d84",16,["D",1757544346815],"// swaggerApi.ts - Swagger UI documentation for RankWatch API\n\nimport { Hono } from \"https://deno.land/x/hono@v3.12.6/mod.ts\";\nimport { cors } from \"https://deno.land/x/hono@v3.12.6/middleware.ts\";\n\nconst app = new Hono();\n\n// CORS middleware\napp.use(\"*\", cors({\n origin: \"*\",\n allowMethods: [\"GET\", \"POST\", \"PUT\", \"DELETE\", \"OPTIONS\"],\n allowHeaders: [\"Content-Type\", \"Authorization\"],\n}));\n\n// OpenAPI 3.0 Specification for RankWatch API\nconst openApiSpec = {\n openapi: \"3.0.3\",\n info: {\n title: \"RankWatch API\",\n description: \"A powerful keyword ranking monitoring API built with Val Town. Track your SEO rankings with precision and reliability.\",\n version: \"1.0.0\",\n contact: {\n name: \"RankWatch Support\",\n email: \"support@rankwatch.dev\",\n },\n license: {\n name: \"MIT\",\n url: \"https://opensource.org/licenses/MIT\",\n },\n },\n servers: [\n {\n url: \"https://your-val.val.town\",\n description: \"Production server\",\n },\n ],\n tags: [\n {\n name: \"Authentication\",\n description: \"User authentication and authorization\",\n },\n {\n name: \"Projects\",\n description: \"Project management operations\",\n },\n {\n name: \"Keywords\",\n description: \"Keyword management operations\",\n },\n {\n name: \"Rankings\",\n description: \"Ranking data and analysis\",\n },\n {\n name: \"Search\",\n description: \"Global search functionality\",\n },\n {\n name: \"Export\",\n description: \"Data export operations\",\n },\n ],\n paths: {\n \"/auth/register\": {\n post: {\n tags: [\"Authentication\"],\n summary: \"Register new user\",\n description: \"Create a new user account with email and password\",\n requestBody: {\n required: true,\n content: {\n \"application/json\": {\n schema: {\n type: \"object\",\n required: [\"email\", \"password\"],\n properties: {\n email: {\n type: \"string\",\n format: \"email\",\n example: \"user@example.com\",\n },\n password: {\n type: \"string\",\n minLength: 8,\n example: \"SecurePass123\",\n },\n plan: {\n type: \"string\",\n enum: [\"freemium\", \"unlimited\"],\n default: \"freemium\",\n },\n },\n },\n },\n },\n },\n responses: {\n 200: {\n description: \"User registered successfully\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/AuthResponse\",\n },\n },\n },\n },\n 400: {\n description: \"Invalid input or user already exists\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/ErrorResponse\",\n },\n },\n },\n },\n },\n },\n },\n \"/auth/login\": {\n post: {\n tags: [\"Authentication\"],\n summary: \"Login user\",\n description: \"Authenticate user with email and password\",\n requestBody: {\n required: true,\n content: {\n \"application/json\": {\n schema: {\n type: \"object\",\n required: [\"email\", \"password\"],\n properties: {\n email: {\n type: \"string\",\n format: \"email\",\n example: \"user@example.com\",\n },\n password: {\n type: \"string\",\n example: \"SecurePass123\",\n },\n },\n },\n },\n },\n },\n responses: {\n 200: {\n description: \"Login successful\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/AuthResponse\",\n },\n },\n },\n },\n 401: {\n description: \"Invalid credentials\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/ErrorResponse\",\n },\n },\n },\n },\n },\n },\n },\n \"/auth/me\": {\n get: {\n tags: [\"Authentication\"],\n summary: \"Get current user info\",\n description: \"Retrieve information about the authenticated user\",\n security: [{ bearerAuth: [] }],\n responses: {\n 200: {\n description: \"User information retrieved\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/UserResponse\",\n },\n },\n },\n },\n 401: {\n description: \"Unauthorized\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/ErrorResponse\",\n },\n },\n },\n },\n },\n },\n },\n \"/projects\": {\n get: {\n tags: [\"Projects\"],\n summary: \"List projects\",\n description: \"Get all projects for the authenticated user with pagination\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"page\",\n in: \"query\",\n schema: { type: \"integer\", minimum: 1, default: 1 },\n description: \"Page number\",\n },\n {\n name: \"limit\",\n in: \"query\",\n schema: { type: \"integer\", minimum: 1, maximum: 100, default: 10 },\n description: \"Items per page\",\n },\n {\n name: \"q\",\n in: \"query\",\n schema: { type: \"string\" },\n description: \"Search query\",\n },\n ],\n responses: {\n 200: {\n description: \"Projects retrieved successfully\",\n content: {\n \"application/json\": {\n schema: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: {\n type: \"array\",\n items: { $ref: \"#/components/schemas/Project\" },\n },\n meta: { $ref: \"#/components/schemas/PaginationMeta\" },\n },\n },\n ],\n },\n },\n },\n },\n },\n },\n post: {\n tags: [\"Projects\"],\n summary: \"Create project\",\n description: \"Create a new project\",\n security: [{ bearerAuth: [] }],\n requestBody: {\n required: true,\n content: {\n \"application/json\": {\n schema: {\n type: \"object\",\n required: [\"name\", \"domain\"],\n properties: {\n name: {\n type: \"string\",\n example: \"My Website\",\n },\n domain: {\n type: \"string\",\n example: \"example.com\",\n },\n location: {\n type: \"string\",\n default: \"us\",\n example: \"us\",\n },\n device: {\n type: \"string\",\n enum: [\"desktop\", \"mobile\"],\n default: \"desktop\",\n },\n },\n },\n },\n },\n },\n responses: {\n 200: {\n description: \"Project created successfully\",\n content: {\n \"application/json\": {\n schema: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: { $ref: \"#/components/schemas/Project\" },\n },\n },\n ],\n },\n },\n },\n },\n 403: {\n description: \"Project limit reached\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/ErrorResponse\",\n },\n },\n },\n },\n },\n },\n },\n \"/projects/{id}\": {\n get: {\n tags: [\"Projects\"],\n summary: \"Get project\",\n description: \"Get a specific project by ID\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"id\",\n in: \"path\",\n required: true,\n schema: { type: \"integer\" },\n description: \"Project ID\",\n },\n ],\n responses: {\n 200: {\n description: \"Project retrieved successfully\",\n content: {\n \"application/json\": {\n schema: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: { $ref: \"#/components/schemas/Project\" },\n },\n },\n ],\n },\n },\n },\n },\n 404: {\n description: \"Project not found\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/ErrorResponse\",\n },\n },\n },\n },\n },\n },\n put: {\n tags: [\"Projects\"],\n summary: \"Update project\",\n description: \"Update an existing project\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"id\",\n in: \"path\",\n required: true,\n schema: { type: \"integer\" },\n description: \"Project ID\",\n },\n ],\n requestBody: {\n required: true,\n content: {\n \"application/json\": {\n schema: {\n type: \"object\",\n properties: {\n name: { type: \"string\" },\n domain: { type: \"string\" },\n location: { type: \"string\" },\n device: { type: \"string\", enum: [\"desktop\", \"mobile\"] },\n },\n },\n },\n },\n },\n responses: {\n 200: {\n description: \"Project updated successfully\",\n content: {\n \"application/json\": {\n schema: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: { $ref: \"#/components/schemas/Project\" },\n },\n },\n ],\n },\n },\n },\n },\n },\n },\n delete: {\n tags: [\"Projects\"],\n summary: \"Delete project\",\n description: \"Soft delete a project and all its keywords\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"id\",\n in: \"path\",\n required: true,\n schema: { type: \"integer\" },\n description: \"Project ID\",\n },\n ],\n responses: {\n 200: {\n description: \"Project deleted successfully\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/SuccessResponse\",\n },\n },\n },\n },\n },\n },\n },\n \"/projects/{projectId}/keywords\": {\n get: {\n tags: [\"Keywords\"],\n summary: \"List keywords\",\n description: \"Get all keywords for a project\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"projectId\",\n in: \"path\",\n required: true,\n schema: { type: \"integer\" },\n description: \"Project ID\",\n },\n {\n name: \"page\",\n in: \"query\",\n schema: { type: \"integer\", minimum: 1, default: 1 },\n },\n {\n name: \"limit\",\n in: \"query\",\n schema: { type: \"integer\", minimum: 1, maximum: 100, default: 10 },\n },\n {\n name: \"q\",\n in: \"query\",\n schema: { type: \"string\" },\n description: \"Search query\",\n },\n ],\n responses: {\n 200: {\n description: \"Keywords retrieved successfully\",\n content: {\n \"application/json\": {\n schema: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: {\n type: \"array\",\n items: { $ref: \"#/components/schemas/Keyword\" },\n },\n meta: { $ref: \"#/components/schemas/PaginationMeta\" },\n },\n },\n ],\n },\n },\n },\n },\n },\n },\n post: {\n tags: [\"Keywords\"],\n summary: \"Add keyword\",\n description: \"Add a new keyword to a project\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"projectId\",\n in: \"path\",\n required: true,\n schema: { type: \"integer\" },\n description: \"Project ID\",\n },\n ],\n requestBody: {\n required: true,\n content: {\n \"application/json\": {\n schema: {\n type: \"object\",\n required: [\"keyword\"],\n properties: {\n keyword: {\n type: \"string\",\n example: \"best seo tools\",\n },\n target_url: {\n type: \"string\",\n format: \"uri\",\n example: \"https://example.com/seo-tools\",\n },\n active: {\n type: \"boolean\",\n default: true,\n },\n },\n },\n },\n },\n },\n responses: {\n 200: {\n description: \"Keyword created successfully\",\n content: {\n \"application/json\": {\n schema: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: { $ref: \"#/components/schemas/Keyword\" },\n },\n },\n ],\n },\n },\n },\n },\n 403: {\n description: \"Keyword limit reached\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/ErrorResponse\",\n },\n },\n },\n },\n },\n },\n },\n \"/keywords/{id}/rankings\": {\n get: {\n tags: [\"Rankings\"],\n summary: \"Get current ranking\",\n description: \"Get the current ranking for a keyword\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"id\",\n in: \"path\",\n required: true,\n schema: { type: \"integer\" },\n description: \"Keyword ID\",\n },\n ],\n responses: {\n 200: {\n description: \"Current ranking retrieved\",\n content: {\n \"application/json\": {\n schema: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: { $ref: \"#/components/schemas/RankingResult\" },\n },\n },\n ],\n },\n },\n },\n },\n },\n },\n },\n \"/keywords/{id}/check-now\": {\n post: {\n tags: [\"Rankings\"],\n summary: \"Force ranking check\",\n description: \"Manually trigger an immediate ranking check (unlimited plan only)\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"id\",\n in: \"path\",\n required: true,\n schema: { type: \"integer\" },\n description: \"Keyword ID\",\n },\n ],\n responses: {\n 200: {\n description: \"Ranking check completed\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/SuccessResponse\",\n },\n },\n },\n },\n 403: {\n description: \"Feature only available for unlimited plan\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/ErrorResponse\",\n },\n },\n },\n },\n },\n },\n },\n \"/search\": {\n get: {\n tags: [\"Search\"],\n summary: \"Global search\",\n description: \"Search across all user data (projects, keywords, rankings)\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"q\",\n in: \"query\",\n required: true,\n schema: { type: \"string\", minLength: 2 },\n description: \"Search query\",\n },\n {\n name: \"type\",\n in: \"query\",\n schema: {\n type: \"string\",\n enum: [\"all\", \"projects\", \"keywords\", \"rankings\"],\n default: \"all\",\n },\n description: \"Search type filter\",\n },\n {\n name: \"page\",\n in: \"query\",\n schema: { type: \"integer\", minimum: 1, default: 1 },\n },\n {\n name: \"limit\",\n in: \"query\",\n schema: { type: \"integer\", minimum: 1, maximum: 100, default: 10 },\n },\n ],\n responses: {\n 200: {\n description: \"Search results retrieved\",\n content: {\n \"application/json\": {\n schema: {\n $ref: \"#/components/schemas/SearchResponse\",\n },\n },\n },\n },\n },\n },\n },\n \"/projects/{id}/export\": {\n get: {\n tags: [\"Export\"],\n summary: \"Export project data\",\n description: \"Export project summary and keywords as CSV\",\n security: [{ bearerAuth: [] }],\n parameters: [\n {\n name: \"id\",\n in: \"path\",\n required: true,\n schema: { type: \"integer\" },\n description: \"Project ID\",\n },\n ],\n responses: {\n 200: {\n description: \"CSV file download\",\n content: {\n \"text/csv\": {\n schema: {\n type: \"string\",\n format: \"binary\",\n },\n },\n },\n headers: {\n \"Content-Disposition\": {\n description: \"Attachment filename\",\n schema: { type: \"string\" },\n },\n },\n },\n },\n },\n },\n },\n components: {\n securitySchemes: {\n bearerAuth: {\n type: \"http\",\n scheme: \"bearer\",\n bearerFormat: \"JWT\",\n },\n },\n schemas: {\n SuccessResponse: {\n type: \"object\",\n properties: {\n success: { type: \"boolean\", example: true },\n message: { type: \"string\", example: \"Operation successful\" },\n data: { type: \"object\" },\n meta: { type: \"object\" },\n },\n },\n ErrorResponse: {\n type: \"object\",\n properties: {\n success: { type: \"boolean\", example: false },\n error: { type: \"string\", example: \"Error message\" },\n details: { type: \"object\" },\n },\n },\n AuthResponse: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: {\n type: \"object\",\n properties: {\n user: { $ref: \"#/components/schemas/User\" },\n token: { type: \"string\", example: \"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...\" },\n },\n },\n },\n },\n ],\n },\n UserResponse: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: { $ref: \"#/components/schemas/User\" },\n },\n },\n ],\n },\n User: {\n type: \"object\",\n properties: {\n id: { type: \"integer\", example: 1 },\n email: { type: \"string\", format: \"email\", example: \"user@example.com\" },\n plan: { type: \"string\", enum: [\"freemium\", \"unlimited\"], example: \"freemium\" },\n created_at: { type: \"string\", format: \"date-time\", example: \"2025-01-01T00:00:00.000Z\" },\n },\n },\n Project: {\n type: \"object\",\n properties: {\n id: { type: \"integer\", example: 1 },\n name: { type: \"string\", example: \"My Website\" },\n domain: { type: \"string\", example: \"example.com\" },\n location: { type: \"string\", example: \"us\" },\n device: { type: \"string\", enum: [\"desktop\", \"mobile\"], example: \"desktop\" },\n created_at: { type: \"string\", format: \"date-time\" },\n updated_at: { type: \"string\", format: \"date-time\" },\n },\n },\n Keyword: {\n type: \"object\",\n properties: {\n id: { type: \"integer\", example: 1 },\n project_id: { type: \"integer\", example: 1 },\n keyword: { type: \"string\", example: \"best seo tools\" },\n target_url: { type: \"string\", format: \"uri\", example: \"https://example.com/seo-tools\" },\n active: { type: \"boolean\", example: true },\n created_at: { type: \"string\", format: \"date-time\" },\n updated_at: { type: \"string\", format: \"date-time\" },\n },\n },\n RankingResult: {\n type: \"object\",\n properties: {\n keyword: { $ref: \"#/components/schemas/Keyword\" },\n project: { $ref: \"#/components/schemas/Project\" },\n current_ranking: {\n type: \"object\",\n properties: {\n position: { type: \"integer\", example: 15 },\n url: { type: \"string\", example: \"https://example.com/page\" },\n title: { type: \"string\", example: \"Page Title\" },\n checked_at: { type: \"string\", format: \"date-time\" },\n },\n },\n change: { type: \"integer\", example: 3 },\n },\n },\n SearchResponse: {\n allOf: [\n { $ref: \"#/components/schemas/SuccessResponse\" },\n {\n type: \"object\",\n properties: {\n data: {\n type: \"object\",\n properties: {\n results: { type: \"object\" },\n summary: {\n type: \"object\",\n properties: {\n total_found: { type: \"integer\" },\n showing: { type: \"integer\" },\n query: { type: \"string\" },\n type: { type: \"string\" },\n },\n },\n },\n },\n },\n },\n ],\n },\n PaginationMeta: {\n type: \"object\",\n properties: {\n page: { type: \"integer\", example: 1 },\n limit: { type: \"integer\", example: 10 },\n total: { type: \"integer\", example: 50 },\n totalPages: { type: \"integer\", example: 5 },\n hasNext: { type: \"boolean\", example: true },\n hasPrev: { type: \"boolean\", example: false },\n },\n },\n },\n },\n};\n\n// GET /docs - Swagger UI HTML page\napp.get(\"/docs\", (c) => {\n const html = `\n\n\n\n \n \n \n RankWatch API Documentation\n \n \n\n\n

\n \n \n\n`;\n\n return new Response(html, {\n headers: {\n \"Content-Type\": \"text/html\",\n },\n });\n});\n\n// GET /docs/openapi.json - OpenAPI specification\napp.get(\"/docs/openapi.json\", (c) => {\n return new Response(JSON.stringify(openApiSpec, null, 2), {\n headers: {\n \"Content-Type\": \"application/json\",\n },\n });\n});\n\n// GET /docs/redoc - Alternative ReDoc documentation\napp.get(\"/docs/redoc\", (c) => {\n const html = `\n\n\n\n RankWatch API Documentation\n \n \n \n\n\n \n \n\n`;\n\n return new Response(html, {\n headers: {\n \"Content-Type\": \"text/html\",\n },\n });\n});\n\n// GET /docs/health - Documentation service health check\napp.get(\"/docs/health\", (c) => {\n return new Response(JSON.stringify({\n success: true,\n service: \"RankWatch API Documentation\",\n status: \"healthy\",\n timestamp: new Date().toISOString(),\n endpoints: {\n swagger_ui: \"/docs\",\n openapi_spec: \"/docs/openapi.json\",\n redoc: \"/docs/redoc\",\n }\n }), {\n headers: {\n \"Content-Type\": \"application/json\",\n },\n });\n});\n\n// Export the Hono app\nexport default {\n fetch: app.fetch,\n};",[],[99],{"_104":343,"_95":344,"_153":345,"_155":-5,"_156":138,"_157":346,"_159":160,"_106":347,"_100":32,"_162":195,"_164":348,"_166":22,"_167":-5,"_168":349,"_98":350},"9cf8c51a-8e96-11f0-b551-0224a6c84d84","utils.ts","9cf8c5e2-8e96-11f0-b551-0224a6c84d84",8,["D",1757543797816],"// utils.ts - Utility functions and helpers for RankWatch\n\n// Standard API response interface\nexport interface ApiResponse {\n success: boolean;\n data?: T;\n error?: string;\n message?: string;\n meta?: {\n page?: number;\n limit?: number;\n total?: number;\n [key: string]: any;\n };\n}\n\n// Create standardized success response\nexport function createSuccessResponse(\n data: T,\n message?: string,\n meta?: any\n): Response {\n const response: ApiResponse = {\n success: true,\n data,\n ...(message && { message }),\n ...(meta && { meta }),\n };\n\n return new Response(JSON.stringify(response), {\n status: 200,\n headers: { \"Content-Type\": \"application/json\" },\n });\n}\n\n// Create standardized error response\nexport function createErrorResponse(\n error: string,\n status: number = 400,\n details?: any\n): Response {\n const response: ApiResponse = {\n success: false,\n error,\n ...(details && { details }),\n };\n\n return new Response(JSON.stringify(response), {\n status,\n headers: { \"Content-Type\": \"application/json\" },\n });\n}\n\n// Validate email format\nexport function isValidEmail(email: string): boolean {\n const emailRegex = /^[^\\s@]+@[^\\s@]+\\.[^\\s@]+$/;\n return emailRegex.test(email);\n}\n\n// Validate password strength\nexport function isValidPassword(password: string): { valid: boolean; message?: string } {\n if (password.length < 8) {\n return { valid: false, message: \"Password must be at least 8 characters long\" };\n }\n \n if (!/(?=.*[a-z])(?=.*[A-Z])(?=.*\\d)/.test(password)) {\n return { \n valid: false, \n message: \"Password must contain at least one uppercase letter, one lowercase letter, and one number\" \n };\n }\n\n return { valid: true };\n}\n\n// Validate URL format\nexport function isValidUrl(url: string): boolean {\n try {\n new URL(url);\n return true;\n } catch {\n return false;\n }\n}\n\n// Validate keyword (basic validation)\nexport function isValidKeyword(keyword: string): { valid: boolean; message?: string } {\n if (!keyword || keyword.trim().length === 0) {\n return { valid: false, message: \"Keyword cannot be empty\" };\n }\n\n if (keyword.length > 255) {\n return { valid: false, message: \"Keyword must be less than 255 characters\" };\n }\n\n return { valid: true };\n}\n\n// Sanitize input string\nexport function sanitizeString(input: string): string {\n return input.trim().replace(/[<>]/g, \"\");\n}\n\n// Generate pagination metadata\nexport function createPaginationMeta(\n page: number,\n limit: number,\n total: number\n): { page: number; limit: number; total: number; totalPages: number; hasNext: boolean; hasPrev: boolean } {\n const totalPages = Math.ceil(total / limit);\n \n return {\n page,\n limit,\n total,\n totalPages,\n hasNext: page < totalPages,\n hasPrev: page > 1,\n };\n}\n\n// Parse query parameters for pagination\nexport function parsePaginationParams(url: URL): { page: number; limit: number } {\n const page = Math.max(1, parseInt(url.searchParams.get(\"page\") || \"1\"));\n const limit = Math.min(100, Math.max(1, parseInt(url.searchParams.get(\"limit\") || \"10\")));\n \n return { page, limit };\n}\n\n// Parse search query parameter\nexport function parseSearchQuery(url: URL): string {\n return url.searchParams.get(\"q\")?.trim() || \"\";\n}\n\n// Format date for API responses\nexport function formatDate(date: Date): string {\n return date.toISOString();\n}\n\n// Parse JSON body safely\nexport async function parseJsonBody(request: Request): Promise {\n try {\n const text = await request.text();\n if (!text) {\n throw new Error(\"Request body is empty\");\n }\n return JSON.parse(text);\n } catch (error) {\n throw new Error(\"Invalid JSON in request body\");\n }\n}\n\n// Validate required fields in object\nexport function validateRequiredFields(\n obj: any,\n requiredFields: string[]\n): { valid: boolean; missing?: string[] } {\n const missing: string[] = [];\n \n for (const field of requiredFields) {\n if (obj[field] === undefined || obj[field] === null || obj[field] === \"\") {\n missing.push(field);\n }\n }\n\n return {\n valid: missing.length === 0,\n ...(missing.length > 0 && { missing }),\n };\n}\n\n// Generate CSV from array of objects\nexport function arrayToCSV(data: any[], headers?: string[]): string {\n if (!data || data.length === 0) {\n return \"\";\n }\n\n const csvHeaders = headers || Object.keys(data[0]);\n const csvRows = [];\n\n // Add headers\n csvRows.push(csvHeaders.map(header => `\"${header}\"`).join(\",\"));\n\n // Add data rows\n for (const row of data) {\n const values = csvHeaders.map(header => {\n const value = row[header];\n if (value === null || value === undefined) {\n return '\"\"';\n }\n return `\"${String(value).replace(/\"/g, '\"\"')}\"`;\n });\n csvRows.push(values.join(\",\"));\n }\n\n return csvRows.join(\"\\n\");\n}\n\n// Sleep utility for delays\nexport function sleep(ms: number): Promise {\n return new Promise(resolve => setTimeout(resolve, ms));\n}\n\n// Retry function with exponential backoff\nexport async function retryWithBackoff(\n fn: () => Promise,\n maxRetries: number = 3,\n baseDelay: number = 1000\n): Promise {\n let lastError: Error;\n\n for (let attempt = 0; attempt <= maxRetries; attempt++) {\n try {\n return await fn();\n } catch (error) {\n lastError = error as Error;\n \n if (attempt === maxRetries) {\n break;\n }\n\n const delay = baseDelay * Math.pow(2, attempt);\n await sleep(delay);\n }\n }\n\n throw lastError!;\n}\n\n// Log request for debugging\nexport function logRequest(request: Request, userId?: number): void {\n const method = request.method;\n const url = new URL(request.url);\n const path = url.pathname;\n const timestamp = new Date().toISOString();\n \n console.log(`[${timestamp}] ${method} ${path} - User: ${userId || 'anonymous'}`);\n}\n\n// Soft delete timestamp\nexport function getSoftDeleteTimestamp(): string {\n return new Date().toISOString();\n}\n\n// Check if record is soft deleted\nexport function isSoftDeleted(deletedAt: string | null): boolean {\n return deletedAt !== null;\n}",[],[344],"files",[150,171,180,189,199,208,217,227,236,245,254,263,272,281,290,299,307,316,325,334,342],"counts",{"_355":32,"_356":249,"_357":32,"_358":175},"pulls","branches","forks","envVars","contents",[],{"_104":335,"_95":99,"_153":336,"_155":-5,"_156":138,"_157":337,"_159":160,"_106":362,"_100":32,"_162":195,"_164":339,"_166":22,"_167":-5,"_98":363},["D",1757544346815],[99],"upstreamProject","upstreamBranch","emptyEnvVars","isNew","flags",[],"routes/_app.x.$handle.$name.branch.$branch.version.$version.code.$",{"_93":94,"_95":96,"_97":43,"_100":101,"_98":99,"_102":372,"_148":377,"_351":441,"_163":442,"_359":444,"_445":-5,"_446":22,"_447":-5,"_448":-5,"_449":-5,"_450":451},{"_104":105,"_95":96,"_106":373,"_108":-5,"_109":22,"_110":111,"_112":72,"_113":-5,"_14":114,"_115":-5,"_116":22,"_117":-5,"_118":32,"_16":119,"_120":121,"_122":121,"_123":124,"_125":124,"_126":124,"_127":121,"_128":121,"_23":374,"_132":375,"_135":376,"_147":22},["D",1757542051489],{"_93":94,"_130":131},{"_93":94,"_134":131},{"_100":137,"_95":43,"_104":138,"_139":105,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":96,"_146":94},[378,381,384,387,390,393,396,399,402,405,408,411,414,417,420,423,426,429,432,435,438],{"_104":151,"_95":152,"_153":154,"_155":-5,"_156":138,"_157":158,"_159":160,"_106":161,"_100":32,"_162":163,"_164":165,"_166":22,"_167":-5,"_168":379,"_98":380},[],[152],{"_104":172,"_95":173,"_153":174,"_155":-5,"_156":138,"_157":175,"_159":160,"_106":176,"_100":32,"_162":163,"_164":177,"_166":22,"_167":-5,"_168":382,"_98":383},[],[173],{"_104":181,"_95":182,"_153":183,"_155":-5,"_156":138,"_157":184,"_159":160,"_106":185,"_100":32,"_162":163,"_164":186,"_166":22,"_167":-5,"_168":385,"_98":386},[],[182],{"_104":190,"_95":191,"_153":192,"_155":-5,"_156":138,"_157":193,"_159":160,"_106":194,"_100":32,"_162":195,"_164":196,"_166":22,"_167":-5,"_168":388,"_98":389},[],[191],{"_104":200,"_95":201,"_153":202,"_155":-5,"_156":138,"_157":203,"_159":160,"_106":204,"_100":32,"_162":195,"_164":205,"_166":22,"_167":-5,"_168":391,"_98":392},[],[201],{"_104":209,"_95":210,"_153":211,"_155":-5,"_156":138,"_157":212,"_159":160,"_106":213,"_100":32,"_162":195,"_164":214,"_166":22,"_167":-5,"_168":394,"_98":395},[],[210],{"_104":218,"_95":219,"_153":220,"_155":-5,"_156":138,"_157":221,"_159":160,"_106":222,"_100":32,"_162":223,"_164":224,"_166":22,"_167":-5,"_168":397,"_98":398},[],[219],{"_104":228,"_95":229,"_153":230,"_155":-5,"_156":138,"_157":231,"_159":160,"_106":232,"_100":32,"_162":223,"_164":233,"_166":22,"_167":-5,"_168":400,"_98":401},[],[229],{"_104":237,"_95":238,"_153":239,"_155":-5,"_156":138,"_157":240,"_159":160,"_106":241,"_100":32,"_162":195,"_164":242,"_166":22,"_167":-5,"_168":403,"_98":404},[],[238],{"_104":246,"_95":247,"_153":248,"_155":-5,"_156":138,"_157":249,"_159":160,"_106":250,"_100":32,"_162":163,"_164":251,"_166":22,"_167":-5,"_168":406,"_98":407},[],[247],{"_104":255,"_95":256,"_153":257,"_155":-5,"_156":138,"_157":258,"_159":160,"_106":259,"_100":32,"_162":195,"_164":260,"_166":22,"_167":-5,"_168":409,"_98":410},[],[256],{"_104":264,"_95":265,"_153":266,"_155":-5,"_156":138,"_157":267,"_159":160,"_106":268,"_100":32,"_162":195,"_164":269,"_166":22,"_167":-5,"_168":412,"_98":413},[],[265],{"_104":273,"_95":274,"_153":275,"_155":-5,"_156":138,"_157":276,"_159":160,"_106":277,"_100":32,"_162":195,"_164":278,"_166":22,"_167":-5,"_168":415,"_98":416},[],[274],{"_104":282,"_95":283,"_153":284,"_155":-5,"_156":138,"_157":285,"_159":160,"_106":286,"_100":32,"_162":195,"_164":287,"_166":22,"_167":-5,"_168":418,"_98":419},[],[283],{"_104":291,"_95":292,"_153":293,"_155":-5,"_156":138,"_157":294,"_159":160,"_106":295,"_100":32,"_162":195,"_164":296,"_166":22,"_167":-5,"_168":421,"_98":422},[],[292],{"_104":300,"_95":301,"_153":302,"_155":-5,"_156":138,"_157":101,"_159":22,"_106":303,"_100":249,"_162":195,"_164":304,"_166":22,"_167":-5,"_168":424,"_98":425},[],[301],{"_104":308,"_95":309,"_153":310,"_155":-5,"_156":138,"_157":311,"_159":160,"_106":312,"_100":32,"_162":195,"_164":313,"_166":22,"_167":-5,"_168":427,"_98":428},[],[309],{"_104":317,"_95":318,"_153":319,"_155":-5,"_156":138,"_157":320,"_159":160,"_106":321,"_100":32,"_162":195,"_164":322,"_166":22,"_167":-5,"_168":430,"_98":431},[],[318],{"_104":326,"_95":327,"_153":328,"_155":-5,"_156":138,"_157":329,"_159":160,"_106":330,"_100":32,"_162":195,"_164":331,"_166":22,"_167":-5,"_168":433,"_98":434},[],[327],{"_104":335,"_95":99,"_153":336,"_155":-5,"_156":138,"_157":337,"_159":160,"_106":338,"_100":32,"_162":195,"_164":339,"_166":22,"_167":-5,"_168":436,"_98":437},[],[99],{"_104":343,"_95":344,"_153":345,"_155":-5,"_156":138,"_157":346,"_159":160,"_106":347,"_100":32,"_162":195,"_164":348,"_166":22,"_167":-5,"_168":439,"_98":440},[],[344],[378,381,384,387,390,393,396,399,402,405,408,411,414,417,420,423,426,429,432,435,438],{"_104":335,"_95":99,"_153":336,"_155":-5,"_156":138,"_157":337,"_159":160,"_106":362,"_100":32,"_162":195,"_164":339,"_166":22,"_167":-5,"_98":443},[99],[],"upstream","showCompareCallout","pull","ahead","behind","templates",[],"actionData","errors"]