// Drizzle - SQLに近い直感的な記法
const users = await db
  .select({
    id: users.id,
    name: users.name,
    postCount: count(posts.id)
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .where(eq(users.isActive, true))
  .groupBy(users.id);

// Prisma - 独自のオブジェクト記法
const users = await prisma.user.findMany({
  where: { isActive: true },
  include: {
    _count: {
      select: { posts: true }
    }
  }
});

// Supabase-js - チェーンメソッド
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts(count)
  `)
  .eq('is_active', true);

// schema/users.ts
import { pgTable, text, boolean, timestamp, uuid } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').default(false),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

// 投稿数とともにアクティブユーザーを取得
async function getActiveUsersWithStats() {
  const result = await db
    .select({
      userId: users.id,
      userName: users.name,
      email: users.email,
      totalPosts: count(posts.id),
      publishedPosts: count(
        case_().when(posts.published, 1).else(null)
      ),
      latestPostDate: max(posts.createdAt),
    })
    .from(users)
    .leftJoin(posts, eq(users.id, posts.userId))
    .where(eq(users.isActive, true))
    .groupBy(users.id)
    .having(gt(count(posts.id), 0))
    .orderBy(desc(count(posts.id)));

  return result;
}

// ユーザーと初期投稿を同時に作成
async function createUserWithWelcomePost(userData: NewUser) {
  return await db.transaction(async (tx) => {
    // ユーザー作成
    const [newUser] = await tx
      .insert(users)
      .values(userData)
      .returning();

    // ウェルカム投稿を作成
    const [welcomePost] = await tx
      .insert(posts)
      .values({
        userId: newUser.id,
        title: 'Welcome to our platform!',
        content: `Hello ${newUser.name}, welcome aboard!`,
        published: true,
      })
      .returning();

    return { user: newUser, post: welcomePost };
  });
}

// Claude Codeへの指示例
"ユーザーの最新10件の投稿を取得するクエリを書いて"

// 生成されるコード
const recentPosts = await db
  .select()
  .from(posts)
  .where(eq(posts.userId, userId))
  .orderBy(desc(posts.createdAt))
  .limit(10);

// Step 1: 基本クエリから開始
const allUsers = await db.select().from(users);

// Step 2: 条件を追加
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

// Step 3: JOINを追加
const usersWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .where(eq(users.isActive, true));

// Step 4: 集計を追加
const userStats = await db
  .select({
    user: users,
    postCount: count(posts.id)
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .groupBy(users.id);

// TypeScriptの型エラーが具体的
db.select()
  .from(users)
  .where(eq(users.email, 123)); // ❌ Type error: number is not assignable to string

// SQLエラーも理解しやすい
db.select()
  .from(users)
  .where(eq(users.nonExistentColumn, 'value')); // ❌ Property 'nonExistentColumn' does not exist

// 複数テーブルを結合した統計情報の取得
const analytics = await db
  .select({
    date: sql<string>`DATE(${orders.createdAt})`,
    totalOrders: count(orders.id),
    uniqueCustomers: countDistinct(orders.customerId),
    totalRevenue: sum(orderItems.price),
    avgOrderValue: avg(orderItems.price),
  })
  .from(orders)
  .leftJoin(orderItems, eq(orders.id, orderItems.orderId))
  .leftJoin(customers, eq(orders.customerId, customers.id))
  .where(gte(orders.createdAt, lastMonth))
  .groupBy(sql`DATE(${orders.createdAt})`);

function buildDynamicQuery(filters: FilterOptions) {
  let query = db.select().from(products);
  
  const conditions = [];
  
  if (filters.category) {
    conditions.push(eq(products.category, filters.category));
  }
  
  if (filters.minPrice) {
    conditions.push(gte(products.price, filters.minPrice));
  }
  
  if (filters.inStock) {
    conditions.push(gt(products.stock, 0));
  }
  
  if (conditions.length > 0) {
    query = query.where(and(...conditions));
  }
  
  if (filters.sortBy) {
    query = query.orderBy(
      filters.sortOrder === 'desc' 
        ? desc(products[filters.sortBy])
        : asc(products[filters.sortBy])
    );
  }
  
  return query;
}

// Window関数を使った高度なクエリ
const rankedProducts = await db.execute(sql`
  WITH RankedProducts AS (
    SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank
    FROM products
  )
  SELECT * FROM RankedProducts WHERE rank <= 5
`);

# 必要なパッケージのインストール
npm install drizzle-orm postgres
npm install -D drizzle-kit @types/pg

# 設定ファイルの作成
touch drizzle.config.ts

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema/*',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const connectionString = process.env.DATABASE_URL!;
const sql = postgres(connectionString);

export const db = drizzle(sql, { schema });

# マイグレーションファイルの生成
npx drizzle-kit generate:pg

# マイグレーションの実行
npx drizzle-kit push:pg