Aula 7: Schemas Completos do Banco - Restaurantes, Pedidos e Produtos

// src/application/infra/db/schema/restaurants.ts
import { text, timestamp, pgTable } from "drizzle-orm/pg-core";
import { createId } from "@paralleldrive/cuid2";
import { users } from "./users";
import { relations } from "drizzle-orm";
import { orders } from "./orders";
import { products } from "./products";

export const restaurants = pgTable("restaurants", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  managerId: text("manager_id").references(() => users.id, {
    onDelete: "set null", // Se manager for deletado, restaurante fica sem manager
  }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

// Definir relacionamentos
export const restaurantRelations = relations(restaurants, ({ one, many }) => ({
  // Um restaurante tem um manager (usuário)
  manager: one(users, {
    fields: [restaurants.managerId],
    references: [users.id],
    relationName: "restaurant_manager",
  }),
  // Um restaurante tem muitos pedidos
  orders: many(orders),
  // Um restaurante tem muitos produtos
  products: many(products),
}));

// src/application/infra/db/schema/users.ts
import { text, timestamp, pgTable, pgEnum } from "drizzle-orm/pg-core";
import { createId } from "@paralleldrive/cuid2";
import { relations } from "drizzle-orm";
import { orders } from "./orders";
import { restaurants } from "./restaurants";

export const userRoles = pgEnum("user_role", ["manager", "customer"]);

export const users = pgTable("users", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  phone: text("phone"),
  role: userRoles("role").default("customer").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const usersRelations = relations(users, ({ many, one }) => ({
  // Um manager pode gerenciar um restaurante
  managedRestaurant: one(restaurants, {
    fields: [users.id],
    references: [restaurants.managerId],
    relationName: "managed_restaurant",
  }),
  // Um customer pode fazer muitos pedidos
  orders: many(orders),
}));

// src/application/infra/db/schema/orders.ts
import { relations } from "drizzle-orm";
import { pgTable, text, timestamp, pgEnum, integer } from "drizzle-orm/pg-core";
import { createId } from "@paralleldrive/cuid2";
import { orderItems } from "./order-items";
import { users } from "./users";
import { restaurants } from "./restaurants";

// Enum para status do pedido
export const orderStatusEnum = pgEnum("order_status", [
  "pending",    // Aguardando aprovação
  "preparing",  // Em preparo
  "ready",      // Pronto para entrega
  "delivered",  // Entregue
  "cancelled",  // Cancelado
]);

export const orders = pgTable("orders", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => createId()),
  customerId: text("customer_id").references(() => users.id, {
    onDelete: "set null", // Se customer for deletado, pedido fica sem customer
  }),
  restaurantId: text("restaurant_id")
    .notNull()
    .references(() => restaurants.id, { onDelete: "cascade" }), // Se restaurante for deletado, pedidos são deletados
  status: orderStatusEnum("status").default("pending").notNull(),
  priceInCents: integer("price_in_cents").notNull(), // Preço em centavos para evitar problemas de float
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const orderRelations = relations(orders, ({ many, one }) => ({
  // Um pedido pertence a um customer
  customer: one(users, {
    fields: [orders.customerId],
    references: [users.id],
    relationName: "order_customer",
  }),
  // Um pedido pertence a um restaurante
  restaurant: one(restaurants, {
    fields: [orders.restaurantId],
    references: [restaurants.id],
    relationName: "order_restaurant",
  }),
  // Um pedido tem muitos itens
  orderItems: many(orderItems),
}));

// src/application/infra/db/schema/products.ts
import { text, timestamp, pgTable, integer } from "drizzle-orm/pg-core";
import { createId } from "@paralleldrive/cuid2";
import { restaurants } from "./restaurants";
import { relations } from "drizzle-orm";
import { orderItems } from "./order-items";

export const products = pgTable("products", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  priceInCents: integer("price_in_cents").notNull(), // Preço em centavos
  restaurantId: text("restaurant_id")
    .notNull()
    .references(() => restaurants.id, { onDelete: "cascade" }), // Se restaurante for deletado, produtos são deletados
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const productRelations = relations(products, ({ one, many }) => ({
  // Um produto pertence a um restaurante
  restaurant: one(restaurants, {
    fields: [products.restaurantId],
    references: [restaurants.id],
    relationName: "product_restaurant",
  }),
  // Um produto pode estar em muitos itens de pedidos
  orderItems: many(orderItems),
}));

// src/application/infra/db/schema/order-items.ts
import { relations } from "drizzle-orm";
import { createId } from "@paralleldrive/cuid2";
import { integer, pgTable, text, timestamp } from "drizzle-orm/pg-core";
import { orders } from "./orders";
import { products } from "./products";

export const orderItems = pgTable("order_items", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => createId()),
  orderId: text("order_id")
    .notNull()
    .references(() => orders.id, { onDelete: "cascade" }), // Se pedido for deletado, itens são deletados
  productId: text("product_id")
    .notNull()
    .references(() => products.id, { onDelete: "set null" }), // Se produto for deletado, item fica sem produto
  priceInCents: integer("price_in_cents").notNull(), // Preço do produto no momento do pedido
  quantity: integer("quantity").notNull(), // Quantidade do produto
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const orderItemRelations = relations(orderItems, ({ one }) => ({
  // Um item pertence a um pedido
  order: one(orders, {
    fields: [orderItems.orderId],
    references: [orders.id],
    relationName: "order_item_order",
  }),
  // Um item referencia um produto
  product: one(products, {
    fields: [orderItems.productId],
    references: [products.id],
    relationName: "order_item_product",
  }),
}));

// src/application/infra/db/schema/index.ts
export * from "./auth-links";
export * from "./order-items";
export * from "./orders";
export * from "./products";
export * from "./restaurants";
export * from "./users";

# Gerar migração
bun run drizzle-kit generate

# Executar migração
bun run drizzle-kit migrate

# Verificar se as tabelas foram criadas
bun run drizzle-kit studio

📊 ESTRUTURA DO BANCO DE DADOS

┌─ users (usuários)
│  ├─ id (PK)
│  ├─ name, email, phone
│  ├─ role (manager | customer)
│  └─ timestamps
│
├─ restaurants (restaurantes)
│  ├─ id (PK)
│  ├─ name, description
│  ├─ managerId (FK → users.id)
│  └─ timestamps
│
├─ products (produtos)
│  ├─ id (PK)
│  ├─ name, description, priceInCents
│  ├─ restaurantId (FK → restaurants.id)
│  └─ timestamps
│
├─ orders (pedidos)
│  ├─ id (PK)
│  ├─ customerId (FK → users.id)
│  ├─ restaurantId (FK → restaurants.id)
│  ├─ status (pending|preparing|ready|delivered|cancelled)
│  ├─ priceInCents
│  └─ timestamps
│
├─ order_items (itens dos pedidos)
│  ├─ id (PK)
│  ├─ orderId (FK → orders.id)
│  ├─ productId (FK → products.id)
│  ├─ quantity, priceInCents
│  └─ timestamps
│
└─ auth_links (links de autenticação)
   ├─ id (PK)
   ├─ userId (FK → users.id)
   ├─ code, expiresAt
   └─ timestamps