import { toInsertableSchema, toFullSelectableSchema, WithSchema, connect, queryBuilder } from 'mysql-with-kysely'
import type { User } from './model'

// for query builder
type Database = {
  user: WithSchema<User>
}

// for your code
type SelectableSchema = toFullSelectableSchema<Database>
type InsertableSchema = toInsertableSchema<Database>

const { db, close } = connect<Database>({ uri: 'mysql://root:root@localhost:3306/test' });

// collect your metrics
db.subscribe(({ sql, normalizedSql, durationMs, occurredAt, parameters }) => {
})

// your query builder
const qb = queryBuilder<Database>()

// write your codes type safely
// type of users is `Array<SelectableSchema['user']>`
// if you don't need created_at & updated_at, use toSelectableSchema instead of toFullSelectableSchema
const users = await db.query(qb
  .selectFrom('user')
  .selectAll()
  .orderBy('id', 'desc')
  .limit(1),
)

// type of value is `InsertableSchema['user']`
const value = { name: 'kanziw', email: '[email protected]' }     
const { insertId } = await db.execute(qb
  .insertInto('user')
  .values(value),
)

// close MySQL connection
await close()