Schema Management

/**
 * Access the schema builder instance
 * @returns SchemaBuilder for database schema operations
 */
schema: Knex.SchemaBuilder;

interface SchemaBuilder {
  /**
   * Create a new database schema
   * @param schemaName - Name of the schema to create
   * @returns SchemaBuilder for chaining
   */
  createSchema(schemaName: string): SchemaBuilder;

  /**
   * Create schema if it doesn't exist
   * @param schemaName - Name of the schema to create
   * @returns SchemaBuilder for chaining
   */
  createSchemaIfNotExists(schemaName: string): SchemaBuilder;

  /**
   * Drop an existing schema
   * @param schemaName - Name of the schema to drop
   * @param cascade - Whether to cascade drop dependent objects
   * @returns SchemaBuilder for chaining
   */
  dropSchema(schemaName: string, cascade?: boolean): SchemaBuilder;

  /**
   * Drop schema if it exists
   * @param schemaName - Name of the schema to drop
   * @param cascade - Whether to cascade drop dependent objects
   * @returns SchemaBuilder for chaining
   */
  dropSchemaIfExists(schemaName: string, cascade?: boolean): SchemaBuilder;

  /**
   * Use a specific schema for subsequent operations
   * @param schemaName - Schema name to use
   * @returns SchemaBuilder with schema context
   */
  withSchema(schemaName: string): SchemaBuilder;
}

/**
 * Create a new table
 * @param tableName - Name of the table to create
 * @param callback - Callback to define table structure
 * @returns SchemaBuilder for chaining
 */
createTable(tableName: string, callback: (tableBuilder: CreateTableBuilder) => any): SchemaBuilder;

/**
 * Create table if it doesn't exist
 * @param tableName - Name of the table to create
 * @param callback - Callback to define table structure
 * @returns SchemaBuilder for chaining
 */
createTableIfNotExists(tableName: string, callback: (tableBuilder: CreateTableBuilder) => any): SchemaBuilder;

/**
 * Create table like an existing table
 * @param tableName - Name of the new table
 * @param likeTableName - Name of the table to copy structure from
 * @param callback - Optional callback for additional modifications
 * @returns SchemaBuilder for chaining
 */
createTableLike(tableName: string, likeTableName: string, callback?: (tableBuilder: CreateTableBuilder) => any): SchemaBuilder;

/**
 * Alter an existing table
 * @param tableName - Name of the table to alter
 * @param callback - Callback to define alterations
 * @returns SchemaBuilder for chaining
 */
alterTable(tableName: string, callback: (tableBuilder: AlterTableBuilder) => any): SchemaBuilder;

/**
 * Rename a table
 * @param oldName - Current table name
 * @param newName - New table name
 * @returns SchemaBuilder for chaining
 */
renameTable(oldName: string, newName: string): SchemaBuilder;

/**
 * Drop a table
 * @param tableName - Name of the table to drop
 * @returns SchemaBuilder for chaining
 */
dropTable(tableName: string): SchemaBuilder;

/**
 * Drop table if it exists
 * @param tableName - Name of the table to drop
 * @returns SchemaBuilder for chaining
 */
dropTableIfExists(tableName: string): SchemaBuilder;

/**
 * Check if a table exists
 * @param tableName - Name of the table to check
 * @returns Promise resolving to boolean
 */
hasTable(tableName: string): Promise<boolean>;

/**
 * Check if a column exists in a table
 * @param tableName - Name of the table
 * @param columnName - Name of the column to check
 * @returns Promise resolving to boolean
 */
hasColumn(tableName: string, columnName: string): Promise<boolean>;

/**
 * Create a database view
 * @param viewName - Name of the view to create
 * @param callback - Callback to define view query
 * @returns SchemaBuilder for chaining
 */
createView(viewName: string, callback: (viewBuilder: ViewBuilder) => any): SchemaBuilder;

/**
 * Create or replace a view
 * @param viewName - Name of the view
 * @param callback - Callback to define view query
 * @returns SchemaBuilder for chaining
 */
createViewOrReplace(viewName: string, callback: (viewBuilder: ViewBuilder) => any): SchemaBuilder;

/**
 * Create a materialized view (PostgreSQL)
 * @param viewName - Name of the materialized view
 * @param callback - Callback to define view query
 * @returns SchemaBuilder for chaining
 */
createMaterializedView(viewName: string, callback: (viewBuilder: ViewBuilder) => any): SchemaBuilder;

/**
 * Refresh a materialized view (PostgreSQL)
 * @param viewName - Name of the materialized view
 * @param concurrently - Whether to refresh concurrently
 * @returns SchemaBuilder for chaining
 */
refreshMaterializedView(viewName: string, concurrently?: boolean): SchemaBuilder;

/**
 * Drop a view
 * @param viewName - Name of the view to drop
 * @returns SchemaBuilder for chaining
 */
dropView(viewName: string): SchemaBuilder;

/**
 * Drop view if it exists
 * @param viewName - Name of the view to drop
 * @returns SchemaBuilder for chaining
 */
dropViewIfExists(viewName: string): SchemaBuilder;

/**
 * Drop a materialized view
 * @param viewName - Name of the materialized view to drop
 * @returns SchemaBuilder for chaining
 */
dropMaterializedView(viewName: string): SchemaBuilder;

/**
 * Drop materialized view if it exists
 * @param viewName - Name of the materialized view to drop
 * @returns SchemaBuilder for chaining
 */
dropMaterializedViewIfExists(viewName: string): SchemaBuilder;

/**
 * Rename a view
 * @param oldName - Current view name
 * @param newName - New view name
 * @returns SchemaBuilder for chaining
 */
renameView(oldName: string, newName: string): SchemaBuilder;

/**
 * Alter a view
 * @param viewName - Name of the view to alter
 * @param callback - Callback to define alterations
 * @returns SchemaBuilder for chaining
 */
alterView(viewName: string, callback: (viewBuilder: ViewBuilder) => any): SchemaBuilder;

interface ViewBuilder {
  /**
   * Define the view query
   * @param query - Query builder or raw SQL for the view
   * @returns ViewBuilder for chaining
   */
  as(query: QueryBuilder | Raw): ViewBuilder;

  /**
   * Set view columns
   * @param columns - Array of column names
   * @returns ViewBuilder for chaining
   */
  columns(columns: readonly string[]): ViewBuilder;
}

interface CreateTableBuilder {
  // Primary key columns
  /**
   * Auto-incrementing integer primary key
   * @param columnName - Name of the column (default: 'id')
   * @param options - Column options
   * @returns ColumnBuilder for further configuration
   */
  increments(columnName?: string, options?: { primaryKey?: boolean }): ColumnBuilder;

  /**
   * Auto-incrementing big integer primary key
   * @param columnName - Name of the column (default: 'id')
   * @returns ColumnBuilder for further configuration
   */
  bigIncrements(columnName?: string): ColumnBuilder;

  // Numeric columns
  /**
   * Integer column
   * @param columnName - Name of the column
   * @param length - Optional length specification
   * @returns ColumnBuilder for further configuration
   */
  integer(columnName: string, length?: number): ColumnBuilder;

  /**
   * Tiny integer column (MySQL: TINYINT)
   * @param columnName - Name of the column
   * @param length - Optional length specification
   * @returns ColumnBuilder for further configuration
   */
  tinyint(columnName: string, length?: number): ColumnBuilder;

  /**
   * Small integer column
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  smallint(columnName: string): ColumnBuilder;

  /**
   * Medium integer column (MySQL: MEDIUMINT)
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  mediumint(columnName: string): ColumnBuilder;

  /**
   * Big integer column
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  bigint(columnName: string): ColumnBuilder;

  /**
   * Big integer column (alias for bigint)
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  bigInteger(columnName: string): ColumnBuilder;

  /**
   * Floating point number column
   * @param columnName - Name of the column
   * @param precision - Total number of digits
   * @param scale - Number of decimal places
   * @returns ColumnBuilder for further configuration
   */
  float(columnName: string, precision?: number, scale?: number): ColumnBuilder;

  /**
   * Double precision floating point column
   * @param columnName - Name of the column
   * @param precision - Total number of digits
   * @param scale - Number of decimal places
   * @returns ColumnBuilder for further configuration
   */
  double(columnName: string, precision?: number, scale?: number): ColumnBuilder;

  /**
   * Decimal/numeric column with fixed precision
   * @param columnName - Name of the column
   * @param precision - Total number of digits
   * @param scale - Number of decimal places
   * @returns ColumnBuilder for further configuration
   */
  decimal(columnName: string, precision?: number, scale?: number): ColumnBuilder;

  // String columns
  /**
   * Variable-length string column
   * @param columnName - Name of the column
   * @param length - Maximum length (default varies by database)
   * @returns ColumnBuilder for further configuration
   */
  string(columnName: string, length?: number): ColumnBuilder;

  /**
   * Variable-length text column
   * @param columnName - Name of the column
   * @param textType - Text type ('text', 'mediumtext', 'longtext')
   * @returns ColumnBuilder for further configuration
   */
  text(columnName: string, textType?: string): ColumnBuilder;

  // Date/time columns
  /**
   * Boolean column
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  boolean(columnName: string): ColumnBuilder;

  /**
   * Date column (date only, no time)
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  date(columnName: string): ColumnBuilder;

  /**
   * DateTime column
   * @param columnName - Name of the column
   * @param options - DateTime options
   * @returns ColumnBuilder for further configuration
   */
  dateTime(columnName: string, options?: Readonly<{ useTz?: boolean; precision?: number }>): ColumnBuilder;

  /**
   * DateTime column (alias for dateTime)
   * @param columnName - Name of the column
   * @param options - DateTime options
   * @returns ColumnBuilder for further configuration
   */
  datetime(columnName: string, options?: Readonly<{ useTz?: boolean; precision?: number }>): ColumnBuilder;

  /**
   * Time column (time only, no date)
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  time(columnName: string): ColumnBuilder;

  /**
   * Timestamp column
   * @param columnName - Name of the column
   * @param options - Timestamp options
   * @returns ColumnBuilder for further configuration
   */
  timestamp(columnName: string, options?: Readonly<{ useTz?: boolean; precision?: number }>): ColumnBuilder;

  /**
   * Add created_at and updated_at timestamp columns
   * @param useTimestamps - Whether to use timestamps (default: true)
   * @param defaultToNow - Whether to default to current timestamp (default: false)
   * @param useCamelCase - Whether to use camelCase names (createdAt, updatedAt)
   * @returns CreateTableBuilder for chaining
   */
  timestamps(useTimestamps?: boolean, defaultToNow?: boolean, useCamelCase?: boolean): CreateTableBuilder;

  // Binary and specialized columns
  /**
   * Binary data column
   * @param columnName - Name of the column
   * @param length - Maximum length
   * @returns ColumnBuilder for further configuration
   */
  binary(columnName: string, length?: number): ColumnBuilder;

  /**
   * JSON column
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  json(columnName: string): ColumnBuilder;

  /**
   * JSONB column (PostgreSQL binary JSON)
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  jsonb(columnName: string): ColumnBuilder;

  /**
   * UUID column
   * @param columnName - Name of the column
   * @param options - UUID options
   * @returns ColumnBuilder for further configuration
   */
  uuid(columnName: string, options?: Readonly<{ useBinaryUuid?: boolean; primaryKey?: boolean }>): ColumnBuilder;

  /**
   * Enum column with predefined values
   * @param columnName - Name of the column
   * @param values - Array of allowed values
   * @param options - Enum options
   * @returns ColumnBuilder for further configuration
   */
  enum(columnName: string, values: readonly Value[], options?: EnumOptions): ColumnBuilder;

  /**
   * Enum column (alias for enum)
   * @param columnName - Name of the column
   * @param values - Array of allowed values
   * @param options - Enum options
   * @returns ColumnBuilder for further configuration
   */
  enu(columnName: string, values: readonly Value[], options?: EnumOptions): ColumnBuilder;

  // Geometric columns
  /**
   * Geometry column for spatial data
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  geometry(columnName: string): ColumnBuilder;

  /**
   * Geography column for spatial data
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  geography(columnName: string): ColumnBuilder;

  /**
   * Point column for geometric points
   * @param columnName - Name of the column
   * @returns ColumnBuilder for further configuration
   */
  point(columnName: string): ColumnBuilder;

  /**
   * Column with database-specific type
   * @param columnName - Name of the column
   * @param type - Database-specific type string
   * @returns ColumnBuilder for further configuration
   */
  specificType(columnName: string, type: string): ColumnBuilder;
}

interface AlterTableBuilder extends CreateTableBuilder {
  /**
   * Drop a column
   * @param columnName - Name of the column to drop
   * @returns AlterTableBuilder for chaining
   */
  dropColumn(columnName: string): AlterTableBuilder;

  /**
   * Drop multiple columns
   * @param columnNames - Names of columns to drop
   * @returns AlterTableBuilder for chaining
   */
  dropColumns(...columnNames: string[]): AlterTableBuilder;

  /**
   * Rename a column
   * @param from - Current column name
   * @param to - New column name
   * @returns AlterTableBuilder for chaining
   */
  renameColumn(from: string, to: string): AlterTableBuilder;

  /**
   * Make a column nullable
   * @param columnName - Name of the column
   * @returns AlterTableBuilder for chaining
   */
  setNullable(columnName: string): AlterTableBuilder;

  /**
   * Make a column not nullable
   * @param columnName - Name of the column
   * @returns AlterTableBuilder for chaining
   */
  dropNullable(columnName: string): AlterTableBuilder;
}

interface ColumnBuilder {
  /**
   * Add an index to the column
   * @param indexName - Optional name for the index
   * @param indexType - Optional index type
   * @returns ColumnBuilder for chaining
   */
  index(indexName?: string, indexType?: string): ColumnBuilder;

  /**
   * Make column a primary key
   * @param constraintName - Optional constraint name
   * @returns ColumnBuilder for chaining
   */
  primary(constraintName?: string): ColumnBuilder;

  /**
   * Add unique constraint to column
   * @param options - Unique constraint options
   * @returns ColumnBuilder for chaining
   */
  unique(options?: Readonly<{ indexName?: string; storageEngineIndexType?: string; deferrable?: deferrableType }>): ColumnBuilder;

  /**
   * Create foreign key reference
   * @param columnName - Referenced column name
   * @returns ReferencingColumnBuilder for foreign key configuration
   */
  references(columnName: string): ReferencingColumnBuilder;

  /**
   * Set default value for column
   * @param value - Default value
   * @param options - Default value options
   * @returns ColumnBuilder for chaining
   */
  defaultTo(value: Value, options?: DefaultToOptions): ColumnBuilder;

  /**
   * Make numeric column unsigned
   * @returns ColumnBuilder for chaining
   */
  unsigned(): ColumnBuilder;

  /**
   * Make column NOT NULL
   * @returns ColumnBuilder for chaining
   */
  notNullable(): ColumnBuilder;

  /**
   * Make column nullable
   * @returns ColumnBuilder for chaining
   */
  nullable(): ColumnBuilder;

  /**
   * Add comment to column
   * @param val - Comment text
   * @returns ColumnBuilder for chaining
   */
  comment(val: string): ColumnBuilder;

  /**
   * Alter column definition
   * @param options - Alter options
   * @returns ColumnBuilder for chaining
   */
  alter(options?: ColumnAlterOptions): ColumnBuilder;

  /**
   * Position column after another column (MySQL)
   * @param columnName - Column to position after
   * @returns ColumnBuilder for chaining
   */
  after(columnName: string): ColumnBuilder;

  /**
   * Position column first in table (MySQL)
   * @returns ColumnBuilder for chaining
   */
  first(): ColumnBuilder;

  /**
   * Set column collation
   * @param collation - Collation name
   * @returns ColumnBuilder for chaining
   */
  collate(collation: string): ColumnBuilder;

  // Check constraints
  /**
   * Add check constraint for positive values
   * @param constraintName - Optional constraint name
   * @returns ColumnBuilder for chaining
   */
  checkPositive(constraintName?: string): ColumnBuilder;

  /**
   * Add check constraint for negative values
   * @param constraintName - Optional constraint name
   * @returns ColumnBuilder for chaining
   */
  checkNegative(constraintName?: string): ColumnBuilder;

  /**
   * Add check constraint for values in list
   * @param values - Array of allowed values
   * @param constraintName - Optional constraint name
   * @returns ColumnBuilder for chaining
   */
  checkIn(values: readonly Value[], constraintName?: string): ColumnBuilder;

  /**
   * Add check constraint for values not in list
   * @param values - Array of disallowed values
   * @param constraintName - Optional constraint name
   * @returns ColumnBuilder for chaining
   */
  checkNotIn(values: readonly Value[], constraintName?: string): ColumnBuilder;

  /**
   * Add check constraint for values in range
   * @param values - Array with min and max values
   * @param constraintName - Optional constraint name
   * @returns ColumnBuilder for chaining
   */
  checkBetween(values: readonly [Value, Value], constraintName?: string): ColumnBuilder;

  /**
   * Add check constraint for string length
   * @param operator - Comparison operator
   * @param length - Length to compare against
   * @param constraintName - Optional constraint name
   * @returns ColumnBuilder for chaining
   */
  checkLength(operator: string, length: number, constraintName?: string): ColumnBuilder;

  /**
   * Add check constraint with regex pattern
   * @param regex - Regular expression pattern
   * @param constraintName - Optional constraint name
   * @returns ColumnBuilder for chaining
   */
  checkRegex(regex: string, constraintName?: string): ColumnBuilder;
}

interface ReferencingColumnBuilder extends ColumnBuilder {
  /**
   * Specify the referenced table
   * @param tableName - Name of the referenced table
   * @returns ColumnBuilder for chaining
   */
  inTable(tableName: string): ColumnBuilder;

  /**
   * Set foreign key constraint name
   * @param constraintName - Name for the foreign key constraint
   * @returns ColumnBuilder for chaining
   */
  withKeyName(constraintName: string): ColumnBuilder;

  /**
   * Set ON DELETE action
   * @param command - Action to take (CASCADE, SET NULL, RESTRICT, etc.)
   * @returns ColumnBuilder for chaining
   */
  onDelete(command: string): ColumnBuilder;

  /**
   * Set ON UPDATE action
   * @param command - Action to take (CASCADE, SET NULL, RESTRICT, etc.)
   * @returns ColumnBuilder for chaining
   */
  onUpdate(command: string): ColumnBuilder;

  /**
   * Make foreign key constraint deferrable
   * @param type - Deferrable type
   * @returns ColumnBuilder for chaining
   */
  deferrable(type: deferrableType): ColumnBuilder;
}

/**
 * Add primary key constraint
 * @param columnNames - Array of column names for composite primary key
 * @param options - Primary key options
 * @returns CreateTableBuilder for chaining
 */
primary(columnNames: readonly string[], options?: Readonly<{ constraintName?: string; deferrable?: deferrableType }>): CreateTableBuilder;

/**
 * Add unique constraint
 * @param columnNames - Array of column names
 * @param options - Unique constraint options
 * @returns CreateTableBuilder for chaining
 */
unique(columnNames: readonly string[], options?: Readonly<{ indexName?: string; storageEngineIndexType?: string; deferrable?: deferrableType }>): CreateTableBuilder;

/**
 * Add database index
 * @param columnNames - Array of column names
 * @param indexName - Optional index name
 * @param options - Index options
 * @returns CreateTableBuilder for chaining
 */
index(columnNames: readonly (string | Raw)[], indexName?: string, options?: Readonly<{ indexType?: string; storageEngineIndexType?: string; predicate?: QueryBuilder }>): CreateTableBuilder;

/**
 * Add foreign key constraint
 * @param columns - Local column names
 * @param constraintName - Optional constraint name
 * @returns TableBuilder for chaining
 */
foreign(columns: string | readonly string[], constraintName?: string): ReferencingColumnBuilder;

/**
 * Add check constraint
 * @param checkPredicate - Check condition as raw SQL or QueryBuilder
 * @param bindings - Parameter bindings for raw SQL
 * @param constraintName - Optional constraint name
 * @returns CreateTableBuilder for chaining
 */
check(checkPredicate: string | Raw | QueryBuilder | boolean, bindings?: RawBinding[], constraintName?: string): CreateTableBuilder;

/**
 * Drop primary key constraint
 * @param constraintName - Optional constraint name
 * @returns AlterTableBuilder for chaining
 */
dropPrimary(constraintName?: string): AlterTableBuilder;

/**
 * Drop unique constraint
 * @param columnNames - Column names or index name
 * @param indexName - Index name if first parameter is columns
 * @returns AlterTableBuilder for chaining
 */
dropUnique(columnNames: readonly string[] | string, indexName?: string): AlterTableBuilder;

/**
 * Drop index
 * @param columnNames - Column names or index name
 * @param indexName - Index name if first parameter is columns
 * @returns AlterTableBuilder for chaining
 */
dropIndex(columnNames: readonly (string | Raw)[] | string, indexName?: string): AlterTableBuilder;

/**
 * Drop foreign key constraint
 * @param columnNames - Column names or constraint name
 * @param constraintName - Constraint name if first parameter is columns
 * @returns AlterTableBuilder for chaining
 */
dropForeign(columnNames: readonly string[] | string, constraintName?: string): AlterTableBuilder;

/**
 * Drop check constraints
 * @param checkConstraintNames - Array of check constraint names to drop
 * @returns AlterTableBuilder for chaining
 */
dropChecks(checkConstraintNames: readonly string[]): AlterTableBuilder;

/**
 * Drop timestamp columns (created_at, updated_at)
 * @param useCamelCase - Whether timestamp columns use camelCase names
 * @returns AlterTableBuilder for chaining
 */
dropTimestamps(useCamelCase?: boolean): AlterTableBuilder;

type Value = string | number | boolean | Date | Array<string | number | boolean | Date> | null | undefined;
type deferrableType = 'not deferrable' | 'immediate' | 'deferred';

interface EnumOptions {
  useNative?: boolean;
  existingType?: boolean;
  schemaName?: string;
  enumName?: string;
}

interface DefaultToOptions {
  constraintName?: string;
}

interface ColumnAlterOptions {
  alterNullable?: boolean;
  alterType?: boolean;
}

interface TableBuilder extends CreateTableBuilder {
  queryContext(context: any): TableBuilder;
}

const knex = require('knex')({ client: 'postgresql', connection: process.env.DATABASE_URL });

// Create a comprehensive users table
await knex.schema.createTable('users', table => {
  table.increments('id').primary();
  table.string('email', 255).notNullable().unique();
  table.string('username', 50).notNullable().unique();
  table.string('password_hash').notNullable();
  table.string('first_name', 100);
  table.string('last_name', 100);
  table.date('birth_date');
  table.enum('status', ['active', 'inactive', 'suspended']).defaultTo('active');
  table.json('preferences').defaultTo('{}');
  table.boolean('email_verified').defaultTo(false);
  table.timestamp('email_verified_at').nullable();
  table.timestamps(true, true); // created_at, updated_at with defaults
  
  // Indexes
  table.index(['last_name', 'first_name']);
  table.index('email_verified');
  
  // Check constraints
  table.check('length(username) >= 3', [], 'username_min_length');
  table.check('birth_date IS NULL OR birth_date < CURRENT_DATE', [], 'valid_birth_date');
});

// Create posts table with foreign key
await knex.schema.createTable('posts', table => {
  table.increments('id');
  table.string('title').notNullable();
  table.text('content');
  table.string('slug').unique();
  table.integer('user_id').unsigned().notNullable()
    .references('id').inTable('users').onDelete('CASCADE');
  table.enum('status', ['draft', 'published', 'archived']).defaultTo('draft');
  table.integer('view_count').unsigned().defaultTo(0);
  table.timestamp('published_at').nullable();
  table.timestamps(true, true);
  
  table.index(['user_id', 'status']);
  table.index('published_at');
});

// Alter table to add new column
await knex.schema.alterTable('users', table => {
  table.string('phone', 20).nullable().after('email');
  table.index('phone');
});

// Create schema and table within it
await knex.schema.createSchema('blog');
await knex.schema.withSchema('blog').createTable('categories', table => {
  table.increments('id');
  table.string('name').notNullable().unique();
  table.string('slug').notNullable().unique();
  table.text('description');
  table.integer('parent_id').unsigned().nullable()
    .references('id').inTable('blog.categories');
  table.timestamps(true, true);
});

// Create materialized view (PostgreSQL)
await knex.schema.createMaterializedView('user_stats', view => {
  view.as(knex.select('user_id')
    .count('* as post_count')
    .sum('view_count as total_views')
    .from('posts')
    .groupBy('user_id'));
});