xiaoshu-app/scripts/tests/member-credit-ledger.test.mjs

import {test,before,after} from 'node:test';
import assert from 'node:assert/strict';
import {readFileSync} from 'node:fs';
import {PGlite} from '@electric-sql/pglite';
const db=new PGlite();const sql=readFileSync(new URL('../sql/member-enrollment.sql',import.meta.url),'utf8');
const query=async(s,p=[])=>(await db.query(s,p)).rows;
const one=async(s,p=[])=>(await query(s,p))[0];
before(async()=>{
 await db.exec(`CREATE TABLE "_User" ("objectId" text PRIMARY KEY,"company" text,"legacyUserId" double precision,"legacyGroupId" double precision,"identityType" text,"isDisabled" boolean,"legacyUserData" jsonb,"updatedAt" timestamptz);
 CREATE TABLE "CourseBinding" ("objectId" text PRIMARY KEY,"company" text,"id" double precision,"createdAt" timestamptz,"updatedAt" timestamptz,"sourceKey" text,"yhid" text,"kcid" text,"yxx" double precision,"cksl" double precision,"syjd" text);
 CREATE TABLE "CommonModel" ("objectId" text PRIMARY KEY,"company" text,"createdAt" timestamptz,"updatedAt" timestamptz,"createTime" timestamptz,"sourceKey" text,"generalId" double precision,"itemId" double precision,"modelId" double precision,"nodeId" double precision,"tableName" text,"title" text,"subtitle" text,"status" double precision,"inputer" text);
 CREATE TABLE "CourseAppointment" ("objectId" text PRIMARY KEY,"company" text,"id" double precision,"szyh" text,"kcid" text,"pl" text,"fxpl" text,"dszt" text,"dslx" text,"jffs" text,"jssj" text,"updatedAt" timestamptz,"creditCostsSnapshot" jsonb,"creditHoldStatus" text,"courseCategoryKey" text,"courseCategoryName" text,"teacherPaySnapshot" double precision);
 CREATE TABLE "LessonRecord" ("objectId" text PRIMARY KEY,"company" text,"id" double precision,"createdAt" timestamptz,"updatedAt" timestamptz,"sourceKey" text,"xymz" text,"jsmz" text,"kcid" text,"kclx" text,"kcmc" text,"yyds" text,"jffs" text,"kzsj" text,"lessonAt" timestamptz);
 CREATE TABLE "DailyStudyRecord" ("objectId" text);
 CREATE TABLE "CourseTeachingProfile" ("company" text,"courseId" double precision,"categoryKey" text,"confirmed" boolean);
 CREATE TABLE "CourseCreditReservation" ("objectId" text PRIMARY KEY,"company" text,"studentId" double precision,"appointmentId" text,"creditCosts" jsonb,"state" text,"consumedAt" timestamptz,"updatedAt" timestamptz);`);
 for(const table of ['UserExpDomP','UserSIcon','UserExpHis','UserUserPoint'])await db.exec(`CREATE TABLE "${table}" ("objectId" text PRIMARY KEY,"company" text,"sourceKey" text UNIQUE,"createdAt" timestamptz,"updatedAt" timestamptz,"userId" double precision,"score" double precision,"scoreBefore" double precision,"hisTime" timestamptz,"operator" double precision,"scoreType" double precision,"detail" text,"remark" text,"payMethod" text)`);
 await db.exec(`INSERT INTO "_User" VALUES ('store','co',10,2,'store',false,'{}',now()),('student','co',20,1,'member',false,'{"ParentUserID":10}',now()),('empty','co',21,1,'member',false,'{}',now()),('foreign','other',22,1,'member',false,'{}',now());`);
 await db.exec(`ALTER TABLE "CourseAppointment" ADD COLUMN "kssj" text,ADD COLUMN "plxm" text,ADD COLUMN "yykcid" text,ADD COLUMN "durationMinutes" numeric,ADD COLUMN "deliveryMode" text,ADD COLUMN "teachingRuleId" text,ADD COLUMN "bxrq" text,ADD COLUMN "sdsd" text,ADD COLUMN "yysj" text,ADD COLUMN "sourceKey" text,ADD COLUMN "createdAt" timestamptz;
 ALTER TABLE "CourseCreditReservation" ADD COLUMN "sourceKey" text,ADD COLUMN "createdAt" timestamptz,ADD COLUMN "appointmentGeneralId" numeric,ADD COLUMN "ruleId" text,ADD COLUMN "reservedAt" timestamptz;`);
 await db.exec(`CREATE TABLE "Node" ("company" text,"nodeId" numeric,"parentId" numeric,"nodeName" text,"zstatus" numeric);
 CREATE TABLE "SysLog" ("objectId" text PRIMARY KEY,"company" text,"createdAt" timestamptz,"updatedAt" timestamptz,"sourceKey" text NOT NULL UNIQUE,"id" double precision,"cdate" timestamptz,"cname" text,"type1" text,"type2" text,"type3" text,"clevel" double precision,"cadminId" double precision,"detail" text,"content1" text,"content2" text);
 INSERT INTO "Node" VALUES ('co',100,8,'词表',99),('co',101,8,'空词表',99);
 INSERT INTO "CommonModel" ("objectId","company","nodeId","modelId","status") SELECT 'word-'||i,'co',100,52,99 FROM generate_series(1,200) i;`);
 await db.exec(sql);
 await db.exec('ALTER TABLE "_User" ADD COLUMN "realName" text, ADD COLUMN nickname text, ADD COLUMN username text');
 await db.exec(readFileSync(new URL('../sql/member-credit-ledger.sql',import.meta.url),'utf8'));
});
after(()=>db.close());
const bal=async(id)=>(await one('SELECT "legacyUserData" b FROM "_User" WHERE "objectId"=$1',[id])).b;
const payload=(account,amount,extra={})=>({account,amount,direction:'add',payMethod:'运营记账',...extra});
const plan=async(p)=>(await one("SELECT xs_credit_plan('co','student',$1::jsonb) r",[JSON.stringify(p)])).r;
const apply=async(p,key,version)=>(await one("SELECT xs_credit_operation('co','admin','student',$1::jsonb,'测试充值',$2,$3) r",[JSON.stringify(p),key,version|| (await plan(p)).version])).r;
test('成套充值、版本、退款、差错调整和原子回滚',async()=>{
 await db.exec(`SELECT set_config('xiaoshu.credit_write','yes',false); UPDATE "_User" SET "legacyUserData"='{"Purse":100,"SilverCoin":100,"UserPoint":100,"UserExp":10}' WHERE "objectId"='store';`);
 const p=payload('Purse',40),preview=await plan(p);assert.equal(preview.lines[1].amount,20);
 const r=await apply(p,'paired-0001',preview.version);assert.equal((await bal('student')).Purse,40);assert.equal((await bal('student')).UserPoint,20);assert.equal((await bal('store')).UserPoint,80);
 assert.deepEqual(await apply(p,'paired-0001',preview.version),r);
 await assert.rejects(apply(payload('Purse',1),'stale-0001',preview.version),/预览/);
 const refund=payload('Purse',10,{direction:'deduct',sourceTransactionId:r.transactions[0].objectId});await apply(refund,'refund-0001');assert.equal((await bal('student')).Purse,30);assert.equal((await bal('student')).UserPoint,15);assert.equal((await bal('store')).UserPoint,85);
 await assert.rejects(apply({...refund,amount:31},'refund-too-many'),/可退/);
 const correction=payload('Purse',2,{direction:'adjust',adjustSign:'deduct',adjustMode:'paired',evidence:'历史原单错误凭据'});await apply(correction,'correct-0001');assert.equal((await bal('student')).Purse,28);assert.equal((await bal('student')).UserPoint,14);assert.equal((await bal('store')).UserPoint,85);
 const before=await bal('student');await assert.rejects(apply(payload('Purse',300),'stock-too-low'),/库存不足/);assert.deepEqual(await bal('student'),before);
 await db.exec(`INSERT INTO "CourseCreditReservation"("objectId",company,"studentId","appointmentId","creditCosts",state) VALUES('held','co',20,'absent','[{"account":"Purse","amount":28}]','reserved');`);
 await assert.rejects(apply(payload('Purse',1,{direction:'adjust',adjustSign:'deduct',evidence:'测试依据'}),'hold-00001'),/预留/);
 await db.exec(`DELETE FROM "CourseCreditReservation" WHERE "objectId"='held'`);
 const page=await query(`SELECT * FROM xs_member_ledger WHERE company='co' AND "studentId"=20`);assert.equal(new Set(page.map(r=>r.groupId)).size,3);assert.equal(page.length,6);
});
test('体验待核验阻止、一次授予、退回不恢复、旧入口也阻止',async()=>{
 const p=payload('UserExp',1);await assert.rejects(apply(p,'trial-pending'),/待核验/);
 await db.exec(`UPDATE "TrialEligibility" SET status='eligible' WHERE company='co' AND "studentId"=20`);
 const r=await apply(p,'trial-00001');assert.equal((await bal('student')).UserExp,1);
 await assert.rejects(apply(p,'trial-00002'),/体验/);
 await apply(payload('UserExp',1,{direction:'deduct',sourceTransactionId:r.transactions[0].objectId}),'trial-return');
 assert.equal((await bal('student')).UserExp,0);await assert.rejects(apply(p,'trial-after-return'),/体验/);
 await assert.rejects(one("SELECT xs_credit_post('co','admin','transfer','student','UserExp',1,'','旧页面重试','转账','trial-old-gateway')"),/体验/);
});
test('完整旧流水持续分页导入，幂等且余额不变；未知余额保留空值',async()=>{
 const vm=await import('node:vm');
 await db.exec(`ALTER TABLE "_User" ADD "sourceKey" text,ADD "isDeleted" boolean; UPDATE "_User" SET "sourceKey"='legacy-sync:user:20',username='student',"legacyUserData"="legacyUserData"||'{"UserName":"student"}' WHERE "objectId"='student'; CREATE TABLE "LegacySyncConflict"("objectId" text,company text,"sourceKey" text,dataset text,"legacyKey" text,"conflictType" text,message text,resolved boolean,resolution text,"createdAt" timestamptz,"updatedAt" timestamptz);`);
 const Psql={query,any:query,one,oneOrNone:async(s,p)=>(await query(s,p))[0]||null,none:async(s,p)=>{await query(s,p);},tx:async f=>{await db.exec("BEGIN;SELECT set_config('xiaoshu.credit_write','yes',true)");try{const r=await f(Psql);await db.exec('COMMIT');return r;}catch(e){await db.exec('ROLLBACK');throw e;}}};
 const ctx={Psql,Date};vm.createContext(ctx);vm.runInContext(readFileSync(new URL('../cloud/legacy-finance.js',import.meta.url),'utf8'),ctx);
 await db.exec(`UPDATE "_User" SET "sourceKey"=NULL WHERE "objectId"='student'`);await assert.rejects(ctx.importLegacyFinance('co',20,'new-only-test',()=>{throw Error('must not fetch');}),/identity_unverified/);await db.exec(`UPDATE "_User" SET "sourceKey"='legacy-sync:user:20' WHERE "objectId"='student'`);
 const rows=Array.from({length:1001},(_,i)=>({ExpHisID:i+1,UserID:20,UserName:'student',score:i===0?20:-1,score_before:i===0?null:20-i,HisTime:'2026-10-11T20:47:00',Detail:'原始记录'}));
 const read=async p=>({items:p.stype===2?rows.slice((p.cpage-1)*1000,p.cpage*1000):[],page:{itemCount:p.stype===2?1001:0,pageCount:p.stype===2?2:1}});
 const before=await bal('student'),r=await ctx.importLegacyFinance('co',20,'import-0001',read);assert.equal(r.created,1001);assert.deepEqual(await bal('student'),before);assert.equal((await ctx.importLegacyFinance('co',20,'import-0002',read)).created,0);assert.equal((await one('SELECT count(*)::int n FROM "LegacyFinanceReceipt"')).n,8);
 assert.equal((await one(`SELECT "before" FROM xs_member_ledger WHERE reference='1' AND account='SilverCoin'`)).before,null);
 const uppercase=async p=>({items:p.stype===4?[{...rows[0],ExpHisID:2001,score:undefined,Score:20}]:[],page:{itemCount:p.stype===4?1:0,pageCount:1}});assert.equal((await ctx.importLegacyFinance('co',20,'import-upper',uppercase)).created,1);assert.equal((await one(`SELECT "after" FROM xs_member_ledger WHERE reference='2001' AND account='UserPoint'`)).after,null);assert.equal(Number((await one(`SELECT score FROM "UserUserPoint" WHERE "legacyFinanceId"='2001'`)).score),20);
 Object.assign(ctx,{memberTarget:async()=>({id:'student',get:k=>k==='company'?'co':20}),pointerId:v=>v,number:(v,f=0)=>Number.isFinite(Number(v))?Number(v):f,text:v=>String(v??''),fail:(_s,m)=>{throw Error(m);}});vm.runInContext(readFileSync(new URL('../cloud/member-credit-ledger.js',import.meta.url),'utf8'),ctx);
 const ids=[];for(let page=1;page<=11;page++){const result=await ctx.memberLedger({},{account:'SilverCoin',page,pageSize:100,dateFrom:'2026-10-11',dateTo:'2026-10-11'});assert.equal(result.total,1001);ids.push(...result.items.map(r=>r.groupId));}assert.equal(ids.length,1001);assert.equal(new Set(ids).size,1001);assert.equal((await ctx.memberLedger({},{account:'SilverCoin',dateFrom:'2026-10-12'})).total,0);
 rows[0].score=30;await assert.rejects(ctx.importLegacyFinance('co',20,'import-0003',read),/conflict/);assert.equal((await one('SELECT count(*)::int n FROM "LegacySyncConflict"')).n,1);assert.deepEqual(await bal('student'),before);
});
test('40 次30分钟结课后39和19.5；60分钟及重复结课快照一致',async()=>{
 await db.exec("BEGIN;SELECT set_config('xiaoshu.credit_write','yes',true)");try{
 await db.exec(`UPDATE "_User" SET "legacyUserData"='{"ParentUserID":10,"Purse":0,"SilverCoin":0,"UserPoint":0}' WHERE "objectId"='student';UPDATE "_User" SET "legacyUserData"='{"Purse":100,"SilverCoin":100,"UserPoint":100}' WHERE "objectId"='store';`);
 await apply(payload('Purse',40),'scenario-40');assert.equal((await bal('student')).UserPoint,20);
 const enroll=(await one(`SELECT xs_enroll('co','admin',20,'word','[{"courseId":100,"courseName":"词表","categoryName":"单词课"}]','[[{"account":"Purse","amount":1},{"account":"UserPoint","amount":0.5}]]','测试开课','scenario-enroll') r`)).r;
 async function finish(id,account,point){const costs=[{account,amount:1},{account:'UserPoint',amount:point}];await query(`INSERT INTO "CourseAppointment"("objectId",company,id,szyh,kcid,pl,dszt,dslx,"bindingId","creditCostsSnapshot","teacherPaySnapshot","courseCategoryKey") VALUES($1,'co',$2,'20','100','30','10',$3,$4,$5::jsonb,30,'word')`,['a'+id,id,point===0.5?'1':'2',enroll.bindingIds[0],JSON.stringify(costs)]);await query(`INSERT INTO "CommonModel"("objectId",company,"modelId","generalId","itemId",status) VALUES($1,'co',54,$2,$2,99)`,['c'+id,id]);await query(`INSERT INTO "CourseCreditReservation"("objectId",company,"studentId","appointmentId","creditCosts",state) VALUES($1,'co',20,$2,$3::jsonb,'reserved')`,['r'+id,'c'+id,JSON.stringify(costs)]);const r=await one(`SELECT xs_complete_appointment('co',$1,'admin') r`,['c'+id]);assert.equal((await one(`SELECT xs_complete_appointment('co',$1,'admin') r`,['c'+id])).r.idempotent,true);return r.r;}
 const r=await finish(9001,'Purse',0.5);assert.equal((await bal('student')).Purse,39);assert.equal((await bal('student')).UserPoint,19.5);assert.equal(Number((await one('SELECT "teacherPaySnapshot" FROM "LessonRecord" WHERE "objectId"=$1',[r.lessonId])).teacherPaySnapshot),30);
 await apply(payload('SilverCoin',20),'scenario-60');await finish(9002,'SilverCoin',1);assert.equal((await bal('student')).SilverCoin,19);assert.equal((await bal('student')).UserPoint,38.5);
 const lines=await query(`SELECT * FROM xs_member_ledger WHERE "lessonId"=$1`,[r.lessonId]);assert.equal(lines.length,2);assert.ok(lines.every(l=>l.kind==='lesson'));
 }finally{await db.exec('ROLLBACK');}
});
test('体验预留只能一次，取消释放后可重新预约',async()=>{
 await db.exec("BEGIN;SELECT set_config('xiaoshu.credit_write','yes',true)");try{await db.exec(`UPDATE "TrialEligibility" SET status='eligible' WHERE company='co' AND "studentId"=20;UPDATE "_User" SET "legacyUserData"=jsonb_set("legacyUserData",'{UserExp}','1') WHERE "objectId"='student'`);
 const reserve=id=>query(`INSERT INTO "CourseCreditReservation"("objectId",company,"studentId","appointmentId","creditCosts",state) VALUES($1,'co',20,$1,'[{"account":"UserExp","amount":1}]','reserved')`,[id]);await reserve('trialhold1');
 await db.exec('SAVEPOINT second_trial');await assert.rejects(reserve('trialhold2'),/预留|体验/);await db.exec('ROLLBACK TO SAVEPOINT second_trial');
 await query(`UPDATE "CourseCreditReservation" SET state='released' WHERE "objectId"='trialhold1'`);await reserve('trialhold2');assert.equal((await one(`SELECT xs_reserved('co',20,'UserExp') n`)).n,'1');
 }finally{await db.exec('ROLLBACK');}
});

test('仅修复未经编辑的早期旧流水8小时时区偏移，留存前后审计且不改变额度',async()=>{
 const vm=await import('node:vm');
 await db.exec('ALTER TABLE "UserExpDomP" ADD COLUMN "expHisId" numeric');
 const Psql={query,any:query,one,oneOrNone:async(s,p)=>(await query(s,p))[0]||null,none:async(s,p)=>{await query(s,p);},tx:async f=>{await db.exec('BEGIN');try{const r=await f(Psql);await db.exec('COMMIT');return r;}catch(e){await db.exec('ROLLBACK');throw e;}}};
 const ctx={Psql,Date};vm.createContext(ctx);vm.runInContext(readFileSync(new URL('../cloud/legacy-finance.js',import.meta.url),'utf8'),ctx);
 const raw={ExpHisID:3001,UserID:20,UserName:'student',Score:10,score_before:0,HisTime:'2024-06-15T22:39:22',Detail:'原始充值',Remark:'旧系统',Operator:1,ScoreType:1};
 const original={objectId:'old-timezone',company:'co',sourceKey:'[["ExpHisID",3001]]',expHisId:3001,userId:20,score:10,scoreBefore:0,hisTime:'2024-06-16T06:39:22+08:00',detail:raw.Detail,remark:raw.Remark,operator:1,scoreType:1};
 const read=async p=>({items:p.stype===1?[raw]:[],page:{itemCount:p.stype===1?1:0,pageCount:1}});
 await query('INSERT INTO "UserExpDomP" SELECT * FROM jsonb_populate_record(NULL::"UserExpDomP",$1::jsonb)',[JSON.stringify(original)]);
 const before=await bal('student');const imported=await ctx.importLegacyFinance('co',20,'timezone-repair-1',read);assert.equal(imported.created,0);assert.deepEqual(await bal('student'),before);
 const corrected=await one('SELECT "hisTime" FROM "UserExpDomP" WHERE "objectId"=$1',[original.objectId]);assert.equal(Date.parse(corrected.hisTime),Date.parse('2024-06-15T22:39:22+08:00'));
 const audit=await one('SELECT message,resolved FROM "LegacySyncConflict" WHERE "sourceKey"=$1',['finance-timezone:Purse:3001']);assert.equal(audit.resolved,true);assert.equal(JSON.parse(audit.message).before.objectId,original.objectId);
 assert.equal((await ctx.importLegacyFinance('co',20,'timezone-repair-2',read)).created,0);assert.equal((await one('SELECT count(*)::int n FROM "LegacySyncConflict" WHERE "sourceKey"=$1',['finance-timezone:Purse:3001'])).n,1);
 for(const patch of [{updatedAt:'2026-10-01T00:00:00Z'},{createdAt:'2026-10-01T00:00:00Z'},{transactionId:'new-operation'},{legacyFinanceHash:'verified'},{sourceKey:'credit:new'},{hisTime:'2024-06-16T05:39:22+08:00'},{detail:'人工修正'}])assert.equal(ctx.untouchedLegacyFinanceTimezoneRow({...original,...patch},raw,'3001','2024-06-15T22:39:22+08:00'),false);
});