import { init, DuckDB } from '@ducklings/browser';

// Initialize the WASM module (runs in Web Worker)
await init();

// Create a database and connection
const db = new DuckDB();
const conn = await db.connect();

// Execute queries - returns array of JS objects
const rows = await conn.query('SELECT 42 as answer');
console.log(rows); // [{ answer: 42 }]

// Or get results as Arrow Table
const table = await conn.queryArrow('SELECT * FROM range(5)');
console.log(table.numRows); // 5

// Clean up
await conn.close();
await db.close();

import { init, DuckDB } from '@ducklings/workers';
import wasmModule from '@ducklings/workers/wasm';

export default {
  async fetch(request: Request): Promise<Response> {
    // Initialize with pre-compiled WASM module
    await init({ wasmModule });

    const db = new DuckDB();
    const conn = db.connect();

    // All queries are async
    const rows = await conn.query<{answer: number}>('SELECT 42 as answer');

    conn.close();
    db.close();

    return Response.json(rows);
  }
};

import { init, DuckDB, tableToIPC } from '@ducklings/workers';
import wasmModule from '@ducklings/workers/wasm';

export default {
  async fetch(request: Request): Promise<Response> {
    await init({ wasmModule });

    const db = new DuckDB();
    const conn = db.connect();

    // Get results as Arrow Table
    const table = await conn.queryArrow('SELECT * FROM range(1000) t(i)');

    // Serialize to Arrow IPC stream format
    const ipcBytes = tableToIPC(table, { format: 'stream' });

    conn.close();
    db.close();

    return new Response(ipcBytes, {
      headers: {
        'Content-Type': 'application/vnd.apache.arrow.stream',
      },
    });
  }
};

// JavaScript (Flechette)
import { tableFromIPC } from '@ducklings/browser';
const response = await fetch('https://your-worker.dev/arrow');
const bytes = new Uint8Array(await response.arrayBuffer());
const table = tableFromIPC(bytes);

// Browser - auto-locates WASM file
import { init } from '@ducklings/browser';
await init();

// Browser with Vite - use ?url imports for reliable asset resolution
import wasmUrl from '@ducklings/browser/wasm/duckdb.wasm?url';
import wasmJsUrl from '@ducklings/browser/wasm/duckdb.js?url';
import workerUrl from '@ducklings/browser/worker?url';
await init({ wasmUrl, wasmJsUrl, workerUrl });

// Browser - custom WASM URL
await init('/path/to/duckdb.wasm');

// Cloudflare Workers - use the workers package
import { init } from '@ducklings/workers';
import wasmModule from '@ducklings/workers/wasm';
await init({ wasmModule });

// Create in-memory database
const db = new DuckDB();

// Or create with automatic initialization (browser only)
const db = await DuckDB.create();

// Create a connection
const conn = db.connect();

// Close the database
db.close();

import { DuckDB, AccessMode } from '@ducklings/workers';

// Default configuration (secure defaults)
const db = new DuckDB();
// Equivalent to:
// new DuckDB({
//   accessMode: AccessMode.AUTOMATIC,
//   enableExternalAccess: true,
//   lockConfiguration: true,
// });

// Read-only mode - prevents any data modification
const readOnlyDb = new DuckDB({
  accessMode: AccessMode.READ_ONLY,
});

// Disable external access (disables httpfs)
const isolatedDb = new DuckDB({
  enableExternalAccess: false,
});

// Allow runtime config changes via SQL
const flexibleDb = new DuckDB({
  lockConfiguration: false,
});

// Custom configuration options
const customDb = new DuckDB({
  customConfig: {
    threads: '1',
  },
});

const conn = await db.connect();

// Query returning JS objects
const rows = await conn.query<{id: number, name: string}>('SELECT * FROM users');
// Returns: [{id: 1, name: 'Alice'}, {id: 2, name: 'Bob'}]

// Query returning Arrow Table (Flechette)
const table = await conn.queryArrow('SELECT * FROM users');
// Returns: Flechette Table with .numRows, .numCols, .schema, .toArray()

// Execute statement without returning results
await conn.execute('INSERT INTO users VALUES (1, "Alice")');

// Insert Arrow IPC data into a table
import { tableFromArrays, tableToIPC, utf8 } from '@ducklings/browser';
const arrow = tableFromArrays(
  { id: [1, 2], name: ['Alice', 'Bob'] },
  { types: { name: utf8() } }
);
await conn.insertArrowFromIPCStream('users', tableToIPC(arrow, { format: 'stream' }));

// Close the connection
await conn.close();

// Create a prepared statement
const stmt = await conn.prepare('SELECT * FROM users WHERE id = ? AND active = ?');

// Bind parameters (1-based index)
stmt.bindInt32(1, 42);
stmt.bindBoolean(2, true);

// Or use generic bind with type inference
stmt.bind(1, 42);        // number -> int32
stmt.bind(2, 'hello');   // string
stmt.bind(3, true);      // boolean
stmt.bind(4, null);      // null
stmt.bind(5, 123n);      // bigint -> int64

// Execute and get results
const results = await stmt.run();
console.log(results); // [{id: 42, name: 'Alice', active: true}]

// Clean up
await stmt.close();

// Manual transaction control
await conn.beginTransaction();
try {
  await conn.query('INSERT INTO accounts VALUES (1, 1000)');
  await conn.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
  await conn.commit();
} catch (e) {
  await conn.rollback();
  throw e;
}

// Or use the transaction wrapper (auto-rollback on error)
await conn.transaction(async () => {
  await conn.query('INSERT INTO orders VALUES (...)');
  await conn.query('UPDATE inventory SET qty = qty - 1');
  return 'success';
});

const stream = await conn.queryStreaming('SELECT * FROM large_table');

// Get metadata
console.log(stream.columnCount);  // Number of columns
console.log(stream.chunkCount);   // Number of chunks
console.log(stream.getColumns()); // Column info [{name, type}, ...]

// Iterate over chunks
for await (const chunk of stream) {
  console.log(`Chunk has ${chunk.rowCount} rows`);

  // Typed accessors
  for (let row = 0; row < chunk.rowCount; row++) {
    const id = chunk.getInt64(row, 0);
    const name = chunk.getString(row, 1);
    const active = chunk.getBoolean(row, 2);
    const isNull = chunk.isNull(row, 3);
  }

  // Or get entire column as array
  const ids = chunk.getColumn(0);
}
// Stream auto-closes after iteration

import { tableFromArrays, tableFromIPC, tableToIPC, utf8 } from '@ducklings/browser';

// Query as Arrow Table
const table = await conn.queryArrow('SELECT i, i*2 AS doubled FROM range(5) t(i)');
console.log(table.numRows);    // 5
console.log(table.numCols);    // 2
console.log(table.schema);     // Column metadata

// Build Arrow tables manually
const custom = tableFromArrays({
  id: [1, 2, 3],
  name: ['Alice', 'Bob', 'Charlie']
});

// Serialize to/from Arrow IPC
const bytes = tableToIPC(table);
const restored = tableFromIPC(bytes);

// Insert Arrow IPC data directly into a table
const data = tableFromArrays(
  { id: [1, 2, 3], label: ['a', 'b', 'c'] },
  { types: { label: utf8() } }  // Use plain utf8 (see note below)
);
const ipc = tableToIPC(data, { format: 'stream' });
await conn.insertArrowFromIPCStream('my_table', ipc);

// Query remote Parquet file
const rows = await conn.query(`
  SELECT *
  FROM 'https://example.com/data.parquet'
  LIMIT 10
`);

// Query remote CSV file
const csvData = await conn.query(`
  SELECT *
  FROM read_csv('https://example.com/data.csv')
`);

// Query remote JSON file
const jsonData = await conn.query(`
  SELECT *
  FROM read_json('https://example.com/data.json')
`);

// Create an S3 secret
await conn.execute(`
  CREATE SECRET my_s3 (
    TYPE S3,
    KEY_ID 'your-access-key-id',
    SECRET 'your-secret-access-key',
    REGION 'us-east-1'
  )
`);

// Query S3 files
const rows = await conn.query(`
  SELECT * FROM 's3://bucket-name/path/to/file.parquet'
`);

// Create a Cloudflare R2 secret
await conn.execute(`
  CREATE SECRET my_r2 (
    TYPE R2,
    KEY_ID 'your-r2-access-key-id',
    SECRET 'your-r2-secret-access-key',
    ACCOUNT_ID 'your-cloudflare-account-id'
  )
`);

// Query R2 files
const rows = await conn.query(`
  SELECT * FROM 'r2://bucket-name/path/to/file.parquet'
`);

// Create a Google Cloud Storage secret
await conn.execute(`
  CREATE SECRET my_gcs (
    TYPE GCS,
    KEY_ID 'your-gcs-key-id',
    SECRET 'your-gcs-secret'
  )
`);

// Query GCS files
const rows = await conn.query(`
  SELECT * FROM 'gcs://bucket-name/path/to/file.parquet'
`);

// Parse JSON strings
const parsed = await conn.query(`SELECT json('{"name": "Alice", "age": 30}')`);

// Extract values (returns JSON type with quotes)
const jsonValue = await conn.query(`
  SELECT json_extract('{"user": {"name": "Bob"}}', '$.user.name') AS name
`);
// Returns: [{name: "\"Bob\""}]

// Extract as string (returns raw value without quotes)
const stringValue = await conn.query(`
  SELECT json_extract_string('{"user": {"name": "Bob"}}', '$.user.name') AS name
`);
// Returns: [{name: "Bob"}]

// Using ->> operator (shorthand for json_extract_string)
const result = await conn.query(`
  SELECT '{"id": 1, "name": "Test"}'::JSON->>'$.name' AS name
`);
// Returns: [{name: "Test"}]

// Get JSON keys
const keys = await conn.query(`SELECT json_keys('{"a": 1, "b": 2, "c": 3}')`);

// Read JSON files (local or remote with httpfs)
const data = await conn.query(`SELECT * FROM read_json('data.json')`);

// Convert to JSON
const jsonOut = await conn.query(`
  SELECT to_json({name: 'test', values: [1, 2, 3]})
`);

const stream = await conn.queryStreaming(`
  SELECT json('{"key": "value"}') AS data
`);

for await (const chunk of stream) {
  // Automatically parses JSON string to object
  const obj = chunk.getJSON<{key: string}>(0, 0);
  console.log(obj.key); // "value"
}
// Stream auto-closes after iteration