Exemples d'Opérations de Base de Données Web TypeScript

// Web TypeScript SQLite Connection Examples
// Using SQL.js (SQLite compiled to WebAssembly) for browser-based database operations

// 1. SQLite Database Connection
class SQLiteDatabase {
  protected db: any = null;
  protected SQL: any = null;

  // Initialize SQL.js
  async initialize(): Promise<void> {
    // Load SQL.js from CDN
    const sqlJsConfig = await this.loadSQLJS();

    // Initialize SQL.js
    this.SQL = await sqlJsConfig;
    this.db = new this.SQL.Database();

    console.log('SQLite database initialized');
  }

  // Load SQL.js library
  private async loadSQLJS(): Promise<any> {
    // Dynamic import of SQL.js
    const module = await import('sql.js');
    return module;
  }

  // Create database from binary array
  async createFromBuffer(buffer: Uint8Array): Promise<void> {
    this.SQL = await this.loadSQLJS();
    this.db = new this.SQL.Database(buffer);
    console.log('Database created from buffer');
  }

  // Load database from file
  async loadFromFile(file: File): Promise<void> {
    const arrayBuffer = await file.arrayBuffer();
    const uint8Array = new Uint8Array(arrayBuffer);
    await this.createFromBuffer(uint8Array);
  }

  // Export database to binary
  exportDatabase(): Uint8Array {
    if (!this.db) {
      throw new Error('Database not initialized');
    }

    return this.db.export();
  }

  // Download database as file
  downloadDatabase(fileName: string = 'database.db'): void {
    const data = this.exportDatabase();
    const blob = new Blob([data], { type: 'application/x-sqlite3' });

    const url = URL.createObjectURL(blob);
    const link = document.createElement('a');
    link.href = url;
    link.download = fileName;
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
    URL.revokeObjectURL(url);
  }

  // Check if database is open
  isOpen(): boolean {
    return this.db !== null;
  }

  // Get raw SQL.js database handle
  getDB(): any {
    if (!this.db) {
      throw new Error('Database not initialized');
    }
    return this.db;
  }

  // Close database
  close(): void {
    if (this.db) {
      this.db.close();
      this.db = null;
      console.log('Database closed');
    }
  }
}

// 2. Database Schema Manager
class DatabaseSchema {
  private db: SQLiteDatabase;

  constructor(db: SQLiteDatabase) {
    this.db = db;
  }

  // Create table
  createTable(tableName: string, columns: Record<string, string>): void {
    const columnDefs = Object.entries(columns)
      .map(([name, type]) => `${name} ${type}`)
      .join(', ');

    const sql = `CREATE TABLE IF NOT EXISTS ${tableName} (${columnDefs})`;
    this.db.getDB().run(sql);

    console.log(`Table ${tableName} created`);
  }

  // Drop table
  dropTable(tableName: string): void {
    const sql = `DROP TABLE IF EXISTS ${tableName}`;
    this.db.getDB().run(sql);

    console.log(`Table ${tableName} dropped`);
  }

  // Check if table exists
  tableExists(tableName: string): boolean {
    const sql = `SELECT name FROM sqlite_master WHERE type='table' AND name=?`;
    const result = this.db.getDB().exec(sql, [tableName]);

    return result.length > 0;
  }

  // List all tables
  listTables(): string[] {
    const sql = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
    const result = this.db.getDB().exec(sql);

    if (result.length > 0) {
      return result[0].values.map((row: any) => row[0]);
    }

    return [];
  }

  // Get table schema
  getTableSchema(tableName: string): any[] {
    const sql = `PRAGMA table_info(${tableName})`;
    const result = this.db.getDB().exec(sql);

    if (result.length > 0) {
      return result[0].values.map((row: any) => ({
        cid: row[0],
        name: row[1],
        type: row[2],
        notNull: row[3] === 1,
        defaultValue: row[4],
        pk: row[5] === 1
      }));
    }

    return [];
  }
}

// 3. In-Memory Database
class InMemoryDatabase extends SQLiteDatabase {
  // Create temporary database
  static async create(): Promise<InMemoryDatabase> {
    const db = new InMemoryDatabase();
    await db.initialize();
    return db;
  }

  // Save state
  saveState(): Uint8Array {
    return this.exportDatabase();
  }

  // Restore state
  restoreState(data: Uint8Array): void {
    if (this.db) {
      this.db.close();
    }
    this.db = new this.SQL.Database(data);
  }
}

// 4. Persistent Database (IndexedDB)
class PersistentDatabase {
  private dbName: string;
  private storeName: string = 'sqlite_databases';
  private db: IDBDatabase | null = null;
  private sqlDB: SQLiteDatabase | null = null;

  constructor(dbName: string) {
    this.dbName = dbName;
  }

  // Initialize IndexedDB
  async initialize(): Promise<void> {
    return new Promise((resolve, reject) => {
      const request = indexedDB.open(this.dbName, 1);

      request.onerror = () => reject(request.error);
      request.onsuccess = () => {
        this.db = request.result;
        resolve();
      };

      request.onupgradeneeded = (event) => {
        const db = (event.target as IDBOpenDBRequest).result;
        if (!db.objectStoreNames.contains(this.storeName)) {
          db.createObjectStore(this.storeName);
        }
      };
    });
  }

  // Save SQLite database to IndexedDB
  async saveDatabase(name: string, sqlDB: SQLiteDatabase): Promise<void> {
    const data = sqlDB.exportDatabase();

    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction([this.storeName], 'readwrite');
      const store = transaction.objectStore(this.storeName);
      const request = store.put(data, name);

      request.onsuccess = () => resolve();
      request.onerror = () => reject(request.error);
    });
  }

  // Load SQLite database from IndexedDB
  async loadDatabase(name: string): Promise<SQLiteDatabase> {
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction([this.storeName], 'readonly');
      const store = transaction.objectStore(this.storeName);
      const request = store.get(name);

      request.onsuccess = async () => {
        const data = request.result;
        if (data) {
          const sqlDB = new SQLiteDatabase();
          await sqlDB.initialize();
          sqlDB.getDB().close();
          sqlDB['db'] = new sqlDB['SQL'].Database(data);
          resolve(sqlDB);
        } else {
          reject(new Error('Database not found'));
        }
      };

      request.onerror = () => reject(request.error);
    });
  }

  // List all saved databases
  async listDatabases(): Promise<string[]> {
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction([this.storeName], 'readonly');
      const store = transaction.objectStore(this.storeName);
      const request = store.getAllKeys();

      request.onsuccess = () => resolve(request.result as string[]);
      request.onerror = () => reject(request.error);
    });
  }

  // Delete database
  async deleteDatabase(name: string): Promise<void> {
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction([this.storeName], 'readwrite');
      const store = transaction.objectStore(this.storeName);
      const request = store.delete(name);

      request.onsuccess = () => resolve();
      request.onerror = () => reject(request.error);
    });
  }

  // Close IndexedDB
  close(): void {
    if (this.db) {
      this.db.close();
      this.db = null;
    }
  }
}

// 5. Database Connection Pool (Simulated)
class DatabasePool {
  private pools: Map<string, SQLiteDatabase[]> = new Map();
  private maxSize: number = 5;

  // Create pool
  createPool(name: string, size: number = this.maxSize): void {
    const pool: SQLiteDatabase[] = [];

    for (let i = 0; i < size; i++) {
      // Would create connections here
      // In browser, this is simulated
    }

    this.pools.set(name, pool);
  }

  // Get connection from pool
  async getConnection(name: string): Promise<SQLiteDatabase | null> {
    const pool = this.pools.get(name);

    if (pool && pool.length > 0) {
      return pool.pop()!;
    }

    // Create new connection
    const db = new SQLiteDatabase();
    await db.initialize();
    return db;
  }

  // Return connection to pool
  returnConnection(name: string, db: SQLiteDatabase): void {
    const pool = this.pools.get(name);

    if (pool && pool.length < this.maxSize) {
      pool.push(db);
    } else {
      db.close();
    }
  }

  // Close all connections in pool
  closePool(name: string): void {
    const pool = this.pools.get(name);

    if (pool) {
      pool.forEach(db => db.close());
      pool.length = 0;
    }
  }

  // Close all pools
  closeAll(): void {
    this.pools.forEach((pool, name) => {
      this.closePool(name);
    });
    this.pools.clear();
  }
}

// 6. Database Health Monitor
class DatabaseHealthMonitor {
  private db: SQLiteDatabase;
  private metrics: Map<string, any> = new Map();

  constructor(db: SQLiteDatabase) {
    this.db = db;
  }

  // Get database size
  getDatabaseSize(): number {
    const data = this.db.exportDatabase();
    return data.length;
  }

  // Get database page size
  getPageSize(): number {
    const result = this.db.getDB().exec('PRAGMA page_size');
    return result.length > 0 ? result[0].values[0][0] : 0;
  }

  // Get page count
  getPageCount(): number {
    const result = this.db.getDB().exec('PRAGMA page_count');
    return result.length > 0 ? result[0].values[0][0] : 0;
  }

  // Get database version
  getDatabaseVersion(): string {
    const result = this.db.getDB().exec('SELECT sqlite_version()');
    return result.length > 0 ? result[0].values[0][0] : '';
  }

  // Check database integrity
  checkIntegrity(): boolean {
    const result = this.db.getDB().exec('PRAGMA integrity_check');
    return result.length > 0 && result[0].values[0][0] === 'ok';
  }

  // Get table row counts
  getTableRowCounts(): Map<string, number> {
    const counts = new Map<string, number>();
    const tables = this.db.getDB().exec("SELECT name FROM sqlite_master WHERE type='table'");

    if (tables.length > 0) {
      for (const table of tables[0].values) {
        const tableName = table[0];
        const result = this.db.getDB().exec(`SELECT COUNT(*) FROM ${tableName}`);
        if (result.length > 0) {
          counts.set(tableName, result[0].values[0][0]);
        }
      }
    }

    return counts;
  }

  // Get health report
  getHealthReport(): Record<string, any> {
    return {
      size: this.getDatabaseSize(),
      pageSize: this.getPageSize(),
      pageCount: this.getPageCount(),
      version: this.getDatabaseVersion(),
      integrity: this.checkIntegrity(),
      tableCounts: Object.fromEntries(this.getTableRowCounts())
    };
  }
}

// Usage Examples
async function demonstrateSQLiteConnection() {
  console.log('=== Web TypeScript SQLite Connection Examples ===\n');

  // 1. Initialize in-memory database
  console.log('--- 1. Initialize In-Memory Database ---');
  const db1 = await InMemoryDatabase.create();
  console.log(`Database open: ${db1.isOpen()}`);

  // 2. Create schema
  console.log('\n--- 2. Create Schema ---');
  const schema = new DatabaseSchema(db1);
  schema.createTable('users', {
    id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
    name: 'TEXT NOT NULL',
    email: 'TEXT UNIQUE',
    created_at: 'DATETIME DEFAULT CURRENT_TIMESTAMP'
  });

  const tables = schema.listTables();
  console.log(`Tables: ${tables.join(', ')}`);

  // 3. Get table schema
  console.log('\n--- 3. Get Table Schema ---');
  const tableSchema = schema.getTableSchema('users');
  tableSchema.forEach(col => {
    console.log(`  ${col.name}: ${col.type}`);
  });

  // 4. Persistent database
  console.log('\n--- 4. Persistent Database ---');
  const persistentDB = new PersistentDatabase('MyAppDB');
  await persistentDB.initialize();
  await persistentDB.saveDatabase('main_db', db1);
  console.log('Database saved to IndexedDB');

  const savedDBs = await persistentDB.listDatabases();
  console.log(`Saved databases: ${savedDBs.join(', ')}`);

  // 5. Health monitor
  console.log('\n--- 5. Health Monitor ---');
  const monitor = new DatabaseHealthMonitor(db1);
  const health = monitor.getHealthReport();
  console.log('Health Report:');
  console.log(`  Size: ${health.size} bytes`);
  console.log(`  Version: ${health.version}`);
  console.log(`  Integrity: ${health.integrity}`);

  // 6. Export and download
  console.log('\n--- 6. Export Database ---');
  db1.downloadDatabase('my_database.db');

  // Cleanup
  db1.close();
  persistentDB.close();

  console.log('\n=== All SQLite Connection Examples Completed ===');
}

// Export functions
export { SQLiteDatabase, DatabaseSchema, InMemoryDatabase, PersistentDatabase, DatabasePool, DatabaseHealthMonitor };
export { demonstrateSQLiteConnection };

// Web TypeScript SQL Query Execution Examples
// Using SQL.js for executing SQL queries in browser environment

// 1. Query Builder
class QueryBuilder {
  private table: string = '';
  private selectColumns: string[] = ['*'];
  private whereConditions: string[] = [];
  private joinClauses: string[] = [];
  private orderByClause: string = '';
  private limitValue: number = 0;
  private offsetValue: number = 0;
  private bindParams: any[] = [];

  // Select table
  from(table: string): this {
    this.table = table;
    return this;
  }

  // Select columns
  select(...columns: string[]): this {
    this.selectColumns = columns;
    return this;
  }

  // Where clause
  where(condition: string, ...params: any[]): this {
    this.whereConditions.push(condition);
    this.bindParams.push(...params);
    return this;
  }

  // Join clause
  join(table: string, onCondition: string): this {
    this.joinClauses.push(`JOIN ${table} ON ${onCondition}`);
    return this;
  }

  // Left join
  leftJoin(table: string, onCondition: string): this {
    this.joinClauses.push(`LEFT JOIN ${table} ON ${onCondition}`);
    return this;
  }

  // Order by
  orderBy(column: string, direction: 'ASC' | 'DESC' = 'ASC'): this {
    this.orderByClause = `ORDER BY ${column} ${direction}`;
    return this;
  }

  // Limit
  limit(count: number): this {
    this.limitValue = count;
    return this;
  }

  // Offset
  offset(count: number): this {
    this.offsetValue = count;
    return this;
  }

  // Build query
  build(): { sql: string; params: any[] } {
    const sql = `SELECT ${this.selectColumns.join(', ')} FROM ${this.table}`;

    const parts = [sql];

    if (this.joinClauses.length > 0) {
      parts.push(this.joinClauses.join(' '));
    }

    if (this.whereConditions.length > 0) {
      parts.push(`WHERE ${this.whereConditions.join(' AND ')}`);
    }

    if (this.orderByClause) {
      parts.push(this.orderByClause);
    }

    if (this.limitValue > 0) {
      parts.push(`LIMIT ${this.limitValue}`);
    }

    if (this.offsetValue > 0) {
      parts.push(`OFFSET ${this.offsetValue}`);
    }

    return {
      sql: parts.join(' '),
      params: this.bindParams
    };
  }

  // Reset builder
  reset(): this {
    this.table = '';
    this.selectColumns = ['*'];
    this.whereConditions = [];
    this.joinClauses = [];
    this.orderByClause = '';
    this.limitValue = 0;
    this.offsetValue = 0;
    this.bindParams = [];
    return this;
  }
}

// 2. Database Query Executor
class QueryExecutor {
  private db: any;

  constructor(db: any) {
    this.db = db;
  }

  // Execute SELECT query
  select(sql: string, params: any[] = []): any[] {
    try {
      const results = this.db.exec(sql, params);

      if (results.length > 0) {
        const columns = results[0].columns;
        const values = results[0].values;

        return values.map((row: any[]) => {
          const obj: Record<string, any> = {};
          columns.forEach((col: string, index: number) => {
            obj[col] = row[index];
          });
          return obj;
        });
      }

      return [];
    } catch (error) {
      console.error('Query error:', error);
      throw error;
    }
  }

  // Execute SELECT and return single row
  selectOne(sql: string, params: any[] = []): any | null {
    const results = this.select(sql, params);
    return results.length > 0 ? results[0] : null;
  }

  // Execute INSERT query
  insert(table: string, data: Record<string, any>): number {
    const columns = Object.keys(data);
    const values = Object.values(data);
    const placeholders = columns.map(() => '?').join(', ');

    const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES (${placeholders})`;

    try {
      this.db.run(sql, values);
      return this.getLastInsertId();
    } catch (error) {
      console.error('Insert error:', error);
      throw error;
    }
  }

  // Execute batch INSERT
  batchInsert(table: string, dataList: Record<string, any>[]): number {
    if (dataList.length === 0) return 0;

    const columns = Object.keys(dataList[0]);
    const placeholders = columns.map(() => '?').join(', ');

    const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES (${placeholders})`;

    let insertedCount = 0;

    try {
      this.db.run('BEGIN TRANSACTION');

      for (const data of dataList) {
        const values = columns.map(col => data[col]);
        this.db.run(sql, values);
        insertedCount++;
      }

      this.db.run('COMMIT');
      return insertedCount;
    } catch (error) {
      this.db.run('ROLLBACK');
      console.error('Batch insert error:', error);
      throw error;
    }
  }

  // Execute UPDATE query
  update(table: string, data: Record<string, any>, where: string, whereParams: any[] = []): number {
    const setClause = Object.keys(data)
      .map(key => `${key} = ?`)
      .join(', ');

    const values = [...Object.values(data), ...whereParams];
    const sql = `UPDATE ${table} SET ${setClause} WHERE ${where}`;

    try {
      this.db.run(sql, values);
      return this.getChanges();
    } catch (error) {
      console.error('Update error:', error);
      throw error;
    }
  }

  // Execute DELETE query
  delete(table: string, where: string, params: any[] = []): number {
    const sql = `DELETE FROM ${table} WHERE ${where}`;

    try {
      this.db.run(sql, params);
      return this.getChanges();
    } catch (error) {
      console.error('Delete error:', error);
      throw error;
    }
  }

  // Execute raw SQL
  execute(sql: string, params: any[] = []): any[] {
    return this.select(sql, params);
  }

  // Get last insert ID
  getLastInsertId(): number {
    const result = this.db.exec('SELECT last_insert_rowid()');
    return result.length > 0 ? result[0].values[0][0] : 0;
  }

  // Get number of affected rows
  getChanges(): number {
    const result = this.db.exec('SELECT changes()');
    return result.length > 0 ? result[0].values[0][0] : 0;
  }

  // Count rows
  count(table: string, where: string = '', params: any[] = []): number {
    let sql = `SELECT COUNT(*) as count FROM ${table}`;

    if (where) {
      sql += ` WHERE ${where}`;
    }

    const result = this.selectOne(sql, params);
    return result ? result.count : 0;
  }

  // Check if exists
  exists(table: string, where: string, params: any[] = []): boolean {
    const count = this.count(table, where, params);
    return count > 0;
  }
}

// 3. Repository Pattern
class Repository {
  protected executor: QueryExecutor;
  protected tableName: string;

  constructor(db: any, tableName: string) {
    this.executor = new QueryExecutor(db);
    this.tableName = tableName;
  }

  // Find by ID
  findById(id: number): any | null {
    return this.executor.selectOne(
      `SELECT * FROM ${this.tableName} WHERE id = ?`,
      [id]
    );
  }

  // Find all
  findAll(limit: number = 100, offset: number = 0): any[] {
    return this.executor.select(
      `SELECT * FROM ${this.tableName} LIMIT ? OFFSET ?`,
      [limit, offset]
    );
  }

  // Find where
  findWhere(where: string, params: any[]): any[] {
    return this.executor.select(
      `SELECT * FROM ${this.tableName} WHERE ${where}`,
      params
    );
  }

  // Find one where
  findOneWhere(where: string, params: any[]): any | null {
    return this.executor.selectOne(
      `SELECT * FROM ${this.tableName} WHERE ${where}`,
      params
    );
  }

  // Insert
  create(data: Record<string, any>): number {
    return this.executor.insert(this.tableName, data);
  }

  // Update
  update(id: number, data: Record<string, any>): number {
    return this.executor.update(
      this.tableName,
      data,
      'id = ?',
      [id]
    );
  }

  // Delete
  delete(id: number): number {
    return this.executor.delete(
      this.tableName,
      'id = ?',
      [id]
    );
  }

  // Count
  count(): number {
    return this.executor.count(this.tableName);
  }

  // Exists
  exists(id: number): boolean {
    return this.executor.exists(this.tableName, 'id = ?', [id]);
  }
}

// 4. Active Record Pattern
class ActiveRecord {
  protected db: any;
  protected tableName: string = '';
  protected data: Record<string, any> = {};
  protected isNew: boolean = true;

  constructor(db: any, data: Record<string, any> = {}) {
    this.db = db;
    this.data = { ...data };
    if (data.id) {
      this.isNew = false;
    }
  }

  // Get attribute
  get(key: string): any {
    return this.data[key];
  }

  // Set attribute
  set(key: string, value: any): void {
    this.data[key] = value;
  }

  // Save
  save(): number {
    const executor = new QueryExecutor(this.db);

    if (this.isNew) {
      const id = executor.insert(this.tableName, this.data);
      this.data.id = id;
      this.isNew = false;
      return id;
    } else {
      executor.update(
        this.tableName,
        this.data,
        'id = ?',
        [this.data.id]
      );
      return this.data.id;
    }
  }

  // Delete
  delete(): number {
    if (this.isNew) return 0;

    const executor = new QueryExecutor(this.db);
    return executor.delete(this.tableName, 'id = ?', [this.data.id]);
  }

  // Refresh
  refresh(): void {
    if (this.isNew) return;

    const executor = new QueryExecutor(this.db);
    const data = executor.selectOne(
      `SELECT * FROM ${this.tableName} WHERE id = ?`,
      [this.data.id]
    );

    if (data) {
      this.data = data;
    }
  }

  // Convert to JSON
  toJSON(): Record<string, any> {
    return { ...this.data };
  }
}

// 5. Query Logger
class QueryLogger {
  private executor: QueryExecutor;
  private logs: Array<{ sql: string; params: any[]; time: number }> = [];

  constructor(executor: QueryExecutor) {
    this.executor = executor;
  }

  // Execute with logging
  select(sql: string, params: any[] = []): any[] {
    const start = performance.now();
    const results = this.executor.select(sql, params);
    const time = performance.now() - start;

    this.logs.push({ sql, params, time });
    console.log(`[SELECT] ${sql} - ${time.toFixed(2)}ms`);

    return results;
  }

  // Get slow queries
  getSlowQueries(threshold: number = 100): Array<{ sql: string; params: any[]; time: number }> {
    return this.logs.filter(log => log.time > threshold);
  }

  // Get query statistics
  getStatistics(): Record<string, any> {
    const totalTime = this.logs.reduce((sum, log) => sum + log.time, 0);
    const avgTime = totalTime / this.logs.length;
    const maxTime = Math.max(...this.logs.map(log => log.time));

    return {
      totalQueries: this.logs.length,
      totalTime: totalTime.toFixed(2),
      averageTime: avgTime.toFixed(2),
      maxTime: maxTime.toFixed(2),
      slowQueries: this.getSlowQueries().length
    };
  }

  // Clear logs
  clear(): void {
    this.logs = [];
  }
}

// 6. Data Mapper
class DataMapper {
  private executor: QueryExecutor;

  constructor(db: any) {
    this.executor = new QueryExecutor(db);
  }

  // Map rows to entities
  mapToEntities<T>(rows: any[], entityClass: new () => T): T[] {
    return rows.map(row => this.mapToEntity(row, entityClass));
  }

  // Map row to entity
  mapToEntity<T>(row: any, entityClass: new () => T): T {
    const entity = new entityClass();
    Object.assign(entity, row);
    return entity;
  }

  // Execute query and map to entities
  queryAndMap<T>(sql: string, params: any[], entityClass: new () => T): T[] {
    const rows = this.executor.select(sql, params);
    return this.mapToEntities(rows, entityClass);
  }
}

// Usage Examples
async function demonstrateSQLQueryExecution() {
  console.log('=== Web TypeScript SQL Query Execution Examples ===\n');

  // Simulate database connection
  const mockDB = {
    exec: (sql: string, params: any[] = []) => {
      // Mock implementation
      console.log(`Executing: ${sql}`);
      return [];
    },
    run: (sql: string, params: any[] = []) => {
      console.log(`Running: ${sql}`);
    }
  };

  const executor = new QueryExecutor(mockDB);

  // 1. Query builder
  console.log('--- 1. Query Builder ---');
  const builder = new QueryBuilder();
  const query = builder
    .select('id', 'name', 'email')
    .from('users')
    .where('age > ?', 18)
    .where('status = ?', 'active')
    .orderBy('name', 'ASC')
    .limit(10)
    .build();

  console.log(`SQL: ${query.sql}`);
  console.log(`Params: ${query.params}`);

  // 2. Repository
  console.log('\n--- 2. Repository Pattern ---');
  class UserRepository extends Repository {
    findByEmail(email: string) {
      return this.findOneWhere('email = ?', [email]);
    }

    findActiveUsers() {
      return this.findWhere('status = ?', ['active']);
    }

    searchByName(keyword: string) {
      return this.findWhere('name LIKE ?', [`%${keyword}%`]);
    }
  }

  const userRepo = new UserRepository(mockDB, 'users');
  console.log('Repository created for users table');

  // 3. Query logging
  console.log('\n--- 3. Query Logging ---');
  const logger = new QueryLogger(executor);
  // logger.select('SELECT * FROM users');

  console.log('\n=== All SQL Query Execution Examples Completed ===');
}

// Export functions
export { QueryBuilder, QueryExecutor, Repository, ActiveRecord, QueryLogger, DataMapper };
export { demonstrateSQLQueryExecution };

// Web TypeScript Transaction Handling Examples
// Using SQL.js for transaction management in browser environment

// 1. Transaction Manager
class TransactionManager {
  private db: any;
  private inTransaction: boolean = false;
  private savepoints: string[] = [];

  constructor(db: any) {
    this.db = db;
  }

  // Begin transaction
  begin(): void {
    if (this.inTransaction) {
      throw new Error('Transaction already in progress');
    }

    this.db.run('BEGIN TRANSACTION');
    this.inTransaction = true;
    console.log('Transaction started');
  }

  // Commit transaction
  commit(): void {
    if (!this.inTransaction) {
      throw new Error('No transaction in progress');
    }

    this.db.run('COMMIT');
    this.inTransaction = false;
    this.savepoints = [];
    console.log('Transaction committed');
  }

  // Rollback transaction
  rollback(): void {
    if (!this.inTransaction) {
      throw new Error('No transaction in progress');
    }

    this.db.run('ROLLBACK');
    this.inTransaction = false;
    this.savepoints = [];
    console.log('Transaction rolled back');
  }

  // Create savepoint
  createSavepoint(name: string): void {
    if (!this.inTransaction) {
      throw new Error('No transaction in progress');
    }

    this.db.run(`SAVEPOINT ${name}`);
    this.savepoints.push(name);
    console.log(`Savepoint ${name} created`);
  }

  // Release savepoint
  releaseSavepoint(name: string): void {
    if (!this.savepoints.includes(name)) {
      throw new Error(`Savepoint ${name} not found`);
    }

    this.db.run(`RELEASE SAVEPOINT ${name}`);
    this.savepoints = this.savepoints.filter(sp => sp !== name);
    console.log(`Savepoint ${name} released`);
  }

  // Rollback to savepoint
  rollbackToSavepoint(name: string): void {
    if (!this.savepoints.includes(name)) {
      throw new Error(`Savepoint ${name} not found`);
    }

    this.db.run(`ROLLBACK TO SAVEPOINT ${name}`);
    this.savepoints = this.savepoints.filter(sp => sp !== name);
    console.log(`Rolled back to savepoint ${name}`);
  }

  // Execute in transaction
  async transaction<T>(callback: () => T): Promise<T> {
    this.begin();

    try {
      const result = callback();
      this.commit();
      return result;
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  // Check if in transaction
  isInTransaction(): boolean {
    return this.inTransaction;
  }

  // Get active savepoints
  getSavepoints(): string[] {
    return [...this.savepoints];
  }
}

// 2. Unit of Work Pattern
class UnitOfWork {
  private db: any;
  private transaction: TransactionManager;
  private newEntities: Map<string, any[]> = new Map();
  private modifiedEntities: Map<string, any[]> = new Map();
  private deletedEntities: Map<string, any[]> = new Map();

  constructor(db: any) {
    this.db = db;
    this.transaction = new TransactionManager(db);
  }

  // Register new entity
  registerNew(tableName: string, entity: any): void {
    if (!this.newEntities.has(tableName)) {
      this.newEntities.set(tableName, []);
    }
    this.newEntities.get(tableName)!.push(entity);
  }

  // Register modified entity
  registerModified(tableName: string, entity: any): void {
    if (!this.modifiedEntities.has(tableName)) {
      this.modifiedEntities.set(tableName, []);
    }
    this.modifiedEntities.get(tableName)!.push(entity);
  }

  // Register deleted entity
  registerDeleted(tableName: string, entity: any): void {
    if (!this.deletedEntities.has(tableName)) {
      this.deletedEntities.set(tableName, []);
    }
    this.deletedEntities.get(tableName)!.push(entity);
  }

  // Commit all changes
  commit(): void {
    this.transaction.begin();

    try {
      // Insert new entities
      for (const [tableName, entities] of this.newEntities) {
        for (const entity of entities) {
          const executor = new QueryExecutor(this.db);
          const columns = Object.keys(entity);
          const values = Object.values(entity);
          const placeholders = columns.map(() => '?').join(', ');
          const sql = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
          this.db.run(sql, values);
        }
      }

      // Update modified entities
      for (const [tableName, entities] of this.modifiedEntities) {
        for (const entity of entities) {
          const executor = new QueryExecutor(this.db);
          const columns = Object.keys(entity).filter(k => k !== 'id');
          const values = columns.map(k => entity[k]);
          const setClause = columns.map(k => `${k} = ?`).join(', ');
          const sql = `UPDATE ${tableName} SET ${setClause} WHERE id = ?`;
          this.db.run(sql, [...values, entity.id]);
        }
      }

      // Delete entities
      for (const [tableName, entities] of this.deletedEntities) {
        for (const entity of entities) {
          const executor = new QueryExecutor(this.db);
          const sql = `DELETE FROM ${tableName} WHERE id = ?`;
          this.db.run(sql, [entity.id]);
        }
      }

      this.transaction.commit();
      this.clean();
    } catch (error) {
      this.transaction.rollback();
      throw error;
    }
  }

  // Rollback all changes
  rollback(): void {
    this.transaction.rollback();
    this.clean();
  }

  // Clean tracked entities
  private clean(): void {
    this.newEntities.clear();
    this.modifiedEntities.clear();
    this.deletedEntities.clear();
  }

  // Check if has changes
  hasChanges(): boolean {
    return this.newEntities.size > 0 ||
           this.modifiedEntities.size > 0 ||
           this.deletedEntities.size > 0;
  }
}

// 3. Transaction Isolation Levels
class TransactionIsolation {
  private db: any;

  constructor(db: any) {
    this.db = db;
  }

  // Set isolation level (simulated for SQL.js)
  setIsolationLevel(level: 'DEFERRED' | 'IMMEDIATE' | 'EXCLUSIVE'): void {
    const sql = `BEGIN ${level} TRANSACTION`;
    this.db.run(sql);
    console.log(`Isolation level set to ${level}`);
  }

  // Read uncommitted (simulated)
  readUncommitted(callback: () => void): void {
    console.log('Starting READ UNCOMMITTED transaction');
    this.begin();
    try {
      callback();
      this.commit();
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  // Read committed (simulated)
  readCommitted(callback: () => void): void {
    console.log('Starting READ COMMITTED transaction');
    this.begin();
    try {
      callback();
      this.commit();
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  // Repeatable read (simulated)
  repeatableRead(callback: () => void): void {
    console.log('Starting REPEATABLE READ transaction');
    this.begin();
    try {
      callback();
      this.commit();
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  // Serializable (simulated)
  serializable(callback: () => void): void {
    console.log('Starting SERIALIZABLE transaction');
    this.begin();
    try {
      callback();
      this.commit();
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  private begin(): void {
    this.db.run('BEGIN IMMEDIATE TRANSACTION');
  }

  private commit(): void {
    this.db.run('COMMIT');
  }

  private rollback(): void {
    this.db.run('ROLLBACK');
  }
}

// 4. Nested Transactions
class NestedTransaction {
  private db: any;
  private transactionStack: TransactionManager[] = [];
  private depth: number = 0;

  constructor(db: any) {
    this.db = db;
  }

  // Begin nested transaction
  begin(): void {
    if (this.depth === 0) {
      // Top-level transaction
      const tx = new TransactionManager(this.db);
      tx.begin();
      this.transactionStack.push(tx);
    } else {
      // Nested transaction using savepoint
      const parent = this.transactionStack[this.depth - 1];
      const savepointName = `sp_${this.depth}`;
      parent.createSavepoint(savepointName);
      this.transactionStack.push(parent);
    }

    this.depth++;
    console.log(`Transaction depth: ${this.depth}`);
  }

  // Commit current level
  commit(): void {
    if (this.depth === 0) {
      throw new Error('No transaction to commit');
    }

    this.depth--;

    if (this.depth === 0) {
      // Commit top-level transaction
      const tx = this.transactionStack.pop()!;
      tx.commit();
    } else {
      // Release savepoint
      const parent = this.transactionStack[this.depth - 1];
      const savepointName = `sp_${this.depth}`;
      parent.releaseSavepoint(savepointName);
      this.transactionStack.pop();
    }

    console.log(`Transaction depth: ${this.depth}`);
  }

  // Rollback current level
  rollback(): void {
    if (this.depth === 0) {
      throw new Error('No transaction to rollback');
    }

    this.depth--;

    if (this.depth === 0) {
      // Rollback top-level transaction
      const tx = this.transactionStack.pop()!;
      tx.rollback();
    } else {
      // Rollback to savepoint
      const parent = this.transactionStack[this.depth - 1];
      const savepointName = `sp_${this.depth}`;
      parent.rollbackToSavepoint(savepointName);
      this.transactionStack.pop();
    }

    console.log(`Transaction depth: ${this.depth}`);
  }

  // Get current depth
  getDepth(): number {
    return this.depth;
  }
}

// 5. Transaction Logger
class TransactionLogger {
  private transaction: TransactionManager;
  private logs: Array<{ event: string; timestamp: number }> = [];

  constructor(transaction: TransactionManager) {
    this.transaction = transaction;
  }

  // Begin with logging
  begin(): void {
    this.log('BEGIN');
    this.transaction.begin();
  }

  // Commit with logging
  commit(): void {
    this.log('COMMIT');
    this.transaction.commit();
  }

  // Rollback with logging
  rollback(): void {
    this.log('ROLLBACK');
    this.transaction.rollback();
  }

  // Log event
  private log(event: string): void {
    this.logs.push({
      event,
      timestamp: Date.now()
    });
    console.log(`[TX LOG] ${event} at ${new Date().toISOString()}`);
  }

  // Get transaction history
  getHistory(): Array<{ event: string; timestamp: number }> {
    return [...this.logs];
  }

  // Clear logs
  clear(): void {
    this.logs = [];
  }
}

// 6. Distributed Transaction Coordinator (Simulated)
class DistributedTransactionCoordinator {
  private participants: Map<string, any> = new Map();
  private prepared: Set<string> = new Set();

  // Register participant
  registerParticipant(name: string, db: any): void {
    this.participants.set(name, db);
    console.log(`Participant ${name} registered`);
  }

  // Two-phase commit: prepare phase
  async prepare(): Promise<boolean> {
    console.log('Starting prepare phase...');

    for (const [name, db] of this.participants) {
      try {
        db.run('BEGIN TRANSACTION');
        // Simulate prepare
        this.prepared.add(name);
        console.log(`${name} prepared`);
      } catch (error) {
        console.error(`${name} prepare failed`, error);
        await this.rollback();
        return false;
      }
    }

    return true;
  }

  // Two-phase commit: commit phase
  async commit(): Promise<void> {
    console.log('Starting commit phase...');

    for (const [name, db] of this.participants) {
      if (this.prepared.has(name)) {
        try {
          db.run('COMMIT');
          console.log(`${name} committed`);
        } catch (error) {
          console.error(`${name} commit failed`, error);
        }
      }
    }

    this.prepared.clear();
  }

  // Two-phase commit: rollback
  async rollback(): Promise<void> {
    console.log('Starting rollback...');

    for (const [name, db] of this.participants) {
      try {
        db.run('ROLLBACK');
        console.log(`${name} rolled back`);
      } catch (error) {
        console.error(`${name} rollback failed`, error);
      }
    }

    this.prepared.clear();
  }

  // Execute distributed transaction
  async execute(callback: () => Promise<void>): Promise<boolean> {
    const prepared = await this.prepare();

    if (!prepared) {
      return false;
    }

    try {
      await callback();
      await this.commit();
      return true;
    } catch (error) {
      console.error('Distributed transaction failed', error);
      await this.rollback();
      return false;
    }
  }
}

// Query Executor helper
class QueryExecutor {
  constructor(private db: any) {}

  select(sql: string, params: any[] = []): any[] {
    const results = this.db.exec(sql, params);
    if (results.length > 0) {
      const columns = results[0].columns;
      const values = results[0].values;
      return values.map((row: any[]) => {
        const obj: Record<string, any> = {};
        columns.forEach((col: string, index: number) => {
          obj[col] = row[index];
        });
        return obj;
      });
    }
    return [];
  }
}

// Usage Examples
async function demonstrateTransactionHandling() {
  console.log('=== Web TypeScript Transaction Handling Examples ===\n');

  // Mock database
  const mockDB = {
    exec: (sql: string, params?: any) => {
      console.log(`Executing: ${sql}`);
      return [];
    },
    run: (sql: string, params?: any) => {
      console.log(`Running: ${sql}`);
    }
  };

  // 1. Basic transaction
  console.log('--- 1. Basic Transaction ---');
  const txManager = new TransactionManager(mockDB);
  txManager.begin();
  console.log(`In transaction: ${txManager.isInTransaction()}`);
  txManager.commit();

  // 2. Transaction with callback
  console.log('\n--- 2. Transaction with Callback ---');
  try {
    await txManager.transaction(() => {
      console.log('Executing operations in transaction...');
      mockDB.run('INSERT INTO users (name) VALUES (?)', ['Alice']);
      mockDB.run('INSERT INTO users (name) VALUES (?)', ['Bob']);
      return 'success';
    });
  } catch (error) {
    console.error('Transaction failed:', error);
  }

  // 3. Savepoints
  console.log('\n--- 3. Savepoints ---');
  txManager.begin();
  mockDB.run('INSERT INTO users (name) VALUES (?)', ['Charlie']);

  txManager.createSavepoint('sp1');
  mockDB.run('INSERT INTO users (name) VALUES (?)', ['David']);

  txManager.rollbackToSavepoint('sp1');
  txManager.commit();

  // 4. Nested transactions
  console.log('\n--- 4. Nested Transactions ---');
  const nestedTx = new NestedTransaction(mockDB);
  nestedTx.begin();
  console.log(`Depth: ${nestedTx.getDepth()}`);

  nestedTx.begin();
  console.log(`Depth: ${nestedTx.getDepth()}`);

  nestedTx.commit();
  nestedTx.commit();

  // 5. Unit of Work
  console.log('\n--- 5. Unit of Work ---');
  const uow = new UnitOfWork(mockDB);
  uow.registerNew('users', { name: 'Eve' });
  uow.registerNew('users', { name: 'Frank' });

  if (uow.hasChanges()) {
    console.log('Committing changes...');
    // uow.commit();
  }

  console.log('\n=== All Transaction Handling Examples Completed ===');
}

// Export functions
export { TransactionManager, UnitOfWork, TransactionIsolation, NestedTransaction, TransactionLogger, DistributedTransactionCoordinator };
export { demonstrateTransactionHandling };