Database & ORM

// Available column types in Blueprint
schema.create('products', (Blueprint table) {
  // Primary Keys
  table.id(); // Auto-incrementing BIGINT primary key
  table.id('custom_id'); // Custom primary key name

  // Strings
  table.string('name', length: 100); // VARCHAR(100)
  table.text('description'); // TEXT

  // Numbers
  table.integer('quantity'); // INT
  table.bigInteger('views'); // BIGINT
  table.float('price'); // FLOAT

  // Dates & Times
  table.date('birth_date'); // DATE
  table.timestamp('created_at'); // TIMESTAMP

  // Boolean & Enum
  table.boolean('is_active'); // BOOLEAN/TINYINT
  table.enumColumn('status', ['pending', 'approved', 'rejected']);

  // JSON
  table.json('metadata'); // JSON column

  // Special helper methods
  table.timestamps(); // Adds created_at and updated_at
  table.softDeletes(); // Adds deleted_at for soft deletes

  // Foreign Keys & Relations
  table.foreignId('user_id'); // BIGINT UNSIGNED for foreign keys
  table.morphs('commentable'); // Polymorphic relation columns
  // Creates: commentable_type (VARCHAR) and commentable_id (BIGINT)
});

// Using the Migrator class
import 'package:khadem/khadem.dart';

final migrator = Migrator(Khadem.db);

// Run all pending migrations
await migrator.upAll();

// Rollback all migrations
await migrator.downAll();

// Reset and re-run all migrations
await migrator.reset();

// Refresh database (downAll + upAll)
await migrator.refresh();

// Show migration status
await migrator.status();

// Run specific migration
await migrator.up('create_users_table');

// Rollback specific migration
await migrator.down('create_users_table');

// Create a new user
final user = User(name: 'John Doe', email: 'john@example.com', password: 'secret');
await user.save();
print('User created with ID: ${user.id}');

// Find user by ID using query builder
final foundUser = await User().query.where('id', '=', 1).first();
if (foundUser != null) {
  print('Found: ${foundUser.name}');
}

// Find users by condition
final users = await User().query.where('email', '=', 'john@example.com').get();
print('Found ${users.length} users');

// Update user
user.name = 'John Smith';
await user.save();

// Delete user
await user.delete();

// Mass assignment with fillable protection
final newUser = User()..fromJson({
  'name': 'Jane Doe',
  'email': 'jane@example.com',
  'password': 'secret',
  'role': 'admin', // Ignored if not in fillable
});
await newUser.save();

// Load relationships
final user = await User().query.where('id', '=', 1).first();
await user?.load('posts');

// Eager load with query
final users = await User().query.withRelations(['posts']).get();

// Check if relation is loaded
if (user?.isRelationLoaded('posts') ?? false) {
  final posts = user!.getRelation('posts');
}

// Load missing relations only
await user?.loadMissing(['posts', 'profile']);

// Get only specific attributes
final userData = user?.only(['name', 'email']);

// Get all except specific attributes
final userData = user?.except(['password']);

// Complex where conditions
final users = await User().query
    .where('age', '>', 18)
    .where('city', '=', 'New York')
    .orWhere('city', '=', 'Los Angeles')
    .get();

// Ordering and pagination
final users = await User().query
    .orderBy('created_at', direction: 'DESC')
    .limit(10)
    .offset(20)
    .get();

// whereHas - Query based on relationships
final usersWithPosts = await User().query
    .whereHas('posts', (query) {
      query.where('status', '=', 'published');
    })
    .get();

// Grouping with having
final query = User().query
    .select(['role', 'COUNT(*) as count'])
    .groupBy('role')
    .having('count', '>', 5);

// Raw queries
final users = await User().query
    .whereRaw('created_at > ?', [DateTime.now().subtract(Duration(days: 30))])
    .get();

// JSON queries
final users = await User().query
    .whereJsonContains('settings->notifications', 'email')
    .get();

// Raw SQL queries using DatabaseManager
import 'package:khadem/khadem.dart';

final db = Khadem.db;
final connection = db.connection;

// Raw select
final results = await connection.query(
  'SELECT * FROM users WHERE id = ?',
  [1]
);

// Raw insert
await connection.execute(
  'INSERT INTO users (name, email, created_at, updated_at) VALUES (?, ?, ?, ?)',
  ['John Doe', 'john@example.com', DateTime.now(), DateTime.now()]
);

// Raw update
await connection.execute(
  'UPDATE users SET name = ?, updated_at = ? WHERE id = ?',
  ['John Smith', DateTime.now(), 1]
);

// Raw delete
await connection.execute('DELETE FROM users WHERE id = ?', [1]);

// Use whereRaw in query builder for safer raw queries
final users = await User().query
    .whereRaw('YEAR(created_at) = ?', [2024])
    .get();

// Using factories for seeding
class UserFactory {
  static Map<String, dynamic> make() {
    return {
      'name': 'Test User',
      'email': 'test@example.com',
      'password': 'hashed_password',
      'created_at': DateTime.now(),
      'updated_at': DateTime.now(),
    };
  }

  static Map<String, dynamic> makeWithOverrides(Map<String, dynamic> overrides) {
    return {...make(), ...overrides};
  }
}

// In seeder
class UserSeeder implements Seeder {
  @override
  String get name => 'user_seeder';

  @override
  Future<void> run() async {
    // Create 10 users using factory
    for (var i = 0; i < 10; i++) {
      final userData = UserFactory.makeWithOverrides({
        'name': 'User $i',
        'email': 'user$i@example.com',
      });
      
      final user = User()..fromJson(userData);
      await user.save();
    }
  }
}

// Using Seeder class
import 'package:khadem/khadem.dart';

// Create and run database seeder
final databaseSeeder = DatabaseSeeder();
await databaseSeeder.run();

// Run specific seeder
final userSeeder = UserSeeder();
await userSeeder.run();

// Or manually seed data
await User(name: 'Admin', email: 'admin@example.com', password: 'hashed').save();
await User(name: 'User', email: 'user@example.com', password: 'hashed').save();