MySQL-Specific Features { .api }

import { tinyint } from 'drizzle-orm/mysql-core';

/**
 * Creates a TINYINT column
 * @param name - Column name (optional, can be set later)
 * @param config - Configuration options
 * @returns Column builder
 */
function tinyint(name?: string, config?: {
  unsigned?: boolean;
}): MySqlTinyIntBuilder;

import { mysqlTable, tinyint } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  // Signed tinyint (-128 to 127)
  age: tinyint('age'),

  // Unsigned tinyint (0 to 255)
  statusCode: tinyint('status_code', { unsigned: true }),

  // With modifiers
  priority: tinyint('priority')
    .notNull()
    .default(0),

  // With AUTO_INCREMENT
  counter: tinyint('counter', { unsigned: true })
    .autoincrement(),
});

interface MySqlIntConfig {
  unsigned?: boolean;
}

class MySqlTinyInt {
  readonly unsigned: boolean;

  // SQL type: "tinyint" or "tinyint unsigned"
  getSQLType(): string;

  // Maps string values from driver to number
  mapFromDriverValue(value: number | string): number;
}

import { smallint } from 'drizzle-orm/mysql-core';

/**
 * Creates a SMALLINT column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function smallint(name?: string, config?: {
  unsigned?: boolean;
}): MySqlSmallIntBuilder;

export const products = mysqlTable('products', {
  quantity: smallint('quantity', { unsigned: true }),

  adjustment: smallint('adjustment')
    .default(0),
});

import { mediumint } from 'drizzle-orm/mysql-core';

/**
 * Creates a MEDIUMINT column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function mediumint(name?: string, config?: {
  unsigned?: boolean;
}): MySqlMediumIntBuilder;

export const events = mysqlTable('events', {
  // Suitable for large counts but not huge
  attendeeCount: mediumint('attendee_count', { unsigned: true }),

  offset: mediumint('offset'),
});

import { int } from 'drizzle-orm/mysql-core';

/**
 * Creates an INT column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function int(name?: string, config?: {
  unsigned?: boolean;
}): MySqlIntBuilder;

export const users = mysqlTable('users', {
  // Primary key with AUTO_INCREMENT
  id: int('id', { unsigned: true })
    .primaryKey()
    .autoincrement(),

  // Regular integer
  score: int('score')
    .notNull()
    .default(0),

  // Unsigned for positive values only
  viewCount: int('view_count', { unsigned: true }),
});

import { bigint } from 'drizzle-orm/mysql-core';

/**
 * Creates a BIGINT column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function bigint(name?: string, config: {
  mode: 'number' | 'bigint';
  unsigned?: boolean;
}): MySqlBigInt53Builder | MySqlBigInt64Builder;

export const analytics = mysqlTable('analytics', {
  // Mode: 'number' - returns JavaScript number (safe up to 2^53-1)
  viewCount: bigint('view_count', {
    mode: 'number',
    unsigned: true
  }),

  // Mode: 'bigint' - returns JavaScript bigint (full 64-bit range)
  largeNumber: bigint('large_number', { mode: 'bigint' }),

  // With AUTO_INCREMENT
  id: bigint('id', { mode: 'number', unsigned: true })
    .primaryKey()
    .autoincrement(),
});

interface MySqlBigIntConfig<T extends 'number' | 'bigint'> {
  mode: T;
  unsigned?: boolean;
}

// When mode is 'number'
class MySqlBigInt53 {
  // SQL type: "bigint" or "bigint unsigned"
  // JavaScript type: number
  mapFromDriverValue(value: number | string): number;
}

// When mode is 'bigint'
class MySqlBigInt64 {
  // SQL type: "bigint" or "bigint unsigned"
  // JavaScript type: bigint
  mapFromDriverValue(value: string): bigint;
}

import { decimal } from 'drizzle-orm/mysql-core';

/**
 * Creates a DECIMAL column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function decimal(name?: string, config?: {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
  mode?: 'string' | 'number' | 'bigint';
}): MySqlDecimalBuilder;

export const products = mysqlTable('products', {
  // Mode: 'string' (default) - most precise, no rounding
  price: decimal('price', {
    precision: 10,
    scale: 2,
    mode: 'string'
  }),

  // Mode: 'number' - JavaScript number
  discountRate: decimal('discount_rate', {
    precision: 5,
    scale: 2,
    mode: 'number'
  }),

  // Mode: 'bigint' - for large integers stored as decimal
  largeValue: decimal('large_value', {
    precision: 20,
    scale: 0,
    mode: 'bigint'
  }),

  // Default precision(10, 0) renders as just "decimal"
  simpleDecimal: decimal('simple_decimal'),

  // Unsigned decimal
  positiveAmount: decimal('positive_amount', {
    precision: 12,
    scale: 2,
    unsigned: true
  }),
});

interface MySqlDecimalConfig<T extends 'string' | 'number' | 'bigint'> {
  precision?: number;  // Total number of digits
  scale?: number;      // Number of digits after decimal point
  unsigned?: boolean;
  mode?: T;
}

// Mode: 'string' (default)
class MySqlDecimal {
  readonly precision: number | undefined;
  readonly scale: number | undefined;
  readonly unsigned: boolean | undefined;

  // Returns: "decimal", "decimal(10,2)", "decimal(10,2) unsigned"
  getSQLType(): string;

  // Always returns string for precision
  mapFromDriverValue(value: unknown): string;
}

// Mode: 'number'
class MySqlDecimalNumber {
  mapFromDriverValue(value: unknown): number;
  mapToDriverValue(value: number): string;
}

// Mode: 'bigint'
class MySqlDecimalBigInt {
  mapFromDriverValue(value: unknown): bigint;
  mapToDriverValue(value: bigint): string;
}

import { double } from 'drizzle-orm/mysql-core';

/**
 * Creates a DOUBLE column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function double(name?: string, config?: {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
}): MySqlDoubleBuilder;

export const measurements = mysqlTable('measurements', {
  // Simple double
  temperature: double('temperature'),

  // With precision and scale
  coordinate: double('coordinate', {
    precision: 10,
    scale: 8
  }),

  // Unsigned
  distance: double('distance', { unsigned: true }),
});

interface MySqlDoubleConfig {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
}

class MySqlDouble {
  readonly precision: number | undefined;
  readonly scale: number | undefined;
  readonly unsigned: boolean | undefined;

  // Returns: "double", "double(10,8)", "double unsigned"
  getSQLType(): string;
}

import { float } from 'drizzle-orm/mysql-core';

/**
 * Creates a FLOAT column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function float(name?: string, config?: {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
}): MySqlFloatBuilder;

export const sensors = mysqlTable('sensors', {
  reading: float('reading'),

  percentage: float('percentage', {
    precision: 5,
    scale: 2,
    unsigned: true
  }),
});

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

/**
 * Creates a REAL column (alias for DOUBLE)
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function real(name?: string, config?: {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
}): MySqlRealBuilder;

import { varchar } from 'drizzle-orm/mysql-core';

/**
 * Creates a VARCHAR column
 * @param name - Column name (optional)
 * @param config - Configuration with required length
 * @returns Column builder
 */
function varchar<T extends readonly [string, ...string[]]>(
  name?: string,
  config: {
    length: number;
    enum?: T;  // For TypeScript enum validation
  }
): MySqlVarCharBuilder;

export const users = mysqlTable('users', {
  // Basic varchar
  username: varchar('username', { length: 255 }),

  // With not null and unique
  email: varchar('email', { length: 255 })
    .notNull()
    .unique(),

  // With enum for type safety
  role: varchar('role', {
    length: 50,
    enum: ['admin', 'user', 'guest'] as const
  }),

  // With default
  status: varchar('status', { length: 20 })
    .default('active'),
});

interface MySqlVarCharConfig<TEnum, TLength> {
  length: TLength;  // Required
  enum?: TEnum;     // Optional enum values
}

class MySqlVarChar<T> {
  readonly length: number | undefined;
  readonly enumValues: readonly string[] | undefined;

  // Returns: "varchar" or "varchar(255)"
  getSQLType(): string;
}

import { char } from 'drizzle-orm/mysql-core';

/**
 * Creates a CHAR column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function char<T extends readonly [string, ...string[]]>(
  name?: string,
  config?: {
    length?: number;
    enum?: T;
  }
): MySqlCharBuilder;

export const codes = mysqlTable('codes', {
  // Fixed-length code
  countryCode: char('country_code', { length: 2 }),

  // With enum
  grade: char('grade', {
    length: 1,
    enum: ['A', 'B', 'C', 'D', 'F'] as const
  }),

  // No length specified
  flag: char('flag'),
});

import { text, tinytext, mediumtext, longtext } from 'drizzle-orm/mysql-core';

/**
 * Creates a TEXT column
 * Maximum: 65,535 bytes (~64 KB)
 */
function text<T extends readonly [string, ...string[]]>(
  name?: string,
  config?: {
    enum?: T;
  }
): MySqlTextBuilder;

/**
 * Creates a TINYTEXT column
 * Maximum: 255 bytes
 */
function tinytext<T extends readonly [string, ...string[]]>(
  name?: string,
  config?: {
    enum?: T;
  }
): MySqlTextBuilder;

/**
 * Creates a MEDIUMTEXT column
 * Maximum: 16,777,215 bytes (~16 MB)
 */
function mediumtext<T extends readonly [string, ...string[]]>(
  name?: string,
  config?: {
    enum?: T;
  }
): MySqlTextBuilder;

/**
 * Creates a LONGTEXT column
 * Maximum: 4,294,967,295 bytes (~4 GB)
 */
function longtext<T extends readonly [string, ...string[]]>(
  name?: string,
  config?: {
    enum?: T;
  }
): MySqlTextBuilder;

export const posts = mysqlTable('posts', {
  // TINYTEXT - for very short text
  summary: tinytext('summary'),

  // TEXT - for regular content
  content: text('content').notNull(),

  // MEDIUMTEXT - for large content
  article: mediumtext('article'),

  // LONGTEXT - for very large content
  bookContent: longtext('book_content'),

  // With enum (type validation)
  category: text('category', {
    enum: ['tech', 'lifestyle', 'business'] as const
  }),
});

type MySqlTextColumnType = 'tinytext' | 'text' | 'mediumtext' | 'longtext';

interface MySqlTextConfig<TEnum> {
  enum?: TEnum;
}

class MySqlText {
  readonly textType: MySqlTextColumnType;
  readonly enumValues: readonly string[] | undefined;

  // Returns: "tinytext", "text", "mediumtext", or "longtext"
  getSQLType(): string;
}

import { binary } from 'drizzle-orm/mysql-core';

/**
 * Creates a BINARY column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function binary(name?: string, config?: {
  length?: number;
}): MySqlBinaryBuilder;

export const files = mysqlTable('files', {
  // Fixed-length hash
  md5Hash: binary('md5_hash', { length: 16 }),

  // Unspecified length
  flag: binary('flag'),
});

interface MySqlBinaryConfig {
  length?: number;
}

class MySqlBinary {
  readonly length: number | undefined;

  // Returns: "binary" or "binary(16)"
  getSQLType(): string;

  // Converts Buffer/Uint8Array to string
  mapFromDriverValue(value: string | Buffer | Uint8Array): string;
}

import { varbinary } from 'drizzle-orm/mysql-core';

/**
 * Creates a VARBINARY column
 * @param name - Column name (optional)
 * @param config - Configuration with required length
 * @returns Column builder
 */
function varbinary(name?: string, config: {
  length: number;
}): MySqlVarBinaryBuilder;

export const attachments = mysqlTable('attachments', {
  // Variable-length binary data
  thumbnail: varbinary('thumbnail', { length: 1024 }),

  // Large binary
  data: varbinary('data', { length: 65535 }),
});

import { date } from 'drizzle-orm/mysql-core';

/**
 * Creates a DATE column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function date(name?: string, config?: {
  mode?: 'date' | 'string';
}): MySqlDateBuilder;

export const events = mysqlTable('events', {
  // Mode: 'date' (default) - JavaScript Date object
  eventDate: date('event_date'),

  // Mode: 'string' - ISO string format
  startDate: date('start_date', { mode: 'string' }),

  // With default
  createdDate: date('created_date')
    .defaultNow(),
});

interface MySqlDateConfig<TMode extends 'date' | 'string'> {
  mode?: TMode;
}

// Mode: 'date' (default)
class MySqlDate {
  // SQL type: "date"
  // JavaScript type: Date
  getSQLType(): string;
  mapToDriverValue(value: Date): string;
  mapFromDriverValue(value: string): Date;
}

// Mode: 'string'
class MySqlDateString {
  // SQL type: "date"
  // JavaScript type: string
  getSQLType(): string;
}

import { datetime } from 'drizzle-orm/mysql-core';

/**
 * Creates a DATETIME column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function datetime(name?: string, config?: {
  mode?: 'date' | 'string';
  fsp?: 0 | 1 | 2 | 3 | 4 | 5 | 6;  // Fractional seconds precision
}): MySqlDateTimeBuilder;

export const logs = mysqlTable('logs', {
  // Basic datetime
  timestamp: datetime('timestamp'),

  // With fractional seconds precision (0-6)
  preciseTime: datetime('precise_time', { fsp: 3 }),  // milliseconds
  microTime: datetime('micro_time', { fsp: 6 }),      // microseconds

  // Mode: 'string' - returns ISO string
  eventTime: datetime('event_time', {
    mode: 'string',
    fsp: 3
  }),

  // With default
  createdAt: datetime('created_at', { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP(3)`),

  updatedAt: datetime('updated_at', { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .$onUpdate(() => sql`CURRENT_TIMESTAMP(3)`),
});

type DatetimeFsp = 0 | 1 | 2 | 3 | 4 | 5 | 6;

interface MySqlDatetimeConfig<TMode extends 'date' | 'string'> {
  mode?: TMode;
  fsp?: DatetimeFsp;  // Fractional seconds precision
}

// Mode: 'date' (default)
class MySqlDateTime {
  readonly fsp: number | undefined;

  // Returns: "datetime", "datetime(3)", "datetime(6)", etc.
  getSQLType(): string;

  // Converts to/from ISO format without timezone
  mapToDriverValue(value: Date): string;
  mapFromDriverValue(value: string): Date;
}

// Mode: 'string'
class MySqlDateTimeString {
  readonly fsp: number | undefined;

  // Returns datetime as string
  getSQLType(): string;
}

import { time } from 'drizzle-orm/mysql-core';

/**
 * Creates a TIME column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function time(name?: string, config?: {
  fsp?: 0 | 1 | 2 | 3 | 4 | 5 | 6;  // Fractional seconds precision
}): MySqlTimeBuilder;

export const schedules = mysqlTable('schedules', {
  // Basic time
  openTime: time('open_time'),

  // With fractional seconds
  preciseTime: time('precise_time', { fsp: 3 }),

  // With default
  defaultTime: time('default_time')
    .default('09:00:00'),
});

import { timestamp } from 'drizzle-orm/mysql-core';

/**
 * Creates a TIMESTAMP column
 * @param name - Column name (optional)
 * @param config - Configuration options
 * @returns Column builder
 */
function timestamp(name?: string, config?: {
  mode?: 'date' | 'string';
  fsp?: 0 | 1 | 2 | 3 | 4 | 5 | 6;  // Fractional seconds precision
}): MySqlTimestampBuilder;

export const posts = mysqlTable('posts', {
  // Basic timestamp
  publishedAt: timestamp('published_at'),

  // With fractional seconds
  createdAt: timestamp('created_at', { fsp: 3 })
    .notNull()
    .defaultNow(),

  // Mode: 'string'
  lastModified: timestamp('last_modified', {
    mode: 'string',
    fsp: 3
  }),

  // Auto-update on row update
  updatedAt: timestamp('updated_at', { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .$onUpdateFn(() => sql`CURRENT_TIMESTAMP(3)`),
});

type TimestampFsp = 0 | 1 | 2 | 3 | 4 | 5 | 6;

interface MySqlTimestampConfig<TMode extends 'date' | 'string'> {
  mode?: TMode;
  fsp?: TimestampFsp;
}

class MySqlTimestamp {
  readonly fsp: number | undefined;

  // Returns: "timestamp", "timestamp(3)", etc.
  getSQLType(): string;

  // Stores UTC, converts on retrieval based on session timezone
  mapToDriverValue(value: Date): string;
  mapFromDriverValue(value: string): Date;
}

import { year } from 'drizzle-orm/mysql-core';

/**
 * Creates a YEAR column
 * @param name - Column name (optional)
 * @returns Column builder
 */
function year(name?: string): MySqlYearBuilder;

export const cars = mysqlTable('cars', {
  // Year of manufacture
  manufacturingYear: year('manufacturing_year'),

  // With default
  modelYear: year('model_year')
    .default(2024),
});

class MySqlYear {
  // SQL type: "year"
  // JavaScript type: number
  // Range: 1901-2155
  getSQLType(): string;
}

import { boolean } from 'drizzle-orm/mysql-core';

/**
 * Creates a BOOLEAN column (TINYINT(1))
 * @param name - Column name (optional)
 * @returns Column builder
 */
function boolean(name?: string): MySqlBooleanBuilder;

export const users = mysqlTable('users', {
  isActive: boolean('is_active')
    .notNull()
    .default(true),

  emailVerified: boolean('email_verified')
    .default(false),

  isAdmin: boolean('is_admin')
    .notNull()
    .default(false),
});

class MySqlBoolean {
  // SQL type: "boolean" (renders as TINYINT(1))
  // JavaScript type: boolean
  // Stored as: 0 (false) or 1 (true)
  getSQLType(): string;
  mapFromDriverValue(value: number): boolean;
  mapToDriverValue(value: boolean): number;
}

import { json } from 'drizzle-orm/mysql-core';

/**
 * Creates a JSON column
 * @param name - Column name (optional)
 * @returns Column builder
 */
function json<T = unknown>(name?: string): MySqlJsonBuilder<T>;

interface UserPreferences {
  theme: 'light' | 'dark';
  notifications: boolean;
  language: string;
}

interface ProductMetadata {
  tags: string[];
  attributes: Record<string, any>;
}

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),

  // Generic JSON
  settings: json('settings'),

  // Typed JSON with interface
  preferences: json<UserPreferences>('preferences')
    .notNull()
    .default({ theme: 'light', notifications: true, language: 'en' }),

  // Array type
  tags: json<string[]>('tags')
    .default([]),

  // Complex object
  metadata: json<ProductMetadata>('metadata'),
});

// Usage in queries
await db.insert(users).values({
  preferences: {
    theme: 'dark',
    notifications: false,
    language: 'es'
  }
});

const result = await db.select().from(users);
console.log(result[0].preferences.theme); // TypeScript knows this is a string

class MySqlJson<T> {
  // SQL type: "json"
  // JavaScript type: T (generic)
  getSQLType(): string;

  // Automatically serializes/deserializes
  mapToDriverValue(value: T): string;
  mapFromDriverValue(value: string): T;
}

import { mysqlEnum } from 'drizzle-orm/mysql-core';

/**
 * Creates a MySQL ENUM column
 * @param name - Column name (optional)
 * @param values - Array of enum values or TypeScript enum object
 * @returns Column builder
 */
function mysqlEnum<T extends readonly [string, ...string[]]>(
  name?: string,
  values: T
): MySqlEnumColumnBuilder;

function mysqlEnum<E extends Record<string, string>>(
  name?: string,
  enumObj: E
): MySqlEnumObjectColumnBuilder;

// Array-based enum
export const users = mysqlTable('users', {
  role: mysqlEnum('role', ['admin', 'user', 'guest'] as const)
    .notNull()
    .default('user'),

  status: mysqlEnum('status', ['active', 'inactive', 'suspended'] as const),
});

// TypeScript enum
enum UserRole {
  Admin = 'admin',
  User = 'user',
  Guest = 'guest'
}

export const accounts = mysqlTable('accounts', {
  role: mysqlEnum('role', UserRole)
    .notNull()
    .default(UserRole.User),
});

// Usage in queries
await db.insert(users).values({
  role: 'admin',  // Type-safe: only 'admin' | 'user' | 'guest' allowed
  status: 'active'
});

// Invalid value causes TypeScript error
await db.insert(users).values({
  role: 'superuser',  // ❌ Error: Type '"superuser"' is not assignable
});

// For array-based enums
class MySqlEnumColumn<T> {
  readonly enumValues: readonly string[];

  // Returns: "enum('admin','user','guest')"
  getSQLType(): string;
}

// For TypeScript enum objects
class MySqlEnumObjectColumn<T> {
  readonly enumValues: string[];

  // Returns: "enum('admin','user','guest')"
  getSQLType(): string;
}

// Multiple enums
export const orders = mysqlTable('orders', {
  id: int('id').primaryKey(),

  status: mysqlEnum('status', [
    'pending',
    'processing',
    'shipped',
    'delivered',
    'cancelled'
  ] as const).notNull().default('pending'),

  priority: mysqlEnum('priority', [
    'low',
    'medium',
    'high',
    'urgent'
  ] as const).notNull().default('medium'),

  paymentMethod: mysqlEnum('payment_method', [
    'credit_card',
    'debit_card',
    'paypal',
    'bank_transfer'
  ] as const),
});

// Query with enum filtering
const urgentOrders = await db
  .select()
  .from(orders)
  .where(eq(orders.priority, 'urgent'));

import { mysqlSchema } from 'drizzle-orm/mysql-core';

/**
 * Creates a MySQL schema/database namespace
 * @param name - Schema/database name
 * @returns Schema instance with table and view methods
 */
function mysqlSchema<TName extends string>(
  name: TName
): MySqlSchema<TName>;

// Alias
const mysqlDatabase = mysqlSchema;

import { mysqlSchema, int, varchar } from 'drizzle-orm/mysql-core';

// Create schema
export const mySchema = mysqlSchema('my_schema');

// Define tables within schema
export const users = mySchema.table('users', {
  id: int('id').primaryKey().autoincrement(),
  name: varchar('name', { length: 255 }).notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
}, (table) => [
  index('name_idx').on(table.name),
]);

export const posts = mySchema.table('posts', {
  id: int('id').primaryKey().autoincrement(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  authorId: int('author_id').notNull(),
}, (table) => [
  foreignKey({
    columns: [table.authorId],
    foreignColumns: [users.id],
  }),
]);

// Define views within schema
export const activeUsers = mySchema.view('active_users').as((qb) =>
  qb.select().from(users).where(eq(users.isActive, true))
);

class MySqlSchema<TName extends string> {
  readonly schemaName: TName;

  /**
   * Creates a table within this schema
   */
  table: MySqlTableFn<TName>;

  /**
   * Creates a view within this schema
   */
  view: typeof mysqlView;
}

// Application database
export const appSchema = mysqlSchema('app');

export const users = appSchema.table('users', {
  id: int('id').primaryKey(),
  username: varchar('username', { length: 100 }),
});

// Analytics database
export const analyticsSchema = mysqlSchema('analytics');

export const pageViews = analyticsSchema.table('page_views', {
  id: bigint('id', { mode: 'number' }).primaryKey(),
  url: varchar('url', { length: 500 }),
  timestamp: timestamp('timestamp', { fsp: 3 }),
});

// Queries
const userList = await db.select().from(users);
const analytics = await db.select().from(pageViews);

import { mysqlView } from 'drizzle-orm/mysql-core';

/**
 * Creates a MySQL view
 * @param name - View name
 * @param columns - Optional column definitions for existing views
 * @returns View builder
 */
function mysqlView<TName extends string>(
  name: TName
): ViewBuilder<TName>;

function mysqlView<
  TName extends string,
  TColumns extends Record<string, MySqlColumnBuilderBase>
>(
  name: TName,
  columns: TColumns
): ManualViewBuilder<TName, TColumns>;

import { mysqlView, mysqlTable, int, varchar } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  name: varchar('name', { length: 255 }),
  email: varchar('email', { length: 255 }),
  isActive: boolean('is_active'),
  roleId: int('role_id'),
});

// Simple view
export const activeUsers = mysqlView('active_users')
  .as((qb) => qb.select().from(users).where(eq(users.isActive, true)));

// View with selected fields
export const userSummary = mysqlView('user_summary')
  .as((qb) => qb.select({
    id: users.id,
    name: users.name,
    email: users.email,
  }).from(users));

// View with joins
export const userWithRole = mysqlView('user_with_role')
  .as((qb) => qb
    .select({
      userId: users.id,
      userName: users.name,
      roleName: roles.name,
    })
    .from(users)
    .leftJoin(roles, eq(users.roleId, roles.id))
  );

// Query views like tables
const active = await db.select().from(activeUsers);
const summary = await db.select().from(userSummary).where(eq(userSummary.id, 1));

// Define structure for existing view
export const existingView = mysqlView('existing_view', {
  id: int('id'),
  name: varchar('name', { length: 255 }),
  total: int('total'),
}).existing();

// Use like any other table
const data = await db.select().from(existingView);

// With algorithm
export const mergeView = mysqlView('merge_view')
  .algorithm('merge')  // 'undefined' | 'merge' | 'temptable'
  .as((qb) => qb.select().from(users));

// With SQL security
export const secureView = mysqlView('secure_view')
  .sqlSecurity('definer')  // 'definer' | 'invoker'
  .as((qb) => qb.select().from(users));

// With check option
export const checkedView = mysqlView('checked_view')
  .withCheckOption('cascaded')  // 'cascaded' | 'local'
  .as((qb) => qb.select().from(users).where(eq(users.isActive, true)));

// Combined
export const complexView = mysqlView('complex_view')
  .algorithm('merge')
  .sqlSecurity('definer')
  .withCheckOption('cascaded')
  .as((qb) => qb.select().from(users));

interface ViewBuilderConfig {
  algorithm?: 'undefined' | 'merge' | 'temptable';
  sqlSecurity?: 'definer' | 'invoker';
  withCheckOption?: 'cascaded' | 'local';
}

class ViewBuilder<TName extends string> {
  /**
   * Sets the view algorithm
   */
  algorithm(algorithm: 'undefined' | 'merge' | 'temptable'): this;

  /**
   * Sets SQL security context
   */
  sqlSecurity(security: 'definer' | 'invoker'): this;

  /**
   * Enables WITH CHECK OPTION
   */
  withCheckOption(option?: 'cascaded' | 'local'): this;

  /**
   * Defines the view query
   */
  as<TSelectedFields>(
    query: TypedQueryBuilder<TSelectedFields>
  ): MySqlView<TName, false, TSelectedFields>;
}

import { mysqlTable, int, varchar, index } from 'drizzle-orm/mysql-core';

/**
 * Creates a regular index
 * @param name - Index name
 * @returns Index builder
 */
function index(name: string): IndexBuilderOn;

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  name: varchar('name', { length: 255 }),
  city: varchar('city', { length: 100 }),
  createdAt: timestamp('created_at'),
}, (table) => [
  // Simple index
  index('email_idx').on(table.email),

  // Composite index
  index('name_city_idx').on(table.name, table.city),

  // With USING clause (btree or hash)
  index('city_idx').on(table.city).using('btree'),

  // With algorithm
  index('created_idx')
    .on(table.createdAt)
    .algorithm('inplace'),  // 'default' | 'inplace' | 'copy'

  // With lock
  index('name_idx')
    .on(table.name)
    .lock('none'),  // 'default' | 'none' | 'shared' | 'exclusive'
]);

import { uniqueIndex } from 'drizzle-orm/mysql-core';

/**
 * Creates a unique index
 * @param name - Index name
 * @returns Index builder
 */
function uniqueIndex(name: string): IndexBuilderOn;

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  username: varchar('username', { length: 100 }),
  slug: varchar('slug', { length: 255 }),
}, (table) => [
  // Unique index
  uniqueIndex('email_unique').on(table.email),

  // Composite unique index
  uniqueIndex('username_slug_unique').on(table.username, table.slug),

  // With options
  uniqueIndex('slug_unique')
    .on(table.slug)
    .using('btree')
    .algorithm('inplace'),
]);

// Alternative: use .unique() on column
export const accounts = mysqlTable('accounts', {
  id: int('id').primaryKey(),
  email: varchar('email', { length: 255 }).unique(),  // Creates unique index automatically
  username: varchar('username', { length: 100 }).unique('custom_username_unique'),
});

export const products = mysqlTable('products', {
  id: int('id').primaryKey(),
  sku: varchar('sku', { length: 100 }),
  category: varchar('category', { length: 50 }),
  price: decimal('price', { precision: 10, scale: 2 }),
}, (table) => [
  // BTREE (default) - good for range queries, ordering
  index('sku_idx')
    .on(table.sku)
    .using('btree'),

  // HASH - good for exact match queries only
  index('category_idx')
    .on(table.category)
    .using('hash'),
]);

export const largeTable = mysqlTable('large_table', {
  id: int('id').primaryKey(),
  data: text('data'),
  status: varchar('status', { length: 50 }),
}, (table) => [
  // Control how index is built
  index('status_idx')
    .on(table.status)
    .algorithm('inplace')  // Build index without table copy
    .lock('none'),         // Allow concurrent reads/writes

  // Force table copy (slower but sometimes necessary)
  index('data_idx')
    .on(table.data)
    .algorithm('copy')
    .lock('shared'),      // Allow reads, block writes
]);

interface IndexConfig {
  name: string;
  columns: IndexColumn[];
  unique?: boolean;
  using?: 'btree' | 'hash';
  algorithm?: 'default' | 'inplace' | 'copy';
  lock?: 'default' | 'none' | 'shared' | 'exclusive';
}

class IndexBuilder {
  /**
   * Sets index method
   */
  using(method: 'btree' | 'hash'): this;

  /**
   * Sets algorithm for index creation
   */
  algorithm(algorithm: 'default' | 'inplace' | 'copy'): this;

  /**
   * Sets lock level during index creation
   */
  lock(lock: 'default' | 'none' | 'shared' | 'exclusive'): this;
}

import { sql } from 'drizzle-orm';

// Full-text index (use in migrations)
await db.execute(sql`
  CREATE FULLTEXT INDEX content_fulltext
  ON posts(title, content)
`);

// Spatial index
await db.execute(sql`
  CREATE SPATIAL INDEX location_spatial
  ON places(coordinates)
`);

// Full-text search query
const results = await db.execute(sql`
  SELECT * FROM posts
  WHERE MATCH(title, content) AGAINST('search term' IN BOOLEAN MODE)
`);

import { mysqlTable, int, varchar } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  email: varchar('email', { length: 255 }).unique(),
  name: varchar('name', { length: 255 }),
  loginCount: int('login_count').default(0),
});

/**
 * ON DUPLICATE KEY UPDATE clause
 * Updates specified columns if a duplicate key error occurs
 */

// Basic usage
await db.insert(users)
  .values({
    id: 1,
    email: 'user@example.com',
    name: 'John Doe',
    loginCount: 1,
  })
  .onDuplicateKeyUpdate({
    set: {
      name: 'John Doe',
      loginCount: sql`${users.loginCount} + 1`,
    },
  });

// Using VALUES() function to reference insert values
await db.insert(users)
  .values({
    email: 'user@example.com',
    name: 'Jane Smith',
    loginCount: 1,
  })
  .onDuplicateKeyUpdate({
    set: {
      name: sql`VALUES(${users.name})`,
      loginCount: sql`${users.loginCount} + VALUES(${users.loginCount})`,
    },
  });

// Update all columns except primary key
await db.insert(products)
  .values(newProduct)
  .onDuplicateKeyUpdate({
    set: {
      name: sql`VALUES(${products.name})`,
      price: sql`VALUES(${products.price})`,
      stock: sql`VALUES(${products.stock})`,
      updatedAt: sql`NOW()`,
    },
  });

// Bulk insert with conflict handling
await db.insert(users)
  .values([
    { email: 'user1@example.com', name: 'User 1', loginCount: 1 },
    { email: 'user2@example.com', name: 'User 2', loginCount: 1 },
    { email: 'user3@example.com', name: 'User 3', loginCount: 1 },
  ])
  .onDuplicateKeyUpdate({
    set: {
      loginCount: sql`${users.loginCount} + 1`,
    },
  });

interface MySqlInsertOnDuplicateKeyUpdateConfig<T> {
  set: MySqlUpdateSetSource<T>;
}

class MySqlInsertBase<TTable> {
  /**
   * Adds ON DUPLICATE KEY UPDATE clause
   * @param config - Configuration with columns to update
   */
  onDuplicateKeyUpdate(
    config: MySqlInsertOnDuplicateKeyUpdateConfig<TTable>
  ): this;
}

/**
 * INSERT IGNORE statement
 * Silently ignores rows that would cause duplicate key errors
 */

// Single insert with ignore
await db.insert(users)
  .ignore()
  .values({
    email: 'user@example.com',
    name: 'John Doe',
  });

// Bulk insert with ignore
await db.insert(users)
  .ignore()
  .values([
    { email: 'user1@example.com', name: 'User 1' },
    { email: 'user2@example.com', name: 'User 2' },
    { email: 'user3@example.com', name: 'User 3' },
  ]);

// Combined with other clauses (ignore must come first)
await db.insert(logs)
  .ignore()
  .values(logEntries);

// Returns number of inserted rows (excluding ignored)
const result = await db.insert(users)
  .ignore()
  .values(userData);

class MySqlInsertBuilder<TTable> {
  /**
   * Enables INSERT IGNORE mode
   * @returns Insert builder for chaining
   */
  ignore(): this;

  /**
   * Specifies values to insert
   */
  values(
    values: MySqlInsertValue<TTable> | MySqlInsertValue<TTable>[]
  ): MySqlInsertBase<TTable>;
}

// INSERT IGNORE - silently skips duplicates
await db.insert(users)
  .ignore()
  .values({ email: 'exists@example.com', name: 'New Name' });
// If email exists, nothing happens. Existing row unchanged.

// ON DUPLICATE KEY UPDATE - updates existing row
await db.insert(users)
  .values({ email: 'exists@example.com', name: 'New Name' })
  .onDuplicateKeyUpdate({
    set: { name: 'New Name' }
  });
// If email exists, updates the name field.

import { mysqlTable, int, bigint, tinyint, smallint, mediumint } from 'drizzle-orm/mysql-core';

/**
 * AUTO_INCREMENT modifier
 * Can be applied to any integer type column
 */

// INT with AUTO_INCREMENT
export const users = mysqlTable('users', {
  id: int('id', { unsigned: true })
    .primaryKey()
    .autoincrement(),

  name: varchar('name', { length: 255 }),
});

// BIGINT with AUTO_INCREMENT
export const logs = mysqlTable('logs', {
  id: bigint('id', { mode: 'number', unsigned: true })
    .primaryKey()
    .autoincrement(),

  message: text('message'),
});

// TINYINT with AUTO_INCREMENT (for small tables)
export const status = mysqlTable('status', {
  id: tinyint('id', { unsigned: true })
    .primaryKey()
    .autoincrement(),

  name: varchar('name', { length: 50 }),
});

// SMALLINT with AUTO_INCREMENT
export const categories = mysqlTable('categories', {
  id: smallint('id', { unsigned: true })
    .primaryKey()
    .autoincrement(),

  name: varchar('name', { length: 100 }),
});

// MEDIUMINT with AUTO_INCREMENT
export const products = mysqlTable('products', {
  id: mediumint('id', { unsigned: true })
    .primaryKey()
    .autoincrement(),

  name: varchar('name', { length: 255 }),
});

import { serial } from 'drizzle-orm/mysql-core';

/**
 * Creates BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY column
 * @param name - Column name (optional)
 * @returns Serial column builder
 */
function serial(name?: string): MySqlSerialBuilder;

export const posts = mysqlTable('posts', {
  // Equivalent to: bigint('id', { unsigned: true }).primaryKey().autoincrement()
  id: serial('id'),

  title: varchar('title', { length: 255 }),
  content: text('content'),
});

// Insert without specifying ID
await db.insert(posts).values({
  title: 'First Post',
  content: 'Content here',
});
// ID is automatically generated

// Get last inserted ID
const result = await db.insert(posts).values({
  title: 'Second Post',
  content: 'More content',
});
console.log(result.insertId); // Auto-generated ID

class MySqlSerialBuilder {
  // Automatically includes:
  // - notNull()
  // - primaryKey()
  // - autoincrement()
  // - default value (auto-generated)
}

class MySqlSerial {
  // SQL type: "serial"
  // Translates to: BIGINT UNSIGNED AUTO_INCREMENT
  getSQLType(): string;

  // Maps string/number from driver to number
  mapFromDriverValue(value: number | string): number;
}

// Set starting value in table options (use raw SQL or migrations)
await db.execute(sql`
  ALTER TABLE users AUTO_INCREMENT = 1000
`);

// Or in CREATE TABLE
await db.execute(sql`
  CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
  ) AUTO_INCREMENT = 1000
`);

// ❌ Not supported in MySQL
await db.insert(users)
  .values({ name: 'John' })
  .returning(); // Error: MySQL doesn't support RETURNING

// ✅ Use separate SELECT
const result = await db.insert(users).values({ name: 'John' });
const insertedUser = await db
  .select()
  .from(users)
  .where(eq(users.id, result.insertId));

// ❌ PostgreSQL style (not in MySQL)
// db.selectDistinctOn([users.email]).from(users);

// ✅ MySQL alternative
await db
  .select()
  .from(users)
  .groupBy(users.email);

// ❌ Not available in MySQL
// const tags = pgArray('tags', varchar(100));

// ✅ Use JSON instead
export const posts = mysqlTable('posts', {
  tags: json<string[]>('tags'),
});

// ❌ Not supported in MySQL
// mysqlMaterializedView('view_name').as(query);

// ✅ Use regular views
export const view = mysqlView('view_name').as(query);

// Only BTREE and HASH available
index('idx').on(table.column).using('btree');
index('idx').on(table.column).using('hash');

// Full-text and spatial indexes require raw SQL

// ✅ Recommended: use lowercase
export const users = mysqlTable('users', {
  userId: int('user_id'), // Note: use snake_case in MySQL
});

// ⚠️ Avoid mixed case for portability
export const Users = mysqlTable('Users', {
  UserId: int('UserId'),
});

// Set isolation level (driver-specific)
await db.execute(sql`SET TRANSACTION ISOLATION LEVEL READ COMMITTED`);

await db.transaction(async (tx) => {
  // Transaction operations
});

// Set collation (use raw SQL or migrations)
await db.execute(sql`
  ALTER TABLE users
  MODIFY name VARCHAR(255)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci
`);

// Set storage engine (use in migrations)
await db.execute(sql`
  CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
`);

// MyISAM for read-heavy tables (no transactions)
await db.execute(sql`
  CREATE TABLE logs (
    id BIGINT PRIMARY KEY,
    message TEXT
  ) ENGINE=MyISAM
`);

export const users = mysqlTable('users', {
  id: int('id', { unsigned: true }).primaryKey().autoincrement(),
});

export const posts = mysqlTable('posts', {
  title: varchar('title', { length: 255 }),    // Short text
  excerpt: text('excerpt'),                      // Medium text (~64KB)
  content: mediumtext('content'),                // Large text (~16MB)
});

export const posts = mysqlTable('posts', {
  id: int('id').primaryKey(),
  authorId: int('author_id').notNull(),
}, (table) => [
  // Always index foreign keys in MySQL
  index('author_idx').on(table.authorId),
  foreignKey({
    columns: [table.authorId],
    foreignColumns: [users.id],
  }),
]);

// Better than VARCHAR for known values
export const orders = mysqlTable('orders', {
  status: mysqlEnum('status', ['pending', 'paid', 'shipped'] as const),
});

export const posts = mysqlTable('posts', {
  // Use TIMESTAMP for auto-update behavior
  createdAt: timestamp('created_at', { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP(3)`),

  updatedAt: timestamp('updated_at', { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP(3)`)
    .$onUpdate(() => sql`CURRENT_TIMESTAMP(3)`),
});