// Define once
const usersTable = defineTable('users', {
  id:        col.serial().primaryKey(),
  firstName: col.varchar(255).notNull(),
  email:     col.text().notNull(),
  isActive:  col.boolean().default(),
  createdAt: col.timestamptz().defaultNow(),
});

// Use everywhere — fully typed, zero boilerplate
const page = await userRepo
  .select({ isActive: true })
  .where({ email: { operator: 'ILIKE', value: '%@gmail.com' } })
  .orderBy([{ column: 'createdAt', direction: 'DESC' }])
  .paginate({ page: 1, pageSize: 20 });
// → { data: User[], count: 150, page: 1, pageSize: 20, nextAction: true, previousAction: false }

// index.ts — must be the very first line
import 'dotenv/config';

import { getPool } from 'reltype';

// schema/usersTable.ts
import { defineTable, col } from 'reltype';

export const usersTable = defineTable('users', {
  id:        col.serial().primaryKey(),
  firstName: col.varchar(255).notNull(),
  lastName:  col.varchar(255).nullable(),
  email:     col.text().notNull(),
  isActive:  col.boolean().default(),
  createdAt: col.timestamptz().defaultNow(),
});

// Types are automatically available — no extra code needed
// InferRow<typeof usersTable>    → full SELECT result type
// InferInsert<typeof usersTable> → INSERT input (required/optional by modifier)
// InferUpdate<typeof usersTable> → UPDATE input (PK excluded, all optional)

import { createRepo } from 'reltype';
import { usersTable } from './schema/usersTable';

export const userRepo = createRepo(usersTable);

// SELECT
const users = await userRepo.select({ isActive: true })
  .orderBy([{ column: 'createdAt', direction: 'DESC' }])
  .limit(10);

// INSERT
const user = await userRepo.create({ firstName: 'Alice', email: 'alice@example.com' });

// UPDATE
const updated = await userRepo.update(user.id, { isActive: false });

// DELETE
const deleted = await userRepo.delete(user.id);

import { InferRow, InferInsert, InferUpdate } from 'reltype';

type User = InferRow<typeof usersTable>;
// {
//   id: number;
//   firstName: string;
//   lastName: string | null;
//   email: string;
//   isActive: boolean;
//   createdAt: Date;
// }

type CreateUser = InferInsert<typeof usersTable>;
// {
//   firstName: string;   ← required (notNull, no default)
//   email: string;       ← required
//   lastName?: string | null;  ← optional (nullable)
//   isActive?: boolean;        ← optional (has DB default)
//   createdAt?: Date;          ← optional (defaultNow)
// }
// id is excluded — serial auto-generates it

type UpdateUser = InferUpdate<typeof usersTable>;
// {
//   firstName?: string;
//   lastName?: string | null;
//   email?: string;
//   isActive?: boolean;
//   createdAt?: Date;
// }
// id is excluded — it's the lookup key

// Simple equality
const users = await userRepo.select({ isActive: true });

// Operators: =, !=, >, <, >=, <=, LIKE, ILIKE, IN, NOT IN, IS NULL, IS NOT NULL
const users = await userRepo.select()
  .where({ createdAt: { operator: '>=', value: new Date('2024-01-01') } })
  .where({ id:        { operator: 'IN', value: [1, 2, 3] }             });

// OR conditions
const users = await userRepo.select({ isActive: true })
  .or({ firstName: { operator: 'ILIKE', value: '%john%' } })
  .or({ email:     { operator: 'ILIKE', value: '%john%' } });
// → WHERE (is_active = true) OR (first_name ILIKE '%john%') OR (email ILIKE '%john%')

// NULL check
const unverified = await userRepo.select()
  .where({ verifiedAt: { operator: 'IS NULL' } });

const users = await userRepo.select()
  .orderBy([
    { column: 'isActive',  direction: 'DESC' },
    { column: 'createdAt', direction: 'ASC'  },
  ])
  .limit(20)
  .offset(40);  // Page 3

// GROUP BY + aggregate
const stats = await userRepo.select()
  .groupBy(['isActive'])
  .calculate([{ fn: 'COUNT', alias: 'count' }]);

const result = await userRepo.select({ isActive: true })
  .join({ table: 'orders', on: 'users.id = orders.user_id', type: 'LEFT' })
  .columns(['users.id', 'users.email', 'COUNT(orders.id) AS orderCount'])
  .groupBy(['users.id', 'users.email'])
  .exec();

const { sql, params } = userRepo.select({ isActive: true })
  .orderBy([{ column: 'createdAt', direction: 'DESC' }])
  .limit(20)
  .toSQL();

console.log(sql);
// SELECT * FROM users WHERE is_active = $1 ORDER BY created_at DESC LIMIT $2
console.log(params);
// [ true, 20 ]

const result = await userRepo.select({ isActive: true })
  .orderBy([{ column: 'createdAt', direction: 'DESC' }])
  .paginate({ page: 1, pageSize: 20 });

// {
//   data:           User[],
//   count:          150,     ← total matching rows (COUNT query runs automatically)
//   page:           1,
//   pageSize:       20,
//   nextAction:     true,    ← has next page
//   previousAction: false,   ← no previous page
// }

// Page 1
const p1 = await userRepo.select({ isActive: true })
  .cursorPaginate({ pageSize: 20, cursorColumn: 'id' });
// → { data: [...], nextCursor: 'eyJpZCI6MjB9', pageSize: 20, hasNext: true }

// Page 2 — pass the cursor
const p2 = await userRepo.select({ isActive: true })
  .cursorPaginate({ pageSize: 20, cursorColumn: 'id', cursor: p1.nextCursor });

// Descending (newest first)
const latest = await userRepo.select()
  .cursorPaginate({ pageSize: 20, cursorColumn: 'createdAt', direction: 'desc' });

// Send email to every active user — without loading all users at once
await userRepo.select({ isActive: true })
  .orderBy([{ column: 'id', direction: 'ASC' }])
  .forEach(async (batch) => {
    await sendEmailBatch(batch);  // batch: User[] (200 rows at a time)
  }, { batchSize: 200 });

for await (const user of userRepo.select({ isActive: true })) {
  await processRow(user);  // one row at a time, low memory usage
}

// Custom batch size for internal fetching
for await (const user of userRepo.select().stream({ batchSize: 1000 })) {
  await writeToFile(user);
}

// Check if your index is being used
const plan = await userRepo.select({ isActive: true })
  .orderBy([{ column: 'createdAt', direction: 'DESC' }])
  .explain(true);  // true = EXPLAIN ANALYZE (actually runs the query)

console.log(plan);
// Index Scan using users_created_at_idx on users ...

// Single aggregation
const result = await userRepo.select().calculate([{ fn: 'COUNT', alias: 'count' }]);
const total = parseInt(String(result.count), 10);  // → 1042

// Multiple aggregations with filter
const stats = await userRepo.select({ isActive: true })
  .calculate([
    { fn: 'COUNT', alias: 'total'    },
    { fn: 'AVG',   column: 'score', alias: 'avgScore' },
    { fn: 'MAX',   column: 'score', alias: 'maxScore' },
  ]);
// → { total: '850', avgScore: '72.4', maxScore: '100' }

// Via repository
const users = await userRepo.raw<{ id: number; orderCount: number }>(
  `SELECT u.id, COUNT(o.id) AS order_count
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   WHERE u.is_active = $1
   GROUP BY u.id`,
  [true],
);
// → [{ id: 1, orderCount: 5 }, ...]  ← order_count → orderCount automatically

// Standalone (no repository)
import { QueryBuilder } from 'reltype';

const rows = await QueryBuilder.raw(
  'SELECT * FROM users WHERE first_name ILIKE $1',
  ['%john%'],
);

const user = await userRepo.create({
  firstName: 'Alice',
  email:     'alice@example.com',
  // isActive, createdAt → optional (DB handles defaults)
});
// → User (full row returned via RETURNING *)

// Only updates the fields you pass
const updated = await userRepo.update(1, {
  firstName: 'Alicia',
  isActive:  true,
});
// → User | null (null if ID not found)

const ok = await userRepo.delete(1);
// → true if deleted, false if not found

// Conflict on primary key (default)
await userRepo.upsert({ id: 1, firstName: 'Bob', email: 'bob@example.com' });

// Conflict on another unique column
await userRepo.upsert(
  { firstName: 'Bob', email: 'bob@example.com' },
  'email',  // snake_case column name
);

const users = await userRepo.bulkCreate([
  { firstName: 'Alice', email: 'alice@example.com' },
  { firstName: 'Bob',   email: 'bob@example.com'   },
  { firstName: 'Carol', email: 'carol@example.com' },
]);
// → User[]  (single INSERT query, RETURNING *)

const users = await userRepo.select({ isActive: true })
  .hooks({
    beforeExec: ({ sql, params }) => {
      console.log('[SQL]', sql);
    },
    afterExec: ({ rows, elapsed }) => {
      if (elapsed > 500) console.warn('Slow query:', elapsed, 'ms');
      metrics.record('db.query.duration', elapsed);
    },
    onError: ({ err, sql }) => {
      alerting.send({ message: err.message, sql });
    },
  })
  .paginate({ page: 1, pageSize: 20 });

userRepo.useHooks({
  beforeExec: ({ sql }) => logger.debug('SQL:', sql),
  afterExec:  ({ elapsed }) => metrics.histogram('db.latency', elapsed),
  onError:    ({ err })   => logger.error('DB error', { kind: err.kind }),
});

import { DbError } from 'reltype';

try {
  await userRepo.create({ firstName: 'Alice', email: 'alice@example.com' });
} catch (err) {
  if (err instanceof DbError) {
    // ✅ Safe to send to the client
    res.status(409).json(err.toUserPayload());
    // → { error: 'A duplicate value already exists.', kind: 'uniqueViolation', isRetryable: false }

    // 🔒 Internal details — never expose these
    logger.error('db error', err.toLogContext());
    // → { pgCode: '23505', table: 'users', constraint: 'users_email_key', detail: '...' }

    // Retry logic for transient errors
    if (err.isRetryable) await retry(operation);
  }
}

app.post('/users', async (req, res) => {
  try {
    const user = await userRepo.create(req.body);
    res.status(201).json(user);
  } catch (err) {
    if (err instanceof DbError) {
      const status =
        err.kind === 'uniqueViolation'   ? 409 :
        err.kind === 'notNullViolation'  ? 400 :
        err.kind === 'foreignKeyViolation' ? 422 :
        err.isRetryable                  ? 503 : 500;
      res.status(status).json(err.toUserPayload());
    } else {
      res.status(500).json({ error: 'Unexpected error.' });
    }
  }
});

import { runInTx } from 'reltype';

await runInTx(async (client) => {
  // Both operations run in the same transaction
  const user  = await userRepo.create({ firstName: 'Alice', email: 'alice@example.com' });
  const order = await orderRepo.create({ userId: user.id, total: 9900 });
  return { user, order };
});
// Automatically ROLLBACK if any operation throws

import { getPool, getPoolStatus, checkPoolHealth, closePool } from 'reltype';

// Real-time pool metrics
const status = getPoolStatus();
// {
//   isInitialized: true,
//   totalCount:    8,   ← total connections open
//   idleCount:     3,   ← ready to use
//   waitingCount:  0,   ← requests waiting (0 = healthy)
//   isHealthy:     true
// }

// Ping the DB server (SELECT 1)
const alive = await checkPoolHealth();  // → boolean

// Graceful shutdown
process.on('SIGTERM', async () => {
  await closePool();
  process.exit(0);
});

// Dot notation
const logsTable = defineTable('audit.activity_logs', { ... });

// Explicit option
const usersTable = defineTable('users', { ... }, { schema: 'auth' });

// → SQL: INSERT INTO "auth"."users" ...
// Identifiers are always quoted to avoid reserved word conflicts

col.text().notNull()           // required on INSERT
col.text().nullable()          // optional on INSERT, allows NULL
col.integer().primaryKey()     // optional on INSERT, serial/auto
col.boolean().default()        // optional on INSERT (DB has a DEFAULT)
col.timestamptz().defaultNow() // optional on INSERT (DEFAULT NOW())

import { BaseRepo, InferRow } from 'reltype';
import { usersTable } from './schema';

class UserRepo extends BaseRepo<typeof usersTable> {
  findActive(): Promise<InferRow<typeof usersTable>[]> {
    return this.findAll({ where: { isActive: true } });
  }

  findByEmail(email: string): Promise<InferRow<typeof usersTable> | null> {
    return this.findOne({ email });
  }

  async search(query: string, page: number) {
    return this.select()
      .or({ firstName: { operator: 'ILIKE', value: `%${query}%` } })
      .or({ email:     { operator: 'ILIKE', value: `%${query}%` } })
      .orderBy([{ column: 'createdAt', direction: 'DESC' }])
      .paginate({ page, pageSize: 20 });
  }
}

export const userRepo = new UserRepo(usersTable);