import {PGlite} from '@electric-sql/pglite';
import fs from 'node:fs/promises';
import path from 'node:path';
import assert from 'node:assert/strict';
import {fileURLToPath} from 'node:url';
const root=path.resolve(path.dirname(fileURLToPath(import.meta.url)),'..');
const expected=JSON.parse(await fs.readFile(path.join(root,'validation/expected.json'),'utf8'));
const db=new PGlite();let passed=0;const results=[];
const eq=(name,a,b)=>{assert.deepEqual(a,b,name);passed++;results.push({name,status:'pass'});};
const query=async sql=>(await db.query(sql)).rows;
try {
await db.exec(await fs.readFile(path.join(root,'sql/00_setup.sql'),'utf8'));
const version=(await query('SELECT version() AS version'))[0].version;
for(const kind of ['examples','solutions']) {
const files=(await fs.readdir(path.join(root,'sql',kind))).filter(f=>f.endsWith('.sql')).sort();
for(const f of files){
const key=`${kind}/${f}`;const sql=await fs.readFile(path.join(root,'sql',key),'utf8');
await db.exec("BEGIN READ ONLY; SET LOCAL statement_timeout = '5s';");
const rows=await query(sql);await db.exec('ROLLBACK;');
if(f==='12_explain.sql')eq(key+' yields a plan',rows.length>0,true);
else eq(key,rows,expected[key]);
}
}
// Independent invariants deliberately not read from stored query snapshots.
eq('14 orders',Number((await query('SELECT COUNT(*) AS n FROM aabha_sql.orders'))[0].n),14);
eq('19 order lines',Number((await query('SELECT COUNT(*) AS n FROM aabha_sql.order_items'))[0].n),19);
eq('11 completed orders',Number((await query("SELECT COUNT(*) AS n FROM aabha_sql.orders WHERE status='completed'"))[0].n),11);
const sum=(await query("SELECT SUM(i.quantity*i.unit_price-i.line_discount) AS n FROM aabha_sql.orders o JOIN aabha_sql.order_items i ON i.order_id=o.order_id WHERE o.status='completed'"))[0];
eq('Hand computed line sales 5860',Number(sum.n),5860);
eq('Hand computed refunds 215',Number((await query('SELECT SUM(amount) AS n FROM aabha_sql.refunds'))[0].n),215);
const monthly=expected['examples/13_monthly.sql'];
eq('Hand computed monthly sales',monthly.map(r=>Number(r.net_sales)),[1620,1575,2450]);
eq('Hand computed monthly order counts',monthly.map(r=>Number(r.completed_orders)),[3,3,5]);
eq('Monthly reconciliation',monthly.reduce((a,r)=>a+Number(r.net_sales),0),5645);
eq('Left join retains all 8 customers',expected['examples/06_left.sql'].length,8);
eq('Zero completed order customers',expected['examples/06_left.sql'].filter(r=>Number(r.completed_orders)===0).map(r=>r.customer_id),[7,8]);
eq('Only customer 7 has no order',expected['solutions/06b.sql'].map(r=>r.customer_id),[7]);
eq('Cutoff population is 6 customers',expected['examples/11_features.sql'].map(r=>r.customer_id),[1,2,3,4,5,6]);
eq('No pre-cutoff sales keeps null recency',expected['examples/11_features.sql'].find(r=>r.customer_id===5).recency_days,null);
eq('February boundary includes Feb 28',expected['examples/03_filter.sql'].map(r=>r.order_id),[1005,1006,1008]);
eq('Latest valid raw rows are 2 and 8',expected['solutions/10b.sql'].map(r=>r.batch_row),[2,8]);
eq('Invalid calendar date fails validation',(await query("SELECT pg_input_is_valid('2025-02-30','date') AS ok"))[0].ok,false);
eq('NULL equality remains unknown',(await query('SELECT NULL::text = NULL::text AS result'))[0].result,null);
eq('NOT IN with NULL selects no rows',(await query('SELECT 1 AS x WHERE 1 NOT IN (2,NULL)')).length,0);
eq('Duplicate monetary values are not duplicate events',(await query('SELECT SUM(v)::integer AS total,SUM(DISTINCT v)::integer AS wrong_total FROM (VALUES(100),(100)) AS t(v)')),[{total:200,wrong_total:100}]);
eq('Tie rank and deterministic row number',(await query('SELECT id,DENSE_RANK() OVER(ORDER BY score DESC)::integer AS rank,ROW_NUMBER() OVER(ORDER BY score DESC,id)::integer AS position FROM (VALUES (1,10),(2,10),(3,5)) AS t(id,score) ORDER BY position')),[{id:1,rank:1,position:1},{id:2,rank:1,position:2},{id:3,rank:2,position:3}]);
// Adversarial records expose errors that unchanged baseline totals cannot detect.
await db.exec(`BEGIN;
INSERT INTO aabha_sql.orders VALUES
(1015,1,'2025-04-01','completed','web'),
(1016,1,'2025-03-25','completed','web');
INSERT INTO aabha_sql.order_items VALUES
(1015,1,101,1,120,0),(1016,1,102,1,40,0);
INSERT INTO aabha_sql.refunds VALUES
(5,1002,'2025-04-01',30),(6,1004,'2025-03-20',10);
`);
const stressed=await query(await fs.readFile(path.join(root,'sql/examples/13_monthly.sql'),'utf8'));
eq('April 1 order is outside the quarter',stressed.map(r=>r.month),['2025-01-01','2025-02-01','2025-03-01']);
eq('Late refund excluded and March refund attributed to original January order',stressed.map(r=>Number(r.net_sales)),[1610,1575,2490]);
eq('Repeat March buyer adds an order',stressed.map(r=>Number(r.completed_orders)),[3,3,6]);
eq('Repeat March buyer does not add a unique buyer',stressed.map(r=>Number(r.buying_customers)),[3,3,5]);
eq('Stress averages use changed order denominators',stressed.map(r=>Number(r.net_average_order_value)),[536.67,525,415]);
await db.exec('ROLLBACK;');
eq('Rolled back stress data restores 14 orders',Number((await query('SELECT COUNT(*) AS n FROM aabha_sql.orders'))[0].n),14);
await db.exec(`BEGIN;
INSERT INTO aabha_sql.customers VALUES
(9,'Isha','Pune','2025-02-28'),(10,'Jai','Pune','2025-03-01');
INSERT INTO aabha_sql.raw_orders VALUES
(9,9001,1,'2025-03-01','completed',321,'2025-04-01 10:00');`);
const featureStress=await query(await fs.readFile(path.join(root,'sql/examples/11_features.sql'),'utf8'));
eq('Eligible customer without orders remains in features',featureStress.find(r=>r.customer_id===9),{customer_id:9,completed_orders:0,sales_before_refunds:'0',recency_days:null});
eq('Customer registered on cutoff remains excluded',featureStress.some(r=>r.customer_id===10),false);
const dedupeStress=await query(await fs.readFile(path.join(root,'sql/solutions/10b.sql'),'utf8'));
eq('Tied raw load timestamp uses descending batch row',dedupeStress.find(r=>r.source_order_id===9001).batch_row,9);
await db.exec('ROLLBACK;');
// Execute expected failures inside transactions; always rollback.
for(const [name,sql,code] of [
['Primary key rejects duplicate order',"INSERT INTO aabha_sql.orders VALUES(1001,1,'2025-01-01','completed','web')",'23505'],
['Foreign key rejects missing customer',"INSERT INTO aabha_sql.orders VALUES(9999,99,'2025-01-01','completed','web')",'23503'],
['Check constraint rejects zero quantity',"INSERT INTO aabha_sql.order_items VALUES(1001,3,101,0,120,0)",'23514']]) {
await db.exec('BEGIN;');let seen;
try{await db.exec(sql);}catch(e){seen=e.code;}finally{await db.exec('ROLLBACK;');}
eq(name,seen,code);
}
await db.exec('BEGIN READ ONLY;');let readonlyCode;
try{await db.exec("UPDATE aabha_sql.customers SET city='Test' WHERE customer_id=1;");}catch(e){readonlyCode=e.code;}finally{await db.exec('ROLLBACK;');}
eq('Read-only transaction blocks persistent table update',readonlyCode,'25006');
eq('Safety check left original city unchanged',(await query('SELECT city FROM aabha_sql.customers WHERE customer_id=1'))[0].city,'Pune');
const report={tested_at:new Date().toISOString(),engine:version,passed,failed:0,scope:'PGlite PostgreSQL WASM; not a native server or browser installation test',results};
await fs.writeFile(path.join(root,'validation/latest-run.json'),JSON.stringify(report,null,2)+'\n');
console.log(`${passed} checks passed. ${version}`);
} finally {await db.close();}
