import { db } from '@packages/database';
import { users, posts } from '@packages/database/schema';
import { eq, and, or, like, desc, asc } from 'drizzle-orm';

// Select all users
const allUsers = await db.select().from(users);

// Select specific columns
const userNames = await db
  .select({ id: users.id, name: users.name })
  .from(users);

// Select with conditions
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

// ✅ Type-safe - TypeScript knows the exact shape
const user = await db.select().from(users).where(eq(users.id, userId));
// user: { id: string; name: string; email: string; ... }[]

// ✅ Compile-time error prevention
await db.select().from(users).where(eq(users.nonExistentField, 'value'));
//                                      ^^^^^^^^^^^^^^^^^^^^ TypeScript error

// ✅ Auto-completion for columns and methods
const result = await db.select({
  id: users.id,        // ✅ Auto-complete available
  name: users.name,    // ✅ Auto-complete available
  email: users.email,  // ✅ Auto-complete available
}).from(users);

// Insert single record
const newUser = await db.insert(users).values({
  email: 'john@example.com',
  name: 'John Doe',
  role: 'user',
}).returning();

console.log(newUser[0].id); // Auto-generated UUID

// Insert multiple records
const newUsers = await db.insert(users).values([
  { email: 'user1@example.com', name: 'User One' },
  { email: 'user2@example.com', name: 'User Two' },
  { email: 'user3@example.com', name: 'User Three' },
]).returning();

// Insert with conflict handling (upsert)
await db.insert(users)
  .values({ email: 'john@example.com', name: 'John Updated' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated', updatedAt: new Date() }
  });

// Select all
const allUsers = await db.select().from(users);

// Select with conditions
const adminUsers = await db
  .select()
  .from(users)
  .where(eq(users.role, 'admin'));

// Select with multiple conditions
const activeAdmins = await db
  .select()
  .from(users)
  .where(and(
    eq(users.role, 'admin'),
    eq(users.isActive, true)
  ));

// Select with OR conditions
const specialUsers = await db
  .select()
  .from(users)
  .where(or(
    eq(users.role, 'admin'),
    like(users.email, '%@company.com')
  ));

// Select specific columns
const userBasics = await db
  .select({
    id: users.id,
    name: users.name,
    email: users.email,
  })
  .from(users);

// Select with ordering
const recentUsers = await db
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(10);

// Select with pagination
const page = 1;
const pageSize = 20;
const offset = (page - 1) * pageSize;

const paginatedUsers = await db
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(pageSize)
  .offset(offset);

// Update single record
await db
  .update(users)
  .set({ 
    name: 'Updated Name',
    updatedAt: new Date()
  })
  .where(eq(users.id, userId));

// Update multiple records
await db
  .update(users)
  .set({ isActive: false })
  .where(eq(users.role, 'inactive'));

// Update with returning
const updatedUsers = await db
  .update(users)
  .set({ role: 'premium' })
  .where(like(users.email, '%@premium.com'))
  .returning();

// Conditional updates
await db
  .update(users)
  .set({ 
    lastLoginAt: new Date(),
    // Only update if current value is null
    ...(user.lastLoginAt === null && { firstLoginAt: new Date() })
  })
  .where(eq(users.id, userId));

// Delete single record
await db.delete(users).where(eq(users.id, userId));

// Delete with conditions
await db.delete(users).where(
  and(
    eq(users.isActive, false),
    lt(users.createdAt, new Date('2023-01-01'))
  )
);

// Delete with returning (to see what was deleted)
const deletedUsers = await db
  .delete(users)
  .where(eq(users.role, 'spam'))
  .returning({ id: users.id, email: users.email });

console.log(`Deleted ${deletedUsers.length} spam users`);

// Inner join
const usersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postId: posts.id,
    postTitle: posts.title,
  })
  .from(users)
  .innerJoin(posts, eq(users.id, posts.userId));

// Left join (include users even if they have no posts)
const allUsersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postId: posts.id,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

// Multiple joins
const userPostsWithCategories = await db
  .select({
    userName: users.name,
    postTitle: posts.title,
    categoryName: categories.name,
  })
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .leftJoin(categories, eq(posts.categoryId, categories.id))
  .where(eq(posts.status, 'published'));

// Subquery to find users with posts
const usersWithPostsSubquery = db
  .select({ userId: posts.userId })
  .from(posts)
  .groupBy(posts.userId);

const activeAuthors = await db
  .select()
  .from(users)
  .where(inArray(users.id, usersWithPostsSubquery));

// Correlated subquery
const usersWithPostCount = await db
  .select({
    id: users.id,
    name: users.name,
    postCount: (
      await db
        .select({ count: count() })
        .from(posts)
        .where(eq(posts.userId, users.id))
    )[0].count,
  })
  .from(users);

import { count, sum, avg, max, min } from 'drizzle-orm';

// Count records
const userCount = await db
  .select({ count: count() })
  .from(users);

// Count with conditions
const activeUserCount = await db
  .select({ count: count() })
  .from(users)
  .where(eq(users.isActive, true));

// Group by with aggregations
const postsByStatus = await db
  .select({
    status: posts.status,
    count: count(),
    avgViews: avg(posts.viewCount),
    maxViews: max(posts.viewCount),
  })
  .from(posts)
  .groupBy(posts.status);

// Having clause with groups
const prolificAuthors = await db
  .select({
    userId: posts.userId,
    userName: users.name,
    postCount: count(),
  })
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .groupBy(posts.userId, users.name)
  .having(gt(count(), 5)); // Authors with more than 5 posts

// Define relations in schema
export const usersRelations = relations(users, ({ one, many }) => ({
  profile: one(userProfiles, {
    fields: [users.id],
    references: [userProfiles.userId],
  }),
  posts: many(posts),
  subscription: one(subscriptions, {
    fields: [users.id],
    references: [subscriptions.userId],
  }),
}));

// Query with relations
const userWithProfile = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    profile: true,
    posts: {
      where: eq(posts.status, 'published'),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
    },
    subscription: {
      with: {
        plan: true,
      },
    },
  },
});

// The result is fully typed:
console.log(userWithProfile?.profile?.bio);
console.log(userWithProfile?.posts[0]?.title);
console.log(userWithProfile?.subscription?.plan?.name);

// Deep nested query
const userWithEverything = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    profile: true,
    posts: {
      with: {
        tags: {
          with: {
            tag: true,
          },
        },
        comments: {
          with: {
            author: {
              columns: { id: true, name: true },
            },
          },
          orderBy: [desc(comments.createdAt)],
          limit: 3,
        },
      },
      orderBy: [desc(posts.publishedAt)],
      limit: 10,
    },
    subscription: {
      with: {
        plan: true,
        payments: {
          orderBy: [desc(payments.createdAt)],
          limit: 5,
        },
      },
    },
  },
});

import { and, or, not, exists, between, isNull, isNotNull } from 'drizzle-orm';

// Complex AND/OR conditions
const complexQuery = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.isActive, true),
      or(
        eq(users.role, 'admin'),
        and(
          eq(users.role, 'user'),
          not(isNull(users.lastLoginAt)),
          gt(users.lastLoginAt, new Date('2024-01-01'))
        )
      )
    )
  );

// Date range queries
const recentUsers = await db
  .select()
  .from(users)
  .where(
    between(
      users.createdAt,
      new Date('2024-01-01'),
      new Date('2024-12-31')
    )
  );

// Null checks
const usersWithoutProfiles = await db
  .select()
  .from(users)
  .leftJoin(userProfiles, eq(users.id, userProfiles.userId))
  .where(isNull(userProfiles.userId));

import { like, ilike, sql } from 'drizzle-orm';

// Case-insensitive search
const searchResults = await db
  .select()
  .from(posts)
  .where(
    or(
      ilike(posts.title, `%${searchTerm}%`),
      ilike(posts.content, `%${searchTerm}%`)
    )
  );

// Full-text search (PostgreSQL)
const fullTextSearch = await db
  .select()
  .from(posts)
  .where(
    sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content}) @@ to_tsquery('english', ${searchTerm})`
  );

// Multiple word search
const multiWordSearch = await db
  .select()
  .from(posts)
  .where(
    and(
      ilike(posts.content, '%react%'),
      ilike(posts.content, '%typescript%')
    )
  );

// User query helpers
export const userQueries = {
  // Find user by email
  findByEmail: (email: string) =>
    db.select().from(users).where(eq(users.email, email)),

  // Find active users
  findActive: () =>
    db.select().from(users).where(eq(users.isActive, true)),

  // Find users by role
  findByRole: (role: 'user' | 'admin') =>
    db.select().from(users).where(eq(users.role, role)),

  // Find users with pagination
  findPaginated: (page: number, pageSize: number = 20) =>
    db.select()
      .from(users)
      .orderBy(desc(users.createdAt))
      .limit(pageSize)
      .offset((page - 1) * pageSize),

  // Count total users
  count: () =>
    db.select({ count: count() }).from(users),

  // Search users
  search: (term: string) =>
    db.select()
      .from(users)
      .where(
        or(
          ilike(users.name, `%${term}%`),
          ilike(users.email, `%${term}%`)
        )
      ),
};

// Usage
const user = await userQueries.findByEmail('john@example.com');
const adminUsers = await userQueries.findByRole('admin');
const page1Users = await userQueries.findPaginated(1, 10);

interface UserFilters {
  role?: 'user' | 'admin';
  isActive?: boolean;
  search?: string;
  createdAfter?: Date;
  createdBefore?: Date;
}

export const findUsersWithFilters = async (filters: UserFilters) => {
  let query = db.select().from(users);

  const conditions = [];

  if (filters.role) {
    conditions.push(eq(users.role, filters.role));
  }

  if (filters.isActive !== undefined) {
    conditions.push(eq(users.isActive, filters.isActive));
  }

  if (filters.search) {
    conditions.push(
      or(
        ilike(users.name, `%${filters.search}%`),
        ilike(users.email, `%${filters.search}%`)
      )
    );
  }

  if (filters.createdAfter) {
    conditions.push(gte(users.createdAt, filters.createdAfter));
  }

  if (filters.createdBefore) {
    conditions.push(lte(users.createdAt, filters.createdBefore));
  }

  if (conditions.length > 0) {
    query = query.where(and(...conditions));
  }

  return query.orderBy(desc(users.createdAt));
};

// Usage
const results = await findUsersWithFilters({
  role: 'user',
  isActive: true,
  search: 'john',
  createdAfter: new Date('2024-01-01'),
});

import { placeholder } from 'drizzle-orm';

// Prepared statements for frequently used queries
const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, placeholder('userId')))
  .prepare();

const getUserPosts = db
  .select()
  .from(posts)
  .where(eq(posts.userId, placeholder('userId')))
  .orderBy(desc(posts.createdAt))
  .limit(placeholder('limit'))
  .prepare();

// Execute prepared statements
const user = await getUserById.execute({ userId: 'user-id' });
const userPosts = await getUserPosts.execute({ 
  userId: 'user-id', 
  limit: 10 
});

// ✅ Good - Only select needed columns
const efficientQuery = await db
  .select({
    userName: users.name,
    postTitle: posts.title,
    postViews: posts.viewCount,
  })
  .from(users)
  .innerJoin(posts, eq(users.id, posts.userId))
  .where(eq(posts.status, 'published'))
  .orderBy(desc(posts.viewCount))
  .limit(10);

// ❌ Avoid - Selecting all columns from large tables
const inefficientQuery = await db
  .select()
  .from(users)
  .innerJoin(posts, eq(users.id, posts.userId))
  .innerJoin(userProfiles, eq(users.id, userProfiles.userId));

// Batch inserts
const batchInsertUsers = async (userData: CreateUser[]) => {
  // Process in chunks to avoid memory issues
  const chunkSize = 1000;
  const results = [];

  for (let i = 0; i < userData.length; i += chunkSize) {
    const chunk = userData.slice(i, i + chunkSize);
    const inserted = await db.insert(users).values(chunk).returning();
    results.push(...inserted);
  }

  return results;
};

// Batch updates
const batchUpdateUsers = async (updates: { id: string; data: Partial<User> }[]) => {
  const promises = updates.map(({ id, data }) =>
    db.update(users)
      .set({ ...data, updatedAt: new Date() })
      .where(eq(users.id, id))
  );

  await Promise.all(promises);
};

import { z } from 'zod';

// Validate input before queries
const searchSchema = z.object({
  term: z.string().min(1).max(100),
  role: z.enum(['user', 'admin']).optional(),
  limit: z.number().min(1).max(100).default(20),
});

export const searchUsers = async (input: unknown) => {
  // Validate input
  const validated = searchSchema.parse(input);

  // Safe to use in query
  return db.select()
    .from(users)
    .where(
      and(
        ilike(users.name, `%${validated.term}%`),
        validated.role ? eq(users.role, validated.role) : undefined
      )
    )
    .limit(validated.limit);
};

// Row-level security helper
export const getUserData = async (requestingUserId: string, targetUserId: string) => {
  // Check if user can access this data
  const requestingUser = await db
    .select({ role: users.role })
    .from(users)
    .where(eq(users.id, requestingUserId))
    .then(rows => rows[0]);

  if (!requestingUser) {
    throw new Error('User not found');
  }

  // Admin can access any user, users can only access their own data
  if (requestingUser.role !== 'admin' && requestingUserId !== targetUserId) {
    throw new Error('Insufficient permissions');
  }

  return db.select().from(users).where(eq(users.id, targetUserId));
};

// ✅ Good - Organized query functions
// queries/users.ts
export const userQueries = {
  findById: (id: string) => /* ... */,
  findByEmail: (email: string) => /* ... */,
  findActive: () => /* ... */,
  search: (term: string) => /* ... */,
};

// queries/posts.ts
export const postQueries = {
  findPublished: () => /* ... */,
  findByAuthor: (authorId: string) => /* ... */,
  findRecent: (limit: number) => /* ... */,
};

// ❌ Avoid - Inline queries in components
const posts = await db.select().from(posts).where(eq(posts.userId, userId));

export const safeQueryUser = async (userId: string) => {
  try {
    const user = await db
      .select()
      .from(users)
      .where(eq(users.id, userId));

    if (user.length === 0) {
      return { success: false, error: 'User not found' };
    }

    return { success: true, data: user[0] };
  } catch (error) {
    console.error('Database query failed:', error);
    return { success: false, error: 'Database error' };
  }
};

// ✅ Good - Explicit typing
interface UserWithPostCount {
  id: string;
  name: string;
  email: string;
  postCount: number;
}

export const getUsersWithPostCounts = async (): Promise<UserWithPostCount[]> => {
  return db
    .select({
      id: users.id,
      name: users.name,
      email: users.email,
      postCount: count(posts.id),
    })
    .from(users)
    .leftJoin(posts, eq(users.id, posts.userId))
    .groupBy(users.id, users.name, users.email);
};

// ❌ Avoid - Implicit any types
export const getUsers = async () => {
  return db.select().from(users); // Return type is inferred
};