[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_108":109,"_110":111},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","mdevslabs","name","mai","path","vibe-mai-fleet.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":55,"_56":57,"_58":59,"_60":32,"_61":-5,"_62":-5,"_63":64,"_65":66,"_67":68,"_69":70,"_71":72,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":84,"_85":86,"_87":88,"_89":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/mdevslabs/mai/code/vibe-mai-fleet.ts","extension","id","0cb446ef-2e81-41fb-acd1-c98bd665257a","fileId","b7867488-a6e4-11f1-ae27-1607ee4eb77e","parentId","branchId","3345cb6c-eeb2-4b61-ab03-8e0abc2dcd73","branchVersion",310,"latest",true,"createdAt",["D",1789229654431],"version",10,"fileType","script","content","/**\n * ============================================================================\n * VIBE SOCIAL PLATFORM — mAI AGENT FLEET (vibe-mai-fleet.ts)\n * Tool declarations and execution engine for database-backed AI agents\n * ============================================================================\n */\n\nimport {\n getDb,\n getTierDailyImageLimit,\n getTierMaiTokenLimit,\n getWeekData,\n} from \"./config.ts\";\nimport { executeWebSearch } from \"./web.ts\";\n\n/**\n * Outils \"sensibles\" : ils modifient le compte ou le contenu public de\n * l'utilisateur. Ils exigent une approbation explicite sauf si le réglage\n * `mai_auto_approve_tools` a été activé dans les paramètres.\n */\nexport const SENSITIVE_TOOLS = [\n \"create_post\",\n \"delete_post\",\n \"update_profile\",\n \"follow_user\",\n \"send_message\",\n \"update_settings\",\n \"repost_post\",\n \"comment_post\",\n];\n\n/** Clamp de période partagé par les outils d'analyse (défaut 30 jours). */\nfunction parsePeriodDays(period: unknown): number {\n return period === \"7d\"\n ? 7\n : period === \"90d\"\n ? 90\n : period === \"12m\"\n ? 365\n : 30;\n}\n\nconst WEEKDAY_NAMES = [\n \"dimanche\",\n \"lundi\",\n \"mardi\",\n \"mercredi\",\n \"jeudi\",\n \"vendredi\",\n \"samedi\",\n];\n\nexport class MAIAgentFleet {\n public static assessContentSafety(\n content: string,\n ): { isSafe: boolean; toxicityScore: number; flagReason?: string } {\n const prohibitedKeywords = [\n \"haine\",\n \"violence explicite\",\n \"terrorisme\",\n \"terrorist\",\n \"cp_illegal\",\n \"doxx\",\n ];\n const lower = content.toLowerCase();\n\n for (const kw of prohibitedKeywords) {\n if (lower.includes(kw)) {\n return {\n isSafe: false,\n toxicityScore: 0.95,\n flagReason: `Terme prohibé détecté (${kw})`,\n };\n }\n }\n\n return { isSafe: true, toxicityScore: 0.02 };\n }\n\n public static async modulateText(\n opts: { text: string; tone?: string; format?: string },\n ): Promise {\n const { text, tone = \"executive\" } = opts;\n const tonePrefixes: Record = {\n executive: \"⚡ \",\n viral: \"🔥 \",\n poetic: \"✨ \",\n minimal: \"✦ \",\n };\n\n const prefix = tonePrefixes[tone] || \"\";\n return `${prefix}${text.trim()}`;\n }\n\n public static synthesizeThread(\n comments: Array<{ author: string; content: string }>,\n ): string {\n if (!comments || comments.length === 0) {\n return \"Aucun commentaire pour le moment.\";\n }\n const count = comments.length;\n const authors = [...new Set(comments.map((c) => c.author))].slice(0, 3)\n .join(\", \");\n return `Synthèse (${count} réponses) : Échanges autour des points partagés par @${authors}.`;\n }\n\n /**\n * Récupère une clé OpenRouter (variable d'environnement ou table mprojects_api_keys).\n */\n public static async getOpenRouterKey(userId: number): Promise {\n if (\n typeof (globalThis as any).Deno !== \"undefined\" &&\n (globalThis as any).Deno.env?.get(\"OPENROUTER_API_KEY\")\n ) {\n return (globalThis as any).Deno.env.get(\"OPENROUTER_API_KEY\");\n }\n if (typeof process !== \"undefined\" && process.env?.OPENROUTER_API_KEY) {\n return process.env.OPENROUTER_API_KEY;\n }\n try {\n const sql = getDb();\n const keyRows =\n await sql`SELECT api_key FROM mprojects_api_keys WHERE user_id::text = ${\n String(userId)\n }::text LIMIT 1`;\n return keyRows[0]?.api_key || \"\";\n } catch {\n return \"\";\n }\n }\n\n /**\n * Modèle mAI par défaut de l'utilisateur (réglage user_settings.mai_default_model,\n * choisi dans les paramètres ou directement dans mAI). Cache mémoire 60 s.\n */\n static userModelCache = new Map<\n string,\n { model: string; expiresAt: number }\n >();\n\n public static async getUserDefaultModel(\n userId: number | string,\n ): Promise {\n const key = String(userId);\n const cached = this.userModelCache.get(key);\n if (cached && cached.expiresAt > Date.now()) return cached.model;\n let model = \"poolside/laguna-xs-2.1:free\";\n try {\n const sql = getDb();\n const rows =\n await sql`SELECT mai_default_model FROM user_settings WHERE user_id = ${\n Number(key)\n } LIMIT 1`;\n const saved = String(rows[0]?.mai_default_model || \"\").trim();\n if (saved) model = saved;\n } catch {}\n this.userModelCache.set(key, { model, expiresAt: Date.now() + 60_000 });\n return model;\n }\n\n /**\n * Appel générique OpenRouter pour les outils textuels (traduction, reformulation...).\n * Sans `model`, utilise le modèle par défaut de l'utilisateur.\n */\n public static async callOpenRouter(\n userId: number,\n system: string,\n user: string,\n model?: string,\n ): Promise {\n const apiKey = await this.getOpenRouterKey(userId);\n if (!apiKey) return null;\n const resolvedModel = model || (await this.getUserDefaultModel(userId));\n try {\n const aiRes = await fetch(\n \"https://openrouter.ai/api/v1/chat/completions\",\n {\n method: \"POST\",\n headers: {\n Authorization: `Bearer ${apiKey}`,\n \"Content-Type\": \"application/json\",\n \"HTTP-Referer\": \"https://mai.val.run\",\n \"X-Title\": \"mAI Social Assistant\",\n },\n body: JSON.stringify({\n model: resolvedModel,\n messages: [\n { role: \"system\", content: system },\n { role: \"user\", content: user },\n ],\n }),\n },\n );\n if (!aiRes.ok) return null;\n const aiData = await aiRes.json();\n return aiData.choices?.[0]?.message?.content || null;\n } catch {\n return null;\n }\n }\n\n public static async executeTool(\n toolName: string,\n args: Record,\n userId: string | number,\n ): Promise<{ success: boolean; result: any; error?: string }> {\n const sql = getDb();\n const startTime = Date.now();\n const uid = Number(userId);\n\n try {\n let resultData: any = null;\n\n switch (toolName) {\n case \"get_account_stats\": {\n const [uRows, prRows, pCount] = await Promise.all([\n sql`SELECT id, username, email, tier, avatar_url, COALESCE(created_at, NOW()) as created_at FROM users WHERE id = ${uid} LIMIT 1`,\n sql`SELECT * FROM profiles WHERE user_id = ${uid} LIMIT 1`,\n sql`SELECT COUNT(*) as count FROM posts WHERE author_id = ${uid}`,\n ]);\n resultData = {\n user: uRows[0],\n profile: prRows[0],\n totalPosts: Number(pCount[0]?.count || 0),\n };\n break;\n }\n\n case \"create_post\": {\n const { content, format = \"micro_text\", media_url } = args;\n if (!content || !content.trim()) {\n throw new Error(\"Le contenu du post est obligatoire.\");\n }\n\n const safety = this.assessContentSafety(content);\n if (!safety.isSafe) {\n throw new Error(\n `Publication refusée par mAI : ${safety.flagReason}`,\n );\n }\n\n const inserted = await sql`\n INSERT INTO posts (author_id, content, format, created_via, toxicity_score, ai_generated)\n VALUES (${uid}, ${content.trim()}, ${format}, 'mai_agent', ${safety.toxicityScore}, TRUE)\n RETURNING *\n `;\n const newPost = inserted[0];\n\n if (media_url) {\n const cleanUrl = String(media_url).split(\"?\")[0].split(\"#\")[0];\n const ext = cleanUrl.split(\".\").pop()?.toLowerCase();\n const mediaType = ext === \"png\"\n ? \"image/png\"\n : ext === \"webp\"\n ? \"image/webp\"\n : ext === \"gif\"\n ? \"image/gif\"\n : ext === \"mp4\"\n ? \"video/mp4\"\n : \"image/jpeg\";\n await sql`\n INSERT INTO media_assets (owner_id, post_id, url, media_type, file_size_bytes, alt_text)\n VALUES (${uid}, ${newPost.id}::uuid, ${media_url}, ${mediaType}, 0, '')\n `;\n }\n\n await sql`UPDATE profiles SET posts_count = posts_count + 1 WHERE user_id = ${uid}`;\n resultData = {\n post: newPost,\n message: \"Post publié avec succès sur Vibe !\",\n };\n break;\n }\n\n case \"delete_post\": {\n const { post_id } = args;\n if (!post_id) throw new Error(\"post_id est requis.\");\n\n const del = await sql`\n DELETE FROM posts WHERE id = ${post_id}::uuid AND author_id = ${uid} RETURNING id\n `;\n if (del.length === 0) {\n throw new Error(\n \"Publication introuvable ou vous n'êtes pas l'auteur.\",\n );\n }\n await sql`UPDATE profiles SET posts_count = GREATEST(0, posts_count - 1) WHERE user_id = ${uid}`;\n resultData = {\n deletedPostId: post_id,\n message: \"Publication supprimée avec succès.\",\n };\n break;\n }\n\n case \"search_posts\": {\n const { query, limit = 10 } = args;\n const rows = await sql`\n SELECT p.*, pr.display_name, pr.avatar_url, u.username\n FROM posts p\n JOIN users u ON u.id = p.author_id\n LEFT JOIN profiles pr ON pr.user_id = u.id\n WHERE p.content ILIKE ('%' || ${query} || '%')\n ORDER BY p.published_at DESC\n LIMIT ${limit}\n `;\n resultData = { query, resultsCount: rows.length, posts: rows };\n break;\n }\n\n case \"generate_vibe_image\": {\n const { prompt, aspect_ratio = \"1:1\" } = args;\n const uRows =\n await sql`SELECT tier FROM users WHERE id = ${uid} LIMIT 1`;\n const tier = uRows[0]?.tier || \"Free\";\n const maxImages = getTierDailyImageLimit(tier);\n\n const todayRows = await sql`\n SELECT images_generated FROM daily_image_usage \n WHERE user_id = ${uid} AND usage_date = CURRENT_DATE LIMIT 1\n `;\n const currentCount = todayRows[0]?.images_generated || 0;\n\n if (currentCount >= maxImages) {\n throw new Error(\n `Quota journalier d'images atteint (${currentCount}/${maxImages} pour le forfait ${tier}).`,\n );\n }\n\n await sql`\n INSERT INTO daily_image_usage (user_id, usage_date, images_generated)\n VALUES (${uid}, CURRENT_DATE, 1)\n ON CONFLICT (user_id, usage_date)\n DO UPDATE SET images_generated = daily_image_usage.images_generated + 1\n `;\n\n const sampleImages = [\n \"https://images.unsplash.com/photo-1618005182384-a83a8bd57fbe?w=1200&q=80\",\n \"https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=1200&q=80\",\n \"https://images.unsplash.com/photo-1634017839464-5c339ebe3cb4?w=1200&q=80\",\n \"https://images.unsplash.com/photo-1550684848-fac1c5b4e853?w=1200&q=80\",\n ];\n const chosen =\n sampleImages[Math.floor(Math.random() * sampleImages.length)];\n\n resultData = {\n prompt,\n aspect_ratio,\n imageUrl: chosen,\n quotaRemaining: Math.max(0, maxImages - (currentCount + 1)),\n message: \"Image générée avec succès via mAI !\",\n };\n break;\n }\n\n case \"check_quotas\": {\n const uRows =\n await sql`SELECT tier FROM users WHERE id = ${uid} LIMIT 1`;\n const tier = uRows[0]?.tier || \"Free\";\n const { weekStartStr, nextResetIso } = getWeekData();\n\n const [usageRows, imgRows] = await Promise.all([\n sql`SELECT COALESCE(SUM(tokens_used), 0) as tokens FROM weekly_usage WHERE user_id = ${uid} AND week_start = ${weekStartStr}::date`,\n sql`SELECT COALESCE(images_generated, 0) as images FROM daily_image_usage WHERE user_id = ${uid} AND usage_date = CURRENT_DATE`,\n ]);\n\n const tokenLimit = getTierMaiTokenLimit(tier);\n const imageLimit = getTierDailyImageLimit(tier);\n const tokensUsed = Number(usageRows[0]?.tokens || 0);\n const imagesUsed = Number(imgRows[0]?.images || 0);\n\n resultData = {\n tier,\n weeklyTokens: {\n used: tokensUsed,\n limit: tokenLimit,\n percent: Math.min(\n 100,\n Math.round((tokensUsed / tokenLimit) * 100),\n ),\n },\n dailyImages: {\n used: imagesUsed,\n limit: imageLimit,\n percent: Math.min(\n 100,\n Math.round((imagesUsed / imageLimit) * 100),\n ),\n },\n resetAt: nextResetIso,\n };\n break;\n }\n\n case \"search_web\": {\n const { query } = args;\n const search = await executeWebSearch(String(query || \"\"), 5);\n if (!search.success || search.results.length === 0) {\n throw new Error(search.error || \"Aucun résultat de recherche web.\");\n }\n const snippet = search.results\n .slice(0, 3)\n .map((r) => `• **${r.title}** — ${r.snippet}\\n ${r.url}`)\n .join(\"\\n\");\n resultData = {\n query,\n snippet,\n provider: search.provider,\n results: search.results,\n };\n break;\n }\n\n case \"fact_check\": {\n const { statement } = args;\n const search = await executeWebSearch(String(statement || \"\"), 5)\n .catch(() => null);\n const sources = search?.success ? search.results.slice(0, 3) : [];\n const llm = await this.callOpenRouter(\n uid,\n \"Tu es un vérificateur de faits rigoureux. Réponds en 3 phrases maximum en français : verdict (Vrai / Faux / Plausible / À vérifier) puis justification brève en t'appuyant sur les sources fournies.\",\n `Affirmation : « ${statement} »\\n\\nSources trouvées :\\n${\n sources.map((s) => `- ${s.title} : ${s.snippet}`).join(\"\\n\") ||\n \"(aucune)\"\n }`,\n );\n resultData = {\n statement,\n verdict: llm\n ? \"Analyse mAI\"\n : sources.length > 0\n ? \"À vérifier\"\n : \"Sources insuffisantes\",\n confidence: sources.length > 0 ? \"Moyen\" : \"Faible\",\n analysis: llm ||\n (sources.length > 0\n ? \"Des sources web ont été trouvées, croisez-les pour vous forger un avis.\"\n : \"Aucune source fiable trouvée sur le web pour cette affirmation.\"),\n sources,\n };\n break;\n }\n\n case \"rewrite_post\": {\n const { text, style = \"viral\" } = args;\n const tones: Record = {\n viral: \"viral\",\n pro: \"executive\",\n humour: \"viral\",\n concis: \"minimal\",\n poétique: \"poetic\",\n };\n const llm = await this.callOpenRouter(\n uid,\n `Reformule le texte suivant en français dans un style « ${style} », percutant et adapté à un réseau social. Réponds UNIQUEMENT par le texte reformulé, sans commentaire.`,\n String(text || \"\"),\n );\n const rewritten = llm ||\n (await this.modulateText({\n text: String(text || \"\"),\n tone: tones[style] || \"viral\",\n }));\n resultData = { rewritten, style };\n break;\n }\n\n case \"suggest_post\": {\n const { topic, style = \"viral\" } = args;\n const llm = await this.callOpenRouter(\n uid,\n `Propose 3 idées de publications courtes pour le réseau social Vibe sur le thème « ${topic} », dans un style « ${style} ». Format : une liste numérotée, chaque post fait 1 à 2 phrases, avec des hashtags pertinents. Réponds UNIQUEMENT par la liste.`,\n String(topic || \"sujets d'actualité\"),\n );\n if (!llm) {\n throw new Error(\n \"Génération indisponible : aucune clé IA configurée sur le serveur.\",\n );\n }\n resultData = { suggestions: llm, topic, style };\n break;\n }\n\n case \"translate\": {\n const { text, target_language = \"anglais\" } = args;\n const llm = await this.callOpenRouter(\n uid,\n `Traduis le texte suivant en ${target_language}. Réponds UNIQUEMENT par la traduction, sans commentaire.`,\n String(text || \"\"),\n );\n if (!llm) {\n throw new Error(\n \"Traduction indisponible : aucune clé IA configurée sur le serveur.\",\n );\n }\n resultData = { translated: llm, targetLanguage: target_language };\n break;\n }\n\n case \"analyze_trends\": {\n const recent = await sql`\n SELECT content FROM posts WHERE published_at > NOW() - INTERVAL '7 days' ORDER BY published_at DESC LIMIT 200\n `;\n const tags: Record = {};\n for (const r of recent) {\n for (\n const m of String(r.content).matchAll(/#([\\p{L}\\p{N}_]{2,30})/gu)\n ) {\n const tag = m[1].toLowerCase();\n tags[tag] = (tags[tag] || 0) + 1;\n }\n }\n const trendingTopics = Object.entries(tags)\n .sort((a, b) => b[1] - a[1])\n .slice(0, 8)\n .map(([name, postsCount]) => ({\n name: `#${name}`,\n postsCount,\n sentiment: postsCount >= 5 ? \"Très actif 🔥\" : \"Actif 📈\",\n }));\n resultData = { trendingTopics };\n break;\n }\n\n case \"update_profile\": {\n const { display_name, bio } = args;\n if (!display_name && !bio) {\n throw new Error(\n \"Fournissez au moins un champ (display_name ou bio).\",\n );\n }\n if (\n display_name !== undefined &&\n (String(display_name).length < 2 ||\n String(display_name).length > 40)\n ) {\n throw new Error(\n \"Le nom affiché doit contenir entre 2 et 40 caractères.\",\n );\n }\n if (bio !== undefined && String(bio).length > 200) {\n throw new Error(\"La bio ne doit pas dépasser 200 caractères.\");\n }\n const updated = await sql`\n UPDATE profiles SET\n display_name = COALESCE(${display_name ?? null}, display_name),\n bio = COALESCE(${bio ?? null}, bio),\n updated_at = NOW()\n WHERE user_id = ${uid}\n RETURNING display_name, bio\n `;\n if (updated.length === 0) throw new Error(\"Profil introuvable.\");\n resultData = {\n profile: updated[0],\n message: \"Profil mis à jour avec succès.\",\n };\n break;\n }\n\n case \"follow_user\": {\n const { username, follow = true } = args;\n const cleanUsername = String(username || \"\").replace(/^@/, \"\").trim()\n .toLowerCase();\n if (!cleanUsername) throw new Error(\"username est requis.\");\n const target =\n await sql`SELECT id FROM users WHERE LOWER(username) = ${cleanUsername} LIMIT 1`;\n if (target.length === 0) {\n throw new Error(`Compte @${cleanUsername} introuvable sur Vibe.`);\n }\n const targetId = Number(target[0].id);\n if (targetId === uid) {\n throw new Error(\"Vous ne pouvez pas vous suivre vous-même.\");\n }\n\n if (follow) {\n const existing =\n await sql`SELECT 1 FROM follows WHERE follower_id = ${uid} AND following_id = ${targetId} LIMIT 1`;\n if (existing.length > 0) {\n resultData = {\n followed: true,\n username: cleanUsername,\n message: `Vous suivez déjà @${cleanUsername}.`,\n };\n break;\n }\n await sql`INSERT INTO follows (follower_id, following_id) VALUES (${uid}, ${targetId}) ON CONFLICT DO NOTHING`;\n await Promise.all([\n sql`UPDATE profiles SET following_count = following_count + 1 WHERE user_id = ${uid}`,\n sql`UPDATE profiles SET followers_count = followers_count + 1 WHERE user_id = ${targetId}`,\n ]);\n resultData = {\n followed: true,\n username: cleanUsername,\n message: `Vous suivez désormais @${cleanUsername} !`,\n };\n } else {\n const del =\n await sql`DELETE FROM follows WHERE follower_id = ${uid} AND following_id = ${targetId} RETURNING 1`;\n if (del.length > 0) {\n await Promise.all([\n sql`UPDATE profiles SET following_count = GREATEST(0, following_count - 1) WHERE user_id = ${uid}`,\n sql`UPDATE profiles SET followers_count = GREATEST(0, followers_count - 1) WHERE user_id = ${targetId}`,\n ]);\n }\n resultData = {\n followed: false,\n username: cleanUsername,\n message: `Vous ne suivez plus @${cleanUsername}.`,\n };\n }\n break;\n }\n\n case \"get_notifications\": {\n const rows = await sql`\n SELECT n.*, u.username as actor_username\n FROM notifications n\n LEFT JOIN users u ON u.id = n.actor_id\n WHERE n.recipient_id = ${uid}\n ORDER BY n.created_at DESC LIMIT 20\n `;\n resultData = { count: rows.length, notifications: rows };\n break;\n }\n\n case \"like_post\": {\n const { post_id, like = true } = args;\n if (\n !post_id ||\n !/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i\n .test(String(post_id))\n ) {\n throw new Error(\"post_id UUID valide est requis.\");\n }\n const postRows =\n await sql`SELECT id, author_id FROM posts WHERE id = ${\n String(post_id)\n }::uuid LIMIT 1`;\n if (postRows.length === 0) {\n throw new Error(\"Publication introuvable.\");\n }\n const authorId = Number(postRows[0].author_id);\n if (like) {\n const existing = await sql`\n SELECT id FROM post_interactions\n WHERE user_id = ${uid} AND post_id = ${\n String(post_id)\n }::uuid AND interaction_type = 'like' LIMIT 1\n `;\n if (existing.length > 0) {\n const c = await sql`SELECT likes_count FROM posts WHERE id = ${\n String(post_id)\n }::uuid LIMIT 1`;\n resultData = {\n liked: true,\n post_id,\n likes_count: Number(c[0]?.likes_count || 0),\n message: \"Vous aimez déjà cette publication.\",\n };\n break;\n }\n await sql`\n INSERT INTO post_interactions (user_id, post_id, interaction_type)\n VALUES (${uid}, ${String(post_id)}::uuid, 'like')\n ON CONFLICT (user_id, post_id, interaction_type) DO NOTHING\n `;\n await sql`UPDATE posts SET likes_count = COALESCE(likes_count,0) + 1 WHERE id = ${\n String(post_id)\n }::uuid`;\n if (authorId !== uid) {\n try {\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, post_id, message)\n VALUES (${authorId}, ${uid}, 'like', ${\n String(post_id)\n }::uuid, 'a aimé votre publication via mAI')\n `;\n } catch {}\n }\n const c = await sql`SELECT likes_count FROM posts WHERE id = ${\n String(post_id)\n }::uuid LIMIT 1`;\n resultData = {\n liked: true,\n post_id,\n likes_count: Number(c[0]?.likes_count || 0),\n message: \"Publication likée avec succès !\",\n };\n } else {\n const existing = await sql`\n SELECT id FROM post_interactions\n WHERE user_id = ${uid} AND post_id = ${\n String(post_id)\n }::uuid AND interaction_type = 'like' LIMIT 1\n `;\n if (existing.length > 0) {\n await sql`DELETE FROM post_interactions WHERE id = ${\n existing[0].id\n }::uuid`;\n await sql`UPDATE posts SET likes_count = GREATEST(0, COALESCE(likes_count,0) - 1) WHERE id = ${\n String(post_id)\n }::uuid`;\n }\n const c = await sql`SELECT likes_count FROM posts WHERE id = ${\n String(post_id)\n }::uuid LIMIT 1`;\n resultData = {\n liked: false,\n post_id,\n likes_count: Number(c[0]?.likes_count || 0),\n message: \"Like retiré.\",\n };\n }\n break;\n }\n\n case \"send_message\": {\n const { username, content } = args;\n const cleanUsername = String(username || \"\").replace(/^@/, \"\").trim()\n .toLowerCase();\n const text = String(content || \"\").trim();\n if (!cleanUsername) {\n throw new Error(\"username destinataire est requis.\");\n }\n if (!text) throw new Error(\"Le contenu du message est obligatoire.\");\n if (text.length > 2000) {\n throw new Error(\"Message trop long (max 2000 caractères).\");\n }\n const safety = this.assessContentSafety(text);\n if (!safety.isSafe) {\n throw new Error(`Message refusé par mAI : ${safety.flagReason}`);\n }\n const target =\n await sql`SELECT id FROM users WHERE LOWER(username) = ${cleanUsername} LIMIT 1`;\n if (target.length === 0) {\n throw new Error(`Compte @${cleanUsername} introuvable sur Vibe.`);\n }\n const recId = Number(target[0].id);\n if (recId === uid) {\n throw new Error(\n \"Vous ne pouvez pas vous envoyer un message à vous-même.\",\n );\n }\n const p1 = uid < recId ? uid : recId;\n const p2 = uid < recId ? recId : uid;\n const convRows = await sql`\n INSERT INTO dm_conversations (participant_one_id, participant_two_id, last_message_preview, last_message_at)\n VALUES (${p1}, ${p2}, ${text.slice(0, 120)}, NOW())\n ON CONFLICT (participant_one_id, participant_two_id)\n DO UPDATE SET last_message_preview = ${\n text.slice(0, 120)\n }, last_message_at = NOW()\n RETURNING id\n `;\n const conversationId = String(convRows[0].id);\n const msg = await sql`\n INSERT INTO direct_messages (conversation_id, sender_id, recipient_id, content)\n VALUES (${conversationId}::uuid, ${uid}, ${recId}, ${text})\n RETURNING *\n `;\n try {\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, message)\n VALUES (${recId}, ${uid}, 'dm', 'vous a envoyé un message via mAI')\n `;\n } catch {}\n resultData = {\n sent: true,\n username: cleanUsername,\n conversation_id: conversationId,\n message_id: String(msg[0]?.id || \"\"),\n preview: text.slice(0, 80),\n message: `Message envoyé à @${cleanUsername} !`,\n };\n break;\n }\n\n case \"update_settings\": {\n const ALLOWED = [\n \"theme_preference\",\n \"ui_language\",\n \"feed_default_mode\",\n \"hide_reposts\",\n \"blocked_keywords\",\n \"accent_color\",\n \"font_size\",\n \"mai_auto_approve_tools\",\n \"mai_default_model\",\n \"mai_context_posts\",\n \"mai_context_dms\",\n \"mai_context_books\",\n \"email_notifications\",\n \"push_notifications\",\n \"notify_on_like\",\n \"notify_on_reply\",\n \"notify_on_dm\",\n \"dm_auto_translate\",\n \"dm_translate_lang\",\n ];\n const patch: Record = {};\n for (const k of ALLOWED) {\n if (args[k] !== undefined) patch[k] = args[k];\n }\n if (Object.keys(patch).length === 0) {\n throw new Error(\"Aucun paramètre autorisé fourni.\");\n }\n if (\n patch.feed_default_mode &&\n ![\"for_you\", \"following\", \"trending\"].includes(\n String(patch.feed_default_mode),\n )\n ) {\n throw new Error(\n \"feed_default_mode invalide (for_you | following | trending).\",\n );\n }\n if (\n patch.theme_preference &&\n ![\"light\", \"dark\", \"auto\"].includes(String(patch.theme_preference))\n ) {\n throw new Error(\"theme_preference invalide (light | dark | auto).\");\n }\n if (\n patch.blocked_keywords && !Array.isArray(patch.blocked_keywords)\n ) {\n throw new Error(\"blocked_keywords doit être un tableau de mots.\");\n }\n await sql`ALTER TABLE user_settings ADD COLUMN IF NOT EXISTS mai_auto_approve_tools BOOLEAN DEFAULT FALSE`\n .catch(() => {});\n await sql`ALTER TABLE user_settings ADD COLUMN IF NOT EXISTS dm_auto_translate BOOLEAN DEFAULT FALSE`\n .catch(() => {});\n await sql`ALTER TABLE user_settings ADD COLUMN IF NOT EXISTS dm_translate_lang TEXT`\n .catch(() => {});\n const cols = Object.keys(patch);\n // Upsert générique : INSERT puis UPDATE ciblé colonne par colonne\n await sql`\n INSERT INTO user_settings (user_id) VALUES (${uid})\n ON CONFLICT (user_id) DO NOTHING\n `.catch(() => {});\n for (const col of cols) {\n const val = patch[col];\n const jsonVal = Array.isArray(val) ? val : null;\n if (jsonVal !== null) {\n await sql`UPDATE user_settings SET blocked_keywords = ${jsonVal} WHERE user_id = ${uid}`;\n } else if (typeof val === \"boolean\") {\n if (col === \"hide_reposts\") {\n await sql`UPDATE user_settings SET hide_reposts = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"mai_auto_approve_tools\") {\n await sql`UPDATE user_settings SET mai_auto_approve_tools = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"mai_context_posts\") {\n await sql`UPDATE user_settings SET mai_context_posts = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"mai_context_dms\") {\n await sql`UPDATE user_settings SET mai_context_dms = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"mai_context_books\") {\n await sql`UPDATE user_settings SET mai_context_books = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"email_notifications\") {\n await sql`UPDATE user_settings SET email_notifications = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"push_notifications\") {\n await sql`UPDATE user_settings SET push_notifications = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"notify_on_like\") {\n await sql`UPDATE user_settings SET notify_on_like = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"notify_on_reply\") {\n await sql`UPDATE user_settings SET notify_on_reply = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"notify_on_dm\") {\n await sql`UPDATE user_settings SET notify_on_dm = ${val} WHERE user_id = ${uid}`;\n } else if (col === \"dm_auto_translate\") {\n await sql`UPDATE user_settings SET dm_auto_translate = ${val} WHERE user_id = ${uid}`;\n }\n } else {\n const s = String(val).slice(0, 100);\n if (col === \"theme_preference\") {\n await sql`UPDATE user_settings SET theme_preference = ${s} WHERE user_id = ${uid}`;\n } else if (col === \"ui_language\") {\n await sql`UPDATE user_settings SET ui_language = ${s} WHERE user_id = ${uid}`;\n } else if (col === \"feed_default_mode\") {\n await sql`UPDATE user_settings SET feed_default_mode = ${s} WHERE user_id = ${uid}`;\n } else if (col === \"accent_color\") {\n await sql`UPDATE user_settings SET accent_color = ${s} WHERE user_id = ${uid}`;\n } else if (col === \"font_size\") {\n await sql`UPDATE user_settings SET font_size = ${s} WHERE user_id = ${uid}`;\n } else if (col === \"mai_default_model\") {\n await sql`UPDATE user_settings SET mai_default_model = ${s} WHERE user_id = ${uid}`;\n } else if (col === \"dm_translate_lang\") {\n await sql`UPDATE user_settings SET dm_translate_lang = ${s} WHERE user_id = ${uid}`;\n }\n }\n }\n try {\n this.userModelCache.delete(String(uid));\n } catch {}\n resultData = {\n updated: true,\n patched: patch,\n message: \"Paramètres mis à jour avec succès via mAI.\",\n };\n break;\n }\n\n case \"bookmark_post\": {\n const { post_id, bookmark = true } = args;\n if (\n !post_id ||\n !/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i\n .test(String(post_id))\n ) {\n throw new Error(\"post_id UUID valide est requis.\");\n }\n const exists = await sql`SELECT id FROM posts WHERE id = ${\n String(post_id)\n }::uuid LIMIT 1`;\n if (exists.length === 0) throw new Error(\"Publication introuvable.\");\n if (bookmark) {\n await sql`\n INSERT INTO bookmarks (user_id, post_id) VALUES (${uid}, ${\n String(post_id)\n }::uuid)\n ON CONFLICT (user_id, post_id) DO NOTHING\n `;\n await sql`UPDATE posts SET bookmarks_count = COALESCE(bookmarks_count,0) + 1 WHERE id = ${\n String(post_id)\n }::uuid`;\n resultData = {\n bookmarked: true,\n post_id,\n message: \"Post ajouté à vos favoris !\",\n };\n } else {\n await sql`DELETE FROM bookmarks WHERE user_id = ${uid} AND post_id = ${\n String(post_id)\n }::uuid`;\n await sql`UPDATE posts SET bookmarks_count = GREATEST(0, COALESCE(bookmarks_count,0) - 1) WHERE id = ${\n String(post_id)\n }::uuid`;\n resultData = {\n bookmarked: false,\n post_id,\n message: \"Post retiré de vos favoris.\",\n };\n }\n break;\n }\n\n case \"repost_post\": {\n const { post_id, repost = true } = args;\n if (\n !post_id ||\n !/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i\n .test(String(post_id))\n ) {\n throw new Error(\"post_id UUID valide est requis.\");\n }\n const prow = await sql`SELECT id, author_id FROM posts WHERE id = ${\n String(post_id)\n }::uuid LIMIT 1`;\n if (prow.length === 0) throw new Error(\"Publication introuvable.\");\n if (repost) {\n const existing = await sql`\n SELECT id FROM post_interactions\n WHERE user_id = ${uid} AND post_id = ${\n String(post_id)\n }::uuid AND interaction_type = 'repost' LIMIT 1\n `;\n if (existing.length > 0) {\n resultData = {\n reposted: true,\n post_id,\n message: \"Vous avez déjà reposté cette publication.\",\n };\n break;\n }\n await sql`\n INSERT INTO post_interactions (user_id, post_id, interaction_type)\n VALUES (${uid}, ${String(post_id)}::uuid, 'repost')\n ON CONFLICT (user_id, post_id, interaction_type) DO NOTHING\n `;\n await sql`UPDATE posts SET reposts_count = COALESCE(reposts_count,0) + 1 WHERE id = ${\n String(post_id)\n }::uuid`;\n resultData = {\n reposted: true,\n post_id,\n message: \"Publication repostée avec succès !\",\n };\n } else {\n await sql`DELETE FROM post_interactions WHERE user_id = ${uid} AND post_id = ${\n String(post_id)\n }::uuid AND interaction_type = 'repost'`;\n await sql`UPDATE posts SET reposts_count = GREATEST(0, COALESCE(reposts_count,0) - 1) WHERE id = ${\n String(post_id)\n }::uuid`;\n resultData = {\n reposted: false,\n post_id,\n message: \"Repost annulé.\",\n };\n }\n break;\n }\n\n case \"comment_post\": {\n const { post_id, content } = args;\n const text = String(content || \"\").trim();\n if (\n !post_id ||\n !/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i\n .test(String(post_id))\n ) {\n throw new Error(\"post_id UUID valide est requis.\");\n }\n if (!text) {\n throw new Error(\"Le contenu du commentaire est obligatoire.\");\n }\n if (text.length > 2000) {\n throw new Error(\"Commentaire trop long (max 2000 caractères).\");\n }\n const safety = this.assessContentSafety(text);\n if (!safety.isSafe) {\n throw new Error(\n `Commentaire refusé par mAI : ${safety.flagReason}`,\n );\n }\n const prow = await sql`SELECT id, author_id FROM posts WHERE id = ${\n String(post_id)\n }::uuid LIMIT 1`;\n if (prow.length === 0) throw new Error(\"Publication introuvable.\");\n const inserted = await sql`\n INSERT INTO comments (post_id, author_id, content, depth)\n VALUES (${String(post_id)}::uuid, ${uid}, ${text}, 1)\n RETURNING id, content, created_at\n `;\n await sql`UPDATE posts SET replies_count = COALESCE(replies_count,0) + 1 WHERE id = ${\n String(post_id)\n }::uuid`;\n const authorId = Number(prow[0].author_id);\n if (authorId !== uid) {\n try {\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, post_id, comment_id, message)\n VALUES (${authorId}, ${uid}, 'reply', ${\n String(post_id)\n }::uuid, ${\n String(inserted[0].id)\n }::uuid, 'a commenté votre publication via mAI')\n `;\n } catch {}\n }\n resultData = {\n commented: true,\n post_id,\n comment_id: String(inserted[0].id),\n message: \"Commentaire publié avec succès !\",\n };\n break;\n }\n\n case \"get_post_stats\": {\n const { post_id } = args;\n if (\n !post_id ||\n !/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i\n .test(String(post_id))\n ) {\n throw new Error(\"post_id UUID valide est requis.\");\n }\n const rows = await sql`\n SELECT p.id, p.content, p.likes_count, p.reposts_count, p.replies_count,\n COALESCE(p.views_count,0) as views_count, COALESCE(p.bookmarks_count,0) as bookmarks_count,\n p.published_at, u.username, pr.display_name\n FROM posts p\n JOIN users u ON u.id = p.author_id\n LEFT JOIN profiles pr ON pr.user_id = u.id\n WHERE p.id = ${String(post_id)}::uuid LIMIT 1\n `;\n if (rows.length === 0) throw new Error(\"Publication introuvable.\");\n const p = rows[0];\n const likes = Number(p.likes_count || 0),\n reposts = Number(p.reposts_count || 0);\n const replies = Number(p.replies_count || 0),\n views = Number(p.views_count || 0);\n const engagement = likes + reposts * 2.5 + replies * 2 + views * 0.1;\n const rate = views > 0\n ? Math.round(((likes + reposts + replies) / views) * 1000) / 10\n : 0;\n resultData = {\n post_id,\n author: p.username,\n content: String(p.content || \"\").slice(0, 300),\n likes,\n reposts,\n replies,\n views,\n bookmarks: Number(p.bookmarks_count || 0),\n engagement: Math.round(engagement * 10) / 10,\n engagement_rate_percent: rate,\n published_at: p.published_at,\n message: \"Analyse du post récupérée.\",\n };\n break;\n }\n\n case \"analyze_audience\": {\n const days = parsePeriodDays(args.period);\n const sinceIso = new Date(Date.now() - days * 86400000).toISOString();\n const [sourcesRows, uniqueRows, hoursRows, followerRows] =\n await Promise.all([\n sql`\n SELECT COALESCE(pv.source, 'feed') AS source, COUNT(*) AS views\n FROM post_views pv JOIN posts p ON p.id = pv.post_id\n WHERE p.author_id = ${uid} AND pv.created_at >= ${sinceIso}::timestamptz\n GROUP BY 1 ORDER BY views DESC LIMIT 8\n `,\n sql`\n SELECT COUNT(DISTINCT pv.user_id) AS unique_viewers\n FROM post_views pv JOIN posts p ON p.id = pv.post_id\n WHERE p.author_id = ${uid} AND pv.created_at >= ${sinceIso}::timestamptz AND pv.user_id IS NOT NULL\n `,\n sql`\n SELECT EXTRACT(HOUR FROM pv.created_at) AS hour, COUNT(*) AS views\n FROM post_views pv JOIN posts p ON p.id = pv.post_id\n WHERE p.author_id = ${uid} AND pv.created_at >= ${sinceIso}::timestamptz\n GROUP BY 1 ORDER BY views DESC LIMIT 5\n `,\n sql`\n SELECT u.username, pr.display_name, COUNT(*) AS views\n FROM post_views pv\n JOIN posts p ON p.id = pv.post_id\n JOIN follows f ON f.follower_id = pv.user_id AND f.following_id = ${uid}\n JOIN users u ON u.id = pv.user_id\n LEFT JOIN profiles pr ON pr.user_id = u.id\n WHERE p.author_id = ${uid} AND pv.created_at >= ${sinceIso}::timestamptz\n GROUP BY u.username, pr.display_name ORDER BY views DESC LIMIT 5\n `,\n ]);\n const totalViews = (sourcesRows as any[]).reduce(\n (s, r) => s + Number(r.views || 0),\n 0,\n );\n resultData = {\n period_days: days,\n total_views: totalViews,\n sources: (sourcesRows as any[]).map((r) => ({\n source: String(r.source),\n views: Number(r.views || 0),\n percent: totalViews > 0\n ? Math.round((Number(r.views || 0) / totalViews) * 100)\n : 0,\n })),\n unique_viewers: Number(uniqueRows[0]?.unique_viewers || 0),\n peak_hours: (hoursRows as any[]).map((r) => ({\n hour: Number(r.hour),\n views: Number(r.views || 0),\n })),\n top_engaged_followers: (followerRows as any[]).map((r) => ({\n username: r.username,\n display_name: r.display_name,\n views: Number(r.views || 0),\n })),\n message: `Audience analysée sur ${days} jours.`,\n };\n break;\n }\n\n case \"best_time_to_post\": {\n const days = parsePeriodDays(args.period);\n const sinceIso = new Date(Date.now() - days * 86400000).toISOString();\n const [slotRows, hourRows, totalRows] = await Promise.all([\n sql`\n SELECT EXTRACT(DOW FROM p.published_at) AS dow, EXTRACT(HOUR FROM p.published_at) AS hour,\n COUNT(*) AS posts_count, AVG(COALESCE(p.views_count,0)) AS avg_views,\n AVG(COALESCE(p.likes_count,0) + COALESCE(p.reposts_count,0) * 2 + COALESCE(p.replies_count,0) * 2) AS avg_engagement\n FROM posts p WHERE p.author_id = ${uid} AND p.published_at >= ${sinceIso}::timestamptz\n GROUP BY 1, 2\n `,\n sql`\n SELECT EXTRACT(HOUR FROM pv.created_at) AS hour, COUNT(*) AS views\n FROM post_views pv JOIN posts p ON p.id = pv.post_id\n WHERE p.author_id = ${uid} AND pv.created_at >= ${sinceIso}::timestamptz\n GROUP BY 1 ORDER BY views DESC LIMIT 6\n `,\n sql`SELECT COUNT(*) AS n FROM posts WHERE author_id = ${uid} AND published_at >= ${sinceIso}::timestamptz`,\n ]);\n const analyzedPosts = Number(totalRows[0]?.n || 0);\n const bestSlots = (slotRows as any[])\n .map((r) => ({\n weekday: WEEKDAY_NAMES[Number(r.dow)] || String(r.dow),\n hour: Number(r.hour),\n posts_count: Number(r.posts_count || 0),\n avg_views: Math.round(Number(r.avg_views || 0)),\n avg_engagement: Math.round(Number(r.avg_engagement || 0) * 10) /\n 10,\n }))\n .sort((a, b) => b.avg_engagement - a.avg_engagement)\n .slice(0, 3);\n resultData = {\n period_days: days,\n analyzed_posts: analyzedPosts,\n best_slots: bestSlots,\n audience_peak_hours: (hourRows as any[]).map((r) => ({\n hour: Number(r.hour),\n views: Number(r.views || 0),\n })),\n confidence: analyzedPosts >= 10\n ? \"high\"\n : analyzedPosts >= 4\n ? \"medium\"\n : \"low\",\n note: analyzedPosts === 0\n ? \"Aucune publication sur la période — publiez quelques posts pour obtenir des recommandations.\"\n : undefined,\n message: bestSlots.length > 0\n ? `Meilleur créneau : ${bestSlots[0].weekday} à ${\n bestSlots[0].hour\n }h (${bestSlots[0].avg_views} vues moyennes).`\n : \"Pas encore assez de données de publication.\",\n };\n break;\n }\n\n case \"compare_periods\": {\n const days = parsePeriodDays(args.period);\n const nowMs = Date.now();\n const nowIso = new Date(nowMs).toISOString();\n const curSince = new Date(nowMs - days * 86400000).toISOString();\n const prevSince = new Date(nowMs - 2 * days * 86400000).toISOString();\n const postAgg = (since: string, until: string) =>\n sql`\n SELECT COALESCE(SUM(views_count),0) AS views, COALESCE(SUM(likes_count),0) AS likes,\n COALESCE(SUM(reposts_count),0) AS reposts, COALESCE(SUM(replies_count),0) AS replies,\n COUNT(*) AS posts\n FROM posts WHERE author_id = ${uid} AND published_at >= ${since}::timestamptz AND published_at < ${until}::timestamptz\n `;\n const [curRows, prevRows, fCur, fPrev, pvCur, pvPrev] = await Promise\n .all([\n postAgg(curSince, nowIso),\n postAgg(prevSince, curSince),\n sql`SELECT COUNT(*) AS n FROM follows WHERE following_id = ${uid} AND created_at >= ${curSince}::timestamptz`,\n sql`SELECT COUNT(*) AS n FROM follows WHERE following_id = ${uid} AND created_at >= ${prevSince}::timestamptz AND created_at < ${curSince}::timestamptz`,\n sql`SELECT COUNT(*) AS n FROM profile_views WHERE profile_user_id = ${uid} AND created_at >= ${curSince}::timestamptz`,\n sql`SELECT COUNT(*) AS n FROM profile_views WHERE profile_user_id = ${uid} AND created_at >= ${prevSince}::timestamptz AND created_at < ${curSince}::timestamptz`,\n ]);\n const metric = (current: number, previous: number) => ({\n current,\n previous,\n delta: current - previous,\n percent: previous > 0\n ? Math.round(((current - previous) / previous) * 1000) / 10\n : current > 0\n ? 100\n : 0,\n });\n const c = curRows[0] || {};\n const p = prevRows[0] || {};\n resultData = {\n period_days: days,\n views: metric(Number(c.views || 0), Number(p.views || 0)),\n likes: metric(Number(c.likes || 0), Number(p.likes || 0)),\n reposts: metric(Number(c.reposts || 0), Number(p.reposts || 0)),\n replies: metric(Number(c.replies || 0), Number(p.replies || 0)),\n posts: metric(Number(c.posts || 0), Number(p.posts || 0)),\n followers_gained: metric(\n Number(fCur[0]?.n || 0),\n Number(fPrev[0]?.n || 0),\n ),\n profile_views: metric(\n Number(pvCur[0]?.n || 0),\n Number(pvPrev[0]?.n || 0),\n ),\n message:\n `Comparaison des ${days} derniers jours vs les ${days} jours précédents.`,\n };\n break;\n }\n\n case \"predict_post_performance\": {\n const content = String(args.content || \"\").trim();\n if (!content) {\n throw new Error(\"Le contenu du brouillon est obligatoire.\");\n }\n const plannedFormat = String(args.format || \"\").trim() || null;\n const sinceIso = new Date(Date.now() - 90 * 86400000).toISOString();\n const [globalRows, formatRows, hourRows] = await Promise.all([\n sql`\n SELECT COUNT(*) AS n, AVG(COALESCE(views_count,0)) AS avg_views,\n AVG(COALESCE(likes_count,0) + COALESCE(reposts_count,0) * 2 + COALESCE(replies_count,0) * 2) AS avg_engagement,\n AVG(LENGTH(content)) AS avg_length\n FROM posts WHERE author_id = ${uid} AND published_at >= ${sinceIso}::timestamptz\n `,\n sql`\n SELECT COALESCE(format,'micro_text') AS format,\n AVG(COALESCE(likes_count,0) + COALESCE(reposts_count,0) * 2 + COALESCE(replies_count,0) * 2) AS avg_engagement\n FROM posts WHERE author_id = ${uid} AND published_at >= ${sinceIso}::timestamptz GROUP BY 1\n `,\n sql`\n SELECT EXTRACT(HOUR FROM published_at) AS hour, AVG(COALESCE(views_count,0)) AS avg_views\n FROM posts WHERE author_id = ${uid} AND published_at >= ${sinceIso}::timestamptz GROUP BY 1\n `,\n ]);\n const hist = globalRows[0] || {};\n const sampleSize = Number(hist.n || 0);\n const avgViews = Number(hist.avg_views || 0);\n const avgEngagement = Number(hist.avg_engagement || 0);\n const avgLength = Number(hist.avg_length || 0);\n\n const breakdown: Array<\n { factor: string; impact: number; tip?: string }\n > = [];\n let score = 50;\n\n if (sampleSize >= 3 && avgLength > 0) {\n const ratio = content.length / avgLength;\n if (ratio >= 0.5 && ratio <= 1.7) {\n score += 6;\n breakdown.push({\n factor: \"Longueur proche de vos habitudes\",\n impact: 6,\n });\n } else if (ratio > 1.7) {\n score -= 6;\n breakdown.push({\n factor: \"Texte plus long que d'habitude\",\n impact: -6,\n tip: \"Raccourcis ou structure en paragraphes courts.\",\n });\n } else {\n score += 2;\n breakdown.push({\n factor: \"Texte plus court que d'habitude\",\n impact: 2,\n });\n }\n }\n\n const hashtagCount =\n (content.match(/#[\\p{L}\\p{N}_]{2,30}/gu) || []).length;\n if (hashtagCount >= 1 && hashtagCount <= 3) {\n score += 8;\n breakdown.push({\n factor: `${hashtagCount} hashtag(s) — dosage optimal`,\n impact: 8,\n });\n } else if (hashtagCount === 0) {\n breakdown.push({\n factor: \"Aucun hashtag\",\n impact: 0,\n tip: \"Ajoutez 1 à 3 hashtags pertinents pour élargir la portée.\",\n });\n } else {\n score -= 6;\n breakdown.push({\n factor: \"Trop de hashtags\",\n impact: -6,\n tip: \"Limitez-vous à 1-3 hashtags pour éviter l'effet spam.\",\n });\n }\n\n if (\n /\\?|commentez|partagez|votre avis|selon vous|dites-moi|votez/i.test(\n content,\n )\n ) {\n score += 6;\n breakdown.push({\n factor: \"Question / appel à l'action présent\",\n impact: 6,\n });\n } else {\n breakdown.push({\n factor: \"Pas de question ni d'appel à l'action\",\n impact: 0,\n tip: \"Une question en fin de post stimule les réponses.\",\n });\n }\n\n if (sampleSize >= 3) {\n const fmt = plannedFormat || \"micro_text\";\n const fmtRow = (formatRows as any[]).find((r) =>\n String(r.format) === fmt\n );\n if (fmtRow) {\n const fmtEng = Number(fmtRow.avg_engagement || 0);\n if (fmtEng > avgEngagement * 1.15) {\n score += 10;\n breakdown.push({\n factor: `Format « ${fmt} » au-dessus de votre moyenne`,\n impact: 10,\n });\n } else if (fmtEng < avgEngagement * 0.85) {\n score -= 8;\n breakdown.push({\n factor: `Format « ${fmt} » sous votre moyenne`,\n impact: -8,\n tip: \"Testez un autre format plus performant.\",\n });\n }\n }\n }\n\n let plannedHour: number | null = null;\n if (args.scheduled_time) {\n const d = new Date(String(args.scheduled_time));\n if (!isNaN(d.getTime())) plannedHour = d.getHours();\n else {\n const hm = String(args.scheduled_time).match(/^(\\d{1,2})/);\n if (hm) plannedHour = Number(hm[1]);\n }\n }\n if (plannedHour !== null && sampleSize >= 3) {\n const hourRow = (hourRows as any[]).find((r) =>\n Number(r.hour) === plannedHour\n );\n if (hourRow) {\n const hourViews = Number(hourRow.avg_views || 0);\n const hourLabel = `${String(plannedHour).padStart(2, \"0\")}h`;\n if (hourViews > avgViews * 1.15) {\n score += 8;\n breakdown.push({\n factor: `Créneau ${hourLabel} au-dessus de votre moyenne`,\n impact: 8,\n });\n } else if (hourViews < avgViews * 0.85) {\n score -= 6;\n breakdown.push({\n factor: `Créneau ${hourLabel} sous votre moyenne`,\n impact: -6,\n tip:\n \"Utilisez /besttime pour trouver vos meilleurs créneaux.\",\n });\n }\n }\n }\n\n score = Math.max(0, Math.min(100, score));\n resultData = {\n score,\n estimated_reach: Math.round(avgViews * (0.6 + score / 100)),\n estimated_engagement:\n Math.round(avgEngagement * (0.6 + score / 100) * 10) / 10,\n based_on_posts: sampleSize,\n breakdown,\n cold_start: sampleSize < 3,\n message: sampleSize < 3\n ? \"Pas assez d'historique (moins de 3 posts sur 90 jours) — estimation neutre.\"\n : `Score ${score}/100 basé sur ${sampleSize} posts récents.`,\n };\n break;\n }\n\n case \"analyze_content_performance\": {\n const days = parsePeriodDays(args.period);\n const sinceIso = new Date(Date.now() - days * 86400000).toISOString();\n const rows = await sql`\n SELECT COALESCE(format,'micro_text') AS format, content,\n COALESCE(views_count,0) AS views, COALESCE(likes_count,0) AS likes,\n COALESCE(reposts_count,0) AS reposts, COALESCE(replies_count,0) AS replies\n FROM posts WHERE author_id = ${uid} AND published_at >= ${sinceIso}::timestamptz\n ORDER BY published_at DESC LIMIT 500\n `;\n const byFormat: Record<\n string,\n { posts: number; views: number; engagement: number }\n > = {};\n const byTag: Record<\n string,\n { posts: number; views: number; likes: number }\n > = {};\n for (const r of rows as any[]) {\n const engagement = Number(r.likes) + Number(r.reposts) * 2 +\n Number(r.replies) * 2;\n const f = String(r.format || \"micro_text\");\n byFormat[f] = byFormat[f] || { posts: 0, views: 0, engagement: 0 };\n byFormat[f].posts += 1;\n byFormat[f].views += Number(r.views);\n byFormat[f].engagement += engagement;\n for (\n const m of String(r.content || \"\").matchAll(\n /#([\\p{L}\\p{N}_]{2,30})/gu,\n )\n ) {\n const tag = m[1].toLowerCase();\n byTag[tag] = byTag[tag] || { posts: 0, views: 0, likes: 0 };\n byTag[tag].posts += 1;\n byTag[tag].views += Number(r.views);\n byTag[tag].likes += Number(r.likes);\n }\n }\n const formats = Object.entries(byFormat)\n .map(([format, s]) => ({\n format,\n posts: s.posts,\n avg_views: Math.round(s.views / Math.max(1, s.posts)),\n avg_engagement:\n Math.round((s.engagement / Math.max(1, s.posts)) * 10) / 10,\n engagement_rate_percent: s.views > 0\n ? Math.round((s.engagement / s.views) * 1000) / 10\n : 0,\n }))\n .sort((a, b) => b.avg_engagement - a.avg_engagement);\n const top_hashtags = Object.entries(byTag)\n .map(([tag, s]) => ({\n tag: `#${tag}`,\n posts: s.posts,\n avg_views: Math.round(s.views / Math.max(1, s.posts)),\n avg_likes: Math.round((s.likes / Math.max(1, s.posts)) * 10) / 10,\n }))\n .sort((a, b) => b.avg_views - a.avg_views)\n .slice(0, 10);\n resultData = {\n period_days: days,\n posts_analyzed: (rows as any[]).length,\n formats,\n top_hashtags,\n message: formats.length > 0\n ? `Format le plus engageant : ${formats[0].format} (${\n formats[0].avg_engagement\n } engagement moyen).`\n : \"Aucun post publié sur la période.\",\n };\n break;\n }\n\n case \"analyze_dm_activity\": {\n const days = parsePeriodDays(args.period);\n const sinceIso = new Date(Date.now() - days * 86400000).toISOString();\n const msgs = await sql`\n SELECT dm.id, dm.conversation_id, dm.sender_id, dm.recipient_id, dm.created_at\n FROM direct_messages dm\n WHERE (dm.sender_id = ${uid} OR dm.recipient_id = ${uid})\n AND dm.created_at >= ${sinceIso}::timestamptz\n ORDER BY dm.conversation_id, dm.created_at ASC\n LIMIT 1000\n `;\n const rowsArr = msgs as any[];\n const sent = rowsArr.filter((m) =>\n Number(m.sender_id) === uid\n ).length;\n const received = rowsArr.length - sent;\n const convIds = new Set(\n rowsArr.map((m) => String(m.conversation_id)),\n );\n let replyDeltas = 0;\n let replyCount = 0;\n let prev: any = null;\n for (const m of rowsArr) {\n if (\n prev &&\n String(prev.conversation_id) === String(m.conversation_id) &&\n Number(prev.sender_id) !== Number(m.sender_id)\n ) {\n const delta = new Date(m.created_at).getTime() -\n new Date(prev.created_at).getTime();\n if (delta > 0 && delta < 24 * 3600 * 1000) {\n replyDeltas += delta;\n replyCount += 1;\n }\n }\n prev = m;\n }\n const perOther: Record = {};\n for (const m of rowsArr) {\n const other = Number(m.sender_id) === uid\n ? Number(m.recipient_id || 0)\n : Number(m.sender_id);\n if (other && other !== uid) {\n perOther[other] = (perOther[other] || 0) + 1;\n }\n }\n const topIds = Object.entries(perOther)\n .sort((a, b) => b[1] - a[1])\n .slice(0, 5)\n .map(([id]) => Number(id));\n let topCorrespondents: any[] = [];\n if (topIds.length > 0) {\n const users = await sql`\n SELECT u.id, u.username, pr.display_name FROM users u\n LEFT JOIN profiles pr ON pr.user_id = u.id\n WHERE u.id = ANY(${topIds})\n `;\n const byId = new Map(\n (users as any[]).map((u) => [Number(u.id), u]),\n );\n topCorrespondents = topIds.map((id) => ({\n username: byId.get(id)?.username || null,\n display_name: byId.get(id)?.display_name || null,\n messages: perOther[id],\n }));\n }\n const groupsRows =\n await sql`SELECT COUNT(*) AS n FROM dm_group_members WHERE user_id = ${uid}`;\n resultData = {\n period_days: days,\n messages_total: rowsArr.length,\n sent,\n received,\n active_conversations: convIds.size,\n groups: Number(groupsRows[0]?.n || 0),\n avg_reply_minutes: replyCount > 0\n ? Math.round((replyDeltas / replyCount / 60000) * 10) / 10\n : null,\n replies_measured: replyCount,\n top_correspondents: topCorrespondents,\n note: rowsArr.length >= 1000\n ? \"Analyse limitée aux 1000 derniers messages de la période.\"\n : undefined,\n message:\n `${rowsArr.length} messages sur ${days} jours (${sent} envoyés, ${received} reçus).`,\n };\n break;\n }\n\n case \"analyze_book_stats\": {\n const days = parsePeriodDays(args.period);\n const sinceIso = new Date(Date.now() - days * 86400000).toISOString();\n const books = await sql`\n SELECT b.id, b.title, b.icon,\n (SELECT COUNT(*) FROM vibe_book_items bi WHERE bi.book_id = b.id) AS items_count,\n (SELECT COUNT(DISTINCT bi.added_by) FROM vibe_book_items bi WHERE bi.book_id = b.id AND bi.added_by IS NOT NULL) AS contributors_count,\n (SELECT COUNT(*) FROM vibe_book_members bm WHERE bm.book_id = b.id) AS members_count,\n (SELECT COUNT(*) FROM vibe_book_items bi WHERE bi.book_id = b.id AND bi.added_at >= ${sinceIso}::timestamptz) AS recent_items,\n (SELECT MAX(bi.added_at) FROM vibe_book_items bi WHERE bi.book_id = b.id) AS last_added_at\n FROM vibe_books b\n WHERE b.user_id = ${uid} OR EXISTS (SELECT 1 FROM vibe_book_members bm WHERE bm.book_id = b.id AND bm.user_id = ${uid})\n ORDER BY b.updated_at DESC NULLS LAST\n LIMIT 20\n `;\n const bookIds = (books as any[]).map((b) => String(b.id));\n let topPosts: any[] = [];\n if (bookIds.length > 0) {\n topPosts = await sql`\n SELECT bi.book_id, p.id AS post_id, p.content, COALESCE(p.views_count,0) AS views, COALESCE(p.likes_count,0) AS likes\n FROM vibe_book_items bi\n JOIN posts p ON p.id = bi.post_id\n WHERE bi.book_id = ANY(${bookIds}::uuid[])\n ORDER BY COALESCE(p.views_count,0) DESC\n LIMIT 60\n ` as any[];\n }\n const topPerBook = new Map();\n for (const r of topPosts) {\n const key = String(r.book_id);\n if (!topPerBook.has(key)) {\n topPerBook.set(key, {\n post_id: r.post_id,\n excerpt: String(r.content || \"\").slice(0, 120),\n views: Number(r.views || 0),\n likes: Number(r.likes || 0),\n });\n }\n }\n resultData = {\n books: (books as any[]).map((b) => ({\n id: b.id,\n title: b.title,\n icon: b.icon,\n items_count: Number(b.items_count || 0),\n members_count: Number(b.members_count || 0),\n contributors_count: Number(b.contributors_count || 0),\n recent_items: Number(b.recent_items || 0),\n last_added_at: b.last_added_at,\n top_post: topPerBook.get(String(b.id)) || null,\n })),\n books_count: (books as any[]).length,\n message: `${(books as any[]).length} livre(s) analysé(s).`,\n };\n break;\n }\n\n case \"analyze_hashtags\": {\n const days = parsePeriodDays(args.period);\n const sinceIso = new Date(Date.now() - days * 86400000).toISOString();\n const [mine, trending] = await Promise.all([\n sql`\n SELECT content, COALESCE(views_count,0) AS views, COALESCE(likes_count,0) AS likes,\n COALESCE(reposts_count,0) AS reposts, COALESCE(replies_count,0) AS replies\n FROM posts WHERE author_id = ${uid} AND published_at >= ${sinceIso}::timestamptz\n ORDER BY published_at DESC LIMIT 500\n `,\n sql`SELECT content FROM posts WHERE published_at > NOW() - INTERVAL '7 days' ORDER BY published_at DESC LIMIT 200`,\n ]);\n const tagStats: Record<\n string,\n { posts: number; views: number; likes: number; engagement: number }\n > = {};\n for (const r of mine as any[]) {\n const eng = Number(r.likes) + Number(r.reposts) * 2 +\n Number(r.replies) * 2;\n for (\n const m of String(r.content || \"\").matchAll(\n /#([\\p{L}\\p{N}_]{2,30})/gu,\n )\n ) {\n const tag = m[1].toLowerCase();\n tagStats[tag] = tagStats[tag] ||\n { posts: 0, views: 0, likes: 0, engagement: 0 };\n tagStats[tag].posts += 1;\n tagStats[tag].views += Number(r.views);\n tagStats[tag].likes += Number(r.likes);\n tagStats[tag].engagement += eng;\n }\n }\n const hashtags = Object.entries(tagStats)\n .map(([tag, s]) => ({\n tag: `#${tag}`,\n posts: s.posts,\n avg_views: Math.round(s.views / Math.max(1, s.posts)),\n avg_likes: Math.round((s.likes / Math.max(1, s.posts)) * 10) / 10,\n engagement_rate_percent: s.views > 0\n ? Math.round((s.engagement / s.views) * 1000) / 10\n : 0,\n }))\n .sort((a, b) => b.avg_views - a.avg_views);\n const used = new Set(hashtags.map((h) => h.tag.slice(1)));\n const trendCounts: Record = {};\n for (const r of trending as any[]) {\n for (\n const m of String(r.content || \"\").matchAll(\n /#([\\p{L}\\p{N}_]{2,30})/gu,\n )\n ) {\n const tag = m[1].toLowerCase();\n trendCounts[tag] = (trendCounts[tag] || 0) + 1;\n }\n }\n const suggestions = Object.entries(trendCounts)\n .filter(([tag]) => !used.has(tag))\n .sort((a, b) => b[1] - a[1])\n .slice(0, 6)\n .map(([tag, count]) => ({\n tag: `#${tag}`,\n platform_posts_7d: count,\n }));\n resultData = {\n period_days: days,\n hashtags: hashtags.slice(0, 10),\n best_hashtag: hashtags[0] || null,\n suggestions,\n message: hashtags.length === 0\n ? \"Aucun hashtag utilisé sur la période.\"\n : `Top hashtag : ${hashtags[0].tag} (${\n hashtags[0].avg_views\n } vues moyennes).`,\n };\n break;\n }\n\n default:\n throw new Error(`Outil inconnu : ${toolName}`);\n }\n\n const duration = Date.now() - startTime;\n await sql`\n INSERT INTO mai_tool_executions (user_id, tool_name, parameters, result, status, execution_time_ms)\n VALUES (${uid}, ${toolName}, ${JSON.stringify(args)}::jsonb, ${\n JSON.stringify(resultData)\n }::jsonb, 'success', ${duration})\n `;\n\n return { success: true, result: resultData };\n } catch (err: any) {\n console.error(`[MAIAgentFleet] Error executing tool ${toolName}:`, err);\n const duration = Date.now() - startTime;\n await sql`\n INSERT INTO mai_tool_executions (user_id, tool_name, parameters, result, status, execution_time_ms)\n VALUES (${uid}, ${toolName}, ${JSON.stringify(args)}::jsonb, ${\n JSON.stringify({ error: err.message })\n }::jsonb, 'failed', ${duration})\n `.catch(() => {});\n return { success: false, result: null, error: err.message };\n }\n }\n}","deleted",false,"label",[14],"200f8efb-f4ef-4117-ae26-ee737dbffb76",["D",1785339526786],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Avec l'API d'mAI, récupérez des données sur les projets !","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/cdf5d0c3-f220-4dec-712e-87455a037c00/square512?exp=1789538399&sig=cd777e882462fce47bac2003be9a9fe1267d4472bdb46131ff02f8b27f89b2ac","userId","7ed13655-83c2-4934-a981-01578302df33","pinnedFileId","968028a2-8b63-11f1-b718-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019f99eb-d944-7332-99f8-2fbb8b1e7011","tursoName","200f8efbf4ef4117ae26ee737dbffb76","tursoHostname","200f8efbf4ef4117ae26ee737dbffb76-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789474235934],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_107":103},"org",{"_9":10,"_100":101,"_102":103,"_56":57,"_104":105,"_106":42},"branch",{"_35":90,"_11":91,"_22":28,"_92":45,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":12,"_99":10},"isOwned",364,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","mProjects","avatar","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/ec72623f-c02d-461a-9b1c-c2f416214400/square512?exp=1789538399&sig=b90c7895d89b2c8a9d976c298024ed06d954dd92e3be0b0905971776951516a4","tier","free","canRestrictVals","profileImageUrl","ctx",{"_83":-5,"_141":142,"_143":144,"_145":146,"_147":42,"_148":42,"_149":42,"_150":32,"_151":-5,"_152":-7,"_153":-7,"_154":64,"_155":42,"_156":42,"_157":-5,"_158":42,"_159":42,"_160":42,"_161":-5},"ENV",{"_112":113,"_114":115,"_116":91,"_117":118,"_119":120,"_121":122,"_123":124,"_125":126,"_127":128,"_129":130,"_131":132,"_133":134,"_135":136,"_137":138,"_139":140},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]