[{"_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-posts-crud.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-posts-crud.ts","extension","id","94f77583-fda3-4a64-b71e-f55bf8dceb6f","fileId","c96c0516-ae03-11f1-9d35-1607ee4eb77e","parentId","branchId","3345cb6c-eeb2-4b61-ab03-8e0abc2dcd73","branchVersion",312,"latest",true,"createdAt",["D",1789229675080],"version",4,"fileType","script","content","/**\n * ============================================================================\n * VIBE SOCIAL PLATFORM — POSTS CRUD & EXTENSIONS (vibe-posts-crud.ts)\n * Création / lecture / modification / suppression de posts, sondages (vote),\n * statistiques créateur, co-signature, posts programmés et brouillons serveur.\n * Enregistré par vibe-posts.ts (registerVibePostsRoutes) via\n * registerPostCrudRoutes — l'ordre des registerMulti est inchangé.\n * ============================================================================\n */\n\nimport {\n extractToken,\n getDb,\n isPaidTier,\n rateLimit,\n verifyToken,\n} from \"./config.ts\";\nimport { isBlockEitherWay, type RegisterMultiFn } from \"./vibe-common.ts\";\nimport { MAIAgentFleet } from \"./vibe-mai-fleet.ts\";\nimport { pushRealtimeEvent } from \"./realtime.ts\";\nimport { ensureCircleTable } from \"./vibe-circle.ts\";\nimport {\n attachBookRefs,\n attachPollsAndCollabs,\n attachQuotedPosts,\n ensurePostColumns,\n extractBookRefIds,\n fetchPostMedia,\n isUuid,\n publishDuePosts,\n stripHtmlTags,\n syncPostBookRefs,\n visibilityFilter,\n} from \"./vibe-posts-core.ts\";\nimport { ensureBooksTables } from \"./vibe-books.ts\";\n\n/** Longueur maximale d'une Vibe (publication), en texte brut, par forfait. */\nconst POST_CHARS_LIMIT_FREE = 1_000;\n/** Garde-fou technique pour les forfaits payants (« caractères illimités » en pratique). */\nconst POST_CHARS_LIMIT_PAID = 50_000;\n\nconst postCharsError = (isPaid: boolean, charLimit: number) => ({\n error: isPaid\n ? \"La publication est trop longue.\"\n : `Une Vibe est limitée à ${\n POST_CHARS_LIMIT_FREE.toLocaleString(\"fr-FR\")\n } caractères (texte) avec le forfait Free. Passez à Plus, Pro ou Max pour publier sans limite.`,\n char_limit: charLimit,\n plan_required: !isPaid,\n});\n\nexport function registerPostCrudRoutes(registerMulti: RegisterMultiFn) {\n // 1. POSTS CRUD\n const handleCreatePost = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n\n // Anti-spam : 10 posts / 5 min / utilisateur\n if (!rateLimit(`post:${userId}`, 10, 5 * 60_000)) {\n return c.json({\n error: \"Vous publiez trop vite. Patientez un instant.\",\n }, 429);\n }\n\n const body = await c.req.json();\n const {\n content,\n format = \"micro_text\",\n media_url,\n media_assets = [],\n quoted_post_id,\n } = body;\n // Audience : Public (défaut), Abonnés uniquement, Cercle Privé, privé (soi seul)\n const visibility =\n [\"public\", \"followers\", \"circle\", \"private\"].includes(body.visibility)\n ? body.visibility\n : \"public\";\n const sql = getDb();\n await ensurePostColumns();\n await ensureCircleTable().catch(() => {});\n\n if (!content || !content.trim()) {\n return c.json({ error: \"Le contenu est obligatoire.\" }, 400);\n }\n\n // Limite de longueur par forfait (texte brut, balises de mise en forme exclues)\n const tierRows =\n await sql`SELECT tier FROM users WHERE id = ${userId} LIMIT 1`;\n const isPaid = isPaidTier(tierRows[0]?.tier);\n const charLimit = isPaid ? POST_CHARS_LIMIT_PAID : POST_CHARS_LIMIT_FREE;\n if (stripHtmlTags(String(content)).length > charLimit) {\n return c.json(postCharsError(isPaid, charLimit), 400);\n }\n\n // Scan de sécurité sur le texte brut (sans balises de mise en forme)\n const safety = MAIAgentFleet.assessContentSafety(stripHtmlTags(content));\n\n // Planification de publication : réservée aux abonnés Plus / Pro / Max\n let scheduledAt: string | null = null;\n let postStatus = \"published\";\n if (body.scheduled_at) {\n const ts = Date.parse(String(body.scheduled_at));\n if (Number.isNaN(ts) || ts <= Date.now()) {\n return c.json(\n { error: \"Date de planification invalide ou passée.\" },\n 400,\n );\n }\n if (!isPaid) {\n return c.json(\n {\n error:\n \"La planification des vibes est réservée aux abonnés Plus, Pro et Max.\",\n plan_required: true,\n },\n 403,\n );\n }\n scheduledAt = new Date(ts).toISOString();\n postStatus = \"scheduled\";\n }\n\n if (!safety.isSafe) {\n return c.json(\n { error: `Publication refusée : ${safety.flagReason}` },\n 403,\n );\n }\n\n // Badge « Créé avec l'IA » : valeur fournie par le client, sinon\n // réglage utilisateur posts_ai_generated_by_default\n let aiGeneratedFlag = body.ai_generated;\n if (aiGeneratedFlag === undefined || aiGeneratedFlag === null) {\n try {\n const settingsRows =\n await sql`SELECT posts_ai_generated_by_default FROM user_settings WHERE user_id = ${userId} LIMIT 1`;\n aiGeneratedFlag = Boolean(\n settingsRows[0]?.posts_ai_generated_by_default,\n );\n } catch {\n aiGeneratedFlag = false;\n }\n }\n aiGeneratedFlag = Boolean(aiGeneratedFlag);\n\n // Post cité (quote-post) : validation existence + visibilité\n let quotedId: string | null = null;\n if (\n quoted_post_id && typeof quoted_post_id === \"string\" &&\n isUuid(quoted_post_id)\n ) {\n const quotedRows =\n await sql`SELECT id, author_id, visibility FROM posts WHERE id = ${quoted_post_id}::uuid LIMIT 1`;\n if (quotedRows.length > 0) {\n const quoted = quotedRows[0];\n const quotedAuthor = Number(quoted.author_id);\n let canQuote = quoted.visibility === \"public\" ||\n quotedAuthor === userId;\n if (!canQuote && quoted.visibility === \"followers\") {\n const followerRows =\n await sql`SELECT 1 FROM follows WHERE follower_id = ${userId} AND following_id = ${quotedAuthor} LIMIT 1`;\n canQuote = followerRows.length > 0;\n } else if (!canQuote && quoted.visibility === \"circle\") {\n const memberRows =\n await sql`SELECT 1 FROM circle_members WHERE user_id = ${quotedAuthor} AND member_user_id = ${userId} LIMIT 1`;\n canQuote = memberRows.length > 0;\n }\n if (canQuote) quotedId = String(quoted.id);\n }\n }\n\n const inserted = await sql`\n INSERT INTO posts (author_id, content, format, visibility, toxicity_score, created_via, ai_generated, quoted_post_id, status, scheduled_at)\n VALUES (${userId}, ${content.trim()}, ${format}, ${visibility}, ${safety.toxicityScore}, 'web', ${aiGeneratedFlag}, ${quotedId}, ${postStatus}, ${scheduledAt})\n RETURNING *\n `;\n\n const newPost = inserted[0];\n\n // Références de Livres (@livre / attachement) : extraites du HTML et persistées\n const bookRefIds = extractBookRefIds(String(content));\n if (bookRefIds.length > 0) {\n await ensureBooksTables(sql).catch(() => {});\n await syncPostBookRefs(sql, String(newPost.id), bookRefIds);\n }\n\n // Inférence du type MIME à partir de l'extension du fichier\n const inferMediaType = (url: string, fallback = \"image/jpeg\"): string => {\n try {\n const cleanUrl = url.split(\"?\")[0].split(\"#\")[0];\n const ext = cleanUrl.split(\".\").pop()?.toLowerCase();\n switch (ext) {\n case \"png\":\n return \"image/png\";\n case \"webp\":\n return \"image/webp\";\n case \"gif\":\n return \"image/gif\";\n case \"svg\":\n return \"image/svg+xml\";\n case \"jpg\":\n case \"jpeg\":\n return \"image/jpeg\";\n case \"mp4\":\n return \"video/mp4\";\n case \"webm\":\n return \"video/webm\";\n case \"mov\":\n return \"video/quicktime\";\n case \"mp3\":\n return \"audio/mpeg\";\n case \"wav\":\n return \"audio/wav\";\n case \"ogg\":\n return \"audio/ogg\";\n default:\n return fallback;\n }\n } catch {\n return fallback;\n }\n };\n\n // Normalisation des médias (support de media_url et de la liste media_assets)\n const normalizedMedia: Array<{\n url: string;\n media_type: string;\n file_size_bytes: number;\n alt_text: string;\n }> = [];\n\n if (Array.isArray(media_assets)) {\n for (const media of media_assets) {\n if (!media || !media.url) continue;\n const urlStr = String(media.url).trim();\n if (!urlStr) continue;\n normalizedMedia.push({\n url: urlStr,\n media_type: media.media_type || media.type ||\n inferMediaType(urlStr),\n file_size_bytes: Math.max(\n 0,\n Math.round(\n Number(\n media.file_size_bytes ?? media.size ?? media.file_size ?? 0,\n ) || 0,\n ),\n ),\n alt_text: media.alt_text || media.alt || \"\",\n });\n }\n }\n\n if (media_url && typeof media_url === \"string\" && media_url.trim()) {\n const trimmedUrl = media_url.trim();\n const alreadyPresent = normalizedMedia.some((m) =>\n m.url === trimmedUrl\n );\n if (!alreadyPresent) {\n normalizedMedia.unshift({\n url: trimmedUrl,\n media_type: body.media_type || inferMediaType(trimmedUrl),\n file_size_bytes: Math.max(\n 0,\n Math.round(\n Number(\n body.file_size_bytes ?? body.size ?? body.file_size ?? 0,\n ) || 0,\n ),\n ),\n alt_text: body.alt_text || \"\",\n });\n }\n }\n\n const insertedMediaList: any[] = [];\n // Position explicite du client (drag & drop) sinon ordre d'affichage\n const mediaPositions = Array.isArray(body.media_positions)\n ? body.media_positions.map((v: any) => Number(v) || 0)\n : [];\n let posIdx = 0;\n for (const media of normalizedMedia) {\n const position = Number.isFinite(mediaPositions[posIdx])\n ? mediaPositions[posIdx]\n : posIdx;\n posIdx += 1;\n const res = await sql`\n INSERT INTO media_assets (owner_id, post_id, url, media_type, file_size_bytes, alt_text, position)\n VALUES (${userId}, ${newPost.id}::uuid, ${media.url}, ${media.media_type}, ${media.file_size_bytes}, ${media.alt_text}, ${position})\n RETURNING id, url, media_type, file_size_bytes, alt_text\n `.catch(async () =>\n await sql`\n INSERT INTO media_assets (owner_id, post_id, url, media_type, file_size_bytes, alt_text)\n VALUES (${userId}, ${newPost.id}::uuid, ${media.url}, ${media.media_type}, ${media.file_size_bytes}, ${media.alt_text})\n RETURNING id, url, media_type, file_size_bytes, alt_text\n `\n );\n if (res && res[0]) {\n insertedMediaList.push(res[0]);\n }\n }\n\n // Sondage intégré : { options: string[2..4], duration_hours: 1h..7j, question? }\n let createdPoll: any = null;\n try {\n const pollBody = (body as any)?.poll;\n if (pollBody && Array.isArray(pollBody.options)) {\n const labels = pollBody.options.map((o: any) =>\n String(o ?? \"\").trim()\n ).filter(Boolean).slice(0, 4);\n const durationHours = Math.min(\n 24 * 7,\n Math.max(1, Math.round(Number(pollBody.duration_hours) || 24)),\n );\n if (labels.length >= 2) {\n const question =\n String(pollBody.question || \"\").trim().slice(0, 120) ||\n \"Votre avis ?\";\n const endsAt = new Date(Date.now() + durationHours * 3600_000)\n .toISOString();\n const pollRows = await sql`\n INSERT INTO post_polls (post_id, question, ends_at)\n VALUES (${newPost.id}::uuid, ${question}, ${endsAt}::timestamptz)\n RETURNING *\n `;\n if (pollRows[0]) {\n createdPoll = {\n ...pollRows[0],\n options: [],\n my_vote: null,\n expired: false,\n };\n for (let i = 0; i < labels.length; i++) {\n const optRows = await sql`\n INSERT INTO post_poll_options (poll_id, label, position)\n VALUES (${pollRows[0].id}::uuid, ${\n labels[i].slice(0, 80)\n }, ${i})\n RETURNING *\n `;\n if (optRows[0]) createdPoll.options.push(optRows[0]);\n }\n }\n }\n }\n } catch (pollErr) {\n console.warn(\"[Vibe API] Erreur création sondage:\", pollErr);\n }\n\n // Co-auteur : { collaborator_username }\n // - réglage invité collab_auto_accept = TRUE → co-signature directe (status\n // 'accepted', auto_accepted = TRUE, simple notification, AUCUNE invitation)\n // - sinon → invitation 'pending' à accepter/refuser (comportement historique)\n let createdCollaborators: any[] = [];\n try {\n const collabUsername = String(\n (body as any)?.collaborator_username || \"\",\n ).trim().replace(/^@/, \"\").toLowerCase();\n if (collabUsername) {\n const targetRows =\n await sql`SELECT id, username FROM users WHERE LOWER(username) = ${collabUsername} LIMIT 1`;\n const targetId = targetRows[0] ? Number(targetRows[0].id) : 0;\n if (\n targetId && targetId !== userId &&\n !(await isBlockEitherWay(userId, targetId))\n ) {\n // Réglage d'auto-acceptation du co-auteur désigné\n let autoAccept = false;\n try {\n const s =\n await sql`SELECT collab_auto_accept FROM user_settings WHERE user_id = ${targetId} LIMIT 1`\n .catch(() => []);\n autoAccept = Boolean(s[0]?.collab_auto_accept);\n } catch {}\n if (autoAccept) {\n await sql`\n INSERT INTO post_collaborators (post_id, user_id, status, auto_accepted, accepted_at)\n VALUES (${newPost.id}::uuid, ${targetId}, 'accepted', TRUE, NOW())\n ON CONFLICT (post_id, user_id) DO NOTHING\n `.catch(() => {});\n createdCollaborators = [{\n username: targetRows[0].username,\n status: \"accepted\",\n }];\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, post_id, message)\n VALUES (${targetId}, ${userId}, 'mention', ${newPost.id}::uuid, 'vous a ajouté comme co-auteur d\\'une publication')\n `.catch(() => {});\n try {\n await pushRealtimeEvent(targetId, \"notification\", {\n type: \"collab_accepted\",\n post_id: newPost.id,\n actor_id: userId,\n });\n } catch {}\n } else {\n await sql`\n INSERT INTO post_collaborators (post_id, user_id, status)\n VALUES (${newPost.id}::uuid, ${targetId}, 'pending')\n ON CONFLICT (post_id, user_id) DO NOTHING\n `.catch(() => {});\n createdCollaborators = [{\n username: targetRows[0].username,\n status: \"pending\",\n }];\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, post_id, message)\n VALUES (${targetId}, ${userId}, 'mention', ${newPost.id}::uuid, 'vous a invité à co-signer une publication')\n `.catch(() => {});\n try {\n await pushRealtimeEvent(targetId, \"notification\", {\n type: \"collab_invite\",\n post_id: newPost.id,\n actor_id: userId,\n });\n } catch {}\n }\n }\n }\n } catch (collabErr) {\n console.warn(\"[Vibe API] Erreur invitation co-auteur:\", collabErr);\n }\n\n const userRow =\n await sql`SELECT username FROM users WHERE id = ${userId} LIMIT 1`;\n const profileRow =\n await sql`SELECT display_name, avatar_url FROM profiles WHERE user_id = ${userId} LIMIT 1`;\n\n // Publication planifiée : le compteur de posts et les notifications\n // (mentions, citations) seront déclenchés à l'échéance (publishDuePosts).\n if (postStatus === \"scheduled\") {\n const createdPost: any = {\n ...newPost,\n username: userRow[0]?.username,\n display_name: profileRow[0]?.display_name || userRow[0]?.username,\n avatar_url: profileRow[0]?.avatar_url,\n media_assets: insertedMediaList,\n quoted_post: null,\n poll: createdPoll,\n collaborators: createdCollaborators,\n };\n await attachBookRefs([createdPost], userId).catch(() => {});\n return c.json({ success: true, post: createdPost }, 201);\n }\n\n await sql`UPDATE profiles SET posts_count = posts_count + 1 WHERE user_id = ${userId}`;\n\n // Détection et notification des mentions @username dans les publications\n // (les ancres de Livres @livre sont retirées du scan pour éviter de faux utilisateurs)\n try {\n const plainContent = stripHtmlTags(\n String(content).replace(/]*data-book-id[\\s\\S]*?<\\/a>/gi, \" \"),\n );\n const mentionMatches = Array.from(\n new Set(plainContent.match(/@([a-zA-Z0-9_]{1,30})/g) || []),\n ).map((m: string) => m.slice(1).toLowerCase());\n if (mentionMatches.length > 0) {\n const mentionedUsers = await sql`\n SELECT id, username FROM users\n WHERE LOWER(username) = ANY(${mentionMatches}) AND id <> ${userId}\n `;\n const snippet = plainContent.length > 45\n ? `${plainContent.slice(0, 45)}…`\n : plainContent;\n for (const u of mentionedUsers) {\n // Blocage croisé : aucune notification de mention ne traverse un blocage\n if (await isBlockEitherWay(userId, Number(u.id))) continue;\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, post_id, message)\n VALUES (${u.id}, ${userId}, 'mention', ${newPost.id}::uuid, ${`vous a mentionné dans une publication : « ${snippet} »`})\n `.catch(() => {});\n }\n }\n } catch (mentionErr) {\n console.warn(\n \"[Vibe API] Erreur notification mention post:\",\n mentionErr,\n );\n }\n\n // Notification de citation à l'auteur du post original\n if (quotedId) {\n try {\n const quotedRows =\n await sql`SELECT author_id, content FROM posts WHERE id = ${quotedId}::uuid LIMIT 1`;\n const recipientId = Number(quotedRows[0]?.author_id);\n if (\n recipientId && recipientId !== userId &&\n !(await isBlockEitherWay(userId, recipientId))\n ) {\n const plainQuoted = stripHtmlTags(content);\n const qSnippet = plainQuoted.length > 45\n ? `${plainQuoted.slice(0, 45)}…`\n : plainQuoted;\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, post_id, message)\n VALUES (${recipientId}, ${userId}, 'quote', ${newPost.id}::uuid, ${`a cité votre publication : « ${qSnippet} »`})\n `.catch(() => {});\n }\n } catch (quoteErr) {\n console.warn(\"[Vibe API] Erreur notification citation:\", quoteErr);\n }\n }\n\n // Notification aux abonnés aux posts de ce compte (post_subscriptions)\n try {\n const subscribers = await sql`\n SELECT ps.subscriber_id FROM post_subscriptions ps\n WHERE ps.author_id = ${userId} AND ps.subscriber_id <> ${userId}\n `;\n const plainSub = stripHtmlTags(content);\n const subSnippet = plainSub.length > 45\n ? `${plainSub.slice(0, 45)}…`\n : plainSub;\n for (const s of subscribers) {\n if (await isBlockEitherWay(userId, Number(s.subscriber_id))) continue;\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, post_id, message)\n VALUES (${s.subscriber_id}, ${userId}, 'post', ${newPost.id}::uuid, ${`a publié une nouvelle Vibe : « ${subSnippet} »`})\n `.catch(() => {});\n }\n } catch (subErr) {\n console.warn(\"[Vibe API] Erreur notification abonnés posts:\", subErr);\n }\n\n const createdPost: any = {\n ...newPost,\n username: userRow[0]?.username,\n display_name: profileRow[0]?.display_name || userRow[0]?.username,\n avatar_url: profileRow[0]?.avatar_url,\n media_assets: insertedMediaList,\n quoted_post: null,\n poll: createdPoll,\n collaborators: createdCollaborators,\n };\n await attachQuotedPosts([createdPost]);\n await attachBookRefs([createdPost], userId).catch(() => {});\n\n return c.json({\n success: true,\n post: createdPost,\n }, 201);\n } catch (err: any) {\n console.error(\"[Vibe API] Error creating post:\", err);\n return c.json({ error: \"Erreur serveur lors de la publication.\" }, 500);\n }\n };\n\n registerMulti(\"post\", [\n \"/api/vibe/posts\",\n \"/vibe/posts\",\n \"/v1/posts\",\n \"/posts\",\n ], handleCreatePost);\n\n // 1b. POLL VOTE (vote unique modifiable)\n const handlePollVote = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const postId = c.req.param(\"id\");\n if (!isUuid(postId)) {\n return c.json({ error: \"Identifiant invalide.\" }, 400);\n }\n const body = await c.req.json().catch(() => ({}));\n const optionId = String(body?.option_id || \"\");\n if (!isUuid(optionId)) return c.json({ error: \"Option invalide.\" }, 400);\n const sql = getDb();\n await ensurePostColumns();\n const polls =\n await sql`SELECT * FROM post_polls WHERE post_id = ${postId}::uuid LIMIT 1`;\n if (polls.length === 0) {\n return c.json({ error: \"Aucun sondage sur ce post.\" }, 404);\n }\n const poll = polls[0];\n if (new Date(poll.ends_at).getTime() <= Date.now()) {\n return c.json({ error: \"Sondage expiré.\" }, 410);\n }\n const optRows =\n await sql`SELECT * FROM post_poll_options WHERE id = ${optionId}::uuid AND poll_id = ${poll.id}::uuid LIMIT 1`;\n if (optRows.length === 0) {\n return c.json({ error: \"Option introuvable pour ce sondage.\" }, 404);\n }\n const existing =\n await sql`SELECT option_id FROM post_poll_votes WHERE poll_id = ${poll.id}::uuid AND user_id = ${userId} LIMIT 1`;\n if (existing.length > 0 && String(existing[0].option_id) === optionId) {\n // Idempotent : même option → résultats courants\n } else if (existing.length > 0) {\n const oldOptionId = String(existing[0].option_id);\n await sql`UPDATE post_poll_votes SET option_id = ${optionId}::uuid, voted_at = NOW() WHERE poll_id = ${poll.id}::uuid AND user_id = ${userId}`;\n await sql`UPDATE post_poll_options SET votes_count = GREATEST(0, votes_count - 1) WHERE id = ${oldOptionId}::uuid`;\n await sql`UPDATE post_poll_options SET votes_count = votes_count + 1 WHERE id = ${optionId}::uuid`;\n } else {\n await sql`INSERT INTO post_poll_votes (poll_id, user_id, option_id) VALUES (${poll.id}::uuid, ${userId}, ${optionId}::uuid)`;\n await sql`UPDATE post_poll_options SET votes_count = votes_count + 1 WHERE id = ${optionId}::uuid`;\n await sql`UPDATE post_polls SET total_votes = total_votes + 1 WHERE id = ${poll.id}::uuid`;\n }\n const freshPoll =\n (await sql`SELECT * FROM post_polls WHERE id = ${poll.id}::uuid LIMIT 1`)[\n 0\n ] || poll;\n const freshOptions =\n await sql`SELECT id, label, votes_count, position FROM post_poll_options WHERE poll_id = ${poll.id}::uuid ORDER BY position ASC`;\n return c.json({\n success: true,\n poll: {\n ...freshPoll,\n options: freshOptions,\n my_vote: optionId,\n expired: false,\n },\n });\n } catch (err: any) {\n console.error(\"[vibe-posts] Poll vote error:\", err);\n return c.json({ error: \"Erreur vote sondage.\" }, 500);\n }\n };\n\n registerMulti(\"post\", [\n \"/api/vibe/posts/:id/poll/vote\",\n \"/vibe/posts/:id/poll/vote\",\n \"/v1/posts/:id/poll/vote\",\n \"/posts/:id/poll/vote\",\n ], handlePollVote);\n\n // 1c. POST STATS (auteur uniquement ; reach temporel via post_views)\n const handlePostStats = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const postId = c.req.param(\"id\");\n if (!isUuid(postId)) {\n return c.json({ error: \"Identifiant invalide.\" }, 400);\n }\n const periodParam = String(c.req.query(\"period\") || \"7d\").toLowerCase();\n const periodDays = periodParam === \"30d\"\n ? 30\n : periodParam === \"90d\"\n ? 90\n : 7;\n const sinceIso = new Date(Date.now() - periodDays * 86400000)\n .toISOString();\n const sql = getDb();\n const rows =\n await sql`SELECT author_id, views_count, likes_count, reposts_count, replies_count, bookmarks_count FROM posts WHERE id = ${postId}::uuid LIMIT 1`;\n if (rows.length === 0) {\n return c.json({ error: \"Publication introuvable.\" }, 404);\n }\n if (Number(rows[0].author_id) !== userId) {\n return c.json({ error: \"Réservé à l'auteur.\" }, 403);\n }\n const p = rows[0];\n const views = Number(p.views_count || 0);\n const likes = Number(p.likes_count || 0);\n const reposts = Number(p.reposts_count || 0);\n const replies = Number(p.replies_count || 0);\n const bookmarks = Number(p.bookmarks_count || 0);\n const engagement_rate = views > 0\n ? Math.round(((likes + reposts + replies + bookmarks) / views) * 1000) /\n 10\n : 0;\n // Série temporelle des vues (best-effort)\n let reach: Array<{ day: string; views: number }> = [];\n try {\n await ensurePostColumns().catch(() => {});\n const viewRows = await sql`\n SELECT created_at::date AS day, COUNT(*) AS views\n FROM post_views\n WHERE post_id = ${postId}::uuid AND created_at >= ${sinceIso}::timestamptz\n GROUP BY created_at::date\n ORDER BY day ASC\n `;\n const byDay = new Map();\n for (const r of viewRows as any[]) {\n const d = String((r as any).day).slice(0, 10);\n byDay.set(d, Number((r as any).views || 0));\n }\n for (let i = periodDays - 1; i >= 0; i--) {\n const d = new Date(Date.now() - i * 86400000).toISOString().slice(\n 0,\n 10,\n );\n reach.push({ day: d, views: byDay.get(d) || 0 });\n }\n } catch {}\n return c.json({\n views,\n likes,\n reposts,\n replies,\n bookmarks,\n engagement_rate,\n period: periodParam,\n reach_7d: reach,\n reach,\n top_referrers: [],\n });\n } catch (err: any) {\n return c.json({ error: \"Erreur stats post.\" }, 500);\n }\n };\n\n registerMulti(\"get\", [\n \"/api/vibe/posts/:id/stats\",\n \"/vibe/posts/:id/stats\",\n \"/v1/posts/:id/stats\",\n \"/posts/:id/stats\",\n ], handlePostStats);\n\n // 1d. CREATOR STATS (période ?period=7d|30d|90d|12m, défaut 30d)\n // Champs historiques conservés (total_*, top_post, daily) + nouveaux :\n // series[{day,views,likes,reposts,replies,profile_views}], sources, profile_views.\n const handleCreatorStats = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const periodParam = String(c.req.query(\"period\") || \"30d\").toLowerCase();\n const periodDays = periodParam === \"7d\"\n ? 7\n : periodParam === \"90d\"\n ? 90\n : periodParam === \"12m\"\n ? 365\n : 30;\n const sinceIso = new Date(Date.now() - periodDays * 86400000)\n .toISOString();\n const sql = getDb();\n await publishDuePosts().catch(() => {});\n await ensurePostColumns().catch(() => {});\n const agg = await sql`\n SELECT COALESCE(SUM(views_count), 0) AS total_views,\n COALESCE(SUM(likes_count), 0) AS total_likes,\n COALESCE(SUM(reposts_count), 0) AS total_reposts,\n COALESCE(SUM(replies_count), 0) AS total_replies,\n COUNT(*) AS posts_count\n FROM posts\n WHERE author_id = ${userId} AND published_at >= ${sinceIso}::timestamptz\n `;\n const top = await sql`\n SELECT id, content, views_count, likes_count, reposts_count, replies_count, published_at,\n (COALESCE(likes_count,0) + COALESCE(reposts_count,0) * 2 + COALESCE(replies_count,0) * 2) AS engagement\n FROM posts\n WHERE author_id = ${userId} AND published_at >= ${sinceIso}::timestamptz\n ORDER BY engagement DESC, published_at DESC\n LIMIT 1\n `;\n const daily = await sql`\n SELECT published_at::date AS day, COALESCE(SUM(views_count), 0) AS views, COUNT(*) AS posts\n FROM posts\n WHERE author_id = ${userId} AND published_at >= ${sinceIso}::timestamptz\n GROUP BY published_at::date\n ORDER BY day ASC\n `;\n const a = agg[0] || {};\n // Séries temporelles par date d'ÉVÉNEMENT (best-effort, tables absentes => zéros)\n const days: string[] = [];\n for (let i = periodDays - 1; i >= 0; i--) {\n days.push(\n new Date(Date.now() - i * 86400000).toISOString().slice(0, 10),\n );\n }\n const bucket = (rows: any[], key = \"day\", val = \"n\") => {\n const m = new Map();\n for (const r of rows as any[]) {\n m.set(String(r[key]).slice(0, 10), Number(r[val] || 0));\n }\n return m;\n };\n let viewsByDay = new Map();\n let likesByDay = new Map();\n let repostsByDay = new Map();\n let repliesByDay = new Map();\n let profileByDay = new Map();\n let sources: Array<{ source: string; views: number }> = [];\n let profileViewsTotal = 0;\n try {\n const [v, l, rp] = await Promise.all([\n sql`SELECT pv.created_at::date AS day, COUNT(*) AS n FROM post_views pv JOIN posts p ON p.id = pv.post_id WHERE p.author_id = ${userId} AND pv.created_at >= ${sinceIso}::timestamptz GROUP BY pv.created_at::date`,\n sql`SELECT pi.created_at::date AS day, COUNT(*) AS n FROM post_interactions pi JOIN posts p ON p.id = pi.post_id WHERE p.author_id = ${userId} AND pi.interaction_type = 'like' AND pi.created_at >= ${sinceIso}::timestamptz GROUP BY pi.created_at::date`,\n sql`SELECT pi.created_at::date AS day, COUNT(*) AS n FROM post_interactions pi JOIN posts p ON p.id = pi.post_id WHERE p.author_id = ${userId} AND pi.interaction_type = 'repost' AND pi.created_at >= ${sinceIso}::timestamptz GROUP BY pi.created_at::date`,\n ]);\n viewsByDay = bucket(v as any[]);\n likesByDay = bucket(l as any[]);\n repostsByDay = bucket(rp as any[]);\n } catch {}\n try {\n const cm =\n await sql`SELECT c.created_at::date AS day, COUNT(*) AS n FROM comments c JOIN posts p ON p.id = c.post_id WHERE p.author_id = ${userId} AND c.created_at >= ${sinceIso}::timestamptz GROUP BY c.created_at::date`;\n repliesByDay = bucket(cm as any[]);\n } catch {}\n try {\n const pv =\n await sql`SELECT created_at::date AS day, COUNT(*) AS n FROM profile_views WHERE profile_user_id = ${userId} AND created_at >= ${sinceIso}::timestamptz GROUP BY created_at::date`;\n profileByDay = bucket(pv as any[]);\n for (const n of profileByDay.values()) profileViewsTotal += n;\n } catch {}\n try {\n const s =\n await sql`SELECT COALESCE(pv.source, 'feed') AS source, COUNT(*) AS views FROM post_views pv JOIN posts p ON p.id = pv.post_id WHERE p.author_id = ${userId} AND pv.created_at >= ${sinceIso}::timestamptz GROUP BY COALESCE(pv.source, 'feed') ORDER BY views DESC`;\n sources = (s as any[]).map((r: any) => ({\n source: String(r.source),\n views: Number(r.views || 0),\n }));\n } catch {}\n // ── Nouvelles analyses (migration 019) ──\n // 1. Comparaison avec la période précédente (croissance)\n let previous_period: any = null;\n try {\n const prevSince = new Date(Date.now() - 2 * periodDays * 86400000)\n .toISOString();\n const prevUntil = new Date(Date.now() - periodDays * 86400000)\n .toISOString();\n const prev = await 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\n WHERE author_id = ${userId} AND published_at >= ${prevSince}::timestamptz AND published_at < ${prevUntil}::timestamptz\n `;\n previous_period = {\n total_views: Number(prev[0]?.views || 0),\n total_likes: Number(prev[0]?.likes || 0),\n total_reposts: Number(prev[0]?.reposts || 0),\n total_replies: Number(prev[0]?.replies || 0),\n posts_count: Number(prev[0]?.posts || 0),\n };\n } catch {}\n // 2. Meilleur jour (vues) + meilleur jour de publication\n let best_day: any = null;\n let best_publish_day: any = null;\n try {\n const bd = await sql`\n SELECT created_at::date AS day, COUNT(*) AS views\n FROM post_views pv JOIN posts p ON p.id = pv.post_id\n WHERE p.author_id = ${userId} AND pv.created_at >= ${sinceIso}::timestamptz\n GROUP BY 1 ORDER BY views DESC LIMIT 1\n `;\n if (bd[0]) {\n best_day = {\n day: String(bd[0].day).slice(0, 10),\n views: Number(bd[0].views || 0),\n };\n }\n } catch {}\n try {\n const bpd = await sql`\n SELECT published_at::date AS day, COALESCE(SUM(views_count), 0) AS views, COUNT(*) AS posts\n FROM posts WHERE author_id = ${userId} AND published_at >= ${sinceIso}::timestamptz\n GROUP BY 1 ORDER BY views DESC LIMIT 1\n `;\n if (bpd[0]) {\n best_publish_day = {\n day: String(bpd[0].day).slice(0, 10),\n views: Number(bpd[0].views || 0),\n posts: Number(bpd[0].posts || 0),\n };\n }\n } catch {}\n // 3. Répartition horaire des vues (0-23) pour trouver les heures optimales\n let hourly: Array<{ hour: number; views: number }> = [];\n try {\n const h = await sql`\n SELECT EXTRACT(HOUR FROM pv.created_at)::int AS hour, COUNT(*) AS views\n FROM post_views pv JOIN posts p ON p.id = pv.post_id\n WHERE p.author_id = ${userId} AND pv.created_at >= ${sinceIso}::timestamptz\n GROUP BY 1 ORDER BY 1\n `;\n const byHour = new Map(\n (h as any[]).map((r: any) => [Number(r.hour), Number(r.views || 0)]),\n );\n hourly = Array.from(\n { length: 24 },\n (_, i) => ({ hour: i, views: byHour.get(i) || 0 }),\n );\n } catch {}\n // 4. Posts les plus engageants de la période (top 5)\n let top_posts: any[] = [];\n try {\n const tp = await sql`\n SELECT id, content, views_count, likes_count, reposts_count, replies_count, published_at,\n (COALESCE(likes_count,0) + COALESCE(reposts_count,0)*2 + COALESCE(replies_count,0)*2) AS engagement\n FROM posts WHERE author_id = ${userId} AND published_at >= ${sinceIso}::timestamptz\n ORDER BY engagement DESC, published_at DESC LIMIT 5\n `;\n top_posts = (tp as any[]).map((p: any) => ({\n ...p,\n engagement: Number(p.engagement || 0),\n }));\n } catch {}\n // 5. Taux d'engagement moyen par post et fréquence de publication\n const views_total = Number(a.total_views || 0);\n const engagementRate = views_total > 0\n ? Math.round(\n ((Number(a.total_likes || 0) + Number(a.total_reposts || 0) * 2 +\n Number(a.total_replies || 0) * 2) / views_total) * 1000,\n ) / 10\n : 0;\n const postsPerWeek = periodDays >= 7\n ? Math.round((Number(a.posts_count || 0) / (periodDays / 7)) * 10) / 10\n : Number(a.posts_count || 0);\n const series = days.map((day) => ({\n day,\n views: viewsByDay.get(day) || 0,\n likes: likesByDay.get(day) || 0,\n reposts: repostsByDay.get(day) || 0,\n replies: repliesByDay.get(day) || 0,\n profile_views: profileByDay.get(day) || 0,\n }));\n return c.json({\n total_views: views_total,\n total_likes: Number(a.total_likes || 0),\n total_reposts: Number(a.total_reposts || 0),\n total_replies: Number(a.total_replies || 0),\n posts_count: Number(a.posts_count || 0),\n top_post: top[0] || null,\n daily,\n period: periodParam,\n series,\n sources,\n profile_views: profileViewsTotal,\n // Nouvelles analyses (migration 019)\n previous_period,\n best_day,\n best_publish_day,\n hourly,\n top_posts,\n engagement_rate: engagementRate,\n posts_per_week: postsPerWeek,\n });\n } catch (err: any) {\n return c.json({ error: \"Erreur stats créateur.\" }, 500);\n }\n };\n\n registerMulti(\"get\", [\n \"/api/vibe/users/me/creator-stats\",\n \"/vibe/users/me/creator-stats\",\n \"/v1/users/me/creator-stats\",\n \"/users/me/creator-stats\",\n ], handleCreatorStats);\n\n // 1e. COLLABORATION accept / decline (co-auteur invité uniquement)\n const handleCollabRespond = async (c: any, accept: boolean) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const postId = c.req.param(\"id\");\n if (!isUuid(postId)) {\n return c.json({ error: \"Identifiant invalide.\" }, 400);\n }\n const sql = getDb();\n await ensurePostColumns();\n const rows =\n await sql`SELECT status FROM post_collaborators WHERE post_id = ${postId}::uuid AND user_id = ${userId} LIMIT 1`;\n if (rows.length === 0) {\n return c.json({ error: \"Aucune invitation.\" }, 404);\n }\n await sql`\n UPDATE post_collaborators\n SET status = ${accept ? \"accepted\" : \"declined\"}, accepted_at = ${\n accept ? new Date().toISOString() : null\n }::timestamptz\n WHERE post_id = ${postId}::uuid AND user_id = ${userId}\n `;\n try {\n const postRows =\n await sql`SELECT author_id FROM posts WHERE id = ${postId}::uuid LIMIT 1`;\n const authorId = Number(postRows[0]?.author_id);\n if (authorId) {\n await sql`\n INSERT INTO notifications (recipient_id, actor_id, type, post_id, message)\n VALUES (${authorId}, ${userId}, 'mention', ${postId}::uuid, ${\n accept\n ? \"a accepté votre invitation de co-signature\"\n : \"a décliné votre invitation de co-signature\"\n })\n `.catch(() => {});\n await pushRealtimeEvent(authorId, \"notification\", {\n type: accept ? \"collab_accepted\" : \"collab_declined\",\n post_id: postId,\n actor_id: userId,\n }).catch(() => {});\n }\n } catch {}\n return c.json({\n success: true,\n status: accept ? \"accepted\" : \"declined\",\n });\n } catch (err: any) {\n return c.json({ error: \"Erreur réponse collaboration.\" }, 500);\n }\n };\n\n registerMulti(\"post\", [\n \"/api/vibe/posts/:id/collaborate/accept\",\n \"/vibe/posts/:id/collaborate/accept\",\n \"/v1/posts/:id/collaborate/accept\",\n \"/posts/:id/collaborate/accept\",\n ], (c: any) => handleCollabRespond(c, true));\n registerMulti(\"post\", [\n \"/api/vibe/posts/:id/collaborate/decline\",\n \"/vibe/posts/:id/collaborate/decline\",\n \"/v1/posts/:id/collaborate/decline\",\n \"/posts/:id/collaborate/decline\",\n ], (c: any) => handleCollabRespond(c, false));\n\n // 1f. SCHEDULED LIST + RESCHEDULE (gate Plus/Pro/Max, null = publier aussitôt)\n const handleScheduledList = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const sql = getDb();\n await ensurePostColumns();\n const posts = 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.author_id = ${userId} AND p.status = 'scheduled'\n ORDER BY p.scheduled_at ASC\n `;\n await fetchPostMedia(posts);\n await attachPollsAndCollabs(posts, userId);\n return c.json({ posts });\n } catch (err: any) {\n return c.json({ error: \"Erreur posts programmés.\" }, 500);\n }\n };\n\n registerMulti(\"get\", [\n \"/api/vibe/posts/scheduled\",\n \"/vibe/posts/scheduled\",\n \"/v1/posts/scheduled\",\n \"/posts/scheduled\",\n ], handleScheduledList);\n\n const handleReschedule = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const postId = c.req.param(\"id\");\n if (!isUuid(postId)) {\n return c.json({ error: \"Identifiant invalide.\" }, 400);\n }\n const body = await c.req.json().catch(() => ({}));\n const sql = getDb();\n const existing =\n await sql`SELECT id, author_id FROM posts WHERE id = ${postId}::uuid LIMIT 1`;\n if (existing.length === 0) {\n return c.json({ error: \"Publication introuvable.\" }, 404);\n }\n if (Number(existing[0].author_id) !== userId) {\n return c.json({ error: \"Réservé à l'auteur.\" }, 403);\n }\n const tierRows =\n await sql`SELECT tier FROM users WHERE id = ${userId} LIMIT 1`;\n if (!isPaidTier(tierRows[0]?.tier)) {\n return c.json({\n error:\n \"La planification des vibes est réservée aux abonnés Plus, Pro et Max.\",\n plan_required: true,\n }, 403);\n }\n const raw = body?.scheduled_at;\n if (raw === null || raw === \"\") {\n await sql`UPDATE posts SET status = 'published', scheduled_at = NULL, published_at = NOW(), updated_at = NOW() WHERE id = ${postId}::uuid`;\n return c.json({ success: true, status: \"published\" });\n }\n const ts = Date.parse(String(raw || \"\"));\n if (Number.isNaN(ts) || ts <= Date.now()) {\n return c.json(\n { error: \"Date de planification invalide ou passée.\" },\n 400,\n );\n }\n const scheduledAt = new Date(ts).toISOString();\n await sql`UPDATE posts SET status = 'scheduled', scheduled_at = ${scheduledAt}::timestamptz, updated_at = NOW() WHERE id = ${postId}::uuid`;\n return c.json({\n success: true,\n status: \"scheduled\",\n scheduled_at: scheduledAt,\n });\n } catch (err: any) {\n return c.json({ error: \"Erreur replanification.\" }, 500);\n }\n };\n\n registerMulti(\"patch\", [\n \"/api/vibe/posts/:id/reschedule\",\n \"/vibe/posts/:id/reschedule\",\n \"/v1/posts/:id/reschedule\",\n \"/posts/:id/reschedule\",\n ], handleReschedule);\n\n // 1g. DRAFTS CRUD (multi-brouillons, tri updated_at DESC)\n const handleDraftsList = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const sql = getDb();\n await ensurePostColumns();\n const drafts =\n await sql`SELECT * FROM post_drafts WHERE user_id = ${userId} ORDER BY updated_at DESC LIMIT 20`;\n return c.json({ drafts });\n } catch (err: any) {\n return c.json({ error: \"Erreur brouillons.\" }, 500);\n }\n };\n\n registerMulti(\"get\", [\n \"/api/vibe/drafts\",\n \"/vibe/drafts\",\n \"/v1/drafts\",\n \"/drafts\",\n ], handleDraftsList);\n\n const handleDraftUpsert = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const body = await c.req.json().catch(() => ({}));\n const draftId = c.req.param(\"id\") || (body as any)?.id;\n const html = String(body?.html || \"\");\n const text = String(body?.text || \"\");\n if (!html && !text) return c.json({ error: \"Brouillon vide.\" }, 400);\n const visibility =\n [\"public\", \"followers\", \"circle\", \"private\"].includes(body?.visibility)\n ? body.visibility\n : \"public\";\n const scheduledAt = body?.scheduled_at\n ? new Date(Date.parse(String(body.scheduled_at))).toISOString()\n : null;\n const aiGenerated = Boolean(body?.ai_generated);\n const mediaAssets = Array.isArray(body?.media_assets)\n ? body.media_assets.slice(0, 10)\n : [];\n const sql = getDb();\n await ensurePostColumns();\n if (draftId && isUuid(String(draftId))) {\n const owned = await sql`SELECT id FROM post_drafts WHERE id = ${\n String(draftId)\n }::uuid AND user_id = ${userId} LIMIT 1`;\n if (owned.length === 0) {\n return c.json({ error: \"Brouillon introuvable.\" }, 404);\n }\n await sql`\n UPDATE post_drafts\n SET html = ${html}, text = ${text}, visibility = ${visibility}, scheduled_at = ${scheduledAt}::timestamptz,\n ai_generated = ${aiGenerated}, media_assets = ${\n JSON.stringify(mediaAssets)\n }::jsonb, updated_at = NOW()\n WHERE id = ${String(draftId)}::uuid\n `;\n return c.json({ success: true, id: String(draftId) });\n }\n const inserted = await sql`\n INSERT INTO post_drafts (user_id, html, text, visibility, scheduled_at, ai_generated, media_assets)\n VALUES (${userId}, ${html}, ${text}, ${visibility}, ${scheduledAt}::timestamptz, ${aiGenerated}, ${\n JSON.stringify(mediaAssets)\n }::jsonb)\n RETURNING id\n `;\n return c.json({ success: true, id: String(inserted[0]?.id) }, 201);\n } catch (err: any) {\n return c.json({ error: \"Erreur sauvegarde brouillon.\" }, 500);\n }\n };\n\n registerMulti(\"post\", [\n \"/api/vibe/drafts\",\n \"/vibe/drafts\",\n \"/v1/drafts\",\n \"/drafts\",\n ], handleDraftUpsert);\n registerMulti(\"put\", [\n \"/api/vibe/drafts/:id\",\n \"/vibe/drafts/:id\",\n \"/v1/drafts/:id\",\n \"/drafts/:id\",\n ], handleDraftUpsert);\n\n const handleDraftDelete = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n const draftId = c.req.param(\"id\");\n if (!isUuid(String(draftId))) {\n return c.json({ error: \"Identifiant invalide.\" }, 400);\n }\n const sql = getDb();\n await sql`DELETE FROM post_drafts WHERE id = ${\n String(draftId)\n }::uuid AND user_id = ${userId}`;\n return c.json({ success: true });\n } catch (err: any) {\n return c.json({ error: \"Erreur suppression brouillon.\" }, 500);\n }\n };\n\n registerMulti(\"delete\", [\n \"/api/vibe/drafts/:id\",\n \"/vibe/drafts/:id\",\n \"/v1/drafts/:id\",\n \"/drafts/:id\",\n ], handleDraftDelete);\n\n const handleGetPost = async (c: any) => {\n try {\n const postId = c.req.param(\"id\");\n const token = extractToken(c.req.raw);\n let currentUserId: number | null = null;\n if (token) {\n try {\n const payload = await verifyToken(token);\n currentUserId = Number(payload.sub || (payload as any).id);\n } catch {}\n }\n\n const sql = getDb();\n await ensureCircleTable().catch(() => {});\n const rows = await sql`\n SELECT p.*, pr.display_name, pr.avatar_url, u.username,\n ${\n currentUserId\n ? sql`(SELECT COUNT(*) FROM post_interactions WHERE post_id = p.id AND user_id = ${currentUserId} AND interaction_type = 'like') > 0`\n : sql`FALSE`\n } as has_liked,\n ${\n currentUserId\n ? sql`(SELECT COUNT(*) FROM post_interactions WHERE post_id = p.id AND user_id = ${currentUserId} AND interaction_type = 'repost') > 0`\n : sql`FALSE`\n } as has_reposted,\n ${\n currentUserId\n ? sql`(SELECT COUNT(*) FROM bookmarks WHERE post_id = p.id AND user_id = ${currentUserId}) > 0`\n : sql`FALSE`\n } as has_bookmarked,\n ${\n currentUserId\n ? sql`(SELECT pi.interaction_type FROM post_interactions pi WHERE pi.post_id = p.id AND pi.user_id = ${currentUserId} AND pi.interaction_type IN ('interest_more', 'interest_less') LIMIT 1)`\n : sql`NULL`\n } as my_feedback\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 = ${postId}::uuid ${visibilityFilter(currentUserId)}\n LIMIT 1\n `;\n\n if (rows.length === 0) {\n return c.json({ error: \"Publication introuvable.\" }, 404);\n }\n\n // Une vibe planifiée n'est visible que par son auteur\n if (\n (rows[0] as any).status === \"scheduled\" &&\n Number((rows[0] as any).author_id) !== currentUserId\n ) {\n return c.json({ error: \"Publication introuvable.\" }, 404);\n }\n\n const media =\n await sql`SELECT * FROM media_assets WHERE post_id = ${postId}::uuid ORDER BY position ASC, created_at ASC`\n .catch(async () =>\n await sql`SELECT * FROM media_assets WHERE post_id = ${postId}::uuid`\n );\n const postResult = { ...rows[0], media_assets: media };\n await attachQuotedPosts([postResult]);\n await attachPollsAndCollabs([postResult], currentUserId);\n await attachBookRefs([postResult], currentUserId).catch(() => {});\n return c.json({ post: postResult });\n } catch {\n return c.json({ error: \"Erreur lors de la récupération.\" }, 500);\n }\n };\n\n registerMulti(\"get\", [\n \"/api/vibe/posts/:id\",\n \"/vibe/posts/:id\",\n \"/v1/posts/:id\",\n \"/posts/:id\",\n ], handleGetPost);\n\n const handleDeletePost = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n\n const postId = c.req.param(\"id\");\n const sql = getDb();\n\n const deleted = await sql`\n DELETE FROM posts WHERE id = ${postId}::uuid AND author_id = ${userId} RETURNING id\n `;\n\n if (deleted.length === 0) {\n return c.json(\n { error: \"Publication introuvable ou non autorisée.\" },\n 403,\n );\n }\n\n await sql`UPDATE profiles SET posts_count = GREATEST(0, posts_count - 1) WHERE user_id = ${userId}`;\n return c.json({ success: true, message: \"Publication supprimée.\" });\n } catch {\n return c.json({ error: \"Erreur suppression.\" }, 500);\n }\n };\n\n registerMulti(\"delete\", [\n \"/api/vibe/posts/:id\",\n \"/vibe/posts/:id\",\n \"/v1/posts/:id\",\n \"/posts/:id\",\n ], handleDeletePost);\n\n // 1bis. ÉDITION D'UNE PUBLICATION (auteur uniquement)\n const handleUpdatePost = async (c: any) => {\n try {\n const token = extractToken(c.req.raw);\n if (!token) return c.json({ error: \"Non authentifié.\" }, 401);\n const payload = await verifyToken(token);\n const userId = Number(payload.sub || (payload as any).id);\n\n const postId = c.req.param(\"id\");\n if (!isUuid(postId)) {\n return c.json({ error: \"Identifiant de post invalide.\" }, 400);\n }\n\n const body = await c.req.json();\n const { content, media_assets = [] } = body;\n\n const sql = getDb();\n await ensurePostColumns();\n\n const existing =\n await sql`SELECT id, author_id, status, scheduled_at FROM posts WHERE id = ${postId}::uuid LIMIT 1`;\n if (existing.length === 0) {\n return c.json({ error: \"Publication introuvable.\" }, 404);\n }\n if (Number(existing[0].author_id) !== userId) {\n return c.json({\n error: \"Seul l'auteur peut modifier cette publication.\",\n }, 403);\n }\n\n if (!content || !String(content).trim()) {\n return c.json({ error: \"Le contenu est obligatoire.\" }, 400);\n }\n\n // Limite de longueur par forfait (texte brut, balises de mise en forme exclues)\n const tierRows =\n await sql`SELECT tier FROM users WHERE id = ${userId} LIMIT 1`;\n const isPaid = isPaidTier(tierRows[0]?.tier);\n const charLimit = isPaid ? POST_CHARS_LIMIT_PAID : POST_CHARS_LIMIT_FREE;\n if (stripHtmlTags(String(content)).length > charLimit) {\n return c.json(postCharsError(isPaid, charLimit), 400);\n }\n\n const safety = MAIAgentFleet.assessContentSafety(stripHtmlTags(content));\n if (!safety.isSafe) {\n return c.json(\n { error: `Publication refusée : ${safety.flagReason}` },\n 403,\n );\n }\n\n // Re-planification éventuelle (réservée Plus/Pro/Max)\n let scheduledAt: string | null = null;\n if (body.scheduled_at !== undefined) {\n if (body.scheduled_at === null || body.scheduled_at === \"\") {\n scheduledAt = null;\n } else {\n const ts = Date.parse(String(body.scheduled_at));\n if (Number.isNaN(ts) || ts <= Date.now()) {\n return c.json({\n error: \"Date de planification invalide ou passée.\",\n }, 400);\n }\n if (!isPaid) {\n return c.json(\n {\n error:\n \"La planification des vibes est réservée aux abonnés Plus, Pro et Max.\",\n plan_required: true,\n },\n 403,\n );\n }\n scheduledAt = new Date(ts).toISOString();\n }\n }\n\n // Audience mise à jour si fournie (Public / Abonnés / Cercle Privé / privé)\n const visibilityUpdate =\n [\"public\", \"followers\", \"circle\", \"private\"].includes(body.visibility)\n ? body.visibility\n : null;\n\n const updated = await sql`\n UPDATE posts\n SET content = ${String(content).trim()},\n toxicity_score = ${safety.toxicityScore},\n visibility = COALESCE(${visibilityUpdate}, visibility),\n updated_at = NOW(),\n published_at = CASE WHEN ${scheduledAt}::timestamptz IS NULL AND status = 'scheduled' THEN NOW() ELSE published_at END,\n status = CASE\n WHEN ${scheduledAt}::timestamptz IS NOT NULL THEN 'scheduled'\n ELSE 'published'\n END,\n scheduled_at = ${scheduledAt}::timestamptz\n WHERE id = ${postId}::uuid AND author_id = ${userId}\n RETURNING *\n `;\n if (updated.length === 0) {\n return c.json({ error: \"Publication introuvable.\" }, 404);\n }\n\n // Références de Livres : resynchronisées avec le nouveau contenu\n const updatedBookRefIds = extractBookRefIds(String(content));\n if (updatedBookRefIds.length > 0) {\n await ensureBooksTables(sql).catch(() => {});\n }\n await syncPostBookRefs(sql, postId, updatedBookRefIds);\n\n // Remplacement des médias (légendes incluses) si la liste est fournie\n let insertedMediaList: any[] = [];\n if (Array.isArray(media_assets)) {\n await sql`DELETE FROM media_assets WHERE post_id = ${postId}::uuid AND comment_id IS NULL`;\n const inferMediaType = (\n url: string,\n fallback = \"image/jpeg\",\n ): string => {\n try {\n const ext = url.split(\"?\")[0].split(\".\").pop()?.toLowerCase();\n switch (ext) {\n case \"png\":\n return \"image/png\";\n case \"webp\":\n return \"image/webp\";\n case \"gif\":\n return \"image/gif\";\n case \"jpg\":\n case \"jpeg\":\n return \"image/jpeg\";\n case \"mp4\":\n return \"video/mp4\";\n case \"webm\":\n return \"video/webm\";\n case \"mov\":\n return \"video/quicktime\";\n default:\n return fallback;\n }\n } catch {\n return fallback;\n }\n };\n const mediaPositions = Array.isArray(body.media_positions)\n ? body.media_positions\n : [];\n for (let mi = 0; mi < media_assets.length; mi++) {\n const media = media_assets[mi];\n if (!media || !media.url) continue;\n const urlStr = String(media.url).trim();\n if (!urlStr) continue;\n const position = Number.isFinite(Number(mediaPositions[mi]))\n ? Number(mediaPositions[mi])\n : mi;\n let res: any[] = [];\n try {\n res = await sql`\n INSERT INTO media_assets (owner_id, post_id, url, media_type, file_size_bytes, alt_text, position)\n VALUES (${userId}, ${postId}::uuid, ${urlStr}, ${\n media.media_type || media.type || inferMediaType(urlStr)\n },\n ${\n Math.max(\n 0,\n Math.round(\n Number(media.file_size_bytes ?? media.size ?? 0) || 0,\n ),\n )\n },\n ${media.alt_text || media.alt || \"\"}, ${position})\n RETURNING id, url, media_type, file_size_bytes, alt_text, position\n `;\n } catch {\n // Colonne position absente (base ancienne) : insertion sans ordre\n res = await sql`\n INSERT INTO media_assets (owner_id, post_id, url, media_type, file_size_bytes, alt_text)\n VALUES (${userId}, ${postId}::uuid, ${urlStr}, ${\n media.media_type || media.type || inferMediaType(urlStr)\n },\n ${\n Math.max(\n 0,\n Math.round(\n Number(media.file_size_bytes ?? media.size ?? 0) || 0,\n ),\n )\n },\n ${media.alt_text || media.alt || \"\"})\n RETURNING id, url, media_type, file_size_bytes, alt_text\n `;\n }\n if (res && res[0]) insertedMediaList.push(res[0]);\n }\n }\n\n const userRow =\n await sql`SELECT username FROM users WHERE id = ${userId} LIMIT 1`;\n const profileRow =\n await sql`SELECT display_name, avatar_url FROM profiles WHERE user_id = ${userId} LIMIT 1`;\n const updatedPost: any = {\n ...updated[0],\n username: userRow[0]?.username,\n display_name: profileRow[0]?.display_name || userRow[0]?.username,\n avatar_url: profileRow[0]?.avatar_url,\n media_assets: insertedMediaList,\n quoted_post: null,\n };\n await attachQuotedPosts([updatedPost]);\n await attachBookRefs([updatedPost], userId).catch(() => {});\n\n return c.json({ success: true, post: updatedPost });\n } catch (err: any) {\n console.error(\"[Vibe API] Error updating post:\", err);\n return c.json({\n error: \"Erreur lors de la modification de la publication.\",\n }, 500);\n }\n };\n\n registerMulti(\"patch\", [\n \"/api/vibe/posts/:id\",\n \"/vibe/posts/:id\",\n \"/v1/posts/:id\",\n \"/posts/:id\",\n ], handleUpdatePost);\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=1789541999&sig=7f12ae40e2d4d645116f81aed8161642cd2da1506f0b80a779ec57c88213b6a1","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=1789541999&sig=b8a4bd681ba35663ee760ea4ee42cf81eb333e427132a2271b6ff477c9901c45","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"]