// config/datastores.js
module.exports.datastores = {
  default: {
    adapter: 'sails-sqlite',
    url: 'db/production.sqlite'
  }
}

// config/datastores.js
module.exports.datastores = {
  default: {
    adapter: 'sails-sqlite',
    url: 'db/production.sqlite',

    // Recommended performance pragmas for optimal SQLite performance
    pragmas: {
      journal_mode: 'WAL', // Better concurrency
      synchronous: 'NORMAL', // Balanced durability/performance
      cache_size: -262144, // 256MB cache
      mmap_size: 268435456, // 256MB memory-mapped I/O
      foreign_keys: 'ON', // Enforce foreign keys
      busy_timeout: 30000, // 30 second busy timeout
      temp_store: 'MEMORY' // Store temp tables in memory
    },

    // Connection options
    timeout: 10000, // 10 second connection timeout
    verbose: process.env.NODE_ENV === 'development' ? console.log : null
  }
}

// api/models/User.js
module.exports = {
  attributes: {
    id: {
      type: 'number',
      autoIncrement: true,
      columnName: 'id'
    },
    name: {
      type: 'string',
      required: true,
      maxLength: 100
    },
    email: {
      type: 'string',
      required: true,
      unique: true,
      isEmail: true
    },
    preferences: {
      type: 'json',
      defaultsTo: {}
    },
    isActive: {
      type: 'boolean',
      defaultsTo: true,
      columnName: 'is_active'
    }
  }
}

// High-performance batch insert
const users = await User.createEach([
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' },
  { name: 'Charlie', email: '[email protected]' }
]).fetch()

console.log(`Created ${users.length} users efficiently`)

// Using the enhanced database manager
const dsEntry = sails.datastores.default
const result = dsEntry.manager.runInTransaction(() => {
  // Multiple operations in a single transaction
  const user = dsEntry.manager
    .prepare('INSERT INTO users (name, email) VALUES (?, ?)')
    .run('John', '[email protected]')
  const profile = dsEntry.manager
    .prepare('INSERT INTO profiles (user_id, bio) VALUES (?, ?)')
    .run(user.lastInsertRowid, 'Software Developer')
  return { user, profile }
})

// Check database health
const dsEntry = sails.datastores.default
if (dsEntry.manager.isHealthy()) {
  console.log('Database connection is healthy')
} else {
  console.error('Database connection issues detected')
}

// Optimize database performance (run periodically)
const dsEntry = sails.datastores.default
dsEntry.manager.optimize() // Runs PRAGMA optimize, VACUUM, ANALYZE