[{"_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-dms-core.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-dms-core.ts","extension","id","20a1214b-17c6-4c57-9c98-10cf6ecbb42e","fileId","5c3d9f9c-aece-11f1-9133-1607ee4eb77e","parentId","branchId","3345cb6c-eeb2-4b61-ab03-8e0abc2dcd73","branchVersion",341,"latest",true,"createdAt",["D",1789311921611],"version",3,"fileType","script","content","/**\n * ============================================================================\n * VIBE SOCIAL PLATFORM — DMs : SOCLE PARTAGÉ (vibe-dms-core.ts)\n * Tables DM (ensureDMTables), compte officiel mAI, helpers SSE et requêtes\n * communes aux routes DMs. Scindé de vibe-dms.ts (limite de taille Val Town).\n * ============================================================================\n */\n\nimport {\n getDb,\n getTierMaiTokenLimit,\n getUserQuotaBoost,\n getWeekData,\n isPaidTier,\n} from \"./config.ts\";\nimport { pushRealtimeEvent } from \"./realtime.ts\";\nimport { stripHtmlTags } from \"./vibe-posts-core.ts\";\n\n/** Longueurs maximales d'un message privé (texte brut, HTML décompté) par forfait. */\nexport const DM_MESSAGE_CHARS_FREE = 3000;\nexport const DM_MESSAGE_CHARS_PAID = 10000;\nexport const dmPlainLength = (content: unknown): number =>\n stripHtmlTags(String(content ?? \"\")).length;\n\n/** Limite du compte : 10 000 caractères pour Plus/Pro/Max, 3 000 sinon. */\nexport const getDmMessageCharLimit = async (\n sql: any,\n userId: number,\n): Promise => {\n try {\n const rows = await sql`SELECT tier FROM users WHERE id = ${userId} LIMIT 1`;\n return isPaidTier(rows[0]?.tier)\n ? DM_MESSAGE_CHARS_PAID\n : DM_MESSAGE_CHARS_FREE;\n } catch {\n return DM_MESSAGE_CHARS_FREE;\n }\n};\n\n/**\n * Crée les tables de modération DM si besoin (idempotent, exécuté une fois).\n * — blocked_users : relations de blocage\n * — dm_reports : signalements\n * — dm_conv_meta : métadonnées locales par utilisateur (renommage conversation)\n * — dm_reactions : réactions emoji + colonne reply_to_id (réponses)\n */\nlet dmTablesReady = false;\nexport async function ensureDMTables() {\n if (dmTablesReady) return;\n try {\n const sql = getDb();\n await sql`\n CREATE TABLE IF NOT EXISTS blocked_users (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n user_id BIGINT NOT NULL,\n blocked_user_id BIGINT NOT NULL,\n created_at TIMESTAMPTZ DEFAULT NOW(),\n UNIQUE (user_id, blocked_user_id)\n )\n `;\n await sql`\n CREATE TABLE IF NOT EXISTS muted_users (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n user_id BIGINT NOT NULL,\n muted_user_id BIGINT NOT NULL,\n created_at TIMESTAMPTZ DEFAULT NOW(),\n UNIQUE (user_id, muted_user_id)\n )\n `;\n await sql`\n CREATE TABLE IF NOT EXISTS dm_reports (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n reporter_id BIGINT NOT NULL,\n reported_user_id BIGINT,\n message_id UUID,\n reason TEXT,\n created_at TIMESTAMPTZ DEFAULT NOW()\n )\n `;\n await sql`\n CREATE TABLE IF NOT EXISTS dm_conv_meta (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n user_id BIGINT NOT NULL,\n partner_id BIGINT NOT NULL,\n custom_name TEXT,\n UNIQUE (user_id, partner_id)\n )\n `;\n await sql`\n CREATE TABLE IF NOT EXISTS dm_reactions (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n message_id UUID NOT NULL,\n user_id BIGINT NOT NULL,\n emoji TEXT NOT NULL,\n created_at TIMESTAMPTZ DEFAULT NOW(),\n UNIQUE (message_id, user_id, emoji)\n )\n `;\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS reply_to_id UUID`\n .catch(() => {});\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS is_edited BOOLEAN DEFAULT FALSE`\n .catch(() => {});\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS edited_at TIMESTAMPTZ`\n .catch(() => {});\n // Messages de groupe (migration 019) : conversations multi-participants\n await sql`ALTER TABLE dm_conversations ADD COLUMN IF NOT EXISTS is_group BOOLEAN DEFAULT FALSE`\n .catch(() => {});\n // Les groupes utilisent participant_two_id = NULL (plusieurs groupes par compte\n // malgré UNIQUE(participant_one_id, participant_two_id) — les NULL sont distincts)\n await sql`ALTER TABLE dm_conversations ALTER COLUMN participant_two_id DROP NOT NULL`\n .catch(() => {});\n // Les messages de groupe n'ont pas de destinataire unique (lecture via read_by)\n await sql`ALTER TABLE direct_messages ALTER COLUMN recipient_id DROP NOT NULL`\n .catch(() => {});\n await sql`ALTER TABLE dm_conversations ADD COLUMN IF NOT EXISTS group_name VARCHAR(100)`\n .catch(() => {});\n await sql`ALTER TABLE dm_conversations ADD COLUMN IF NOT EXISTS group_avatar_url TEXT`\n .catch(() => {});\n await sql`ALTER TABLE dm_conversations ADD COLUMN IF NOT EXISTS created_by BIGINT`\n .catch(() => {});\n await sql`\n CREATE TABLE IF NOT EXISTS dm_group_members (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n conversation_id UUID NOT NULL REFERENCES dm_conversations(id) ON DELETE CASCADE,\n user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n role VARCHAR(20) DEFAULT 'member',\n joined_at TIMESTAMPTZ DEFAULT NOW(),\n added_by BIGINT,\n UNIQUE (conversation_id, user_id)\n )\n `.catch(() => {});\n await sql`CREATE INDEX IF NOT EXISTS idx_group_members_conv ON dm_group_members(conversation_id)`\n .catch(() => {});\n await sql`CREATE INDEX IF NOT EXISTS idx_group_members_user ON dm_group_members(user_id)`\n .catch(() => {});\n // Mentions urgentes (@tous / @utilisateur) + lecture multi-membres (groupes)\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS mentions JSONB DEFAULT '[]'`\n .catch(() => {});\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS urgent_mentions JSONB DEFAULT '[]'`\n .catch(() => {});\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS read_by JSONB DEFAULT '[]'`\n .catch(() => {});\n // Messages programmés (même mécanique que les posts planifiés)\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'sent'`\n .catch(() => {});\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS send_at TIMESTAMPTZ`\n .catch(() => {});\n await sql`UPDATE direct_messages SET status = 'sent' WHERE status IS NULL`\n .catch(() => {});\n await sql`CREATE INDEX IF NOT EXISTS idx_dm_scheduled ON direct_messages(status, send_at) WHERE status = 'scheduled'`\n .catch(() => {});\n // Traduction mémorisée, transfert (attribution) et masquage local (« pour moi »)\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS translations JSONB DEFAULT '{}'`\n .catch(() => {});\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS forwarded_from JSONB`\n .catch(() => {});\n await sql`\n CREATE TABLE IF NOT EXISTS dm_hidden_messages (\n user_id BIGINT NOT NULL,\n message_id UUID NOT NULL REFERENCES direct_messages(id) ON DELETE CASCADE,\n created_at TIMESTAMPTZ DEFAULT NOW(),\n PRIMARY KEY (user_id, message_id)\n )\n `.catch(() => {});\n await sql`CREATE INDEX IF NOT EXISTS idx_dm_hidden_user ON dm_hidden_messages(user_id)`\n .catch(() => {});\n // Messages épinglés (max 3 par conversation, visibles des deux participants)\n await sql`\n CREATE TABLE IF NOT EXISTS dm_pinned_messages (\n conversation_id UUID NOT NULL,\n message_id UUID NOT NULL REFERENCES direct_messages(id) ON DELETE CASCADE,\n pinned_by BIGINT NOT NULL,\n pinned_at TIMESTAMPTZ DEFAULT NOW(),\n PRIMARY KEY (conversation_id, message_id)\n )\n `.catch(() => {});\n // Conversations de Livre (chantier « Livres dans Messages ») : rattachement\n // d'une conversation de groupe à un Livre. Pas de FK : vibe_books peut ne\n // pas encore exister au démarrage.\n await sql`ALTER TABLE dm_conversations ADD COLUMN IF NOT EXISTS book_id UUID`\n .catch(() => {});\n await sql`CREATE UNIQUE INDEX IF NOT EXISTS idx_dm_conversations_book ON dm_conversations(book_id) WHERE book_id IS NOT NULL`\n .catch(() => {});\n // Publication jointe à un message (carte cliquable dans les conversations de Livre)\n await sql`ALTER TABLE direct_messages ADD COLUMN IF NOT EXISTS attached_post_id UUID`\n .catch(() => {});\n await sql`CREATE INDEX IF NOT EXISTS idx_dm_attached_post ON direct_messages(attached_post_id) WHERE attached_post_id IS NOT NULL`\n .catch(() => {});\n dmTablesReady = true;\n } catch (err) {\n console.warn(\"[vibe-dms] ensureDMTables skipped:\", (err as any)?.message);\n }\n}\n\n/**\n * Compte officiel mAI (pseudo-utilisateur) : répond automatiquement aux DMs.\n * Seed idempotent — même pattern que la migration 005 du compte @bot.\n */\nlet maiAccountReady = false;\nexport async function ensureMAIAccount() {\n if (maiAccountReady) return;\n try {\n const sql = getDb();\n await sql`\n INSERT INTO users (username, email, password_hash, tier, avatar_url, is_verified)\n VALUES (\n 'mai',\n 'mai@vibe.ai',\n '$2a$10$mAIvibeOfficialAccountNoLoginAllowedxxxxxxxxxxxxxxxxxxxx',\n 'Max',\n 'https://api.dicebear.com/7.x/bottts/svg?seed=mai-vibe',\n TRUE\n )\n ON CONFLICT (username) DO UPDATE\n SET is_verified = TRUE,\n tier = 'Max',\n avatar_url = 'https://api.dicebear.com/7.x/bottts/svg?seed=mai-vibe'\n `;\n await sql`\n INSERT INTO profiles (user_id, display_name, bio, avatar_url, is_verified)\n SELECT id, 'mAI', 'L''intelligence artificielle officielle de Vibe. Écrivez-moi en message privé : je réponds à toutes vos questions. ✨', 'https://api.dicebear.com/7.x/bottts/svg?seed=mai-vibe', TRUE\n FROM users WHERE username = 'mai'\n ON CONFLICT (user_id) DO UPDATE SET\n display_name = 'mAI',\n bio = 'L''intelligence artificielle officielle de Vibe. Écrivez-moi en message privé : je réponds à toutes vos questions. ✨',\n avatar_url = 'https://api.dicebear.com/7.x/bottts/svg?seed=mai-vibe',\n is_verified = TRUE\n `;\n await sql`\n INSERT INTO user_settings (user_id, allow_dms)\n SELECT id, 'everyone' FROM users WHERE username = 'mai'\n ON CONFLICT (user_id) DO NOTHING\n `;\n maiAccountReady = true;\n } catch (err) {\n console.warn(\"[vibe-dms] ensureMAIAccount skipped:\", (err as any)?.message);\n }\n}\n\n/** Identifiant du compte système @mai (auteur des réponses IA), ou null. */\nexport async function getMAIUserId(sql: any): Promise {\n try {\n const rows =\n await sql`SELECT id FROM users WHERE LOWER(username) = 'mai' LIMIT 1`;\n return rows.length > 0 ? Number(rows[0].id) : null;\n } catch {\n return null;\n }\n}\n\n/**\n * Réponse automatique du compte mAI à un DM : génère une réponse avec\n * OpenRouter en tenant compte de l'historique de la conversation, puis\n * débite l'usage hebdomadaire de l'expéditeur.\n */\nexport async function generateMAIDMReply(\n sql: any,\n conversationId: string,\n senderId: number,\n maiUserId: number,\n senderMessage: string,\n) {\n try {\n // Historique de la conversation DM (contexte complet pour mAI)\n const historyRows = await sql`\n SELECT m.content, m.sender_id, u.username as sender_username\n FROM direct_messages m\n JOIN users u ON u.id = m.sender_id\n WHERE m.conversation_id = ${conversationId}::uuid\n ORDER BY m.created_at DESC\n LIMIT 40\n `.catch(() => []);\n const transcript = historyRows\n .reverse()\n .map((m: any) =>\n `${\n m.sender_id === maiUserId ? \"mAI\" : `@${m.sender_username}`\n }: ${m.content}`\n )\n .join(\"\\n\");\n\n // Quota hebdomadaire mAI de l'expéditeur\n const { weekStartStr } = getWeekData();\n const [usageRow, senderRow] = await Promise.all([\n sql`SELECT tokens_used FROM weekly_usage WHERE user_id = ${senderId} AND week_start = ${weekStartStr}::date LIMIT 1`\n .catch(() => []),\n sql`SELECT tier FROM users WHERE id = ${senderId} LIMIT 1`.catch(\n () => []\n ),\n ]);\n const maiBoost = await getUserQuotaBoost(sql, String(senderId), \"mai\");\n const tokenLimit = getTierMaiTokenLimit(senderRow[0]?.tier) + maiBoost;\n const currentUsage = Number(usageRow[0]?.tokens_used || 0);\n\n let reply: string;\n if (currentUsage >= tokenLimit) {\n reply =\n \"⚠️ Votre quota hebdomadaire mAI est atteint. Il se réinitialise lundi — ou passez à un forfait supérieur pour discuter davantage avec moi.\";\n } else {\n const keyRows = await sql`\n SELECT api_key FROM mprojects_api_keys WHERE user_id::text = ${senderId}::text LIMIT 1\n `.catch(() => []);\n const openRouterApiKey =\n (typeof (globalThis as any).Deno !== \"undefined\" &&\n (globalThis as any).Deno.env?.get(\"OPENROUTER_API_KEY\")) ||\n (typeof process !== \"undefined\" && process.env?.OPENROUTER_API_KEY) ||\n (keyRows.length > 0 ? keyRows[0].api_key : \"\");\n\n const systemPrompt =\n \"Tu es mAI, l'intelligence artificielle officielle du réseau social Vibe. Tu réponds aux messages privés des utilisateurs comme un vrai interlocuteur : bienveillant, concis et utile. \" +\n \"Tu réponds en français avec quelques émojis. Tu connais tout l'historique de la conversation : poursuis naturellement l'échange sans redemander des informations déjà données. \" +\n \"Ne fais AUCUNE évaluation de sécurité, n'écris JAMAIS 'User Safety: safe' ni aucun méta-commentaire.\";\n\n const userPrompt = (transcript.trim()\n ? `Historique de la conversation :\\n${transcript}\\n\\n`\n : \"Nouvelle conversation.\\n\\n\") +\n `L'utilisateur vient d'envoyer : « ${senderMessage} »\\n\\nRéponds UNIQUEMENT avec le texte de ta réponse en DM.`;\n\n reply = \"\";\n if (openRouterApiKey) {\n const candidateModels = [\n \"poolside/laguna-xs-2.1:free\",\n \"minimax/minimax-m2.7:free\",\n \"liquid/lfm-2.5-2.6b:free\",\n \"nvidia/nemotron-3.5-lightning:free\",\n ];\n for (const modelToTry of candidateModels) {\n try {\n const aiRes = await fetch(\n \"https://openrouter.ai/api/v1/chat/completions\",\n {\n method: \"POST\",\n headers: {\n Authorization: `Bearer ${openRouterApiKey}`,\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: modelToTry,\n messages: [\n { role: \"system\", content: systemPrompt },\n { role: \"user\", content: userPrompt },\n ],\n }),\n },\n );\n if (aiRes.ok) {\n const aiData = await aiRes.json();\n const text = aiData.choices?.[0]?.message?.content;\n if (text && typeof text === \"string\" && text.trim()) {\n reply = text\n .replace(/User Safety:\\s*safe\\.?/gi, \"\")\n .replace(/^User Safety:[^\\n]*\\n*/gi, \"\")\n .trim();\n break;\n }\n }\n } catch (callErr) {\n console.warn(\n `[vibe-dms] mAI DM modèle ${modelToTry} en échec:`,\n callErr,\n );\n }\n }\n }\n if (!reply) {\n reply =\n \"Je n'arrive pas à générer ma réponse pour le moment (service IA momentanément indisponible). Réessayez dans un instant ✨\";\n }\n\n // Débit de l'usage hebdomadaire de l'expéditeur (chaque message compte)\n const estimatedTokens = Math.max(\n 75,\n Math.ceil((userPrompt.length + reply.length) / 3),\n );\n await sql`\n INSERT INTO weekly_usage (user_id, week_start, tokens_used)\n VALUES (${senderId}, ${weekStartStr}::date, ${estimatedTokens})\n ON CONFLICT (user_id, week_start)\n DO UPDATE SET tokens_used = weekly_usage.tokens_used + ${estimatedTokens}, updated_at = NOW()\n `.catch(() => {});\n }\n\n // Insertion de la réponse mAI dans la conversation\n const maiMsg = await sql`\n INSERT INTO direct_messages (conversation_id, sender_id, recipient_id, content)\n VALUES (${conversationId}::uuid, ${maiUserId}, ${senderId}, ${reply})\n RETURNING *\n `;\n await sql`\n UPDATE dm_conversations SET last_message_preview = ${\n reply.slice(0, 120)\n }, last_message_at = NOW()\n WHERE id = ${conversationId}::uuid\n `.catch(() => {});\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, message)\n VALUES (${senderId}, ${maiUserId}, 'dm', 'vous a envoyé un message')\n `.catch(() => {});\n\n // Temps réel : pousse la réponse mAI à l'expéditeur (flux SSE)\n try {\n await pushRealtimeEvent(\n senderId,\n \"dm_message\",\n maiMsg[0] || {\n conversation_id: conversationId,\n sender_id: maiUserId,\n recipient_id: senderId,\n content: reply,\n },\n );\n } catch {}\n } catch (err) {\n console.warn(\n \"[vibe-dms] generateMAIDMReply failed:\",\n (err as any)?.message,\n );\n }\n}\n\n/**\n * Publication paresseuse des DMs programmés arrivés à échéance (miroir\n * publishDuePosts) : appelée en tête de handleDMMessages, throttle 30 s.\n */\nlet lastDMpublishCheck = 0;\nexport async function publishScheduledDMs(): Promise {\n const now = Date.now();\n if (now - lastDMpublishCheck < 30_000) return;\n lastDMpublishCheck = now;\n try {\n const sql = getDb();\n const due = await sql`\n UPDATE direct_messages\n SET status = 'sent', send_at = NULL\n WHERE status = 'scheduled' AND send_at IS NOT NULL AND send_at <= NOW()\n RETURNING *\n `.catch(() => []);\n for (const m of due || []) {\n try {\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, message)\n VALUES (${m.recipient_id}, ${m.sender_id}, 'dm', 'vous a envoyé un message')\n `.catch(() => {});\n await pushRealtimeEvent(Number(m.recipient_id), \"dm_message\", m);\n } catch {}\n }\n } catch (err) {\n console.warn(\n \"[vibe-dms] publishScheduledDMs skipped:\",\n (err as any)?.message,\n );\n }\n}\n\n/** Résout l'UUID de conversation dm_conversations depuis (userId, partnerId). */\nexport async function resolveConversationId(\n sql: any,\n userId: number,\n partnerId: number,\n): Promise {\n try {\n const p1 = userId < partnerId ? userId : partnerId;\n const p2 = userId < partnerId ? partnerId : userId;\n const rows = await sql`\n SELECT id FROM dm_conversations\n WHERE participant_one_id = ${p1} AND participant_two_id = ${p2}\n LIMIT 1\n `;\n return rows[0] ? String(rows[0].id) : null;\n } catch {\n return null;\n }\n}\n\n/**\n * Résout une conversation depuis un partnerId brut : \"123\" (1-à-1) ou\n * \"group:\" (groupe). Vérifie l'appartenance de l'utilisateur et\n * retourne les membres pour la diffusion SSE.\n */\nexport async function resolveConversationTarget(\n sql: any,\n userId: number,\n raw: string,\n): Promise<\n { conversationId: string; isGroup: boolean; memberIds: number[] } | null\n> {\n try {\n if (raw.startsWith(\"group:\")) {\n const groupId = raw.slice(6);\n if (\n !/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(\n groupId,\n )\n ) return null;\n const memberRows =\n await sql`SELECT user_id FROM dm_group_members WHERE conversation_id = ${groupId}::uuid`;\n const memberIds = (memberRows as any[]).map((r) => Number(r.user_id));\n if (!memberIds.includes(userId)) return null;\n return { conversationId: groupId, isGroup: true, memberIds };\n }\n const partnerId = Number(raw);\n if (!partnerId || partnerId === userId) return null;\n const conversationId = await resolveConversationId(sql, userId, partnerId);\n if (!conversationId) return null;\n return { conversationId, isGroup: false, memberIds: [userId, partnerId] };\n } catch {\n return null;\n }\n}\n\n/** Diffuse un événement SSE à plusieurs utilisateurs (fan-out groupe). */\nexport async function pushToUsers(\n ids: number[],\n type: string,\n payload: any,\n exclude?: number,\n): Promise {\n for (const id of ids) {\n if (exclude && id === exclude) continue;\n try {\n await pushRealtimeEvent(id, type, payload);\n } catch {}\n }\n}\n\n/** Messages épinglés d'une conversation (aperçus, max 3), masqués exclus. */\nexport async function fetchPinnedMessages(\n sql: any,\n conversationId: string,\n userId: number,\n): Promise {\n try {\n return await sql`\n SELECT m.*, u.username as sender_username\n FROM dm_pinned_messages pm\n JOIN direct_messages m ON m.id = pm.message_id\n JOIN users u ON u.id = m.sender_id\n WHERE pm.conversation_id = ${conversationId}::uuid\n AND NOT EXISTS (SELECT 1 FROM dm_hidden_messages h WHERE h.message_id = m.id AND h.user_id = ${userId})\n ORDER BY pm.pinned_at ASC\n LIMIT 3\n `;\n } catch {\n return [];\n }\n}\n\n/** Infos publiques de la publication jointe à un message (carte cliquable). */\nexport async function fetchDMAttachedPost(\n sql: any,\n postId: string,\n): Promise<\n {\n id: string;\n username: string;\n display_name: string | null;\n avatar_url: string | null;\n excerpt: string;\n } | null\n> {\n try {\n const rows = await sql`\n SELECT p.id, p.content, u.username, pr.display_name, pr.avatar_url\n FROM posts p\n JOIN users u ON u.id = p.author_id\n LEFT JOIN profiles pr ON pr.user_id = p.author_id\n WHERE p.id = ${postId}::uuid\n LIMIT 1\n `;\n if (rows.length === 0) return null;\n const r = rows[0];\n return {\n id: String(r.id),\n username: String(r.username),\n display_name: r.display_name || null,\n avatar_url: r.avatar_url || null,\n excerpt: stripHtmlTags(String(r.content || \"\")).replace(/\\s+/g, \" \")\n .trim().slice(0, 140),\n };\n } catch {\n return null;\n }\n}\n\n/** Enrichit des messages lus avec leur publication jointe (1 requête batch). */\nexport async function attachDMAttachedPosts(\n sql: any,\n messages: any[],\n): Promise {\n if (!Array.isArray(messages) || messages.length === 0) return;\n const ids = Array.from(\n new Set(\n messages.filter((m) => m?.attached_post_id).map((m) =>\n String(m.attached_post_id)\n ),\n ),\n );\n if (ids.length === 0) return;\n try {\n const rows = await sql`\n SELECT p.id, p.content, u.username, pr.display_name, pr.avatar_url\n FROM posts p\n JOIN users u ON u.id = p.author_id\n LEFT JOIN profiles pr ON pr.user_id = p.author_id\n WHERE p.id = ANY(${ids}::uuid[])\n `;\n const byId = new Map(\n (rows as any[]).map((r) => [String(r.id), {\n id: String(r.id),\n username: String(r.username),\n display_name: r.display_name || null,\n avatar_url: r.avatar_url || null,\n excerpt: stripHtmlTags(String(r.content || \"\")).replace(/\\s+/g, \" \")\n .trim().slice(0, 140),\n }]),\n );\n for (const m of messages) {\n if (m?.attached_post_id) {\n m.attached_post = byId.get(String(m.attached_post_id)) || null;\n }\n }\n } catch {}\n}\n\n/**\n * Normalise l'attribution d'un message transféré : seul l'id du message\n * d'origine est accepté, l'auteur est re-dérivé depuis la base (anti-usurpation).\n */\nexport async function deriveForwardedFrom(\n sql: any,\n raw: any,\n): Promise<\n { message_id: string; username: string; display_name: string | null } | null\n> {\n try {\n if (!raw || typeof raw !== \"object\" || !raw.message_id) return null;\n const id = String(raw.message_id);\n if (\n !/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(\n id,\n )\n ) return null;\n const rows = await sql`\n SELECT m.id AS message_id, u.username, pr.display_name\n FROM direct_messages m\n JOIN users u ON u.id = m.sender_id\n LEFT JOIN profiles pr ON pr.user_id = u.id\n WHERE m.id = ${id}::uuid\n LIMIT 1\n `;\n if (rows.length === 0) return null;\n return {\n message_id: String(rows[0].message_id),\n username: String(rows[0].username),\n display_name: rows[0].display_name || null,\n };\n } catch {\n return null;\n }\n}\n\n/** Vérifie qu'un utilisateur peut agir sur un message (expéditeur, destinataire ou membre du groupe). */\nexport const assertMessageAccess = async (\n sql: any,\n userId: number,\n messageId: string,\n): Promise<{ ok: boolean; message?: any; error?: string; code?: number }> => {\n const rows =\n await sql`SELECT id, sender_id, recipient_id, conversation_id FROM direct_messages WHERE id = ${messageId}::uuid LIMIT 1`;\n if (rows.length === 0) {\n return { ok: false, error: \"Message introuvable.\", code: 404 };\n }\n const m = rows[0];\n if (Number(m.sender_id) === userId || Number(m.recipient_id) === userId) {\n return { ok: true, message: m };\n }\n const memberRows = await sql`\n SELECT 1 FROM dm_group_members WHERE conversation_id = ${m.conversation_id}::uuid AND user_id = ${userId} LIMIT 1\n `;\n if (memberRows.length === 0) {\n return { ok: false, error: \"Accès refusé.\", code: 403 };\n }\n return { ok: true, message: m };\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=1789534799&sig=d0e9bd008a19d21a4278d66ba8a82f38d1cd4969f07f21769dd2a06ad26857ad","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=1789534799&sig=35a45bbea9f7c308b27b7036fc6bf7e06c021ee516b5deb1d8a1b8e08015b411","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"]