import { getDatabase, Database } from '@numbereight/udatabase';
import { DateTime } from 'luxon';

const database: Database<'postgres' | 'snowflake'> = getDatabase(connectionInfo);

const yesterday = DateTime.now().minus({ days: 1 }).toISOString();

await database.run<{ timestamp: string }>(
    // This is a query that will run regardless of database type
    //  if it could only run in postgres I would write 'postgres' here
    //  attempting to run a query on a database that does not match the type results in an error 
    database.type,

    // Query names appear in log messages and can be used to trace errors
    'exampleQueryOne',
  
    // The query itself.
    // This can be a string if no binding is required, or it can be a function
    //  that takes a Query instance and returns a string.
    // Binding values inline will result in the correct binding sanitisation.
    q => `
        SELECT
            timestamp
        FROM timestamps
        WHERE timestamp > ${q.value(yesterday)}::timestamp
    `
);

const deductFrom = 'Mr. Bean';
const transactionAmount = 100000;
const creditTo = 'Mr. Blobby';
const newBalance = await database.transaction<number>('my accounting transaction', async (transaction) => {
    const [{ reference: transactionReference }] = await transaction.run<{ reference }>('createTransaction', q => `
        INSERT INTO transactions
          (type, description)
        VALUES ('transfer', ${q.value(`${transactionAmount} from ${deductFrom} to ${creditTo}`)})
        RETURNING reference
    `);
    await transaction.run('debit', q => `
        INSERT INTO balances
            (owner, amount, reference)
        VALUES (${q.value(deductFrom)}, ${q.value(transactionAmount)}, ${q.value(transactionReference)})
    `);
    await transaction.run('credit', q => `
        INSERT INTO balances
            (owner, amount, reference)
        VALUES (${q.value(deductFrom)}, ${q.value(transactionAmount)}, ${q.value(transactionReference)})
    `);
    const [{ balance }] = await transaction.run<{ balance: number }>('balanceCheck', q => `
        SELECT
            SUM(amount) AS balance
        FROM balances
        WHERE owner=${q.value(creditTo)}
    `);
    return balance;
});