xiaoshu-app/scripts/tests/business-record-pagination.test.mjs

import {test} from 'node:test';import assert from 'node:assert/strict';import vm from 'node:vm';import {readFileSync} from 'node:fs';import {PGlite} from '@electric-sql/pglite';
const source=readFileSync(new URL('../deploy-admin-functions.mjs',import.meta.url),'utf8').match(/const operationsGatewayCode = String.raw`([\s\S]*?)\n`;/)[1];
test('完整来源超过5000条仍可分页、搜索较早记录，空末页保留真实总数且租户隔离',async()=>{
 const db=new PGlite();try{
  await db.exec(`CREATE TABLE records(company text,"objectId" text,"targetId" text,"generalId" integer,"commonUpdatedAt" timestamptz,"studentObjectId" text,"studentRealName" text,"studentNickname" text,"studentUsername" text,"studentLegacy" jsonb,"courseTitle" text,"userId" text,dqrq text,learned int,ygg int,djq int);
   INSERT INTO records SELECT 'co','r'||g,'r'||g,g,'2026-10-12'::timestamptz-g*interval '1 second','s','学员'||g,'','','{}','单词','20','20261012',20,15,5 FROM generate_series(1,6005) g;
   INSERT INTO records SELECT 'other','private','private',99999,now(),'x','其他帐套','','','{}','单词','99','20261012',20,20,0;`);
  const ctx=vm.createContext({Date,Psql:{query:async(sql,args)=>(await db.query(sql,args)).rows}});vm.runInContext(source,ctx);
  const sql='SELECT * FROM records WHERE company=$1 AND ($2=\'\' OR "userId"=$2) ORDER BY "commonUpdatedAt" DESC';
  let page=await ctx.businessRecordSqlPage('learning-records',sql,['co',''],{page:251,pageSize:20});assert.equal(page.total,6005);assert.equal(page.items.length,20);assert.equal(page.items[0].generalId,5001);
  page=await ctx.businessRecordSqlPage('learning-records',sql,['co',''],{search:'学员6005'});assert.equal(page.total,1);assert.equal(page.items[0].generalId,6005);
  page=await ctx.businessRecordSqlPage('learning-records',sql,['co',''],{page:999,pageSize:20});assert.equal(page.total,6005);assert.equal(page.items.length,0);
  for(const search of ['其他帐套','%','_'])assert.equal((await ctx.businessRecordSqlPage('learning-records',sql,['co',''],{search})).total,0);
 }finally{await db.close();}
});

test('预约日期范围包含北京时间首尾全天，单侧筛选、分页和无效范围均正确',async()=>{
 const db=new PGlite();try{
  await db.exec(readFileSync(new URL('../sql/payroll-integrity.sql',import.meta.url),'utf8').match(/CREATE OR REPLACE FUNCTION xs_payroll_time\([\s\S]*?\$\$;/)[0]);
  await db.exec(`CREATE TABLE appointments(company text,"objectId" text,"targetId" text,"commonUpdatedAt" timestamptz,yysj text,bxrq text,sdsd text);
   INSERT INTO appointments VALUES
   ('co','before','before',now(),'2027-01-01T15:59:59Z','',''),
   ('co','start','start',now(),'2027-01-01T16:00:00Z','',''),
   ('co','split','split',now(),'2027-01-02','2027-01-02','12:00'),
   ('co','end','end',now(),'2027-01-02T15:59:59Z','',''),
   ('co','after','after',now(),'2027-01-02T16:00:00Z','',''),
   ('other','private','private',now(),'2027-01-02 12:00','','');`);
  const ctx=vm.createContext({Date,fail:(_code,message)=>{throw Error(message);},Psql:{query:async(sql,args)=>(await db.query(sql,args)).rows}});vm.runInContext(source,ctx);
  const read=input=>ctx.businessRecordSqlPage('appointments','SELECT * FROM appointments WHERE company=$1',['co'],input);
  const filters={dateFrom:'2027-01-02',dateTo:'2027-01-02'};
  let result=await read({page:1,pageSize:2,filters});assert.equal(result.total,3);assert.deepEqual(result.items.map(r=>r.targetId),['end','split']);
  result=await read({page:2,pageSize:2,filters});assert.equal(result.total,3);assert.deepEqual(result.items.map(r=>r.targetId),['start']);
  assert.equal((await read({filters:{dateFrom:'2027-01-02'}})).total,4);
  assert.equal((await read({filters:{dateTo:'2027-01-02'}})).total,4);
  for(const filters of [{dateFrom:'2027-01-03',dateTo:'2027-01-02'},{dateFrom:'2027-02-30'},{dateTo:'invalid'}])await assert.rejects(read({filters}),/日期/);
 }finally{await db.close();}
});

test('跨日期预约按实际预约时间倒序后分页，忽略更新时间并兼容分开的日期时间',async()=>{
 const db=new PGlite();try{
  const payrollSql=readFileSync(new URL('../sql/payroll-integrity.sql',import.meta.url),'utf8');
  await db.exec(payrollSql.match(/CREATE OR REPLACE FUNCTION xs_payroll_time\([\s\S]*?\$\$;/)[0]);
  await db.exec(`CREATE TABLE appointments(company text,"objectId" text,"targetId" text,"generalId" int,"commonUpdatedAt" timestamptz,
   "studentObjectId" text,"studentRealName" text,"studentNickname" text,"studentUsername" text,"studentLegacy" jsonb,"courseTitle" text,
   szyh text,kcid text,"coachObjectId" text,"coachRealName" text,"coachNickname" text,"coachUsername" text,"coachLegacy" jsonb,"courseCategoryKey" text,"courseCategoryName" text,dszt text,dslx text,yysj text,bxrq text,sdsd text)`);
  const records=[
   ['december','2026-12-05 19:30','',''],['february','2027-02-06 19:30','',''],
   ['january','2027-01-02 19:30','',''],['later-time','2027-01-02 20:30','',''],
   ['separate-date','','2027-01-23','19:30'],['fallback-date','invalid','2027-01-09','19:30'],
   ['date-only','2027-01-02','2027-01-02','21:00'],['utc','2027-01-02T12:00:00Z','',''],['unknown','invalid','',''],['z-tie','2027-02-06 19:30','',''],
  ];
  for(const [index,[id,scheduled,date,time]] of records.entries())await db.query(`INSERT INTO appointments(company,"objectId","targetId","generalId","commonUpdatedAt","studentRealName","studentLegacy","coachLegacy",yysj,bxrq,sdsd,dszt)
   VALUES('co',$1,$1,$2::int,'2026-11-01'::timestamptz+$2::int*interval '1 hour','目标学员','{}','{}',$3,$4,$5,$6)`,[id,index,scheduled,date,time,index%2?'0':'40']);
  await db.exec(`INSERT INTO appointments(company,"objectId","targetId","commonUpdatedAt","studentRealName",yysj) VALUES('other','private','private',now(),'目标学员','2050-01-01 00:00'),('co','different','different',now(),'其他学员','2040-01-01 00:00')`);
  const ctx=vm.createContext({Date,Psql:{query:async(sql,args)=>(await db.query(sql,args)).rows}});vm.runInContext(source,ctx);
  const items=[];
  for(let page=1;page<=Math.ceil(records.length/3);page++){
   const result=await ctx.businessRecordSqlPage('appointments','SELECT * FROM appointments WHERE company=$1',['co'],{page,pageSize:3,search:'目标学员'});
   assert.equal(result.total,records.length);items.push(...result.items);
  }
  assert.deepEqual(items.map(row=>row.targetId),['z-tie','february','separate-date','fallback-date','date-only','later-time','utc','january','december','unknown']);
  const range=await ctx.businessRecordSqlPage('appointments','SELECT * FROM appointments WHERE company=$1',['co'],{page:2,pageSize:2,search:'目标学员',filters:{dateFrom:'2027-01-02',dateTo:'2027-01-02'}});
  assert.equal(range.total,4);assert.deepEqual(range.items.map(row=>row.targetId),['utc','january']);
 }finally{await db.close();}
});