// schema.ts
import { pgTable, serial, varchar, timestamp, boolean, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 100 }),
  createdAt: timestamp('created_at').defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: varchar('content'),
  published: boolean('published').default(false),
  authorId: integer('author_id').references(() => users.id),
});

import { eq, and, like, desc } from 'drizzle-orm';
import { db } from './db';
import { users, posts } from './schema';

// Create
const [user] = await db.insert(users)
  .values({ email: 'user@example.com', name: 'John' })
  .returning();

// Read
const allUsers = await db.select().from(users)
  .where(like(users.email, '%@example.com'))
  .orderBy(desc(users.createdAt))
  .limit(10);

const user = await db.select().from(users)
  .where(eq(users.id, 1))
  .limit(1);

// Update
await db.update(users)
  .set({ name: 'Jane' })
  .where(eq(users.id, 1));

// Delete
await db.delete(users).where(eq(users.id, 1));

const usersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .where(eq(posts.published, true));

npx drizzle-kit generate
npx drizzle-kit migrate
npx drizzle-kit studio

// db.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // SECURITY: Use proper CA certificate in production instead of disabling verification
  // ssl: { rejectUnauthorized: false } is INSECURE - vulnerable to MITM attacks
  ssl: process.env.NODE_ENV === 'production'
    ? { ca: process.env.DB_CA_CERT }
    : false,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
});

pool.on('error', (err) => {
  console.error('Unexpected pool error:', err);
});

export const db = drizzle(pool, { schema });

// Health check
export async function healthCheck() {
  const client = await pool.connect();
  try {
    await client.query('SELECT 1');
    return { status: 'healthy' };
  } finally {
    client.release();
  }
}

// Graceful shutdown
export async function closePool() {
  await pool.end();
}

import { db } from './db';

async function transferFunds(fromId: string, toId: string, amount: number) {
  return await db.transaction(async (tx) => {
    const [from] = await tx
      .select()
      .from(accounts)
      .where(eq(accounts.id, fromId))
      .for('update');

    if (!from || from.balance < amount) {
      throw new Error('Insufficient funds');
    }

    await tx
      .update(accounts)
      .set({ balance: sql`${accounts.balance} - ${amount}` })
      .where(eq(accounts.id, fromId));

    await tx
      .update(accounts)
      .set({ balance: sql`${accounts.balance} + ${amount}` })
      .where(eq(accounts.id, toId));

    return { success: true };
  });
}

// Pagination with cursor
async function getUsers(cursor?: string, limit = 20) {
  const query = db.select().from(users);

  if (cursor) {
    query.where(gt(users.id, cursor));
  }

  const results = await query
    .orderBy(asc(users.id))
    .limit(limit + 1);

  const hasMore = results.length > limit;
  const data = hasMore ? results.slice(0, -1) : results;

  return {
    data,
    nextCursor: hasMore ? data[data.length - 1].id : null,
  };
}

// Batch inserts
async function bulkInsertUsers(usersData: NewUser[]) {
  const batchSize = 100;
  for (let i = 0; i < usersData.length; i += batchSize) {
    const batch = usersData.slice(i, i + batchSize);
    await db.insert(users).values(batch);
  }
}

// Select only needed columns
const userNames = await db
  .select({ id: users.id, name: users.name })
  .from(users)
  .where(eq(users.isActive, true));

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',
  out: './migrations',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
  strict: true,
  verbose: true,
} satisfies Config;

// package.json scripts
// "db:generate": "drizzle-kit generate:pg",
// "db:migrate": "drizzle-kit migrate",
// "db:push": "drizzle-kit push:pg", // Dev only
// "db:studio": "drizzle-kit studio"

import { sql } from 'drizzle-orm';

const getUserById = db.query.users
  .findFirst({
    where: eq(users.id, sql.placeholder('id')),
    with: { posts: true },
  })
  .prepare('get_user_by_id');

// Usage (reuses execution plan)
const user = await getUserById.execute({ id: userId });

// tests/db.test.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';

describe('Database', () => {
  let pool: Pool;
  let testDb: ReturnType<typeof drizzle>;

  beforeAll(async () => {
    pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
    testDb = drizzle(pool);
    await migrate(testDb, { migrationsFolder: './migrations' });
  });

  afterAll(async () => {
    await pool.end();
  });

  beforeEach(async () => {
    await testDb.delete(users);
  });

  it('should create user', async () => {
    const [user] = await testDb
      .insert(users)
      .values({ email: 'test@example.com', name: 'Test' })
      .returning();

    expect(user.email).toBe('test@example.com');
  });
});