import { SQL } from 'bun';

// Must run with a non-UTC timezone to expose the bug:
//   TZ=Asia/Bangkok bun run repro.ts
//
// Start databases:
//   docker run --rm -p 3307:3306 -e MYSQL_ROOT_PASSWORD=test -e MYSQL_DATABASE=test mysql:8
//   docker run --rm -p 5433:5432 -e POSTGRES_PASSWORD=test postgres:17-alpine

const tz = Intl.DateTimeFormat().resolvedOptions().timeZone;
console.log(`Process timezone: ${tz}`);

// On a UTC+7 machine this Date has UTC epoch 2024-01-15T05:30:45.678Z
const d = new Date(2024, 0, 15, 12, 30, 45, 678);
console.log(`Sent UTC epoch:   ${d.toISOString()}`);

// ── MySQL 8 ──────────────────────────────────────────────────────────────

const mysql = new SQL('mysql://root:test@localhost:3307/test', {
  max: 1, ssl: false,
});

await mysql.unsafe('DROP TABLE IF EXISTS ts_test');
await mysql.unsafe(`
  CREATE TABLE ts_test (
    id   INT PRIMARY KEY,
    ts   DATETIME(3),
    tstz TIMESTAMP(3)
  )
`);

await mysql`INSERT INTO ts_test (id, ts, tstz) VALUES (${1}, ${d}, ${d})`;

const [row] = await mysql.unsafe(
  'SELECT ts, tstz FROM ts_test WHERE id = 1',
) as any[];

console.log('\nMySQL 8');
console.log(`  DATETIME  returned: ${(row.ts   as Date).toISOString()}`);
console.log(`  TIMESTAMP returned: ${(row.tstz as Date).toISOString()}`);
// Expected both: 2024-01-15T05:30:45.678Z
// Actual on UTC+7: 2024-01-14T22:30:45.678Z  (-7h)

await mysql.unsafe('DROP TABLE IF EXISTS ts_test');
mysql.close();

// ── PostgreSQL 17 ────────────────────────────────────────────────────────

const pg = new SQL('postgres://postgres:test@localhost:5433/postgres', {
  max: 1, ssl: false,
});

await pg.unsafe('DROP TABLE IF EXISTS ts_test');
await pg.unsafe(`
  CREATE TABLE ts_test (
    id   INT PRIMARY KEY,
    ts   TIMESTAMP(3),           -- without time zone (affected)
    tstz TIMESTAMPTZ(3)          -- with time zone    (correct)
  )
`);

await pg`INSERT INTO ts_test (id, ts, tstz) VALUES (${1}, ${d}, ${d})`;

const [pgRow] = await pg.unsafe(
  'SELECT ts, tstz FROM ts_test WHERE id = 1',
) as any[];

console.log('\nPostgreSQL 17 (prepared statements)');
console.log(`  TIMESTAMP   returned: ${(pgRow.ts   as Date).toISOString()}`);
console.log(`  TIMESTAMPTZ returned: ${(pgRow.tstz as Date).toISOString()}`);
// Expected both: 2024-01-15T05:30:45.678Z
// TIMESTAMP on UTC+7:   2024-01-14T22:30:45.678Z  (-7h)  ← bug
// TIMESTAMPTZ on UTC+7: 2024-01-15T05:30:45.678Z  (0h)   ← correct

await pg.unsafe('DROP TABLE IF EXISTS ts_test');
pg.close();