علاقات قاعدة البيانات والاستعلامات

// models/User.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true
  },
  email: {
    type: DataTypes.STRING(100),
    allowNull: false,
    unique: true
  }
}, {
  tableName: 'users',
  timestamps: true
});

module.exports = User;

// models/Post.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Post = sequelize.define('Post', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  title: {
    type: DataTypes.STRING(200),
    allowNull: false
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  userId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'user_id',
    references: {
      model: 'users',
      key: 'id'
    },
    onUpdate: 'CASCADE',
    onDelete: 'CASCADE'
  }
}, {
  tableName: 'posts',
  timestamps: true
});

module.exports = Post;

// models/index.js - تعريف الارتباطات
const User = require('./User');
const Post = require('./Post');

// مستخدم واحد لديه العديد من المنشورات
User.hasMany(Post, {
  foreignKey: 'userId',
  as: 'posts',
  onDelete: 'CASCADE'
});

// كل منشور ينتمي إلى مستخدم واحد
Post.belongsTo(User, {
  foreignKey: 'userId',
  as: 'author'
});

module.exports = { User, Post };

// الحصول على المستخدم مع جميع المنشورات
const getUserWithPosts = async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id, {
      include: [{
        model: Post,
        as: 'posts'
      }]
    });

    if (!user) {
      return res.status(404).json({
        success: false,
        error: 'المستخدم غير موجود'
      });
    }

    res.status(200).json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// الحصول على المنشور مع المؤلف
const getPostWithAuthor = async (req, res) => {
  try {
    const post = await Post.findByPk(req.params.id, {
      include: [{
        model: User,
        as: 'author',
        attributes: ['id', 'username', 'email']
      }]
    });

    if (!post) {
      return res.status(404).json({
        success: false,
        error: 'المنشور غير موجود'
      });
    }

    res.status(200).json({
      success: true,
      data: post
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// models/User.js
const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({
  username: {
    type: String,
    required: true,
    unique: true
  },
  email: {
    type: String,
    required: true,
    unique: true
  }
}, {
  timestamps: true,
  toJSON: { virtuals: true },
  toObject: { virtuals: true }
});

// التعبئة الافتراضية
userSchema.virtual('posts', {
  ref: 'Post',
  localField: '_id',
  foreignField: 'author'
});

const User = mongoose.model('User', userSchema);
module.exports = User;

// models/Post.js
const mongoose = require('mongoose');

const postSchema = new mongoose.Schema({
  title: {
    type: String,
    required: true
  },
  content: {
    type: String,
    required: true
  },
  author: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true
  }
}, {
  timestamps: true
});

const Post = mongoose.model('Post', postSchema);
module.exports = Post;

// الاستعلام مع التعبئة
const getUserWithPosts = async (req, res) => {
  try {
    const user = await User
      .findById(req.params.id)
      .populate('posts');

    if (!user) {
      return res.status(404).json({
        success: false,
        error: 'المستخدم غير موجود'
      });
    }

    res.status(200).json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

const getPostWithAuthor = async (req, res) => {
  try {
    const post = await Post
      .findById(req.params.id)
      .populate('author', 'username email');

    if (!post) {
      return res.status(404).json({
        success: false,
        error: 'المنشور غير موجود'
      });
    }

    res.status(200).json({
      success: true,
      data: post
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// models/Student.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Student = sequelize.define('Student', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(100),
    allowNull: false
  },
  email: {
    type: DataTypes.STRING(100),
    allowNull: false,
    unique: true
  }
}, {
  tableName: 'students',
  timestamps: true
});

module.exports = Student;

// models/Course.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Course = sequelize.define('Course', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(100),
    allowNull: false
  },
  code: {
    type: DataTypes.STRING(20),
    allowNull: false,
    unique: true
  }
}, {
  tableName: 'courses',
  timestamps: true
});

module.exports = Course;

// models/Enrollment.js - جدول الوصلة
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Enrollment = sequelize.define('Enrollment', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  studentId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'student_id'
  },
  courseId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'course_id'
  },
  enrollmentDate: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW,
    field: 'enrollment_date'
  },
  grade: {
    type: DataTypes.STRING(2),
    allowNull: true
  }
}, {
  tableName: 'enrollments',
  timestamps: true
});

module.exports = Enrollment;

// models/index.js - تعريف الارتباطات
const Student = require('./Student');
const Course = require('./Course');
const Enrollment = require('./Enrollment');

// علاقة متعدد لمتعدد
Student.belongsToMany(Course, {
  through: Enrollment,
  foreignKey: 'studentId',
  otherKey: 'courseId',
  as: 'courses'
});

Course.belongsToMany(Student, {
  through: Enrollment,
  foreignKey: 'courseId',
  otherKey: 'studentId',
  as: 'students'
});

// الوصول إلى جدول الوصلة
Student.hasMany(Enrollment, { foreignKey: 'studentId' });
Enrollment.belongsTo(Student, { foreignKey: 'studentId' });

Course.hasMany(Enrollment, { foreignKey: 'courseId' });
Enrollment.belongsTo(Course, { foreignKey: 'courseId' });

module.exports = { Student, Course, Enrollment };

// الاستعلام عن متعدد لمتعدد
const getStudentWithCourses = async (req, res) => {
  try {
    const student = await Student.findByPk(req.params.id, {
      include: [{
        model: Course,
        as: 'courses',
        through: {
          attributes: ['enrollmentDate', 'grade']
        }
      }]
    });

    res.status(200).json({
      success: true,
      data: student
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// تسجيل الطالب في المقرر
const enrollStudent = async (req, res) => {
  try {
    const { studentId, courseId, grade } = req.body;

    const enrollment = await Enrollment.create({
      studentId,
      courseId,
      grade
    });

    res.status(201).json({
      success: true,
      data: enrollment
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};

// models/Student.js
const mongoose = require('mongoose');

const studentSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true
  },
  email: {
    type: String,
    required: true,
    unique: true
  },
  courses: [{
    course: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Course'
    },
    enrollmentDate: {
      type: Date,
      default: Date.now
    },
    grade: String
  }]
}, {
  timestamps: true
});

const Student = mongoose.model('Student', studentSchema);
module.exports = Student;

// models/Course.js
const mongoose = require('mongoose');

const courseSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true
  },
  code: {
    type: String,
    required: true,
    unique: true
  },
  students: [{
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Student'
  }]
}, {
  timestamps: true
});

const Course = mongoose.model('Course', courseSchema);
module.exports = Course;

// الاستعلام عن متعدد لمتعدد
const getStudentWithCourses = async (req, res) => {
  try {
    const student = await Student
      .findById(req.params.id)
      .populate('courses.course', 'name code');

    res.status(200).json({
      success: true,
      data: student
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// تسجيل الطالب في المقرر
const enrollStudent = async (req, res) => {
  try {
    const { studentId, courseId, grade } = req.body;

    const student = await Student.findById(studentId);
    const course = await Course.findById(courseId);

    if (!student || !course) {
      return res.status(404).json({
        success: false,
        error: 'الطالب أو المقرر غير موجود'
      });
    }

    // الإضافة إلى مقررات الطالب
    student.courses.push({
      course: courseId,
      grade
    });
    await student.save();

    // الإضافة إلى طلاب المقرر
    course.students.push(studentId);
    await course.save();

    res.status(201).json({
      success: true,
      data: student
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};

const { fn, col, literal } = require('sequelize');

// عد المنشورات لكل مستخدم
const countPostsPerUser = async (req, res) => {
  try {
    const users = await User.findAll({
      attributes: [
        'id',
        'username',
        [fn('COUNT', col('posts.id')), 'postCount']
      ],
      include: [{
        model: Post,
        as: 'posts',
        attributes: []
      }],
      group: ['User.id'],
      raw: true
    });

    res.status(200).json({
      success: true,
      data: users
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// المتوسط، المجموع، الأدنى، الأقصى
const getStatistics = async (req, res) => {
  try {
    const stats = await Post.findAll({
      attributes: [
        [fn('COUNT', col('id')), 'totalPosts'],
        [fn('AVG', col('viewCount')), 'averageViews'],
        [fn('SUM', col('viewCount')), 'totalViews'],
        [fn('MIN', col('createdAt')), 'oldestPost'],
        [fn('MAX', col('createdAt')), 'newestPost']
      ],
      raw: true
    });

    res.status(200).json({
      success: true,
      data: stats[0]
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// عد المنشورات لكل مستخدم
const countPostsPerUser = async (req, res) => {
  try {
    const result = await Post.aggregate([
      {
        $group: {
          _id: '$author',
          postCount: { $sum: 1 }
        }
      },
      {
        $lookup: {
          from: 'users',
          localField: '_id',
          foreignField: '_id',
          as: 'userInfo'
        }
      },
      {
        $unwind: '$userInfo'
      },
      {
        $project: {
          _id: 1,
          username: '$userInfo.username',
          email: '$userInfo.email',
          postCount: 1
        }
      },
      {
        $sort: { postCount: -1 }
      }
    ]);

    res.status(200).json({
      success: true,
      data: result
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// الإحصائيات مع التجميع
const getStatistics = async (req, res) => {
  try {
    const stats = await Post.aggregate([
      {
        $group: {
          _id: null,
          totalPosts: { $sum: 1 },
          averageViews: { $avg: '$viewCount' },
          totalViews: { $sum: '$viewCount' },
          minViews: { $min: '$viewCount' },
          maxViews: { $max: '$viewCount' }
        }
      }
    ]);

    res.status(200).json({
      success: true,
      data: stats[0]
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// في تعريف النموذج
const User = sequelize.define('User', {
  // ... الحقول
}, {
  indexes: [
    {
      unique: true,
      fields: ['email']
    },
    {
      fields: ['username']
    },
    {
      fields: ['createdAt', 'isActive']
    },
    {
      type: 'FULLTEXT',
      fields: ['bio']
    }
  ]
});

const userSchema = new mongoose.Schema({
  email: {
    type: String,
    required: true,
    unique: true,
    index: true
  },
  username: {
    type: String,
    index: true
  },
  bio: {
    type: String
  }
});

// فهرس مركب
userSchema.index({ createdAt: -1, isActive: 1 });

// فهرس نصي للبحث
userSchema.index({ bio: 'text' });

const transferFunds = async (req, res) => {
  const t = await sequelize.transaction();

  try {
    const { fromUserId, toUserId, amount } = req.body;

    // الخصم من المرسل
    await Account.decrement('balance', {
      by: amount,
      where: { userId: fromUserId },
      transaction: t
    });

    // الإضافة إلى المستلم
    await Account.increment('balance', {
      by: amount,
      where: { userId: toUserId },
      transaction: t
    });

    // إنشاء سجل المعاملة
    await Transaction.create({
      fromUserId,
      toUserId,
      amount,
      type: 'transfer'
    }, { transaction: t });

    await t.commit();

    res.status(200).json({
      success: true,
      message: 'اكتمل التحويل بنجاح'
    });
  } catch (error) {
    await t.rollback();

    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};

const transferFunds = async (req, res) => {
  const session = await mongoose.startSession();
  session.startTransaction();

  try {
    const { fromUserId, toUserId, amount } = req.body;

    // الخصم من المرسل
    await Account.findOneAndUpdate(
      { userId: fromUserId },
      { $inc: { balance: -amount } },
      { session }
    );

    // الإضافة إلى المستلم
    await Account.findOneAndUpdate(
      { userId: toUserId },
      { $inc: { balance: amount } },
      { session }
    );

    // إنشاء سجل المعاملة
    await Transaction.create([{
      fromUserId,
      toUserId,
      amount,
      type: 'transfer'
    }], { session });

    await session.commitTransaction();
    session.endSession();

    res.status(200).json({
      success: true,
      message: 'اكتمل التحويل بنجاح'
    });
  } catch (error) {
    await session.abortTransaction();
    session.endSession();

    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};