const attachment = await client.createDatabase(`statement-cache-test.fdb`);
const transaction = await attachment.startTransaction();
try {
    await attachment.execute(transaction, `
        create table person (
            id integer primary key,
            name varchar(60) not null
        )`);

    await attachment.execute(transaction, `
        create table item (
            id integer primary key,
            description varchar(60) not null
        )`);

    await attachment.execute(transaction, `
        create table sale (
            id integer primary key,
            person_id integer not null references person,
            sale_date date not null
        )`);

    await attachment.execute(transaction, `
        create table sale_item (
            id integer primary key,
            sale_id integer not null references sale,
            item_id integer not null references item,
            val numeric(10,2)
        )`);

    await transaction.commitRetaining();

    for (let i = 0; i < 500; ++i) {
        await attachment.execute(transaction,
            `insert into person (id, name) values (?, ?)`,
            [i, `${i}`]
        );

        await attachment.execute(transaction,
            `insert into item (id, description) values (?, ?)`,
            [i, `${i}`]
        );
    }

    for (let i = 0; i < 500; ++i) {
        await attachment.execute(transaction,
            `insert into sale (id, person_id, sale_date) values (?, ?, dateadd(? day to date '2022-12-01'))`,
            [i, i % 500, i]
        );

        for (let j = 0; j < 3; ++j) {
            await attachment.execute(transaction,
                `insert into sale_item (id, sale_id, item_id, val) values (?, ?, ?, ?)`,
                [i * 3 + j, i, (i * 3 + j) % 500, j]
            );
        }
    }

    await transaction.commitRetaining();

    await attachment.execute(transaction,
        `create index sale_date on sale (sale_date)`);

    await transaction.commitRetaining();

    const date1 = new Date(Date.parse('2022-01-01'));
    const date2 = new Date(Date.parse('2022-12-31'));

    for (let i = 0; i < 15000; ++i) {
        await attachment.executeSingleton(transaction, `
            select extract(year from s.sale_date) yr,
                   extract(month from s.sale_date) mnt,
                   sum(si.val)
              from person p
              join sale s
                on s.person_id = p.id
              join sale_item si
                on si.sale_id = s.id
              join item i
                on i.id = si.item_id
              where s.sale_date between ? and ?
              group by extract(year from s.sale_date),
                       extract(month from s.sale_date)
              order by 1, 2
            `,
            [date1, date2]
        );
    }
}
finally {
    await transaction.commit();
    await attachment.dropDatabase();
}