Sequelize Beispiele

const { Sequelize, DataTypes, Model, Op } = require('sequelize')

// Initialize Sequelize connection
const sequelize = new Sequelize({
  dialect: 'postgres',
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  username: 'postgres',
  password: 'password',
  logging: console.log,
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
})

// User Model
class User extends Model {}
User.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  username: {
    type: DataTypes.STRING,
    allowNull: true,
    unique: true,
    validate: {
      len: [3, 30]
    }
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      len: [6, 100]
    }
  },
  firstName: {
    type: DataTypes.STRING,
    allowNull: true
  },
  lastName: {
    type: DataTypes.STRING,
    allowNull: true
  },
  isActive: {
    type: DataTypes.BOOLEAN,
    defaultValue: true
  },
  role: {
    type: DataTypes.ENUM('USER', 'ADMIN', 'MODERATOR', 'AUTHOR'),
    defaultValue: 'USER'
  },
  lastLoginAt: {
    type: DataTypes.DATE,
    allowNull: true
  },
  profilePicture: {
    type: DataTypes.STRING,
    allowNull: true
  }
}, {
  sequelize,
  modelName: 'User',
  tableName: 'users',
  timestamps: true,
  indexes: [
    {
      unique: true,
      fields: ['email']
    },
    {
      fields: ['username']
    },
    {
      fields: ['isActive']
    }
  ]
})

// Profile Model
class Profile extends Model {}
Profile.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  userId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    unique: true,
    references: {
      model: User,
      key: 'id'
    }
  },
  bio: {
    type: DataTypes.TEXT,
    allowNull: true
  },
  avatar: {
    type: DataTypes.STRING,
    allowNull: true
  },
  website: {
    type: DataTypes.STRING,
    allowNull: true,
    validate: {
      isUrl: true
    }
  },
  birthDate: {
    type: DataTypes.DATEONLY,
    allowNull: true
  },
  phone: {
    type: DataTypes.STRING,
    allowNull: true
  },
  address: {
    type: DataTypes.JSON,
    allowNull: true
  }
}, {
  sequelize,
  modelName: 'Profile',
  tableName: 'profiles'
})

// Post Model
class Post extends Model {}
Post.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      len: [1, 200]
    }
  },
  slug: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  excerpt: {
    type: DataTypes.STRING,
    allowNull: true
  },
  publishedAt: {
    type: DataTypes.DATE,
    allowNull: true
  },
  status: {
    type: DataTypes.ENUM('DRAFT', 'PUBLISHED', 'ARCHIVED'),
    defaultValue: 'DRAFT'
  },
  featuredImage: {
    type: DataTypes.STRING,
    allowNull: true
  },
  tags: {
    type: DataTypes.JSON,
    allowNull: true,
    defaultValue: []
  },
  viewCount: {
    type: DataTypes.INTEGER,
    defaultValue: 0
  },
  metadata: {
    type: DataTypes.JSON,
    allowNull: true
  }
}, {
  sequelize,
  modelName: 'Post',
  tableName: 'posts',
  indexes: [
    {
      fields: ['authorId']
    },
    {
      fields: ['status']
    },
    {
      fields: ['publishedAt']
    },
    {
      fields: ['slug']
    }
  ]
})

// Comment Model
class Comment extends Model {}
Comment.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false,
    validate: {
      len: [1, 1000]
    }
  },
  postId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: {
      model: Post,
      key: 'id'
    }
  },
  authorId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: {
      model: User,
      key: 'id'
    }
  },
  parentId: {
    type: DataTypes.INTEGER,
    allowNull: true,
    references: {
      model: 'comments',
      key: 'id'
    }
  },
  isApproved: {
    type: DataTypes.BOOLEAN,
    defaultValue: false
  }
}, {
  sequelize,
  modelName: 'Comment',
  tableName: 'comments',
  indexes: [
    {
      fields: ['postId']
    },
    {
      fields: ['authorId']
    },
    {
      fields: ['parentId']
    }
  ]
})

// Category Model
class Category extends Model {}
Category.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  description: {
    type: DataTypes.TEXT,
    allowNull: true
  },
  slug: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  color: {
    type: DataTypes.STRING,
    allowNull: true
  },
  icon: {
    type: DataTypes.STRING,
    allowNull: true
  }
}, {
  sequelize,
  modelName: 'Category',
  tableName: 'categories'
})

// Define Associations (Relationships)
User.hasOne(Profile, {
  foreignKey: 'userId',
  as: 'profile'
})
Profile.belongsTo(User, {
  foreignKey: 'userId',
  as: 'user'
})

User.hasMany(Post, {
  foreignKey: 'authorId',
  as: 'posts'
})
Post.belongsTo(User, {
  foreignKey: 'authorId',
  as: 'author'
})

User.hasMany(Comment, {
  foreignKey: 'authorId',
  as: 'comments'
})
Comment.belongsTo(User, {
  foreignKey: 'authorId',
  as: 'author'
})

Post.hasMany(Comment, {
  foreignKey: 'postId',
  as: 'comments'
})
Comment.belongsTo(Post, {
  foreignKey: 'postId',
  as: 'post'
})

// Self-referencing relationship for nested comments
Comment.hasMany(Comment, {
  foreignKey: 'parentId',
  as: 'replies'
})
Comment.belongsTo(Comment, {
  foreignKey: 'parentId',
  as: 'parent'
})

// Many-to-Many: Posts and Categories
const PostCategory = sequelize.define('PostCategory', {
  postId: {
    type: DataTypes.INTEGER,
    references: {
      model: Post,
      key: 'id'
    }
  },
  categoryId: {
    type: DataTypes.INTEGER,
    references: {
      model: Category,
      key: 'id'
    }
  }
}, {
  tableName: 'post_categories',
  timestamps: false
})

Post.belongsToMany(Category, {
  through: PostCategory,
  foreignKey: 'postId',
  otherKey: 'categoryId',
  as: 'categories'
})

Category.belongsToMany(Post, {
  through: PostCategory,
  foreignKey: 'categoryId',
  otherKey: 'postId',
  as: 'posts'
})

// Hooks and Instance Methods
User.beforeCreate(async (user) => {
  // Hash password before creating user
  const bcrypt = require('bcrypt')
  user.password = await bcrypt.hash(user.password, 10)
})

User.prototype.comparePassword = async function(password) {
  const bcrypt = require('bcrypt')
  return await bcrypt.compare(password, this.password)
}

User.prototype.toJSON = function() {
  const values = Object.assign({}, this.get())
  delete values.password
  return values
}

Post.beforeSave(async (post) => {
  // Generate excerpt if not provided
  if (!post.excerpt && post.content) {
    post.excerpt = post.content.substring(0, 150) + '...'
  }

  // Generate slug if not provided
  if (!post.slug && post.title) {
    post.slug = post.title.toLowerCase()
      .replace(/[^a-z0-9]+/g, '-')
      .replace(/(^-|-$)/g, '')
  }
})

module.exports = {
  sequelize,
  User,
  Profile,
  Post,
  Comment,
  Category,
  PostCategory
}

const { User, Profile, Post, Comment } = require('./models')

// CREATE Operations
async function createUser(userData) {
  try {
    const user = await User.create({
      email: userData.email,
      username: userData.username,
      password: userData.password,
      firstName: userData.firstName,
      lastName: userData.lastName,
      role: userData.role || 'USER'
    })

    return user
  } catch (error) {
    console.error('Error creating user:', error)
    throw error
  }
}

// Create user with profile
async function createUserWithProfile(userData) {
  const user = await User.create(userData, {
    include: [{
      association: User.Profile,
      as: 'profile'
    }]
  })
  return user
}

// Create multiple users
async function createMultipleUsers(usersData) {
  const users = await User.bulkCreate(usersData, {
    validate: true,
    individualHooks: true
  })
  return users
}

// READ Operations
async function getUserById(id) {
  const user = await User.findByPk(id, {
    include: [
      {
        model: Profile,
        as: 'profile'
      },
      {
        model: Post,
        as: 'posts',
        include: [
          {
            model: Comment,
            as: 'comments'
          }
        ]
      }
    ]
  })
  return user
}

async function getUserByEmail(email) {
  const user = await User.findOne({
    where: { email },
    attributes: ['id', 'email', 'username', 'firstName', 'lastName', 'role', 'createdAt']
  })
  return user
}

async function getAllUsers(options = {}) {
  const {
    page = 1,
    limit = 10,
    search,
    role,
    sortBy = 'createdAt',
    sortOrder = 'DESC'
  } = options

  const offset = (page - 1) * limit

  const where = {
    [Op.and]: []
  }

  if (search) {
    where[Op.and].push({
      [Op.or]: [
        { username: { [Op.like]: `%${search}%` } },
        { email: { [Op.like]: `%${search}%` } },
        { firstName: { [Op.like]: `%${search}%` } },
        { lastName: { [Op.like]: `%${search}%` } }
      ]
    })
  }

  if (role) {
    where[Op.and].push({ role })
  }

  where[Op.and].push({ isActive: true })

  const { count, rows } = await User.findAndCountAll({
    where,
    offset,
    limit,
    include: [
      {
        model: Profile,
        as: 'profile',
        attributes: ['bio', 'avatar', 'website']
      }
    ],
    order: [[sortBy, sortOrder.toUpperCase()]]
  })

  return {
    users: rows,
    pagination: {
      page,
      limit,
      total: count,
      pages: Math.ceil(count / limit)
    }
  }
}

// UPDATE Operations
async function updateUser(id, updateData) {
  const user = await User.update(updateData, {
    where: { id },
    returning: true,
    include: [{
      model: Profile,
      as: 'profile'
    }]
  })

  return user[1][0] // Return the updated user
}

async function updateOrCreateProfile(userId, profileData) {
  const [profile, created] = await Profile.findOrCreate({
    where: { userId },
    defaults: {
      userId,
      ...profileData
    }
  })

  if (!created) {
    await profile.update(profileData)
  }

  return profile
}

// DELETE Operations
async function deleteUser(id) {
  // Soft delete
  const user = await User.update(
    { isActive: false },
    { where: { id } }
  )
  return user
}

async function hardDeleteUser(id) {
  const user = await User.destroy({
    where: { id }
  })
  return user
}

// Complex Queries
async function getUserWithPostStats(userId) {
  const user = await User.findByPk(userId, {
    attributes: [
      'id', 'email', 'username', 'firstName', 'lastName', 'role'
    ],
    include: [
      {
        model: Post,
        as: 'posts',
        attributes: [
          'id', 'title', 'publishedAt', 'viewCount', 'status'
        ],
        include: [
          {
            model: Comment,
            as: 'comments',
            attributes: ['id'],
            required: false
          }
        ]
      }
    ]
  })

  // Add post counts
  const userWithStats = user.toJSON()
  userWithStats.stats = {
    totalPosts: user.posts.length,
    publishedPosts: user.posts.filter(p => p.status === 'PUBLISHED').length,
    totalComments: user.posts.reduce((sum, post) => sum + post.comments.length, 0),
    totalViews: user.posts.reduce((sum, post) => sum + post.viewCount, 0)
  }

  return userWithStats
}

// Advanced Search
async function searchPosts(query) {
  const posts = await Post.findAll({
    where: {
      [Op.and]: [
        {
          [Op.or]: [
            { title: { [Op.like]: `%${query}%` } },
            { content: { [Op.like]: `%${query}%` } },
            { excerpt: { [Op.like]: `%${query}%` } }
          ]
        },
        { status: 'PUBLISHED' },
        { publishedAt: { [Op.not]: null } }
      ]
    },
    include: [
      {
        model: User,
        as: 'author',
        attributes: ['id', 'username', 'firstName', 'lastName']
      },
      {
        model: Comment,
        as: 'comments',
        attributes: ['id'],
        required: false
      }
    ],
    order: [['publishedAt', 'DESC']],
    limit: 20
  })

  return posts
}

// Aggregation Queries
async function getPostAnalytics() {
  const analytics = await Post.findAll({
    attributes: [
      [sequelize.fn('COUNT', sequelize.col('id')), 'totalPosts'],
      [sequelize.fn('COUNT', sequelize.col('id')), 'publishedPosts'],
      [sequelize.fn('AVG', sequelize.col('viewCount')), 'avgViews'],
      [sequelize.fn('SUM', sequelize.col('viewCount')), 'totalViews']
    ],
    where: {
      [Op.or]: [
        { status: 'PUBLISHED' },
        { status: 'DRAFT' }
      ]
    },
    raw: true
  })

  return analytics[0]
}

// Raw SQL Queries
async function getUsersWithMostPosts() {
  const users = await sequelize.query(`
    SELECT
      u.id,
      u.username,
      u.email,
      COUNT(p.id) as post_count,
      COUNT(c.id) as comment_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.author_id
    LEFT JOIN comments c ON u.id = c.author_id
    WHERE u.is_active = true
    GROUP BY u.id, u.username, u.email
    HAVING COUNT(p.id) > 0
    ORDER BY post_count DESC
    LIMIT 10
  `, {
    type: Sequelize.QueryTypes.SELECT
  })

  return users
}

// Transaction Operations
async function transferPostOwnership(postId, newAuthorId) {
  const result = await sequelize.transaction(async (t) => {
    const post = await Post.findByPk(postId, {
      include: [{
        model: User,
        as: 'author'
      }],
      transaction: t
    })

    if (!post) {
      throw new Error('Post not found')
    }

    const updatedPost = await post.update({
      authorId: newAuthorId
    }, { transaction: t })

    return {
      previousAuthor: post.author,
      updatedPost
    }
  })

  return result
}

// Bulk Operations
async function archiveOldPosts(daysOld = 365) {
  const cutoffDate = new Date()
  cutoffDate.setDate(cutoffDate.getDate() - daysOld)

  const [affectedCount] = await Post.update(
    {
      status: 'ARCHIVED',
      archivedAt: new Date()
    },
    {
      where: {
        publishedAt: {
          [Op.lt]: cutoffDate
        },
        status: 'PUBLISHED'
      }
    }
  )

  return affectedCount
}

module.exports = {
  createUser,
  createUserWithProfile,
  createMultipleUsers,
  getUserById,
  getUserByEmail,
  getAllUsers,
  updateUser,
  updateOrCreateProfile,
  deleteUser,
  hardDeleteUser,
  getUserWithPostStats,
  searchPosts,
  getPostAnalytics,
  getUsersWithMostPosts,
  transferPostOwnership,
  archiveOldPosts
}

// Migration Example: 20231201000001-create-users.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('users', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      username: {
        type: Sequelize.STRING,
        allowNull: true,
        unique: true
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false
      },
      firstName: {
        type: Sequelize.STRING,
        allowNull: true
      },
      lastName: {
        type: Sequelize.STRING,
        allowNull: true
      },
      isActive: {
        type: Sequelize.BOOLEAN,
        defaultValue: true
      },
      role: {
        type: Sequelize.ENUM('USER', 'ADMIN', 'MODERATOR', 'AUTHOR'),
        defaultValue: 'USER'
      },
      lastLoginAt: {
        type: Sequelize.DATE,
        allowNull: true
      },
      createdAt: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    })

    // Create indexes
    await queryInterface.addIndex('users', ['email'], { unique: true })
    await queryInterface.addIndex('users', ['username'])
    await queryInterface.addIndex('users', ['isActive'])
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('users')
  }
}

// Migration Example: 20231201000002-create-profiles.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('profiles', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        unique: true,
        references: {
          model: 'users',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      bio: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      avatar: {
        type: Sequelize.STRING,
        allowNull: true
      },
      website: {
        type: Sequelize.STRING,
        allowNull: true
      },
      birthDate: {
        type: Sequelize.DATEONLY,
        allowNull: true
      },
      phone: {
        type: Sequelize.STRING,
        allowNull: true
      },
      address: {
        type: Sequelize.JSON,
        allowNull: true
      },
      createdAt: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    })

    await queryInterface.addIndex('profiles', ['userId'], { unique: true })
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('profiles')
  }
}

// Migration Example: 20231201000003-add-user-profile-field.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('users', 'profilePicture', {
      type: Sequelize.STRING,
      allowNull: true
    })
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('users', 'profilePicture')
  }
}

// Seeders Example: 20231201000001-users-seeder.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    const bcrypt = require('bcrypt')

    const users = [
      {
        email: '[email protected]',
        username: 'admin',
        password: await bcrypt.hash('admin123', 10),
        firstName: 'Admin',
        lastName: 'User',
        role: 'ADMIN',
        isActive: true,
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        email: '[email protected]',
        username: 'john_doe',
        password: await bcrypt.hash('password123', 10),
        firstName: 'John',
        lastName: 'Doe',
        role: 'USER',
        isActive: true,
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        email: '[email protected]',
        username: 'jane_smith',
        password: await bcrypt.hash('password123', 10),
        firstName: 'Jane',
        lastName: 'Smith',
        role: 'AUTHOR',
        isActive: true,
        createdAt: new Date(),
        updatedAt: new Date()
      }
    ]

    await queryInterface.bulkInsert('users', users)
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.bulkDelete('users', {
      email: {
        [Sequelize.Op.in]: [
          '[email protected]',
          '[email protected]',
          '[email protected]'
        ]
      }
    })
  }
}

// Database Configuration
const { Sequelize } = require('sequelize')

const sequelize = new Sequelize({
  dialect: 'postgres',
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'myapp',
  username: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'password',
  logging: process.env.NODE_ENV === 'development' ? console.log : false,
  pool: {
    max: parseInt(process.env.DB_POOL_MAX) || 5,
    min: parseInt(process.env.DB_POOL_MIN) || 0,
    acquire: parseInt(process.env.DB_POOL_ACQUIRE) || 30000,
    idle: parseInt(process.env.DB_POOL_IDLE) || 10000
  }
})

module.exports = sequelize

const { Sequelize, DataTypes, Model, Op } = require('sequelize')

// 1. Advanced Scopes
class User extends Model {
  // Instance methods
  getFullName() {
    return `${this.firstName} ${this.lastName}`
  }

  async getRecentPosts(limit = 5) {
    return await this.getPosts({
      where: {
        status: 'PUBLISHED',
        publishedAt: {
          [Op.not]: null
        }
      },
      order: [['publishedAt', 'DESC']],
      limit
    })
  }

  async deactivate() {
    return await this.update({ isActive: false })
  }
}

User.init({
  // ... fields
}, {
  sequelize,
  modelName: 'User',

  // Scopes for reusable queries
  scopes: {
    active: {
      where: { isActive: true }
    },
    admins: {
      where: { role: 'ADMIN' }
    },
    recent: (days = 30) => ({
      where: {
        createdAt: {
          [Op.gte]: new Date(Date.now() - days * 24 * 60 * 60 * 1000)
        }
      }
    }),
    withProfile: {
      include: [{
        model: Profile,
        as: 'profile'
      }]
    },
    withStats: {
      attributes: {
        include: [
          [
            sequelize.literal(`(
              SELECT COUNT(*)
              FROM posts
              WHERE posts.author_id = User.id
              AND posts.status = 'PUBLISHED'
            )`),
            'publishedPostCount'
          ],
          [
            sequelize.literal(`(
              SELECT COUNT(*)
              FROM comments
              WHERE comments.author_id = User.id
            )`),
            'commentCount'
          ]
        ]
      }
    }
  }
})

// Using scopes
async function getActiveAdminsWithStats() {
  const users = await User.scope([
    'active',
    'admins',
    'withProfile',
    'withStats'
  ]).findAll()

  return users
}

async function getRecentUsers(days = 7) {
  return await User.scope('recent', days).findAll({
    order: [['createdAt', 'DESC']]
  })
}

// 2. Advanced Hooks and Middleware
User.addHook('beforeCreate', async (user) => {
  console.log('Creating user:', user.email)

  // Additional validation
  if (user.email && user.email.endsWith('@spam.com')) {
    throw new Error('Spam emails not allowed')
  }
})

User.addHook('afterCreate', async (user) => {
  // Send welcome email
  await sendWelcomeEmail(user.email, user.getFullName())
})

User.addHook('beforeUpdate', async (user) => {
  if (user.changed('password')) {
    // Rehash password if changed
    const bcrypt = require('bcrypt')
    user.password = await bcrypt.hash(user.password, 10)
  }
})

User.addHook('afterDestroy', async (user) => {
  // Soft delete related data
  await Post.update(
    { authorId: null },
    { where: { authorId: user.id } }
  )
})

// 3. Advanced Transactions
async function complexTransaction() {
  const result = await sequelize.transaction({
    isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE,
    type: Sequelize.Transaction.TYPES.DEFERRED
  }, async (t) => {

    // Create user
    const user = await User.create({
      email: '[email protected]',
      password: 'password123'
    }, { transaction: t })

    // Create profile
    const profile = await Profile.create({
      userId: user.id,
      bio: 'New user bio'
    }, { transaction: t })

    // Create initial post
    const post = await Post.create({
      title: 'Welcome Post',
      content: 'My first post',
      authorId: user.id,
      status: 'PUBLISHED'
    }, { transaction: t })

    // If any operation fails, all changes are rolled back
    return { user, profile, post }
  })

  return result
}

// Parallel transactions
async function processBatchUsers(userDataArray) {
  const promises = userDataArray.map(userData =>
    sequelize.transaction(async (t) => {
      const user = await User.create(userData, { transaction: t })
      await Profile.create({
        userId: user.id,
        bio: `Bio for ${user.email}`
      }, { transaction: t })
      return user
    })
  )

  const results = await Promise.all(promises)
  return results
}

// 4. Performance Optimization

// Eager loading with nested associations
async function getPostsWithFullDetails(postIds) {
  const posts = await Post.findAll({
    where: {
      id: { [Op.in]: postIds }
    },
    include: [
      {
        model: User,
        as: 'author',
        attributes: ['id', 'username', 'firstName', 'lastName'],
        include: [{
          model: Profile,
          as: 'profile',
          attributes: ['avatar', 'bio']
        }]
      },
      {
        model: Comment,
        as: 'comments',
        include: [{
          model: User,
          as: 'author',
          attributes: ['username', 'firstName']
        }],
        where: { isApproved: true },
        required: false
      },
      {
        model: Category,
        as: 'categories',
        attributes: ['id', 'name', 'slug']
      }
    ],
    order: [
      ['publishedAt', 'DESC'],
      [{ model: Comment, as: 'comments' }, 'createdAt', 'ASC']
    ]
  })

  return posts
}

// Query optimization with limit and pagination
async function getOptimizedUserList(options = {}) {
  const { page = 1, limit = 20, search, sortBy = 'createdAt' } = options
  const offset = (page - 1) * limit

  const where = {
    isActive: true
  }

  if (search) {
    where[Op.or] = [
      { username: { [Op.iLike]: `%${search}%` } },
      { email: { [Op.iLike]: `%${search}%` } },
      [sequelize.fn('CONCAT', sequelize.col('firstName'), ' ', sequelize.col('lastName')),
       { [Op.iLike]: `%${search}%` }]
    ]
  }

  const { count, rows } = await User.findAndCountAll({
    where,
    offset,
    limit,
    attributes: ['id', 'username', 'email', 'firstName', 'lastName', 'role', 'createdAt'],
    order: [[sortBy, 'DESC']],
    include: [
      {
        model: Profile,
        as: 'profile',
        attributes: ['avatar'],
        required: false
      }
    ]
  })

  return {
    users: rows,
    pagination: {
      page,
      limit,
      total: count,
      pages: Math.ceil(count / limit)
    }
  }
}

// 5. Raw Query Optimization
async function getDashboardStats() {
  const stats = await sequelize.query(`
    SELECT
      (SELECT COUNT(*) FROM users WHERE is_active = true) as total_users,
      (SELECT COUNT(*) FROM posts WHERE status = 'PUBLISHED') as published_posts,
      (SELECT COUNT(*) FROM comments WHERE is_approved = true) as approved_comments,
      (SELECT COUNT(*) FROM categories) as total_categories,
      (SELECT AVG(view_count) FROM posts WHERE status = 'PUBLISHED') as avg_post_views,
      (SELECT
        COUNT(CASE WHEN status = 'PUBLISHED' THEN 1 END) * 100.0 / COUNT(*)
        FROM posts
        WHERE published_at >= NOW() - INTERVAL '30 days'
      ) as recent_publish_rate
  `, {
    type: Sequelize.QueryTypes.SELECT,
    plain: true
  })

  return stats
}

// 6. Caching Layer
class CacheService {
  constructor(ttl = 300) { // 5 minutes default TTL
    this.cache = new Map()
    this.ttl = ttl * 1000
  }

  set(key, value) {
    this.cache.set(key, {
      value,
      timestamp: Date.now()
    })
  }

  get(key) {
    const item = this.cache.get(key)
    if (!item) return null

    if (Date.now() - item.timestamp > this.ttl) {
      this.cache.delete(key)
      return null
    }

    return item.value
  }

  has(key) {
    return this.get(key) !== null
  }

  delete(key) {
    return this.cache.delete(key)
  }
}

const cache = new CacheService()

async function getCachedUserWithPosts(userId) {
  const cacheKey = `user_with_posts_${userId}`

  let cached = cache.get(cacheKey)
  if (cached) return cached

  const user = await User.findByPk(userId, {
    include: [
      {
        model: Post,
        as: 'posts',
        where: { status: 'PUBLISHED' },
        required: false,
        order: [['publishedAt', 'DESC']]
      }
    ]
  })

  cache.set(cacheKey, user)
  return user
}

// 7. Connection Pool Management
async function getConnectionPoolStats() {
  const pool = sequelize.connectionManager.pool

  return {
    total: pool.total,
    all: pool.all,
    waiting: pool.waiting,
    idle: pool.idle,
    used: pool.used,
    max: pool.max,
    min: pool.min
  }
}

// 8. Error Handling and Validation
class ValidationError extends Error {
  constructor(message, field) {
    super(message)
    this.name = 'ValidationError'
    this.field = field
  }
}

async function createUserWithValidation(userData) {
  try {
    // Custom validation
    if (userData.email && userData.email.endsWith('@blocked.com')) {
      throw new ValidationError('Email domain is blocked', 'email')
    }

    if (userData.username && userData.username.length < 3) {
      throw new ValidationError('Username must be at least 3 characters', 'username')
    }

    const user = await User.create(userData)
    return user
  } catch (error) {
    if (error.name === 'SequelizeUniqueConstraintError') {
      const field = error.errors[0]?.path
      throw new ValidationError(`${field} already exists`, field)
    }

    if (error.name === 'SequelizeValidationError') {
      const field = error.errors[0]?.path
      const message = error.errors[0]?.message
      throw new ValidationError(message, field)
    }

    throw error
  }
}

module.exports = {
  User,
  getActiveAdminsWithStats,
  getRecentUsers,
  complexTransaction,
  processBatchUsers,
  getPostsWithFullDetails,
  getOptimizedUserList,
  getDashboardStats,
  getCachedUserWithPosts,
  getConnectionPoolStats,
  createUserWithValidation,
  ValidationError,
  cache
}