marketing-app/server/post-composition.js

const nocodb = require('./nocodb');

async function getPostComposition(postId) {
  const post = await nocodb.get('Posts', postId);
  if (!post) return null;

  const artefacts = await nocodb.list('Artefacts', {
    where: `(post_id,eq,${postId})`, limit: 100,
  });

  const caption = artefacts.find(a => a.type === 'copy' && a.copy_type === 'caption') || null;
  const bodyCopy = artefacts.find(a => a.type === 'copy' && (a.copy_type === 'body' || !a.copy_type)) || null;
  const design = artefacts.find(a => (a.type || 'design') === 'design') || null;
  const video = artefacts.find(a => a.type === 'video') || null;

  let platforms = [];
  try { platforms = JSON.parse(post.platforms || '[]'); } catch { platforms = post.platform ? [post.platform] : []; }

  const waitingOn = [];
  if (caption && caption.status !== 'approved') waitingOn.push('Caption');
  if (bodyCopy && bodyCopy.status !== 'approved') waitingOn.push('Copy');
  if (design && design.status !== 'approved') waitingOn.push('Design');
  if (video && video.status !== 'approved') waitingOn.push('Video');

  const hasPieces = caption || bodyCopy || design || video;
  const piecesReady = hasPieces && waitingOn.length === 0;

  // Get texts from ArtefactVersionTexts for copy artefacts (content preview + languages)
  const getTexts = async (artefactId) => {
    try {
      const versions = await nocodb.list('ArtefactVersions', { where: `(artefact_id,eq,${artefactId})`, sort: '-version_number', limit: 1 });
      if (versions.length === 0) return { texts: [], contentPreview: '' };
      const texts = await nocodb.list('ArtefactVersionTexts', { where: `(version_id,eq,${versions[0].Id})`, limit: 20 });
      const languages = texts.map(tt => ({ language: tt.language_code || tt.language, status: tt.status || 'draft' }));
      const contentPreview = texts.length > 0 ? (texts[0].content || '').slice(0, 120) : '';
      return { texts: languages, contentPreview };
    } catch { return { texts: [], contentPreview: '' }; }
  };

  const [captionTexts, bodyTexts] = await Promise.all([
    caption ? getTexts(caption.Id) : { texts: [], contentPreview: '' },
    bodyCopy ? getTexts(bodyCopy.Id) : { texts: [], contentPreview: '' },
  ]);

  // Get first attachment for design/video thumbnail
  const getFirstAttachment = async (artefactId) => {
    try {
      const versions = await nocodb.list('ArtefactVersions', { where: `(artefact_id,eq,${artefactId})`, sort: '-version_number', limit: 1 });
      if (versions.length === 0) return null;
      const attachments = await nocodb.list('ArtefactAttachments', { where: `(version_id,eq,${versions[0].Id})`, limit: 1 });
      if (attachments.length === 0) return null;
      const att = attachments[0];
      return att.drive_url || (att.filename ? `/api/uploads/${att.filename}` : null);
    } catch { return null; }
  };
  const [designThumb, videoThumb] = await Promise.all([
    design ? (design.thumbnail_url || getFirstAttachment(design.Id)) : null,
    video ? (video.thumbnail_url || getFirstAttachment(video.Id)) : null,
  ]);

  // Resolve approver names for each piece
  const resolveApprover = async (record) => {
    if (!record || !record.approver_ids) return { approver_ids: null, approver_name: null };
    const ids = record.approver_ids.split(',').map(s => s.trim()).filter(Boolean);
    if (ids.length === 0) return { approver_ids: null, approver_name: null };
    try {
      const user = await nocodb.get('Users', Number(ids[0]));
      return { approver_ids: record.approver_ids, approver_name: user ? (user.display_name || user.name || user.email) : null };
    } catch { return { approver_ids: record.approver_ids, approver_name: null }; }
  };

  const [captionApprover, bodyApprover, designApprover, videoApprover] = await Promise.all([
    resolveApprover(caption),
    resolveApprover(bodyCopy),
    resolveApprover(design),
    resolveApprover(video),
  ]);

  return {
    caption: caption ? { id: caption.Id, title: caption.title, status: caption.status, content_preview: captionTexts.contentPreview, languages: captionTexts.texts, ...captionApprover } : null,
    body_copy: bodyCopy ? { id: bodyCopy.Id, title: bodyCopy.title, status: bodyCopy.status, content_preview: bodyTexts.contentPreview, languages: bodyTexts.texts, ...bodyApprover } : null,
    design: design ? { id: design.Id, title: design.title, status: design.status, thumbnail_url: designThumb, current_version: design.current_version, ...designApprover } : null,
    video: video ? { id: video.Id, title: video.title, status: video.status, thumbnail_url: videoThumb, current_version: video.current_version, ...videoApprover } : null,
    platforms,
    pieces_ready: piecesReady,
    waiting_on: waitingOn,
    stage: post.stage || 'copy',
  };
}

function computeStage(composition) {
  const { caption, body_copy, design, video, pieces_ready } = composition;
  if (pieces_ready) return 'post';
  if (design || video) return 'design';
  if (caption || body_copy) return 'translate';
  return 'copy';
}

// Sync the post's thumbnail_url from its linked design artefact
async function syncPostThumbnail(postId) {
  try {
    const artefacts = await nocodb.list('Artefacts', {
      where: `(post_id,eq,${postId})`, limit: 100,
    });
    const design = artefacts.find(a => (a.type || 'design') === 'design');
    let thumb = null;
    if (design) {
      thumb = design.thumbnail_url || null;
      if (!thumb) {
        const versions = await nocodb.list('ArtefactVersions', { where: `(artefact_id,eq,${design.Id})`, sort: '-version_number', limit: 1 });
        if (versions.length > 0) {
          const attachments = await nocodb.list('ArtefactAttachments', { where: `(version_id,eq,${versions[0].Id})`, limit: 1 });
          if (attachments.length > 0) {
            const att = attachments[0];
            thumb = att.drive_url || (att.filename ? `/api/uploads/${att.filename}` : null);
          }
        }
      }
    }
    await nocodb.update('Posts', Number(postId), { thumbnail_url: thumb || null });
  } catch (e) {
    console.error('syncPostThumbnail error:', e);
  }
}

module.exports = { getPostComposition, computeStage, syncPostThumbnail };