import { createClient, container, field } from 'cosmosql';

const users = container('users', {
  id: field.string(),
  email: field.string(),
  name: field.string()
}).partitionKey('email');

const db = await createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'myapp',
  mode: 'auto-create'
}).withContainers({ users });

// Point read (1 RU - cheapest possible)
const user = await db.users.findUnique({
  where: { id: 'user_123', email: 'john@example.com' }
});

// Query within partition (5 RU - still cheap)
const posts = await db.posts.findMany({
  partitionKey: 'user_123',
  where: { isPublished: true }
});

// TypeScript knows all return types automatically

const addPreferences = defineMigration({
  version: 1,
  name: 'add-user-preferences',
  async up({ db, logger }) {
    await db.users.updateMany({
      where: {},
      data: (doc) => ({ preferences: { theme: 'light' } }),
      enableCrossPartitionQuery: true
    });
  }
});

await db.migrations.apply({ target: 'latest', confirm: true });

// Health check
const health = await db.management.healthCheck();
console.log(`Health: ${health.overallHealth}`);

// Schema diff
const diff = await db.management.diffSchema();
if (diff.requiresAction) {
  console.log('Schema drift detected!');
}

// Update many documents
await db.users.updateMany({
  where: { isActive: false },
  data: { status: 'archived' },
  enableCrossPartitionQuery: true,
  onProgress: (stats) => console.log(`${stats.percentage}%`)
});

npm install cosmosql

git clone https://github.com/Stoffberg/cosmosql.git
cd cosmosql

# Using Bun (recommended)
bun install

# Or using npm
npm install

bun run build

# Or
npm run build

# All tests
bun test

# Unit tests only
bun test tests/unit

# Integration tests only
bun test tests/integration

COSMOS_ENDPOINT=https://your-account.documents.azure.com:443/
COSMOS_KEY=your-key
COSMOS_DATABASE=test-db

# Check for issues
bun run lint

# Auto-fix issues
bun run format

cosmosql/
├── src/
│   ├── client/          # Client creation and configuration
│   ├── container/       # Container schema definitions
│   ├── query/           # Query builder and execution
│   ├── types/           # Type definitions and inference
│   └── utils/           # Utilities and helpers
├── tests/
│   ├── unit/            # Unit tests
│   └── integration/     # Integration tests with real CosmosDB
└── package.json

git checkout -b feature/your-feature-name
# or
git checkout -b fix/your-bug-fix

bun test
bun run lint

git commit -m "feat: add new feature description"

git push origin feature/your-feature-name

// Offset pagination (LIMIT/OFFSET)
const page1 = await db.posts.findMany({
  partitionKey: 'user_123',
  take: 20,
  skip: 0
});

const page2 = await db.posts.findMany({
  partitionKey: 'user_123',
  take: 20,
  skip: 20
});

// Single-partition batch operations only
await db.posts.createMany({
  data: [
    { id: 'post_1', userId: 'user_123', title: 'Post 1' },
    { id: 'post_2', userId: 'user_123', title: 'Post 2' }
  ],
  partitionKey: 'user_123' // All documents must share partition key
});

// Geospatial example (when supported via raw SQL)
const result = await db.locations.query({
  sql: `
    SELECT * FROM c 
    WHERE ST_DISTANCE(c.location, {
      'type': 'Point',
      'coordinates': [@lng, @lat]
    }) < @distance
  `,
  parameters: [
    { name: '@lng', value: -122.4194 },
    { name: '@lat', value: 37.7749 },
    { name: '@distance', value: 5000 }
  ]
});

// Using Azure SDK for change feed
import { CosmosClient } from '@azure/cosmos';

const client = new CosmosClient(connectionString);
const container = client.database('mydb').container('users');

// Use SDK for change feed
const changeFeedProcessor = container.items
  .changeFeed({
    startFromBeginning: false
  })
  .on('change', (changes) => {
    // Handle changes
  });

const productionDb = await createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'production',
  mode: 'verify'
}).withContainers({ users, posts });

const analyticsDb = await createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'analytics',
  mode: 'verify'
}).withContainers({ events, metrics });

import { CosmosClient } from '@azure/cosmos';

const client = new CosmosClient(connectionString);
const database = client.database('mydb');
const container = database.container('users');

import { createClient, container, field } from 'cosmosql';

// Define your schema
const users = container('users', {
  id: field.string(),
  email: field.string(),
  name: field.string()
}).partitionKey('email');

// Create typed client (async - validates/creates containers)
const db = await createClient({
  connectionString,
  database: 'mydb',
  mode: 'auto-create'
}).withContainers({ users });

const { resources } = await container.items
  .query({
    query: 'SELECT * FROM c WHERE c.id = @id AND c.email = @email',
    parameters: [
      { name: '@id', value: 'user_123' },
      { name: '@email', value: 'john@example.com' }
    ]
  })
  .fetchAll();

const user = resources[0]; // No type safety
// Type: any - could be undefined, wrong shape, etc.

// Point read (most efficient)
const user = await db.users.findUnique({
  where: {
    id: 'user_123',
    email: 'john@example.com' // Partition key required
  }
});

// Type: { id: string; email: string; name: string } | null
// TypeScript knows the exact shape and nullability

const { resources } = await container.items
  .query({
    query: 'SELECT * FROM c WHERE c.isActive = @active ORDER BY c.createdAt DESC',
    parameters: [{ name: '@active', value: true }]
  })
  .fetchAll();

// No type safety, no partition key enforcement

// Partition-scoped query (efficient)
const activeUsers = await db.users.findMany({
  partitionKey: 'john@example.com', // Required!
  where: {
    isActive: true
  },
  orderBy: {
    createdAt: 'desc'
  }
});

// Type: Array<{ id: string; email: string; ... }>
// Partition key is enforced at compile time

const { resource: newUser } = await container.items.create({
  id: 'user_123',
  email: 'john@example.com',
  name: 'John Doe'
});

// No type checking - could have typos, missing fields, etc.

const newUser = await db.users.create({
  data: {
    id: 'user_123',
    email: 'john@example.com',
    name: 'John Doe'
    // TypeScript ensures all required fields are present
    // Autocomplete helps you discover available fields
  }
});

// Type: { id: string; email: string; name: string }
// Fully typed result

const { resource: updated } = await container
  .item('user_123', 'john@example.com') // ID and partition key
  .replace({
    id: 'user_123',
    email: 'john@example.com',
    name: 'John Updated',
    age: 30 // Could forget fields, wrong types, etc.
  });

// Update specific fields (partial update)
const updated = await db.users.update({
  where: {
    id: 'user_123',
    email: 'john@example.com'
  },
  data: {
    name: 'John Updated',
    age: 30
    // Only include fields you want to update
    // TypeScript ensures types match schema
  }
});

// Type: { id: string; email: string; name: string; age: number }

await container
  .item('user_123', 'john@example.com')
  .delete();

await db.users.delete({
  where: {
    id: 'user_123',
    email: 'john@example.com' // Partition key required
  }
});

// Type-safe: can't delete without partition key

try {
  await container.items.create(user);
} catch (error: any) {
  if (error.code === 409) {
    // Conflict
  } else if (error.code === 429) {
    // Rate limited
  }
  // Error types are loose, easy to miss cases
}

import { CosmosError } from 'cosmosql';

try {
  await db.users.create({ data: user });
} catch (error) {
  if (error instanceof CosmosError) {
    switch (error.code) {
      case 409: // Conflict
      case 429: // Rate limited
      // Type-safe error handling
    }
  }
}

// Easy to accidentally do expensive cross-partition query
const { resources } = await container.items
  .query('SELECT * FROM c WHERE c.isActive = true')
  .fetchAll();
// This scans ALL partitions - expensive!

// Type system prevents accidental cross-partition queries
const users = await db.users.findMany({
  enableCrossPartitionQuery: true, // Must explicitly opt in
  where: { isActive: true }
});

// You're forced to think about the cost

// ❌ BAD: Fetch entire document
const user = await db.users.findUnique({
  where: { id: 'user_123', email: 'john@example.com' }
});
// Returns all fields

// ✅ GOOD: Fetch only what you need
const user = await db.users.findUnique({
  where: { id: 'user_123', email: 'john@example.com' },
  select: { name: true, email: true }
});
// Smaller payload = faster transfer = lower latency

const users = container('users', {
  id: field.string(),
  email: field.string(),
  name: field.string(),
  metadata: field.object({ /* large nested object */ })
}).partitionKey('email')
  .indexingPolicy({
    automatic: true,
    includedPaths: [
      '/id/?',
      '/email/?',
      '/name/?'
    ],
    excludedPaths: [
      '/metadata/*' // Don't index large nested objects
    ]
  });

// Find all active users
const users = await findWhere({ isActive: true });

// ❌ TypeScript won't compile this
const users = await db.users.findMany({
  where: { isActive: true }
  // Error: Missing required property 'partitionKey'
});

// ✅ Forces you to be explicit
const users = await db.users.findMany({
  partitionKey: 'user@example.com', // Scans one partition
  where: { isActive: true }
});

// ✅ Or opt-in to cross-partition (expensive) queries
const users = await db.users.findMany({
  enableCrossPartitionQuery: true, // "I know this is expensive"
  where: { isActive: true }
});

// ✅ Good: email addresses
const users = container('users', schema).partitionKey('email');

// ❌ Bad: boolean status
const users = container('users', schema).partitionKey('isActive');
// Only 2 values → only 2 partitions → poor distribution

// ✅ Good: user IDs spread evenly
const posts = container('posts', schema).partitionKey('userId');

// ⚠️ Risky: category (one category could be huge)
const products = container('products', schema).partitionKey('category');
// "Electronics" might be 80% of your data → hot partition

// If you always query by user
const posts = container('posts', schema).partitionKey('userId'); // ✅

// If you always query by date
const metrics = container('metrics', schema).partitionKey('timestamp'); // ✅

// If you query by multiple fields
const orders = container('orders', schema).partitionKey('customerId'); // ✅

// Partition-scoped queries
1M queries × 3 RU = 3M RU/month ≈ $72/month

// Cross-partition queries  
1M queries × 100 RU = 100M RU/month ≈ $2,400/month

import { container, field } from 'cosmosql';

const posts = container('posts', {
  // Required fields
  id: field.string(),
  userId: field.string(),
  title: field.string(),
  
  // Optional fields
  subtitle: field.string().optional(),
  
  // Defaults
  viewCount: field.number().default(0),
  isPublished: field.boolean().default(false),
  
  // Arrays
  tags: field.array(field.string()),
  
  // Nested objects
  metadata: field.object({
    source: field.string(),
    version: field.number()
  }).optional(),
  
  // TTL (auto-delete after N seconds)
  ttl: field.number().optional()
}).partitionKey('userId');

// Get TypeScript type
type Post = typeof posts.infer;

import { createClient } from 'cosmosql';

const db = await createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'myapp',
  mode: 'auto-create' // Validates and creates containers
}).withContainers({
  users,
  posts,
  comments
});

// Now you have typed access
db.users     // ContainerClient<User>
db.posts     // ContainerClient<Post>
db.comments  // ContainerClient<Comment>

const prodDb = await createClient({...}).withContainers({users});
const analyticsDb = await createClient({...}).withContainers({events});

const documents = container('documents', {
  id: field.string(),
  tenantId: field.string(),
  organizationId: field.string(),
  name: field.string(),
  content: field.string()
}).partitionKey('tenantId', 'organizationId'); // Composite key

// Querying requires both parts
const docs = await db.documents.findMany({
  partitionKey: ['tenant_1', 'org_a'], // Array for composite keys
  where: { name: { startsWith: 'Report' } }
});

npm install cosmosql

import { createClient, container, field } from 'cosmosql';
import dotenv from 'dotenv';

dotenv.config();

// Step 1: Define your schema
const users = container('users', {
  id: field.string(),
  email: field.string(),
  name: field.string(),
  age: field.number(),
  isActive: field.boolean().default(true),
  createdAt: field.date()
}).partitionKey('email');

// Step 2: Create a client (async - validates and creates containers)
const db = await createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'myapp',
  mode: 'auto-create' // Creates database/containers if missing (dev default)
}).withContainers({ users });

// Step 3: Create a user
async function main() {
  const newUser = await db.users.create({
    data: {
      id: 'user_123',
      email: 'john@example.com',
      name: 'John Doe',
      age: 30,
      createdAt: new Date()
      // isActive automatically true (default)
    }
  });
  console.log('Created user:', newUser.name);
  
  // Step 4: Query by ID + partition key (1 RU - cheapest)
  const user = await db.users.findUnique({
    where: {
      id: 'user_123',
      email: 'john@example.com' // Partition key required!
    }
  });
  console.log('Found user:', user?.name);
}

main().catch(console.error);

Created user: John Doe
Found user: John Doe

const users = container('users', {
  id: field.string(),
  email: field.string(),
  // ...
}).partitionKey('email');

const db = await createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'myapp',
  mode: 'auto-create' // 'auto-create' | 'verify' | 'skip'
}).withContainers({ users });

const newUser = await db.users.create({ data: { ... } });

const user = await db.users.findUnique({
  where: { id: 'user_123', email: 'john@example.com' }
});

const posts = container('posts', {
  id: field.string(),
  userId: field.string(),
  title: field.string(),
  content: field.string(),
  publishedAt: field.date().optional()
}).partitionKey('userId');

COSMOS_CONNECTION_STRING="AccountEndpoint=https://..."

import { createClient, container, field } from 'cosmosql';
import dotenv from 'dotenv';

dotenv.config();

const posts = container('posts', {
  id: field.string(),
  userId: field.string(),
  title: field.string(),
  content: field.string(),
  publishedAt: field.date().optional()
}).partitionKey('userId');

const db = await createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'myapp',
  mode: process.env.NODE_ENV === 'production' ? 'verify' : 'auto-create'
}).withContainers({ posts });

async function main() {
  // Create a post
  const post = await db.posts.create({
    data: {
      id: 'post_123',
      userId: 'user_456',
      title: 'Hello CosmosQL',
      content: 'This is my first post',
      publishedAt: new Date()
    }
  });
  console.log('Created:', post.title);
  
  // Query posts for a user
  const userPosts = await db.posts.findMany({
    partitionKey: 'user_456',
    where: { publishedAt: { ne: null } }
  });
  console.log(`Found ${userPosts.length} published posts`);
}

main().catch(console.error);

const users = container('users', {
  id: field.string(),
  email: field.string(),
  name: field.string()
})
  .partitionKey('email')
  .throughput(400) // Optional: Set RU/s (auto-scale also available)
  .indexing({       // Optional: Configure indexing policy
    automatic: true,
    excludePaths: ['/largeField/?'] // Exclude large fields from indexing
  });

const db = await createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'myapp',
  mode: 'auto-create' // Updates indexing in auto-create mode
}).withContainers({ users });

// Schema says: partitionKey('email')
// Existing container has: partitionKey('id')
// Throws: "Partition key mismatch: expected '/email', found '/id'"
//         "Cannot modify partition key. Delete container or use different name."

AccountEndpoint=https://your-account.documents.azure.com:443/;AccountKey=your-key==;

// ✅ Good: User emails (high cardinality, even distribution)
const users = container('users', schema).partitionKey('email');

// ✅ Good: UserId for user-specific data
const posts = container('posts', schema).partitionKey('userId');

// ❌ Bad: Boolean field (only 2 values, uneven distribution)
const users = container('users', schema).partitionKey('isActive');

// ❌ Bad: Status with few values
const orders = container('orders', schema).partitionKey('status');

const documents = container('documents', {
  id: field.string(),
  tenantId: field.string(),
  organizationId: field.string(),
  name: field.string(),
  content: field.string()
}).partitionKey('tenantId', 'organizationId'); // Composite key

// Querying requires both parts
const docs = await db.documents.findMany({
  partitionKey: ['tenant_1', 'org_a'], // Array for composite keys
  where: { name: { startsWith: 'Report' } }
});

const sessions = container('sessions', {
  id: field.string(),
  userId: field.string(),
  token: field.string(),
  createdAt: field.date(),
  ttl: field.number() // Seconds until auto-delete
}).partitionKey('userId');

// Create session that expires in 1 hour
await db.sessions.create({
  data: {
    id: 'session_123',
    userId: 'user_123',
    token: 'abc...',
    createdAt: new Date(),
    ttl: 3600 // 1 hour in seconds
  }
});

// CosmosDB automatically deletes after 1 hour

const result = await db.users.query<{ name: string; age: number }>({
  sql: `
    SELECT c.name, c.age 
    FROM c 
    WHERE c.age > @minAge 
    AND c.isActive = @active
  `,
  parameters: [
    { name: '@minAge', value: 18 },
    { name: '@active', value: true }
  ],
  partitionKey: 'john@example.com' // Optional: for partition-scoped query
});

// Type: Array<{ name: string; age: number }>

import { CosmosError, isCosmosError } from 'cosmosql';

try {
  await db.users.create({ data: user });
} catch (error) {
  if (isCosmosError(error)) {
    // Type-safe error handling
    switch (error.statusCode) {
      case 400: // Bad Request
        console.error('Invalid request:', error.message);
        break;
      case 401: // Unauthorized
        console.error('Authentication failed - check your connection string');
        break;
      case 403: // Forbidden
        console.error('Access denied - check permissions');
        break;
      case 404: // Not Found
        console.error('Container or database not found');
        break;
      case 409: // Conflict (duplicate ID)
        console.error('Document already exists with this ID');
        break;
      case 412: // Precondition Failed (ETag mismatch)
        console.error('Document was modified, please retry');
        break;
      case 429: // Too Many Requests (rate limit)
        console.error('Rate limited, retry after:', error.retryAfter, 'seconds');
        // CosmosError includes retryAfter property
        break;
      case 500: // Internal Server Error
        console.error('CosmosDB service error:', error.message);
        break;
      default:
        console.error('CosmosDB error:', error.code, error.message);
    }
  } else {
    // Not a CosmosDB error, rethrow
    throw error;
  }
}

class CosmosError extends Error {
  statusCode: number;      // HTTP status code (400, 404, 429, etc.)
  code: string;            // CosmosDB error code
  message: string;         // Error message
  retryAfter?: number;     // Seconds to wait before retry (for 429 errors)
}

const db = await createClient({
  connectionString: '...',
  database: 'myapp',
  mode: 'verify', // Production: fail-fast on misconfiguration
  retryOptions: {
    maxRetries: 3,        // Maximum retry attempts
    initialDelay: 100,    // Initial delay in ms
    maxDelay: 5000        // Maximum delay in ms
  }
}).withContainers({ users });

// Automatically retries with exponential backoff on 429 errors
// Uses retryAfter from CosmosDB response when available

import { createClient, container, field } from 'cosmosql';

const orderSchema = container('orders', {
  id: field.string(),
  customerId: field.string(),
  amount: field.number(),
  status: field.string(),
}).partitionKey('customerId');

const client = createClient({
  connectionString: process.env.COSMOS_CONNECTION_STRING!,
  database: 'mydb',
});

const db = await client.withContainers({ orders: orderSchema });

// Count orders for a customer
const count = await db.orders.count({
  partitionKey: 'customer-123',
});
// Result: 42

// Count only completed orders
const completedCount = await db.orders.count({
  partitionKey: 'customer-123',
  where: {
    status: 'completed',
  },
});

// Count with complex filters
const recentHighValueCount = await db.orders.count({
  partitionKey: 'customer-123',
  where: {
    status: 'completed',
    amount: { gte: 100 },
    createdAt: { gte: new Date('2024-01-01') },
  },
});

// Count ALL orders across all customers
const totalOrders = await db.orders.count({
  enableCrossPartitionQuery: true,
});

// Cross-partition count with filter
const allCompletedOrders = await db.orders.count({
  enableCrossPartitionQuery: true,
  where: { status: 'completed' },
});

const result = await db.orders.aggregate({
  partitionKey: 'customer-123',
  _count: {
    select: {
      amount: true,        // Count orders with amount
      discount: true,      // Count orders with discount (might be optional)
      trackingNumber: true,
    },
  },
});

// Result type is inferred:
// {
//   _count: {
//     amount: number;
//     discount: number;
//     trackingNumber: number;
//   }
// }

// Sum all order amounts for a customer
const result = await db.orders.aggregate({
  partitionKey: 'customer-123',
  _sum: {
    amount: true,
  },
});

// Result: { _sum: { amount: 1250.50 | null } }

const stats = await db.orders.aggregate({
  partitionKey: 'customer-123',
  _count: true,                    // Total count
  _sum: { amount: true },          // Total revenue
  _avg: { amount: true },          // Average order value
  _min: { amount: true },          // Smallest order
  _max: { amount: true },          // Largest order
});

// Result type (fully inferred):
// {
//   _count: number;
//   _sum: { amount: number | null };
//   _avg: { amount: number | null };
//   _min: { amount: number | null };
//   _max: { amount: number | null };
// }

console.log(`
  Orders: ${stats._count}
  Total Revenue: $${stats._sum.amount}
  Average Order: $${stats._avg.amount}
  Range: $${stats._min.amount} - $${stats._max.amount}
`);

// Stats for completed orders only
const completedStats = await db.orders.aggregate({
  partitionKey: 'customer-123',
  where: { status: 'completed' },
  _count: true,
  _sum: { amount: true },
  _avg: { amount: true },
});

// Year-to-date statistics
const ytdStats = await db.orders.aggregate({
  partitionKey: 'customer-123',
  where: {
    createdAt: { gte: new Date('2024-01-01') },
  },
  _sum: { amount: true, tax: true, shipping: true },
  _count: true,
});

const productSchema = container('products', {
  id: field.string(),
  category: field.string(),
  price: field.number(),
  cost: field.number(),
  stock: field.number(),
  weight: field.number(),
}).partitionKey('category');

const stats = await db.products.aggregate({
  partitionKey: 'electronics',
  _sum: {
    stock: true,    // Total inventory
    price: true,    // Total value at retail
    cost: true,     // Total cost basis
  },
  _avg: {
    price: true,    // Average price
    weight: true,   // Average weight
  },
  _min: {
    price: true,    // Cheapest product
  },
  _max: {
    price: true,    // Most expensive product
  },
});

// Calculate profit margin
const totalProfit = stats._sum.price! - stats._sum.cost!;
const margin = (totalProfit / stats._sum.price!) * 100;
console.log(`Profit margin: ${margin.toFixed(2)}%`);

// No aggregate parameter
const users = await db.users.findMany({ partitionKey: "john@example.com" });
// Type: User[]

const selectedUsers = await db.users.findMany({ 
  partitionKey: "john@example.com",
  select: { id: true, name: true }
});
// Type: Array<{ id: string, name: string }>

// With aggregate parameter
const result = await db.users.findMany({
  partitionKey: "john@example.com",
  aggregate: { _count: true }
});
// Type: { data: User[], _count: number }

const result = await db.users.findMany({
  partitionKey: "john@example.com",
  where: { isActive: true },
  aggregate: {
    _count: true,
    _avg: { age: true }
  }
});

console.log(`Found ${result._count} active users`);
console.log(`Average age: ${result._avg.age}`);
console.log(`Users:`, result.data);

const stats = await db.orders.findMany({
  partitionKey: "customer-123",
  aggregate: {
    _count: true,
    _sum: { totalAmount: true },
    _avg: { totalAmount: true },
    _min: { createdAt: true },
    _max: { createdAt: true }
  }
});

console.log(`Order Statistics:`);
console.log(`  Total orders: ${stats._count}`);
console.log(`  Total revenue: ${stats._sum.totalAmount}`);
console.log(`  Average order: ${stats._avg.totalAmount}`);
console.log(`  First order: ${stats._min.createdAt}`);
console.log(`  Latest order: ${stats._max.createdAt}`);
console.log(`  Orders: ${stats.data.length} items`);

const result = await db.products.findMany({
  partitionKey: "category-electronics",
  where: { 
    price: { gte: 100 },
    inStock: true 
  },
  select: { id: true, name: true, price: true },
  aggregate: {
    _count: true,
    _avg: { price: true },
    _min: { price: true },
    _max: { price: true }
  }
});

// Only selected fields in data
console.log(result.data); // Array<{ id: string, name: string, price: number }>

// All aggregations available
console.log(`Expensive products: ${result._count}`);
console.log(`Price range: ${result._min.price} - ${result._max.price}`);

const result = await db.users.findMany({
  partitionKey: "john@example.com",
  take: 10,  // Get first 10 users
  skip: 0,
  aggregate: {
    _count: true,  // Count ALL users matching query
  }
});

console.log(`Showing ${result.data.length} of ${result._count} total users`);

// Calculate total pages
const totalPages = Math.ceil(result._count / 10);
console.log(`Page 1 of ${totalPages}`);

const result = await db.products.findMany({
  where: { category: "electronics" },
  enableCrossPartitionQuery: true,
  aggregate: {
    _count: true,
    _avg: { price: true }
  }
});

console.log(`Electronics across all partitions:`);
console.log(`  Total products: ${result._count}`);
console.log(`  Average price: ${result._avg.price}`);

// Before: Two separate queries
const users = await db.users.findMany({ partitionKey: "john@example.com" });
const stats = await db.users.aggregate({
  partitionKey: "john@example.com",
  _count: true,
  _avg: { age: true }
});

// After: Single efficient query
const result = await db.users.findMany({
  partitionKey: "john@example.com",
  aggregate: {
    _count: true,
    _avg: { age: true }
  }
});
// result.data contains users
// result._count and result._avg contain aggregations

const result = await db.users.findMany({
  partitionKey: "john@example.com",
  aggregate: { _count: true }
});
// result._count: number

// Count specific fields (non-null values)
const result = await db.users.findMany({
  partitionKey: "john@example.com",
  aggregate: {
    _count: { select: { name: true, email: true } }
  }
});
// result._count: { name: number, email: number }

const result = await db.orders.findMany({
  partitionKey: "customer-123",
  aggregate: {
    _sum: { amount: true, tax: true },
    _avg: { amount: true },
    _min: { amount: true, createdAt: true },
    _max: { amount: true, createdAt: true }
  }
});

// All typed correctly based on field types
// number fields return number | null
// date fields return Date | null

const result = await db.users.findMany({
  partitionKey: "john@example.com",
  aggregate: {
    _count: true,
    _avg: { age: true },
    _sum: { score: true }
  }
});

// Type: { 
//   data: User[], 
//   _count: number,
//   _avg: { age: number | null },
//   _sum: { score: number | null }
// }

const salesSchema = container('sales', {
  id: field.string(),
  storeId: field.string(),
  category: field.string(),
  amount: field.number(),
  quantity: field.number(),
}).partitionKey('storeId');

// Sales by category
const categoryStats = await db.sales.groupBy({
  by: 'category',
  enableCrossPartitionQuery: true,
  _count: true,
  _sum: { amount: true, quantity: true },
  _avg: { amount: true },
});

// Result type (fully inferred):
// Array<{
//   category: string;
//   _count: number;
//   _sum: { amount: number | null; quantity: number | null };
//   _avg: { amount: number | null };
// }>

// Use the results
for (const group of categoryStats) {
  console.log(`${group.category}:
    Sales: $${group._sum.amount}
    Units: ${group._sum.quantity}
    Avg: $${group._avg.amount}
  `);
}

// Sales by store AND category
const detailedStats = await db.sales.groupBy({
  by: ['storeId', 'category'] as const,  // 'as const' for better type inference
  enableCrossPartitionQuery: true,
  _count: true,
  _sum: { amount: true },
});

// Result type:
// Array<{
//   storeId: string;
//   category: string;
//   _count: number;
//   _sum: { amount: number | null };
// }>

// Find best performing store-category combination
const topPerformer = detailedStats.reduce((max, group) => 
  (group._sum.amount || 0) > (max._sum.amount || 0) ? group : max
);

console.log(`Top: ${topPerformer.storeId} - ${topPerformer.category}`);

// Top categories by revenue
const topCategories = await db.sales.groupBy({
  by: 'category',
  enableCrossPartitionQuery: true,
  _count: true,
  _sum: { amount: true },
  orderBy: {
    _sum_amount: 'desc',  // Order by aggregated field
  },
  take: 10,  // Top 10
});

// Categories with most transactions
const popularCategories = await db.sales.groupBy({
  by: 'category',
  enableCrossPartitionQuery: true,
  _count: true,
  orderBy: {
    _count: 'desc',
  },
  take: 5,
});

// Paginated category report
const page1 = await db.sales.groupBy({
  by: 'category',
  enableCrossPartitionQuery: true,
  _sum: { amount: true },
  orderBy: { category: 'asc' },
  take: 20,
  skip: 0,
});

const page2 = await db.sales.groupBy({
  by: 'category',
  enableCrossPartitionQuery: true,
  _sum: { amount: true },
  orderBy: { category: 'asc' },
  take: 20,
  skip: 20,
});

const userSchema = container('analytics', {
  id: field.string(),
  userId: field.string(),
  eventType: field.string(),
  country: field.string(),
  timestamp: field.date(),
  duration: field.number(),
}).partitionKey('userId');

// Events by type for a specific user in the last 30 days
const recentEvents = await db.analytics.groupBy({
  by: 'eventType',
  partitionKey: 'user-123',
  where: {
    timestamp: { gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) },
  },
  _count: true,
  _avg: { duration: true },
});

// Cross-partition: Events by country for premium users
const countryStats = await db.analytics.groupBy({
  by: 'country',
  enableCrossPartitionQuery: true,
  where: {
    eventType: 'purchase',
  },
  _count: true,
  _sum: { duration: true },
});

// Quick sum - cleaner than aggregate()
const totalRevenue = await db.orders.sum('amount', {
  partitionKey: 'customer-123',
});
// Returns: number | null

// With filter
const completedRevenue = await db.orders.sum('amount', {
  partitionKey: 'customer-123',
  where: { status: 'completed' },
});

// Average order value
const avgOrderValue = await db.orders.avg('amount', {
  partitionKey: 'customer-123',
});
// Returns: number | null

// Average rating for a product
const avgRating = await db.reviews.avg('rating', {
  partitionKey: 'product-456',
  where: { verified: true },
});

// Find cheapest product
const cheapestPrice = await db.products.min('price', {
  partitionKey: 'electronics',
});
// Returns: number | null

// Earliest order date
const firstOrder = await db.orders.min('createdAt', {
  partitionKey: 'customer-123',
});
// Returns: Date | null (type inferred from field!)

// Most expensive product
const highestPrice = await db.products.max('price', {
  partitionKey: 'electronics',
});

// Latest activity
const lastSeen = await db.users.max('lastLoginAt', {
  partitionKey: 'tenant-1',
});
// Returns: Date | null

const orderSchema = container('orders', {
  id: field.string(),
  customerId: field.string(),
  amount: field.number(),
  tax: field.number().optional(),
  status: field.string(),
  createdAt: field.date(),
}).partitionKey('customerId');

// TypeScript knows exact types
const stats = await db.orders.aggregate({
  partitionKey: 'customer-123',
  _count: true,
  _sum: { amount: true, tax: true },
  _min: { createdAt: true },
  _max: { createdAt: true },
});

// ✅ All these are correctly typed:
const count: number = stats._count;
const total: number | null = stats._sum.amount;
const taxTotal: number | null = stats._sum.tax;
const firstOrder: Date | null = stats._min.createdAt;
const lastOrder: Date | null = stats._max.createdAt;

// ❌ TypeScript errors:
// stats._sum.status;  // Error: 'status' is not a number field
// stats._avg.createdAt;  // Error: 'createdAt' is not a number field

// Single field grouping
const result1 = await db.orders.groupBy({
  by: 'status',
  enableCrossPartitionQuery: true,
  _count: true,
  _sum: { amount: true },
});
// Type: Array<{ status: string; _count: number; _sum: { amount: number | null } }>

// Multiple field grouping
const result2 = await db.orders.groupBy({
  by: ['customerId', 'status'] as const,
  enableCrossPartitionQuery: true,
  _count: true,
});
// Type: Array<{ customerId: string; status: string; _count: number }>

// Access fields with full type safety
for (const group of result1) {
  console.log(group.status);      // ✅ string
  console.log(group._count);      // ✅ number
  console.log(group._sum.amount); // ✅ number | null
}