Drizzle ORM Beispiele

// Drizzle ORM Basic Setup Example

import { drizzle } from 'drizzle-orm/postgres-js';
import { pgTable, serial, text, integer, timestamp, boolean, varchar, pgEnum } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { sql } from 'drizzle-orm';

// 1. Database Configuration
// Environment variables
const DATABASE_URL = process.env.DATABASE_URL || 'postgresql://localhost:5432/myapp';

// Database instance
export const db = drizzle(DATABASE_URL);

// 2. Enums
export const userRoleEnum = pgEnum('user_role', ['admin', 'user', 'moderator']);
export const postStatusEnum = pgEnum('post_status', ['draft', 'published', 'archived']);
export const orderStatusEnum = pgEnum('order_status', ['pending', 'confirmed', 'shipped', 'delivered', 'cancelled']);

// 3. Table Definitions

// Users table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  username: varchar('username', { length: 50 }).unique().notNull(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  firstName: varchar('first_name', { length: 100 }).notNull(),
  lastName: varchar('last_name', { length: 100 }).notNull(),
  role: userRoleEnum('role').default('user'),
  isActive: boolean('is_active').default(true),
  emailVerified: boolean('email_verified').default(false),
  avatarUrl: varchar('avatar_url', { length: 500 }).optional(),
  bio: text('bio').optional(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow(),
  lastLoginAt: timestamp('last_login_at').optional()
});

// Categories table
export const categories = pgTable('categories', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  slug: varchar('slug', { length: 100 }).unique().notNull(),
  description: text('description').optional(),
  parentId: integer('parent_id').references(() => categories.id).optional(),
  isActive: boolean('is_active').default(true),
  sortOrder: integer('sort_order').default(0),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// Posts table
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).unique().notNull(),
  content: text('content').notNull(),
  excerpt: text('excerpt').optional(),
  authorId: integer('author_id').references(() => users.id).notNull(),
  status: postStatusEnum('status').default('draft'),
  featured: boolean('featured').default(false),
  viewCount: integer('view_count').default(0),
  publishedAt: timestamp('published_at').optional(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Post categories join table
export const postCategories = pgTable('post_categories', {
  postId: integer('post_id').references(() => posts.id).notNull(),
  categoryId: integer('category_id').references(() => categories.id).notNull(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// Comments table
export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  postId: integer('post_id').references(() => posts.id).notNull(),
  authorId: integer('author_id').references(() => users.id).notNull(),
  content: text('content').notNull(),
  parentId: integer('parent_id').references(() => comments.id).optional(),
  isApproved: boolean('is_approved').default(false),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Tags table
export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 50 }).unique().notNull(),
  slug: varchar('slug', { length: 50 }).unique().notNull(),
  color: varchar('color', { length: 7 }).default('#000000'), // Hex color
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// Post tags join table
export const postTags = pgTable('post_tags', {
  postId: integer('post_id').references(() => posts.id).notNull(),
  tagId: integer('tag_id').references(() => tags.id).notNull(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// 4. Type Definitions (TypeScript types extracted from schemas)
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Category = typeof categories.$inferSelect;
export type NewCategory = typeof categories.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;
export type Comment = typeof comments.$inferSelect;
export type NewComment = typeof comments.$inferInsert;
export type Tag = typeof tags.$inferSelect;
export type NewTag = typeof tags.$inferInsert;

// 5. Relations Definition
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts, { relationName: 'author' }),
  comments: many(comments, { relationName: 'author' })
}));

export const categoriesRelations = relations(categories, ({ many, one }) => ({
  parent: one(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: 'children'
  }),
  children: many(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: 'parent'
  }),
  posts: many(posts, { relationName: 'categories' })
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
    relationName: 'posts'
  }),
  comments: many(comments, { relationName: 'post' }),
  categories: many(categories, { relationName: 'posts' }),
  tags: many(tags, { relationName: 'posts' }),
  parentComments: many(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'childComments'
  })
}));

export const commentsRelations = relations(comments, ({ one, many }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
    relationName: 'comments'
  }),
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
    relationName: 'comments'
  }),
  parent: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'childComments'
  }),
  childComments: many(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'parent'
  })
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  posts: many(posts, { relationName: 'tags' })
}));

// 6. Basic CRUD Operations

// Create operations
export async function createUser(userData: NewUser): Promise<User> {
  const [user] = await db.insert(users).values(userData).returning();
  return user;
}

export async function createCategory(categoryData: NewCategory): Promise<Category> {
  const [category] = await db.insert(categories).values(categoryData).returning();
  return category;
}

export async function createPost(postData: NewPost, categoryIds?: number[], tagIds?: number[]): Promise<Post> {
  const [post] = await db.insert(posts).values(postData).returning();

  // Add categories if provided
  if (categoryIds && categoryIds.length > 0) {
    await db.insert(postCategories).values(
      categoryIds.map(categoryId => ({
        postId: post.id,
        categoryId
      }))
    );
  }

  // Add tags if provided
  if (tagIds && tagIds.length > 0) {
    await db.insert(postTags).values(
      tagIds.map(tagId => ({
        postId: post.id,
        tagId
      }))
    );
  }

  return post;
}

export async function createComment(commentData: NewComment): Promise<Comment> {
  const [comment] = await db.insert(comments).values(commentData).returning();
  return comment;
}

export async function createTag(tagData: NewTag): Promise<Tag> {
  const [tag] = await db.insert(tags).values(tagData).returning();
  return tag;
}

// Read operations
export async function getUserById(id: number): Promise<User | undefined> {
  const [user] = await db.select().from(users).where(eq(users.id, id)).limit(1);
  return user;
}

export async function getUserByEmail(email: string): Promise<User | undefined> {
  const [user] = await db.select().from(users).where(eq(users.email, email)).limit(1);
  return user;
}

export function getAllUsers(): Promise<User[]> {
  return db.select().from(users);
}

export function getActiveUsers(): Promise<User[]> {
  return db.select().from(users).where(eq(users.isActive, true));
}

export async function getPostById(id: number): Promise<Post | undefined> {
  const [post] = await db.select().from(posts).where(eq(posts.id, id)).limit(1);
  return post;
}

export function getPostsByAuthor(authorId: number): Promise<Post[]> {
  return db.select().from(posts).where(eq(posts.authorId, authorId));
}

export function getPublishedPosts(): Promise<Post[]> {
  return db.select().from(posts).where(eq(posts.status, 'published'));
}

export function getFeaturedPosts(): Promise<Post[]> {
  return db.select().from(posts).where(eq(posts.featured, true));
}

export function searchPosts(searchTerm: string): Promise<Post[]> {
  return db.select().from(posts).where(
    or(
      ilike(posts.title, `%${searchTerm}%`),
      ilike(posts.content, `%${searchTerm}%`)
    )
  );
}

export function getPostsWithCategories(postId: number): Promise<Post & { categories: Category[] }> {
  return db
    .select({
      id: posts.id,
      title: posts.title,
      slug: posts.slug,
      content: posts.content,
      excerpt: posts.excerpt,
      authorId: posts.authorId,
      status: posts.status,
      featured: posts.featured,
      viewCount: posts.viewCount,
      publishedAt: posts.publishedAt,
      createdAt: posts.createdAt,
      updatedAt: posts.updatedAt
    })
    .from(posts)
    .leftJoin(postCategories, eq(posts.id, postCategories.postId))
    .leftJoin(categories, eq(postCategories.categoryId, categories.id))
    .where(eq(posts.id, postId))
    .groupBy(posts.id)
    .then(posts => {
      // This is a simplified version - in real code, you'd use more sophisticated SQL
      return posts.map(post => ({
        ...post,
        categories: [] // Would need to fetch categories separately or use proper SQL
      }));
    });
}

// Update operations
export async function updateUser(id: number, updateData: Partial<NewUser>): Promise<User> {
  const [user] = await db
    .update(users)
    .set({ ...updateData, updatedAt: new Date() })
    .where(eq(users.id, id))
    .returning();
  return user;
}

export async function updatePost(id: number, updateData: Partial<NewPost>): Promise<Post> {
  const [post] = await db
    .update(posts)
    .set({ ...updateData, updatedAt: new Date() })
    .where(eq(posts.id, id))
    .returning();
  return post;
}

export async function incrementPostViewCount(id: number): Promise<Post> {
  const [post] = await db
    .update(posts)
    .set({
      viewCount: sql`${posts.viewCount} + 1`,
      updatedAt: new Date()
    })
    .where(eq(posts.id, id))
    .returning();
  return post;
}

// Delete operations
export async function deleteUser(id: number): Promise<void> {
  await db.delete(users).where(eq(users.id, id));
}

export async function deletePost(id: number): Promise<void> {
  await db.delete(posts).where(eq(posts.id, id));
}

export async function deleteComment(id: number): Promise<void> {
  await db.delete(comments).where(eq(comments.id, id));
}

// 7. Advanced Queries
export function getUsersWithPostCount() {
  return db
    .select({
      id: users.id,
      username: users.username,
      email: users.email,
      firstName: users.firstName,
      lastName: users.lastName,
      role: users.role,
      isActive: users.isActive,
      postCount: count(posts.id)
    })
    .from(users)
    .leftJoin(posts, eq(users.id, posts.authorId))
    .groupBy(users.id)
    .orderBy(desc(sql`count(${posts.id})`));
}

export function getRecentPosts(limit: number = 10) {
  return db
    .select()
    .from(posts)
    .where(eq(posts.status, 'published'))
    .orderBy(desc(posts.createdAt))
    .limit(limit);
}

export function getPopularPosts(days: number = 30, limit: number = 5) {
  return db
    .select()
    .from(posts)
    .where(
      and(
        eq(posts.status, 'published'),
        gte(posts.createdAt, new Date(Date.now() - days * 24 * 60 * 60 * 1000))
      )
    )
    .orderBy(desc(posts.viewCount))
    .limit(limit);
}

export function getPostsWithComments(postId: number) {
  return db.query.posts
    .findFirst({
      where: eq(posts.id, postId),
      with: {
        author: {
          columns: {
            id: true,
            username: true,
            firstName: true,
            lastName: true
          }
        },
        comments: {
          orderBy: desc(comments.createdAt),
          with: {
            author: {
              columns: {
                id: true,
                username: true,
                firstName: true,
                lastName: true
              }
            }
          }
        }
      }
    });
}

// 8. Transaction Examples
export async function createPostWithTags(
  postData: NewPost,
  tagNames: string[]
): Promise<Post> {
  return db.transaction(async (tx) => {
    // Create the post
    const [post] = await tx.insert(posts).values(postData).returning();

    // Create or find tags
    const createdTags = await Promise.all(
      tagNames.map(async (tagName) => {
        // Check if tag already exists
        const [existingTag] = await tx
          .select()
          .from(tags)
          .where(eq(tags.name, tagName))
          .limit(1);

        if (existingTag) {
          return existingTag;
        }

        // Create new tag
        const [newTag] = await tx
          .insert(tags)
          .values({
            name: tagName,
            slug: tagName.toLowerCase().replace(/\s+/g, '-')
          })
          .returning();

        return newTag;
      })
    );

    // Create post-tag relationships
    await tx.insert(postTags).values(
      createdTags.map(tag => ({
        postId: post.id,
        tagId: tag.id
      }))
    );

    return post;
  });
}

export async function transferPostOwnership(
  postId: number,
  newAuthorId: number
): Promise<Post> {
  return db.transaction(async (tx) => {
    // Update the post
    const [post] = await tx
      .update(posts)
      .set({ authorId: newAuthorId, updatedAt: new Date() })
      .where(eq(posts.id, postId))
      .returning();

    return post;
  });
}

// 9. Migration Helper Functions
export async function createDatabaseSchema() {
  // This would typically be handled by Drizzle migrations
  // but here's an example of programmatic schema creation

  console.log('Creating database schema...');

  // Tables would be created here if not using migration files
  console.log('Database schema created successfully!');
}

// 10. Database Health Check
export async function checkDatabaseHealth(): Promise<boolean> {
  try {
    // Test basic connection
    await db.select().from(users).limit(1);

    // Test all tables exist
    const tables = [
      'users',
      'categories',
      'posts',
      'comments',
      'tags',
      'post_categories',
      'post_tags'
    ];

    for (const table of tables) {
      await db.select().from(sql.raw(`SELECT 1 FROM ${table} LIMIT 1`));
    }

    console.log('Database health check passed');
    return true;
  } catch (error) {
    console.error('Database health check failed:', error);
    return false;
  }
}

// 11. Utility Functions

// Type-safe query builder helper
function buildQuery<T>(
  table: any,
  options: {
    where?: any;
    orderBy?: any;
    limit?: number;
    offset?: number;
    select?: any[];
  } = {}
) {
  let query = db.select(options.select || []).from(table);

  if (options.where) {
    query = query.where(options.where);
  }

  if (options.orderBy) {
    query = query.orderBy(options.orderBy);
  }

  if (options.limit) {
    query = query.limit(options.limit);
  }

  if (options.offset) {
    query = query.offset(options.offset);
  }

  return query as any; // Return as T
}

// Pagination helper
export interface PaginationOptions {
  page?: number;
  limit?: number;
  sortBy?: string;
  sortOrder?: 'asc' | 'desc';
}

export interface PaginatedResult<T> {
  data: T[];
  pagination: {
    page: number;
    limit: number;
    total: number;
    totalPages: number;
    hasNext: boolean;
    hasPrev: boolean;
  };
}

export async function getPaginatedResults<T>(
  query: any,
  options: PaginationOptions = {}
): Promise<PaginatedResult<T>> {
  const page = options.page || 1;
  const limit = options.limit || 20;
  const offset = (page - 1) * limit;

  // Get total count
  const [{ count }] = await db
    .select({ count: sql`count(*)` })
    .from(query as any);

  const total = Number(count);

  // Get paginated data
  const data = await query
    .limit(limit)
    .offset(offset)
    .orderBy(
      options.sortBy && options.sortOrder
        ? options.sortOrder === 'asc'
          ? asc(sql.raw(`${options.sortBy}`))
          : desc(sql.raw(`${options.sortBy}`))
        : sql`id`
    );

  return {
    data,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
      hasNext: page * limit < total,
      hasPrev: page > 1
    }
  };
}

// Import required operators and functions
import { eq, and, or, gte, lte, ilike, count, asc, desc } from 'drizzle-orm';

export {
  users,
  categories,
  posts,
  comments,
  tags,
  postCategories,
  postTags,
  db,
  type User,
  type NewUser,
  type Category,
  type NewCategory,
  type Post,
  type NewPost,
  type Comment,
  type NewComment,
  type Tag,
  type NewTag
};

// Drizzle Migrations and Relations Advanced Example

import { drizzle } from 'drizzle-orm/postgres-js';
import { pgTable, serial, text, integer, timestamp, boolean, varchar, pgEnum, json, real } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { sql } from 'drizzle-orm';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { eq, and, or, inArray, isNull, not, asc, desc } from 'drizzle-orm';

// Database configuration
const DATABASE_URL = process.env.DATABASE_URL || 'postgresql://localhost:5432/myapp';
export const db = drizzle(DATABASE_URL);

// 1. Enhanced Table Definitions with Advanced Features

// Payment status enum
export const paymentStatusEnum = pgEnum('payment_status', [
  'pending',
  'processing',
  'completed',
  'failed',
  'refunded'
]);

// Notification type enum
export const notificationTypeEnum = pgEnum('notification_type', [
  'info',
  'success',
  'warning',
  'error'
]);

// Users table with additional fields
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  username: varchar('username', { length: 50 }).unique().notNull(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  firstName: varchar('first_name', { length: 100 }).notNull(),
  lastName: varchar('last_name', { length: 100 }).notNull(),
  avatarUrl: varchar('avatar_url', { length: 500 }).optional(),
  bio: text('bio').optional(),
  website: varchar('website', { length: 255 }).optional(),
  location: json('location').optional(), // Store as JSON
  preferences: json('preferences').default({}), // User preferences
  isActive: boolean('is_active').default(true),
  isVerified: boolean('is_verified').default(false),
  lastLoginAt: timestamp('last_login_at').optional(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Enhanced products table
export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).unique().notNull(),
  description: text('description').notNull(),
  shortDescription: varchar('short_description', { length: 500 }).optional(),
  price: real('price').notNull(),
  compareAtPrice: real('compare_at_price').optional(),
  sku: varchar('sku', { length: 100 }).unique().notNull(),
  barcode: varchar('barcode', { length: 50 }).unique().optional(),
  trackInventory: boolean('track_inventory').default(true),
  stockQuantity: integer('stock_quantity').default(0),
  minStockLevel: integer('min_stock_level').default(0),
  weight: real('weight').optional(),
  dimensions: json('dimensions').optional(), // { length, width, height }
  images: json('images').default([]), // Array of image URLs
  isDigital: boolean('is_digital').default(false),
  isActive: boolean('is_active').default(true),
  featured: boolean('featured').default(false),
  rating: real('rating').default(0),
  reviewCount: integer('review_count').default(0),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Orders table
export const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  orderNumber: varchar('order_number', { length: 50 }).unique().notNull(),
  customerId: integer('customer_id').references(() => users.id).notNull(),
  status: paymentStatusEnum('status').default('pending'),
  subtotal: real('subtotal').notNull(),
  tax: real('tax').notNull(),
  shipping: real('shipping').notNull(),
  total: real('total').notNull(),
  currency: varchar('currency', { length: 3 }).default('USD'),
  shippingAddress: json('shipping_address').notNull(),
  billingAddress: json('billing_address').notNull(),
  notes: text('notes').optional(),
  shippedAt: timestamp('shipped_at').optional(),
  deliveredAt: timestamp('delivered_at').optional(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Order items table
export const orderItems = pgTable('order_items', {
  id: serial('id').primaryKey(),
  orderId: integer('order_id').references(() => orders.id).notNull(),
  productId: integer('product_id').references(() => products.id).notNull(),
  quantity: integer('quantity').positive().notNull(),
  unitPrice: real('unit_price').notNull(),
  totalPrice: real('total_price').notNull(),
  productName: varchar('product_name', { length: 255 }).notNull(), // Store product name at time of order
  productSku: varchar('product_sku', { length: 100 }).notNull(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// Reviews table
export const reviews = pgTable('reviews', {
  id: serial('id').primaryKey(),
  productId: integer('product_id').references(() => products.id).notNull(),
  customerId: integer('customer_id').references(() => users.id).notNull(),
  rating: integer('rating').min(1).max(5).notNull(),
  title: varchar('title', { length: 255 }).optional(),
  content: text('content').notNull(),
  isVerified: boolean('is_verified').default(false),
  isApproved: boolean('is_approved').default(true),
  helpfulCount: integer('helpful_count').default(0),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Notifications table
export const notifications = pgTable('notifications', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id).notNull(),
  type: notificationTypeEnum('type').notNull(),
  title: varchar('title', { length: 255 }).notNull(),
  message: text('message').notNull(),
  data: json('data').optional(),
  isRead: boolean('is_read').default(false),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  readAt: timestamp('read_at').optional()
});

// 2. Enhanced Relations

export const usersRelations = relations(users, ({ many }) => ({
  orders: many(orders, { relationName: 'customer' }),
  reviews: many(reviews, { relationName: 'customer' }),
  notifications: many(notifications, { relationName: 'user' })
}));

export const productsRelations = relations(products, ({ many }) => ({
  orderItems: many(orderItems, { relationName: 'product' }),
  reviews: many(reviews, { relationName: 'product' })
}));

export const ordersRelations = relations(orders, ({ one, many }) => ({
  customer: one(users, {
    fields: [orders.customerId],
    references: [users.id],
    relationName: 'orders'
  }),
  items: many(orderItems, { relationName: 'order' })
}));

export const orderItemsRelations = relations(orderItems, ({ one }) => ({
  order: one(orders, {
    fields: [orderItems.orderId],
    references: [orders.id],
    relationName: 'items'
  }),
  product: one(products, {
    fields: [orderItems.productId],
    references: [products.id],
    relationName: 'items'
  })
}));

export const reviewsRelations = relations(reviews, ({ one }) => ({
  product: one(products, {
    fields: [reviews.productId],
    references: [products.id],
    relationName: 'reviews'
  }),
  customer: one(users, {
    fields: [reviews.customerId],
    references: [users.id],
    relationName: 'reviews'
  })
}));

export const notificationsRelations = relations(notifications, ({ one }) => ({
  user: one(users, {
    fields: [notifications.userId],
    references: [users.id],
    relationName: 'notifications'
  })
}));

// 3. Migration Functions

// Migration configuration
export const migrationConfig = {
  migrationsFolder: './migrations',
  database: DATABASE_URL
};

// Create initial migration
export async function createInitialMigration() {
  const migrationId = Date.now().toString();
  const migrationSql = `
    -- Create users table
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      username VARCHAR(50) UNIQUE NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      password_hash VARCHAR(255) NOT NULL,
      first_name VARCHAR(100) NOT NULL,
      last_name VARCHAR(100) NOT NULL,
      avatar_url VARCHAR(500),
      bio TEXT,
      website VARCHAR(255),
      location JSONB,
      preferences JSONB DEFAULT '{}',
      is_active BOOLEAN DEFAULT TRUE,
      is_verified BOOLEAN DEFAULT FALSE,
      last_login_at TIMESTAMP,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- Create indexes
    CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
    CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
    CREATE INDEX IF NOT EXISTS idx_users_active ON users(is_active);

    -- Create products table
    CREATE TABLE IF NOT EXISTS products (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      slug VARCHAR(255) UNIQUE NOT NULL,
      description TEXT NOT NULL,
      short_description VARCHAR(500),
      price REAL NOT NULL,
      compare_at_price REAL,
      sku VARCHAR(100) UNIQUE NOT NULL,
      barcode VARCHAR(50) UNIQUE,
      track_inventory BOOLEAN DEFAULT TRUE,
      stock_quantity INTEGER DEFAULT 0,
      min_stock_level INTEGER DEFAULT 0,
      weight REAL,
      dimensions JSONB,
      images JSONB DEFAULT '[]',
      is_digital BOOLEAN DEFAULT FALSE,
      is_active BOOLEAN DEFAULT TRUE,
      featured BOOLEAN DEFAULT FALSE,
      rating REAL DEFAULT 0,
      review_count INTEGER DEFAULT 0,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- Create products indexes
    CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);
    CREATE INDEX IF NOT EXISTS idx_products_active ON products(is_active);
    CREATE INDEX IF NOT EXISTS idx_products_featured ON products(featured);
    CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating);

    -- Create orders table
    CREATE TABLE IF NOT EXISTS orders (
      id SERIAL PRIMARY KEY,
      order_number VARCHAR(50) UNIQUE NOT NULL,
      customer_id INTEGER REFERENCES users(id) NOT NULL,
      status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'refunded')),
      subtotal REAL NOT NULL,
      tax REAL NOT NULL,
      shipping REAL NOT NULL,
      total REAL NOT NULL,
      currency VARCHAR(3) DEFAULT 'USD',
      shipping_address JSONB NOT NULL,
      billing_address JSONB NOT NULL,
      notes TEXT,
      shipped_at TIMESTAMP,
      delivered_at TIMESTAMP,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- Create orders indexes
    CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);
    CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
    CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);

    -- Add foreign key constraint
    ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
      FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE CASCADE;

    -- Insert migration record
    INSERT INTO drizzle_migrations (id, name, created_at)
    VALUES ('${migrationId}', 'initial_schema', CURRENT_TIMESTAMP);
  `;

  return migrationSql;
}

// 4. Advanced Query Examples with Relations

// Get user with all their orders and reviews
export async function getUserWithOrdersAndReviews(userId: number) {
  return db.query.users
    .findFirst({
      where: eq(users.id, userId),
      with: {
        orders: {
          orderBy: desc(orders.createdAt),
          with: {
            items: {
              with: {
                product: true
              }
            }
          }
        },
        reviews: {
          where: eq(reviews.isApproved, true),
          orderBy: [desc(reviews.createdAt), desc(reviews.id)],
          limit: 10
        }
      }
    });
}

// Get product with reviews and order statistics
export async function getProductWithStats(productId: number) {
  const product = await db.query.products
    .findFirst({
      where: eq(products.id, productId),
      with: {
        reviews: {
          where: eq(reviews.isApproved, true),
          orderBy: desc(reviews.createdAt),
          limit: 10
        },
        orderItems: {
          with: {
            order: {
              columns: {
                id: true,
                orderNumber: true,
                customerId: true,
                status: true
              }
            }
          }
        }
      }
    });

  if (!product) return null;

  // Calculate order statistics
  const [orderStats] = await db
    .select({
      totalOrders: count(orderItems.id),
      totalQuantity: sum(orderItems.quantity),
      totalRevenue: sum(orderItems.totalPrice)
    })
    .from(orderItems)
    .where(eq(orderItems.productId, productId));

  return {
    ...product,
    orderStats: orderStats || {
      totalOrders: 0,
      totalQuantity: 0,
      totalRevenue: 0
    }
  };
}

// Get orders with customer and product information
export async function getOrdersWithDetails() {
  return db.query.orders
    .findMany({
      with: {
        customer: true,
        items: {
          with: {
            product: {
              columns: {
                id: true,
                name: true,
                sku: true,
                price: true
              }
            }
          }
        }
      },
      orderBy: desc(orders.createdAt)
    });
}

// Get customer order history with statistics
export async function getCustomerOrderHistory(customerId: number) {
  const orders = await db.query.orders
    .findMany({
      where: eq(orders.customerId, customerId),
      with: {
        items: {
          with: {
            product: true
          }
        }
      },
      orderBy: desc(orders.createdAt)
    });

  // Calculate statistics
  const [stats] = await db
    .select({
      totalOrders: count(orders.id),
      totalSpent: sum(orders.total),
      orderCount: count(orders.id)
    })
    .from(orders)
    .where(eq(orders.customerId, customerId));

  return {
    orders,
    stats: stats || { totalOrders: 0, totalSpent: 0, orderCount: 0 }
  };
}

// 5. Complex Business Logic Operations

// Complete order processing
export async function processOrder(orderData: {
  customerId: number;
  items: Array<{ productId: number; quantity: number }>;
  shippingAddress: any;
  billingAddress: any;
}) {
  return db.transaction(async (tx) => {
    // Calculate totals
    let subtotal = 0;
    let items = [];

    for (const item of orderData.items) {
      // Check product availability
      const [product] = await tx
        .select()
        .from(products)
        .where(eq(products.id, item.productId))
        .limit(1);

      if (!product) {
        throw new Error(`Product ${item.productId} not found`);
      }

      if (product.trackInventory && product.stockQuantity < item.quantity) {
        throw new Error(`Insufficient stock for product ${product.name}. Available: ${product.stockQuantity}, Requested: ${item.quantity}`);
      }

      const unitPrice = product.price;
      const totalPrice = unitPrice * item.quantity;
      subtotal += totalPrice;

      // Store item details
      items.push({
        productId: product.id,
        productName: product.name,
        productSku: product.sku,
        quantity: item.quantity,
        unitPrice,
        totalPrice
      });

      // Update inventory if tracking
      if (product.trackInventory) {
        await tx
          .update(products)
          .set({
            stockQuantity: sql`${products.stockQuantity} - ${item.quantity}`,
            updatedAt: new Date()
          })
          .where(eq(products.id, item.productId));
      }
    }

    // Calculate tax and shipping (simplified)
    const tax = subtotal * 0.1; // 10% tax
    const shipping = items.length > 0 ? 5.99 : 0; // Flat shipping rate
    const total = subtotal + tax + shipping;

    // Generate order number
    const orderNumber = `ORD-${Date.now()}`;

    // Create order
    const [order] = await tx
      .insert(orders)
      .values({
        orderNumber,
        customerId: orderData.customerId,
        status: 'pending',
        subtotal,
        tax,
        shipping,
        total,
        shippingAddress: orderData.shippingAddress,
        billingAddress: orderData.billingAddress,
        currency: 'USD'
      })
      .returning();

    // Create order items
    await tx.insert(orderItems).values(
      items.map(item => ({
        orderId: order.id,
        ...item
      }))
    );

    // Create notification for customer
    await tx.insert(notifications).values({
      userId: orderData.customerId,
      type: 'success',
      title: 'Order Placed Successfully',
      message: `Your order ${orderNumber} has been placed successfully.`,
      data: {
        orderId: order.id,
        orderNumber,
        total
      }
    });

    return {
      order,
      items
    };
  });
}

// Bulk operations
export async function bulkUpdateProductPrices(updates: Array<{ productId: number; newPrice: number }>) {
  const result = await db.transaction(async (tx) => {
    const updatedProducts = await Promise.all(
      updates.map(async ({ productId, newPrice }) => {
        const [product] = await tx
          .update(products)
          .set({ price: newPrice, updatedAt: new Date() })
          .where(eq(products.id, productId))
          .returning();

        return product;
      })
    );

    return updatedProducts;
  });

  return result;
}

// 6. Data Analysis and Reporting

// Sales report by date range
export async function getSalesReport(startDate: Date, endDate: Date) {
  const sales = await db
    .select({
      date: sql`DATE(orders.created_at)`.as('date'),
      orderCount: count(orders.id),
      totalRevenue: sum(orders.total),
      averageOrderValue: sql`AVG(orders.total)`
    })
    .from(orders)
    .where(
      and(
        gte(orders.createdAt, startDate),
        lte(orders.createdAt, endDate)
      )
    )
    .groupBy(sql`DATE(orders.created_at)`)
    .orderBy(sql`DATE(orders.created_at)`);

  return sales;
}

// Top performing products
export async function getTopPerformingProducts(limit: number = 10) {
  return db
    .select({
      id: products.id,
      name: products.name,
      sku: products.sku,
      price: products.price,
      totalSold: sum(orderItems.quantity),
      totalRevenue: sum(orderItems.totalPrice),
      orderCount: count(sql`DISTINCT ${orderItems.orderId}`),
      averageRating: sql`COALESCE(
        (
          SELECT AVG(rating)
          FROM reviews
          WHERE reviews.productId = products.id
          AND reviews.isApproved = true
        ), 0
      )`,
      reviewCount: count(
        sql`${reviews.id}`
      )
    })
    .from(products)
    .leftJoin(orderItems, eq(orderItems.productId, products.id))
    .leftJoin(reviews, eq(reviews.productId, products.id))
    .groupBy(products.id)
    .orderBy(desc(sql`sum(${orderItems.totalPrice})`))
    .limit(limit);
}

// Customer analytics
export async function getCustomerAnalytics() {
  const analytics = await db
    .select({
      totalCustomers: count(users.id),
      activeCustomers: count(sql`CASE WHEN ${users.lastLoginAt} > CURRENT_DATE - INTERVAL '30 days' THEN 1 END`),
      verifiedCustomers: count(sql`${users.isVerified}`),
      registrationTrend: sql`COUNT(CASE WHEN created_at >= CURRENT_DATE - INTERVAL '30 days' THEN 1 END)`,
      topCountries: sql`json_agg(DISTINCT CASE
        WHEN ${users.location} IS NOT NULL
        THEN (${users.location}->>'country')
        ELSE 'Unknown'
      END) ORDER BY count(*) DESC LIMIT 5)`
    })
    .from(users);

  // Recent registrations
  const recentRegistrations = await db
    .select()
    .from(users)
    .orderBy(desc(users.createdAt))
    .limit(10);

  return {
    analytics: analytics[0],
    recentRegistrations
  };
}

// 7. Backup and Data Export

// Export user data for GDPR compliance
export async function exportUserData(userId: number) {
  const userData = await db.query.users
    .findFirst({
      where: eq(users.id, userId),
      with: {
        orders: {
          columns: {
            id: true,
            orderNumber: true,
            status: true,
            total: true,
            createdAt: true
          },
          with: {
            items: {
              columns: {
                productName: true,
                quantity: true,
                unitPrice: true,
                totalPrice: true
              }
            }
          }
        },
        reviews: {
          columns: {
            id: true,
            rating: true,
            title: true,
            content: true,
            createdAt: true
          }
        },
        notifications: {
          columns: {
            id: true,
            type: true,
            title: true,
            message: true,
            isRead: true,
            createdAt: true
          }
        }
      }
    });

  return userData;
}

// Inventory report
export async function getInventoryReport() {
  return db
    .select({
      id: products.id,
      name: products.name,
      sku: products.sku,
      price: products.price,
      stockQuantity: products.stockQuantity,
      minStockLevel: products.minStockLevel,
      trackInventory: products.trackInventory,
      reorderPoint: sql`${products.minStockLevel}`,
      stockStatus: sql`
        CASE
          WHEN ${products.trackInventory} = false THEN 'Not tracked'
          WHEN ${products.stockQuantity} <= ${products.minStockLevel} THEN 'Low stock'
          WHEN ${products.stockQuantity} = 0 THEN 'Out of stock'
          ELSE 'In stock'
        END
      `,
      lastUpdated: products.updatedAt,
      totalValue: sql`${products.price} * ${products.stockQuantity}`
    })
    .from(products)
    .orderBy(
      sql`CASE
        WHEN ${products.stockQuantity} <= ${products.minStockLevel} THEN 1
        ELSE 2
      END`,
      asc(sql`${products.name}`)
    .where(eq(products.isActive, true));
}

// 8. Search and Filtering

// Advanced product search with filters
export function searchProducts(filters: {
  query?: string;
  minPrice?: number;
  maxPrice?: number;
  category?: string[];
  minRating?: number;
  inStock?: boolean;
  sortBy?: 'name' | 'price' | 'rating' | 'created_at';
  sortOrder?: 'asc' | 'desc';
  limit?: number;
  page?: number;
}) {
  let query = db.select().from(products).where(eq(products.isActive, true));

  // Add text search
  if (filters.query) {
    query = query.where(
      or(
        ilike(products.name, `%${filters.query}%`),
        ilike(products.description, `%${filters.query}%`),
        ilike(products.shortDescription, `%${filters.query}%`)
      )
    );
  }

  // Add price range filter
  if (filters.minPrice !== undefined) {
    query = query.where(gte(products.price, filters.minPrice));
  }
  if (filters.maxPrice !== undefined) {
    query = query.where(lte(products.price, filters.maxPrice));
  }

  // Add stock filter
  if (filters.inStock !== undefined) {
    if (filters.inStock) {
      query = query.where(gt(products.stockQuantity, 0));
    } else {
      query = query.where(eq(products.stockQuantity, 0));
    }
  }

  // Add rating filter
  if (filters.minRating !== undefined) {
    query = query.where(gte(products.rating, filters.minRating));
  }

  // Add sorting
  const sortBy = filters.sortBy || 'created_at';
  const sortOrder = filters.sortOrder || 'desc';
  query = query.orderBy(
    sortOrder === 'asc' ? asc(sql`${sortBy}`) : desc(sql`${sortBy}`)
  );

  // Add pagination
  if (filters.limit) {
    query = query.limit(filters.limit);
    if (filters.page && filters.page > 1) {
      query = query.offset((filters.page - 1) * filters.limit);
    }
  }

  return query;
}

// 9. Type Definitions
import type { PgTable } from 'drizzle-orm/pg-core';
import { InferSelectModel } from 'drizzle-orm';

export type User = InferSelectModel<typeof users>;
export type Product = InferSelectModel<typeof products>;
export type Order = InferSelectModel<typeof orders>;
export type OrderItem = InferSelectModel<typeof orderItems>;
export <type T> New = Insertable<PgTable<T>>;

export {
  users,
  products,
  orders,
  orderItems,
  reviews,
  notifications,
  db,
  migrationConfig,
  getUserWithOrdersAndReviews,
  getProductWithStats,
  getOrdersWithDetails,
  getCustomerOrderHistory,
  processOrder,
  bulkUpdateProductPrices,
  getSalesReport,
  getTopPerformingProducts,
  getCustomerAnalytics,
  exportUserData,
  getInventoryReport,
  searchProducts
};