import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";
import { relations } from "./relations";

const db = drizzle(process.env.DATABASE_URL, { schema, relations });

import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool, schema, relations });

import {
  pgTable,
  pgEnum,
  serial,
  text,
  integer,
  timestamp,
  uuid,
  jsonb,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const statusEnum = pgEnum("status", ["active", "inactive", "banned"]);

export const users = pgTable(
  "users",
  {
    id: uuid("id")
      .default(sql`gen_random_uuid()`)
      .primaryKey(),
    name: text("name").notNull(),
    email: text("email").notNull().unique(),
    status: statusEnum().default("active").notNull(),
    metadata: jsonb("metadata").$type<{ roles: string[] }>(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [index("users_email_idx").on(t.email)],
);

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  authorId: uuid("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts({ from: r.users.id, to: r.posts.authorId }),
  },
  posts: {
    author: r.one.users({ from: r.posts.authorId, to: r.users.id }),
  },
}));

import { eq, and, ilike, sql } from "drizzle-orm";

// SELECT
const allUsers = await db.select().from(users);
const user = await db.select().from(users).where(eq(users.id, id));

// INSERT
const [created] = await db
  .insert(users)
  .values({ name: "Dan", email: "dan@example.com" })
  .returning();

// UPDATE
await db.update(users).set({ name: "Updated" }).where(eq(users.id, id));

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

// UPSERT
await db
  .insert(users)
  .values({ id, name: "Dan", email: "dan@ex.com" })
  .onConflictDoUpdate({ target: users.id, set: { name: "Dan" } });

// Nested eager loading (single SQL query)
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
  where: { status: "active" },
  orderBy: { createdAt: "desc" },
  limit: 10,
});

const user = await db.query.users.findFirst({
  where: { id: userId },
  with: { posts: { columns: { id: true, title: true } } },
});

# drizzle.config.ts -> see references/migrations.md
npx drizzle-kit generate     # schema diff -> SQL files
npx drizzle-kit migrate      # apply SQL to database
npx drizzle-kit push         # direct push (no SQL files)
npx drizzle-kit pull         # introspect DB -> Drizzle schema
npx drizzle-kit studio       # visual browser UI

const filters: SQL[] = [];
if (name) filters.push(ilike(users.name, `%${name}%`));
if (status) filters.push(eq(users.status, status));
await db
  .select()
  .from(users)
  .where(and(...filters));

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ name: "Dan" }).returning();
  await tx.insert(posts).values({ title: "Hello", authorId: user.id });
});

type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;