SQLite-Specific Features { .api }

import { integer, sqliteTable } from 'drizzle-orm/sqlite-core';

/**
 * Integer column with multiple mode options
 *
 * @param name - Column name (optional, can be omitted for inline definition)
 * @param config - Configuration object with mode option
 *
 * Modes:
 * - Default (no mode): JavaScript number
 * - 'timestamp': Unix timestamp in seconds (Date object)
 * - 'timestamp_ms': Unix timestamp in milliseconds (Date object)
 * - 'boolean': Boolean value (stored as 0/1)
 */
function integer<TMode extends 'number' | 'timestamp' | 'timestamp_ms' | 'boolean'>(
  name?: string,
  config?: { mode: TMode }
): IntegerBuilder;

// Usage examples
const users = sqliteTable('users', {
  // Default mode: JavaScript number
  id: integer('id').primaryKey({ autoIncrement: true }),
  age: integer('age').notNull(),

  // Timestamp mode: Unix timestamp in seconds (Date)
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date()),

  // Timestamp milliseconds mode: Unix timestamp in ms (Date)
  updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
    .notNull()
    .$defaultFn(() => new Date()),

  // Boolean mode: Stored as 0/1
  isActive: integer('is_active', { mode: 'boolean' })
    .notNull()
    .default(true),

  // Boolean with SQL default
  emailVerified: integer('email_verified', { mode: 'boolean' })
    .default(sql`0`),
});

// Type inference
type User = typeof users.$inferSelect;
// {
//   id: number;
//   age: number;
//   createdAt: Date;      // mode: 'timestamp'
//   updatedAt: Date;      // mode: 'timestamp_ms'
//   isActive: boolean;    // mode: 'boolean'
//   emailVerified: boolean;
// }

type NewUser = typeof users.$inferInsert;
// {
//   id?: number;
//   age: number;
//   createdAt: Date;
//   updatedAt: Date;
//   isActive?: boolean;
//   emailVerified?: boolean;
// }

/**
 * Primary key with auto-increment configuration
 *
 * @param config - Configuration object
 * @param config.autoIncrement - Enable auto-increment (AUTOINCREMENT)
 * @param config.onConflict - ON CONFLICT clause for primary key
 */
interface PrimaryKeyConfig {
  autoIncrement?: boolean;
  onConflict?: 'rollback' | 'abort' | 'fail' | 'ignore' | 'replace';
}

const posts = sqliteTable('posts', {
  // Auto-incrementing primary key
  id: integer('id').primaryKey({ autoIncrement: true }),

  // Primary key with ON CONFLICT
  slug: integer('slug').primaryKey({ onConflict: 'replace' }),
});

const events = sqliteTable('events', {
  id: integer('id').primaryKey({ autoIncrement: true }),

  // Current timestamp in milliseconds (deprecated helper)
  createdAt: integer('created_at', { mode: 'timestamp_ms' })
    .notNull()
    .defaultNow(), // Deprecated: use .default() or .$defaultFn()

  // Recommended approaches:
  // 1. Using $defaultFn (runtime default)
  timestamp1: integer('timestamp1', { mode: 'timestamp_ms' })
    .$defaultFn(() => new Date()),

  // 2. Using SQL default
  timestamp2: integer('timestamp2', { mode: 'timestamp_ms' })
    .default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`),

  // 3. Unix timestamp in seconds
  timestamp3: integer('timestamp3', { mode: 'timestamp' })
    .default(sql`(unixepoch())`),
});

import { text } from 'drizzle-orm/sqlite-core';

/**
 * Text column with optional modes and enum validation
 *
 * @param name - Column name (optional)
 * @param config - Configuration object
 *
 * Modes:
 * - 'text' (default): String value
 * - 'json': JSON data (auto-serialized)
 *
 * Config options:
 * - mode: 'text' | 'json'
 * - enum: Readonly array of valid string values
 * - length: Maximum length (for documentation, not enforced by SQLite)
 */
interface TextConfig<TMode extends 'text' | 'json'> {
  mode?: TMode;
  enum?: readonly string[];
  length?: number;
}

// Usage examples
const products = sqliteTable('products', {
  // Basic text column
  name: text('name').notNull(),

  // Text with length (documentation only, not enforced)
  code: text('code', { length: 10 }).notNull(),

  // Text with enum validation
  status: text('status', {
    enum: ['draft', 'published', 'archived']
  }).notNull().default('draft'),

  // JSON mode: Automatic serialization/deserialization
  metadata: text('metadata', { mode: 'json' }),

  // JSON with type inference
  settings: text('settings', { mode: 'json' }).$type<{
    theme: string;
    notifications: boolean;
  }>(),

  // JSON array
  tags: text('tags', { mode: 'json' }).$type<string[]>(),
});

// Type inference
type Product = typeof products.$inferSelect;
// {
//   name: string;
//   code: string;
//   status: 'draft' | 'published' | 'archived';
//   metadata: unknown;  // mode: 'json'
//   settings: { theme: string; notifications: boolean; } | null;
//   tags: string[] | null;
// }

// JSON column usage
await db.insert(products).values({
  name: 'Widget',
  code: 'WDG001',
  status: 'draft',
  metadata: { key: 'value' }, // Automatically serialized
  settings: { theme: 'dark', notifications: true },
  tags: ['new', 'featured'],
});

// Query with JSON data
const allProducts = await db.select().from(products);
console.log(allProducts[0].settings?.theme); // Type-safe access

// Define enum values
const roles = ['admin', 'user', 'guest'] as const;

const accounts = sqliteTable('accounts', {
  id: integer('id').primaryKey({ autoIncrement: true }),

  // Enum with type inference
  role: text('role', { enum: roles }).notNull().default('user'),
});

type Account = typeof accounts.$inferSelect;
// { id: number; role: 'admin' | 'user' | 'guest' }

import { blob } from 'drizzle-orm/sqlite-core';

/**
 * Blob column for binary data with multiple mode options
 *
 * @param name - Column name (optional)
 * @param config - Configuration object with mode option
 *
 * Modes:
 * - 'buffer' (default): Buffer/Uint8Array
 * - 'json': JSON data (stored as binary)
 * - 'bigint': BigInt value (stored as text in blob)
 *
 * Note: For JSON mode, text('...', { mode: 'json' }) is recommended
 * because SQLite's JSON functions don't work with BLOBs
 */
interface BlobConfig {
  mode: 'buffer' | 'json' | 'bigint';
}

// Usage examples
const files = sqliteTable('files', {
  id: integer('id').primaryKey({ autoIncrement: true }),

  // Buffer mode (default): Binary data
  data: blob('data', { mode: 'buffer' }).notNull(),

  // JSON mode: JSON stored as blob (not recommended)
  // Use text('...', { mode: 'json' }) instead for JSON functions support
  metadata: blob('metadata', { mode: 'json' }),

  // BigInt mode: Large integers
  largeNumber: blob('large_number', { mode: 'bigint' }),
});

// Type inference
type File = typeof files.$inferSelect;
// {
//   id: number;
//   data: Buffer;
//   metadata: unknown;
//   largeNumber: bigint | null;
// }

// Usage with Buffer
await db.insert(files).values({
  data: Buffer.from('Hello, World!'),
  metadata: { type: 'text/plain' },
  largeNumber: 9007199254740991n,
});

// Reading blob data
const file = await db.select().from(files).where(eq(files.id, 1));
console.log(file[0].data.toString()); // "Hello, World!"
console.log(file[0].largeNumber); // 9007199254740991n

import { real } from 'drizzle-orm/sqlite-core';

/**
 * Real number column (floating-point)
 *
 * @param name - Column name (optional)
 * @returns Real column builder
 *
 * Storage: 8-byte IEEE floating point number
 */
function real(name?: string): RealBuilder;

// Usage examples
const measurements = sqliteTable('measurements', {
  id: integer('id').primaryKey({ autoIncrement: true }),

  // Basic real number
  temperature: real('temperature').notNull(),

  // With default value
  humidity: real('humidity').default(50.0),

  // Calculated value
  average: real('average').generatedAlwaysAs(
    sql`(temperature + humidity) / 2.0`
  ),
});

// Type inference
type Measurement = typeof measurements.$inferSelect;
// {
//   id: number;
//   temperature: number;
//   humidity: number | null;
//   average: number | null;
// }

import { numeric } from 'drizzle-orm/sqlite-core';

/**
 * Numeric column with mode options for different numeric types
 *
 * @param name - Column name (optional)
 * @param config - Configuration object with mode option
 *
 * Modes:
 * - Default (no mode): JavaScript string (exact precision)
 * - 'number': JavaScript number
 * - 'bigint': JavaScript bigint
 *
 * Storage: SQLite NUMERIC affinity (can store exact decimals)
 */
interface NumericConfig {
  mode: 'string' | 'number' | 'bigint';
}

// Usage examples
const finances = sqliteTable('finances', {
  id: integer('id').primaryKey({ autoIncrement: true }),

  // Default mode: String for exact precision
  amount: numeric('amount').notNull(),

  // Number mode: JavaScript number
  price: numeric('price', { mode: 'number' }).notNull(),

  // BigInt mode: For large integers
  balance: numeric('balance', { mode: 'bigint' }),
});

// Type inference
type Finance = typeof finances.$inferSelect;
// {
//   id: number;
//   amount: string;      // Default mode
//   price: number;       // mode: 'number'
//   balance: bigint | null; // mode: 'bigint'
// }

// Usage
await db.insert(finances).values({
  amount: '99.99',        // String for exact precision
  price: 99.99,           // Number (may have precision issues)
  balance: 1000000000n,   // BigInt
});

import { customType } from 'drizzle-orm/sqlite-core';

/**
 * Define a custom column type with custom serialization/deserialization
 *
 * @param config - Configuration object
 * @param config.dataType - SQL data type ('integer', 'text', 'blob', 'real', 'numeric')
 * @param config.toDriver - Convert TypeScript value to database value
 * @param config.fromDriver - Convert database value to TypeScript value
 */
interface CustomTypeConfig<T> {
  dataType(): string;
  toDriver(value: T): unknown;
  fromDriver(value: unknown): T;
}

// Example: Custom point type
type Point = { x: number; y: number };

const point = customType<Point>({
  dataType() {
    return 'text';
  },
  toDriver(value: Point): string {
    return `${value.x},${value.y}`;
  },
  fromDriver(value: unknown): Point {
    const [x, y] = (value as string).split(',').map(Number);
    return { x, y };
  },
});

const locations = sqliteTable('locations', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  coordinates: point('coordinates').notNull(),
});

// Usage
await db.insert(locations).values({
  coordinates: { x: 10.5, y: 20.3 },
});

// Example: Custom encrypted string
const encrypted = customType<string>({
  dataType() {
    return 'text';
  },
  toDriver(value: string): string {
    return Buffer.from(value).toString('base64');
  },
  fromDriver(value: unknown): string {
    return Buffer.from(value as string, 'base64').toString('utf-8');
  },
});

const secrets = sqliteTable('secrets', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  token: encrypted('token').notNull(),
});

/**
 * SQLite Type Affinity Rules:
 *
 * TEXT affinity:
 * - Stores values as TEXT, INTEGER, REAL, or BLOB
 * - Drizzle: text(), blob({ mode: 'json' })
 *
 * NUMERIC affinity:
 * - Prefers INTEGER or REAL, falls back to TEXT
 * - Drizzle: numeric()
 *
 * INTEGER affinity:
 * - Stores as INTEGER if possible
 * - Drizzle: integer()
 *
 * REAL affinity:
 * - Stores as REAL or INTEGER
 * - Drizzle: real()
 *
 * BLOB affinity:
 * - No preference, stores as provided
 * - Drizzle: blob()
 */

const flexible = sqliteTable('flexible', {
  // TEXT affinity - most flexible
  textCol: text('text_col'),

  // INTEGER affinity - prefers integers
  intCol: integer('int_col'),

  // REAL affinity - prefers floating point
  realCol: real('real_col'),

  // NUMERIC affinity - flexible numeric
  numericCol: numeric('numeric_col'),

  // BLOB affinity - binary data
  blobCol: blob('blob_col'),
});

/**
 * SQLite automatically coerces types in many situations
 */

const examples = sqliteTable('examples', {
  id: integer('id').primaryKey(),
  value: text('value'),
});

// These all work due to type coercion:
await db.insert(examples).values([
  { id: 1, value: '100' },      // Text stored as text
  { id: 2, value: sql`100` },   // Number coerced to text
  { id: 3, value: sql`100.5` }, // Float coerced to text
]);

// Comparison operators also coerce
await db.select()
  .from(examples)
  .where(sql`${examples.value} = 100`); // Text compared as number

/**
 * SQLite columns can store any type regardless of declared affinity
 * (though it's not recommended for type safety)
 */

const flexible = sqliteTable('flexible', {
  id: integer('id').primaryKey(),
  // Declared as integer, but can store anything
  flexibleCol: integer('flexible_col'),
});

// SQLite allows this (Drizzle types will prevent it)
// await db.run(sql`
//   INSERT INTO flexible (id, flexible_col) VALUES
//   (1, 123),           -- INTEGER
//   (2, 'text'),        -- TEXT
//   (3, 123.45),        -- REAL
//   (4, X'DEADBEEF')    -- BLOB
// `);

import { sqliteTable, sqliteTableCreator } from 'drizzle-orm/sqlite-core';

/**
 * Define a SQLite table
 *
 * @param name - Table name
 * @param columns - Column definitions
 * @param extraConfig - Additional table configuration (indexes, constraints)
 * @returns SQLite table definition
 */
function sqliteTable<
  TName extends string,
  TColumns extends Record<string, SQLiteColumnBuilder>
>(
  name: TName,
  columns: TColumns,
  extraConfig?: (columns: TColumns) => ExtraConfig
): SQLiteTable<TName, TColumns>;

// Basic table
const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});

// Table with extra configuration
const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  userId: integer('user_id').notNull().references(() => users.id),
  title: text('title').notNull(),
  content: text('content').notNull(),
  status: text('status', { enum: ['draft', 'published'] }).notNull(),
  publishedAt: integer('published_at', { mode: 'timestamp' }),
}, (table) => ({
  // Indexes
  userIdIdx: index('user_id_idx').on(table.userId),
  statusIdx: index('status_idx').on(table.status),
  publishedIdx: index('published_idx')
    .on(table.publishedAt)
    .where(sql`${table.status} = 'published'`),

  // Unique constraints
  titleUnique: unique('title_unique').on(table.title),

  // Check constraints
  statusCheck: check('status_check',
    sql`${table.status} IN ('draft', 'published')`
  ),
}));

/**
 * Create custom table name function with prefix
 */
function sqliteTableCreator(
  customTableName: (name: string) => string
): typeof sqliteTable;

// Example: Add prefix to all table names
const pgTable = sqliteTableCreator((name) => `myapp_${name}`);

const customers = pgTable('customers', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
});
// Creates table: myapp_customers

import { sqliteView } from 'drizzle-orm/sqlite-core';

/**
 * Define a SQLite view
 *
 * Two approaches:
 * 1. Automatic: Define with query builder
 * 2. Manual: Define columns manually for existing views
 */

// Approach 1: Automatic view from query
const activeUsers = sqliteView('active_users').as((qb) =>
  qb.select({
    id: users.id,
    name: users.name,
    email: users.email,
  })
  .from(users)
  .where(eq(users.isActive, true))
);

// Type inference works automatically
type ActiveUser = typeof activeUsers.$inferSelect;
// { id: number; name: string; email: string }

// Query the view
const results = await db.select().from(activeUsers);

// Approach 2: Manual view definition for existing views
const userStats = sqliteView('user_stats', {
  userId: integer('user_id').notNull(),
  postCount: integer('post_count').notNull(),
  lastPostDate: integer('last_post_date', { mode: 'timestamp' }),
}).existing();

// View with custom SQL
const complexView = sqliteView('complex_view', {
  id: integer('id').notNull(),
  total: real('total').notNull(),
}).as(sql`
  SELECT
    u.id,
    SUM(o.amount) as total
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  GROUP BY u.id
`);

// Using views in queries
const topUsers = await db
  .select({
    userId: userStats.userId,
    postCount: userStats.postCount,
  })
  .from(userStats)
  .where(gt(userStats.postCount, 10))
  .orderBy(desc(userStats.postCount))
  .limit(10);

// Join with views
const usersWithStats = await db
  .select({
    user: users,
    stats: userStats,
  })
  .from(users)
  .leftJoin(userStats, eq(users.id, userStats.userId));

import { index, uniqueIndex } from 'drizzle-orm/sqlite-core';

/**
 * Create an index
 *
 * @param name - Index name
 * @returns IndexBuilderOn instance with .on() method
 */
function index(name: string): IndexBuilderOn;

/**
 * Create a unique index
 */
function uniqueIndex(name: string): IndexBuilderOn;

/**
 * Index builder with chainable methods
 */
interface IndexBuilder {
  /**
   * Add WHERE clause for partial index
   *
   * @param condition - SQL condition
   */
  where(condition: SQL): this;
}

const products = sqliteTable('products', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  category: text('category').notNull(),
  price: real('price').notNull(),
  discount: real('discount'),
  isActive: integer('is_active', { mode: 'boolean' }).notNull().default(true),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
}, (table) => ({
  // Regular index
  categoryIdx: index('category_idx').on(table.category),

  // Composite index (multiple columns)
  categoryPriceIdx: index('category_price_idx')
    .on(table.category, table.price),

  // Unique index
  nameUnique: uniqueIndex('name_unique').on(table.name),

  // Partial index (with WHERE clause)
  activeProductsIdx: index('active_products_idx')
    .on(table.category)
    .where(sql`${table.isActive} = 1`),

  // Partial index with complex condition
  discountedIdx: index('discounted_idx')
    .on(table.category, table.price)
    .where(sql`${table.discount} IS NOT NULL AND ${table.discount} > 0`),

  // Expression index (SQLite 3.9.0+)
  lowerNameIdx: index('lower_name_idx')
    .on(sql`lower(${table.name})`),

  // Index on JSON field (using ->> operator)
  metadataTypeIdx: index('metadata_type_idx')
    .on(sql`json_extract(${table.metadata}, '$.type')`),
}));

// Using indexes in queries (automatic)
// SQLite query planner will use indexes when appropriate

// Query that uses categoryIdx
const electronics = await db.select()
  .from(products)
  .where(eq(products.category, 'electronics'));

// Query that uses categoryPriceIdx
const cheapElectronics = await db.select()
  .from(products)
  .where(
    and(
      eq(products.category, 'electronics'),
      lt(products.price, 100)
    )
  );

// Query that uses activeProductsIdx (partial index)
const activeElectronics = await db.select()
  .from(products)
  .where(
    and(
      eq(products.category, 'electronics'),
      eq(products.isActive, true)
    )
  );

// Case-insensitive search using expression index
const searchResults = await db.select()
  .from(products)
  .where(sql`lower(${products.name}) = lower('iPhone')`);

/**
 * Index Guidelines for SQLite:
 *
 * 1. Index columns used in WHERE clauses
 * 2. Index columns used in JOIN conditions
 * 3. Use composite indexes for multi-column queries
 * 4. Use partial indexes for subset queries
 * 5. Don't over-index (slows down INSERT/UPDATE)
 * 6. Consider expression indexes for computed values
 */

const orders = sqliteTable('orders', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  customerId: integer('customer_id').notNull(),
  status: text('status', {
    enum: ['pending', 'processing', 'shipped', 'delivered', 'cancelled']
  }).notNull(),
  orderDate: integer('order_date', { mode: 'timestamp' }).notNull(),
  totalAmount: real('total_amount').notNull(),
  isPaid: integer('is_paid', { mode: 'boolean' }).notNull().default(false),
}, (table) => ({
  // Index for customer lookup
  customerIdx: index('customer_idx').on(table.customerId),

  // Composite index for common query pattern
  customerStatusIdx: index('customer_status_idx')
    .on(table.customerId, table.status),

  // Partial index for active orders only
  activeOrdersIdx: index('active_orders_idx')
    .on(table.customerId, table.orderDate)
    .where(sql`${table.status} NOT IN ('delivered', 'cancelled')`),

  // Index for unpaid orders
  unpaidIdx: index('unpaid_idx')
    .on(table.orderDate)
    .where(sql`${table.isPaid} = 0`),

  // Index for date range queries
  orderDateIdx: index('order_date_idx').on(table.orderDate),
}));

import {
  primaryKey,
  foreignKey,
  unique,
  check
} from 'drizzle-orm/sqlite-core';

/**
 * Primary key constraint (composite)
 */
function primaryKey(config: {
  columns: [Column, ...Column[]];
}): PrimaryKeyBuilder;

/**
 * Foreign key constraint
 */
function foreignKey(config: {
  columns: [Column, ...Column[]];
  foreignColumns: [Column, ...Column[]];
}): ForeignKeyBuilder;

/**
 * Unique constraint
 */
function unique(name?: string): UniqueConstraintBuilder;

/**
 * Check constraint
 */
function check(name: string, condition: SQL): CheckBuilder;

// Primary key constraints
const userRoles = sqliteTable('user_roles', {
  userId: integer('user_id').notNull(),
  roleId: integer('role_id').notNull(),
  grantedAt: integer('granted_at', { mode: 'timestamp' }).notNull(),
}, (table) => ({
  // Composite primary key
  pk: primaryKey({ columns: [table.userId, table.roleId] }),

  // Foreign keys
  userFk: foreignKey({
    columns: [table.userId],
    foreignColumns: [users.id],
  }).onDelete('cascade'),

  roleFk: foreignKey({
    columns: [table.roleId],
    foreignColumns: [roles.id],
  }).onDelete('cascade'),
}));

// Unique constraints
const profiles = sqliteTable('profiles', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  userId: integer('user_id').notNull(),
  username: text('username').notNull(),
  email: text('email').notNull(),
}, (table) => ({
  // Single column unique
  usernameUnique: unique('username_unique').on(table.username),

  // Composite unique constraint
  userEmailUnique: unique('user_email_unique').on(table.userId, table.email),
}));

// Check constraints
const accounts = sqliteTable('accounts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  balance: real('balance').notNull().default(0),
  accountType: text('account_type', {
    enum: ['checking', 'savings', 'credit']
  }).notNull(),
  creditLimit: real('credit_limit'),
  minBalance: real('min_balance'),
}, (table) => ({
  // Check balance is non-negative
  balanceCheck: check('balance_check', sql`${table.balance} >= 0`),

  // Check credit limit for credit accounts
  creditLimitCheck: check('credit_limit_check',
    sql`${table.accountType} != 'credit' OR ${table.creditLimit} IS NOT NULL`
  ),

  // Check minimum balance constraint
  minBalanceCheck: check('min_balance_check',
    sql`${table.balance} >= COALESCE(${table.minBalance}, 0)`
  ),
}));

// Foreign key with actions
const comments = sqliteTable('comments', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  postId: integer('post_id').notNull(),
  userId: integer('user_id').notNull(),
  content: text('content').notNull(),
}, (table) => ({
  // ON DELETE CASCADE: Delete comments when post is deleted
  postFk: foreignKey({
    columns: [table.postId],
    foreignColumns: [posts.id],
  }).onDelete('cascade'),

  // ON DELETE SET NULL: Set user_id to NULL when user is deleted
  // Note: column must be nullable
  // userFk: foreignKey({
  //   columns: [table.userId],
  //   foreignColumns: [users.id],
  // }).onDelete('set null'),

  // ON UPDATE CASCADE: Update references when primary key changes
  userFk: foreignKey({
    columns: [table.userId],
    foreignColumns: [users.id],
  }).onUpdate('cascade').onDelete('cascade'),
}));

/**
 * ON CONFLICT types in SQLite
 */
type OnConflict = 'rollback' | 'abort' | 'fail' | 'ignore' | 'replace';

/**
 * ON CONFLICT behavior:
 *
 * - rollback: Abort current statement and roll back transaction
 * - abort: Abort current statement (default)
 * - fail: Continue but fail current statement
 * - ignore: Skip the row that caused the violation
 * - replace: Delete pre-existing row and insert new row
 */

// ON CONFLICT in primary key definition
const devices = sqliteTable('devices', {
  // Primary key with ON CONFLICT REPLACE
  deviceId: text('device_id').primaryKey({ onConflict: 'replace' }),
  name: text('name').notNull(),
  lastSeen: integer('last_seen', { mode: 'timestamp' }).notNull(),
});

// ON CONFLICT in INSERT queries
const sessions = sqliteTable('sessions', {
  id: text('id').primaryKey(),
  userId: integer('user_id').notNull(),
  expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
  data: text('data', { mode: 'json' }),
}, (table) => ({
  userIdx: uniqueIndex('user_idx').on(table.userId),
}));

// ON CONFLICT DO NOTHING
await db.insert(sessions)
  .values({
    id: 'session-123',
    userId: 1,
    expiresAt: new Date(),
  })
  .onConflictDoNothing();

// ON CONFLICT DO NOTHING with target
await db.insert(sessions)
  .values({
    id: 'session-123',
    userId: 1,
    expiresAt: new Date(),
  })
  .onConflictDoNothing({
    target: sessions.id,
  });

// ON CONFLICT DO NOTHING with WHERE clause
await db.insert(sessions)
  .values({
    id: 'session-123',
    userId: 1,
    expiresAt: new Date(),
  })
  .onConflictDoNothing({
    target: sessions.id,
    where: sql`${sessions.expiresAt} > ${Date.now()}`,
  });

// ON CONFLICT DO UPDATE (upsert)
await db.insert(sessions)
  .values({
    id: 'session-123',
    userId: 1,
    expiresAt: new Date(),
    data: { theme: 'dark' },
  })
  .onConflictDoUpdate({
    target: sessions.id,
    set: {
      expiresAt: new Date(),
      data: { theme: 'dark' },
    },
  });

// Upsert with excluded values
await db.insert(sessions)
  .values({
    id: 'session-123',
    userId: 1,
    expiresAt: new Date(),
    data: { theme: 'dark' },
  })
  .onConflictDoUpdate({
    target: sessions.id,
    set: {
      // Use excluded.* to reference the values that would have been inserted
      expiresAt: sql`excluded.expires_at`,
      data: sql`excluded.data`,
    },
  });

// Upsert with conditional update (targetWhere)
await db.insert(sessions)
  .values({
    id: 'session-123',
    userId: 1,
    expiresAt: new Date(),
  })
  .onConflictDoUpdate({
    target: sessions.id,
    targetWhere: sql`${sessions.expiresAt} < ${Date.now()}`,
    set: {
      expiresAt: new Date(),
    },
  });

// Upsert with conditional set (setWhere)
await db.insert(sessions)
  .values({
    id: 'session-123',
    userId: 1,
    expiresAt: new Date(),
  })
  .onConflictDoUpdate({
    target: sessions.id,
    set: {
      expiresAt: new Date(),
    },
    setWhere: sql`${sessions.expiresAt} < excluded.expires_at`,
  });

// Complex upsert pattern
const stats = sqliteTable('stats', {
  key: text('key').primaryKey(),
  count: integer('count').notNull().default(0),
  lastUpdate: integer('last_update', { mode: 'timestamp' }).notNull(),
});

// Increment counter on conflict
await db.insert(stats)
  .values({
    key: 'page_views',
    count: 1,
    lastUpdate: new Date(),
  })
  .onConflictDoUpdate({
    target: stats.key,
    set: {
      count: sql`${stats.count} + 1`,
      lastUpdate: sql`excluded.last_update`,
    },
  });

import { drizzle } from 'drizzle-orm/better-sqlite3'; // or other SQLite driver

/**
 * SQLite database interface
 *
 * Available methods:
 * - select(): Create SELECT query
 * - selectDistinct(): Create SELECT DISTINCT query
 * - insert(): Create INSERT query
 * - update(): Create UPDATE query
 * - delete(): Create DELETE query
 * - with(): Create CTE
 * - $with(): Create typed CTE
 * - $count(): Create COUNT query
 * - run(): Execute statement and return metadata
 * - all(): Execute query and return all rows
 * - get(): Execute query and return first row
 * - values(): Execute query and return raw values
 * - transaction(): Execute transaction
 * - query: Relational query API
 */
interface BaseSQLiteDatabase<TResultKind extends 'sync' | 'async'> {
  select<TSelection>(fields?: TSelection): SQLiteSelectBuilder;
  selectDistinct<TSelection>(fields?: TSelection): SQLiteSelectBuilder;
  insert<TTable>(table: TTable): SQLiteInsertBuilder;
  update<TTable>(table: TTable): SQLiteUpdateBuilder;
  delete<TTable>(table: TTable): SQLiteDeleteBuilder;

  with(...queries: WithSubquery[]): WithQueryBuilder;
  $with(alias: string): WithBuilder;
  $count(source: Table | View | SQL, filters?: SQL): CountBuilder;

  // SQLite-specific methods
  run(query: SQLWrapper | string): RunResult;
  all<T>(query: SQLWrapper | string): T[];
  get<T>(query: SQLWrapper | string): T | undefined;
  values<T>(query: SQLWrapper | string): T[][];

  transaction<T>(
    callback: (tx: Transaction) => T,
    config?: TransactionConfig
  ): T;

  query: RelationalQueryAPI;
}

// Example usage
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('sqlite.db');
const db = drizzle(sqlite, { schema });

// Query methods (covered in Query Builders section)
const users = await db.select().from(usersTable);

// SQLite-specific methods (covered in next section)
const result = db.run(sql`VACUUM`);

/**
 * Execute a statement and return execution metadata
 *
 * @param query - SQL query (SQLWrapper or string)
 * @returns Object with changes and lastInsertRowid
 *
 * Use for:
 * - INSERT statements (get lastInsertRowid)
 * - UPDATE statements (get changes count)
 * - DELETE statements (get changes count)
 * - DDL statements (CREATE, ALTER, DROP)
 * - PRAGMA statements
 */
interface RunResult {
  changes: number;        // Number of rows affected
  lastInsertRowid: number; // Last inserted row ID (BigInt in some drivers)
}

// INSERT and get last inserted ID
const result = db.run(sql`
  INSERT INTO users (name, email) VALUES ('John', 'john@example.com')
`);
console.log(result.lastInsertRowid); // 123
console.log(result.changes);         // 1

// UPDATE and get affected rows count
const updateResult = db.run(sql`
  UPDATE users SET name = 'Jane' WHERE id = 123
`);
console.log(updateResult.changes); // 1

// DELETE and get deleted rows count
const deleteResult = db.run(sql`
  DELETE FROM users WHERE id = 123
`);
console.log(deleteResult.changes); // 1

// VACUUM database
db.run(sql`VACUUM`);

// PRAGMA statements
db.run(sql`PRAGMA foreign_keys = ON`);
db.run(sql`PRAGMA journal_mode = WAL`);

// With query builder
const insertResult = await db.insert(users)
  .values({ name: 'John', email: 'john@example.com' })
  .run();
console.log(insertResult.lastInsertRowid);

/**
 * Execute query and return all rows
 *
 * @param query - SQL query (SQLWrapper or string)
 * @returns Array of row objects
 *
 * Use for:
 * - SELECT queries
 * - Any query that returns multiple rows
 */

// Execute raw query
const allUsers = db.all<{ id: number; name: string; email: string }>(sql`
  SELECT id, name, email FROM users
`);

// With query builder (returns array)
const activeUsers = await db.select()
  .from(users)
  .where(eq(users.isActive, true))
  .all();

// PRAGMA queries that return rows
const foreignKeys = db.all(sql`PRAGMA foreign_key_list(users)`);
const tableInfo = db.all(sql`PRAGMA table_info(users)`);

/**
 * Execute query and return first row only
 *
 * @param query - SQL query (SQLWrapper or string)
 * @returns First row object or undefined
 *
 * Use for:
 * - SELECT queries expecting single row
 * - Queries with LIMIT 1
 * - Aggregate queries
 */

// Get single user
const user = db.get<{ id: number; name: string; email: string }>(sql`
  SELECT id, name, email FROM users WHERE id = 123
`);

if (user) {
  console.log(user.name);
}

// With query builder
const firstUser = await db.select()
  .from(users)
  .where(eq(users.id, 123))
  .get();

// Aggregate query
const stats = db.get<{ count: number; total: number }>(sql`
  SELECT
    COUNT(*) as count,
    SUM(amount) as total
  FROM orders
`);

// PRAGMA query
const journalMode = db.get<{ journal_mode: string }>(sql`
  PRAGMA journal_mode
`);
console.log(journalMode?.journal_mode); // "wal"

/**
 * Execute query and return raw values as arrays
 *
 * @param query - SQL query (SQLWrapper or string)
 * @returns Array of arrays (rows of column values)
 *
 * Use for:
 * - Performance-critical queries
 * - When you don't need column names
 * - Processing large result sets
 */

// Get raw values
const userValues = db.values<[number, string, string]>(sql`
  SELECT id, name, email FROM users
`);

// Returns: [[1, 'John', 'john@example.com'], [2, 'Jane', 'jane@example.com']]
for (const [id, name, email] of userValues) {
  console.log(`User ${id}: ${name} (${email})`);
}

// With query builder
const values = await db.select({
  id: users.id,
  name: users.name,
})
  .from(users)
  .values();

// More efficient than .all() for large result sets
const largeDataset = db.values(sql`
  SELECT * FROM large_table
`);

/**
 * When to use each method:
 *
 * db.run():
 * - INSERT, UPDATE, DELETE (when you don't need RETURNING)
 * - DDL statements (CREATE, ALTER, DROP)
 * - PRAGMA statements
 * - When you need changes count or lastInsertRowid
 *
 * db.all():
 * - SELECT queries returning multiple rows
 * - Default method for query builders
 * - When you need column names (objects)
 *
 * db.get():
 * - SELECT queries expecting single row
 * - Queries with LIMIT 1
 * - Finding by primary key
 *
 * db.values():
 * - Performance-critical queries
 * - Large result sets
 * - When column names are not needed
 */

const examples = sqliteTable('examples', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  value: text('value'),
});

// INSERT - use .run() or execute()
const insert1 = db.run(sql`INSERT INTO examples (value) VALUES ('test')`);
console.log(insert1.lastInsertRowid);

const insert2 = await db.insert(examples)
  .values({ value: 'test' })
  .execute(); // Calls .run() internally
console.log(insert2.lastInsertRowid);

// SELECT multiple - use .all()
const all1 = db.all(sql`SELECT * FROM examples`);
const all2 = await db.select().from(examples).all();

// SELECT single - use .get()
const get1 = db.get(sql`SELECT * FROM examples WHERE id = 1`);
const get2 = await db.select().from(examples).where(eq(examples.id, 1)).get();

// SELECT raw values - use .values()
const values1 = db.values(sql`SELECT id, value FROM examples`);
const values2 = await db.select({
  id: examples.id,
  value: examples.value,
}).from(examples).values();

/**
 * SELECT query builder
 */

// Basic select
const users = await db.select().from(usersTable);

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

// With WHERE clause
const activeUsers = await db.select()
  .from(usersTable)
  .where(eq(usersTable.isActive, true));

// With joins
const usersWithPosts = await db.select({
  user: usersTable,
  post: postsTable,
})
  .from(usersTable)
  .leftJoin(postsTable, eq(usersTable.id, postsTable.userId));

// With aggregation
const userPostCounts = await db.select({
  userId: usersTable.id,
  userName: usersTable.name,
  postCount: count(postsTable.id),
})
  .from(usersTable)
  .leftJoin(postsTable, eq(usersTable.id, postsTable.userId))
  .groupBy(usersTable.id);

// With ORDER BY and LIMIT
const topUsers = await db.select()
  .from(usersTable)
  .orderBy(desc(usersTable.createdAt))
  .limit(10)
  .offset(20);

// DISTINCT
const uniqueCategories = await db.selectDistinct({
  category: productsTable.category,
})
  .from(productsTable);

// Subqueries
const avgPrice = db.select({
  avg: sql<number>`avg(${productsTable.price})`,
}).from(productsTable);

const expensiveProducts = await db.select()
  .from(productsTable)
  .where(gt(productsTable.price, sql`(${avgPrice})`));

// Common Table Expressions (CTEs)
const activeUsersCTE = db.$with('active_users').as(
  db.select().from(usersTable).where(eq(usersTable.isActive, true))
);

const results = await db.with(activeUsersCTE)
  .select()
  .from(activeUsersCTE)
  .leftJoin(postsTable, eq(activeUsersCTE.id, postsTable.userId));

/**
 * INSERT query builder with SQLite-specific features
 */

// Insert single row
await db.insert(usersTable).values({
  name: 'John',
  email: 'john@example.com',
});

// Insert multiple rows
await db.insert(usersTable).values([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
]);

// Insert with RETURNING (SQLite 3.35.0+)
const insertedUsers = await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .returning();

// Insert with RETURNING specific columns
const insertedIds = await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .returning({ id: usersTable.id });

// Insert from SELECT
await db.insert(usersTable)
  .select(
    db.select({
      name: oldUsersTable.name,
      email: oldUsersTable.email,
    }).from(oldUsersTable)
  );

// ON CONFLICT DO NOTHING
await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .onConflictDoNothing();

// ON CONFLICT DO NOTHING with target
await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .onConflictDoNothing({ target: usersTable.email });

// ON CONFLICT DO UPDATE (upsert)
await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .onConflictDoUpdate({
    target: usersTable.email,
    set: { name: 'John Updated' },
  });

// Upsert with excluded values
await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .onConflictDoUpdate({
    target: usersTable.email,
    set: {
      name: sql`excluded.name`,
      updatedAt: sql`CURRENT_TIMESTAMP`,
    },
  });

// Get lastInsertRowid
const result = await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .run();
console.log(result.lastInsertRowid);

/**
 * UPDATE query builder with SQLite-specific features
 */

// Update all rows
await db.update(usersTable)
  .set({ isActive: false });

// Update with WHERE
await db.update(usersTable)
  .set({ name: 'John Updated' })
  .where(eq(usersTable.id, 1));

// Update with RETURNING (SQLite 3.35.0+)
const updatedUsers = await db.update(usersTable)
  .set({ name: 'John Updated' })
  .where(eq(usersTable.id, 1))
  .returning();

// Update with RETURNING specific columns
const updatedIds = await db.update(usersTable)
  .set({ name: 'John Updated' })
  .where(eq(usersTable.id, 1))
  .returning({ id: usersTable.id, name: usersTable.name });

// Update with SQL expressions
await db.update(productsTable)
  .set({
    price: sql`${productsTable.price} * 1.1`,
    updatedAt: sql`CURRENT_TIMESTAMP`,
  })
  .where(eq(productsTable.category, 'electronics'));

// Conditional update
await db.update(usersTable)
  .set({
    lastLogin: new Date(),
    loginCount: sql`${usersTable.loginCount} + 1`,
  })
  .where(eq(usersTable.id, 1));

// Get changes count
const result = await db.update(usersTable)
  .set({ isActive: false })
  .where(eq(usersTable.id, 1))
  .run();
console.log(result.changes);

/**
 * DELETE query builder with SQLite-specific features
 */

// Delete all rows (dangerous!)
await db.delete(usersTable);

// Delete with WHERE
await db.delete(usersTable)
  .where(eq(usersTable.id, 1));

// Delete with RETURNING (SQLite 3.35.0+)
const deletedUsers = await db.delete(usersTable)
  .where(eq(usersTable.id, 1))
  .returning();

// Delete with RETURNING specific columns
const deletedIds = await db.delete(usersTable)
  .where(eq(usersTable.id, 1))
  .returning({ id: usersTable.id, email: usersTable.email });

// Delete with complex WHERE
await db.delete(postsTable)
  .where(
    and(
      eq(postsTable.userId, 1),
      lt(postsTable.createdAt, sql`datetime('now', '-30 days')`)
    )
  );

// Delete using subquery
const inactiveUsers = db.select({ id: usersTable.id })
  .from(usersTable)
  .where(eq(usersTable.isActive, false));

await db.delete(postsTable)
  .where(inArray(postsTable.userId, sql`(${inactiveUsers})`));

// Get changes count
const result = await db.delete(usersTable)
  .where(eq(usersTable.id, 1))
  .run();
console.log(result.changes);

/**
 * RETURNING clause support in SQLite
 *
 * Requirements:
 * - SQLite 3.35.0 or later
 * - Works with INSERT, UPDATE, DELETE
 * - Can return specific columns or all columns
 * - Returns array of rows (even for single row operations)
 */

// INSERT with RETURNING
const newUsers = await db.insert(usersTable)
  .values([
    { name: 'John', email: 'john@example.com' },
    { name: 'Jane', email: 'jane@example.com' },
  ])
  .returning();
// Returns: [{ id: 1, name: 'John', ... }, { id: 2, name: 'Jane', ... }]

// INSERT with RETURNING specific columns
const insertedIds = await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .returning({ id: usersTable.id, name: usersTable.name });
// Returns: [{ id: 1, name: 'John' }]

// UPDATE with RETURNING
const updatedUsers = await db.update(usersTable)
  .set({ isActive: false })
  .where(eq(usersTable.id, 1))
  .returning();
// Returns: [{ id: 1, name: 'John', isActive: false, ... }]

// DELETE with RETURNING
const deletedUsers = await db.delete(usersTable)
  .where(lt(usersTable.lastLogin, sql`datetime('now', '-1 year')`))
  .returning({ id: usersTable.id, email: usersTable.email });
// Returns: [{ id: 3, email: 'old@example.com' }, ...]

// RETURNING with calculations
const updated = await db.update(productsTable)
  .set({ price: sql`${productsTable.price} * 1.1` })
  .where(eq(productsTable.category, 'electronics'))
  .returning({
    id: productsTable.id,
    oldPrice: sql<number>`${productsTable.price} / 1.1`,
    newPrice: productsTable.price,
  });

// RETURNING with UPSERT
const upserted = await db.insert(settingsTable)
  .values({ key: 'theme', value: 'dark' })
  .onConflictDoUpdate({
    target: settingsTable.key,
    set: { value: 'dark', updatedAt: sql`CURRENT_TIMESTAMP` },
  })
  .returning();
// Returns: [{ key: 'theme', value: 'dark', updatedAt: ... }]

// Note: RETURNING always returns an array
const [user] = await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .returning();
// Destructure to get single row

// Without RETURNING, use .run() for metadata
const result = await db.insert(usersTable)
  .values({ name: 'John', email: 'john@example.com' })
  .run();
console.log(result.lastInsertRowid); // 123
console.log(result.changes);          // 1

/**
 * Common issues with RETURNING clause
 */

// 1. Always returns array (even for single row)
const result = await db.insert(usersTable)
  .values({ name: 'John' })
  .returning();
// result is array: [{ id: 1, name: 'John', ... }]

// Use destructuring for single row:
const [user] = await db.insert(usersTable)
  .values({ name: 'John' })
  .returning();

// 2. Returns empty array if no rows affected
const updated = await db.update(usersTable)
  .set({ name: 'John' })
  .where(eq(usersTable.id, 999)) // Doesn't exist
  .returning();
// updated = []

// 3. Requires SQLite 3.35.0+
// Check your SQLite version:
const version = db.get<{ version: string }>(sql`SELECT sqlite_version() as version`);
console.log(version?.version); // Should be >= "3.35.0"

// 4. Cannot use with prepared statements in some cases
// This may fail on older drivers:
const stmt = db.insert(usersTable)
  .values({ name: sql.placeholder('name') })
  .returning()
  .prepare();

/**
 * Transaction support in SQLite
 */

// Basic transaction
await db.transaction(async (tx) => {
  await tx.insert(usersTable).values({ name: 'John' });
  await tx.insert(postsTable).values({ userId: 1, title: 'Hello' });
});

// Transaction with return value
const userId = await db.transaction(async (tx) => {
  const [user] = await tx.insert(usersTable)
    .values({ name: 'John', email: 'john@example.com' })
    .returning({ id: usersTable.id });

  await tx.insert(profilesTable)
    .values({ userId: user.id, bio: 'Hello, world!' });

  return user.id;
});

// Transaction with rollback
try {
  await db.transaction(async (tx) => {
    await tx.insert(usersTable).values({ name: 'John' });

    // Something goes wrong
    throw new Error('Rollback!');

    // This won't execute
    await tx.insert(postsTable).values({ userId: 1, title: 'Hello' });
  });
} catch (error) {
  console.log('Transaction rolled back');
}

// Manual rollback
await db.transaction(async (tx) => {
  await tx.insert(usersTable).values({ name: 'John' });

  const valid = await validateUser(user);
  if (!valid) {
    // Explicitly rollback
    tx.rollback();
  }

  await tx.insert(postsTable).values({ userId: 1, title: 'Hello' });
});

// Nested transactions (savepoints)
await db.transaction(async (tx) => {
  await tx.insert(usersTable).values({ name: 'John' });

  // Inner transaction becomes a savepoint
  await tx.transaction(async (inner) => {
    await inner.insert(postsTable).values({ userId: 1, title: 'Hello' });

    // This only rolls back the inner transaction
    if (someCondition) {
      inner.rollback();
    }
  });

  // Outer transaction continues
  await tx.insert(profilesTable).values({ userId: 1, bio: 'Bio' });
});

// Transaction config
await db.transaction(async (tx) => {
  // Transaction logic
}, {
  behavior: 'deferred', // 'deferred' | 'immediate' | 'exclusive'
});

/**
 * Transaction behaviors:
 *
 * - deferred (default): Lock is acquired when first needed
 * - immediate: Reserved lock acquired immediately
 * - exclusive: Exclusive lock acquired immediately
 */

// Read-only transaction
await db.transaction(async (tx) => {
  const users = await tx.select().from(usersTable);
  const posts = await tx.select().from(postsTable);

  // No modifications, just reads
  return { users, posts };
}, { behavior: 'deferred' });

// Write transaction with immediate lock
await db.transaction(async (tx) => {
  await tx.insert(usersTable).values({ name: 'John' });
  await tx.update(usersTable).set({ isActive: true });
}, { behavior: 'immediate' });

/**
 * PostgreSQL vs MySQL vs SQLite type differences
 */

// 1. No native BOOLEAN type in SQLite
// PostgreSQL/MySQL:
const pgUsers = pgTable('users', {
  isActive: boolean('is_active').notNull().default(true),
});

// SQLite (uses INTEGER):
const sqliteUsers = sqliteTable('users', {
  isActive: integer('is_active', { mode: 'boolean' }).notNull().default(true),
});

// 2. No native DATE/TIMESTAMP types in SQLite
// PostgreSQL:
const pgPosts = pgTable('posts', {
  createdAt: timestamp('created_at').notNull().defaultNow(),
  publishedAt: date('published_at'),
});

// SQLite (uses INTEGER or TEXT):
const sqlitePosts = sqliteTable('posts', {
  // Unix timestamp (INTEGER)
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
  // Unix timestamp with milliseconds
  publishedAt: integer('published_at', { mode: 'timestamp_ms' }),
});

// 3. No native JSON type in SQLite
// PostgreSQL:
const pgProducts = pgTable('products', {
  metadata: jsonb('metadata'),
});

// SQLite (uses TEXT):
const sqliteProducts = sqliteTable('products', {
  metadata: text('metadata', { mode: 'json' }),
});

// 4. No ARRAY types in SQLite
// PostgreSQL:
const pgPosts = pgTable('posts', {
  tags: text('tags').array(),
});

// SQLite (use JSON or separate table):
const sqlitePosts = sqliteTable('posts', {
  tags: text('tags', { mode: 'json' }).$type<string[]>(),
});

// 5. No ENUM types in SQLite
// PostgreSQL:
const statusEnum = pgEnum('status', ['draft', 'published', 'archived']);
const pgPosts = pgTable('posts', {
  status: statusEnum('status').notNull(),
});

// SQLite (use TEXT with CHECK constraint):
const sqlitePosts = sqliteTable('posts', {
  status: text('status', {
    enum: ['draft', 'published', 'archived']
  }).notNull(),
}, (table) => ({
  statusCheck: check('status_check',
    sql`${table.status} IN ('draft', 'published', 'archived')`
  ),
}));

// 6. No SERIAL/BIGSERIAL in SQLite
// PostgreSQL:
const pgUsers = pgTable('users', {
  id: serial('id').primaryKey(),
});

// SQLite (use INTEGER with autoIncrement):
const sqliteUsers = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
});

/**
 * Feature availability differences
 */

// 1. RETURNING clause
// PostgreSQL: Always available
// MySQL: Not available (use LAST_INSERT_ID())
// SQLite: Available in 3.35.0+

// PostgreSQL/SQLite:
const users = await db.insert(usersTable)
  .values({ name: 'John' })
  .returning();

// MySQL (no RETURNING):
const result = await db.insert(usersTable)
  .values({ name: 'John' });
// Access via result.insertId

// 2. ON CONFLICT
// PostgreSQL: ON CONFLICT (full featured)
// MySQL: ON DUPLICATE KEY UPDATE
// SQLite: ON CONFLICT (similar to PostgreSQL)

// PostgreSQL/SQLite:
await db.insert(users)
  .values({ email: 'test@example.com' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Updated' },
  });

// MySQL:
await db.insert(users)
  .values({ email: 'test@example.com' })
  .onDuplicateKeyUpdate({
    set: { name: 'Updated' },
  });

// 3. SELECT DISTINCT ON
// PostgreSQL: Supported
// MySQL: Not supported
// SQLite: Not supported

// PostgreSQL only:
const latest = await db.selectDistinctOn([posts.userId], {
  userId: posts.userId,
  title: posts.title,
})
  .from(posts)
  .orderBy(posts.userId, desc(posts.createdAt));

// SQLite/MySQL alternative (use window functions or subquery):
const latest = await db.select()
  .from(posts)
  .where(sql`
    ${posts.id} IN (
      SELECT id FROM posts p2
      WHERE p2.user_id = ${posts.userId}
      ORDER BY created_at DESC
      LIMIT 1
    )
  `);

// 4. Row locking (FOR UPDATE)
// PostgreSQL: Full support
// MySQL: Full support
// SQLite: Not supported (entire database is locked)

// PostgreSQL/MySQL:
const user = await db.select()
  .from(users)
  .where(eq(users.id, 1))
  .for('update');

// SQLite: Use transactions instead
await db.transaction(async (tx) => {
  const user = await tx.select().from(users).where(eq(users.id, 1));
  // Entire database is locked during transaction
  await tx.update(users).set({ balance: user[0].balance + 100 });
});

// 5. Full text search
// PostgreSQL: Built-in (tsvector)
// MySQL: Built-in (FULLTEXT)
// SQLite: Extension (FTS5)

// PostgreSQL:
const pgPosts = pgTable('posts', {
  content: text('content'),
  searchVector: sql`to_tsvector('english', ${content})`,
});

// MySQL:
const mysqlPosts = mysqlTable('posts', {
  content: text('content'),
}, (table) => ({
  contentIdx: fullTextIndex('content_idx').on(table.content),
}));

// SQLite FTS5 (requires extension):
// CREATE VIRTUAL TABLE posts_fts USING fts5(content);

// 6. Materialized views
// PostgreSQL: Supported
// MySQL: Not supported (manual)
// SQLite: Not supported (manual)

// PostgreSQL only:
const userStats = pgMaterializedView('user_stats').as((qb) =>
  qb.select({
    userId: users.id,
    postCount: sql<number>`count(${posts.id})`,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .groupBy(users.id)
);

// Refresh materialized view
await db.refreshMaterializedView(userStats);

// SQLite/MySQL: Create regular view or cache in table

/**
 * SQL syntax differences
 */

// 1. String concatenation
// PostgreSQL: || or CONCAT()
const pgResult = await db.select({
  fullName: sql`${users.firstName} || ' ' || ${users.lastName}`,
}).from(users);

// MySQL: CONCAT()
const mysqlResult = await db.select({
  fullName: sql`CONCAT(${users.firstName}, ' ', ${users.lastName})`,
}).from(users);

// SQLite: || only
const sqliteResult = await db.select({
  fullName: sql`${users.firstName} || ' ' || ${users.lastName}`,
}).from(users);

// 2. Date/time functions
// PostgreSQL: NOW(), CURRENT_TIMESTAMP
const pgNow = sql`NOW()`;

// MySQL: NOW(), CURRENT_TIMESTAMP
const mysqlNow = sql`NOW()`;

// SQLite: datetime('now'), CURRENT_TIMESTAMP
const sqliteNow = sql`datetime('now')`;
const sqliteTimestamp = sql`CURRENT_TIMESTAMP`;

// 3. Auto-increment
// PostgreSQL: SERIAL, GENERATED ALWAYS AS IDENTITY
const pgTable = pgTable('users', {
  id: serial('id').primaryKey(),
});

// MySQL: AUTO_INCREMENT
const mysqlTable = mysqlTable('users', {
  id: int('id').primaryKey().autoIncrement(),
});

// SQLite: AUTOINCREMENT
const sqliteTable = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
});

// 4. Limit/Offset
// All support LIMIT and OFFSET, but with different syntax

// PostgreSQL/SQLite/MySQL (modern):
const results = await db.select()
  .from(users)
  .limit(10)
  .offset(20);

// MySQL (old syntax):
// SELECT * FROM users LIMIT 20, 10

// 5. ILIKE (case-insensitive LIKE)
// PostgreSQL: ILIKE
const pgResults = await db.select()
  .from(users)
  .where(ilike(users.name, '%john%'));

// MySQL/SQLite: Use LOWER()
const sqliteResults = await db.select()
  .from(users)
  .where(sql`LOWER(${users.name}) LIKE LOWER('%john%')`);

/**
 * Performance differences
 */

// 1. Concurrent writes
// PostgreSQL: Excellent (MVCC, row-level locking)
// MySQL InnoDB: Good (row-level locking)
// SQLite: Limited (database-level locking, one writer at a time)

// SQLite: Use WAL mode for better concurrency
db.run(sql`PRAGMA journal_mode = WAL`);

// 2. Large datasets
// PostgreSQL: Excellent (designed for large datasets)
// MySQL: Good (especially with InnoDB)
// SQLite: Good for small-medium datasets (< 100GB recommended)

// 3. Connection pooling
// PostgreSQL/MySQL: Essential for web applications
// SQLite: Not applicable (file-based, use single connection)

// 4. Network latency
// PostgreSQL/MySQL: Network overhead exists
// SQLite: No network overhead (embedded)

// 5. Transaction performance
// PostgreSQL: Excellent
// MySQL InnoDB: Good
// SQLite: Excellent (when using WAL mode)

// Optimize SQLite for performance:
db.run(sql`PRAGMA journal_mode = WAL`);       // Write-Ahead Logging
db.run(sql`PRAGMA synchronous = NORMAL`);     // Faster commits
db.run(sql`PRAGMA cache_size = -64000`);      // 64MB cache
db.run(sql`PRAGMA temp_store = MEMORY`);      // Temp tables in memory
db.run(sql`PRAGMA mmap_size = 30000000000`);  // Memory-mapped I/O

/**
 * SQLite PRAGMA statements for configuration
 */

// Enable foreign keys (required for foreign key constraints)
db.run(sql`PRAGMA foreign_keys = ON`);

// Set journal mode
db.run(sql`PRAGMA journal_mode = WAL`);  // Write-Ahead Logging (recommended)
db.run(sql`PRAGMA journal_mode = DELETE`); // Default mode
db.run(sql`PRAGMA journal_mode = MEMORY`); // In-memory journal

// Set synchronous mode
db.run(sql`PRAGMA synchronous = FULL`);   // Maximum safety (slow)
db.run(sql`PRAGMA synchronous = NORMAL`); // Balanced (recommended)
db.run(sql`PRAGMA synchronous = OFF`);    // Fastest (unsafe)

// Cache size (negative value is KB)
db.run(sql`PRAGMA cache_size = -64000`);  // 64MB cache

// Auto vacuum
db.run(sql`PRAGMA auto_vacuum = FULL`);
db.run(sql`PRAGMA auto_vacuum = INCREMENTAL`);
db.run(sql`PRAGMA auto_vacuum = NONE`);

// Get database info
const userVersion = db.get(sql`PRAGMA user_version`);
const pageCount = db.get(sql`PRAGMA page_count`);
const pageSize = db.get(sql`PRAGMA page_size`);
const foreignKeys = db.get(sql`PRAGMA foreign_keys`);

// Table info
const tableInfo = db.all(sql`PRAGMA table_info(users)`);
const indexes = db.all(sql`PRAGMA index_list(users)`);
const foreignKeyList = db.all(sql`PRAGMA foreign_key_list(users)`);

// Integrity check
const integrityCheck = db.all(sql`PRAGMA integrity_check`);

// Optimize database
db.run(sql`PRAGMA optimize`);
db.run(sql`VACUUM`);

/**
 * Recommended SQLite initialization for web applications
 */
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('app.db');

// Initialize pragmas
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('foreign_keys = ON');
sqlite.pragma('synchronous = NORMAL');
sqlite.pragma('cache_size = -64000');
sqlite.pragma('temp_store = MEMORY');

const db = drizzle(sqlite, { schema });

// Verify configuration
const config = {
  journalMode: db.get<{ journal_mode: string }>(sql`PRAGMA journal_mode`),
  foreignKeys: db.get<{ foreign_keys: number }>(sql`PRAGMA foreign_keys`),
  synchronous: db.get<{ synchronous: number }>(sql`PRAGMA synchronous`),
};

console.log('SQLite Configuration:', config);