use Cycle\Database\Database;

$database = new Database(/* ... */);

// Get schema for new or existing table
$schema = $database->table('users')->getSchema();

// Check if table exists in database
if ($schema->exists()) {
    echo "Table exists";
} else {
    echo "Table will be created";
}

$schema = $database->table('users')->getSchema();

// Method 1: Explicit column() call
$schema->column('id')->primary();
$schema->column('email')->string(255);
$schema->column('balance')->decimal(10, 2);

// Method 2: Direct shortcut (recommended)
$schema->primary('id');
$schema->string('email', 255);
$schema->decimal('balance', 10, 2);

// Save changes to database
$schema->save();

// This declaration:
$schema->string('username', 64);

// Generates in MySQL:
// `username` VARCHAR(64) NULL

// Generates in PostgreSQL:
// "username" CHARACTER VARYING(64) NULL

// Generates in SQLite:
// "username" TEXT NULL

// User age (0-150)
$schema->tinyInteger('age')->nullable(false);

// Order quantity (thousands)
$schema->integer('quantity')->defaultValue(1);

// File size in bytes (gigabytes)
$schema->bigInteger('file_size_bytes')->nullable(false);

// Use string for indexable fields (emails, usernames)
$schema->string('email', 255)->unique();
$schema->string('username', 64)->nullable(false);

// Use text types for non-indexable content
$schema->text('bio');
$schema->longText('blog_post_content');

// Short codes or identifiers
$schema->string('country_code', 2); // 'US', 'UK', etc.
$schema->string('currency', 3);      // 'USD', 'EUR', etc.

// Money/currency - use DECIMAL for exact values
$schema->decimal('price', 10, 2);        // Up to 99,999,999.99
$schema->decimal('tax_rate', 5, 4);      // Up to 9.9999 (e.g., 0.0825 = 8.25%)

// Scientific calculations - use FLOAT/DOUBLE
$schema->double('latitude');
$schema->double('longitude');
$schema->float('temperature_celsius');

// Avoid FLOAT/DOUBLE for money
// ❌ BAD: $schema->float('price');
// ✅ GOOD: $schema->decimal('price', 10, 2);

// No fractional seconds (most common)
$schema->datetime('created_at');

// With microseconds (for high-precision timestamps)
$schema->datetime('event_occurred_at', 6); // Stores up to microseconds

// Different databases handle precision differently:
// - MySQL: datetime(6) stores up to 6 decimal places
// - PostgreSQL: timestamp(6) with time zone
// - SQL Server: datetime2(6) instead of datetime

// DBAL automatically handles UTC conversion for datetime columns
$schema->datetime('created_at')->defaultValue(AbstractColumn::DATETIME_NOW);

// Current timestamp as default
use Cycle\Database\Schema\AbstractColumn;
$schema->datetime('updated_at')->defaultValue(AbstractColumn::DATETIME_NOW);

// Small images or icons
$schema->binary('avatar');

// File uploads
$schema->longBinary('document')->nullable(true);

// Hash values
$schema->binary('password_hash');

// Consider storing files externally for very large data

// Feature flags
$schema->boolean('is_active')->defaultValue(true);
$schema->boolean('email_verified')->defaultValue(false);

// Permissions
$schema->boolean('can_edit')->nullable(false);

// Structured metadata
$schema->json('user_preferences');
$schema->json('api_response');

// Default JSON value
$schema->json('settings')->defaultValue([
    'theme' => 'light',
    'notifications' => true
]);

// Define allowed values
$schema->column('status')->enum(['active', 'pending', 'disabled']);

// Using shortcut
$schema->enum('priority', ['low', 'medium', 'high', 'urgent']);

$schema->enum('status', ['active', 'pending', 'disabled'])
    ->defaultValue('pending');

$schema->enum('role', ['user', 'admin', 'moderator'])
    ->defaultValue('user')
    ->nullable(false);

// You can add or remove values by redefining the enum
$schema = $database->table('users')->getSchema();

// Add new status value
$schema->enum('status', ['active', 'pending', 'disabled', 'archived'])
    ->defaultValue('pending');

$schema->save(); // DBAL handles the modification

// ✅ GOOD: Use enums for fixed, small sets of values
$schema->enum('status', ['draft', 'published', 'archived']);
$schema->enum('gender', ['male', 'female', 'other']);

// ❌ AVOID: Large or frequently changing value sets
// Instead, use a lookup table with foreign key

// Allow NULL values (default)
$schema->string('middle_name')->nullable(true);

// Require non-NULL values
$schema->string('email')->nullable(false);
$schema->integer('user_id')->nullable(false);

// When adding NOT NULL columns to existing tables with data
$schema->string('phone')
    ->nullable(false)
    ->defaultValue(''); // Provides value for existing rows

// Scalar defaults
$schema->integer('views')->defaultValue(0);
$schema->boolean('is_active')->defaultValue(true);
$schema->string('status')->defaultValue('pending');

// NULL as default
$schema->string('optional_field')->defaultValue(null);

// Database functions (datetime)
use Cycle\Database\Schema\AbstractColumn;
$schema->datetime('created_at')->defaultValue(AbstractColumn::DATETIME_NOW);

// JSON defaults
$schema->json('settings')->defaultValue([
    'notifications' => true,
    'theme' => 'light'
]);

$schema->string('status')->defaultValue(null); // Remove default

$schema->string('username', 64)
    ->nullable(false)
    ->unique();

$schema->decimal('balance', 10, 2)
    ->nullable(false)
    ->defaultValue(0.00);

$schema->datetime('created_at', 6)
    ->nullable(false)
    ->defaultValue(AbstractColumn::DATETIME_NOW);

$schema->enum('status', ['active', 'inactive'])
    ->nullable(false)
    ->defaultValue('active')
    ->index(); // Add simple index

$schema = $database->table('users')->getSchema();

// Standard auto-increment (32-bit)
$schema->primary('id');

// Or 64-bit for very large tables
$schema->bigPrimary('id');

$schema->save();

CREATE TABLE users
(
    id INT (11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

$schema = $database->table('user_roles')->getSchema();

$schema->integer('user_id')->nullable(false);
$schema->integer('role_id')->nullable(false);

// Define composite primary key
$schema->setPrimaryKeys(['user_id', 'role_id']);

$schema->save();

CREATE TABLE ` user_roles `
(
    `
    user_id
    `
    INT
(
    11
) NOT NULL,
    ` role_id ` INT
(
    11
) NOT NULL,
    PRIMARY KEY
(
    `
    user_id
    `,
    `
    role_id
    `
)
    ) ENGINE=InnoDB;

$schema = $database->table('countries')->getSchema();

// String primary key
$schema->string('code', 2)->nullable(false);
$schema->string('name', 255);

$schema->setPrimaryKeys(['code']);

$schema->save();

$schema = $database->table('users')->getSchema();

$schema->primary('id');
$schema->string('email', 255);
$schema->string('username', 64);
$schema->datetime('created_at');

// Method 1: Using index() method
$schema->index(['email']);

// Method 2: Inline with column definition
$schema->column('username')->index();

$schema->save();

// Unique constraint on single column
$schema->string('email', 255)->unique();

// Or using index() method
$schema->string('username', 64);
$schema->index(['username'])->unique(true);

$schema->save();

$schema = $database->table('posts')->getSchema();

$schema->primary('id');
$schema->integer('user_id');
$schema->datetime('created_at');
$schema->string('status', 20);

// Composite index for common queries
$schema->index(['user_id', 'created_at']);
$schema->index(['status', 'created_at']);

// Composite unique index
$schema->string('slug', 255);
$schema->integer('category_id');
$schema->index(['slug', 'category_id'])->unique(true);

$schema->save();

// Ascending order (default)
$schema->index(['created_at ASC']);

// Descending order
$schema->index(['created_at DESC']);

// Mixed sorting in composite index
$schema->index(['status ASC', 'created_at DESC']);

$schema = $database->table('users')->getSchema();

// Make existing index unique
$schema->index(['email'])->unique(true);

// Remove unique constraint
$schema->index(['email'])->unique(false);

$schema->save();

// ❌ CANNOT index text/binary columns
// $schema->text('description')->index(); // Will fail

// ✅ Use string for indexable text
$schema->string('title', 255)->index();

// ❌ CANNOT create unique index on table with duplicate data
// Must clean data first

// ✅ Consider index size limits (database-specific)
// MySQL: ~767 bytes for InnoDB
$schema->string('long_field', 1000)->index(); // May fail on some engines

// Parent table
$users = $database->table('users')->getSchema();
$users->primary('id');
$users->string('name', 64);
$users->save();

// Child table with foreign key
$posts = $database->table('posts')->getSchema();
$posts->primary('id');
$posts->string('title', 255);
$posts->integer('user_id');

// Create foreign key
$posts->foreign('user_id')->references('users', 'id');

$posts->save();

ALTER TABLE posts
    ADD CONSTRAINT posts_foreign_user_id_5f2a8b9c
        FOREIGN KEY (user_id)
            REFERENCES users (id)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION;

$posts = $database->table('posts')->getSchema();
$posts->integer('user_id');

$foreignKey = $posts->foreign('user_id')->references('users', 'id');

// Delete posts when user is deleted
$foreignKey->onDelete(ForeignKeyInterface::CASCADE);

// Update foreign key when parent key changes
$foreignKey->onUpdate(ForeignKeyInterface::CASCADE);

$posts->save();

use Cycle\Database\ForeignKeyInterface;

// Posts are deleted when user is deleted
$posts->integer('author_id')->nullable(false);
$posts->foreign('author_id')
    ->references('users', 'id')
    ->onDelete(ForeignKeyInterface::CASCADE)
    ->onUpdate(ForeignKeyInterface::CASCADE);

// Comments orphaned when post is deleted
$comments->integer('post_id')->nullable(true);
$comments->foreign('post_id')
    ->references('posts', 'id')
    ->onDelete(ForeignKeyInterface::SET_NULL)
    ->onUpdate(ForeignKeyInterface::CASCADE);

// Orders cannot be deleted if they have items
$orderItems->integer('order_id')->nullable(false);
$orderItems->foreign('order_id')
    ->references('orders', 'id')
    ->onDelete(ForeignKeyInterface::RESTRICT)
    ->onUpdate(ForeignKeyInterface::CASCADE);

// Parent table with composite primary key
$userProducts = $database->table('user_products')->getSchema();
$userProducts->integer('user_id')->nullable(false);
$userProducts->integer('product_id')->nullable(false);
$userProducts->setPrimaryKeys(['user_id', 'product_id']);
$userProducts->save();

// Child table referencing composite key
$favorites = $database->table('favorites')->getSchema();
$favorites->primary('id');
$favorites->integer('user_id');
$favorites->integer('product_id');

$favorites->foreign(['user_id', 'product_id'])
    ->references('user_products', ['user_id', 'product_id'])
    ->onDelete(ForeignKeyInterface::CASCADE);

$favorites->save();

$posts->integer('user_id');
$posts->foreign('user_id', false) // false = don't create index
    ->references('users', 'id');

// Some databases (SQL Server) restrict CASCADE actions
// to avoid circular references. Plan your schema accordingly.

// Example: Cannot have mutual CASCADE relationships
// Table A -> CASCADE -> Table B
// Table B -> CASCADE -> Table A  // May be rejected

$schema = $database->table('users')->getSchema();

// Method 1: Using setName()
$schema->column('email')->setName('email_address');

// Method 2: Using renameColumn()
$schema->renameColumn('old_name', 'new_name');

$schema->save();

ALTER TABLE users
    CHANGE email email_address VARCHAR (255) NULL;

$schema = $database->table('old_users')->getSchema();
$schema->setName('users');
$schema->save();

ALTER TABLE old_users
    RENAME TO users;

$schema = $database->table('users')->getSchema();

$schema->renameIndex(
    ['email'],        // Column(s) that form the index
    'idx_user_email'  // New index name
);

$schema->save();

$schema = $database->table('users')->getSchema();

$schema->dropColumn('middle_name');
$schema->dropColumn('legacy_field');

$schema->save();

ALTER TABLE users
    DROP COLUMN middle_name;
ALTER TABLE users
    DROP COLUMN legacy_field;

$schema = $database->table('posts')->getSchema();

// Drop index by column(s)
$schema->dropIndex(['title']);
$schema->dropIndex(['user_id', 'created_at']); // Composite index

$schema->save();

$schema = $database->table('posts')->getSchema();

// Drop by column(s)
$schema->dropForeignKey(['user_id']);

$schema->save();

// Always drop foreign keys before dropping referenced columns
$schema = $database->table('posts')->getSchema();

// 1. Drop foreign key first
$schema->dropForeignKey(['user_id']);

// 2. Then drop the column
$schema->dropColumn('user_id');

$schema->save();

$schema = $database->table('old_table')->getSchema();

$schema->declareDropped();
$schema->save();

$schema = $database->table('users')->getSchema();

// Clear current schema state
$schema->setState(null);

// Now redefine only the columns you want
$schema->primary('id');
$schema->string('email', 255);
$schema->string('username', 64);

// All other columns will be dropped
$schema->save();

$schema = $database->table('users')->getSchema();

// Make some changes
$schema->string('new_field');
$schema->dropColumn('old_field');
$schema->index(['email'])->unique(true);

// Get comparator to inspect changes
$comparator = $schema->getComparator();

// Check what will be added
$addedColumns = $comparator->addedColumns();
foreach ($addedColumns as $column) {
    echo "Will add column: " . $column->getName() . "\n";
}

// Check what will be dropped
$droppedColumns = $comparator->droppedColumns();
foreach ($droppedColumns as $column) {
    echo "Will drop column: " . $column->getName() . "\n";
}

// Check what will be modified
$alteredColumns = $comparator->alteredColumns();
foreach ($alteredColumns as [$new, $old]) {
    echo "Will modify column: " . $new->getName() . "\n";
}

// Check if there are any changes
if ($comparator->hasChanges()) {
    echo "Schema has pending changes\n";
    
    // Optionally save
    $schema->save();
} else {
    echo "Schema is up to date\n";
}

$comparator = $schema->getComparator();

// Use comparator data to generate migration files
foreach ($comparator->addedColumns() as $column) {
    $migrationCode = sprintf(
        '$schema->%s(\'%s\');',
        $column->getAbstractType(),
        $column->getName()
    );
    // Write to migration file...
}

use Cycle\Database\Schema\Reflector;

// Define first table
$users = $database->table('users')->getSchema();
$users->primary('id');
$users->string('username', 64);

// Define related table
$posts = $database->table('posts')->getSchema();
$posts->primary('id');
$posts->string('title', 255);
$posts->integer('user_id');
$posts->foreign('user_id')->references('users', 'id');

// Define another related table
$comments = $database->table('comments')->getSchema();
$comments->primary('id');
$comments->text('content');
$comments->integer('post_id');
$comments->foreign('post_id')->references('posts', 'id');

// Create reflector and add tables
$reflector = new Reflector();
$reflector->addTable($users);
$reflector->addTable($posts);
$reflector->addTable($comments);

// Reflector automatically orders tables by dependencies
// and syncs them in the correct order
$reflector->run();

$reflector = new Reflector();
$reflector->addTable($users);
$reflector->addTable($posts);
$reflector->addTable($comments);

// Get tables in dependency order
$sortedTables = $reflector->sortedTables();

foreach ($sortedTables as $table) {
    echo "Processing: " . $table->getName() . "\n";
}

$dbPrimary = $dbal->database('primary');
$dbSecondary = $dbal->database('secondary');

$reflector = new Reflector();

// Add tables from different databases
$reflector->addTable($dbPrimary->table('users')->getSchema());
$reflector->addTable($dbPrimary->table('posts')->getSchema());
$reflector->addTable($dbSecondary->table('logs')->getSchema());

// Reflector handles multiple database transactions
$reflector->run();

// ✅ GOOD: Right-sized types
$schema->tinyInteger('age');           // 0-255
$schema->string('country_code', 2);    // 'US', 'UK'
$schema->decimal('price', 10, 2);      // Exact money values

// ❌ AVOID: Oversized types
$schema->bigInteger('age');            // Wastes space
$schema->string('country_code', 255);  // Wastes space
$schema->float('price');               // Imprecise for money

// ✅ GOOD: Index frequently queried columns
$schema->string('email', 255)->unique();
$schema->index(['user_id', 'created_at']); // For sorting
$schema->index(['status', 'priority']);    // For filtering

// ❌ AVOID: Over-indexing
// Don't index columns rarely used in queries
// Don't index large text fields

// ✅ GOOD: Clear defaults and null handling
$schema->boolean('is_active')->defaultValue(true)->nullable(false);
$schema->integer('view_count')->defaultValue(0)->nullable(false);
$schema->datetime('created_at')->defaultValue(AbstractColumn::DATETIME_NOW);

// ❌ AVOID: Ambiguous nullable booleans
$schema->boolean('is_verified')->nullable(true); // NULL vs false?

// ✅ GOOD: Enforce referential integrity
$schema->foreign('user_id')
    ->references('users', 'id')
    ->onDelete(ForeignKeyInterface::CASCADE);

// ❌ AVOID: Orphaned records
// Without FK, you may have posts pointing to non-existent users

// ✅ GOOD: Most selective column first
$schema->index(['status', 'created_at', 'user_id']);
// Useful for: WHERE status = ? ORDER BY created_at

// ❌ AVOID: Poor ordering
$schema->index(['created_at', 'status']);
// Can't efficiently filter by status alone

// ✅ GOOD: Multiple related schemas
$reflector = new Reflector();
$reflector->addTable($users);
$reflector->addTable($posts);
$reflector->run(); // All or nothing

// ❌ AVOID: Individual saves
$users->save();
$posts->save(); // May fail if users didn't save

// ✅ GOOD: Check before saving
$comparator = $schema->getComparator();
if ($comparator->droppedColumns()) {
    // Confirm you want to drop these columns
    foreach ($comparator->droppedColumns() as $col) {
        echo "WARNING: Will drop " . $col->getName() . "\n";
    }
}
$schema->save();

// ✅ GOOD: Generate migrations for review
// See the Migrations documentation for details

// ❌ AVOID: Direct schema saves in production
// $schema->save(); // Skip in production, use migrations

// ✅ GOOD: Add comments for clarity
$schema->string('status', 20)
    ->enum(['draft', 'review', 'published', 'archived'])
    ->defaultValue('draft');
// Status workflow: draft -> review -> published -> archived

$schema->decimal('tax_rate', 5, 4);
// Example: 0.0825 for 8.25% tax rate

// ✅ GOOD: Test on development/staging first
// 1. Test schema changes locally
// 2. Verify in staging environment
// 3. Back up production before applying
// 4. Have rollback plan ready

// ❌ AVOID: Untested production changes