import { relations } from "drizzle-orm"; import { index, integer, pgTable, text, timestamp, uniqueIndex, uuid, } from "drizzle-orm/pg-core"; export const users = pgTable("users", { id: uuid("id").primaryKey().defaultRandom(), name: text("name").notNull(), email: text("email").notNull().unique(), passwordHash: text("password_hash").notNull(), createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(), }); export const sessions = pgTable( "sessions", { id: uuid("id").primaryKey().defaultRandom(), token: text("token").notNull().unique(), userId: uuid("user_id") .notNull() .references(() => users.id, { onDelete: "cascade" }), expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(), createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(), }, (t) => [index("sessions_user_idx").on(t.userId), index("sessions_token_idx").on(t.token)], ); export const searchHistory = pgTable( "search_history", { id: uuid("id").primaryKey().defaultRandom(), userId: uuid("user_id") .notNull() .references(() => users.id, { onDelete: "cascade" }), query: text("query").notNull(), tab: text("tab").notNull().default("all"), resultCount: integer("result_count").notNull().default(0), createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(), }, (t) => [ index("search_history_user_idx").on(t.userId), index("search_history_created_idx").on(t.createdAt), ], ); export const collections = pgTable( "collections", { id: uuid("id").primaryKey().defaultRandom(), userId: uuid("user_id") .notNull() .references(() => users.id, { onDelete: "cascade" }), name: text("name").notNull(), description: text("description").notNull().default(""), color: text("color").notNull().default("#4285f4"), createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(), }, (t) => [index("collections_user_idx").on(t.userId)], ); export const savedItems = pgTable( "saved_items", { id: uuid("id").primaryKey().defaultRandom(), userId: uuid("user_id") .notNull() .references(() => users.id, { onDelete: "cascade" }), collectionId: uuid("collection_id").references(() => collections.id, { onDelete: "set null", }), kind: text("kind").notNull().default("web"), // web | news | image | video title: text("title").notNull(), url: text("url").notNull(), snippet: text("snippet").notNull().default(""), thumbnail: text("thumbnail").notNull().default(""), source: text("source").notNull().default(""), query: text("query").notNull().default(""), note: text("note").notNull().default(""), createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(), }, (t) => [ index("saved_items_user_idx").on(t.userId), index("saved_items_collection_idx").on(t.collectionId), uniqueIndex("saved_items_user_url_uidx").on(t.userId, t.url), ], ); export const usersRelations = relations(users, ({ many }) => ({ sessions: many(sessions), history: many(searchHistory), collections: many(collections), savedItems: many(savedItems), })); export const sessionsRelations = relations(sessions, ({ one }) => ({ user: one(users, { fields: [sessions.userId], references: [users.id] }), })); export const searchHistoryRelations = relations(searchHistory, ({ one }) => ({ user: one(users, { fields: [searchHistory.userId], references: [users.id] }), })); export const collectionsRelations = relations(collections, ({ one, many }) => ({ user: one(users, { fields: [collections.userId], references: [users.id] }), items: many(savedItems), })); export const savedItemsRelations = relations(savedItems, ({ one }) => ({ user: one(users, { fields: [savedItems.userId], references: [users.id] }), collection: one(collections, { fields: [savedItems.collectionId], references: [collections.id], }), })); export type User = typeof users.$inferSelect; export type NewUser = typeof users.$inferInsert; export type Session = typeof sessions.$inferSelect; export type SearchHistoryRow = typeof searchHistory.$inferSelect; export type Collection = typeof collections.$inferSelect; export type SavedItem = typeof savedItems.$inferSelect; export type NewSavedItem = typeof savedItems.$inferInsert;