import {PGlite} from '@electric-sql/pglite';
export const setup=`
CREATE TABLE orders(id int PRIMARY KEY, customer_id int NOT NULL, amount_cents int NOT NULL, ordered_at timestamptz NOT NULL);
CREATE TABLE lines(id int PRIMARY KEY, order_id int REFERENCES orders, sku text NOT NULL);
INSERT INTO orders VALUES
(1,101,12000,'2026-11-01 04:30Z'),(2,102,8000,'2026-11-01 06:30Z'),
(3,101,6000,'2026-11-02 04:30Z'),(4,103,4000,'2026-11-01 02:00Z');
INSERT INTO lines VALUES(1,1,'chair'),(2,1,'cushion'),(3,2,'lamp'),(4,3,'desk'),(5,4,'mat');
CREATE TABLE exposures(event_id int PRIMARY KEY, user_id int NOT NULL, variant text NOT NULL, happened_at timestamptz NOT NULL, received_at timestamptz NOT NULL);
INSERT INTO exposures VALUES
(1,101,'A','2026-10-31 12:00Z','2026-10-31 12:00Z'),
(2,101,'A','2026-10-31 13:00Z','2026-10-31 13:00Z'),
(3,102,'B','2026-10-31 12:00Z','2026-10-31 12:00Z'),
(4,103,'B','2026-10-31 12:00Z','2026-11-03 12:00Z');`;
const queries={
 fanout:{
  broken:`SELECT COUNT(*) AS joined_rows, SUM(o.amount_cents)::int AS revenue_cents FROM orders o JOIN lines l ON l.order_id=o.id`,
  safe:`WITH line_counts AS (SELECT order_id, COUNT(*) AS items FROM lines GROUP BY order_id)
SELECT COUNT(*) AS order_rows, SUM(o.amount_cents)::int AS revenue_cents FROM orders o JOIN line_counts l ON l.order_id=o.id`,
  trace:['Name the grain: revenue belongs to one order, not one order line.','Order 1 has two lines. A direct join repeats its 12000 cents.','Preaggregate the many side, then join one row per order. SUM(DISTINCT amount) would incorrectly merge different orders with the same amount.'],
 },
 cohort:{
  broken:`SELECT e.variant, COUNT(*) AS exposed_rows, COUNT(o.id) AS conversions,
COALESCE(SUM(o.amount_cents),0)::int AS revenue_cents FROM exposures e
LEFT JOIN orders o ON o.customer_id=e.user_id
WHERE e.received_at < '2026-11-02 12:00Z' GROUP BY e.variant ORDER BY e.variant`,
  safe:`WITH first_exposure AS (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY happened_at,event_id) AS n
 FROM exposures WHERE happened_at < '2026-11-02 12:00Z' AND received_at < '2026-11-04 00:00Z'
), per_user AS (
 SELECT e.user_id,e.variant,COUNT(o.id)>0 AS converted,COALESCE(SUM(o.amount_cents),0)::int AS revenue_cents
 FROM first_exposure e LEFT JOIN orders o ON o.customer_id=e.user_id
 AND o.ordered_at>=e.happened_at AND o.ordered_at<e.happened_at+INTERVAL '7 days'
 WHERE e.n=1 GROUP BY e.user_id,e.variant
)
SELECT variant,COUNT(*) AS exposed_users,COUNT(*) FILTER(WHERE converted) AS converted_users,
SUM(revenue_cents)::int AS revenue_cents FROM per_user GROUP BY variant ORDER BY variant`,
  trace:['Exposure events and exposed people are different denominators.','Choose the first exposure with a stable tie-breaker; aggregate orders to one person before counting conversion.','Event time defines eligibility; a separate receipt cutoff makes late-arrival policy reproducible. This fixture intentionally uses different cutoffs to expose the policy difference.','Seven-day attribution here is a teaching definition, not a causal estimate or a statistical-significance test.'],
 },
 timezone:{
  broken:`SELECT COUNT(*) AS orders, SUM(amount_cents)::int AS revenue_cents FROM orders
WHERE ordered_at >= '2026-11-01 00:00Z' AND ordered_at < '2026-11-02 00:00Z'`,
  safe:`SELECT COUNT(*) AS orders, SUM(amount_cents)::int AS revenue_cents FROM orders
WHERE ordered_at >= (TIMESTAMP '2026-11-01 00:00' AT TIME ZONE 'America/New_York')
AND ordered_at < (TIMESTAMP '2026-11-02 00:00' AT TIME ZONE 'America/New_York')`,
  trace:['November 1, 2026 is a 25-hour local day in New York because the clock moves back.','Build both midnight boundaries in the reporting time zone; do not add a fixed 24 hours.','Use a half-open interval: the next midnight belongs to the next day. UTC timestamps store instants; the report supplies the business calendar.'],
 }
};
export async function runSQL(config,options={}){
 if(!Object.hasOwn(queries,config.preset))throw new Error('Choose a listed reporting scenario.');
 const db=new PGlite(options);try{
  await db.exec(setup);const q=queries[config.preset],sql=q[config.variant==='safe'?'safe':'broken'];
  const result=await db.query(sql);const plan=await db.query('EXPLAIN '+sql);
  const raw=await db.query(config.preset==='cohort'?'SELECT * FROM exposures ORDER BY event_id':'SELECT * FROM orders ORDER BY id');
  const rows=result.rows;const revenue=rows.reduce((n,r)=>n+Number(r.revenue_cents),0);
  return {title:config.variant==='safe'?'Count the intended records':'A plausible result with a different meaning',summary:config.preset==='cohort'?'Compare unique exposed people, converted people, and the receipt cutoff before interpreting the result.':'The result comes from actual PostgreSQL queries in a fresh in-memory database.',metrics:[['Revenue (cents)',revenue],['Result groups',rows.length]],trace:q.trace,tables:[{title:'Actual query result',columns:result.fields.map(f=>f.name),rows:rows.map(r=>result.fields.map(f=>r[f.name]))},{title:'Invented source records',columns:raw.fields.map(f=>f.name),rows:raw.rows.map(r=>raw.fields.map(f=>r[f.name]))},{title:'Actual PostgreSQL plan (tiny fixture)',columns:['Plan step'],rows:plan.rows.map(r=>[r['QUERY PLAN']])}],code:sql};
 }finally{await db.close();}
}
