xiaoshu-app/scripts/tests/fixtures/mobile-review-performance-baseline.js

// Original appReviewPage captured before query plan improvements, 2026-11-10.
async function appReviewPage(input,current) {
  const uid=await authorizeRequestedUser(current,input.uid),coach=number(current.get('legacyGroupId'))===3,p=mobilePage(input),includeMetadata=input.includeMetadata!==false,today=appOverviewToday();
  const reviewTarget=coach&&number(input.studentId)?await appReviewTarget(current,input.studentId):coach?0:uid;
  const refreshCoachToday=coach&&!reviewTarget&&input.scope==='today'&&p.index===1&&input.refresh===true&&uid===ownLegacyId(current);
  if(refreshCoachToday)assertLessonTenant(current);
  const legacyReviewSync=reviewTarget?await syncLegacyReviews(reviewTarget,current,input.refresh===true):
    refreshCoachToday?await syncLegacyCoachTodayReviews(uid,current,today):undefined;
  const args=[DEFAULT_COMPANY_ID,String(uid)],clauses=['m."company"=$1','m."'+(coach?'plid':'yhid')+'"::text=$2'];
  // Empty writing records are deliberate notes reviews; other empty sources remain hidden.
  const notesSource=notesReviewSourceSql();
  clauses.push('NOT EXISTS (SELECT 1 FROM "CommonModel" sc JOIN "DailyStudyRecord" d ON d."company"=sc."company" AND d."id"=sc."itemId" WHERE sc."company"=m."company" AND sc."modelId"=56 AND sc."generalId"::text=m."xxjlid"::text AND COALESCE(d."learned",0)=0 AND COALESCE(NULLIF(BTRIM(d."xxqs"),\'\'),\'[]\')=\'[]\' AND NOT ('+notesSource+'))');
  const add=(sql,value)=>{args.push(value);clauses.push(sql.replace('?', '$'+args.length));};
  const studentsPromise=coach&&includeMetadata? Psql.query('SELECT m."yhid"::text AS id,COALESCE(MAX(NULLIF(BTRIM(u."realName"),\'\')),MAX(NULLIF(BTRIM(u."nickname"),\'\')),MAX(NULLIF(BTRIM(u."legacyUserData"->>\'HoneyName\'),\'\')),MAX(NULLIF(BTRIM(u."username"),\'\')),\'学员 \'||m."yhid"::text) AS name,COUNT(*)::int AS "taskCount" FROM "MemoryPracticeRecord" m LEFT JOIN "_User" u ON u."company"=m."company" AND u."legacyUserId"=m."yhid" WHERE '+clauses.join(' AND ')+' GROUP BY m."yhid"',[...args]):Promise.resolve([]);
  if(coach&&number(input.studentId))add('m."yhid"::text=?',String(input.studentId));
  const baseWhere=clauses.join(' AND '),baseArgs=[...args];
  const dateSql='LEFT(COALESCE(NULLIF(m."kywrq",\'\'),m."kywsj"),10)';
  const summaryPromise= Psql.one('SELECT COUNT(*)::int total,COUNT(*) FILTER(WHERE m."fxzt"=\'1\')::int completed,COUNT(*) FILTER(WHERE COALESCE(m."fxzt",\'0\')<>\'1\' AND '+dateSql+'<=$'+(args.length+1)+')::int due,COUNT(*) FILTER(WHERE '+dateSql+'=$'+(args.length+1)+')::int today,COUNT(*) FILTER(WHERE '+dateSql+'=$'+(args.length+1)+' AND m."fxzt"=\'1\')::int \"todayCompleted\",COUNT(*) FILTER(WHERE '+dateSql+'<$'+(args.length+1)+' AND COALESCE(m."fxzt",\'0\')<>\'1\')::int \"overduePending\" FROM "MemoryPracticeRecord" m WHERE '+baseWhere,[...args,today]);
  const datesPromise=includeMetadata? Psql.query('SELECT '+dateSql+' AS date,COUNT(*)::int total,COUNT(*) FILTER(WHERE m."fxzt"=\'1\')::int completed FROM "MemoryPracticeRecord" m WHERE '+baseWhere+' GROUP BY '+dateSql,baseArgs):Promise.resolve([]);
  if(input.scope==='today')add(dateSql+'=?',today);
  else if(input.scope==='overdue') {add(dateSql+'<?',today);clauses.push('COALESCE(m."fxzt",\'0\')<>\'1\'');}
  else if(input.date)add(dateSql+'=?',String(input.date));
  if(input.month)add('LEFT('+dateSql+',7)=?',String(input.month));
  if(input.status==='completed')clauses.push('m."fxzt"=\'1\'');
  if(input.status==='pending')clauses.push('COALESCE(m."fxzt",\'0\')<>\'1\'');
  const where=clauses.join(' AND '),countPromise=Psql.one('SELECT COUNT(*)::int total FROM "MemoryPracticeRecord" m WHERE '+where,args);
  // Historical createdAt is the import time; the study model preserves the original learning time.
  const rowsPromise=Psql.query('SELECT c.*,row_to_json(m) AS "__addon",COALESCE(n."nodeName",CASE WHEN '+notesSource+' THEN \'语法写作复习\' END) AS "kc_title",d."dqrq" AS "learned_date",dc."createTime" AS "learned_at",r."amount" AS money,r."status" AS "rewardStatus",r."ineligibleReason" AS "ineligibleReason",r."completedAt" AS "completedAt",r."originalAmount" AS "originalAmount",r."correctionReason" AS "correctionReason",r."correctionAmount" AS "correctionAmount",'+reviewStudentNameSql()+' AS "honeyName",(SELECT COUNT(*) FROM "MemoryPracticeRecord" sibling WHERE sibling."company"=m."company" AND sibling."yhid"=m."yhid" AND sibling."xxjlid"=m."xxjlid" AND (sibling."kywsj",sibling."id")<=(m."kywsj",m."id"))::int AS "reviewNo" FROM "MemoryPracticeRecord" m JOIN "CommonModel" c ON c."company"=m."company" AND c."modelId"=60 AND c."itemId"=m."id" LEFT JOIN "Node" n ON n."company"=m."company" AND n."nodeId"::text=m."kcid"::text LEFT JOIN "CommonModel" dc ON dc."company"=m."company" AND dc."modelId"=56 AND dc."generalId"::text=m."xxjlid"::text LEFT JOIN "DailyStudyRecord" d ON d."company"=m."company" AND d."id"=dc."itemId" LEFT JOIN "ReviewRewardIncome" r ON r."company"=m."company" AND r."taskId"=m."objectId" LEFT JOIN "_User" u ON u."company"=m."company" AND u."legacyUserId"=m."yhid" WHERE '+where+(input.scope==='today'?' ORDER BY CASE WHEN m."fxzt"=\'1\' THEN 1 ELSE 0 END,m."kywsj",m."id"':' ORDER BY m."kywsj" DESC,m."id" DESC')+' LIMIT $'+(args.length+1)+' OFFSET $'+(args.length+2),[...args,p.size,(p.index-1)*p.size]);
  const versionTarget=coach&&number(input.studentId)?number(input.studentId):uid;
  const [students,summary,dates,count,rows,income,versions]=await Promise.all([studentsPromise,summaryPromise,datesPromise,countPromise,rowsPromise,coach&&input.includeIncome!==false?Psql.one('SELECT COALESCE(SUM(r."amount"),0) AS income FROM "MemoryPracticeRecord" m JOIN "ReviewRewardIncome" r ON r."company"=m."company" AND r."taskId"=m."objectId" WHERE '+where,args).then(row=>number(row.income)):0,appLiveVersionSnapshot(versionTarget,['review','account'],current)]);
  return {items:rows.map(row=>{const addon=row.__addon;delete row.__addon;return{...legacyAliases({...row,...addon},'CommonModel'),money:number(row.money),rewardStatus:row.rewardStatus||'pending'};}),page:mobilePageMeta(count.total,p),summary:{...summary,todayTotal:number(summary.today),todayCompleted:number(summary.todayCompleted),todayPending:number(summary.today)-number(summary.todayCompleted),overduePending:number(summary.overduePending),income,incomeScope:input.month||'all'},dates,students,legacyReviewSync,serverToday:today,...mobileSource(),...versions};
}

async function appLiveScopeVersion(scope,targetId,current){
  const companyId=DEFAULT_COMPANY_ID,uid=String(targetId);
  if(scope==='learning')return appLiveVersion(await Psql.one('SELECT MAX(GREATEST(COALESCE(d."updatedAt",d."createdAt"),COALESCE(c."updatedAt",c."createdAt"))) AS changed_at,COUNT(*)::int AS total FROM "DailyStudyRecord" d LEFT JOIN "CommonModel" c ON c."company"=d."company" AND c."modelId"=56 AND c."itemId"=d."id" WHERE d."company"=$1 AND d."userId"=$2',[companyId,targetId]));
  if(scope==='courses')return appLiveVersion(await Psql.one('SELECT MAX(GREATEST(COALESCE(b."updatedAt",b."createdAt"),COALESCE(c."updatedAt",c."createdAt"),COALESCE(n."updatedAt",n."createdAt"))) AS changed_at,COUNT(*)::int AS total FROM "CourseBinding" b LEFT JOIN "CommonModel" c ON c."company"=b."company" AND c."modelId"=58 AND c."itemId"=b."id" LEFT JOIN "Node" n ON n."company"=b."company" AND CAST(n."nodeId" AS text)=CAST(b."kcid" AS text) WHERE b."company"=$1 AND CAST(b."yhid" AS text)=$2',[companyId,uid]));
  if(scope==='appointments')return appLiveVersion(await Psql.one('SELECT MAX(GREATEST(COALESCE(a."updatedAt",a."createdAt"),COALESCE(c."updatedAt",c."createdAt"))) AS changed_at,COUNT(*)::int AS total FROM "CourseAppointment" a LEFT JOIN "CommonModel" c ON c."company"=a."company" AND c."modelId"=54 AND c."itemId"=a."id" WHERE a."company"=$1 AND (CAST(a."szyh" AS text)=$2 OR CAST(a."pl" AS text)=$2 OR CAST(a."fxpl" AS text)=$2)',[companyId,uid]));
  if(scope==='reading'){
    const learner=await legacyUserObject(targetId),ownerIds=learner?[learner.id,'_User$'+learner.id]:[];if(!ownerIds.length)return ':0';
    return appLiveVersion(await Psql.one('SELECT MAX(GREATEST(COALESCE(a."updatedAt",a."createdAt"),COALESCE(l."updatedAt",l."createdAt"))) AS changed_at,COUNT(DISTINCT a."objectId")::int+COUNT(DISTINCT l."objectId")::int AS total FROM "SurveyItem" a LEFT JOIN "SurveyLog" l ON CAST(l."surveyItem" AS text)=ANY(ARRAY[CAST(a."objectId" AS text),\'SurveyItem$\'||CAST(a."objectId" AS text)]) AND CAST(l."user" AS text)=ANY($1::text[]) WHERE CAST(a."user" AS text)=ANY($1::text[]) AND a."type"=\'reading\' AND COALESCE(a."isDeleted",FALSE)=FALSE',[ownerIds]));
  }
  if(scope==='review')return appLiveVersion(await Psql.one('SELECT MAX(COALESCE("updatedAt","createdAt")) AS changed_at,COUNT(*)::int AS total FROM "MemoryPracticeRecord" WHERE "company"=$1 AND (CAST("yhid" AS text)=$2 OR CAST("plid" AS text)=$2)',[companyId,uid]));
  if(scope==='account')return appLiveVersion(await Psql.one('SELECT MAX(changed_at) AS changed_at,SUM(total)::int AS total FROM (SELECT MAX(COALESCE("updatedAt","createdAt")) changed_at,COUNT(*)::int total FROM "_User" WHERE "company"=$1 AND "legacyUserId"=$2 UNION ALL SELECT MAX(COALESCE("updatedAt","createdAt")),COUNT(*)::int FROM "LessonRecord" WHERE "company"=$1 AND (CAST("xymz" AS text)=$3 OR CAST("jsmz" AS text)=$3) UNION ALL SELECT MAX(COALESCE("updatedAt","createdAt")),COUNT(*)::int FROM "CourseCreditReservation" WHERE "company"=$1 AND "studentId"=$2) versions',[companyId,targetId,uid]));
  return ':0';
}