Drizzle Integration

/**
 * Main Drizzle ORM module re-export
 * Provides access to core Drizzle functionality including SQL template literals,
 * query builders, and utility functions
 */
import * as drizzle from "@payloadcms/db-sqlite/drizzle";

/**
 * SQL template literal function for raw query construction
 * Provides type-safe parameter binding and query building
 */
import { sql } from "@payloadcms/db-sqlite";

import { sql } from "@payloadcms/db-sqlite";
import * as drizzle from "@payloadcms/db-sqlite/drizzle";

// In a migration or custom operation
export async function customQuery({ db }: { db: any }) {
  // Raw SQL with parameters
  const result = await db.run(sql`
    SELECT COUNT(*) as total 
    FROM posts 
    WHERE status = ${'published'}
  `);
  
  // Using Drizzle query builder methods
  const { eq, and, or } = drizzle;
  
  // Complex conditional queries
  const complexQuery = sql`
    SELECT * FROM users 
    WHERE ${and(
      eq(sql`active`, true),
      or(
        eq(sql`role`, 'admin'),
        eq(sql`role`, 'editor')
      )
    )}
  `;
}

/**
 * SQLite-specific Drizzle ORM functionality
 * Includes table creation, column definitions, and SQLite-specific features
 */
import * as sqliteCore from "@payloadcms/db-sqlite/drizzle/sqlite-core";

import * as sqliteCore from "@payloadcms/db-sqlite/drizzle/sqlite-core";

// In schema hooks or custom table definitions
const { 
  sqliteTable, 
  text, 
  integer, 
  real, 
  blob,
  primaryKey,
  foreignKey,
  unique,
  index
} = sqliteCore;

// Custom table definition
const customTable = sqliteTable('custom_analytics', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  event_name: text('event_name').notNull(),
  user_id: integer('user_id').references(() => usersTable.id),
  timestamp: integer('timestamp').notNull(),
  metadata: text('metadata', { mode: 'json' }),
  value: real('value'),
}, (table) => ({
  // Composite indexes
  eventUserIdx: index('event_user_idx').on(table.event_name, table.user_id),
  timestampIdx: index('timestamp_idx').on(table.timestamp),
  // Unique constraints
  uniqueEventUser: unique('unique_event_user').on(table.event_name, table.user_id, table.timestamp),
}));

/**
 * LibSQL-specific Drizzle integration
 * Provides access to LibSQL client features and remote database functionality
 */
import * as libsql from "@payloadcms/db-sqlite/drizzle/libsql";

import * as libsql from "@payloadcms/db-sqlite/drizzle/libsql";

// Access LibSQL-specific features
const { createClient } = libsql;

// Custom LibSQL client configuration (if needed)
export function createCustomClient() {
  return createClient({
    url: process.env.TURSO_DATABASE_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN!,
    // LibSQL-specific options
    syncUrl: process.env.TURSO_SYNC_URL,
    syncInterval: 5000,
  });
}

/**
 * Drizzle relations system for defining table relationships
 * Enables type-safe joins and relationship queries
 */
import * as relations from "@payloadcms/db-sqlite/drizzle/relations";

import * as relations from "@payloadcms/db-sqlite/drizzle/relations";
import { sqliteTable, integer, text } from "@payloadcms/db-sqlite/drizzle/sqlite-core";

const { relations, one, many } = relations;

// Define tables
const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

const posts = sqliteTable('posts', {
  id: integer('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').references(() => users.id),
});

// Define relations
const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

// Use in queries (within migrations or hooks)
export async function getPostsWithAuthors({ db }: { db: any }) {
  return await db.query.posts.findMany({
    with: {
      author: true,
    },
  });
}

/**
 * Advanced query building utilities
 * Provides access to Drizzle's full query construction capabilities
 */
interface DrizzleQueryBuilder {
  /** Conditional operators for WHERE clauses */
  eq: (column: any, value: any) => any;
  ne: (column: any, value: any) => any;
  gt: (column: any, value: any) => any;
  gte: (column: any, value: any) => any;
  lt: (column: any, value: any) => any;
  lte: (column: any, value: any) => any;
  like: (column: any, pattern: string) => any;
  notLike: (column: any, pattern: string) => any;
  isNull: (column: any) => any;
  isNotNull: (column: any) => any;
  inArray: (column: any, values: any[]) => any;
  notInArray: (column: any, values: any[]) => any;
  
  /** Logical operators */
  and: (...conditions: any[]) => any;
  or: (...conditions: any[]) => any;
  not: (condition: any) => any;
  
  /** Aggregation functions */
  count: (column?: any) => any;
  sum: (column: any) => any;
  avg: (column: any) => any;
  min: (column: any) => any;
  max: (column: any) => any;
}

import { sql } from "@payloadcms/db-sqlite";
import { eq, and, or, count, sum } from "@payloadcms/db-sqlite/drizzle";

// In custom operations or hooks
export async function advancedQueries({ db, schema }: { db: any, schema: any }) {
  const { posts, users } = schema.tables;
  
  // Complex filtering
  const activeUserPosts = await db
    .select({
      id: posts.id,
      title: posts.title,
      authorName: users.name,
    })
    .from(posts)
    .innerJoin(users, eq(posts.authorId, users.id))
    .where(
      and(
        eq(posts.status, 'published'),
        or(
          eq(users.role, 'admin'),
          eq(users.role, 'editor')
        ),
        isNotNull(posts.featuredImage)
      )
    )
    .orderBy(posts.createdAt)
    .limit(10);
  
  // Aggregation queries
  const postStats = await db
    .select({
      authorId: posts.authorId,
      authorName: users.name,
      postCount: count(posts.id),
      totalViews: sum(posts.viewCount),
      avgRating: avg(posts.rating),
    })
    .from(posts)
    .innerJoin(users, eq(posts.authorId, users.id))
    .groupBy(posts.authorId, users.name)
    .having(gt(count(posts.id), 5));
  
  // Subqueries
  const topAuthors = db
    .select({ id: users.id })
    .from(users)
    .where(
      inArray(
        users.id,
        db
          .select({ authorId: posts.authorId })
          .from(posts)
          .groupBy(posts.authorId)
          .having(gt(count(posts.id), 10))
      )
    );
  
  return { activeUserPosts, postStats, topAuthors };
}

/**
 * Transaction management utilities
 * Provides low-level transaction control for complex operations
 */
interface DrizzleTransactions {
  /** Execute operations within a transaction */
  transaction: <T>(
    callback: (tx: any) => Promise<T>
  ) => Promise<T>;
}

import { sql } from "@payloadcms/db-sqlite";

// Complex transaction with multiple operations
export async function complexTransaction({ db }: { db: any }) {
  return await db.transaction(async (tx) => {
    // Create user
    const [user] = await tx
      .insert(users)
      .values({ name: 'John Doe', email: 'john@example.com' })
      .returning();
    
    // Create profile
    await tx
      .insert(profiles)
      .values({ 
        userId: user.id, 
        bio: 'Software developer',
        avatar: 'avatar.jpg'
      });
    
    // Update statistics
    await tx.run(sql`
      UPDATE site_stats 
      SET user_count = user_count + 1,
          updated_at = CURRENT_TIMESTAMP
    `);
    
    // Send welcome email (external service)
    await sendWelcomeEmail(user.email);
    
    return user;
  });
}

/**
 * SQLite-specific operators and functions
 * Optimized for SQLite's case-insensitive LIKE behavior
 */
interface SQLiteOperators {
  /** Case-insensitive LIKE (native SQLite behavior) */
  like: (column: any, pattern: string) => any;
  /** Case-insensitive NOT LIKE */
  notLike: (column: any, pattern: string) => any;
  /** JSON operations for JSON columns */
  jsonExtract: (column: any, path: string) => any;
  jsonArrayLength: (column: any) => any;
}

import { like, notLike } from "@payloadcms/db-sqlite/drizzle";
import { sql } from "@payloadcms/db-sqlite";

// SQLite-optimized queries
export async function searchOperations({ db, schema }: { db: any, schema: any }) {
  const { posts } = schema.tables;
  
  // Case-insensitive search (SQLite's native LIKE behavior)
  const searchResults = await db
    .select()
    .from(posts)
    .where(
      or(
        like(posts.title, '%javascript%'),
        like(posts.content, '%typescript%')
      )
    );
  
  // JSON operations
  const postsWithTags = await db
    .select({
      id: posts.id,
      title: posts.title,
      tagCount: sql`json_array_length(${posts.metadata}, '$.tags')`,
      firstTag: sql`json_extract(${posts.metadata}, '$.tags[0]')`,
    })
    .from(posts)
    .where(sql`json_extract(${posts.metadata}, '$.tags') IS NOT NULL`);
  
  return { searchResults, postsWithTags };
}