xiaoshu-app/scripts/cloud/support-requests-app.js

// Ownership always comes from the authenticated account, never a selected student/input uid.
async function appSupportRequests(input,current) {
  const company=current.get('company')?.id || current.get('company')?.objectId;
  const uid=ownLegacyId(current);
  if(!company || !uid)fail(403,'账号缺少反馈记录归属');
  if(input.action==='app_support_request_read') {
    const rows=await Psql.query(`UPDATE "SupportRequestResolution" r SET "readAt"=COALESCE(r."readAt",now())
      FROM "Guestbook" g WHERE r.company=$1 AND r."requestId"=$2 AND r."ownerId"=$3
      AND g.company=r.company AND g."objectId"=r."requestId" AND g.userid=$3 AND COALESCE(g.status,0)<>-2 RETURNING r."requestId"`,[company,String(input.id||''),uid]);
    if(!rows.length)fail(404,'回复不存在或不属于当前账号');
    return {read:true};
  }
  const page=Math.max(1,Math.floor(number(input.page,1))),size=Math.min(50,Math.max(1,Math.floor(number(input.pageSize,10))));
  return await Psql.one(`WITH owned AS (
    SELECT g."objectId" AS id,g.title,COALESCE(g.tcontent,'') AS content,COALESCE(g.gdate,g."createdAt") AS "submittedAt",
      CASE WHEN g.title LIKE '【休假申请】%' THEN 'leave' WHEN g.title LIKE '【意见反馈】%' THEN 'feedback' ELSE 'legacy' END AS kind,
      COALESCE(r.status,'pending') AS status,COALESCE(r.comment,'') AS response,r."handledAt",r."readAt",
      (r."requestId" IS NOT NULL AND r."readAt" IS NULL) AS unread
    FROM "Guestbook" g LEFT JOIN "SupportRequestResolution" r ON r.company=g.company AND r."requestId"=g."objectId" AND r."ownerId"=$2
    WHERE g.company=$1 AND g.userid=$2 AND COALESCE(g.parentid,0)=0 AND COALESCE(g.status,0)<>-2
      AND (g.cateid=22 OR g."sourceKey" LIKE 'cloud:guestbook_add:%')
  ), paged AS (SELECT * FROM owned ORDER BY unread DESC,"submittedAt" DESC NULLS LAST,id DESC LIMIT $3 OFFSET $4)
  SELECT COUNT(*)::int AS total,COUNT(*) FILTER(WHERE unread)::int AS unread,
    COALESCE((SELECT jsonb_agg(p ORDER BY p.unread DESC,p."submittedAt" DESC NULLS LAST,p.id DESC) FROM paged p),'[]'::jsonb) AS items
  FROM owned`,[company,uid,size,(page-1)*size]);
}