import {test,after} from 'node:test';
import assert from 'node:assert/strict';
import {readFileSync} from 'node:fs';
import vm from 'node:vm';
import {PGlite} from '@electric-sql/pglite';
const source=readFileSync(new URL('../deploy-admin-functions.mjs',import.meta.url),'utf8');
const baseline=JSON.parse(readFileSync(new URL('../../perf/admin-data-2026-10-15/legacy-read-queries.json',import.meta.url),'utf8'));
const db=new PGlite(),query=async(sql,args)=>(await db.query(sql,args)).rows;
after(()=>db.close());
const ctx=vm.createContext({Psql:{query},companyIdOf:()=> 'a'});
vm.runInContext(source.match(/const operationsGatewayCode = String.raw`([\s\S]*?)\n`;/)[1],ctx);
const sorted=rows=>rows.toSorted((a,b)=>JSON.stringify(a).localeCompare(JSON.stringify(b)));
test('set-based course and appointment reads preserve tenant, cancellation, duplicates and missing metadata',async()=>{
 await db.exec(`CREATE TABLE "CommonModel"(company text,"modelId" numeric,"itemId" numeric,status numeric);
 CREATE TABLE "CourseAppointment"(company text,id numeric,szyh text,dszt text,dslx text,yysj text,bxrq text,kcid text);
 CREATE TABLE "CourseBinding"(company text,kcid text,yhid text DEFAULT 's1',id numeric DEFAULT 1);
 CREATE TABLE "Node"(company text,"nodeId" numeric,"parentId" numeric,"nodeName" text);
 INSERT INTO "Node" VALUES ('a',1,0,'小学'),('a',2,1,'课程甲'),('a',3,1,'课程乙'),('a',398,1,'写作'),('a',8,1,'未使用'),('b',2,0,'其他帐套课程');
 INSERT INTO "CourseBinding"(company,kcid) VALUES('a','2'),('a','2'),('a','bad'),('b','8');
 INSERT INTO "CourseAppointment" VALUES('a',1,'s1','0','3','2099-01-02',null,'3'),('a',2,'s1','30','1','2026-10-01',null,'3'),('a',3,'s2','11','3',null,'2099-01-03','3'),('a',4,'s2','99','1',null,null,null),('a',5,null,null,null,null,null,null),('b',1,'s1','20','3',null,null,'2');
 INSERT INTO "CommonModel" VALUES('a',54,1,99),('a',54,1,-2),('a',54,1,-2),('a',54,2,99),('a',59,3,-2),('b',54,3,-2),('b',54,1,99),('a',58,1,99),('a',58,1,99),('a',58,1,-2);`);
 const courseSql=vm.runInNewContext(source.match(/courseSql=('[\s\S]*?'),\[categories/)[1]);
 const scheduleSql=vm.runInNewContext(source.match(/const courseRows=await Psql.query\(('([\s\S]*?)'),\[companyId\]/)[1]);
 for(const co of ['a','b',null]){
  assert.deepEqual(await query(scheduleSql,[co]),await query(baseline.scheduleOptions,[co]));
  assert.deepEqual(sorted(await ctx.memberAppointmentStats(co)),sorted(await query(baseline.appointments,[co])));
  assert.deepEqual(await query(courseSql,[co]),await query(baseline.courses,[co]));
 }
 const members=await ctx.memberAppointmentStats('a');
 assert.equal(members.find(r=>r.uid==='s1').appointment_count,2);
 assert.equal(members.find(r=>r.uid==='s1').unfinished,0);
 assert.equal(members.find(r=>r.uid==='s2').unfinished,1);
 assert.equal(members.find(r=>r.uid==='s2').abnormal,1);
 assert.deepEqual((await query(courseSql,['a'])).map(r=>r.id).sort(),['2','3','398']);
});
test('income view preserves column types, corrections and source serialization without JSON row conversion',async()=>{
 await db.exec(`CREATE TABLE "ReviewReward"("objectId" text,company text,"taskId" text,"taskKey" text,"coachId" double precision,"coachName" text,"studentId" double precision,"studentName" text,"courseName" text,"storeId" double precision,"storeName" text,"plannedDate" text,"completedAt" timestamptz,"incomeMonth" text,amount numeric(12,2),status text,exception text,"createdAt" timestamptz,"ineligibleReason" text);
 CREATE TABLE "ReviewRewardCorrection"("objectId" text,company text,"rewardId" text,amount numeric(12,2),reason text,"adjustmentBatchId" text);
 INSERT INTO "ReviewReward"("objectId",company,"taskId",amount,status,"incomeMonth","ineligibleReason") VALUES('one','a','t1',1,'earned','2026-10',''),('two','a','t2',1,'earned','2026-09',''),('old','a','t3',0,'historical','2026-09',''),('other','b','t4',1,'earned','2026-10','');
 INSERT INTO "ReviewRewardCorrection" VALUES('c1','a','one',-1,'逾期','batch'),('cross','b','two',-1,'其他帐套','');
 CREATE VIEW "ReviewRewardIncome" AS SELECT projected.*,r.amount AS "originalAmount",c."objectId" AS "correctionId",c.reason AS "correctionReason",c.amount AS "correctionAmount",c."adjustmentBatchId" FROM "ReviewReward" r LEFT JOIN "ReviewRewardCorrection" c ON c.company=r.company AND c."rewardId"=r."objectId" CROSS JOIN LATERAL jsonb_populate_record(NULL::"ReviewReward",to_jsonb(r)||jsonb_build_object('amount',r.amount+COALESCE(c.amount,0),'status',CASE WHEN c."objectId" IS NULL THEN r.status ELSE 'ineligible' END,'ineligibleReason',CASE WHEN c."objectId" IS NULL THEN r."ineligibleReason" ELSE 'corrected_overdue' END)) projected;`);
 const rows=()=>query('SELECT to_jsonb(r) AS row FROM "ReviewRewardIncome" r ORDER BY "objectId"');
 const types=()=>query(`SELECT attname,atttypid,atttypmod FROM pg_attribute WHERE attrelid='"ReviewRewardIncome"'::regclass AND attnum>0 ORDER BY attnum`);
 const before=await rows(),beforeTypes=await types();
 await db.exec(readFileSync(new URL('../sql/admin-read-performance.sql',import.meta.url),'utf8'));
 assert.deepEqual(await rows(),before);assert.deepEqual(await types(),beforeTypes);
 assert.equal((await rows()).find(r=>r.row.objectId==='one').row.amount,0);
 assert.equal((await rows()).find(r=>r.row.objectId==='two').row.amount,1);
});
test('indexed numeric joins preserve lesson counts and date fallbacks',async()=>{
 await db.exec(`ALTER TABLE "CommonModel" ADD COLUMN "generalId" numeric, ADD COLUMN "createdAt" timestamptz;
 ALTER TABLE "CourseAppointment" ADD COLUMN jssj text;
 CREATE TABLE "LessonRecord"(company text,id numeric,xymz text,"lessonAt" timestamptz,plpjsj text,kclx text,yyds text);
 INSERT INTO "LessonRecord" VALUES('a',91,'s1','2026-10-01T10:00:00Z','','1','1001'),('a',92,'s1',null,'2026-10-02','3','1002'),('a',93,'s2',null,'','4','1003'),('b',91,'s1',null,'','2','1001');
 INSERT INTO "CommonModel"(company,"modelId","itemId","generalId","createdAt") VALUES('a',59,91,1091,'2026-10-01'),('a',59,93,1093,'2026-10-03'),('a',54,1,1001,'2026-10-01'),('b',59,91,1091,'2026-09-01');`);
 const part=source.slice(source.indexOf('async function queryUsers(context,input)')).split('\nfunction pageInput')[0];
 const sql=vm.runInNewContext(part.match(/Psql.query\(('SELECT CAST\(l\.[\s\S]*?'),\[companyId\]/)[1]);
 for(const co of ['a','b',null])assert.deepEqual(sorted(await query(sql,[co])),sorted(await query(baseline.memberLessons,[co])));
 const row=(await query(sql,['a'])).find(r=>r.uid==='s1');assert.equal(row.paid_count,1);assert.equal(row.trial_count,1);
});
test('vocabulary reads do not take schema locks after migration; missing legacy columns still recover',async()=>{
 const current=readFileSync(new URL('../deploy-admin-functions.mjs',import.meta.url),'utf8').match(/const operationsGatewayCode = String.raw`([\s\S]*?)\n`;/)[1];
 const present=['CommonModel','VocabularyWord','Node'].flatMap(table_name=>['_rperm','_wperm'].map(column_name=>({table_name,column_name})));
 for(const missing of [false,true]){
  const calls=[],c=vm.createContext({Psql:{query:async sql=>{calls.push(sql);return sql.startsWith('SELECT')?present.filter(r=>!missing||r.table_name!=='VocabularyWord'):[];}}});vm.runInContext(current,c);
  await Promise.all([c.ensureVocabularyPhysicalSchema(),c.ensureVocabularyPhysicalSchema()]);await c.ensureVocabularyPhysicalSchema();
  assert.equal(calls.filter(s=>s.startsWith('SELECT')).length,1);
  assert.equal(calls.filter(s=>s.startsWith('ALTER')).length,missing?1:0);
  if(missing)assert.match(calls[1],/ALTER TABLE "VocabularyWord"/);
 }
 let fail=true,attempts=0;const c=vm.createContext({Psql:{query:async()=>{attempts++;if(fail)throw Error('temporary schema read failure');return present;}}});vm.runInContext(current,c);
 await assert.rejects(c.ensureVocabularyPhysicalSchema(),/temporary schema/);fail=false;await c.ensureVocabularyPhysicalSchema();assert.equal(attempts,2);
});
test('dashboard estimate uses identical fresh lesson data but cannot authorize settlement; request flags cannot skip checks',async()=>{
 const gateway=readFileSync(new URL('../deploy-admin-functions.mjs',import.meta.url),'utf8').match(/const operationsGatewayCode = String.raw`([\s\S]*?)\n`;/)[1];
 let checks=0;const calls=[],c=vm.createContext({Date,Psql:{one:async sql=>{calls.push(sql);return{rows:[{objectId:'lesson',lessonObjectId:'lesson',standardLessonKey:'legacy:1',legacyGeneralId:1,jsmz:30,xymz:'20',teacherPaySnapshot:40,lessonAt:'2026-10-01T01:00:00Z'}],version:sql.includes('xs_payroll_source_version')?'authoritative':''};},query:async()=>[]}});
 vm.runInContext(gateway+'\n'+readFileSync(new URL('../cloud/payroll-integrity.js',import.meta.url),'utf8'),c);c.companyIdOf=()=> 'co';c.payrollSettlementSyncStatus=async()=>{checks++;return{settlementReady:true,state:'cutoff_verified'};};
 const manager={roles:['ops-manager'],current:{id:'admin'}};
 const normal=await c.payrollCanonicalSnapshot(manager,{forDashboard:true});assert.equal(checks,1);assert.equal(normal.version,'authoritative');
 const estimate=await c.payrollCanonicalSnapshot(manager,{}, {forDashboard:true});assert.equal(checks,1);assert.equal(estimate.version,'');assert.equal(estimate.sourceStatus.sync.settlementReady,false);assert.equal(estimate.sourceStatus.sync.state,'not_checked');assert.deepEqual(estimate.items,normal.items);
 const modes=[];c.payrollCanonicalSnapshot=async(_c,_i,options)=>{modes.push(options.forDashboard);return{items:[],sourceStatus:{},version:'v'};};c.payRateRows=c.teachingRuleRows=c.courseTeachingRows=async()=>[];c.payrollResult=()=>({});c.Psql.one=async()=>({value:false});
 await c.payrollPreviewData(manager,{forDashboard:true},'2026-10',true);
 await c.payrollPreviewData(manager,{},'2026-10',true,false,{forDashboard:true});
 await c.payrollPreviewData(manager,{},'2026-10',false,false,{forDashboard:true});
 await c.payrollPreviewData(manager,{},'2026-10',true,true,{forDashboard:true});
 assert.deepEqual(modes,[false,true,false,false]);
});