import { after, before, test } 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 db = new PGlite();
const source = readFileSync(new URL('../deploy-admin-functions.mjs', import.meta.url), 'utf8');
const gateway = {};
vm.createContext(gateway);
vm.runInContext(source.match(/const operationsGatewayCode = String.raw`([\s\S]*?)\n`;/)[1], gateway);
gateway.companyIdOf = () => 'co';
before(async () => {
  await db.exec(`
    CREATE TABLE "DailyStudyRecord"("objectId" text,"company" text,"id" int,"userId" int,"pl" text,"dsid" text,"dqrq" text,"xxqs" text,"createdAt" timestamptz);
    CREATE TABLE "CommonModel"("objectId" text,"company" text,"modelId" int,"itemId" int,"generalId" int,"createTime" timestamptz,"createdAt" timestamptz);
    CREATE TABLE "_User"("objectId" text,"company" text,"legacyUserId" int,"legacyUserData" jsonb,"realName" text,"nickname" text,"username" text,"mobile" text,"identityType" text,"isDeleted" boolean);
    CREATE TABLE "CourseAppointment"("objectId" text,"company" text,"id" int,"kcid" text,"dszt" text,"courseCategoryKey" text,"courseCategoryName" text,szyh text);
    CREATE TABLE "Node"("company" text,"nodeId" int,"nodeName" text);
    INSERT INTO "DailyStudyRecord" VALUES
      ('compact','co',1,20,'30','','20261004','[]','2026-10-04T00:00:00Z'),
      ('dashed','co',2,20,'30','','2026-10-04','[]','2026-10-04T00:00:00Z'),
      ('slash','co',3,20,'30','','2026/10/03','[]','2026-10-03T00:00:00Z'),
      ('fallback','co',4,20,'30','',NULL,'[]','2026-09-30T16:01:00Z'),
      ('history','co',5,20,'30','','20260930','[]','2026-09-30T00:00:00Z'),
      ('foreign','other',6,20,'30','','20261004','[]','2026-10-04T00:00:00Z');
  `);
});
after(() => db.close());
async function rows(filters) {
  const query = gateway.learningReportQueryParts({}, { page: 1, pageSize: 20, filters });
  return (await db.query('SELECT d."objectId"' + query.from + query.where + ' ORDER BY ' + query.studyDateSql + ' DESC,d."objectId"', query.values)).rows.map(row => row.objectId);
}
test('月份筛选兼容三种日期格式，并按北京时间补齐缺失学习日期', async () => {
  assert.deepEqual(await rows({ month: '2026-10' }), ['compact','dashed','slash','fallback']);
});
test('全部月份包含历史记录，保持帐套隔离', async () => {
  assert.deepEqual(await rows({ month: '' }), ['compact','dashed','slash','fallback','history']);
});
test('日期范围包含首尾当天，兼容不同保存格式', async () => {
  assert.deepEqual(await rows({ dateFrom: '2026-10-03', dateTo: '2026-10-04' }), ['compact','dashed','slash']);
  assert.deepEqual(await rows({ month: '2026-09' }), ['history']);
});

test('报表展示与筛选使用同一个学习日期，斜线日期与 UTC 时间不会少一天', () => {
  for(const raw of ['20261004','2026-10-04','2026/10/04'])assert.equal(gateway.learningReportProjection({dqrq:raw}).studyDate,'2026-10-04');
  assert.equal(gateway.learningReportProjection({recordCreatedAt:'2026-09-30T16:01:00Z'}).studyDate,'2026-10-01');
});

test('门店报表按当前会员归属过滤后再计数，无法通过过滤器读取其他门店',async()=>{
 await db.exec(`INSERT INTO "_User" ("objectId",company,"legacyUserId","legacyUserData","identityType") VALUES ('own','co',20,'{"ParentUserID":57}','member'),('other','co',21,'{"ParentUserID":73}','member');
 INSERT INTO "DailyStudyRecord" ("objectId",company,"userId",dqrq) VALUES ('other-store','co',21,'20261004');`);
 const query=gateway.learningReportQueryParts({storeId:57},{page:1,pageSize:20,filters:{month:'2026-10',storeId:73}});
 const count=(await db.query('SELECT COUNT(*)::int total'+query.from+query.where,query.values)).rows[0].total;
 assert.equal(count,4);
 const foreign=gateway.learningReportQueryParts({storeId:73},{page:1,pageSize:20,filters:{month:'2026-10'}});
 assert.deepEqual((await db.query('SELECT d."objectId"'+foreign.from+foreign.where,foreign.values)).rows.map(r=>r.objectId),['other-store']);
});

test('无教材的语法写作课按预约分类显示并支持课程名称搜索',async()=>{
 await db.exec(`INSERT INTO "CourseAppointment" VALUES
   ('writing','co',201,'0','30','high_writing','高中语法写作课','59'),
   ('word','co',202,'13','30','word','单词课','59'),
   ('legacy-writing','co',203,'398','30','high_writing','高中语法写作课','59'),
   ('legacy-unclassified','co',204,'398','30',NULL,NULL,'59'),
   ('unrelated-appointment','co',205,'398','30',NULL,NULL,'60');
  INSERT INTO "CommonModel" VALUES
   ('writing-main','co',54,201,201,NULL,NULL),
   ('word-main','co',54,202,202,NULL,NULL),
   ('legacy-writing-main','co',54,203,203,NULL,NULL),
   ('legacy-unclassified-main','co',54,204,204,NULL,NULL),
   ('unrelated-main','co',54,205,205,NULL,NULL);
  INSERT INTO "Node" VALUES ('co',13,'人教版词表'),('co',398,'阅读写作');
  INSERT INTO "DailyStudyRecord" VALUES
   ('writing-report','co',21,59,'88','201','20261111','[]',NULL),
   ('word-report','co',22,59,'88','202','20261111','[]',NULL),
   ('legacy-writing-report','co',23,59,'88','203','20261111','[]',NULL),
   ('legacy-unclassified-report','co',24,59,'88','204','20261111','[]',NULL),
   ('unrelated-report','co',25,59,'88','205','20261111','[]',NULL);`);
 const q=gateway.learningReportQueryParts({}, {filters:{dateFrom:'2026-11-11',dateTo:'2026-11-11',studentId:59}});
 const sql='SELECT d."objectId",'+gateway.learningReportCourseTitleSql()+' AS title'+q.from+q.where+' ORDER BY d.id';
 assert.deepEqual((await db.query(sql,q.values)).rows.map(row=>row.title),['高中语法写作课','人教版词表','阅读写作','阅读写作','阅读写作']);
 assert.deepEqual(await rows({courseSearch:'高中语法写作课'}),['writing-report']);
 assert.deepEqual(await rows({courseSearch:'人教版'}),['word-report']);
});

test('写作课不进入单词明细缺失或单词报表筛选', async () => {
  const dates = { dateFrom: '2026-11-11', dateTo: '2026-11-11' };
  assert.deepEqual(await rows({ ...dates, status: 'writing' }), [
    'legacy-unclassified-report', 'legacy-writing-report', 'writing-report',
  ]);
  assert.deepEqual(await rows({ ...dates, status: 'missing' }), ['unrelated-report', 'word-report']);
  assert.deepEqual(await rows({ ...dates, status: 'available' }), []);
});

test('写作报表的词汇掌握率不适用，普通单词报表仍计算掌握率', () => {
  const wordData = { xxqs: '[{"Title":"apple","check":1}]', appointmentObjectId: 'word', appointmentStatus: '30' };
  const writing = gateway.learningReportProjection({ ...wordData, isWriting: true });
  assert.equal(writing.status, 'writing');
  assert.equal(writing.masteryRate, null);
  assert.equal(writing.canExport, false);
  assert.equal(writing.learnedCount, 0);
  const word = gateway.learningReportProjection(wordData);
  assert.equal(word.status, 'available');
  assert.equal(word.masteryRate, 100);
  assert.equal(word.canExport, true);
});

test('单词学习报表统一课型格式，优先显示保存时长并兼容旧课型', () => {
  for(const minutes of [30,60]){
    const report=gateway.learningReportProjection({classType:'2',courseId:203,durationMinutes:minutes,courseCategoryKey:'word',courseCategoryName:'单词课'});
    assert.equal(report.classTypeLabel,'单词课 '+minutes+' 分钟');
  }
  assert.equal(gateway.learningReportProjection({legacyLesson:{classType:'1',courseId:'203'}}).classTypeLabel,'单词课 30 分钟');
  assert.equal(gateway.learningReportProjection({legacyLesson:{classType:'2',courseId:'161'}}).classTypeLabel,'单词课 60 分钟');
  assert.equal(gateway.learningReportProjection({classType:'1',courseId:203}).classTypeLabel,'单词课 30 分钟');
  assert.equal(gateway.learningReportProjection({classType:'2',courseId:203}).classTypeLabel,'单词课 60 分钟');
  assert.equal(gateway.learningReportProjection({classType:'2',courseId:398,isWriting:true}).classTypeLabel,'语法写作课 60 分钟');
});

test('写作报表使用确认分类与实际时长，未知学段不按当前学生档案推测',()=>{
 assert.equal(gateway.learningReportProjection({classType:'2',courseId:398,isWriting:true,courseCategoryKey:'high_writing',courseCategoryName:'高中语法写作课'}).classTypeLabel,'高中语法写作课 60 分钟');
 assert.equal(gateway.learningReportProjection({classType:'2',courseId:398,isWriting:true,durationMinutes:30,courseCategoryKey:'middle_writing'}).classTypeLabel,'中学语法写作课 30 分钟');
 assert.equal(gateway.learningReportProjection({classType:'2',courseId:398,isWriting:false,courseCategoryKey:'word',courseCategoryName:'单词课'}).classTypeLabel,'单词课 60 分钟');
});

test('批量读取已保存时长和旧课型时同时匹配帐套、预约与学员', async () => {
  await db.exec(`
    CREATE TABLE "LessonCompletionReport"(company text,"appointmentGeneralId" numeric,"studentId" numeric,"durationMinutes" numeric,"submittedAt" timestamptz,"objectId" text);
    CREATE TABLE "LessonRecord"(company text,yyds text,xymz text,kclx text,kcid text,"updatedAt" timestamptz,"objectId" text);
    INSERT INTO "LessonCompletionReport" VALUES
      ('co',201,59,30,'2026-11-01','saved'),
      ('other',201,59,60,'2026-11-02','other-company'),
      ('co',201,60,60,'2026-11-02','other-student');
    INSERT INTO "LessonRecord" VALUES
      ('co','202','59','1','203','2026-11-01','legacy'),
      ('other','202','59','2','203','2026-11-02','other-company'),
      ('co','202','60','2','203','2026-11-02','other-student');
  `);
  gateway.Psql = { query: async (sql, values) => (await db.query(sql, values)).rows };
  const enriched = await gateway.learningReportClassTypeRows([
    { recordCompany: 'co', dsid: '201', userId: 59, appointmentObjectId: 'a', classType: '2', courseId: 203, durationMinutes: 60 },
    { recordCompany: 'co', dsid: '202', userId: 59 },
  ]);
  assert.equal(gateway.learningReportProjection(enriched[0]).classTypeLabel, '单词课 30 分钟');
  assert.equal(gateway.learningReportProjection(enriched[1]).classTypeLabel, '单词课 30 分钟');
});

test('报表先定位课次时仍兼容报表对象、通用编号和原学习记录编号', async () => {
  await db.exec(`
    INSERT INTO "CommonModel" VALUES ('study-target','co',56,21,3021,NULL,NULL),('foreign-target','other',56,21,4021,NULL,NULL);
    INSERT INTO "DailyStudyRecord" ("objectId",company,id) VALUES ('foreign-target-record','other',21);
  `);
  let lookup = '';
  gateway.Psql = { query: async sql => { lookup = sql; return []; } };
  await assert.rejects(gateway.learningReportDetail({ isSuperAdmin: true }, { targetId: 'study-target' }), /学习报表不存在/);
  const end = lookup.indexOf(' SELECT c."objectId" AS "targetId"');
  assert.ok(end > 0);
  const targetSql = lookup.slice(0, end) + ' SELECT * FROM target_records';
  for (const target of ['study-target', '3021', 'writing-report']) {
    assert.deepEqual((await db.query(targetSql, ['co', target])).rows.map(row => row.objectId), ['writing-report']);
  }
  assert.deepEqual((await db.query(targetSql, ['co', 'foreign-target'])).rows, []);
});

test('已逐条核实的缺失预约快照补齐课型，同时核验学员和预约身份', () => {
  const reportMetadata = {
    verified: true, studentId: 1790, appointmentId: '802757', classType: '2',
    durationMinutes: 60, courseId: 398, courseName: '阅读写作', categoryName: '语法写作课',
  };
  const row = { userId: 1790, dsid: '802757', reportMetadata, isWriting: true };
  const writing = gateway.learningReportProjection(row);
  assert.equal(writing.classTypeLabel, '语法写作课 60 分钟');
  assert.equal(writing.courseName, '阅读写作');
  assert.equal(writing.masteryRate, null);
  assert.equal(gateway.learningReportProjection({ ...row, userId: 1828 }).classTypeLabel, '未设置');
  assert.equal(gateway.learningReportProjection({ ...row, dsid: 'other' }).classTypeLabel, '未设置');
  const trial = gateway.learningReportProjection({
    userId: 1828, dsid: '817637',
    reportMetadata: { ...reportMetadata, studentId: 1828, appointmentId: '817637', classType: '3', courseId: 405, categoryName: '体验课' },
  });
  assert.equal(trial.classTypeLabel, '体验课 60 分钟');
});

test('已核实的原预约写作课快照参与写作筛选，并拒绝身份不符的快照', async () => {
  await db.exec('ALTER TABLE "DailyStudyRecord" ADD COLUMN "reportMetadata" jsonb');
  const metadata = JSON.stringify({ verified: true, studentId: 1790, appointmentId: '802757', courseId: 398 });
  await db.query('INSERT INTO "DailyStudyRecord" ("objectId",company,id,"userId",dsid,dqrq,xxqs,"reportMetadata") VALUES ($1,$2,19999,1790,$3,$4,$5,$6)', ['resolved-writing','co','802757','20260814','[]',metadata]);
  await db.query('INSERT INTO "DailyStudyRecord" ("objectId",company,id,"userId",dsid,dqrq,xxqs,"reportMetadata") VALUES ($1,$2,20000,1800,$3,$4,$5,$6)', ['mismatched-writing','co','802757','20260814','[]',metadata]);
  assert.deepEqual(await rows({ status: 'writing', studentId: 1790 }), ['resolved-writing']);
  assert.deepEqual(await rows({ status: 'writing', studentId: 1800 }), []);
});